1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Tango.Scripting;
namespace TestApp
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
private IScriptingEngine _engine;
private ScriptSession _session;
public MainWindow()
{
InitializeComponent();
_engine = new ScriptingEngine();
}
private async void Button_Click(object sender, RoutedEventArgs e)
{
Script s = new Script();
s.Code =
@"
using System;
using System.Windows.Forms;
MessageBox.Show(""Hi Roy"");
";
try
{
_session = await _engine.Run(s);
_session.StateChanged += Session_StateChanged;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void Session_StateChanged(object sender, ScriptSessionStateChangedEventArgs e)
{
if (e.State == ScriptSessionState.Completed)
{
MessageBox.Show(e.ReturnValue.ToString());
}
else if (e.State == ScriptSessionState.Aborted)
{
MessageBox.Show("Aborted");
}
else if (e.State == ScriptSessionState.Failed)
{
MessageBox.Show(e.Exception.ToString());
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
_session.Abort();
}
}
}
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;
using System.Threading;
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>
/// Clears the data base.
/// </summary>
public void ClearDataBase()
{
foreach (var table in Tables)
{
var dropCommand = _connection.CreateCommand();
dropCommand.CommandText = String.Format("DELETE FROM {0};", table.TableName);
dropCommand.ExecuteNonQuery();
}
}
/// <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>
/// Replaces the table data with the data from another table.
/// </summary>
/// <param name="otherTable">The other table.</param>
public void ReplaceTableData(ILocalDataBase otherDB, DataTable otherTable)
{
var dropCommand = _connection.CreateCommand();
dropCommand.CommandText = String.Format("DELETE FROM {0};", otherTable.TableName);
dropCommand.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();
}
/// <summary>
/// Gets the SQL command for <see cref="ReplaceTableData(ILocalDataBase, DataTable)"/>.
/// </summary>
/// <param name="otherDB"></param>
/// <param name="otherTable">The other table.</param>
/// <returns></returns>
public string GetReplaceTableDataCommand(ILocalDataBase otherDB, DataTable otherTable)
{
String cmd = String.Format("DELETE FROM {0};", otherTable.TableName) + Environment.NewLine;
cmd += String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source) + Environment.NewLine;
cmd += String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other") + Environment.NewLine;
cmd += "DETACH other;" + Environment.NewLine;
return cmd;
}
/// <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}",
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}",
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(bool))
{
value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
}
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(bool))
{
value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
}
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(bool))
{
value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
}
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(bool))
{
value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
}
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();
_connection.Dispose();
GC.Collect();
DateTime startTime = DateTime.Now;
while (startTime.AddSeconds(2) > DateTime.Now)
{
try
{
using (FileStream stream = File.Open(Source, FileMode.Open, FileAccess.Read))
{
return;
}
}
catch (IOException)
{
GC.Collect();
Thread.Sleep(200);
}
}
}
}
}
|
