Develop secret api. v18

6 files changed, 253 insertions, 116 deletions

diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
index e60b202..e67b7c0 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
@@ -4,20 +4,42 @@ using System.Text;
 
 namespace CrupestApi.Commons.Crud;
 
+public class ColumnHooks
+{
+    public delegate void ColumnHookAction(ColumnInfo column, ref object? value);
+
+    public ColumnHooks(ColumnHookAction afterGet, ColumnHookAction beforeSet)
+    {
+        AfterGet = afterGet;
+        BeforeSet = beforeSet;
+    }
+
+    // Called after SELECT.
+    public ColumnHookAction AfterGet;
+
+    // Called before UPDATE and INSERT.
+    public ColumnHookAction BeforeSet;
+}
+
 public class ColumnInfo
 {
     private readonly AggregateColumnMetadata _metadata = new AggregateColumnMetadata();
 
-    public ColumnInfo(Type entityType, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider)
+    public ColumnInfo(TableInfo table, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider)
     {
-        EntityType = entityType;
+        Table = table;
         _metadata.Add(metadata);
         ColumnType = typeProvider.Get(clrType);
+
+        Hooks = new ColumnHooks(
+            new ColumnHooks.ColumnHookAction(OnAfterGet),
+            new ColumnHooks.ColumnHookAction(OnBeforeSet)
+        );
     }
 
-    public ColumnInfo(PropertyInfo propertyInfo, IColumnTypeProvider typeProvider)
+    public ColumnInfo(TableInfo table, PropertyInfo propertyInfo, IColumnTypeProvider typeProvider)
     {
-        EntityType = propertyInfo.DeclaringType!;
+        Table = table;
         ColumnType = typeProvider.Get(propertyInfo.PropertyType);
 
         var columnAttribute = propertyInfo.GetCustomAttribute<ColumnAttribute>();
@@ -25,9 +47,14 @@ public class ColumnInfo
         {
             _metadata.Add(columnAttribute);
         }
+
+        Hooks = new ColumnHooks(
+            new ColumnHooks.ColumnHookAction(OnAfterGet),
+            new ColumnHooks.ColumnHookAction(OnBeforeSet)
+        );
     }
 
-    public Type EntityType { get; }
+    public TableInfo Table { get; }
     // If null, there is no corresponding property.
     public PropertyInfo? PropertyInfo { get; } = null;
 
@@ -35,6 +62,26 @@ public class ColumnInfo
 
     public IColumnTypeInfo ColumnType { get; }
 
+    public ColumnHooks Hooks { get; }
+
+    private void TryCoerceStringFromNullToEmpty(ref object? value)
+    {
+        if (ColumnType.ClrType == typeof(string) && (Metadata.GetValueOrDefault<bool?>(ColumnMetadataKeys.DefaultEmptyForString) ?? false) && value is null)
+        {
+            value = "";
+        }
+    }
+
+    protected void OnAfterGet(ColumnInfo column, ref object? value)
+    {
+        TryCoerceStringFromNullToEmpty(ref value);
+    }
+
+    protected void OnBeforeSet(ColumnInfo column, ref object? value)
+    {
+        TryCoerceStringFromNullToEmpty(ref value);
+    }
+
     public string ColumnName
     {
         get
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
index 679cb4c..14960c7 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
@@ -4,6 +4,7 @@ using System.Text.Json.Serialization;
 
 namespace CrupestApi.Commons.Crud;
 
+// TODO: Implement this.
 public interface IColumnTypeInfo
 {
     Type ClrType { get; }
@@ -40,6 +41,7 @@ public interface IColumnTypeInfo
     }
 }
 
+// TODO: Implement and register this service.
 public interface IColumnTypeProvider
 {
     IColumnTypeInfo Get(Type clrType);
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
index 7da6ac7..9402d69 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -1,89 +1,39 @@
+using System.Data;
 using Dapper;
-using Microsoft.Data.Sqlite;
 using Microsoft.Extensions.Options;
 
 namespace CrupestApi.Commons.Crud;
 
-public class CrudService<TEntity>
+// TODO: Implement and register this service.
+public class CrudService<TEntity> : IDisposable
 {
     protected readonly TableInfo _table;
+    protected readonly IDbConnection _dbConnection;
     protected readonly IOptionsSnapshot<CrupestApiConfig> _crupestApiOptions;
     private readonly ILogger<CrudService<TEntity>> _logger;
 
-    public CrudService(ServiceProvider services)
+    public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IOptionsSnapshot<CrupestApiConfig> crupestApiOptions, ILogger<CrudService<TEntity>> logger)
     {
-        _table = new TableInfo(typeof(TEntity));
-        _crupestApiOptions = services.GetRequiredService<IOptionsSnapshot<CrupestApiConfig>>();
-        _logger = services.GetRequiredService<ILogger<CrudService<TEntity>>>();
-    }
-
-    public virtual string GetDbConnectionString()
-    {
-        var fileName = Path.Combine(_crupestApiOptions.Value.DataDir, "crupest-api.db");
+        _table = tableInfoFactory.Get(typeof(TEntity));
+        _dbConnection = dbConnectionFactory.Get();
+        _crupestApiOptions = crupestApiOptions;
+        _logger = logger;
 
-        return new SqliteConnectionStringBuilder()
+        if (!_table.CheckExistence(_dbConnection))
         {
-            DataSource = fileName,
-            Mode = SqliteOpenMode.ReadWriteCreate
-        }.ToString();
-    }
-
-
-    public async Task<SqliteConnection> CreateDbConnection()
-    {
-        var connection = new SqliteConnection(GetDbConnectionString());
-        await connection.OpenAsync();
-        return connection;
-    }
-
-    public virtual async Task DoInitializeDatabase(SqliteConnection connection)
-    {
-        await using var transaction = await connection.BeginTransactionAsync();
-        await connection.ExecuteAsync(_table.GenerateCreateTableSql(), transaction: transaction);
-        await transaction.CommitAsync();
-    }
-
-    public virtual async Task<SqliteConnection> EnsureDatabase()
-    {
-        var connection = await CreateDbConnection();
-        var exist = await _table.CheckExistence(connection);
-        if (!exist)
-        {
-            await DoInitializeDatabase(connection);
+            DoInitializeDatabase(_dbConnection);
         }
-        return connection;
-    }
-
-
-    public virtual async Task<IEnumerable<TEntity>> QueryAsync(WhereClause? where = null, OrderByClause? orderBy = null, int? skip = null, int? limit = null)
-    {
-        var connection = await EnsureDatabase();
-        DynamicParameters parameters;
-        var sql = _table.GenerateSelectSql(where, orderBy, skip, limit, out parameters);
-        return await connection.QueryAsync<TEntity>(sql, parameters);
-    }
-
-    public virtual async Task<int> InsertAsync(InsertClause insert)
-    {
-        var connection = await EnsureDatabase();
-        DynamicParameters parameters;
-        var sql = _table.GenerateInsertSql(insert, out parameters);
-        return await connection.ExecuteAsync(sql, parameters);
     }
 
-    public virtual async Task<int> UpdateAsync(WhereClause? where, UpdateClause update)
+    public virtual void DoInitializeDatabase(IDbConnection connection)
     {
-        var connection = await EnsureDatabase();
-        DynamicParameters parameters;
-        var sql = _table.GenerateUpdateSql(where, update, out parameters);
-        return await connection.ExecuteAsync(sql, parameters);
+        using var transaction = connection.BeginTransaction();
+        connection.Execute(_table.GenerateCreateTableSql(), transaction: transaction);
+        transaction.Commit();
     }
 
-    public virtual async Task<int> DeleteAsync(WhereClause? where)
+    public void Dispose()
     {
-        var connection = await EnsureDatabase();
-        DynamicParameters parameters;
-        var sql = _table.GenerateDeleteSql(where, out parameters);
-        return await connection.ExecuteAsync(sql, parameters);
+        _dbConnection.Dispose();
     }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
new file mode 100644
index 0000000..80d1b22
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
@@ -0,0 +1,9 @@
+using System.Data;
+
+namespace CrupestApi.Commons.Crud;
+
+// TODO: Implement and register this service.
+public interface IDbConnectionFactory
+{
+    IDbConnection Get(string? name = null);
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
index 103442c..73082c0 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -1,7 +1,8 @@
 using System.Data;
+using System.Diagnostics;
+using System.Reflection;
 using System.Text;
 using Dapper;
-using Microsoft.Data.Sqlite;
 
 namespace CrupestApi.Commons.Crud;
 
@@ -29,15 +30,26 @@ public class TableInfo
         bool hasPrimaryKey = false;
         bool hasId = false;
 
+        List<PropertyInfo> columnProperties = new();
+        List<PropertyInfo> nonColumnProperties = new();
+
         foreach (var property in properties)
         {
-            var columnInfo = new ColumnInfo(property, _columnTypeProvider);
-            columnInfos.Add(columnInfo);
-            if (columnInfo.IsPrimaryKey)
-                hasPrimaryKey = true;
-            if (columnInfo.ColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
+            if (PropertyIsColumn(property))
             {
-                hasId = true;
+                var columnInfo = new ColumnInfo(this, property, _columnTypeProvider);
+                columnInfos.Add(columnInfo);
+                if (columnInfo.IsPrimaryKey)
+                    hasPrimaryKey = true;
+                if (columnInfo.ColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
+                {
+                    hasId = true;
+                }
+                columnProperties.Add(property);
+            }
+            else
+            {
+                nonColumnProperties.Add(property);
             }
         }
 
@@ -49,15 +61,17 @@ public class TableInfo
         }
 
         ColumnInfos = columnInfos;
+        ColumnProperties = columnProperties;
+        NonColumnProperties = nonColumnProperties;
 
         CheckValidity();
 
-        _lazyColumnNameList = new Lazy<List<string>>(() => ColumnInfos.Select(c => c.SqlColumnName).ToList());
+        _lazyColumnNameList = new Lazy<List<string>>(() => ColumnInfos.Select(c => c.ColumnName).ToList());
     }
 
     private ColumnInfo CreateAutoIdColumn()
     {
-        return new ColumnInfo(EntityType,
+        return new ColumnInfo(this,
                 new ColumnAttribute
                 {
                     ColumnName = "Id",
@@ -71,8 +85,29 @@ public class TableInfo
     public Type EntityType { get; }
     public string TableName { get; }
     public IReadOnlyList<ColumnInfo> ColumnInfos { get; }
+    public IReadOnlyList<PropertyInfo> ColumnProperties { get; }
+    public IReadOnlyList<PropertyInfo> NonColumnProperties { get; }
     public IReadOnlyList<string> ColumnNameList => _lazyColumnNameList.Value;
 
+    protected bool PropertyIsColumn(PropertyInfo property)
+    {
+        var columnAttribute = property.GetCustomAttribute<ColumnAttribute>();
+        if (columnAttribute is null) return false;
+        return true;
+    }
+
+    public ColumnInfo GetColumn(string columnName)
+    {
+        foreach (var column in ColumnInfos)
+        {
+            if (column.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
+            {
+                return column;
+            }
+        }
+        throw new KeyNotFoundException("No such column with given name.");
+    }
+
     public void CheckValidity()
     {
         // Check if there is only one primary key.
@@ -152,6 +187,17 @@ CREATE TABLE {tableName}(
         }
     }
 
+    public void CheckColumnName(string columnName)
+    {
+        foreach (var c in columnName)
+        {
+            if (char.IsWhiteSpace(c))
+            {
+                throw new Exception("White space found in column name, which might be an sql injection attack!");
+            }
+        }
+    }
+
     public void CheckRelatedColumns(IClause? clause)
     {
         if (clause is not null)
@@ -159,6 +205,7 @@ CREATE TABLE {tableName}(
             var relatedColumns = clause.GetRelatedColumns();
             foreach (var column in relatedColumns)
             {
+                CheckColumnName(column);
                 if (!ColumnNameList.Contains(column))
                 {
                     throw new ArgumentException($"Column {column} is not in the table.");
@@ -268,62 +315,137 @@ CREATE TABLE {tableName}(
         return (result.ToString(), parameters);
     }
 
-    // TODO: Continue...
-    public string GenerateUpdateSql(IWhereClause? whereClause, UpdateClause updateClause)
+    public (string sql, DynamicParameters parameters) GenerateUpdateSql(IWhereClause? whereClause, IUpdateClause updateClause)
     {
-        var relatedColumns = new HashSet<string>();
-        if (whereClause is not null)
-            relatedColumns.UnionWith(((IClause)whereClause).GetRelatedColumns() ?? Enumerable.Empty<string>());
-        relatedColumns.UnionWith(updateClause.GetRelatedColumns());
-        foreach (var column in relatedColumns)
-        {
-            if (!ColumnNameList.Contains(column))
-            {
-                throw new ArgumentException($"Column {column} is not in the table.");
-            }
-        }
+        CheckRelatedColumns(whereClause);
+        CheckRelatedColumns(updateClause);
 
-        parameters = new DynamicParameters();
+        var parameters = new DynamicParameters();
 
         StringBuilder sb = new StringBuilder("UPDATE ");
         sb.Append(TableName);
         sb.Append(" SET ");
-        sb.Append(updateClause.GenerateSql(parameters));
+        var (updateSql, updateParameters) = updateClause.GenerateSql();
+        sb.Append(updateSql);
+        parameters.AddDynamicParams(updateParameters);
         if (whereClause is not null)
         {
             sb.Append(" WHERE ");
-            sb.Append(whereClause.GenerateSql(parameters));
+            var (whereSql, whereParameters) = whereClause.GenerateSql();
+            sb.Append(whereSql);
+            parameters.AddDynamicParams(whereParameters);
         }
         sb.Append(';');
 
-        return sb.ToString();
+        return (sb.ToString(), parameters);
     }
 
-    public string GenerateDeleteSql(WhereClause? whereClause, out DynamicParameters parameters)
+    public (string sql, DynamicParameters parameters) GenerateDeleteSql(IWhereClause? whereClause)
     {
+        CheckRelatedColumns(whereClause);
+
+        var parameters = new DynamicParameters();
+
+        StringBuilder sb = new StringBuilder("DELETE FROM ");
+        sb.Append(TableName);
         if (whereClause is not null)
         {
-            var relatedColumns = ((IClause)whereClause).GetRelatedColumns() ?? new List<string>();
-            foreach (var column in relatedColumns)
+            sb.Append(" WHERE ");
+            var (whereSql, whereParameters) = whereClause.GenerateSql();
+            parameters.AddDynamicParams(whereParameters);
+            sb.Append(whereSql);
+        }
+        sb.Append(';');
+
+        return (sb.ToString(), parameters);
+    }
+
+    private object? ClearNonColumnProperties(object? entity)
+    {
+        Debug.Assert(entity is null || entity.GetType() == EntityType);
+        if (entity is null) return entity;
+        foreach (var property in NonColumnProperties)
+        {
+            // Clear any non-column properties.
+            property.SetValue(entity, Activator.CreateInstance(property.PropertyType));
+        }
+        return entity;
+    }
+
+    private object? CallColumnHook(object? entity, string hookName)
+    {
+        Debug.Assert(entity is null || entity.GetType() == EntityType);
+        if (entity is null) return entity;
+        foreach (var column in ColumnInfos)
+        {
+            var property = column.PropertyInfo;
+            if (property is not null)
             {
-                if (!ColumnNameList.Contains(column))
+                var value = property.GetValue(entity);
+
+                switch (hookName)
                 {
-                    throw new ArgumentException($"Column {column} is not in the table.");
-                }
+                    case "AfterGet":
+                        column.Hooks.AfterGet(column, ref value);
+                        break;
+                    case "BeforeSet":
+                        column.Hooks.BeforeSet(column, ref value);
+                        break;
+                    default:
+                        throw new Exception("Unknown hook.");
+                };
+
+                property.SetValue(entity, value);
             }
+
+        }
+        return entity;
+    }
+
+    public virtual IEnumerable<object?> Select(IDbConnection dbConnection, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null)
+    {
+        var (sql, parameters) = GenerateSelectSql(where, orderBy, skip, limit);
+        return dbConnection.Query(EntityType, sql, parameters).Select(e => CallColumnHook(ClearNonColumnProperties(e), "AfterGet"));
+    }
+
+    public virtual int Insert(IDbConnection dbConnection, IInsertClause insert)
+    {
+        var (sql, parameters) = GenerateInsertSql(insert);
+
+        foreach (var item in insert.Items)
+        {
+            var column = GetColumn(item.ColumnName);
+            var value = item.Value;
+            column.Hooks.BeforeSet?.Invoke(column, ref value);
+            item.Value = value;
         }
 
-        parameters = new DynamicParameters();
+        return dbConnection.Execute(sql, parameters);
+    }
 
-        StringBuilder sb = new StringBuilder("DELETE FROM ");
-        sb.Append(TableName);
-        if (whereClause is not null)
+    public virtual int Update(IDbConnection dbConnection, IWhereClause? where, IUpdateClause update)
+    {
+        var (sql, parameters) = GenerateUpdateSql(where, update);
+
+        foreach (var item in update.Items)
         {
-            sb.Append(" WHERE ");
-            sb.Append(whereClause.GenerateSql(parameters));
+            var column = GetColumn(item.ColumnName);
+            var value = item.Value;
+            column.Hooks.BeforeSet?.Invoke(column, ref value);
+            item.Value = value;
         }
-        sb.Append(';');
+        return dbConnection.Execute(sql, parameters);
+    }
 
-        return sb.ToString();
+    public virtual int Delete(IDbConnection dbConnection, IWhereClause? where)
+    {
+        var (sql, parameters) = GenerateDeleteSql(where);
+        return dbConnection.Execute(sql, parameters);
     }
 }
+
+// TODO: Implement and register this service.
+public interface ITableInfoFactory
+{
+    TableInfo Get(Type type);
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
index 7cb5edf..b9cafee 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
@@ -15,8 +15,13 @@ public class UpdateItem
     public object? Value { get; set; }
 }
 
-// TODO: Continue...
-public class UpdateClause
+public interface IUpdateClause : IClause
+{
+    List<UpdateItem> Items { get; }
+    (string sql, DynamicParameters parameters) GenerateSql();
+}
+
+public class UpdateClause : IUpdateClause
 {
     public List<UpdateItem> Items { get; } = new List<UpdateItem>();
 
@@ -51,8 +56,10 @@ public class UpdateClause
         return Items.Select(i => i.ColumnName).ToList();
     }
 
-    public string GenerateSql(DynamicParameters parameters)
+    public (string sql, DynamicParameters parameters) GenerateSql()
     {
+        var parameters = new DynamicParameters();
+
         StringBuilder result = new StringBuilder();
 
         foreach (var item in Items)
@@ -66,6 +73,6 @@ public class UpdateClause
             result.Append($"{item.ColumnName} = @{parameterName}");
         }
 
-        return result.ToString();
+        return (result.ToString(), parameters);
     }
 }