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>
39 lines
2.1 KiB
SQL
39 lines
2.1 KiB
SQL
-- =============================================
|
||
-- 租户-项目两级架构 - 建表脚本
|
||
-- =============================================
|
||
|
||
-- 1. 项目表
|
||
CREATE TABLE IF NOT EXISTS system_project (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '项目编号',
|
||
tenant_id BIGINT NOT NULL COMMENT '所属租户编号',
|
||
name VARCHAR(100) NOT NULL COMMENT '项目名称',
|
||
code VARCHAR(50) NOT NULL COMMENT '项目编码（如楼盘编号）',
|
||
status TINYINT DEFAULT 0 COMMENT '状态（0=正常, 1=禁用）',
|
||
contact_name VARCHAR(30) COMMENT '项目联系人',
|
||
contact_mobile VARCHAR(30) COMMENT '联系手机',
|
||
address VARCHAR(500) COMMENT '项目地址',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
||
UNIQUE KEY uk_tenant_code (tenant_id, code, deleted),
|
||
INDEX idx_tenant (tenant_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目表';
|
||
|
||
-- 2. 用户-项目关联表
|
||
CREATE TABLE IF NOT EXISTS system_user_project (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
|
||
user_id BIGINT NOT NULL COMMENT '用户编号',
|
||
project_id BIGINT NOT NULL COMMENT '项目编号',
|
||
tenant_id BIGINT NOT NULL COMMENT '租户编号',
|
||
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
||
UNIQUE KEY uk_user_project (user_id, project_id, deleted),
|
||
INDEX idx_project (project_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户-项目关联表';
|