/// <summary> /// Create a new comparison item with the name of the compared DB object, its schema /// object type, and the result of the comparison process. /// </summary> /// <param name="name">The name.</param> /// <param name="left">The left.</param> /// <param name="right">The right.</param> /// <param name="res">The result of the comparison process.</param> public SchemaComparisonItem(string name, SQLiteDdlStatement left, SQLiteDdlStatement right, ComparisonResult res) { _name = SQLiteParser.Utils.Chop(name); _left = left; _right = right; _result = res; }
/// <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 }
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> /// Parse the schema information in the specified DB file and return the information /// in a form of a dictionary that provides, for every type of database object (table, index, /// trigger and view) - a dictionary of DDL statements that are keyed by the names of these /// objects. /// </summary> /// <param name="fpath">The path to the SQLite database file</param> /// <returns>The schema information for the specified file.</returns> private Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > ParseDB(string fpath) { Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > res = new Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> >(); res.Add(SchemaObject.Table, new Dictionary <string, SQLiteDdlStatement>()); res.Add(SchemaObject.View, new Dictionary <string, SQLiteDdlStatement>()); res.Add(SchemaObject.Trigger, new Dictionary <string, SQLiteDdlStatement>()); res.Add(SchemaObject.Index, new Dictionary <string, SQLiteDdlStatement>()); SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder(); sb.DataSource = fpath; sb.ReadOnly = true; using (SQLiteConnection conn = new SQLiteConnection(sb.ConnectionString)) { conn.Open(); SQLiteCommand queryCount = new SQLiteCommand( @"SELECT COUNT(*) FROM sqlite_master WHERE sql IS NOT NULL", conn); long count = (long)queryCount.ExecuteScalar(); int index = 0; SQLiteCommand query = new SQLiteCommand( @"SELECT * FROM sqlite_master WHERE sql IS NOT NULL", conn); using (SQLiteDataReader reader = query.ExecuteReader()) { while (reader.Read()) { string sql = (string)reader["sql"]; sql = Utils.StripComments(sql); _scanner.SetSource(sql, 0); // Request the parser to parse the SQL statement bool ok = _parser.Parse(); if (!ok) { throw new ApplicationException("invalid sql string"); } SQLiteDdlStatement stmt = SQLiteDdlMain.GetStatement(); if (stmt is SQLiteCreateTableStatement) { res[SchemaObject.Table].Add(SQLiteParser.Utils.Chop(stmt.ObjectName.ToString()).ToLower(), stmt); } else if (stmt is SQLiteCreateIndexStatement) { res[SchemaObject.Index].Add(SQLiteParser.Utils.Chop(stmt.ObjectName.ToString()).ToLower(), stmt); } else if (stmt is SQLiteCreateViewStatement) { res[SchemaObject.View].Add(SQLiteParser.Utils.Chop(stmt.ObjectName.ToString()).ToLower(), stmt); } else if (stmt is SQLiteCreateTriggerStatement) { res[SchemaObject.Trigger].Add(SQLiteParser.Utils.Chop(stmt.ObjectName.ToString()).ToLower(), stmt); } else { throw new ApplicationException("illegal ddl statement type [" + stmt.GetType().FullName + "]"); } double progress = (100.0 * index++) / count; NotifySecondaryProgress(false, (int)progress, "Parsed object " + stmt.ObjectName.ToString()); if (_cancelled) { throw new UserCancellationException(); } } // while } // using } // using NotifySecondaryProgress(true, 100, "Finished parsing DB " + fpath); return(res); }
/// <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> /// 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>(); List <SQLiteColumnStatement> renamed = 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 // There is however, one exception to this rule. If a column was renamed, it would be possible to map // the old values to the new column -> This is however very experimental, as we have no knowledge if // this was really a rename operation.. Try to detect this.. // => Check if column position of column in updated table is = column position of a deleted column in orig int newIdx = updTable.Columns.IndexOf(c); string oldColumnName = ""; if (origTable.Columns.Count > newIdx) { var columnOld = origTable.Columns[newIdx]; oldColumnName = columnOld.ObjectName.ToString(); if (!common.Contains(columnOld)) { //First indicator! There may be a new column and a deleted old column.. Check datatype + Nullable/Const default if (columnOld.ColumnType.Equals(c.ColumnType)) { //Make sure non-nullable are also non-null in original column if (!c.IsNullable && (columnOld.IsNullable && !c.HasNonNullConstDefault)) { error = true; } } else { error = true; } } else { error = true; } } else { error = true; } if (error) { 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"); } else { 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-- However, a possible rename operation was detected, from " + oldColumnName + " to " + c.ObjectName.ToString() + "\r\n-- Before executing this statement, please verify the logic first!"); sb.Append("\r\n"); //Add the old colum to the common list, so that it's added in the select renamed.Add(origTable.Columns[newIdx]); } } // 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) { var clause = Utils.BuildNullableOrNonNullConstantDefaultSelectList(ncols); var clause2 = Utils.BuildColumnsString(renamed, false); if (!String.IsNullOrWhiteSpace(clause2)) { extralist = "," + clause2; } if (!String.IsNullOrWhiteSpace(clause)) { extralist = "," + clause; } } // 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 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 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"); }
/// <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 }