diff options
| -rw-r--r-- | Bugzilla/DB.pm | 31 | ||||
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 23 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 6 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Mysql.pm | 6 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Oracle.pm | 29 | ||||
| -rw-r--r-- | Bugzilla/Install/Util.pm | 2 | ||||
| -rwxr-xr-x | contrib/bzdbcopy.pl | 83 | ||||
| -rw-r--r-- | template/en/default/global/messages.html.tmpl | 3 |
8 files changed, 146 insertions, 37 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 49692eec0..f97273e1a 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -504,6 +504,19 @@ sub bz_setup_foreign_keys { } } +# This is used by contrib/bzdbcopy.pl, mostly. +sub bz_drop_foreign_keys { + my ($self) = @_; + + my @tables = $self->_bz_real_schema->get_table_list(); + foreach my $table (@tables) { + my @columns = $self->_bz_real_schema->get_table_columns($table); + foreach my $column (@columns) { + $self->bz_drop_fk($table, $column); + } + } +} + ##################################################################### # Schema Modification Methods ##################################################################### @@ -749,6 +762,24 @@ sub bz_drop_column { } } +sub bz_drop_fk { + my ($self, $table, $column) = @_; + + my $col_def = $self->bz_column_info($table, $column); + if ($col_def && exists $col_def->{REFERENCES}) { + my $def = $col_def->{REFERENCES}; + print get_text('install_fk_drop', + { table => $table, column => $column, fk => $def }) + . "\n" if Bugzilla->usage_mode == USAGE_MODE_CMDLINE; + my @sql = $self->_bz_real_schema->get_drop_fk_sql($table,$column,$def); + $self->do($_) foreach @sql; + delete $col_def->{REFERENCES}; + $self->_bz_real_schema->set_column($table, $column, $col_def); + $self->_bz_store_real_schema; + } + +} + sub bz_drop_index { my ($self, $table, $name) = @_; diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm index 509dedaa1..a970e3193 100644 --- a/Bugzilla/DB/Oracle.pm +++ b/Bugzilla/DB/Oracle.pm @@ -406,7 +406,30 @@ sub quote_identifier { return $id; } +##################################################################### +# Protected "Real Database" Schema Information Methods +##################################################################### +sub bz_table_columns_real { + my ($self, $table) = @_; + $table = uc($table); + my @cols = $self->SUPER::bz_table_columns_real($table); + return map { lc($_) } @cols; +} + +sub bz_table_list_real { + my ($self) = @_; + # Oracle only accepts the username in uppercase. + my $db_user = uc(Bugzilla->localconfig->{db_user}); + my $table_sth = $self->table_info(undef, $db_user, undef, "TABLE"); + my @tables = @{$self->selectcol_arrayref($table_sth, { Columns => [3] })}; + # Oracle returns uppercase table names, but Bugzilla expects lowercase + # names. + @tables = map { lc($_) } @tables; + # Oracle has certain tables that start with DR$_IDX. + @tables = grep { $_ !~ /^dr\$/ } @tables; + return @tables; +} ##################################################################### # Custom Database Setup diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index 023f01860..2be64cab3 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -1533,9 +1533,9 @@ sub get_add_fk_sql { return ("ALTER TABLE $table ADD $fk_string"); } -sub _get_drop_fk_sql { - my ($self, $table, $column, $old_def) = @_; - my $fk_name = $self->_get_fk_name($table, $column, $old_def->{REFERENCES}); +sub get_drop_fk_sql { + my ($self, $table, $column, $references) = @_; + my $fk_name = $self->_get_fk_name($table, $column, $references); return ("ALTER TABLE $table DROP CONSTRAINT $fk_name"); } diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm index 300b1a0f1..2f4bc42b2 100644 --- a/Bugzilla/DB/Schema/Mysql.pm +++ b/Bugzilla/DB/Schema/Mysql.pm @@ -193,9 +193,9 @@ sub get_alter_column_ddl { return @statements; } -sub _get_drop_fk_sql { - my ($self, $table, $column, $old_def) = @_; - my $fk_name = $self->_get_fk_name($table, $column, $old_def->{REFERENCES}); +sub get_drop_fk_sql { + my ($self, $table, $column, $references) = @_; + my $fk_name = $self->_get_fk_name($table, $column, $references); my @sql = ("ALTER TABLE $table DROP FOREIGN KEY $fk_name"); my $dbh = Bugzilla->dbh; diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm index 77024507b..8f0f880be 100644 --- a/Bugzilla/DB/Schema/Oracle.pm +++ b/Bugzilla/DB/Schema/Oracle.pm @@ -93,7 +93,7 @@ sub get_table_ddl { } # Create sequences and triggers to emulate SERIAL datatypes. if ( $field_info->{TYPE} =~ /SERIAL/i ) { - push (@ddl, _get_create_seq_ddl($table, $field_name)); + push (@ddl, $self->_get_create_seq_ddl($table, $field_name)); } } return @ddl; @@ -140,17 +140,17 @@ sub get_fk_ddl { $fk_string = $fk_string . " ON DELETE $delete" if $delete; if ( $update =~ /CASCADE/i ){ - my $tr_str = "CREATE OR REPLACE TRIGGER ". $table . "_uc" + my $tr_str = "CREATE OR REPLACE TRIGGER ${fk_name}_UC" . " AFTER UPDATE ON ". $table . " REFERENCING " . " NEW AS NEW " . " OLD AS OLD " . " FOR EACH ROW " . " BEGIN " - . " UPDATE ". $to_table - . " SET ". $to_column . " = :NEW.". $column - . " WHERE ". $to_column . " = :OLD.". $column . ";" - . " END ". $table . "_uc;"; + . " UPDATE $to_table" + . " SET $to_column = :NEW.$column" + . " WHERE $to_column = :OLD.$column;" + . " END ${fk_name}_UC;"; my $dbh = Bugzilla->dbh; $dbh->do($tr_str); } @@ -158,6 +158,18 @@ sub get_fk_ddl { return $fk_string; } +sub get_drop_fk_sql { + my $self = shift; + my ($table, $column, $references) = @_; + my $fk_name = $self->_get_fk_name(@_); + my @sql; + if (!$references->{UPDATE} || $references->{UPDATE} =~ /CASCADE/i) { + push(@sql, "DROP TRIGGER ${fk_name}_uc"); + } + push(@sql, $self->SUPER::get_drop_fk_sql(@_)); + return @sql; +} + sub _get_fk_name { my ($self, $table, $column, $references) = @_; my $to_table = $references->{TABLE}; @@ -185,12 +197,13 @@ sub _get_notnull_trigger_ddl { } sub _get_create_seq_ddl { - my ($table, $column) = @_; + my ($self, $table, $column, $start_with) = @_; + $start_with ||= 1; my @ddl; my $seq_name = "${table}_${column}_SEQ"; my $seq_sql = "CREATE SEQUENCE $seq_name " . " INCREMENT BY 1 " - . " START WITH 1 " + . " START WITH $start_with " . " NOMAXVALUE " . " NOCYCLE " . " NOCACHE"; diff --git a/Bugzilla/Install/Util.pm b/Bugzilla/Install/Util.pm index 3942aa82a..67aeb4873 100644 --- a/Bugzilla/Install/Util.pm +++ b/Bugzilla/Install/Util.pm @@ -80,7 +80,7 @@ sub indicate_progress { my $every = $params->{every} || 1; print "." if !($current % $every); - if ($current % ($every * 60) == 0) { + if ($current == $total || $current % ($every * 60) == 0) { print "$current/$total (" . int($current * 100 / $total) . "%)\n"; } } 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; diff --git a/template/en/default/global/messages.html.tmpl b/template/en/default/global/messages.html.tmpl index 28557bfcb..86660de18 100644 --- a/template/en/default/global/messages.html.tmpl +++ b/template/en/default/global/messages.html.tmpl @@ -388,6 +388,9 @@ [% ELSIF message_tag == "install_fk_add" %] Adding foreign key: [% table FILTER html %].[% column FILTER html %] -> [% fk.TABLE FILTER html %].[% fk.COLUMN FILTER html %]... + [% ELSIF message_tag == "install_fk_drop" %] + Dropping foreign key: [% table FILTER html %].[% column FILTER html %] -> [% fk.TABLE FILTER html %].[% fk.COLUMN FILTER html %]... + [% ELSIF message_tag == "install_group_create" %] Creating group [% name FILTER html %]... |