From c087a3b19712f3792d743622a219ea8c00094fb5 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 1 Nov 2021 10:56:13 +0000 Subject: [PATCH] Bug 29336: Add a few missing FK constraints to authorised values Content-Type: text/plain; charset=utf-8 Test plan: Run dbrev or new install. Signed-off-by: Marcel de Rooy --- .../data/mysql/atomicupdate/bug_29336_2.pl | 58 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 21 ++++--- 2 files changed, 72 insertions(+), 7 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_29336_2.pl diff --git a/installer/data/mysql/atomicupdate/bug_29336_2.pl b/installer/data/mysql/atomicupdate/bug_29336_2.pl new file mode 100755 index 0000000000..7e1d3b78eb --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_29336_2.pl @@ -0,0 +1,58 @@ +use Modern::Perl; +use Data::Dumper; + +return { + bug_number => 29336, + description => "Add missing FK constraints to authorised value categories", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + my %fix_tables = ( + 'additional_fields' => 'authorised_value_category', + 'auth_subfield_structure' => 'authorised_value' + 'auth_tag_structure' => 'authorised_value' + 'borrower_attribute_types' => 'authorised_value_category', + 'club_template_enrollment_fields' => 'authorised_value_category' + 'club_template_fields' => 'authorised_value_category', + 'marc_tag_structure' => 'authorised_value', + ); + + my $sql; + foreach my $table ( keys %fix_tables ) { + my $field = $fix_tables{$table}; + next if foreign_key_exists( $table, undef, $field ); + + # Before setting the FK constraint we need to correct wrong links + $sql = qq| + SELECT DISTINCT $field FROM $table t1 + LEFT JOIN authorised_value_categories avc ON t1.$field=avc.category_name + WHERE t1.$field IS NOT NULL AND avc.category_name IS NULL + |; + my @bad_values = map { $_->[0] } @{$dbh->selectall_arrayref($sql)}; + if( @bad_values ) { + say $out "Found the following bad authorised values categories in $table: ". join(',', @bad_values); + if( $table eq 'additional_fields' ) { + say $out "For this reason we will not set the foreign key constraint on additional_fields. Please fix manually."; + next; + } + $sql = qq| + UPDATE $table t1 + LEFT JOIN authorised_value_categories avc ON t1.$field=avc.category_name + SET t1.$field=NULL + WHERE avc.category_name IS NULL + |; + $dbh->do($sql); + say $out "Have set these values in $table to NULL\n"; + } + + # Ready to set constraint + $sql = qq| + ALTER TABLE $table + ADD CONSTRAINT FOREIGN KEY ($field) + REFERENCES `authorised_value_categories` (`category_name`) + ON DELETE CASCADE ON UPDATE CASCADE + |; + $dbh->do($sql); + } + }, +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c27587bae1..9869209125 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -214,7 +214,8 @@ CREATE TABLE `additional_fields` ( `marcfield` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'contains the marc field to copied into the record', `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?', PRIMARY KEY (`id`), - UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191)) + UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191)), + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -836,7 +837,8 @@ CREATE TABLE `auth_subfield_structure` ( `display_order` int(2) NOT NULL DEFAULT 0, PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`), KEY `tab` (`authtypecode`,`tab`), - CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -856,7 +858,8 @@ CREATE TABLE `auth_tag_structure` ( `mandatory` tinyint(4) NOT NULL DEFAULT 0, `authorised_value` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '#TODO This is a AV category!', PRIMARY KEY (`authtypecode`,`tagfield`), - CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -1091,7 +1094,8 @@ CREATE TABLE `borrower_attribute_types` ( `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if this field is copied to anonymized_borrower_attributes (1 for yes, 0 for no)', `mandatory` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if the attribute is mandatory or not', PRIMARY KEY (`code`), - KEY `auth_val_cat_idx` (`authorised_value_category`) + KEY `auth_val_cat_idx` (`authorised_value_category`), + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -1871,7 +1875,8 @@ CREATE TABLE `club_template_enrollment_fields` ( `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `club_template_id` (`club_template_id`), - CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -1890,7 +1895,8 @@ CREATE TABLE `club_template_fields` ( `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `club_template_id` (`club_template_id`), - CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -3505,7 +3511,8 @@ CREATE TABLE `marc_tag_structure` ( `ind1_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `ind2_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `frameworkcode` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - PRIMARY KEY (`frameworkcode`,`tagfield`) + PRIMARY KEY (`frameworkcode`,`tagfield`), + CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.20.1