SQLite Compare Utility

using System;
using System.Collections.Generic;
using System.Text;
using SQLiteParser;

namespace SQLiteTurbo
{
    /// <summary>
    /// This class is responsible to generate change scripts that can be 
    /// used to migrate from one version of a database to another.
    /// </summary>
    public class ChangeScriptBuilder
    {
        #region Constructors
        #endregion

        #region Public Methods
        /// <summary>
        /// Generate a change script needed to migrate from the left database to the right database object
        /// </summary>
        /// <returns></returns>
        public static string Generate(
            string leftdb,
            string rightdb,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> leftSchema,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> rightSchema,
            Dictionary<SchemaObject, List<SchemaComparisonItem>> comp, ChangeDirection direction)
        {
            StringBuilder sb = new StringBuilder();

            // Do everything within a transaction
            sb.Append("-- Generated by SQLite Compare utility\r\n\r\n");
            if (direction == ChangeDirection.LeftToRight)
                sb.Append("-- The script can be used to migrate database\r\n-- " + leftdb + " schema\r\n-- to the schema of database\r\n-- " + rightdb + "\r\n\r\n");
            else
                sb.Append("-- The script can be used to migrate database\r\n-- " + rightdb + " schema\r\n-- to the schema of database\r\n-- " + leftdb + "\r\n\r\n");
            sb.Append("BEGIN TRANSACTION;\r\n\r\n");

            // There are 4 different types of objects that can be migrated:
            // 1. Tables
            // 2. Indexes
            // 3. Triggers
            // 4. Views
            // Indexes depend on their tables so we can handle them only after handling the
            // respective tables. Triggers depend on the associated table or view. If a trigger
            // depends on a table - we can handle it only after handling the related table. If
            // a trigger depends on a view - we can handle it only after handling the view.
            // A view depends on one or more tables, but, unlike other objects - we can simply drop
            // it and re-create it from scratch without damaging any data or schema information.

            // We'll start by handling the hardest part first - tables
            List<SchemaComparisonItem> tables = comp[SchemaObject.Table];
            foreach (SchemaComparisonItem item in tables)
            {
                if (item.Result != ComparisonResult.Same)
                {
                    string script = GenerateTableChangeScript(item, comp, direction, leftSchema, rightSchema);
                    sb.Append(script);
                }
                else
                {
                    // The table itself did not change, but we still need to check its indexes and triggers
                    if (direction == ChangeDirection.LeftToRight)
                        ApplyIndexOrTriggerChanges(sb, item.LeftDdlStatement, leftSchema, rightSchema);
                    else
                        ApplyIndexOrTriggerChanges(sb, item.RightDdlStatement, rightSchema, leftSchema);
                } // else
            } // foreach

            // Note: as part of creating a change script for a table object - we'll also
            //       generate change script for its related indexes and triggers so they
            //       don't require a separate handling here.

            // We'll finish my handling views
            List<SchemaComparisonItem> views = comp[SchemaObject.View];
            foreach (SchemaComparisonItem item in views)
            {
                if (item.Result != ComparisonResult.Same)
                {
                    string script = GenerateViewChangeScript(item, direction);
                    sb.Append(script);
                }
                else
                {
                    // The view itself did not change, but we still need to check its triggers
                    if (direction == ChangeDirection.LeftToRight)
                        ApplyIndexOrTriggerChanges(sb, item.LeftDdlStatement, leftSchema, rightSchema);
                    else
                        ApplyIndexOrTriggerChanges(sb, item.RightDdlStatement, rightSchema, leftSchema);
                }
            } // foreachs

            // Commit the transaction
            sb.Append("\r\nCOMMIT TRANSACTION;\r\n");

            return sb.ToString();
        }
        #endregion

        #region Private Methods
        private static string GenerateViewChangeScript(SchemaComparisonItem item, 
            ChangeDirection direction)
        {
            StringBuilder sb = new StringBuilder();

            if (item.Result == ComparisonResult.ExistsInLeftDB)
            {
                if (direction == ChangeDirection.LeftToRight)
                    DeleteView(sb, item.LeftDdlStatement);
                else
                    CopyView(sb, item.LeftDdlStatement);
            }
            else if (item.Result == ComparisonResult.ExistsInRightDB)
            {
                if (direction == ChangeDirection.LeftToRight)
                    CopyView(sb, item.RightDdlStatement);
                else
                    DeleteView(sb, item.RightDdlStatement);
            }
            else if (item.Result == ComparisonResult.DifferentSchema)
            {
                if (direction == ChangeDirection.LeftToRight)
                    CopyView(sb, item.RightDdlStatement);
                else
                    CopyView(sb, item.LeftDdlStatement);
            } // else

            return sb.ToString();
        }

