//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.IO;
using System.Data;
using System.Data.Common;

using System.Data.SQLite;

namespace Spludlow.Data
{
	/// <summary>
	/// SQLite DAL Implimentaion - Fairly well implimented, not tested much
	/// 
	/// http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
	/// 
	/// Using static, requires the seperate SQLite.Interop.dll
	/// 
	/// No other dependancies
	/// 
	/// Used sqlite-netFx46-static-binary-x64-2015-1.0.112.0
	/// 
	/// </summary>
	public class DALSQLite : Spludlow.Data.IDAL
	{
		private SQLiteConnection Connection;
		private SQLiteTransaction Transaction;

		private string CacheKeyPrefix;

		private Dictionary<string, string> ConvertDataTypes;

		private string QuoteOpen = "[";
		private string QuoteClose = "]";

		public DALSQLite()
		{
		}
		public DALSQLite(string connectionString)
		{
			this.Initialize(connectionString);
		}
		public void Initialize(string connectionString)
		{
			if (connectionString == null || connectionString.Length == 0)   //	For use with utility methods
			{
				return;
			}
				

			if (connectionString.StartsWith("@") == true)
				connectionString = Spludlow.Config.ConnectionString(connectionString.Substring(1));

			//	Short hand: FILENAME
			if (connectionString.Contains("=") == false && connectionString.Contains(";") == false)
				connectionString = MakeConnectionString(connectionString);

			this.Connection = new SQLiteConnection(connectionString);

			this.CacheKeyPrefix = "DALSQLite-Schemas-" + this.Connection.ConnectionString;	//.DataSource(); // + "-";

			this.ConvertDataTypes = new Dictionary<string, string>();
			string[] convertLookups = new string[]
			{
				"integer			bigint",
				"counter			bigint",
				"autoincrement		bigint",
				"identity			bigint",
				"long				bigint",
				"yesno				bit",
				"logical			bit",
				"bool				bit",
				"boolean			bit",
				"currency			money",
				"numeric			decimal",
				"real				double",
				"float				double",
				"smalldate			datetime",				//	Guessed them from here !!!!!!!!!!!!!!!!!
				"timestamp			datetime",
				"uniqueidentifier	guid",
				"image				blob",
				"general			blob",
				"oleobject			blob",
				"memo				text",
				"longtext			text",
				"note				text",
				"string				text",

			};
			foreach (string convertLookup in convertLookups)
			{
				string[] words = Spludlow.Text.Split(convertLookup, '\t', true, false);
				this.ConvertDataTypes.Add(words[0], words[1]);
			}

			//this.ExecuteNonQuery("PRAGMA foreign_keys = 1");
		}



		public static string MakeConnectionString(string filename)
		{
			string connectionString = @"Data Source='@Filename';";
			connectionString = connectionString.Replace("@Filename", filename);

			connectionString += "datetimeformat=CurrentCulture;";

			return connectionString;
		}

		public string Quotes(string commandText)
		{
			return this.Quotes(commandText, "[", "]");
		}
		public string Quotes(string commandText, string replaceOpen, string replaceClose)
		{
			StringBuilder result = new StringBuilder(commandText);
			result.Replace(replaceOpen, this.QuoteOpen);
			result.Replace(replaceClose, this.QuoteClose);
			return result.ToString();
		}


		//
		//	Open / Close
		//
		private void Open()
		{
			this.Connection.Open();
		}
		private void Close()
		{
			this.Connection.Close();
		}

		public string DataSource()
		{
			return "";	// this.Connection.DataSource; !!!!!!!!!!!!!!!
		}

		public void CommandTimeout(int minutes)
		{

		}

		//
		//	Transactions
		//
		public void Begin()
		{
			if (this.Transaction != null)
				throw new ApplicationException("DALSQLite: Begin Transaction already present.");

			this.Open();

			try
			{
				this.Transaction = this.Connection.BeginTransaction();
			}
			catch
			{
				this.Close();
				throw;
			}
		}

		public void Commit()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DALSQLite: Commit Transaction not present.");

