322 lines
19 KiB
SQL
322 lines
19 KiB
SQL
-- ============================================================================
|
||
-- 给【已存在的表】补列(ADD COLUMN)—— MySQL 手动执行版
|
||
-- ============================================================================
|
||
--
|
||
-- 背景:
|
||
-- 本项目的 alembic 自动迁移是【关闭】的(engine.py 中
|
||
-- `# await _run_alembic_upgrade(url)` 被注释)。`Base.metadata.create_all`
|
||
-- 只会建【新表】,对【已存在的表】既不会补建索引、也不会补加新列。
|
||
-- 因此当我们在某个 ORM model 上新增了一列时,【全新部署】会随建表一并带上,
|
||
-- 但【已有数据的 MySQL 部署】上这一列并不会自动出现,代码读写该列会报错。
|
||
--
|
||
-- 约定(重要):
|
||
-- 今后只要有代码给某张【已存在的表】新增字段(在 ORM model 上加列),就必须
|
||
-- 把对应的 `ADD COLUMN` 语句以下面的【幂等】写法补进本文件,并在迁移目录里
|
||
-- 保留对应的 alembic 版本文件,二者保持一致。运维在升级老库时整文件执行本脚本
|
||
-- 即可补齐所有缺失的列。
|
||
--
|
||
-- 用法:
|
||
-- 1) 先选中目标库(与 database.mysql_url 里的库名一致):
|
||
-- USE your_deerflow_db;
|
||
-- 2) 直接整文件执行即可。本脚本是【幂等】的:已存在的列会跳过,不会报错,
|
||
-- 可重复执行。
|
||
--
|
||
-- 注意:
|
||
-- * 列定义必须与 ORM model / alembic migration 完全一致(类型、长度、可空性)。
|
||
-- * 新增列一律建议 NULL 或带 DEFAULT,避免在大表上回填导致长时间锁表。
|
||
-- * MySQL 8.0 在线 DDL 默认 ALGORITHM=INPLACE 一般不阻塞写入,但仍建议低峰执行。
|
||
-- ============================================================================
|
||
|
||
-- ---- 通用幂等封装:仅当列不存在时才新增 --------------------------------------
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS _add_column_if_missing //
|
||
CREATE PROCEDURE _add_column_if_missing(
|
||
IN p_table VARCHAR(64),
|
||
IN p_column VARCHAR(64),
|
||
IN p_definition VARCHAR(255) -- 列定义,如 'VARCHAR(128) NULL DEFAULT NULL'
|
||
)
|
||
BEGIN
|
||
DECLARE v_exists INT DEFAULT 0;
|
||
SELECT COUNT(*) INTO v_exists
|
||
FROM information_schema.columns
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = p_table
|
||
AND column_name = p_column;
|
||
|
||
IF v_exists = 0 THEN
|
||
SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
SELECT CONCAT('ADDED ', p_table, '.', p_column) AS result;
|
||
ELSE
|
||
SELECT CONCAT('SKIPPED ', p_table, '.', p_column, ' (already exists)') AS result;
|
||
END IF;
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- ---- skills ------------------------------------------------------------------
|
||
-- name_zh: 技能的中文显示名(用户配置或 LLM 生成),对应 migration 20260609_01。
|
||
-- ORM: SkillRow.name_zh = String(128), nullable=True。
|
||
CALL _add_column_if_missing('skills', 'name_zh', 'VARCHAR(128) NULL DEFAULT NULL');
|
||
-- detail: 技能的长文介绍,对应 migration 20260518_01。
|
||
-- ORM: SkillRow.detail = Text, nullable=True。
|
||
CALL _add_column_if_missing('skills', 'detail', 'TEXT NULL DEFAULT NULL');
|
||
-- square_id: 技能所属的发布广场(发布广场功能),对应 migration 20260611_01。
|
||
-- ORM: SkillRow.square_id = String(64), nullable=False, default=''。
|
||
CALL _add_column_if_missing('skills', 'square_id', "VARCHAR(64) NOT NULL DEFAULT ''");
|
||
-- always_on: 常驻(免压缩) —— 开启技能压缩后仍全文注入系统提示词,对应 migration 20260616_03。
|
||
-- ORM: SkillRow.always_on = Boolean, nullable=False, default=False。
|
||
CALL _add_column_if_missing('skills', 'always_on', 'TINYINT(1) NOT NULL DEFAULT 0');
|
||
|
||
-- ---- agents ------------------------------------------------------------------
|
||
-- square_id: 智能体所属的发布广场(发布广场功能),对应 migration 20260611_01。
|
||
-- ORM: AgentRow.square_id = String(64), nullable=False, default=''。
|
||
CALL _add_column_if_missing('agents', 'square_id', "VARCHAR(64) NOT NULL DEFAULT ''");
|
||
|
||
-- ---- roundtable_drafts -------------------------------------------------------
|
||
-- task_id: 外部任务 id(无界嵌入抽屉传入),对应 migration 20260611_01。非空时草稿
|
||
-- 按 task_id 共享、不再按 user_id 分权。ORM: RoundtableDraftRow.task_id =
|
||
-- String(128), nullable=True, index=True。
|
||
CALL _add_column_if_missing('roundtable_drafts', 'task_id', 'VARCHAR(128) NULL DEFAULT NULL');
|
||
|
||
-- orchestration_plan: dag 模式分层编排计划 JSON(stages/阶段目标/总控提示词/finalSynthesis),
|
||
-- 对应 migration 20260612_01。驱动后端 _run_dag + 前端大弹窗分层流程图。
|
||
-- ORM: RoundtableJobRow.orchestration_plan = PortableLongText(LONGTEXT), nullable。
|
||
CALL _add_column_if_missing('roundtable_jobs', 'orchestration_plan', 'LONGTEXT NULL DEFAULT NULL');
|
||
|
||
-- seat_mode: 业务链条「默认席位执行模式」(flash/thinking/pro/ultra),对应 migration 20260615_01。
|
||
-- 选入研讨时弹窗默认用它(仍可临时改)。ORM: RoundtableChainRow.seat_mode = String(16), nullable。
|
||
CALL _add_column_if_missing('roundtable_chains', 'seat_mode', 'VARCHAR(16) NULL DEFAULT NULL');
|
||
|
||
-- gather_first: 业务链条「研讨前并行取数」开关,对应 migration 20260620_02。True=选入研讨时研讨前先
|
||
-- 跑一轮全席位并行取数(只调技能、不研讨),产出作为共享资料注入研讨轮。逐条 opt-in。
|
||
-- ORM: RoundtableChainRow.gather_first = Boolean, NOT NULL, server_default '0'。
|
||
CALL _add_column_if_missing('roundtable_chains', 'gather_first', 'TINYINT(1) NOT NULL DEFAULT 0');
|
||
|
||
-- enabled: 业务链条启停开关,对应 migration 20260819_02。True=启用(可选入研讨);
|
||
-- False=停用(管理列表仍可见)。默认启用,存量行补列后也为 True。
|
||
-- ORM: RoundtableChainRow.enabled = Boolean, NOT NULL, server_default '1'。
|
||
CALL _add_column_if_missing('roundtable_chains', 'enabled', 'TINYINT(1) NOT NULL DEFAULT 1');
|
||
|
||
-- ---- 岗位 (positions) 订阅系统,对应 migration 20260616_01 -------------------
|
||
-- positions / position_tag_bindings 是【新表】,由 create_all 自动建,无需补列。
|
||
-- 以下是给【已存在的表】补的列:
|
||
-- users.position_id: 用户所属岗位(1:1)。ORM: UserRow.position_id = String(64) NULL。
|
||
CALL _add_column_if_missing('users', 'position_id', 'VARCHAR(64) NULL DEFAULT NULL');
|
||
-- agent_favorites: origin 区分用户自收藏 vs 岗位授予;position_id 记授予岗位。
|
||
CALL _add_column_if_missing('agent_favorites', 'origin', "VARCHAR(16) NOT NULL DEFAULT 'user'");
|
||
CALL _add_column_if_missing('agent_favorites', 'position_id', 'VARCHAR(64) NULL DEFAULT NULL');
|
||
-- scheduled_task_subscriptions: 同上。
|
||
CALL _add_column_if_missing('scheduled_task_subscriptions', 'origin', "VARCHAR(16) NOT NULL DEFAULT 'user'");
|
||
CALL _add_column_if_missing('scheduled_task_subscriptions', 'position_id', 'VARCHAR(64) NULL DEFAULT NULL');
|
||
|
||
-- knowledge_notes.folder: 用户自建目录(斜杠分隔路径),驱动产品目录树;NULL=未归类。
|
||
-- (knowledge_folders / knowledge_extract_templates 为【新表】,由 create_all 自动建,无需在此补列。)
|
||
CALL _add_column_if_missing('knowledge_notes', 'folder', 'VARCHAR(512) NULL DEFAULT NULL');
|
||
|
||
-- ---- taskId 深链会商独立存储,对应 migration 20260618_01 ----------------------
|
||
-- roundtable_task_drafts 是【新表】(taskId 聊天记录,只按 task_id 不分权),由 create_all
|
||
-- 自动建,无需补列。以下给【已存在的】roundtable_jobs 补 task_id 列:
|
||
-- task_id: 非空 = task 作业(按 task 共享、不按 user 分权,报告写回 roundtable_task_drafts)。
|
||
-- ORM: RoundtableJobRow.task_id = String(128), nullable, indexed。
|
||
CALL _add_column_if_missing('roundtable_jobs', 'task_id', 'VARCHAR(128) NULL DEFAULT NULL');
|
||
|
||
-- ---- position_roundtable_sessions: opt-in task sharing, migration 20260808_01 ----
|
||
-- Existing records remain personal (`task_scoped = 0`). New records become
|
||
-- shared only when the caller explicitly sends a non-empty route taskId.
|
||
CALL _add_column_if_missing('position_roundtable_sessions', 'task_scoped', 'TINYINT(1) NOT NULL DEFAULT 0');
|
||
|
||
-- ---- 按钮管理「外部链接」追加登录参数,对应 migration 20260625_01 ----------------
|
||
-- login_params_json: url 外链追加的登录参数列表 JSON([{key,source,custom_value},...]),
|
||
-- 由前端运行时从 userInfo 解析后拼到地址尾部;NULL/空 = 不追加(旧行为)。
|
||
-- ORM: TaskButtonRow.login_params_json = PortableJSON()(MySQL 落 TEXT)。
|
||
CALL _add_column_if_missing('task_buttons', 'login_params_json', 'TEXT NULL DEFAULT NULL');
|
||
|
||
-- ---- users.approved: 登录白名单放行标记(黑名单模式),对应 migration 20260625_03 ----
|
||
-- 系统设置 login_whitelist.enabled 打开后,仅被管理员显式「取消放行」(approved=0) 的
|
||
-- 用户被拦;新账号(含从未登录过的用户)默认 1(已放行),admin 永远放行。DEFAULT 1 让
|
||
-- 存量用户(执行本脚本补列时)一并置为已放行,避免首次打开开关把老用户锁死。
|
||
-- ORM: UserRow.approved = Boolean, NOT NULL, default True, server_default '1'。
|
||
CALL _add_column_if_missing('users', 'approved', 'TINYINT(1) NOT NULL DEFAULT 1');
|
||
|
||
-- ---- users.login_password: 账号级「登录口令」(明文),对应 migration 20260625_04 ----
|
||
-- 管理员在白名单管理页给个别账号设置的明文登录口令。非空 → 该账号用户名直登
|
||
-- (/api/v1/auth/login/username) 必须带匹配的 ?password=(优先于全局共享口令门);
|
||
-- NULL/空 → 走原有共享口令门/免口令逻辑。明文存储以便随时复制完整登录链接。
|
||
-- ORM: UserRow.login_password = String(255), nullable。
|
||
CALL _add_column_if_missing('users', 'login_password', 'VARCHAR(255) NULL DEFAULT NULL');
|
||
|
||
-- ---- compact_menu_overrides.menu_type: 简介模式菜单分套(A/B),对应 migration 20260819_01 ----
|
||
-- 两套网页部署各自一份左侧简介菜单。存量行视为类型 A;类型 B 的 node_id 带 B:: 前缀,
|
||
-- 以免在仍以 node_id 为单列主键的旧库上撞主键。
|
||
-- ORM: CompactMenuOverrideRow.menu_type = String(32), NOT NULL, default A, server_default A。
|
||
CALL _add_column_if_missing('compact_menu_overrides', 'menu_type', "VARCHAR(32) NOT NULL DEFAULT 'A'");
|
||
|
||
-- ---- assistant answer feedback, migration 20260916_02 -----------------------
|
||
-- New columns first land nullable/defaulted so existing rows can be backfilled.
|
||
-- target_key is a SHA-256 idempotency digest; a four-string utf8mb4 unique key
|
||
-- would exceed legacy MySQL's index-byte limit.
|
||
CALL _add_column_if_missing('feedback', 'agent_id', "VARCHAR(128) NULL DEFAULT 'lead_agent'");
|
||
CALL _add_column_if_missing('feedback', 'target_key', 'VARCHAR(64) NULL DEFAULT NULL');
|
||
CALL _add_column_if_missing('feedback', 'reasons', 'TEXT NULL DEFAULT NULL');
|
||
CALL _add_column_if_missing('feedback', 'updated_at', 'DATETIME NULL DEFAULT NULL');
|
||
|
||
UPDATE feedback AS f
|
||
LEFT JOIN runs AS r ON r.run_id = f.run_id
|
||
SET f.agent_id = COALESCE(NULLIF(r.assistant_id, ''), NULLIF(f.agent_id, ''), 'lead_agent'),
|
||
f.reasons = COALESCE(f.reasons, '[]'),
|
||
f.updated_at = COALESCE(f.updated_at, f.created_at);
|
||
UPDATE feedback
|
||
SET target_key = SHA2(
|
||
CONCAT(
|
||
COALESCE(user_id, ''), CHAR(31),
|
||
thread_id, CHAR(31),
|
||
COALESCE(message_id, CONCAT('run:', run_id)), CHAR(31),
|
||
agent_id
|
||
),
|
||
256
|
||
)
|
||
WHERE target_key IS NULL OR target_key = '';
|
||
-- Legacy anonymous rows could bypass the old three-column unique index because
|
||
-- MySQL treats NULL values as distinct. Keep one canonical deterministic key
|
||
-- and make only duplicate historical rows unique by their feedback id.
|
||
UPDATE feedback AS duplicate_row
|
||
JOIN feedback AS canonical_row
|
||
ON canonical_row.target_key = duplicate_row.target_key
|
||
AND canonical_row.feedback_id < duplicate_row.feedback_id
|
||
SET duplicate_row.target_key = SHA2(
|
||
CONCAT(duplicate_row.target_key, CHAR(31), 'legacy:', duplicate_row.feedback_id),
|
||
256
|
||
);
|
||
|
||
ALTER TABLE feedback
|
||
MODIFY COLUMN agent_id VARCHAR(128) NOT NULL,
|
||
MODIFY COLUMN target_key VARCHAR(64) NOT NULL,
|
||
MODIFY COLUMN reasons TEXT NOT NULL,
|
||
MODIFY COLUMN updated_at DATETIME NOT NULL;
|
||
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'feedback'
|
||
AND index_name = 'uq_feedback_thread_run_user'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists > 0,
|
||
'ALTER TABLE `feedback` DROP INDEX `uq_feedback_thread_run_user`',
|
||
'SELECT ''SKIPPED uq_feedback_thread_run_user (not present)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'feedback'
|
||
AND index_name = 'uq_feedback_target_key'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists = 0,
|
||
'CREATE UNIQUE INDEX `uq_feedback_target_key` ON `feedback` (`target_key`)',
|
||
'SELECT ''SKIPPED uq_feedback_target_key (already exists)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'feedback'
|
||
AND index_name = 'ix_feedback_agent_created'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists = 0,
|
||
'CREATE INDEX `ix_feedback_agent_created` ON `feedback` (`agent_id`, `created_at`)',
|
||
'SELECT ''SKIPPED ix_feedback_agent_created (already exists)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ---- llmwiki_wiki_sync_states.last_sync_result -----------------------------
|
||
-- 最近一次增量同步的租约围栏统计(JSON；PortableJSON 在 MySQL 落 LONGTEXT)。
|
||
-- 对应 migration 20260920_01。
|
||
CALL _add_column_if_missing('llmwiki_wiki_sync_states', 'last_sync_result', 'LONGTEXT NULL');
|
||
|
||
-- ---- 清理临时存储过程 --------------------------------------------------------
|
||
DROP PROCEDURE IF EXISTS _add_column_if_missing;
|
||
|
||
-- ---- roundtable_drafts.task_id 索引(幂等) -----------------------------------
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'roundtable_drafts'
|
||
AND index_name = 'ix_roundtable_drafts_task_id'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists = 0,
|
||
'CREATE INDEX `ix_roundtable_drafts_task_id` ON `roundtable_drafts` (`task_id`)',
|
||
'SELECT ''SKIPPED ix_roundtable_drafts_task_id (already exists)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ---- knowledge_notes.folder 索引(幂等) --------------------------------------
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'knowledge_notes'
|
||
AND index_name = 'ix_knowledge_notes_folder'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists = 0,
|
||
'CREATE INDEX `ix_knowledge_notes_folder` ON `knowledge_notes` (`folder`(191))',
|
||
'SELECT ''SKIPPED ix_knowledge_notes_folder (already exists)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ---- roundtable_jobs.task_id 索引(幂等) -------------------------------------
|
||
SET @idx_exists = (
|
||
SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'roundtable_jobs'
|
||
AND index_name = 'ix_roundtable_jobs_task_id'
|
||
);
|
||
SET @idx_ddl = IF(@idx_exists = 0,
|
||
'CREATE INDEX `ix_roundtable_jobs_task_id` ON `roundtable_jobs` (`task_id`)',
|
||
'SELECT ''SKIPPED ix_roundtable_jobs_task_id (already exists)'' AS result');
|
||
PREPARE stmt FROM @idx_ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ---- task_buttons ------------------------------------------------------------
|
||
-- 任务工作区跳转按钮的「追加登录态」选项,对应 migration 20260625_02。url 按钮可在外链
|
||
-- 后追加 token(token 登录的上游 token)+ username(登录用户名),供外部系统识别当前用户。
|
||
-- ORM: TaskButtonRow.append_auth = Boolean NOT NULL default False;
|
||
-- auth_token_param / auth_name_param = String(64), nullable, 读时归一化为 token/username。
|
||
CALL _add_column_if_missing('task_buttons', 'append_auth', 'TINYINT(1) NOT NULL DEFAULT 0');
|
||
CALL _add_column_if_missing('task_buttons', 'auth_token_param', "VARCHAR(64) NULL DEFAULT 'token'");
|
||
CALL _add_column_if_missing('task_buttons', 'auth_name_param', "VARCHAR(64) NULL DEFAULT 'username'");
|
||
|
||
-- ---- report_structures.retrieval_directions ---------------------------------
|
||
-- 报告结构可选「检索方向」JSON 列表。空 / NULL = 沿用原收集员拆词检索；
|
||
-- 非空则素材收集按指定方向检索。ORM: PortableJSON() 在 MySQL 落 TEXT。
|
||
CALL _add_column_if_missing('report_structures', 'retrieval_directions', 'TEXT NULL');
|
||
|
||
-- ---- report_structures.retrieval_skills -------------------------------------
|
||
-- 报告结构可选「检索来源」JSON 列表（技能名）。空 / NULL = 沿用原收集员工具；
|
||
-- 非空则素材收集调用指定技能。ORM: PortableJSON() 在 MySQL 落 TEXT。
|
||
CALL _add_column_if_missing('report_structures', 'retrieval_skills', 'TEXT NULL');
|
||
|
||
-- ---- 执行后自检:确认本批次涉及的列都已存在 ---------------------------------
|
||
SELECT table_name, column_name
|
||
FROM information_schema.columns
|
||
WHERE table_schema = DATABASE()
|
||
AND ((table_name = 'skills' AND column_name IN ('name_zh', 'detail', 'square_id', 'always_on'))
|
||
OR (table_name = 'agents' AND column_name IN ('square_id'))
|
||
OR (table_name = 'roundtable_drafts' AND column_name = 'task_id')
|
||
OR (table_name = 'roundtable_jobs' AND column_name = 'task_id')
|
||
OR (table_name = 'position_roundtable_sessions' AND column_name = 'task_scoped')
|
||
OR (table_name = 'task_buttons' AND column_name IN ('append_auth', 'auth_token_param', 'auth_name_param'))
|
||
OR (table_name = 'compact_menu_overrides' AND column_name = 'menu_type')
|
||
OR (table_name = 'roundtable_chains' AND column_name = 'enabled')
|
||
OR (table_name = 'llmwiki_wiki_sync_states' AND column_name = 'last_sync_result')
|
||
OR (table_name = 'report_structures' AND column_name IN ('retrieval_directions', 'retrieval_skills')))
|
||
ORDER BY table_name, column_name;
|