hermes-server/Store/GroupSaveSqlGenerator.cs

using System.Reflection;
using System.Text;
using HermesSocketLibrary.db;

namespace HermesSocketServer.Store
{
    public class GroupSaveSqlGenerator<T>
    {
        private readonly IDictionary<string, PropertyInfo?> columnPropertyRelations;
        private readonly Serilog.ILogger _logger;

        public GroupSaveSqlGenerator(IDictionary<string, string> columnsToProperties, Serilog.ILogger logger)
        {
            columnPropertyRelations = columnsToProperties.ToDictionary(p => p.Key, p => typeof(T).GetProperty(p.Value));
            _logger = logger;

            var nullProperties = columnPropertyRelations.Where(p => p.Value == null)
                .Select(p => columnsToProperties[p.Key]);
            if (nullProperties.Any())
                throw new ArgumentException("Some properties do not exist on the values given: " + string.Join(", ", nullProperties));
        }

        public async Task DoPreparedStatement<V>(Database database, string sql, IEnumerable<V> values, string[] columns)
        {
            try
            {
                await database.Execute(sql, (c) =>
                {
                    var valueCounter = 0;
                    foreach (var value in values)
                    {
                        foreach (var column in columns)
                        {
                            var propValue = columnPropertyRelations[column]!.GetValue(value);
                            c.Parameters.AddWithValue(column.ToLower() + valueCounter, propValue ?? DBNull.Value);
                        }
                        valueCounter++;
                    }
                });
            }
            catch (Exception ex)
            {
                _logger.Error(ex, "Failed to execute a prepared statement: " + sql);
            }
        }

        public async Task DoPreparedStatementRaw<V>(Database database, string sql, IEnumerable<V> values, string[] columns)
        {
            try
            {
                await database.Execute(sql, (c) =>
                {
                    var valueCounter = 0;
                    foreach (var value in values)
                    {
                        foreach (var column in columns)
                        {
                            object? propValue = value;
                            c.Parameters.AddWithValue(column.ToLower() + valueCounter, propValue ?? DBNull.Value);
                        }
                        valueCounter++;
                    }
                });
            }
            catch (Exception ex)
            {
                _logger.Error(ex, "Failed to execute a prepared statement: " + sql);
            }
        }

        public string GenerateInsertSql(string table, IEnumerable<T> values, IEnumerable<string> columns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (values == null)
                throw new ArgumentNullException(nameof(values));
            if (!values.Any())
                throw new ArgumentException("Empty list given.", nameof(values));
            if (columns == null)
                throw new ArgumentNullException(nameof(columns));
            if (!columns.Any())
                throw new ArgumentException("Empty list given.", nameof(columns));

            var ctp = columns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            sb.Append($"INSERT INTO \"{table}\" (\"{string.Join("\", \"", columns)}\") VALUES ");
            foreach (var value in values)
            {
                sb.Append("(");
                foreach (var column in columns)
                {
                    var propValue = columnPropertyRelations[column]!.GetValue(value);
                    var propType = columnPropertyRelations[column]!.PropertyType;
                    WriteValue(sb, propValue ?? DBNull.Value, propType);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append(';');
            return sb.ToString();
        }

        public string GeneratePreparedInsertSql(string table, int rows, IEnumerable<string> columns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (columns == null)
                throw new ArgumentNullException(nameof(columns));
            if (!columns.Any())
                throw new ArgumentException("Empty list given.", nameof(columns));

            var ctp = columns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            var columnsLower = columns.Select(c => c.ToLower());
            sb.Append($"INSERT INTO \"{table}\" (\"{string.Join("\", \"", columns)}\") VALUES ");
            for (var row = 0; row < rows; row++)
            {
                sb.Append("(");
                foreach (var column in columnsLower)
                {
                    sb.Append('@')
                        .Append(column)
                        .Append(row)
                        .Append(", ");
                }
                sb.Remove(sb.Length - 2, 2)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append(';');
            return sb.ToString();
        }

