summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authormkanat%bugzilla.org <>2008-07-01 16:42:07 +0200
committermkanat%bugzilla.org <>2008-07-01 16:42:07 +0200
commitf1f87e08fc375fd3195b1defa15e971b0b39a141 (patch)
treeaa61b499fef9b79fc09a8d01e5f62c43b670c9aa
parent6ebbe174496b4f39a0737ec5febbaf78422e0856 (diff)
downloadbugzilla-f1f87e08fc375fd3195b1defa15e971b0b39a141.tar.gz
bugzilla-f1f87e08fc375fd3195b1defa15e971b0b39a141.tar.xz
Bug 442863: ON DELETE CASCADE Foreign Keys should automatically delete invalid values during checksetup
Patch By Max Kanat-Alexander (module owner) a=mkanat
-rw-r--r--Bugzilla/DB.pm59
-rw-r--r--template/en/default/global/messages.html.tmpl17
2 files changed, 56 insertions, 20 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 07b2c5fe9..b23c865c1 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -501,8 +501,7 @@ sub bz_add_fk {
my $col_def = $self->bz_column_info($table, $column);
if (!$col_def->{REFERENCES}) {
- $self->_check_references($table, $column, $def->{TABLE},
- $def->{COLUMN});
+ $self->_check_references($table, $column, $def);
print get_text('install_fk_add',
{ table => $table, column => $column, fk => $def })
. "\n" if Bugzilla->usage_mode == USAGE_MODE_CMDLINE;
@@ -1205,7 +1204,9 @@ sub _bz_populate_enum_table {
# 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 ($self, $table, $column, $foreign_table, $foreign_column) = @_;
+ my ($self, $table, $column, $fk) = @_;
+ my $foreign_table = $fk->{TABLE};
+ my $foreign_column = $fk->{COLUMN};
my $bad_values = $self->selectcol_arrayref(
"SELECT DISTINCT $table.$column
@@ -1215,23 +1216,41 @@ sub _check_references {
AND $table.$column IS NOT 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;
+ my $delete_action = $fk->{DELETE} || '';
+ if ($delete_action eq 'CASCADE') {
+ $self->do("DELETE FROM $table WHERE $column IN ("
+ . join(',', ('?') x @$bad_values) . ")",
+ undef, @$bad_values);
+ if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
+ print "\n", get_text('install_fk_invalid_fixed',
+ { table => $table, column => $column,
+ foreign_table => $foreign_table,
+ foreign_column => $foreign_column,
+ 'values' => $bad_values, action => 'delete' }), "\n";
+ }
+ }
+ elsif ($delete_action eq 'SET NULL') {
+ $self->do("UPDATE $table SET $column = NULL
+ WHERE $column IN ("
+ . join(',', ('?') x @$bad_values) . ")",
+ undef, @$bad_values);
+ if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
+ print "\n", get_text('install_fk_invalid_fixed',
+ { table => $table, column => $column,
+ foreign_table => $foreign_table,
+ foreign_column => $foreign_column,
+ 'values' => $bad_values, action => 'null' }), "\n";
+ }
+ }
+ else {
+ print "\n", get_text('install_fk_invalid',
+ { table => $table, column => $column,
+ foreign_table => $foreign_table,
+ foreign_column => $foreign_column,
+ 'values' => $bad_values }), "\n";
+ # I just picked a number above 2, to be considered "abnormal exit"
+ exit 3
+ }
}
}
diff --git a/template/en/default/global/messages.html.tmpl b/template/en/default/global/messages.html.tmpl
index be0d851e6..fa66e273b 100644
--- a/template/en/default/global/messages.html.tmpl
+++ b/template/en/default/global/messages.html.tmpl
@@ -599,6 +599,23 @@
[% ELSIF message_tag == "install_fk_drop" %]
Dropping foreign key: [% table FILTER html %].[% column FILTER html %] -&gt; [% fk.TABLE FILTER html %].[% fk.COLUMN FILTER html %]...
+ [% ELSIF message_tag == "install_fk_invalid" %]
+ ERROR: There are invalid values for the [% column FILTER html %] column in the [% table FILTER html %]
+ table. (These values do not exist in the [% foreign_table FILTER html %] table, in the
+ [% foreign_column FILTER html %] 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 FILTER html %] in [% table FILTER html %] to point to valid values in [% foreign_table FILTER html %].[% foreign_column FILTER html %].
+
+ The bad values from the [% table FILTER html %].[% column FILTER html %] column are:
+ [%+ values.join(', ') FILTER html %]
+
+ [% ELSIF message_tag == "install_fk_invalid_fixed" %]
+ WARNING: There were invalid values in [% table FILTER html %].[% column FILTER html %]
+ that have been [% IF action == 'delete' %]deleted[% ELSE %]set to NULL[% END %]:
+ [%+ values.join(', ') FILTER html %]
+
[% ELSIF message_tag == "install_group_create" %]
Creating group [% name FILTER html %]...