			try
			{
				this.Transaction.Commit();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		public void Rollback()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DALSQLite: Rollback Transaction not present.");

			try
			{
				this.Transaction.Rollback();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Schema
		//

		//	dataSet = database.SelectDS("PRAGMA table_info(tuningdetail)");

		public DataSet Schema(string tableName)
		{
			return this.Schema(new string[] { tableName });
		}
		public DataSet Schema(string[] tableNames)
		{
			DataSet fullSchema = this.Schema();

			DataSet partSchema = new DataSet();

			foreach (string tableName in tableNames)
			{
				foreach (string suffix in new string[] { "_Columns", "_Keys" })
					partSchema.Tables.Add(fullSchema.Tables[tableName + suffix].Copy());
			}

			return partSchema;
		}
		public DataSet Schema()
		{
			DataSet schema = (DataSet)Spludlow.Caching.Get(this.CacheKeyPrefix);

			if (schema != null)
				return schema;

			schema = new DataSet();

			DataSet schemaReader = this.SchemaReader();
			DataSet schemaNative = this.SchemaNative();
			DataSet schemaConnection = this.SchemaConnection();

			foreach (string tableName in this.TableList())
			{
				this.SchemaColumns(schema, tableName, schemaReader);
				this.SchemaKeys(schema, tableName, schemaReader, schemaNative, schemaConnection);
			}

			//Spludlow.Data.Schemas.FixForiegnKeys(schema);           //	seen Int64 on PK and Int32 on FK

			Spludlow.Caching.Set(this.CacheKeyPrefix, schema);

			return schema;
		}

		private void SchemaColumns(DataSet schema, string tableName, DataSet schemaReader)
		{
			DataTable schemaTable = Spludlow.Data.Schemas.NewSchemaColumnsTable(tableName);

			foreach (DataRow readerRow in schemaReader.Tables[tableName].Rows)
			{
				string columnName = (string)readerRow["ColumnName"];
				int columnSize = (int)readerRow["ColumnSize"];
				string typeName = ((string)readerRow["DataTypeName"]).ToLower();

				if (this.ConvertDataTypes.ContainsKey(typeName) == true)
					typeName = this.ConvertDataTypes[typeName];

				if (typeName.ToLower().EndsWith("identity") == true)        //	?????? using AutoIncrement now ???
				{
					typeName = typeName.Substring(0, typeName.Length - 8).Trim();
					readerRow["IsAutoIncrement"] = true;
				}
				
				if (columnSize == 0x7FFFFFFF && typeName.EndsWith("char") == true)
				{
					switch (typeName)
					{
						case "char":
						case "varchar":
							typeName = "text";
							break;
						case "nchar":
						case "nvarchar":
							typeName = "ntext";
							break;
						default:
							throw new ApplicationException("DAL SQLite, SchemaColumns; Unkown long char datatype:" + typeName);
					}
				}

				DataRow schemaRow = schemaTable.NewRow();

				schemaRow["ColumnName"] = columnName;
				schemaRow["Ordinal"] = (int)readerRow["ColumnOrdinal"];
				schemaRow["DataTypeId"] = Spludlow.Data.Schemas.NativeToCommon(typeName, "SQLite");
				schemaRow["MaxLength"] = columnSize;
				if (readerRow.IsNull("NumericPrecision") == false)
					schemaRow["Precision"] = (int)readerRow["NumericPrecision"];
				if (readerRow.IsNull("NumericScale") == false)
					schemaRow["Scale"] = (int)readerRow["NumericScale"];
				schemaRow["PrimaryKey"] = (bool)readerRow["IsKey"];
				schemaRow["AllowDBNull"] = (bool)readerRow["AllowDBNull"];
				schemaRow["AutoIncrement"] = (bool)readerRow["IsAutoIncrement"];
				schemaRow["Longest"] = 0;

				schemaTable.Rows.Add(schemaRow);
			}

			Spludlow.Data.Schemas.TidySchemaTable(schemaTable);
			schema.Tables.Add(schemaTable);
		}

		private void SchemaKeys(DataSet schema, string tableName, DataSet schemaReader, DataSet schemaNative, DataSet schemaConnection)
		{
			DataTable keysTable = Spludlow.Data.Schemas.NewSchemaKeysTable(tableName);

			foreach (DataRow indexRow in schemaConnection.Tables["Indexes"].Select("TABLE_NAME = '" + tableName + "'"))
			{
				string indexName = (string)indexRow["INDEX_NAME"];

				string keyType = "I";
				if ((bool)indexRow["PRIMARY_KEY"] == true)
				{
					keyType = "P";
				}
				else
				{
					if ((bool)indexRow["UNIQUE"] == true)
						keyType = "U";
				}

				foreach (DataRow indexColumnRow in schemaConnection.Tables["IndexColumns"].Select("TABLE_NAME = '" + tableName + "' AND INDEX_NAME = '" + indexName + "'", "ORDINAL_POSITION"))
				{
					string columnName = (string)indexColumnRow["COLUMN_NAME"];

					int ordinal = (int)indexColumnRow["ORDINAL_POSITION"];
					bool descending = false;
					if ((string)indexColumnRow["SORT_MODE"] != "ASC")
						descending = true;

					keysTable.Rows.Add(new object[] { indexName, keyType, ordinal, descending, columnName, null, null });
				}
			}

			List<string> parentTables = new List<string>();

			foreach (DataRow keyRow in schemaConnection.Tables["ForeignKeys"].Select("TABLE_NAME = '" + tableName + "'"))
			{
				string parentTable = (string)keyRow["FKEY_TO_TABLE"];
				if (parentTables.Contains(parentTable) == false)
					parentTables.Add(parentTable);
			}

			foreach (string parentTable in parentTables)
			{
				string keyName = "FK_" + tableName + "_" + parentTable;

				foreach (DataRow keyRow in schemaConnection.Tables["ForeignKeys"].Select("TABLE_NAME = '" + tableName + "' AND FKEY_TO_TABLE = '" + parentTable + "'", "FKEY_FROM_ORDINAL_POSITION"))
				{
					int ordinal = (int)keyRow["FKEY_FROM_ORDINAL_POSITION"];
					string columnName = (string)keyRow["FKEY_FROM_COLUMN"];
					string parentColumn = (string)keyRow["FKEY_TO_COLUMN"];

					bool descending = false;	//	Should look up from parent primary key !!!

					keysTable.Rows.Add(new object[] { keyName, "F", ordinal, descending, columnName, parentTable, parentColumn });
				}
			}
			
			schema.Tables.Add(keysTable);
		}

		public DataSet SchemaNative()
		{
			DataSet dataSet = new DataSet();

			//	https://www.sqlite.org/fileformat2.html	2.6

			string[] systemTables = new string[]
			{
				"sqlite_master",
				//"sqlite_sequence",	No allways present
			};

			foreach (string systemTableName in systemTables)
			{
				this.Fill(dataSet, "SELECT * FROM " + systemTableName);
				Spludlow.Data.ADO.NameLastTable(dataSet, systemTableName);
			}

			return dataSet;
		}

		public DataSet SchemaConnection()
		{
			DataSet schema;

			if (this.Transaction == null)
				this.Open();
			try
			{
				schema = Spludlow.Data.DALCommon.SchemaConnection(this.Connection);
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}

			DataTable table;

			table = schema.Tables["Columns"];
			table.PrimaryKey = new DataColumn[] { table.Columns["TABLE_NAME"], table.Columns["COLUMN_NAME"] };      //	Same in all DALS ?? !!!

			return schema;
		}

		public DataSet SchemaReader()
		{
			string[] tableNames = this.TableList();

			if (this.Transaction == null)
				this.Open();
			try
			{
				return Spludlow.Data.DALCommon.SchemaReader(this.Connection, this.Transaction, tableNames, "\"", "\"");
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}
		}

		public DataSet SchemaANSI()
		{
			DataSet schema = new DataSet();
			return schema;
		}

		public void SchemaRefresh()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQLite; To Refresh the schema you must have a current database");

			string key = this.CacheKeyPrefix;	// + databaseName;

			Spludlow.Caching.Remove(key);
		}

		//
		//	Databases
		//

		public string[] DatabaseList()
		{
			return new string[0];
		}


		public void ChangeDatabase(string databaseName)
		{
			this.Initialize(databaseName);
		}



		public string CurrentDatabase()
		{
			string[] parts = Spludlow.Text.Split(this.Connection.ConnectionString, ';', true, false);
			foreach (string part in parts)
			{
				if (part.ToLower().StartsWith("data source") == false)
					continue;

				return part.Trim(new char[] { ' ', '=', '\'' });
			}

			return "";
		}

		public void DatabaseCreate(string databaseName)
		{
			if (File.Exists(databaseName) == true)
				throw new ApplicationException("DAL SQLite; Creating database that already exists: " + databaseName);

			File.WriteAllText(databaseName, "");

			//this.RunScript(databaseName, ".databases" + Environment.NewLine + ".quit");	//	Can just create empty file!
		}

		public void DatabaseCreate(string databaseName, DataSet schema)	//	SQLite adds all keys when DB is created
		{
			this.DatabaseCreate(databaseName);

			this.ChangeDatabase(databaseName);

			string[] tableNames = Spludlow.Data.Schemas.TableNamesInOrder(schema);

			foreach (string tableName in tableNames)
			{
				this.TableCreate(tableName, schema);
				this.TableAddKeys(tableName, schema, "UI");
			}
		}

		public void DatabaseDelete(string databaseName)
		{
			File.Delete(databaseName);
		}

		public bool DatabaseExists(string databaseName)
		{
			return File.Exists(databaseName);
		}





		//
		//	Tables
		//

		public string[] TableList()
		{
			List<string> tableNames = new List<string>();
			DataTable table = this.Select("SELECT name from sqlite_master WHERE type = 'table'");
			foreach (DataRow row in table.Rows)
			{
				string tableName = (string)row[0];
				if (tableName.StartsWith("sqlite_") == false)
					tableNames.Add(tableName);
			}
			return tableNames.ToArray();
		}


		public void TableCreate(string tableName, DataSet schema)
		{
			this.TableCreate(tableName, schema.Tables[tableName + "_Columns"], schema.Tables[tableName + "_Keys"]);
		}
		public void TableCreate(string tableName, DataTable schemaColumns, DataTable schemaKeys)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To create a table you must have a current database");

			Spludlow.Data.DALCommon.CreateTableInfo info = new DALCommon.CreateTableInfo();

			info.DatabaseType = "SQLite";

			info.QuoteOpen = "[";
			info.QuoteClose = "]";

			info.AllowNull = "NULL";
			info.DontAllowNull = "NOT NULL";

			info.Identity = "__AUTOINCREMENT__";	//	Assumes is single column Primary key int (should be Int64)

			info.ColumnLine = "@ColumnName @DataType @Identity @Null";
			info.PrimaryLine = "CONSTRAINT @KeyName PRIMARY KEY (@Columns)";	//	Set in main table defintion
			//info.UniqueLine = "CONSTRAINT @KeyName UNIQUE INDEX (@Columns)";
			info.ForeignLine = "CONSTRAINT @KeyName FOREIGN KEY (@Columns) REFERENCES @ParentTable (@ParentColumns)";	//	Have to create with table
			//info.IndexLine = "CONSTRAINT @KeyName INDEX (@Columns)";

			//	Indexes have to be added afterwards !!!!
			
			string commandText = Spludlow.Data.DALCommon.MakeCreateTableCommandText(tableName, schemaColumns, schemaKeys, info);

			//	Fix AUTOINCREMENT - Bodge job!!!
			if (commandText.Contains(info.Identity) == true)
			{
				StringBuilder result = new StringBuilder();

				using (StringReader reader = new StringReader(commandText))
				{
					string line;
					while ((line = reader.ReadLine()) != null)
					{
						//	[LogId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
						if (line.Contains(info.Identity) == true)
						{
							//if (line.Contains(" bigint ") == false)
							//	Spludlow.Log.Warning("DAL SQLite; PRIMARY KEY AUTOINCREMENT is not Int64 table: " + tableName);

							int index = line.IndexOf("]");
							line = line.Substring(0, index + 1) + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,";
							result.AppendLine(line);
							continue;
						}

						//	CONSTRAINT [PK_Logs] PRIMARY KEY ([LogId])
						if (line.Contains("CONSTRAINT") == true && line.Contains("PRIMARY KEY") == true)
							continue;

						result.AppendLine(line);
					}
				}

				result.Replace("," + Environment.NewLine + ")" + Environment.NewLine, Environment.NewLine + ")" + Environment.NewLine);	//	If ends with a comma, sort it out

				commandText = result.ToString();
			}

			this.ExecuteNonQuery(commandText);
		}

