Bug 731156: [Oracle] Adding or removing a DB column does not handle SERIAL correctly

r=dkl a=LpSolit

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;