//	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 Npgsql;

namespace Spludlow.Data
{
	/// <summary>
	/// PostgreSQL DAL Implimentation - Fairly impliented, not tested much
	/// 
	/// http://www.npgsql.org/
	/// Npgsql.dll
	/// System.Threading.Tasks.Extensions.dll (refernced by Npgsql.dll should get automatically copied to bin)
	/// 
	/// C:\Program Files\PostgreSQL\9.5\data\pg_hba.conf needs configering on server to allow client access
	/// 
	/// </summary>
	public class DALPostgreSQL : Spludlow.Data.IDAL
	{
		private NpgsqlConnection Connection;
		private NpgsqlTransaction 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 List<string> SystemDatabaseNames = new List<string>(new string[]
		{
//			"sys",
		});

		public DALPostgreSQL()
		{
		}
		public DALPostgreSQL(string connectionString)
		{
			this.Initialize(connectionString);
		}
		public void Initialize(string connectionString)
		{
			if (connectionString == null || connectionString.Length == 0)
				return;

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

			this.Connection = new NpgsqlConnection(connectionString);

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

			this.ConvertDataTypes = new Dictionary<string, string>();
			string[] convertLookups = new string[]
			{
				"bool				boolean",

				"int2				smallint",
				"int				integer",
				"int4				integer",
				"int8				bigint",

				"decimal			numeric",

				"bpchar				character",
				"char				character",

				"varchar			character varying",

				"float4				real",
				"float8				double precision",

				"serial4			serial",
				"serial8			bigserial",

			};
			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", "User Id", "Password" };
			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("';");
			}

			if (userName == null)
				connectionString.Append("Integrated Security=true;");

			return connectionString.ToString();
		}
		private void Open()
		{
			this.Connection.Open();

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

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

		}

		//
		//	Current
		//

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

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

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

