From 463d0cf5170b2d9a52534f3307cff952d6935c78 Mon Sep 17 00:00:00 2001
From: Frédéric Buclin <LpSolit@gmail.com>
Date: Thu, 30 Aug 2012 00:45:52 +0200
Subject: Bug 731156: [Oracle] Adding or removing a DB column does not handle
 SERIAL correctly r=dkl a=LpSolit

---
 Bugzilla/DB/Schema/Oracle.pm | 64 +++++++++++++++++++++++++++++++++-----------
 1 file changed, 48 insertions(+), 16 deletions(-)

(limited to 'Bugzilla/DB/Schema')

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;
-- 
cgit v1.2.3-24-g4f1b