介紹
GitHub地址
ShardingCore 一款ef-core下高性能、輕量級針對分表分庫讀寫分離的解決方案，具有零依賴、零學(xué)習(xí)成本、零業(yè)務(wù)代碼入侵
dotnet下唯一一款全自動分表,多字段分表框架,擁有高性能,零依賴、零學(xué)習(xí)成本、零業(yè)務(wù)代碼入侵,并且支持讀寫分離動態(tài)分表分庫,同一種路由可以完全自定義的新星組件,通過本框架你不但可以學(xué)到很多分片的思想和技巧,并且更能學(xué)到Expression的奇思妙用
我不是efcore怎么辦
這邊肯定有小伙伴要問有沒有不是efcore的,我這邊很確信的和你講有并且適應(yīng)所有的ADO.NET包括sqlhelper
ShardingConnector 一款基于ado.net下的高性能分表分庫解決方案目前已有demo案例，這個框架你可以認(rèn)為是.Net版本的ShardingSphere但是目前僅實(shí)現(xiàn)了ShardingSphere-JDBC,后續(xù)將會實(shí)現(xiàn)ShardingSphere-Proxy
原理
ShardingCore的整體架構(gòu)是一個殼dbcontext帶多個dbcontext，殼dbcontext不進(jìn)行增刪改查，由內(nèi)部的dbcontext自己去執(zhí)行，這個因?yàn)閑fcore的一個對象對應(yīng)一個表所限制的。
我們這邊把殼dbcontext稱作shellDbContext，執(zhí)行的dbcontext叫做executorDbContext,對于ShardingCore還有一個要求就是需要初始化啟動的時候Start()，Start()內(nèi)部需要IServiceProvider來獲取DbContext，所以說整個框架離不開ioc，那么就需要啟動的時候依賴注入DbContext,又因?yàn)橐蕾囎⑷肴绻悄J(rèn)的只能允許單個構(gòu)造函數(shù)。
實(shí)現(xiàn)
添加依賴
這邊我們添加了三個包,分別是ShardingCore,Microsoft.EntityFrameworkCore.SqlServer,Pomelo.EntityFrameworkCore.MySql,其中ShardingCore用的是預(yù)覽版的如果不勾選那么將無法顯示出來,為什么我們需要添加額外的兩個數(shù)據(jù)庫驅(qū)動呢,原因是因?yàn)槲覀冃枰诓煌淖鈶粝聦?shí)現(xiàn)不同的數(shù)據(jù)庫的配置,比如租戶A和我們簽訂的協(xié)議里面有說明系統(tǒng)使用開源數(shù)據(jù)庫,或者希望使用Linux平臺那么可以針對租戶A進(jìn)行配置MySql或者PgSql,租戶B是資深軟粉說需要使用MSSQL那么就可以針對其配置MSSQL.一般情況下我們可能不會出現(xiàn)多數(shù)據(jù)庫的情況但是為了照顧到特殊情況我們這邊也針對這種情況進(jìn)行了支持。
公共用戶存儲
首先在我還沒有創(chuàng)建租戶的時候是不存在數(shù)據(jù)庫的所以我的數(shù)據(jù)自然而然不會存在當(dāng)前租戶下,這邊我們采用的是存儲到其他數(shù)據(jù)庫中,假設(shè)我們使用一個公共的數(shù)據(jù)庫作為用戶系統(tǒng).
創(chuàng)建用戶系統(tǒng)
創(chuàng)建系統(tǒng)用戶和創(chuàng)建系統(tǒng)用戶在數(shù)據(jù)庫內(nèi)的映射關(guān)系
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));
}
}
創(chuàng)建這個數(shù)據(jù)庫該有的配置信息表，便于后期啟動后重建
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));
}
}
創(chuàng)建對應(yīng)的系統(tǒng)用戶存儲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());
}
}
創(chuàng)建一個租戶的DbContext
public class TenantDbContext:AbstractShardingDbContext,IShardingTableDbContext
{
public TenantDbContext(DbContextOptions<TenantDbContext> options) : base(options)
{
}
public IRouteTail RouteTail { get; set; }
}
目前我們先定義好后續(xù)進(jìn)行編寫內(nèi)部的租戶代碼
創(chuàng)建動態(tài)租戶參數(shù)
動態(tài)租戶分片配置信息在ShardingCore只需要實(shí)現(xiàn)IVirtualDataSourceConfigurationParams<TShardingDbContext>接口,但是這個接口有很多參數(shù)需要填寫,所以這邊框架針對這個接口進(jìn)行了默認(rèn)參數(shù)的抽象類AbstractVirtualDataSourceConfigurationParams<TShardingDbContext>。
這邊我們針對配置參數(shù)進(jìn)行配置采用新建一個配置json的對象
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; }
}
參數(shù)里面配置了當(dāng)前數(shù)據(jù)庫,這邊比較簡單我們就暫時使用單表分庫的模式來實(shí)現(xiàn),目前暫時不對每個租戶分庫進(jìn)行演示。之后并且編寫SqlServer和MySql的配置支持
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;
//用來快速判斷是否存在數(shù)據(jù)庫中的表
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;
}
}
編寫用戶注冊接口
[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();
//注冊完成后進(jìn)行配置生成
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");
//秘鑰，就是標(biāo)頭，這里用Hmacsha256算法，需要256bit的密鑰
var securityKey = new SigningCredentials(new SymmetricSecurityKey(Encoding.ASCII.GetBytes("123123!@#!@#123123")), SecurityAlgorithms.HmacSha256);
//Claim，JwtRegisteredClaimNames中預(yù)定義了好多種默認(rèn)的參數(shù)名，也可以像下面的Guid一樣自己定義鍵名.
//ClaimTypes也預(yù)定義了好多類型如role、email、name。Role用于賦予權(quán)限，不同的角色可以訪問不同的接口
//相當(dāng)于有效載荷
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; }
}
簡單來說明一下,這邊我們采用的是用戶的id作為租戶id,將租戶id作為數(shù)據(jù)庫配置,來支持多配置模式。到此為止我們的用戶系統(tǒng)就已經(jīng)完成了是不是十分的簡單僅僅幾段代碼,用戶這邊注冊完成后將會創(chuàng)建對應(yīng)的數(shù)據(jù)庫和對應(yīng)的表,如果你是分表的那么將會自動創(chuàng)建對應(yīng)的數(shù)據(jù)庫表等信息。
租戶系統(tǒng)
租戶系統(tǒng)我們做一個訂單的簡單演示,使用訂單id取模,取模取5來進(jìn)行分表操作
新增租戶系統(tǒng)的訂單信息
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));
}
}
新增訂單路由
public class OrderVirtualTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<Order>
{
public OrderVirtualTableRoute() : base(2, 5)
{
}
public override void Configure(EntityMetadataTableBuilder<Order> builder)
{
builder.ShardingProperty(o => o.Id);
}
}
簡單的字符串取模
添加租戶中間件
添加租戶中間件,在系統(tǒng)中如果使用多配置那么就必須要指定本次創(chuàng)建的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);
}
}
該中間件攔截/api/tenant路徑下的所有請求并且針對這些請求添加對應(yīng)的租戶信息
配置租戶擴(kuò)展初始化數(shù)據(jù)
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));
}
}
}
}
}
這邊因?yàn)槲覀冡槍ψ鈶粜畔⑦M(jìn)行了初始化而不是硬編碼,所以需要一個在啟動的時候?qū)ψ鈶粜畔⑦M(jìn)行動態(tài)添加
配置多租戶
啟動配置Startup
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddAuthentication();
#region 用戶系統(tǒng)配置
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);
//認(rèn)證參數(shù)
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 =>
{
//默認(rèn)配置一個
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();
//在認(rèn)證后啟用租戶選擇中間件
app.UseMiddleware<TenantSelectMiddleware>();
app.MapControllers();
app.Run();
編寫租戶操作
[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);
}
}
啟動項(xiàng)目
這邊我們基本上已經(jīng)配置好我們所需要的之后我們就可以直接啟動項(xiàng)目了
這邊我們通過接口注冊了一個TenantA的用戶并且選擇了使用MSSQL,這邊成就幫我們自動生成好了對應(yīng)的數(shù)據(jù)庫表結(jié)構(gòu)
接下來我么再注冊一個TenantB用戶選擇MySql
通過截圖我們可以看到ShardingCore也是為我們創(chuàng)建好了對應(yīng)的數(shù)據(jù)庫和對應(yīng)的表信息
登錄租戶
首先我們登錄
TenantA用戶token
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiJkNGMwZjZiNzI5MzE0M2VlYWM0Yjg3NzUwYzE4MWUzOSIsInVpZCI6ImMxMWRkZjFmNTY0MjQwZjc5YTQzNTEzZGMwNmVjZGMxIiwiZXhwIjoxNjQxODI4ODQ0fQ.zJefwnmcIEZm-kizlN7DhwTRgGxiCg52Esa8QmHiEKY
TenantB用戶token
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2xvY2FsaG9zdDo1MDAwIiwiYXVkIjoiYXBpIiwiaWQiOiIwNzY4NzUwMmVjYzY0NTMyOGFkNTcwZDRkYjMwNDI3MSIsInVpZCI6ImVkODg4YTc3MzAwYTQ4NjZhYmUyNWY2MTE1NmEwZTQzIiwiZXhwIjoxNjQxODI4ODgxfQ.cL0d010jdXLXNGT8M0wsRMqn3VeIxFnV0keM0H3SPzo
接下來我們分別對兩個租戶進(jìn)行交叉處理
AddOrder
租戶A插入一個訂單,訂單Id：aef6905f512a4f72baac5f149ef32d21
TenantB用戶也插入一個訂單,訂單id:450f5dd0e82442eca33dfcf3d57fafa3
兩個用戶處理
通過日志打印明顯能夠感覺出來兩者是區(qū)分了不同的數(shù)據(jù)庫
UpdateOrder
GetOrders文章來源：http://www.zghlxwxcb.cn/news/detail-475927.html
來源
.NET Core 框架 WTM 的分表分庫實(shí)現(xiàn)
.net core下分表分庫解決方案–多租戶文章來源地址http://www.zghlxwxcb.cn/news/detail-475927.html
到了這里，關(guān)于【.Net Core】ShardingCore分庫分表解決方案之多租戶的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
