private void btnReorderColumns_Click(object sender, EventArgs e)
{
if (ucDiff.IsLeftFocused)
{
SQLiteCreateTableStatement reordered = Utils.ReOrderTableColumns(
(SQLiteCreateTableStatement)_item.RightDdlStatement,
(SQLiteCreateTableStatement)_item.LeftDdlStatement);
string[] left = _nlrx.Split(reordered.ToString());
string[] right = _nlrx.Split(_item.RightDdlStatement.ToString());
ucDiff.ReplaceText(left, right);
}
else
{
SQLiteCreateTableStatement reordered = Utils.ReOrderTableColumns(
(SQLiteCreateTableStatement)_item.LeftDdlStatement,
(SQLiteCreateTableStatement)_item.RightDdlStatement);
string[] left = _nlrx.Split(_item.LeftDdlStatement.ToString());
string[] right = _nlrx.Split(reordered.ToString());
ucDiff.ReplaceText(left, right);
}
UpdateState();
}
/// <summary>
/// Opens the search data dialog
/// </summary>
public void SearchData()
{
bool isLeft = grdLeft.Focused;
SQLiteCreateTableStatement table =
isLeft ? (SQLiteCreateTableStatement)_item.LeftDdlStatement : (SQLiteCreateTableStatement)_item.RightDdlStatement;
FastGrid grid = isLeft ? grdLeft : grdRight;
FastGridLocation loc = grid.SelectedCellLocation;
FastGridColumn fcol = grid.Columns[loc.ColumnIndex];
string cname = (string)fcol.Tag;
_searchDialog.PrepareDialog(table.Columns, cname, _diff, _tableChanges, loc.RowIndex + 1, isLeft);
DialogResult res = _searchDialog.ShowDialog(this);
if (res == DialogResult.OK && _searchDialog.MatchedRowIndex != -1)
{
FastGridSelection sel = new FastGridSelection();
sel.AddSelection(_searchDialog.MatchedRowIndex, _searchDialog.MatchedRowIndex);
FastGridLocation nloc = new FastGridLocation(_searchDialog.MatchedRowIndex, loc.ColumnIndex);
grdLeft.SelectedCellLocation = nloc;
grdLeft.Selection = sel;
grdRight.SelectedCellLocation = nloc;
grdRight.Selection = (FastGridSelection)sel.Clone();
}
}
private DialogResult OpenCellEditDialog(SQLiteCreateTableStatement table, SQLiteColumnStatement column, ref object value)
{
DialogResult res;
CellValueEditorDialog dlg = new CellValueEditorDialog();
res = dlg.ShowEditor(this, table, column, ref value);
return(res);
}
/// <summary>
/// Show the editor for the specified column and field value.
/// </summary>
/// <param name="owner">The owner window</param>
/// <param name="table">The table sql schema object</param>
/// <param name="column">The column whose field is edited</param>
/// <param name="value">The initial value to edit</param>
/// <returns>
/// If the user chose to apply his changes - DialogResult.OK (in which case the <paramref name="value"/>
/// parameter is changed to the edited value). Otherwise a DialogResult.Cancel value is returned.
/// </returns>
public DialogResult ShowEditor(IWin32Window owner, SQLiteCreateTableStatement table, SQLiteColumnStatement column, ref object value)
{
_table = table;
_column = column;
this.Text = "Edit " + column.ObjectName.ToString();
TabPage tbp = null;
_nullable = column.IsNullable;
if (value == DBNull.Value)
{
cbxSetAsNull.Checked = true;
tbp = GetTabPageForColumnType(column);
}
else
{
cbxSetAsNull.Checked = false;
tbp = GetTabPageForValueType(value);
}
if (Utils.IsColumnActingAsRowIdAlias(_table, column.ObjectName.ToString()))
{
// This is a special case in which we allow the user to edit a INTEGER PRIMARY KEY column
// that acts as an alias to the underlying RowID column. In this case we can't allow the user
// to choose any other type other than INTEGER when editing the field.
tbp = tbpEditInteger;
}
TabPage tbp2 = GetTabPageForColumnType(column);
if (!object.ReferenceEquals(tbp, tbp2))
{
RemoveAllPagesExcept(tbp, tbp2);
SetTabPageValue(tbp, value);
SetTabPageValue(tbp2, value);
}
else
{
RemoveAllPagesExcept(tbp);
SetTabPageValue(tbp, value);
}
_origTabPage = tbp;
tbcTypes.SelectedTab = tbp;
UpdateState();
DialogResult res = ShowDialog(owner);
if (res == DialogResult.OK)
{
value = _value;
}
GC.Collect();
return(res);
}
/// <summary>
/// Checks if the specified table contains a column with the specified name.
/// </summary>
/// <param name="table">The table to check</param>
/// <param name="column">The column to chcek</param>
/// <returns>TRUE if the table contains a column with the specified name, FALSE otherwise.</returns>
private bool TableContainsColumn(SQLiteCreateTableStatement table, SQLiteColumnStatement column)
{
foreach (SQLiteColumnStatement tc in table.Columns)
{
if (tc.ObjectName.Equals(column.ObjectName))
{
return(true);
}
} // foreach
return(false);
}
private void UpdateInsertCommandFields(SQLiteCreateTableStatement table,
SQLiteCommand insert, SQLiteDataReader reader)
{
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
string prmName = Utils.GetColumnParameterName(col.ObjectName.ToString());
string colName = SQLiteParser.Utils.Chop(col.ObjectName.ToString());
insert.Parameters[prmName].Value = reader[colName];
} // for
}
/// <summary>
/// Check if the specified talbe has primary key(s)
/// </summary>
/// <param name="table">the table to check</param>
/// <returns>TRUE if the table has primary key(s)</returns>
private bool HasPrimaryKeys(SQLiteCreateTableStatement table)
{
List <SQLiteColumnStatement> res = Utils.GetPrimaryColumns(table);
if (res.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private SQLiteCommand BuildInsertCommand(SQLiteCreateTableStatement table, string tableName,
SQLiteConnection conn, SQLiteTransaction tx)
{
SQLiteCommand res = new SQLiteCommand();
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + tableName + " (");
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
sb.Append(col.ObjectName.ToString());
if (i < table.Columns.Count - 1)
{
sb.Append(",");
}
} // for
sb.Append(") VALUES (");
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
string prmName = Utils.GetColumnParameterName(col.ObjectName.ToString());
sb.Append(prmName);
if (i < table.Columns.Count - 1)
{
sb.Append(",");
}
res.Parameters.Add(prmName, Utils.GetDbType(col.ColumnType));
} // for
sb.Append(")");
res.CommandText = sb.ToString();
res.Connection = conn;
res.Transaction = tx;
return(res);
}
private void CopyTable(
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > leftSchema,
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > rightSchema,
SQLiteCreateTableStatement left,
SQLiteCreateTableStatement right,
string leftdb,
string rightdb,
bool leftToRight)
{
using (SQLiteConnection leftConn = MakeDbConnection(leftdb))
{
leftConn.Open();
using (SQLiteConnection rightConn = MakeDbConnection(rightdb))
{
rightConn.Open();
string name;
if (left != null)
{
name = left.ObjectName.ToString();
}
else
{
name = right.ObjectName.ToString();
}
if (leftToRight)
{
ReplaceTable(leftSchema, rightSchema, name, leftConn, rightConn, left);
}
else
{
ReplaceTable(rightSchema, leftSchema, name, rightConn, leftConn, right);
}
} // using
} // using
}
/// <summary>
/// This purpose of this method is to fill the needed row with data
/// taken from the table-changes object. This is part of a virtual mode
/// implementation of the FastGrid control designed to minimize memory
/// requirements only to the bare essentials.
/// </summary>
/// <param name="grid">The grid that issued to the RowNeeded request</param>
/// <param name="rowIndex">The index of the row to fill</param>
/// <param name="row">The row to fill</param>
private void FillRow(FastGrid grid, long rowIndex, FastGridRow row)
{
// Fetch the table change item from the cache/database
TableChangeItem item = _tableChanges.GetChangeItem(_diff, rowIndex);
bool empty;
bool isLeft;
SQLiteCreateTableStatement table = null;
if (grid == grdLeft)
{
isLeft = true;
empty = item.LeftFields == null;
table = _tableChanges.LeftTable;
}
else
{
isLeft = false;
empty = item.RightFields == null;
table = _tableChanges.RightTable;
} // else
if (empty)
{
for (int i = 0; i < row.Cells.Length; i++)
{
row.Cells[i].Style.BackColor = EMPTY_ROW_BACK_COLOR;
}
}
else
{
for (int i = 0; i < row.Cells.Length; i++)
{
string cname = (string)grid.Columns[i].Tag;
SQLiteColumnStatement col = Utils.GetColumnByName(table, cname);
object fval = item.GetField(cname, isLeft);
if (fval == DBNull.Value || fval == null)
{
row.Cells[i].Value = "NULL";
row.Cells[i].Style.Font = _italic;
}
else if (Utils.GetDbType(col.ColumnType) == DbType.Binary)
{
long v = (long)fval;
if (v == 1)
{
row.Cells[i].Value = "BLOB";
}
else
{
row.Cells[i].Value = "NULL";
}
row.Cells[i].Style.Font = _italic;
}
else
{
if (fval is byte[])
{
string tmp = Encoding.ASCII.GetString((byte[])fval);
row.Cells[i].Value = tmp;
}
else
{
row.Cells[i].Value = fval;
}
} // else
// Mark different cells with special background color
if (item.Result == ComparisonResult.DifferentData)
{
if (i < item.LeftFields.Length && i < item.RightFields.Length)
{
if (item.ChangedBlobsColumnNames != null && item.ChangedBlobsColumnNames.Contains(col.ObjectName.ToString()))
{
row.Cells[i].Style.BackColor = DIFFERENT_CELL_BACK_COLOR;
}
else
{
// Check only if the field appears in both tables and has different values
if (item.HasField(cname, true) && item.HasField(cname, false) && !item.GetField(cname, true).Equals(item.GetField(cname, false)))
{
object tmp = item.GetField(cname, isLeft);
if (tmp is long && ((long)tmp) == 0 && Utils.GetDbType(col.ColumnType) == DbType.Binary &&
item.GetField(cname, !isLeft) == DBNull.Value)
{
// Ignore the case when the values are not equal when one of the fields is a BLOB and the
// other is not, but both values indicate NULL content.
}
else
{
tmp = item.GetField(cname, !isLeft);
SQLiteCreateTableStatement tbl;
if (isLeft)
{
tbl = _tableChanges.RightTable;
}
else
{
tbl = _tableChanges.LeftTable;
}
object tmp2 = item.GetField(cname, isLeft);
SQLiteColumnStatement ocol = Utils.GetColumnByName(tbl.Columns, cname);
if (tmp2 == DBNull.Value && tmp != null && tmp is long && ((long)tmp) == 0)
{
// Ignore the reverse case when both fields are actually NULL, but one of them is BLOB
// and ther other is not.
}
else
{
row.Cells[i].Style.BackColor = DIFFERENT_CELL_BACK_COLOR;
}
} // else
} // if
} // else
} // if
} // if
} // for
} // else
}
private void FillGridColumns(FastGrid grid, List <SQLiteColumnStatement> common, SQLiteCreateTableStatement table)
{
// Construct a list of columns that starts with all the primary key columns
// and followed by all other columns that are common to both tables.
List <SQLiteColumnStatement> pkeys = Utils.GetPrimaryColumns(table);
List <SQLiteColumnStatement> cols = new List <SQLiteColumnStatement>();
foreach (SQLiteColumnStatement cs in common)
{
bool found = false;
for (int i = 0; i < pkeys.Count; i++)
{
if (pkeys[i].ObjectName.Equals(cs.ObjectName))
{
found = true;
break;
}
} // for
if (!found)
{
cols.Add(cs);
}
} // foreach
// Construct a list of columns that are unique to this table (not common with another table)
List <SQLiteColumnStatement> mycols = new List <SQLiteColumnStatement>();
foreach (SQLiteColumnStatement c in table.Columns)
{
bool found = false;
foreach (SQLiteColumnStatement cm in common)
{
if (c.ObjectName.Equals(cm.ObjectName))
{
found = true;
break;
}
}
if (!found)
{
mycols.Add(c);
}
} // foreach
grid.Columns.Clear();
// Add primary key columns
for (int i = 0; i < pkeys.Count; i++)
{
grid.Columns.Add(MakeGridColumn(true, GetColumnFromTable(pkeys[i].ObjectName, table.Columns)));
}
// Add common columns
for (int i = 0; i < cols.Count; i++)
{
grid.Columns.Add(MakeGridColumn(false, GetColumnFromTable(cols[i].ObjectName, table.Columns)));
}
// Add non common columns
for (int i = 0; i < mycols.Count; i++)
{
grid.Columns.Add(MakeGridColumn(false, GetColumnFromTable(mycols[i].ObjectName, table.Columns)));
}
grid.RefreshLayout();
}
private void btnCompareData_Click(object sender, EventArgs e)
{
// Before comparing data we have to check if there are any BLOB columns
// in the any common columns of the tables. If there are any - we have to
// ask the user if he wants to compare BLOB fields or not.
SQLiteCreateTableStatement leftTable = _item.LeftDdlStatement as SQLiteCreateTableStatement;
SQLiteCreateTableStatement rightTable = _item.RightDdlStatement as SQLiteCreateTableStatement;
List <SQLiteColumnStatement> common = Utils.GetCommonColumns(leftTable, rightTable);
bool allowBlobComparison = false;
if (Utils.ContainsBlobColumn(common))
{
DialogResult res = MessageBox.Show(this,
"At least one column that will be compared is a BLOB.\r\nComparing BLOB fields can potentially take " +
"a lot of time to perform.\r\nDo you want to disable BLOB content comparison in order\r\nto make " +
"the comparison go faster?",
"Disable BLOB Contents Comparison?",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning);
if (res == DialogResult.No)
{
allowBlobComparison = true;
}
}
string errmsg;
if (!Utils.IsTableComparisonAllowed(leftTable, rightTable,
out errmsg, allowBlobComparison))
{
MessageBox.Show(this, errmsg, "Data comparison is not allowed",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
TableCompareWorker worker = new TableCompareWorker(
leftTable, rightTable, _leftdb, _rightdb, allowBlobComparison);
ProgressDialog dlg = new ProgressDialog();
dlg.Start(this, worker);
if (dlg.Error != null)
{
if (dlg.Error.GetType() != typeof(UserCancellationException))
{
_item.ErrorMessage = dlg.Error.Message;
}
return;
}
_tableChanges = (TableChanges)dlg.Result;
if (!tbcViews.TabPages.Contains(tbpData))
{
tbcViews.TabPages.Add(tbpData);
}
// Update the schema comparison item
panel1.Visible = false;
_item.ErrorMessage = null;
_item.TableChanges = _tableChanges;
if (SchemaChanged != null)
{
SchemaChanged(this, EventArgs.Empty);
}
// Set the table changes object into the table diff control
UpdateDataTab();
tbcViews.SelectedTab = tbpData;
}
/// <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 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>();
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
}
