|
Line 0
Link Here
|
| 0 |
- |
1 |
$DBversion = 'XXX'; |
|
|
2 |
if( CheckVersion( $DBversion ) ) { |
| 3 |
|
| 4 |
if( !TableExists( 'holds' ) ) { |
| 5 |
$dbh->do( q| |
| 6 |
CREATE TABLE `holds` ( |
| 7 |
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'internal hold identifier', |
| 8 |
`patron_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', |
| 9 |
`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', |
| 10 |
`desk_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', |
| 11 |
`created_date` DATE DEFAULT NULL COMMENT 'the date the hold was placed', |
| 12 |
`biblio_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', |
| 13 |
`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', |
| 14 |
`item_type` VARCHAR(10) DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', |
| 15 |
`item_level` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is the hold was placed at item level', |
| 16 |
`completed_date` DATE DEFAULT NULL COMMENT 'the date this hold was completed (fulfilled or cancelled)', |
| 17 |
`notes` LONGTEXT COMMENT 'notes related to this hold', |
| 18 |
`priority` SMALLINT(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', |
| 19 |
`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')", |
| 20 |
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'the date and time this hold was last updated', |
| 21 |
`waiting_date` DATE DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', |
| 22 |
`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)", |
| 23 |
`lowest_priority` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned the lowest priority', |
| 24 |
`non_priority` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned no priority', |
| 25 |
`suspended` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If it is a non-priority hold', |
| 26 |
`suspended_until_date` DATETIME NULL DEFAULT NULL COMMENT 'Date until it is suspended', |
| 27 |
`completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT "If it has been completed (i.e. either 'fulfilled' or 'cancelled')", |
| 28 |
`cancellation_reason` VARCHAR(80) NULL DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', |
| 29 |
PRIMARY KEY (`id`), |
| 30 |
KEY `priority_status_idx` (`priority`, `status`), |
| 31 |
KEY `patron_id` (`patron_id`), |
| 32 |
KEY `biblio_id` (`biblio_id`), |
| 33 |
KEY `item_id` (`item_id`), |
| 34 |
KEY `pickup_library_id` (`pickup_library_id`), |
| 35 |
KEY `item_type` (`item_type`), |
| 36 |
CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 37 |
CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 38 |
CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 39 |
CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 40 |
CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 41 |
CONSTRAINT `holds_ibfk_6` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE |
| 42 |
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; |
| 43 |
|); |
| 44 |
|
| 45 |
# migrate 'reserves' and 'old_reserves' into 'holds' |
| 46 |
$dbh->do(q| |
| 47 |
INSERT INTO holds ( |
| 48 |
id, |
| 49 |
patron_id, |
| 50 |
created_date, |
| 51 |
biblio_id, |
| 52 |
pickup_library_id, |
| 53 |
desk_id, |
| 54 |
completed_date, |
| 55 |
cancellation_reason, |
| 56 |
notes, |
| 57 |
priority, |
| 58 |
status, |
| 59 |
timestamp, |
| 60 |
item_id, |
| 61 |
waiting_date, |
| 62 |
expiration_date, |
| 63 |
lowest_priority, |
| 64 |
suspended, |
| 65 |
suspended_until_date, |
| 66 |
item_type, |
| 67 |
item_level, |
| 68 |
completed |
| 69 |
) |
| 70 |
SELECT * |
| 71 |
FROM ( |
| 72 |
SELECT reserve_id, |
| 73 |
borrowernumber, |
| 74 |
reservedate, |
| 75 |
biblionumber, |
| 76 |
branchcode, |
| 77 |
desk_id, |
| 78 |
NULL, |
| 79 |
cancellation_reason, |
| 80 |
reservenotes, |
| 81 |
priority, |
| 82 |
CASE |
| 83 |
WHEN found = 'W' THEN |
| 84 |
'waiting' |
| 85 |
WHEN found = 'T' THEN |
| 86 |
'in_transit' |
| 87 |
ELSE |
| 88 |
'placed' |
| 89 |
END, |
| 90 |
timestamp, |
| 91 |
itemnumber, |
| 92 |
waitingdate, |
| 93 |
expirationdate, |
| 94 |
lowestPriority, |
| 95 |
suspend, |
| 96 |
suspend_until, |
| 97 |
itemtype, |
| 98 |
item_level_hold, |
| 99 |
0 |
| 100 |
FROM reserves |
| 101 |
UNION |
| 102 |
SELECT o.reserve_id, |
| 103 |
o.borrowernumber, |
| 104 |
o.reservedate, |
| 105 |
o.biblionumber, |
| 106 |
o.branchcode, |
| 107 |
o.desk_id, |
| 108 |
o.timestamp, |
| 109 |
o.cancellation_reason, |
| 110 |
o.reservenotes, |
| 111 |
o.priority, |
| 112 |
CASE |
| 113 |
WHEN o.found = 'F' THEN |
| 114 |
'fulfilled' |
| 115 |
ELSE |
| 116 |
'cancelled' |
| 117 |
END, |
| 118 |
o.timestamp, |
| 119 |
o.itemnumber, |
| 120 |
o.waitingdate, |
| 121 |
o.expirationdate, |
| 122 |
o.lowestPriority, |
| 123 |
o.suspend, |
| 124 |
o.suspend_until, |
| 125 |
o.itemtype, |
| 126 |
o.item_level_hold, |
| 127 |
1 AS completed |
| 128 |
FROM old_reserves o |
| 129 |
LEFT JOIN |
| 130 |
reserves r |
| 131 |
ON o.reserve_id = r.reserve_id |
| 132 |
WHERE r.reserve_id is null |
| 133 |
) reserves |
| 134 |
ORDER BY reservedate |
| 135 |
|); |
| 136 |
|
| 137 |
if ( foreign_key_exists('club_holds_to_patron_holds', 'clubs_holds_paton_holds_ibfk_3') ) { |
| 138 |
$dbh->do(q| |
| 139 |
ALTER TABLE club_holds_to_patron_holds |
| 140 |
DROP FOREIGN KEY clubs_holds_paton_holds_ibfk_3 |
| 141 |
|); |
| 142 |
} |
| 143 |
|
| 144 |
$dbh->do(q| |
| 145 |
ALTER TABLE club_holds_to_patron_holds |
| 146 |
ADD CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 147 |
|); |
| 148 |
|
| 149 |
} |
| 150 |
|
| 151 |
if ( TableExists('holds') && TableExists('reserves') && !table_is_view('reserves') ) { |
| 152 |
$dbh->do(q| |
| 153 |
DELETE FROM res USING `reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id |
| 154 |
|); |
| 155 |
|
| 156 |
$sth = $dbh->prepare("SELECT COUNT(*) AS count FROM reserves"); |
| 157 |
$sth->execute(); |
| 158 |
my $row = $sth->fetchrow_hashref; |
| 159 |
if ($row->{count}) { |
| 160 |
print "Warning to database administrator:\n" |
| 161 |
. "There were $row->{count} reserves that could not be moved, please check '_reserves'.\n"; |
| 162 |
$dbh->do("RENAME TABLE reserves TO _reserves"); |
| 163 |
} else { |
| 164 |
$dbh->do("DROP TABLE reserves"); |
| 165 |
} |
| 166 |
} |
| 167 |
|
| 168 |
$dbh->do(q| |
| 169 |
CREATE VIEW `reserves` |
| 170 |
AS |
| 171 |
SELECT |
| 172 |
`id` AS `reserve_id`, |
| 173 |
`patron_id` AS `borrowernumber`, |
| 174 |
`created_date` AS `reservedate`, |
| 175 |
`biblio_id` AS `biblionumber`, |
| 176 |
`pickup_library_id` AS `branchcode`, |
| 177 |
`desk_id`, |
| 178 |
NULL AS `cancellationdate`, |
| 179 |
`cancellation_reason`, |
| 180 |
`notes` AS `reservenotes`, |
| 181 |
`priority`, |
| 182 |
CASE |
| 183 |
when `status` = 'waiting' then |
| 184 |
'W' |
| 185 |
when `status` = 'in_transit' then |
| 186 |
'T' |
| 187 |
else |
| 188 |
NULL |
| 189 |
END AS `found`, |
| 190 |
`timestamp`, |
| 191 |
`item_id` AS `itemnumber`, |
| 192 |
`waiting_date` AS `waitingdate`, |
| 193 |
`expiration_date` AS `expirationdate`, |
| 194 |
`lowest_priority` AS `lowestPriority`, |
| 195 |
`suspended` AS `suspend`, |
| 196 |
`suspended_until_date` AS `suspend_until`, |
| 197 |
`item_type` AS `itemtype`, |
| 198 |
`item_level` AS `item_level_hold`, |
| 199 |
`non_priority` |
| 200 |
FROM `holds` |
| 201 |
WHERE completed=0; |
| 202 |
|) unless TableExists('reserves') && table_is_view('reserves'); |
| 203 |
|
| 204 |
if ( TableExists('holds') && TableExists('old_reserves') && !table_is_view('old_reserves') ) { |
| 205 |
$dbh->do(q| |
| 206 |
DELETE FROM res USING `old_reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id |
| 207 |
|); |
| 208 |
|
| 209 |
$sth = $dbh->prepare("SELECT COUNT(*) AS count FROM old_reserves"); |
| 210 |
$sth->execute(); |
| 211 |
my $row = $sth->fetchrow_hashref; |
| 212 |
if ($row->{count}) { |
| 213 |
print "Warning to database administrator:\n" |
| 214 |
. "There were $row->{count} old reserves that could not be moved, please check '_old_reserves'.\n"; |
| 215 |
$dbh->do("RENAME TABLE old_reserves TO _old_reserves"); |
| 216 |
} else { |
| 217 |
$dbh->do("DROP TABLE old_reserves"); |
| 218 |
} |
| 219 |
} |
| 220 |
|
| 221 |
$dbh->do(q| |
| 222 |
CREATE VIEW `old_reserves` |
| 223 |
AS |
| 224 |
SELECT |
| 225 |
`id` AS `reserve_id`, |
| 226 |
`patron_id` AS `borrowernumber`, |
| 227 |
`created_date` AS `reservedate`, |
| 228 |
`biblio_id` AS `biblionumber`, |
| 229 |
`pickup_library_id` AS `branchcode`, |
| 230 |
`desk_id`, |
| 231 |
CASE |
| 232 |
WHEN `status` = 'cancelled' THEN |
| 233 |
`completed_date` |
| 234 |
ELSE |
| 235 |
NULL |
| 236 |
END AS `cancellationdate`, |
| 237 |
`cancellation_reason`, |
| 238 |
`notes` AS `reservenotes`, |
| 239 |
`priority`, |
| 240 |
CASE |
| 241 |
WHEN `status` = 'fulfilled' THEN |
| 242 |
'F' |
| 243 |
ELSE |
| 244 |
NULL |
| 245 |
END AS `found`, |
| 246 |
`timestamp`, |
| 247 |
`item_id` AS `itemnumber`, |
| 248 |
`waiting_date` AS `waitingdate`, |
| 249 |
`expiration_date` AS `expirationdate`, |
| 250 |
`lowest_priority` AS `lowestPriority`, |
| 251 |
`suspended` AS `suspend`, |
| 252 |
`suspended_until_date` AS `suspend_until`, |
| 253 |
`item_type` AS `itemtype`, |
| 254 |
`item_level` AS `item_level_hold`, |
| 255 |
`non_priority` |
| 256 |
FROM `holds` |
| 257 |
WHERE completed=1; |
| 258 |
|) unless TableExists('old_reserves') && table_is_view('old_reserves'); |
| 259 |
|
| 260 |
NewVersion( $DBversion, 25260, "Merge 'reserves' and 'old_reserves' into a new 'holds' table"); |
| 261 |
} |