Bugzilla – Attachment 103986 Details for
Bug 25260
Merge 'reserves' and 'old_reserves' into a new 'holds' table
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 25260: Atomic update
Bug-25260-Atomic-update.patch (text/plain), 11.77 KB, created by
Agustín Moyano
on 2020-04-29 21:11:26 UTC
(
hide
)
Description:
Bug 25260: Atomic update
Filename:
MIME Type:
Creator:
Agustín Moyano
Created:
2020-04-29 21:11:26 UTC
Size:
11.77 KB
patch
obsolete
>From 7fb7d1795fcbfbdebe004a4da9ecf9c7e86bb71c Mon Sep 17 00:00:00 2001 >From: Agustin Moyano <agustinmoyano@theke.io> >Date: Tue, 21 Apr 2020 12:42:13 -0300 >Subject: [PATCH] Bug 25260: Atomic update > >--- > .../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..be45cb4283 >--- /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 >+ `placing_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 >+ `completion_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, >+ placing_date, >+ biblio_id, >+ pickup_library_id, >+ completion_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`, >+ `placing_date` AS `reservedate`, >+ `biblionumber`, >+ `branchcode`, >+ `completion_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`, >+ `placing_date` AS `reservedate`, >+ `biblionumber`, >+ `branchcode`, >+ CASE >+ WHEN `status` = 'cancelled' THEN >+ `completion_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.25.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 25260
:
103984
|
103985
|
103986
|
103987
|
103988
|
103989
|
103990
|
103991
|
103992
|
103993
|
104053
|
104054
|
104055
|
104056
|
104057
|
117745
|
117746
|
117747
|
117748
|
117749
|
117750
|
117751
|
122761
|
122762
|
122763
|
122764
|
122765
|
122766
|
122767
|
122787
|
122788
|
122789
|
122790
|
122791
|
122792
|
122793
|
122976
|
122977
|
122978
|
122979
|
122980
|
122981
|
122982
|
122983
|
123071
|
123072
|
123073
|
123074
|
123075
|
123076
|
123077
|
123078
|
123079
|
123080
|
123257
|
123258
|
123259
|
123260
|
123261
|
123396