Develop secret api. v14

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)