From 1287f0c4ccf4d6a8884beebc59893997b458d045 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 Signed-off-by: Martin Renvoize --- 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 2d6b523eed..99ba6b38ae 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.32.0