Add migration. v2.0

2 files changed, 134 insertions, 128 deletions

diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
index 3d59c21..e5ef05d 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs
@@ -2,44 +2,24 @@ using System.Data;
 
 namespace CrupestApi.Commons.Crud.Migrations;
 
-public class TableColumn : IEquatable<TableColumn>
+public class TableColumn
 {
-    public TableColumn(string name, string type, bool isNullable, int primaryKey)
+    public TableColumn(string name, string type, bool notNull, int primaryKey)
     {
         Name = name.ToLowerInvariant();
         Type = type.ToLowerInvariant();
-        IsNullable = isNullable;
+        NotNull = notNull;
         PrimaryKey = primaryKey;
     }
 
     public string Name { get; set; }
     public string Type { get; set; }
-    public bool IsNullable { get; set; }
+    public bool NotNull { 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
@@ -53,14 +33,21 @@ public class Table
     public List<TableColumn> Columns { get; set; } = new List<TableColumn>();
 }
 
+public class MigrationRecord
+{
+    public string TableName { get; set; } = default!;
+    public int Version { get; set; }
+    public Table Structure { get; set; } = default!;
+}
+
 public interface IDatabaseMigrator
 {
-    Table GetTable(IDbConnection dbConnection, string name);
+    List<MigrationRecord> GetRecords(IDbConnection dbConnection, string tableName);
+
+    Table? GetTable(IDbConnection dbConnection, string tableName);
     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
index 762e95d..83b360b 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs
@@ -1,5 +1,6 @@
 using System.Data;
 using System.Text;
+using System.Text.Json;
 using System.Text.RegularExpressions;
 using Dapper;
 
@@ -15,24 +16,90 @@ public class SqliteDatabaseMigrator : IDatabaseMigrator
         }
     }
 
-    public Table GetTable(IDbConnection dbConnection, string name)
+    private const string MigrationHistoryTableName = "migration_history";
+
+    private class MigrationRecordEntity
     {
-        CheckTableName(name);
+        public string TableName { get; set; } = string.Empty;
+        public int Version { get; set; }
+        public string Structure { get; set; } = string.Empty;
+    }
 
-        var table = new Table(name);
-        var queryColumns = dbConnection.Query<dynamic>($"PRAGMA table_info({name})");
+    private void EnsureHistoryDatabase(IDbConnection dbConnection)
+    {
+        var exist = dbConnection.Query<int>($"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{MigrationHistoryTableName}';").Single() == 1;
+        if (!exist)
+        {
+            dbConnection.Execute($@"
+                CREATE TABLE {MigrationHistoryTableName} (
+                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
+                    TableName TEXT NOT NULL,
+                    Version INT NOT NULL,
+                    Structure TEXT NOT NULL
+                );
+            ");
+        }
+    }
 
-        foreach (var column in queryColumns)
+    public List<MigrationRecord> GetRecords(IDbConnection dbConnection, string tableName)
+    {
+        CheckTableName(tableName);
+        EnsureHistoryDatabase(dbConnection);
+
+        var recordEntities = dbConnection.Query<MigrationRecordEntity>(
+            $"SELECT * FROM {MigrationHistoryTableName} WHERE TableName = @TableName ORDER BY Version ASC;",
+            new { TableName = tableName }
+        ).ToList();
+
+        var records = recordEntities.Select(entity =>
         {
-            var columnName = (string)column.name;
-            var columnType = (string)column.type;
-            var isNullable = (bool)column.notnull;
-            var primaryKey = (long)column.pk;
+            var structure = JsonSerializer.Deserialize<Table>(entity.Structure);
+            if (structure is null) throw new Exception("Migration record is corrupted. Failed to convert structure.");
+            return new MigrationRecord
+            {
+                TableName = entity.TableName,
+                Version = entity.Version,
+                Structure = structure
+            };
+        }).ToList();
+
+        return records;
+    }
 
-            table.Columns.Add(new TableColumn(columnName, columnType, isNullable, (int)primaryKey));
+
+    public Table? GetTable(IDbConnection dbConnection, string tableName)
+    {
+        CheckTableName(tableName);
+
+        var count = dbConnection.QuerySingle<int>(
+            "SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
+            new { TableName = tableName });
+        if (count == 0)
+        {
+            return null;
+        }
+        else if (count > 1)
+        {
+            throw new Exception($"More than 1 table has name {tableName}. What happened?");
         }
+        else
+        {
 
-        return table;
+            var table = new Table(tableName);
+            var queryColumns = dbConnection.Query<dynamic>($"PRAGMA table_info({tableName})");
+
+            foreach (var column in queryColumns)
+            {
+                var columnName = (string)column.name;
+                var columnType = (string)column.type;
+                var isNullable = Convert.ToBoolean(column.notnull);
+                var primaryKey = Convert.ToInt32(column.pk);
+
+                table.Columns.Add(new TableColumn(columnName, columnType, isNullable, primaryKey));
+            }
+
+            return table;
+        }
     }
 
     public Table ConvertTableInfoToTable(TableInfo tableInfo)
@@ -42,42 +109,28 @@ public class SqliteDatabaseMigrator : IDatabaseMigrator
         foreach (var columnInfo in tableInfo.Columns)
         {
             table.Columns.Add(new TableColumn(columnInfo.ColumnName, columnInfo.ColumnType.GetSqlTypeString(),
-                !columnInfo.IsNotNull, columnInfo.IsPrimaryKey ? 1 : 0));
+                columnInfo.IsNotNull, columnInfo.IsPrimaryKey ? 1 : 0));
         }
 
         return table;
     }
 
