/// <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
}
