private void CopyTrigger(
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > leftSchema,
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > rightSchema,
SQLiteCreateTriggerStatement left,
SQLiteCreateTriggerStatement right, string leftdb, string rightdb, bool leftToRight)
{
using (SQLiteConnection leftConn = MakeDbConnection(leftdb))
{
leftConn.Open();
using (SQLiteConnection rightConn = MakeDbConnection(rightdb))
{
rightConn.Open();
SQLiteTransaction tx = null;
try
{
string name;
if (left != null)
{
name = left.ObjectName.ToString();
}
else
{
name = right.ObjectName.ToString();
}
if (leftToRight)
{
tx = rightConn.BeginTransaction();
ReplaceTrigger(name, rightSchema, rightConn, left, tx, 15, 70);
}
else
{
tx = leftConn.BeginTransaction();
ReplaceTrigger(name, leftSchema, leftConn, right, tx, 15, 70);
}
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
throw;
} // catch
} // using
} // using
}
private void ReplaceTrigger(string name,
Dictionary <SchemaObject, Dictionary <string, SQLiteDdlStatement> > schema,
SQLiteConnection conn, SQLiteCreateTriggerStatement stmt,
SQLiteTransaction tx, int start, int end)
{
if (stmt != null && schema != null)
{
// Make sure that the table that contains the trigger exists in the target database schema
// before trying to copy the trigger there.
string tableName = stmt.TableName.ToString().ToLower();
if (!schema[SchemaObject.Table].ContainsKey(tableName))
{
throw new InvalidOperationException("The trigger " + stmt.ObjectName.ToString() +
" cannot be added because table " + tableName +
" does not exist in the target database.\r\nCopying the table will automatically" +
" add the trigger you are trying to copy.");
}
}
NotifyPrimaryProgress(false, start, "deleting trigger " + name);
SQLiteCommand cmd = new SQLiteCommand(
@"DROP TRIGGER IF EXISTS " + name, conn, tx);
cmd.ExecuteNonQuery();
if (_cancelled)
{
throw new UserCancellationException();
}
if (stmt != null)
{
NotifyPrimaryProgress(false, (end - start) / 2 + start, "re-creating trigger ...");
// Re-create the trigger in the right database based on the
// trigger schema in the left database.
cmd = new SQLiteCommand(stmt.ToString(), conn, tx);
cmd.ExecuteNonQuery();
}
}
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");
}
