From f144b4e8853468cc991252f54d9eb3051832a02f Mon Sep 17 00:00:00 2001 From: Lari Taskula Date: Wed, 1 Apr 2020 14:29:43 +0000 Subject: [PATCH] Bug 25103: Add authorised value for checkout types This commit adds an authorised value category "CHECKOUT_TYPE" and an authorised value "ONSITE" (on-site) and "CHECKOUT" (normal checkout). To test: 1. perl installer/data/mysql/updatedatabase.pl Sponsored-by: The National Library of Finland --- ..._25103_dynamically_add_checkout_types.perl | 45 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 7 ++- .../data/mysql/mandatory/auth_val_cat.sql | 4 ++ 3 files changed, 54 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_25103_dynamically_add_checkout_types.perl diff --git a/installer/data/mysql/atomicupdate/bug_25103_dynamically_add_checkout_types.perl b/installer/data/mysql/atomicupdate/bug_25103_dynamically_add_checkout_types.perl new file mode 100644 index 0000000000..b0721695ec --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_25103_dynamically_add_checkout_types.perl @@ -0,0 +1,45 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + if ($dbh->do("SELECT 1 FROM authorised_value_categories WHERE category_name='CHECKOUT_TYPE'") == 0) { + # Add authorised value category "CHECKOUT_TYPE" + $dbh->do( q| + INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES ('CHECKOUT_TYPE'); + | ); + } + + if ($dbh->do("SELECT 1 FROM authorised_values WHERE authorised_value='ONSITE'") == 0) { + # Add authorised value "ONSITE" under "CHECKOUT_TYPE" category + $dbh->do( q| + INSERT IGNORE INTO authorised_values(category, authorised_value, lib, lib_opac) VALUES ('CHECKOUT_TYPE', 'ONSITE', 'On-site checkout', 'On-site checkout'); + | ); + } + if ($dbh->do("SELECT 1 FROM authorised_values WHERE authorised_value='CHECKOUT'") == 0) { + # Add authorised value "CHECKOUT" under "CHECKOUT_TYPE" category + $dbh->do( q| + INSERT IGNORE INTO authorised_values(category, authorised_value, lib, lib_opac) VALUES ('CHECKOUT_TYPE', 'CHECKOUT', 'Normal checkout', 'Normal checkout'); + | ); + } + + $dbh->do( q| + ALTER TABLE issues MODIFY COLUMN checkout_type VARCHAR(80) NOT NULL DEFAULT 'CHECKOUT'; + | ); + + $dbh->do( q| + ALTER TABLE old_issues MODIFY COLUMN checkout_type VARCHAR(80) NOT NULL DEFAULT 'CHECKOUT'; + | ); + + # Add a foreign key between old_issues.checkout_type and authorised_values.authorised_value + if ( !foreign_key_exists( 'issues', 'issues_ctfk' ) ) { + $dbh->do( "ALTER TABLE issues ADD CONSTRAINT issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE RESTRICT" ); + } + + # Add a foreign key between issues.checkout_type and authorised_values.authorised_value + if ( !foreign_key_exists( 'old_issues', 'old_issues_ctfk' ) ) { + $dbh->do( "ALTER TABLE old_issues ADD CONSTRAINT old_issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE RESTRICT" ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 25103 - Dynamically add checkout types\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c52e349934..71827b856f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1649,7 +1649,8 @@ CREATE TABLE `issues` ( -- information related to check outs or issues KEY `branchcode_idx` (`branchcode`), KEY `bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, - CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE + CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE, + CONSTRAINT `issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -1682,7 +1683,9 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) - ON DELETE SET NULL ON UPDATE SET NULL + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) + ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- diff --git a/installer/data/mysql/mandatory/auth_val_cat.sql b/installer/data/mysql/mandatory/auth_val_cat.sql index cca989c9df..2e8775929e 100644 --- a/installer/data/mysql/mandatory/auth_val_cat.sql +++ b/installer/data/mysql/mandatory/auth_val_cat.sql @@ -65,3 +65,7 @@ INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES -- For Claims returned INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES ('RETURN_CLAIM_RESOLUTION'); + +-- For Circulation types +INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES + ('CHECKOUT_TYPE'); -- 2.17.1