        private static void DeleteView(StringBuilder sb, SQLiteDdlStatement stmt)
        {
            sb.Append("\r\n-- Deleting view " + stmt.ObjectName.ToString() + " from the updated schema\r\n\r\n");
            sb.Append("DROP VIEW IF EXISTS " + stmt.ObjectName.ToString() + ";\r\n");
        }

        private static void CopyView(StringBuilder sb, SQLiteDdlStatement stmt)
        {
            sb.Append("\r\n-- Creating view " + stmt.ObjectName.ToString() + " from scratch\r\n\r\n");
            sb.Append("DROP VIEW IF EXISTS " + stmt.ObjectName.ToString() + ";\r\n");
            sb.Append(stmt.ToString() + ";\r\n");
        }

        private static string GenerateTableChangeScript(SchemaComparisonItem item,
            Dictionary<SchemaObject, List<SchemaComparisonItem>> comp, ChangeDirection direction,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> leftSchema,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> rightSchema)
        {
            StringBuilder sb = new StringBuilder();

            if (item.Result == ComparisonResult.ExistsInLeftDB)
            {
                if (direction == ChangeDirection.LeftToRight)
                    DeleteTable(sb, item.LeftDdlStatement);
                else
                    CopyTable(sb, item.LeftDdlStatement, leftSchema);                    
            }
            else if (item.Result == ComparisonResult.ExistsInRightDB)
            {
                if (direction == ChangeDirection.RightToLeft)
                    DeleteTable(sb, item.RightDdlStatement);
                else
                    CopyTable(sb, item.RightDdlStatement, rightSchema);
            }
            else if (item.Result == ComparisonResult.DifferentSchema)
            {
                if (direction == ChangeDirection.LeftToRight)
                    MigrateTable(sb, item.RightDdlStatement, leftSchema, rightSchema);
                else
                    MigrateTable(sb, item.LeftDdlStatement, rightSchema, leftSchema);
            }

            return sb.ToString();
        }

