private void ReplaceTable(Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > fromSchema,
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > toSchema,
string name, SQLiteConnection from, SQLiteConnection to, SQLiteCreateTableStatement table)
{
long size = 0;
long count = 0;
SQLiteCommand cmd = null;
SQLiteTransaction tx = to.BeginTransaction();
if (table == null)
{
// In this case we need to delete the table in the destination database
try
{
NotifyPrimaryProgress(false, 50, "Deleting table " + name);
cmd = new SQLiteCommand("DROP TABLE " + name, to, tx);
cmd.ExecuteNonQuery();
tx.Commit();
return;
}
catch (Exception ex)
{
tx.Rollback();
throw;
} // catch
}
bool needTemporaryTable = false;
string tableName = table.ObjectName.ToString();
string tmpName = Utils.GetTempName(tableName);
try
{
// If the table does not exist in the target database - don't createt a temporary table
// - instead create the target table immediatly.
cmd = new SQLiteCommand("SELECT count(*) from sqlite_master where type = 'table' and name = '" +
SQLiteParser.Utils.Chop(table.ObjectName.ToString()) + "'", to, tx);
count = (long)cmd.ExecuteScalar();
if (count > 0)
{
// The table already exists in the target database, so we need to first copy the
// source table to a temporary table.
NotifyPrimaryProgress(false, 20, "Creating temporary table ..");
cmd = new SQLiteCommand(table.ToStatement(tmpName), to, tx);
cmd.ExecuteNonQuery();
tableName = tmpName;
needTemporaryTable = true;
}
else
{
// The table does not exist in the target database, so we can copy the source table
// directly to the target database.
NotifyPrimaryProgress(false, 20, "Creating table ..");
cmd = new SQLiteCommand(table.ToString(), to, tx);
cmd.ExecuteNonQuery();
needTemporaryTable = false;
}
NotifyPrimaryProgress(false, 25, "Computing table size ..");
cmd = new SQLiteCommand("SELECT COUNT(*) FROM " + table.ObjectName.ToString(), from);
size = (long)cmd.ExecuteScalar();
if (_cancelled)
{
throw new UserCancellationException();
}
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
throw;
} // catch
try
{
tx = to.BeginTransaction();
NotifyPrimaryProgress(false, 30, "Copying table rows ..");
SQLiteCommand insert = BuildInsertCommand(table, tableName, to, tx);
cmd = new SQLiteCommand(@"SELECT * FROM " + table.ObjectName, from);
int prev = 0;
int curr = 0;
count = 0;
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UpdateInsertCommandFields(table, insert, reader);
insert.ExecuteNonQuery();
count++;
if (count % 1000 == 0)
{
tx.Commit();
tx = to.BeginTransaction();
curr = (int)(40.0 * count / size + 30);
if (curr > prev)
{
prev = curr;
NotifyPrimaryProgress(false, curr, "" + count + " rows copied so far");
}
} // if
if (_cancelled)
{
throw new UserCancellationException();
}
} // while
} // using
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
if (needTemporaryTable)
{
// Discard the temporary table that was created in the database
SQLiteCommand deltemp = new SQLiteCommand(@"DROP TABLE " + tmpName, to);
deltemp.ExecuteNonQuery();
}
else
{
// Dicard the target table that was created in the database
SQLiteCommand deltable = new SQLiteCommand(@"DROP TABLE " + table.ObjectName.ToString(), to);
deltable.ExecuteNonQuery();
} // else
throw;
} // catch
NotifyPrimaryProgress(false, 70, "finalizing table copy operation (may take some time)..");
// Delete the original table and rename the temporary table to have the same name
// Note: this step is done at the very end in order to allow the user to cancel the operation
// without data loss.
tx = to.BeginTransaction();
try
{
if (_cancelled)
{
throw new UserCancellationException();
}
if (needTemporaryTable)
{
// In case we used a temporary table, we'll now drop the original table
// and rename the temporary table to have the name of the original table.
SQLiteCommand drop = new SQLiteCommand(
@"DROP TABLE " + table.ObjectName.ToString(), to, tx);
drop.ExecuteNonQuery();
SQLiteCommand alter = new SQLiteCommand(
@"ALTER TABLE " + tmpName + " RENAME TO " + table.ObjectName.ToString(), to, tx);
alter.ExecuteNonQuery();
} // if
// Add all indexes of the replaced table
int start = 80;
foreach (SQLiteDdlStatement stmt in fromSchema[SchemaObject.Index].Values)
{
if (_cancelled)
{
throw new UserCancellationException();
}
SQLiteCreateIndexStatement cindex = stmt as SQLiteCreateIndexStatement;
if (SQLiteParser.Utils.Chop(cindex.OnTable).ToLower() ==
SQLiteParser.Utils.Chop(table.ObjectName.ToString()).ToLower())
{
ReplaceIndex(cindex.ObjectName.ToString(), null, to, cindex, tx, start, start + 1);
start++;
if (start == 90)
{
start = 89;
}
}
} // foreach
// Add all table triggers of the replaced table
start = 90;
foreach (SQLiteDdlStatement stmt in fromSchema[SchemaObject.Trigger].Values)
{
SQLiteCreateTriggerStatement trigger = stmt as SQLiteCreateTriggerStatement;
if (SQLiteParser.Utils.Chop(trigger.TableName.ToString()).ToLower() ==
SQLiteParser.Utils.Chop(table.ObjectName.ToString()).ToLower())
{
ReplaceTrigger(trigger.ObjectName.ToString(), null, to, trigger, tx, start, start + 1);
start++;
if (start == 100)
{
start = 99;
}
}
} // foreach
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
if (needTemporaryTable)
{
// Discard the temporary table that was created in the database
SQLiteCommand deltemp = new SQLiteCommand(@"DROP TABLE " + tmpName, to);
deltemp.ExecuteNonQuery();
}
else
{
// Dicard the target table that was created in the database
SQLiteCommand deltable = new SQLiteCommand(@"DROP TABLE " + table.ObjectName.ToString(), to);
deltable.ExecuteNonQuery();
} // else
throw;
} // catch
NotifyPrimaryProgress(false, 99, "total of " + count + " rows copied");
}
/// <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
}
/// <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
}
