iot/projects/UserCenter/db.sql

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`);