diff options
Diffstat (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations')
2 files changed, 0 insertions, 219 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 deleted file mode 100644 index f1ae616..0000000 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs +++ /dev/null @@ -1,44 +0,0 @@ -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/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs deleted file mode 100644 index 33310d6..0000000 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs +++ /dev/null @@ -1,175 +0,0 @@ -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); - } -} |