diff options
9 files changed, 227 insertions, 137 deletions
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs index c3d118c..1329c99 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs @@ -1,8 +1,12 @@ +using System.Data; using System.Reflection; using System.Text; namespace CrupestApi.Commons.Crud; +public delegate Task EntityPreSave(object? entity, ColumnInfo column, TableInfo table, IDbConnection connection); +public delegate Task EntityPostGet(object? entity, ColumnInfo column, TableInfo table, IDbConnection connection); + public class ColumnInfo { private Type ExtractRealTypeFromNullable(Type type) @@ -24,7 +28,7 @@ public class ColumnInfo } EntityType = entityType; - PropertyName = null; + PropertyName = sqlColumnName; PropertyType = typeof(int); PropertyRealType = typeof(int); SqlColumnName = sqlColumnName; @@ -45,36 +49,55 @@ public class ColumnInfo EntityType = entityType; PropertyName = entityPropertyName; + PropertyInfo = entityType.GetProperty(entityPropertyName); - var property = entityType.GetProperty(entityPropertyName); - - if (property is null) + if (PropertyInfo is null) throw new Exception("Public property with given name does not exist."); - PropertyType = property.PropertyType; + PropertyType = PropertyInfo.PropertyType; PropertyRealType = ExtractRealTypeFromNullable(PropertyType); - var columnAttribute = property.GetCustomAttribute<ColumnAttribute>(); + var columnAttribute = PropertyInfo.GetCustomAttribute<ColumnAttribute>(); if (columnAttribute is null) { SqlColumnName = PropertyName; Nullable = true; IndexType = ColumnIndexType.None; + DefaultEmptyForString = false; } else { SqlColumnName = columnAttribute.DatabaseName ?? PropertyName; Nullable = !columnAttribute.NonNullable; IndexType = columnAttribute.IndexType; + DefaultEmptyForString = columnAttribute.DefaultEmptyForString; } ColumnTypeInfo = typeRegistry.GetRequiredByDataType(PropertyRealType); TypeRegistry = typeRegistry; + + if (DefaultEmptyForString) + { + EntityPostGet += (entity, column, _, _) => + { + var pi = column.PropertyInfo; + if (pi is not null && column.ColumnTypeInfo.GetDatabaseType() == typeof(string)) + { + var value = pi.GetValue(entity); + if (value is null) + { + pi.SetValue(entity, string.Empty); + } + } + return Task.CompletedTask; + }; + } } public Type EntityType { get; } // If null, there is no corresponding property. - public string? PropertyName { get; } + public PropertyInfo? PropertyInfo { get; } = null; + public string PropertyName { get; } public Type PropertyType { get; } public Type PropertyRealType { get; } public string SqlColumnName { get; } @@ -84,6 +107,10 @@ public class ColumnInfo public bool IsPrimaryKey { get; } public bool IsAutoIncrement { get; } public ColumnIndexType IndexType { get; } + public bool DefaultEmptyForString { get; } + + public event EntityPreSave? EntityPreSave; + public event EntityPostGet? EntityPostGet; public string SqlType => TypeRegistry.GetSqlType(ColumnTypeInfo); diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs index 6f46cd5..c31a13e 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs @@ -28,4 +28,7 @@ public class ColumnAttribute : Attribute, IColumnMetadata public bool IsAutoIncrement { get; set; } public ColumnIndexType IndexType { get; set; } = ColumnIndexType.None; + + // Use empty string for default value of string type. + public bool DefaultEmptyForString { get; set; } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs index ff8ccea..d7adb33 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs @@ -1,9 +1,16 @@ using System.Diagnostics; +using System.Text.Json; +using System.Text.Json.Serialization; namespace CrupestApi.Commons.Crud; public interface IColumnTypeInfo { + JsonConverter? GetOptionalJsonConverter() + { + return null; + } + Type GetDataType(); Type GetDatabaseType(); object ConvertToDatabase(object data); @@ -122,6 +129,46 @@ public abstract class CustomColumnTypeInfo<TDataType, TDatabaseType> : ICustomCo } } +public class DateTimeColumnTypeInfo : CustomColumnTypeInfo<DateTime, long> +{ + private readonly DateTimeJsonConverter _jsonConverter = new DateTimeJsonConverter(); + + public JsonConverter GetJsonConverter() + { + return _jsonConverter; + } + + public override long ConvertToDatabase(DateTime data) + { + return new DateTimeOffset(data).ToUnixTimeSeconds(); + } + + public override DateTime ConvertFromDatabase(long databaseData) + { + return DateTimeOffset.FromUnixTimeSeconds(databaseData).LocalDateTime; + } +} + +public class DateTimeJsonConverter : JsonConverter<DateTime> +{ + public override bool HandleNull => false; + + public override bool CanConvert(Type typeToConvert) + { + return typeToConvert == typeof(DateTime); + } + + public override DateTime Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options) + { + return DateTimeOffset.FromUnixTimeSeconds(reader.GetInt64()).LocalDateTime; + } + + public override void Write(Utf8JsonWriter writer, DateTime value, JsonSerializerOptions options) + { + writer.WriteNumberValue(new DateTimeOffset(value).ToUnixTimeSeconds()); + } +} + public class ColumnTypeInfoRegistry { public static IReadOnlyList<IColumnTypeInfo> BuiltinList = new List<IColumnTypeInfo>() @@ -139,7 +186,7 @@ public class ColumnTypeInfoRegistry public static IReadOnlyList<IColumnTypeInfo> CustomList = new List<IColumnTypeInfo>() { - // TODO: Add custom ones. + new DateTimeColumnTypeInfo(), }; public static ColumnTypeInfoRegistry Singleton { get; } @@ -229,4 +276,13 @@ public class ColumnTypeInfoRegistry } } + public IEnumerable<JsonConverter> GetJsonConverters() + { + foreach (var columnTypeInfo in _list) + { + var converter = columnTypeInfo.GetOptionalJsonConverter(); + if (converter is not null) + yield return converter; + } + } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs index bbd5e9a..7da6ac7 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs @@ -8,13 +8,13 @@ public class CrudService<TEntity> { protected readonly TableInfo _table; protected readonly IOptionsSnapshot<CrupestApiConfig> _crupestApiOptions; - protected readonly ILogger<CrudService<TEntity>> _logger; + private readonly ILogger<CrudService<TEntity>> _logger; - public CrudService(IOptionsSnapshot<CrupestApiConfig> crupestApiOptions, ILogger<CrudService<TEntity>> logger) + public CrudService(ServiceProvider services) { _table = new TableInfo(typeof(TEntity)); - _crupestApiOptions = crupestApiOptions; - _logger = logger; + _crupestApiOptions = services.GetRequiredService<IOptionsSnapshot<CrupestApiConfig>>(); + _logger = services.GetRequiredService<ILogger<CrudService<TEntity>>>(); } public virtual string GetDbConnectionString() @@ -78,4 +78,12 @@ public class CrudService<TEntity> var sql = _table.GenerateUpdateSql(where, update, out parameters); return await connection.ExecuteAsync(sql, parameters); } + + public virtual async Task<int> DeleteAsync(WhereClause? where) + { + var connection = await EnsureDatabase(); + DynamicParameters parameters; + var sql = _table.GenerateDeleteSql(where, out parameters); + return await connection.ExecuteAsync(sql, parameters); + } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs index 9610e40..c58897c 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs @@ -190,6 +190,46 @@ CREATE TABLE {tableName}( return result.ToString(); } + public InsertClause GenerateInsertClauseFromObject(object value) + { + var insertClause = InsertClause.Create(); + + foreach (var column in ColumnInfos) + { + var propertyInfo = column.PropertyInfo; + if (propertyInfo is null) + { + propertyInfo = EntityType.GetProperty(column.PropertyName); + } + if (propertyInfo is null) + { + if (column.IsAutoIncrement) + { + continue; + } + else + { + throw new Exception($"Property {column.PropertyName} not found."); + } + } + + var propertyValue = propertyInfo.GetValue(value); + if (propertyValue is null) + { + if (column.IsAutoIncrement) + { + continue; + } + else + { + insertClause.Add(column.SqlColumnName, propertyValue); + } + } + } + + return insertClause; + } + public string GenerateInsertSql(InsertClause insertClause, out DynamicParameters parameters) { var relatedColumns = insertClause.GetRelatedColumns(); @@ -244,4 +284,32 @@ CREATE TABLE {tableName}( return sb.ToString(); } -}
\ No newline at end of file + + public string GenerateDeleteSql(WhereClause? whereClause, out DynamicParameters parameters) + { + if (whereClause is not null) + { + var relatedColumns = ((IWhereClause)whereClause).GetRelatedColumns() ?? new List<string>(); + foreach (var column in relatedColumns) + { + if (!ColumnNameList.Contains(column)) + { + throw new ArgumentException($"Column {column} is not in the table."); + } + } + } + + parameters = new DynamicParameters(); + + StringBuilder sb = new StringBuilder("DELETE FROM "); + sb.Append(TableName); + if (whereClause is not null) + { + sb.Append(" WHERE "); + sb.Append(whereClause.GenerateSql(parameters)); + } + sb.Append(';'); + + return sb.ToString(); + } +} diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs index 674ac9d..2352e7e 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs @@ -213,6 +213,11 @@ public class WhereClause : IWhereClause return new WhereClause(clauses); } + public WhereClause Add(string column, string op, object value) + { + return Add(CompareWhereClause.Create(column, op, value)); + } + public WhereClause Eq(string column, object value) { return Add(CompareWhereClause.Eq(column, value)); diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Json.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Json.cs index 1d8106c..bbb6efe 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Json.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Json.cs @@ -1,4 +1,5 @@ using System.Text.Json; +using CrupestApi.Commons.Crud; using Microsoft.Extensions.Options; namespace CrupestApi.Commons; @@ -13,6 +14,10 @@ public static class CrupestApiJsonExtensions config.AllowTrailingCommas = true; config.PropertyNameCaseInsensitive = true; config.PropertyNamingPolicy = JsonNamingPolicy.CamelCase; + foreach (var converter in ColumnTypeInfoRegistry.Singleton.GetJsonConverters()) + { + config.Converters.Add(converter); + } }); return services; diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretInfo.cs index 0fe95cb..01aead7 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretInfo.cs @@ -1,21 +1,17 @@ +using CrupestApi.Commons.Crud; + namespace CrupestApi.Secrets; public class SecretInfo { - public SecretInfo(string key, string secret, string description, DateTime? expireTime, bool revoked, DateTime createdTime) - { - Key = key; - Secret = secret; - Description = description; - ExpireTime = expireTime?.ToString("O"); - Revoked = revoked; - CreateTime = createdTime.ToString("O"); - } - - public string Key { get; set; } - public string Secret { get; set; } - public string Description { get; set; } - public string? ExpireTime { get; set; } + [Column(NonNullable = true)] + public string Key { get; set; } = default!; + [Column(NonNullable = true)] + public string Secret { get; set; } = default!; + [Column(DefaultEmptyForString = true)] + public string Description { get; set; } = default!; + [Column(NonNullable = false)] + public DateTime ExpireTime { get; set; } public bool Revoked { get; set; } - public string CreateTime { get; set; } + public DateTime CreateTime { get; set; } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsService.cs index 5cdcc54..23a0e82 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Secrets/SecretsService.cs @@ -3,18 +3,20 @@ using System.Diagnostics; using System.Security.Cryptography; using System.Text; using CrupestApi.Commons; +using CrupestApi.Commons.Crud; using Dapper; using Microsoft.Data.Sqlite; using Microsoft.Extensions.Options; namespace CrupestApi.Secrets; -public class SecretsService : ISecretsService +public class SecretsService : CrudService<SecretInfo>, ISecretsService { private readonly IOptionsSnapshot<CrupestApiConfig> _crupestApiConfig; private readonly ILogger<SecretsService> _logger; - public SecretsService(IOptionsSnapshot<CrupestApiConfig> crupestApiConfig, ILogger<SecretsService> logger) + public SecretsService(IOptionsSnapshot<CrupestApiConfig> crupestApiConfig, ILogger<SecretsService> logger, ServiceProvider services) + : base(services) { _crupestApiConfig = crupestApiConfig; _logger = logger; @@ -25,55 +27,15 @@ public class SecretsService : ISecretsService return Path.Combine(_crupestApiConfig.Value.DataDir, "secrets.db"); } - private async Task<SqliteConnection> EnsureDatabase() + public override string GetDbConnectionString() { - var dataSource = GetDatabasePath(); - var connectionStringBuilder = new SqliteConnectionStringBuilder() - { - DataSource = dataSource - }; - - if (!File.Exists(dataSource)) - { - _logger.LogInformation("Data source {0} does not exist. Create one.", dataSource); - connectionStringBuilder.Mode = SqliteOpenMode.ReadWriteCreate; - var connectionString = connectionStringBuilder.ToString(); - var connection = new SqliteConnection(connectionString); - var transaction = await connection.BeginTransactionAsync(); - - connection.Execute(@" -CREATE TABLE secrets ( - Id INTEGER PRIMARY KEY AUTOINCREMENT, - Key TEXT NOT NULL, - Secret TEXT NOT NULL, - Description TEXT NOT NULL, - ExpireTime TEXT, - Revoked INTEGER NOT NULL, - CreateTime TEXT NOT NULL -); - -CREATE INDEX secrets_key ON secrets (key); - -INSERT INTO secrets (Key, Secret, Description, ExpireTime, Revoked, CreateTime) VALUES (@SecretManagementKey, 'crupest', 'This is the default secret management key.', NULL, 0, @CreateTime); - ", - new - { - SecretManagementKey = SecretsConstants.SecretManagementKey, - CreateTime = DateTime.Now.ToString("O"), - }); - - await transaction.CommitAsync(); + var fileName = GetDatabasePath(); - _logger.LogInformation("{0} created with 'crupest' as the default secret management value. Please immediate revoke it and create a new one.", dataSource); - return connection; - } - else + return new SqliteConnectionStringBuilder() { - _logger.LogInformation("Data source {0} already exists. Will use it."); - connectionStringBuilder.Mode = SqliteOpenMode.ReadWrite; - var connectionString = connectionStringBuilder.ToString(); - return new SqliteConnection(connectionString); - } + DataSource = fileName, + Mode = SqliteOpenMode.ReadWriteCreate + }.ToString(); } private string GenerateRandomKey(int length) @@ -88,83 +50,43 @@ INSERT INTO secrets (Key, Secret, Description, ExpireTime, Revoked, CreateTime) return result.ToString(); } - private async Task<SecretInfo> GetSecretAsync(IDbConnection dbConnection, string secret) + public async Task<SecretInfo> CreateSecretAsync(SecretInfo secretInfo) { - var result = await dbConnection.QueryFirstOrDefaultAsync<SecretInfo>(@" -SELECT Id, Key, Secret, Description, ExpireTime, Revoked, CreateTime FROM secrets WHERE Secret = @Secret; - ", new + if (secretInfo.Secret is not null) { - Secret = secret - }); - - return result; - - } - - public async Task<SecretInfo?> GetSecretAsync(string secret) - { - using var dbConnection = await EnsureDatabase(); - return await GetSecretAsync(dbConnection, secret); - } + throw new ArgumentException("Secret is auto generated. Don't specify it explicit.") + } - public async Task<SecretInfo> CreateSecretAsync(string key, string description, DateTime? expireTime = null) - { - var dbConnection = await EnsureDatabase(); + secretInfo.Secret = GenerateRandomKey(16); + secretInfo.CreateTime = DateTime.Now; - var secret = GenerateRandomKey(16); - var now = DateTime.Now; + await InsertAsync(_table.GenerateInsertClauseFromObject(secretInfo)); - dbConnection.Execute(@" -INSERT INTO secrets (Key, Secret, Description, ExpireTime, Revoked, CreateTime) VALUES (@Key, @Secret, @Description, @ExpireTime, 0, @CreateTime); - ", - new - { - Key = key, - Secret = secret, - Description = description, - ExpireTime = expireTime?.ToString("O"), - CreateTime = now.ToString("O"), - }); - - return new SecretInfo(key, secret, description, expireTime, false, now); + return secretInfo; } public async Task<List<SecretInfo>> GetSecretListAsync(bool includeExpired = false, bool includeRevoked = false) { - var dbConnection = await EnsureDatabase(); - - var query = await dbConnection.QueryAsync<SecretInfo>(@" -SELECT Key, Secret, Description, ExpireTime, Revoked, CreateTime FROM secrets -WHERE @IncludeExpired OR ExpireTime IS NULL OR ExpireTime > @Now AND - @IncludeRevoked OR Revoked = 0; - ", new - { - IncludeExpired = includeExpired, - IncludeRevoked = includeRevoked, - Now = DateTime.Now.ToString("O"), - }); - - return query.ToList(); + return (await QueryAsync()).ToList(); } public async Task<List<SecretInfo>> GetSecretListByKeyAsync(string key, bool includeExpired = false, bool includeRevoked = false) { - var dbConnection = await EnsureDatabase(); + WhereClause where = WhereClause.Create(); - var query = await dbConnection.QueryAsync<SecretInfo>(@" -SELECT Key, Secret, Description, ExpireTime, Revoked, CreateTime FROM secrets -WHERE Key = @Key AND -(@IncludeExpired OR ExpireTime IS NULL OR ExpireTime > @Now) AND -(@IncludeRevoked OR Revoked = 0); - ", new + where.Eq(nameof(SecretInfo.Key), key); + + if (!includeExpired) { - Key = key, - IncludeExpired = includeExpired, - IncludeRevoked = includeRevoked, - Now = DateTime.Now.ToString("O"), - }); + where.Add(nameof(SecretInfo.ExpireTime), "<=", ) + } + + if (!includeRevoked) + { + where.Eq(nameof(SecretInfo.Revoked), false); + } - return query.ToList(); + return (await QueryAsync(where)).ToList(); } public async Task<SecretInfo> ModifySecretAsync(string secret, SecretModifyRequest modifyRequest) |