From a4b66874db35b0ad64257b202dc48d43924eb14e Mon Sep 17 00:00:00 2001 From: "mkanat%bugzilla.org" <> Date: Thu, 13 Dec 2007 09:14:32 +0000 Subject: Bug 408032: [Oracle] Make bzdbcopy.pl work with Oracle Patch By Max Kanat-Alexander (module owner) a=mkanat --- contrib/bzdbcopy.pl | 83 +++++++++++++++++++++++++++++++++++++++-------------- 1 file changed, 61 insertions(+), 22 deletions(-) (limited to 'contrib') diff --git a/contrib/bzdbcopy.pl b/contrib/bzdbcopy.pl index 9edf585af..5f5800d88 100755 --- a/contrib/bzdbcopy.pl +++ b/contrib/bzdbcopy.pl @@ -21,7 +21,9 @@ use strict; use lib qw(. lib); use Bugzilla; +use Bugzilla::Constants; use Bugzilla::DB; +use Bugzilla::Install::Util qw(indicate_progress); use Bugzilla::Util; ##################################################################### @@ -33,25 +35,29 @@ use constant SOURCE_DB_TYPE => 'Mysql'; use constant SOURCE_DB_NAME => 'bugs'; use constant SOURCE_DB_USER => 'bugs'; use constant SOURCE_DB_PASSWORD => ''; +use constant SOURCE_DB_HOST => 'localhost'; # Settings for the 'Target' DB that you are copying to. use constant TARGET_DB_TYPE => 'Pg'; use constant TARGET_DB_NAME => 'bugs'; use constant TARGET_DB_USER => 'bugs'; use constant TARGET_DB_PASSWORD => ''; +use constant TARGET_DB_HOST => 'localhost'; ##################################################################### # MAIN SCRIPT ##################################################################### +Bugzilla->usage_mode(USAGE_MODE_CMDLINE); + print "Connecting to the '" . SOURCE_DB_NAME . "' source database on " . SOURCE_DB_TYPE . "...\n"; -my $source_db = Bugzilla::DB::_connect(SOURCE_DB_TYPE, 'localhost', +my $source_db = Bugzilla::DB::_connect(SOURCE_DB_TYPE, SOURCE_DB_HOST, SOURCE_DB_NAME, undef, undef, SOURCE_DB_USER, SOURCE_DB_PASSWORD); print "Connecting to the '" . TARGET_DB_NAME . "' target database on " . TARGET_DB_TYPE . "...\n"; -my $target_db = Bugzilla::DB::_connect(TARGET_DB_TYPE, 'localhost', +my $target_db = Bugzilla::DB::_connect(TARGET_DB_TYPE, TARGET_DB_HOST, TARGET_DB_NAME, undef, undef, TARGET_DB_USER, TARGET_DB_PASSWORD); my $ident_char = $target_db->get_info( 29 ); # SQL_IDENTIFIER_QUOTE_CHAR @@ -65,11 +71,12 @@ my @table_list = $target_db->bz_table_list_real(); my $bz_schema_location = lsearch(\@table_list, 'bz_schema'); splice(@table_list, $bz_schema_location, 1) if $bz_schema_location > 0; -# We turn off autocommit on the target DB, because we're doing so -# much copying. -$target_db->{AutoCommit} = 0; -$target_db->{AutoCommit} == 0 - || warn "Failed to disable autocommit on " . TARGET_DB_TYPE; +# Instead of figuring out some fancy algorithm to insert data in the right +# order and not break FK integrity, we just drop them all. +$target_db->bz_drop_foreign_keys(); +# We start a transaction on the target DB, which helps when we're doing +# so many inserts. +$target_db->bz_start_transaction(); foreach my $table (@table_list) { my @serial_cols; print "Reading data from the source '$table' table on " @@ -94,9 +101,25 @@ foreach my $table (@table_list) { print "Clearing out the target '$table' table on " . TARGET_DB_TYPE . "...\n"; $target_db->do("DELETE FROM $table"); + + # Oracle doesn't like us manually inserting into tables that have + # auto-increment PKs set, because of the way we made auto-increment + # fields work. + if ($target_db->isa('Bugzilla::DB::Oracle')) { + foreach my $column (@table_columns) { + my $col_info = $source_db->bz_column_info($table, $column); + if ($col_info && $col_info->{TYPE} =~ /SERIAL/i) { + print "Dropping the sequence + trigger on $table.$column...\n"; + $target_db->do("DROP TRIGGER ${table}_${column}_TR"); + $target_db->do("DROP SEQUENCE ${table}_${column}_SEQ"); + } + } + } print "Writing data to the target '$table' table on " - . TARGET_DB_TYPE . "..."; + . TARGET_DB_TYPE . "...\n"; + my $count = 0; + my $total = scalar @$data_in; foreach my $row (@$data_in) { # Each column needs to be bound separately, because # many columns need to be dealt with specially. @@ -144,24 +167,39 @@ foreach my $table (@table_list) { } $insert_sth->execute(); + $count++; + indicate_progress({ current => $count, total => $total, every => 100 }); } - # PostgreSQL doesn't like it when you insert values into - # a serial field; it doesn't increment the counter - # automatically. - if ($target_db->isa('Bugzilla::DB::Pg')) { - foreach my $column (@table_columns) { - my $col_info = $source_db->bz_column_info($table, $column); - if ($col_info && $col_info->{TYPE} =~ /SERIAL/i) { - # Set the sequence to the current max value + 1. - my ($max_val) = $target_db->selectrow_array( + # For some DBs, we have to do clever things with auto-increment fields. + foreach my $column (@table_columns) { + next if $target_db->isa('Bugzilla::DB::Mysql'); + my $col_info = $source_db->bz_column_info($table, $column); + if ($col_info && $col_info->{TYPE} =~ /SERIAL/i) { + my ($max_val) = $target_db->selectrow_array( "SELECT MAX($column) FROM $table"); - $max_val = 0 if !defined $max_val; - $max_val++; - print "\nSetting the next value for $table.$column to $max_val."; + # Set the sequence to the current max value + 1. + $max_val = 0 if !defined $max_val; + $max_val++; + print "\nSetting the next value for $table.$column to $max_val."; + if ($target_db->isa('Bugzilla::DB::Pg')) { + # PostgreSQL doesn't like it when you insert values into + # a serial field; it doesn't increment the counter + # automatically. $target_db->do("SELECT pg_catalog.setval ('${table}_${column}_seq', $max_val, false)"); } + elsif ($target_db->isa('Bugzilla::DB::Oracle')) { + # Oracle increments the counter on every insert, and *always* + # sets the field, even if you gave it a value. So if there + # were already rows in the target DB (like the default rows + # created by checksetup), you'll get crazy values in your + # id columns. So we just dropped the sequences above and + # we re-create them here, starting with the right number. + my @sql = $target_db->_bz_real_schema->_get_create_seq_ddl( + $table, $column, $max_val); + $target_db->do($_) foreach @sql; + } } } @@ -169,9 +207,10 @@ foreach my $table (@table_list) { } print "Committing changes to the target database...\n"; -$target_db->commit; +$target_db->bz_commit_transaction(); +$target_db->bz_setup_foreign_keys(); -print "All done! Make sure to run checksetup on the new DB.\n"; +print "All done! Make sure to run checksetup.pl on the new DB.\n"; $source_db->disconnect; $target_db->disconnect; -- cgit v1.2.3-24-g4f1b