From 5828cf52aa133a0c509b3eb4ff9a606c0a1eeb0c Mon Sep 17 00:00:00 2001 From: Agustin Moyano Date: Tue, 21 Apr 2020 12:42:13 -0300 Subject: [PATCH] Bug 25260: Atomic update Sponsored-by: ByWater Solutions Sponsored-by: Theke Solutions --- .../data/mysql/atomicupdate/hold_union.perl | 264 ++++++++++++++++++ 1 file changed, 264 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/hold_union.perl diff --git a/installer/data/mysql/atomicupdate/hold_union.perl b/installer/data/mysql/atomicupdate/hold_union.perl new file mode 100644 index 0000000000..affaf861d8 --- /dev/null +++ b/installer/data/mysql/atomicupdate/hold_union.perl @@ -0,0 +1,264 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + + if( !TableExists( 'holds' ) ) { + $dbh->do( q| + CREATE TABLE `holds` ( + -- information related to holds in Koha + `id` INT(11) NOT NULL AUTO_INCREMENT, + -- primary key + `patron_id` INT(11) DEFAULT NULL, + -- foreign key from the borrowers table defining which patron this hold is for + `pickup_library_id` VARCHAR(10) DEFAULT NULL, + -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at + `created_date` DATE DEFAULT NULL, + -- the date the hold was placed + `biblio_id` INT(11) DEFAULT NULL, + -- foreign key from the biblio table defining which bib record this hold is on + `item_id` INT(11) DEFAULT NULL, + -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + `item_type` VARCHAR(10) DEFAULT NULL, + -- If record level hold, the optional itemtype of the item the patron is requesting + `item_level` TINYINT(1) NOT NULL DEFAULT 0, + -- Is the hold was placed at item level + `completed_date` DATE DEFAULT NULL, + -- the date this hold was completed (fulfilled or cancelled) + `notes` LONGTEXT, + -- notes related to this hold + `priority` SMALLINT(6) NOT NULL DEFAULT 1, + -- where in the queue the patron sits + `status` ENUM('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL, + -- the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') + `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + -- the date and time this hold was last updated + `waiting_date` DATE DEFAULT NULL, + -- the date the item was marked as waiting for the patron at the library + `expiration_date` DATE DEFAULT NULL, + -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) + `lowest_priority` TINYINT(1) NOT NULL DEFAULT 0, + -- If it was assigned the lowest priority + `suspended` TINYINT(1) NOT NULL DEFAULT 0, + -- If it is suspended + `suspended_until_date` DATETIME NULL DEFAULT NULL, + -- Date until it is suspended + `completed` TINYINT(1) NOT NULL DEFAULT 0, + -- If it has been completed (i.e. either 'fulfilled' or 'cancelled') + PRIMARY KEY (`id`), + KEY `priority_status_idx` (`priority`, `status`), + KEY `patron_id` (`patron_id`), + KEY `biblio_id` (`biblio_id`), + KEY `item_id` (`item_id`), + KEY `pickup_library_id` (`pickup_library_id`), + KEY `item_type` (`item_type`), + CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + |); + + $dbh->do(q| + INSERT INTO holds ( + id, + patron_id, + created_date, + biblio_id, + pickup_library_id, + completed_date, + notes, + priority, + status, + timestamp, + item_id, + waiting_date, + expiration_date, + lowest_priority, + suspended, + suspended_until_date, + item_type, + item_level, + completed + ) + SELECT * + FROM ( + SELECT reserve_id, + borrowernumber, + reservedate, + biblionumber, + branchcode, + NULL, + reservenotes, + priority, + CASE + WHEN found = 'W' THEN + 'waiting' + WHEN found = 'T' THEN + 'in_transit' + ELSE + 'placed' + END, + timestamp, + itemnumber, + waitingdate, + expirationdate, + lowestPriority, + suspend, + suspend_until, + itemtype, + item_level_hold, + 0 + FROM reserves + UNION + SELECT o.reserve_id, + o.borrowernumber, + o.reservedate, + o.biblionumber, + o.branchcode, + o.timestamp, + o.reservenotes, + o.priority, + CASE + WHEN o.found = 'F' THEN + 'fulfilled' + ELSE + 'cancelled' + END, + o.timestamp, + o.itemnumber, + o.waitingdate, + o.expirationdate, + o.lowestPriority, + o.suspend, + o.suspend_until, + o.itemtype, + o.item_level_hold, + 1 AS completed + FROM old_reserves o + LEFT JOIN + reserves r + ON o.reserve_id = r.reserve_id + WHERE r.reserve_id is null + ) reserves + ORDER BY reservedate + |); + + $dbh->do(q| + ALTER TABLE club_holds_to_patron_holds + DROP FOREIGN KEY clubs_holds_paton_holds_ibfk_3 + |); + + $dbh->do(q| + ALTER TABLE club_holds_to_patron_holds + ADD CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE + |); + + } + + if(TableExists( 'holds' ) && TableExists('reserves')) { + $dbh->do(q| + DELETE FROM res USING `reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id + |); + + $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM reserves"); + $sth->execute(); + my $row = $sth->fetchrow_hashref; + if ($row->{count}) { + print "Warning to database administrator:\n" + . "There were $row->{count} reserves that could not be moved, please check '_reserves'.\n"; + $dbh->do("RENAME TABLE reserves TO _reserves"); + } else { + $dbh->do("DROP TABLE reserves"); + } + + + $dbh->do(q| + CREATE VIEW `reserves` + AS + SELECT + `id` AS `reserve_id`, + `borrowernumber`, + `created_date` AS `reservedate`, + `biblionumber`, + `branchcode`, + `completed_date` AS `cancellationdate`, + `notes` AS `reservenotes`, + `priority`, + CASE + when `status` = 'waiting' then + 'W' + when `status` = 'in_transit' then + 'T' + else + null + END AS `found`, + `timestamp`, + `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `itemtype`, + `item_level` AS `item_level_hold` + FROM `holds` + WHERE completed=0 + |); + } + + if(TableExists( 'holds' ) && TableExists('old_reserves')) { + $dbh->do(q| + DELETE FROM res USING `old_reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id + |); + + $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM old_reserves"); + $sth->execute(); + my $row = $sth->fetchrow_hashref; + if ($row->{count}) { + print "Warning to database administrator:\n" + . "There were $row->{count} old reserves that could not be moved, please check '_old_reserves'.\n"; + $dbh->do("RENAME TABLE old_reserves TO _old_reserves"); + } else { + $dbh->do("DROP TABLE old_reserves"); + } + + $dbh->do(q| + CREATE VIEW `old_reserves` + AS + SELECT + `id` AS `reserve_id`, + `borrowernumber`, + `created_date` AS `reservedate`, + `biblionumber`, + `branchcode`, + CASE + WHEN `status` = 'cancelled' THEN + `completed_date` + ELSE + NULL + END AS `cancellationdate`, + `notes` AS `reservenotes`, + `priority`, + CASE + WHEN `status` = 'fulfilled' THEN + 'F' + ELSE + NULL + END AS `found`, + `timestamp`, + `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `itemtype`, + `item_level` AS `item_level_hold` + FROM `holds` + WHERE completed=1 + |); + } + + # Always end with this (adjust the bug info) + NewVersion( $DBversion, 77777, "Add holds table and remove reserves and old_reserves tables"); +} -- 2.26.2