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

r=dkl a=LpSolit

1 files changed, 48 insertions, 16 deletions

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;