summaryrefslogtreecommitdiffstats
path: root/Bugzilla/Install
diff options
context:
space:
mode:
authormkanat%bugzilla.org <>2007-03-10 05:13:09 +0100
committermkanat%bugzilla.org <>2007-03-10 05:13:09 +0100
commit3bc6ea42732020b00fef53a9b556e4a37e591bd7 (patch)
tree8de3ef7295c5e0345f8981fd32e08b268015e132 /Bugzilla/Install
parent3a943fe055afe25c540698b0417854bd3f5b5ef4 (diff)
downloadbugzilla-3bc6ea42732020b00fef53a9b556e4a37e591bd7.tar.gz
bugzilla-3bc6ea42732020b00fef53a9b556e4a37e591bd7.tar.xz
Bug 347439: Implement support for referential integrity in Bugzilla::DB::Schema and implement it on profiles_activity
Patch By Max Kanat-Alexander <mkanat@bugzilla.org> (module owner) a=mkanat
Diffstat (limited to 'Bugzilla/Install')
-rw-r--r--Bugzilla/Install/DB.pm47
1 files changed, 47 insertions, 0 deletions
diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm
index 00032d15b..5e6401828 100644
--- a/Bugzilla/Install/DB.pm
+++ b/Bugzilla/Install/DB.pm
@@ -49,6 +49,39 @@ sub indicate_progress {
}
}
+# This is used before adding a foreign key to a column, to make sure
+# that the database won't fail adding the key.
+sub check_references {
+ my ($table, $column, $foreign_table, $foreign_column) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ my $bad_values = $dbh->selectcol_arrayref(
+ "SELECT DISTINCT $table.$column
+ FROM $table LEFT JOIN $foreign_table
+ ON $table.$column = $foreign_table.$foreign_column
+ WHERE $foreign_table.$foreign_column IS NULL");
+
+ if (@$bad_values) {
+ my $values = join(', ', @$bad_values);
+ print <<EOT;
+
+ERROR: There are invalid values for the $column column in the $table
+table. (These values do not exist in the $foreign_table table, in the
+$foreign_column column.)
+
+Before continuing with checksetup, you will need to fix these values,
+either by deleting these rows from the database, or changing the values
+of $column in $table to point to valid values in $foreign_table.$foreign_column.
+
+The bad values from the $table.$column column are:
+$values
+
+EOT
+ # I just picked a number above 2, to be considered "abnormal exit."
+ exit 3;
+ }
+}
+
# NOTE: This is NOT the function for general table updates. See
# update_table_definitions for that. This is only for the fielddefs table.
sub update_fielddefs_definition {
@@ -519,6 +552,20 @@ sub update_table_definitions {
$dbh->bz_add_column('milestones', 'id',
{TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
+ # Referential Integrity begins here
+ check_references('profiles_activity', 'userid', 'profiles', 'userid');
+ $dbh->bz_alter_column('profiles_activity', 'userid',
+ {TYPE => 'INT3', NOTNULL => 1, REFERENCES =>
+ {TABLE => 'profiles', COLUMN => 'userid', DELETE => 'CASCADE'}});
+ check_references('profiles_activity', 'who', 'profiles', 'userid');
+ $dbh->bz_alter_column('profiles_activity', 'who',
+ {TYPE => 'INT3', NOTNULL => 1, REFERENCES =>
+ {TABLE => 'profiles', COLUMN => 'userid'}});
+ check_references('profiles_activity', 'fieldid', 'fielddefs', 'id');
+ $dbh->bz_alter_column('profiles_activity', 'fieldid',
+ {TYPE => 'INT3', NOTNULL => 1, REFERENCES =>
+ {TABLE => 'fielddefs', COLUMN => 'id'}});
+
################################################################
# New --TABLE-- changes should go *** A B O V E *** this point #
################################################################