using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Data.SQLite;

namespace TestSqlite
{
    class Program
    {

        static void Main(string[] args)
        {
            SQLiteConnectionStringBuilder b = new SQLiteConnectionStringBuilder();
            b.DataSource = @"c:\sqlitetemp.db";
            b.SyncMode = SynchronizationModes.Normal;
            

            using (SQLiteConnection c = new SQLiteConnection(b.ToString()))
            {
                c.Open();

                try
                {
                    using (SQLiteCommand co = c.CreateCommand())
                    {
                        co.CommandText = "drop table tst";
                        co.ExecuteNonQuery();
                    }
                }
                catch
                {
                }

                using (SQLiteCommand co = c.CreateCommand())
                {
                    co.CommandText = "create table tst (a, b, c)";
                    co.ExecuteNonQuery();
                }
            }
            ThreadStart ts = delegate { Writer(0); };
            Thread writer = new Thread(ts);
            writer.Start();
            ts = delegate { Writer(1); };
            Thread writer2 = new Thread(ts);
            writer2.Start();
            Thread reader = new Thread(Reader);
            reader.IsBackground = true;
            reader.Start();

            Console.ReadKey();
        }

        private static void Writer(int instance)
        {
            SQLiteConnectionStringBuilder b = new SQLiteConnectionStringBuilder();
            b.DataSource = @"c:\sqlitetemp.db";

            using (SQLiteConnection c = new SQLiteConnection(b.ToString()))
            {
                c.Open();

                int z = 0;
                TimeSpan totalTime = TimeSpan.Zero;

                Random r = new Random();
                for (int i = 0; i < 100; i++)
                {
                    using (SQLiteCommand t = c.CreateCommand())
                    {
                        // needs to be immediate, otherwise two threads will block each other ...
                        t.CommandText = "begin IMMEDIATE transaction";
                        t.ExecuteNonQuery();
                    }

                    DateTime start = DateTime.Now;
                    for (int j = 0; j < 1000; j++)
                    {  

                        using (SQLiteCommand co = c.CreateCommand())
                        {
                            co.CommandText = "insert into tst (a, b, c) values (@a, @b, @c)";
                            co.Parameters.AddWithValue("@a", r.NextDouble());
                            co.Parameters.AddWithValue("@b", new string((char)(r.Next() % 255), r.Next() % 1024));
                            co.Parameters.AddWithValue("@c", z);
                            co.ExecuteNonQuery();
                        }

                        

                        z++;
                    }

                    using (SQLiteCommand t = c.CreateCommand())
                    {
                        t.CommandText = "commit transaction";
                        t.ExecuteNonQuery();
                    }

                    DateTime end = DateTime.Now;

                    totalTime += (end - start);

                    Console.SetCursorPosition(0, instance);
                    Console.Write(string.Concat("w: ", (totalTime.TotalMilliseconds / z), "        Total Records: ", z, "         inst:", instance, "        "));
                }
            }
        }

        private static void Reader()
        {
            SQLiteConnectionStringBuilder b = new SQLiteConnectionStringBuilder();
            b.DataSource = @"c:\sqlitetemp.db";

            using (SQLiteConnection c = new SQLiteConnection(b.ToString()))
            {
                c.Open();

                int z = 0;
                TimeSpan totalTime = TimeSpan.Zero;

                Random r = new Random();
                for (int i = 0; i < 1000; i++)
                {
                    DateTime start = DateTime.Now;
                    for (int j = 0; j < 100; j++)
                    {
                        int count = 0;
                        using (SQLiteCommand co = c.CreateCommand())
                        {
                            co.CommandText = "select count(*) from tst";
                            count = Convert.ToInt32(co.ExecuteScalar());
                        }
                        if (count > 0)
                        {
                            int rs = Math.Max(0, r.Next() % count - 10);
                            int re = Math.Min(count - 1, rs + 10);

                            using (SQLiteCommand co = c.CreateCommand())
                            {
                                co.CommandText = "select * from tst where c >= @cs and c <= @ce";
                                co.Parameters.AddWithValue("@cs", rs);
                                co.Parameters.AddWithValue("@ce", re);
                                using (SQLiteDataReader reader = co.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {

                                    }
                                }
                            }
                        }
                        z++;
                    }
                    DateTime end = DateTime.Now;

                    totalTime += (end - start);

                    Console.SetCursorPosition(0, 5);
                    Console.Write(string.Concat("r: ", (totalTime.TotalMilliseconds / z), "        "));
                }
            }
        }
    }
}