MES-ETL/MesETL.Test/DatabaseToolBox.cs

using System.Data;
using System.Text;
using MesETL.App.Helpers;
using MesETL.Shared.Helper;
using MySqlConnector;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Xunit.Abstractions;

namespace TestProject1;

public class DatabaseToolBox
{
    private readonly ITestOutputHelper _output;
    public const string ConnStr = "Server=localhost;Port=3306;UserId=root;Password=123456;";

    public DatabaseToolBox(ITestOutputHelper output)
    {
        _output = output;
    }

    [Theory]
    [InlineData("cferp_void_1")]
    public async Task AlterAllTableToBlackHole(string database)
    {
        var connStr = ConnStr + $"Database={database}";
        var tables = await DatabaseHelper.QueryTableAsync(connStr,
            $"""
             SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{database}';
             """);

        foreach (DataRow row in tables.Tables[0].Rows)
        {
            var tableName = row["TABLE_NAME"].ToString();
            var sql = $"""
                       ALTER TABLE `{tableName}` REMOVE PARTITIONING;
                       """;
            try
            {
                await DatabaseHelper.NonQueryAsync(connStr, sql);
            }
            catch (MySqlException e) when (e.ErrorCode == MySqlErrorCode.PartitionManagementOnNoPartitioned)
            {
            }

            sql = $"""
                   ALTER TABLE `{tableName}` ENGINE=BLACKHOLE;
                   """;
            await DatabaseHelper.NonQueryAsync(connStr, sql);
        }
    }

    public async Task<long> CountAllTable(string connStr, string database)
    {
        var count = 0L;
        var set = await DatabaseHelper.QueryTableAsync(connStr,
            $"""
             SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{database}';
             """);
        foreach (DataRow row in set.Tables[0].Rows)
        {
            count += Convert.ToInt64(await DatabaseHelper.QueryScalarAsync(connStr,
                $"""
                 SELECT COUNT(1) FROM `{database}`.`{row[0]}`;
                 """));
        }
        _output.WriteLine($"Record count: {count} records");
        return count;
    }

    [Theory]
    [InlineData([new[]{"cferp_test_1", "cferp_test_2", "cferp_test_3"}])]
    public async Task CountAllDatabase(IEnumerable<string> databases)
    {
        var count = 0L;
        var connStr = "Server=192.168.1.245;Port=3306;UserId=root;Password=ruixinjie!@#123;";
        foreach (var db in databases)
        {
            count += await CountAllTable(connStr, db);
        }

        _output.WriteLine(count.ToString());
    }

    public async Task<TableIndex[]> GetAllTableIndexes(string database)
    {
        var data = await DatabaseHelper.QueryTableAsync(ConnStr,
            $"""
            SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME, INDEX_TYPE FROM information_schema.STATISTICS
                     WHERE TABLE_SCHEMA = '{database}'
                       AND INDEX_NAME != 'PRIMARY'
                       AND INDEX_TYPE = 'BTREE'
            """);
        return data.Tables[0].Rows.Cast<DataRow>().Select(row =>
        {
            return new TableIndex(row["TABLE_NAME"].ToString()!,
                row["INDEX_NAME"].ToString()!,
                !Convert.ToBoolean(row["NON_UNIQUE"]),
                row["COLUMN_NAME"].ToString()!,
                row["INDEX_TYPE"] switch
                {
                    "BTREE" => TableIndex.TableIndexType.BTree,
                    "PRIMARY" => TableIndex.TableIndexType.Primary,
                    "HASH" => TableIndex.TableIndexType.Hash,
                    _ => throw new ArgumentOutOfRangeException(nameof(row))
                }
            );
        }).ToArray();
    }

    [Theory]
    [InlineData(["mesdb_1"])]
    [InlineData(["mesdb_2"])]
    [InlineData(["mesdb_3"])]
    [InlineData(["mesdb_4"])]
    [InlineData(["mesdb_5"])]
    public async Task ShowIndex(string database)
    {
        var indexes = await GetAllTableIndexes(database);
        var sb = new StringBuilder();
        foreach (var (tableName, indexName, isUnique, columnName, tableIndexType) in indexes!)
        {
            sb.AppendLine($"Drop {(isUnique ? "UNIQUE" : string.Empty)} INDEX `{indexName}` ON `{database}`.`{tableName}`;");
        }

        _output.WriteLine(sb.ToString());
    }

