using System;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Threading;

namespace SQLiteConcurrencyTest
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            new Test();
        }
    }

    internal class Test
    {
        private const int Range = 5000;

        private static readonly string[] TestStrings =
        {
            "Lorem Ipsum",
            "Phasellus luctus velit in magna facilisis, vitae fringilla velit fringilla. Nulla tempor eget lectus nec facilisis. Fusce gravida lacinia molestie.",
            "Nulla facilisi. Etiam pulvinar lorem et lectus condimentum suscipit. Ut faucibus nulla consectetur, ullamcorper urna eleifend, gravida libero."
        };

        [ThreadStatic] private static Random _random;

        public Test()
        {
            // Create new, empty file
            SQLiteConnection.CreateFile("test.db3");

            using (DbConnection localConnection = CreateConnection())
            {
                DoQuery(localConnection, "CREATE TABLE test ( [key] int NOT NULL, [value] text NOT NULL, PRIMARY KEY ([key]))");
            }

            // Create and start worker threads
            for (int i = 0; i < 3; i++)
            {
                new Thread(InsertOrReplace).Start();
            }

            new Thread(Select).Start();
        }

        private DbConnection CreateConnection()
        {
            var connectionStringBuilder = new SQLiteConnectionStringBuilder
                                          {
                                              DataSource = "test.db3",
                                              Password = "something",
                                              SyncMode = SynchronizationModes.Off,

                                              // Setting JournalMode to Wal eliminates the problem.

                                              // JournalMode = SQLiteJournalModeEnum.Wal,

                                              // OR setting CacheSize to 0 greatly reduces the problem.
                                              // However, exceptions still occur from time to time.

                                              //CacheSize = 0
                                          };

            return new SQLiteConnection(connectionStringBuilder.ToString()).OpenAndReturn();
        }

        private void InsertOrReplace()
        {
            Console.WriteLine("Start Insert Or Replace thread...");

            _random = new Random();

            using (DbConnection localConnection = CreateConnection())
            {
                for (int j = 0; j < 2000; j++)
                {
                    DoQuery(localConnection,
                        String.Format("INSERT OR REPLACE INTO test (key, value) VALUES ({0}, '{1}{2}')", _random.Next(Range), TestStrings[_random.Next(TestStrings.Count())], _random.Next(Range)));
                }
            }

            Console.WriteLine("End Insert Or Replace thread...");
        }

        private void Select()
        {
            Console.WriteLine("Start Select thread...");

            _random = new Random();

            using (DbConnection localConnection = CreateConnection())
            {
                for (int i = 0; i < 30000; i++)
                {
                    DoQuery(localConnection, String.Format("SELECT * FROM test WHERE key={0}", _random.Next(Range)));
                }
            }
            Console.WriteLine("End Select thread...");
        }

        private void DoQuery(DbConnection conn, String query)
        {
            DbCommand command = conn.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
        }
    }
}