3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.11');
6 -- Evergreen DB patch XXXX.fix_author_other_index.sql
8 -- Fix author|other index so that it doesn't exclude 700
9 -- fields that contain relator values in the $e or $4.
12 -- check whether patch can be applied
13 INSERT INTO config.upgrade_log (version) VALUES ('0599'); -- miker/gmc
15 UPDATE config.metabib_field
16 SET xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role/mods32:roleTerm[text()='creator'])]$$
17 WHERE field_class = 'author'
19 AND xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role)]$$
20 AND format = 'mods32';
22 -- To reindex the affected bibs, you can run something like this:
24 -- SELECT metabib.reingest_metabib_field_entries(record)
26 -- SELECT DISTINCT record
27 -- FROM metabib.real_full_rec
28 -- WHERE tag IN ('600', '700', '720', '800')
29 -- AND subfield IN ('4', 'e')
32 -- Evergreen DB patch XXXX.schema.asset_merge_record_assets.sql
36 INSERT INTO config.upgrade_log (version) VALUES ('0639');
38 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
40 moved_objects INT := 0;
41 source_cn asset.call_number%ROWTYPE;
42 target_cn asset.call_number%ROWTYPE;
43 metarec metabib.metarecord%ROWTYPE;
44 hold action.hold_request%ROWTYPE;
45 ser_rec serial.record_entry%ROWTYPE;
52 -- move any 856 entries on records that have at least one MARC-mapped URI entry
53 SELECT INTO uri_count COUNT(*)
54 FROM asset.uri_call_number_map m
55 JOIN asset.call_number cn ON (m.call_number = cn.id)
56 WHERE cn.record = source_record;
60 -- This returns more nodes than you might expect:
61 -- 7 instead of 1 for an 856 with $u $y $9
62 SELECT COUNT(*) INTO counter
63 FROM oils_xpath_table(
66 'biblio.record_entry',
68 'id=' || source_record
71 FOR i IN 1 .. counter LOOP
72 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
74 ' ind1="' || FIRST(ind1) || '"' ||
75 ' ind2="' || FIRST(ind2) || '">' ||
78 '<subfield code="' || subfield || '">' ||
81 regexp_replace(data,'&','&','g'),
87 ) || '</datafield>' INTO uri_datafield
88 FROM oils_xpath_table(
91 'biblio.record_entry',
92 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
93 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
94 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
95 '//*[@tag="856"][position()=' || i || ']/*[@code]',
96 'id=' || source_record
97 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
99 -- As most of the results will be NULL, protect against NULLifying
100 -- the valid content that we do generate
101 uri_text := uri_text || COALESCE(uri_datafield, '');
104 IF uri_text <> '' THEN
105 UPDATE biblio.record_entry
106 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
107 WHERE id = target_record;
112 -- Find and move metarecords to the target record
113 SELECT INTO metarec *
114 FROM metabib.metarecord
115 WHERE master_record = source_record;
118 UPDATE metabib.metarecord
119 SET master_record = target_record,
121 WHERE id = metarec.id;
123 moved_objects := moved_objects + 1;
126 -- Find call numbers attached to the source ...
127 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
129 SELECT INTO target_cn *
130 FROM asset.call_number
131 WHERE label = source_cn.label
132 AND owning_lib = source_cn.owning_lib
133 AND record = target_record;
135 -- ... and if there's a conflicting one on the target ...
138 -- ... move the copies to that, and ...
140 SET call_number = target_cn.id
141 WHERE call_number = source_cn.id;
143 -- ... move V holds to the move-target call number
144 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
146 UPDATE action.hold_request
147 SET target = target_cn.id
150 moved_objects := moved_objects + 1;
155 -- ... just move the call number to the target record
156 UPDATE asset.call_number
157 SET record = target_record
158 WHERE id = source_cn.id;
161 moved_objects := moved_objects + 1;
164 -- Find T holds targeting the source record ...
165 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
167 -- ... and move them to the target record
168 UPDATE action.hold_request
169 SET target = target_record
172 moved_objects := moved_objects + 1;
175 -- Find serial records targeting the source record ...
176 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
177 -- ... and move them to the target record
178 UPDATE serial.record_entry
179 SET record = target_record
180 WHERE id = ser_rec.id;
182 moved_objects := moved_objects + 1;
185 -- Finally, "delete" the source record
186 DELETE FROM biblio.record_entry WHERE id = source_record;
188 -- That's all, folks!
189 RETURN moved_objects;
191 $func$ LANGUAGE plpgsql;
194 INSERT INTO config.upgrade_log (version) VALUES ('0646');
196 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
201 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
203 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
208 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
209 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
212 actor.org_unit_descendants(ans.id) d
213 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
214 JOIN asset.copy_location cl ON (cp.location = cl.id)
215 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
219 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
226 $f$ LANGUAGE PLPGSQL;
228 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
233 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
235 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
240 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
241 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
244 actor.org_unit_descendants(ans.id) d
245 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
246 JOIN asset.copy_location cl ON (cp.location = cl.id)
247 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
251 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
258 $f$ LANGUAGE PLPGSQL;
261 INSERT INTO config.upgrade_log (version) VALUES ('0649');
263 CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS
264 SELECT cp.id, COALESCE(c.circ_count, 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count
266 LEFT JOIN extend_reporter.legacy_circ_count c USING (id)
267 LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id
268 LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id
269 GROUP BY cp.id, c.circ_count;
273 INSERT INTO config.upgrade_log (version) VALUES ('0658');
275 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
276 # Derived from the Koha C4::ClassSortRoutine::Dewey module
277 # Copyright (C) 2007 LibLime
278 # Licensed under the GPL v2 or later
283 my $init = uc(shift);
287 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
288 my @tokens = split /\.|\s+/, $init;
289 my $digit_group_count = 0;
290 for (my $i = 0; $i <= $#tokens; $i++) {
291 if ($tokens[$i] =~ /^\d+$/) {
292 $digit_group_count++;
293 if (2 == $digit_group_count) {
294 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
295 $tokens[$i] =~ tr/ /0/;
299 # Pad the first digit_group if there was only one
300 if (1 == $digit_group_count) {
301 $tokens[0] .= '_000000000000000'
303 my $key = join("_", @tokens);
304 $key =~ s/[^\p{IsAlnum}_]//g;
308 $func$ LANGUAGE PLPERLU;
310 -- regenerate sort keys for any dewey call numbers
311 UPDATE asset.call_number SET id = id WHERE label_class = 2;
314 INSERT INTO config.upgrade_log (version) VALUES ('0665');
316 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$ DECLARE
317 user_object actor.usr%ROWTYPE;
318 standing_penalty config.standing_penalty%ROWTYPE;
319 item_object asset.copy%ROWTYPE;
320 item_status_object config.copy_status%ROWTYPE;
321 item_location_object asset.copy_location%ROWTYPE;
322 result action.matrix_test_result;
323 circ_test config.circ_matrix_matchpoint%ROWTYPE;
324 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
325 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
326 hold_ratio action.hold_stats%ROWTYPE;
330 context_org_list INT[];
333 result.success := TRUE;
335 -- Fail if the user is BARRED
336 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
338 -- Fail if we couldn't find the user
339 IF user_object.id IS NULL THEN
340 result.fail_part := 'no_user';
341 result.success := FALSE;
347 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
349 -- Fail if we couldn't find the item
350 IF item_object.id IS NULL THEN
351 result.fail_part := 'no_item';
352 result.success := FALSE;
358 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
359 result.matchpoint := circ_test.id;
361 -- Fail if we couldn't find a matchpoint
362 IF result.matchpoint IS NULL THEN
363 result.fail_part := 'no_matchpoint';
364 result.success := FALSE;
369 IF user_object.barred IS TRUE THEN
370 result.fail_part := 'actor.usr.barred';
371 result.success := FALSE;
376 -- Fail if the item can't circulate
377 IF item_object.circulate IS FALSE THEN
378 result.fail_part := 'asset.copy.circulate';
379 result.success := FALSE;
384 -- Fail if the item isn't in a circulateable status on a non-renewal
385 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
386 result.fail_part := 'asset.copy.status';
387 result.success := FALSE;
390 ELSIF renewal AND item_object.status <> 1 THEN
391 result.fail_part := 'asset.copy.status';
392 result.success := FALSE;
397 -- Fail if the item can't circulate because of the shelving location
398 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
399 IF item_location_object.circulate IS FALSE THEN
400 result.fail_part := 'asset.copy_location.circulate';
401 result.success := FALSE;
406 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
408 -- Fail if the test is set to hard non-circulating
409 IF circ_test.circulate IS FALSE THEN
410 result.fail_part := 'config.circ_matrix_test.circulate';
411 result.success := FALSE;
416 -- Fail if the total copy-hold ratio is too low
417 IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
418 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
419 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
420 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
421 result.success := FALSE;
427 -- Fail if the available copy-hold ratio is too low
428 IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
429 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
430 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
431 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
432 result.success := FALSE;
439 penalty_type = '%RENEW%';
441 penalty_type = '%CIRC%';
444 FOR standing_penalty IN
445 SELECT DISTINCT csp.*
446 FROM actor.usr_standing_penalty usp
447 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
448 WHERE usr = match_user
449 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
450 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
451 AND csp.block_list LIKE penalty_type LOOP
453 result.fail_part := standing_penalty.name;
454 result.success := FALSE;
459 -- Fail if the user has too many items with specific circ_modifiers checked out
461 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
462 SELECT INTO items_out COUNT(*)
463 FROM action.circulation circ
464 JOIN asset.copy cp ON (cp.id = circ.target_copy)
465 WHERE circ.usr = match_user
466 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
467 AND circ.checkin_time IS NULL
468 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
469 AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
470 IF items_out >= out_by_circ_mod.items_out THEN
471 result.fail_part := 'config.circ_matrix_circ_mod_test';
472 result.success := FALSE;
479 -- If we passed everything, return the successful matchpoint id
486 $func$ LANGUAGE plpgsql;
489 INSERT INTO config.upgrade_log (version) VALUES ('0691');
491 CREATE INDEX poi_po_idx ON acq.po_item (purchase_order);
493 CREATE INDEX ie_inv_idx on acq.invoice_entry (invoice);
494 CREATE INDEX ie_po_idx on acq.invoice_entry (purchase_order);
495 CREATE INDEX ie_li_idx on acq.invoice_entry (lineitem);
497 CREATE INDEX ii_inv_idx on acq.invoice_item (invoice);
498 CREATE INDEX ii_po_idx on acq.invoice_item (purchase_order);
499 CREATE INDEX ii_poi_idx on acq.invoice_item (po_item);