Sql.cs

// #define DRYRUN
#region Header
// Sql.cs\n Copyright (C) Adonis Deliannis, 2019\nCreated 26 08, 2019
// Place each class and interface in their respected files to clean up this file for easier reading, your prerogative.
#endregion

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using log4net;
using MySql.Data.MySqlClient;

namespace ToasterNetwork.MySql {
    
     public interface ISql {
        /// <summary>
        /// Inserts data into the specified table.
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="data"><see cref="KeyValuePair{TKey,TValue}"/> to insert into <see cref="table"/></param>
        /// <remarks>The <see cref="data"/> parameter must match the table's structure else it will throw an error.</remarks>
        Task InsertInto(string table, Dictionary<string, dynamic> data);

        /// <summary>
        /// Updates given data to the specified <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="newData"><see cref="KeyValuePair{TKey,TValue}"/> to update in the <see cref="table"/></param>
        /// <param name="queryColumn">The query to use for WHERE</param>
        /// <param name="queryData">The data to use for WHERE</param>
        /// <remarks>The <see cref="newData"/> parameter must match whatever you are updating else it will throw an error.
        /// <see cref="queryColumn"/> and <see cref="queryData"/> refer to the WHERE clause such as 'WHERE <see cref="queryColumn"/>=<see cref="queryData"/>'
        /// </remarks>
        Task Update(string table, Dictionary< string, dynamic > newData, string queryColumn, dynamic queryData);

        /// <summary>
        /// Removes the element by <see cref="index"/> from the <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="index">The index of the element you want to remove</param>
        Task Delete(string table, int index);

        /// <summary>
        /// Removes the element by <see cref="query"/> from the <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="query">The query specification to select the element you want to remove</param>
        Task Delete(string table, string query);

        /// <summary>
        /// Opens a connection
        /// </summary>
        Task Connect();

        /// <summary>
        /// Closes the connection
        /// </summary>
        Task Disconnect();

    }
    
    internal class Sql : ISql {
        private readonly SqlConfig _config;
        private MySqlConnection _con;
        private ILog _log = LogManager.GetLogger("SQL");

        public Sql(SqlConfig config) {
            _config = config;
            _con =
                new
                    MySqlConnection($"Server={_config.Server};Port={_config.Port};Database={_config.Database};Uid={_config.Username};Pwd={_config.Password};");
        }

        private async Task< int > Execute(string query) {
        #if DRYRUN
            _log.Info($"DRY: {query}");
            await Task.CompletedTask;
            return 0;
        #else
            _log.Info(query);
            var cmd = new MySqlCommand(query, _con);
            return await cmd.ExecuteNonQueryAsync();
        #endif
        }

        private async Task< DbDataReader > ExecuteReader(string query) {
            var cmd = new MySqlCommand(query, _con);
            return await cmd.ExecuteReaderAsync();
        }

        #region Implementation of ISql

        /// <inheritdoc />
        public async Task InsertInto(string table, Dictionary< string, dynamic > data) {
            // data.Values.Aggregate((x, y) => $"{x}, {y}");
            var sb = new StringBuilder();
            
            foreach (KeyValuePair<string,dynamic> k in data) {

                sb.Append(long.TryParse(Convert.ToString(k.Value), out long v)
                              ? $"`{k.Key}`={v},"
                              : $"`{k.Key}`='{k.Value}',");
            }

            await
                Execute($"INSERT INTO {table.EscapeString()} ({data.Keys.Aggregate((x, y) => $"{x.EscapeString()}, {y.EscapeString()}")}) VALUES ({data.Values.Aggregate((x, y) => $"{x}, {y}")})");
        }

        /// <inheritdoc />
        public async Task Update(string table, Dictionary< string, dynamic > newData, string queryColumn, dynamic queryValue) {
            var sb = new StringBuilder();
            foreach (KeyValuePair<string,dynamic> k in newData) {

                sb.Append(long.TryParse(Convert.ToString(k.Value), out long v)
                              ? $"`{k.Key}`={v},"
                              : $"`{k.Key}`='{k.Value}',");
            }
            string query =
                $"UPDATE {table.EscapeString()} set {sb.ToString().TrimEnd(",".ToCharArray())} WHERE `{queryColumn}`='{queryValue}'";
            _log.Info(query);
            await Execute(query);
        }

        /// <inheritdoc />
        public async Task Delete(string table, int index) {
            await Delete(table, $"INDEX=`{MySqlHelper.EscapeString(index.ToString())}`");
        }

        /// <inheritdoc />
        public async Task Delete(string table, string query) {

            await Execute($"DELETE FROM `{table}` WHERE `{query}`;");
        }

        public async Task< bool > Exists(string table, string column, dynamic entry) {
            bool ret = false;
            DbDataReader reader = await ExecuteReader($"SELECT * FROM `{table}` WHERE `{column}`='{entry}'");
            while (await reader.ReadAsync()) {
                dynamic ts = reader.GetFieldValueAsync< dynamic >(0);
                if (await ts != entry) continue;
                ret = true;
                break;
            }
            
            reader.Close();
            return ret;
        }

        /// <inheritdoc />
        public async Task Connect() {
            await _con.OpenAsync();
        }

        /// <inheritdoc />
        public async Task Disconnect() {
            await _con.CloseAsync();
        }

        public async Task< int > GetUsername() {
            DbDataReader db = await ExecuteReader($"SELECT `userid` FROM `userdb` WHERE `username`='{_config.Username}'");
            await db.ReadAsync();
            int ret = (int) db[ 0 ];
            db.Close();
            return ret;
        }

        #endregion

        // ReSharper disable CS8625
        public async Task< List< object > > GetEntry(string table, string column, string queryColumn = "",
                                                       dynamic queryValue = default) {
            string query = queryColumn.IsEmpty()
                ? $"SELECT {column} from `{table}`"
                : $"SELECT {column} from `{table}` where `{queryColumn}`='{queryValue}'";
            
            _log.Info(query);
            DbDataReader reader = await ExecuteReader(query);

            List< object > lst = new List< object >();
            await reader.ReadAsync();
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            lst.AddRange(values);
            // while (await reader.ReadAsync()) {
            //     object[] values = new object[reader.FieldCount];
            //     reader.GetValues(values);
            //     lst.Add(values);
            // }
            reader.Close();
            return lst;
        }
    }
    
        internal class BaseModuleConfig {
        [JsonProperty("enabled")]
        public bool Enabled { get; set; }

        [JsonProperty("suppress-errors")]
        public bool SuppressErrors { get; set; }
    }

    internal partial class Config : BaseModuleConfig {
        [JsonProperty("sql", Required = Required.Always)]
        public SqlConfig Sql { get; set; }
    }

    public class SqlConfig {
        [JsonProperty("server", Required = Required.Always)]
        public string Server { get; set; }

        [JsonProperty("port", Required = Required.Always)]
        public int Port { get; set; }

        [JsonProperty("username", Required = Required.Always)]
        public string Username { get; set; }

        [JsonProperty("password", Required = Required.Always)]
        public string Password { get; set; }

        [JsonProperty("database")]
        public string Database { get; set; }
    }
    
    internal static class Extensions {
         public static string EscapeString(this string s) => MySqlHelper.EscapeString(s);
    }
}