diff options
Diffstat (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons')
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); } } |