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 |
} |