1 files changed, 434 insertions, 0 deletions
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
+{
+    /// <summary>
+    /// Represents an SQLite database adapter used for synchronization by <see cref="LocalDBComparer"/>.
+    /// </summary>
+    /// <seealso cref="Tango.Synchronization.ISqlDataBase" />
+    public class SQLiteDataBase : ILocalDataBase
+    {
+        private SQLiteConnection _connection;
+
+        /// <summary>
+        /// Gets the database source URL/File.
+        /// </summary>
+        public String Source { get; private set; }
+
+        /// <summary>
+        /// Gets the database tables collection.
+        /// </summary>
+        public List<DataTable> Tables { get; private set; }
+
+        /// <summary>
+        /// Initializes a new instance of the <see cref="SQLiteDataBase"/> class.
+        /// </summary>
+        /// <param name="filePath">The file path.</param>
+        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();
+        }
+
+        /// <summary>
+        /// Loads the tables (Must be done before any synchronization).
+        /// </summary>
+        public void LoadTables()
+        {
+            Tables = new List<DataTable>();
+
+            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));
+                }
+            }
+        }
+
+        /// <summary>
+        /// Loads the table.
+        /// </summary>
+        /// <param name="name">The name.</param>
+        /// <returns></returns>
+        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;
+        }
+
+        /// <summary>
+        /// Clones a table from another database into this database.
+        /// </summary>
+        /// <param name="otherDB">The other database.</param>
+        /// <param name="otherTable">The other table.</param>
+        /// <returns></returns>
+        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;
+        }
+
+        /// <summary>
+        /// Gets the SQL command for <see cref="CloneTableFrom(ILocalDataBase, DataTable)" />.
+        /// </summary>
+        /// <param name="otherDB">The other database.</param>
+        /// <param name="otherTable">The other table.</param>
+        /// <returns></returns>
+        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;
+        }
+
+        /// <summary>
+        /// Adds the specified column to the specified table.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="column">The column.</param>
+        /// <returns></returns>
+        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];
+        }
+
+        /// <summary>
+        /// Gets the SQL command for <see cref="AddColumn(DataTable, DataColumn)" />.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="column">The column.</param>
+        /// <returns></returns>
+        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;
+        }
+
+        /// <summary>
+        /// Adds the specified row to the specified table.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="row">The row.</param>
+        public void AddRow(DataTable table, DataRow row)
+        {
+            var insertCommand = _connection.CreateCommand();
+
+            List<String> values = new List<string>();
+
+            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[];
+        }
+
+        /// <summary>
+        /// Gets the SQL command for <see cref="AddRow(DataTable, DataRow)" />.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="row">The row.</param>
+        /// <returns></returns>
+        public String GetAddRowCommand(DataTable table, DataRow row)
+        {
+            List<String> values = new List<string>();
+
+            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;
+        }
+
+        /// <summary>
+        /// Updates the matching row by the row GUID.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="row">The row.</param>
+        public void UpdateRow(DataTable table, DataRow row)
+        {
+            var updateCommand = _connection.CreateCommand();
+
+            List<String> values = new List<string>();
+
+            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[];
+        }
+
+        /// <summary>
+        /// Gets the SQL command for <see cref="UpdateRow(DataTable, DataRow)" />.
+        /// </summary>
+        /// <param name="table">The table.</param>
+        /// <param name="row">The row.</param>
+        /// <returns></returns>
+        public String GetUpdateRowCommand(DataTable table, DataRow row)
+        {
+            List<String> values = new List<string>();
+
+            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;
+        }
+
+        /// <summary>
+        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
+        /// </summary>
+        public void Dispose()
+        {
+            _connection.Close();
+        }
+    }
+}