Bugzilla – Attachment 117745 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), 13.96 KB, created by
Martin Renvoize (ashimema)
on 2021-03-04 16:18:35 UTC
(
hide
)
Description:
Bug 25260: Atomic update
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2021-03-04 16:18:35 UTC
Size:
13.96 KB
patch
obsolete
>From b291e7b27c4760eece97710beaf5289a446ea849 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 > >Sponsored-by: ByWater Solutions >Sponsored-by: Theke Solutions >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >--- > C4/Installer.pm | 18 +- > .../atomicupdate/bug_25260_holds_table.perl | 261 ++++++++++++++++++ > 2 files changed, 278 insertions(+), 1 deletion(-) > create mode 100644 installer/data/mysql/atomicupdate/bug_25260_holds_table.perl > >diff --git a/C4/Installer.pm b/C4/Installer.pm >index a004cb8150..a8d7e7e2b9 100644 >--- a/C4/Installer.pm >+++ b/C4/Installer.pm >@@ -30,7 +30,7 @@ use vars qw(@ISA @EXPORT); > BEGIN { > require Exporter; > @ISA = qw( Exporter ); >- push @EXPORT, qw( primary_key_exists foreign_key_exists index_exists column_exists TableExists); >+ push @EXPORT, qw( primary_key_exists foreign_key_exists index_exists column_exists TableExists table_is_view); > }; > > =head1 NAME >@@ -683,6 +683,22 @@ sub TableExists { # Could be renamed table_exists for consistency > return 0; > } > >+sub table_is_view { >+ my ($table_name) = @_; >+ >+ return unless TableExists($table_name); >+ my $dbh = C4::Context->dbh; >+ my $database = C4::Context->config("database"); >+ >+ my ($is_view) = $dbh->selectrow_array(qq{ >+ SELECT COUNT(*) >+ FROM information_schema.tables >+ WHERE TABLE_SCHEMA='$database' >+ AND TABLE_NAME='$table_name' >+ AND TABLE_TYPE='VIEW'; >+ }); >+ return ($is_view) ? 1 : 0; >+} > > =head1 AUTHOR > >diff --git a/installer/data/mysql/atomicupdate/bug_25260_holds_table.perl b/installer/data/mysql/atomicupdate/bug_25260_holds_table.perl >new file mode 100644 >index 0000000000..fd2fb34e45 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_25260_holds_table.perl >@@ -0,0 +1,261 @@ >+$DBversion = 'XXX'; >+if( CheckVersion( $DBversion ) ) { >+ >+ if( !TableExists( 'holds' ) ) { >+ $dbh->do( q| >+ CREATE TABLE `holds` ( >+ `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'internal hold identifier', >+ `patron_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', >+ `pickup_library_id` VARCHAR(10) DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', >+ `desk_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', >+ `created_date` DATE DEFAULT NULL COMMENT 'the date the hold was placed', >+ `biblio_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', >+ `item_id` INT(11) DEFAULT NULL COMMENT '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 COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', >+ `item_level` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is the hold was placed at item level', >+ `completed_date` DATE DEFAULT NULL COMMENT 'the date this hold was completed (fulfilled or cancelled)', >+ `notes` LONGTEXT COMMENT 'notes related to this hold', >+ `priority` SMALLINT(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', >+ `status` ENUM('placed', 'fulfilled', 'processing', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL COMMENT "the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled')", >+ `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'the date and time this hold was last updated', >+ `waiting_date` DATE DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', >+ `expiration_date` DATE DEFAULT NULL COMMENT "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 COMMENT 'if it was assigned the lowest priority', >+ `non_priority` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned no priority', >+ `suspended` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If it is a non-priority hold', >+ `suspended_until_date` DATETIME NULL DEFAULT NULL COMMENT 'Date until it is suspended', >+ `completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT "If it has been completed (i.e. either 'fulfilled' or 'cancelled')", >+ `cancellation_reason` VARCHAR(80) NULL DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', >+ 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 (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, >+ CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) 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 (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE, >+ CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, >+ CONSTRAINT `holds_ibfk_6` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE >+ ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; >+ |); >+ >+ # migrate 'reserves' and 'old_reserves' into 'holds' >+ $dbh->do(q| >+ INSERT INTO holds ( >+ id, >+ patron_id, >+ created_date, >+ biblio_id, >+ pickup_library_id, >+ desk_id, >+ completed_date, >+ cancellation_reason, >+ 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, >+ desk_id, >+ NULL, >+ cancellation_reason, >+ 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.desk_id, >+ o.timestamp, >+ o.cancellation_reason, >+ 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 >+ |); >+ >+ if ( foreign_key_exists('club_holds_to_patron_holds', 'clubs_holds_paton_holds_ibfk_3') ) { >+ $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') && !table_is_view('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`, >+ `patron_id` AS `borrowernumber`, >+ `created_date` AS `reservedate`, >+ `biblio_id` AS `biblionumber`, >+ `pickup_library_id` AS `branchcode`, >+ `desk_id`, >+ NULL AS `cancellationdate`, >+ `cancellation_reason`, >+ `notes` AS `reservenotes`, >+ `priority`, >+ CASE >+ when `status` = 'waiting' then >+ 'W' >+ when `status` = 'in_transit' then >+ 'T' >+ else >+ NULL >+ END AS `found`, >+ `timestamp`, >+ `item_id` AS `itemnumber`, >+ `waiting_date` AS `waitingdate`, >+ `expiration_date` AS `expirationdate`, >+ `lowest_priority` AS `lowestPriority`, >+ `suspended` AS `suspend`, >+ `suspended_until_date` AS `suspend_until`, >+ `item_type` AS `itemtype`, >+ `item_level` AS `item_level_hold`, >+ `non_priority` >+ FROM `holds` >+ WHERE completed=0; >+ |) unless TableExists('reserves') && table_is_view('reserves'); >+ >+ if ( TableExists('holds') && TableExists('old_reserves') && !table_is_view('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`, >+ `patron_id` AS `borrowernumber`, >+ `created_date` AS `reservedate`, >+ `biblio_id` AS `biblionumber`, >+ `pickup_library_id` AS `branchcode`, >+ `desk_id`, >+ CASE >+ WHEN `status` = 'cancelled' THEN >+ `completed_date` >+ ELSE >+ NULL >+ END AS `cancellationdate`, >+ `cancellation_reason`, >+ `notes` AS `reservenotes`, >+ `priority`, >+ CASE >+ WHEN `status` = 'fulfilled' THEN >+ 'F' >+ ELSE >+ NULL >+ END AS `found`, >+ `timestamp`, >+ `item_id` AS `itemnumber`, >+ `waiting_date` AS `waitingdate`, >+ `expiration_date` AS `expirationdate`, >+ `lowest_priority` AS `lowestPriority`, >+ `suspended` AS `suspend`, >+ `suspended_until_date` AS `suspend_until`, >+ `item_type` AS `itemtype`, >+ `item_level` AS `item_level_hold`, >+ `non_priority` >+ FROM `holds` >+ WHERE completed=1; >+ |) unless TableExists('old_reserves') && table_is_view('old_reserves'); >+ >+ NewVersion( $DBversion, 25260, "Merge 'reserves' and 'old_reserves' into a new 'holds' table"); >+} >-- >2.20.1
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