diff options
Diffstat (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud')
4 files changed, 147 insertions, 146 deletions
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs index 13d9d6d..e8d3c2e 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs @@ -222,7 +222,7 @@ public class ColumnInfo else if (IsNotNull) { result.Append(' '); - result.Append(" NOT NULL"); + result.Append("NOT NULL"); } if (IsAutoIncrement) diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs index 5e00b28..1e881d3 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs @@ -27,25 +27,14 @@ public class CrudService<TEntity> : IDisposable where TEntity : class _shouldDisposeConnection = dbConnectionFactory.ShouldDisposeConnection; _migrator = migrator; _logger = loggerFactory.CreateLogger<CrudService<TEntity>>(); + } - if (migrator.NeedMigrate(_dbConnection, _table)) + protected virtual void EnsureDatabase() + { + if (_migrator.NeedMigrate(_dbConnection, _table)) { _logger.LogInformation($"Entity {_table.TableName} needs migration."); - if (migrator.CanAutoMigrate(_dbConnection, _table)) - { - _logger.LogInformation($"Entity {_table.TableName} can be auto migrated."); - migrator.AutoMigrate(_dbConnection, _table); - AfterMigrate(_dbConnection, _table, loggerFactory); - } - else - { - _logger.LogInformation($"Entity {_table.TableName} can not be auto migrated."); - throw new Exception($"Entity {_table.TableName} needs migration but can not be auto migrated."); - } - } - else - { - _logger.LogInformation($"Entity {_table.TableName} does not need migration."); + _migrator.AutoMigrate(_dbConnection, _table); } } @@ -54,7 +43,7 @@ public class CrudService<TEntity> : IDisposable where TEntity : class return typeof(TEntity).Name; } - protected virtual void AfterMigrate(IDbConnection dbConnection, TableInfo tableInfo, ILoggerFactory loggerFactory) + protected virtual void AfterMigrate(IDbConnection dbConnection, TableInfo tableInfo) { } @@ -67,18 +56,21 @@ public class CrudService<TEntity> : IDisposable where TEntity : class public List<TEntity> GetAll() { + EnsureDatabase(); var result = _table.Select<TEntity>(_dbConnection, null); return result; } public int GetCount() { + EnsureDatabase(); var result = _table.SelectCount(_dbConnection); return result; } public TEntity GetByKey(object key) { + EnsureDatabase(); var result = _table.Select<TEntity>(_dbConnection, null, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key)).SingleOrDefault(); if (result is null) { @@ -100,6 +92,7 @@ public class CrudService<TEntity> : IDisposable where TEntity : class public object Create(TEntity entity) { + EnsureDatabase(); var insertClause = ConvertEntityToInsertClauses(entity); _table.Insert(_dbConnection, insertClause, out var key); return key; @@ -121,6 +114,7 @@ public class CrudService<TEntity> : IDisposable where TEntity : class // Return new key. public object UpdateByKey(object key, TEntity entity, UpdateBehavior behavior = UpdateBehavior.None) { + EnsureDatabase(); var affectedCount = _table.Update(_dbConnection, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key), ConvertEntityToUpdateClauses(entity, behavior), out var newKey); if (affectedCount == 0) @@ -132,6 +126,7 @@ public class CrudService<TEntity> : IDisposable where TEntity : class public bool DeleteByKey(object key) { + EnsureDatabase(); return _table.Delete(_dbConnection, WhereClause.Create().Eq(_table.KeyColumn.ColumnName, key)) == 1; } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs index 3d59c21..e5ef05d 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs @@ -2,44 +2,24 @@ using System.Data; namespace CrupestApi.Commons.Crud.Migrations; -public class TableColumn : IEquatable<TableColumn> +public class TableColumn { - public TableColumn(string name, string type, bool isNullable, int primaryKey) + public TableColumn(string name, string type, bool notNull, int primaryKey) { Name = name.ToLowerInvariant(); Type = type.ToLowerInvariant(); - IsNullable = isNullable; + NotNull = notNull; PrimaryKey = primaryKey; } public string Name { get; set; } public string Type { get; set; } - public bool IsNullable { 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; } - - bool IEquatable<TableColumn>.Equals(TableColumn? other) - { - if (other is null) - { - return false; - } - - return Name == other.Name && Type == other.Type && IsNullable == other.IsNullable && PrimaryKey == other.PrimaryKey; - } - - public override bool Equals(object? obj) - { - return Equals(obj as TableColumn); - } - - public override int GetHashCode() - { - return HashCode.Combine(Name, Type, IsNullable, PrimaryKey); - } } public class Table @@ -53,14 +33,21 @@ public class Table public List<TableColumn> Columns { get; set; } = new List<TableColumn>(); } +public class MigrationRecord +{ + public string TableName { get; set; } = default!; + public int Version { get; set; } + public Table Structure { get; set; } = default!; +} + public interface IDatabaseMigrator { - Table GetTable(IDbConnection dbConnection, string name); + List<MigrationRecord> GetRecords(IDbConnection dbConnection, string tableName); + + Table? GetTable(IDbConnection dbConnection, string tableName); Table ConvertTableInfoToTable(TableInfo tableInfo); string GenerateCreateTableColumnSqlSegment(TableColumn column); string GenerateCreateTableSql(string tableName, IEnumerable<TableColumn> columns); - bool TableExists(IDbConnection connection, string tableName); bool NeedMigrate(IDbConnection dbConnection, TableInfo tableInfo); - bool CanAutoMigrate(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 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); } } |