		public void TableDelete(string tableName)
		{
			this.ExecuteNonQuery("DROP TABLE [" + tableName + "]");
		}

		public bool TableExists(string tableName)
		{
			object obj = this.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "'");

			if (obj == null || obj is DBNull)
				return false;

			return true;
		}

		public void TableClear(string tableName)
		{
			this.ExecuteNonQuery("DELETE FROM [" + tableName + "]");        //	SQLite don't care about foriegn keys (may leave DB with orphans!)
			this.ExecuteNonQuery("VACUUM");
		}

		public void TableAddKeys(DataSet schema, string keyTypesPUIF)
		{
			string[] tableNames = Spludlow.Data.Schemas.TableNames(schema);
			this.TableAddKeys(tableNames, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string tableName, DataSet schema, string keyTypesPUIF)
		{
			this.TableAddKeys(new string[] { tableName }, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string[] tableNames, DataSet schema, string keyTypesPUIF)
		{
			//	SQLite cant do P or F has to be done when creating table U and I must be done seperate to creating table

			string commandTextUnique = "CREATE UNIQUE INDEX [@KeyName] ON @TableName (@Columns)";
			string commandTextIndex = "CREATE INDEX [@KeyName] ON @TableName (@Columns)";

			foreach (string tableName in tableNames)
			{
				foreach (char keyType in keyTypesPUIF)
				{
					DataTable keysTable = schema.Tables[tableName + "_Keys"];

					List<string> keyNames = new List<string>();

					foreach (DataRow schemaKeyRow in keysTable.Select("KeyType = '" + keyType + "'"))
					{
						string keyName = (string)schemaKeyRow["KeyName"];
						if (keyNames.Contains(keyName) == false)
							keyNames.Add(keyName);
					}

					foreach (string keyName in keyNames)
					{
						Spludlow.SysTables.SchemaKeysRow[] keyRows = (Spludlow.SysTables.SchemaKeysRow[])keysTable.Select("KeyName = '" + keyName + "'");

						StringBuilder commandText = new StringBuilder();

						switch (keyType)
						{
							case 'U':
								commandText.Append(commandTextUnique);
								break;

							case 'I':
								commandText.Append(commandTextIndex);
								break;
						}

						commandText.Replace("@KeyName", keyName);
						commandText.Replace("@TableName", DALCommon.QuoteTableName(tableName, this.QuoteOpen, this.QuoteClose));

						commandText.Replace("@Columns", DALCommon.ColumnNames(keyRows, "ColumnName", this.QuoteOpen, this.QuoteClose));

						this.ExecuteNonQuery(commandText.ToString());
					}

				}
			}

		}

		//
		//	Logins (All do nothing for this database type)
		//

		public void LoginCreate(string loginName, bool administrator)
		{
		}

		public void LoginCreate(string loginName, string password, bool administrator)
		{
		}

		public void LoginDelete(string loginName)
		{
		}

		public bool LoginExists(string loginName)
		{
			return true;
		}

		public string[] LoginList()
		{
			return new string[0];
		}

		//
		//	Users (All do nothing for this database type)
		//


		public string[] UserList()
		{
			return new string[0];
		}

		public void UserCreate(string userName, string loginName, bool administrator)
		{
		}

		public void UserDelete(string userName)
		{
		}

		public bool UserExists(string userName)
		{
			return true;
		}





		//
		//	Backup, Restore, Script, Operation
		//

		public void Backup(string serverFilename)
		{
			string liveFilename = this.CurrentDatabase();
			if (liveFilename == null || liveFilename.Length == 0)
				throw new ApplicationException("DAL SQLite; Backup no current database.");

			if (File.Exists(liveFilename) == false)
				throw new ApplicationException("DAL SQLite; Backup Live database file does not exist: " + liveFilename);

			if (File.Exists(serverFilename) == true)
				File.Delete(serverFilename);

			File.Copy(liveFilename, serverFilename);		//	Lock File ???
		}

		public void Restore(string serverFilename)
		{
			string liveFilename = this.CurrentDatabase();
			if (liveFilename == null || liveFilename.Length == 0)
				throw new ApplicationException("DAL SQLite; Backup no current database.");

			if (File.Exists(liveFilename) == false)		//	Lock and overwrite ???
				File.Delete(liveFilename);

			File.Copy(serverFilename, liveFilename);
		}

		public string RunScript(string localFilename, string server, string database)
		{
			return this.RunScript(localFilename, server, database, null, null);
		}
		public string RunScript(string localFilename, string server, string database, string userName, string password)
		{
			string script = File.ReadAllText(localFilename);    // Encoding ?	Maybe if no backslashes in start (ie not filename) then use passed text (or @#? at start???)

			return this.RunScript(database, script);
		}

		private string RunScript(string arguments, string input)
		{
			string programPath = this.FindCommandPath();    // @"C:\Program Files (x86)\sqlite-tools-win32-x86-3170000\sqlite3.exe";  //	

			Spludlow.Log.Info("DAL SQLite; Running Script: " + programPath + " " + arguments);

			Spludlow.SpawnProcess.ProcessExitInfo exitInfo = Spludlow.SpawnProcess.Run(programPath, arguments, 0, input); //	No timeout !!!!

			if (exitInfo.StandardError.Length > 0)
				Spludlow.Log.Error("DAL SQLite; RunScript, Error in output", exitInfo.StandardError);

			if (exitInfo.ExitCode != 0)
				throw new ApplicationException("DAL SQLite; RunScript, Bad Exit Code: " + exitInfo.ExitCode);

			return exitInfo.StandardOutput;
		}
		private string FindCommandPath()
		{
			string path;

			path = Spludlow.Config.Get("Spludlow.DatabaseCommandPath.SQLite", true);

			if (path != null)
				return path;

			string name = "sqlite3.exe";

			if (File.Exists(name) == true)
				return Path.GetFullPath(name);

			string found = null;

			foreach (string programFilesDirectory in new string[] { @"C:\Program Files", @"C:\Program Files (x86)" })
			{
				string[] directories = Directory.GetDirectories(programFilesDirectory, "sqlite-tools*");

				foreach (string directory in directories)
				{
					path = directory + @"\sqlite3.exe";
					if (File.Exists(path) == true)
					{
						if (found == null)
							found = path;
						else
							Spludlow.Log.Warning("SQLite; Multiple paths to sqlite3.exe found: " + path);
					}
				}
			}

			if (found == null)
				throw new ApplicationException("SQLite; Unable to find sqlite3.exe");

			return found;
		}

		public void Operation(string details)
		{
			Spludlow.Log.Warning("SQLite; Operation, doing nothing: " + details);
		}

		public string PageCommandText(string commandText, int pageIndex, int pageSize)
		{
			int offset = pageIndex * pageSize;
			StringBuilder text = new StringBuilder(commandText);
			text.Append(" LIMIT @PageSize OFFSET @Offset");
			text.Replace("@Offset", offset.ToString());
			text.Replace("@PageSize", pageSize.ToString());
			return text.ToString();
		}

		//
		//	Make Command
		//

		private void SetDataType(DAL.CommandInfo info, DataRow columnRow, int index)
		{
			string dataTypeId = (string)columnRow["DataTypeId"];
			string nativeType = Spludlow.Data.Schemas.CommonToNative(dataTypeId, "DbType");     //	There is no SQLiteDbType 

			DbType dbType = (DbType)Enum.Parse(typeof(DbType), nativeType);

			info.ParameterTypes[index] = (int)dbType;
			info.ParameterMaxLengths[index] = (int)columnRow["MaxLength"];
		}

		public DAL.CommandInfo MakeCommandInfo(string commandText)
		{
			return MakeCommandInfo(commandText, DALCommon.ExtractParameterNames(commandText));
		}

		public DAL.CommandInfo MakeCommandInfo(string commandText, string[] parameterNames)
		{
			return Spludlow.Data.DALCommon.MakeCommandInfo(commandText, parameterNames, this.Schema(), this.SetDataType);
		}


		public DbCommand MakeCommand(string commandText)
		{
			SQLiteCommand command = new SQLiteCommand(commandText, this.Connection, this.Transaction);
			return command;
		}

		public DbCommand MakeCommand(string commandText, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(string commandText, string[] parameterNames, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText, parameterNames);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			if (commandInfo.ParameterNames.Length != parameterValues.Length)
				throw new ApplicationException("Making Command with different parameterNames and parameterValues counts: " + commandInfo.ParameterNames.Length + ", " + parameterValues.Length);

			SQLiteCommand command = new SQLiteCommand(commandInfo.CommandText, this.Connection, this.Transaction);

			for (int index = 0; index < commandInfo.ParameterNames.Length; ++index)
			{
				string paramterName = commandInfo.ParameterNames[index];

				DbType dbType = (DbType)commandInfo.ParameterTypes[index];
				int maxLength = commandInfo.ParameterMaxLengths[index];

				SQLiteParameter parameter = command.CreateParameter();

				parameter.ParameterName = paramterName;

				if ((int)dbType != -1)
					parameter.DbType = dbType;      //	parameter.TypeName	???

				//if (maxLength > 0)
				//	parameter.Size = maxLength;         //		using Size seems to slow things down big time on Insert DAL Test using dummpy Logs and bodies. Maybe becasue using max size? Do more tests !!!!!!!!!!!!!!

				parameter.Value = parameterValues[index];

				command.Parameters.Add(parameter);
			}

			return command;
		}

		//
		//	ExecuteScalar
		//
		public object ExecuteScalar(string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DbCommand command)
		{
			return this.ExecuteScalar((SQLiteCommand)command);
		}

		private object ExecuteScalar(SQLiteCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteScalar();

			this.Open();
			try
			{
				return command.ExecuteScalar();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	ExecuteNonQuery
		//
		public int ExecuteNonQuery(string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DbCommand command)
		{
			return this.ExecuteNonQuery((SQLiteCommand)command);
		}

		private int ExecuteNonQuery(SQLiteCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteNonQuery();

			this.Open();
			try
			{
				return command.ExecuteNonQuery();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Fill
		//



		public void Fill(DataSet dataSet, string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DbCommand command)
		{
			this.Fill(dataSet, (SQLiteCommand)command);
		}

		private void Fill(DataSet dataSet, SQLiteCommand command)
		{
			this.ExecuteAdapter(command, dataSet, null);
		}

		public void Fill(DataTable table, string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DbCommand command)
		{
			this.Fill(table, (SQLiteCommand)command);
		}

		private void Fill(DataTable table, SQLiteCommand command)
		{
			this.ExecuteAdapter(command, null, table);
		}

		//
		//	Select
		//

		public DataTable Select(string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				return this.Select(command);
		}
		public DataTable Select(string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DbCommand command)
		{
			return this.Select((SQLiteCommand)command);
		}

		private DataTable Select(SQLiteCommand command)
		{
			DataSet dataSet = this.SelectDS(command);
			DataTable table = dataSet.Tables[0];
			dataSet.Tables.Remove(table);
			return table;
		}


		public DataSet SelectDS(string commandText)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DbCommand command)
		{
			return this.SelectDS((SQLiteCommand)command);
		}
		private DataSet SelectDS(SQLiteCommand command)
		{
			DataSet dataSet = new DataSet();
			this.ExecuteAdapter(command, dataSet, null);
			return dataSet;
		}

		private void ExecuteAdapter(SQLiteCommand command, DataSet dataSet, DataTable table)
		{
			if (this.Transaction == null)
				this.Open();

			try
			{
				using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
				{
					if (dataSet != null)
						adapter.Fill(dataSet);

					if (table != null)
						adapter.Fill(table);
				}
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}
		}

		//
		//	Inserter
		//

		public DAL.CommandInfo MakeInserter(string tableName)
		{
			return DALCommon.MakeInserter(this, tableName, "; SELECT last_insert_rowid()");
		}

		public long Insert(DataRow row)
		{
			return Insert(row.Table.TableName, row);
		}

		public long Insert(string tableName, DataRow row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, DataRow row)
		{
			object[] objectRow = DALCommon.ObjectsFromRow(row, insertInfo);
			return Insert(insertInfo, objectRow);
		}

		public long Insert(string tableName, object[] row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, object[] row)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(insertInfo, row))
			{
				if (insertInfo.Identity == false)
				{
					this.ExecuteNonQuery(command);
					return 0;
				}
				else
				{
					object obj = this.ExecuteScalar(command);

					if (obj == null)
						throw new ApplicationException("DAL SQLite Insert; Identity object is null.");

					if (obj is Int64)
						return (long)((Int64)obj);

					throw new ApplicationException("DAL SQLite Insert; Identity object bad type:\t" + obj.GetType().Name);
				}
			}
		}


		//
		//	Update
		//

		public DAL.CommandInfo MakeUpdater(string tableName)
		{
			return MakeUpdater(tableName, new string[0]);
		}

		public DAL.CommandInfo MakeUpdater(string tableName, string[] updateColumns)
		{
			return DALCommon.MakeUpdater(this, this.Schema(), tableName, updateColumns);
		}

		public int Update(DataRow row)
		{
			return this.Update(row.Table.TableName, row);
		}

		public int Update(string tableName, DataRow row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName);
			return this.Update(updateInfo, row);
		}

		public int Update(string tableName, string[] columns, DataRow row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName, columns);
			return this.Update(updateInfo, row);
		}

		public int Update(DAL.CommandInfo info, DataRow row)
		{
			object[] objectRow = DALCommon.ObjectsFromRow(row, info);
			return this.Update(info, objectRow);
		}

		public int Update(string tableName, object[] row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName);
			return this.Update(updateInfo, row);
		}

		public int Update(string tableName, string[] columns, object[] row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName, columns);
			return this.Update(updateInfo, row);
		}

		public int Update(DAL.CommandInfo updateInfo, object[] row)
		{
			using (SQLiteCommand command = (SQLiteCommand)this.MakeCommand(updateInfo, row))
			{
				return this.ExecuteNonQuery(command);
			}
		}


		
		public long InsertOrUpdate(DataRow row)
		{
			return InsertOrUpdate(row.Table.TableName, row);
		}

		public long InsertOrUpdate(string tableName, DataRow row)
		{
			return DALCommon.InsertOrUpdate(row, tableName, this);
		}


		public void BulkInsert(DataTable table, string tableName, string tempDirectory)
		{
			Spludlow.Data.Schemas.DisableAutoIncrement(this.Schema(), tableName);

			DAL.CommandInfo inserter = this.MakeInserter(tableName);

			//	Doing in a transaction as it's faster

			this.CommandTimeout(60);	//	The Commit may take a while

			this.Begin();

			try
			{
				foreach (DataRow row in table.Rows)
					this.Insert(inserter, row);

				this.Commit();
			}
			catch
			{
				this.Rollback();
				throw;
			}
			finally
			{
				this.SchemaRefresh();
			}
		}


	}
}