context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       timop
  //
  // Copyright 2004-2013 by OM International
  //
  // This file is part of OpenPetra.org.
  //
  // OpenPetra.org 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.
  //
  // OpenPetra.org 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 OpenPetra.org.  If not, see <http://www.gnu.org/licenses/>.
  //
  using System;
  using System.Collections;
  using System.Data;
  using System.Data.Common;
  using System.Data.Odbc;
  using System.IO;
  using System.Text.RegularExpressions;
  using Mono.Data.Sqlite;
  
  using Ict.Common;
  using Ict.Common.DB.Exceptions;
  using Ict.Common.IO;
  
  namespace Ict.Common.DB
  {
      /// <summary>
      /// Allows access to SQLite databases on Windows and Linux, using the 'Mono.Data.Sqlite' .NET Data Provider.
      /// </summary>
      public class TSQLite : IDataBaseRDBMS
      {
          /// <summary>
          /// Creates a SQLite connection using the 'Mono.Data.Sqlite' .NET Data Provider.
          /// This works on Windows (with the sqlite3.dll) and on Linux.
          /// </summary>
          /// <param name="AServer">Database file.</param>
          /// <param name="APort">Port that the db server is running on.</param>
          /// <param name="ADatabaseName">Not in use with SQLite.</param>
          /// <param name="AUsername">Not in use with SQLite.</param>
          /// <param name="APassword">Password for opening the database.</param>
          /// <param name="AConnectionString">Not in use with SQLite.</param>
          /// <param name="AStateChangeEventHandler">Event Handler for connection state changes.</param>
          /// <returns>Instantiated SqliteConnection, but not opened yet (null if connection could not be established).
          /// </returns>
          public DbConnection GetConnection(String AServer, String APort,
              String ADatabaseName,
              String AUsername, ref String APassword,
              ref String AConnectionString,
              StateChangeEventHandler AStateChangeEventHandler)
          {
              ArrayList ExceptionList;
              SqliteConnection TheConnection = null;
  
              if (!File.Exists(AServer))
              {
                  // on Windows, we cannot store the database in userappdata during installation, because
                  // the setup has to be run as administrator.
                  // therefore the first time the user starts Petra, we need to prepare his environment
                  // see also http://www.vincenzo.net/isxkb/index.php?title=Vista_considerations#Best_Practices
  
                  // copy the base database
                  string baseDatabase = TAppSettingsManager.GetValue("Server.SQLiteBaseFile");
  
                  if (!Directory.Exists(Path.GetDirectoryName(AServer)))
                  {
                      Directory.CreateDirectory(Path.GetDirectoryName(AServer));
                  }
  
                  File.Copy(baseDatabase, AServer);
              }
  
              if (String.IsNullOrEmpty(AConnectionString))
              {
                  AConnectionString = "Data Source=" + Path.GetFullPath(AServer);
  
                  // sqlite on Windows does not support encryption with a password
                  // System.EntryPointNotFoundException: sqlite3_key
                  APassword = string.Empty;
  
                  if (APassword.Length > 0)
                  {
                      AConnectionString += ";Password=";
                  }
              }
  
              if (new TFileVersionInfo(SqliteConnection.SQLiteVersion).Compare(new TFileVersionInfo("3.7.11")) < 0)
              {
                  // for insert statements with multiple rows. see http://www.sqlite.org/releaselog/3_7_11.html
                  TLogging.Log("OpenPetra requires SQLite >= 3.7.11, but current version is " + SqliteConnection.SQLiteVersion);
                  return null;
              }
  
              try
              {
                  // Now try to connect to the DB
                  TheConnection = new SqliteConnection(AConnectionString + APassword);
              }
              catch (Exception exp)
              {
                  ExceptionList = new ArrayList();
                  ExceptionList.Add((("Error establishing a DB connection to: " + AConnectionString) + Environment.NewLine));
                  ExceptionList.Add((("Exception thrown :- " + exp.ToString()) + Environment.NewLine));
                  TLogging.Log(ExceptionList, true);
              }
  
              if (TheConnection != null)
              {
                  TheConnection.StateChange += AStateChangeEventHandler;
              }
  
              return TheConnection;
          }
  
          /// <summary>
          /// Initialises the connection after it was opened. Enforces Foreign Key constraints on SQLite.
          /// </summary>
          /// <param name="AConnection">DB Connection.</param>
          public void InitConnection(DbConnection AConnection)
          {
              string enforceForeignKeyConstraints = "PRAGMA foreign_keys = ON;";
  
              using (SqliteCommand cmd = new SqliteCommand(enforceForeignKeyConstraints, (SqliteConnection)AConnection))
              {
                  cmd.ExecuteNonQuery();
              }
          }
  
          /// <summary>
          /// Formats an error message if the Exception is of Type 'SQLiteException'.
          /// </summary>
          /// <param name="AException"></param>
          /// <param name="AErrorMessage"></param>
          /// <returns>True if this is an SQLiteException.</returns>
          public bool LogException(Exception AException, ref string AErrorMessage)
          {
              if (AException is SqliteException)
              {
                  AErrorMessage = AErrorMessage + ((SqliteException)AException).ErrorCode.ToString() +
                                  Environment.NewLine;
                  return true;
              }
  
              return false;
          }
  
          /// <summary>
          /// Formats a SQL query so that it works for SQLite.
          /// See also the comments for TDataBase.FormatQueryRDBMSSpecific.
          /// </summary>
          /// <param name="ASqlQuery">SQL Query.</param>
          /// <returns>Formatted SQL Query.</returns>
          public String FormatQueryRDBMSSpecific(String ASqlQuery)
          {
              string ReturnValue = ASqlQuery;
  
              ReturnValue = ReturnValue.Replace("PUB_", "");
              ReturnValue = ReturnValue.Replace("PUB.", "");
              ReturnValue = ReturnValue.Replace("pub_", "");
              ReturnValue = ReturnValue.Replace("pub.", "");
              ReturnValue = ReturnValue.Replace("public.", "");
              ReturnValue = ReturnValue.Replace("\"", "'");
  
              ReturnValue = ReturnValue.Replace("NOW()", "datetime('now')");
  
              ReturnValue = ReturnValue.Replace("= false", "= 0");
              ReturnValue = ReturnValue.Replace("= true", "= 1");
              ReturnValue = ReturnValue.Replace("=false", "=0");
              ReturnValue = ReturnValue.Replace("=true", "=1");
              ReturnValue = ReturnValue.Replace("=FALSE", "=0");
              ReturnValue = ReturnValue.Replace("=TRUE", "=1");
              ReturnValue = ReturnValue.Replace("= FALSE", "= 0");
              ReturnValue = ReturnValue.Replace("= TRUE", "= 1");
              ReturnValue = ReturnValue.Replace(" as ", " AS ");
              ReturnValue = ReturnValue.Replace("true AS ", "1 AS ");
              ReturnValue = ReturnValue.Replace("false AS ", "0 AS ");
              ReturnValue = ReturnValue.Replace("TRUE AS ", "1 AS ");
              ReturnValue = ReturnValue.Replace("FALSE AS ", "0 AS ");
  
              // INSERT INTO table () VALUES
              ReturnValue = ReturnValue.Replace("() VALUES", " VALUES");
  
              Match m = Regex.Match(ReturnValue, "#([0-9][0-9][0-9][0-9])-([0-9][0-9])-([0-9][0-9])#");
  
              while (m.Success)
              {
                  // needs to be 'yyyy-MM-dd 00:00:00'
                  // added 00:00:00 time to fix issue https://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=11
                  ReturnValue = ReturnValue.Replace("#" + m.Groups[1] + "-" + m.Groups[2] + "-" + m.Groups[3] + "#",
                      "'" + m.Groups[1] + "-" + m.Groups[2] + "-" + m.Groups[3] + " 00:00:00'");
                  m = Regex.Match(ReturnValue, "#([0-9][0-9][0-9][0-9])-([0-9][0-9])-([0-9][0-9])#");
              }
  
              // LIKE of sqlite is always case insensitive, so no modification needed (eg ILIKE for postgresql)
  
              // Get the correct function for DAYOFYEAR
              while (ReturnValue.Contains("DAYOFYEAR("))
              {
                  ReturnValue = ReplaceDayOfYear(ReturnValue);
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// Converts an Array of DbParameter (eg. OdbcParameter) to an Array
          /// of SQLiteParameter.
          /// </summary>
          /// <param name="AParameterArray">Array of DbParameter that is to be converted.</param>
          /// <param name="ASqlStatement">SQL Statement. It will not be modified!</param>
          /// <returns>Array of SQLiteParameter (converted from <paramref name="AParameterArray" />.</returns>
          public DbParameter[] ConvertOdbcParameters(DbParameter[] AParameterArray, ref string ASqlStatement)
          {
              SqliteParameter[] ReturnValue = new SqliteParameter[AParameterArray.Length];
              OdbcParameter[] AParameterArrayOdbc;
  
  
gt:             if (!(AParameterArray is SqliteParameter[]))