0%

[DotnetCore]ORM系列-Chloe:共同欄位新增、編輯技巧

前提情要

有在觀看筆者的ORM系列文就知道,其中EFCore就這麼一篇出現過,即使用override SaveChanges方法來達到針對共同欄位的新增、編輯功能,什麼叫共同欄位,筆者常設計的就是CreatedAtCreatedByUpdatedAtUpdatedBy等,這因開發環境不同,習慣的命名方式可能不一樣,但一樣的是,要紀錄該筆資料列的新增、編輯的時間及使用者,原因就這麼單純。
若以DapperADO.Net以純SQL的方式製作,會在InsertUpdate語法上多組這些相關欄位上去,也不是不行,但以一個大型系統來說,若偏後台管理平台來說,這些欄位都是極重要,且每張資料表皆必須要有,這時每段Service都要填上,也是會累死人,[DotnetCore]ORM系列-EFCore:資料表共同欄位設定就是於EFCore世界中的解決方式,筆者今天想要來聊聊Chloe的解決方式。

內容

回憶Interceptor

這章節,筆者就很快帶過,Chloe套件本身提供攔截器,你要存取資料庫的IO作業途中,可以透過攔截器做一些事情,例如紀錄Log等等,可以透過它提供的事件器做攔截,主要大分類分為ExecutingExecuted兩大類,對於ADO.Net提供的方法

  • ExecuteNonQuery
  • ExecuteReader
  • ExecuteScalar

以上這三種對應ExecutingExecuted,共有六大Method可以改寫

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
public class DbCommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuted(IDbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
throw new NotImplementedException();
}

public void NonQueryExecuting(IDbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
throw new NotImplementedException();
}

/* 执行 DbCommand.ExecuteReader() 时调用 */
public void ReaderExecuting(IDbCommand command, DbCommandInterceptionContext<IDataReader> interceptionContext)
{
interceptionContext.DataBag.Add("startTime", DateTime.Now);
Console.WriteLine(command.CommandText);
}
/* 执行 DbCommand.ExecuteReader() 后调用 */
public void ReaderExecuted(IDbCommand command, DbCommandInterceptionContext<IDataReader> interceptionContext)
{
DateTime startTime = (DateTime)(interceptionContext.DataBag["startTime"]);
Console.WriteLine(DateTime.Now.Subtract(startTime).TotalMilliseconds);
if (interceptionContext.Exception == null)
Console.WriteLine(interceptionContext.Result.FieldCount);
}

public void ScalarExecuted(IDbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
throw new NotImplementedException();
}

public void ScalarExecuting(IDbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
throw new NotImplementedException();
}
}

接著於DI區段中註冊其Interceptor

1
2
3
4
5
6
7
8
9
10
11
12
public void ConfigureServices(IServiceCollection services)
{
// 以上省略
services.AddScoped<ChloeORM.IDbContext>((serviceProvider) =>
{
var dbContext = new ChloeORM.MySql.MySqlContext(
new MySqlConnectionFactory(Configuration.GetConnectionString("ClassicModels")));
dbContext.Session.AddInterceptor(new DbCommandInterceptor());//新增自己實作的Interceptor
return dbContext;
});
// 以下省略
}

Insert改造作業

筆者DEMO御用的MySQL範例資料庫classicmodels,可惜沒有設計這種共同欄位,筆者就拿自己實作的專案中真實例子來做示範,但因專案本身需保密,筆者這邊就僅貼出與這主題相關的Code,該專案搭配的資料庫是PostgreSql,因此Chloe套件部份需安裝

1
dotnet add package Chloe.PostgreSQL

另外註明一下專案搭配資料庫中,共同欄位分別為

  • created_at
  • created_by
  • updated_at
  • updated_by

SQL Parser

筆者要來解釋一下,為什麼需要這節勒,從上述的回憶章節可以看出,在Interceptor事件中可以取得的是IDbCommand物件,可以想像一下,之所以可以透過Lamba的形式存取、操作資料庫,是因Chloe套件將我們撰寫的Lambda語法透過Expression Tree的解析,最終組合成Sql指令,並以IDBCommand的形式,透過Microsoft.Data.SqlClient(ADO.Net),進行資料庫操作。
因此我們想要在Interceptor事件中加工,必須得解析其CommandText,筆者搜尋了一下SQL Parser這個關鍵字,眼中浮現的是https://github.com/bruce-dunwiddie/tsql-parserTSQL解析一大利器,不妨來用看看吧。

安裝套件

1
dotnet add package TSQL.Parser

原理介紹

跟著筆者看一段code感受一下其用法及威力

1
2
3
4
5
6
7
8
var tsql = command.CommandText;
TSQLStatement statement = TSQLStatementReader.ParseStatements(tsql)[0];
switch (statement.Type)
{
case TSQLStatementType.Insert:
//撰寫實際操作內容
break;
}

