public class SysUser
{
public string Id { get; set; }
public string Name { get; set; }
public string Password { get; set; }
public DateTime CreationTime { get; set; }
public bool IsDeleted { get; set; }
}
public class SysUserMap:IEntityTypeConfiguration<sysuser>
{
public void Configure(EntityTypeBuilder<sysuser> builder)
{
builder.HasKey(o => o.Id);
builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
builder.Property(o => o.Name).IsRequired().HasMaxLength(50);
builder.Property(o => o.Password).IsRequired().IsUnicode(false).HasMaxLength(50);
builder.HasQueryFilter(o => o.IsDeleted == false);
builder.ToTable(nameof(SysUser));
}
}</sysuser></sysuser>创建这个数据库该有的配置信息表，便于后期启动后重建
public class SysUserTenantConfig
{
public string Id { get; set; }
public string UserId { get; set; }
/// <summary>
/// 添加ShardingCore配置的Json包
/// </summary>
public string ConfigJson { get; set; }
public DateTime CreationTime { get; set; }
public bool IsDeleted { get; set; }
}
public class SysUserTenantConfigMap:IEntityTypeConfiguration<sysusertenantconfig>
{
public void Configure(EntityTypeBuilder<sysusertenantconfig> builder)
{
builder.HasKey(o => o.Id);
builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
builder.Property(o => o.UserId).IsRequired().IsUnicode(false).HasMaxLength(50);
builder.Property(o => o.ConfigJson).IsRequired().HasMaxLength(2000);
builder.HasQueryFilter(o => o.IsDeleted == false);
builder.ToTable(nameof(SysUserTenantConfig));
}
}</sysusertenantconfig></sysusertenantconfig>创建对应的系统用户存储DbContext
public class IdentityDbContext:DbContext
{
public IdentityDbContext(DbContextOptions<identitydbcontext> options):base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfiguration(new SysUserMap());
modelBuilder.ApplyConfiguration(new SysUserTenantConfigMap());
}
}</identitydbcontext>创建一个租户的DbContext
public class TenantDbContext:AbstractShardingDbContext,IShardingTableDbContext
{
public TenantDbContext(DbContextOptions<tenantdbcontext> options) : base(options)
{
}
public IRouteTail RouteTail { get; set; }
}</tenantdbcontext>目前我们先定义好后续进行编写内部的租户代码
创建动态租户参数
动态租户分片配置信息在
public class ShardingTenantOptions
{
public string ConfigId { get; set;}
public int Priority { get; set;}
public string DefaultDataSourceName { get; set;}
public string DefaultConnectionString { get; set;}
public DbTypeEnum DbType { get; set; }
}参数里面配置了当前数据库,这边比较简单我们就暂时使用单表分库的模式来实现,目前暂时不对每个租户分库进行演示。之后并且编写
public class SqlShardingConfiguration : AbstractVirtualDataSourceConfigurationParams<tenantdbcontext>
{
private static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{
builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
public override string ConfigId { get; }
public override int Priority { get; }
public override string DefaultDataSourceName { get; }
public override string DefaultConnectionString { get; }
public override ITableEnsureManager TableEnsureManager { get; }
private readonly DbTypeEnum _dbType;
public SqlShardingConfiguration(ShardingTenantOptions options)
{
ConfigId = options.ConfigId;
Priority = options.Priority;
DefaultDataSourceName = options.DefaultDataSourceName;
DefaultConnectionString = options.DefaultConnectionString;
_dbType = options.DbType;
//用来快速判断是否存在数据库中的表
if (_dbType == DbTypeEnum.MSSQL)
{
TableEnsureManager = new SqlServerTableEnsureManager<tenantdbcontext>();
}
else if (_dbType == DbTypeEnum.MYSQL)
{
TableEnsureManager = new MySqlTableEnsureManager<tenantdbcontext>();
}
else
{
throw new NotImplementedException();
}
}
public override DbContextOptionsBuilder UseDbContextOptionsBuilder(string connectionString,
DbContextOptionsBuilder dbContextOptionsBuilder)
{
switch (_dbType)
{
case DbTypeEnum.MSSQL:
{
dbContextOptionsBuilder.UseSqlServer(connectionString).UseLoggerFactory(efLogger);
}
break;
case DbTypeEnum.MYSQL:
{
dbContextOptionsBuilder.UseMySql(connectionString, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
}
break;
default: throw new NotImplementedException();
}
return dbContextOptionsBuilder;
}
public override DbContextOptionsBuilder UseDbContextOptionsBuilder(DbConnection dbConnection,
DbContextOptionsBuilder dbContextOptionsBuilder)
{
switch (_dbType)
{
case DbTypeEnum.MSSQL:
{
dbContextOptionsBuilder.UseSqlServer(dbConnection).UseLoggerFactory(efLogger);
}
break;
case DbTypeEnum.MYSQL:
{
dbContextOptionsBuilder.UseMySql(dbConnection, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);
}
break;
default: throw new NotImplementedException();
}
return dbContextOptionsBuilder;
}
}</tenantdbcontext></tenantdbcontext></tenantdbcontext>编写用户注册接口
[Route("api/[controller]/[action]")]
[ApiController]
[AllowAnonymous]
public class PassportController:ControllerBase
{
private readonly IdentityDbContext _identityDbContext;
public PassportController(IdentityDbContext identityDbContext)
{
_identityDbContext = identityDbContext;
}
[HttpPost]
public async Task<iactionresult> Register(RegisterRequest request)
{
if (await _identityDbContext.Set<sysuser>().AnyAsync(o => o.Name == request.Name))
return BadRequest("user not exists");
var sysUser = new SysUser()
{
Id = Guid.NewGuid().ToString("n"),
Name = request.Name,
Password = request.Password,
CreationTime=DateTime.Now
};
var shardingTenantOptions = new ShardingTenantOptions()
{
ConfigId = sysUser.Id,
Priority = new Random().Next(1,10),
DbType = request.DbType,
DefaultDataSourceName = "ds0",
DefaultConnectionString = GetDefaultString(request.DbType,sysUser.Id)
};
var sysUserTenantConfig = new SysUserTenantConfig()
{
Id = Guid.NewGuid().ToString("n"),
UserId = sysUser.Id,
CreationTime = DateTime.Now,
ConfigJson = JsonConvert.SerializeObject(shardingTenantOptions)
};
await _identityDbContext.AddAsync(sysUser);
await _identityDbContext.AddAsync(sysUserTenantConfig);
await _identityDbContext.SaveChangesAsync();
//注册完成后进行配置生成
DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(new SqlShardingConfiguration(shardingTenantOptions));
return Ok();
}
[HttpPost]
public async Task<iactionresult> Login(LoginRequest request)
{
var sysUser = await _identityDbContext.Set<sysuser>().FirstOrDefaultAsync(o=>o.Name==request.Name&&o.Password==request.Password);
if (sysUser == null)
return BadRequest("name or password error");
//秘钥，就是标头，这里用Hmacsha256算法，需要256bit的密钥
var securityKey = new SigningCredentials(new SymmetricSecurityKey(Encoding.ASCII.GetBytes("123123!@#!@#123123")), SecurityAlgorithms.HmacSha256);
//Claim，JwtRegisteredClaimNames中预定义了好多种默认的参数名，也可以像下面的Guid一样自己定义键名.
//ClaimTypes也预定义了好多类型如role、email、name。Role用于赋予权限，不同的角色可以访问不同的接口
//相当于有效载荷
var claims = new Claim[] {
new Claim(JwtRegisteredClaimNames.Iss,"https://localhost:5000"),
new Claim(JwtRegisteredClaimNames.Aud,"api"),
new Claim("id",Guid.NewGuid().ToString("n")),
new Claim("uid",sysUser.Id),
};
SecurityToken securityToken = new JwtSecurityToken(
signingCredentials: securityKey,
expires: DateTime.Now.AddHours(2),//过期时间
claims: claims
);
var token = new JwtSecurityTokenHandler().WriteToken(securityToken);
return Ok(token);
}
private string GetDefaultString(DbTypeEnum dbType, string userId)
{
switch (dbType)
{
case DbTypeEnum.MSSQL: return $"Data Source=localhost;Initial Catalog=DB{userId};Integrated Security=True;";
case DbTypeEnum.MYSQL: return $"server=127.0.0.1;port=3306;database=DB{userId};userid=root;password=L6yBtV6qNENrwBy7;";
default: throw new NotImplementedException();
}
}
}
public class RegisterRequest
{
public string Name { get; set; }
public string Password { get; set; }
public DbTypeEnum DbType { get; set; }
}
public class LoginRequest
{
public string Name { get; set; }
public string Password { get; set; }
}</sysuser></iactionresult></sysuser></iactionresult>简单来说明一下,这边我们采用的是用户的id作为租户id,将租户id作为数据库配置,来支持多配置模式。到此为止我们的用户系统就已经完成了是不是十分的简单仅仅几段代码,用户这边注册完成后将会创建对应的数据库和对应的表,如果你是分表的那么将会自动创建对应的数据库表等信息。
租户系统
租户系统我们做一个订单的简单演示,使用订单id取模,取模取5来进行分表操作
新增租户系统的订单信息
public class Order
{
public string Id { get; set; }
public string Name { get; set; }
public DateTime CreationTime { get; set; }
public bool IsDeleted { get; set; }
}
public class OrderMap:IEntityTypeConfiguration<order>
{
public void Configure(EntityTypeBuilder<order> builder)
{
builder.HasKey(o => o.Id);
builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50);
builder.Property(o => o.Name).IsRequired().HasMaxLength(100);
builder.HasQueryFilter(o => o.IsDeleted == false);
builder.ToTable(nameof(Order));
}
}</order></order>新增订单路由
public class OrderVirtualTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<order>
{
public OrderVirtualTableRoute() : base(2, 5)
{
}
public override void Configure(EntityMetadataTableBuilder<order> builder)
{
builder.ShardingProperty(o => o.Id);
}
}</order></order>简单的字符串取模
添加租户中间件
添加租户中间件,在系统中如果使用多配置那么就必须要指定本次创建的dbcontext使用的是哪个配置
public class TenantSelectMiddleware
{
private readonly RequestDelegate _next;
private readonly IVirtualDataSourceManager<tenantdbcontext> _virtualDataSourceManager;
public TenantSelectMiddleware(RequestDelegate next, IVirtualDataSourceManager<tenantdbcontext> virtualDataSourceManager)
{
_next = next;
_virtualDataSourceManager = virtualDataSourceManager;
}
public async Task Invoke(HttpContext context)
{
if (context.Request.Path.ToString().StartsWith("/api/tenant", StringComparison.CurrentCultureIgnoreCase))
{
if (!context.User.Identity.IsAuthenticated)
{
await _next(context);
return;
}
var tenantId = context.User.Claims.FirstOrDefault((o) => o.Type == "uid")?.Value;
if (string.IsNullOrWhiteSpace(tenantId))
{
await DoUnAuthorized(context, "not found tenant id");
return;
}
using (_virtualDataSourceManager.CreateScope(tenantId))
{
await _next(context);
}
}
else
{
await _next(context);
}
}
private async Task DoUnAuthorized(HttpContext context, string msg)
{
context.Response.StatusCode = 403;
await context.Response.WriteAsync(msg);
}
}</tenantdbcontext></tenantdbcontext>该中间件拦截
public static class TenantExtension
{
public static void InitTenant(this IServiceProvider serviceProvider)
{
using (var scope = serviceProvider.CreateScope())
{
var identityDbContext = scope.ServiceProvider.GetRequiredService<identitydbcontext>();
identityDbContext.Database.EnsureCreated();
var sysUserTenantConfigs = identityDbContext.Set<sysusertenantconfig>().ToList();
if (sysUserTenantConfigs.Any())
{
foreach (var sysUserTenantConfig in sysUserTenantConfigs)
{
var shardingTenantOptions = JsonConvert.DeserializeObject<shardingtenantoptions>(sysUserTenantConfig.ConfigJson);
DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(
new SqlShardingConfiguration(shardingTenantOptions));
}
}
}
}
}</shardingtenantoptions></sysusertenantconfig></identitydbcontext>这边因为我们针对租户信息进行了初始化而不是硬编码,所以需要一个在启动的时候对租户信息进行动态添加
配置多租户
启动配置
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddAuthentication();
#region 用户系统配置
builder.Services.AddDbContext<identitydbcontext>(o =>
o.UseSqlServer("Data Source=localhost;Initial Catalog=IdDb;Integrated Security=True;"));
//生成密钥
var keyByteArray = Encoding.ASCII.GetBytes("123123!@#!@#123123");
var signingKey = new SymmetricSecurityKey(keyByteArray);
//认证参数
builder.Services.AddAuthentication("Bearer")
.AddJwtBearer(o =>
{
o.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuerSigningKey = true,
IssuerSigningKey = signingKey,
ValidateIssuer = true,
ValidIssuer = "https://localhost:5000",
ValidateAudience = true,
ValidAudience = "api",
ValidateLifetime = true,
ClockSkew = TimeSpan.Zero,
RequireExpirationTime = true,
};
});
#endregion
#region 配置ShardingCore
builder.Services.AddShardingDbContext<tenantdbcontext>()
.AddEntityConfig(op =>
{
op.CreateShardingTableOnStart = true;
op.EnsureCreatedWithOutShardingTable = true;
op.AddShardingTableRoute<ordervirtualtableroute>();
})
.AddConfig(op =>
{
//默认配置一个
op.ConfigId = $"test_{Guid.NewGuid():n}";
op.Priority = 99999;
op.AddDefaultDataSource("ds0", "Data Source=localhost;Initial Catalog=TestTenantDb;Integrated Security=True;");
op.UseShardingQuery((conStr, b) =>
{
b.UseSqlServer(conStr);
});
op.UseShardingTransaction((conn, b) =>
{
b.UseSqlServer(conn);
});
}).EnsureMultiConfig(ShardingConfigurationStrategyEnum.ThrowIfNull);
#endregion
var app = builder.Build();
// Configure the HTTP request pipeline.
app.Services.GetRequiredService<ishardingbootstrapper>().Start();
//初始化启动配置租户信息
app.Services.InitTenant();
app.UseAuthorization();
app.UseAuthorization();
//在认证后启用租户选择中间件
app.UseMiddleware<tenantselectmiddleware>();
app.MapControllers();
app.Run();
</tenantselectmiddleware></ishardingbootstrapper></ordervirtualtableroute></tenantdbcontext></identitydbcontext>编写租户操作
[Route("api/tenant/[controller]/[action]")]
[ApiController]
[Authorize(AuthenticationSchemes = "Bearer")]
public class TenantController : ControllerBase
{
private readonly TenantDbContext _tenantDbContext;
public TenantController(TenantDbContext tenantDbContext)
{
_tenantDbContext = tenantDbContext;
}
public async Task<iactionresult> AddOrder()
{
var order = new Order()
{
Id = Guid.NewGuid().ToString("n"),
CreationTime = DateTime.Now,
Name = new Random().Next(1,100)+"_name"
};
await _tenantDbContext.AddAsync(order);
await _tenantDbContext.SaveChangesAsync();
return Ok(order.Id);
}
public async Task<iactionresult> UpdateOrder([FromQuery]string id)
{
var order =await _tenantDbContext.Set<order>().FirstOrDefaultAsync(o=>o.Id==id);
if (order == null) return BadRequest();
order.Name = new Random().Next(1, 100) + "_name";
await _tenantDbContext.SaveChangesAsync();
return Ok(order.Id);
}
public async Task<iactionresult> GetOrders()
{
var orders =await _tenantDbContext.Set<order>().ToListAsync();
return Ok(orders);
}
}</order></iactionresult></order></iactionresult></iactionresult>启动项目
这边我们基本上已经配置好我们所需要的之后我们就可以直接启动项目了
深入浅出特征工程 – 基于 OpenMLDB 的实践指南（上）
