|
|
-- 完整的数据库初始化脚本
|
|
|
-- 使用前请先创建数据库: CREATE DATABASE Simple_CA DEFAULT CHARACTER SET = 'utf8mb4';
|
|
|
|
|
|
USE Simple_CA;
|
|
|
|
|
|
-- 如果表已存在则删除（谨慎使用）
|
|
|
-- DROP TABLE IF EXISTS `crls`;
|
|
|
-- DROP TABLE IF EXISTS `certificates`;
|
|
|
-- DROP TABLE IF EXISTS `ca_requests`;
|
|
|
-- DROP TABLE IF EXISTS `user_tokens`;
|
|
|
-- DROP TABLE IF EXISTS `users`;
|
|
|
|
|
|
-- 创建用户表
|
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
|
`created_at` datetime DEFAULT NULL,
|
|
|
`updated_at` datetime DEFAULT NULL,
|
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
|
`username` varchar(16) NOT NULL,
|
|
|
`password` varchar(255) NOT NULL,
|
|
|
`email` varchar(255) DEFAULT NULL,
|
|
|
`authority` int DEFAULT NULL COMMENT '权限，1表示系统管理员',
|
|
|
PRIMARY KEY (`id`),
|
|
|
UNIQUE KEY `username` (`username`),
|
|
|
KEY `idx_users_deleted_at` (`deleted_at`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
|
|
|
|
|
|
-- 创建用户Token表
|
|
|
CREATE TABLE IF NOT EXISTS `user_tokens` (
|
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
|
`created_at` datetime DEFAULT NULL,
|
|
|
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
|
`user_id` int NOT NULL,
|
|
|
`token` varchar(512) NOT NULL COMMENT 'JWT Token',
|
|
|
`expire_time` bigint NOT NULL,
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `idx_user_tokens_deleted_at` (`deleted_at`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户token表';
|
|
|
|
|
|
-- 创建CA请求表
|
|
|
CREATE TABLE IF NOT EXISTS `ca_requests` (
|
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
|
`created_at` datetime DEFAULT NULL COMMENT '字段创建时间',
|
|
|
`updated_at` datetime DEFAULT NULL COMMENT '更新时间',
|
|
|
`deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
|
|
|
`user_id` int NOT NULL COMMENT '申请证书的用户ID',
|
|
|
`state` int unsigned NOT NULL COMMENT '证书状态（1：待审核， 2： 审核通过， 3：审核未通过）',
|
|
|
`public_key` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '公钥',
|
|
|
`csr_content` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'CSR文件内容',
|
|
|
`country` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '国家',
|
|
|
`province` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '州市',
|
|
|
`locality` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地区',
|
|
|
`organization` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '组织',
|
|
|
`organization_unit_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门',
|
|
|
`common_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
|
|
|
`email_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `idx_ca_requests_deleted_at` (`deleted_at`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='请求列表';
|
|
|
|
|
|
-- 创建证书表
|
|
|
CREATE TABLE IF NOT EXISTS `certificates` (
|
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
|
`created_at` datetime DEFAULT NULL,
|
|
|
`updated_at` datetime DEFAULT NULL,
|
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
|
`user_id` int NOT NULL COMMENT '证书拥有者ID',
|
|
|
`state` int unsigned NOT NULL COMMENT '状态（1 代表在使用中，2代表已撤销或过期）',
|
|
|
`request_id` int NOT NULL COMMENT '证书请求ID',
|
|
|
`expire_time` datetime NOT NULL COMMENT '过期时间戳',
|
|
|
`certificate_content` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '证书内容(PEM格式)',
|
|
|
`serial_number` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '证书序列号',
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `idx_certificates_deleted_at` (`deleted_at`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='证书列表';
|
|
|
|
|
|
-- 创建证书吊销列表表
|
|
|
CREATE TABLE IF NOT EXISTS `crls` (
|
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
|
`created_at` datetime DEFAULT NULL,
|
|
|
`updated_at` datetime DEFAULT NULL,
|
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
|
`certificate_id` int NOT NULL COMMENT '证书ID',
|
|
|
`input_time` bigint NOT NULL COMMENT '加入时间戳',
|
|
|
PRIMARY KEY (`id`),
|
|
|
KEY `idx_crls_deleted_at` (`deleted_at`)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='证书吊销列表';
|
|
|
|