我們將得到的sql指令透過TSQLStatementReader.ParseStatements解析,因無法確認sql指令包含幾個sql指令,因此該Method回傳的是一個TSQLStatement List,因筆者確定專案情境只會包含一個sql指令,因此取第一個Item,接著要做一些過濾使更安全的實作。試想我們撰寫程式碼的地方是Interceptor,經由註冊是全域套用的,只要透過Chloe套件操作的DBCommand皆會經過該Interceptor事件方法。
因Sql指令本身屬select或exec SP,insert或者update等諸多指令,皆會經過這些事件方法中,因此必須過濾為Insert Type,理解到這邊已掌握其套路。接著介紹一下TSQL.Parser的運作原理,他會將我們餵進去的sql指令一段一段解析,拆解為不同類型的TSQLTokenType,筆者用到的類別為

  • StringLiteral
  • Variable

Insert Into這種則歸類在Keyword類型中,但因筆者一開始就判斷好TSQLStatementType.Insert,因此不需要再過濾一次其Keyword類型的TSQLTokenType,我們只要將StringLiteralVariable搜集好,再做一些邏輯處理就可以了。

整合至Interceptor事件中

透過上個章節,已經有TSQL.Parser的基礎概念後,接著該是實作的時候了,但還差一步,回憶章節中提到的有六大Event Method可以使用,筆者這主題的情境是實際執行SQL指令前做加工,因此一定是寫在Executing事件中,再來就要實際跑跑看ChloeInsert語法會跑到哪個事件中,經由筆者測試,發現ChloeInsert語法會使用ExecuteReader,而非ExecuteNonQuery,其因在於Chloe很貼心的將Insert後的資料列所對應之識別序號會撈回來讓程式端繼續使用,因此使用ExecuteReader
這樣一來我們就知道將程式該寫在哪裡,就在ReaderExecuting事件中,接著看程式碼之前聊聊筆者的思路

  • 蒐集StringLiteralVariable類型的TSQLTokenType
  • 因Sql指令的特色,Column與Variable是雙雙對對對應關係,因此透過關鍵字create_atupdate_at找到StringLiteral清單中的Index
  • 透過第二步驟得到的Index,於Variable清單中搜尋對應的Paramater Name
  • 透過第三步驟得到的Parameter Name於command.Paramaters中搜尋其對應Parameter
  • 透過第四步驟得到的DBParameterValue重新指定為DateTime.Now

講完洛洛等的思路,咀嚼一下,接著看實際程式碼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public void ReaderExecuting(IDbCommand command, DbCommandInterceptionContext<IDataReader> interceptionContext)
{
var tsql = command.CommandText;
TSQLStatement statement = TSQLStatementReader.ParseStatements(tsql)[0];
switch (statement.Type)
{
case TSQLStatementType.Insert:
TSQLInsertStatement insert = TSQLStatementReader.ParseStatements(tsql)[0] as TSQLInsertStatement;
var stringLiteralList = new List<string>();
var variableList = new List<string>();
foreach (TSQLToken token in insert.Tokens)
{
switch (token.Type)
{
case TSQLTokenType.StringLiteral:
stringLiteralList.Add(token.Text);
break;
case TSQLTokenType.Variable:
variableList.Add(token.Text);
break;
}
}
var createdAtIndex = stringLiteralList.IndexOf("\"created_at\"");
var updatedAtIndex = stringLiteralList.IndexOf("\"updated_at\"");
var createdAtdbParameter = command.Parameters[command.Parameters.IndexOf(variableList[createdAtIndex - 1])] as NpgsqlParameter;
createdAtdbParameter.Value = DateTime.Now;
var updatedAtdbParameter = command.Parameters[command.Parameters.IndexOf(variableList[updatedAtIndex - 1])] as NpgsqlParameter;
updatedAtdbParameter.Value = DateTime.Now;
break;
default:
break;
}
// 以下省略
}

以上程式碼部份,要交代一下的是,筆者已經在前面說過ColumnVariable(Parameter Name)雙雙對對的關係,為何還要Index - 1,而不是直接使用相同的Index?那是因為Chloe本身在轉換Sql指令時Table名稱也會加雙引號,導致TSQL Parser的解析下,Table名稱也會被歸類在String LiteralTSQLTokenType中,因此Index - 1才會是對的Index,這是筆者事先於Linqpad中驗證過得出的經驗。

結論

來到這,已大功告成,筆者認為已把來龍去脈交代過,進階延伸就靠各位的本領了,因筆者某些專案僅使用Chloe套件存取資料庫,因此得找出相對應的解決方案,筆者也持續在精進中,希望這篇對你有幫助。

參考