njzscloud/z-doc/数据库初始化脚本/初始建表.sql

381 lines
18 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user
(
id BIGINT NOT NULL COMMENT 'Id',
nickname VARCHAR(255) NOT NULL COMMENT '昵称',
avatar VARCHAR(255) NOT NULL COMMENT '头像',
gender VARCHAR(10) DEFAULT 'Unknown' NOT NULL COMMENT '性别; 字典代码gender',
email VARCHAR(255) NOT NULL COMMENT '邮箱',
phone VARCHAR(20) NOT NULL COMMENT '手机号',
tenant_id BIGINT DEFAULT 1 NOT NULL COMMENT '租户 Id; sys_tenant.id',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '用户信息表';
DROP TABLE IF EXISTS sys_role_resource;
CREATE TABLE sys_role_resource
(
id BIGINT NOT NULL COMMENT 'Id',
role_id BIGINT NOT NULL COMMENT '角色 Id; sys_role.id',
res_id BIGINT NOT NULL COMMENT '资源 Id; sys_resource.id',
res_sn VARCHAR(255) NOT NULL COMMENT '资源编码; sys_resource.sn',
table_name VARCHAR(255) NOT NULL COMMENT '表名称',
data_id BIGINT NOT NULL COMMENT '数据行 Id',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '角色-资源关系表';
CREATE UNIQUE INDEX uk__role_id__menu_id ON sys_role_resource (role_id, res_id);
DROP TABLE IF EXISTS sys_tenant;
CREATE TABLE sys_tenant
(
id BIGINT NOT NULL COMMENT 'Id',
sn VARCHAR(255) NOT NULL COMMENT '编号',
tenant_name VARCHAR(255) NOT NULL COMMENT '租户名称',
tenant_status VARCHAR(255) NOT NULL COMMENT '状态; 字典编码tenant_ststus',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '租户表';
DROP TABLE IF EXISTS sys_user_account;
CREATE TABLE sys_user_account
(
id BIGINT NOT NULL COMMENT 'Id',
user_id BIGINT NOT NULL COMMENT '用户 Id; sys_user.id',
username VARCHAR(255) NOT NULL COMMENT '用户名',
email VARCHAR(255) NOT NULL COMMENT '邮箱',
phone VARCHAR(20) NOT NULL COMMENT '手机号',
secret VARCHAR(255) NOT NULL COMMENT '密码',
wechat_openid VARCHAR(255) NOT NULL COMMENT '微信 openid',
wechat_unionid VARCHAR(255) NOT NULL COMMENT '微信 unionid',
regdate DATETIME NOT NULL COMMENT '注册时间',
client_code INT DEFAULT 0 NOT NULL COMMENT '允许登录的客户端; 字典代码client_code',
disabled TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否禁用; 0-->启用、1-->禁用',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '用户账号信息表';
CREATE UNIQUE INDEX uk__user_id ON sys_user_account (user_id);
CREATE UNIQUE INDEX uk__username ON sys_user_account (username);
CREATE INDEX ix__phone ON sys_user_account (phone);
CREATE INDEX ix__wechat_openid__wechat_unionid ON sys_user_account (wechat_openid, wechat_unionid);
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role
(
id BIGINT NOT NULL COMMENT 'Id',
user_id BIGINT NOT NULL COMMENT '用户 Id; sys_user.id',
role_id BIGINT NOT NULL COMMENT '角色 Id; sys_role.id',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '用户-角色关系表';
CREATE UNIQUE INDEX uk__user_id__role_id ON sys_user_role (user_id, role_id);
DROP TABLE IF EXISTS sys_role;
CREATE TABLE sys_role
(
id BIGINT NOT NULL COMMENT 'Id',
role_code VARCHAR(64) NOT NULL COMMENT '角色代码; 以 ROLE_ 开头',
role_name VARCHAR(64) NOT NULL COMMENT '角色名称',
memo VARCHAR(512) NOT NULL COMMENT '备注',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '角色表';
CREATE UNIQUE INDEX uk__role_code ON sys_role (role_code);
DROP TABLE IF EXISTS sys_endpoint;
CREATE TABLE sys_endpoint
(
id BIGINT NOT NULL COMMENT 'Id',
request_method VARCHAR(10) NOT NULL COMMENT '请求方式; 字典代码request_method',
routing_path VARCHAR(255) NOT NULL COMMENT '路由前缀; 以 / 开头 或 为空',
endpoint_path VARCHAR(255) NOT NULL COMMENT '端点地址; 以 / 开头, Ant 匹配模式',
access_model VARCHAR(64) DEFAULT 'Logined' NOT NULL COMMENT '接口访问模式; 字典代码endpoint_access_model',
memo VARCHAR(512) NOT NULL COMMENT '备注',
creator_id BIGINT NOT NULL COMMENT '创建人 Id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '端点信息表';
CREATE UNIQUE INDEX uk__request_method__routing_path__endpoint_path ON sys_endpoint (request_method, routing_path, endpoint_path);
DROP TABLE IF EXISTS sys_menu;
CREATE TABLE sys_menu
(
id BIGINT NOT NULL COMMENT 'Id',
sn VARCHAR(255) NOT NULL COMMENT '编号',
pid BIGINT NOT NULL COMMENT '上级 Id; 层级为 1 的节点值为 0',
title VARCHAR(255) NOT NULL COMMENT '菜单名称',
icon VARCHAR(255) NOT NULL COMMENT '图标',
tier INT NOT NULL COMMENT '层级; >= 1',
breadcrumb VARCHAR(512) DEFAULT '[]' NOT NULL COMMENT '面包路径; 逗号分隔',
menu_category VARCHAR(32) NOT NULL COMMENT '类型; 字典代码menu_category',
freeze TINYINT(1) DEFAULT 0 NOT NULL COMMENT '标签是否冻结; 0-->否、1-->是',
sort INT NOT NULL COMMENT '排序',
route_name VARCHAR(128) NOT NULL COMMENT '路由名称',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '菜单信息表';
DROP TABLE IF EXISTS sys_file;
CREATE TABLE sys_file
(
id BIGINT NOT NULL COMMENT 'Id',
bucket_name VARCHAR(255) NOT NULL COMMENT '桶名称',
object_name VARCHAR(512) NOT NULL COMMENT '对象名称',
original_name VARCHAR(255) NOT NULL COMMENT '原文件名称',
size BIGINT NOT NULL COMMENT '文件大小; 单位: 字节',
mime VARCHAR(255) NOT NULL COMMENT 'MIME 类型',
link VARCHAR(512) NOT NULL COMMENT '访问链接',
file_service VARCHAR(10) NOT NULL COMMENT '存储服务; 字典代码file_service',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '文件记录表';
DROP TABLE IF EXISTS sys_file_ref;
CREATE TABLE sys_file_ref
(
id BIGINT NOT NULL COMMENT 'Id',
coord_table VARCHAR(255) NOT NULL COMMENT '表名称',
coord_field VARCHAR(255) NOT NULL COMMENT '表字段',
coord_id BIGINT NOT NULL COMMENT '表记录 Id',
file_id BIGINT NOT NULL COMMENT '文件 Id; sys_file.id',
link VARCHAR(512) NOT NULL COMMENT '访问链接; sys_file.link',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '文件引用表';
CREATE UNIQUE INDEX uk__coord_id__coord_field__coord_table__file_id ON sys_file_ref (coord_id, coord_field, coord_table, file_id);
DROP TABLE IF EXISTS sys_login_record;
CREATE TABLE sys_login_record
(
id BIGINT NOT NULL COMMENT 'Id',
user_id BIGINT NOT NULL COMMENT '用户 Id; sys_user.id',
user_account_id BIGINT NOT NULL COMMENT '用户账号 Id; sys_user_account.id',
login_time DATETIME NOT NULL COMMENT '登录时间',
client_code INT NOT NULL COMMENT '本次登录使用的客户端; 字典代码client_code',
auth_way VARCHAR(50) NOT NULL COMMENT '登录方式; 字典代码auth_way',
user_agent VARCHAR(512) NOT NULL COMMENT '用户代理',
ip VARCHAR(128) NOT NULL COMMENT 'IP 地址',
province_code BIGINT NULL COMMENT 'IP 归属地, 省 (编码); sys_district.id',
province_name VARCHAR(255) NOT NULL COMMENT 'IP 归属地, 省 (名称); sys_district.name',
city_code BIGINT NULL COMMENT 'IP 归属地, 市 (编码); sys_district.id',
city_name VARCHAR(255) NOT NULL COMMENT 'IP 归属地, 市 (名称); sys_district.name',
area_code BIGINT NULL COMMENT 'IP 归属地, 区 (编码); sys_district.id',
area_name VARCHAR(255) NOT NULL COMMENT 'IP 归属地, 区 (名称); sys_district.name',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '用户登录记录表';
CREATE INDEX ix__user_id ON sys_login_record (user_id);
CREATE INDEX ix__user_account_id ON sys_login_record (user_account_id);
DROP TABLE IF EXISTS sys_dict;
CREATE TABLE sys_dict
(
id BIGINT NOT NULL COMMENT 'Id',
dict_key VARCHAR(255) NOT NULL COMMENT '字典标识',
dict_name VARCHAR(255) NOT NULL COMMENT '字典名称',
memo VARCHAR(512) NOT NULL COMMENT '备注',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '字典表';
CREATE UNIQUE INDEX uk__dict_key ON sys_dict (dict_key);
DROP TABLE IF EXISTS sys_district;
CREATE TABLE sys_district
(
id VARCHAR(16) NOT NULL COMMENT 'Id; 地区代码',
pid VARCHAR(16) NOT NULL COMMENT '上级地区代码',
province VARCHAR(16) DEFAULT '' NOT NULL COMMENT '',
city VARCHAR(16) DEFAULT '' NOT NULL COMMENT '',
area VARCHAR(16) DEFAULT '' NOT NULL COMMENT '区县',
town VARCHAR(16) DEFAULT '' NOT NULL COMMENT '乡镇街道',
district_name VARCHAR(255) NOT NULL COMMENT '地区名称',
tier INT NOT NULL COMMENT '层级; >= 1',
sort INT DEFAULT 0 NOT NULL COMMENT '排序',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '省市区表';
DROP TABLE IF EXISTS sys_dict_item;
CREATE TABLE sys_dict_item
(
id BIGINT NOT NULL COMMENT 'Id',
dict_id BIGINT NOT NULL COMMENT '字典 Id; sys_dict.id',
dict_key VARCHAR(255) NOT NULL COMMENT '字典标识; sys_dict.dict_key',
val VARCHAR(100) NOT NULL COMMENT '值; 分类值/字典项值',
txt VARCHAR(100) NOT NULL COMMENT '显示文本; 分类显示文本/字典项显示文本',
sort INT NOT NULL COMMENT '排序',
memo VARCHAR(512) NOT NULL COMMENT '备注',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '字典条目表';
CREATE INDEX ix__dict_id ON sys_dict_item (dict_id);
CREATE INDEX ix__dict_key ON sys_dict_item (dict_key);
DROP TABLE IF EXISTS sys_resource;
CREATE TABLE sys_resource
(
id BIGINT NOT NULL COMMENT 'Id',
sn VARCHAR(255) NOT NULL COMMENT '编号',
table_name VARCHAR(255) NOT NULL COMMENT '表名称',
data_id BIGINT NOT NULL COMMENT '数据行 Id',
memo VARCHAR(512) NOT NULL COMMENT '备注',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '系统资源表';
DROP TABLE IF EXISTS sys_token;
CREATE TABLE sys_token
(
id BIGINT NOT NULL COMMENT 'Id',
user_id BIGINT NOT NULL COMMENT '用户 Id',
tid VARCHAR(128) NOT NULL COMMENT 'Token Id',
tkey VARCHAR(512) NOT NULL COMMENT 'Token 键',
tval TEXT NOT NULL COMMENT 'Token 值',
user_detail TEXT NOT NULL COMMENT '用户信息',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '登录令牌表';
DROP TABLE IF EXISTS sys_task;
CREATE TABLE sys_task
(
id BIGINT NOT NULL COMMENT 'Id',
task_name VARCHAR(128) NOT NULL COMMENT '任务名称',
fn VARCHAR(128) NOT NULL COMMENT '任务执行函数',
schedule_type VARCHAR(32) NOT NULL COMMENT '调度方式; 字典代码schedule_type',
schedule_conf VARCHAR(24) NOT NULL COMMENT '调度配置; 手动时为空,固定周期时单位为秒',
critical_timing BIGINT NOT NULL COMMENT '临界时间',
disabled TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否禁用; 0-->否、1-->是',
creator_id BIGINT NOT NULL COMMENT '创建人 Id; sys_user.id',
modifier_id BIGINT NOT NULL COMMENT '修改人 Id; sys_user.id',
create_time DATETIME NOT NULL COMMENT '创建时间',
modify_time DATETIME NOT NULL COMMENT '修改时间',
deleted TINYINT(1) DEFAULT 0 NOT NULL COMMENT '是否删除; 0-->未删除、1-->已删除',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '定时任务表';
DROP TABLE IF EXISTS sys_inc_sn;
CREATE TABLE sys_inc_sn
(
id BIGINT NOT NULL COMMENT 'Id',
code VARCHAR(128) NOT NULL COMMENT '标识',
val BIGINT NOT NULL COMMENT '当前值',
step INT NOT NULL COMMENT '步长',
initial_val BIGINT NOT NULL COMMENT '初始值',
pad_mode VARCHAR(32) NOT NULL COMMENT '填充模式; 字典编码pad_mode',
pad_val VARCHAR(64) NOT NULL COMMENT '填充值',
pad_len INT NOT NULL COMMENT '填充长度',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '递增编码表';
DROP TABLE IF EXISTS sys_sn_config;
CREATE TABLE sys_sn_config
(
id BIGINT NOT NULL COMMENT 'Id',
sncode VARCHAR(128) NOT NULL COMMENT '编码名称; 字典编码sncode',
config TEXT NOT NULL COMMENT '配置',
memo VARCHAR(128) NOT NULL COMMENT '备注',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '编码配置表';
DROP TABLE IF EXISTS sys_tpl;
CREATE TABLE sys_tpl
(
id BIGINT NOT NULL COMMENT 'Id',
tpl_name VARCHAR(255) NOT NULL COMMENT '模板名称',
tpl_category VARCHAR(64) NOT NULL COMMENT '模板类型; 字典编码tpl_category',
tpl LONGTEXT NOT NULL COMMENT '模板内容',
model_data LONGTEXT NOT NULL COMMENT '模型数据',
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '代码模板';