Develop secret api. v21

8 files changed, 211 insertions, 82 deletions

diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
index e67b7c0..d278d23 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
@@ -8,17 +8,21 @@ public class ColumnHooks
 {
     public delegate void ColumnHookAction(ColumnInfo column, ref object? value);
 
-    public ColumnHooks(ColumnHookAction afterGet, ColumnHookAction beforeSet)
+    public ColumnHooks(ColumnHookAction afterSelect, ColumnHookAction beforeInsert, ColumnHookAction beforeUpdate)
     {
-        AfterGet = afterGet;
-        BeforeSet = beforeSet;
+        AfterSelect = afterSelect;
+        BeforeInsert = beforeInsert;
+        BeforeUpdate = beforeUpdate;
     }
 
     // Called after SELECT.
-    public ColumnHookAction AfterGet;
+    public ColumnHookAction AfterSelect;
 
-    // Called before UPDATE and INSERT.
-    public ColumnHookAction BeforeSet;
+    // Called before INSERT.
+    public ColumnHookAction BeforeInsert;
+
+    // Called before UPDATE
+    public ColumnHookAction BeforeUpdate;
 }
 
 public class ColumnInfo
@@ -32,8 +36,9 @@ public class ColumnInfo
         ColumnType = typeProvider.Get(clrType);
 
         Hooks = new ColumnHooks(
-            new ColumnHooks.ColumnHookAction(OnAfterGet),
-            new ColumnHooks.ColumnHookAction(OnBeforeSet)
+            new ColumnHooks.ColumnHookAction(OnAfterSelect),
+            new ColumnHooks.ColumnHookAction(OnBeforeInsert),
+            new ColumnHooks.ColumnHookAction(OnBeforeUpdate)
         );
     }
 
@@ -49,8 +54,9 @@ public class ColumnInfo
         }
 
         Hooks = new ColumnHooks(
-            new ColumnHooks.ColumnHookAction(OnAfterGet),
-            new ColumnHooks.ColumnHookAction(OnBeforeSet)
+            new ColumnHooks.ColumnHookAction(OnAfterSelect),
+            new ColumnHooks.ColumnHookAction(OnBeforeInsert),
+            new ColumnHooks.ColumnHookAction(OnBeforeUpdate)
         );
     }
 
@@ -72,12 +78,21 @@ public class ColumnInfo
         }
     }
 
-    protected void OnAfterGet(ColumnInfo column, ref object? value)
+    protected void OnAfterSelect(ColumnInfo column, ref object? value)
     {
         TryCoerceStringFromNullToEmpty(ref value);
     }
 
-    protected void OnBeforeSet(ColumnInfo column, ref object? value)
+    protected void OnBeforeInsert(ColumnInfo column, ref object? value)
+    {
+        TryCoerceStringFromNullToEmpty(ref value);
+        if (column.IsNotNull && !column.IsAutoIncrement)
+        {
+            throw new Exception($"Column {column.ColumnName} can't be empty.");
+        }
+    }
+
+    protected void OnBeforeUpdate(ColumnInfo column, ref object? value)
     {
         TryCoerceStringFromNullToEmpty(ref value);
     }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
index 05ee269..91e49f8 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
@@ -7,7 +7,16 @@ public static class ColumnMetadataKeys
     public const string IsPrimaryKey = nameof(ColumnAttribute.IsPrimaryKey);
     public const string IsAutoIncrement = nameof(ColumnAttribute.IsAutoIncrement);
     public const string Index = nameof(ColumnAttribute.Index);
+
+    /// <summary>
+    /// This will add hooks for string type column to coerce null to ""(empty string) when get or set. No effect on non-string type.
+    /// </summary> 
     public const string DefaultEmptyForString = nameof(ColumnAttribute.DefaultEmptyForString);
+
+    /// <summary>
+    /// This indicates that you take care of generate this column value when create entity. User calling the api can not specify the value.
+    /// </summary>
+    public const string ClientGenerate = nameof(ColumnAttribute.DefaultEmptyForString);
 }
 
 public interface IColumnMetadata
@@ -72,9 +81,12 @@ public class ColumnAttribute : Attribute, IColumnMetadata
     // default None
     public ColumnIndexType Index { get; init; } = ColumnIndexType.None;
 
-    // Use empty string for default value of string type.
+    /// <see cref="ColumnMetadataKeys.DefaultEmptyForString"/>
     public bool DefaultEmptyForString { get; init; }
 
