Attachment 132966 Details for Bug 30449 – Bug 30449: Add missing FK constraint on borrower_attribute_types

[patch] Bug 30449: Add missing FK constraint on borrower_attribute_types

Bug-30449-Add-missing-FK-constraint-on-borroweratt.patch (text/plain), 3.51 KB, created by Marcel de Rooy on 2022-04-05 12:42:32 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2022-04-05 12:42:32 UTC

Size: 3.51 KB

patch

obsolete

>From 38e8e60b94aaffacd4e7082b6ea816feb37ecb0b Mon Sep 17 00:00:00 2001
>From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>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 <m.de.rooy@rijksmuseum.nl>
>---
> .../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

Actions: View | Diff | Splinter Review

Attachments on bug 30449: 132965 | 132966 | 133777 | 133778 | 133779 | 133883 | 133884 | 133885