diff options
author | crupest <crupest@outlook.com> | 2022-12-25 14:52:46 +0800 |
---|---|---|
committer | crupest <crupest@outlook.com> | 2022-12-25 14:53:58 +0800 |
commit | c8e6e2081b6d1a1b1f4b7ddd8923e2af70f82e29 (patch) | |
tree | 0004822a2ea7a987176e28396c7cc74cb61ef692 /docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs | |
parent | 71bda510363822defa74760d93947ff33a8775f0 (diff) | |
download | crupest-c8e6e2081b6d1a1b1f4b7ddd8923e2af70f82e29.tar.gz crupest-c8e6e2081b6d1a1b1f4b7ddd8923e2af70f82e29.tar.bz2 crupest-c8e6e2081b6d1a1b1f4b7ddd8923e2af70f82e29.zip |
Add migration.
Diffstat (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs')
-rw-r--r-- | docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/SqliteDatabaseMigrator.cs | 212 |
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; + } + } +} |