//	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.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.IO;

using MySql.Data.MySqlClient;

namespace Spludlow.Data
{
	/// <summary>
	/// MySQL DAL Implimentaion - fairly complete, logins and users not done yet
	/// 
	/// Only require referencing a single pure .net DLL
	/// https://dev.mysql.com/downloads/connector/net/
	/// Use the .Net & Mono (Architecture Independent), ZIP Archive
	/// 
	/// Backup requires "C:\Users\SpludlowUser\my.ini" is configured
	/// </summary>
	public class DALMySQL : Spludlow.Data.IDAL
	{
		private MySqlConnection Connection;
		private MySqlTransaction Transaction = null;

		private string ChangedDatabase = null;
		private string CurrentDatabaseCache = null;

		private string CacheKeyPrefix;

		private Dictionary<string, string> ConvertDataTypes;

		private string QuoteOpen = "`";
		private string QuoteClose = "`";

		private int CommandTimeoutSeconds = -1;

		private List<string> SystemDatabaseNames = new List<string>(new string[]
		{
			"information_schema",
			"mysql",
			"performance_schema",
			"sys",
		});

		public DALMySQL()
		{
		}
		public DALMySQL(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));

			this.Connection = new MySqlConnection(connectionString);

			this.CacheKeyPrefix = "DALMySQL-Schemas-" + this.DataSource() + "-";

			this.ConvertDataTypes = new Dictionary<string, string>();
			string[] convertLookups = new string[]
			{
				"blob		longblob",
				"tinyblob	longblob",
				"mediumblob	longblob",
				"longblob	longblob",
				"text		longtext",
				"tinytext	longtext",
				"mediumtext	longtext",
				"longtext	longtext",
				"timestamp	datetime",		//	matter ???
				"set		varchar",
				"enum		varchar",
				"year		int",
			};
			foreach (string convertLookup in convertLookups)
			{
				string[] words = Spludlow.Text.Split(convertLookup, '\t', true, false);
				this.ConvertDataTypes.Add(words[0], words[1]);
			}

