From 38e8e60b94aaffacd4e7082b6ea816feb37ecb0b Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 4 Apr 2022 13:20:37 +0000 Subject: [PATCH] Bug 30449: Add missing FK constraint on borrower_attribute_types Content-Type: text/plain; charset=utf-8 Old Koha databases probably have it, newer ones might not. See also BZ description. Test plan: [1} Check SHOW CREATE TABLE borrower_attribute_types to see if you have any index and FK constraint on category_code. [2] Run updatedatabase. [3] If you had category_code_fk, it should be replaced. [4] Remove index and constraint again using things like: alter table borrower_attribute_types drop constraint `borrower_attribute_types_ibfk_1`; alter table borrower_attribute_types drop index category_code; [5] Run updatedatabase. [6] You should have KEY category_code and FK borrower_attribute_types_ibfk_1. [7] Run updatedatabase. Idempotent, no changes. Signed-off-by: Marcel de Rooy --- .../data/mysql/atomicupdate/bug_30449.pl | 23 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +++- 2 files changed, 26 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_30449.pl diff --git a/installer/data/mysql/atomicupdate/bug_30449.pl b/installer/data/mysql/atomicupdate/bug_30449.pl new file mode 100755 index 0000000000..316f4016fa --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_30449.pl @@ -0,0 +1,23 @@ +use Modern::Perl; + +return { + bug_number => 30449, + description => "Check borrower_attribute_types FK constraint", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) { + $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| ); + if( index_exists('borrower_attribute_types', 'category_code_fk') ) { + $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| ); + } + } + if( !foreign_key_exists('borrower_attribute_types', 'borrower_attribute_types_ibfk_1') ) { + if( !index_exists('borrower_attribute_types', 'category_code') ) { + $dbh->do( q|ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)| ); + } + $dbh->do( q|ALTER TABLE borrower_attribute_types ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)| ); + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 69b3613963..0b3d219d6f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1123,7 +1123,9 @@ 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`), + KEY `category_code` (`category_code`), + CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.20.1