//  
//  DbsAccess.cs
//  
//  Author:
//       Robert BRACCAGNI alias Gai-Luron <lfsgailuron@free.fr>
// 
//  Copyright (c) 2010 Gai-Luron
// 
//  This program is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
// 
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
// 
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.

using System;
using System.Data;
#if MONO
    using Mono.Data.SqliteClient;
#else
    using System.Data.SQLite;
#endif

namespace LFSDbs
{
    public class DbsAccess
    {
        public IDbConnection dbCon;
#if MONO
        public IDbCommand dbSql;
#endif
        private GLDebug.Debug myDebug;

        static System.Collections.Hashtable toto = new System.Collections.Hashtable();

            public DbsAccess( GLDebug.Debug pmyDebug, string DbName ){

#if MONO
                string connectionString = "URI=file:" + DbName + ".dbs" + ",version=3";
#else
                string connectionString = "Data Source=" + DbName + ".dbs;version=3;";
#endif
                // IDbConnection pdbCon;
                this.myDebug = pmyDebug;

                if (!System.IO.File.Exists(DbName + ".dbs"))
                {
                    myDebug.WriteLine("mss","Dbs not exist, Creation of : " + DbName + ".dbs");
                }
#if MONO
                dbCon = (IDbConnection)new SqliteConnection( connectionString );
#else
                dbCon = (IDbConnection)new SQLiteConnection(connectionString);
#endif
                dbCon.Open();
#if MONO
                dbSql = dbCon.CreateCommand();
#endif

            }

