forked from jkcl/reposync
104 lines
5.9 KiB
SQL
104 lines
5.9 KiB
SQL
-- Issue相关表结构
|
|
|
|
-- Issue主表
|
|
CREATE TABLE `issue` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`issue_id` varchar(50) NOT NULL COMMENT 'Issue唯一标识',
|
|
`project` varchar(50) NOT NULL COMMENT '工程名称',
|
|
`type` enum('GitHub','Gitee','Gitlink') NOT NULL COMMENT '仓库类型',
|
|
`title` varchar(500) NOT NULL COMMENT 'Issue标题',
|
|
`body` text COMMENT 'Issue描述',
|
|
`state` varchar(20) NOT NULL DEFAULT 'open' COMMENT 'Issue状态',
|
|
`number` varchar(50) COMMENT 'Issue编号',
|
|
`html_url` varchar(500) COMMENT 'Issue页面地址',
|
|
`api_url` varchar(500) COMMENT 'Issue API地址',
|
|
`assignee` varchar(100) COMMENT '负责人',
|
|
`labels` text COMMENT '标签(JSON格式)',
|
|
`milestone` varchar(100) COMMENT '里程碑',
|
|
`priority` int DEFAULT 0 COMMENT '优先级',
|
|
`issue_type` varchar(50) COMMENT 'Issue类型',
|
|
`security_hole` tinyint(1) DEFAULT FALSE COMMENT '是否为私有issue',
|
|
`created_at` datetime COMMENT '创建时间',
|
|
`updated_at` datetime COMMENT '更新时间',
|
|
`closed_at` datetime COMMENT '关闭时间',
|
|
`sync_status` tinyint(1) DEFAULT FALSE COMMENT '同步状态',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_issue_project_type_id` (`issue_id`, `project`, `type`),
|
|
KEY `idx_project_type` (`project`, `type`),
|
|
KEY `idx_sync_status` (`sync_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Issue信息表';
|
|
|
|
-- Issue同步映射表
|
|
CREATE TABLE `issue_sync_mapping` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`source_issue_id` bigint unsigned NOT NULL COMMENT '源Issue ID',
|
|
`target_issue_id` bigint unsigned NOT NULL COMMENT '目标Issue ID',
|
|
`source_type` enum('GitHub','Gitee','Gitlink') NOT NULL COMMENT '源仓库类型',
|
|
`target_type` enum('GitHub','Gitee','Gitlink') NOT NULL COMMENT '目标仓库类型',
|
|
`project` varchar(50) NOT NULL COMMENT '工程名称',
|
|
`sync_direction` enum('one_way','two_way') NOT NULL DEFAULT 'one_way' COMMENT '同步方向',
|
|
`sync_status` tinyint(1) DEFAULT FALSE COMMENT '同步状态',
|
|
`last_sync_time` datetime COMMENT '最后同步时间',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_source_target` (`source_issue_id`, `target_issue_id`),
|
|
KEY `idx_project` (`project`),
|
|
KEY `idx_sync_status` (`sync_status`),
|
|
FOREIGN KEY (`source_issue_id`) REFERENCES `issue`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`target_issue_id`) REFERENCES `issue`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Issue同步映射表';
|
|
|
|
-- Issue同步配置表
|
|
CREATE TABLE `issue_sync_config` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`project` varchar(50) NOT NULL COMMENT '工程名称',
|
|
`source_type` enum('GitHub','Gitee','Gitlink') NOT NULL COMMENT '源仓库类型',
|
|
`target_type` enum('GitHub','Gitee','Gitlink') NOT NULL COMMENT '目标仓库类型',
|
|
`source_repo` varchar(200) NOT NULL COMMENT '源仓库地址',
|
|
`target_repo` varchar(200) NOT NULL COMMENT '目标仓库地址',
|
|
`sync_direction` enum('one_way','two_way') NOT NULL DEFAULT 'one_way' COMMENT '同步方向',
|
|
`enable` tinyint(1) DEFAULT TRUE COMMENT '是否启用',
|
|
`auto_sync` tinyint(1) DEFAULT FALSE COMMENT '是否自动同步',
|
|
`sync_interval` int DEFAULT 3600 COMMENT '同步间隔(秒)',
|
|
`last_sync_time` datetime COMMENT '最后同步时间',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_project_source_target` (`project`, `source_type`, `target_type`),
|
|
KEY `idx_enable` (`enable`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Issue同步配置表';
|
|
|
|
-- Issue同步日志表
|
|
CREATE TABLE `issue_sync_log` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`sync_config_id` bigint unsigned NOT NULL COMMENT '同步配置ID',
|
|
`source_issue_id` bigint unsigned COMMENT '源Issue ID',
|
|
`target_issue_id` bigint unsigned COMMENT '目标Issue ID',
|
|
`operation` varchar(50) NOT NULL COMMENT '操作类型',
|
|
`status` varchar(20) NOT NULL COMMENT '操作状态',
|
|
`message` text COMMENT '操作消息',
|
|
`details` text COMMENT '详细信息',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_sync_config_id` (`sync_config_id`),
|
|
KEY `idx_create_time` (`create_time`),
|
|
FOREIGN KEY (`sync_config_id`) REFERENCES `issue_sync_config`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`source_issue_id`) REFERENCES `issue`(`id`) ON DELETE SET NULL,
|
|
FOREIGN KEY (`target_issue_id`) REFERENCES `issue`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Issue同步日志表';
|
|
|
|
CREATE TABLE IF NOT EXISTS sync_repo_mapping (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
repo_name VARCHAR(128) UNIQUE NOT NULL COMMENT '仓库名称',
|
|
enable BOOLEAN DEFAULT TRUE COMMENT '是否启用同步',
|
|
internal_repo_address VARCHAR(255) NOT NULL COMMENT '内部仓库地址',
|
|
inter_token VARCHAR(255) COMMENT '内部仓库token',
|
|
external_repo_address VARCHAR(255) NOT NULL COMMENT '外部仓库地址',
|
|
exter_token VARCHAR(255) COMMENT '外部仓库token',
|
|
sync_granularity ENUM('all', 'one') COMMENT '同步类型',
|
|
sync_direction ENUM('to_outer', 'to_inter') COMMENT '首次同步方向',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |