From 481ca4c3225b6df1140224ee5e20342d049d632f Mon Sep 17 00:00:00 2001
From: Lari Taskula <lari.taskula@hypernova.fi>
Date: Tue, 31 Mar 2020 16:24:53 +0000
Subject: [PATCH] Bug 25089: Add a new checkout_type to circulation_rules table

To add:
1. perl installer/data/mysql/updatedatabase.pl

Sponsored-by: The National Library of Finland
---
 ...dd_checkout_type_to_circulation_rules.perl | 21 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 ++-
 2 files changed, 23 insertions(+), 1 deletion(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_25089_add_checkout_type_to_circulation_rules.perl

diff --git a/installer/data/mysql/atomicupdate/bug_25089_add_checkout_type_to_circulation_rules.perl b/installer/data/mysql/atomicupdate/bug_25089_add_checkout_type_to_circulation_rules.perl
new file mode 100644
index 0000000000..74fad3c4ff
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_25089_add_checkout_type_to_circulation_rules.perl
@@ -0,0 +1,21 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+
+    if ( ! column_exists( 'circulation_rules', 'checkout_type' ) ) {
+        $dbh->do( q|
+            ALTER TABLE circulation_rules ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') DEFAULT NULL AFTER itemtype;
+        | );
+
+        if ( index_exists( 'circulation_rules', 'branchcode' ) ) {
+            # we need to drop this forgein key before updating our unique index
+            $dbh->do( "ALTER TABLE `circulation_rules` DROP FOREIGN KEY circ_rules_ibfk_1;" );
+            $dbh->do( "ALTER TABLE `circulation_rules` DROP INDEX `branchcode`;" );
+            $dbh->do( "ALTER TABLE `circulation_rules` ADD CONSTRAINT circ_rules_ibfk_1 FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE" );
+            $dbh->do( "ALTER TABLE `circulation_rules` ADD UNIQUE INDEX (`branchcode`,`categorycode`,`itemtype`,`checkout_type`,`rule_name`)" );
+        }
+    }
+
+    # Always end with this (adjust the bug info)
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug 24101 - Add new circulation rule scope checkout_type )\n";
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c52e349934..12da2ad7f9 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -4317,6 +4317,7 @@ CREATE TABLE `circulation_rules` (
   `branchcode` varchar(10) NULL default NULL,
   `categorycode` varchar(10) NULL default NULL,
   `itemtype` varchar(10) NULL default NULL,
+  `checkout_type` ENUM('CHECKOUT', 'ONSITE') NULL default NULL,
   `rule_name` varchar(32) NOT NULL,
   `rule_value` varchar(32) NOT NULL,
   PRIMARY KEY (`id`),
@@ -4324,7 +4325,7 @@ CREATE TABLE `circulation_rules` (
   CONSTRAINT `circ_rules_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `circ_rules_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
   KEY `rule_name` (`rule_name`),
-  UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`)
+  UNIQUE (`branchcode`,`categorycode`,`itemtype`,`checkout_type`,`rule_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 --
-- 
2.17.1