From 66146a6e56c9a23cad32a6cbf897018372c07e80 Mon Sep 17 00:00:00 2001
From: "mkanat%bugzilla.org" <>
Date: Sun, 29 Jun 2008 00:17:47 +0000
Subject: Bug 429804: Add Foreign Keys to Multiselect fields Patch By Alex
 Eiser <aeiser@arc.nasa.gov> r=mkanat, a=mkanat

---
 Bugzilla/DB.pm         | 14 ++++++++++----
 Bugzilla/DB/Schema.pm  |  1 +
 Bugzilla/Install/DB.pm | 22 ++++++++++++++++++++++
 3 files changed, 33 insertions(+), 4 deletions(-)

diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 2eba30482..9d4f41546 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -674,11 +674,17 @@ sub bz_add_field_tables {
     
     $self->_bz_add_field_table($field->name,
                                 $self->_bz_schema->FIELD_TABLE_SCHEMA);
-    if ( $field->type == FIELD_TYPE_MULTI_SELECT ) {
-        $self->_bz_add_field_table('bug_' . $field->name,
-                $self->_bz_schema->MULTI_SELECT_VALUE_TABLE);
+    if ($field->type == FIELD_TYPE_MULTI_SELECT) {
+        my $ms_table = "bug_" . $field->name;
+        $self->_bz_add_field_table($ms_table,
+            $self->_bz_schema->MULTI_SELECT_VALUE_TABLE);
+
+        $self->bz_add_fk($ms_table, 'bug_id', {TABLE => 'bugs',
+                                               COLUMN => 'bug_id',
+                                               DELETE => 'CASCADE'});
+        $self->bz_add_fk($ms_table, 'value',  {TABLE  => $field->name,
+                                               COLUMN => 'value'});
     }
-
 }
 
 sub bz_drop_field_tables {
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 4cfb62eb4..06a956222 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -1401,6 +1401,7 @@ use constant FIELD_TABLE_SCHEMA => {
     ],
 };
 
+# Foreign Keys are added in Bugzilla::DB::bz_add_field_tables
 use constant MULTI_SELECT_VALUE_TABLE => {
     FIELDS => [
         bug_id => {TYPE => 'INT3', NOTNULL => 1},
diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm
index d1f1aadb5..54dbfc564 100644
--- a/Bugzilla/Install/DB.pm
+++ b/Bugzilla/Install/DB.pm
@@ -526,6 +526,9 @@ sub update_table_definitions {
     $dbh->bz_alter_column('series', 'query',
         { TYPE => 'MEDIUMTEXT', NOTNULL => 1 });
 
+    # Add FK to multi select field tables
+    _add_foreign_keys_to_multiselects();
+    
     ################################################################
     # New --TABLE-- changes should go *** A B O V E *** this point #
     ################################################################
@@ -2993,6 +2996,25 @@ sub _check_content_length {
     }
 }
 
+sub _add_foreign_keys_to_multiselects {
+    my $dbh = Bugzilla->dbh;
+
+    my $names = $dbh->selectcol_arrayref(
+        'SELECT name 
+           FROM fielddefs 
+          WHERE type = ' . FIELD_TYPE_MULTI_SELECT);
+
+    foreach my $name (@$names) {
+        $dbh->bz_add_fk("bug_$name", "bug_id", {TABLE  => 'bugs',
+                                                COLUMN => 'bug_id',
+                                                DELETE => 'CASCADE',});
+                                                
+        $dbh->bz_add_fk("bug_$name", "value", {TABLE  => $name,
+                                               COLUMN => 'value',
+                                               DELETE => 'RESTRICT',});
+    }
+}
+
 sub _populate_bugs_fulltext {
     my $dbh = Bugzilla->dbh;
     my $fulltext = $dbh->selectrow_array('SELECT 1 FROM bugs_fulltext '
-- 
cgit v1.2.3-24-g4f1b