			this.CurrentDatabaseCache = databaseName;
		}

		//
		//	Transactions
		//

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

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

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

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

		//
		//	Schema
		//

		//public static void ReportDataTypes()
		//{
		//	List<string> list = new List<string>();

		//	foreach (string name in Enum.GetNames(typeof(NpgsqlTypes.NpgsqlDbType)))
		//		list.Add(name);

		//	Spludlow.Log.Report("ReportDataTypes", list.ToArray());
		//}

		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 schemaConnection = this.SchemaConnection();
			DataSet schemaANSI = this.SchemaANSI();

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

			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, DataSet schemaConnection)
		{

			SysTables.SchemaColumnsDataTable 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 connectionRow = schemaConnection.Tables["Columns"].Rows.Find(new object[] { tableName, columnName });
				if (connectionRow == null)
					throw new ApplicationException("DAL PostgrSQL Schema; Can not find Connection Schema row for column: " + tableName + "." + columnName);

				string nativeDataType = (string)connectionRow["data_type"];

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

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

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

				SysTables.SchemaColumnsRow schemaRow = schemaTable.NewSchemaColumnsRow();

				schemaRow.ColumnName = columnName;
				schemaRow.Ordinal = (int)readerRow["ColumnOrdinal"];
				schemaRow.DataTypeId = Spludlow.Data.Schemas.NativeToCommon(nativeDataType, "PostgreSQL");
				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 schemaReader, DataSet schemaNative, DataSet schemaConnection, DataSet schemaANSI)
		{
			SysTables.SchemaKeysDataTable keysTable = Spludlow.Data.Schemas.NewSchemaKeysTable(tableName);

			DataRow[] rows;

			////	DESC ?

			//	PK
			rows = schemaANSI.Tables["table_constraints"].Select("constraint_schema = 'public' AND constraint_type = 'PRIMARY KEY' AND table_name = '" + tableName + "'");

			if (rows.Length > 1)
				throw new ApplicationException("DAL PostgreSQL Schema Keys; More than one PK for table:" + tableName);

			if (rows.Length == 1)
			{
				string keyName = (string)rows[0]["constraint_name"];

				rows = schemaANSI.Tables["key_column_usage"].Select("constraint_schema = 'public' AND constraint_name = '" + keyName + "' AND table_name = '" + tableName + "'", "ordinal_position");

				if (rows.Length == 0)
					throw new ApplicationException("DAL PostgreSQL Schema Keys; Nothing in key_column_usage on PK for table:" + tableName + ", keyname:" + keyName);

				foreach (DataRow row in rows)
				{
					string columnName = (string)row["column_name"];
					int ordinal = (int)row["ordinal_position"];
					keysTable.Rows.Add(new object[] { keyName, "P", ordinal, false, columnName, null, null });
				}
			}


			//	Indexes

			//	Get Table Id
			rows = schemaNative.Tables["pg_class"].Select("relname = '" + tableName + "' AND relkind = 'r'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL PostgreSQL Schema Keys; Did not find one row in pg_class for table:" + tableName);
			uint tableId = (uint)rows[0]["oid"];

			//	Indexes for table
			foreach (DataRow indexRow in schemaNative.Tables["pg_index"].Select("indrelid = " + tableId))
			{
				if ((bool)indexRow["indisprimary"] == true)     //	Already have from above
					continue;

				string keyType = "I";
				if ((bool)indexRow["indisunique"] == true)
					keyType = "U";

				uint indexId = (uint)indexRow["indexrelid"];

				//	Get Index name
				rows = schemaNative.Tables["pg_class"].Select("oid = " + indexId + " AND relkind = 'i'");
				if (rows.Length != 1)
					throw new ApplicationException("DAL PostgreSQL Schema Keys; Did not find one row in pg_class for index:" + tableName + ", indexId:" + indexId);
				string indexName = (string)rows[0]["relname"];


				//	Index Columns
				foreach (DataRow indexColumnRow in schemaNative.Tables["pg_attribute"].Select("attrelid = " + indexId, "attnum"))
				{
					string columnName = (string)indexColumnRow["attname"];
					int ordinal = (Int16)indexColumnRow["attnum"];

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



			//	FKs

			foreach (DataRow keyRow in schemaANSI.Tables["table_constraints"].Select("constraint_schema = 'public' AND constraint_type = 'FOREIGN KEY' AND table_name = '" + tableName + "'"))
			{
				//	Key Name

				string keyName = (string)keyRow["constraint_name"];

				//	Parent PK Name

				rows = schemaANSI.Tables["referential_constraints"].Select("constraint_schema = 'public' AND constraint_name = '" + keyName + "'");

				if (rows.Length != 1)
					throw new ApplicationException("DAL PostgreSQL Schema Keys; Did not find 1 row in referential_constraints on FK for table:" + tableName + ", keyname:" + keyName + ", rows:" + rows.Length);

				string parentPrimaryKeyName = (string)rows[0]["unique_constraint_name"];

				//	Key Columns

				DataRow[] keyColmnRows = schemaANSI.Tables["key_column_usage"].Select("constraint_schema = 'public' AND constraint_name = '" + keyName + "' AND table_name = '" + tableName + "'", "ordinal_position");

				if (keyColmnRows.Length == 0)
					throw new ApplicationException("DAL PostgreSQL Schema Keys; Nothing in key_column_usage on FK for table:" + tableName + ", keyname:" + keyName);

				foreach (DataRow keyColumnRow in keyColmnRows)
				{
					string columnName = (string)keyColumnRow["column_name"];
					int ordinal = (int)keyColumnRow["ordinal_position"];

					//	Parent PK Column rows
					DataRow[] parentPrimaryKeyRows = schemaANSI.Tables["key_column_usage"].Select("constraint_schema = 'public' AND constraint_name = '" + parentPrimaryKeyName + "' AND ordinal_position = " + ordinal); //	dup key names accross tables?

					if (parentPrimaryKeyRows.Length != 1)
						throw new ApplicationException("DAL PostgreSQL Schema Keys; Din not find one parent PK column for FK for table:" + tableName + ", keyname:" + keyName + ", ParentPK: " + parentPrimaryKeyName + ", rows:" + parentPrimaryKeyRows.Length);

					string parentTableName = (string)parentPrimaryKeyRows[0]["table_name"];
					string parentColumnName = (string)parentPrimaryKeyRows[0]["column_name"];

					keysTable.Rows.Add(new object[] { keyName, "F", ordinal, false, columnName, parentTableName, parentColumnName });
				}
			}



			schema.Tables.Add(keysTable);
		}

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

			DataSet dataSet = new DataSet();

			string[] names = new string[]
			{
				"pg_attribute",
				"pg_class",
				"pg_index",
				"pg_namespace",

				"pg_indexes",
				"pg_tables",
			};

			foreach (string name in names)
			{
				string selectColumns = "*";

				if (name == "pg_attribute")
					selectColumns = "attrelid, attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage, attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attoptions, attfdwoptions";

				if (name == "pg_class")
					selectColumns = "oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids, relhaspkey, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relfrozenxid, relminmxid, reloptions";

				if (name == "pg_namespace")
					selectColumns = "oid, nspname, nspowner";

				DataTable table = this.Select("SELECT " + selectColumns + " FROM pg_catalog." + name);

				List<string> columnNames = new List<string>();
				foreach (DataColumn column in table.Columns)
				{
					if (column.DataType.IsArray == true || column.DataType == typeof(Array))
						columnNames.Add(column.ColumnName);
				}
				foreach (string columnName in columnNames)
					table.Columns.Remove(table.Columns[columnName]);

				table.TableName = name;
				dataSet.Tables.Add(table);
			}
			
			DataRow[] rows;

			rows = dataSet.Tables["pg_namespace"].Select("nspname = 'public'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL PostgreSQL; Can not find publicNamespaceId in native Schema");
			uint publicNamespaceId = (uint)rows[0]["oid"];

			//	Remove classes not in public namespace
			rows = dataSet.Tables["pg_class"].Select("relnamespace <> " + publicNamespaceId);
			foreach (DataRow row in rows)
				row.Delete();
			dataSet.Tables["pg_class"].AcceptChanges();

			//	Remove attributes not in classes
			List<uint> classIds = new List<uint>();
			foreach (DataRow row in dataSet.Tables["pg_class"].Rows)
				classIds.Add((uint)row["oid"]);

			List<DataRow> deleteList = new List<DataRow>();
			foreach (DataRow row in dataSet.Tables["pg_attribute"].Rows)
			{
				uint attrelid = (uint)row["attrelid"];

				if (classIds.Contains(attrelid) == false)
					row.Delete();
			}
			dataSet.Tables["pg_attribute"].AcceptChanges();

			return dataSet;
		}

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

			DataSet schema;

			if (this.Transaction == null)
				this.Open();
			try
			{
				schema = Spludlow.Data.DALCommon.SchemaReader(this.Connection, this.Transaction, tableNames, this.QuoteOpen, this.QuoteClose);
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}


			return schema;
		}

		public DataSet SchemaConnection()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL PostgreSQL; 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, "Schema", "public");
			}
			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"] };

			return schema;
		}

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

			string key = this.CacheKeyPrefix + databaseName;

			Spludlow.Caching.Remove(key);
		}

		public DataSet SchemaANSI()
		{
			string[] viewNames = new string[]
			{
				"columns",
				"constraint_column_usage",
				"constraint_table_usage",
				"key_column_usage",
				"schemata",
				"referential_constraints",
				"table_constraints",
				"tables",
			};

			DataSet dataSet = new DataSet();

			foreach (string viewName in viewNames)
			{
				DataTable table = this.Select("SELECT * FROM INFORMATION_SCHEMA." + viewName);
				table.TableName = viewName;

				dataSet.Tables.Add(table);
			}

			return dataSet;
		}

		//
		//	Databases
		//

		public string[] DatabaseList()
		{
			DataTable table = this.Select("SELECT datname FROM pg_catalog.pg_database WHERE (datistemplate = FALSE AND datname <> 'postgres')");

			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 bool DatabaseExists(string databaseName)
		{
			string commandText = "SELECT datname FROM pg_catalog.pg_database WHERE datname = '" + databaseName + "'";

			object result = this.ExecuteScalar(commandText);

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

			return true;
		}

		public void DatabaseCreate(string databaseName)
		{
			this.ExecuteNonQuery("CREATE DATABASE \"" + databaseName + "\"");
		}

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

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

		//
		//	Tables
		//

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

			DataTable table = this.Select("SELECT table_name FROM information_schema.tables WHERE table_schema='public';");

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

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

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

			info.DatabaseType = "PostgreSQL";

			info.QuoteOpen = "\"";
			info.QuoteClose = "\"";

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

			info.Identity = "__SERIAL__";

			info.ColumnLine = "@ColumnName @DataType @Null @Identity";
			info.PrimaryLine = "CONSTRAINT @KeyName PRIMARY KEY (@Columns)";

			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)
					{
						if (line.Contains(info.Identity) == true)
						{
							string dataType = "SERIAL";
							if (line.Contains(" bigint ") == true)
								dataType = "BIGSERIAL";

							int index = line.IndexOf("\"", 1);
							line = line.Substring(0, index + 1) + " " + dataType + ",";
							result.AppendLine(line);
							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();
			}

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

			this.ExecuteNonQuery(commandText);
		}

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


		public void TableClear(string tableName)
		{
			this.ExecuteNonQuery("TRUNCATE TABLE \"" + tableName + "\"");
		}
		
		public void TableDelete(string tableName)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL PostgrSQL; 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 PostgrSQL; To perform a TableExists you must have a current database");

			string commandText = "SELECT table_name FROM information_schema.tables WHERE (table_schema = 'public' AND table_name = @TableName)";

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

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

			return true;

		}

		//
		//	Login
		//
		public void LoginCreate(string loginName, bool administrator)
		{
			LoginCreate(loginName, null, administrator);
		}

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

		}

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

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


		public void Backup(string serverFilename)
		{
			throw new NotImplementedException();
		}

		public void Restore(string serverFilename)
		{
			throw new NotImplementedException();
		}
		
		public string RunScript(string localFilename, string server, string database)
		{
			throw new NotImplementedException();
		}

		public string RunScript(string localFilename, string server, string database, string userName, string password)
		{
			throw new NotImplementedException();
		}

		public void Operation(string details)
		{
			throw new NotImplementedException();
		}

		public string PageCommandText(string commandText, int pageIndex, int pageSize)
		{
			throw new NotImplementedException();
		}

		//
		//	Command
		//

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

			NpgsqlTypes.NpgsqlDbType dbType = (NpgsqlTypes.NpgsqlDbType)Enum.Parse(typeof(NpgsqlTypes.NpgsqlDbType), 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);
		}

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

			//	The time in seconds to wait for the command to execute. The default is 30 seconds.
			//command.CommandTimeout = 20 * 60;
			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);

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

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

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

				NpgsqlParameter parameter = command.CreateParameter();

				parameter.ParameterName = paramterName;

				if ((int)dbType != -1)
					parameter.NpgsqlDbType = 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 (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DbCommand command)
		{
			return this.ExecuteScalar((NpgsqlCommand)command);
		}

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

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

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

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

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

		//
		//	Fill
		//

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

			try
			{
				using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(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 (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (NpgsqlCommand command = (NpgsqlCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DbCommand command)
		{
			this.Fill(dataSet, (NpgsqlCommand)command);
		}

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

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

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

		//
		//	Select
		//

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

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


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


		//
		//	Inserter
		//

		public DAL.CommandInfo MakeInserter(string tableName)
		{
			string identityColumn = "";

			DataRow[] rows = this.Schema().Tables[tableName + "_Columns"].Select("AutoIncrement = 1");
			if (rows.Length > 1)
				throw new ApplicationException("DAL PostgrSQL, MakeInserter; More than 1 AutoIncrement column not supported for table: " + tableName);
			if (rows.Length == 1)
				identityColumn = (string)rows[0]["ColumnName"];

			return DALCommon.MakeInserter(this, tableName, " RETURNING \"" + identityColumn + "\"");
		}

		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 (NpgsqlCommand command = (NpgsqlCommand)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 Int64)
						return (long)((Int64)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 (NpgsqlCommand command = (NpgsqlCommand)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)
		{
			throw new NotImplementedException();
		}




	}
}