You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
iot/projects/UserCenter/db.sql

191 lines
8.0 KiB

CREATE TABLE `uc_Department` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`Number` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`Path` longtext CHARACTER SET utf8mb4 NULL,
`ParentId` char(36) NULL,
CONSTRAINT `PK_uc_Department` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_Department_uc_Department_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `uc_Department` (`Id`) ON DELETE RESTRICT
);
CREATE TABLE `uc_Organ` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`Number` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`Path` longtext CHARACTER SET utf8mb4 NULL,
`ParentId` char(36) NULL,
`Contact` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_uc_Organ` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_Organ_uc_Organ_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `uc_Organ` (`Id`) ON DELETE RESTRICT
);
CREATE TABLE `uc_PermissionCategory` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`Number` varchar(255) CHARACTER SET utf8mb4 NULL,
`Path` longtext CHARACTER SET utf8mb4 NULL,
`ParentId` char(36) NULL,
CONSTRAINT `PK_uc_PermissionCategory` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_PermissionCategory_uc_PermissionCategory_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `uc_PermissionCategory` (`Id`) ON DELETE SET NULL
);
CREATE TABLE `uc_Role` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` varchar(255) CHARACTER SET utf8mb4 NULL,
`IsReadOnly` tinyint(1) NOT NULL,
CONSTRAINT `PK_uc_Role` PRIMARY KEY (`Id`)
);
CREATE TABLE `uc_Setting` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` longtext CHARACTER SET utf8mb4 NOT NULL,
`Value` longtext CHARACTER SET utf8mb4 NULL,
`Type` int NOT NULL,
CONSTRAINT `PK_uc_Setting` PRIMARY KEY (`Id`)
);
CREATE TABLE `uc_Site` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` varchar(255) CHARACTER SET utf8mb4 NULL,
`Icon` longtext CHARACTER SET utf8mb4 NULL,
`Description` longtext CHARACTER SET utf8mb4 NULL,
`Key` longtext CHARACTER SET utf8mb4 NULL,
`Home` longtext CHARACTER SET utf8mb4 NULL,
`Login` longtext CHARACTER SET utf8mb4 NULL,
`Logout` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_uc_Site` PRIMARY KEY (`Id`)
);
CREATE TABLE `uc_User` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`UserName` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`SecurityStamp` longtext CHARACTER SET utf8mb4 NULL,
`PasswordHash` longtext CHARACTER SET utf8mb4 NULL,
`PasswordConfirmed` tinyint(1) NOT NULL,
`Email` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`EmailConfirmed` tinyint(1) NOT NULL,
`PhoneNumber` varchar(255) CHARACTER SET utf8mb4 NULL,
`PhoneNumberConfirmed` tinyint(1) NOT NULL,
`PayPassword` longtext CHARACTER SET utf8mb4 NULL,
`PayPasswordConfirmed` tinyint(1) NOT NULL,
`RealName` longtext CHARACTER SET utf8mb4 NULL,
`IdCardNumber` longtext CHARACTER SET utf8mb4 NULL,
`IdentityConfirmed` tinyint(1) NOT NULL,
`NickName` varchar(255) CHARACTER SET utf8mb4 NULL,
`Avatar` longtext CHARACTER SET utf8mb4 NULL,
`FaceImage` longtext CHARACTER SET utf8mb4 NULL,
`Sex` int NOT NULL,
`Birthday` datetime(6) NULL,
`AvailBalance` decimal(65,30) NOT NULL,
`FrozenBlance` decimal(65,30) NOT NULL,
`Point` bigint NOT NULL,
`LockoutEnabled` tinyint(1) NOT NULL,
`AccessFailedCount` int NOT NULL,
`LockoutEnd` datetime(6) NULL,
`RowVersion` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_uc_User` PRIMARY KEY (`Id`)
);
CREATE TABLE `uc_Permission` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`Number` varchar(255) CHARACTER SET utf8mb4 NULL,
`CategoryId` char(36) NULL,
CONSTRAINT `PK_uc_Permission` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_Permission_uc_PermissionCategory_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `uc_PermissionCategory` (`Id`) ON DELETE SET NULL
);
CREATE TABLE `uc_OrganUser` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`UserId` char(36) NOT NULL,
`OrganId` char(36) NOT NULL,
CONSTRAINT `PK_uc_OrganUser` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_OrganUser_uc_Organ_OrganId` FOREIGN KEY (`OrganId`) REFERENCES `uc_Organ` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_uc_OrganUser_uc_User_UserId` FOREIGN KEY (`UserId`) REFERENCES `uc_User` (`Id`) ON DELETE CASCADE
);
CREATE TABLE `uc_UserDepartment` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`UserId` char(36) NOT NULL,
`DepartmentId` char(36) NOT NULL,
CONSTRAINT `PK_uc_UserDepartment` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_UserDepartment_uc_Department_DepartmentId` FOREIGN KEY (`DepartmentId`) REFERENCES `uc_Department` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_uc_UserDepartment_uc_User_UserId` FOREIGN KEY (`UserId`) REFERENCES `uc_User` (`Id`) ON DELETE CASCADE
);
CREATE TABLE `uc_UserRole` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`UserId` char(36) NOT NULL,
`RoleId` char(36) NOT NULL,
CONSTRAINT `PK_uc_UserRole` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_UserRole_uc_Role_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `uc_Role` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_uc_UserRole_uc_User_UserId` FOREIGN KEY (`UserId`) REFERENCES `uc_User` (`Id`) ON DELETE CASCADE
);
CREATE TABLE `uc_RolePermission` (
`Id` char(36) NOT NULL,
`IsDeleted` longtext CHARACTER SET utf8mb4 NULL,
`RoleId` char(36) NOT NULL,
`PermissionId` char(36) NOT NULL,
`IsReadOnly` tinyint(1) NOT NULL,
CONSTRAINT `PK_uc_RolePermission` PRIMARY KEY (`Id`),
CONSTRAINT `FK_uc_RolePermission_uc_Permission_PermissionId` FOREIGN KEY (`PermissionId`) REFERENCES `uc_Permission` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_uc_RolePermission_uc_Role_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `uc_Role` (`Id`) ON DELETE CASCADE
);
CREATE UNIQUE INDEX `IX_uc_Department_Number` ON `uc_Department` (`Number`);
CREATE INDEX `IX_uc_Department_ParentId` ON `uc_Department` (`ParentId`);
CREATE UNIQUE INDEX `IX_uc_Organ_Number` ON `uc_Organ` (`Number`);
CREATE INDEX `IX_uc_Organ_ParentId` ON `uc_Organ` (`ParentId`);
CREATE INDEX `IX_uc_OrganUser_OrganId` ON `uc_OrganUser` (`OrganId`);
CREATE UNIQUE INDEX `IX_uc_OrganUser_UserId_OrganId` ON `uc_OrganUser` (`UserId`, `OrganId`);
CREATE INDEX `IX_uc_Permission_CategoryId` ON `uc_Permission` (`CategoryId`);
CREATE UNIQUE INDEX `IX_uc_Permission_Number` ON `uc_Permission` (`Number`);
CREATE UNIQUE INDEX `IX_uc_PermissionCategory_Number` ON `uc_PermissionCategory` (`Number`);
CREATE INDEX `IX_uc_PermissionCategory_ParentId` ON `uc_PermissionCategory` (`ParentId`);
CREATE UNIQUE INDEX `IX_uc_Role_Name` ON `uc_Role` (`Name`);
CREATE INDEX `IX_uc_RolePermission_PermissionId` ON `uc_RolePermission` (`PermissionId`);
CREATE UNIQUE INDEX `IX_uc_RolePermission_RoleId_PermissionId` ON `uc_RolePermission` (`RoleId`, `PermissionId`);
CREATE UNIQUE INDEX `IX_uc_Site_Name` ON `uc_Site` (`Name`);
CREATE UNIQUE INDEX `IX_uc_User_Email` ON `uc_User` (`Email`);
CREATE UNIQUE INDEX `IX_uc_User_NickName` ON `uc_User` (`NickName`);
CREATE UNIQUE INDEX `IX_uc_User_PhoneNumber` ON `uc_User` (`PhoneNumber`);
CREATE UNIQUE INDEX `IX_uc_User_UserName` ON `uc_User` (`UserName`);
CREATE INDEX `IX_uc_UserDepartment_DepartmentId` ON `uc_UserDepartment` (`DepartmentId`);
CREATE UNIQUE INDEX `IX_uc_UserDepartment_UserId_DepartmentId` ON `uc_UserDepartment` (`UserId`, `DepartmentId`);
CREATE INDEX `IX_uc_UserRole_RoleId` ON `uc_UserRole` (`RoleId`);
CREATE UNIQUE INDEX `IX_uc_UserRole_UserId_RoleId` ON `uc_UserRole` (`UserId`, `RoleId`);