V1__initial_schema.sql
10 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
-- Flyway migration V1 — initial schema for 小羚羊
-- Generated: 2026-05-13T07:35:36Z
-- Source: 由 A4 db-init 从 docs/03-数据库设计文档.md 翻译生成（schema SSoT 是 docs/03）
-- This is the FIRST migration; subsequent schema changes must be written as new files sql/migrations/V2__<desc>.sql, V3__... etc.
-- Apply: Flyway runs this automatically at Spring Boot startup.
-- Do not hand-edit this file after it is committed; write a new migration instead.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ============================================================
-- tUser — 系统用户主表
-- ============================================================
CREATE TABLE `tUser` (
`iIncrement` INT NOT NULL AUTO_INCREMENT COMMENT '整数主键 ID（标准列）',
`sId` VARCHAR(100) NULL COMMENT '业务 ID（标准列）',
`sBrandsId` VARCHAR(100) NULL COMMENT '品牌 ID（多租户隔离，标准列）',
`sSubsidiaryId` VARCHAR(100) NULL COMMENT '子公司 ID（组织层级隔离，标准列）',
`tCreateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
`sUserCode` VARCHAR(32) NOT NULL COMMENT '用户号，业务唯一编码',
`sUserName` VARCHAR(50) NOT NULL COMMENT '用户名（登录标识），全局唯一',
`iEmployeeId` INT NULL DEFAULT NULL COMMENT '关联 tEmployee.iIncrement，可选；选填后页面回显员工名/部门',
`sUserType` VARCHAR(20) NOT NULL DEFAULT 'NORMAL' COMMENT '用户类型：NORMAL=普通用户，ADMIN=超级管理员',
`sLanguage` VARCHAR(16) NOT NULL DEFAULT 'zh-CN' COMMENT '界面语言：zh-CN 中文，en 英文，zh-TW 繁体',
`iCanEditDoc` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '单据修改权限：0=否，1=是',
`sPasswordHash` VARCHAR(100) NOT NULL COMMENT '密码哈希（BCrypt 或同强度算法），初始值由系统生成；以哈希形式存储',
`iIsDisabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '作废标志：0=有效，1=已作废（被作废后不可登录）',
`tLastLoginDate` DATETIME NULL DEFAULT NULL COMMENT '最近一次登录时间，登录成功时更新',
`iLoginFailCount` INT NOT NULL DEFAULT 0 COMMENT '连续登录失败计数，登录成功清零（用于阈值锁定）',
`tLockedUntil` DATETIME NULL DEFAULT NULL COMMENT '临时锁定截止时间；非空且大于当前时刻视为锁定',
`sCreatedBy` VARCHAR(50) NULL DEFAULT NULL COMMENT '制单人（创建该用户的操作员用户名）',
`tUpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`iIncrement`),
UNIQUE KEY `uk_tUser_sUserName` (`sUserName`),
UNIQUE KEY `uk_tUser_sUserCode` (`sUserCode`),
KEY `idx_tUser_iEmployeeId` (`iEmployeeId`),
KEY `idx_tUser_tenant_status` (`sBrandsId`, `sSubsidiaryId`, `iIsDisabled`),
KEY `idx_tUser_tLastLoginDate` (`tLastLoginDate`),
KEY `idx_tUser_sUserType` (`sUserType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户主表';
-- ============================================================
-- tEmployee — 职员字典
-- ============================================================
CREATE TABLE `tEmployee` (
`iIncrement` INT NOT NULL AUTO_INCREMENT COMMENT '整数主键 ID（标准列）',
`sId` VARCHAR(100) NULL COMMENT '业务 ID（标准列）',
`sBrandsId` VARCHAR(100) NULL COMMENT '品牌 ID（多租户隔离，标准列）',
`sSubsidiaryId` VARCHAR(100) NULL COMMENT '子公司 ID（组织层级隔离，标准列）',
`tCreateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
`sEmployeeCode` VARCHAR(32) NOT NULL COMMENT '员工号，业务唯一编码',
`sEmployeeName` VARCHAR(50) NOT NULL COMMENT '员工姓名',
`sDepartment` VARCHAR(100) NULL DEFAULT NULL COMMENT '所属部门名',
`iIsDisabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '作废标志：0=有效，1=已作废',
`tUpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`iIncrement`),
UNIQUE KEY `uk_tEmployee_sEmployeeCode` (`sEmployeeCode`),
KEY `idx_tEmployee_sEmployeeName` (`sEmployeeName`),
KEY `idx_tEmployee_sDepartment` (`sDepartment`),
KEY `idx_tEmployee_tenant` (`sBrandsId`, `sSubsidiaryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='职员字典';
-- ============================================================
-- tPermission — 权限分类字典
-- ============================================================
CREATE TABLE `tPermission` (
`iIncrement` INT NOT NULL AUTO_INCREMENT COMMENT '整数主键 ID（标准列）',
`sId` VARCHAR(100) NULL COMMENT '业务 ID（标准列）',
`sBrandsId` VARCHAR(100) NULL COMMENT '品牌 ID（多租户隔离，标准列）',
`sSubsidiaryId` VARCHAR(100) NULL COMMENT '子公司 ID（组织层级隔离，标准列）',
`tCreateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
`sCategory` VARCHAR(100) NOT NULL COMMENT '权限分类编码（如 usr:user:create）',
`sCategoryName` VARCHAR(100) NOT NULL COMMENT '权限分类中文名（用于权限组复选框展示）',
`sDescription` VARCHAR(255) NULL DEFAULT NULL COMMENT '权限说明',
`iIsDisabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '作废标志：0=有效，1=已作废',
`tUpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`iIncrement`),
UNIQUE KEY `uk_tPermission_sCategory` (`sCategory`),
KEY `idx_tPermission_sCategoryName` (`sCategoryName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='权限分类字典';
-- ============================================================
-- tUserPermission — 用户 ↔ 权限多对多关联表
-- ============================================================
CREATE TABLE `tUserPermission` (
`iIncrement` INT NOT NULL AUTO_INCREMENT COMMENT '整数主键 ID（标准列）',
`sId` VARCHAR(100) NULL COMMENT '业务 ID（标准列）',
`sBrandsId` VARCHAR(100) NULL COMMENT '品牌 ID（多租户隔离，标准列）',
`sSubsidiaryId` VARCHAR(100) NULL COMMENT '子公司 ID（组织层级隔离，标准列）',
`tCreateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
`iUserId` INT NOT NULL COMMENT '用户主键，关联 tUser.iIncrement',
`iPermissionId` INT NOT NULL COMMENT '权限主键，关联 tPermission.iIncrement',
`sGrantedBy` VARCHAR(50) NULL DEFAULT NULL COMMENT '授权操作员用户名（审计用）',
PRIMARY KEY (`iIncrement`),
UNIQUE KEY `uk_tUserPermission_user_perm` (`iUserId`, `iPermissionId`),
KEY `idx_tUserPermission_iPermissionId` (`iPermissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户 ↔ 权限多对多关联表';
-- ============================================================
-- tCompany — 公司及版本字典
-- ============================================================
CREATE TABLE `tCompany` (
`iIncrement` INT NOT NULL AUTO_INCREMENT COMMENT '整数主键 ID（标准列）',
`sId` VARCHAR(100) NULL COMMENT '业务 ID（标准列）',
`sBrandsId` VARCHAR(100) NULL COMMENT '品牌 ID（多租户隔离，标准列）',
`sSubsidiaryId` VARCHAR(100) NULL COMMENT '子公司 ID（组织层级隔离，标准列）',
`tCreateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
`sCompanyName` VARCHAR(100) NOT NULL COMMENT '公司名称',
`sEdition` VARCHAR(32) NOT NULL DEFAULT '标准版' COMMENT '版本：标准版 / 专业版 / 企业版 等',
`iIsDisabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '作废标志',
`tUpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`iIncrement`),
KEY `idx_tCompany_sCompanyName` (`sCompanyName`),
KEY `idx_tCompany_sEdition` (`sEdition`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司及版本字典';
-- ============================================================
-- 外键
-- ============================================================
ALTER TABLE `tUser`
ADD CONSTRAINT `fk_tUser_iEmployeeId`
FOREIGN KEY (`iEmployeeId`) REFERENCES `tEmployee` (`iIncrement`)
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `tUserPermission`
ADD CONSTRAINT `fk_tUserPermission_iUserId`
FOREIGN KEY (`iUserId`) REFERENCES `tUser` (`iIncrement`)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `tUserPermission`
ADD CONSTRAINT `fk_tUserPermission_iPermissionId`
FOREIGN KEY (`iPermissionId`) REFERENCES `tPermission` (`iIncrement`)
ON DELETE RESTRICT ON UPDATE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;