platform_snowflake_ids.sql 3.0 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768
  1. -- ============================================================================
  2. -- 平台主键切换:PostgreSQL identity/serial → 应用侧雪花 ID
  3. --
  4. -- 目标库:PostgreSQL master(zsjz-ai 平台库)
  5. -- 执行方式:手工执行(项目无 Flyway / Liquibase);可重复执行
  6. --
  7. -- 变更后:
  8. -- agent.row_id 由 AgentEntity 的 IdType.ASSIGN_ID 生成
  9. -- case_info.id 由 CaseInfo 的 IdType.ASSIGN_ID 生成
  10. -- sys_user.id 由 SysUser 的 IdType.ASSIGN_ID 生成
  11. -- gateway_user_route.id 由 UserRouteRepository 的 IdWorker.getId() 生成
  12. --
  13. -- 存量行保留原 id;新行必须由应用显式提供 id,数据库不再有默认值/序列。
  14. -- 迁移前请停止 ai-server / ai-gateway,避免旧进程继续按无 id 插入。
  15. -- ============================================================================
  16. -- 1. identity 列(agent / case_info)
  17. ALTER TABLE IF EXISTS agent
  18. ALTER COLUMN row_id DROP IDENTITY IF EXISTS;
  19. ALTER TABLE IF EXISTS case_info
  20. ALTER COLUMN id DROP IDENTITY IF EXISTS;
  21. -- 2. serial 列(gateway_user_route;sys_user 已归档停用,不再改造)
  22. ALTER TABLE IF EXISTS gateway_user_route
  23. ALTER COLUMN id DROP IDENTITY IF EXISTS;
  24. ALTER TABLE IF EXISTS gateway_user_route
  25. ALTER COLUMN id DROP DEFAULT;
  26. -- 3. 删除遗留序列(identity 序列可能已被 DROP IDENTITY 一并删除)
  27. DROP SEQUENCE IF EXISTS public.dataagent_agent_row_id_seq;
  28. DROP SEQUENCE IF EXISTS public.case_info_id_seq;
  29. DROP SEQUENCE IF EXISTS public.gateway_user_route_id_seq;
  30. -- 4. 注释同步(避免继续显示“自增”;表不存在时跳过,保证脚本可用于部分环境)
  31. DO $$
  32. BEGIN
  33. IF to_regclass('public.agent') IS NOT NULL THEN
  34. COMMENT ON COLUMN agent.row_id IS '主键 ID(MyBatis-Plus 雪花算法生成)';
  35. END IF;
  36. IF to_regclass('public.case_info') IS NOT NULL THEN
  37. COMMENT ON COLUMN case_info.id IS '案件 ID(MyBatis-Plus 雪花算法生成)';
  38. END IF;
  39. IF to_regclass('public.gateway_user_route') IS NOT NULL THEN
  40. COMMENT ON COLUMN gateway_user_route.id IS '路由 ID(应用侧雪花算法生成)';
  41. END IF;
  42. END $$;
  43. -- 5. 校验:以下应为 0 行
  44. SELECT c.relname AS table_name,
  45. a.attname AS column_name,
  46. a.attidentity,
  47. pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
  48. pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS serial_sequence
  49. FROM pg_attribute a
  50. JOIN pg_class c ON c.oid = a.attrelid
  51. JOIN pg_namespace n ON n.oid = c.relnamespace
  52. LEFT JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
  53. WHERE n.nspname = 'public'
  54. AND c.relkind = 'r'
  55. AND a.attnum > 0
  56. AND NOT a.attisdropped
  57. AND (c.relname, a.attname) IN (
  58. ('agent', 'row_id'),
  59. ('case_info', 'id'),
  60. ('gateway_user_route', 'id')
  61. )
  62. AND (a.attidentity <> '' OR ad.adbin IS NOT NULL OR pg_get_serial_sequence(
  63. format('%I.%I', n.nspname, c.relname), a.attname) IS NOT NULL);