ABP-VNext 用户权限管理系统实战02---用户权限表的创建与迁移
一、表实体建立
1、菜单表
[Comment("菜单表")] [Table("t_identity_menu")] public class Menu : AuditedAggregateRoot<Guid>, ISoftDelete, IMultiTenant { [MaxLength(200)] [Comment("菜单名")] public string Name { get; set; } [MaxLength(50)] [Comment("菜单编码")] public int Code { get; set; } [MaxLength(36)] [Comment("父级菜单标识")] public Guid ParentId { get; set; } [Comment("层级")] public int Level { get; set; } [MaxLength(200)] [Comment("图标路径")] public string IconUrl { get; set; } [MaxLength(200)] [Comment("跳转路径")] public string RedirectUrl { get; set; } [Comment("菜单类型 0 APP 1 web")] public int MenuType { get; set; } [Comment("地址类型")] public int MenuInOut { get; set; } [Comment("排序")] public int Sort { get; set; } [Comment("是否启用")] public bool IsActive { get; set; } [Comment("是否删除")] public bool IsDeleted { get; set; } [Comment("租户Id")] public Guid? TenantId { set; get; } [Comment("用户归属系统Id")] [MaxLength(20)] public string SystemId { get; set; } [Comment("备注")] [MaxLength(500)] public string Remark { get; set; } }
2、角色权限中间表
[Comment("角色权限中间表")] [Table("t_identity_role_authority")] public class RoleAuthority : AuditedAggregateRoot<Guid>, ISoftDelete, IMultiTenant {/// <summary> /// 角色id /// </summary> [MaxLength(36)] [Comment("角色标识")] public Guid RoleId { get; set; } /// <summary> /// 权限id /// </summary> [MaxLength(36)] [Comment("权限标识")] public Guid AuthId { get; set; } [Comment("是否删除")] public bool IsDeleted { get; set; } /// <summary> /// 租户Id /// </summary> public Guid? TenantId { get; set; } /// <summary> /// 构造函数 /// </summary> /// <param name="id"></param> /// <param name="tenantId"></param> /// <param name="roleId"></param> /// <param name="authId"></param> public RoleAuthority(Guid id, Guid? tenantId, Guid roleId, Guid authId) { TenantId = tenantId; Id = id; RoleId = roleId; AuthId = authId; } }
3、角色扩展表
[Comment("角色扩展表")] [Table("t_identity_role_expand")] public class RoleExpand : AuditedAggregateRoot<Guid> { public RoleExpand(Guid Id, int RoleType, Guid RoleId, int RoleStatus) { this.Id = Id; this.RoleId = RoleId; this.RoleType = RoleType; this.RoleStatus = RoleStatus; } [MaxLength(36)] [Comment("角色标识")] public Guid RoleId { get; set; } [Comment("角色类型")] public int RoleType { get; set; } [MaxLength(36)] [Comment("用户状态：0启用、1禁用")] public int RoleStatus { get; set; } [MaxLength(20)] [Comment("归属系统Id")] public string SystemId { get; set; } }
4、角色菜单中间表
[Comment("角色菜单中间表")] [Table("t_identity_role_menu")] public class RoleMenu : AuditedAggregateRoot<Guid>, ISoftDelete { [MaxLength(36)] [Comment("角色标识")] public Guid RoleId { get; set; } [MaxLength(36)] [Comment("菜单标识")] public Guid MenuId { get; set; } [Comment("是否删除")] public bool IsDeleted { get; set; } }
5、用户扩展表
[Comment("用户扩展表")] [Table("t_identity_user_expand")] public class UserExpand : AuditedAggregateRoot<Guid> { public UserExpand(Guid Id, int UserType, Guid UserId, UserStatusEnum userStatus = UserStatusEnum.Enable, int isConfirm = 0) { this.Id = Id; this.UserId = UserId; this.UserType = UserType; this.UserStatus = userStatus; this.IsConfirm = isConfirm; } [MaxLength(36)] [Comment("用户标识")] public Guid UserId { get; set; } [Comment("用户类型")] public int UserType { get; set; } [Comment("用户状态")] public UserStatusEnum UserStatus { get; set; } [Comment("是否已确认结果，0未确认，1已确认")] public int IsConfirm { get; set; } [MaxLength(200)] [Comment("用户图像")] public string HeadFileUrl { get; set; } [MaxLength(20)] [Comment("归属系统Id")] public string SystemId { get; set; } [Comment("锁定状态")] public bool? IsLock { get; set; } [Comment("登录失败次数")] public int? LoginFailedCount { get; set; } [Comment("锁定时间")] public DateTime? LockTime { get; set; } [Comment("锁定人")] public Guid? LockUserId { get; set; } [Comment("解锁时间")] public DateTime? UnLockTime { get; set; } [Comment("解锁人")] public Guid? UnLockUserId { get; set; } }
二、配置实体到EFCORE
1、IdentityManagementDbContext中增加实体集
2、IdentityManagementDbContextModelCreatingExtensions中增加实体和表的映射
三、建立公共的数据迁移项目
1、新建一个web项目
项目中引用对应的程序集：Volo.Abp.EntityFrameworkCore.MySQL、Microsoft.EntityFrameworkCore，引入对应要迁移的项目：Bridge.IdentityManagement.EntityFrameworkCore
注意版本号要统一。
2、新增EFCoreDbContextFactory类和EFCoreMigrationDbContext类
EFCoreDbContextFactory类，在类中配置了数据库类型，要用的配置文件
public class EFCoreDbContextFactory : IDesignTimeDbContextFactory<EFCoreMigrationDbContext> { public EFCoreMigrationDbContext CreateDbContext(string[] args) { var configuration = BuildConfiguration(); var builder = new DbContextOptionsBuilder<EFCoreMigrationDbContext>() .UseMySql(configuration.GetConnectionString("Default"), ServerVersion.AutoDetect(configuration.GetConnectionString("Default")), o => o.SchemaBehavior(MySqlSchemaBehavior.Ignore)); return new EFCoreMigrationDbContext(builder.Options); } private static IConfigurationRoot BuildConfiguration() { var builder = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json", optional: false); return builder.Build(); } }
EFCoreMigrationDbContext类中加和了要迁移的实体，要实体和表的映射扩展方法
public class EFCoreMigrationDbContext : AbpDbContext<EFCoreMigrationDbContext> { public EFCoreMigrationDbContext( DbContextOptions<EFCoreMigrationDbContext> options ) : base(options) { } #region 用户权限管理模块 public DbSet<Menu> Menus { get; set; } public DbSet<RoleAuthority> RoleAuthoritys { get; set; } public DbSet<Authority> Authoritys { get; set; } public DbSet<RoleMenu> RoleMenus { get; set; } public DbSet<UserExpand> UserExpands { get; set; } #endregion protected override void OnModelCreating(ModelBuilder builder) { builder.ConfigureIdentityManagement(); base.OnModelCreating(builder); } }
3、修改配置文件，增加数据库连接
"ConnectionStrings": { "Default": "Server=123.249.14.34;Port=3306; Database=bridge; User=root; Password=xxxx;" },
四、迁移数据表
1、生成迁移文件
Add-Migration -Context EFCoreMigrationDbContext -Name bridge_identity_20231022 -OutputDir Migrations/EFCoreMigrationDb
2、迁移数据库
方法一：生成sql脚本
如果你不想直接迁移到数据库，只生成sql脚本请用如下的命令
Script-Migration -Context EFCoreMigrationDbContext
如果想迁移指定文件的内容请用如下的命令，增加了from 和 to参数
Script-Migration -From 20231023025116_bridge_identity_20231022 -To 20231023025116_bridge_identity_20231022 -Context EFCoreMigrationDbContext
生成的sql脚本如下，可直接在数据库中执行：
CREATE TABLE IF NOT EXISTS `__EFMigrationsHistory` ( `MigrationId` varchar(150) CHARACTER SET utf8mb4 NOT NULL, `ProductVersion` varchar(32) CHARACTER SET utf8mb4 NOT NULL, CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`) ) CHARACTER SET=utf8mb4; START TRANSACTION; ALTER DATABASE CHARACTER SET utf8mb4; CREATE TABLE `Authoritys` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `PageCode` varchar(64) CHARACTER SET utf8mb4 NULL, `PageName` varchar(64) CHARACTER SET utf8mb4 NULL, `MenuId` char(36) COLLATE ascii_general_ci NULL, `Operate` varchar(64) CHARACTER SET utf8mb4 NULL, `OperateName` varchar(64) CHARACTER SET utf8mb4 NULL, `IsDeleted` tinyint(1) NOT NULL DEFAULT FALSE, `TenantId` char(36) COLLATE ascii_general_ci NULL, `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_Authoritys` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4; CREATE TABLE `t_identity_menu` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `Name` varchar(200) CHARACTER SET utf8mb4 NULL COMMENT '菜单名', `Code` int NOT NULL COMMENT '菜单编码', `ParentId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '父级菜单标识', `Level` int NOT NULL COMMENT '层级', `IconUrl` varchar(200) CHARACTER SET utf8mb4 NULL COMMENT '图标路径', `RedirectUrl` varchar(200) CHARACTER SET utf8mb4 NULL COMMENT '跳转路径', `MenuType` int NOT NULL COMMENT '菜单类型 0 APP 1 web', `MenuInOut` int NOT NULL COMMENT '地址类型', `Sort` int NOT NULL COMMENT '排序', `IsActive` tinyint(1) NOT NULL COMMENT '是否启用', `IsDeleted` tinyint(1) NOT NULL DEFAULT FALSE COMMENT '是否删除', `TenantId` char(36) COLLATE ascii_general_ci NULL COMMENT '租户Id', `SystemId` varchar(20) CHARACTER SET utf8mb4 NULL COMMENT '用户归属系统Id', `Remark` varchar(500) CHARACTER SET utf8mb4 NULL COMMENT '备注', `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_t_identity_menu` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4 COMMENT='菜单表'; CREATE TABLE `t_identity_role_authority` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `RoleId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '角色标识', `AuthId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '权限标识', `IsDeleted` tinyint(1) NOT NULL DEFAULT FALSE COMMENT '是否删除', `TenantId` char(36) COLLATE ascii_general_ci NULL, `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_t_identity_role_authority` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4 COMMENT='角色权限中间表'; CREATE TABLE `t_identity_role_expand` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `RoleId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '角色标识', `RoleType` int NOT NULL COMMENT '角色类型', `RoleStatus` int NOT NULL COMMENT '用户状态：0启用、1禁用', `SystemId` varchar(20) CHARACTER SET utf8mb4 NULL COMMENT '归属系统Id', `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_t_identity_role_expand` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4 COMMENT='角色扩展表'; CREATE TABLE `t_identity_role_menu` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `RoleId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '角色标识', `MenuId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '菜单标识', `IsDeleted` tinyint(1) NOT NULL DEFAULT FALSE COMMENT '是否删除', `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_t_identity_role_menu` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4 COMMENT='角色菜单中间表'; CREATE TABLE `t_identity_user_expand` ( `Id` char(36) COLLATE ascii_general_ci NOT NULL, `UserId` char(36) COLLATE ascii_general_ci NOT NULL COMMENT '用户标识', `UserType` int NOT NULL COMMENT '用户类型', `UserStatus` int NOT NULL COMMENT '用户状态', `IsConfirm` int NOT NULL COMMENT '是否已确认结果，0未确认，1已确认', `HeadFileUrl` varchar(200) CHARACTER SET utf8mb4 NULL COMMENT '用户图像', `SystemId` varchar(20) CHARACTER SET utf8mb4 NULL COMMENT '归属系统Id', `IsLock` tinyint(1) NULL COMMENT '锁定状态', `LoginFailedCount` int NULL COMMENT '登录失败次数', `LockTime` datetime(6) NULL COMMENT '锁定时间', `LockUserId` char(36) COLLATE ascii_general_ci NULL COMMENT '锁定人', `UnLockTime` datetime(6) NULL COMMENT '解锁时间', `UnLockUserId` char(36) COLLATE ascii_general_ci NULL COMMENT '解锁人', `ExtraProperties` longtext CHARACTER SET utf8mb4 NULL, `ConcurrencyStamp` varchar(40) CHARACTER SET utf8mb4 NULL, `CreationTime` datetime(6) NOT NULL, `CreatorId` char(36) COLLATE ascii_general_ci NULL, `LastModificationTime` datetime(6) NULL, `LastModifierId` char(36) COLLATE ascii_general_ci NULL, CONSTRAINT `PK_t_identity_user_expand` PRIMARY KEY (`Id`) ) CHARACTER SET=utf8mb4 COMMENT='用户扩展表'; INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`) VALUES ('20231023025116_bridge_identity_20231022', '7.0.1'); COMMIT;
方法二：直接迁移
Update-DataBase 20231023025116_bridge_identity_20231022
完成后查看数据库表：
