From 313cf1f5feb3c57c403c5d0692b9fbb7f086c1a8 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 "OS" Else checkout_type becomes "C" To test: 1. perl installer/data/mysql/updatedatabase.pl Sponsored-by: The National Library of Finland --- ...vert_onsite_checkout_to_checkout_type.perl | 59 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 10 +++- 2 files changed, 66 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl diff --git a/installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl b/installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl new file mode 100644 index 0000000000..8038be5755 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl @@ -0,0 +1,59 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # ISSUES + # Convert issues.onsite_checkout to checkout_type => null / 'OS' + if ( ! column_exists( 'issues', 'checkout_type' ) ) { + $dbh->do( q| + ALTER TABLE issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT 'C' AFTER onsite_checkout; + | ); + } + if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) { + $dbh->do( q| + UPDATE issues SET checkout_type='C' WHERE onsite_checkout<>1; + | ); + $dbh->do( q| + UPDATE issues SET checkout_type='OS' WHERE onsite_checkout=1; + | ); + } + if ( column_exists( 'issues', 'onsite_checkout' ) ) { + $dbh->do( q| + ALTER TABLE issues DROP COLUMN onsite_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 SET NULL" ); + } + + # OLD_ISSUES + # Convert old_issues.onsite_checkout to checkout_type => null / 'OS' + if ( ! column_exists( 'old_issues', 'checkout_type' ) ) { + $dbh->do( q| + ALTER TABLE old_issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT NULL 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='C' WHERE onsite_checkout<>1; + | ); + $dbh->do( q| + UPDATE old_issues SET checkout_type='OS' WHERE onsite_checkout=1; + | ); + } + if ( column_exists( 'old_issues', 'onsite_checkout' ) ) { + $dbh->do( q| + ALTER TABLE old_issues DROP COLUMN onsite_checkout; + | ); + } + + # 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 SET NULL" ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 25037 - Add issues.checkout_type column, convert issues.onsite_checkout=1 to issues.checkout_type=OS, delete issues.onsite_checkout column )\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4308bcdc21..dbb4a2acc4 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` varchar(80) default "C", -- checkout type, default C means normal checkout `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,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 DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -1670,7 +1671,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` varchar(80) default "C", -- checkout type, default C means normal checkout `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 +1684,9 @@ 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 + CONSTRAINT `old_issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) + ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.17.1