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