Bugzilla – Attachment 152731 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.92 KB, created by
Marcel de Rooy
on 2023-06-27 12:53:32 UTC
(
hide
)
Description:
Bug 29390: Add a few missing FK constraints to authorised values
Filename:
MIME Type:
Creator:
Marcel de Rooy
Created:
2023-06-27 12:53:32 UTC
Size:
8.92 KB
patch
obsolete
>From 4bd952121915a2da4906c08d72c8dfcdf7ea0a5b 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: >See next patch. Resolving syntax error here. >Run dbrev or new install. > >Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >--- > .../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..5629c261a8 >--- /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 ( sort 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 ${table}_authorised_value_categories_fk 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 3f666d2df9..5de15b00f3 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -248,7 +248,8 @@ CREATE TABLE `additional_fields` ( > `marcfield_mode` enum('get','set') NOT NULL DEFAULT 'get' COMMENT 'mode of operation (get or set) for marcfield', > `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 `additional_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `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 */; > >@@ -921,7 +922,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 `auth_subfield_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `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 */; > >@@ -941,7 +943,8 @@ CREATE TABLE `auth_tag_structure` ( > `mandatory` tinyint(4) NOT NULL DEFAULT 0, > `authorised_value` varchar(32) DEFAULT NULL, > 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 `auth_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `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 */; > >@@ -1183,7 +1186,8 @@ CREATE TABLE `borrower_attribute_types` ( > PRIMARY KEY (`code`), > KEY `auth_val_cat_idx` (`authorised_value_category`), > KEY `category_code` (`category_code`), >- CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) >+ CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`), >+ CONSTRAINT `borrower_attribute_types_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `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 */; > >@@ -1999,7 +2003,8 @@ CREATE TABLE `club_template_enrollment_fields` ( > `authorised_value_category` varchar(32) 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 `club_template_enrollment_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `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 */; > >@@ -2018,7 +2023,8 @@ CREATE TABLE `club_template_fields` ( > `authorised_value_category` varchar(32) 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 `club_template_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `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 */; > >@@ -4147,7 +4153,8 @@ CREATE TABLE `marc_tag_structure` ( > `ind1_defaultvalue` varchar(1) NOT NULL DEFAULT '', > `ind2_defaultvalue` varchar(1) NOT NULL DEFAULT '', > `frameworkcode` varchar(4) NOT NULL DEFAULT '', >- PRIMARY KEY (`frameworkcode`,`tagfield`) >+ PRIMARY KEY (`frameworkcode`,`tagfield`), >+ CONSTRAINT `marc_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `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.30.2
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