summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorFrédéric Buclin <LpSolit@gmail.com>2012-08-30 00:45:52 +0200
committerFrédéric Buclin <LpSolit@gmail.com>2012-08-30 00:45:52 +0200
commit463d0cf5170b2d9a52534f3307cff952d6935c78 (patch)
tree7b42066f121484e73a66f6f8d6b73b6c60b07e69
parent27cc6e1bb1b039e7af6348f4c06f71a1f1b06493 (diff)
downloadbugzilla-463d0cf5170b2d9a52534f3307cff952d6935c78.tar.gz
bugzilla-463d0cf5170b2d9a52534f3307cff952d6935c78.tar.xz
Bug 731156: [Oracle] Adding or removing a DB column does not handle SERIAL correctly
r=dkl a=LpSolit
-rw-r--r--Bugzilla/DB/Oracle.pm62
-rw-r--r--Bugzilla/DB/Schema/Oracle.pm64
2 files changed, 97 insertions, 29 deletions
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index d7ff08676..f5045b2c7 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -616,11 +616,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';
@@ -634,15 +648,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 "
@@ -653,22 +665,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 f6a55b479..381906d2e 100644
--- a/Bugzilla/DB/Schema/Oracle.pm
+++ b/Bugzilla/DB/Schema/Oracle.pm
@@ -184,6 +184,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) = @_;
@@ -349,6 +374,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)) {
@@ -450,20 +498,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;