Attachment 99273 Details for Bug 23727 – Bug 23727: Add new columns

[patch] Bug 23727: Add new columns

Bug-23727-Add-new-columns.patch (text/plain), 6.47 KB, created by Kyle M Hall (khall) on 2020-02-19 20:17:37 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2020-02-19 20:17:37 UTC

Size: 6.47 KB

patch

obsolete

>From b925c847ee6572817c8c9ee8352615d1a2372a85 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>Date: Fri, 14 Feb 2020 08:41:28 -0500
>Subject: [PATCH] Bug 23727: Add new columns
>
>---
> .../data/mysql/atomicupdate/bug_23727.perl    | 69 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  8 +++
> 2 files changed, 77 insertions(+)
> create mode 100644 installer/data/mysql/atomicupdate/bug_23727.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_23727.perl b/installer/data/mysql/atomicupdate/bug_23727.perl
>new file mode 100644
>index 0000000000..855d0e1908
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_23727.perl
>@@ -0,0 +1,69 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+    if ( !column_exists( 'course_items', 'itype_enabled' ) ) {
>+        $dbh->do(q{
>+            ALTER TABLE course_items
>+            ADD COLUMN itype_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER itype,
>+            ADD COLUMN ccode_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER ccode,
>+            ADD COLUMN holdingbranch_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER holdingbranch,
>+            ADD COLUMN location_enabled tinyint(1) NOT NULL DEFAULT 0 AFTER location,
>+            ADD COLUMN itype_storage varchar(10) DEFAULT NULL AFTER itype_enabled,
>+            ADD COLUMN ccode_storage varchar(80) DEFAULT NULL AFTER ccode_enabled,
>+            ADD COLUMN holdingbranch_storage varchar(10) DEFAULT NULL AFTER ccode_enabled,
>+            ADD COLUMN location_storage varchar(80) DEFAULT NULL AFTER location_enabled
>+        });
>+
>+        my $item_level_items = C4::Context->preference('item-level_itypes');
>+        my $itype_field = $item_level_items ? 'i.itype' : 'bi.itemtype';
>+        $dbh->do(qq{
>+            UPDATE course_items ci
>+            LEFT JOIN items i USING ( itemnumber )
>+            LEFT JOIN biblioitems bi USING ( biblioitemnumber )
>+            SET
>+
>+            -- Assume the column is enabled if the course item is active and i.itype/bi.itemtype is set,
>+            -- or if the course item is not enabled and ci.itype is set
>+            ci.itype_enabled = IF( ci.enabled = 'yes', IF( $itype_field IS NULL, 0, 1 ), IF(  ci.itype IS NULL, 0, 1  ) ),
>+            ci.ccode_enabled = IF( ci.enabled = 'yes', IF( i.ccode IS NULL, 0, 1 ), IF(  ci.ccode IS NULL, 0, 1  ) ),
>+            ci.holdingbranch_enabled = IF( ci.enabled = 'yes', IF( i.holdingbranch IS NULL, 0, 1 ), IF(  ci.holdingbranch IS NULL, 0, 1  ) ),
>+            ci.location_enabled = IF( ci.enabled = 'yes', IF( i.location IS NULL, 0, 1 ), IF(  ci.location IS NULL, 0, 1  ) ),
>+
>+            -- If the course item is enabled, copy the value from the item.
>+            -- If the course item is not enabled, keep the existing value
>+            ci.itype = IF( ci.enabled = 'yes', $itype_field, ci.itype ),
>+            ci.ccode = IF( ci.enabled = 'yes', i.ccode, ci.ccode ),
>+            ci.holdingbranch = IF( ci.enabled = 'yes', i.holdingbranch, ci.holdingbranch ),
>+            ci.location = IF( ci.enabled = 'yes', i.location, ci.location ),
>+
>+            -- If the course is enabled, copy the value from the item to storage.
>+            -- If it is not enabled, copy the value from the course item to storage
>+            ci.itype_storage = IF( ci.enabled = 'no', $itype_field, ci.itype ),
>+            ci.ccode_storage = IF( ci.enabled = 'no', i.ccode, ci.ccode ),
>+            ci.holdingbranch_storage = IF( ci.enabled = 'no', i.holdingbranch, ci.holdingbranch ),
>+            ci.location_storage = IF( ci.enabled = 'no', i.location, ci.location );
>+        });
>+
>+        # Clean up the storage columns
>+        $dbh->do(q{
>+            UPDATE course_items SET
>+                itype_storage = NULL,
>+                ccode_storage = NULL,
>+                holdingbranch_storage = NULL,
>+                location_storage = NULL
>+            WHERE enabled = 'no';
>+        });
>+
>+        # Clean up the course enabled value columns
>+        $dbh->do(q{
>+            UPDATE course_items SET
>+                itype = IF( itype_enabled = 'no', NULL, itype ),
>+                ccode = IF( ccode_enabled = 'no', NULL, ccode ),
>+                holdingbranch = IF( holdingbranch_enabled = 'no', NULL, holdingbranch ),
>+                location = IF( location_enabled = 'no', NULL, location )
>+            WHERE enabled = 'no';
>+        });
>+    }
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 23727 - Editing course reserve items is broken)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index f3b965f23c..5737069fbb 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3791,9 +3791,17 @@ CREATE TABLE `course_items` (
>   `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id
>   `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve
>   `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional)
>+  `itype_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
>+  `itype_storage` varchar(10) DEFAULT NULL, -- a place to store the itype when item is on course reserve
>   `ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional)
>+  `ccode_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if ccode should be changed while on course reserve
>+  `ccode_storage` varchar(80) DEFAULT NULL, -- a place to store the ccode when item is on course reserve
>   `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional)
>+  `holdingbranch_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
>+  `holdingbranch_storage` varchar(10) DEFAULT NULL, -- a place to store the holdingbranch when item is on course reserve
>   `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional)
>+  `location_enabled` tinyint(1) NOT NULL DEFAULT 0, -- indicates if itype should be changed while on course reserve
>+  `location_storage` varchar(80) DEFAULT NULL, -- a place to store the location when the item is on course reserve
>   `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no'
>   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
>    PRIMARY KEY (`ci_id`),
>-- 
>2.21.1 (Apple Git-122.3)

Actions: View | Diff | Splinter Review

Attachments on bug 23727: 95104 | 98943 | 98944 | 98945 | 99269 | 99270 | 99271 | 99272 | 99273 | 99274 | 99275 | 99276 | 99277 | 99327 | 99328 | 99329 | 99330 | 99331 | 99332 | 99333 | 99334 | 99335 | 99336 | 103126 | 103127 | 103128 | 103131 | 103132 | 103133 | 103134 | 103135 | 103136 | 104782 | 104783 | 104784 | 104785 | 104786 | 104787