Added SENSORS table to db.

Added SQLServer To SQLite conversion library.

1 files changed, 1371 insertions, 0 deletions

diff --git a/Software/Visual_Studio/Tango.Synchronization/Conversion/SqlServerToSQLiteConverter.cs b/Software/Visual_Studio/Tango.Synchronization/Conversion/SqlServerToSQLiteConverter.cs
new file mode 100644
index 000000000..360198320
--- /dev/null
+++ b/Software/Visual_Studio/Tango.Synchronization/Conversion/SqlServerToSQLiteConverter.cs
@@ -0,0 +1,1371 @@
+using System;
+using System.Collections.Generic;
+using System.Text;
+using System.Data;
+using System.Data.SqlClient;
+using System.Data.SQLite;
+using System.Threading;
+using System.Text.RegularExpressions;
+using System.IO;
+using Tango.Logging;
+
+namespace Tango.Synchronization.Conversion
+{
+    /// <summary>
+    /// This class is responsible to take a single SQL Server database
+    /// and convert it to an SQLite database file.
+    /// </summary>
+    /// <remarks>The class knows how to convert table and index structures only.</remarks>
+    public class SqlServerToSQLiteConverter
+    {
+        #region Public Properties
+        /// <summary>
+        /// Gets a value indicating whether this instance is active.
+        /// </summary>
+        /// <value><c>true</c> if this instance is active; otherwise, <c>false</c>.</value>
+        public bool IsActive
+        {
+            get { return _isActive; }
+        }
+
+        public bool GenerateForeignKeys { get; set; }
+
+        public bool GenerateIndexes { get; set; }
+
+        #endregion
+
+        #region Public Methods
+        /// <summary>
+        /// Cancels the conversion.
+        /// </summary>
+        public void CancelConversion()
+        {
+            _cancelled = true;
+        }
+
+        /// <summary>
+        /// This method takes as input the connection string to an SQL Server database
+        /// and creates a corresponding SQLite database file with a schema derived from
+        /// the SQL Server database.
+        /// </summary>
+        /// <param name="sqlServerConnString">The connection string to the SQL Server database.</param>
+        /// <param name="sqlitePath">The path to the SQLite database file that needs to get created.</param>
+        /// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
+        /// <param name="handler">A handler delegate for progress notifications.</param>
+        /// <param name="selectionHandler">The selection handler that allows the user to select which
+        /// tables to convert</param>
+        /// <remarks>The method continues asynchronously in the background and the caller returned
+        /// immediately.</remarks>
+        public void ConvertSqlServerToSQLiteDatabase(string sqlServerConnString,
+            string sqlitePath, string password, SqlConversionHandler handler,
+            SqlTableSelectionHandler selectionHandler,
+            FailedViewDefinitionHandler viewFailureHandler,
+            bool createTriggers, bool createViews)
+        {
+            // Clear canceled flag
+            _cancelled = false;
+
+            WaitCallback wc = new WaitCallback(delegate (object state)
+            {
+                try
+                {
+                    _isActive = true;
+                    ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers, createViews);
+                    _isActive = false;
+                    handler(true, true, 100, "Finished converting database");
+                }
+                catch (Exception ex)
+                {
+                    LogManager.Log(ex, "Failed to convert SQL Server database to SQLite database");
+                    _isActive = false;
+                    handler(true, false, 100, ex.Message);
+                } // catch
+            });
+            ThreadPool.QueueUserWorkItem(wc);
+        }
+        #endregion
+
+        #region Private Methods
+        /// <summary>
+        /// Do the entire process of first reading the SQL Server schema, creating a corresponding
+        /// SQLite schema, and copying all rows from the SQL Server database to the SQLite database.
+        /// </summary>
+        /// <param name="sqlConnString">The SQL Server connection string</param>
+        /// <param name="sqlitePath">The path to the generated SQLite database file</param>
+        /// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
+        /// <param name="handler">A handler to handle progress notifications.</param>
+        /// <param name="selectionHandler">The selection handler which allows the user to select which tables to 
+        /// convert.</param>
+        private void ConvertSqlServerDatabaseToSQLiteFile(
+            string sqlConnString, string sqlitePath, string password, SqlConversionHandler handler,
+            SqlTableSelectionHandler selectionHandler,
+            FailedViewDefinitionHandler viewFailureHandler,
+            bool createTriggers, bool createViews)
+        {
+            // Delete the target file if it exists already.
+            if (File.Exists(sqlitePath))
+                File.Delete(sqlitePath);
+
+            // Read the schema of the SQL Server database into a memory structure
+            DatabaseSchema ds = ReadSqlServerSchema(sqlConnString, handler, selectionHandler);
+
+            // Create the SQLite database and apply the schema
+            CreateSQLiteDatabase(sqlitePath, ds, password, handler, viewFailureHandler, createViews);
+
+            // Copy all rows from SQL Server tables to the newly created SQLite database
+            CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, ds.Tables, password, handler);
+
+            // Add triggers based on foreign key constraints
+            if (createTriggers)
+                AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, handler);
+
+        }
+
+        /// <summary>
+        /// Copies table rows from the SQL Server database to the SQLite database.
+        /// </summary>
+        /// <param name="sqlConnString">The SQL Server connection string</param>
+        /// <param name="sqlitePath">The path to the SQLite database file.</param>
+        /// <param name="schema">The schema of the SQL Server database.</param>
+        /// <param name="password">The password to use for encrypting the file</param>
+        /// <param name="handler">A handler to handle progress notifications.</param>
+        private void CopySqlServerRowsToSQLiteDB(
+            string sqlConnString, string sqlitePath, List<TableSchema> schema,
+            string password, SqlConversionHandler handler)
+        {
+            CheckCancelled();
+            handler(false, true, 0, "Preparing to insert tables...");
+            LogManager.Log("preparing to insert tables ...");
+
+            // Connect to the SQL Server database
+            using (SqlConnection ssconn = new SqlConnection(sqlConnString))
+            {
+                ssconn.Open();
+
+                // Connect to the SQLite database next
+                string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
+                using (SQLiteConnection sqconn = new SQLiteConnection(sqliteConnString))
+                {
+                    sqconn.Open();
+
+                    // Go over all tables in the schema and copy their rows
+                    for (int i = 0; i < schema.Count; i++)
+                    {
+                        if (schema[i].CopyData)
+                        {
+                            SQLiteTransaction tx = sqconn.BeginTransaction();
+                            try
+                            {
+                                string tableQuery = BuildSqlServerTableQuery(schema[i]);
+                                SqlCommand query = new SqlCommand(tableQuery, ssconn);
+                                using (SqlDataReader reader = query.ExecuteReader())
+                                {
+                                    SQLiteCommand insert = BuildSQLiteInsert(schema[i]);
+                                    int counter = 0;
+                                    while (reader.Read())
+                                    {
+                                        insert.Connection = sqconn;
+                                        insert.Transaction = tx;
+                                        List<string> pnames = new List<string>();
+                                        for (int j = 0; j < schema[i].Columns.Count; j++)
+                                        {
+                                            string pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
+                                            insert.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
+                                            pnames.Add(pname);
+                                        }
+                                        insert.ExecuteNonQuery();
+                                        counter++;
+                                        if (counter % 1000 == 0)
+                                        {
+                                            CheckCancelled();
+                                            tx.Commit();
+                                            handler(false, true, (int)(100.0 * i / schema.Count),
+                                                "Added " + counter + " rows to table " + schema[i].TableName + " so far");
+                                            tx = sqconn.BeginTransaction();
+                                        }
+                                    } // while
+                                } // using
+
+                                CheckCancelled();
+                                tx.Commit();
+
+                                handler(false, true, (int)(100.0 * i / schema.Count), "Finished inserting rows for table " + schema[i].TableName);
+                                LogManager.Log("finished inserting all rows for table [" + schema[i].TableName + "]");
+                            }
+                            catch (Exception ex)
+                            {
+                                LogManager.Log(ex, "unexpected exception");
+                                tx.Rollback();
+                                throw;
+                            } // catch
+                        }
+                    }
+                } // using
+            } // using
+        }
+
+        /// <summary>
+        /// Used in order to adjust the value received from SQL Server for the SQLite database.
+        /// </summary>
+        /// <param name="val">The value object</param>
+        /// <param name="columnSchema">The corresponding column schema</param>
+        /// <returns>SQLite adjusted value.</returns>
+        private object CastValueForColumn(object val, ColumnSchema columnSchema)
+        {
+            if (val is DBNull)
+                return null;
+
+            DbType dt = GetDbTypeOfColumn(columnSchema);
+
+            switch (dt)
+            {
+                case DbType.Int32:
+                    if (val is short)
+                        return (int)(short)val;
+                    if (val is byte)
+                        return (int)(byte)val;
+                    if (val is long)
+                        return (int)(long)val;
+                    if (val is decimal)
+                        return (int)(decimal)val;
+                    break;
+
+                case DbType.Int16:
+                    if (val is int)
+                        return (short)(int)val;
+                    if (val is byte)
+                        return (short)(byte)val;
+                    if (val is long)
+                        return (short)(long)val;
+                    if (val is decimal)
+                        return (short)(decimal)val;
+                    break;
+
+                case DbType.Int64:
+                    if (val is int)
+                        return (long)(int)val;
+                    if (val is short)
+                        return (long)(short)val;
+                    if (val is byte)
+                        return (long)(byte)val;
+                    if (val is decimal)
+                        return (long)(decimal)val;
+                    break;
+
+                case DbType.Single:
+                    if (val is double)
+                        return (float)(double)val;
+                    if (val is decimal)
+                        return (float)(decimal)val;
+                    break;
+
+                case DbType.Double:
+                    if (val is float)
+                        return (double)(float)val;
+                    if (val is double)
+                        return (double)val;
+                    if (val is decimal)
+                        return (double)(decimal)val;
+                    break;
+
+                case DbType.String:
+                    if (val is Guid)
+                        return ((Guid)val).ToString();
+                    break;
+
+                case DbType.Guid:
+                    if (val is string)
+                        return ParseStringAsGuid((string)val);
+                    if (val is byte[])
+                        return ParseBlobAsGuid((byte[])val);
+                    break;
+
+                case DbType.Binary:
+                case DbType.Boolean:
+                case DbType.DateTime:
+                    break;
+
+                default:
+                    LogManager.Log(new ArgumentException("argument exception - illegal database type"));
+                    throw new ArgumentException("Illegal database type [" + Enum.GetName(typeof(DbType), dt) + "]");
+            } // switch
+
+            return val;
+        }
+
+        private Guid ParseBlobAsGuid(byte[] blob)
+        {
+            byte[] data = blob;
+            if (blob.Length > 16)
+            {
+                data = new byte[16];
+                for (int i = 0; i < 16; i++)
+                    data[i] = blob[i];
+            }
+            else if (blob.Length < 16)
+            {
+                data = new byte[16];
+                for (int i = 0; i < blob.Length; i++)
+                    data[i] = blob[i];
+            }
+
+            return new Guid(data);
+        }
+
+        private Guid ParseStringAsGuid(string str)
+        {
+            try
+            {
+                return new Guid(str);
+            }
+            catch (Exception ex)
+            {
+                return Guid.Empty;
+            } // catch
+        }
+
+        /// <summary>
+        /// Creates a command object needed to insert values into a specific SQLite table.
+        /// </summary>
+        /// <param name="ts">The table schema object for the table.</param>
+        /// <returns>A command object with the required functionality.</returns>
+        private SQLiteCommand BuildSQLiteInsert(TableSchema ts)
+        {
+            SQLiteCommand res = new SQLiteCommand();
+
+            StringBuilder sb = new StringBuilder();
+            sb.Append("INSERT INTO [" + ts.TableName + "] (");
+            for (int i = 0; i < ts.Columns.Count; i++)
+            {
+                sb.Append("[" + ts.Columns[i].ColumnName + "]");
+                if (i < ts.Columns.Count - 1)
+                    sb.Append(", ");
+            } // for
+            sb.Append(") VALUES (");
+
+            List<string> pnames = new List<string>();
+            for (int i = 0; i < ts.Columns.Count; i++)
+            {
+                string pname = "@" + GetNormalizedName(ts.Columns[i].ColumnName, pnames);
+                sb.Append(pname);
+                if (i < ts.Columns.Count - 1)
+                    sb.Append(", ");
+
+                DbType dbType = GetDbTypeOfColumn(ts.Columns[i]);
+                SQLiteParameter prm = new SQLiteParameter(pname, dbType, ts.Columns[i].ColumnName);
+                res.Parameters.Add(prm);
+
+                // Remember the parameter name in order to avoid duplicates
+                pnames.Add(pname);
+            } // for
+            sb.Append(")");
+            res.CommandText = sb.ToString();
+            res.CommandType = CommandType.Text;
+            return res;
+        }
+
+        /// <summary>
+        /// Used in order to avoid breaking naming rules (e.g., when a table has
+        /// a name in SQL Server that cannot be used as a basis for a matching index
+        /// name in SQLite).
+        /// </summary>
+        /// <param name="str">The name to change if necessary</param>
+        /// <param name="names">Used to avoid duplicate names</param>
+        /// <returns>A normalized name</returns>
+        private string GetNormalizedName(string str, List<string> names)
+        {
+            StringBuilder sb = new StringBuilder();
+            for (int i = 0; i < str.Length; i++)
+            {
+                if (Char.IsLetterOrDigit(str[i]) || str[i] == '_')
+                    sb.Append(str[i]);
+                else
+                    sb.Append("_");
+            } // for
+
+            // Avoid returning duplicate name
+            if (names.Contains(sb.ToString()))
+                return GetNormalizedName(sb.ToString() + "_", names);
+            else
+                return sb.ToString();
+        }
+
+        /// <summary>
+        /// Matches SQL Server types to general DB types
+        /// </summary>
+        /// <param name="cs">The column schema to use for the match</param>
+        /// <returns>The matched DB type</returns>
+        private DbType GetDbTypeOfColumn(ColumnSchema cs)
+        {
+            if (cs.ColumnType == "tinyint")
+                return DbType.Byte;
+            if (cs.ColumnType == "int")
+                return DbType.Int32;
+            if (cs.ColumnType == "smallint")
+                return DbType.Int16;
+            if (cs.ColumnType == "bigint")
+                return DbType.Int64;
+            if (cs.ColumnType == "bit")
+                return DbType.Boolean;
+            if (cs.ColumnType == "nvarchar" || cs.ColumnType == "varchar" ||
+                cs.ColumnType == "text" || cs.ColumnType == "ntext")
+                return DbType.String;
+            if (cs.ColumnType == "float")
+                return DbType.Double;
+            if (cs.ColumnType == "real")
+                return DbType.Single;
+            if (cs.ColumnType == "blob")
+                return DbType.Binary;
+            if (cs.ColumnType == "numeric")
+                return DbType.Double;
+            if (cs.ColumnType == "timestamp" || cs.ColumnType == "datetime" || cs.ColumnType == "datetime2" || cs.ColumnType == "date" || cs.ColumnType == "time")
+                return DbType.DateTime;
+            if (cs.ColumnType == "nchar" || cs.ColumnType == "char")
+                return DbType.String;
+            if (cs.ColumnType == "uniqueidentifier" || cs.ColumnType == "guid")
+                return DbType.Guid;
+            if (cs.ColumnType == "xml")
+                return DbType.String;
+            if (cs.ColumnType == "sql_variant")
+                return DbType.Object;
+            if (cs.ColumnType == "integer")
+                return DbType.Int64;
+
+            LogManager.Log("illegal db type found");
+            throw new ApplicationException("Illegal DB type found (" + cs.ColumnType + ")");
+        }
+
+        /// <summary>
+        /// Builds a SELECT query for a specific table. Needed in the process of copying rows
+        /// from the SQL Server database to the SQLite database.
+        /// </summary>
+        /// <param name="ts">The table schema of the table for which we need the query.</param>
+        /// <returns>The SELECT query for the table.</returns>
+        private string BuildSqlServerTableQuery(TableSchema ts)
+        {
+            StringBuilder sb = new StringBuilder();
+            sb.Append("SELECT ");
+            for (int i = 0; i < ts.Columns.Count; i++)
+            {
+                sb.Append("[" + ts.Columns[i].ColumnName + "]");
+                if (i < ts.Columns.Count - 1)
+                    sb.Append(", ");
+            } // for
+            sb.Append(" FROM " + ts.TableSchemaName + "." + "[" + ts.TableName + "]");
+            return sb.ToString();
+        }
+
+        /// <summary>
+        /// Creates the SQLite database from the schema read from the SQL Server.
+        /// </summary>
+        /// <param name="sqlitePath">The path to the generated DB file.</param>
+        /// <param name="schema">The schema of the SQL server database.</param>
+        /// <param name="password">The password to use for encrypting the DB or null if non is needed.</param>
+        /// <param name="handler">A handle for progress notifications.</param>
+        private void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password,
+            SqlConversionHandler handler,
+            FailedViewDefinitionHandler viewFailureHandler, bool createViews)
+        {
+            LogManager.Log("Creating SQLite database...");
+
+            // Create the SQLite database file
+            SQLiteConnection.CreateFile(sqlitePath);
+
+            LogManager.Log("SQLite file was created successfully at [" + sqlitePath + "]");
+
+            // Connect to the newly created database
+            string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
+            using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
+            {
+                conn.Open();
+
+                // Create all tables in the new database
+                int count = 0;
+                foreach (TableSchema dt in schema.Tables)
+                {
+                    try
+                    {
+                        AddSQLiteTable(conn, dt);
+                    }
+                    catch (Exception ex)
+                    {
+                        LogManager.Log(ex, "AddSQLiteTable failed");
+                        throw;
+                    }
+                    count++;
+                    CheckCancelled();
+                    handler(false, true, (int)(count * 50.0 / schema.Tables.Count), "Added table " + dt.TableName + " to the SQLite database");
+
+                    LogManager.Log("added schema for SQLite table [" + dt.TableName + "]");
+                } // foreach
+
+                // Create all views in the new database
+                count = 0;
+                if (createViews)
+                {
+                    foreach (ViewSchema vs in schema.Views)
+                    {
+                        try
+                        {
+                            AddSQLiteView(conn, vs, viewFailureHandler);
+                        }
+                        catch (Exception ex)
+                        {
+                            LogManager.Log(ex, "AddSQLiteView failed");
+                            throw;
+                        } // catch
+                        count++;
+                        CheckCancelled();
+                        handler(false, true, 50 + (int)(count * 50.0 / schema.Views.Count), "Added view " + vs.ViewName + " to the SQLite database");
+
+                        LogManager.Log("added schema for SQLite view [" + vs.ViewName + "]");
+
+                    } // foreach
+                } // if
+            } // using
+
+            LogManager.Log("finished adding all table/view schemas for SQLite database");
+        }
+
+        private void AddSQLiteView(SQLiteConnection conn, ViewSchema vs, FailedViewDefinitionHandler handler)
+        {
+            // Prepare a CREATE VIEW DDL statement
+            string stmt = vs.ViewSQL;
+            LogManager.Log("\n\n" + stmt + "\n\n");
+
+            // Execute the query in order to actually create the view.
+            SQLiteTransaction tx = conn.BeginTransaction();
+            try
+            {
+                SQLiteCommand cmd = new SQLiteCommand(stmt, conn, tx);
+                cmd.ExecuteNonQuery();
+
+                tx.Commit();
+            }
+            catch (SQLiteException ex)
+            {
+                tx.Rollback();
+
+                if (handler != null)
+                {
+                    ViewSchema updated = new ViewSchema();
+                    updated.ViewName = vs.ViewName;
+                    updated.ViewSQL = vs.ViewSQL;
+
+                    // Ask the user to supply the new view definition SQL statement
+                    string sql = handler(updated);
+
+                    if (sql == null)
+                        return; // Discard the view
+                    else
+                    {
+                        // Try to re-create the view with the user-supplied view definition SQL
+                        updated.ViewSQL = sql;
+                        AddSQLiteView(conn, updated, handler);
+                    }
+                }
+                else
+                    throw;
+            } // catch
+        }
+
+        /// <summary>
+        /// Creates the CREATE TABLE DDL for SQLite and a specific table.
+        /// </summary>
+        /// <param name="conn">The SQLite connection</param>
+        /// <param name="dt">The table schema object for the table to be generated.</param>
+        private void AddSQLiteTable(SQLiteConnection conn, TableSchema dt)
+        {
+            // Prepare a CREATE TABLE DDL statement
+            string stmt = BuildCreateTableQuery(dt);
+
+            LogManager.Log("\n\n" + stmt + "\n\n");
+
+            // Execute the query in order to actually create the table.
+            SQLiteCommand cmd = new SQLiteCommand(stmt, conn);
+            cmd.ExecuteNonQuery();
+        }
+
+        /// <summary>
+        /// returns the CREATE TABLE DDL for creating the SQLite table from the specified
+        /// table schema object.
+        /// </summary>
+        /// <param name="ts">The table schema object from which to create the SQL statement.</param>
+        /// <returns>CREATE TABLE DDL for the specified table.</returns>
+        private string BuildCreateTableQuery(TableSchema ts)
+        {
+            StringBuilder sb = new StringBuilder();
+
+            sb.Append("CREATE TABLE [" + ts.TableName + "] (\n");
+
+            bool pkey = false;
+            for (int i = 0; i < ts.Columns.Count; i++)
+            {
+                ColumnSchema col = ts.Columns[i];
+                string cline = BuildColumnStatement(col, ts, ref pkey);
+                sb.Append(cline);
+                if (i < ts.Columns.Count - 1)
+                    sb.Append(",\n");
+            } // foreach
+
+            // add primary keys...
+            if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0 & !pkey)
+            {
+                sb.Append(",\n");
+                sb.Append("    PRIMARY KEY (");
+                for (int i = 0; i < ts.PrimaryKey.Count; i++)
+                {
+                    sb.Append("[" + ts.PrimaryKey[i] + "]");
+                    if (i < ts.PrimaryKey.Count - 1)
+                        sb.Append(", ");
+                } // for
+                sb.Append(")\n");
+            }
+            else
+                sb.Append("\n");
+
+            if (GenerateForeignKeys)
+            {
+                // add foreign keys...
+                if (ts.ForeignKeys.Count > 0)
+                {
+                    sb.Append(",\n");
+                    for (int i = 0; i < ts.ForeignKeys.Count; i++)
+                    {
+                        ForeignKeySchema foreignKey = ts.ForeignKeys[i];
+                        string stmt = string.Format("    FOREIGN KEY ([{0}])\n        REFERENCES [{1}]([{2}])",
+                                    foreignKey.ColumnName, foreignKey.ForeignTableName, foreignKey.ForeignColumnName);
+
+                        sb.Append(stmt);
+                        if (i < ts.ForeignKeys.Count - 1)
+                            sb.Append(",\n");
+                    } // for
+                }
+            }
+
+            sb.Append("\n");
+            sb.Append(");\n");
+
+            // Create any relevant indexes
+            if (GenerateIndexes)
+            {
+                if (ts.Indexes != null)
+                {
+                    for (int i = 0; i < ts.Indexes.Count; i++)
+                    {
+                        string stmt = BuildCreateIndex(ts.TableName, ts.Indexes[i]);
+                        sb.Append(stmt + ";\n");
+                    } // for
+                } // if
+            }
+
+            string query = sb.ToString();
+            return query;
+        }
+
+        /// <summary>
+        /// Creates a CREATE INDEX DDL for the specified table and index schema.
+        /// </summary>
+        /// <param name="tableName">The name of the indexed table.</param>
+        /// <param name="indexSchema">The schema of the index object</param>
+        /// <returns>A CREATE INDEX DDL (SQLite format).</returns>
+        private string BuildCreateIndex(string tableName, IndexSchema indexSchema)
+        {
+            StringBuilder sb = new StringBuilder();
+            sb.Append("CREATE ");
+            if (indexSchema.IsUnique)
+                sb.Append("UNIQUE ");
+            sb.Append("INDEX [" + tableName + "_" + indexSchema.IndexName + "]\n");
+            sb.Append("ON [" + tableName + "]\n");
+            sb.Append("(");
+            for (int i = 0; i < indexSchema.Columns.Count; i++)
+            {
+                sb.Append("[" + indexSchema.Columns[i].ColumnName + "]");
+                if (!indexSchema.Columns[i].IsAscending)
+                    sb.Append(" DESC");
+                if (i < indexSchema.Columns.Count - 1)
+                    sb.Append(", ");
+            } // for
+            sb.Append(")");
+
+            return sb.ToString();
+        }
+
+        /// <summary>
+        /// Used when creating the CREATE TABLE DDL. Creates a single row
+        /// for the specified column.
+        /// </summary>
+        /// <param name="col">The column schema</param>
+        /// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
+        private string BuildColumnStatement(ColumnSchema col, TableSchema ts, ref bool pkey)
+        {
+            StringBuilder sb = new StringBuilder();
+            sb.Append("\t[" + col.ColumnName + "]\t");
+
+            // Special treatment for IDENTITY columns
+            if (col.IsIdentity)
+            {
+                if (ts.PrimaryKey.Count == 1 && (col.ColumnType == "tinyint" || col.ColumnType == "int" || col.ColumnType == "smallint" ||
+                    col.ColumnType == "bigint" || col.ColumnType == "integer"))
+                {
+                    sb.Append("integer PRIMARY KEY AUTOINCREMENT");
+                    pkey = true;
+                }
+                else
+                    sb.Append("integer");
+            }
+            else
+            {
+                if (col.ColumnType == "int")
+                    sb.Append("integer");
+                else
+                {
+                    sb.Append(col.ColumnType);
+                }
+                if (col.Length > 0)
+                    sb.Append("(" + col.Length + ")");
+            }
+            if (!col.IsNullable)
+                sb.Append(" NOT NULL");
+
+            if (col.IsCaseSensitivite.HasValue && !col.IsCaseSensitivite.Value)
+                sb.Append(" COLLATE NOCASE");
+
+            string defval = StripParens(col.DefaultValue);
+            defval = DiscardNational(defval);
+            LogManager.Log("DEFAULT VALUE BEFORE [" + col.DefaultValue + "] AFTER [" + defval + "]");
+            if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
+            {
+                LogManager.Log("converted SQL Server GETDATE() to CURRENT_TIMESTAMP for column [" + col.ColumnName + "]");
+                sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");
+            }
+            else if (defval != string.Empty && IsValidDefaultValue(defval))
+                sb.Append(" DEFAULT " + defval);
+
+            return sb.ToString();
+        }
+
+        /// <summary>
+        /// Discards the national prefix if exists (e.g., N'sometext') which is not
+        /// supported in SQLite.
+        /// </summary>
+        /// <param name="value">The value.</param>
+        /// <returns></returns>
+        private string DiscardNational(string value)
+        {
+            Regex rx = new Regex(@"N\'([^\']*)\'");
+            Match m = rx.Match(value);
+            if (m.Success)
+                return m.Groups[1].Value;
+            else
+                return value;
+        }
+
+        /// <summary>
+        /// Check if the DEFAULT clause is valid by SQLite standards
+        /// </summary>
+        /// <param name="value"></param>
+        /// <returns></returns>
+        private bool IsValidDefaultValue(string value)
+        {
+            if (IsSingleQuoted(value))
+                return true;
+
+            double testnum;
+            if (!double.TryParse(value, out testnum))
+                return false;
+            return true;
+        }
+
+        private bool IsSingleQuoted(string value)
+        {
+            value = value.Trim();
+            if (value.StartsWith("'") && value.EndsWith("'"))
+                return true;
+            return false;
+        }
+
+        /// <summary>
+        /// Strip any parentheses from the string.
+        /// </summary>
+        /// <param name="value">The string to strip</param>
+        /// <returns>The stripped string</returns>
+        private string StripParens(string value)
+        {
+            Regex rx = new Regex(@"\(([^\)]*)\)");
+            Match m = rx.Match(value);
+            if (!m.Success)
+                return value;
+            else
+                return StripParens(m.Groups[1].Value);
+        }
+
+        /// <summary>
+        /// Reads the entire SQL Server DB schema using the specified connection string.
+        /// </summary>
+        /// <param name="connString">The connection string used for reading SQL Server schema.</param>
+        /// <param name="handler">A handler for progress notifications.</param>
+        /// <param name="selectionHandler">The selection handler which allows the user to select 
+        /// which tables to convert.</param>
+        /// <returns>database schema objects for every table/view in the SQL Server database.</returns>
+        private DatabaseSchema ReadSqlServerSchema(string connString, SqlConversionHandler handler,
+            SqlTableSelectionHandler selectionHandler)
+        {
+            // First step is to read the names of all tables in the database
+            List<TableSchema> tables = new List<TableSchema>();
+            using (SqlConnection conn = new SqlConnection(connString))
+            {
+                conn.Open();
+
+                List<string> tableNames = new List<string>();
+                List<string> tblschema = new List<string>();
+
+                // This command will read the names of all tables in the database
+                SqlCommand cmd = new SqlCommand(@"select * from INFORMATION_SCHEMA.TABLES  where TABLE_TYPE = 'BASE TABLE'", conn);
+                using (SqlDataReader reader = cmd.ExecuteReader())
+                {
+                    while (reader.Read())
+                    {
+                        if (reader["TABLE_NAME"] == DBNull.Value)
+                            continue;
+                        if (reader["TABLE_SCHEMA"] == DBNull.Value)
+                            continue;
+                        tableNames.Add((string)reader["TABLE_NAME"]);
+                        tblschema.Add((string)reader["TABLE_SCHEMA"]);
+                    } // while
+                } // using
+
+                // Next step is to use ADO APIs to query the schema of each table.
+                int count = 0;
+                for (int i = 0; i < tableNames.Count; i++)
+                {
+                    string tname = tableNames[i];
+                    string tschma = tblschema[i];
+                    TableSchema ts = CreateTableSchema(conn, tname, tschma);
+                    CreateForeignKeySchema(conn, ts);
+                    tables.Add(ts);
+                    count++;
+                    CheckCancelled();
+                    handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);
+
+                    LogManager.Log("parsed table schema for [" + tname + "]");
+                } // foreach
+            } // using
+
+            LogManager.Log("finished parsing all tables in SQL Server schema");
+
+            // Allow the user a chance to select which tables to convert
+            if (selectionHandler != null)
+            {
+                List<TableSchema> updated = selectionHandler(tables);
+                if (updated != null)
+                    tables = updated;
+            } // if
+
+            Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
+
+            // Continue and read all of the views in the database
+            List<ViewSchema> views = new List<ViewSchema>();
+            using (SqlConnection conn = new SqlConnection(connString))
+            {
+                conn.Open();
+
+                SqlCommand cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION  from INFORMATION_SCHEMA.VIEWS", conn);
+                using (SqlDataReader reader = cmd.ExecuteReader())
+                {
+                    int count = 0;
+                    while (reader.Read())
+                    {
+                        ViewSchema vs = new ViewSchema();
+
+                        if (reader["TABLE_NAME"] == DBNull.Value)
+                            continue;
+                        if (reader["VIEW_DEFINITION"] == DBNull.Value)
+                            continue;
+                        vs.ViewName = (string)reader["TABLE_NAME"];
+                        vs.ViewSQL = (string)reader["VIEW_DEFINITION"];
+
+                        // Remove all ".dbo" strings from the view definition
+                        vs.ViewSQL = removedbo.Replace(vs.ViewSQL, string.Empty);
+
+                        views.Add(vs);
+
+                        count++;
+                        CheckCancelled();
+                        handler(false, true, 50 + (int)(count * 50.0 / views.Count), "Parsed view " + vs.ViewName);
+
+                        LogManager.Log("parsed view schema for [" + vs.ViewName + "]");
+                    } // while
+                } // using
+
+            } // using
+
+            DatabaseSchema ds = new DatabaseSchema();
+            ds.Tables = tables;
+            ds.Views = views;
+            return ds;
+        }
+
+        /// <summary>
+        /// Convenience method for checking if the conversion progress needs to be cancelled.
+        /// </summary>
+        private void CheckCancelled()
+        {
+            if (_cancelled)
+                throw new ApplicationException("User cancelled the conversion");
+        }
+
+        /// <summary>
+        /// Creates a TableSchema object using the specified SQL Server connection
+        /// and the name of the table for which we need to create the schema.
+        /// </summary>
+        /// <param name="conn">The SQL Server connection to use</param>
+        /// <param name="tableName">The name of the table for which we wants to create the table schema.</param>
+        /// <returns>A table schema object that represents our knowledge of the table schema</returns>
+        private TableSchema CreateTableSchema(SqlConnection conn, string tableName, string tschma)
+        {
+            TableSchema res = new TableSchema();
+            res.TableName = tableName;
+            res.TableSchemaName = tschma;
+            res.Columns = new List<ColumnSchema>();
+            SqlCommand cmd = new SqlCommand(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, " +
+                @" (columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT], " +
+                @"CHARACTER_MAXIMUM_LENGTH AS CSIZE " +
+                "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY " +
+                "ORDINAL_POSITION ASC", conn);
+            using (SqlDataReader reader = cmd.ExecuteReader())
+            {
+                while (reader.Read())
+                {
+                    object tmp = reader["COLUMN_NAME"];
+                    if (tmp is DBNull)
+                        continue;
+                    string colName = (string)reader["COLUMN_NAME"];
+
+                    tmp = reader["COLUMN_DEFAULT"];
+                    string colDefault;
+                    if (tmp is DBNull)
+                        colDefault = string.Empty;
+                    else
+                        colDefault = (string)tmp;
+
+                    tmp = reader["IS_NULLABLE"];
+                    bool isNullable = ((string)tmp == "YES");
+                    string dataType = (string)reader["DATA_TYPE"];
+                    bool isIdentity = false;
+                    if (reader["IDENT"] != DBNull.Value)
+                        isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
+                    int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;
+
+                    ValidateDataType(dataType);
+
+                    // Note that not all data type names need to be converted because
+                    // SQLite establishes type affinity by searching certain strings
+                    // in the type name. For example - everything containing the string
+                    // 'int' in its type name will be assigned an INTEGER affinity
+                    if (dataType == "timestamp")
+                        dataType = "blob";
+                    else if (dataType == "datetime" || dataType == "smalldatetime" || dataType == "date" || dataType == "datetime2" || dataType == "time")
+                        dataType = "datetime";
+                    else if (dataType == "decimal")
+                        dataType = "numeric";
+                    else if (dataType == "money" || dataType == "smallmoney")
+                        dataType = "numeric";
+                    else if (dataType == "binary" || dataType == "varbinary" ||
+                        dataType == "image")
+                        dataType = "blob";
+                    else if (dataType == "tinyint")
+                        dataType = "smallint";
+                    else if (dataType == "bigint")
+                        dataType = "integer";
+                    else if (dataType == "sql_variant")
+                        dataType = "blob";
+                    else if (dataType == "xml")
+                        dataType = "varchar";
+                    else if (dataType == "uniqueidentifier")
+                        dataType = "guid";
+                    else if (dataType == "ntext")
+                        dataType = "text";
+                    else if (dataType == "nchar")
+                        dataType = "char";
+
+                    if (dataType == "bit" || dataType == "int")
+                    {
+                        if (colDefault == "('False')")
+                            colDefault = "(0)";
+                        else if (colDefault == "('True')")
+                            colDefault = "(1)";
+                    }
+
+                    colDefault = FixDefaultValueString(colDefault);
+
+                    ColumnSchema col = new ColumnSchema();
+                    col.ColumnName = colName;
+                    col.ColumnType = dataType;
+                    col.Length = length;
+                    col.IsNullable = isNullable;
+                    col.IsIdentity = isIdentity;
+                    col.DefaultValue = AdjustDefaultValue(colDefault);
+                    res.Columns.Add(col);
+                } // while
+            } // using
+
+            // Find PRIMARY KEY information
+            SqlCommand cmd2 = new SqlCommand(@"EXEC sp_pkeys '" + tableName + "'", conn);
+            using (SqlDataReader reader = cmd2.ExecuteReader())
+            {
+                res.PrimaryKey = new List<string>();
+                while (reader.Read())
+                {
+                    string colName = (string)reader["COLUMN_NAME"];
+                    res.PrimaryKey.Add(colName);
+                } // while
+            } // using
+
+            // Find COLLATE information for all columns in the table
+            SqlCommand cmd4 = new SqlCommand(
+                @"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", conn);
+            using (SqlDataReader reader = cmd4.ExecuteReader())
+            {
+                while (reader.Read())
+                {
+                    bool? isCaseSensitive = null;
+                    string colName = (string)reader["name"];
+                    if (reader["tds_collation"] != DBNull.Value)
+                    {
+                        byte[] mask = (byte[])reader["tds_collation"];
+                        if ((mask[2] & 0x10) != 0)
+                            isCaseSensitive = false;
+                        else
+                            isCaseSensitive = true;
+                    } // if
+
+                    if (isCaseSensitive.HasValue)
+                    {
+                        // Update the corresponding column schema.
+                        foreach (ColumnSchema csc in res.Columns)
+                        {
+                            if (csc.ColumnName == colName)
+                            {
+                                csc.IsCaseSensitivite = isCaseSensitive;
+                                break;
+                            }
+                        } // foreach
+                    } // if
+                } // while
+            } // using
+
+            try
+            {
+                // Find index information
+                SqlCommand cmd3 = new SqlCommand(
+                    @"exec sp_helpindex '" + tschma + "." + tableName + "'", conn);
+                using (SqlDataReader reader = cmd3.ExecuteReader())
+                {
+                    res.Indexes = new List<IndexSchema>();
+                    while (reader.Read())
+                    {
+                        string indexName = (string)reader["index_name"];
+                        string desc = (string)reader["index_description"];
+                        string keys = (string)reader["index_keys"];
+
+                        // Don't add the index if it is actually a primary key index
+                        if (desc.Contains("primary key"))
+                            continue;
+
+                        IndexSchema index = BuildIndexSchema(indexName, desc, keys);
+                        res.Indexes.Add(index);
+                    } // while
+                } // using
+            }
+            catch (Exception ex)
+            {
+                LogManager.Log("failed to read index information for table [" + tableName + "]");
+            } // catch
+
+            return res;
+        }
+
+        /// <summary>
+        /// Small validation method to make sure we don't miss anything without getting
+        /// an exception.
+        /// </summary>
+        /// <param name="dataType">The datatype to validate.</param>
+        private void ValidateDataType(string dataType)
+        {
+            if (dataType == "int" || dataType == "smallint" ||
+                dataType == "bit" || dataType == "float" ||
+                dataType == "real" || dataType == "nvarchar" ||
+                dataType == "varchar" || dataType == "timestamp" ||
+                dataType == "varbinary" || dataType == "image" ||
+                dataType == "text" || dataType == "ntext" ||
+                dataType == "bigint" ||
+                dataType == "char" || dataType == "numeric" ||
+                dataType == "binary" || dataType == "smalldatetime" ||
+                dataType == "smallmoney" || dataType == "money" ||
+                dataType == "tinyint" || dataType == "uniqueidentifier" ||
+                dataType == "xml" || dataType == "sql_variant" || dataType == "datetime2" || dataType == "date" || dataType == "time" ||
+                dataType == "decimal" || dataType == "nchar" || dataType == "datetime")
+                return;
+            throw new ApplicationException("Validation failed for data type [" + dataType + "]");
+        }
+
+        /// <summary>
+        /// Does some necessary adjustments to a value string that appears in a column DEFAULT
+        /// clause.
+        /// </summary>
+        /// <param name="colDefault">The original default value string (as read from SQL Server).</param>
+        /// <returns>Adjusted DEFAULT value string (for SQLite)</returns>
+        private string FixDefaultValueString(string colDefault)
+        {
+            bool replaced = false;
+            string res = colDefault.Trim();
+
+            // Find first/last indexes in which to search
+            int first = -1;
+            int last = -1;
+            for (int i = 0; i < res.Length; i++)
+            {
+                if (res[i] == '\'' && first == -1)
+                    first = i;
+                if (res[i] == '\'' && first != -1 && i > last)
+                    last = i;
+            } // for
+
+            if (first != -1 && last > first)
+                return res.Substring(first, last - first + 1);
+
+            StringBuilder sb = new StringBuilder();
+            for (int i = 0; i < res.Length; i++)
+            {
+                if (res[i] != '(' && res[i] != ')')
+                {
+                    sb.Append(res[i]);
+                    replaced = true;
+                }
+            }
+            if (replaced)
+                return "(" + sb.ToString() + ")";
+            else
+                return sb.ToString();
+        }
+
+
+
+        /// <summary>
+        /// Add foreign key schema object from the specified components (Read from SQL Server).
+        /// </summary>
+        /// <param name="conn">The SQL Server connection to use</param>
+        /// <param name="ts">The table schema to whom foreign key schema should be added to</param>
+        private void CreateForeignKeySchema(SqlConnection conn, TableSchema ts)
+        {
+            ts.ForeignKeys = new List<ForeignKeySchema>();
+
+            SqlCommand cmd = new SqlCommand(
+                @"SELECT " +
+                @"  ColumnName = CU.COLUMN_NAME, " +
+                @"  ForeignTableName  = PK.TABLE_NAME, " +
+                @"  ForeignColumnName = PT.COLUMN_NAME, " +
+                @"  DeleteRule = C.DELETE_RULE, " +
+                @"  IsNullable = COL.IS_NULLABLE " +
+                @"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
+                @"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
+                @"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
+                @"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
+                @"INNER JOIN " +
+                @"  ( " +
+                @"    SELECT i1.TABLE_NAME, i2.COLUMN_NAME " +
+                @"    FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 " +
+                @"    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME " +
+                @"    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
+                @"  ) " +
+                @"PT ON PT.TABLE_NAME = PK.TABLE_NAME " +
+                @"INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COL ON CU.COLUMN_NAME = COL.COLUMN_NAME AND FK.TABLE_NAME = COL.TABLE_NAME " +
+                @"WHERE FK.Table_NAME='" + ts.TableName + "'", conn);
+
+            using (SqlDataReader reader = cmd.ExecuteReader())
+            {
+                while (reader.Read())
+                {
+                    ForeignKeySchema fkc = new ForeignKeySchema();
+                    fkc.ColumnName = (string)reader["ColumnName"];
+                    fkc.ForeignTableName = (string)reader["ForeignTableName"];
+                    fkc.ForeignColumnName = (string)reader["ForeignColumnName"];
+                    fkc.CascadeOnDelete = (string)reader["DeleteRule"] == "CASCADE";
+                    fkc.IsNullable = (string)reader["IsNullable"] == "YES";
+                    fkc.TableName = ts.TableName;
+                    ts.ForeignKeys.Add(fkc);
+                }
+            }
+        }
+
+        /// <summary>
+        /// Builds an index schema object from the specified components (Read from SQL Server).
+        /// </summary>
+        /// <param name="indexName">The name of the index</param>
+        /// <param name="desc">The description of the index</param>
+        /// <param name="keys">Key columns that are part of the index.</param>
+        /// <returns>An index schema object that represents our knowledge of the index</returns>
+        private IndexSchema BuildIndexSchema(string indexName, string desc, string keys)
+        {
+            IndexSchema res = new IndexSchema();
+            res.IndexName = indexName;
+
+            // Determine if this is a unique index or not.
+            string[] descParts = desc.Split(',');
+            foreach (string p in descParts)
+            {
+                if (p.Trim().Contains("unique"))
+                {
+                    res.IsUnique = true;
+                    break;
+                }
+            } // foreach
+
+            // Get all key names and check if they are ASCENDING or DESCENDING
+            res.Columns = new List<IndexColumn>();
+            string[] keysParts = keys.Split(',');
+            foreach (string p in keysParts)
+            {
+                Match m = _keyRx.Match(p.Trim());
+                if (!m.Success)
+                {
+                    throw new ApplicationException("Illegal key name [" + p + "] in index [" +
+                        indexName + "]");
+                }
+
+                string key = m.Groups[1].Value;
+                IndexColumn ic = new IndexColumn();
+                ic.ColumnName = key;
+                if (m.Groups[2].Success)
+                    ic.IsAscending = false;
+                else
+                    ic.IsAscending = true;
+
+                res.Columns.Add(ic);
+            } // foreach
+
+            return res;
+        }
+
+        /// <summary>
+        /// More adjustments for the DEFAULT value clause.
+        /// </summary>
+        /// <param name="val">The value to adjust</param>
+        /// <returns>Adjusted DEFAULT value string</returns>
+        private string AdjustDefaultValue(string val)
+        {
+            if (val == null || val == string.Empty)
+                return val;
+
+            Match m = _defaultValueRx.Match(val);
+            if (m.Success)
+                return m.Groups[1].Value;
+            return val;
+        }
+
+        /// <summary>
+        /// Creates SQLite connection string from the specified DB file path.
+        /// </summary>
+        /// <param name="sqlitePath">The path to the SQLite database file.</param>
+        /// <returns>SQLite connection string</returns>
+        private string CreateSQLiteConnectionString(string sqlitePath, string password)
+        {
+            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
+            builder.DataSource = sqlitePath;
+            if (password != null)
+                builder.Password = password;
+            builder.PageSize = 4096;
+            builder.UseUTF16Encoding = true;
+            string connstring = builder.ConnectionString;
+
+            return connstring;
+        }
+        #endregion
+
+        #region Trigger related
+        private void AddTriggersForForeignKeys(string sqlitePath, IEnumerable<TableSchema> schema,
+            string password, SqlConversionHandler handler)
+        {
+            // Connect to the newly created database
+            string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
+            using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
+            {
+                conn.Open();
+                // foreach
+                foreach (TableSchema dt in schema)
+                {
+                    try
+                    {
+                        AddTableTriggers(conn, dt);
+                    }
+                    catch (Exception ex)
+                    {
+                        LogManager.Log(ex, "AddTableTriggers failed");
+                        throw;
+                    }
+                }
+
+            } // using
+
+            LogManager.Log("finished adding triggers to schema");
+        }
+
+        private void AddTableTriggers(SQLiteConnection conn, TableSchema dt)
+        {
+            IList<TriggerSchema> triggers = TriggerBuilder.GetForeignKeyTriggers(dt);
+            foreach (TriggerSchema trigger in triggers)
+            {
+                SQLiteCommand cmd = new SQLiteCommand(WriteTriggerSchema(trigger), conn);
+                cmd.ExecuteNonQuery();
+            }
+        }
+        #endregion
+
+        /// <summary>
+        /// Gets a create script for the triggerSchema in sqlite syntax
+        /// </summary>
+        /// <param name="ts">Trigger to script</param>
+        /// <returns>Executable script</returns>
+        public string WriteTriggerSchema(TriggerSchema ts)
+        {
+            return @"CREATE TRIGGER [" + ts.Name + "] " +
+                   ts.Type + " " + ts.Event +
+                   " ON [" + ts.Table + "] " +
+                   "BEGIN " + ts.Body + " END;";
+        }
+
+        #region Private Variables
+        private bool _isActive = false;
+        private bool _cancelled = false;
+        private Regex _keyRx = new Regex(@"(([a-zA-Z_äöüÄÖÜß0-9\.]|(\s+))+)(\(\-\))?");
+        private Regex _defaultValueRx = new Regex(@"\(N(\'.*\')\)");
+        #endregion
+    }
+
+    /// <summary>
+    /// This handler is called whenever a progress is made in the conversion process.
+    /// </summary>
+    /// <param name="done">TRUE indicates that the entire conversion process is finished.</param>
+    /// <param name="success">TRUE indicates that the current step finished successfully.</param>
+    /// <param name="percent">Progress percent (0-100)</param>
+    /// <param name="msg">A message that accompanies the progress.</param>
+    public delegate void SqlConversionHandler(bool done, bool success, int percent, string msg);
+
+    /// <summary>
+    /// This handler allows the user to change which tables get converted from SQL Server
+    /// to SQLite.
+    /// </summary>
+    /// <param name="schema">The original SQL Server DB schema</param>
+    /// <returns>The same schema minus any table we don't want to convert.</returns>
+    public delegate List<TableSchema> SqlTableSelectionHandler(List<TableSchema> schema);
+
+    /// <summary>
+    /// This handler is called in order to handle the case when copying the SQL Server view SQL
+    /// statement is not enough and the user needs to either update the view definition himself
+    /// or discard the view definition from the generated SQLite database.
+    /// </summary>
+    /// <param name="vs">The problematic view definition</param>
+    /// <returns>The updated view definition, or NULL in case the view should be discarded</returns>
+    public delegate string FailedViewDefinitionHandler(ViewSchema vs);
+}