使用 docker container 測試實際寫入資料庫

發表於 更新於 閱讀次數： Disqus：

寫入資料庫中文的時候，會因為採用了 Unicode 導致寫入 DB 錯誤，因此需要測試實際 adapter 可否寫入 docker 的 mysql

基本上只指定採用的 charset 為 utf8mb4 即可，在建立資料表、預存程序的時候，也要指定相同的字元集；不特別指定定序，則採用預設的定序即可

連線字串

確保有宣告 charset=utf8mb4

1
2
3
"ConnectionStrings": {
    "DB_connection": "server=localhost;port=3306;user id=root;password=example;sslmode=none;charset=utf8mb4;ConnectionTimeout=30;",
  },

建立預存程序

中文接收參數需指定字元集: CHARACTER SET utf8mb4

1
2
3
4
CREATE PROCEDURE `usp_customers_update_v2` (IN customerName VARCHAR(255) CHARACTER SET utf8mb4)
BEGIN
    UPDATE Customers SET Name = customerName WHERE Id = 1;
END

建立測試資料表

中文欄位需指定字元集: CHARACTER SET utf8mb4

1
CREATE TABLE IF NOT EXISTS Customers( Id INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) CHARACTER SET utf8mb4);

建立測試資料庫

帳號:root
密碼:example

1
docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=example -p 3306:3306 mysql:5.7.19 --default-authentication-plugin=mysql_native_password

注意事項

  1. SP 接收參數的部分宣告 charset=utf8mb4 , 對應的資料表欄位也要宣告字元集，這種方式已測試過支援難字、emoji 等特殊字元
  2. SP 接收參數的部分宣告 nvarchar(255)，對應的資料表欄位宣告 varchar(255) 的情況也可以支援中文，但不清楚是否支援難字、emoji 等特殊字元
1
2
3
4
5
6
7
8
-- 查看資料庫的一些設定
SHOW VARIABLES WHERE Variable_name LIKE 'character_set\_%' OR Variable_name LIKE 'collation%';

> MySQL字元編碼集中有兩套UTF-8編碼實現：「utf8」和「utf8mb4」，其中「utf8」是一個字最多占據3位元組空間的編碼實現；而「utf8mb4」則是一個字最多占據4位元組空間的編碼實現，也就是UTF-8的完整實現。這是由於MySQL在4.1版本開始支援UTF-8編碼（當時參考UTF-8草案版本為RFC 2279）時，為2003年，並且在同年9月限制了其實現的UTF-8編碼的空間占用最多為3位元組，而UTF-8正式形成標準化文件（RFC 3629）是其之後。限制UTF-8編碼實現的編碼空間占用一般被認為是考慮到資料庫檔案設計的相容性和讀取最佳化，但實際上並沒有達到目的，而且在UTF-8編碼開始出現需要存入非基本多文種平面的Unicode字元（例如emoji字元）時導致無法存入（由於3位元組的實現只能存入基本多文種平面內的字元）。直到2010年在5.5版本推出「utf8mb4」來代替、「utf8」重新命名為「utf8mb3」並調整「utf8」為「utf8mb3」的別名，並不建議使用舊「utf8」編碼，以此修正遺留問題

