Bugzilla – Attachment 127215 Details for
Bug 29390
Authorised values: Add a few missing foreign keys
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 29390: Add a few missing FK constraints to authorised values
Bug-29390-Add-a-few-missing-FK-constraints-to-auth.patch (text/plain), 8.95 KB, created by
Marcel de Rooy
on 2021-11-02 12:33:01 UTC
(
hide
)
Description:
Bug 29390: Add a few missing FK constraints to authorised values
Filename:
MIME Type:
Creator:
Marcel de Rooy
Created:
2021-11-02 12:33:01 UTC
Size:
8.95 KB
patch
obsolete
>From 61a9dc43eaa0b8dcf8964944ddb5e649e780c00d Mon Sep 17 00:00:00 2001 >From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Date: Mon, 1 Nov 2021 10:56:13 +0000 >Subject: [PATCH] Bug 29390: Add a few missing FK constraints to authorised > values >Content-Type: text/plain; charset=utf-8 > >Test plan: >Run dbrev or new install. > >WORK IN PROGRESS -- POSTPONED 02-11-2021 >* Fix constraint names >* Fix table order >--- > .../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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 29390
:
127214
|
127215
|
128985
|
152730
|
152731
|
152732
|
152733
|
152734
|
155529
|
155530
|
155531
|
155532
|
155533