Add migration. v2.0

1 files changed, 120 insertions, 101 deletions

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);
     }
 }