# DB讀寫分離開發流程
###### tags: `錢龍皇` `開發流程` `優化`
---
## 關於DB讀寫分離的架構
---
## 開發的流程
```flow
st=>start: 需求
an=>operation: 分析
imp=>operation: 實作
e=>end: 測試
st->an->imp->e
```
---
## 需求
1. Master: 1 Slave: 2個以上
2. 被定義為Slave的方法: 先用亂數取得要挑哪一個讀庫的 Connection String
3. 框架/介面設計規劃，要能縮短入門時間，容易開發
4. 定義Master/Slave請綁定在方法上，為了之後抽換通訊皮，比如同時支援: http/gRPC/websocket
5. 容易擴充/維護/修改
---
## 分析
---
### 目前存取DB的方式:
---
- EntityFramework
注入時定義連線字串 : Startup.cs
```csharp=
services.AddDbContextPool<Starnet2Context>((sp, options) =>
{
options.UseMySql("mysqlConnectionString");
});
```
---
使用時直接從注入區取得dbContext實體進行操作
```csharp=
private readonly MyContext dbContext;
publicRepository(MyContext context)
{
dbContext = context ?? throw new ArgumentNullException(nameof(context));
}
public Player Get(uint id)
{
return dbContext.Player.FirstOrDefault(e=>e.Id == id);
}
public void Add(Player player)
{
dbContext.Add(player);
}
```
---
- MysqlConnection & Dapper
於方法內直接定義sql語法和連線字串進行資料庫存取
---
```csharp=
var sql = @"SELECT A.playerId,
A.nickname AS NickName,B.realCredit AS Points,A.activeValue AS ActiveValue
FROM player A
LEFT JOIN playerCredit B On A.uid = B.uid
WHERE B.coinType = 1
ORDER BY B.realCredit DESC,A.createTime ASC
LIMIT 100";
using (var connection = new MySqlConnection("mysqlConnectionString"))
{
connection.Open();
var result = await connection.QueryAsync<RankPointsOriginalDetail>(sql);
}
```
---
### 思考方向
- 存取DB連線字串時從設定值中進行取用
- 取用EntityFramework : DBContext 要為泛型，因各服務中定義的DBContext皆獨立
- 使用reflect來處理動態型別建立時的建構子需填入設定值
---
- 外部使用時按照使用需求取得所需物件即可
1.哪個DataBase(StarNet2,RecordDB)
2.什麼動作(Read , Write)
---
## 實作
---
## DataFlow 架構圖
```graphviz
digraph sturcture{
rankdir=LR;
subgraph cluster_internal{
label = "InternalService";
fontsize = 24;
config [label="config.json" fontcolor=darkgreen , shape=note];
}
subgraph cluster_application{
label = "DataService";
fontsize = 24;
subgraph cluster_configuration{
label = "StarNet2.DomainModel.Configuration";
fontsize = 16;
configservice [label="{<f0> Configservice|<f1> GetDBContext()\nGetMySqlConnection()\nGetDBConnectionString()\nConfig }" fontcolor=red shape=Mrecord];
client [label="InternalServiceClient" shape=component];
}
};
client -> config[label="1. request", fontcolor=red]
config -> client[label="2. config", fontcolor=darkgreen]
client -> configservice:f1[label="3. config", fontcolor=darkgreen]
}
```
---
### 設定提供者
---
- 實作內容 : 提供外部此次增加的連線設定
- Solution : Web_StarNet2.InternalService
- Project : InternalServiceClient
---
DataBaseSettings.cs - 新增設定:ConnectionStrings連線庫
```csharp=
[JsonProperty("starNet2")]
public string StarNet2 { get; set; } = string.Empty;
[JsonProperty("recordDB")]
public string RecordDB { get; set; } = string.Empty;
// 以上為原本使用的設定值
[JsonProperty("dbMasterSlave")]
public DBMasterSlave DBMasterSlave { get; set; } = new DBMasterSlave();
}
public class DBMasterSlave
{
[JsonProperty("starNet2")]
public Dictionary<string, List<string>> StarNet2 { get; set; } = new Dictionary<string, List<string>>();
[JsonProperty("recordDB")]
public Dictionary<string, List<string>> RecordDB { get; set; } = new Dictionary<string, List<string>>();
}
```
---
對應InternalService/Config.json 建立相關設定
```json=
// 資料庫設定
"databaseSetting": {
"starNet2": "connectionstring",
"recordDB": "connectionstring",
// 以上為原本使用的設定值
"dbMasterSlave": {
"starNet2":{
"writers": [
"connectionstring"
],
"readers": [
"connectionstring",
"connectionstring"
]
},
"recordDB": {
"writers": [
"connectionstring"
],
"readers": [
"connectionstring",
"connectionstring"
]
}
}
},
```
---
- 發佈新版套件(StarNet2.InternalService.Client0.0.44)供給外部取用
- InternalService更新供給新設定值
---
### 方法提供者
---
- 實作內容:按使用方提供的列舉參數回傳對應的DB連線物件
- Solution : StartNet2.DomainModel
- Project : StarNet2.DomainModel.Configuration
---
DBEnum.cs - 定義取用DB&動作列舉
```csharp=
public enum DBActionTypes
{
READ = 1,
WRITE = 2
}
public enum DBTypes
{
STARNET2 = 1,
RECORD = 2
}
```
---
ConfigService.cs - 新增方法
```csharp=
/// <summary> DBActionTypes轉換為設定檔中的KeyString </summary>
private readonly Dictionary<DBActionTypes, string> DBActionTypesStringMap = new Dictionary<DBActionTypes, string>{
{ DBActionTypes.READ,"readers" },
{ DBActionTypes.WRITE,"writers" }
};
/// <summary>
/// 取得指定讀寫連線字串，會採用隨機的方式取得指定串列中連線字串
/// </summary>
/// <param name="type"></param>
/// <param name="actionTypes"></param>
/// <returns></returns>
public string GetDBConnectionString(DBTypes dBTypes, DBActionTypes actionTypes)
{
var connectionstr = string.Empty;
Random rnd = new Random();
var list = dBTypes.Equals(DBTypes.STARNET2) ?
Config.DatabaseSetting.DBMasterSlave.StarNet2[DBActionTypesStringMap[actionTypes]]:
Config.DatabaseSetting.DBMasterSlave.Record[DBActionTypesStringMap[actionTypes]];
connectionstr = list[rnd.Next(list.Count)];
return connectionstr;
}
/// <summary> 取得指定讀寫DBContext</summary>
public TContext GetDBContext<TContext>(DBTypes type, DBActionTypes actionTypes) where TContext : DbContext
{
var connectionstr = GetDBConnectionString(type, actionTypes);
var contextOptions = new DbContextOptionsBuilder<TContext>().UseMySql(connectionstr).Options;
return (TContext)Activator.CreateInstance(typeof(TContext), new object[] { contextOptions });
}
/// <summary> 取得指定讀寫MySqlConnection </summary>
public MySqlConnection GetMySqlConnection(DBTypes type, DBActionTypes actionTypes)
{
return new MySqlConnection(GetDBConnectionString(type, actionTypes));
}
```
---
- 發佈新版套件(StarNet2.DomainModel.Configuration0.0.14)供給外部取用
---
## 測試
---
- Solution : DataService
- Project : DataServiceCore
---
### 測試內容 :
- EntityFramework : 取得&更新使用者活躍度採用新版套件取得動態DB物件進行DB操作，透過PostMan進行連續呼叫確認異動內容是否正確
---
PlayerRepository.cs
```csharp=
// Read Test
public async Task<bool> IsLeader(uint playerId)
{
using (var dbContext = _configService.GetDBContext<Starnet2Context>(DBTypes.STARNET2, DBActionTypes.READ))
{
var findPlayer = await dbContext.Players
.AsNoTracking()
.FirstOrDefaultAsync(e => e.Account == account)
.ConfigureAwait(false);
if (findPlayer == null)
{
return null;
}
else
{
return new PlayerInfo
{
Id = findPlayer.PlayerId,
Uid = Guid.Parse(findPlayer.Uid),
Account = findPlayer.Account,
NickName = findPlayer.Nickname,
Email = findPlayer.Email,
PromotionCode = findPlayer.PromotionCode,
State = findPlayer.State,
CellPhone = findPlayer.CellPhone,
ActiveValue = findPlayer.ActiveValue,
CreateTime = findPlayer.CreateTime,
UpperPlayerId = findPlayer.UpperPlayerId
};
}
}
}
// Write Test
public async Task UpdatePlayerActiveValue(uint playerId, int activeValue)
{
// 取用WRITE類型DBContext操作
using (var _dbContext = _configService.GetDBContext<Starnet2Context>(DBTypes.STARNET2, DBActionTypes.WRITE))
{
var findPlayer = await _dbContext.Players
.FirstOrDefaultAsync(e => e.PlayerId == playerId)
.ConfigureAwait(false);
if (findPlayer == null)
{
throw new DataServiceException
{
ErrorCode = EnumErrorType.AccountNotExist,
ErrorMessage = EnumErrorType.AccountNotExist.ToString()
};
}
findPlayer.ActiveValue = activeValue;
await _dbContext.SaveChangesAsync();
}
}
```
---
### 測試內容 :
- MysqlConnection : 取得排行榜採用新版套件取得MySqlConnection，透過PostMan進行連續呼叫確認是否有動態去存取不同DB
---
RankRepo.cs
```csharp=
var sql = @"SELECT A.playerId,A.nickname AS NickName,B.realCredit AS Points,A.activeValue AS ActiveValue
FROM player A
LEFT JOIN playerCredit B On A.uid = B.uid
WHERE B.coinType = 1
ORDER BY B.realCredit DESC,A.createTime ASC
LIMIT 100";
// 註解到舊的using (var connection = new MySqlConnection(_configService.Config.DatabaseSetting.StarNet2))
using (var connection = _configService.GetMySqlConnection(DBTypes.STARNET2,DBActionTypes.READ))
{
connection.Open();
var result = await connection.QueryAsync<RankPointsOriginalDetail>(sql);
rank = result.ToList();
}
```
---
### 就這樣做完啦~很簡單對吧 (´∀`)
{"metaMigratedAt":"2023-06-16T10:27:50.902Z","metaMigratedFrom":"Content","title":"DB讀寫分離開發流程","breaks":true,"contributors":"[{\"id\":\"d99e1b91-e122-4da1-ab67-fed55d0216e6\",\"add\":17687,\"del\":7553}]"}