HyperCube/MySQLConnector.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using HyperCube.Models;
using MySql.Data.MySqlClient;
using Console = HyperCube.Utils.AdvConsole;

namespace HyperCube
{
    public class MySQLConnector
    {
        private MySQLConnector(){ }

        private readonly string Server = "dmatter.net";
        private readonly string DatabaseName = "documents";
        private readonly string UserName = "promsystem";
        private readonly string Password = "PrmSystem212";

        MySqlCommand SQLcom;

        public MySqlConnection Connection { get; set; }

        private static MySQLConnector _instance = null;
        public static MySQLConnector Instance()
        {
            if (_instance == null)
                _instance = new MySQLConnector();
            return _instance;
        }

        public async Task<bool> IsConnect()
        {           
            string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
            if (Connection != null)
                await SQLSelectComplex("select COUNT(*) from accounts", false);
            else
                Console.WriteLine($"SQL IsConnect Connection null");

            if (Connection == null || Connection.State != System.Data.ConnectionState.Open)
            {
                Console.WriteLine($"SQL IsConnect making NEW Connection!");
                Connection = new MySqlConnection(connstring);
                Connection.Open();
            }
            await Task.WhenAll();
            return true;
        }

        public async Task<long> SQLInsert(string sql)
        {
            Console.WriteLine($"SQLInsert {sql}");
            long lastID = 0;

            bool connected = await IsConnect();
            if (connected)
            {
                MySqlCommand SQLcom3 = new(sql, Connection);
                //SQLcom3.ExecuteNonQuery();
                await SQLcom3.ExecuteNonQueryAsync();
                lastID = SQLcom3.LastInsertedId;
                SQLcom3.Dispose();
                Console.WriteLine($"SQLInsert end");
            }
            else
                Console.WriteLine("Not connected to DB.");

            await Task.WhenAll();
            return lastID;
        }        

        public async Task<Dictionary<string, AccountModel>> SQLSelectASPUsers()
        {
            Console.WriteLine($"SQLSelectASPUsers");
            string sql = "select * from aspnetusers";
            Dictionary<string, AccountModel> accs = new();

            bool connected = await IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                MySqlDataReader rdr = SQLcom.ExecuteReader();

                while (rdr.Read())
                {
                    AccountModel newacc = new ();
                    newacc.UUID = rdr.GetString(0); 
                    newacc.Name = rdr.GetString(1);
                    newacc.Email = rdr.GetString(3);
                    newacc.PWDHash = rdr.GetString(6);
                    //if (!rdr.IsDBNull(15))
                    //    newacc.eth_address = rdr.GetString(15);

                    if (!accs.ContainsKey(newacc.UUID))
                        accs.Add(newacc.UUID, newacc);
                    newacc.bsel = rdr.GetByte(17);
                }

                await Task.WhenAll();
                rdr.Close();
                return accs;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }

        public async Task<Dictionary<int, SmartContract>> SQLSelectContracts(int blockchainId)
        {
            Console.WriteLine($"SQLSelectContracts");
            var bc = Blockchain.Find(blockchainId);
            if (bc != null)
            {
                string sql = "select * from smart_contracts where blockchain_id =" + blockchainId;
                Dictionary<int, SmartContract> contracts = new();

                bool connected = await IsConnect();
                if (connected)
                {
                    SQLcom = new(sql, Connection);
                    MySqlDataReader rdr = SQLcom.ExecuteReader();

                    while (rdr.Read())
                    {
                        var id = rdr.GetInt32(0);
                        var code = rdr.GetString(1);
                        var bytecode = rdr.GetString(2);
                        var name = rdr.GetString(3);
                        var gas = rdr.GetString(8);

                        SmartContract contract;
                        if (name == "Verify")
                            contract = new VerifyContract(name, code, bytecode, blockchainId, gas);
                        else
                            contract = new SmartContract(name, code, bytecode, blockchainId, gas);
                        contract.ID = id;
                        if (!rdr.IsDBNull(6))
                        {
                            contract.Address = rdr.GetString(6);
                        }
                        Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
                        if (!contracts.ContainsKey(contract.ID))
                            contracts.Add(contract.ID, contract);
                        if (!bc.contractNames.ContainsKey(name))
                            bc.contractNames.Add(name, contract);
                    }

                    await Task.WhenAll();
                    rdr.Close();
                    return contracts;
                }
                else
                    Console.WriteLine("Not connected to DB.");
            }
            else
                Console.WriteLine($"Error: blockchain {blockchainId} not found");
            return null;
        }