    [Theory]
    [InlineData(["cferp_test_1", "D:/Indexes_cferp_test_1.json"])]
    [InlineData(["cferp_test_2", "D:/Indexes_cferp_test_2.json"])]
    [InlineData(["cferp_test_3", "D:/Indexes_cferp_test_3.json"])]
    public async Task ExportAllIndexes(string database, string outputPath)
    {
        var indexes = await GetAllTableIndexes(database);
        var json = JArray.FromObject(indexes);
        await File.WriteAllTextAsync(outputPath, json.ToString());
        _output.WriteLine($"Exported {indexes.Length} indexes to '{outputPath}'");
    }

    [Theory]
    [InlineData("cferp_test_1", "D:/Indexes_cferp_test_1.json")]
    [InlineData("cferp_test_2", "D:/Indexes_cferp_test_2.json")]
    [InlineData("cferp_test_3", "D:/Indexes_cferp_test_3.json")]
    public async Task ImportAllIndexes(string database, string importPath)
    {
        var json = await File.ReadAllTextAsync(importPath);
        var indexes = JsonConvert.DeserializeObject<TableIndex[]>(json);
        var sb = new StringBuilder();
        foreach (var (tableName, indexName, isUnique, columnName, tableIndexType) in indexes!)
        {
            sb.AppendLine($"CREATE {(isUnique ? "UNIQUE" : string.Empty)} INDEX `{indexName}` ON `{database}`.`{tableName}` (`{columnName}`) USING {tableIndexType};");
        }
        await DatabaseHelper.NonQueryAsync(ConnStr, sb.ToString());
        _output.WriteLine($"Import {indexes.Length} indexes to '{database}' from '{importPath}'");
    }

    [Theory]
    [InlineData(["mesdb_1"])]
    [InlineData(["mesdb_2"])]
    [InlineData(["mesdb_3"])]
    [InlineData(["mesdb_4"])]
    [InlineData(["mesdb_5"])]
    public async Task DropAllIndex(string database)
    {
        var indexes = await GetAllTableIndexes(database);
        var sb = new StringBuilder();
        foreach (var (tableName, indexName) in indexes)
        {
            sb.AppendLine($"DROP INDEX `{indexName}` ON `{database}`.`{tableName}`;");
        }
        await DatabaseHelper.NonQueryAsync(ConnStr, sb.ToString());
        _output.WriteLine($"Dropped {indexes.Length} indexes from {database}");
    }

    [Theory]
    [InlineData("mesdb_1")]
    [InlineData("mesdb_2")]
    [InlineData("mesdb_3")]
    [InlineData("mesdb_4")]
    [InlineData("mesdb_5")]
    [InlineData("mesdb_6")]
    public async Task TruncateAllTable(string database)
    {
        var tables = await DatabaseHelper.QueryTableAsync(ConnStr,
            $"""
             SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{database}';
             """);
        var sb = new StringBuilder();
        sb.AppendLine($"USE `{database}`;");
        foreach (DataRow row in tables.Tables[0].Rows)
        {
            var tableName = row["TABLE_NAME"].ToString();
            var sql = $"""
                       TRUNCATE TABLE `{tableName}`;
                       """;
            sb.AppendLine(sql);
        }
        await DatabaseHelper.NonQueryAsync(ConnStr, sb.ToString());
    }

    [Theory]
    [InlineData("cferp_test_1")]
    [InlineData("cferp_test_2")]
    [InlineData("cferp_test_3")]
    public async Task AnalyzeAllTable(string database)
    {
        var tables = await DatabaseHelper.QueryTableAsync(ConnStr,
            $"""
             SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{database}';
             """);
        var sb = new StringBuilder();
        sb.AppendLine($"USE `{database}`;");
        foreach (DataRow row in tables.Tables[0].Rows)
        {
            var tableName = row["TABLE_NAME"].ToString();
            var sql = $"""
                       ANALYZE TABLE `{tableName}`;
                       """;
            sb.AppendLine(sql);
        }
        await DatabaseHelper.NonQueryAsync(ConnStr, sb.ToString());
    }
}