            public bool isExistTable(string tableName)
            {
                bool ret = false;

//                dbSql.CommandText = "SELECT name FROM SQLITE_MASTER WHERE type = 'table' AND name = '" + tableName + "'";
//                IDataReader reader = dbSql.ExecuteReader();
                IDataReader reader = executeQuery("SELECT name FROM SQLITE_MASTER WHERE type = 'table' AND name = '" + tableName + "'" );
                if (reader.Read())
                {
                    ret = true;
                }
                reader.Close();
                reader.Dispose();
                return ret;
            }
            public bool isExistColum(string tableName, string colName)
            {
            //If a Try here and not for other... maybe will have to test it out...

#if !MONO
            IDbCommand dbSql;
                dbSql = dbCon.CreateCommand();
#endif

                dbSql.CommandText = "SELECT " + colName + " FROM " + tableName + " LIMIT 1";
                try
                {
                    IDataReader reader = dbSql.ExecuteReader();
                    reader.Read();
                    reader.Close();
                    reader.Dispose();
                }
                catch
                {
                    return false;
                };
                return true;
            }
            public bool isExistIndex(string indexName)
            {
#if !MONO
            IDbCommand dbSql;
                dbSql = dbCon.CreateCommand();
#endif

                dbSql.CommandText = "SELECT name FROM sqlite_master WHERE type = 'index' AND name = '" + indexName + "'";
                IDataReader reader = dbSql.ExecuteReader();
                if (reader.Read())
                    return true;
                reader.Close();
                reader.Dispose();
                return false;
            }
            public uint getLastRowId(string _tableName)
            {
                uint ret = 0;
                IDataReader result = executeQuery("SELECT MAX( rowid ) FROM `" + _tableName + "`");
                //            IDataReader result = ExecuteQuery("SELECT MAX(`guid`) FROM `" + _tableName + "`");
                if (result.Read())
                {
                    ret = (uint)result.GetInt32(0);
                }
                result.Close();
                result.Dispose();
                return ret;
            }
        public IDataReader executeQuery(string _command)
        {
            return executeQuery2(_command);
        }
        private IDataReader executeQuery2(string _command)
        {
            DateTime timeOut = DateTime.Now.AddSeconds((double)20);
            System.Random rnd = new System.Random(DateTime.Now.Millisecond);

            IDataReader ir = null;
            while (true)
            {
                try
                {
#if !MONO
                    IDbCommand dbSql;
                    dbSql = dbCon.CreateCommand();
#endif
                    dbSql.CommandText = _command;
                    ir = dbSql.ExecuteReader();
                    break;
                }
#if MONO
                catch (SqliteBusyException e)
                {
                    if (DateTime.Now > timeOut)
                    {
                        myDebug.WriteLine("err","Database Busy SQL COMMAND : ");
                        myDebug.printDateOnEachLine = false;
                        myDebug.WriteLine("err", _command);
                        myDebug.printDateOnEachLine = true;
                        throw e;
                    }
                    System.Threading.Thread.Sleep( rnd.Next(80, 300) );
                }
                catch( Exception e )
                {
                    if (DateTime.Now > timeOut || e.Message != "database is locked")
                    {

                        myDebug.WriteLine("err","SQL COMMAND : ");
                        myDebug.printDateOnEachLine = false;
                        myDebug.WriteLine("err", _command);
						myDebug.WriteLine("err", "");
						myDebug.WriteLine("err", e.Message);
						myDebug.WriteLine("err", "");
						myDebug.printDateOnEachLine = true;
                        throw e;
                    }
                    Console.WriteLine("Database Busy Retry ");
                    System.Threading.Thread.Sleep(rnd.Next(80, 300));
                }
#else
                catch (SQLiteException e)
                {
                    switch (e.ErrorCode)
                    {
                        case 5:
                            if (DateTime.Now > timeOut){
                                myDebug.WriteLine("err", e.Message + " : ");
                                myDebug.printDateOnEachLine = false;
                                myDebug.WriteLine("err", _command);
                                myDebug.printDateOnEachLine = true;
                                throw e;
                            }
                            Console.WriteLine(e.Message);
                            break;

                        case 6:
                            if (DateTime.Now > timeOut){
                                myDebug.WriteLine("err",e.Message + " : ");
                                myDebug.printDateOnEachLine = false;
                                myDebug.WriteLine("err", _command);
						        myDebug.WriteLine("err", "");
						        myDebug.WriteLine("err", e.Message);
						        myDebug.WriteLine("err", "");
						        myDebug.printDateOnEachLine = true;
                                throw e;
                            }
                            Console.WriteLine( e.Message );
                            break;
                    }
                    System.Threading.Thread.Sleep(rnd.Next(80, 300));
                }
#endif
            }
            return ir;
        }
        public long executeNonQuery(string _command)
        {
/*
            if (_command == "BEGIN TRANSACTION" || _command == "COMMIT TRANSACTION")
                Console.WriteLine(_command);
*/
            return executeNonQuery2(_command);
        }
        public long executeNonQuery2(string _command)
        {

                DateTime timeOut = DateTime.Now.AddSeconds((double)20);
                System.Random rnd = new System.Random(DateTime.Now.Millisecond);

                int i=0;
                while (true)
                {

                    try
                    {
#if !MONO
                        IDbCommand dbSql;
                        dbSql = dbCon.CreateCommand();
#endif
                        dbSql.CommandText = _command;
                        i = dbSql.ExecuteNonQuery();
                        dbSql.Dispose();
                        break;
                    }
#if MONO
                catch (SqliteBusyException e)
                {
                    if (DateTime.Now > timeOut)
                    {
                        myDebug.WriteLine("err","Database Busy SQL COMMAND : ");
                        myDebug.printDateOnEachLine = false;
                        myDebug.WriteLine("err", _command);
                        myDebug.printDateOnEachLine = true;
                        throw e;
                    }
                    System.Threading.Thread.Sleep( rnd.Next(80, 300) );
                }
                catch( Exception e )
                {
                    if (DateTime.Now > timeOut || e.Message != "database is locked")
                    {

                        myDebug.WriteLine("err","SQL COMMAND : ");
                        myDebug.printDateOnEachLine = false;
                        myDebug.WriteLine("err", _command);
						myDebug.WriteLine("err", "");
						myDebug.WriteLine("err", e.Message);
						myDebug.WriteLine("err", "");
						myDebug.printDateOnEachLine = true;
                        throw e;
                    }
                    Console.WriteLine("Database Busy Retry ");
                    System.Threading.Thread.Sleep(rnd.Next(80, 300));
                }
#else
                    catch (SQLiteException e)
                    {
                    
                        switch (e.ErrorCode)
                        {
                            case 5:
                                if (DateTime.Now > timeOut)
                                {
                                    myDebug.WriteLine("err", e.Message + " : ");
                                    myDebug.printDateOnEachLine = false;
                                    myDebug.WriteLine("err", _command);
                                    myDebug.printDateOnEachLine = true;
                                    throw e;
                                }
                                Console.WriteLine(e.Message);
                                break;
                            case 6:
                                if (DateTime.Now > timeOut)
                                {
                                    myDebug.WriteLine("err", e.Message + " : ");
                                    myDebug.printDateOnEachLine = false;
                                    myDebug.WriteLine("err", _command);
                                    myDebug.WriteLine("err", "");
                                    myDebug.WriteLine("err", e.Message);
                                    myDebug.WriteLine("err", "");
                                    myDebug.printDateOnEachLine = true;
                                    throw e;
                                }
                                Console.WriteLine(e.Message);
                                break;
                            default:
                                Console.Write("DBS Error: " + e.ErrorCode );
                                Console.WriteLine( " Not Catched" + e.Message);
                                System.Threading.Thread.Sleep(rnd.Next(80, 300));
                                break;
                        
                        }
                        System.Threading.Thread.Sleep(rnd.Next(80, 300));
                    }
                    
#endif
                }
                return i;
            }
     }

}