        /// <summary>
        /// Create the SQL code that is necessary to migrate an existing table to its
        /// updated schema.
        /// </summary>
        /// <param name="sb">The string builder to which the code will be added</param>
        /// <param name="stmt">The updated table schema object</param>
        /// <param name="srcSchema">The source schema</param>
        /// <param name="dstSchema">The destination schema</param>
        private static void MigrateTable(StringBuilder sb, SQLiteDdlStatement stmt, 
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> srcSchema, 
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> dstSchema)
        {
            List<SQLiteColumnStatement> added = null;
            SQLiteDdlStatement orig = srcSchema[SchemaObject.Table][SQLiteParser.Utils.Chop(stmt.ObjectName.ToString()).ToLower()];

            if (Utils.AlterTableIsPossible(orig, stmt, ref added))
            {
                sb.Append("\r\n-- Adding missing table columns\r\n\r\n");

                // In this case we can migrate the table by doing ALTER TABLE ADD COLUMN commands
                foreach (SQLiteColumnStatement col in added)
                {
                    sb.Append("ALTER TABLE " + stmt.ObjectName.ToString() + " ADD COLUMN " + col.ToString() + ";\r\n");
                } // foreach

                // If there are any differences in indexes or triggers - apply them now
                ApplyIndexOrTriggerChanges(sb, stmt, srcSchema, dstSchema);
            }
            else
            {
                // In this case we need to re-build the table from scratch in order to "change" it.
                sb.Append("\r\n-- Creating table "+stmt.ObjectName.ToString()+" from scratch (simple ALTER TABLE is not enough)\r\n\r\n");

                // Create a table with the correct schema but with a temporary name 
                string tmpname = Utils.GetTempName(stmt.ObjectName.ToString());
                SQLiteCreateTableStatement updTable = (SQLiteCreateTableStatement)stmt;
                sb.Append(updTable.ToStatement(tmpname) + ";\r\n");

                // Get the original table schema object
                SQLiteCreateTableStatement origTable = (SQLiteCreateTableStatement)orig;

                // Compute the set of common columns to the updated table schema and the original
                // table schema
                List<SQLiteColumnStatement> diffcols = null;
                List<SQLiteColumnStatement> common = Utils.GetCommonColumns(origTable, updTable, false, out diffcols);

                // Copy data rows from the original table to the temporary table
                if (common.Count > 0)
                {
                    // Check if all the columns that belong solely to the updated table schema (and are not
                    // common with the original table schema) supports NULL values or have DEFAULT values.
                    bool error = false;
                    List<SQLiteColumnStatement> ncols = new List<SQLiteColumnStatement>();
                    foreach (SQLiteColumnStatement c in updTable.Columns)
                    {
                        if (!Utils.ColumnListContains(common, c))
                            ncols.Add(c);
                    } // foreach
                    foreach (SQLiteColumnStatement c in ncols)
                    {
                        if (c.IsNullable || c.HasNonNullConstDefault)
                            continue;

                        // This column will cause any attempt to insert data into the updated table
                        // schema to fail, so we'll issue a warning comment
                        error = true;
                        sb.Append("\r\n-- WARNING: Column " + c.ObjectName.ToString() + " in table " + updTable.ObjectName.ToString() + " is NOT NULL and doesn't have a non-null constant DEFAULT clause. " +
                                  "\r\n--          This will cause any attempt to copy rows from the original table to the updated table to fail." +
                                  "\r\n--          No rows will be copied from the original table to the updated table!");
                        sb.Append("\r\n");
                    } // foreach

                    // Build a select columns list                    
                    string selectlist = Utils.BuildColumnsString(common, false);

                    // Build a select column list for those columns that belong exclusively to the updated table
                    // schema and that are nullable or have non-null DEFAULT clause.
                    string extralist = string.Empty;
                    if (ncols.Count > 0)
                        extralist = "," + Utils.BuildNullableOrNonNullConstantDefaultSelectList(ncols);

                    // Compute the list of all columns that are common to both tables and those that exist only in the
                    // updated table but are nullable or have non-null constant default.                    
                    string allCols = null;
                    if (common.Count > 0)
                    {
                        if (ncols.Count > 0)
                            allCols = selectlist + "," + Utils.BuildColumnsString(ncols, false);
                        else
                            allCols = selectlist;
                    }
                    else
                        allCols = Utils.BuildColumnsString(ncols, false);

                    if (!error)
                    {
                        // Now copy only the columns that can be transferred from the original table
                        sb.Append("\r\n-- Copying rows from original table to the new table\r\n\r\n");
                        sb.Append("INSERT INTO " + tmpname + " ("+allCols+")"+
                            " SELECT " + selectlist + extralist+" FROM " + origTable.ObjectName.ToString() + ";\r\n");
                    } // if
                } // if

                // Drop the original table and rename the temporary table to have the name of the original table
                sb.Append("\r\n-- Droping the original table and renaming the temporary table\r\n\r\n");
                sb.Append("DROP TABLE " + origTable.ObjectName.ToString() + ";\r\n");
                sb.Append("ALTER TABLE " + tmpname + " RENAME TO " + origTable.ObjectName.ToString() + ";\r\n");                

                // Re-create all indexes of the updated table
                bool found = false;
                foreach (SQLiteCreateIndexStatement cindex in dstSchema[SchemaObject.Index].Values)
                {
                    if (SQLiteParser.Utils.Chop(cindex.OnTable).ToLower() ==
                        SQLiteParser.Utils.Chop(updTable.ObjectName.ToString()).ToLower())
                    {
                        if (!found)
                        {
                            sb.Append("\r\n-- Creating associated indexes from scratch\r\n\r\n");
                            found = true;
                        }

                        sb.Append(cindex.ToString() + ";\r\n");
                    }
                } // foreach

                // Re-create all triggers of the updated table
                found = false;
                foreach (SQLiteCreateTriggerStatement trg in dstSchema[SchemaObject.Trigger].Values)
                {
                    if (trg.TableName.Equals(updTable.ObjectName))
                    {
                        if (!found)
                        {
                            sb.Append("\r\n-- Creating associated triggers from scratch\r\n\r\n");
                            found = true;
                        }

                        sb.Append(trg.ToString() + ";\r\n");
                    }
                } // foreach
            } // else
        }

