Attachment 94524 Details for Bug 23813 – Bug 23813: (bug 18925 follow-up) Remove invalid circ rules to avoid FK constraints to fail

[patch] Bug 23813: (bug 18925 follow-up) Remove invalid circ rules to avoid FK constraints to fail

Bug-23813-bug-18925-follow-up-Remove-invalid-circ-.patch (text/plain), 2.92 KB, created by Jonathan Druart on 2019-10-22 10:49:22 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2019-10-22 10:49:22 UTC

Size: 2.92 KB

patch

obsolete

>From 53a5ad82b534f15f2f065644d67ed5f9914b3a9f Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Tue, 22 Oct 2019 12:42:09 +0200
>Subject: [PATCH] Bug 23813: (bug 18925 follow-up) Remove invalid circ rules to
> avoid FK constraints to fail
>
>There is not FK constraints on the itemtype, categorycode and branchcode
>columns of the issuingrules table, because of the '*' value we use for
>default.
>We created the new table circulation_rules with those constraints, and
>when we moved the rules for maxissueqty, the INSERT failed.
>
>To avoid the FK constraints to fail we are going to delete the invalid
>rows before.
>
>It could lead to data lost, but this situation certainly comes from a
>bad data manipulation at some point of the history of the installation.
>
>Test plan:
>git reset --hard 1e4f442442844cfaf17f5b06f122f6259d5fee92 # before 18.12.00.020
>reset_all
>
>insert into branches(branchcode, branchname) values('rmme', 'rmme');
>insert into categories (categorycode, description) values ('rmme', 'rmme');
>insert into itemtypes (itemtype, description) values('rmme', 'rmme');
>
>Go to http://pro.kohadev.org/cgi-bin/koha/admin/smart-rules.pl
>Create a new rule for the patron category
>Create a new rule for the item type
>Copy all the default rule to the new library
>Create other rules for "Default checkout, hold policy by patron category" and "Default holds policy by item type" using this patron's category and itemtype.
>Same for patrons' categories and itemtypes you will not delete.
>
>delete from branches where branchcode="rmme";
>delete from categories where categorycode="rmme";
>delete from itemtypes where itemtype="rmme";
>
>git checkout master
>and execute the DB process.
>
>=> With this patch applied you will not get the error and the rules will
>be moved correctly.
>---
> installer/data/mysql/updatedatabase.pl | 11 +++++++++++
> 1 file changed, 11 insertions(+)
>
>diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
>index cf00117a53..493720bbf3 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -17542,6 +17542,17 @@ if( CheckVersion( $DBversion ) ) {
>     }
> 
>     if ( column_exists( 'issuingrules', 'maxissueqty' ) ) {
>+        # Cleaning invalid rules before, to avoid FK contraints to fail
>+        $dbh->do(q|
>+            DELETE FROM issuingrules WHERE categorycode != '*' AND categorycode NOT IN (SELECT categorycode FROM categories);
>+        |);
>+        $dbh->do(q|
>+            DELETE FROM issuingrules WHERE branchcode != '*' AND branchcode NOT IN (SELECT branchcode FROM branches);
>+        |);
>+        $dbh->do(q|
>+            DELETE FROM issuingrules WHERE itemtype != '*' AND itemtype NOT IN (SELECT itemtype FROM itemtypes);
>+        |);
>+
>         $dbh->do("
>             INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value )
>             SELECT IF(categorycode='*', NULL, categorycode),
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 23813: 94524 | 95020