Develop secret api. v10

4 files changed, 348 insertions, 0 deletions

diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
index acb05f8..f6bbb12 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs
@@ -53,4 +53,13 @@ public class CrudService<TEntity>
         }
         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);
+    }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
new file mode 100644
index 0000000..bd4f300
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
@@ -0,0 +1,41 @@
+namespace CrupestApi.Commons.Crud;
+
+public class OrderByItem
+{
+    public OrderByItem(string columnName, bool isAscending)
+    {
+        ColumnName = columnName;
+        IsAscending = isAscending;
+    }
+
+    public string ColumnName { get; }
+    public bool IsAscending { get; }
+
+    public string GenerateSql()
+    {
+        return $"{ColumnName} {(IsAscending ? "ASC" : "DESC")}";
+    }
+}
+
+public class OrderByClause : List<OrderByItem>
+{
+    public OrderByClause(IEnumerable<OrderByItem> items)
+        : base(items)
+    {
+    }
+
+    public OrderByClause(params OrderByItem[] items)
+        : base(items)
+    {
+    }
+
+    public static OrderByClause Create(params OrderByItem[] items)
+    {
+        return new OrderByClause(items);
+    }
+
+    public string GenerateSql()
+    {
+        return "ORDER BY " + string.Join(", ", this.Select(i => i.GenerateSql()));
+    }
+}
\ No newline at end of file
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
index 38daa3f..04d7f40 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -6,6 +6,8 @@ namespace CrupestApi.Commons.Crud;
 
 public class TableInfo
 {
+    private readonly Lazy<List<string>> _lazyColumnNameList;
+
     // For custom name.
     public TableInfo(Type entityType)
         : this(entityType.Name, entityType)
@@ -47,11 +49,14 @@ public class TableInfo
         ColumnInfos = columnInfos;
 
         CheckValidity();
+
+        _lazyColumnNameList = new Lazy<List<string>>(() => ColumnInfos.Select(c => c.SqlColumnName).ToList());
     }
 
     public Type EntityType { get; }
     public string TableName { get; }
     public IReadOnlyList<ColumnInfo> ColumnInfos { get; }
+    public IReadOnlyList<string> ColumnNameList => _lazyColumnNameList.Value;
 
     public void CheckValidity()
     {
@@ -130,6 +135,58 @@ CREATE TABLE {tableName}(
         {
             return true;
         }
+    }
+
+    public string GenerateSelectSql(WhereClause? whereClause, OrderByClause? orderByClause, int? skip, int? limit, out DynamicParameters parameters)
+    {
+        if (whereClause is not null)
+        {
+            var relatedFields = ((IWhereClause)whereClause).GetRelatedColumns();
+            if (relatedFields is not null)
+            {
+                foreach (var field in relatedFields)
+                {
+                    if (!ColumnNameList.Contains(field))
+                    {
+                        throw new ArgumentException($"Field {field} is not in the table.");
+                    }
+                }
+            }
+        }
+
+        parameters = new DynamicParameters();
+
+        StringBuilder result = new StringBuilder()
+            .Append("SELECT * FROM ")
+            .Append(TableName);
+
+        if (whereClause is not null)
+        {
+            result.Append(' ');
+            result.Append(whereClause.GenerateSql(parameters));
+        }
+
+        if (orderByClause is not null)
+        {
+            result.Append(' ');
+            result.Append(orderByClause.GenerateSql());
+        }
+
+
+        if (limit is not null)
+        {
+            result.Append(" LIMIT @Limit");
+            parameters.Add("Limit", limit.Value);
+        }
+
+        if (skip is not null)
+        {
+            result.Append(" OFFSET @Skip");
+            parameters.Add("Skip", skip.Value);
+        }
+
+        result.Append(';');
 
+        return result.ToString();
     }
 }