        private static void ApplyIndexOrTriggerChanges(StringBuilder sb, SQLiteDdlStatement stmt,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> srcSchema,
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> dstSchema)
        {
            // If there are any differences in indexes - apply them now
            List<SQLiteCreateIndexStatement> changedIndexes = null;
            List<SQLiteObjectName> removedIndexes = null;
            if (Utils.TableIndexesWereChanged(stmt.ObjectName.ToString(), srcSchema[SchemaObject.Index], dstSchema[SchemaObject.Index],
                out changedIndexes, out removedIndexes))
            {
                foreach (SQLiteObjectName iname in removedIndexes)
                    sb.Append("DROP INDEX IF EXISTS " + iname.ToString() + ";\r\n");

                foreach (SQLiteCreateIndexStatement idx in changedIndexes)
                {
                    sb.Append("DROP INDEX IF EXISTS " + idx.ObjectName.ToString() + ";\r\n");
                    sb.Append(idx.ToString() + ";\r\n");
                } // foreach
            }

            // If there are any differences in triggers - apply them now
            List<SQLiteCreateTriggerStatement> changedTriggers = null;
            List<SQLiteObjectName> removedTriggers = null;
            if (Utils.TableTriggersWereChanged(stmt.ObjectName.ToString(), srcSchema[SchemaObject.Trigger], dstSchema[SchemaObject.Trigger],
                out changedTriggers, out removedTriggers))
            {
                foreach (SQLiteObjectName iname in removedTriggers)
                    sb.Append("DROP TRIGGER IF EXISTS " + iname.ToString() + ";\r\n");

                foreach (SQLiteCreateTriggerStatement trg in changedTriggers)
                {
                    sb.Append("DROP TRIGGER IF EXISTS " + trg.ObjectName.ToString() + ";\r\n");
                    sb.Append(trg.ToString() + ";\r\n");
                } // foreach
            } // if
        }

        /// <summary>
        /// appends the SQL commands needed to delete a table from the target database
        /// </summary>
        /// <param name="sb">The string builder objcet</param>
        /// <param name="stmt">The table statement schema objcet</param>
        private static void DeleteTable(StringBuilder sb, SQLiteDdlStatement stmt)
        {
            string tableName = stmt.ObjectName.ToString();

            sb.Append("\r\nDROP TABLE " + tableName + ";\r\n");
        }

        /// <summary>
        /// Append the SQL commands needed to copy a table from a source database to
        /// the target database.
        /// </summary>
        /// <param name="sb">The string builder object</param>
        /// <param name="stmt">The CREATE TABLE schema object</param>
        /// <param name="srcSchema">The source schema from which the table is copied</param>
        private static void CopyTable(StringBuilder sb, SQLiteDdlStatement stmt, 
            Dictionary<SchemaObject, Dictionary<string, SQLiteDdlStatement>> srcSchema)
        {
            string tableName = stmt.ObjectName.ToString();

            // The table (and any associated triggers and/or indexes) does not exist in the
            // right database, so we need to create it from scratch.
            string create = stmt.ToString();
            sb.Append("\r\n"+create + ";\r\n");

            // Create any associated indexes
            Dictionary<string, SQLiteDdlStatement> indexes = srcSchema[SchemaObject.Index];
            foreach (SQLiteCreateIndexStatement cindex in indexes.Values)
            {
                if (SQLiteParser.Utils.Chop(cindex.OnTable).ToLower() ==
                    SQLiteParser.Utils.Chop(tableName).ToLower())
                {
                    sb.Append(cindex.ToString() + ";\r\n");
                }
            } // foreach

            // Now add CREATE for any triggers of this table
            Dictionary<string, SQLiteDdlStatement> triggers = srcSchema[SchemaObject.Trigger];
            foreach (SQLiteCreateTriggerStatement ctrig in triggers.Values)
            {
                if (SQLiteParser.Utils.Chop(ctrig.TableName.ToString()).ToLower() ==
                    SQLiteParser.Utils.Chop(tableName).ToLower())
                {
                    sb.Append(ctrig.ToString() + ";\r\n");
                }
            } // foreach
        }
        #endregion
    }

    public enum ChangeDirection
    {
        None = 0,

        LeftToRight = 1,

        RightToLeft = 2,
    }
}