From df2f9b27b12356d6e92c64a72049f0fc4e6b86c0 Mon Sep 17 00:00:00 2001 From: Roy Ben-Shabat Date: Mon, 27 Nov 2017 21:51:38 +0200 Subject: Started Remote <=> Local Synchronization... /; --- .../Tango.Synchronization/Constants.cs | 35 -- .../Visual_Studio/Tango.Synchronization/Diff.cs | 65 +++ .../Tango.Synchronization/DiffAction.cs | 40 ++ .../Tango.Synchronization/ExtensionMethods.cs | 88 ----- .../Tango.Synchronization/ISqlDataBase.cs | 93 ----- .../Tango.Synchronization/Local/Constants.cs | 35 ++ .../Local/ExtensionMethods.cs | 88 +++++ .../Tango.Synchronization/Local/ILocalDataBase.cs | 93 +++++ .../Tango.Synchronization/Local/LocalDBComparer.cs | 200 ++++++++++ .../Tango.Synchronization/Local/SqliteDataBase.cs | 434 +++++++++++++++++++++ .../Remote/RemoteDBComparer.cs | 45 +++ .../Tango.Synchronization/SqlDataBaseComparer.cs | 200 ---------- .../Visual_Studio/Tango.Synchronization/SqlDiff.cs | 65 --- .../Tango.Synchronization/SqlDiffAction.cs | 40 -- .../Tango.Synchronization/SqliteDataBase.cs | 434 --------------------- .../Tango.Synchronization.csproj | 24 +- 16 files changed, 1017 insertions(+), 962 deletions(-) delete mode 100644 Software/Visual_Studio/Tango.Synchronization/Constants.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Diff.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/DiffAction.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/ExtensionMethods.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/ISqlDataBase.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Local/Constants.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Local/ExtensionMethods.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Local/ILocalDataBase.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Local/LocalDBComparer.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Local/SqliteDataBase.cs create mode 100644 Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/SqlDataBaseComparer.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/SqlDiff.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/SqlDiffAction.cs delete mode 100644 Software/Visual_Studio/Tango.Synchronization/SqliteDataBase.cs (limited to 'Software/Visual_Studio/Tango.Synchronization') diff --git a/Software/Visual_Studio/Tango.Synchronization/Constants.cs b/Software/Visual_Studio/Tango.Synchronization/Constants.cs deleted file mode 100644 index 3558fd506..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/Constants.cs +++ /dev/null @@ -1,35 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - internal static class Constants - { - //SQLite Internal Constants - public const String SEQUENCE_TABLE_NAME = "sqlite_sequence"; - public const String TABLE_INFO = "table_info"; - public const String COLUMN_TYPE = "type"; - public const String IS_NOT_NULL = "notnull"; - public const String DEFAULT_VALUE = "dflt_value"; - - //Must Column Names. - - /// - /// The table auto incremented id column name. - /// - public const String ID = "ID"; - - /// - /// The table unique identifier column name used for synchronization. - /// - public const String GUID = "GUID"; - - /// - /// The table last updated column name used for synchronization. - /// - public const String LAST_UPDATED = "LAST_UPDATED"; - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/Diff.cs b/Software/Visual_Studio/Tango.Synchronization/Diff.cs new file mode 100644 index 000000000..45bd1581c --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Diff.cs @@ -0,0 +1,65 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization +{ + /// + /// Represents an result. + /// + public class Diff + { + private Action _action; + + /// + /// Gets the difference action. + /// + public DiffAction Action { get; private set; } + + /// + /// Gets the difference description. + /// + public String Description { get; private set; } + + /// + /// Gets the command. + /// + public String Command { get; private set; } + + /// + /// Initializes a new instance of the class. + /// + /// The data base action. + /// The description. + /// The action. + /// The command. + public Diff(DiffAction dataBaseAction, String description, Action action, String command) + { + Action = dataBaseAction; + Description = description; + _action = action; + Command = command; + } + + /// + /// Commits the difference to the target DB. + /// + public void Commit() + { + _action(); + } + + /// + /// Returns a that represents this instance. + /// + /// + /// A that represents this instance. + /// + public override string ToString() + { + return Command; + } + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/DiffAction.cs b/Software/Visual_Studio/Tango.Synchronization/DiffAction.cs new file mode 100644 index 000000000..2395d819c --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/DiffAction.cs @@ -0,0 +1,40 @@ +using System; +using System.Collections.Generic; +using System.ComponentModel; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization +{ + /// + /// Represents the type of . + /// + public enum DiffAction + { + /// + /// Add table to slave + /// + AddTableToSlave, + /// + /// Add column to slave + /// + AddColumnToSlave, + /// + /// Add row to slave + /// + AddRowToSlave, + /// + /// Add row to master + /// + AddRowToMaster, + /// + /// Update row in slave + /// + UpdateRowInSlave, + /// + /// Update row in master + /// + UpdateRowInMaster, + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/ExtensionMethods.cs b/Software/Visual_Studio/Tango.Synchronization/ExtensionMethods.cs deleted file mode 100644 index 7f16f7006..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/ExtensionMethods.cs +++ /dev/null @@ -1,88 +0,0 @@ -using Tango.Synchronization; -using System; -using System.Collections.Generic; -using System.Data; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - internal static class SQLExtensions - { - /// - /// Gets the type of the SQL column. - /// - /// The column. - /// - public static String GetSQLType(this DataColumn column) - { - return column.ExtendedProperties[Constants.COLUMN_TYPE].ToString(); - } - - /// - /// Gets the information table. - /// - /// The table. - /// - public static DataTable GetInfoTable(this DataTable table) - { - return table.ExtendedProperties[Constants.TABLE_INFO] as DataTable; - } - - /// - /// Determines whether column is marked NOT NULL. - /// - /// The column. - public static bool IsNotNull(this DataColumn column) - { - int value = int.Parse(column.ExtendedProperties[Constants.IS_NOT_NULL].ToString()); - return value == 0 ? false : true; - } - - /// - /// Gets the column default value. - /// - /// The column. - /// - public static String GetDefaultValue(this DataColumn column) - { - String def = column.ExtendedProperties[Constants.DEFAULT_VALUE].ToString(); - - double num = -1; - - if (double.TryParse(def, out num)) - { - return num.ToString(); - } - else - { - if (def.Contains("randomblob") || def.Contains("localtime")) - { - def = def.Insert(0, "("); - def = def.Insert(def.Length - 1, ")"); - } - return def; - } - } - - /// - /// Determines whether column definition has default value. - /// - /// The column. - public static bool HasDefaultValue(this DataColumn column) - { - return !String.IsNullOrWhiteSpace(column.ExtendedProperties[Constants.DEFAULT_VALUE].ToString()); - } - - /// - /// Converts the DateTime instance to SQLite DateTime string. - /// - /// The date. - /// - public static String ToSQLiteDateString(this DateTime date) - { - return date.ToString("yyyy-MM-dd HH:mm:ss.fff"); - } - } -} \ No newline at end of file diff --git a/Software/Visual_Studio/Tango.Synchronization/ISqlDataBase.cs b/Software/Visual_Studio/Tango.Synchronization/ISqlDataBase.cs deleted file mode 100644 index fa4e018be..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/ISqlDataBase.cs +++ /dev/null @@ -1,93 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Data; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - /// - /// Represents an SQL data base adapter used by . - /// - /// - public interface ISqlDataBase : IDisposable - { - /// - /// Gets the database source URL/File. - /// - String Source { get; } - - /// - /// Gets the database tables collection. - /// - List Tables { get; } - - /// - /// Loads the tables (Must be done before any synchronization). - /// - void LoadTables(); - - /// - /// Clones a table from another database into this database. - /// - /// The other database. - /// The other table. - /// - DataTable CloneTableFrom(ISqlDataBase otherDB, DataTable otherTable); - - /// - /// Gets the SQL command for . - /// - /// The other database. - /// The other table. - /// - String GetCloneTableFromCommand(ISqlDataBase otherDB, DataTable otherTable); - - /// - /// Adds the specified column to the specified table. - /// - /// The table. - /// The column. - /// - DataColumn AddColumn(DataTable table, DataColumn column); - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The column. - /// - String GetAddColumnCommand(DataTable table, DataColumn column); - - /// - /// Adds the specified row to the specified table. - /// - /// The table. - /// The row. - void AddRow(DataTable table, DataRow row); - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The row. - /// - String GetAddRowCommand(DataTable table, DataRow row); - - /// - /// Updates the matching row by the row GUID. - /// - /// The table. - /// The row. - void UpdateRow(DataTable table, DataRow row); - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The row. - /// - String GetUpdateRowCommand(DataTable table, DataRow row); - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/Local/Constants.cs b/Software/Visual_Studio/Tango.Synchronization/Local/Constants.cs new file mode 100644 index 000000000..ed8084301 --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Local/Constants.cs @@ -0,0 +1,35 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization.Local +{ + internal static class Constants + { + //SQLite Internal Constants + public const String SEQUENCE_TABLE_NAME = "sqlite_sequence"; + public const String TABLE_INFO = "table_info"; + public const String COLUMN_TYPE = "type"; + public const String IS_NOT_NULL = "notnull"; + public const String DEFAULT_VALUE = "dflt_value"; + + //Must Column Names. + + /// + /// The table auto incremented id column name. + /// + public const String ID = "ID"; + + /// + /// The table unique identifier column name used for synchronization. + /// + public const String GUID = "GUID"; + + /// + /// The table last updated column name used for synchronization. + /// + public const String LAST_UPDATED = "LAST_UPDATED"; + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/Local/ExtensionMethods.cs b/Software/Visual_Studio/Tango.Synchronization/Local/ExtensionMethods.cs new file mode 100644 index 000000000..8746f9146 --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Local/ExtensionMethods.cs @@ -0,0 +1,88 @@ +using Tango.Synchronization; +using System; +using System.Collections.Generic; +using System.Data; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization.Local +{ + internal static class SQLExtensions + { + /// + /// Gets the type of the SQL column. + /// + /// The column. + /// + public static String GetSQLType(this DataColumn column) + { + return column.ExtendedProperties[Constants.COLUMN_TYPE].ToString(); + } + + /// + /// Gets the information table. + /// + /// The table. + /// + public static DataTable GetInfoTable(this DataTable table) + { + return table.ExtendedProperties[Constants.TABLE_INFO] as DataTable; + } + + /// + /// Determines whether column is marked NOT NULL. + /// + /// The column. + public static bool IsNotNull(this DataColumn column) + { + int value = int.Parse(column.ExtendedProperties[Constants.IS_NOT_NULL].ToString()); + return value == 0 ? false : true; + } + + /// + /// Gets the column default value. + /// + /// The column. + /// + public static String GetDefaultValue(this DataColumn column) + { + String def = column.ExtendedProperties[Constants.DEFAULT_VALUE].ToString(); + + double num = -1; + + if (double.TryParse(def, out num)) + { + return num.ToString(); + } + else + { + if (def.Contains("randomblob") || def.Contains("localtime")) + { + def = def.Insert(0, "("); + def = def.Insert(def.Length - 1, ")"); + } + return def; + } + } + + /// + /// Determines whether column definition has default value. + /// + /// The column. + public static bool HasDefaultValue(this DataColumn column) + { + return !String.IsNullOrWhiteSpace(column.ExtendedProperties[Constants.DEFAULT_VALUE].ToString()); + } + + /// + /// Converts the DateTime instance to SQLite DateTime string. + /// + /// The date. + /// + public static String ToSQLiteDateString(this DateTime date) + { + return date.ToString("yyyy-MM-dd HH:mm:ss.fff"); + } + } +} \ No newline at end of file diff --git a/Software/Visual_Studio/Tango.Synchronization/Local/ILocalDataBase.cs b/Software/Visual_Studio/Tango.Synchronization/Local/ILocalDataBase.cs new file mode 100644 index 000000000..7e53aeb1c --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Local/ILocalDataBase.cs @@ -0,0 +1,93 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization.Local +{ + /// + /// Represents an SQL data base adapter used by . + /// + /// + public interface ILocalDataBase : IDisposable + { + /// + /// Gets the database source URL/File. + /// + String Source { get; } + + /// + /// Gets the database tables collection. + /// + List Tables { get; } + + /// + /// Loads the tables (Must be done before any synchronization). + /// + void LoadTables(); + + /// + /// Clones a table from another database into this database. + /// + /// The other database. + /// The other table. + /// + DataTable CloneTableFrom(ILocalDataBase otherDB, DataTable otherTable); + + /// + /// Gets the SQL command for . + /// + /// The other database. + /// The other table. + /// + String GetCloneTableFromCommand(ILocalDataBase otherDB, DataTable otherTable); + + /// + /// Adds the specified column to the specified table. + /// + /// The table. + /// The column. + /// + DataColumn AddColumn(DataTable table, DataColumn column); + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The column. + /// + String GetAddColumnCommand(DataTable table, DataColumn column); + + /// + /// Adds the specified row to the specified table. + /// + /// The table. + /// The row. + void AddRow(DataTable table, DataRow row); + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The row. + /// + String GetAddRowCommand(DataTable table, DataRow row); + + /// + /// Updates the matching row by the row GUID. + /// + /// The table. + /// The row. + void UpdateRow(DataTable table, DataRow row); + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The row. + /// + String GetUpdateRowCommand(DataTable table, DataRow row); + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/Local/LocalDBComparer.cs b/Software/Visual_Studio/Tango.Synchronization/Local/LocalDBComparer.cs new file mode 100644 index 000000000..63f2ecfaa --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Local/LocalDBComparer.cs @@ -0,0 +1,200 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.IO; +using System.Linq; +using System.Text; +using System.Threading.Tasks; +using Tango.Logging; + +namespace Tango.Synchronization.Local +{ + /// + /// Represents an synchronization engine. + /// + /// + public class LocalDBComparer : IDisposable + { + /// + /// Gets the master SQL. + /// + public ILocalDataBase MasterSQL { get; private set; } + + /// + /// Gets the slave SQL. + /// + public ILocalDataBase SlaveSQL { get; private set; } + + /// + /// Initializes a new instance of the class. + /// + /// The master SQL. + /// The slave SQL. + public LocalDBComparer(ILocalDataBase masterSQL, ILocalDataBase slaveSQL) + { + MasterSQL = masterSQL; + SlaveSQL = slaveSQL; + } + + /// + /// Compares the master and slave SQL data base and returns a collection of . + /// + /// + public List Compare() + { + LogManager.Log("Comparing databases " + Path.GetFileName(MasterSQL.Source) + " <=> " + Path.GetFileName(SlaveSQL.Source)); + + LogManager.Log("Loading master tables..."); + MasterSQL.LoadTables(); + + LogManager.Log("Loading slave tables..."); + SlaveSQL.LoadTables(); + + List diffs = new List(); + + foreach (var masterTable in MasterSQL.Tables) + { + LogManager.Log("Comparing table " + masterTable.TableName); + LogManager.Log("Searching table " + masterTable.TableName + " on slave..."); + + var slaveTable = SlaveSQL.Tables.SingleOrDefault(x => x.TableName == masterTable.TableName); //Get matching slave table on slave db. + + if (slaveTable == null) //Table not found on slave. + { + LogManager.Log("Table not found on slave, adding difference."); + //Clone table from slave db to master db including records! + diffs.Add(new Diff(DiffAction.AddTableToSlave, String.Format("Add table {0} to slave", masterTable.TableName), () => SlaveSQL.CloneTableFrom(MasterSQL, masterTable), SlaveSQL.GetCloneTableFromCommand(MasterSQL, masterTable))); + continue; + } + + LogManager.Log("Table found, comparing columns..."); + + foreach (DataColumn masterColumn in masterTable.Columns) + { + LogManager.Log("Searching for column " + masterColumn.ColumnName + " on slave..."); + + var slaveColumn = slaveTable.Columns[masterColumn.ColumnName]; //Get matching slave column on slave table. + + if (slaveColumn == null) //Slave column not found. + { + LogManager.Log("Column not found on slave, adding difference."); + //Add column to slave table. + diffs.Add(new Diff(DiffAction.AddColumnToSlave, String.Format("Add column {0} to slave table", masterColumn.ColumnName), () => SlaveSQL.AddColumn(masterTable, masterColumn), SlaveSQL.GetAddColumnCommand(masterTable, masterColumn))); + } + + LogManager.Log("Column found."); + } + + List addToSlave = new List(); + List updateSlave = new List(); + List addToMaster = new List(); + List updateMaster = new List(); + + LogManager.Log("Comparing rows..."); + + int count = 0; + + foreach (DataRow masterRow in masterTable.Rows) + { + LogManager.Log("Comparing row " + count++); + + String guid = masterRow.Field(Constants.GUID); + + LogManager.Log("Searching for row with GUID " + guid + " on slave table..."); + + //Get Matching slave row. + DataRow slaveRow = slaveTable.AsEnumerable().SingleOrDefault(x => x.Field(Constants.GUID) == guid); + + if (slaveRow != null) + { + LogManager.Log("Slave row found, comparing dates..."); + + DateTime masterDate = masterRow.Field(Constants.LAST_UPDATED); + DateTime slaveDate = slaveRow.Field(Constants.LAST_UPDATED); + + if (masterDate > slaveDate) + { + LogManager.Log("Master => Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."); + updateSlave.Add(masterRow); + } + else if (slaveDate > masterDate) + { + LogManager.Log("Master <= Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."); + updateMaster.Add(slaveRow); + } + else + { + LogManager.Log("Master <=> Slave No Update."); + } + } + else + { + LogManager.Log("Slave row not found, adding difference."); + addToSlave.Add(masterRow); + } + } + + LogManager.Log("Done comparing rows..."); + + LogManager.Log("Searching for missing rows on master..."); + + foreach (DataRow slaveRow in slaveTable.Rows) + { + String guid = slaveRow.Field(Constants.GUID); + + LogManager.Log("Searching for row with GUID " + guid + " on master table..."); + + //Get Matching slave row. + DataRow masterRow = masterTable.AsEnumerable().SingleOrDefault(x => x.Field(Constants.GUID) == guid); + + if (masterRow == null) + { + LogManager.Log("Master row not found, adding difference."); + addToMaster.Add(slaveRow); + } + else + { + LogManager.Log("Master row found."); + } + } + + LogManager.Log("Done searching for missing rows on master..."); + + foreach (var row in addToSlave) + { + diffs.Add(new Diff(DiffAction.AddRowToSlave, String.Format("Add row to slave table {0}", slaveTable.TableName), () => SlaveSQL.AddRow(slaveTable, row), SlaveSQL.GetAddRowCommand(slaveTable, row))); + } + + foreach (var row in addToMaster) + { + diffs.Add(new Diff(DiffAction.AddRowToMaster, String.Format("Add row to master table {0}", masterTable.TableName), () => MasterSQL.AddRow(masterTable, row), MasterSQL.GetAddRowCommand(masterTable, row))); + } + + foreach (var row in updateSlave) + { + diffs.Add(new Diff(DiffAction.UpdateRowInSlave, String.Format("Update row in slave table {0}", slaveTable.TableName), () => SlaveSQL.UpdateRow(slaveTable, row), SlaveSQL.GetUpdateRowCommand(slaveTable, row))); + } + + foreach (var row in updateMaster) + { + diffs.Add(new Diff(DiffAction.UpdateRowInMaster, String.Format("Update row in master table {0}", masterTable.TableName), () => MasterSQL.UpdateRow(masterTable, row), MasterSQL.GetUpdateRowCommand(masterTable, row))); + } + + LogManager.Log("Done comparing table " + masterTable.TableName); + } + + LogManager.Log("Databases comparison completed."); + + return diffs; + } + + /// + /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. + /// + public void Dispose() + { + MasterSQL.Dispose(); + SlaveSQL.Dispose(); + } + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/Local/SqliteDataBase.cs b/Software/Visual_Studio/Tango.Synchronization/Local/SqliteDataBase.cs new file mode 100644 index 000000000..35e54c66a --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Local/SqliteDataBase.cs @@ -0,0 +1,434 @@ +using Tango.Synchronization; +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SQLite; +using System.Diagnostics; +using System.IO; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.Synchronization.Local +{ + /// + /// Represents an SQLite database adapter used for synchronization by . + /// + /// + public class SQLiteDataBase : ILocalDataBase + { + private SQLiteConnection _connection; + + /// + /// Gets the database source URL/File. + /// + public String Source { get; private set; } + + /// + /// Gets the database tables collection. + /// + public List Tables { get; private set; } + + /// + /// Initializes a new instance of the class. + /// + /// The file path. + public SQLiteDataBase(String filePath) + { + Source = filePath; + + _connection = new SQLiteConnection(String.Format("Data Source={0};Version=3;New={1};Compress=FALSE;", + filePath, + (File.Exists(filePath) ? "False" : "True") + )); + + _connection.Open(); + } + + /// + /// Loads the tables (Must be done before any synchronization). + /// + public void LoadTables() + { + Tables = new List(); + + var command = _connection.CreateCommand(); + command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'"; + var reader = command.ExecuteReader(); + + while (reader.Read()) + { + String name = reader.GetString(0); + + if (name != Constants.SEQUENCE_TABLE_NAME) + { + Debug.WriteLine(name); + Tables.Add(LoadTable(name)); + } + } + } + + /// + /// Loads the table. + /// + /// The name. + /// + private DataTable LoadTable(String name) + { + var tableCommand = _connection.CreateCommand(); + tableCommand.CommandText = "SELECT * FROM " + name; + + DataTable table = new DataTable(name); + table.Load(tableCommand.ExecuteReader()); + + var infoCommand = _connection.CreateCommand(); + infoCommand.CommandText = String.Format("PRAGMA table_info({0});", name); + DataTable infoTable = new DataTable(name); + infoTable.Load(infoCommand.ExecuteReader()); + + table.ExtendedProperties.Add(Constants.TABLE_INFO, infoTable); + + for (int i = 0; i < table.Columns.Count; i++) + { + DataColumn column = table.Columns[i]; + column.ExtendedProperties.Add(Constants.COLUMN_TYPE, infoTable.Rows[i][Constants.COLUMN_TYPE]); + column.ExtendedProperties.Add(Constants.IS_NOT_NULL, infoTable.Rows[i][Constants.IS_NOT_NULL]); + column.ExtendedProperties.Add(Constants.DEFAULT_VALUE, infoTable.Rows[i][Constants.DEFAULT_VALUE]); + } + + return table; + } + + /// + /// Clones a table from another database into this database. + /// + /// The other database. + /// The other table. + /// + public DataTable CloneTableFrom(ILocalDataBase otherDB, DataTable otherTable) + { + String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName); + + foreach (DataColumn column in otherTable.Columns) + { + cmd += String.Format("{0} {1} {2} {3} {4} {5}", + column.ColumnName, + column.GetSQLType(), + otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, + column.Unique ? "UNIQUE" : null, + column.IsNotNull() ? "NOT NULL" : null, + column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null) + + + ", " + Environment.NewLine; + } + + cmd += ");"; + + cmd = cmd.Remove(cmd.LastIndexOf(","), 1); + + var createCommand = _connection.CreateCommand(); + createCommand.CommandText = cmd; + createCommand.ExecuteNonQuery(); + + var attacheCommand = _connection.CreateCommand(); + attacheCommand.CommandText = String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source); + attacheCommand.ExecuteNonQuery(); + + var copyCommand = _connection.CreateCommand(); + copyCommand.CommandText = String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other"); + copyCommand.ExecuteNonQuery(); + + var detacheCommand = _connection.CreateCommand(); + detacheCommand.CommandText = "DETACH other;"; + detacheCommand.ExecuteNonQuery(); + + var table = LoadTable(otherTable.TableName); + Tables.Add(table); + return table; + } + + /// + /// Gets the SQL command for . + /// + /// The other database. + /// The other table. + /// + public String GetCloneTableFromCommand(ILocalDataBase otherDB, DataTable otherTable) + { + String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName); + + foreach (DataColumn column in otherTable.Columns) + { + cmd += String.Format("{0} {1} {2} {3} {4} {5}", + column.ColumnName, + column.GetSQLType(), + otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, + column.Unique ? "UNIQUE" : null, + column.IsNotNull() ? "NOT NULL" : null, + column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null) + + + ", " + Environment.NewLine; + } + + cmd += ");"; + + cmd = cmd.Remove(cmd.LastIndexOf(","), 1); + + cmd += Environment.NewLine; + + cmd += String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source); + + cmd += Environment.NewLine; + + cmd += String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other"); + + cmd += Environment.NewLine; + + cmd += "DETACH other;"; + + return cmd; + } + + /// + /// Adds the specified column to the specified table. + /// + /// The table. + /// The column. + /// + public DataColumn AddColumn(DataTable table, DataColumn column) + { + var insertCommand = _connection.CreateCommand(); + insertCommand.CommandText = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5} {6}", + table.TableName, + column.ColumnName, + column.GetSQLType(), + table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, + column.Unique ? "UNIQUE" : null, + column.IsNotNull() ? "NOT NULL" : null, + column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null); + + + insertCommand.ExecuteNonQuery(); + var t = LoadTable(table.TableName); + var index = Tables.IndexOf(Tables.Single(x => x.TableName == table.TableName)); + Tables[index] = t; + return t.Columns[column.ColumnName]; + } + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The column. + /// + public String GetAddColumnCommand(DataTable table, DataColumn column) + { + String cmd = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5} {6}", + table.TableName, + column.ColumnName, + column.GetSQLType(), + table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, + column.Unique ? "UNIQUE" : null, + column.IsNotNull() ? "NOT NULL" : null, + column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null); + + return cmd; + } + + /// + /// Adds the specified row to the specified table. + /// + /// The table. + /// The row. + public void AddRow(DataTable table, DataRow row) + { + var insertCommand = _connection.CreateCommand(); + + List values = new List(); + + for (int i = 0; i < row.ItemArray.Length; i++) + { + String value = row.ItemArray[i].ToString(); + + double num = 0; + + if (row.ItemArray[i].GetType() == typeof(DateTime)) + { + value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); + } + + if (!double.TryParse(value, out num)) + { + value = "'" + value + "'"; + } + + values.Add(value); + } + + insertCommand.CommandText = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join(",", values)); + insertCommand.ExecuteNonQuery(); + + var desRow = table.NewRow(); + desRow.ItemArray = row.ItemArray.Clone() as object[]; + } + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The row. + /// + public String GetAddRowCommand(DataTable table, DataRow row) + { + List values = new List(); + + for (int i = 0; i < row.ItemArray.Length; i++) + { + String value = row.ItemArray[i].ToString(); + + double num = 0; + + if (row.ItemArray[i].GetType() == typeof(DateTime)) + { + value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); + } + + if (!double.TryParse(value, out num)) + { + value = "'" + value + "'"; + } + + values.Add(value); + } + + String cmd = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join("," + Environment.NewLine, values)); + return cmd; + } + + /// + /// Updates the matching row by the row GUID. + /// + /// The table. + /// The row. + public void UpdateRow(DataTable table, DataRow row) + { + var updateCommand = _connection.CreateCommand(); + + List values = new List(); + + for (int i = 0; i < row.ItemArray.Length; i++) + { + String value = row.ItemArray[i].ToString(); + + double num = 0; + + if (row.ItemArray[i].GetType() == typeof(DateTime)) + { + value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); + } + + if (!double.TryParse(value, out num)) + { + value = "'" + value + "'"; + } + + values.Add(value); + } + + String cmd = String.Format("UPDATE {0} SET ", table.TableName); + + String guid = String.Empty; + + for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) + { + DataColumn column = table.Columns[columnIndex]; + if (column.ColumnName == Constants.ID) + { + continue; + } + else if (column.ColumnName == Constants.GUID) + { + guid = values[columnIndex]; + continue; + } + + cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine); + } + + cmd = cmd.Remove(cmd.LastIndexOf(","), 1); + + cmd += "WHERE" + Environment.NewLine; + cmd += String.Format("{0} = {1};", Constants.GUID, guid); + + updateCommand.CommandText = cmd; + updateCommand.ExecuteNonQuery(); + var desRow = table.NewRow(); + desRow.ItemArray = row.ItemArray.Clone() as object[]; + } + + /// + /// Gets the SQL command for . + /// + /// The table. + /// The row. + /// + public String GetUpdateRowCommand(DataTable table, DataRow row) + { + List values = new List(); + + for (int i = 0; i < row.ItemArray.Length; i++) + { + String value = row.ItemArray[i].ToString(); + + double num = 0; + + if (row.ItemArray[i].GetType() == typeof(DateTime)) + { + value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); + } + + if (!double.TryParse(value, out num)) + { + value = "'" + value + "'"; + } + + values.Add(value); + } + + String cmd = String.Format("UPDATE {0} SET ", table.TableName); + + String guid = String.Empty; + + for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) + { + DataColumn column = table.Columns[columnIndex]; + if (column.ColumnName == Constants.ID) + { + continue; + } + else if (column.ColumnName == Constants.GUID) + { + guid = values[columnIndex]; + continue; + } + + cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine); + } + + cmd = cmd.Remove(cmd.LastIndexOf(","), 1); + + cmd += "WHERE" + Environment.NewLine; + cmd += String.Format("{0} = {1};", Constants.GUID, guid); + + return cmd; + } + + /// + /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. + /// + public void Dispose() + { + _connection.Close(); + } + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs b/Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs new file mode 100644 index 000000000..1635e6fa7 --- /dev/null +++ b/Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs @@ -0,0 +1,45 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; +using remote = Tango.DAL.Remote.DB; +using local = Tango.DAL.Local.DB; + +namespace Tango.Synchronization.Remote +{ + public class RemoteDBComparer : IDisposable + { + private remote.RemoteDB _remoteDB; + private local.LocalDB _localDB; + private String _machineSerial; + + public RemoteDBComparer(remote.RemoteDB remoteDB, local.LocalDB localDB,String machineSerial) + { + _machineSerial = machineSerial; + _remoteDB = remoteDB; + _localDB = localDB; + } + + public List Compare() + { + List diffs = new List(); + + var remoteMachine = _remoteDB.MACHINES.SingleOrDefault(x => x.SERIAL_NUMBER == _machineSerial); + + + //var localMachine = _localDB.MACHINES.SingleOrDefault(x => x.Se) + + + + + return diffs; + } + + public void Dispose() + { + _remoteDB.Dispose(); + _localDB.Dispose(); + } + } +} diff --git a/Software/Visual_Studio/Tango.Synchronization/SqlDataBaseComparer.cs b/Software/Visual_Studio/Tango.Synchronization/SqlDataBaseComparer.cs deleted file mode 100644 index 980a23f1e..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/SqlDataBaseComparer.cs +++ /dev/null @@ -1,200 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Data; -using System.IO; -using System.Linq; -using System.Text; -using System.Threading.Tasks; -using Tango.Logging; - -namespace Tango.Synchronization -{ - /// - /// Represents an synchronization engine. - /// - /// - public class SqlDataBaseComparer : IDisposable - { - /// - /// Gets the master SQL. - /// - public ISqlDataBase MasterSQL { get; private set; } - - /// - /// Gets the slave SQL. - /// - public ISqlDataBase SlaveSQL { get; private set; } - - /// - /// Initializes a new instance of the class. - /// - /// The master SQL. - /// The slave SQL. - public SqlDataBaseComparer(ISqlDataBase masterSQL, ISqlDataBase slaveSQL) - { - MasterSQL = masterSQL; - SlaveSQL = slaveSQL; - } - - /// - /// Compares the master and slave SQL data base and returns a collection of . - /// - /// - public List Compare() - { - LogManager.Log("Comparing databases " + Path.GetFileName(MasterSQL.Source) + " <=> " + Path.GetFileName(SlaveSQL.Source)); - - LogManager.Log("Loading master tables..."); - MasterSQL.LoadTables(); - - LogManager.Log("Loading slave tables..."); - SlaveSQL.LoadTables(); - - List diffs = new List(); - - foreach (var masterTable in MasterSQL.Tables) - { - LogManager.Log("Comparing table " + masterTable.TableName); - LogManager.Log("Searching table " + masterTable.TableName + " on slave..."); - - var slaveTable = SlaveSQL.Tables.SingleOrDefault(x => x.TableName == masterTable.TableName); //Get matching slave table on slave db. - - if (slaveTable == null) //Table not found on slave. - { - LogManager.Log("Table not found on slave, adding difference."); - //Clone table from slave db to master db including records! - diffs.Add(new SqlDiff(SqlDiffAction.AddTableToSlave, String.Format("Add table {0} to slave", masterTable.TableName), () => SlaveSQL.CloneTableFrom(MasterSQL, masterTable), SlaveSQL.GetCloneTableFromCommand(MasterSQL, masterTable))); - continue; - } - - LogManager.Log("Table found, comparing columns..."); - - foreach (DataColumn masterColumn in masterTable.Columns) - { - LogManager.Log("Searching for column " + masterColumn.ColumnName + " on slave..."); - - var slaveColumn = slaveTable.Columns[masterColumn.ColumnName]; //Get matching slave column on slave table. - - if (slaveColumn == null) //Slave column not found. - { - LogManager.Log("Column not found on slave, adding difference."); - //Add column to slave table. - diffs.Add(new SqlDiff(SqlDiffAction.AddColumnToSlave, String.Format("Add column {0} to slave table", masterColumn.ColumnName), () => SlaveSQL.AddColumn(masterTable, masterColumn), SlaveSQL.GetAddColumnCommand(masterTable, masterColumn))); - } - - LogManager.Log("Column found."); - } - - List addToSlave = new List(); - List updateSlave = new List(); - List addToMaster = new List(); - List updateMaster = new List(); - - LogManager.Log("Comparing rows..."); - - int count = 0; - - foreach (DataRow masterRow in masterTable.Rows) - { - LogManager.Log("Comparing row " + count++); - - String guid = masterRow.Field(Constants.GUID); - - LogManager.Log("Searching for row with GUID " + guid + " on slave table..."); - - //Get Matching slave row. - DataRow slaveRow = slaveTable.AsEnumerable().SingleOrDefault(x => x.Field(Constants.GUID) == guid); - - if (slaveRow != null) - { - LogManager.Log("Slave row found, comparing dates..."); - - DateTime masterDate = masterRow.Field(Constants.LAST_UPDATED); - DateTime slaveDate = slaveRow.Field(Constants.LAST_UPDATED); - - if (masterDate > slaveDate) - { - LogManager.Log("Master => Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."); - updateSlave.Add(masterRow); - } - else if (slaveDate > masterDate) - { - LogManager.Log("Master <= Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."); - updateMaster.Add(slaveRow); - } - else - { - LogManager.Log("Master <=> Slave No Update."); - } - } - else - { - LogManager.Log("Slave row not found, adding difference."); - addToSlave.Add(masterRow); - } - } - - LogManager.Log("Done comparing rows..."); - - LogManager.Log("Searching for missing rows on master..."); - - foreach (DataRow slaveRow in slaveTable.Rows) - { - String guid = slaveRow.Field(Constants.GUID); - - LogManager.Log("Searching for row with GUID " + guid + " on master table..."); - - //Get Matching slave row. - DataRow masterRow = masterTable.AsEnumerable().SingleOrDefault(x => x.Field(Constants.GUID) == guid); - - if (masterRow == null) - { - LogManager.Log("Master row not found, adding difference."); - addToMaster.Add(slaveRow); - } - else - { - LogManager.Log("Master row found."); - } - } - - LogManager.Log("Done searching for missing rows on master..."); - - foreach (var row in addToSlave) - { - diffs.Add(new SqlDiff(SqlDiffAction.AddRowToSlave, String.Format("Add row to slave table {0}", slaveTable.TableName), () => SlaveSQL.AddRow(slaveTable, row), SlaveSQL.GetAddRowCommand(slaveTable, row))); - } - - foreach (var row in addToMaster) - { - diffs.Add(new SqlDiff(SqlDiffAction.AddRowToMaster, String.Format("Add row to master table {0}", masterTable.TableName), () => MasterSQL.AddRow(masterTable, row), MasterSQL.GetAddRowCommand(masterTable, row))); - } - - foreach (var row in updateSlave) - { - diffs.Add(new SqlDiff(SqlDiffAction.UpdateRowInSlave, String.Format("Update row in slave table {0}", slaveTable.TableName), () => SlaveSQL.UpdateRow(slaveTable, row), SlaveSQL.GetUpdateRowCommand(slaveTable, row))); - } - - foreach (var row in updateMaster) - { - diffs.Add(new SqlDiff(SqlDiffAction.UpdateRowInMaster, String.Format("Update row in master table {0}", masterTable.TableName), () => MasterSQL.UpdateRow(masterTable, row), MasterSQL.GetUpdateRowCommand(masterTable, row))); - } - - LogManager.Log("Done comparing table " + masterTable.TableName); - } - - LogManager.Log("Databases comparison completed."); - - return diffs; - } - - /// - /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. - /// - public void Dispose() - { - MasterSQL.Dispose(); - SlaveSQL.Dispose(); - } - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/SqlDiff.cs b/Software/Visual_Studio/Tango.Synchronization/SqlDiff.cs deleted file mode 100644 index b5a274cdc..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/SqlDiff.cs +++ /dev/null @@ -1,65 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - /// - /// Represents an result. - /// - public class SqlDiff - { - private Action _action; - - /// - /// Gets the difference action. - /// - public SqlDiffAction Action { get; private set; } - - /// - /// Gets the difference description. - /// - public String Description { get; private set; } - - /// - /// Gets the command. - /// - public String Command { get; private set; } - - /// - /// Initializes a new instance of the class. - /// - /// The data base action. - /// The description. - /// The action. - /// The command. - public SqlDiff(SqlDiffAction dataBaseAction, String description, Action action, String command) - { - Action = dataBaseAction; - Description = description; - _action = action; - Command = command; - } - - /// - /// Commits the difference to the target DB. - /// - public void Commit() - { - _action(); - } - - /// - /// Returns a that represents this instance. - /// - /// - /// A that represents this instance. - /// - public override string ToString() - { - return Command; - } - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/SqlDiffAction.cs b/Software/Visual_Studio/Tango.Synchronization/SqlDiffAction.cs deleted file mode 100644 index 798be78d8..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/SqlDiffAction.cs +++ /dev/null @@ -1,40 +0,0 @@ -using System; -using System.Collections.Generic; -using System.ComponentModel; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - /// - /// Represents the type of . - /// - public enum SqlDiffAction - { - /// - /// Add table to slave - /// - AddTableToSlave, - /// - /// Add column to slave - /// - AddColumnToSlave, - /// - /// Add row to slave - /// - AddRowToSlave, - /// - /// Add row to master - /// - AddRowToMaster, - /// - /// Update row in slave - /// - UpdateRowInSlave, - /// - /// Update row in master - /// - UpdateRowInMaster, - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/SqliteDataBase.cs b/Software/Visual_Studio/Tango.Synchronization/SqliteDataBase.cs deleted file mode 100644 index 75259fc28..000000000 --- a/Software/Visual_Studio/Tango.Synchronization/SqliteDataBase.cs +++ /dev/null @@ -1,434 +0,0 @@ -using Tango.Synchronization; -using System; -using System.Collections.Generic; -using System.Data; -using System.Data.SQLite; -using System.Diagnostics; -using System.IO; -using System.Linq; -using System.Text; -using System.Threading.Tasks; - -namespace Tango.Synchronization -{ - /// - /// Represents an SQLite database adapter used for synchronization by . - /// - /// - public class SqliteDataBase : ISqlDataBase - { - private SQLiteConnection _connection; - - /// - /// Gets the database source URL/File. - /// - public String Source { get; private set; } - - /// - /// Gets the database tables collection. - /// - public List Tables { get; private set; } - - /// - /// Initializes a new instance of the class. - /// - /// The file path. - public SqliteDataBase(String filePath) - { - Source = filePath; - - _connection = new SQLiteConnection(String.Format("Data Source={0};Version=3;New={1};Compress=FALSE;", - filePath, - (File.Exists(filePath) ? "False" : "True") - )); - - _connection.Open(); - } - - /// - /// Loads the tables (Must be done before any synchronization). - /// - public void LoadTables() - { - Tables = new List(); - - var command = _connection.CreateCommand(); - command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'"; - var reader = command.ExecuteReader(); - - while (reader.Read()) - { - String name = reader.GetString(0); - - if (name != Constants.SEQUENCE_TABLE_NAME) - { - Debug.WriteLine(name); - Tables.Add(LoadTable(name)); - } - } - } - - /// - /// Loads the table. - /// - /// The name. - /// - private DataTable LoadTable(String name) - { - var tableCommand = _connection.CreateCommand(); - tableCommand.CommandText = "SELECT * FROM " + name; - - DataTable table = new DataTable(name); - table.Load(tableCommand.ExecuteReader()); - - var infoCommand = _connection.CreateCommand(); - infoCommand.CommandText = String.Format("PRAGMA table_info({0});", name); - DataTable infoTable = new DataTable(name); - infoTable.Load(infoCommand.ExecuteReader()); - - table.ExtendedProperties.Add(Constants.TABLE_INFO, infoTable); - - for (int i = 0; i < table.Columns.Count; i++) - { - DataColumn column = table.Columns[i]; - column.ExtendedProperties.Add(Constants.COLUMN_TYPE, infoTable.Rows[i][Constants.COLUMN_TYPE]); - column.ExtendedProperties.Add(Constants.IS_NOT_NULL, infoTable.Rows[i][Constants.IS_NOT_NULL]); - column.ExtendedProperties.Add(Constants.DEFAULT_VALUE, infoTable.Rows[i][Constants.DEFAULT_VALUE]); - } - - return table; - } - - /// - /// Clones a table from another database into this database. - /// - /// The other database. - /// The other table. - /// - public DataTable CloneTableFrom(ISqlDataBase otherDB, DataTable otherTable) - { - String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName); - - foreach (DataColumn column in otherTable.Columns) - { - cmd += String.Format("{0} {1} {2} {3} {4} {5}", - column.ColumnName, - column.GetSQLType(), - otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, - column.Unique ? "UNIQUE" : null, - column.IsNotNull() ? "NOT NULL" : null, - column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null) - - + ", " + Environment.NewLine; - } - - cmd += ");"; - - cmd = cmd.Remove(cmd.LastIndexOf(","), 1); - - var createCommand = _connection.CreateCommand(); - createCommand.CommandText = cmd; - createCommand.ExecuteNonQuery(); - - var attacheCommand = _connection.CreateCommand(); - attacheCommand.CommandText = String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source); - attacheCommand.ExecuteNonQuery(); - - var copyCommand = _connection.CreateCommand(); - copyCommand.CommandText = String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other"); - copyCommand.ExecuteNonQuery(); - - var detacheCommand = _connection.CreateCommand(); - detacheCommand.CommandText = "DETACH other;"; - detacheCommand.ExecuteNonQuery(); - - var table = LoadTable(otherTable.TableName); - Tables.Add(table); - return table; - } - - /// - /// Gets the SQL command for . - /// - /// The other database. - /// The other table. - /// - public String GetCloneTableFromCommand(ISqlDataBase otherDB, DataTable otherTable) - { - String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName); - - foreach (DataColumn column in otherTable.Columns) - { - cmd += String.Format("{0} {1} {2} {3} {4} {5}", - column.ColumnName, - column.GetSQLType(), - otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, - column.Unique ? "UNIQUE" : null, - column.IsNotNull() ? "NOT NULL" : null, - column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null) - - + ", " + Environment.NewLine; - } - - cmd += ");"; - - cmd = cmd.Remove(cmd.LastIndexOf(","), 1); - - cmd += Environment.NewLine; - - cmd += String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source); - - cmd += Environment.NewLine; - - cmd += String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other"); - - cmd += Environment.NewLine; - - cmd += "DETACH other;"; - - return cmd; - } - - /// - /// Adds the specified column to the specified table. - /// - /// The table. - /// The column. - /// - public DataColumn AddColumn(DataTable table, DataColumn column) - { - var insertCommand = _connection.CreateCommand(); - insertCommand.CommandText = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5} {6}", - table.TableName, - column.ColumnName, - column.GetSQLType(), - table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, - column.Unique ? "UNIQUE" : null, - column.IsNotNull() ? "NOT NULL" : null, - column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null); - - - insertCommand.ExecuteNonQuery(); - var t = LoadTable(table.TableName); - var index = Tables.IndexOf(Tables.Single(x => x.TableName == table.TableName)); - Tables[index] = t; - return t.Columns[column.ColumnName]; - } - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The column. - /// - public String GetAddColumnCommand(DataTable table, DataColumn column) - { - String cmd = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5} {6}", - table.TableName, - column.ColumnName, - column.GetSQLType(), - table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null, - column.Unique ? "UNIQUE" : null, - column.IsNotNull() ? "NOT NULL" : null, - column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null); - - return cmd; - } - - /// - /// Adds the specified row to the specified table. - /// - /// The table. - /// The row. - public void AddRow(DataTable table, DataRow row) - { - var insertCommand = _connection.CreateCommand(); - - List values = new List(); - - for (int i = 0; i < row.ItemArray.Length; i++) - { - String value = row.ItemArray[i].ToString(); - - double num = 0; - - if (row.ItemArray[i].GetType() == typeof(DateTime)) - { - value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); - } - - if (!double.TryParse(value, out num)) - { - value = "'" + value + "'"; - } - - values.Add(value); - } - - insertCommand.CommandText = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join(",", values)); - insertCommand.ExecuteNonQuery(); - - var desRow = table.NewRow(); - desRow.ItemArray = row.ItemArray.Clone() as object[]; - } - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The row. - /// - public String GetAddRowCommand(DataTable table, DataRow row) - { - List values = new List(); - - for (int i = 0; i < row.ItemArray.Length; i++) - { - String value = row.ItemArray[i].ToString(); - - double num = 0; - - if (row.ItemArray[i].GetType() == typeof(DateTime)) - { - value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); - } - - if (!double.TryParse(value, out num)) - { - value = "'" + value + "'"; - } - - values.Add(value); - } - - String cmd = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join("," + Environment.NewLine, values)); - return cmd; - } - - /// - /// Updates the matching row by the row GUID. - /// - /// The table. - /// The row. - public void UpdateRow(DataTable table, DataRow row) - { - var updateCommand = _connection.CreateCommand(); - - List values = new List(); - - for (int i = 0; i < row.ItemArray.Length; i++) - { - String value = row.ItemArray[i].ToString(); - - double num = 0; - - if (row.ItemArray[i].GetType() == typeof(DateTime)) - { - value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); - } - - if (!double.TryParse(value, out num)) - { - value = "'" + value + "'"; - } - - values.Add(value); - } - - String cmd = String.Format("UPDATE {0} SET ", table.TableName); - - String guid = String.Empty; - - for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) - { - DataColumn column = table.Columns[columnIndex]; - if (column.ColumnName == Constants.ID) - { - continue; - } - else if (column.ColumnName == Constants.GUID) - { - guid = values[columnIndex]; - continue; - } - - cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine); - } - - cmd = cmd.Remove(cmd.LastIndexOf(","), 1); - - cmd += "WHERE" + Environment.NewLine; - cmd += String.Format("{0} = {1};", Constants.GUID, guid); - - updateCommand.CommandText = cmd; - updateCommand.ExecuteNonQuery(); - var desRow = table.NewRow(); - desRow.ItemArray = row.ItemArray.Clone() as object[]; - } - - /// - /// Gets the SQL command for . - /// - /// The table. - /// The row. - /// - public String GetUpdateRowCommand(DataTable table, DataRow row) - { - List values = new List(); - - for (int i = 0; i < row.ItemArray.Length; i++) - { - String value = row.ItemArray[i].ToString(); - - double num = 0; - - if (row.ItemArray[i].GetType() == typeof(DateTime)) - { - value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString(); - } - - if (!double.TryParse(value, out num)) - { - value = "'" + value + "'"; - } - - values.Add(value); - } - - String cmd = String.Format("UPDATE {0} SET ", table.TableName); - - String guid = String.Empty; - - for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) - { - DataColumn column = table.Columns[columnIndex]; - if (column.ColumnName == Constants.ID) - { - continue; - } - else if (column.ColumnName == Constants.GUID) - { - guid = values[columnIndex]; - continue; - } - - cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine); - } - - cmd = cmd.Remove(cmd.LastIndexOf(","), 1); - - cmd += "WHERE" + Environment.NewLine; - cmd += String.Format("{0} = {1};", Constants.GUID, guid); - - return cmd; - } - - /// - /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. - /// - public void Dispose() - { - _connection.Close(); - } - } -} diff --git a/Software/Visual_Studio/Tango.Synchronization/Tango.Synchronization.csproj b/Software/Visual_Studio/Tango.Synchronization/Tango.Synchronization.csproj index 98a5044ce..b94eec9fa 100644 --- a/Software/Visual_Studio/Tango.Synchronization/Tango.Synchronization.csproj +++ b/Software/Visual_Studio/Tango.Synchronization/Tango.Synchronization.csproj @@ -64,25 +64,35 @@ GlobalVersionInfo.cs - - - + + + - - - - + + + + + + + {0e0eef3e-8f4e-4f23-9d19-479fd8d76c12} + Tango.DAL.Local + + + {38197109-8610-4d3f-92b9-16d48df94d7c} + Tango.DAL.Remote + {bc932dbd-7cdb-488c-99e4-f02cf441f55e} Tango.Logging + -- cgit v1.3.1