Develop secret api. v17

11 files changed, 409 insertions, 597 deletions

diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
index 081071f..e60b202 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs
@@ -1,113 +1,68 @@
-using System.Data;
+using System.Diagnostics;
 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)
-    {
-        if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
-        {
-            return type.GetGenericArguments()[0];
-        }
-
-        return type;
-    }
+    private readonly AggregateColumnMetadata _metadata = new AggregateColumnMetadata();
 
-    // A column with no property.
-    public ColumnInfo(Type entityType, string sqlColumnName, bool isPrimaryKey, bool isAutoIncrement, ColumnTypeInfo typeInfo, ColumnIndexType indexType = ColumnIndexType.None, ColumnTypeRegistry? typeRegistry = null)
+    public ColumnInfo(Type entityType, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider)
     {
-        if (typeRegistry is null)
-        {
-            typeRegistry = ColumnTypeRegistry.Instance;
-        }
-
         EntityType = entityType;
-        PropertyName = sqlColumnName;
-        PropertyType = typeof(int);
-        PropertyRealType = typeof(int);
-        SqlColumnName = sqlColumnName;
-        ColumnTypeInfo = typeInfo;
-        Nullable = false;
-        IsPrimaryKey = isPrimaryKey;
-        IsAutoIncrement = isAutoIncrement;
-        TypeRegistry = typeRegistry;
-        IndexType = indexType;
+        _metadata.Add(metadata);
+        ColumnType = typeProvider.Get(clrType);
     }
 
-    public ColumnInfo(Type entityType, string entityPropertyName, ColumnTypeRegistry? typeRegistry = null)
+    public ColumnInfo(PropertyInfo propertyInfo, IColumnTypeProvider typeProvider)
     {
-        if (typeRegistry is null)
+        EntityType = propertyInfo.DeclaringType!;
+        ColumnType = typeProvider.Get(propertyInfo.PropertyType);
+
+        var columnAttribute = propertyInfo.GetCustomAttribute<ColumnAttribute>();
+        if (columnAttribute is not null)
         {
-            typeRegistry = ColumnTypeRegistry.Instance;
+            _metadata.Add(columnAttribute);
         }
+    }
 
-        EntityType = entityType;
-        PropertyName = entityPropertyName;
-        PropertyInfo = entityType.GetProperty(entityPropertyName);
+    public Type EntityType { get; }
+    // If null, there is no corresponding property.
+    public PropertyInfo? PropertyInfo { get; } = null;
 
-        if (PropertyInfo is null)
-            throw new Exception("Public property with given name does not exist.");
+    public IColumnMetadata Metadata => _metadata;
 
-        PropertyType = PropertyInfo.PropertyType;
-        PropertyRealType = ExtractRealTypeFromNullable(PropertyType);
+    public IColumnTypeInfo ColumnType { get; }
 
-        var columnAttribute = PropertyInfo.GetCustomAttribute<ColumnAttribute>();
-        if (columnAttribute is null)
-        {
-            SqlColumnName = PropertyName;
-            Nullable = true;
-            IndexType = ColumnIndexType.None;
-            DefaultEmptyForString = false;
-        }
-        else
+    public string ColumnName
+    {
+        get
         {
-            SqlColumnName = columnAttribute.DatabaseName ?? PropertyName;
-            Nullable = !columnAttribute.NonNullable;
-            IndexType = columnAttribute.IndexType;
-            DefaultEmptyForString = columnAttribute.DefaultEmptyForString;
+            object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.ColumnName);
+            Debug.Assert(value is null || value is string);
+            return (string?)value ?? PropertyInfo?.Name ?? throw new Exception("Failed to get column name.");
         }
-
-        ColumnTypeInfo = typeRegistry.GetRequired(PropertyRealType);
-        TypeRegistry = typeRegistry;
     }
 
-    public Type EntityType { get; }
-    // If null, there is no corresponding property.
-    public PropertyInfo? PropertyInfo { get; } = null;
-    public string PropertyName { get; }
-    public Type PropertyType { get; }
-    public Type PropertyRealType { get; }
-    public string SqlColumnName { get; }
-    public ColumnTypeRegistry TypeRegistry { get; set; }
-    public ColumnTypeInfo ColumnTypeInfo { get; }
-    public bool Nullable { get; }
-    public bool IsPrimaryKey { get; }
-    public bool IsAutoIncrement { get; }
-    public ColumnIndexType IndexType { get; }
-
-    // TODO: Implement this behavior.
-    public bool DefaultEmptyForString { get; }
+    public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true;
+    public bool IsAutoIncrement => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsAutoIncrement) is true;
+    public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true;
 
-    public string SqlType => ColumnTypeInfo.SqlType;
+    public ColumnIndexType Index => Metadata.GetValueOrDefault<ColumnIndexType?>(ColumnMetadataKeys.Index) ?? ColumnIndexType.None;
 
