Attachment 78354 Details for Bug 18887 – Bug 18887: Fix DB structure issues

[patch] Bug 18887: Fix DB structure issues

Bug-18887-Fix-DB-structure-issues.patch (text/plain), 4.66 KB, created by Jonathan Druart on 2018-08-31 18:14:16 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2018-08-31 18:14:16 UTC

Size: 4.66 KB

patch

obsolete

>From d116ab26c36c6e4c36d9b8448713d85e7669bde1 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Tue, 24 Jul 2018 09:01:33 -0300
>Subject: [PATCH] Bug 18887: Fix DB structure issues
>
>- Make atomic update idempotent
>- Correct COLLAT (atomic update)
>- Add index on rule_name (kohastructure.sql)
>- Add rule_name to the unique constraint (kohastructure.sql)
>- Remove max_holds columns (kohastructure.sql)
>---
> installer/data/mysql/atomicupdate/bug_18887.perl | 34 +++++++++++++-----------
> installer/data/mysql/kohastructure.sql           |  7 +++--
> 2 files changed, 21 insertions(+), 20 deletions(-)
>
>diff --git a/installer/data/mysql/atomicupdate/bug_18887.perl b/installer/data/mysql/atomicupdate/bug_18887.perl
>index 07ee2d357f..85da1d207e 100644
>--- a/installer/data/mysql/atomicupdate/bug_18887.perl
>+++ b/installer/data/mysql/atomicupdate/bug_18887.perl
>@@ -1,22 +1,24 @@
> $DBversion = 'XXX';  # will be replaced by the RM
> if( CheckVersion( $DBversion ) ) {
> 
>-    $dbh->do(q{
>-        CREATE TABLE `circulation_rules` (
>-          `id` int(11) NOT NULL auto_increment,
>-          `branchcode` varchar(10) NULL default NULL,
>-          `categorycode` varchar(10) NULL default NULL,
>-          `itemtype` varchar(10) NULL default NULL,
>-          `rule_name` varchar(32) NOT NULL,
>-          `rule_value` varchar(32) NOT NULL,
>-          PRIMARY KEY (`id`),
>-          KEY `branchcode` (`branchcode`),
>-          KEY `categorycode` (`categorycode`),
>-          KEY `itemtype` (`itemtype`),
>-          KEY `rule_name` (`rule_name`),
>-          UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`)
>-        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>-    });
>+    unless (TableExists('circulation_rules')){
>+        $dbh->do(q{
>+            CREATE TABLE `circulation_rules` (
>+              `id` int(11) NOT NULL auto_increment,
>+              `branchcode` varchar(10) NULL default NULL,
>+              `categorycode` varchar(10) NULL default NULL,
>+              `itemtype` varchar(10) NULL default NULL,
>+              `rule_name` varchar(32) NOT NULL,
>+              `rule_value` varchar(32) NOT NULL,
>+              PRIMARY KEY (`id`),
>+              KEY `branchcode` (`branchcode`),
>+              KEY `categorycode` (`categorycode`),
>+              KEY `itemtype` (`itemtype`),
>+              KEY `rule_name` (`rule_name`),
>+              UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`)
>+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+        });
>+    }
> 
>     $dbh->do(q{
>         INSERT INTO circulation_rules ( branchcode, categorycode, itemtype, rule_name, rule_value )
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 7ff599b171..ddd25a6a5b 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -349,7 +349,6 @@ CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rule
>   `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode)
>   `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch
>   `maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch
>-  max_holds INT(4) NULL DEFAULT NULL, -- the maximum number of holds a patron may have at a time
>   PRIMARY KEY (`categorycode`, `branchcode`),
>   CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
>     ON DELETE CASCADE ON UPDATE CASCADE,
>@@ -366,7 +365,6 @@ CREATE TABLE `default_borrower_circ_rules` ( -- default checkout rules found und
>   `categorycode` VARCHAR(10) NOT NULL, -- patron category this rul
>   `maxissueqty` int(4) default NULL,
>   `maxonsiteissueqty` int(4) default NULL,
>-  max_holds INT(4) NULL DEFAULT NULL, -- the maximum number of holds a patron may have at a time
>   PRIMARY KEY (`categorycode`),
>   CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
>     ON DELETE CASCADE ON UPDATE CASCADE
>@@ -4218,8 +4216,9 @@ CREATE TABLE `circulation_rules` (
>   KEY `branchcode` (`branchcode`),
>   KEY `categorycode` (`categorycode`),
>   KEY `itemtype` (`itemtype`),
>-  UNIQUE (`branchcode`,`categorycode`,`itemtype`)
>-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>+  KEY `rule_name` (`rule_name`),
>+  UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`)
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 18887: 65233 | 65234 | 65235 | 65236 | 65237 | 67620 | 70986 | 70987 | 70988 | 70989 | 70990 | 70991 | 71031 | 71042 | 72218 | 72219 | 72220 | 72221 | 72222 | 72223 | 72224 | 77080 | 77081 | 77082 | 77083 | 77084 | 77085 | 77086 | 77087 | 77207 | 77208 | 77209 | 77210 | 77211 | 77212 | 77213 | 77214 | 77215 | 77218 | 77238 | 77649 | 77650 | 77651 | 77652 | 77653 | 77654 | 77655 | 77656 | 77657 | 77658 | 77659 | 78346 | 78347 | 78348 | 78349 | 78350 | 78351 | 78352 | 78353 | 78354 | 78355 | 78356 | 78674 | 78675 | 78676 | 78677 | 78678 | 78679 | 78680 | 78681 | 78682 | 78683 | 78684 | 78743 | 78744 | 78745 | 78746 | 78747 | 78748 | 78749 | 78750 | 78751 | 78752 | 78753 | 78879 | 79119 | 79626 | 79627 | 79628 | 79629 | 79630 | 79631 | 79632 | 79633 | 79634 | 79635 | 79636 | 79637 | 79638 | 79639 | 79640 | 79641 | 79761 | 79762 | 79763 | 79764 | 79765 | 79766 | 79767 | 79768 | 79769 | 79770 | 79771 | 79772 | 79773 | 79774 | 79775 | 79776 | 79778 | 79891