        public string GenerateUpdateSql(string table, IEnumerable<T> values, IEnumerable<string> keyColumns, IEnumerable<string> updateColumns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (values == null)
                throw new ArgumentNullException(nameof(values));
            if (!values.Any())
                throw new ArgumentException("Empty list given.", nameof(values));
            if (keyColumns == null)
                throw new ArgumentNullException(nameof(keyColumns));
            if (!keyColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(keyColumns));
            if (updateColumns == null)
                throw new ArgumentNullException(nameof(updateColumns));
            if (!updateColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(updateColumns));

            var columns = keyColumns.Union(updateColumns);
            var ctp = columns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            sb.Append($"UPDATE \"{table}\" as t SET {string.Join(", ", updateColumns.Select(c => "\"" + c + "\" = c.\"" + c + "\""))} FROM (VALUES ");
            foreach (var value in values)
            {
                sb.Append("(");
                foreach (var column in columns)
                {
                    var propValue = columnPropertyRelations[column]!.GetValue(value);
                    var propType = columnPropertyRelations[column]!.PropertyType;
                    WriteValue(sb, propValue, propType);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append($") AS c(\"{string.Join("\", \"", columns)}\") WHERE ")
                .Append(string.Join(" AND ", keyColumns.Select(c => "t.\"" + c + "\" = c.\"" + c + "\"")))
                .Append(";");

            return sb.ToString();
        }

        public string GeneratePreparedUpdateSql(string table, int rows, IEnumerable<string> keyColumns, IEnumerable<string> updateColumns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (keyColumns == null)
                throw new ArgumentNullException(nameof(keyColumns));
            if (!keyColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(keyColumns));
            if (updateColumns == null)
                throw new ArgumentNullException(nameof(updateColumns));
            if (!updateColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(updateColumns));

            var columns = keyColumns.Union(updateColumns);
            var ctp = columns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            sb.Append($"UPDATE \"{table}\" as t SET {string.Join(", ", updateColumns.Select(c => "\"" + c + "\" = c.\"" + c + "\""))} FROM (VALUES ");
            for (var row = 0; row < rows; row++)
            {
                sb.Append("(");
                foreach (var column in columns)
                {
                    sb.Append('@')
                        .Append(column)
                        .Append(row)
                        .Append(", ");
                }
                sb.Remove(sb.Length - 2, 2)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append($") AS c(\"{string.Join("\", \"", columns)}\") WHERE ")
                .Append(string.Join(" AND ", keyColumns.Select(c => "t.\"" + c + "\" = c.\"" + c + "\"")))
                .Append(";");

            return sb.ToString();
        }

        public string GenerateDeleteSql(string table, IEnumerable<string> keys, IEnumerable<string> keyColumns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (keys == null)
                throw new ArgumentNullException(nameof(keys));
            if (!keys.Any())
                throw new ArgumentException("Empty list given.", nameof(keys));
            if (keyColumns == null)
                throw new ArgumentNullException(nameof(keyColumns));
            if (!keyColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(keyColumns));

            var ctp = keyColumns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            sb.Append($"DELETE FROM \"{table}\" WHERE (\"{string.Join("\", \"", keyColumns)}\") IN (");
            foreach (var k in keys)
            {
                sb.Append("(");
                foreach (var column in keyColumns)
                {
                    var propType = columnPropertyRelations[column]!.PropertyType;
                    WriteValue(sb, k, propType);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append(");");

            return sb.ToString();
        }

        public string GeneratePreparedDeleteSql(string table, int rows, IEnumerable<string> keyColumns)
        {
            if (string.IsNullOrWhiteSpace(table))
                throw new ArgumentException("Value is either null or whitespace-filled.", nameof(table));
            if (keyColumns == null)
                throw new ArgumentNullException(nameof(keyColumns));
            if (!keyColumns.Any())
                throw new ArgumentException("Empty list given.", nameof(keyColumns));

            var ctp = keyColumns.ToDictionary(c => c, c => columnPropertyRelations[c]);
            var sb = new StringBuilder();
            sb.Append($"DELETE FROM \"{table}\" WHERE (\"{string.Join("\", \"", keyColumns)}\") IN (");
            for (var row = 0; row < rows; row++)
            {
                sb.Append("(");
                foreach (var column in keyColumns)
                {
                    sb.Append('@')
                        .Append(column)
                        .Append(row)
                        .Append(", ");
                }
                sb.Remove(sb.Length - 2, 2)
                    .Append("),");
            }
            sb.Remove(sb.Length - 1, 1)
                .Append(");");

            return sb.ToString();
        }

        private void WriteValue(StringBuilder sb, object? value, Type type)
        {
            if (type == typeof(string))
                sb.Append("'")
                    .Append(value)
                    .Append("'");
            else if (type == typeof(Guid))
                sb.Append("uuid('")
                    .Append(value?.ToString())
                    .Append("')");
            else if (type == typeof(TimeSpan))
                sb.Append(((TimeSpan)value).TotalMilliseconds);
            else
                sb.Append(value);
        }
    }
}