diff options
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 62 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Oracle.pm | 64 |
2 files changed, 97 insertions, 29 deletions
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm index 83dc3a29a..da263e084 100644 --- a/Bugzilla/DB/Oracle.pm +++ b/Bugzilla/DB/Oracle.pm @@ -637,11 +637,25 @@ sub bz_setup_database { $self->SUPER::bz_setup_database(@_); + my $sth = $self->prepare("SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME = ?"); my @tables = $self->bz_table_list_real(); + foreach my $table (@tables) { my @columns = $self->bz_table_columns_real($table); foreach my $column (@columns) { my $def = $self->bz_column_info($table, $column); + # bz_add_column() before Bugzilla 4.2.3 didn't handle primary keys + # correctly (bug 731156). We have to add missing sequences and + # triggers ourselves. + if ($def->{TYPE} =~ /SERIAL/i) { + my $sequence = "${table}_${column}_SEQ"; + my $exists = $self->selectrow_array($sth, undef, $sequence); + if (!$exists) { + my @sql = $self->_get_create_seq_ddl($table, $column); + $self->do($_) foreach @sql; + } + } + if ($def->{REFERENCES}) { my $references = $def->{REFERENCES}; my $update = $references->{UPDATE} || 'CASCADE'; @@ -655,15 +669,13 @@ sub bz_setup_database { $to_table = 'tag'; } if ( $update =~ /CASCADE/i ){ - my $trigger_name = uc($fk_name . "_UC"); - my $exist_trigger = $self->selectcol_arrayref( - "SELECT OBJECT_NAME FROM USER_OBJECTS - WHERE OBJECT_NAME = ?", undef, $trigger_name); + my $trigger_name = uc($fk_name . "_UC"); + my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name); if(@$exist_trigger) { $self->do("DROP TRIGGER $trigger_name"); } - my $tr_str = "CREATE OR REPLACE TRIGGER $trigger_name" + my $tr_str = "CREATE OR REPLACE TRIGGER $trigger_name" . " AFTER UPDATE OF $to_column ON $to_table " . " REFERENCING " . " NEW AS NEW " @@ -674,22 +686,46 @@ sub bz_setup_database { . " SET $column = :NEW.$to_column" . " WHERE $column = :OLD.$to_column;" . " END $trigger_name;"; - $self->do($tr_str); - } - } - } - } + $self->do($tr_str); + } + } + } + } # Drop the trigger which causes bug 541553 my $trigger_name = "PRODUCTS_MILESTONEURL"; - my $exist_trigger = $self->selectcol_arrayref( - "SELECT OBJECT_NAME FROM USER_OBJECTS - WHERE OBJECT_NAME = ?", undef, $trigger_name); + my $exist_trigger = $self->selectcol_arrayref($sth, undef, $trigger_name); if(@$exist_trigger) { $self->do("DROP TRIGGER $trigger_name"); } } +# These two methods have been copied from Bugzilla::DB::Schema::Oracle. +sub _get_create_seq_ddl { + my ($self, $table, $column) = @_; + + my $seq_name = "${table}_${column}_SEQ"; + my $seq_sql = "CREATE SEQUENCE $seq_name INCREMENT BY 1 START WITH 1 " . + "NOMAXVALUE NOCYCLE NOCACHE"; + my $trigger_sql = $self->_get_create_trigger_ddl($table, $column, $seq_name); + return ($seq_sql, $trigger_sql); +} + +sub _get_create_trigger_ddl { + my ($self, $table, $column, $seq_name) = @_; + + my $trigger_sql = "CREATE OR REPLACE TRIGGER ${table}_${column}_TR " + . " BEFORE INSERT ON $table " + . " FOR EACH ROW " + . " BEGIN " + . " SELECT ${seq_name}.NEXTVAL " + . " INTO :NEW.$column FROM DUAL; " + . " END;"; + return $trigger_sql; +} + +############################################################################ + package Bugzilla::DB::Oracle::st; use base qw(DBI::st); diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm index f2d5b8be0..9fafc4515 100644 --- a/Bugzilla/DB/Schema/Oracle.pm +++ b/Bugzilla/DB/Schema/Oracle.pm @@ -199,6 +199,31 @@ sub _get_fk_name { return $fk_name; } +sub get_add_column_ddl { + my $self = shift; + my ($table, $column, $definition, $init_value) = @_; + my @sql; + + # Create sequences and triggers to emulate SERIAL datatypes. + if ($definition->{TYPE} =~ /SERIAL/i) { + # Clone the definition to not alter the original one. + my %def = %$definition; + # Oracle requires to define the column is several steps. + my $pk = delete $def{PRIMARYKEY}; + my $notnull = delete $def{NOTNULL}; + @sql = $self->SUPER::get_add_column_ddl($table, $column, \%def, $init_value); + push(@sql, $self->_get_create_seq_ddl($table, $column)); + push(@sql, "UPDATE $table SET $column = ${table}_${column}_SEQ.NEXTVAL"); + push(@sql, "ALTER TABLE $table MODIFY $column NOT NULL") if $notnull; + push(@sql, "ALTER TABLE $table ADD PRIMARY KEY ($column)") if $pk; + } + else { + @sql = $self->SUPER::get_add_column_ddl(@_); + } + + return @sql; +} + sub get_alter_column_ddl { my ($self, $table, $column, $new_def, $set_nulls_to) = @_; @@ -364,6 +389,29 @@ sub get_rename_column_ddl { return @sql; } +sub get_drop_column_ddl { + my $self = shift; + my ($table, $column) = @_; + my @sql; + push(@sql, $self->SUPER::get_drop_column_ddl(@_)); + my $dbh=Bugzilla->dbh; + my $trigger_name = uc($table . "_" . $column); + my $exist_trigger = $dbh->selectcol_arrayref( + "SELECT OBJECT_NAME FROM USER_OBJECTS + WHERE OBJECT_NAME = ?", undef, $trigger_name); + if(@$exist_trigger) { + push(@sql, "DROP TRIGGER $trigger_name"); + } + # If this column is of type SERIAL, we need to drop the sequence + # and trigger that went along with it. + my $def = $self->get_column_abstract($table, $column); + if ($def->{TYPE} =~ /SERIAL/i) { + push(@sql, "DROP SEQUENCE ${table}_${column}_SEQ"); + push(@sql, "DROP TRIGGER ${table}_${column}_TR"); + } + return @sql; +} + sub get_rename_table_sql { my ($self, $old_name, $new_name) = @_; if (lc($old_name) eq lc($new_name)) { @@ -465,20 +513,4 @@ sub get_set_serial_sql { return @sql; } -sub get_drop_column_ddl { - my $self = shift; - my ($table, $column) = @_; - my @sql; - push(@sql, $self->SUPER::get_drop_column_ddl(@_)); - my $dbh=Bugzilla->dbh; - my $trigger_name = uc($table . "_" . $column); - my $exist_trigger = $dbh->selectcol_arrayref( - "SELECT OBJECT_NAME FROM USER_OBJECTS - WHERE OBJECT_NAME = ?", undef, $trigger_name); - if(@$exist_trigger) { - push(@sql, "DROP TRIGGER $trigger_name"); - } - return @sql; -} - 1; |