199 lines
6.7 KiB
SQL
199 lines
6.7 KiB
SQL
-- Model config field migration
|
|
-- Target:
|
|
-- 1. Move text-model generation params from llm_model_config columns into extra_config.
|
|
-- 2. Rename audio_model_config.audio_scene to audio_model_config.model_type.
|
|
-- 3. Keep ASR vocabulary on audio_model_config.extra_config.vocabulary_id.
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 1. Add new columns before deploying the new code.
|
|
-- Run these only if the columns/indexes do not already exist.
|
|
-- ---------------------------------------------------------------------
|
|
-- ALTER TABLE `llm_model_config`
|
|
-- ADD COLUMN `model_type` VARCHAR(32) NOT NULL DEFAULT 'text' COMMENT '模型类型: text / vector' AFTER `model_name`;
|
|
--
|
|
-- ALTER TABLE `llm_model_config`
|
|
-- ADD COLUMN `extra_config` JSON DEFAULT NULL COMMENT '模型差异化配置' AFTER `llm_timeout`;
|
|
--
|
|
-- ALTER TABLE `llm_model_config`
|
|
-- ADD KEY `idx_llm_model_config_type` (`model_type`);
|
|
--
|
|
-- ALTER TABLE `audio_model_config`
|
|
-- ADD COLUMN `model_type` VARCHAR(32) DEFAULT NULL COMMENT '模型类型: asr / voiceprint' AFTER `model_name`;
|
|
--
|
|
-- ALTER TABLE `audio_model_config`
|
|
-- ADD KEY `idx_audio_model_config_type` (`model_type`);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 2. Data migration.
|
|
-- Safe to run after a partial migration: old-column based moves only run
|
|
-- when those old columns still exist.
|
|
-- ---------------------------------------------------------------------
|
|
DELIMITER $$
|
|
|
|
DROP PROCEDURE IF EXISTS migrate_model_config_data $$
|
|
CREATE PROCEDURE migrate_model_config_data()
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'llm_model_config'
|
|
AND COLUMN_NAME = 'llm_temperature'
|
|
) THEN
|
|
UPDATE `llm_model_config`
|
|
SET
|
|
`model_type` = COALESCE(NULLIF(`model_type`, ''), 'text'),
|
|
`extra_config` = CASE
|
|
WHEN COALESCE(NULLIF(`model_type`, ''), 'text') = 'text' THEN
|
|
JSON_SET(
|
|
COALESCE(`extra_config`, JSON_OBJECT()),
|
|
'$.temperature', CAST(`llm_temperature` AS DECIMAL(5,2)),
|
|
'$.top_p', CAST(`llm_top_p` AS DECIMAL(5,2)),
|
|
'$.max_tokens', CAST(`llm_max_tokens` AS UNSIGNED),
|
|
'$.system_prompt', `llm_system_prompt`
|
|
)
|
|
ELSE COALESCE(`extra_config`, JSON_OBJECT())
|
|
END;
|
|
ELSE
|
|
UPDATE `llm_model_config`
|
|
SET
|
|
`model_type` = COALESCE(NULLIF(`model_type`, ''), 'text'),
|
|
`extra_config` = COALESCE(`extra_config`, JSON_OBJECT());
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND COLUMN_NAME = 'audio_scene'
|
|
) THEN
|
|
UPDATE `audio_model_config`
|
|
SET `model_type` = `audio_scene`
|
|
WHERE `model_type` IS NULL OR `model_type` = '';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND COLUMN_NAME = 'hot_word_group_id'
|
|
) THEN
|
|
UPDATE `audio_model_config` a
|
|
JOIN `hot_word_group` g ON g.`id` = a.`hot_word_group_id`
|
|
SET a.`extra_config` = JSON_SET(
|
|
COALESCE(a.`extra_config`, JSON_OBJECT()),
|
|
'$.vocabulary_id',
|
|
g.`vocabulary_id`
|
|
)
|
|
WHERE a.`model_type` = 'asr'
|
|
AND a.`hot_word_group_id` IS NOT NULL
|
|
AND g.`vocabulary_id` IS NOT NULL
|
|
AND g.`vocabulary_id` <> '';
|
|
END IF;
|
|
|
|
ALTER TABLE `audio_model_config`
|
|
MODIFY COLUMN `model_type` VARCHAR(32) NOT NULL COMMENT '模型类型: asr / voiceprint';
|
|
END $$
|
|
|
|
CALL migrate_model_config_data() $$
|
|
DROP PROCEDURE IF EXISTS migrate_model_config_data $$
|
|
|
|
DELIMITER ;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 3. Cleanup after the new code is deployed and verified.
|
|
-- The following block is idempotent for MySQL: it checks the current
|
|
-- schema before dropping legacy columns/indexes/constraints.
|
|
-- ---------------------------------------------------------------------
|
|
DELIMITER $$
|
|
|
|
DROP PROCEDURE IF EXISTS migrate_model_config_cleanup $$
|
|
CREATE PROCEDURE migrate_model_config_cleanup()
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'llm_model_config'
|
|
AND COLUMN_NAME = 'llm_temperature'
|
|
) THEN
|
|
ALTER TABLE `llm_model_config` DROP COLUMN `llm_temperature`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'llm_model_config'
|
|
AND COLUMN_NAME = 'llm_top_p'
|
|
) THEN
|
|
ALTER TABLE `llm_model_config` DROP COLUMN `llm_top_p`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'llm_model_config'
|
|
AND COLUMN_NAME = 'llm_max_tokens'
|
|
) THEN
|
|
ALTER TABLE `llm_model_config` DROP COLUMN `llm_max_tokens`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'llm_model_config'
|
|
AND COLUMN_NAME = 'llm_system_prompt'
|
|
) THEN
|
|
ALTER TABLE `llm_model_config` DROP COLUMN `llm_system_prompt`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND INDEX_NAME = 'idx_audio_model_config_scene'
|
|
) THEN
|
|
ALTER TABLE `audio_model_config` DROP INDEX `idx_audio_model_config_scene`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND COLUMN_NAME = 'audio_scene'
|
|
) THEN
|
|
ALTER TABLE `audio_model_config` DROP COLUMN `audio_scene`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND CONSTRAINT_NAME = 'fk_audio_model_config_hot_word_group_id'
|
|
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
|
|
) THEN
|
|
ALTER TABLE `audio_model_config` DROP FOREIGN KEY `fk_audio_model_config_hot_word_group_id`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND INDEX_NAME = 'idx_audio_model_config_hot_word_group_id'
|
|
) THEN
|
|
ALTER TABLE `audio_model_config` DROP INDEX `idx_audio_model_config_hot_word_group_id`;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'audio_model_config'
|
|
AND COLUMN_NAME = 'hot_word_group_id'
|
|
) THEN
|
|
ALTER TABLE `audio_model_config` DROP COLUMN `hot_word_group_id`;
|
|
END IF;
|
|
END $$
|
|
|
|
CALL migrate_model_config_cleanup() $$
|
|
DROP PROCEDURE IF EXISTS migrate_model_config_cleanup $$
|
|
|
|
DELIMITER ;
|