20250319-chat-preset.sql

-------------------------------------------------------------------------------
-- 1~4 合并：ChatPreset 和 ChatPresetSpan 表的创建 + ModelReference 数据插入和更新
-------------------------------------------------------------------------------

-- ChatPreset 表
CREATE TABLE `ChatPreset`
(
    `Id`        INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    `Name`      TEXT          NOT NULL,
    `UserId`    INTEGER       NOT NULL,
    `UpdatedAt` TEXT          NOT NULL,
    CONSTRAINT `FK_ChatPreset_User`
        FOREIGN KEY (`UserId`)
        REFERENCES `User`(`Id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- 在 SQLite 中需要单独创建非主键索引
CREATE INDEX `IX_ChatPreset_UserId` ON `ChatPreset`(`UserId`);
CREATE INDEX `IX_ChatPreset_Name`   ON `ChatPreset`(`Name`);

-- ChatPresetSpan 表
CREATE TABLE `ChatPresetSpan`
(
    `ChatPresetId`  INTEGER NOT NULL,
    `SpanId`        INTEGER NOT NULL,
    `ChatConfigId`  INTEGER NOT NULL,
    -- SQLite 没有 BIT 类型，可用 INTEGER(0或1) 替代；也可加 CHECK(`Enabled` IN (0,1))
    `Enabled`       INTEGER NOT NULL DEFAULT 1,

    CONSTRAINT `PK_ChatPresetSpan`
        PRIMARY KEY (`ChatPresetId`, `SpanId`),

    CONSTRAINT `FK_ChatPresetSpan_Preset`
        FOREIGN KEY (`ChatPresetId`)
        REFERENCES `ChatPreset`(`Id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT `FK_ChatPresetSpan_Config`
        FOREIGN KEY (`ChatConfigId`)
        REFERENCES `ChatConfig`(`Id`)
        ON UPDATE CASCADE
        -- SQL Server 中的 ON DELETE NO ACTION 对应 SQLite 的 NO ACTION/RESTRICT
        ON DELETE NO ACTION
);

CREATE INDEX `IX_ChatPresetSpan_Config` ON `ChatPresetSpan`(`ChatConfigId`);

-- 插入示例数据 (ModelReference)
INSERT INTO `ModelReference`
    (`Id`, `ProviderId`, `Name`, `DisplayName`, `PublishDate`, `MinTemperature`,
     `MaxTemperature`, `AllowSearch`, `AllowVision`, `AllowSystemPrompt`,
     `AllowStreaming`, `ReasoningResponseKindId`, `ContextWindow`, `MaxResponseTokens`,
     `TokenizerId`, `InputTokenPrice1M`, `OutputTokenPrice1M`, `CurrencyCode`)
VALUES
(120, 1, 'gpt-4.5-preview', 'gpt-4.5', '2025-02-27', 0,    2,    0, 1, 1, 1, 0, 128000, 16384, 2,  75, 150, 'USD'),
(520, 5, 'gpt-4.5-preview', 'gpt-4.5', '2025-02-27', 0,    2,    0, 1, 1, 1, 0, 128000, 16384, 2,  75, 150, 'USD'),
(737, 7, 'qwen2.5-omni-7b', 'qwen2.5-omni', '2025-03-27', 0,    1.99, 0, 1, 1, 1, 0, 32768, 2048, NULL, 0,   0,   'RMB'),
(621, 6, 'ernie-4.5-8k-preview', 'ERNIE-4.5', NULL,        0.01, 1,    1, 0, 1, 1, 0, 8192,  2048, NULL, 4,  16,  'RMB'),
(1608,16, 'deepseek-v3-250324', 'DeepSeek-V3(0324)', '2025-03-24', 0, 1, 0, 0, 1, 1, 0, 64000, 16384, NULL, 2,  8,   'RMB');

-- 更新示例数据 (ModelReference)
UPDATE `ModelReference`
SET
    `Name`              = 'gemini-2.5-pro-exp-03-25',
    `PublishDate`       = '2025-03-25',
    `ContextWindow`     = 1000000,
    `MaxResponseTokens` = 64000
WHERE `Id` = 1302;

-------------------------------------------------------------------------------

-- 提示：SQLite 默认不启用外键约束，若需要外键支持，请执行：
-- PRAGMA foreign_keys = ON;