\ No newline at end of file
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
new file mode 100644
index 0000000..a62aaec
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
@@ -0,0 +1,241 @@
+using System.Diagnostics;
+using Dapper;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IWhereClause
+{
+    string GenerateSql(DynamicParameters parameters);
+
+    IEnumerable<IWhereClause>? GetSubclauses()
+    {
+        return null;
+    }
+
+    IEnumerable<string>? GetRelatedColumns()
+    {
+        var subclauses = GetSubclauses();
+        if (subclauses is null) return null;
+        var result = new List<string>();
+        foreach (var subclause in subclauses)
+        {
+            var columns = subclause.GetRelatedColumns();
+            if (columns is not null)
+                result.AddRange(columns);
+        }
+        return result;
+    }
+
+    public static string RandomKey(int length)
+    {
+        // I think it's safe to use random here because it's just to differentiate the parameters.
+        // TODO: Consider data race!
+        var random = new Random();
+        var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
+        var result = new string(Enumerable.Repeat(chars, length)
+            .Select(s => s[random.Next(s.Length)]).ToArray());
+        return result;
+    }
+
+    public static string GenerateRandomParameterName(DynamicParameters parameters)
+    {
+        var parameterName = IWhereClause.RandomKey(10);
+        int retryTimes = 1;
+        while (parameters.ParameterNames.Contains(parameterName))
+        {
+            retryTimes++;
+            Debug.Assert(retryTimes <= 100);
+            parameterName = IWhereClause.RandomKey(10);
+        }
+        return parameterName;
+    }
+}
+
+public static class DynamicParametersExtensions
+{
+    public static string AddRandomNameParameter(this DynamicParameters parameters, object value)
+    {
+        var parameterName = IWhereClause.GenerateRandomParameterName(parameters);
+        parameters.Add(parameterName, value);
+        return parameterName;
+    }
+}
+
+public class AndWhereClause : IWhereClause
+{
+    public List<IWhereClause> Clauses { get; } = new List<IWhereClause>();
+
+    public IEnumerable<IWhereClause> GetSubclauses()
+    {
+        return Clauses;
+    }
+
+    public AndWhereClause(IEnumerable<IWhereClause> clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public AndWhereClause(params IWhereClause[] clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public static AndWhereClause Create(params IWhereClause[] clauses)
+    {
+        return new AndWhereClause(clauses);
+    }
+
+    public string GenerateSql(DynamicParameters parameters)
+    {
+        return string.Join(" AND ", Clauses.Select(c => $"({c.GenerateSql(parameters)})"));
+    }
+}
+
+public class OrWhereClause : IWhereClause
+{
+    public List<IWhereClause> Clauses { get; } = new List<IWhereClause>();
+
+    public IEnumerable<IWhereClause> GetSubclauses()
+    {
+        return Clauses;
+    }
+
+    public OrWhereClause(IEnumerable<IWhereClause> clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public OrWhereClause(params IWhereClause[] clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public static OrWhereClause Create(params IWhereClause[] clauses)
+    {
+        return new OrWhereClause(clauses);
+    }
+
+    public string GenerateSql(DynamicParameters parameters)
+    {
+        return string.Join(" OR ", Clauses.Select(c => $"({c.GenerateSql(parameters)})"));
+    }
+}
+
+public class CompareWhereClause : IWhereClause
+{
+    public string Column { get; }
+    public string Operator { get; }
+    public object Value { get; }
+
+    public List<string> GetRelatedColumns()
+    {
+        return new List<string> { Column };
+    }
+
+    // It's user's responsibility to keep column safe, with proper escape.
+    public CompareWhereClause(string column, string @operator, object value)
+    {
+        Column = column;
+        Operator = @operator;
+        Value = value;
+    }
+
+    public static CompareWhereClause Create(string column, string @operator, object value)
+    {
+        return new CompareWhereClause(column, @operator, value);
+    }
+
+    public static CompareWhereClause Eq(string column, object value)
+    {
+        return new CompareWhereClause(column, "=", value);
+    }
+
+    public static CompareWhereClause Neq(string column, object value)
+    {
+        return new CompareWhereClause(column, "<>", value);
+    }
+
+    public static CompareWhereClause Gt(string column, object value)
+    {
+        return new CompareWhereClause(column, ">", value);
+    }
+
+    public static CompareWhereClause Gte(string column, object value)
+    {
+        return new CompareWhereClause(column, ">=", value);
+    }
+
+    public static CompareWhereClause Lt(string column, object value)
+    {
+        return new CompareWhereClause(column, "<", value);
+    }
+
+    public static CompareWhereClause Lte(string column, object value)
+    {
+        return new CompareWhereClause(column, "<=", value);
+    }
+
+    public string GenerateSql(DynamicParameters parameters)
+    {
+        var parameterName = parameters.AddRandomNameParameter(Value);
+        return $"{Column} {Operator} @{parameterName}";
+    }
+}
+
+public class WhereClause : IWhereClause
+{
+    public DynamicParameters Parameters { get; } = new DynamicParameters();
+    public List<IWhereClause> Clauses { get; } = new List<IWhereClause>();
+
+    public WhereClause(IEnumerable<IWhereClause> clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public WhereClause(params IWhereClause[] clauses)
+    {
+        Clauses.AddRange(clauses);
+    }
+
+    public IEnumerable<IWhereClause> GetSubclauses()
+    {
+        return Clauses;
+    }
+
+    public WhereClause Add(params IWhereClause[] clauses)
+    {
+        Clauses.AddRange(clauses);
+        return this;
+    }
+
+    public static WhereClause Create(params IWhereClause[] clauses)
+    {
+        return new WhereClause(clauses);
+    }
+
+    public WhereClause Eq(string column, object value)
+    {
+        return Add(CompareWhereClause.Eq(column, value));
+    }
+
+    public WhereClause Neq(string column, object value)
+    {
+        return Add(CompareWhereClause.Neq(column, value));
+    }
+
+    public WhereClause Eq(IEnumerable<KeyValuePair<string, object>> columnValueMap)
+    {
+        var clauses = columnValueMap.Select(kv => (IWhereClause)CompareWhereClause.Eq(kv.Key, kv.Value)).ToArray();
+        return Add(clauses);
+    }
+
+    public string GenerateSql(DynamicParameters dynamicParameters)
+    {
+        return string.Join(" AND ", Clauses.Select(c => $"({c.GenerateSql(Parameters)})"));
+    }
+
+    public string GenerateSql()
+    {
+        return GenerateSql(Parameters);
+    }
+}