-    public string GenerateCreateTableColumnString()
+    public string GenerateCreateTableColumnString(string? dbProviderId = null)
     {
         StringBuilder result = new StringBuilder();
-        result.Append(SqlColumnName);
+        result.Append(ColumnName);
         result.Append(' ');
-        result.Append(SqlType);
+        result.Append(ColumnType.GetSqlTypeString(dbProviderId));
         if (IsPrimaryKey)
         {
             result.Append(' ');
             result.Append("PRIMARY KEY");
         }
-        else if (!Nullable)
+        else if (IsNotNull)
         {
             result.Append(' ');
             result.Append(" NOT NULL");
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
index c31a13e..05ee269 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs
@@ -1,8 +1,50 @@
 namespace CrupestApi.Commons.Crud;
 
+public static class ColumnMetadataKeys
+{
+    public const string ColumnName = nameof(ColumnAttribute.ColumnName);
+    public const string NotNull = nameof(ColumnAttribute.NotNull);
+    public const string IsPrimaryKey = nameof(ColumnAttribute.IsPrimaryKey);
+    public const string IsAutoIncrement = nameof(ColumnAttribute.IsAutoIncrement);
+    public const string Index = nameof(ColumnAttribute.Index);
+    public const string DefaultEmptyForString = nameof(ColumnAttribute.DefaultEmptyForString);
+}
+
 public interface IColumnMetadata
 {
+    bool TryGetValue(string key, out object? value);
+
+    object? GetValueOrDefault(string key)
+    {
+        if (TryGetValue(key, out var value))
+        {
+            return value;
+        }
+        else
+        {
+            return null;
+        }
+    }
 
+    T? GetValueOrDefault<T>(string key)
+    {
+        return (T?)GetValueOrDefault(key);
+    }
+
+    object? this[string key]
+    {
+        get
+        {
+            if (TryGetValue(key, out var value))
+            {
+                return value;
+            }
+            else
+            {
+                throw new KeyNotFoundException("Key not found.");
+            }
+        }
+    }
 }
 
 public enum ColumnIndexType
@@ -16,19 +58,80 @@ public enum ColumnIndexType
 public class ColumnAttribute : Attribute, IColumnMetadata
 {
     // if null, use the property name.
-    public string? DatabaseName { get; set; }
+    public string? ColumnName { get; init; }
 
     // default false.
-    public bool NonNullable { get; set; }
+    public bool NotNull { get; init; }
 
     // default false
-    public bool IsPrimaryKey { get; set; }
+    public bool IsPrimaryKey { get; init; }
 
     // default false
-    public bool IsAutoIncrement { get; set; }
+    public bool IsAutoIncrement { get; init; }
 
-    public ColumnIndexType IndexType { get; set; } = ColumnIndexType.None;
+    // default None
+    public ColumnIndexType Index { get; init; } = ColumnIndexType.None;
 
     // Use empty string for default value of string type.
-    public bool DefaultEmptyForString { get; set; }
+    public bool DefaultEmptyForString { get; init; }
+
+    public bool TryGetValue(string key, out object? value)
+    {
+        var property = GetType().GetProperty(key);
+        if (property is null)
+        {
+            value = null;
+            return false;
+        }
+        value = property.GetValue(this);
+        return true;
+    }
+}
+
+public class AggregateColumnMetadata : IColumnMetadata
+{
+    private IDictionary<string, object?> _own = new Dictionary<string, object?>();
+    private IList<IColumnMetadata> _children = new List<IColumnMetadata>();
+
+    public void Add(string key, object? value)
+    {
+        _own[key] = value;
+    }
+
+    public void Remove(string key)
+    {
+        _own.Remove(key);
+    }
+
+    public void Add(IColumnMetadata child)
+    {
+        _children.Add(child);
+    }
+
+    public void Remove(IColumnMetadata child)
+    {
+        _children.Remove(child);
+    }
+
+    public bool TryGetValue(string key, out object? value)
+    {
+        if (_own.ContainsKey(key))
+        {
+            value = _own[key];
+            return true;
+        }
+
+        bool found = false;
+        value = null;
+        foreach (var child in _children)
+        {
+            if (child.TryGetValue(key, out var tempValue))
+            {
+                value = tempValue;
+                found = true;
+            }
+        }
+
+        return found;
+    }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
index 4e640ff..679cb4c 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnTypeInfo.cs
@@ -1,259 +1,46 @@
-using System.Collections.Generic;
 using System.Data;
 using System.Diagnostics;
-using System.Text.Json;
 using System.Text.Json.Serialization;
 
 namespace CrupestApi.Commons.Crud;
 
-/// <summary> Represents a type of one column. </summary>
-public abstract class ColumnTypeInfo
+public interface IColumnTypeInfo
 {
-    protected ColumnTypeInfo(Type supportedType)
-    {
-        SupportedType = supportedType;
-    }
+    Type ClrType { get; }
+    Type DatabaseClrType { get; }
+    DbType DbType { get; }
 
-    public Type SupportedType { get; }
-
-    public bool IsOfSupportedType(object value)
+    string GetSqlTypeString(string? dbProviderId = null)
     {
-        return value is not null && SupportedType.IsAssignableFrom(value.GetType());
+        // Default implementation for SQLite
+        return DbType switch
+        {
+            DbType.String => "TEXT",
+            DbType.Int16 or DbType.Int32 or DbType.Int64 => "INTEGER",
+            DbType.Double => "REAL",
+            DbType.Binary => "BLOB",
+            _ => throw new Exception($"Unsupported DbType: {DbType}"),
+        };
     }
 
-    public abstract BasicColumnTypeInfo UnderlineType { get; }
-
-    public abstract IReadOnlyList<DerivedColumnTypeInfo> DerivedTypes { get; }
-
-    public abstract string SqlType { get; }
-
-    public abstract DbType DbType { get; }
-
-    /// <summary>
-    /// An optional json converter for this type.
-    /// </summary>
-    /// <returns>The converter if this type needs a json converter. Otherwise null.</returns>
-    public abstract JsonConverter? GetJsonConverter();
-
-    /// <summary>
-    /// Convert a value into underline type. 
-    /// </summary>
-    public abstract object? ConvertToUnderline(object? value);
-
-    /// <summary>
-    /// Convert to a value of this type from value of underline type.
-    /// </summary>
-    public abstract object? ConvertFromUnderline(object? underlineValue);
-}
-
-public class BasicColumnTypeInfo : ColumnTypeInfo
-{
-    public static BasicColumnTypeInfo<char> CharColumnTypeInfo { get; } = new BasicColumnTypeInfo<char>("INTEGER", DbType.Int32);
-    public static BasicColumnTypeInfo<short> ShortColumnTypeInfo { get; } = new BasicColumnTypeInfo<short>("INTEGER", DbType.Int32);
-    public static BasicColumnTypeInfo<int> IntColumnTypeInfo { get; } = new BasicColumnTypeInfo<int>("INTEGER", DbType.Int32);
-    public static BasicColumnTypeInfo<long> LongColumnTypeInfo { get; } = new BasicColumnTypeInfo<long>("INTEGER", DbType.Int64);
-    public static BasicColumnTypeInfo<float> FloatColumnTypeInfo { get; } = new BasicColumnTypeInfo<float>("REAL", DbType.Double);
-    public static BasicColumnTypeInfo<double> DoubleColumnTypeInfo { get; } = new BasicColumnTypeInfo<double>("REAL", DbType.Double);
-    public static BasicColumnTypeInfo<string> StringColumnTypeInfo { get; } = new BasicColumnTypeInfo<string>("TEXT", DbType.String);
-    public static BasicColumnTypeInfo<byte[]> ByteColumnTypeInfo { get; } = new BasicColumnTypeInfo<byte[]>("BLOB", DbType.Binary);
-
-    private readonly string _sqlType;
-    private readonly DbType _dbType;
-    internal List<DerivedColumnTypeInfo> _derivedTypes = new List<DerivedColumnTypeInfo>();
+    JsonConverter? JsonConverter { get; }
 
-    public BasicColumnTypeInfo(Type type, string sqlType, DbType dbType)
-    : base(type)
+    // You must override this method if ClrType != DatabaseClrType
+    object? ConvertFromDatabase(object? databaseValue)
     {
-        _sqlType = sqlType;
-        _dbType = dbType;
+        Debug.Assert(ClrType == DatabaseClrType);
+        return databaseValue;
     }
 
-    public override BasicColumnTypeInfo UnderlineType => this;
-
-    public override IReadOnlyList<DerivedColumnTypeInfo> DerivedTypes => _derivedTypes;
-
-    public override string SqlType => _sqlType;
-
-    public override DbType DbType => _dbType;
-
-    public override object? ConvertToUnderline(object? value)
+    // You must override this method if ClrType != DatabaseClrType
+    object? ConvertToDatabase(object? value)
     {
-        Debug.Assert(value is null || SupportedType.IsInstanceOfType(value));
+        Debug.Assert(ClrType == DatabaseClrType);
         return value;
     }
-
-    public override object? ConvertFromUnderline(object? underlineValue)
-    {
-        Debug.Assert(underlineValue is null || SupportedType.IsInstanceOfType(underlineValue));
-        return underlineValue;
-    }
-
-    public override JsonConverter? GetJsonConverter()
-    {
-        return null;
-    }
-}
-
-public class BasicColumnTypeInfo<T> : BasicColumnTypeInfo
-{
-    public BasicColumnTypeInfo(string sqlType, DbType dbType) : base(typeof(T), sqlType, dbType) { }
-}
-
-public abstract class DerivedColumnTypeInfo : ColumnTypeInfo
-{
-    protected DerivedColumnTypeInfo(Type supportedType, BasicColumnTypeInfo underlineType)
-        : base(supportedType)
-    {
-        UnderlineType = underlineType;
-        UnderlineType._derivedTypes.Add(this);
-    }
-
-    public override BasicColumnTypeInfo UnderlineType { get; }
-
-    private static readonly List<DerivedColumnTypeInfo> _emptyList = new List<DerivedColumnTypeInfo>();
-
-    public override IReadOnlyList<DerivedColumnTypeInfo> DerivedTypes => _emptyList;
-
-    public override string SqlType => UnderlineType!.SqlType;
-
-    public override DbType DbType => UnderlineType!.DbType;
 }
 
-public class DateTimeColumnTypeInfo : DerivedColumnTypeInfo
+public interface IColumnTypeProvider
 {
-    private readonly DateTimeJsonConverter _jsonConverter = new DateTimeJsonConverter();
-
-    public DateTimeColumnTypeInfo()
-        : base(typeof(DateTime), BasicColumnTypeInfo.LongColumnTypeInfo)
-    {
-
-    }
-
-    public override JsonConverter GetJsonConverter()
-    {
-        return _jsonConverter;
-    }
-
-    public override object? ConvertToUnderline(object? value)
-    {
-        if (value is null) return null;
-
-        Debug.Assert(value is DateTime);
-        return new DateTimeOffset((DateTime)value).ToUnixTimeSeconds();
-    }
-
-    public override object? ConvertFromUnderline(object? underlineValue)
-    {
-        if (underlineValue is null) return null;
-
-        Debug.Assert(typeof(long).IsAssignableFrom(underlineValue.GetType()));
-        return DateTimeOffset.FromUnixTimeSeconds((long)underlineValue).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 ColumnTypeRegistry
-{
-    public static IReadOnlyList<BasicColumnTypeInfo> BasicTypeList = new List<BasicColumnTypeInfo>()
-    {
-        BasicColumnTypeInfo.CharColumnTypeInfo,
-        BasicColumnTypeInfo.ShortColumnTypeInfo,
-        BasicColumnTypeInfo.IntColumnTypeInfo,
-        BasicColumnTypeInfo.LongColumnTypeInfo,
-        BasicColumnTypeInfo.FloatColumnTypeInfo,
-        BasicColumnTypeInfo.DoubleColumnTypeInfo,
-        BasicColumnTypeInfo.StringColumnTypeInfo,
-        BasicColumnTypeInfo.ByteColumnTypeInfo,
-    };
-
-    public static ColumnTypeRegistry Instance { get; }
-
-    static ColumnTypeRegistry()
-    {
-        Instance = new ColumnTypeRegistry();
-
-        foreach (var basicColumnTypeInfo in BasicTypeList)
-        {
-            Instance.Register(basicColumnTypeInfo);
-        }
-
-        Instance.Register(new DateTimeColumnTypeInfo());
-    }
-
-    private readonly List<ColumnTypeInfo> _list;
-    private readonly Dictionary<Type, ColumnTypeInfo> _map;
-
-    public ColumnTypeRegistry()
-    {
-        _list = new List<ColumnTypeInfo>();
-        _map = new Dictionary<Type, ColumnTypeInfo>();
-    }
-
-    public void Register(ColumnTypeInfo columnTypeInfo)
-    {
-        Debug.Assert(!_list.Contains(columnTypeInfo));
-        Debug.Assert(!_map.ContainsKey(columnTypeInfo.SupportedType));
-        _list.Add(columnTypeInfo);
-        _map.Add(columnTypeInfo.SupportedType, columnTypeInfo);
-    }
-
-    public ColumnTypeInfo? Get(Type type)
-    {
-        return _map.GetValueOrDefault(type);
-    }
-
-    public ColumnTypeInfo? Get<T>()
-    {
-        return Get(typeof(T));
-    }
-
-    public ColumnTypeInfo GetRequired(Type type)
-    {
-        return Get(type) ?? throw new Exception("Unsupported type.");
-    }
-
-    public ColumnTypeInfo GetRequired<T>()
-    {
-        return GetRequired(typeof(T));
-    }
-
-    public object? ConvertToUnderline(object? value)
-    {
-        if (value is null) return null;
-
-        var type = value.GetType();
-        var columnTypeInfo = Get(type);
-        if (columnTypeInfo is null) throw new Exception("Unsupported type.");
-
-        return columnTypeInfo.ConvertToUnderline(value);
-    }
-
-    public IEnumerable<JsonConverter> GetJsonConverters()
-    {
-        foreach (var columnTypeInfo in _list)
-        {
-            var converter = columnTypeInfo.GetJsonConverter();
-            if (converter is not null)
-                yield return converter;
-        }
-    }
+    IColumnTypeInfo Get(Type clrType);
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs
deleted file mode 100644
index 77b3c66..0000000
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DatabaseInternalException.cs
+++ /dev/null
@@ -1,12 +0,0 @@
-namespace CrupestApi.Commons.Crud;
-
-[System.Serializable]
-public class DatabaseInternalException : System.Exception
-{
-    public DatabaseInternalException() { }
-    public DatabaseInternalException(string message) : base(message) { }
-    public DatabaseInternalException(string message, System.Exception inner) : base(message, inner) { }
-    protected DatabaseInternalException(
-        System.Runtime.Serialization.SerializationInfo info,
-        System.Runtime.Serialization.StreamingContext context) : base(info, context) { }
-}
\ No newline at end of file
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs
new file mode 100644
index 0000000..956206d
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs
@@ -0,0 +1,41 @@
+using System.Data;
+using System.Diagnostics;
+using Dapper;
+
+namespace CrupestApi.Commons.Crud;
+
+public static class DynamicParametersExtensions
+{
+    private static Random random = new Random();
+    private const string chars = "abcdefghijklmnopqrstuvwxyz";
+
+    public static string GenerateRandomKey(int length)
+    {
+        lock (random)
+        {
+            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 = GenerateRandomKey(10);
+        int retryTimes = 1;
+        while (parameters.ParameterNames.Contains(parameterName))
+        {
+            retryTimes++;
+            Debug.Assert(retryTimes <= 100);
+            parameterName = GenerateRandomKey(10);
+        }
+        return parameterName;
+    }
+
+    public static string AddRandomNameParameter(this DynamicParameters parameters, object? value)
+    {
+        var parameterName = GenerateRandomParameterName(parameters);
+        parameters.Add(parameterName, value);
+        return parameterName;
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs
new file mode 100644
index 0000000..964a669
--- /dev/null
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/IClause.cs
@@ -0,0 +1,24 @@
+using Dapper;
+
+namespace CrupestApi.Commons.Crud;
+
+public interface IClause
+{
+    IEnumerable<IClause> GetSubclauses()
+    {
+        return Enumerable.Empty<IClause>();
+    }
+
+    IEnumerable<string> GetRelatedColumns()
+    {
+        var subclauses = GetSubclauses();
+        var result = new List<string>();
+        foreach (var subclause in subclauses)
+        {
+            var columns = subclause.GetRelatedColumns();
+            if (columns is not null)
+                result.AddRange(columns);
+        }
+        return result;
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
index 35b7cc9..b5f9f38 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs
@@ -11,35 +11,21 @@ public class InsertItem
         Value = value;
     }
 
-    public InsertItem(KeyValuePair<string, object?> pair)
-    {
-        ColumnName = pair.Key;
-        Value = pair.Value;
-    }
-
     public string ColumnName { get; set; }
     public object? Value { get; set; }
+}
 
-    public static implicit operator KeyValuePair<string, object?>(InsertItem item)
-    {
-        return new(item.ColumnName, item.Value);
-    }
-
-    public static implicit operator InsertItem(KeyValuePair<string, object?> pair)
-    {
-        return new(pair);
-    }
+public interface IInsertClause : IClause
+{
+    List<InsertItem> Items { get; }
+    string GenerateColumnListSql(string? dbProviderId = null);
+    (string sql, DynamicParameters parameters) GenerateValueListSql(string? dbProviderId = null);
 }
 
-public class InsertClause
+public class InsertClause : IInsertClause
 {
     public List<InsertItem> Items { get; } = new List<InsertItem>();
 
-    public InsertClause(IEnumerable<InsertItem> items)
-    {
-        Items.AddRange(items);
-    }
-
     public InsertClause(params InsertItem[] items)
     {
         Items.AddRange(items);
@@ -66,13 +52,14 @@ public class InsertClause
         return Items.Select(i => i.ColumnName).ToList();
     }
 
-    public string GenerateColumnListSql()
+    public string GenerateColumnListSql(string? dbProviderId = null)
     {
         return string.Join(", ", Items.Select(i => i.ColumnName));
     }
 
-    public string GenerateValueListSql(DynamicParameters parameters)
+    public (string sql, DynamicParameters parameters) GenerateValueListSql(string? dbProviderId = null)
     {
+        var parameters = new DynamicParameters();
         var sb = new StringBuilder();
         for (var i = 0; i < Items.Count; i++)
         {
@@ -83,6 +70,6 @@ public class InsertClause
                 sb.Append(", ");
         }
 
-        return sb.ToString();
+        return (sb.ToString(), parameters);
     }
 }
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
index bd4f300..68b5d60 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs
@@ -1,3 +1,5 @@
+using Dapper;
+
 namespace CrupestApi.Commons.Crud;
 
 public class OrderByItem
@@ -17,16 +19,19 @@ public class OrderByItem
     }
 }
 
-public class OrderByClause : List<OrderByItem>
+public interface IOrderByClause : IClause
 {
-    public OrderByClause(IEnumerable<OrderByItem> items)
-        : base(items)
-    {
-    }
+    List<OrderByItem> Items { get; }
+    (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null);
+}
+
+public class OrderByClause : IOrderByClause
+{
+    public List<OrderByItem> Items { get; } = new List<OrderByItem>();
 
     public OrderByClause(params OrderByItem[] items)
-        : base(items)
     {
+        Items.AddRange(items);
     }
 
     public static OrderByClause Create(params OrderByItem[] items)
@@ -34,8 +39,13 @@ public class OrderByClause : List<OrderByItem>
         return new OrderByClause(items);
     }
 
-    public string GenerateSql()
+    public List<string> GetRelatedColumns()
     {
-        return "ORDER BY " + string.Join(", ", this.Select(i => i.GenerateSql()));
+        return Items.Select(x => x.ColumnName).ToList();
     }
-}
\ No newline at end of file
+
+    public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null)
+    {
+        return ("ORDER BY " + string.Join(", ", Items.Select(i => i.GenerateSql())), new DynamicParameters());
+    }
+}
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
index ac02226..103442c 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs
@@ -1,3 +1,4 @@
+using System.Data;
 using System.Text;
 using Dapper;
 using Microsoft.Data.Sqlite;
@@ -6,17 +7,18 @@ namespace CrupestApi.Commons.Crud;
 
 public class TableInfo
 {
+    private readonly IColumnTypeProvider _columnTypeProvider;
     private readonly Lazy<List<string>> _lazyColumnNameList;
 
-    // For custom name.
-    public TableInfo(Type entityType)
-        : this(entityType.Name, entityType)
+    public TableInfo(Type entityType, IColumnTypeProvider columnTypeProvider)
+        : this(entityType.Name, entityType, columnTypeProvider)
     {
 
     }
 
-    public TableInfo(string tableName, Type entityType)
+    public TableInfo(string tableName, Type entityType, IColumnTypeProvider columnTypeProvider)
     {
+        _columnTypeProvider = columnTypeProvider;
         TableName = tableName;
         EntityType = entityType;
 
@@ -29,11 +31,11 @@ public class TableInfo
 
         foreach (var property in properties)
         {
-            var columnInfo = new ColumnInfo(entityType, property.Name);
+            var columnInfo = new ColumnInfo(property, _columnTypeProvider);
             columnInfos.Add(columnInfo);
             if (columnInfo.IsPrimaryKey)
                 hasPrimaryKey = true;
-            if (columnInfo.SqlColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
+            if (columnInfo.ColumnName.Equals("id", StringComparison.OrdinalIgnoreCase))
             {
                 hasId = true;
             }
@@ -42,7 +44,7 @@ public class TableInfo
         if (!hasPrimaryKey)
         {
             if (hasId) throw new Exception("A column named id already exists but is not primary key.");
-            var columnInfo = new ColumnInfo(entityType, "id", true, true, ColumnTypeRegistry.Instance.GetRequired<int>());
+            var columnInfo = CreateAutoIdColumn();
             columnInfos.Add(columnInfo);
         }
 
@@ -53,6 +55,19 @@ public class TableInfo
         _lazyColumnNameList = new Lazy<List<string>>(() => ColumnInfos.Select(c => c.SqlColumnName).ToList());
     }
 
+    private ColumnInfo CreateAutoIdColumn()
+    {
+        return new ColumnInfo(EntityType,
+                new ColumnAttribute
+                {
+                    ColumnName = "Id",
+                    NotNull = true,
+                    IsPrimaryKey = true,
+                    IsAutoIncrement = true,
+                },
+            typeof(long), _columnTypeProvider);
+    }
+
     public Type EntityType { get; }
     public string TableName { get; }
     public IReadOnlyList<ColumnInfo> ColumnInfos { get; }
@@ -78,30 +93,30 @@ public class TableInfo
 
         foreach (var column in ColumnInfos)
         {
-            if (sqlNameSet.Contains(column.SqlColumnName))
-                throw new Exception($"Two columns have the same sql name '{column.SqlColumnName}'.");
-            sqlNameSet.Add(column.SqlColumnName);
+            if (sqlNameSet.Contains(column.ColumnName))
+                throw new Exception($"Two columns have the same sql name '{column.ColumnName}'.");
+            sqlNameSet.Add(column.ColumnName);
         }
     }
 
-    public string GenerateCreateIndexSql()
+    public string GenerateCreateIndexSql(string? dbProviderId = null)
     {
         var sb = new StringBuilder();
 
         foreach (var column in ColumnInfos)
         {
-            if (column.IndexType == ColumnIndexType.None) continue;
+            if (column.Index == ColumnIndexType.None) continue;
 
-            sb.Append($"CREATE {(column.IndexType == ColumnIndexType.Unique ? "UNIQUE" : "")} INDEX {TableName}_{column.SqlColumnName}_index ON {TableName} ({column.SqlColumnName});\n");
+            sb.Append($"CREATE {(column.Index == ColumnIndexType.Unique ? "UNIQUE" : "")} INDEX {TableName}_{column.ColumnName}_index ON {TableName} ({column.ColumnName});\n");
         }
 
         return sb.ToString();
     }
 
-    public string GenerateCreateTableSql(bool createIndex = true)
+    public string GenerateCreateTableSql(bool createIndex = true, string? dbProviderId = null)
     {
         var tableName = TableName;
-        var columnSql = string.Join(",\n", ColumnInfos.Select(c => c.GenerateCreateTableColumnString()));
+        var columnSql = string.Join(",\n", ColumnInfos.Select(c => c.GenerateCreateTableColumnString(dbProviderId)));
 
         var sql = $@"
 CREATE TABLE {tableName}(
@@ -111,25 +126,25 @@ CREATE TABLE {tableName}(
 
         if (createIndex)
         {
-            sql += GenerateCreateIndexSql();
+            sql += GenerateCreateIndexSql(dbProviderId);
         }
 
         return sql;
     }
 
-    public async Task<bool> CheckExistence(SqliteConnection connection)
+    public bool CheckExistence(IDbConnection connection)
     {
         var tableName = TableName;
-        var count = (await connection.QueryAsync<int>(
+        var count = connection.QuerySingle<int>(
             @"SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND tbl_name = @TableName;",
-            new { TableName = tableName })).Single();
+            new { TableName = tableName });
         if (count == 0)
         {
             return false;
         }
         else if (count > 1)
         {
-            throw new DatabaseInternalException($"More than 1 table has name {tableName}. What happened?");
+            throw new Exception($"More than 1 table has name {tableName}. What happened?");
         }
         else
         {
@@ -137,24 +152,27 @@ CREATE TABLE {tableName}(
         }
     }
 
-    public string GenerateSelectSql(WhereClause? whereClause, OrderByClause? orderByClause, int? skip, int? limit, out DynamicParameters parameters)
+    public void CheckRelatedColumns(IClause? clause)
     {
-        if (whereClause is not null)
+        if (clause is not null)
         {
-            var relatedFields = ((IWhereClause)whereClause).GetRelatedColumns();
-            if (relatedFields is not null)
+            var relatedColumns = clause.GetRelatedColumns();
+            foreach (var column in relatedColumns)
             {
-                foreach (var field in relatedFields)
+                if (!ColumnNameList.Contains(column))
                 {
-                    if (!ColumnNameList.Contains(field))
-                    {
-                        throw new ArgumentException($"Field {field} is not in the table.");
-                    }
+                    throw new ArgumentException($"Column {column} is not in the table.");
                 }
             }
         }
+    }
 
-        parameters = new DynamicParameters();
+    public (string sql, DynamicParameters parameters) GenerateSelectSql(IWhereClause? whereClause, IOrderByClause? orderByClause = null, int? skip = null, int? limit = null, string? dbProviderId = null)
+    {
+        CheckRelatedColumns(whereClause);
+        CheckRelatedColumns(orderByClause);
+
+        var parameters = new DynamicParameters();
 
         StringBuilder result = new StringBuilder()
             .Append("SELECT * FROM ")
@@ -163,16 +181,19 @@ CREATE TABLE {tableName}(
         if (whereClause is not null)
         {
             result.Append(' ');
-            result.Append(whereClause.GenerateSql(parameters));
+            var (whereSql, whereParameters) = whereClause.GenerateSql(dbProviderId);
+            parameters.AddDynamicParams(whereParameters);
+            result.Append(whereSql);
         }
 
         if (orderByClause is not null)
         {
             result.Append(' ');
-            result.Append(orderByClause.GenerateSql());
+            var (orderBySql, orderByParameters) = orderByClause.GenerateSql(dbProviderId);
+            parameters.AddDynamicParams(orderByClause);
+            result.Append(orderBySql);
         }
 
-
         if (limit is not null)
         {
             result.Append(" LIMIT @Limit");
@@ -187,7 +208,7 @@ CREATE TABLE {tableName}(
 
         result.Append(';');
 
-        return result.ToString();
+        return (result.ToString(), parameters);
     }
 
     public InsertClause GenerateInsertClauseFromObject(object value)
@@ -196,11 +217,7 @@ CREATE TABLE {tableName}(
 
         foreach (var column in ColumnInfos)
         {
-            var propertyInfo = column.PropertyInfo;
-            if (propertyInfo is null)
-            {
-                propertyInfo = EntityType.GetProperty(column.PropertyName);
-            }
+            var propertyInfo = EntityType.GetProperty(column.ColumnName);
             if (propertyInfo is null)
             {
                 if (column.IsAutoIncrement)
@@ -209,7 +226,7 @@ CREATE TABLE {tableName}(
                 }
                 else
                 {
-                    throw new Exception($"Property {column.PropertyName} not found.");
+                    throw new Exception($"Property {column.ColumnName} not found.");
                 }
             }
 
@@ -222,7 +239,7 @@ CREATE TABLE {tableName}(
                 }
                 else
                 {
-                    insertClause.Add(column.SqlColumnName, propertyValue);
+                    insertClause.Add(column.ColumnName, propertyValue);
                 }
             }
         }
@@ -230,36 +247,33 @@ CREATE TABLE {tableName}(
         return insertClause;
     }
 
-    public string GenerateInsertSql(InsertClause insertClause, out DynamicParameters parameters)
+    public (string sql, DynamicParameters parameters) GenerateInsertSql(IInsertClause insertClause, string? dbProviderId = null)
     {
-        var relatedColumns = insertClause.GetRelatedColumns();
-        foreach (var column in relatedColumns)
-        {
-            if (!ColumnNameList.Contains(column))
-            {
-                throw new ArgumentException($"Column {column} is not in the table.");
-            }
-        }
+        CheckRelatedColumns(insertClause);
 
-        parameters = new DynamicParameters();
+        var parameters = new DynamicParameters();
 
         var result = new StringBuilder()
             .Append("INSERT INTO ")
             .Append(TableName)
             .Append(" (")
-            .Append(insertClause.GenerateColumnListSql())
-            .Append(") VALUES (")
-            .Append(insertClause.GenerateValueListSql(parameters))
-            .Append(");");
+            .Append(insertClause.GenerateColumnListSql(dbProviderId))
+            .Append(") VALUES (");
+
+        var (valueSql, valueParameters) = insertClause.GenerateValueListSql(dbProviderId);
+        result.Append(valueSql).Append(");");
+
+        parameters.AddDynamicParams(valueParameters);
 
-        return result.ToString();
+        return (result.ToString(), parameters);
     }
 
-    public string GenerateUpdateSql(WhereClause? whereClause, UpdateClause updateClause, out DynamicParameters parameters)
+    // TODO: Continue...
+    public string GenerateUpdateSql(IWhereClause? whereClause, UpdateClause updateClause)
     {
         var relatedColumns = new HashSet<string>();
         if (whereClause is not null)
-            relatedColumns.UnionWith(((IWhereClause)whereClause).GetRelatedColumns() ?? Enumerable.Empty<string>());
+            relatedColumns.UnionWith(((IClause)whereClause).GetRelatedColumns() ?? Enumerable.Empty<string>());
         relatedColumns.UnionWith(updateClause.GetRelatedColumns());
         foreach (var column in relatedColumns)
         {
@@ -289,7 +303,7 @@ CREATE TABLE {tableName}(
     {
         if (whereClause is not null)
         {
-            var relatedColumns = ((IWhereClause)whereClause).GetRelatedColumns() ?? new List<string>();
+            var relatedColumns = ((IClause)whereClause).GetRelatedColumns() ?? new List<string>();
             foreach (var column in relatedColumns)
             {
                 if (!ColumnNameList.Contains(column))
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
index 0997656..7cb5edf 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs
@@ -11,26 +11,11 @@ public class UpdateItem
         Value = value;
     }
 
-    public UpdateItem(KeyValuePair<string, object?> pair)
-    {
-        ColumnName = pair.Key;
-        Value = pair.Value;
-    }
-
     public string ColumnName { get; set; }
     public object? Value { get; set; }
-
-    public static implicit operator KeyValuePair<string, object?>(UpdateItem item)
-    {
-        return new(item.ColumnName, item.Value);
-    }
-
-    public static implicit operator UpdateItem(KeyValuePair<string, object?> pair)
-    {
-        return new(pair);
-    }
 }
 
+// TODO: Continue...
 public class UpdateClause
 {
     public List<UpdateItem> Items { get; } = new List<UpdateItem>();
diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
index 26dc306..8a0b7ac 100644
--- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
+++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs
@@ -1,128 +1,108 @@
 using System.Data;
-using System.Diagnostics;
+using System.Text;
 using Dapper;
 
 namespace CrupestApi.Commons.Crud;
 
-public interface IWhereClause
+public interface IWhereClause : IClause
 {
-    string GenerateSql(DynamicParameters parameters);
+    (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null);
+}
 
-    IEnumerable<IWhereClause>? GetSubclauses()
+public class CompositeWhereClause : IWhereClause
+{
+    public CompositeWhereClause(string concatOp, bool parenthesesSubclause, params IWhereClause[] subclauses)
     {
-        return null;
+        ConcatOp = concatOp;
+        ParenthesesSubclause = parenthesesSubclause;
+        Subclauses = subclauses;
     }
 
-    IEnumerable<string>? GetRelatedColumns()
+    public string ConcatOp { get; }
+    public bool ParenthesesSubclause { get; }
+    public IWhereClause[] Subclauses { get; }
+
+    public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null)
     {
+        var parameters = new DynamicParameters();
+        var sql = new StringBuilder();
         var subclauses = GetSubclauses();
-        if (subclauses is null) return null;
-        var result = new List<string>();
-        foreach (var subclause in subclauses)
+        if (subclauses is null) return ("", parameters);
+        var first = true;
+        foreach (var subclause in Subclauses)
         {
-            var columns = subclause.GetRelatedColumns();
-            if (columns is not null)
-                result.AddRange(columns);
+            var (subSql, subParameters) = subclause.GenerateSql(dbProviderId);
+            if (subSql is null) continue;
+            if (first)
+            {
+                first = false;
+            }
+            else
+            {
+                sql.Append($" {ConcatOp} ");
+            }
+            if (ParenthesesSubclause)
+            {
+                sql.Append("(");
+            }
+            sql.Append(subSql);
+            if (ParenthesesSubclause)
+            {
+                sql.Append(")");
+            }
+            parameters.AddDynamicParams(subParameters);
         }
-        return result;
+        return (sql.ToString(), parameters);
     }
 
-    public static string RandomKey(int length)
+    public object GetSubclauses()
     {
-        // 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;
+        return Subclauses;
     }
 }
 
-public static class DynamicParametersExtensions
+public class AndWhereClause : CompositeWhereClause
 {
-    public static string AddRandomNameParameter(this DynamicParameters parameters, object? value)
+    public AndWhereClause(params IWhereClause[] clauses)
+    : this(true, clauses)
     {
-        var parameterName = IWhereClause.GenerateRandomParameterName(parameters);
-        parameters.Add(parameterName, ColumnTypeRegistry.Instance.ConvertToUnderline(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)
+    public AndWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
+    : base("AND", parenthesesSubclause, 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 class OrWhereClause : CompositeWhereClause
 {
-    public List<IWhereClause> Clauses { get; } = new List<IWhereClause>();
-
-    public IEnumerable<IWhereClause> GetSubclauses()
+    public OrWhereClause(params IWhereClause[] clauses)
+        : this(true, clauses)
     {
-        return Clauses;
-    }
 
-    public OrWhereClause(IEnumerable<IWhereClause> clauses)
-    {
-        Clauses.AddRange(clauses);
     }
 
-    public OrWhereClause(params IWhereClause[] clauses)
+    public OrWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
+        : base("OR", parenthesesSubclause, 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
+// It's simple because it only compare column and value but not expressions.
+public class SimpleCompareWhereClause : IWhereClause
 {
     public string Column { get; }
     public string Operator { get; }
@@ -134,114 +114,52 @@ public class CompareWhereClause : IWhereClause
     }
 
     // It's user's responsibility to keep column safe, with proper escape.
-    public CompareWhereClause(string column, string @operator, object value)
+    public SimpleCompareWhereClause(string column, string op, object value)
     {
         Column = column;
-        Operator = @operator;
+        Operator = op;
         Value = value;
     }
 
-    public static CompareWhereClause Create(string column, string @operator, object value)
+    public static SimpleCompareWhereClause Create(string column, string op, object value)
     {
-        return new CompareWhereClause(column, @operator, value);
+        return new SimpleCompareWhereClause(column, op, value);
     }
 
-    public static CompareWhereClause Eq(string column, object value)
+    public static SimpleCompareWhereClause Eq(string column, object value)
     {
-        return new CompareWhereClause(column, "=", value);
+        return new SimpleCompareWhereClause(column, "=", value);
     }
 
-    public static CompareWhereClause Neq(string column, object value)
+    public static SimpleCompareWhereClause Neq(string column, object value)
     {
-        return new CompareWhereClause(column, "<>", value);
+        return new SimpleCompareWhereClause(column, "<>", value);
     }
 
-    public static CompareWhereClause Gt(string column, object value)
+    public static SimpleCompareWhereClause Gt(string column, object value)
     {
-        return new CompareWhereClause(column, ">", value);
+        return new SimpleCompareWhereClause(column, ">", value);
     }
 
-    public static CompareWhereClause Gte(string column, object value)
+    public static SimpleCompareWhereClause Gte(string column, object value)
     {
-        return new CompareWhereClause(column, ">=", value);
+        return new SimpleCompareWhereClause(column, ">=", value);
     }
 
-    public static CompareWhereClause Lt(string column, object value)
+    public static SimpleCompareWhereClause Lt(string column, object value)
     {
-        return new CompareWhereClause(column, "<", value);
+        return new SimpleCompareWhereClause(column, "<", value);
     }
 
-    public static CompareWhereClause Lte(string column, object value)
+    public static SimpleCompareWhereClause Lte(string column, object value)
     {
-        return new CompareWhereClause(column, "<=", value);
+        return new SimpleCompareWhereClause(column, "<=", value);
     }
 
-    public string GenerateSql(DynamicParameters parameters)
+    public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null)
     {
+        var parameters = new DynamicParameters();
         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 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));
-    }
-
-    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);
+        return ($"{Column} {Operator} @{parameterName}", parameters);
     }
 }