Disconnected Client Architecture

/*
Copyright (c) 2007, Marc Clifton
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list
  of conditions and the following disclaimer. 

* Redistributions in binary form must reproduce the above copyright notice, this 
  list of conditions and the following disclaimer in the documentation and/or other
  materials provided with the distribution. 
 
* Neither the name of Marc Clifton nor the names of its contributors may be
  used to endorse or promote products derived from this software without specific
  prior written permission. 

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text;

using DCA.CommandPackets;

using Clifton.Data;
using Clifton.Tools.Data;
using Clifton.Tools.Strings;

using DisconnectedClientArchitecture.Client;

namespace DCA.ClientAPI
{
	/// <summary>
	/// Concrete implementation of a disconnected server communication service.
	/// Transactions are stored using Sqlite.
	/// </summary>
	public class DisconnectedServerComm : ServerComm
	{
		protected DbConnection sqliteConn;
		protected SchemaService schemaService;

		public DisconnectedServerComm(SchemaService schemaService)
		{
			this.schemaService=schemaService;
			bool existingDb = File.Exists("transactions.db");
			sqliteConn = new SQLiteConnection();
			sqliteConn.ConnectionString = "Data Source=transactions.db";
			// Password protect a new database or use this password for an existing database.
			// TODO: Use your own password.
			((SQLiteConnection)sqliteConn).SetPassword(new byte[] { 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08 }); 
			sqliteConn.Open();

			if (!existingDb)
			{
				CreateTransactionTables();
			}
		}

		/// <summary>
		/// Calls the connection failure handler to simulate connecting to the server.
		/// </summary>
		public override void Connect()
		{
			HandleConnectionFailure();
		}

		/// <summary>
		/// Calls the command failure handler to handle the command locally.
		/// </summary>
		/// <param name="cmd"></param>
		public override void WriteCommand(ICommand cmd)
		{
			HandleCommandFailure(cmd);
		}

		/// <summary>
		/// Calls the response failure handler to handle the response locally.
		/// </summary>
		/// <param name="cmd"></param>
		/// <param name="resp"></param>
		public override void ReadResponse(ICommand cmd, IResponse resp)
		{
			HandleResponseFailure(cmd, resp);
		}

		/// <summary>
		/// Disconnecting in a disconnected mode does nothing.
		/// </summary>
		public override void Disconnect()
		{
			// Do nothing.
		}

		/// <summary>
		/// Stopping the reader thread in a disconnected mode does nothing.
		/// </summary>
		public override void StopReaderThread()
		{
			// Do nothing.
		}

		/// <summary>
		/// Simulate the connection by creating a session ID for the login response.
		/// The user ID cannot be established at this point if it doesn't already exist.
		/// </summary>
		public void HandleConnectionFailure()
		{
			sessionID = Guid.NewGuid();
		}

		/// <summary>
		/// Essentially does nothing, but in the future might authenticate the user locally.
		/// This would possibly involve dealing with menu, dialog, record, and field permissions as well.
		/// </summary>
		/// <param name="cmd"></param>
		public void HandleCommandFailure(ICommand cmd)
		{
			switch ((Command)cmd.CommandId)
			{
				case Command.Login:
					// TODO: Do we allow unauthorized login?
					// TODO: What about permissions?
					break;
			}
		}

		/// <summary>
		/// Synthesizes the appropriate response for the given command/response pair.
		/// </summary>
		/// <param name="cmd"></param>
		/// <param name="resp"></param>
		public void HandleResponseFailure(ICommand cmd, IResponse resp)
		{
			bool handled = false;

			// Check command for special handling.
			switch ((Command)cmd.CommandId)
			{
				case Command.PostTransactions:
					// A PostTransactions command logs the transactions to the local database.
					PostTransactionsCommand ptc = (PostTransactionsCommand)cmd;
					SaveTransactions(ptc, true);
					handled = true;
					break;

				// Deprecated.
				//case Command.NewSessionID:
				//    ((LoginResponse)resp).SessionId = sessionID;
				//    ((LoginResponse)resp).Success = true;
				//    break;
			}

			// If not handled based on the command type, do a generic response type processing.
			if (!handled)
			{
				switch ((Response)resp.ResponseId)
				{
					case Response.Login:
						// Simulate a successful login by returning the session ID.
						// No user ID is possible.
						((LoginResponse)resp).SessionId = sessionID;
						((LoginResponse)resp).Success = true;
						break;

					case Response.Null:
						// Do nothing.
						break;

					case Response.ViewData:
						// Load the view from the cache and synchronize it with local transactions.
						LoadViewCommand loadViewCmd = (LoadViewCommand)cmd;
						DataTable dt = Cache.ReadTable(loadViewCmd.ViewName, loadViewCmd.ContainerName);
						UpdateWithCachedTransactions(dt, loadViewCmd);
						((LoadViewResponse)resp).Table = dt;
						break;

					case Response.CreateView:
						// Creates the view from the schema.  This is a special case where an empty but possibly synchronized (notification enabled)
						// view is created with a possible qualifier.
						CreateViewCommand createViewCmd = (CreateViewCommand)cmd;
						// we can't set up the filter for any listener at this point.
						// TODO: When server reconnects, need to re-establish the correct filter and set the view's keepSynchronized flag.
						((CreateViewResponse)resp).Table = schemaService.CreateView(createViewCmd.ViewName).Table;
						break;
				}
			}
		}

		/// <summary>
		/// Update the DataTable with the local transactions for this view and container.
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="lvc"></param>
		protected void UpdateWithCachedTransactions(DataTable dt, LoadViewCommand lvc)
		{
			DataTable transactions = DataTableSynchronizationManager.GetEmptyTransactionTable();
			LoadTransactions(transactions, dt.TableName, lvc.ContainerName, false);
			DataTableTransactionLog dttl = new DataTableTransactionLog(dt);
			DataTableSynchronizationManager dtsm = new DataTableSynchronizationManager(dttl);
			dtsm.AddTransactions(transactions);
			dtsm.Sync();
			dttl.SourceTable = null;
			dt.AcceptChanges();
		}

		/// <summary>
		/// Loads the transactions DataTable with the local transactions for the specified view and container.
		/// </summary>
		/// <param name="transactions"></param>
		/// <param name="viewName"></param>
		/// <param name="containerName"></param>
		/// <param name="offlineOnly">True to load only offline transactions, rather than offline transactions and transactions successfully posted to the server.</param>
		public void LoadTransactions(DataTable transactions, string viewName, string containerName, bool offlineOnly)
		{
			using (DbCommand cmd = sqliteConn.CreateCommand())
			{
				if (offlineOnly)
				{
					// Get only offline transactions.  Used to sync the server with offline transactions.
					cmd.CommandText = "select * from Transactions where ViewName=@ViewName and ContainerName=@ContainerName and OfflineTransaction=1 order by ID";
				}
				else
				{
					// Get all transactions.  Used to sync the client when offline and loading a view.
					cmd.CommandText = "select * from Transactions where ViewName=@ViewName and ContainerName=@ContainerName order by ID";
				}

				cmd.Parameters.Add(new SQLiteParameter("@ViewName", viewName));
				cmd.Parameters.Add(new SQLiteParameter("@ContainerName", containerName));
				cmd.Parameters.Add(new SQLiteParameter("@OfflineTransaction", offlineOnly));
				SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)cmd);
				DataTable dt2 = new DataTable();
				da.Fill(dt2);

				// We now have a list of all the transactions for this view associated with the container.
				// Now get the transaction records.  Each transaction may consist of one or more transaction records--
				// changes to the view's data consisting of one or more row insert, update, or delete transactions.
				foreach (DataRow transRow in dt2.Rows)
				{
					// Get the comma delimited PK column list.
					string pkcols = transRow["PKColumnNames"].ToString();
					// Create the PK columns if they don't exist.
					CreatePkColumns(transactions, pkcols);
					int id = Convert.ToInt32(transRow["ID"]);

					// Get the transaction records...
					using (DbCommand cmd2 = sqliteConn.CreateCommand())
					{
						cmd2.CommandText = "select ID, TransType, ColumnName, ValueType, NewValue from TransactionRecords where TransactionID=@TransactionID order by ID";
						cmd2.Parameters.Add(new SQLiteParameter("@TransactionID", id));
						SQLiteDataAdapter da2 = new SQLiteDataAdapter((SQLiteCommand)cmd2);
						DataTable dtTransRec = new DataTable();
						da2.Fill(dtTransRec);

						// We now have a list of transaction records.  However, we still need the PK's for each transaction to fully qualify the row being updated.
						foreach (DataRow drTransRec in dtTransRec.Rows)
						{
							// Create a new row suitable for the data transaction logger.
							DataRow row = transactions.NewRow();
							row["TransType"] = drTransRec["TransType"];
							row["ColumnName"] = drTransRec["ColumnName"];
							row["ValueType"] = drTransRec["ValueType"];
							row["NewValue"] = drTransRec["NewValue"];
							int recid = Convert.ToInt32(drTransRec["ID"]);
							// Get the PK values.
							PopulatePKValues(row, recid);
							// We now have a completed transaction record row which can be added to the transaction
							// table and later synchronized using the data table synchronization manager.
							transactions.Rows.Add(row);
						}
					}
				}
			}
		}

		// TODO: If we set up Sqlite with FK's and cascading deletes, we would just need to delete the master Transaction records.
		/// <summary>
		/// Delete transactions associated with the view and container.
		/// </summary>
		/// <param name="p"></param>
		/// <param name="p_2"></param>
		public void DeleteTransactions(string viewName, string containerName)
		{
			using (DbCommand cmd = sqliteConn.CreateCommand())
			{
				cmd.CommandText = "select * from Transactions where ViewName=@ViewName and ContainerName=@ContainerName order by ID";
				cmd.Parameters.Add(new SQLiteParameter("@ViewName", viewName));
				cmd.Parameters.Add(new SQLiteParameter("@ContainerName", containerName));
				SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)cmd);
				DataTable dt2 = new DataTable(); // DataTableSynchronizationManager.GetEmptyTransactionTable();
				da.Fill(dt2);

				// For each transaction...
				foreach (DataRow transRow in dt2.Rows)
				{
					int id = Convert.ToInt32(transRow["ID"]);

					// Get the transaction records.
					using (DbCommand cmd2 = sqliteConn.CreateCommand())
					{
						cmd2.CommandText = "select ID, TransType, ColumnName, ValueType, NewValue from TransactionRecords where TransactionID=@TransactionID order by ID";
						cmd2.Parameters.Add(new SQLiteParameter("@TransactionID", id));
						SQLiteDataAdapter da2 = new SQLiteDataAdapter((SQLiteCommand)cmd2);
						DataTable dtTransRec = new DataTable();
						da2.Fill(dtTransRec);

						// For each transaction record...
						foreach (DataRow drTransRec in dtTransRec.Rows)
						{
							// Delete the PK information.
							using (DbCommand cmd3 = sqliteConn.CreateCommand())
							{
								cmd3.CommandText = "delete from TransactionRecordPrimaryKeys where TransactionRecordID=@ID";
								cmd3.Parameters.Add(new SQLiteParameter("@ID", drTransRec["ID"]));
								cmd3.ExecuteNonQuery();
							}
						}

						// Delete the transaction record.
						cmd2.CommandText = "delete from TransactionRecords where TransactionID=@TransactionID";
						cmd2.ExecuteNonQuery();
					}
				}

				// Delete the transaction.
				cmd.CommandText = "delete from Transactions where ViewName=@ViewName and ContainerName=@ContainerName";
				cmd.ExecuteNonQuery();
			}
		}

		protected void CreateTransactionTables()
		{
			// A transaction consists of:
			// ID (numeric, autoinc)
			// ContainerName
			// ViewName
			// PkColumnNames (comma separated)
			// One or more transaction records

			// A transaction record consists of:
			// ID (numeric, autoinc)
			// TransactionID (numeric) (referencing the parent transaction)
			// TransType (int)
			// ColumnName (string)
			// ValueType (string, nullable)
			// NewValue (string, nullable)
			// One or more PK values qualifying the row being altered (including inserts).

			// Create the database tables.
			using (DbCommand cmd = sqliteConn.CreateCommand())
			{
				cmd.CommandText = "create table Transactions (ID integer primary key autoincrement, ContainerName varchar(64), ViewName varchar(64), PkColumnNames text, OfflineTransaction integer)";
				cmd.ExecuteNonQuery();
				cmd.CommandText = "create table TransactionRecords (ID integer primary key autoincrement, TransactionID integer, TransType integer, ColumnName varchar(64), ValueType varchar(64), NewValue text)";
				cmd.ExecuteNonQuery();
				cmd.CommandText = "create table TransactionRecordPrimaryKeys (ID integer primary key autoincrement, TransactionRecordID integer, PkColumnName varchar(64), PkValue varchar(64))";
				cmd.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Saves the transactions locally.  This includes all offline transactions and online 
		/// transactions that might need to be used to synchronize the cached view if the client
		/// later starts in a disconnected mode.
		/// </summary>
		/// <param name="ptc"></param>
		/// <param name="isOffline"></param>
		public void SaveTransactions(PostTransactionsCommand ptc, bool isOffline)
		{
			// Build the comma delimited PK list.
			StringBuilder csPkList = BuildCsPkList(ptc);

			// Write out the transactions...
			using (DbCommand cmd = sqliteConn.CreateCommand())
			{
				// Write out the view and container for which these transactions are associated.
				int id = WriteTransactionInfo(cmd, ptc, csPkList, isOffline ? 1 : 0);

				// For each transaction in the log associated with the view and container...
				foreach (DataRow row in ptc.Transactions.Rows)
				{
					// Write the transaction record.
					using (DbCommand cmd2=sqliteConn.CreateCommand())
					{
						int recId = WriteTransactionRecord(cmd2, row, id);

						// Each transaction record has one or more PK values that uniquely identify the row being operated on in the view's table.
						foreach (string pkcol in ptc.PKColumnNames)
						{
							using (DbCommand cmd3 = sqliteConn.CreateCommand())
							{
								WriteTransactionRecordPrimaryKeys(cmd3, recId, pkcol, row);
							}
						}
					}
				}
			}
		}

		/// <summary>
		/// Write the transaction information, consisting of the container and view names, the PK column names,
		/// and the offline flag.
		/// </summary>
		protected int WriteTransactionInfo(DbCommand cmd, PostTransactionsCommand ptc, StringBuilder csPkList, int iFlag)
		{
			cmd.CommandText = "insert into Transactions (ContainerName, ViewName, PKColumnNames, OfflineTransaction) values (@ContainerName, @ViewName, @PKColumnNames, @OfflineTransaction);select last_insert_rowid() AS [ID]";
			cmd.Parameters.Add(new SQLiteParameter("@ContainerName", ptc.ContainerName));
			cmd.Parameters.Add(new SQLiteParameter("@ViewName", ptc.ViewName));
			cmd.Parameters.Add(new SQLiteParameter("@PKColumnNames", csPkList.ToString()));
			cmd.Parameters.Add(new SQLiteParameter("@OfflineTransaction", iFlag));
			int id = Convert.ToInt32(cmd.ExecuteScalar());

			return id;
		}

		/// <summary>
		/// Write the transaction record, which consists of the transaction type, column name, value type, and new value.
		/// </summary>
		protected int WriteTransactionRecord(DbCommand cmd2, DataRow record, int transactionId)
		{
			cmd2.CommandText = "insert into TransactionRecords (TransactionID, TransType, ColumnName, ValueType, NewValue) values (@TransactionID, @TransType, @ColumnName, @ValueType, @NewValue);select last_insert_rowid() AS [ID]";
			cmd2.Parameters.Add(new SQLiteParameter("@TransactionID", transactionId));
			cmd2.Parameters.Add(new SQLiteParameter("@TransType", record["TransType"]));
			cmd2.Parameters.Add(new SQLiteParameter("@ColumnName", record["ColumnName"]));
			cmd2.Parameters.Add(new SQLiteParameter("@ValueType", record["ValueType"]));
			cmd2.Parameters.Add(new SQLiteParameter("@NewValue", record["NewValue"]));
			int recId = Convert.ToInt32(cmd2.ExecuteScalar());

			return recId;
		}

		/// <summary>
		/// Write the transaction PK list, which consists of the column name and PK value.
		/// </summary>
		protected void WriteTransactionRecordPrimaryKeys(DbCommand cmd3, int trecId, string pkName, DataRow record)
		{
			cmd3.CommandText = "insert into TransactionRecordPrimaryKeys (TransactionRecordID, PkColumnName, PkValue) values (@TransactionRecordID, @PkColumnName, @PkValue)";
			cmd3.Parameters.Add(new SQLiteParameter("@TransactionRecordID", trecId));
			cmd3.Parameters.Add(new SQLiteParameter("@PkColumnName", pkName));
			cmd3.Parameters.Add(new SQLiteParameter("@PkValue", record[pkName].ToString()));
			cmd3.ExecuteNonQuery();
		}

		/// <summary>
		/// Builds a comma separated list of PK's and they're types in the format [pkname]([datatype]),...
		/// </summary>
		/// <param name="ptc"></param>
		/// <returns></returns>
		protected StringBuilder BuildCsPkList(PostTransactionsCommand ptc)
		{
			StringBuilder sb = new StringBuilder();
			string and = String.Empty;

			foreach (string str in ptc.PKColumnNames)
			{
				sb.Append(and);
				sb.Append(str);
				sb.Append("(" + ptc.Transactions.Columns[str].DataType.ToString() + ")");
				and = ",";
			}

			return sb;
		}

		/// <summary>
		/// Creates the PK columns in the transaction DataTable if they don't already exist.  This receives
		/// the pk column list in the format [colName]([datatype])...
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="pkcols"></param>
		protected void CreatePkColumns(DataTable dt, string pkcols)
		{
			string[] cols = pkcols.Split(',');

			foreach (string col in cols)
			{
				string colName = StringHelpers.LeftOf(col, '(');

				// Column name will already exist if synchronizing against a live view.
				if (!dt.Columns.Contains(colName))
				{
					string colType = StringHelpers.Between(col, '(', ')');
					DataColumn dc = new DataColumn(colName, Type.GetType(colType));
					dt.Columns.Add(dc);
				}
			}
		}

		/// <summary>
		/// For a given transaction record, get the associated transaction record primary keys and
		/// initialize the associated row fields with the PK values.
		/// </summary>
		/// <param name="row"></param>
		/// <param name="recid"></param>
		protected void PopulatePKValues(DataRow row, int recid)
		{
			using (DbCommand cmd = sqliteConn.CreateCommand())
			{
				cmd.CommandText = "select * from TransactionRecordPrimaryKeys where TransactionRecordID=@ID";
				cmd.Parameters.Add(new SQLiteParameter("@ID", recid));
				SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)cmd);
				DataTable dt = new DataTable();
				da.Fill(dt);

				foreach (DataRow pkrow in dt.Rows)
				{
					string pkColName=pkrow["PkColumnName"].ToString();
					object pkVal=pkrow["PkValue"];
					row[pkColName] = Converter.Convert(pkVal, row.Table.Columns[pkColName].DataType);
				}
			}
		}
	}
}