lzh
a2f500fa20
DO 迁移 (15个 TenantBaseDO → ProjectBaseDO): - IoT: IotDeviceDO - Ops 核心: OpsOrderDO, OpsOrderEventDO, OpsOrderDispatchDO, OpsOrderQueueDO, OpsBusAreaDO, OpsAreaDeviceRelationDO, OpsDeviceTrajectoryDO - Ops 保洁: OpsOrderCleanExtDO, OpsCleanerStatusDO, OpsCleanerPerformanceMonthlyDO, OpsInspectionRecordDO, OpsInspectionRecordItemDO - Ops 安保: OpsOrderSecurityExtDO, OpsAreaSecurityUserDO IoT 适配: - IotDeviceRespDTO 新增 projectId 字段 - IotDeviceMessage 新增 projectId 字段 - IotDeviceMessageServiceImpl.appendDeviceMessage() 设置 projectId - IotCleanRuleMessageHandler 嵌套 ProjectUtils.execute() 设置项目上下文 缓存改造: - ProjectRedisCacheManager extends TenantRedisCacheManager，追加 :projectId 后缀 - ViewshTenantAutoConfiguration 替换为 ProjectRedisCacheManager SQL 迁移脚本 (sql/mysql/project/): - 01-create-tables.sql: system_project + system_user_project 建表 - 02-default-data.sql: 默认项目 + 用户关联回填 - 03-alter-business-tables.sql: 15 张表添加 project_id (NULL → 回填 → NOT NULL → 索引) - 04-index-audit.sql: 现有索引审计 + project_id 补充建议 - 99-rollback.sql: 完整回滚方案 附带修复: - fix(ops): UserDispatchStatusServiceImpl 添加缺失的 KEY_PREFIX 常量 Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
87 lines
4.6 KiB
SQL
87 lines
4.6 KiB
SQL
-- =============================================
|
||
-- 租户-项目两级架构 - 唯一索引审计脚本
|
||
-- 检查各业务表现有唯一键，评估是否需要将 project_id 纳入唯一约束
|
||
-- 执行顺序：在 03-alter-business-tables.sql 之后执行
|
||
-- =============================================
|
||
|
||
-- -----------------------------------------------
|
||
-- 查看各表当前的索引情况（审计用，执行前先 SHOW INDEX 确认）
|
||
-- -----------------------------------------------
|
||
SHOW INDEX FROM iot_device;
|
||
SHOW INDEX FROM ops_order;
|
||
SHOW INDEX FROM ops_order_event;
|
||
SHOW INDEX FROM ops_order_dispatch;
|
||
SHOW INDEX FROM ops_order_queue;
|
||
SHOW INDEX FROM ops_bus_area;
|
||
SHOW INDEX FROM ops_area_device_relation;
|
||
SHOW INDEX FROM ops_order_security_ext;
|
||
SHOW INDEX FROM ops_area_security_user;
|
||
SHOW INDEX FROM ops_order_clean_ext;
|
||
SHOW INDEX FROM ops_cleaner_status;
|
||
SHOW INDEX FROM ops_cleaner_performance_monthly;
|
||
SHOW INDEX FROM ops_device_trajectory;
|
||
SHOW INDEX FROM ops_inspection_record;
|
||
SHOW INDEX FROM ops_inspection_record_item;
|
||
|
||
-- -----------------------------------------------
|
||
-- 唯一键改造建议（根据实际业务语义决定是否执行）
|
||
-- 说明：若某张表的唯一键仅含 tenant_id，则需要将 project_id 也纳入，
|
||
-- 以确保在同一租户下不同项目之间的数据互不干扰。
|
||
-- -----------------------------------------------
|
||
|
||
-- ops_bus_area：区域编码在项目内唯一（原唯一键可能仅含 tenant_id + code）
|
||
-- 示例：DROP INDEX uk_tenant_code ON ops_bus_area;
|
||
-- 示例：ALTER TABLE ops_bus_area ADD UNIQUE KEY uk_project_code (tenant_id, project_id, code, deleted);
|
||
|
||
-- ops_area_device_relation：设备-区域关联在项目内唯一
|
||
-- 示例：DROP INDEX uk_area_device ON ops_area_device_relation;
|
||
-- 示例：ALTER TABLE ops_area_device_relation ADD UNIQUE KEY uk_project_area_device (project_id, area_id, device_id, deleted);
|
||
|
||
-- ops_area_security_user：安保用户-区域关联在项目内唯一
|
||
-- 示例：DROP INDEX uk_area_user ON ops_area_security_user;
|
||
-- 示例：ALTER TABLE ops_area_security_user ADD UNIQUE KEY uk_project_area_user (project_id, area_id, user_id, deleted);
|
||
|
||
-- ops_cleaner_status：保洁员状态在项目内唯一（每个项目内每个用户仅一条状态记录）
|
||
-- 示例：DROP INDEX uk_tenant_user ON ops_cleaner_status;
|
||
-- 示例：ALTER TABLE ops_cleaner_status ADD UNIQUE KEY uk_project_user (project_id, user_id, deleted);
|
||
|
||
-- ops_cleaner_performance_monthly：月度绩效在项目内唯一（project + user + year_month）
|
||
-- 示例：DROP INDEX uk_tenant_user_month ON ops_cleaner_performance_monthly;
|
||
-- 示例：ALTER TABLE ops_cleaner_performance_monthly ADD UNIQUE KEY uk_project_user_month (project_id, user_id, stat_year, stat_month, deleted);
|
||
|
||
-- iot_device：设备标识符在项目内唯一（device_key / product_key + device_name）
|
||
-- 示例：根据实际唯一键定义决定是否改造。
|
||
|
||
-- -----------------------------------------------
|
||
-- 验证：各表 project_id 回填情况
|
||
-- -----------------------------------------------
|
||
SELECT 'iot_device' AS tbl, COUNT(*) AS total, SUM(project_id IS NULL) AS null_count FROM iot_device
|
||
UNION ALL
|
||
SELECT 'ops_order', COUNT(*), SUM(project_id IS NULL) FROM ops_order
|
||
UNION ALL
|
||
SELECT 'ops_order_event', COUNT(*), SUM(project_id IS NULL) FROM ops_order_event
|
||
UNION ALL
|
||
SELECT 'ops_order_dispatch', COUNT(*), SUM(project_id IS NULL) FROM ops_order_dispatch
|
||
UNION ALL
|
||
SELECT 'ops_order_queue', COUNT(*), SUM(project_id IS NULL) FROM ops_order_queue
|
||
UNION ALL
|
||
SELECT 'ops_bus_area', COUNT(*), SUM(project_id IS NULL) FROM ops_bus_area
|
||
UNION ALL
|
||
SELECT 'ops_area_device_relation', COUNT(*), SUM(project_id IS NULL) FROM ops_area_device_relation
|
||
UNION ALL
|
||
SELECT 'ops_order_security_ext', COUNT(*), SUM(project_id IS NULL) FROM ops_order_security_ext
|
||
UNION ALL
|
||
SELECT 'ops_area_security_user', COUNT(*), SUM(project_id IS NULL) FROM ops_area_security_user
|
||
UNION ALL
|
||
SELECT 'ops_order_clean_ext', COUNT(*), SUM(project_id IS NULL) FROM ops_order_clean_ext
|
||
UNION ALL
|
||
SELECT 'ops_cleaner_status', COUNT(*), SUM(project_id IS NULL) FROM ops_cleaner_status
|
||
UNION ALL
|
||
SELECT 'ops_cleaner_performance_monthly', COUNT(*), SUM(project_id IS NULL) FROM ops_cleaner_performance_monthly
|
||
UNION ALL
|
||
SELECT 'ops_device_trajectory', COUNT(*), SUM(project_id IS NULL) FROM ops_device_trajectory
|
||
UNION ALL
|
||
SELECT 'ops_inspection_record', COUNT(*), SUM(project_id IS NULL) FROM ops_inspection_record
|
||
UNION ALL
|
||
SELECT 'ops_inspection_record_item', COUNT(*), SUM(project_id IS NULL) FROM ops_inspection_record_item;
|