Bug 683644: Foreign keys aren't renamed correctly when DB tables are renamed

r=wicked a=LpSolit

2 files changed, 70 insertions, 20 deletions

diff --git a/Bugzilla/DB/Schema/Oracle.pm b/Bugzilla/DB/Schema/Oracle.pm
index 6011cecfc..cdc39409d 100644
--- a/Bugzilla/DB/Schema/Oracle.pm
+++ b/Bugzilla/DB/Schema/Oracle.pm
@@ -351,17 +351,10 @@ sub get_rename_column_ddl {
     my $def = $self->get_column_abstract($table, $old_name);
     if ($def->{TYPE} =~ /SERIAL/i) {
         # We have to rename the series also, and fix the default of the series.
-        push(@sql, "RENAME ${table}_${old_name}_SEQ TO 
-                      ${table}_${new_name}_seq");
-        my $serial_sql =
-                       "CREATE OR REPLACE TRIGGER ${table}_${new_name}_TR "
-                     . " BEFORE INSERT ON ${table} "
-                     . " FOR EACH ROW "
-                     . " BEGIN "
-                     . "   SELECT ${table}_${new_name}_SEQ.NEXTVAL "
-                     . "   INTO :NEW.${new_name} FROM DUAL; "
-                     . " END;";
-        push(@sql, $serial_sql);
+        my $old_seq = "${table}_${old_name}_SEQ";
+        my $new_seq = "${table}_${new_name}_SEQ";
+        push(@sql, "RENAME $old_seq TO $new_seq");
+        push(@sql, $self->_get_create_trigger_ddl($table, $new_name, $new_seq));
         push(@sql, "DROP TRIGGER ${table}_${old_name}_TR");
     }
     if ($def->{TYPE} =~ /varchar|text/i && $def->{NOTNULL} ) {
@@ -371,6 +364,35 @@ sub get_rename_column_ddl {
     return @sql;
 }
 
+sub get_rename_table_sql {
+    my ($self, $old_name, $new_name) = @_;
+    if (lc($old_name) eq lc($new_name)) {
+        # if the only change is a case change, return an empty list.
+        return ();
+    }
+
+    my @sql = ("ALTER TABLE $old_name RENAME TO $new_name");
+    my @columns = $self->get_table_columns($old_name);
+    foreach my $column (@columns) {
+        my $def = $self->get_column_abstract($old_name, $column);
+        if ($def->{TYPE} =~ /SERIAL/i) {
+            # If there's a SERIAL column on this table, we also need
+            # to rename the sequence.
+            my $old_seq = "${old_name}_${column}_SEQ";
+            my $new_seq = "${new_name}_${column}_SEQ";
+            push(@sql, "RENAME $old_seq TO $new_seq");
+            push(@sql, $self->_get_create_trigger_ddl($new_name, $column, $new_seq));
+            push(@sql, "DROP TRIGGER ${old_name}_${column}_TR");
+        }
+        if ($def->{TYPE} =~ /varchar|text/i && $def->{NOTNULL}) {
+            push(@sql, _get_notnull_trigger_ddl($new_name, $column));
+            push(@sql, "DROP TRIGGER ${$old_name}_${column}");
+        }
+    }
+
+    return @sql;
+}
+
 sub _get_notnull_trigger_ddl {
       my ($table, $column) = @_;
 
@@ -398,19 +420,24 @@ sub _get_create_seq_ddl {
                    . " NOMAXVALUE "
                    . " NOCYCLE "
                    . " NOCACHE";
-     my $serial_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;";
     push (@ddl, $seq_sql);
-    push (@ddl, $serial_sql);
+    push(@ddl, $self->_get_create_trigger_ddl($table, $column, $seq_name));
 
     return @ddl;
 }
 
+sub _get_create_trigger_ddl {
+    my ($self, $table, $column, $seq_name) = @_;
+    my $serial_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 $serial_sql;
+}
+
 sub get_set_serial_sql { 
     my ($self, $table, $column, $value) = @_; 
     my @sql;
diff --git a/Bugzilla/DB/Schema/Pg.pm b/Bugzilla/DB/Schema/Pg.pm
index 517837dcc..ef6e5671d 100644
--- a/Bugzilla/DB/Schema/Pg.pm
+++ b/Bugzilla/DB/Schema/Pg.pm
@@ -114,7 +114,30 @@ sub get_rename_table_sql {
         # is case-insensitive and will return an error about a duplicate name
         return ();
     }
-    return ("ALTER TABLE $old_name RENAME TO $new_name");
+
+    my @sql = ("ALTER TABLE $old_name RENAME TO $new_name");
+
+    # If there's a SERIAL column on this table, we also need to rename the
+    # sequence.
+    # If there is a PRIMARY KEY, we need to rename it too.
+    my @columns = $self->get_table_columns($old_name);
+    foreach my $column (@columns) {
+        my $def = $self->get_column_abstract($old_name, $column);
+        if ($def->{TYPE} =~ /SERIAL/i) {
+            my $old_seq = "${old_name}_${column}_seq";
+            my $new_seq = "${new_name}_${column}_seq";
+            push(@sql, "ALTER SEQUENCE $old_seq RENAME TO $new_seq");
+            push(@sql, "ALTER TABLE $new_name ALTER COLUMN $column
+                             SET DEFAULT NEXTVAL('$new_seq')");
+        }
+        if ($def->{PRIMARYKEY}) {
+            my $old_pk = "${old_name}_pkey";
+            my $new_pk = "${new_name}_pkey";
+            push(@sql, "ALTER INDEX $old_pk RENAME to $new_pk");
+        }
+    }
+
+    return @sql;
 }
 
 sub get_set_serial_sql {