Add migration.

10 files changed, 326 insertions, 53 deletions

diff --git a/cspell.yaml b/cspell.yaml
index 7ebeb5b..b9a2fc9 100644
--- a/cspell.yaml
+++ b/cspell.yaml
@@ -16,4 +16,5 @@ words:
   - ASPNETCORE
   - autoincrement
   - crupest
+  - Fxxk
   - todos
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
index 284dbe2..ad0d34c 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs
@@ -1,3 +1,4 @@
+using CrupestApi.Commons.Crud.Migrations;
 using Microsoft.Extensions.Logging.Abstractions;
 
 namespace CrupestApi.Commons.Crud.Tests;
@@ -15,7 +16,7 @@ public class CrudServiceTest
         var dbConnectionFactory = new SqliteMemoryConnectionFactory();
 
         _crudService = new CrudService<TestEntity>(
-            tableInfoFactory, dbConnectionFactory, NullLoggerFactory.Instance);
+            tableInfoFactory, dbConnectionFactory, new SqliteDatabaseMigrator(), NullLoggerFactory.Instance);
     }
 
     [Fact]
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
index 30b75af..13d9d6d 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
@@ -58,7 +58,7 @@ public class ColumnInfo
     public IColumnTypeInfo ColumnType { get; }
 
     public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true;
-    public bool IsAutoIncrement => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsAutoIncrement) is true;
+    public bool IsAutoIncrement => IsPrimaryKey;
     public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true;
     public bool IsOnlyGenerated => Metadata.GetValueOrDefault(ColumnMetadataKeys.OnlyGenerated) is true;
     public bool IsNoUpdate => Metadata.GetValueOrDefault(ColumnMetadataKeys.NoUpdate) is true;
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
index 0ee2837..7247ff1 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
@@ -5,7 +5,6 @@ public static class ColumnMetadataKeys
     public const string ColumnName = nameof(ColumnAttribute.ColumnName);
     public const string NotNull = nameof(ColumnAttribute.NotNull);
     public const string IsPrimaryKey = nameof(ColumnAttribute.IsPrimaryKey);
-    public const string IsAutoIncrement = nameof(ColumnAttribute.IsAutoIncrement);
     public const string Index = nameof(ColumnAttribute.Index);
 
     /// <summary>
@@ -104,9 +103,6 @@ public class ColumnAttribute : Attribute, IColumnMetadata
     // default false
     public bool IsPrimaryKey { get; init; }
 
-    // default false
-    public bool IsAutoIncrement { get; init; }
-
     // default None
     public ColumnIndexType Index { get; init; } = ColumnIndexType.None;
 
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
index a56790a..5e00b28 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -1,5 +1,5 @@
 using System.Data;
-using Dapper;
+using CrupestApi.Commons.Crud.Migrations;
 
 namespace CrupestApi.Commons.Crud;
 
@@ -16,17 +16,37 @@ public class CrudService<TEntity> : IDisposable where TEntity : class
     protected readonly string? _connectionName;
     protected readonly IDbConnection _dbConnection;
     private readonly bool _shouldDisposeConnection;
+    private IDatabaseMigrator _migrator;
     private readonly ILogger<CrudService<TEntity>> _logger;
 
-    public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, ILoggerFactory loggerFactory)
+    public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory)
     {
         _connectionName = GetConnectionName();
         _table = tableInfoFactory.Get(typeof(TEntity));
         _dbConnection = dbConnectionFactory.Get(_connectionName);
         _shouldDisposeConnection = dbConnectionFactory.ShouldDisposeConnection;
+        _migrator = migrator;
         _logger = loggerFactory.CreateLogger<CrudService<TEntity>>();
 
-        CheckDatabase(_dbConnection);
+        if (migrator.NeedMigrate(_dbConnection, _table))
+        {
+            _logger.LogInformation($"Entity {_table.TableName} needs migration.");
+            if (migrator.CanAutoMigrate(_dbConnection, _table))
+            {
+                _logger.LogInformation($"Entity {_table.TableName} can be auto migrated.");
+                migrator.AutoMigrate(_dbConnection, _table);
+                AfterMigrate(_dbConnection, _table, loggerFactory);
+            }
+            else
+            {
+                _logger.LogInformation($"Entity {_table.TableName} can not be auto migrated.");
+                throw new Exception($"Entity {_table.TableName} needs migration but can not be auto migrated.");
+            }
+        }
+        else
+        {
+            _logger.LogInformation($"Entity {_table.TableName} does not need migration.");
+        }
     }
 
     protected virtual string GetConnectionName()
