diff options
10 files changed, 326 insertions, 53 deletions
diff --git a/cspell.yaml b/cspell.yaml index 7ebeb5b..b9a2fc9 100644 --- a/cspell.yaml +++ b/cspell.yaml @@ -16,4 +16,5 @@ words: - ASPNETCORE - autoincrement - crupest + - Fxxk - todos diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs index 284dbe2..ad0d34c 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons.Tests/Crud/CrudServiceTest.cs @@ -1,3 +1,4 @@ +using CrupestApi.Commons.Crud.Migrations; using Microsoft.Extensions.Logging.Abstractions; namespace CrupestApi.Commons.Crud.Tests; @@ -15,7 +16,7 @@ public class CrudServiceTest var dbConnectionFactory = new SqliteMemoryConnectionFactory(); _crudService = new CrudService<TestEntity>( - tableInfoFactory, dbConnectionFactory, NullLoggerFactory.Instance); + tableInfoFactory, dbConnectionFactory, new SqliteDatabaseMigrator(), NullLoggerFactory.Instance); } [Fact] diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs index 30b75af..13d9d6d 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs @@ -58,7 +58,7 @@ public class ColumnInfo public IColumnTypeInfo ColumnType { get; } public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true; - public bool IsAutoIncrement => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsAutoIncrement) is true; + public bool IsAutoIncrement => IsPrimaryKey; public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true; public bool IsOnlyGenerated => Metadata.GetValueOrDefault(ColumnMetadataKeys.OnlyGenerated) is true; public bool IsNoUpdate => Metadata.GetValueOrDefault(ColumnMetadataKeys.NoUpdate) is true; diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs index 0ee2837..7247ff1 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs @@ -5,7 +5,6 @@ public static class ColumnMetadataKeys public const string ColumnName = nameof(ColumnAttribute.ColumnName); public const string NotNull = nameof(ColumnAttribute.NotNull); public const string IsPrimaryKey = nameof(ColumnAttribute.IsPrimaryKey); - public const string IsAutoIncrement = nameof(ColumnAttribute.IsAutoIncrement); public const string Index = nameof(ColumnAttribute.Index); /// <summary> @@ -104,9 +103,6 @@ public class ColumnAttribute : Attribute, IColumnMetadata // default false public bool IsPrimaryKey { get; init; } - // default false - public bool IsAutoIncrement { get; init; } - // default None public ColumnIndexType Index { get; init; } = ColumnIndexType.None; diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs index a56790a..5e00b28 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs @@ -1,5 +1,5 @@ using System.Data; -using Dapper; +using CrupestApi.Commons.Crud.Migrations; namespace CrupestApi.Commons.Crud; @@ -16,17 +16,37 @@ public class CrudService<TEntity> : IDisposable where TEntity : class protected readonly string? _connectionName; protected readonly IDbConnection _dbConnection; private readonly bool _shouldDisposeConnection; + private IDatabaseMigrator _migrator; private readonly ILogger<CrudService<TEntity>> _logger; - public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, ILoggerFactory loggerFactory) + public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory) { _connectionName = GetConnectionName(); _table = tableInfoFactory.Get(typeof(TEntity)); _dbConnection = dbConnectionFactory.Get(_connectionName); _shouldDisposeConnection = dbConnectionFactory.ShouldDisposeConnection; + _migrator = migrator; _logger = loggerFactory.CreateLogger<CrudService<TEntity>>(); - CheckDatabase(_dbConnection); + 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."); + } } protected virtual string GetConnectionName() @@ -34,19 +54,9 @@ public class CrudService<TEntity> : IDisposable where TEntity : class return typeof(TEntity).Name; } - protected virtual void CheckDatabase(IDbConnection dbConnection) + protected virtual void AfterMigrate(IDbConnection dbConnection, TableInfo tableInfo, ILoggerFactory loggerFactory) { - if (!_table.CheckExistence(dbConnection)) - { - DoInitializeDatabase(dbConnection); - } - } - protected virtual void DoInitializeDatabase(IDbConnection connection) - { - using var transaction = connection.BeginTransaction(); - connection.Execute(_table.GenerateCreateTableSql(), transaction: transaction); - transaction.Commit(); } public void Dispose() diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs index e9f28bc..a7e5193 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs @@ -1,3 +1,4 @@ +using CrupestApi.Commons.Crud.Migrations; using CrupestApi.Commons.Secrets; using Microsoft.Extensions.DependencyInjection.Extensions; @@ -10,6 +11,7 @@ public static class CrudServiceCollectionExtensions services.TryAddSingleton<IDbConnectionFactory, SqliteConnectionFactory>(); services.TryAddSingleton<IColumnTypeProvider, ColumnTypeProvider>(); services.TryAddSingleton<ITableInfoFactory, TableInfoFactory>(); + services.TryAddSingleton<IDatabaseMigrator, SqliteDatabaseMigrator>(); services.AddSecrets(); return services; } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs new file mode 100644 index 0000000..3d59c21 --- /dev/null +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/Migrations/DatabaseMigrator.cs @@ -0,0 +1,66 @@ +using System.Data; + +namespace CrupestApi.Commons.Crud.Migrations; + +public class TableColumn : IEquatable<TableColumn> +{ + public TableColumn(string name, string type, bool isNullable, int primaryKey) + { + Name = name.ToLowerInvariant(); + Type = type.ToLowerInvariant(); + IsNullable = isNullable; + PrimaryKey = primaryKey; + } + + public string Name { get; set; } + public string Type { get; set; } + public bool IsNullable { 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 +{ + 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 name); + 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 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; + } + } +} diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs index 41ef097..4a7ea95 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs @@ -115,7 +115,6 @@ public class TableInfo ColumnName = "Id", NotNull = true, IsPrimaryKey = true, - IsAutoIncrement = true, }, typeof(long), _columnTypeProvider, _loggerFactory); } @@ -231,26 +230,6 @@ CREATE TABLE {tableName}( return sql; } - public bool CheckExistence(IDbConnection connection) - { - var tableName = 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; - } - } - public void CheckColumnName(string columnName) { if (!ColumnNameList.Contains(columnName)) diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs index 9a0ec95..b8a1bbe 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Secrets/SecretService.cs @@ -1,26 +1,32 @@ using System.Data; using CrupestApi.Commons.Crud; +using CrupestApi.Commons.Crud.Migrations; namespace CrupestApi.Commons.Secrets; public class SecretService : CrudService<SecretInfo>, ISecretService { - public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, ILoggerFactory loggerFactory) - : base(tableInfoFactory, dbConnectionFactory, loggerFactory) - { + private readonly ILogger<SecretService> _logger; + public SecretService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IDatabaseMigrator migrator, ILoggerFactory loggerFactory) + : base(tableInfoFactory, dbConnectionFactory, migrator, loggerFactory) + { + _logger = loggerFactory.CreateLogger<SecretService>(); } - protected override void DoInitializeDatabase(IDbConnection connection) + protected override void AfterMigrate(IDbConnection connection, TableInfo table, ILoggerFactory loggerFactory) { - base.DoInitializeDatabase(connection); - using var transaction = connection.BeginTransaction(); - var insertClause = InsertClause.Create() - .Add(nameof(SecretInfo.Key), SecretsConstants.SecretManagementKey) - .Add(nameof(SecretInfo.Secret), "crupest") - .Add(nameof(SecretInfo.Description), "This is the init key. Please revoke it immediately after creating a new one."); - _table.Insert(connection, insertClause, out var _); - transaction.Commit(); + if (table.SelectCount(connection) == 0) + { + loggerFactory.CreateLogger<SecretService>().LogInformation("No secrets found, insert default secrets."); + using var transaction = connection.BeginTransaction(); + var insertClause = InsertClause.Create() + .Add(nameof(SecretInfo.Key), SecretsConstants.SecretManagementKey) + .Add(nameof(SecretInfo.Secret), "crupest") + .Add(nameof(SecretInfo.Description), "This is the init key. Please revoke it immediately after creating a new one."); + _table.Insert(connection, insertClause, out var _); + transaction.Commit(); + } } public void CreateTestSecret(string key, string secret) |