diff options
Diffstat (limited to 'dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations')
2 files changed, 219 insertions, 0 deletions
diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs new file mode 100644 index 0000000..f1ae616 --- /dev/null +++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs @@ -0,0 +1,44 @@ +using System.Data; + +namespace CrupestApi.Commons.Crud.Migrations; + +public class TableColumn +{ + public TableColumn(string name, string type, bool notNull, int primaryKey) + { + Name = name; + Type = type; + NotNull = notNull; + PrimaryKey = primaryKey; + } + + public string Name { get; set; } + public string Type { 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; } +} + +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 tableName); + Table ConvertTableInfoToTable(TableInfo tableInfo); + string GenerateCreateTableColumnSqlSegment(TableColumn column); + string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns); + bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo); + void AutoMigrate(IDbConnection dbConnection, TableInfo tableInfo); +} diff --git a/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs new file mode 100644 index 0000000..33310d6 --- /dev/null +++ b/dropped/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs @@ -0,0 +1,175 @@ +using System.Data; +using System.Text; +using System.Text.Json; +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 tableName) + { + var count = dbConnection.QuerySingle<int>( + "SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND 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 + { + 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) + { + 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 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.NotNull) + { + result.Append(" NOT NULL"); + } + + return result.ToString(); + } + + public string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns) + { + CheckTableName(tableName); + + var sql = $@" +CREATE TABLE {tableName} ( + {string.Join(",\n ", columns.Select(GenerateCreateTableColumnSqlSegment))} +); + ".Trim(); + + return sql; + + } + + public void AutoMigrate(IDbConnection dbConnection, TableInfo 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.QuerySingle<int>($"SELECT count(*) FROM {tableName}") > 0) + { + 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(); + + if (databaseTable is not null) + { + var tempTableName = tableInfo.TableName + "_temp"; + dbConnection.Execute($"ALTER TABLE {tableName} RENAME TO {tempTableName}", new { TableName = tableName, tempTableName }); + + var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns); + 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(); + + foreach (var columnName in notChangeColumns) + { + parameters.Add(columnName, originalRow[columnName]); + } + + foreach (var columnName in addColumns) + { + parameters.Add(columnName, tableInfo.GetColumn(columnName).GenerateDefaultValue()); + } + + 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); + } + + // Finally drop old table + dbConnection.Execute($"DROP TABLE {tempTableName}"); + } + else + { + var createTableSql = GenerateCreateTableSql(tableName, wantedTable.Columns); + dbConnection.Execute(createTableSql); + } + + // Commit transaction. + transaction.Commit(); + } + + public bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo) + { + var tableName = tableInfo.TableName; + var databaseTable = GetTable(dbConnection, tableName); + var wantedTable = ConvertTableInfoToTable(tableInfo); + 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); + } +} |