@@ -34,19 +54,9 @@ public class CrudService<TEntity> : IDisposable where TEntity : class
         return typeof(TEntity).Name;
     }
 
-    protected virtual void CheckDatabase(IDbConnection dbConnection)
+    protected virtual void AfterMigrate(IDbConnection dbConnection, TableInfo tableInfo, ILoggerFactory loggerFactory)
     {
-        if (!_table.CheckExistence(dbConnection))
-        {
-            DoInitializeDatabase(dbConnection);
-        }
-    }
 
-    protected virtual void DoInitializeDatabase(IDbConnection connection)
-    {
-        using var transaction = connection.BeginTransaction();
-        connection.Execute(_table.GenerateCreateTableSql(), transaction: transaction);
-        transaction.Commit();
     }
 
     public void Dispose()
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
index e9f28bc..a7e5193 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
@@ -1,3 +1,4 @@
+using CrupestApi.Commons.Crud.Migrations;
 using CrupestApi.Commons.Secrets;
 using Microsoft.Extensions.DependencyInjection.Extensions;
 
@@ -10,6 +11,7 @@ public static class CrudServiceCollectionExtensions
         services.TryAddSingleton<IDbConnectionFactory, SqliteConnectionFactory>();
         services.TryAddSingleton<IColumnTypeProvider, ColumnTypeProvider>();
         services.TryAddSingleton<ITableInfoFactory, TableInfoFactory>();
+        services.TryAddSingleton<IDatabaseMigrator, SqliteDatabaseMigrator>();
         services.AddSecrets();
         return services;
     }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
