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