+    /// <see cref="ColumnMetadataKeys.ClientGenerate"/>
+    public bool ClientGenerate { get; init; }
+
     public bool TryGetValue(string key, out object? value)
     {
         var property = GetType().GetProperty(key);
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
index 70fdbfd..7591271 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
@@ -20,6 +20,7 @@ public interface IColumnTypeInfo
 
     Type ClrType { get; }
     Type DatabaseClrType { get; }
+    bool IsSimple { get { return ClrType == DatabaseClrType; } }
     DbType DbType
     {
         get
@@ -84,14 +85,14 @@ public interface IColumnTypeInfo
     // You must override this method if ClrType != DatabaseClrType
     object? ConvertFromDatabase(object? databaseValue)
     {
-        Debug.Assert(ClrType == DatabaseClrType);
+        Debug.Assert(IsSimple);
         return databaseValue;
     }
 
     // You must override this method if ClrType != DatabaseClrType
     object? ConvertToDatabase(object? value)
     {
-        Debug.Assert(ClrType == DatabaseClrType);
+        Debug.Assert(IsSimple);
         return value;
     }
 }
@@ -178,6 +179,12 @@ public class ColumnTypeProvider : IColumnTypeProvider
         }
         else
         {
+            if (clrType == typeof(Nullable<>))
+            {
+                clrType = clrType.GetGenericArguments()[0];
+                return Get(clrType);
+            }
+
             throw new Exception($"Unsupported 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 9402d69..e098aca 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -1,23 +1,21 @@
 using System.Data;
 using Dapper;
-using Microsoft.Extensions.Options;
 
 namespace CrupestApi.Commons.Crud;
 
-// TODO: Implement and register this service.
-public class CrudService<TEntity> : IDisposable
+public class CrudService<TEntity> : IDisposable where TEntity : class
 {
     protected readonly TableInfo _table;
+    protected readonly string? _connectionName;
     protected readonly IDbConnection _dbConnection;
-    protected readonly IOptionsSnapshot<CrupestApiConfig> _crupestApiOptions;
     private readonly ILogger<CrudService<TEntity>> _logger;
 
-    public CrudService(ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, IOptionsSnapshot<CrupestApiConfig> crupestApiOptions, ILogger<CrudService<TEntity>> logger)
+    public CrudService(string? connectionName, ITableInfoFactory tableInfoFactory, IDbConnectionFactory dbConnectionFactory, ILoggerFactory loggerFactory)
     {
+        _connectionName = connectionName;
         _table = tableInfoFactory.Get(typeof(TEntity));
-        _dbConnection = dbConnectionFactory.Get();
-        _crupestApiOptions = crupestApiOptions;
-        _logger = logger;
+        _dbConnection = dbConnectionFactory.Get(_connectionName);
+        _logger = loggerFactory.CreateLogger<CrudService<TEntity>>();
 
         if (!_table.CheckExistence(_dbConnection))
         {
@@ -36,4 +34,29 @@ public class CrudService<TEntity> : IDisposable
     {
         _dbConnection.Dispose();
     }
+
+    public List<TEntity> Select(IWhereClause? filter)
+    {
+        return _table.Select(_dbConnection, filter).Cast<TEntity>().ToList();
+    }
+
+    public int Insert(IInsertClause insertClause)
+    {
+        return _table.Insert(_dbConnection, insertClause);
+    }
+
+    public int Insert(TEntity entity)
+    {
+        return _table.Insert(_dbConnection, _table.GenerateInsertClauseFromEntity(entity));
+    }
+
+    public int Update(IUpdateClause updateClause, IWhereClause? filter)
+    {
+        return _table.Update(_dbConnection, filter, updateClause);
+    }
+
+    public int Delete(IWhereClause? filter)
+    {
+        return _table.Delete(_dbConnection, filter);
+    }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
index 92b5660..29504f4 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudServiceCollectionExtensions.cs
@@ -6,7 +6,9 @@ public static class CrudServiceCollectionExtensions
 {
     public static IServiceCollection UseCrud(this IServiceCollection services)
     {
+        services.TryAddSingleton<IDbConnectionFactory, SqliteConnectionFactory>();
         services.TryAddSingleton<IColumnTypeProvider, ColumnTypeProvider>();
+        services.TryAddSingleton<ITableInfoFactory, TableInfoFactory>();
         return services;
     }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
index 80d1b22..2ee01ca 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbConnectionFactory.cs
@@ -1,4 +1,6 @@
 using System.Data;
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Options;
 
 namespace CrupestApi.Commons.Crud;
 
@@ -7,3 +9,24 @@ public interface IDbConnectionFactory
 {
     IDbConnection Get(string? name = null);
 }
+
+public class SqliteConnectionFactory : IDbConnectionFactory
+{
+    private readonly IOptionsMonitor<CrupestApiConfig> _apiConfigMonitor;
+
+    public SqliteConnectionFactory(IOptionsMonitor<CrupestApiConfig> apiConfigMonitor)
+    {
+        _apiConfigMonitor = apiConfigMonitor;
+    }
+
+    public IDbConnection Get(string? name = null)
+    {
+        var connectionString = new SqliteConnectionStringBuilder()
+        {
+            DataSource = Path.Combine(_apiConfigMonitor.CurrentValue.DataDir, $"{name ?? "crupest-api"}.db"),
+            Mode = SqliteOpenMode.ReadWriteCreate
+        }.ToString();
+
+        return new SqliteConnection(connectionString);
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
index 73082c0..4836720 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -189,12 +189,9 @@ CREATE TABLE {tableName}(
 
     public void CheckColumnName(string columnName)
     {
-        foreach (var c in columnName)
+        if (!ColumnNameList.Contains(columnName))
         {
-            if (char.IsWhiteSpace(c))
-            {
-                throw new Exception("White space found in column name, which might be an sql injection attack!");
-            }
+            throw new ArgumentException($"Column {columnName} is not in the table.");
         }
     }
 
@@ -206,10 +203,6 @@ CREATE TABLE {tableName}(
             foreach (var column in relatedColumns)
             {
                 CheckColumnName(column);
-                if (!ColumnNameList.Contains(column))
-                {
-                    throw new ArgumentException($"Column {column} is not in the table.");
-                }
             }
         }
     }
@@ -258,13 +251,15 @@ CREATE TABLE {tableName}(
         return (result.ToString(), parameters);
     }
 
-    public InsertClause GenerateInsertClauseFromObject(object value)
+    public InsertClause GenerateInsertClauseFromEntity(object entity)
     {
+        Debug.Assert(EntityType.IsInstanceOfType(entity));
+
         var insertClause = InsertClause.Create();
 
         foreach (var column in ColumnInfos)
         {
-            var propertyInfo = EntityType.GetProperty(column.ColumnName);
+            var propertyInfo = column.PropertyInfo;
             if (propertyInfo is null)
             {
                 if (column.IsAutoIncrement)
@@ -277,7 +272,7 @@ CREATE TABLE {tableName}(
                 }
             }
 
-            var propertyValue = propertyInfo.GetValue(value);
+            var propertyValue = propertyInfo.GetValue(entity);
             if (propertyValue is null)
             {
                 if (column.IsAutoIncrement)
@@ -360,52 +355,63 @@ CREATE TABLE {tableName}(
         return (sb.ToString(), parameters);
     }
 
-    private object? ClearNonColumnProperties(object? entity)
+    private DynamicParameters ConvertParameters(DynamicParameters parameters)
     {
-        Debug.Assert(entity is null || entity.GetType() == EntityType);
-        if (entity is null) return entity;
-        foreach (var property in NonColumnProperties)
+        var result = new DynamicParameters();
+        foreach (var paramName in parameters.ParameterNames)
         {
-            // Clear any non-column properties.
-            property.SetValue(entity, Activator.CreateInstance(property.PropertyType));
+            var value = parameters.Get<object?>(paramName);
+            if (value is null)
+            {
+                result.Add(paramName, null);
+                continue;
+            }
+            var typeInfo = _columnTypeProvider.Get(value.GetType());
+            result.Add(paramName, typeInfo.ConvertToDatabase(value));
         }
-        return entity;
+        return result;
     }
 
-    private object? CallColumnHook(object? entity, string hookName)
+    private object? ConvertFromDynamicToEntity(dynamic d)
     {
-        Debug.Assert(entity is null || entity.GetType() == EntityType);
-        if (entity is null) return entity;
+        if (d is null) return null;
+
+        var result = Activator.CreateInstance(EntityType);
+
         foreach (var column in ColumnInfos)
         {
-            var property = column.PropertyInfo;
-            if (property is not null)
+            var propertyInfo = column.PropertyInfo;
+            if (propertyInfo is not null)
             {
-                var value = property.GetValue(entity);
-
-                switch (hookName)
-                {
-                    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);
+                object? value = d[column.ColumnName];
+                value = column.ColumnType.ConvertFromDatabase(value);
+                propertyInfo.SetValue(result, value);
             }
-
         }
-        return entity;
+
+        return result;
     }
 
     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"));
+        return dbConnection.Query<dynamic>(sql, parameters).Select(d =>
+        {
+            var e = ConvertFromDynamicToEntity(d);
+
+            foreach (var column in ColumnInfos)
+            {
+                var propertyInfo = column.PropertyInfo;
+                if (propertyInfo is not null)
+                {
+                    var value = propertyInfo.GetValue(e);
+                    column.Hooks.AfterSelect(column, ref value);
+                    propertyInfo.SetValue(e, value);
+                }
+            }
+
+            return e;
+        });
     }
 
     public virtual int Insert(IDbConnection dbConnection, IInsertClause insert)
@@ -416,11 +422,11 @@ CREATE TABLE {tableName}(
         {
             var column = GetColumn(item.ColumnName);
             var value = item.Value;
-            column.Hooks.BeforeSet?.Invoke(column, ref value);
+            column.Hooks.BeforeInsert(column, ref value);
             item.Value = value;
         }
 
-        return dbConnection.Execute(sql, parameters);
+        return dbConnection.Execute(sql, ConvertParameters(parameters));
     }
 
     public virtual int Update(IDbConnection dbConnection, IWhereClause? where, IUpdateClause update)
@@ -431,21 +437,45 @@ CREATE TABLE {tableName}(
         {
             var column = GetColumn(item.ColumnName);
             var value = item.Value;
-            column.Hooks.BeforeSet?.Invoke(column, ref value);
+            column.Hooks.BeforeUpdate(column, ref value);
             item.Value = value;
         }
-        return dbConnection.Execute(sql, parameters);
+        return dbConnection.Execute(sql, ConvertParameters(parameters));
     }
 
     public virtual int Delete(IDbConnection dbConnection, IWhereClause? where)
     {
         var (sql, parameters) = GenerateDeleteSql(where);
-        return dbConnection.Execute(sql, parameters);
+        return dbConnection.Execute(sql, ConvertParameters(parameters));
     }
 }
 
-// TODO: Implement and register this service.
 public interface ITableInfoFactory
 {
     TableInfo Get(Type type);
 }
+
+public class TableInfoFactory : ITableInfoFactory
+{
+    private readonly Dictionary<Type, TableInfo> _cache = new Dictionary<Type, TableInfo>();
+    private readonly IColumnTypeProvider _columnTypeProvider;
+
+    public TableInfoFactory(IColumnTypeProvider columnTypeProvider)
+    {
+        _columnTypeProvider = columnTypeProvider;
+    }
+
+    public TableInfo Get(Type type)
+    {
+        if (_cache.TryGetValue(type, out var tableInfo))
+        {
+            return tableInfo;
+        }
+        else
+        {
+            tableInfo = new TableInfo(type, _columnTypeProvider);
+            _cache.Add(type, tableInfo);
+            return tableInfo;
+        }
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
index 8a0b7ac..8ae2c01 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
@@ -15,12 +15,12 @@ public class CompositeWhereClause : IWhereClause
     {
         ConcatOp = concatOp;
         ParenthesesSubclause = parenthesesSubclause;
-        Subclauses = subclauses;
+        Subclauses = subclauses.ToList();
     }
 
     public string ConcatOp { get; }
     public bool ParenthesesSubclause { get; }
-    public IWhereClause[] Subclauses { get; }
+    public List<IWhereClause> Subclauses { get; }
 
     public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null)
     {
@@ -106,7 +106,7 @@ public class SimpleCompareWhereClause : IWhereClause
 {
     public string Column { get; }
     public string Operator { get; }
-    public object Value { get; }
+    public object? Value { get; }
 
     public List<string> GetRelatedColumns()
     {
@@ -114,44 +114,44 @@ public class SimpleCompareWhereClause : IWhereClause
     }
 
     // It's user's responsibility to keep column safe, with proper escape.
-    public SimpleCompareWhereClause(string column, string op, object value)
+    public SimpleCompareWhereClause(string column, string op, object? value)
     {
         Column = column;
         Operator = op;
         Value = value;
     }
 
-    public static SimpleCompareWhereClause Create(string column, string op, object value)
+    public static SimpleCompareWhereClause Create(string column, string op, object? value)
     {
         return new SimpleCompareWhereClause(column, op, value);
     }
 
-    public static SimpleCompareWhereClause Eq(string column, object value)
+    public static SimpleCompareWhereClause Eq(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, "=", value);
     }
 
-    public static SimpleCompareWhereClause Neq(string column, object value)
+    public static SimpleCompareWhereClause Neq(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, "<>", value);
     }
 
-    public static SimpleCompareWhereClause Gt(string column, object value)
+    public static SimpleCompareWhereClause Gt(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, ">", value);
     }
 
-    public static SimpleCompareWhereClause Gte(string column, object value)
+    public static SimpleCompareWhereClause Gte(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, ">=", value);
     }
 
-    public static SimpleCompareWhereClause Lt(string column, object value)
+    public static SimpleCompareWhereClause Lt(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, "<", value);
     }
 
-    public static SimpleCompareWhereClause Lte(string column, object value)
+    public static SimpleCompareWhereClause Lte(string column, object? value)
     {
         return new SimpleCompareWhereClause(column, "<=", value);
     }
@@ -163,3 +163,20 @@ public class SimpleCompareWhereClause : IWhereClause
         return ($"{Column} {Operator} @{parameterName}", parameters);
     }
 }
+
+public class WhereClause : AndWhereClause
+{
+    public WhereClause()
+    {
+    }
+
+    public void Add(IWhereClause subclause)
+    {
+        Subclauses.Add(subclause);
+    }
+
+    public void Eq(string column, object? value)
+    {
+        Subclauses.Add(SimpleCompareWhereClause.Eq(column, value));
+    }
+}