Attachment 102267 Details for Bug 25037 – Bug 25037: Convert issues.onsite_checkout to issues.checkout_type

[patch] Bug 25037: Convert issues.onsite_checkout to issues.checkout_type

Bug-25037-Convert-issuesonsitecheckout-to-issuesch.patch (text/plain), 6.48 KB, created by Lari Taskula on 2020-04-02 06:05:39 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Lari Taskula

Created: 2020-04-02 06:05:39 UTC

Size: 6.48 KB

patch

obsolete

>From cf77fbb7ff6dd482f5780851b9044e0c4efddf58 Mon Sep 17 00:00:00 2001
>From: Lari Taskula <lari.taskula@hypernova.fi>
>Date: Wed, 1 Apr 2020 17:28:11 +0000
>Subject: [PATCH] Bug 25037: Convert issues.onsite_checkout to
> issues.checkout_type
>
>To test:
>1. perl installer/data/mysql/updatedatabase.pl
>---
> ...vert_onsite_checkout_to_checkout_type.perl | 53 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        | 10 ++--
> 2 files changed, 60 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..c54ae89ade
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl
>@@ -0,0 +1,53 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    # ISSUES
>+    # Convert issues.onsite_checkout to checkout_type => null / 'ONSITE'
>+    if ( ! column_exists( 'issues', 'checkout_type' ) ) {
>+        $dbh->do( q|
>+            ALTER TABLE issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT NULL AFTER onsite_checkout;
>+        | );
>+    }
>+    if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) {
>+        $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;
>+        | );
>+    }
>+
>+    # 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 / 'ONSITE'
>+    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='ONSITE' 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=ONSITE, delete issues.onsite_checkout column )\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 4308bcdc21..53ef66cee1 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 NULL, -- 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,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 NULL, -- 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 +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

Actions: View | Diff | Splinter Review

Attachments on bug 25037: 102259 | 102260 | 102261 | 102262 | 102263 | 102264 | 102265 | 102266 | 102267 | 102268 | 102269 | 102272 | 102413 | 102414 | 102415 | 102416 | 102417 | 102418 | 102419 | 102420 | 102421 | 102422 | 102423 | 102424 | 102586 | 102587 | 102588 | 102589 | 102590 | 102591 | 102592 | 102593 | 102594 | 102595 | 102596 | 102597 | 102598 | 102599 | 102655 | 102656 | 102657 | 102658 | 102659 | 102660 | 102661 | 102662 | 102663 | 102664 | 102665 | 102666 | 102675 | 102676 | 104267 | 104268 | 105638 | 105780 | 105781 | 105782 | 105783 | 105784