466 lines
26 KiB
Markdown
466 lines
26 KiB
Markdown
# 架构决策记录 (ADR) - 数据库表结构设计和创建
|
||
|
||
## 背景
|
||
|
||
在 Salesforce 元数据拉取和部署功能（REQ-010）中，需要设计并创建数据库表结构以支持以下功能：
|
||
- Salesforce 组织配置管理
|
||
- 元数据包定义管理
|
||
- 元数据任务定义管理
|
||
- 元数据组件索引和版本管理
|
||
- 作业执行记录和监控
|
||
- 部署历史记录
|
||
- 部署组件结果明细
|
||
- 详细日志记录
|
||
|
||
根据 REQ-010-1 需求文档，需要设计 9 个核心数据库表，支持元数据管理的完整生命周期。
|
||
|
||
## 决策
|
||
|
||
采用 **MyBatis Plus + MySQL** 作为数据库持久层方案，具体设计如下：
|
||
|
||
### 1. 技术栈选择
|
||
|
||
- **持久层框架**: MyBatis Plus 3.x
|
||
- **数据库**: MySQL 8.0+
|
||
- **连接池**: HikariCP (Spring Boot 默认)
|
||
- **数据库迁移**: Flyway
|
||
- **缓存**: Redis (可选，用于缓存热点数据)
|
||
|
||
### 2. 数据库表设计
|
||
|
||
#### 表 1: datai_meta_org_config (Salesforce 组织配置表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_org_config`;
|
||
CREATE TABLE `datai_meta_org_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织名称',
|
||
`org_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织类型 (Production/Sandbox/Developer)',
|
||
`instance_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '实例URL',
|
||
`auth_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '认证类型 (OAuth2/JWT)',
|
||
`client_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'OAuth客户端ID',
|
||
`client_secret` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'OAuth客户端密钥',
|
||
`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
|
||
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码 (加密存储)',
|
||
`security_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '安全令牌',
|
||
`access_token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '访问令牌',
|
||
`refresh_token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '刷新令牌',
|
||
`token_expires_at` datetime NULL DEFAULT NULL COMMENT '令牌过期时间',
|
||
`api_version` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '60.0' COMMENT 'API版本',
|
||
`is_active` tinyint NOT NULL DEFAULT 1 COMMENT '是否激活',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE INDEX `uk_org_name_dept`(`dept_id` ASC, `org_name` ASC) USING BTREE,
|
||
INDEX `idx_org_type`(`org_type` ASC) USING BTREE,
|
||
INDEX `idx_is_active`(`is_active` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Salesforce组织配置表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 2: datai_meta_task (元数据任务定义表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_task`;
|
||
CREATE TABLE `datai_meta_task` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`task_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务名称',
|
||
`task_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务类型 (Retrieve/Deploy/QuickDeploy/Destructive)',
|
||
`org_config_id` bigint NOT NULL COMMENT '组织配置ID',
|
||
`package_xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Package XML配置',
|
||
`destructive_changes_xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'Destructive Changes XML配置',
|
||
`run_options` json NULL COMMENT '运行选项 (JSON格式)',
|
||
`schedule_cron` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '定时任务Cron表达式',
|
||
`is_active` tinyint NOT NULL DEFAULT 1 COMMENT '是否激活',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_org_config_id`(`org_config_id` ASC) USING BTREE,
|
||
INDEX `idx_task_type`(`task_type` ASC) USING BTREE,
|
||
INDEX `idx_is_active`(`is_active` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '元数据任务定义表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 3: datai_meta_package_item (元数据包定义明细表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_package_item`;
|
||
CREATE TABLE `datai_meta_package_item` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`group_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分组ID（可关联 TaskID 或自定义的 PackageID）',
|
||
`metadata_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '元数据类型 (e.g., ApexClass, CustomObject, Layout)',
|
||
`member_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '成员名称 (e.g., MyController, Account, *)',
|
||
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_group_id`(`group_id` ASC) USING BTREE,
|
||
INDEX `idx_metadata_type`(`metadata_type` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '元数据包定义明细表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 4: datai_meta_component (元数据组件索引表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_component`;
|
||
CREATE TABLE `datai_meta_component` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`org_config_id` bigint NOT NULL COMMENT '关联 datai_meta_org_config',
|
||
`metadata_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '类型 (e.g., ApexTrigger)',
|
||
`full_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组件全名 (e.g., AccountTrigger)',
|
||
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件路径名 (e.g., triggers/AccountTrigger.trigger)',
|
||
`last_retrieved_at` datetime NULL DEFAULT NULL COMMENT '最后一次拉取时间',
|
||
`latest_version_id` bigint NULL DEFAULT NULL COMMENT '指向最新的 component_version ID',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_org_config_id`(`org_config_id` ASC) USING BTREE,
|
||
INDEX `idx_metadata_type`(`metadata_type` ASC) USING BTREE,
|
||
INDEX `idx_latest_version_id`(`latest_version_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '元数据组件索引表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 5: datai_meta_component_version (元数据版本内容表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_component_version`;
|
||
CREATE TABLE `datai_meta_component_version` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`component_id` bigint NOT NULL COMMENT '关联 datai_meta_component',
|
||
`version_number` int NOT NULL COMMENT '版本号 (1, 2, 3...)',
|
||
`content_hash` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '内容的 SHA-256 哈希值。拉取时对比 Hash，相同则不新增版本。',
|
||
`content_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '实际的代码内容 (XML/Apex)。如果是二进制(StaticResource)，建议存 OSS 路径。',
|
||
`is_binary` tinyint NOT NULL DEFAULT 0 COMMENT '是否为二进制文件',
|
||
`commit_message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '(可选) 类似于 Git commit message，由系统自动生成或用户填写',
|
||
`job_execution_id` bigint NULL DEFAULT NULL COMMENT '是哪次任务产生的这个版本',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '版本创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_component_id`(`component_id` ASC) USING BTREE,
|
||
INDEX `idx_version_number`(`version_number` ASC) USING BTREE,
|
||
INDEX `idx_content_hash`(`content_hash` ASC) USING BTREE,
|
||
INDEX `idx_job_execution_id`(`job_execution_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '元数据版本内容表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 6: datai_meta_job_execution (作业执行记录表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_job_execution`;
|
||
CREATE TABLE `datai_meta_job_execution` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`task_id` bigint NOT NULL COMMENT '任务ID',
|
||
`org_config_id` bigint NOT NULL COMMENT '组织配置ID',
|
||
`job_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '作业类型 (Retrieve/Deploy/QuickDeploy/Destructive)',
|
||
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '状态 (Pending/Processing/Success/Failed/Partial_Success)',
|
||
`job_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Salesforce作业ID',
|
||
`async_process_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '异步进程ID',
|
||
`state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '作业状态 (Queued/InProgress/Completed/Failed/Canceled)',
|
||
`start_time` datetime NULL DEFAULT NULL COMMENT '开始时间',
|
||
`end_time` datetime NULL DEFAULT NULL COMMENT '结束时间',
|
||
`duration_seconds` int NULL DEFAULT NULL COMMENT '耗时(秒)',
|
||
`progress_percent` int NULL DEFAULT 0 COMMENT '进度百分比',
|
||
`number_components_total` int NULL DEFAULT NULL COMMENT '总组件数',
|
||
`number_components_deployed` int NULL DEFAULT NULL COMMENT '已部署组件数',
|
||
`number_component_errors` int NULL DEFAULT NULL COMMENT '错误组件数',
|
||
`number_test_warnings` int NULL DEFAULT NULL COMMENT '测试警告数',
|
||
`number_test_errors` int NULL DEFAULT NULL COMMENT '测试错误数',
|
||
`number_tests_total` int NULL DEFAULT NULL COMMENT '总测试数',
|
||
`number_tests_completed` int NULL DEFAULT NULL COMMENT '已完成测试数',
|
||
`run_test_result` json NULL COMMENT '测试结果 (JSON格式)',
|
||
`deploy_details` json NULL COMMENT '部署详情 (JSON格式)',
|
||
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '错误信息',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_task_id`(`task_id` ASC) USING BTREE,
|
||
INDEX `idx_org_config_id`(`org_config_id` ASC) USING BTREE,
|
||
INDEX `idx_job_type`(`job_type` ASC) USING BTREE,
|
||
INDEX `idx_status`(`status` ASC) USING BTREE,
|
||
INDEX `idx_job_id`(`job_id` ASC) USING BTREE,
|
||
INDEX `idx_create_time`(`create_time` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '作业执行记录表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 7: datai_meta_deploy_history (部署历史表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_deploy_history`;
|
||
CREATE TABLE `datai_meta_deploy_history` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`job_execution_id` bigint NOT NULL COMMENT '作业执行ID',
|
||
`org_config_id` bigint NOT NULL COMMENT '组织配置ID',
|
||
`deploy_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部署类型 (Full/Incremental/QuickDeploy/Destructive)',
|
||
`deploy_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部署状态 (Success/Failed/Partial_Success)',
|
||
`deploy_time` datetime NOT NULL COMMENT '部署时间',
|
||
`deploy_duration_seconds` int NULL DEFAULT NULL COMMENT '部署耗时(秒)',
|
||
`component_count` int NULL DEFAULT NULL COMMENT '组件数量',
|
||
`success_count` int NULL DEFAULT NULL COMMENT '成功数量',
|
||
`failure_count` int NULL DEFAULT NULL COMMENT '失败数量',
|
||
`code_coverage_percent` decimal(5,2) NULL DEFAULT NULL COMMENT '代码覆盖率(%)',
|
||
`test_run_count` int NULL DEFAULT NULL COMMENT '测试运行数',
|
||
`test_pass_count` int NULL DEFAULT NULL COMMENT '测试通过数',
|
||
`test_fail_count` int NULL DEFAULT NULL COMMENT '测试失败数',
|
||
`deploy_details` json NULL COMMENT '部署详情 (JSON格式)',
|
||
`error_summary` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '错误摘要',
|
||
`rollback_available` tinyint NOT NULL DEFAULT 0 COMMENT '是否可回滚',
|
||
`rollback_snapshot_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '回滚快照ID',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_job_execution_id`(`job_execution_id` ASC) USING BTREE,
|
||
INDEX `idx_org_config_id`(`org_config_id` ASC) USING BTREE,
|
||
INDEX `idx_deploy_type`(`deploy_type` ASC) USING BTREE,
|
||
INDEX `idx_deploy_status`(`deploy_status` ASC) USING BTREE,
|
||
INDEX `idx_deploy_time`(`deploy_time` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '部署历史表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 8: datai_meta_job_log (作业日志表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_job_log`;
|
||
CREATE TABLE `datai_meta_job_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`job_execution_id` bigint NOT NULL COMMENT '作业执行ID',
|
||
`log_level` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '日志级别 (INFO/WARN/ERROR/DEBUG)',
|
||
`log_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '日志消息',
|
||
`log_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '日志时间',
|
||
`log_source` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '日志来源',
|
||
`log_category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '日志分类',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_job_execution_id`(`job_execution_id` ASC) USING BTREE,
|
||
INDEX `idx_log_level`(`log_level` ASC) USING BTREE,
|
||
INDEX `idx_log_time`(`log_time` ASC) USING BTREE,
|
||
INDEX `idx_log_category`(`log_category` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '作业日志表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
#### 表 9: datai_meta_deploy_component_result (部署组件结果明细表)
|
||
|
||
```sql
|
||
DROP TABLE IF EXISTS `datai_meta_deploy_component_result`;
|
||
CREATE TABLE `datai_meta_deploy_component_result` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`deploy_history_id` bigint NOT NULL COMMENT '关联 datai_meta_deploy_history',
|
||
`component_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组件名 (e.g., MyClass)',
|
||
`component_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组件类型',
|
||
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件名',
|
||
`is_success` tinyint NOT NULL COMMENT '是否成功',
|
||
`is_changed` tinyint NOT NULL COMMENT '是否发生变更',
|
||
`line_number` int NULL DEFAULT NULL COMMENT '错误行号 (Salesforce 会返回)',
|
||
`column_number` int NULL DEFAULT NULL COMMENT '错误列号',
|
||
`problem` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '具体报错信息 (e.g., "Variable does not exist")',
|
||
`problem_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Error 或 Warning',
|
||
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门ID',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_deploy_history_id`(`deploy_history_id` ASC) USING BTREE,
|
||
INDEX `idx_component_type`(`component_type` ASC) USING BTREE,
|
||
INDEX `idx_is_success`(`is_success` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '部署组件结果明细表' ROW_FORMAT = DYNAMIC;
|
||
```
|
||
|
||
### 3. 索引策略
|
||
|
||
- **主键索引**: 所有表使用自增主键，使用 BTREE 索引
|
||
- **唯一索引**: 根据业务需求创建唯一索引（如组织名称、组件版本）
|
||
- **查询索引**: 根据常用查询条件创建索引，避免冗余索引
|
||
- **索引命名规范**:
|
||
- 唯一索引: uk_{table}_{description}
|
||
- 普通索引: idx_{field} 或 idx_{table}_{field}
|
||
|
||
### 4. 数据库迁移策略
|
||
|
||
- 使用 Flyway 进行数据库版本管理
|
||
- 迁移脚本命名: V{version}__{description}.sql
|
||
- 支持版本回滚
|
||
- 记录迁移历史
|
||
|
||
## 备选方案
|
||
|
||
### 方案 1: 使用 JPA + PostgreSQL
|
||
|
||
**优点**:
|
||
- JPA 提供对象关系映射，开发效率高
|
||
- PostgreSQL 支持更丰富的数据类型 (JSONB, ARRAY)
|
||
- PostgreSQL 支持全文搜索和地理空间数据
|
||
- PostgreSQL 的 MVCC 机制提供更好的并发性能
|
||
|
||
**缺点**:
|
||
- JPA 学习曲线较陡峭，复杂查询性能不佳
|
||
- PostgreSQL 运维成本较高
|
||
- 与现有技术栈 (MyBatis Plus) 不一致
|
||
- PostgreSQL 在云环境中的部署成本较高
|
||
|
||
### 方案 2: 使用 MyBatis Plus + MySQL (已选择)
|
||
|
||
**优点**:
|
||
- MyBatis Plus 与现有技术栈一致，学习成本低
|
||
- MySQL 运维成熟，云环境支持好
|
||
- MyBatis Plus 提供强大的 CRUD 操作和分页功能
|
||
- MyBatis Plus 支持代码生成器，提高开发效率
|
||
- MySQL 社区活跃，文档丰富
|
||
- MySQL 支持主从复制和读写分离
|
||
|
||
**缺点**:
|
||
- MySQL 对 JSON 类型的支持不如 PostgreSQL
|
||
- MySQL 的全文搜索功能相对较弱
|
||
- 复杂查询需要手写 SQL
|
||
|
||
## 影响
|
||
|
||
### 1. 系统架构影响
|
||
|
||
- **数据层**: 引入 6 个核心表，支持元数据管理全生命周期
|
||
- **持久层**: 使用 MyBatis Plus 简化数据访问层开发
|
||
- **缓存层**: 可选使用 Redis 缓存热点数据，提高查询性能
|
||
- **迁移层**: 使用 Flyway 管理数据库版本，支持版本回滚
|
||
|
||
### 2. 开发流程影响
|
||
|
||
- **开发效率**: MyBatis Plus 代码生成器可快速生成实体类和 Mapper
|
||
- **测试**: 需要编写单元测试和集成测试验证数据库操作
|
||
- **文档**: 需要维护数据库设计文档和 ER 图
|
||
- **协作**: 需要遵循数据库命名规范和设计规范
|
||
|
||
### 3. 运维管理影响
|
||
|
||
- **监控**: 需要监控数据库性能和慢查询
|
||
- **备份**: 需要定期备份数据库
|
||
- **迁移**: 使用 Flyway 管理数据库版本，支持平滑升级
|
||
- **容量规划**: 需要根据数据量规划数据库容量
|
||
|
||
## 风险
|
||
|
||
### 1. 技术风险
|
||
|
||
- **数据一致性风险**: 应用层需要保证数据一致性
|
||
- **性能风险**: 大量数据查询可能影响性能，需要优化索引
|
||
- **迁移风险**: 数据库迁移失败可能导致系统不可用
|
||
- **并发风险**: 高并发场景下可能出现死锁
|
||
|
||
### 2. 业务风险
|
||
|
||
- **数据丢失风险**: 部署历史和日志数据丢失可能影响追溯
|
||
- **容量风险**: 元数据组件和日志数据增长过快可能占用大量存储
|
||
- **兼容性风险**: 数据库版本升级可能导致兼容性问题
|
||
|
||
### 3. 实施风险
|
||
|
||
- **开发周期风险**: 数据库设计和开发周期较长
|
||
- **测试风险**: 需要大量的测试用例验证数据库操作
|
||
- **培训风险**: 开发人员需要熟悉 MyBatis Plus 和 MySQL 最佳实践
|
||
|
||
## 回滚策略
|
||
|
||
### 1. 数据库回滚
|
||
|
||
- **Flyway 回滚**: 使用 Flyway 的回滚功能回滚数据库版本
|
||
- **备份恢复**: 定期备份数据库，出现问题时恢复备份
|
||
- **数据迁移**: 编写数据迁移脚本，支持数据回滚
|
||
|
||
### 2. 代码回滚
|
||
|
||
- **版本控制**: 使用 Git 管理代码，支持快速回滚
|
||
- **功能开关**: 使用功能开关控制新功能的启用和禁用
|
||
- **灰度发布**: 支持灰度发布，降低风险
|
||
|
||
### 3. 应急预案
|
||
|
||
- **紧急回滚**: 准备紧急回滚脚本，快速回滚到稳定版本
|
||
- **数据修复**: 准备数据修复脚本，修复异常数据
|
||
- **监控告警**: 设置监控告警，及时发现和解决问题
|
||
|
||
## 验收标准
|
||
|
||
### 1. 功能验证
|
||
|
||
- ✅ 所有 9 个数据库表创建成功
|
||
- ✅ 表结构符合现有表规范（datai_ 前缀、基础字段一致）
|
||
- ✅ 索引创建正确，查询性能满足要求
|
||
- ✅ Flyway 迁移脚本执行成功
|
||
- ✅ 支持数据库版本回滚
|
||
|
||
### 2. 性能验证
|
||
|
||
- ✅ 单表查询响应时间 < 100ms
|
||
- ✅ 多表关联查询响应时间 < 500ms
|
||
- ✅ 批量插入 1000 条数据 < 1s
|
||
- ✅ 支持并发查询，不出现死锁
|
||
|
||
### 3. 可靠性验证
|
||
|
||
- ✅ 数据一致性得到保证
|
||
- ✅ 数据库迁移不丢失数据
|
||
- ✅ 支持数据库备份和恢复
|
||
|
||
### 4. 安全性验证
|
||
|
||
- ✅ 敏感字段 (密码、令牌) 加密存储
|
||
- ✅ 数据库访问权限正确配置
|
||
- ✅ SQL 注入防护有效
|
||
- ✅ 数据传输加密
|
||
|
||
## 视觉锚点
|
||
|
||
### Visual Reference
|
||
|
||
- [Authentication.canvas](../../Authentication.canvas) - 项目架构图
|
||
- **相关节点**: [集成核心](node_integration_core) - 提供与Salesforce的各种连接方式
|
||
- **相关节点**: [SessionManager](node_session_manager_detail) - 会话管理，提供登录服务
|
||
|
||
### Status
|
||
|
||
- [x] Draft
|
||
- [ ] Accepted
|
||
- [ ] Superceded
|
||
|
||
## 参考资料
|
||
|
||
1. [REQ-010-1.md](../requirements/REQ-010-1.md) - 数据库表结构设计和创建需求文档
|
||
2. [REQ-010.md](../requirements/REQ-010.md) - Salesforce元数据拉取和部署主需求文档
|
||
3. [MyBatis Plus 官方文档](https://baomidou.com/)
|
||
4. [MySQL 8.0 官方文档](https://dev.mysql.com/doc/refman/8.0/en/)
|
||
5. [Flyway 官方文档](https://flywaydb.org/documentation/)
|
||
6. [HikariCP 官方文档](https://github.com/brettwooldridge/HikariCP)
|
||
7. [Salesforce Metadata API Developer Guide](https://developer.salesforce.com/docs/atlas.en-us.api_meta.meta/api_meta/api_meta_intro.htm)
|
||
8. [0009-metadata-retrieve-deploy.md](0009-metadata-retrieve-deploy.md) - 元数据拉取和部署架构决策
|