From 8da992ec86ac417f3216945aca81aa53e6084e4f Mon Sep 17 00:00:00 2001 From: Lari Taskula Date: Wed, 1 Apr 2020 17:28:11 +0000 Subject: [PATCH] Bug 25037: Convert issues.onsite_checkout to issues.checkout_type If onsite_checkout=1, checkout_type becomes "ONSITE" Else checkout_type becomes "CHECKOUT" To test: 1. perl installer/data/mysql/updatedatabase.pl Sponsored-by: The National Library of Finland --- ...vert_onsite_checkout_to_checkout_type.perl | 49 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 7 +-- 2 files changed, 53 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_25037_convert_onsite_checkout_to_checkout_type.perl diff --git a/installer/data/mysql/atomicupdate/bug_25037_convert_onsite_checkout_to_checkout_type.perl b/installer/data/mysql/atomicupdate/bug_25037_convert_onsite_checkout_to_checkout_type.perl new file mode 100644 index 0000000000..f6b0e251d7 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_25037_convert_onsite_checkout_to_checkout_type.perl @@ -0,0 +1,49 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # ISSUES + # Convert issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE' + if ( ! column_exists( 'issues', 'checkout_type' ) ) { + $dbh->do( q| + ALTER TABLE issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout; + | ); + } + if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) { + $dbh->do( q| + UPDATE issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1; + | ); + $dbh->do( q| + UPDATE issues SET checkout_type='ONSITE' WHERE onsite_checkout=1; + | ); + } + if ( column_exists( 'issues', 'onsite_checkout' ) ) { + $dbh->do( q| + ALTER TABLE issues DROP COLUMN onsite_checkout; + | ); + } + + # OLD_ISSUES + # Convert old_issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE' + if ( ! column_exists( 'old_issues', 'checkout_type' ) ) { + $dbh->do( q| + ALTER TABLE old_issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout; + | ); + } + if ( column_exists( 'old_issues', 'onsite_checkout' ) && column_exists( 'old_issues', 'checkout_type' ) ) { + $dbh->do( q| + UPDATE old_issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1; + | ); + $dbh->do( q| + UPDATE old_issues SET checkout_type='ONSITE' WHERE onsite_checkout=1; + | ); + } + if ( column_exists( 'old_issues', 'onsite_checkout' ) ) { + $dbh->do( q| + ALTER TABLE old_issues DROP COLUMN onsite_checkout; + | ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 25037 - Convert issues.onsite_checkout to issues.checkout_type column)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index af10ac5317..68ad01f9bb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1638,7 +1638,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched `issuedate` datetime default NULL, -- date the item was checked out or issued - `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag + `checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type `note` LONGTEXT default NULL, -- issue note text `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss) `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null @@ -1649,7 +1649,7 @@ 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, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -1670,7 +1670,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched `issuedate` datetime default NULL, -- date the item was checked out or issued - `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag + `checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type `note` LONGTEXT default NULL, -- issue note text `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss) `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null @@ -1683,6 +1683,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r 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 + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.17.1