-    public bool CanAutoMigrate(IDbConnection dbConnection, TableInfo tableInfo)
+    public string GenerateCreateTableColumnSqlSegment(TableColumn column)
     {
-        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))
+        StringBuilder result = new StringBuilder();
+        result.Append(column.Name);
+        result.Append(' ');
+        result.Append(column.Type);
+        if (column.PrimaryKey is not 0)
         {
-            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;
+            result.Append(" PRIMARY KEY AUTOINCREMENT");
         }
-        else
+        else if (column.NotNull)
         {
-            return false;
+            result.Append(" NOT NULL");
         }
+
+        return result.ToString();
     }
 
     public string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns)
@@ -87,7 +140,7 @@ public class SqliteDatabaseMigrator : IDatabaseMigrator
         var columnSql = string.Join(",\n", columns.Select(GenerateCreateTableColumnSqlSegment));
 
         var sql = $@"
-CREATE TABLE {tableName}(
+CREATE TABLE {tableName} (
     {columnSql}
 );
         ";
@@ -98,30 +151,36 @@ CREATE TABLE {tableName}(
 
     public void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo)
     {
-        if (!CanAutoMigrate(dbConnection, tableInfo))
+        var tableName = tableInfo.TableName;
+        var databaseTable = GetTable(dbConnection, tableName);
+        var wantedTable = ConvertTableInfoToTable(tableInfo);
+        var databaseTableColumnNames = databaseTable is null ? new List<string>() : databaseTable.Columns.Select(column => column.Name).ToList();
+        var wantedTableColumnNames = wantedTable.Columns.Select(column => column.Name).ToList();
+
+        var notChangeColumns = wantedTableColumnNames.Where(column => databaseTableColumnNames.Contains(column)).ToList();
+        var addColumns = wantedTableColumnNames.Where(column => !databaseTableColumnNames.Contains(column)).ToList();
+
+        if (databaseTable is not null && dbConnection.Query<int>($"SELECT count(*) FROM {tableName}").Single() > 0)
         {
-            throw new Exception("The table can't be auto migrated.");
+            foreach (var columnName in addColumns)
+            {
+                var columnInfo = tableInfo.GetColumn(columnName);
+                if (!columnInfo.CanBeGenerated)
+                {
+                    throw new Exception($"Column {columnName} cannot be generated. So we can't auto-migrate.");
+                }
+            }
         }
 
         // 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)
+        if (databaseTable is not null)
         {
-            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());
+            var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns);
             dbConnection.Execute(createTableSql);
 
             // Copy old data to new table.
@@ -130,19 +189,18 @@ CREATE TABLE {tableName}(
             {
                 var parameters = new DynamicParameters();
 
-                var originalColumnNames = originalRow.Keys.ToList();
-                foreach (var columnName in originalColumnNames)
+                foreach (var columnName in notChangeColumns)
                 {
                     parameters.Add(columnName, originalRow[columnName]);
                 }
-                var addColumnNames = addColumns.Select(c => c.Name).ToList();
-                foreach (var columnName in addColumnNames)
+
+                foreach (var columnName in addColumns)
                 {
                     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 columnSql = string.Join(", ", wantedTableColumnNames);
+                string valuesSql = string.Join(", ", wantedTableColumnNames.Select(c => "@" + c));
 
                 string sql = $"INSERT INTO {tableName} ({columnSql}) VALUES {valuesSql})";
                 dbConnection.Execute(sql, parameters);
@@ -153,7 +211,7 @@ CREATE TABLE {tableName}(
         }
         else
         {
-            var createTableSql = GenerateCreateTableSql(tableName, wantedTableColumns.ToList());
+            var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns);
             dbConnection.Execute(createTableSql);
         }
 
@@ -161,52 +219,13 @@ CREATE TABLE {tableName}(
         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;
-        }
+        var databaseTableColumns = databaseTable is null ? new HashSet<string>() : new HashSet<string>(databaseTable.Columns.Select(c => c.Name));
+        var wantedTableColumns = new HashSet<string>(wantedTable.Columns.Select(c => c.Name));
+        return !databaseTableColumns.SetEquals(wantedTableColumns);
     }
 }