127 lines
6.7 KiB
Plaintext
127 lines
6.7 KiB
Plaintext
/**
|
||
* 表创建SQL定义
|
||
* 系统所需的所有表结构
|
||
*/
|
||
private static readonly TABLE_DEFINITIONS: Record<string, string> = {
|
||
// 工作空间表
|
||
workspaces: `
|
||
CREATE TABLE IF NOT EXISTS \`workspaces\` (
|
||
\`id\` int NOT NULL AUTO_INCREMENT COMMENT '空间ID（主键）',
|
||
\`name\` varchar(50) NOT NULL COMMENT '空间名称',
|
||
\`creator_id\` int NOT NULL COMMENT '创建者用户ID',
|
||
\`status\` tinyint NOT NULL DEFAULT '1' COMMENT '状态（1=活跃，0=停用）',
|
||
\`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
\`updated_at\` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (\`id\`),
|
||
KEY \`idx_creator_id\` (\`creator_id\`) COMMENT '创建者ID索引，提高查询效率'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工作空间信息表'
|
||
`,
|
||
|
||
// 系统用户表
|
||
system_users: `
|
||
CREATE TABLE IF NOT EXISTS \`system_users\` (
|
||
\`id\` int NOT NULL AUTO_INCREMENT COMMENT '用户ID（主键）',
|
||
\`username\` varchar(50) NOT NULL COMMENT '登录用户名（唯一）',
|
||
\`password\` varchar(100) NOT NULL COMMENT '加密密码',
|
||
\`email\` varchar(100) NOT NULL COMMENT '电子邮箱（唯一）',
|
||
\`phone\` varchar(20) NOT NULL COMMENT '手机号码（唯一）',
|
||
\`workspace_id\` int NOT NULL COMMENT '所属工作空间ID',
|
||
\`role_type\` varchar(30) NOT NULL DEFAULT 'user' COMMENT '角色类型',
|
||
\`role_name\` varchar(50) NOT NULL DEFAULT '普通用户' COMMENT '角色显示名称',
|
||
\`is_custom_role\` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否自定义角色（1=是，0=否）',
|
||
\`status\` tinyint NOT NULL DEFAULT '1' COMMENT '状态（1=启用，0=禁用）',
|
||
\`invited_by\` int DEFAULT NULL COMMENT '邀请人ID（NULL表示自主注册）',
|
||
\`invitation_token\` varchar(100) DEFAULT NULL COMMENT '邀请验证令牌',
|
||
\`last_login_at\` datetime DEFAULT NULL COMMENT '最后登录时间',
|
||
\`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
\`updated_at\` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (\`id\`),
|
||
UNIQUE KEY \`uk_username\` (\`username\`),
|
||
UNIQUE KEY \`uk_email\` (\`email\`),
|
||
UNIQUE KEY \`uk_phone\` (\`phone\`),
|
||
KEY \`idx_workspace_id\` (\`workspace_id\`),
|
||
KEY \`idx_invited_by\` (\`invited_by\`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表'
|
||
`,
|
||
|
||
// 用户角色表
|
||
user_roles: `
|
||
CREATE TABLE IF NOT EXISTS \`user_roles\` (
|
||
\`id\` int NOT NULL AUTO_INCREMENT COMMENT '角色ID（主键）',
|
||
\`type\` varchar(30) NOT NULL COMMENT '角色类型',
|
||
\`name\` varchar(50) NOT NULL COMMENT '角色显示名称',
|
||
\`is_custom\` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否自定义（1=是，0=否）',
|
||
\`workspace_id\` int NOT NULL COMMENT '所属工作空间ID',
|
||
\`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
\`updated_at\` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (\`id\`),
|
||
UNIQUE KEY \`uk_type_workspace\` (\`type\`, \`workspace_id\`),
|
||
KEY \`idx_workspace_id\` (\`workspace_id\`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色表'
|
||
`,
|
||
|
||
// 团队表
|
||
teams: `
|
||
CREATE TABLE IF NOT EXISTS \`teams\` (
|
||
\`id\` int NOT NULL AUTO_INCREMENT COMMENT '团队ID（主键）',
|
||
\`team_code\` varchar(30) NOT NULL COMMENT '团队唯一编码',
|
||
\`name\` varchar(50) NOT NULL COMMENT '团队名称',
|
||
\`db_host\` varchar(100) NOT NULL COMMENT '数据库主机地址',
|
||
\`db_name\` varchar(50) NOT NULL COMMENT '数据库名称',
|
||
\`db_username\` varchar(50) NOT NULL COMMENT '数据库用户名',
|
||
\`db_password\` varchar(100) NOT NULL COMMENT '数据库密码',
|
||
\`workspace_id\` int NOT NULL COMMENT '所属工作空间ID',
|
||
\`owner_id\` int NOT NULL COMMENT '团队管理员ID',
|
||
\`status\` tinyint NOT NULL DEFAULT '1' COMMENT '状态（1=正常，0=停用）',
|
||
\`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
\`updated_at\` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (\`id\`),
|
||
UNIQUE KEY \`uk_team_code\` (\`team_code\`),
|
||
KEY \`idx_workspace_id\` (\`workspace_id\`),
|
||
KEY \`idx_owner_id\` (\`owner_id\`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='团队信息表'
|
||
`,
|
||
|
||
// 用户-团队关系表
|
||
user_team_relations: `
|
||
CREATE TABLE IF NOT EXISTS \`user_team_relations\` (
|
||
\`id\` int NOT NULL AUTO_INCREMENT COMMENT '关系ID（主键）',
|
||
\`user_id\` int NOT NULL COMMENT '用户ID',
|
||
\`team_id\` int NOT NULL COMMENT '团队ID',
|
||
\`role\` varchar(20) NOT NULL DEFAULT 'member' COMMENT '角色（owner=拥有者，admin=管理员，member=成员）',
|
||
\`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
\`updated_at\` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (\`id\`),
|
||
UNIQUE KEY \`uk_user_team\` (\`user_id\`,\`team_id\`) COMMENT '确保用户在一个团队中只有一个角色',
|
||
KEY \`idx_team_id\` (\`team_id\`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户团队关系表'
|
||
`
|
||
};
|
||
|
||
/**
|
||
* 外键定义
|
||
* 定义表之间的关系
|
||
*/
|
||
private static readonly FOREIGN_KEY_DEFINITIONS: string[] = [
|
||
// 系统用户表的外键
|
||
`ALTER TABLE \`system_users\`
|
||
ADD CONSTRAINT \`fk_user_workspace\` FOREIGN KEY (\`workspace_id\`) REFERENCES \`workspaces\` (\`id\`),
|
||
ADD CONSTRAINT \`fk_user_inviter\` FOREIGN KEY (\`invited_by\`) REFERENCES \`system_users\` (\`id\`)`,
|
||
|
||
// 用户角色表的外键
|
||
`ALTER TABLE \`user_roles\`
|
||
ADD CONSTRAINT \`fk_role_workspace\` FOREIGN KEY (\`workspace_id\`) REFERENCES \`workspaces\` (\`id\`)`,
|
||
|
||
// 团队表的外键
|
||
`ALTER TABLE \`teams\`
|
||
ADD CONSTRAINT \`fk_team_workspace\` FOREIGN KEY (\`workspace_id\`) REFERENCES \`workspaces\` (\`id\`),
|
||
ADD CONSTRAINT \`fk_team_owner\` FOREIGN KEY (\`owner_id\`) REFERENCES \`system_users\` (\`id\`)`,
|
||
|
||
// 用户-团队关系表的外键
|
||
`ALTER TABLE \`user_team_relations\`
|
||
ADD CONSTRAINT \`fk_relation_user\` FOREIGN KEY (\`user_id\`) REFERENCES \`system_users\` (\`id\`),
|
||
ADD CONSTRAINT \`fk_relation_team\` FOREIGN KEY (\`team_id\`) REFERENCES \`teams\` (\`id\`)`
|
||
];
|
||
|
||
|