			this.ChangeDatabase(null);
		}

		//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();
		//}

		public static string MakeConnectionString(string host, string database, string userName, string password)
		{
			string[] names = new string[] { "Server", "Database", "Uid", "Pwd" };
			string[] values = new string[] { host, database, userName, password };

			StringBuilder connectionString = new StringBuilder();

			for (int index = 0; index < values.Length; ++index)
			{
				string value = values[index];
				if (value == null)
					continue;

				connectionString.Append(names[index]);
				connectionString.Append("='");
				connectionString.Append(value);
				connectionString.Append("';");
			}

			return connectionString.ToString();
		}


		private void Open()
		{
			this.Connection.Open();

			if (this.ChangedDatabase != null)
			{
				try
				{
					string database = this.ChangedDatabase;
					if (database == "")
						database = "mysql";

					this.Connection.ChangeDatabase(database);
				}
				catch
				{
					this.Connection.Close();
					throw;
				}
			}
		}
		private void Close()
		{
			this.Connection.Close();
		}

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

		public void CommandTimeout(int minutes)
		{
			if (minutes < 0)
				this.CommandTimeoutSeconds = -1;
			else
				this.CommandTimeoutSeconds = minutes * 60;
		}

		//
		//	Current
		//

		public string CurrentDatabase()
		{
			return this.CurrentDatabaseCache;
		}
		public void ChangeDatabase(string databaseName)
		{
			this.ChangedDatabase = databaseName;

			object result = this.ExecuteScalar("SELECT DATABASE();");
			if (result == null || result is DBNull)
				databaseName = null;
			else
				databaseName = (string)result;

			if (databaseName == "mysql")
				databaseName = null;

			//	Zero length ? test

			this.CurrentDatabaseCache = databaseName;
		}

		//
		//	Transactions
		//

		public void Begin()
		{
			if (this.Transaction != null)
				throw new ApplicationException("DALMySql: 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("DALMySql: Commit Transaction not present.");

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

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

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

		//
		//	Schema
		//

		public DataSet Schema()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySql; To get the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			DataSet schema = (DataSet)Spludlow.Caching.Get(key);

			if (schema != null)
				return schema;

			schema = new DataSet();

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

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

			Spludlow.Caching.Set(key, schema);

			return schema;
		}

		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;
		}

		private void SchemaColumns(DataSet schema, string tableName, DataSet schemaReader, DataSet schemaNative)
		{
			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"];

				DataRow nativeRow = schemaNative.Tables["COLUMNS"].Rows.Find(new object[] { tableName, columnName });
				if (nativeRow == null)
					throw new ApplicationException("DAL MySql Schema; Can not find native Schema row for column: " + tableName + "." + columnName);

				string typeName = (string)nativeRow["DATA_TYPE"];

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

				if (typeName == "tinyint" && columnSize == 1)
					typeName += "(1)";

				string columnType = (string)nativeRow["COLUMN_TYPE"];
				if (columnType.Contains("unsigned") == true)
					typeName += " unsigned";

				DataRow schemaRow = schemaTable.NewRow();

				schemaRow["ColumnName"] = columnName;
				schemaRow["Ordinal"] = (int)readerRow["ColumnOrdinal"];
				schemaRow["DataTypeId"] = Spludlow.Data.Schemas.NativeToCommon(typeName, "MySql");
				schemaRow["MaxLength"] = columnSize;
				schemaRow["Precision"] = (int)readerRow["NumericPrecision"];
				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 schemaStandard)
		{
			DataTable keysTable = Spludlow.Data.Schemas.NewSchemaKeysTable(tableName);

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

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

				foreach (DataRow indexColumnRow in schemaStandard.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_ORDER"] != "A")
						descending = true;
					
					//	MySQL allows same index across tables, other DB types no not, so prefix the table name

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

			foreach (DataRow foreignKeyRow in schemaStandard.Tables["Foreign Keys"].Select("table_name = '" + tableName + "'"))
			{
				string constraintName = (string)foreignKeyRow["constraint_name"];

				foreach (DataRow keyColumnRow in schemaStandard.Tables["Foreign Key Columns"].Select("CONSTRAINT_NAME = '" + constraintName + "' AND TABLE_NAME = '" + tableName + "'", "ORDINAL_POSITION"))
				{
					string columnName = (string)keyColumnRow["COLUMN_NAME"];
					string parentTable = (string)keyColumnRow["REFERENCED_TABLE_NAME"];
					string parentColumn = (string)keyColumnRow["REFERENCED_COLUMN_NAME"];
					int ordinal = (int)(Int64)keyColumnRow["ORDINAL_POSITION"];							//	MySQL dopes !!!!

					keysTable.Rows.Add(new object[] { constraintName, "F", ordinal, false, columnName, parentTable, parentColumn });
				}
			}

			schema.Tables.Add(keysTable);
		}

		public DataSet SchemaNative()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySql; To get the Native schema you must have a current database");

			//"CHARACTER_SETS",
			//"COLLATIONS",
			//"COLLATION_CHARACTER_SET_APPLICABILITY",
			//"COLUMNS",
			//"COLUMN_PRIVILEGES",
			//"ENGINES",
			//"EVENTS",
			//"FILES",
			//"GLOBAL_STATUS",
			//"GLOBAL_VARIABLES",
			//"KEY_COLUMN_USAGE",
			//"ndb_transid_mysql_connection_map",
			//"OPTIMIZER_TRACE",
			//"PARAMETERS",
			//"PARTITIONS",
			//"PLUGINS",
			//"PROCESSLIST",
			//"PROFILING",
			//"REFERENTIAL_CONSTRAINTS",
			//"ROUTINES",
			//"SCHEMATA",
			//"SCHEMA_PRIVILEGES",
			//"STATISTICS",
			//"TABLES",
			//"TABLESPACES",
			//"TABLE_CONSTRAINTS",
			//"TABLE_PRIVILEGES",
			//"TRIGGERS",
			//"USER_PRIVILEGES",
			//"VIEWS",

			string[] systemTables = new string[]
			{
				"COLUMNS					TABLE_SCHEMA",
				"KEY_COLUMN_USAGE			CONSTRAINT_SCHEMA",
				"REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA",
				"SCHEMATA					SCHEMA_NAME",
				"TABLES						TABLE_SCHEMA",
				"TABLE_CONSTRAINTS			CONSTRAINT_SCHEMA",
			};

			DataSet dataSet = new DataSet();

			string commandTemplate = "SELECT * FROM information_schema.@TableName WHERE (@FilterColumn = '@DatabaseName')";
			
			foreach (string systemTableInfo in systemTables)
			{
				string[] words = Spludlow.Text.Split(systemTableInfo, '\t', true);
				string systemTableName = words[0];
				string filterColumn = words[1];

				string commandText = commandTemplate;
				commandText = commandText.Replace("@TableName", systemTableName);
				commandText = commandText.Replace("@FilterColumn", filterColumn);
				commandText = commandText.Replace("@DatabaseName", databaseName);

				this.Fill(dataSet, commandText);

				Spludlow.Data.ADO.NameLastTable(dataSet, systemTableName);
			}

			DataTable table;

			table = dataSet.Tables["TABLES"];
			table.PrimaryKey = new DataColumn[] { table.Columns["TABLE_NAME"] };

			table = dataSet.Tables["COLUMNS"];
			table.PrimaryKey = new DataColumn[] { table.Columns["TABLE_NAME"], table.Columns["COLUMN_NAME"] };

			return dataSet;
		}



		public DataSet SchemaConnection()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySql; To get the Standard schema you must have a current database");

			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;

			string[] fixTables = new string[]
			{
				"Views						TABLE_SCHEMA",
				"ViewColumns				VIEW_SCHEMA",
				"Procedure Parameters		SPECIFIC_SCHEMA",
				"Procedures					ROUTINE_SCHEMA",
				"Triggers					TRIGGER_SCHEMA",
			};

			foreach (string fixTableInfo in fixTables)
			{
				string[] words = Spludlow.Text.Split(fixTableInfo, '\t', true);
				string tableName = words[0];
				string filterColumn = words[1];

				table = schema.Tables[tableName];
				DataTable copyTable = table.Copy();
				table.Clear();

				foreach (DataRow row in copyTable.Rows)
				{
					if (row.IsNull(filterColumn) == false && (string)row[filterColumn] == databaseName)
						table.ImportRow(row);
				}
			}

			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 void SchemaRefresh()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySql; To Refresh the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			Spludlow.Caching.Remove(key);
		}


		//
		//	Databases
		//
		public string[] DatabaseList()
		{
			DataTable table = this.Select("select SCHEMA_NAME from information_schema.SCHEMATA");

			List<string> databases = new List<string>();
			foreach (DataRow row in table.Rows)
			{
				string name = (string)row[0];

				if (SystemDatabaseNames.Contains(name) == false)
					databases.Add(name);
			}
			return databases.ToArray();
		}
		public void DatabaseCreate(string databaseName)
		{
			string commandText = "CREATE SCHEMA " + databaseName;
			this.ExecuteNonQuery(commandText);
		}

		public void DatabaseCreate(string databaseName, DataSet schema)
		{
			Spludlow.Data.DALCommon.DatabaseCreate(this, databaseName, schema);
		}

		public void DatabaseDelete(string databaseName)
		{
			string commandText = "DROP DATABASE " + databaseName;
			this.ExecuteNonQuery(commandText);
		}

		public bool DatabaseExists(string databaseName)
		{
			string commandText = "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + databaseName + "'";

			object result = this.ExecuteScalar(commandText);

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

			return true;
		}

		//
		//	Tables
		//

		public void TableClear(string tableName)
		{
			this.ExecuteNonQuery("TRUNCATE TABLE `" + tableName + "`");	//	MySQL handles foriegn keys for TRUNCATE itself ?
		}

		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 MySQL; To create a table you must have a current database");

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

			info.DatabaseType = "MySql";

			info.QuoteOpen = "`";
			info.QuoteClose = "`";

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

			info.Identity = "AUTO_INCREMENT";

			info.ColumnLine = "@ColumnName @DataType @Null @Identity";
			info.PrimaryLine = "CONSTRAINT @KeyName PRIMARY KEY (@Columns)";    //	"PRIMARY KEY (@Columns)";
			info.UniqueLine = null;		//	"UNIQUE KEY @KeyName (@Columns)";
			info.ForeignLine = null;    //	"FOREIGN KEY @KeyName (@Columns) REFERENCES @ParentTable(@ParentColumns)";
			info.IndexLine = null;		//	"INDEX @KeyName (@Columns)";

			string commandText = Spludlow.Data.DALCommon.MakeCreateTableCommandText(tableName, schemaColumns, schemaKeys, info);

			Spludlow.Log.Report("DAL; Creating Table: " + tableName, new object[] { schemaColumns, schemaKeys, commandText });

			this.ExecuteNonQuery(commandText);
		}

		public void TableDelete(string tableName)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySQL; To delete a table you must have a current database");

			string commandText = "DROP TABLE `" + tableName + "`";
			this.ExecuteNonQuery(commandText);
		}

		public bool TableExists(string tableName)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySQL; To perform a TableExists you must have a current database");

			string commandText = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE (TABLE_SCHEMA = @DatabaseName AND TABLE_NAME = @TableName)";

			object result = this.ExecuteScalar(commandText, new object[] { databaseName, tableName });

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

			return true;
		}

		public string[] TableList()
		{
			string database = this.CurrentDatabase();
			if (database == null)
				throw new ApplicationException("DAL MySQL; To list tables you must have a current database");

			DataTable table = this.Select("select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA = '" + database + "'");

			List<string> tableNames = new List<string>();
			foreach (DataRow row in table.Rows)
				tableNames.Add((string)row[0]);

			return tableNames.ToArray();
		}

		public void TableAddKeys(DataSet schema, string keyTypesPUIF)
		{
			string[] tableNames = Spludlow.Data.Schemas.OrderTablesOnAncestors(this.TableList(), schema);   //	Need ?
			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)
		{
			string commandTextPrimary = "ALTER TABLE `@TableName` ADD CONSTRAINT `@KeyName` PRIMARY KEY (@Columns)";
			string commandTextUnique = "CREATE UNIQUE INDEX `@KeyName` ON `@TableName` (@Columns)";
			string commandTextIndex = "CREATE INDEX `@KeyName` ON `@TableName` (@Columns)";
			string commandTextForeign = "ALTER TABLE `@TableName` ADD CONSTRAINT `@KeyName` FOREIGN KEY (@Columns) REFERENCES `@ParentTable` (@ParentColumns)";

			foreach (string commandText in DALCommon.TableAddKeysCommandText(tableNames, schema, keyTypesPUIF, commandTextPrimary, commandTextUnique, commandTextIndex, commandTextForeign, this.QuoteOpen, this.QuoteClose))
				this.ExecuteNonQuery(commandText);
		}

		//
		//	Login
		//
		public void LoginCreate(string loginName, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void LoginCreate(string loginName, string password, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void LoginDelete(string loginName)
		{
			throw new NotImplementedException();
		}

		public bool LoginExists(string loginName)
		{
			throw new NotImplementedException();
		}

		public string[] LoginList()
		{
			throw new NotImplementedException();
		}

		//
		//	User
		//
		public void UserCreate(string userName, string loginName, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void UserDelete(string userName)
		{
			throw new NotImplementedException();
		}

		public bool UserExists(string userName)
		{
			throw new NotImplementedException();
		}

		public string[] UserList()
		{
			throw new NotImplementedException();
		}


		//
		//	Util
		//

		public void Backup(string serverFilename)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySQL; To perform a Backup you must have a current database");

			string mysqldumpPath = this.BinDirectory() + @"\mysqldump.exe";

			if (File.Exists(mysqldumpPath) == false)
				throw new ApplicationException("DAL MySQL, Backup; Can't find mysqldump: " + mysqldumpPath);

			string defaultsFilename = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\" + "my.ini";

			if (File.Exists(defaultsFilename) == false)
				throw new ApplicationException("DAL MySQL, Backup; Can't find defaults File, it is required for backup and should contain credentials: " + defaultsFilename);

			StringBuilder arguments = new StringBuilder("--defaults-file=\"@DefaultsFile\" --host=\"@Server\" --result-file=\"@Filename\" \"@Database\"");

			arguments.Replace("@DefaultsFile", defaultsFilename);
			arguments.Replace("@Server", this.DataSource());
			arguments.Replace("@Filename", serverFilename);
			arguments.Replace("@Database", databaseName);

			Spludlow.SpawnProcess.ProcessExitInfo info = Spludlow.SpawnProcess.Run(mysqldumpPath, arguments.ToString(), 0);	//	Can hang if something has DB locked
			
			if (info.StandardError.Length == 0 && info.ExitCode == 0)
				return;

			throw new ApplicationException("Archive, Create 7Zip, ExitCode:" + info.ExitCode + ", " + Environment.NewLine + "StandardError:" + info.StandardError);	// + Environment.NewLine + 			"StandardOutput:" + info.StandardOutput);

		}

		public void Restore(string serverFilename)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL MySQL; To perform a Restore you must have a current database");

			string server = this.DataSource();

			this.RunScript(serverFilename, server, databaseName);
		}
		public string RunScript(string localFilename, string server, string database)     //	Utility method not using any object varibles, can use with null constructor
		{
			return this.RunScript(localFilename, server, database, null, null);
		}
		public string RunScript(string localFilename, string server, string database, string userName, string password)
		{
			string mysqldumpPath = this.BinDirectory() + @"\mysql.exe";

			string defaultsFilename = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\" + "my.ini";

			string execute = "source " + localFilename;

			string[] argumentNames;
			string[] argumentValues;

			if (userName == null)
			{
				argumentNames = new string[] { "--defaults-file", "--host", "--database", "--execute" };
				argumentValues = new string[] { defaultsFilename, server, database, execute };
			}
			else
			{
				argumentNames = new string[] { "--user", "--password", "--host", "--database", "--execute" };
				argumentValues = new string[] { userName, password, server, database, execute };
			}

			StringBuilder arguments = new StringBuilder();
			for (int index = 0; index < argumentNames.Length; ++index)
			{
				if (arguments.Length > 0)
					arguments.Append(" ");

				arguments.Append(argumentNames[index]);
				arguments.Append("=\"");
				arguments.Append(argumentValues[index]);
				arguments.Append("\"");
			}

			string argumentsText = arguments.ToString();

			Spludlow.Log.Info("DAL MySQL; Running Script: " + mysqldumpPath + " " + argumentsText);

			Spludlow.SpawnProcess.ProcessExitInfo exitInfo = Spludlow.SpawnProcess.Run(mysqldumpPath, argumentsText, 0); //	No timeout !!!!

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

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

			return exitInfo.StandardOutput;

		}

		private string BinDirectory()
		{
			string binDirectory = Spludlow.Config.Get("MySQLBinDirectory", true);

			if (binDirectory != null)
				return binDirectory;

			foreach (string productDirectory in Directory.GetDirectories(@"C:\Program Files\MySQL", "MySQL Server *"))
			{
				string posibleDirectory = productDirectory + @"\bin";
				if (Directory.Exists(posibleDirectory) == true)
				{
					if (binDirectory != null)
						Spludlow.Log.Warning("DAL MySQL, Finding BIN Directory; Multiple matches found");

					binDirectory = posibleDirectory;
				}
			}

			if (binDirectory == null)
				throw new ApplicationException("DAL MySQL, Finding BIN Directory; Did not found, you can use the config key 'MySQLBinDirectory' to specify it");

			return binDirectory;
		}

		public void Operation(string details)
		{
			if (details.StartsWith("identity insert") == true)	//	Don't need to do anything for MySQL
				return;

			if (details.StartsWith("shrink") == true)
				return;

			throw new ApplicationException("DAL SQL Operation; Unknown: " + details);
		}

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

		//
		//	Command
		//

		private static void FixCommandText(MySqlCommand command)
		{
			if (command.CommandText.Contains("[") == false)
				return;

			StringBuilder text = new StringBuilder(command.CommandText);
			text.Replace("[", "`");
			text.Replace("]", "`");

			command.CommandText = text.ToString();
		}

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

			MySqlDbType sqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), nativeType);

			info.ParameterTypes[index] = (int)sqlDbType;
			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);
		}

		private MySqlCommand MakeCommandNative(string commandText)
		{
			MySqlCommand command = new MySqlCommand(commandText, this.Connection, this.Transaction);

			if (this.CommandTimeoutSeconds >= 0)
				command.CommandTimeout = this.CommandTimeoutSeconds;

			return command;
		}

		public DbCommand MakeCommand(string commandText)
		{
			return this.MakeCommandNative(commandText);
		}

		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);

			MySqlCommand command = this.MakeCommandNative(commandInfo.CommandText);

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

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

				MySqlParameter parameter = command.CreateParameter();

				parameter.ParameterName = paramterName;


				if ((int)dbType != -1)
					parameter.MySqlDbType = dbType;   //	seems to make slightly slower ???????????????????????????????? try differnt DALs

				//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 (MySqlCommand command = (MySqlCommand)this.MakeCommand(commandText))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, object[] parameterValues)
		{
			using (MySqlCommand command = (MySqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (MySqlCommand command = (MySqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (MySqlCommand command = (MySqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DbCommand command)
		{
			return this.ExecuteScalar((MySqlCommand)command);
		}

		private object ExecuteScalar(MySqlCommand command)
		{
			FixCommandText(command);

			if (this.Transaction != null)
				return command.ExecuteScalar();

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

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

		private int ExecuteNonQuery(MySqlCommand command)
		{
			FixCommandText(command);

			if (this.Transaction != null)
				return command.ExecuteNonQuery();

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



		//
		//	Fill
		//

		private void ExecuteAdapter(MySqlCommand command, DataSet dataSet, DataTable table)
		{
			FixCommandText(command);

			if (this.Transaction == null)
				this.Open();

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

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

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

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

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

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

		//
		//	Select
		//

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

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


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





		//
		//	Inserter
		//

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

		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 (MySqlCommand command = (MySqlCommand)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 Insert; Identity object is null.");

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

					throw new ApplicationException("DAL 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 (MySqlCommand command = (MySqlCommand)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);
		}

		/// <summary>
		/// Not very well implimented at the moment
		/// </summary>
		public void BulkInsert(DataTable table, string tableName, string tempDirectory)	//	Awfully slow on UK BasicCompanyData
		{
			Encoding encoding = Encoding.UTF8;

			string filename = tempDirectory + @"\" + tableName + "-" + Spludlow.Text.TimeStamp() + ".txt";

			try
			{
				Spludlow.Data.TextTable.Write(filename, table, encoding);

				MySqlBulkLoader bulkLoad = new MySqlBulkLoader(this.Connection);

				bulkLoad.TableName = tableName;

				bulkLoad.FileName = filename;

				//bulkLoad.CharacterSet

				bulkLoad.FieldTerminator = "\t";
				bulkLoad.LineTerminator = Environment.NewLine;

				bulkLoad.FieldQuotationCharacter = '\"';        //	To Handle empty strings ""
				bulkLoad.FieldQuotationOptional = true;

				bulkLoad.NumberOfLinesToSkip = 2;

				this.Open();

				try
				{
					bulkLoad.Load();
				}
				finally
				{
					this.Close();
				}
			}
			finally
			{
				if (File.Exists(filename) == true)
					File.Delete(filename);
			}




		}

		public DataSet SchemaANSI()
		{
			throw new NotImplementedException();
		}


	}
}