new file mode 100644
index 0000000..3d59c21
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
@@ -0,0 +1,66 @@
+using System.Data;
+
+namespace CrupestApi.Commons.Crud.Migrations;
+
+public class TableColumn : IEquatable<TableColumn>
+{
+    public TableColumn(string name, string type, bool isNullable, int primaryKey)
+    {
+        Name = name.ToLowerInvariant();
+        Type = type.ToLowerInvariant();
+        IsNullable = isNullable;
+        PrimaryKey = primaryKey;
+    }
+
+    public string Name { get; set; }
+    public string Type { get; set; }
+    public bool IsNullable { get; set; }
+
+    /// <summary>
+    /// 0 if not primary key. 1-based index if in primary key.
+    /// </summary>
+    public int PrimaryKey { get; set; }
+
+    bool IEquatable<TableColumn>.Equals(TableColumn? other)
+    {
+        if (other is null)
+        {
+            return false;
+        }
+
+        return Name == other.Name && Type == other.Type && IsNullable == other.IsNullable && PrimaryKey == other.PrimaryKey;
+    }
+
+    public override bool Equals(object? obj)
+    {
+        return Equals(obj as TableColumn);
+    }
+
+    public override int GetHashCode()
+    {
+        return HashCode.Combine(Name, Type, IsNullable, PrimaryKey);
+    }
+}
+
+public class Table
+{
+    public Table(string name)
+    {
+        Name = name;
+    }
+
+    public string Name { get; set; }
+    public List<TableColumn> Columns { get; set; } = new List<TableColumn>();
+}
+
+public interface IDatabaseMigrator
+{
+    Table GetTable(IDbConnection dbConnection, string name);
+    Table ConvertTableInfoToTable(TableInfo tableInfo);
+    string GenerateCreateTableColumnSqlSegment(TableColumn column);
+    string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns);
+    bool TableExists(IDbConnection connection, string tableName);
+    bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo);
+    bool CanAutoMigrate(IDbConnection dbConnection, TableInfo tableInfo);
+    void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo);
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
new file mode 100644
index 0000000..762e95d
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
@@ -0,0 +1,212 @@
+using System.Data;
+using System.Text;
+using System.Text.RegularExpressions;
+using Dapper;
+
+namespace CrupestApi.Commons.Crud.Migrations;
+
+public class SqliteDatabaseMigrator : IDatabaseMigrator
+{
+    private void CheckTableName(string name)
+    {
+        if (Regex.Match(name, @"^[_0-9a-zA-Z]+$").Success is false)
+        {
+            throw new ArgumentException("Fxxk, what have you passed as table name.");
+        }
+    }
+
+    public Table GetTable(IDbConnection dbConnection, string name)
+    {
+        CheckTableName(name);
+
+        var table = new Table(name);
+        var queryColumns = dbConnection.Query<dynamic>($"PRAGMA table_info({name})");
+
+        foreach (var column in queryColumns)
+        {
+            var columnName = (string)column.name;
+            var columnType = (string)column.type;
+            var isNullable = (bool)column.notnull;
+            var primaryKey = (long)column.pk;
+
+            table.Columns.Add(new TableColumn(columnName, columnType, isNullable, (int)primaryKey));
+        }
+
+        return table;
+    }
+
+    public Table ConvertTableInfoToTable(TableInfo tableInfo)
+    {
+        var table = new Table(tableInfo.TableName);
+
+        foreach (var columnInfo in tableInfo.Columns)
+        {
+            table.Columns.Add(new TableColumn(columnInfo.ColumnName, columnInfo.ColumnType.GetSqlTypeString(),
+                !columnInfo.IsNotNull, columnInfo.IsPrimaryKey ? 1 : 0));
+        }
+
+        return table;
+    }
+
+    public bool CanAutoMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+    {
+        if (!TableExists(dbConnection, tableInfo.TableName)) return true;
+
+        var databaseTable = GetTable(dbConnection, tableInfo.TableName);
+        var wantedTable = ConvertTableInfoToTable(tableInfo);
+        var databaseTableColumns = new HashSet<TableColumn>(databaseTable.Columns);
+        var wantedTableColumns = new HashSet<TableColumn>(wantedTable.Columns);
+
+        if (databaseTableColumns.IsSubsetOf(wantedTableColumns))
+        {
+            var addColumns = wantedTableColumns.Except(databaseTableColumns);
+            foreach (var column in addColumns)
+            {
+                if (tableInfo.GetColumn(column.Name) is not null)
+                {
+                    var columnInfo = tableInfo.GetColumn(column.Name);
+                    if (!columnInfo.CanBeGenerated)
+                    {
+                        return false;
+                    }
+                }
+
+            }
+            return true;
+        }
+        else
+        {
+            return false;
+        }
+    }
+
+    public string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns)
+    {
+        CheckTableName(tableName);
+
+        var columnSql = string.Join(",\n", columns.Select(GenerateCreateTableColumnSqlSegment));
+
+        var sql = $@"
+CREATE TABLE {tableName}(
+    {columnSql}
+);
+        ";
+
+        return sql;
+
+    }
+
+    public void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+    {
+        if (!CanAutoMigrate(dbConnection, tableInfo))
+        {
+            throw new Exception("The table can't be auto migrated.");
+        }
+
+        // We are sqlite, so it's a little bit difficult.
+        using var transaction = dbConnection.BeginTransaction();
+
+        var tableName = tableInfo.TableName;
+
+        var wantedTable = ConvertTableInfoToTable(tableInfo);
+        var wantedTableColumns = new HashSet<TableColumn>(wantedTable.Columns);
+
+        var exist = TableExists(dbConnection, tableName);
+        if (exist)
+        {
+            var databaseTable = GetTable(dbConnection, tableName);
+            var databaseTableColumns = new HashSet<TableColumn>(databaseTable.Columns);
+            var addColumns = wantedTableColumns.Except(databaseTableColumns);
+
+            var tempTableName = tableInfo.TableName + "_temp";
+            dbConnection.Execute($"ALTER TABLE {tableName} RENAME TO {tempTableName}", new { TableName = tableName, tempTableName });
+
+            var createTableSql = GenerateCreateTableSql(tableName, wantedTableColumns.ToList());
+            dbConnection.Execute(createTableSql);
+
+            // Copy old data to new table.
+            var originalRows = dbConnection.Query<dynamic>($"SELECT * FROM {tempTableName}").Cast<IDictionary<string, object?>>().ToList();
+            foreach (var originalRow in originalRows)
+            {
+                var parameters = new DynamicParameters();
+
+                var originalColumnNames = originalRow.Keys.ToList();
+                foreach (var columnName in originalColumnNames)
+                {
+                    parameters.Add(columnName, originalRow[columnName]);
+                }
+                var addColumnNames = addColumns.Select(c => c.Name).ToList();
+                foreach (var columnName in addColumnNames)
+                {
+                    parameters.Add(columnName, tableInfo.GetColumn(columnName).GenerateDefaultValue());
+                }
+
+                string columnSql = string.Join(", ", wantedTableColumns.Select(c => c.Name));
+                string valuesSql = string.Join(", ", wantedTableColumns.Select(c => "@" + c.Name));
+
+                string sql = $"INSERT INTO {tableName} ({columnSql}) VALUES {valuesSql})";
+                dbConnection.Execute(sql, parameters);
+            }
+
+            // Finally drop old table
+            dbConnection.Execute($"DROP TABLE {tempTableName}");
+        }
+        else
+        {
+            var createTableSql = GenerateCreateTableSql(tableName, wantedTableColumns.ToList());
+            dbConnection.Execute(createTableSql);
+        }
+
+        // Commit transaction.
+        transaction.Commit();
+    }
+
+    public string GenerateCreateTableColumnSqlSegment(TableColumn column)
+    {
+        StringBuilder result = new StringBuilder();
+        result.Append(column.Name);
+        result.Append(' ');
+        result.Append(column.Type);
+        if (column.PrimaryKey is not 0)
+        {
+            result.Append(" PRIMARY KEY AUTOINCREMENT");
+        }
+        else if (!column.IsNullable)
+        {
+            result.Append(" NOT NULL");
+        }
+
+        return result.ToString();
+    }
+
+    public bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+    {
+        if (!TableExists(dbConnection, tableInfo.TableName)) return true;
+
+        var tableName = tableInfo.TableName;
+        var databaseTable = GetTable(dbConnection, tableName);
+        var wantedTable = ConvertTableInfoToTable(tableInfo);
+        var databaseTableColumns = new HashSet<TableColumn>(databaseTable.Columns);
+        var wantedTableColumns = new HashSet<TableColumn>(wantedTable.Columns);
+        return databaseTableColumns != wantedTableColumns;
+    }
+
+    public bool TableExists(IDbConnection connection, string tableName)
+    {
+        var count = connection.QuerySingle<int>(
+            "SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
+            new { TableName = tableName });
+        if (count == 0)
+        {
+            return false;
+        }
+        else if (count > 1)
+        {
+            throw new Exception($"More than 1 table has name {tableName}. What happened?");
+        }
+        else
+        {
+            return true;
+        }
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
index 41ef097..4a7ea95 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -115,7 +115,6 @@ public class TableInfo
                     ColumnName = "Id",
                     NotNull = true,
                     IsPrimaryKey = true,
-                    IsAutoIncrement = true,
                 },
             typeof(long), _columnTypeProvider, _loggerFactory);
     }
