1 files changed, 212 insertions, 0 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
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;
+        }
+    }
+}