> 來源: [維基百科](https://zh.wikipedia.org/wiki/UTF-8)

這也是為甚麼現在用 mysql 記得要用 utf8mb4 的原因，因為 utf8mb4 是完整的 utf8 編碼，可以支援 emoji 等特殊字元

範例

測試過程當中所需要用到的資料庫、資料表、預存程序等等，則是透過 TestDatabaseManager在單元測試啟動的時候做初始化，當然也會在測試結束後清理資料庫

CustomerAdapterTests.cs

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
// CustomerAdapterTests.cs
using MyProject.Adapter.Base;
using MyProject.Adapter.Common;
using MyProject.Adapter.MySql;
using MyProject.DataClass.DTO.MySql;
using MyProject.DataClass.Logger;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

namespace MyProject.Tests.InfrastructureTest;

public class CustomerAdapterTests
{
    private TestDatabaseManager _testDatabaseManager;
    private string _connectionString = string.Empty;


    [SetUp]
    public void Setup()
    {
        var builder = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);

        var configuration = builder.Build();
        _connectionString = configuration.GetConnectionString("DB_connection") ?? throw new InvalidOperationException("Missing connection string in configuration");

        _testDatabaseManager = new TestDatabaseManager(_connectionString);
        _testDatabaseManager.InitializeDatabase();

        var serviceProvider = Substitute.For<IServiceProvider>();
        serviceProvider.GetService(typeof(IConfiguration)).Returns(configuration);
        DBConfig.Configure(serviceProvider);

        var extendLogger = new ExtendLogger(Substitute.For<ILogger<ExtendLogger>>());
        serviceProvider.GetService(typeof(ExtendLogger)).Returns(extendLogger);
        PerformanceLogger.Configure(serviceProvider);
    }

    [TearDown]
    public void DestroyTestDatabase()
    {
        _testDatabaseManager.CleanUpDatabase();
    }


    private static void UpdateCustomerNameShouldSuccess(string customerName)
    {
        var customer = new Customer { customer_name = customerName, };
        // 此處使用 CustomerAdapter 處理資料庫的更新寫入
        var actual = CustomerAdapter.Update(customer);
        Assert.That(actual, Is.True);
    }

    [Test]
    [Category("RequiresDocker")]
    public void update_customer_name_contain_utf8mb4()
    {
        UpdateCustomerNameShouldSuccess("陳玉\ud855\udd65");
    }
}

TestDatabaseManager.cs

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
// TestDatabaseManager.cs
using System.Data;
using MySql.Data.MySqlClient;

namespace MyProject.Tests.InfrastructureTest;

public class TestDatabaseManager : IDisposable
{
    private const string TestDbName = "TestDB";
    private readonly MySqlConnection _connection;

    /// <summary>
    /// 資料庫管理員
    /// </summary>
    /// <remarks>
    /// 使用 docker 建立資料庫
    /// docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=example -p 3306:3306 mysql:5.7.19 --default-authentication-plugin=mysql_native_password
    /// </remarks>
    /// <param name="connectionString"></param>
    public TestDatabaseManager(string? connectionString)
    {
        if (string.IsNullOrEmpty(connectionString))
            throw new ArgumentNullException(nameof(connectionString));

        _connection = new MySqlConnection(connectionString);
    }

    public void InitializeDatabase()
    {
        CreateDatabase(TestDbName);
        UseDatabase(TestDbName);

        CreateTableCustomer();
        CreateProcedureCustomerUpdate();
    }

    private void CreateProcedureCustomerUpdate()
    {
        ExecuteNonQuery(_connection,
            @"CREATE PROCEDURE `usp_customers_update` (IN customerName VARCHAR(255) CHARACTER SET utf8mb4)
                BEGIN
                    UPDATE Customers SET Name = customerName WHERE Id = 1;
                END"
        );
    }

    private void CreateTableCustomer()
    {
        ExecuteNonQuery(_connection, "CREATE TABLE IF NOT EXISTS Customers( Id INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) CHARACTER SET utf8mb4);");
        ExecuteNonQuery(_connection, "INSERT INTO Customers (Name) VALUES ('Art Huang');");
    }

    private void UseDatabase(string database)
    {
        ExecuteNonQuery(_connection, $"USE {database};");
    }

    private void CreateDatabase(string database)
    {
        ExecuteNonQuery(_connection, $"CREATE DATABASE IF NOT EXISTS {database};");
    }

    private static void ExecuteNonQuery(MySqlConnection con, string sqlCommand)
    {
        using var cmd = new MySqlCommand(sqlCommand, con);
        try
        {
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex) when (ex is not MySqlException)
        {
            Console.WriteLine("An unexpected error occurred while trying to execute the database command.");
            Console.WriteLine(ex.Message);
            throw;  // Rethrow the exception to indicate that this method failed
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
        }
    }

    public void CleanUpDatabase()
    {
        ExecuteNonQuery(_connection, "DROP DATABASE IF EXISTS TestDB;");
    }

    public void Dispose()
    {
        _connection?.Dispose();
        GC.SuppressFinalize(this);
    }
}