CDB latest and final

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Collections;
using System.Diagnostics;
using System.IO;

using System.Data.SQLite;

using MySql.Data;
using MySql.Data.MySqlClient;

namespace adonet_4
{
    public class Globals
        {
            private static SortedDictionary<string, List<String>> _mapNameToDataList = new SortedDictionary<string, List<String>>();
            private static List<string> _listTablenames;

            public static SortedDictionary<string, List<String>> mapNameToDataList
            {
                get { return _mapNameToDataList; }
                set { _mapNameToDataList = value; }
            }

            public static List<string> listTableNames
            {
                get { return _listTablenames; }
                set { _listTablenames = value; }
            }
        }

    class Program
    {

        static void execReaderSqlite(SQLiteCommand command)
        {
            try
            {
                SQLiteDataReader reader = command.ExecuteReader();
                /*Console.WriteLine("Executing Sqlite reader for query:" + command.CommandText);
                do
                {
                    while (reader.Read())
                    {
                        Object[] values = new Object[reader.FieldCount];
                        int fieldCount = reader.GetValues(values);

                        for (int i = 0; i < fieldCount; i++)
                        {
                            Console.Write("\t{0}", values[i]);
                        }
                        Console.WriteLine();
                    }

                } while (reader.NextResult());
                */
                reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            //Console.WriteLine();
        }

        //
        //
        //      P E R E G R O O Z K A
        //
        //


        static void execReaderMysql(MySqlCommand command)
        {
            try
            {
                MySqlDataReader reader = command.ExecuteReader();
                /*Console.WriteLine("Executing Mysql reader for query:" + command.CommandText);
                do
                {
                    while (reader.Read())
                    {
                        Object[] values = new Object[reader.FieldCount];
                        int fieldCount = reader.GetValues(values);

                        for (int i = 0; i < fieldCount; i++)
                        {
                            Console.Write("\t{0}", values[i]);

                        }
                        Console.WriteLine();
                    }

                } while (reader.NextResult());
                */
                reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            //Console.WriteLine();
        }


        static List<string> retrieveTablenames(SQLiteConnection connection)
        {
            List<string> tmpList = new List<string>();

            try
            {

                string q = "SELECT name FROM sqlite_master WHERE type = 'table'";
                SQLiteCommand cmd = new SQLiteCommand(q, connection);

                SQLiteDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    int j = 0;
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string tableName = reader.GetValue(i).ToString();
                            if (tableName.ToLower().Equals("failedtests"))
                            {
                                continue;
                            }

                            tmpList.Add(tableName);
                        }
                        j++;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }

            return tmpList;
        }


        static List<String> retrieveData(string tableName, SQLiteConnection connection)
        {

            string q = string.Format("SELECT * FROM {0}", tableName);
            SQLiteCommand cmd = new SQLiteCommand(q, connection);

            List<String> data = new List<String>();

            try {
                //Console.WriteLine("\treading table [" + tableName + "]");

                if (null != connection && ConnectionState.Open != connection.State)
                {
                    connection.Open();
                }
                //string q = "SELECT name FROM sqlite_master WHERE type = 'table'";
                //q = "select * from Persons";
                //SQLiteCommand cmd = new SQLiteCommand(query, connection);

                SQLiteDataReader reader = cmd.ExecuteReader();


                int entryNum = 0;

                if (reader.HasRows)
                {

                    while (reader.Read())
                    {

                        String entry = "(";

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            //Console.Write("\t{0}", Convert.ToString(reader[i]));
                            try
                            {
                                string cellType = reader.GetDataTypeName(i).ToString();
                                switch (cellType)
                                {
                                    case "INTEGER":
                                        break;
                                    case "DATETIME":
                                        entry += "'";
                                        break;
                                    default:
                                        entry += "'"; //\"
                                        break;
                                }

                                string cellValue = reader.GetValue(i).ToString();
                                cellValue = cellValue.Replace("'", "''");
                                if (cellValue.Equals(""))
                                {
                                    entry = entry.Remove(entry.Length - 1);
                                    entry += "null";
                                    goto fuck;
                                }

                                entry += cellValue;

                                switch (cellType)
                                {
                                    case "INTEGER":
                                        break;
                                    case "DATETIME":
                                        entry += "'";
                                        break;
                                    default:
                                        entry += "'"; //\"
                                        break;
                                }


                                fuck:
                                entry += ", ";


                            }
                            catch (Exception e)
                            {
                                Console.WriteLine(e.Message);
                            }

                        }

                        entry = entry.Remove(entry.Length - 2);
                        entry += ")";

                        data.Add(entry);

                        entryNum++;


                        if (1 == entryNum)
                        {
                            //Console.WriteLine("\tEntry example: " + entry);
                        }
                    }
                }

                reader.Close();

                Console.WriteLine("\t" + entryNum + " entries for table [" + tableName + "] has been processed");

            }
            catch (SQLiteException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            finally
            {
                connection.Close();
            }

            return data;
        }


        static void insertData(String tableName, List<String> data, MySqlConnection connection)
        {
            try
            {
                Console.WriteLine("\treading table [" + tableName + "]");

                var dataAsString = String.Join(", ", data);
                Console.WriteLine("\tquery length: " + dataAsString.Length);


                if (null != connection && ConnectionState.Open != connection.State)
                {
                    connection.Open();
                }

                String q = String.Format("insert into {0} values {1}", tableName, dataAsString);

                MySqlCommand cmd = new MySqlCommand(q, connection);

                cmd.ExecuteNonQuery();

            }
            catch (MySqlException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            finally
            {
                connection.Close();
            }
        }




        static void Main(string[] args)
        {
            int tableNumSqlite = 0, tableNumMysql = 0;

            Console.WriteLine("============================================================================");
            Console.WriteLine("---------------------------------SQLITE-------------------------------------");
            Console.WriteLine("============================================================================");

            String biqQuerySQLite = null;
            using (StreamReader sr = new StreamReader("contents.sql"))
            {
                try
                {
                    biqQuerySQLite = sr.ReadToEnd();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }
            String biqQueryMySQL = null;
            using (StreamReader sr = new StreamReader("contents_Mysql.sql"))
            {
                try
                {
                    biqQueryMySQL = sr.ReadToEnd();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }


            string connStringSqlite = @"Data Source=C:\Users\anthony\Documents\tbmstu.db; Version=3; PRAGMA schema.cache_size = 0;";
            string connStringMysql = "Server=localhost;Uid=TokyoNyquiSD;Pwd=qazxsw;Database=tbm3;";


            Stopwatch stopWatch = new Stopwatch();
            using (SQLiteConnection conn1 = new SQLiteConnection(connStringSqlite))
            {
                try
                {
                    conn1.Open();
                    Console.WriteLine("connection to SQLite has been set\n");

                    Console.Write("retrieving tablenames: ");

                    Globals.listTableNames = retrieveTablenames(conn1);

                    Console.WriteLine("successfully retrieved\n");



                    stopWatch.Start();
                    foreach (string table in Globals.listTableNames)
                    {
                        //Console.WriteLine("retrieving data for table " + ++tableNumSqlite);

                        List<String> data = retrieveData(table, conn1);
                        //Globals.mapNameToDataList.Add(table, data);
                        tableNumSqlite++;
                        //Console.WriteLine("\tdata has been added to map");
                    }
                    stopWatch.Stop();

                    Console.WriteLine("\nelapsed time for " + tableNumSqlite + " tables: " + stopWatch.Elapsed.ToString() + "\n");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }

            double sqliteTime = 0;
            double mysqlTime = 0;
            double amount = 1;
            //for (int i = 0; i < amount; i++)
            //{
                stopWatch.Reset();
                using (SQLiteConnection conn1 = new SQLiteConnection(connStringSqlite))
                {
                    try
                    {
                        conn1.Open();

                        stopWatch.Start();
                        execReaderSqlite(new SQLiteCommand(biqQuerySQLite, conn1));
                        stopWatch.Stop();

                        conn1.Close();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                sqliteTime += stopWatch.Elapsed.Milliseconds;

                stopWatch.Reset();
                using (MySqlConnection conn1 = new MySqlConnection(connStringMysql))
                {
                    try
                    {
                        conn1.Open();

                        stopWatch.Start();
                        execReaderMysql(new MySqlCommand(biqQueryMySQL, conn1));
                        stopWatch.Stop();

                        conn1.Close();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                mysqlTime += stopWatch.Elapsed.Milliseconds;
            //}
            double avgMysqlTime = (double)mysqlTime / amount;
            double avgSqliteTime = (double)sqliteTime / amount;
            Console.WriteLine("sqliteTimeAvg: " + avgSqliteTime + "ms\n mysqlTimeavg: " + avgMysqlTime + "ms");

            Console.WriteLine("SQLite if you were connecting every time:");

            int laps = 100;
            int overallTime;
            Stopwatch timer = new Stopwatch();
            int j = 0;
            foreach (string table in Globals.listTableNames)
            {
                overallTime = 0;
                for (int i = 0; i < laps; i++)
                {

                    timer.Start();

                    SQLiteConnection conn1 = new SQLiteConnection(connStringSqlite);
                    conn1.Open();
                    execReaderSqlite(new SQLiteCommand("select * from " + table, conn1));
                    conn1.Close();

                    timer.Stop();
                    overallTime += timer.Elapsed.Milliseconds;
                    timer.Reset();

                }
                double avgTime = (double)overallTime / (double)laps;
                Console.WriteLine("[" + j + "]: " + table + " \t\t\toa: " + overallTime + "ms, \tavg: " + avgTime + "ms");
                j++;
            }




            Console.WriteLine("\nMySQL if you were connecting every time:");
            laps = 10;
            overallTime = 0;
            j = 0;
            foreach (string table in Globals.listTableNames)
            {
                overallTime = 0;
                for (int i = 0; i < laps; i++)
                {

                    timer.Start();

                    MySqlConnection conn2 = new MySqlConnection(connStringMysql);
                    conn2.Open();
                    execReaderMysql(new MySqlCommand("select SQL_NO_CACHE * from " + table, conn2));
                    conn2.Close();

                    timer.Stop();
                    overallTime += timer.Elapsed.Milliseconds;
                    timer.Reset();

                }
                double avgTime = (double)overallTime / (double)laps;
                Console.WriteLine("[" + j + "]: " + table + " \t\t\toa: " + overallTime + "ms, \tavg: " + avgTime + "ms");
                j++;
            }


            Console.WriteLine("single table:");
            timer.Reset();
            timer.Start();
            MySqlConnection conn4 = new MySqlConnection(connStringMysql);
            conn4.Open();
            execReaderMysql(new MySqlCommand("select * from submissions", conn4));
            conn4.Close();
            timer.Stop();
            Console.WriteLine("\nelapsed time for sing subm table: " + timer.Elapsed.Milliseconds + "\n");



            Console.WriteLine("SQLite if you were connected:");
            laps = 10;
            timer.Reset();
            j = 0;

            using (SQLiteConnection conn1 = new SQLiteConnection(connStringSqlite))
            {
                conn1.Open();
                foreach (string table in Globals.listTableNames)
                {
                    overallTime = 0;
                    for (int i = 0; i < laps; i++)
                    {

                        timer.Start();


                        execReaderSqlite(new SQLiteCommand("select * from " + table, conn1));


                        timer.Stop();
                        overallTime += timer.Elapsed.Milliseconds;
                        timer.Reset();

                    }
                    double avgTime = (double)overallTime / (double)laps;
                    Console.WriteLine("[" + j + "]: " + table + " \t\t\toa: " + overallTime + "ms, \tavg: " + avgTime + "ms");
                    j++;
                }
            }


            connStringMysql = "Server=localhost;Uid=TokyoNyquiSD;Pwd=qazxsw;Database=tbmstu;";
            Console.WriteLine("\nMySQL if you were connected:");
            laps = 10;
            overallTime = 0;
            j = 0;

            using (MySqlConnection conn2 = new MySqlConnection(connStringMysql))
            {
                conn2.Open();
                foreach (string table in Globals.listTableNames)
                {
                    overallTime = 0;
                    for (int i = 0; i < laps; i++)
                    {

                        timer.Start();
                        execReaderMysql(new MySqlCommand("select SQL_NO_CACHE * from " + table, conn2));
                        timer.Stop();
                        overallTime += timer.Elapsed.Milliseconds;
                        timer.Reset();

                    }
                    double avgTime = (double)overallTime / (double)laps;
                    Console.WriteLine("[" + j + "]: " + table + " \t\t\toa: " + overallTime + "ms, \tavg: " + avgTime + "ms");
                    j++;
                }
            }



            Console.ReadLine();
            return;

            Console.WriteLine("============================================================================");
            Console.WriteLine("----------------------------------MYSQL-------------------------------------");
            Console.WriteLine("============================================================================\n");


            connStringMysql = "Server=localhost;Uid=TokyoNyquiSD;Pwd=qazxsw;Database=tbmstu;";
            using (MySqlConnection conn2 = new MySqlConnection(connStringMysql))
            {
                try
                {
                    conn2.Open();
                    Console.WriteLine("connection to MySQL has been set\n");


                    stopWatch.Reset();
                    stopWatch.Start();
                    foreach (string table in Globals.listTableNames)
                    {
                        //if (!table.Equals("Sessions"))
                        //{
                            Console.WriteLine("inserting data into table " + ++tableNumMysql + ": " + table);

                            if (table.Equals("RelTasksModules") || table.Equals("FailedTests"))
                            {
                                Console.WriteLine("\t-skipping-");
                                continue;
                            }

                            if (table.Equals("Comments"))
                            {
                                // BE AWARE OF SHITCODE HERE

                                int divisionParts = 200;
                                int count = Globals.mapNameToDataList["Comments"].Count - (Globals.mapNameToDataList["Comments"].Count % 10);
                                int partSize = (int)(count / divisionParts);
                                int from = 0;

                                //List<String> dataInRange = Globals.mapNameToDataList["Comments"].GetRange(7000, 10000);
                                //insertData("Comments", dataInRange, conn2);

                                Console.WriteLine("\tall data count:" + count);

                                int f = 0;
                                while (from < count)
                                {
                                    /*if (from > 7000 && from < 8000) {
                                        //List<String> datRange = Globals.mapNameToDataList["Comments"].GetRange(7200, 5000);

                                        //insertData("Comments", datRange, conn2);
                                        from += partSize;
                                        Console.WriteLine("asda");
                                        continue;
                                    }*/



                                    List<String> dataInRange = Globals.mapNameToDataList["Comments"].GetRange(from, partSize);

                                    if (dataInRange.Contains("'2013-04-04 20:10:17'"))
                                    {
                                        Console.WriteLine("found it");
                                        from += partSize;
                                        continue;
                                    }

                                    Console.WriteLine("\t" + f++ + " data range:" + from + ".." + (from + partSize));
                                    insertData("Comments", dataInRange, conn2);


                                    from += partSize;
                                }
                                Console.WriteLine("\tdone");

                                //Console.WriteLine("count:" + Globals.mapNameToDataList["Comments"].Count / 10);
                                //List<String> first50k = Globals.mapNameToDataList["Comments"].
                                //  GetRange(0, Globals.mapNameToDataList["Comments"].Count / 10);
                                //List<String> rest50k = Globals.mapNameToDataList["Comments"].
                                    //GetRange(Globals.mapNameToDataList["Comments"].Count / 2,
                                        //   Globals.mapNameToDataList["Comments"].Count - 1);

                                //insertData("Comments", first50k, conn2);
                                //Console.WriteLine("\t30k");
                                //insertData("Comments", rest50k, conn2);
                                //Console.WriteLine("\trest");



                                continue;
                            }

                            List<String> data = Globals.mapNameToDataList[table];
                            insertData(table, data, conn2);

                            if (table.Equals("RelLanguagesModules"))
                            {
                                //insert RelTasksModules first
                                List<String> specData = Globals.mapNameToDataList["RelTasksModules"];
                                insertData("RelTasksModules", specData, conn2);
                                Console.WriteLine("\tpre-added RelTasksModules");
                                continue;
                            }




                        //}



                    }
                    stopWatch.Stop();



                    Console.WriteLine("\nelapsed time for " + tableNumMysql + " tables: " + stopWatch.Elapsed.ToString() + "\n");


                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }


                //MySqlCommand cmd2 = new MySqlCommand("select table_name from information_schema.tables", conn2);
                //execReaderMysql(cmd2);
                //Console.WriteLine("cmd2 done");

                MySqlCommand cmd3 = new MySqlCommand("select * from persons", conn2);
                execReaderMysql(cmd3);

                Console.WriteLine("cmd3 done");

                /*string q4 = "insert into persons(personid, firstname, lastname, login, password, avatar, recoversession, banned) values " + Globals.mapNameToData["Persons"];
                Console.WriteLine(q4);
                MySqlCommand cmd4 = new MySqlCommand(q4, conn2);
                cmd4.ExecuteNonQuery();
                Console.WriteLine("cmd4 done");*/

                execReaderMysql(cmd3);

                conn2.Dispose();


                Console.WriteLine("all done");
                Console.ReadLine();
            }
        }

    }
}