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();
}
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");
}