        public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
        {
            //return null;
            Console.WriteLine($"SQLSelectArticles {sql}");
            Dictionary<int, ArticleModel> articleModels = new();
            ArticleModel articleModel;

            bool connected = await IsConnect();
            if (connected)
            {
                var SQLcom = new MySqlCommand(sql, Connection);
                var rdr = await SQLcom.ExecuteReaderAsync();
                int count = 0;
                bool stop = false;
                while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
                {
                    /// tmp
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.IsDBNull(i))
                        {
                            Console.WriteLine($"SQLSelectArticles NULL detected {i}");
                            stop = true;
                            break;
                        }
                    }
                    if (stop)
                    {
                        stop = false;
                        continue;
                    }

                    articleModel = new();
                    articleModel.ID = rdr.GetInt32(0);
                    articleModel.Filename = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) articleModel.Name = rdr.GetString(2); else articleModel.Name = "NULL";
                    articleModel.PublishDate = rdr.GetDateTime(3);
                    articleModel.Authors = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) articleModel.Status = (ArticleStatus)rdr.GetInt32(5); else articleModel.Status = ArticleStatus.New;

                    if (articleModels.ContainsKey(articleModel.ID))
                        continue;
                    
                    count++;

                    articleModels.Add(articleModel.ID, articleModel);
                }

                Console.WriteLine($"SQLSelectArticles count: {count}");
                rdr.Close();
                
                return articleModels;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }
        const string typeguid = "System.Guid";

        public async Task<List<Dictionary<string, object>>> SQLSelectComplex(string request, bool check = true)
        {
            Console.WriteLine($"SQLSelectComplex {request}");
            bool connected = true;
            if (check)
            {
                //Console.WriteLine($"SQLSelectComplex check");
                connected = await IsConnect();
            }
            if (connected)
            {
                //Console.WriteLine($"SQLSelectComplex connected");
                List<Dictionary<string, object>> retval = new();
                MySqlCommand SQLcom2 = new(request, Connection);
                //Console.WriteLine($"SQLSelectComplex new SQLcom");
                //try
                //{
                var Reader = await SQLcom2.ExecuteReaderAsync();
                //Console.WriteLine($"SQLSelectComplex ExecuteReader");
                while (Reader.Read())
                    {
                        //Console.WriteLine($"SQLSelectComplex Reader.Read");
                        Dictionary<string, object> data = new();

                        for (int i = 0; i < Reader.FieldCount; i++)
                        {
                            if (!Reader.IsDBNull(i))
                            {
                                data.Add(Reader.GetName(i), Reader.GetValue(i));
                            }
                            else
                            {
                                //Console.WriteLine(Reader.GetName(i)+": GetDataTypeName " + Reader.GetDataTypeName(i) + " GetFieldType " + Reader.GetFieldType(i).ToString());
                                if (Reader.GetFieldType(i).ToString() == typeguid)
                                {
                                    data.Add(Reader.GetName(i), "00000000-0000-0000-0000-000000000000");
                                }
                                else if (Reader.GetName(i) == "mac_address")
                                {
                                    data.Add(Reader.GetName(i), "00-00-00-00-00-00");
                                }
                                else
                                {
                                    data.Add(Reader.GetName(i), null);
                                }
                            }
                        }
                        retval.Add(data);
                    }
                    
                //Console.WriteLine($"SQLSelectComplex Reader.Close");
                Reader.Close();
                await Task.WhenAll();
                //}
                //catch (Exception e)
                //{
                //    Console.WriteLine("SQL Exception 5.1 " + e.Message + " query " + request + "stack trace" + e.StackTrace);
                //}
                return retval;
            }
            return null;
        }

        public async Task<ArticleModel> SQLSelectArticle(string sql)
        {
            Console.WriteLine($"SQLSelectArticle");
            ArticleModel articleModel = new();

            bool connected = await IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                MySqlDataReader rdr = SQLcom.ExecuteReader();

                while (rdr.Read())  ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status, rating
                {
                    articleModel.ID = rdr.GetInt32(0);
                    articleModel.Filename = rdr.GetString(1);
                    articleModel.Name = rdr.GetString(2);
                    articleModel.Authors = rdr.GetString(3);
                    articleModel.PublishDate = rdr.GetDateTime(4);
                    articleModel.Annotation = rdr.GetString(5);
                    articleModel.Keywords = rdr.GetString(6);
                    articleModel.Status = (ArticleStatus)rdr.GetInt16(7);
                    if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);

                    Console.WriteLine($"ID: {articleModel.ID}, rating: {articleModel.Rating}");
                }                
                rdr.Close();
                await Task.WhenAll();
                return articleModel;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }

        public async Task<string> SQLSelectUUID(string sql)
        {
            Console.WriteLine($"SQLSelectUUID");
            bool connected = await IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                object obj = await SQLcom.ExecuteScalarAsync();
                return obj.ToString();
            }
            else return "";
        }

        public async Task<int> SQLSelectCount(string sql)
        {
            Console.WriteLine($"SQLSelectCount");
            bool connected = await IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                object obj = await SQLcom.ExecuteScalarAsync();
                int count = int.Parse(obj.ToString());
                return count;
            }
            else return 0;
        }

        public void Close()
        {
            Connection.Close();
        }
    }
}