@@ -231,26 +230,6 @@ CREATE TABLE {tableName}(
         return sql;
     }
 
-    public bool CheckExistence(IDbConnection connection)
-    {
-        var tableName = TableName;
-        var count = connection.QuerySingle<int>(
-            @"SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
-            new { TableName = tableName });
-        if (count == 0)
-        {
-            return false;
-        }
-        else if (count > 1)
-        {
-            throw new Exception($"More than 1 table has name {tableName}. What happened?");
-        }
-        else
-        {
-            return true;
-        }
-    }
-
     public void CheckColumnName(string columnName)
     {
         if (!ColumnNameList.Contains(columnName))
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
index 9a0ec95..b8a1bbe 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs
@@ -1,26 +1,32 @@
 using System.Data;
 using CrupestApi.Commons.Crud;
+using CrupestApi.Commons.Crud.Migrations;
 
 namespace CrupestApi.Commons.Secrets;
 
 public class SecretService : CrudService<SecretInfo>, ISecretService
 {
-    public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, ILoggerFactory loggerFactory)
-        : base(tableInfoFactory, dbConnectionFactory, loggerFactory)
-    {
+    private readonly ILogger<SecretService> _logger;
 
+    public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory)
+        : base(tableInfoFactory, dbConnectionFactory, migrator, loggerFactory)
+    {
+        _logger = loggerFactory.CreateLogger<SecretService>();
     }
 
-    protected override void DoInitializeDatabase(IDbConnection connection)
+    protected override void AfterMigrate(IDbConnection connection, TableInfo table, ILoggerFactory loggerFactory)
     {
-        base.DoInitializeDatabase(connection);
-        using var transaction = connection.BeginTransaction();
-        var insertClause = InsertClause.Create()
-            .Add(nameof(SecretInfo.Key), SecretsConstants.SecretManagementKey)
-            .Add(nameof(SecretInfo.Secret), "crupest")
-            .Add(nameof(SecretInfo.Description), "This is the init key. Please revoke it immediately after creating a new one.");
-        _table.Insert(connection, insertClause, out var _);
-        transaction.Commit();
+        if (table.SelectCount(connection) == 0)
+        {
+            loggerFactory.CreateLogger<SecretService>().LogInformation("No secrets found, insert default secrets.");
+            using var transaction = connection.BeginTransaction();
+            var insertClause = InsertClause.Create()
+                .Add(nameof(SecretInfo.Key), SecretsConstants.SecretManagementKey)
+                .Add(nameof(SecretInfo.Secret), "crupest")
+                .Add(nameof(SecretInfo.Description), "This is the init key. Please revoke it immediately after creating a new one.");
+            _table.Insert(connection, insertClause, out var _);
+            transaction.Commit();
+        }
     }
 
     public void CreateTestSecret(string key, string secret)