4 ALTER TABLE permission.grp_tree
5 ADD COLUMN hold_priority INT NOT NULL DEFAULT 0;
8 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN strict_ou_match BOOL NOT NULL DEFAULT FALSE;
11 -- Rather than polluting the public schema with general Evergreen
12 -- functions, carve out a dedicated schema
13 CREATE SCHEMA evergreen;
15 -- Replace all uses of PostgreSQL's built-in LOWER() function with
16 -- a more locale-savvy PLPERLU evergreen.lowercase() function
17 CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$
19 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
22 CREATE OR REPLACE FUNCTION evergreen.change_db_setting(setting_name TEXT, settings TEXT[]) RETURNS VOID AS $$
24 EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || quote_ident(setting_name) || ' = ' || array_to_string(settings, ',');
30 SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']);
32 -- Fix function breakage due to short search path
33 CREATE OR REPLACE FUNCTION evergreen.force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$
34 use Unicode::Normalize 'normalize';
35 return normalize($_[1],$_[0]); # reverse the params
36 $func$ LANGUAGE PLPERLU;
38 CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$
40 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
45 CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$
46 SELECT REPLACE(REPLACE(REPLACE($1,
50 $$ LANGUAGE SQL IMMUTABLE;
52 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
54 use_id_for_tcn BOOLEAN;
56 -- Remove any existing 901 fields before we insert the authoritative one
57 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
59 IF TG_TABLE_SCHEMA = 'biblio' THEN
60 -- Set TCN value to record ID?
61 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
62 WHERE name = 'cat.bib.use_id_for_tcn';
64 IF use_id_for_tcn = 't' THEN
65 NEW.tcn_value := NEW.id;
68 NEW.marc := REGEXP_REPLACE(
70 E'(</(?:[^:]*?:)?record>)',
71 E'<datafield tag="901" ind1=" " ind2=" ">' ||
72 '<subfield code="a">' || evergreen.xml_escape(NEW.tcn_value) || E'</subfield>' ||
73 '<subfield code="b">' || evergreen.xml_escape(NEW.tcn_source) || E'</subfield>' ||
74 '<subfield code="c">' || NEW.id || E'</subfield>' ||
75 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
76 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
77 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
80 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
81 NEW.marc := REGEXP_REPLACE(
83 E'(</(?:[^:]*?:)?record>)',
84 E'<datafield tag="901" ind1=" " ind2=" ">' ||
85 '<subfield code="c">' || NEW.id || E'</subfield>' ||
86 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
89 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
90 NEW.marc := REGEXP_REPLACE(
92 E'(</(?:[^:]*?:)?record>)',
93 E'<datafield tag="901" ind1=" " ind2=" ">' ||
94 '<subfield code="c">' || NEW.id || E'</subfield>' ||
95 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
96 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
97 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
101 NEW.marc := REGEXP_REPLACE(
103 E'(</(?:[^:]*?:)?record>)',
104 E'<datafield tag="901" ind1=" " ind2=" ">' ||
105 '<subfield code="c">' || NEW.id || E'</subfield>' ||
106 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
113 $func$ LANGUAGE PLPGSQL;
115 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL;
117 CREATE OR REPLACE FUNCTION evergreen.lpad_number_substrings( TEXT, TEXT, INT ) RETURNS TEXT AS $$
123 while ($string =~ /(?:^|\D)(\d{1,$find})(?:$|\D)/) {
125 $padded = $pad x ($len - length($padded)) . $padded;
126 $string =~ s/$1/$padded/sg;
133 ALTER TABLE config.hard_due_date DROP CONSTRAINT hard_due_date_name_check;
136 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
139 use Unicode::Normalize;
142 my $str = decode_utf8(shift);
145 # Apply NACO normalization to input string; based on
146 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
148 # Note that unlike a strict reading of the NACO normalization rules,
149 # output is returned as lowercase instead of uppercase for compatibility
150 # with previous versions of the Evergreen naco_normalize routine.
152 # Convert to upper-case first; even though final output will be lowercase, doing this will
153 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
154 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
157 # remove non-filing strings
158 $str =~ s/\x{0098}.*?\x{009C}//g;
162 # additional substitutions - 3.6.
163 $str =~ s/\x{00C6}/AE/g;
164 $str =~ s/\x{00DE}/TH/g;
165 $str =~ s/\x{0152}/OE/g;
166 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}]['/DDOLl/d;
168 # transformations based on Unicode category codes
169 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
171 if ($sf && $sf =~ /^a/o) {
172 my $commapos = index($str, ',');
173 if ($commapos > -1) {
174 if ($commapos != length($str) - 1) {
175 $str =~ s/,/\x07/; # preserve first comma
180 # since we've stripped out the control characters, we can now
181 # use a few as placeholders temporarily
182 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
183 $str =~ s/[\p{Pc}\p{Pd}\p{Pe}\p{Pf}\p{Pi}\p{Po}\p{Ps}\p{Sk}\p{Sm}\p{So}\p{Zl}\p{Zp}\p{Zs}]/ /g;
184 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
187 $str =~ tr/\x{0660}-\x{0669}\x{06F0}-\x{06F9}\x{07C0}-\x{07C9}\x{0966}-\x{096F}\x{09E6}-\x{09EF}\x{0A66}-\x{0A6F}\x{0AE6}-\x{0AEF}\x{0B66}-\x{0B6F}\x{0BE6}-\x{0BEF}\x{0C66}-\x{0C6F}\x{0CE6}-\x{0CEF}\x{0D66}-\x{0D6F}\x{0E50}-\x{0E59}\x{0ED0}-\x{0ED9}\x{0F20}-\x{0F29}\x{1040}-\x{1049}\x{1090}-\x{1099}\x{17E0}-\x{17E9}\x{1810}-\x{1819}\x{1946}-\x{194F}\x{19D0}-\x{19D9}\x{1A80}-\x{1A89}\x{1A90}-\x{1A99}\x{1B50}-\x{1B59}\x{1BB0}-\x{1BB9}\x{1C40}-\x{1C49}\x{1C50}-\x{1C59}\x{A620}-\x{A629}\x{A8D0}-\x{A8D9}\x{A900}-\x{A909}\x{A9D0}-\x{A9D9}\x{AA50}-\x{AA59}\x{ABF0}-\x{ABF9}\x{FF10}-\x{FF19}/0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9/;
189 # intentionally skipping step 8 of the NACO algorithm; if the string
190 # gets normalized away, that's fine.
192 # leading and trailing spaces
198 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
201 CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
202 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
205 SELECT pgt.parent, gad.distance+1
206 FROM permission.grp_tree pgt JOIN grp_ancestors_distance gad ON pgt.id = gad.id
207 WHERE pgt.parent IS NOT NULL
209 SELECT * FROM grp_ancestors_distance;
210 $$ LANGUAGE SQL STABLE;
212 CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
213 WITH RECURSIVE grp_descendants_distance(id, distance) AS (
216 SELECT pgt.id, gdd.distance+1
217 FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON pgt.parent = gdd.id
219 SELECT * FROM grp_descendants_distance;
220 $$ LANGUAGE SQL STABLE;
222 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
223 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
226 SELECT ou.parent_ou, ouad.distance+1
227 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON ou.id = ouad.id
228 WHERE ou.parent_ou IS NOT NULL
230 SELECT * FROM org_unit_ancestors_distance;
231 $$ LANGUAGE SQL STABLE;
233 CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
234 WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
237 SELECT ou.id, oudd.distance+1
238 FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON ou.parent_ou = oudd.id
240 SELECT * FROM org_unit_descendants_distance;
241 $$ LANGUAGE SQL STABLE;
243 ALTER TABLE config.circ_matrix_matchpoint
244 ADD COLUMN user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED;
246 CREATE TABLE config.circ_matrix_weights (
247 id SERIAL PRIMARY KEY,
248 name TEXT NOT NULL UNIQUE,
249 org_unit NUMERIC(6,2) NOT NULL,
250 grp NUMERIC(6,2) NOT NULL,
251 circ_modifier NUMERIC(6,2) NOT NULL,
252 marc_type NUMERIC(6,2) NOT NULL,
253 marc_form NUMERIC(6,2) NOT NULL,
254 marc_vr_format NUMERIC(6,2) NOT NULL,
255 copy_circ_lib NUMERIC(6,2) NOT NULL,
256 copy_owning_lib NUMERIC(6,2) NOT NULL,
257 user_home_ou NUMERIC(6,2) NOT NULL,
258 ref_flag NUMERIC(6,2) NOT NULL,
259 juvenile_flag NUMERIC(6,2) NOT NULL,
260 is_renewal NUMERIC(6,2) NOT NULL,
261 usr_age_lower_bound NUMERIC(6,2) NOT NULL,
262 usr_age_upper_bound NUMERIC(6,2) NOT NULL
265 CREATE TABLE config.hold_matrix_weights (
266 id SERIAL PRIMARY KEY,
267 name TEXT NOT NULL UNIQUE,
268 user_home_ou NUMERIC(6,2) NOT NULL,
269 request_ou NUMERIC(6,2) NOT NULL,
270 pickup_ou NUMERIC(6,2) NOT NULL,
271 item_owning_ou NUMERIC(6,2) NOT NULL,
272 item_circ_ou NUMERIC(6,2) NOT NULL,
273 usr_grp NUMERIC(6,2) NOT NULL,
274 requestor_grp NUMERIC(6,2) NOT NULL,
275 circ_modifier NUMERIC(6,2) NOT NULL,
276 marc_type NUMERIC(6,2) NOT NULL,
277 marc_form NUMERIC(6,2) NOT NULL,
278 marc_vr_format NUMERIC(6,2) NOT NULL,
279 juvenile_flag NUMERIC(6,2) NOT NULL,
280 ref_flag NUMERIC(6,2) NOT NULL
283 CREATE TABLE config.weight_assoc (
284 id SERIAL PRIMARY KEY,
285 active BOOL NOT NULL,
286 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
287 circ_weights INT REFERENCES config.circ_matrix_weights (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
288 hold_weights INT REFERENCES config.hold_matrix_weights (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
290 CREATE UNIQUE INDEX cwa_one_active_per_ou ON config.weight_assoc (org_unit) WHERE active;
292 INSERT INTO config.circ_matrix_weights(name, org_unit, grp, circ_modifier, marc_type, marc_form, marc_vr_format, copy_circ_lib, copy_owning_lib, user_home_ou, ref_flag, juvenile_flag, is_renewal, usr_age_upper_bound, usr_age_lower_bound) VALUES
293 ('Default', 10.0, 11.0, 5.0, 4.0, 3.0, 2.0, 8.0, 8.0, 8.0, 1.0, 6.0, 7.0, 0.0, 0.0),
294 ('Org_Unit_First', 11.0, 10.0, 5.0, 4.0, 3.0, 2.0, 8.0, 8.0, 8.0, 1.0, 6.0, 7.0, 0.0, 0.0),
295 ('Item_Owner_First', 8.0, 8.0, 5.0, 4.0, 3.0, 2.0, 10.0, 11.0, 8.0, 1.0, 6.0, 7.0, 0.0, 0.0),
296 ('All_Equal', 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0);
298 INSERT INTO config.hold_matrix_weights(name, user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, usr_grp, requestor_grp, circ_modifier, marc_type, marc_form, marc_vr_format, juvenile_flag, ref_flag) VALUES
299 ('Default', 5.0, 5.0, 5.0, 5.0, 5.0, 7.0, 8.0, 4.0, 3.0, 2.0, 1.0, 4.0, 0.0),
300 ('Item_Owner_First', 5.0, 5.0, 5.0, 8.0, 7.0, 5.0, 5.0, 4.0, 3.0, 2.0, 1.0, 4.0, 0.0),
301 ('User_Before_Requestor', 5.0, 5.0, 5.0, 5.0, 5.0, 8.0, 7.0, 4.0, 3.0, 2.0, 1.0, 4.0, 0.0),
302 ('All_Equal', 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0);
304 INSERT INTO config.weight_assoc(active, org_unit, circ_weights, hold_weights) VALUES
308 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
310 specified_dest_usr IN INTEGER
314 renamable_row RECORD;
318 IF specified_dest_usr IS NULL THEN
319 dest_usr := 1; -- Admin user on stock installs
321 dest_usr := specified_dest_usr;
327 mailing_address = NULL,
328 billing_address = NULL
332 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
333 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
334 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
335 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
336 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
337 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
338 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
340 -- Update with a rename to avoid collisions
344 WHERE owner = src_usr
346 suffix := ' (' || src_usr || ')';
350 SET owner = dest_usr, name = name || suffix
351 WHERE id = renamable_row.id;
352 EXCEPTION WHEN unique_violation THEN
353 suffix := suffix || ' ';
360 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
361 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
362 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
363 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
364 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
365 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
366 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
367 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
370 DELETE FROM action.circulation WHERE usr = src_usr;
371 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
372 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
373 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
374 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
375 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
376 DELETE FROM action.hold_request WHERE usr = src_usr;
377 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
378 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
379 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
380 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
381 DELETE FROM action.survey_response WHERE usr = src_usr;
382 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
385 DELETE FROM actor.card WHERE usr = src_usr;
386 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
388 -- The following update is intended to avoid transient violations of a foreign
389 -- key constraint, whereby actor.usr_address references itself. It may not be
390 -- necessary, but it does no harm.
391 UPDATE actor.usr_address SET replaces = NULL
392 WHERE usr = src_usr AND replaces IS NOT NULL;
393 DELETE FROM actor.usr_address WHERE usr = src_usr;
394 DELETE FROM actor.usr_note WHERE usr = src_usr;
395 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
396 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
397 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
398 DELETE FROM actor.usr_setting WHERE usr = src_usr;
399 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
400 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
403 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
404 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
405 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
406 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
407 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
408 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
411 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
412 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
413 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
414 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
415 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
416 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
417 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
418 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
421 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
422 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
423 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
424 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
427 -- Update buckets with a rename to avoid collisions
430 FROM container.biblio_record_entry_bucket
431 WHERE owner = src_usr
433 suffix := ' (' || src_usr || ')';
436 UPDATE container.biblio_record_entry_bucket
437 SET owner = dest_usr, name = name || suffix
438 WHERE id = renamable_row.id;
439 EXCEPTION WHEN unique_violation THEN
440 suffix := suffix || ' ';
449 FROM container.call_number_bucket
450 WHERE owner = src_usr
452 suffix := ' (' || src_usr || ')';
455 UPDATE container.call_number_bucket
456 SET owner = dest_usr, name = name || suffix
457 WHERE id = renamable_row.id;
458 EXCEPTION WHEN unique_violation THEN
459 suffix := suffix || ' ';
468 FROM container.copy_bucket
469 WHERE owner = src_usr
471 suffix := ' (' || src_usr || ')';
474 UPDATE container.copy_bucket
475 SET owner = dest_usr, name = name || suffix
476 WHERE id = renamable_row.id;
477 EXCEPTION WHEN unique_violation THEN
478 suffix := suffix || ' ';
487 FROM container.user_bucket
488 WHERE owner = src_usr
490 suffix := ' (' || src_usr || ')';
493 UPDATE container.user_bucket
494 SET owner = dest_usr, name = name || suffix
495 WHERE id = renamable_row.id;
496 EXCEPTION WHEN unique_violation THEN
497 suffix := suffix || ' ';
504 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
507 DELETE FROM money.billable_xact WHERE usr = src_usr;
508 DELETE FROM money.collections_tracker WHERE usr = src_usr;
509 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
512 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
513 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
514 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
515 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
518 -- Update with a rename to avoid collisions
522 FROM reporter.output_folder
523 WHERE owner = src_usr
525 suffix := ' (' || src_usr || ')';
528 UPDATE reporter.output_folder
529 SET owner = dest_usr, name = name || suffix
530 WHERE id = renamable_row.id;
531 EXCEPTION WHEN unique_violation THEN
532 suffix := suffix || ' ';
538 EXCEPTION WHEN undefined_table THEN
543 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
544 EXCEPTION WHEN undefined_table THEN
548 -- Update with a rename to avoid collisions
552 FROM reporter.report_folder
553 WHERE owner = src_usr
555 suffix := ' (' || src_usr || ')';
558 UPDATE reporter.report_folder
559 SET owner = dest_usr, name = name || suffix
560 WHERE id = renamable_row.id;
561 EXCEPTION WHEN unique_violation THEN
562 suffix := suffix || ' ';
568 EXCEPTION WHEN undefined_table THEN
573 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
574 EXCEPTION WHEN undefined_table THEN
579 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
580 EXCEPTION WHEN undefined_table THEN
584 -- Update with a rename to avoid collisions
588 FROM reporter.template_folder
589 WHERE owner = src_usr
591 suffix := ' (' || src_usr || ')';
594 UPDATE reporter.template_folder
595 SET owner = dest_usr, name = name || suffix
596 WHERE id = renamable_row.id;
597 EXCEPTION WHEN unique_violation THEN
598 suffix := suffix || ' ';
604 EXCEPTION WHEN undefined_table THEN
609 -- Update with a rename to avoid collisions
613 WHERE owner = src_usr
615 suffix := ' (' || src_usr || ')';
618 UPDATE vandelay.queue
619 SET owner = dest_usr, name = name || suffix
620 WHERE id = renamable_row.id;
621 EXCEPTION WHEN unique_violation THEN
622 suffix := suffix || ' ';
633 -- Drop old (non-functional) constraints
635 ALTER TABLE config.circ_matrix_matchpoint
636 DROP CONSTRAINT ep_once_per_grp_loc_mod_marc;
638 ALTER TABLE config.hold_matrix_matchpoint
639 DROP CONSTRAINT hous_once_per_grp_loc_mod_marc;
641 -- Clean up tables before making normalized index
643 CREATE OR REPLACE FUNCTION action.cleanup_matrix_matchpoints() RETURNS void AS $func$
649 SELECT org_unit, grp, circ_modifier, marc_type, marc_form, marc_vr_format, copy_circ_lib, copy_owning_lib, user_home_ou, ref_flag, juvenile_flag, is_renewal, usr_age_lower_bound, usr_age_upper_bound, COUNT(id) as rowcount, MIN(id) as firstrow
650 FROM config.circ_matrix_matchpoint
652 GROUP BY org_unit, grp, circ_modifier, marc_type, marc_form, marc_vr_format, copy_circ_lib, copy_owning_lib, user_home_ou, ref_flag, juvenile_flag, is_renewal, usr_age_lower_bound, usr_age_upper_bound
653 HAVING COUNT(id) > 1 LOOP
655 UPDATE config.circ_matrix_matchpoint SET active=false
656 WHERE id > temp_row.firstrow
657 AND org_unit = temp_row.org_unit
658 AND grp = temp_row.grp
659 AND circ_modifier IS NOT DISTINCT FROM temp_row.circ_modifier
660 AND marc_type IS NOT DISTINCT FROM temp_row.marc_type
661 AND marc_form IS NOT DISTINCT FROM temp_row.marc_form
662 AND marc_vr_format IS NOT DISTINCT FROM temp_row.marc_vr_format
663 AND copy_circ_lib IS NOT DISTINCT FROM temp_row.copy_circ_lib
664 AND copy_owning_lib IS NOT DISTINCT FROM temp_row.copy_owning_lib
665 AND user_home_ou IS NOT DISTINCT FROM temp_row.user_home_ou
666 AND ref_flag IS NOT DISTINCT FROM temp_row.ref_flag
667 AND juvenile_flag IS NOT DISTINCT FROM temp_row.juvenile_flag
668 AND is_renewal IS NOT DISTINCT FROM temp_row.is_renewal
669 AND usr_age_lower_bound IS NOT DISTINCT FROM temp_row.usr_age_lower_bound
670 AND usr_age_upper_bound IS NOT DISTINCT FROM temp_row.usr_age_upper_bound;
675 SELECT user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, usr_grp, requestor_grp, circ_modifier, marc_type, marc_form, marc_vr_format, juvenile_flag, ref_flag, COUNT(id) as rowcount, MIN(id) as firstrow
676 FROM config.hold_matrix_matchpoint
678 GROUP BY user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, usr_grp, requestor_grp, circ_modifier, marc_type, marc_form, marc_vr_format, juvenile_flag, ref_flag
679 HAVING COUNT(id) > 1 LOOP
681 UPDATE config.hold_matrix_matchpoint SET active=false
682 WHERE id > temp_row.firstrow
683 AND user_home_ou IS NOT DISTINCT FROM temp_row.user_home_ou
684 AND request_ou IS NOT DISTINCT FROM temp_row.request_ou
685 AND pickup_ou IS NOT DISTINCT FROM temp_row.pickup_ou
686 AND item_owning_ou IS NOT DISTINCT FROM temp_row.item_owning_ou
687 AND item_circ_ou IS NOT DISTINCT FROM temp_row.item_circ_ou
688 AND usr_grp IS NOT DISTINCT FROM temp_row.usr_grp
689 AND requestor_grp IS NOT DISTINCT FROM temp_row.requestor_grp
690 AND circ_modifier IS NOT DISTINCT FROM temp_row.circ_modifier
691 AND marc_type IS NOT DISTINCT FROM temp_row.marc_type
692 AND marc_form IS NOT DISTINCT FROM temp_row.marc_form
693 AND marc_vr_format IS NOT DISTINCT FROM temp_row.marc_vr_format
694 AND juvenile_flag IS NOT DISTINCT FROM temp_row.juvenile_flag
695 AND ref_flag IS NOT DISTINCT FROM temp_row.ref_flag;
698 $func$ LANGUAGE plpgsql;
700 SELECT action.cleanup_matrix_matchpoints();
702 DROP FUNCTION IF EXISTS action.cleanup_matrix_matchpoints();
704 -- Create Normalized indexes
706 CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound::TEXT, ''), COALESCE(usr_age_upper_bound::TEXT, '')) WHERE active;
708 CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, '')) WHERE active;
711 DROP FUNCTION asset.metarecord_copy_count ( INT, BIGINT, BOOL );
712 DROP FUNCTION asset.record_copy_count ( INT, BIGINT, BOOL );
714 DROP FUNCTION asset.opac_ou_record_copy_count (INT, BIGINT);
715 CREATE OR REPLACE FUNCTION asset.opac_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$
720 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;
722 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
727 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
731 actor.org_unit_descendants(ans.id) d
732 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
733 JOIN asset.copy cp ON (cp.id = av.copy_id)
737 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
744 $f$ LANGUAGE PLPGSQL;
746 DROP FUNCTION asset.opac_lasso_record_copy_count (INT, BIGINT);
747 CREATE OR REPLACE FUNCTION asset.opac_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$
752 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;
754 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
759 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
763 actor.org_unit_descendants(ans.id) d
764 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
765 JOIN asset.copy cp ON (cp.id = av.copy_id)
769 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
776 $f$ LANGUAGE PLPGSQL;
778 DROP FUNCTION asset.staff_ou_record_copy_count (INT, BIGINT);
780 DROP FUNCTION asset.staff_lasso_record_copy_count (INT, BIGINT);
782 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
784 IF staff IS TRUE THEN
786 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
788 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
792 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
794 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
800 $f$ LANGUAGE PLPGSQL;
802 DROP FUNCTION asset.opac_ou_metarecord_copy_count (INT, BIGINT);
803 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
808 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;
810 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
815 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
819 actor.org_unit_descendants(ans.id) d
820 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
821 JOIN asset.copy cp ON (cp.id = av.copy_id)
822 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
826 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
833 $f$ LANGUAGE PLPGSQL;
835 DROP FUNCTION asset.opac_lasso_metarecord_copy_count (INT, BIGINT);
836 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
841 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;
843 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
848 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
852 actor.org_unit_descendants(ans.id) d
853 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
854 JOIN asset.copy cp ON (cp.id = av.copy_id)
855 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
859 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
866 $f$ LANGUAGE PLPGSQL;
868 DROP FUNCTION asset.staff_lasso_metarecord_copy_count (INT, BIGINT);
870 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
872 IF staff IS TRUE THEN
874 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
876 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
880 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
882 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
888 $f$ LANGUAGE PLPGSQL;
891 CREATE OR REPLACE VIEW reporter.simple_record AS
898 title.value AS title,
899 uniform_title.value AS uniform_title,
900 author.value AS author,
901 publisher.value AS publisher,
902 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
903 series_title.value AS series_title,
904 series_statement.value AS series_statement,
905 summary.value AS summary,
906 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
907 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
908 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
909 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
910 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
911 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
912 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
913 ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
914 FROM biblio.record_entry r
915 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
916 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
917 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
918 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
919 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
920 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
921 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
922 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
923 LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
924 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
925 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
926 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
928 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
934 FIRST(title.value) AS title,
935 FIRST(author.value) AS author,
936 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher,
937 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate,
938 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
939 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn
940 FROM biblio.record_entry r
941 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
942 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
943 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
944 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
945 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
946 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
950 ALTER TABLE money.credit_card_payment ADD COLUMN cc_order_number TEXT;
953 -- Circ matchpoint table changes
954 ALTER TABLE config.circ_matrix_matchpoint
955 ALTER COLUMN circulate DROP NOT NULL, -- Fallthrough enable
956 ALTER COLUMN circulate DROP DEFAULT, -- Stop defaulting to true to enable default to fallthrough
957 ALTER COLUMN duration_rule DROP NOT NULL, -- Fallthrough enable
958 ALTER COLUMN recurring_fine_rule DROP NOT NULL, -- Fallthrough enable
959 ALTER COLUMN max_fine_rule DROP NOT NULL, -- Fallthrough enable
960 ADD COLUMN renewals INT; -- Renewals override
962 -- Changing return types requires explicit dropping of old versions
963 DROP FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL );
964 DROP FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL );
965 DROP FUNCTION action.item_user_circ_test( INT, BIGINT, INT );
966 DROP FUNCTION action.item_user_renew_test( INT, BIGINT, INT );
969 CREATE TYPE action.found_circ_matrix_matchpoint AS ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] );
971 -- Helper function - For manual calling, it can be easier to pass in IDs instead of objects
972 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.found_circ_matrix_matchpoint AS $func$
974 item_object asset.copy%ROWTYPE;
975 user_object actor.usr%ROWTYPE;
977 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
978 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
980 RETURN QUERY SELECT * FROM action.find_circ_matrix_matchpoint( context_ou, item_object, user_object, renewal );
982 $func$ LANGUAGE plpgsql;
984 CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL );
986 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
988 user_object actor.usr%ROWTYPE;
989 standing_penalty config.standing_penalty%ROWTYPE;
990 item_object asset.copy%ROWTYPE;
991 item_status_object config.copy_status%ROWTYPE;
992 item_location_object asset.copy_location%ROWTYPE;
993 result action.circ_matrix_test_result;
994 circ_test action.found_circ_matrix_matchpoint;
995 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
996 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
997 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
998 hold_ratio action.hold_stats%ROWTYPE;
1001 context_org_list INT[];
1004 -- Assume success unless we hit a failure condition
1005 result.success := TRUE;
1007 -- Fail if the user is BARRED
1008 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1010 -- Fail if we couldn't find the user
1011 IF user_object.id IS NULL THEN
1012 result.fail_part := 'no_user';
1013 result.success := FALSE;
1019 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1021 -- Fail if we couldn't find the item
1022 IF item_object.id IS NULL THEN
1023 result.fail_part := 'no_item';
1024 result.success := FALSE;
1030 IF user_object.barred IS TRUE THEN
1031 result.fail_part := 'actor.usr.barred';
1032 result.success := FALSE;
1037 -- Fail if the item can't circulate
1038 IF item_object.circulate IS FALSE THEN
1039 result.fail_part := 'asset.copy.circulate';
1040 result.success := FALSE;
1045 -- Fail if the item isn't in a circulateable status on a non-renewal
1046 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
1047 result.fail_part := 'asset.copy.status';
1048 result.success := FALSE;
1051 ELSIF renewal AND item_object.status <> 1 THEN
1052 result.fail_part := 'asset.copy.status';
1053 result.success := FALSE;
1058 -- Fail if the item can't circulate because of the shelving location
1059 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
1060 IF item_location_object.circulate IS FALSE THEN
1061 result.fail_part := 'asset.copy_location.circulate';
1062 result.success := FALSE;
1067 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
1069 circ_matchpoint := circ_test.matchpoint;
1070 result.matchpoint := circ_matchpoint.id;
1071 result.circulate := circ_matchpoint.circulate;
1072 result.duration_rule := circ_matchpoint.duration_rule;
1073 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
1074 result.max_fine_rule := circ_matchpoint.max_fine_rule;
1075 result.hard_due_date := circ_matchpoint.hard_due_date;
1076 result.renewals := circ_matchpoint.renewals;
1077 result.buildrows := circ_test.buildrows;
1079 -- Fail if we couldn't find a matchpoint
1080 IF circ_test.success = false THEN
1081 result.fail_part := 'no_matchpoint';
1082 result.success := FALSE;
1085 RETURN; -- All tests after this point require a matchpoint. No sense in running on an incomplete or missing one.
1088 -- Apparently....use the circ matchpoint org unit to determine what org units are valid.
1089 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit );
1092 penalty_type = '%RENEW%';
1094 penalty_type = '%CIRC%';
1097 FOR standing_penalty IN
1098 SELECT DISTINCT csp.*
1099 FROM actor.usr_standing_penalty usp
1100 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1101 WHERE usr = match_user
1102 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1103 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1104 AND csp.block_list LIKE penalty_type LOOP
1106 result.fail_part := standing_penalty.name;
1107 result.success := FALSE;
1112 -- Fail if the test is set to hard non-circulating
1113 IF circ_matchpoint.circulate IS FALSE THEN
1114 result.fail_part := 'config.circ_matrix_test.circulate';
1115 result.success := FALSE;
1120 -- Fail if the total copy-hold ratio is too low
1121 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
1122 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
1123 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
1124 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
1125 result.success := FALSE;
1131 -- Fail if the available copy-hold ratio is too low
1132 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
1133 IF hold_ratio.hold_count IS NULL THEN
1134 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
1136 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
1137 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
1138 result.success := FALSE;
1144 -- Fail if the user has too many items with specific circ_modifiers checked out
1145 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
1146 SELECT INTO items_out COUNT(*)
1147 FROM action.circulation circ
1148 JOIN asset.copy cp ON (cp.id = circ.target_copy)
1149 WHERE circ.usr = match_user
1150 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
1151 AND circ.checkin_time IS NULL
1152 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
1153 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);
1154 IF items_out >= out_by_circ_mod.items_out THEN
1155 result.fail_part := 'config.circ_matrix_circ_mod_test';
1156 result.success := FALSE;
1162 -- If we passed everything, return the successful matchpoint id
1169 $func$ LANGUAGE plpgsql;
1171 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
1172 SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
1173 $func$ LANGUAGE SQL;
1175 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
1176 SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
1177 $func$ LANGUAGE SQL;
1180 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$
1185 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;
1187 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
1192 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
1196 actor.org_unit_descendants(ans.id) d
1197 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
1198 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
1202 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1209 $f$ LANGUAGE PLPGSQL;
1211 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$
1216 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;
1218 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
1223 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
1227 actor.org_unit_descendants(ans.id) d
1228 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
1229 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
1233 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1240 $f$ LANGUAGE PLPGSQL;
1242 DROP FUNCTION asset.staff_ou_metarecord_copy_count (INT, BIGINT);
1243 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
1248 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;
1250 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
1255 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
1259 actor.org_unit_descendants(ans.id) d
1260 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
1261 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
1262 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
1266 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1273 $f$ LANGUAGE PLPGSQL;
1275 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
1280 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;
1282 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
1287 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
1291 actor.org_unit_descendants(ans.id) d
1292 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
1293 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
1294 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
1298 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1305 $f$ LANGUAGE PLPGSQL;
1309 UPDATE config.org_unit_setting_type
1310 SET description = 'Amount of time before a hold expires at which point the patron should be alerted. Examples: "5 days", "1 hour"'
1311 WHERE label = 'Holds: Expire Alert Interval';
1313 UPDATE config.org_unit_setting_type
1314 SET description = 'When predicting the amount of time a patron will be waiting for a hold to be fulfilled, this is the default estimated length of time to assume an item will be checked out. Examples: "3 weeks", "7 days"'
1315 WHERE label = 'Holds: Default Estimated Wait';
1317 UPDATE config.org_unit_setting_type
1318 SET description = 'When predicting the amount of time a patron will be waiting for a hold to be fulfilled, this is the minimum estimated length of time to assume an item will be checked out. Examples: "1 week", "5 days"'
1319 WHERE label = 'Holds: Minimum Estimated Wait';
1321 UPDATE config.org_unit_setting_type
1322 SET description = 'The purpose is to provide an interval of time after an item goes into the on-holds-shelf status before it appears to patrons that it is actually on the holds shelf. This gives staff time to process the item before it shows as ready-for-pickup. Examples: "5 days", "1 hour"'
1323 WHERE label = 'Hold Shelf Status Delay';
1326 UPDATE config.metabib_field
1327 SET xpath = $$//mods32:mods/mods32:subject$$
1328 WHERE field_class = 'subject' AND name = 'complete';
1330 UPDATE config.metabib_field
1331 SET xpath = $$//marc:datafield[@tag='099']$$
1332 WHERE field_class = 'identifier' AND name = 'bibcn';
1335 CREATE TABLE config.record_attr_definition (
1336 name TEXT PRIMARY KEY,
1337 label TEXT NOT NULL, -- I18N
1339 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
1340 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
1342 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
1343 tag TEXT, -- LIKE format
1344 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
1346 -- This is used for both tag/sf and xpath entries
1349 -- For xpath-extracted attrs
1351 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1356 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
1358 -- For phys-char fields
1359 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
1362 CREATE TABLE config.record_attr_index_norm_map (
1363 id SERIAL PRIMARY KEY,
1364 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1365 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1367 pos INT NOT NULL DEFAULT 0
1370 CREATE TABLE config.coded_value_map (
1371 id SERIAL PRIMARY KEY,
1372 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1374 value TEXT NOT NULL,
1378 -- record attributes
1379 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('alph','Alph','Alph');
1380 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('audience','Audn','Audn');
1381 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('bib_level','BLvl','BLvl');
1382 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('biog','Biog','Biog');
1383 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('conf','Conf','Conf');
1384 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('control_type','Ctrl','Ctrl');
1385 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ctry','Ctry','Ctry');
1386 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date1','Date1','Date1');
1387 INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter) values ('pubdate','Pub Date','Date1',TRUE,FALSE);
1388 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date2','Date2','Date2');
1389 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('cat_form','Desc','Desc');
1390 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('pub_status','DtSt','DtSt');
1391 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('enc_level','ELvl','ELvl');
1392 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('fest','Fest','Fest');
1393 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_form','Form','Form');
1394 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('gpub','GPub','GPub');
1395 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ills','Ills','Ills');
1396 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('indx','Indx','Indx');
1397 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_lang','Lang','Lang');
1398 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('lit_form','LitF','LitF');
1399 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('mrec','MRec','MRec');
1400 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ff_sl','S/L','S/L');
1401 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('type_mat','TMat','TMat');
1402 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_type','Type','Type');
1403 INSERT INTO config.record_attr_definition (name,label,phys_char_sf) values ('vr_format','Videorecording format',72);
1404 INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('titlesort','Title',TRUE,FALSE,'tnf');
1405 INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('authorsort','Author',TRUE,FALSE,'1%');
1407 INSERT INTO config.coded_value_map (ctype,code,value,description)
1408 SELECT 'item_lang' AS ctype, code, value, NULL FROM config.language_map
1410 SELECT 'bib_level' AS ctype, code, value, NULL FROM config.bib_level_map
1412 SELECT 'item_form' AS ctype, code, value, NULL FROM config.item_form_map
1414 SELECT 'item_type' AS ctype, code, value, NULL FROM config.item_type_map
1416 SELECT 'lit_form' AS ctype, code, value, description FROM config.lit_form_map
1418 SELECT 'audience' AS ctype, code, value, description FROM config.audience_map
1420 SELECT 'vr_format' AS ctype, code, value, NULL FROM config.videorecording_format_map;
1422 ALTER TABLE config.i18n_locale DROP CONSTRAINT i18n_locale_marc_code_fkey;
1424 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_form_fkey;
1425 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_type_fkey;
1426 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_vr_format_fkey;
1428 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_form_fkey;
1429 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_type_fkey;
1430 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_vr_format_fkey;
1432 DROP TABLE config.language_map;
1433 DROP TABLE config.bib_level_map;
1434 DROP TABLE config.item_form_map;
1435 DROP TABLE config.item_type_map;
1436 DROP TABLE config.lit_form_map;
1437 DROP TABLE config.audience_map;
1438 DROP TABLE config.videorecording_format_map;
1440 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clm.value' AND ccvm.ctype = 'item_lang' AND identity_value = ccvm.code;
1441 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cblvl.value' AND ccvm.ctype = 'bib_level' AND identity_value = ccvm.code;
1442 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cifm.value' AND ccvm.ctype = 'item_form' AND identity_value = ccvm.code;
1443 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'citm.value' AND ccvm.ctype = 'item_type' AND identity_value = ccvm.code;
1444 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.value' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code;
1445 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.value' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code;
1446 UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cvrfm.value' AND ccvm.ctype = 'vr_format' AND identity_value = ccvm.code;
1448 UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.description' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code;
1449 UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.description' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code;
1451 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
1452 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
1453 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
1454 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
1455 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
1456 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
1457 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
1459 CREATE TABLE metabib.record_attr (
1460 id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
1461 attrs HSTORE NOT NULL DEFAULT ''::HSTORE
1463 CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs);
1464 CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ( (attrs->'date1') );
1465 CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ( (attrs->'date1'), (attrs->'date2') );
1467 INSERT INTO metabib.record_attr (id,attrs)
1468 SELECT mrd.record, hstore(mrd) - '{id,record}'::TEXT[] FROM metabib.rec_descriptor mrd;
1470 -- Back-compat view ... we're moving to an HSTORE world
1471 CREATE TYPE metabib.rec_desc_type AS (
1489 DROP TABLE metabib.rec_descriptor CASCADE;
1491 CREATE VIEW metabib.rec_descriptor AS
1494 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
1495 FROM metabib.record_attr;
1497 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
1504 retval config.marc21_rec_type_map%ROWTYPE;
1506 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
1508 IF ldr IS NULL OR ldr = '' THEN
1509 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
1513 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
1514 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
1517 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
1518 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
1520 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
1522 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
1525 IF retval.code IS NULL THEN
1526 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
1531 $func$ LANGUAGE PLPGSQL;
1533 CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$
1534 SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1535 $func$ LANGUAGE SQL;
1537 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
1544 rtype := (vandelay.marc21_record_type( marc )).code;
1545 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
1546 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
1547 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
1550 val := REPEAT( ff_pos.default_val, ff_pos.length );
1556 $func$ LANGUAGE PLPGSQL;
1558 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
1559 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
1560 $func$ LANGUAGE SQL;
1562 CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
1563 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
1568 output biblio.record_ff_map%ROWTYPE;
1570 rtype := (vandelay.marc21_record_type( marc )).code;
1572 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
1573 output.ff_name := ff_pos.fixed_field;
1574 output.ff_value := NULL;
1576 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP
1577 output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
1578 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
1580 output.ff_value := NULL;
1587 $func$ LANGUAGE PLPGSQL;
1589 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
1590 SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1591 $func$ LANGUAGE SQL;
1593 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
1597 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
1598 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
1599 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
1600 retval biblio.marc21_physical_characteristics%ROWTYPE;
1603 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
1605 IF _007 IS NOT NULL AND _007 <> '' THEN
1606 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
1608 IF ptype.ptype_key IS NOT NULL THEN
1609 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
1610 SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length );
1612 IF pval.id IS NOT NULL THEN
1615 retval.ptype := ptype.ptype_key;
1616 retval.subfield := psf.id;
1617 retval.value := pval.id;
1627 $func$ LANGUAGE PLPGSQL;
1629 CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
1630 SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1631 $func$ LANGUAGE SQL;
1633 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
1635 transformed_xml TEXT;
1638 xfrm config.xml_transform%ROWTYPE;
1640 new_attrs HSTORE := ''::HSTORE;
1641 attr_def config.record_attr_definition%ROWTYPE;
1644 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
1645 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
1646 DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records
1647 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
1648 RETURN NEW; -- and we're done
1651 IF TG_OP = 'UPDATE' THEN -- re-ingest?
1652 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
1654 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
1659 -- Record authority linking
1660 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
1662 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
1665 -- Flatten and insert the mfr data
1666 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
1668 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
1670 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
1671 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
1673 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
1675 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
1676 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
1677 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
1678 WHERE record = NEW.id
1679 AND tag LIKE attr_def.tag
1681 WHEN attr_def.sf_list IS NOT NULL
1682 THEN POSITION(subfield IN attr_def.sf_list) > 0
1689 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
1690 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
1692 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
1694 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
1696 -- See if we can skip the XSLT ... it's expensive
1697 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1698 -- Can't skip the transform
1699 IF xfrm.xslt <> '---' THEN
1700 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
1702 transformed_xml := NEW.marc;
1705 prev_xfrm := xfrm.name;
1708 IF xfrm.name IS NULL THEN
1709 -- just grab the marcxml (empty) transform
1710 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
1711 prev_xfrm := xfrm.name;
1714 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
1716 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
1717 SELECT value::TEXT INTO attr_value
1718 FROM biblio.marc21_physical_characteristics(NEW.id)
1719 WHERE subfield = attr_def.phys_char_sf
1720 LIMIT 1; -- Just in case ...
1724 -- apply index normalizers to attr_value
1726 SELECT n.func AS func,
1727 n.param_count AS param_count,
1729 FROM config.index_normalizer n
1730 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
1731 WHERE attr = attr_def.name
1733 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1734 quote_literal( attr_value ) ||
1736 WHEN normalizer.param_count > 0
1737 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1740 ')' INTO attr_value;
1744 -- Add the new value to the hstore
1745 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
1749 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
1750 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
1752 UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id;
1758 -- Gather and insert the field entry data
1759 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
1761 -- Located URI magic
1762 IF TG_OP = 'INSERT' THEN
1763 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1765 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1768 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1770 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1774 -- (re)map metarecord-bib linking
1775 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
1776 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
1778 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1780 ELSE -- we're doing an update, and we're not deleted, remap
1781 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
1783 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1789 $func$ LANGUAGE PLPGSQL;
1791 DROP FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT );
1793 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
1794 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
1795 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1797 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
1798 SELECT approximate_date( $1, '0');
1799 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1801 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
1802 SELECT approximate_date( $1, '9');
1803 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1805 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
1806 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
1807 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1809 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
1810 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
1811 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1813 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
1818 # Find the first ISBN, force it to ISBN13 and return it
1822 foreach my $word (split(/\s/, $input)) {
1823 my $isbn = Business::ISBN->new($word);
1825 # First check the checksum; if it is not valid, fix it and add the original
1826 # bad-checksum ISBN to the output
1827 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
1828 $isbn->fix_checksum();
1831 # If we now have a valid ISBN, force it to ISBN13 and return it
1832 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
1835 $func$ LANGUAGE PLPERLU;
1837 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
1839 * Copyright (C) 2011 Equinox Software
1840 * Mike Rylander <mrylander@gmail.com>
1842 * Inspired by translate_isbn1013
1844 * The force_to_isbn13 function takes an input ISBN and returns the ISBN13
1845 * version without hypens and with a repaired checksum if the checksum was bad
1850 UPDATE config.metabib_field
1851 SET xpath = $$//marc:datafield[@tag='024' and @ind1='1']/marc:subfield[@code='a' or @code='z']$$
1852 WHERE field_class = 'identifier' AND name = 'upc';
1854 UPDATE config.metabib_field
1855 SET xpath = $$//marc:datafield[@tag='024' and @ind1='2']/marc:subfield[@code='a' or @code='z']$$
1856 WHERE field_class = 'identifier' AND name = 'ismn';
1858 UPDATE config.metabib_field
1859 SET xpath = $$//marc:datafield[@tag='024' and @ind1='3']/marc:subfield[@code='a' or @code='z']$$
1860 WHERE field_class = 'identifier' AND name = 'ean';
1862 UPDATE config.metabib_field
1863 SET xpath = $$//marc:datafield[@tag='024' and @ind1='0']/marc:subfield[@code='a' or @code='z']$$
1864 WHERE field_class = 'identifier' AND name = 'isrc';
1866 UPDATE config.metabib_field
1867 SET xpath = $$//marc:datafield[@tag='024' and @ind1='4']/marc:subfield[@code='a' or @code='z']$$
1868 WHERE field_class = 'identifier' AND name = 'sici';
1871 INSERT into config.org_unit_setting_type
1872 ( name, label, description, datatype ) VALUES
1874 ( 'ui.patron.edit.au.active.show',
1875 oils_i18n_gettext('ui.patron.edit.au.active.show', 'GUI: Show active field on patron registration', 'coust', 'label'),
1876 oils_i18n_gettext('ui.patron.edit.au.active.show', 'The active field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1878 ( 'ui.patron.edit.au.active.suggest',
1879 oils_i18n_gettext('ui.patron.edit.au.active.suggest', 'GUI: Suggest active field on patron registration', 'coust', 'label'),
1880 oils_i18n_gettext('ui.patron.edit.au.active.suggest', 'The active field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1882 ( 'ui.patron.edit.au.alert_message.show',
1883 oils_i18n_gettext('ui.patron.edit.au.alert_message.show', 'GUI: Show alert_message field on patron registration', 'coust', 'label'),
1884 oils_i18n_gettext('ui.patron.edit.au.alert_message.show', 'The alert_message field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1886 ( 'ui.patron.edit.au.alert_message.suggest',
1887 oils_i18n_gettext('ui.patron.edit.au.alert_message.suggest', 'GUI: Suggest alert_message field on patron registration', 'coust', 'label'),
1888 oils_i18n_gettext('ui.patron.edit.au.alert_message.suggest', 'The alert_message field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1890 ( 'ui.patron.edit.au.alias.show',
1891 oils_i18n_gettext('ui.patron.edit.au.alias.show', 'GUI: Show alias field on patron registration', 'coust', 'label'),
1892 oils_i18n_gettext('ui.patron.edit.au.alias.show', 'The alias field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1894 ( 'ui.patron.edit.au.alias.suggest',
1895 oils_i18n_gettext('ui.patron.edit.au.alias.suggest', 'GUI: Suggest alias field on patron registration', 'coust', 'label'),
1896 oils_i18n_gettext('ui.patron.edit.au.alias.suggest', 'The alias field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1898 ( 'ui.patron.edit.au.barred.show',
1899 oils_i18n_gettext('ui.patron.edit.au.barred.show', 'GUI: Show barred field on patron registration', 'coust', 'label'),
1900 oils_i18n_gettext('ui.patron.edit.au.barred.show', 'The barred field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1902 ( 'ui.patron.edit.au.barred.suggest',
1903 oils_i18n_gettext('ui.patron.edit.au.barred.suggest', 'GUI: Suggest barred field on patron registration', 'coust', 'label'),
1904 oils_i18n_gettext('ui.patron.edit.au.barred.suggest', 'The barred field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1906 ( 'ui.patron.edit.au.claims_never_checked_out_count.show',
1907 oils_i18n_gettext('ui.patron.edit.au.claims_never_checked_out_count.show', 'GUI: Show claims_never_checked_out_count field on patron registration', 'coust', 'label'),
1908 oils_i18n_gettext('ui.patron.edit.au.claims_never_checked_out_count.show', 'The claims_never_checked_out_count field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1910 ( 'ui.patron.edit.au.claims_never_checked_out_count.suggest',
1911 oils_i18n_gettext('ui.patron.edit.au.claims_never_checked_out_count.suggest', 'GUI: Suggest claims_never_checked_out_count field on patron registration', 'coust', 'label'),
1912 oils_i18n_gettext('ui.patron.edit.au.claims_never_checked_out_count.suggest', 'The claims_never_checked_out_count field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1914 ( 'ui.patron.edit.au.claims_returned_count.show',
1915 oils_i18n_gettext('ui.patron.edit.au.claims_returned_count.show', 'GUI: Show claims_returned_count field on patron registration', 'coust', 'label'),
1916 oils_i18n_gettext('ui.patron.edit.au.claims_returned_count.show', 'The claims_returned_count field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1918 ( 'ui.patron.edit.au.claims_returned_count.suggest',
1919 oils_i18n_gettext('ui.patron.edit.au.claims_returned_count.suggest', 'GUI: Suggest claims_returned_count field on patron registration', 'coust', 'label'),
1920 oils_i18n_gettext('ui.patron.edit.au.claims_returned_count.suggest', 'The claims_returned_count field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1922 ( 'ui.patron.edit.au.day_phone.example',
1923 oils_i18n_gettext('ui.patron.edit.au.day_phone.example', 'GUI: Example for day_phone field on patron registration', 'coust', 'label'),
1924 oils_i18n_gettext('ui.patron.edit.au.day_phone.example', 'The Example for validation on the day_phone field in patron registration.', 'coust', 'description'),
1926 ( 'ui.patron.edit.au.day_phone.regex',
1927 oils_i18n_gettext('ui.patron.edit.au.day_phone.regex', 'GUI: Regex for day_phone field on patron registration', 'coust', 'label'),
1928 oils_i18n_gettext('ui.patron.edit.au.day_phone.regex', 'The Regular Expression for validation on the day_phone field in patron registration.', 'coust', 'description'),
1930 ( 'ui.patron.edit.au.day_phone.require',
1931 oils_i18n_gettext('ui.patron.edit.au.day_phone.require', 'GUI: Require day_phone field on patron registration', 'coust', 'label'),
1932 oils_i18n_gettext('ui.patron.edit.au.day_phone.require', 'The day_phone field will be required on the patron registration screen.', 'coust', 'description'),
1934 ( 'ui.patron.edit.au.day_phone.show',
1935 oils_i18n_gettext('ui.patron.edit.au.day_phone.show', 'GUI: Show day_phone field on patron registration', 'coust', 'label'),
1936 oils_i18n_gettext('ui.patron.edit.au.day_phone.show', 'The day_phone field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1938 ( 'ui.patron.edit.au.day_phone.suggest',
1939 oils_i18n_gettext('ui.patron.edit.au.day_phone.suggest', 'GUI: Suggest day_phone field on patron registration', 'coust', 'label'),
1940 oils_i18n_gettext('ui.patron.edit.au.day_phone.suggest', 'The day_phone field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1942 ( 'ui.patron.edit.au.dob.calendar',
1943 oils_i18n_gettext('ui.patron.edit.au.dob.calendar', 'GUI: Show calendar widget for dob field on patron registration', 'coust', 'label'),
1944 oils_i18n_gettext('ui.patron.edit.au.dob.calendar', 'If set the calendar widget will appear when editing the dob field on the patron registration form.', 'coust', 'description'),
1946 ( 'ui.patron.edit.au.dob.require',
1947 oils_i18n_gettext('ui.patron.edit.au.dob.require', 'GUI: Require dob field on patron registration', 'coust', 'label'),
1948 oils_i18n_gettext('ui.patron.edit.au.dob.require', 'The dob field will be required on the patron registration screen.', 'coust', 'description'),
1950 ( 'ui.patron.edit.au.dob.show',
1951 oils_i18n_gettext('ui.patron.edit.au.dob.show', 'GUI: Show dob field on patron registration', 'coust', 'label'),
1952 oils_i18n_gettext('ui.patron.edit.au.dob.show', 'The dob field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1954 ( 'ui.patron.edit.au.dob.suggest',
1955 oils_i18n_gettext('ui.patron.edit.au.dob.suggest', 'GUI: Suggest dob field on patron registration', 'coust', 'label'),
1956 oils_i18n_gettext('ui.patron.edit.au.dob.suggest', 'The dob field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1958 ( 'ui.patron.edit.au.email.example',
1959 oils_i18n_gettext('ui.patron.edit.au.email.example', 'GUI: Example for email field on patron registration', 'coust', 'label'),
1960 oils_i18n_gettext('ui.patron.edit.au.email.example', 'The Example for validation on the email field in patron registration.', 'coust', 'description'),
1962 ( 'ui.patron.edit.au.email.regex',
1963 oils_i18n_gettext('ui.patron.edit.au.email.regex', 'GUI: Regex for email field on patron registration', 'coust', 'label'),
1964 oils_i18n_gettext('ui.patron.edit.au.email.regex', 'The Regular Expression for validation on the email field in patron registration.', 'coust', 'description'),
1966 ( 'ui.patron.edit.au.email.require',
1967 oils_i18n_gettext('ui.patron.edit.au.email.require', 'GUI: Require email field on patron registration', 'coust', 'label'),
1968 oils_i18n_gettext('ui.patron.edit.au.email.require', 'The email field will be required on the patron registration screen.', 'coust', 'description'),
1970 ( 'ui.patron.edit.au.email.show',
1971 oils_i18n_gettext('ui.patron.edit.au.email.show', 'GUI: Show email field on patron registration', 'coust', 'label'),
1972 oils_i18n_gettext('ui.patron.edit.au.email.show', 'The email field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1974 ( 'ui.patron.edit.au.email.suggest',
1975 oils_i18n_gettext('ui.patron.edit.au.email.suggest', 'GUI: Suggest email field on patron registration', 'coust', 'label'),
1976 oils_i18n_gettext('ui.patron.edit.au.email.suggest', 'The email field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1978 ( 'ui.patron.edit.au.evening_phone.example',
1979 oils_i18n_gettext('ui.patron.edit.au.evening_phone.example', 'GUI: Example for evening_phone field on patron registration', 'coust', 'label'),
1980 oils_i18n_gettext('ui.patron.edit.au.evening_phone.example', 'The Example for validation on the evening_phone field in patron registration.', 'coust', 'description'),
1982 ( 'ui.patron.edit.au.evening_phone.regex',
1983 oils_i18n_gettext('ui.patron.edit.au.evening_phone.regex', 'GUI: Regex for evening_phone field on patron registration', 'coust', 'label'),
1984 oils_i18n_gettext('ui.patron.edit.au.evening_phone.regex', 'The Regular Expression for validation on the evening_phone field in patron registration.', 'coust', 'description'),
1986 ( 'ui.patron.edit.au.evening_phone.require',
1987 oils_i18n_gettext('ui.patron.edit.au.evening_phone.require', 'GUI: Require evening_phone field on patron registration', 'coust', 'label'),
1988 oils_i18n_gettext('ui.patron.edit.au.evening_phone.require', 'The evening_phone field will be required on the patron registration screen.', 'coust', 'description'),
1990 ( 'ui.patron.edit.au.evening_phone.show',
1991 oils_i18n_gettext('ui.patron.edit.au.evening_phone.show', 'GUI: Show evening_phone field on patron registration', 'coust', 'label'),
1992 oils_i18n_gettext('ui.patron.edit.au.evening_phone.show', 'The evening_phone field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
1994 ( 'ui.patron.edit.au.evening_phone.suggest',
1995 oils_i18n_gettext('ui.patron.edit.au.evening_phone.suggest', 'GUI: Suggest evening_phone field on patron registration', 'coust', 'label'),
1996 oils_i18n_gettext('ui.patron.edit.au.evening_phone.suggest', 'The evening_phone field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
1998 ( 'ui.patron.edit.au.ident_value.show',
1999 oils_i18n_gettext('ui.patron.edit.au.ident_value.show', 'GUI: Show ident_value field on patron registration', 'coust', 'label'),
2000 oils_i18n_gettext('ui.patron.edit.au.ident_value.show', 'The ident_value field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2002 ( 'ui.patron.edit.au.ident_value.suggest',
2003 oils_i18n_gettext('ui.patron.edit.au.ident_value.suggest', 'GUI: Suggest ident_value field on patron registration', 'coust', 'label'),
2004 oils_i18n_gettext('ui.patron.edit.au.ident_value.suggest', 'The ident_value field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2006 ( 'ui.patron.edit.au.ident_value2.show',
2007 oils_i18n_gettext('ui.patron.edit.au.ident_value2.show', 'GUI: Show ident_value2 field on patron registration', 'coust', 'label'),
2008 oils_i18n_gettext('ui.patron.edit.au.ident_value2.show', 'The ident_value2 field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2010 ( 'ui.patron.edit.au.ident_value2.suggest',
2011 oils_i18n_gettext('ui.patron.edit.au.ident_value2.suggest', 'GUI: Suggest ident_value2 field on patron registration', 'coust', 'label'),
2012 oils_i18n_gettext('ui.patron.edit.au.ident_value2.suggest', 'The ident_value2 field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2014 ( 'ui.patron.edit.au.juvenile.show',
2015 oils_i18n_gettext('ui.patron.edit.au.juvenile.show', 'GUI: Show juvenile field on patron registration', 'coust', 'label'),
2016 oils_i18n_gettext('ui.patron.edit.au.juvenile.show', 'The juvenile field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2018 ( 'ui.patron.edit.au.juvenile.suggest',
2019 oils_i18n_gettext('ui.patron.edit.au.juvenile.suggest', 'GUI: Suggest juvenile field on patron registration', 'coust', 'label'),
2020 oils_i18n_gettext('ui.patron.edit.au.juvenile.suggest', 'The juvenile field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2022 ( 'ui.patron.edit.au.master_account.show',
2023 oils_i18n_gettext('ui.patron.edit.au.master_account.show', 'GUI: Show master_account field on patron registration', 'coust', 'label'),
2024 oils_i18n_gettext('ui.patron.edit.au.master_account.show', 'The master_account field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2026 ( 'ui.patron.edit.au.master_account.suggest',
2027 oils_i18n_gettext('ui.patron.edit.au.master_account.suggest', 'GUI: Suggest master_account field on patron registration', 'coust', 'label'),
2028 oils_i18n_gettext('ui.patron.edit.au.master_account.suggest', 'The master_account field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2030 ( 'ui.patron.edit.au.other_phone.example',
2031 oils_i18n_gettext('ui.patron.edit.au.other_phone.example', 'GUI: Example for other_phone field on patron registration', 'coust', 'label'),
2032 oils_i18n_gettext('ui.patron.edit.au.other_phone.example', 'The Example for validation on the other_phone field in patron registration.', 'coust', 'description'),
2034 ( 'ui.patron.edit.au.other_phone.regex',
2035 oils_i18n_gettext('ui.patron.edit.au.other_phone.regex', 'GUI: Regex for other_phone field on patron registration', 'coust', 'label'),
2036 oils_i18n_gettext('ui.patron.edit.au.other_phone.regex', 'The Regular Expression for validation on the other_phone field in patron registration.', 'coust', 'description'),
2038 ( 'ui.patron.edit.au.other_phone.require',
2039 oils_i18n_gettext('ui.patron.edit.au.other_phone.require', 'GUI: Require other_phone field on patron registration', 'coust', 'label'),
2040 oils_i18n_gettext('ui.patron.edit.au.other_phone.require', 'The other_phone field will be required on the patron registration screen.', 'coust', 'description'),
2042 ( 'ui.patron.edit.au.other_phone.show',
2043 oils_i18n_gettext('ui.patron.edit.au.other_phone.show', 'GUI: Show other_phone field on patron registration', 'coust', 'label'),
2044 oils_i18n_gettext('ui.patron.edit.au.other_phone.show', 'The other_phone field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2046 ( 'ui.patron.edit.au.other_phone.suggest',
2047 oils_i18n_gettext('ui.patron.edit.au.other_phone.suggest', 'GUI: Suggest other_phone field on patron registration', 'coust', 'label'),
2048 oils_i18n_gettext('ui.patron.edit.au.other_phone.suggest', 'The other_phone field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2050 ( 'ui.patron.edit.au.second_given_name.show',
2051 oils_i18n_gettext('ui.patron.edit.au.second_given_name.show', 'GUI: Show second_given_name field on patron registration', 'coust', 'label'),
2052 oils_i18n_gettext('ui.patron.edit.au.second_given_name.show', 'The second_given_name field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2054 ( 'ui.patron.edit.au.second_given_name.suggest',
2055 oils_i18n_gettext('ui.patron.edit.au.second_given_name.suggest', 'GUI: Suggest second_given_name field on patron registration', 'coust', 'label'),
2056 oils_i18n_gettext('ui.patron.edit.au.second_given_name.suggest', 'The second_given_name field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2058 ( 'ui.patron.edit.au.suffix.show',
2059 oils_i18n_gettext('ui.patron.edit.au.suffix.show', 'GUI: Show suffix field on patron registration', 'coust', 'label'),
2060 oils_i18n_gettext('ui.patron.edit.au.suffix.show', 'The suffix field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', 'coust', 'description'),
2062 ( 'ui.patron.edit.au.suffix.suggest',
2063 oils_i18n_gettext('ui.patron.edit.au.suffix.suggest', 'GUI: Suggest suffix field on patron registration', 'coust', 'label'),
2064 oils_i18n_gettext('ui.patron.edit.au.suffix.suggest', 'The suffix field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', 'coust', 'description'),
2066 ( 'ui.patron.edit.aua.county.require',
2067 oils_i18n_gettext('ui.patron.edit.aua.county.require', 'GUI: Require county field on patron registration', 'coust', 'label'),
2068 oils_i18n_gettext('ui.patron.edit.aua.county.require', 'The county field will be required on the patron registration screen.', 'coust', 'description'),
2070 ( 'ui.patron.edit.aua.post_code.example',
2071 oils_i18n_gettext('ui.patron.edit.aua.post_code.example', 'GUI: Example for post_code field on patron registration', 'coust', 'label'),
2072 oils_i18n_gettext('ui.patron.edit.aua.post_code.example', 'The Example for validation on the post_code field in patron registration.', 'coust', 'description'),
2074 ( 'ui.patron.edit.aua.post_code.regex',
2075 oils_i18n_gettext('ui.patron.edit.aua.post_code.regex', 'GUI: Regex for post_code field on patron registration', 'coust', 'label'),
2076 oils_i18n_gettext('ui.patron.edit.aua.post_code.regex', 'The Regular Expression for validation on the post_code field in patron registration.', 'coust', 'description'),
2078 ( 'ui.patron.edit.default_suggested',
2079 oils_i18n_gettext('ui.patron.edit.default_suggested', 'GUI: Default showing suggested patron registration fields', 'coust', 'label'),
2080 oils_i18n_gettext('ui.patron.edit.default_suggested', 'Instead of All fields, show just suggested fields in patron registration by default.', 'coust', 'description'),
2082 ( 'ui.patron.edit.phone.example',
2083 oils_i18n_gettext('ui.patron.edit.phone.example', 'GUI: Example for phone fields on patron registration', 'coust', 'label'),
2084 oils_i18n_gettext('ui.patron.edit.phone.example', 'The Example for validation on phone fields in patron registration. Applies to all phone fields without their own setting.', 'coust', 'description'),
2086 ( 'ui.patron.edit.phone.regex',
2087 oils_i18n_gettext('ui.patron.edit.phone.regex', 'GUI: Regex for phone fields on patron registration', 'coust', 'label'),
2088 oils_i18n_gettext('ui.patron.edit.phone.regex', 'The Regular Expression for validation on phone fields in patron registration. Applies to all phone fields without their own setting.', 'coust', 'description'),
2091 -- update actor.usr_address indexes
2092 DROP INDEX IF EXISTS actor.actor_usr_addr_street1_idx;
2093 DROP INDEX IF EXISTS actor.actor_usr_addr_street2_idx;
2094 DROP INDEX IF EXISTS actor.actor_usr_addr_city_idx;
2095 DROP INDEX IF EXISTS actor.actor_usr_addr_state_idx;
2096 DROP INDEX IF EXISTS actor.actor_usr_addr_post_code_idx;
2098 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
2099 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
2100 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
2101 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
2102 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
2104 -- update actor.usr indexes
2105 DROP INDEX IF EXISTS actor.actor_usr_first_given_name_idx;
2106 DROP INDEX IF EXISTS actor.actor_usr_second_given_name_idx;
2107 DROP INDEX IF EXISTS actor.actor_usr_family_name_idx;
2108 DROP INDEX IF EXISTS actor.actor_usr_email_idx;
2109 DROP INDEX IF EXISTS actor.actor_usr_day_phone_idx;
2110 DROP INDEX IF EXISTS actor.actor_usr_evening_phone_idx;
2111 DROP INDEX IF EXISTS actor.actor_usr_other_phone_idx;
2112 DROP INDEX IF EXISTS actor.actor_usr_ident_value_idx;
2113 DROP INDEX IF EXISTS actor.actor_usr_ident_value2_idx;
2115 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
2116 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
2117 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
2118 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
2119 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
2120 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
2121 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
2122 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
2123 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
2125 -- update actor.card indexes
2126 DROP INDEX IF EXISTS actor.actor_card_barcode_evergreen_lowercase_idx;
2127 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
2129 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
2138 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
2140 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
2142 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
2143 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
2145 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
2146 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
2147 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
2148 IF exact_id IS NOT NULL THEN
2149 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
2154 IF exact_id IS NULL THEN
2155 FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP
2157 -- All numbers? check for an id match
2158 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
2159 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
2160 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
2164 -- Looks like an ISBN? check for an isbn match
2165 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
2166 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
2167 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
2169 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
2173 -- subcheck for isbn-as-tcn
2174 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
2175 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
2179 -- check for an OCLC tcn_value match
2180 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
2181 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
2182 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
2186 -- check for a direct tcn_value match
2187 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
2188 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
2191 -- check for a direct item barcode match
2194 FROM biblio.record_entry b
2195 JOIN asset.call_number cn ON (cn.record = b.id)
2196 JOIN asset.copy cp ON (cp.call_number = cn.id)
2197 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
2199 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
2207 $func$ LANGUAGE PLPGSQL;
2211 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
2212 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
2213 # thus could probably be considered a derived work, although nothing was
2214 # directly copied - but to err on the safe side of providing attribution:
2215 # Copyright (C) 2007 LibLime
2216 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
2217 # Licensed under the GPL v2 or later
2222 # Converts the callnumber to uppercase
2223 # Strips spaces from start and end of the call number
2224 # Converts anything other than letters, digits, and periods into spaces
2225 # Collapses multiple spaces into a single underscore
2226 my $callnum = uc(shift);
2227 $callnum =~ s/^\s//g;
2228 $callnum =~ s/\s$//g;
2229 # NOTE: this previously used underscores, but this caused sorting issues
2230 # for the "before" half of page 0 on CN browse, sorting CNs containing a
2231 # decimal before "whole number" CNs
2232 $callnum =~ s/[^A-Z0-9_.]/ /g;
2233 $callnum =~ s/ {2,}/ /g;
2236 $func$ LANGUAGE PLPERLU;
2241 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('language','Language (2.0 compat version)','Lang');
2242 UPDATE metabib.record_attr SET attrs = attrs || hstore('language',(attrs->'item_lang'));
2246 UPDATE asset.call_number_class
2247 SET field = '080ab,082ab,092abef'
2252 UPDATE asset.call_number_class
2253 SET field = '050ab,055ab,090abef'
2258 -- Using a tool such as pgadmin to run this script may fail
2259 -- If it does, try psql command line.
2261 -- Change this to FALSE to disable updating existing circs
2262 -- Otherwise will use the fine interval for the grace period
2268 ALTER TABLE config.circ_matrix_matchpoint
2269 ADD COLUMN grace_period INTERVAL;
2271 ALTER TABLE config.rule_recurring_fine
2272 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '1 day';
2274 ALTER TABLE action.circulation
2275 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds';
2277 ALTER TABLE action.aged_circulation
2278 ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds';
2280 -- Remove defaults needed to stop null complaints
2282 ALTER TABLE action.circulation
2283 ALTER COLUMN grace_period DROP DEFAULT;
2285 ALTER TABLE action.aged_circulation
2286 ALTER COLUMN grace_period DROP DEFAULT;
2290 DROP VIEW action.all_circulation;
2291 DROP VIEW action.open_circulation;
2292 DROP VIEW action.billable_circulations;
2296 CREATE OR REPLACE VIEW action.all_circulation AS
2297 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
2298 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
2299 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
2300 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
2301 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
2302 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
2303 FROM action.aged_circulation
2305 SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
2306 cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
2307 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
2308 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
2309 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
2310 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
2312 FROM action.circulation circ
2313 JOIN asset.copy cp ON (circ.target_copy = cp.id)
2314 JOIN asset.call_number cn ON (cp.call_number = cn.id)
2315 JOIN actor.usr p ON (circ.usr = p.id)
2316 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
2317 LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
2319 CREATE OR REPLACE VIEW action.open_circulation AS
2321 FROM action.circulation
2322 WHERE checkin_time IS NULL
2326 CREATE OR REPLACE VIEW action.billable_circulations AS
2328 FROM action.circulation
2329 WHERE xact_finish IS NULL;
2331 -- Drop Functions that rely on types
2333 DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT, BOOL);
2334 DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT);
2335 DROP FUNCTION action.item_user_renew_test(INT, BIGINT, INT);
2337 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
2339 user_object actor.usr%ROWTYPE;
2340 standing_penalty config.standing_penalty%ROWTYPE;
2341 item_object asset.copy%ROWTYPE;
2342 item_status_object config.copy_status%ROWTYPE;
2343 item_location_object asset.copy_location%ROWTYPE;
2344 result action.circ_matrix_test_result;
2345 circ_test action.found_circ_matrix_matchpoint;
2346 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
2347 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
2348 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
2349 hold_ratio action.hold_stats%ROWTYPE;
2352 context_org_list INT[];
2355 -- Assume success unless we hit a failure condition
2356 result.success := TRUE;
2358 -- Fail if the user is BARRED
2359 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
2361 -- Fail if we couldn't find the user
2362 IF user_object.id IS NULL THEN
2363 result.fail_part := 'no_user';
2364 result.success := FALSE;
2370 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
2372 -- Fail if we couldn't find the item
2373 IF item_object.id IS NULL THEN
2374 result.fail_part := 'no_item';
2375 result.success := FALSE;
2381 IF user_object.barred IS TRUE THEN
2382 result.fail_part := 'actor.usr.barred';
2383 result.success := FALSE;
2388 -- Fail if the item can't circulate
2389 IF item_object.circulate IS FALSE THEN
2390 result.fail_part := 'asset.copy.circulate';
2391 result.success := FALSE;
2396 -- Fail if the item isn't in a circulateable status on a non-renewal
2397 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
2398 result.fail_part := 'asset.copy.status';
2399 result.success := FALSE;
2402 ELSIF renewal AND item_object.status <> 1 THEN
2403 result.fail_part := 'asset.copy.status';
2404 result.success := FALSE;
2409 -- Fail if the item can't circulate because of the shelving location
2410 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
2411 IF item_location_object.circulate IS FALSE THEN
2412 result.fail_part := 'asset.copy_location.circulate';
2413 result.success := FALSE;
2418 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
2420 circ_matchpoint := circ_test.matchpoint;
2421 result.matchpoint := circ_matchpoint.id;
2422 result.circulate := circ_matchpoint.circulate;
2423 result.duration_rule := circ_matchpoint.duration_rule;
2424 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
2425 result.max_fine_rule := circ_matchpoint.max_fine_rule;
2426 result.hard_due_date := circ_matchpoint.hard_due_date;
2427 result.renewals := circ_matchpoint.renewals;
2428 result.grace_period := circ_matchpoint.grace_period;
2429 result.buildrows := circ_test.buildrows;
2431 -- Fail if we couldn't find a matchpoint
2432 IF circ_test.success = false THEN
2433 result.fail_part := 'no_matchpoint';
2434 result.success := FALSE;
2437 RETURN; -- All tests after this point require a matchpoint. No sense in running on an incomplete or missing one.
2440 -- Apparently....use the circ matchpoint org unit to determine what org units are valid.
2441 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit );
2444 penalty_type = '%RENEW%';
2446 penalty_type = '%CIRC%';
2449 FOR standing_penalty IN
2450 SELECT DISTINCT csp.*
2451 FROM actor.usr_standing_penalty usp
2452 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
2453 WHERE usr = match_user
2454 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
2455 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
2456 AND csp.block_list LIKE penalty_type LOOP
2458 result.fail_part := standing_penalty.name;
2459 result.success := FALSE;
2464 -- Fail if the test is set to hard non-circulating
2465 IF circ_matchpoint.circulate IS FALSE THEN
2466 result.fail_part := 'config.circ_matrix_test.circulate';
2467 result.success := FALSE;
2472 -- Fail if the total copy-hold ratio is too low
2473 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
2474 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
2475 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
2476 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
2477 result.success := FALSE;
2483 -- Fail if the available copy-hold ratio is too low
2484 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
2485 IF hold_ratio.hold_count IS NULL THEN
2486 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
2488 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
2489 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
2490 result.success := FALSE;
2496 -- Fail if the user has too many items with specific circ_modifiers checked out
2497 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
2498 SELECT INTO items_out COUNT(*)
2499 FROM action.circulation circ
2500 JOIN asset.copy cp ON (cp.id = circ.target_copy)
2501 WHERE circ.usr = match_user
2502 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
2503 AND circ.checkin_time IS NULL
2504 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
2505 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);
2506 IF items_out >= out_by_circ_mod.items_out THEN
2507 result.fail_part := 'config.circ_matrix_circ_mod_test';
2508 result.success := FALSE;
2514 -- If we passed everything, return the successful matchpoint id
2521 $func$ LANGUAGE plpgsql;
2523 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
2524 SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
2525 $func$ LANGUAGE SQL;
2527 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
2528 SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
2529 $func$ LANGUAGE SQL;
2531 -- Update recurring fine rules
2532 UPDATE config.rule_recurring_fine SET grace_period=recurrence_interval;
2534 -- Update Circulation Data
2535 -- Only update if we were told to and the circ hasn't been checked in
2536 UPDATE action.circulation SET grace_period=fine_interval WHERE :CircGrace AND (checkin_time IS NULL);
2539 CREATE TABLE biblio.monograph_part (
2540 id SERIAL PRIMARY KEY,
2541 record BIGINT NOT NULL REFERENCES biblio.record_entry (id),
2542 label TEXT NOT NULL,
2543 label_sortkey TEXT NOT NULL,
2544 CONSTRAINT record_label_unique UNIQUE (record,label)
2547 CREATE OR REPLACE FUNCTION biblio.normalize_biblio_monograph_part_sortkey () RETURNS TRIGGER AS $$
2549 NEW.label_sortkey := REGEXP_REPLACE(
2550 evergreen.lpad_number_substrings(
2551 naco_normalize(NEW.label),
2561 $$ LANGUAGE PLPGSQL;
2563 CREATE TRIGGER norm_sort_label BEFORE INSERT OR UPDATE ON biblio.monograph_part FOR EACH ROW EXECUTE PROCEDURE biblio.normalize_biblio_monograph_part_sortkey();
2565 CREATE TABLE asset.copy_part_map (
2566 id SERIAL PRIMARY KEY,
2567 target_copy BIGINT NOT NULL, -- points o asset.copy
2568 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
2570 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
2572 CREATE TABLE asset.call_number_prefix (
2573 id SERIAL PRIMARY KEY,
2574 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
2575 label TEXT NOT NULL, -- i18n
2579 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
2581 NEW.label_sortkey := REGEXP_REPLACE(
2582 evergreen.lpad_number_substrings(
2583 naco_normalize(NEW.label),
2593 $$ LANGUAGE PLPGSQL;
2595 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
2596 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
2597 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
2599 CREATE TABLE asset.call_number_suffix (
2600 id SERIAL PRIMARY KEY,
2601 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
2602 label TEXT NOT NULL, -- i18n
2605 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
2606 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
2607 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
2609 INSERT INTO asset.call_number_suffix (id, owning_lib, label) VALUES (-1, 1, '');
2610 INSERT INTO asset.call_number_prefix (id, owning_lib, label) VALUES (-1, 1, '');
2612 DROP INDEX IF EXISTS asset.asset_call_number_label_once_per_lib;
2614 ALTER TABLE asset.call_number
2615 ADD COLUMN prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
2616 ADD COLUMN suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED;
2618 ALTER TABLE auditor.asset_call_number_history
2619 ADD COLUMN prefix INT NOT NULL DEFAULT -1,
2620 ADD COLUMN suffix INT NOT NULL DEFAULT -1;
2622 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label, prefix, suffix) WHERE deleted = FALSE OR deleted IS FALSE;
2624 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
2625 'ui.cat.volume_copy_editor.horizontal',
2627 'ui.cat.volume_copy_editor.horizontal',
2628 'GUI: Horizontal layout for Volume/Copy Creator/Editor.',
2631 'ui.cat.volume_copy_editor.horizontal',
2632 'The main entry point for this interface is in Holdings Maintenance, Actions for Selected Rows, Edit Item Attributes / Call Numbers / Replace Barcodes. This setting changes the top and bottom panes for that interface into left and right panes.',
2633 'coust', 'description'),
2640 ALTER FUNCTION actor.org_unit_descendants( INT, INT ) ROWS 1;
2641 ALTER FUNCTION actor.org_unit_descendants( INT ) ROWS 1;
2642 ALTER FUNCTION actor.org_unit_descendants_distance( INT ) ROWS 1;
2643 ALTER FUNCTION actor.org_unit_ancestors( INT ) ROWS 1;
2644 ALTER FUNCTION actor.org_unit_ancestors_distance( INT ) ROWS 1;
2645 ALTER FUNCTION actor.org_unit_full_path ( INT ) ROWS 2;
2646 ALTER FUNCTION actor.org_unit_full_path ( INT, INT ) ROWS 2;
2647 ALTER FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) ROWS 1;
2648 ALTER FUNCTION actor.org_unit_common_ancestors ( INT, INT ) ROWS 1;
2649 ALTER FUNCTION actor.org_unit_ancestor_setting( TEXT, INT ) ROWS 1;
2650 ALTER FUNCTION permission.grp_ancestors ( INT ) ROWS 1;
2651 ALTER FUNCTION permission.grp_ancestors_distance( INT ) ROWS 1;
2652 ALTER FUNCTION permission.grp_descendants_distance( INT ) ROWS 1;
2653 ALTER FUNCTION permission.usr_perms ( INT ) ROWS 10;
2654 ALTER FUNCTION permission.usr_has_perm_at_nd ( INT, TEXT) ROWS 1;
2655 ALTER FUNCTION permission.usr_has_perm_at_all_nd ( INT, TEXT ) ROWS 1;
2656 ALTER FUNCTION permission.usr_has_perm_at ( INT, TEXT ) ROWS 1;
2657 ALTER FUNCTION permission.usr_has_perm_at_all ( INT, TEXT ) ROWS 1;
2660 CREATE TRIGGER facet_force_nfc_tgr
2661 BEFORE UPDATE OR INSERT ON metabib.facet_entry
2662 FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc();
2664 DROP FUNCTION IF EXISTS public.force_unicode_normal_form (TEXT,TEXT);
2665 DROP FUNCTION IF EXISTS public.facet_force_nfc ();
2667 DROP TRIGGER b_maintain_901 ON biblio.record_entry;
2668 DROP TRIGGER b_maintain_901 ON authority.record_entry;
2669 DROP TRIGGER b_maintain_901 ON serial.record_entry;
2671 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
2672 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
2673 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
2675 DROP FUNCTION IF EXISTS public.maintain_901 ();
2677 ------ Backporting note: 2.1+ only beyond here --------
2679 CREATE SCHEMA unapi;
2681 CREATE TABLE unapi.bre_output_layout (
2682 name TEXT PRIMARY KEY,
2683 transform TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
2684 mime_type TEXT NOT NULL,
2685 feed_top TEXT NOT NULL,
2686 holdings_element TEXT,
2688 description_element TEXT,
2689 creator_element TEXT,
2690 update_ts_element TEXT
2693 INSERT INTO unapi.bre_output_layout
2694 (name, transform, mime_type, holdings_element, feed_top, title_element, description_element, creator_element, update_ts_element)
2696 ('holdings_xml', NULL, 'application/xml', NULL, 'hxml', NULL, NULL, NULL, NULL),
2697 ('marcxml', 'marcxml', 'application/marc+xml', 'record', 'collection', NULL, NULL, NULL, NULL),
2698 ('mods32', 'mods32', 'application/mods+xml', 'mods', 'modsCollection', NULL, NULL, NULL, NULL)
2701 -- Dummy functions, so we can create the real ones out of order
2702 CREATE OR REPLACE FUNCTION unapi.aou ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2703 CREATE OR REPLACE FUNCTION unapi.acnp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2704 CREATE OR REPLACE FUNCTION unapi.acns ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2705 CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2706 CREATE OR REPLACE FUNCTION unapi.ssub ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2707 CREATE OR REPLACE FUNCTION unapi.sdist ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2708 CREATE OR REPLACE FUNCTION unapi.sstr ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2709 CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2710 CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2711 CREATE OR REPLACE FUNCTION unapi.sisum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2712 CREATE OR REPLACE FUNCTION unapi.sbsum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2713 CREATE OR REPLACE FUNCTION unapi.sssum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2714 CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2715 CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2716 CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2717 CREATE OR REPLACE FUNCTION unapi.acpn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2718 CREATE OR REPLACE FUNCTION unapi.acl ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2719 CREATE OR REPLACE FUNCTION unapi.ccs ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2720 CREATE OR REPLACE FUNCTION unapi.ascecm ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2721 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2722 CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2724 CREATE OR REPLACE FUNCTION unapi.holdings_xml ( bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2725 CREATE OR REPLACE FUNCTION unapi.biblio_record_entry_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, title TEXT DEFAULT NULL, description TEXT DEFAULT NULL, creator TEXT DEFAULT NULL, update_ts TEXT DEFAULT NULL, unapi_url TEXT DEFAULT NULL, header_xml XML DEFAULT NULL ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL;
2727 CREATE OR REPLACE FUNCTION unapi.memoize (classname TEXT, obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
2733 'id' || COALESCE(obj_id::TEXT,'') ||
2734 'format' || COALESCE(format::TEXT,'') ||
2735 'ename' || COALESCE(ename::TEXT,'') ||
2736 'includes' || COALESCE(includes::TEXT,'{}'::TEXT[]::TEXT) ||
2737 'org' || COALESCE(org::TEXT,'') ||
2738 'depth' || COALESCE(depth::TEXT,'') ||
2739 'slimit' || COALESCE(slimit::TEXT,'') ||
2740 'soffset' || COALESCE(soffset::TEXT,'') ||
2741 'include_xmlns' || COALESCE(include_xmlns::TEXT,'');
2742 -- RAISE NOTICE 'memoize key: %', key;
2745 -- RAISE NOTICE 'memoize hash: %', key;
2747 -- XXX cache logic ... memcached? table?
2749 EXECUTE $$SELECT unapi.$$ || classname || $$( $1, $2, $3, $4, $5, $6, $7, $8, $9);$$ INTO output USING obj_id, format, ename, includes, org, depth, slimit, soffset, include_xmlns;
2752 $F$ LANGUAGE PLPGSQL;
2754 CREATE OR REPLACE FUNCTION unapi.biblio_record_entry_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, title TEXT DEFAULT NULL, description TEXT DEFAULT NULL, creator TEXT DEFAULT NULL, update_ts TEXT DEFAULT NULL, unapi_url TEXT DEFAULT NULL, header_xml XML DEFAULT NULL ) RETURNS XML AS $F$
2756 layout unapi.bre_output_layout%ROWTYPE;
2757 transform config.xml_transform%ROWTYPE;
2760 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
2762 element_list TEXT[];
2765 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
2766 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
2768 IF layout.name IS NULL THEN
2772 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
2773 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
2775 -- Gather the bib xml
2776 SELECT XMLAGG( unapi.bre(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
2778 IF layout.title_element IS NOT NULL THEN
2779 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', CASE WHEN $4 THEN XMLATTRIBUTES( $1 AS xmlns) ELSE NULL END, $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title, include_xmlns;
2782 IF layout.description_element IS NOT NULL THEN
2783 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', CASE WHEN $4 THEN XMLATTRIBUTES( $1 AS xmlns) ELSE NULL END, $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description, include_xmlns;
2786 IF layout.creator_element IS NOT NULL THEN
2787 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', CASE WHEN $4 THEN XMLATTRIBUTES( $1 AS xmlns) ELSE NULL END, $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator, include_xmlns;
2790 IF layout.update_ts_element IS NOT NULL THEN
2791 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', CASE WHEN $4 THEN XMLATTRIBUTES( $1 AS xmlns) ELSE NULL END, $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts, include_xmlns;
2794 IF unapi_url IS NOT NULL THEN
2795 EXECUTE $$SELECT XMLCONCAT( XMLELEMENT( name link, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-server' AS rel, $1 AS href, 'unapi' AS title)), $2)$$ INTO tmp_xml USING unapi_url, tmp_xml::XML;
2798 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
2800 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
2801 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
2802 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', CASE WHEN $4 THEN XMLATTRIBUTES( $1 AS xmlns) ELSE NULL END, $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, include_xmlns;
2805 RETURN tmp_xml::XML;
2807 $F$ LANGUAGE PLPGSQL;
2809 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
2811 me biblio.record_entry%ROWTYPE;
2812 layout unapi.bre_output_layout%ROWTYPE;
2813 xfrm config.xml_transform%ROWTYPE;
2821 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
2823 IF ouid IS NULL THEN
2827 IF format = 'holdings_xml' THEN -- the special case
2828 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
2832 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
2834 IF layout.name IS NULL THEN
2838 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
2840 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
2842 -- grab hodlings if we need them
2843 IF ('holdings_xml' = ANY (includes)) THEN
2844 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns);
2850 -- generate our item node
2853 IF format = 'marcxml' THEN
2855 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
2856 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
2859 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
2862 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
2864 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
2865 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
2868 IF ('bre.unapi' = ANY (includes)) THEN
2869 output := REGEXP_REPLACE(
2871 '</' || top_el || '>(.*?)',
2875 'http://www.w3.org/1999/xhtml' AS xmlns,
2876 'unapi-id' AS class,
2877 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
2879 )::TEXT || '</' || top_el || E'>\\1'
2887 $F$ LANGUAGE PLPGSQL;
2889 CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$
2893 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2894 CASE WHEN ('bre' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
2898 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2901 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2903 FROM asset.opac_ou_record_copy_count($2, $1)
2907 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2909 FROM asset.staff_ou_record_copy_count($2, $1)
2914 WHEN ('bmp' = ANY ($5)) THEN
2915 XMLELEMENT( name monograph_parts,
2916 XMLAGG((SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE) FROM biblio.monograph_part WHERE record = $1))
2920 CASE WHEN ('acn' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN
2923 (SELECT XMLAGG(acn) FROM (
2924 SELECT unapi.acn(acn.id,'xml','volume', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value('{acn,auri}'::TEXT[] || $5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE)
2925 FROM asset.call_number acn
2926 WHERE acn.record = $1
2930 JOIN actor.org_unit_descendants(
2935 FROM actor.org_unit_type aout
2936 JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2)
2939 ) aoud ON (acp.circ_lib = aoud.id)
2942 ORDER BY label_sortkey
2948 CASE WHEN ('ssub' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN
2951 (SELECT XMLAGG(ssub) FROM (
2952 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2953 FROM serial.subscription
2954 WHERE record_entry = $1
2961 CREATE OR REPLACE FUNCTION unapi.ssub ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
2965 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2966 'tag:open-ils.org:U2@ssub/' || id AS id,
2967 start_date AS start, end_date AS end, expected_date_offset
2969 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8),
2970 XMLELEMENT( name distributions,
2972 WHEN ('sdist' = ANY ($4)) THEN
2973 XMLAGG((SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE) FROM serial.distribution WHERE subscription = ssub.id))
2978 FROM serial.subscription ssub
2980 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
2983 CREATE OR REPLACE FUNCTION unapi.sdist ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
2987 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2988 'tag:open-ils.org:U2@sdist/' || id AS id,
2989 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
2990 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
2991 unit_label_prefix, label, unit_label_suffix, summary_method
2993 unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
2994 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) ELSE NULL END,
2995 XMLELEMENT( name streams,
2997 WHEN ('sstr' = ANY ($4)) THEN
2998 XMLAGG((SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) FROM serial.stream WHERE distribution = sdist.id))
3002 XMLELEMENT( name summaries,
3004 WHEN ('ssum' = ANY ($4)) THEN
3005 XMLAGG((SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) FROM serial.basic_summary WHERE distribution = sdist.id))
3009 WHEN ('ssum' = ANY ($4)) THEN
3010 XMLAGG((SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) FROM serial.index_summary WHERE distribution = sdist.id))
3014 WHEN ('ssum' = ANY ($4)) THEN
3015 XMLAGG((SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) FROM serial.supplement_summary WHERE distribution = sdist.id))
3020 FROM serial.distribution sdist
3022 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
3025 CREATE OR REPLACE FUNCTION unapi.sstr ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3029 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3030 'tag:open-ils.org:U2@sstr/' || id AS id,
3033 CASE WHEN distribution IS NOT NULL AND ('sdist' = ANY ($4)) THEN unapi.sssum( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3034 XMLELEMENT( name items,
3036 WHEN ('sitem' = ANY ($4)) THEN
3037 XMLAGG((SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) FROM serial.item WHERE stream = sstr.id))
3042 FROM serial.stream sstr
3044 GROUP BY id, routing_label, distribution;
3047 CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3051 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3052 'tag:open-ils.org:U2@siss/' || id AS id,
3053 create_date, edit_date, label, date_published,
3054 holding_code, holding_type, holding_link_id
3056 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3057 XMLELEMENT( name items,
3059 WHEN ('sitem' = ANY ($4)) THEN
3060 XMLAGG((SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) FROM serial.item WHERE issuance = sstr.id))
3065 FROM serial.issuance sstr
3067 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
3070 CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3074 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3075 'tag:open-ils.org:U2@sitem/' || id AS id,
3076 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
3077 date_expected, date_received
3079 CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3080 CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3081 CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3082 CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END
3083 -- XMLELEMENT( name notes,
3085 -- WHEN ('acpn' = ANY ($4)) THEN
3086 -- XMLAGG((SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8) FROM asset.copy_note WHERE owning_copy = cp.id AND pub))
3091 FROM serial.item sitem
3096 CREATE OR REPLACE FUNCTION unapi.sssum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3098 name serial_summary,
3100 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3101 'tag:open-ils.org:U2@sbsum/' || id AS id,
3102 'sssum' AS type, generated_coverage, textual_holdings, show_generated
3104 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
3106 FROM serial.supplement_summary ssum
3108 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
3111 CREATE OR REPLACE FUNCTION unapi.sbsum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3113 name serial_summary,
3115 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3116 'tag:open-ils.org:U2@sbsum/' || id AS id,
3117 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
3119 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
3121 FROM serial.basic_summary ssum
3123 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
3126 CREATE OR REPLACE FUNCTION unapi.sisum ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3128 name serial_summary,
3130 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3131 'tag:open-ils.org:U2@sbsum/' || id AS id,
3132 'sisum' AS type, generated_coverage, textual_holdings, show_generated
3134 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
3136 FROM serial.index_summary ssum
3138 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
3142 CREATE OR REPLACE FUNCTION unapi.aou ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3146 IF ename = 'circlib' THEN
3150 'http://open-ils.org/spec/actors/v1' AS xmlns,
3155 FROM actor.org_unit aou
3158 EXECUTE $$SELECT XMLELEMENT(
3159 name $$ || ename || $$,
3161 'http://open-ils.org/spec/actors/v1' AS xmlns,
3162 'tag:open-ils.org:U2@aou/' || id AS id,
3163 shortname, name, opac_visible
3166 FROM actor.org_unit aou
3167 WHERE id = $1 $$ INTO output USING obj_id;
3173 $F$ LANGUAGE PLPGSQL;
3175 CREATE OR REPLACE FUNCTION unapi.acl ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3179 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3184 FROM asset.copy_location
3188 CREATE OR REPLACE FUNCTION unapi.ccs ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3192 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3197 FROM config.copy_status
3201 CREATE OR REPLACE FUNCTION unapi.acpn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3205 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3206 create_date AS date,
3211 FROM asset.copy_note
3215 CREATE OR REPLACE FUNCTION unapi.ascecm ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3219 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3225 FROM asset.stat_cat_entry asce
3226 JOIN asset.stat_cat sc ON (sc.id = asce.stat_cat)
3230 CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3232 name monograph_part,
3234 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3235 'tag:open-ils.org:U2@bmp/' || id AS id,
3239 'tag:open-ils.org:U2@bre/' || record AS record
3242 WHEN ('acp' = ANY ($4)) THEN
3243 XMLELEMENT( name copies,
3244 (SELECT XMLAGG(acp) FROM (
3245 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
3247 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
3249 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
3256 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END
3258 FROM biblio.monograph_part
3260 GROUP BY id, label, label_sortkey, record;
3263 CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3267 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3268 'tag:open-ils.org:U2@acp/' || id AS id,
3269 create_date, edit_date, copy_number, circulate, deposit,
3270 ref, holdable, deleted, deposit_amount, price, barcode,
3271 circ_modifier, circ_as_type, opac_visible
3273 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
3274 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
3275 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
3276 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
3277 CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3278 XMLELEMENT( name copy_notes,
3280 WHEN ('acpn' = ANY ($4)) THEN
3281 XMLAGG((SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.copy_note WHERE owning_copy = cp.id AND pub))
3285 XMLELEMENT( name statcats,
3287 WHEN ('ascecm' = ANY ($4)) THEN
3288 XMLAGG((SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.stat_cat_entry_copy_map WHERE owning_copy = cp.id))
3293 WHEN ('bmp' = ANY ($4)) THEN
3294 XMLELEMENT( name monograph_parts,
3295 XMLAGG((SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.copy_part_map WHERE target_copy = cp.id))
3302 GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible;
3305 CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3309 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3310 'tag:open-ils.org:U2@acp/' || id AS id,
3311 create_date, edit_date, copy_number, circulate, deposit,
3312 ref, holdable, deleted, deposit_amount, price, barcode,
3313 circ_modifier, circ_as_type, opac_visible, status_changed_time,
3314 floating, mint_condition, detailed_contents, sort_key, summary_contents, cost
3316 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
3317 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
3318 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8),
3319 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8),
3320 CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3321 XMLELEMENT( name copy_notes,
3323 WHEN ('acpn' = ANY ($4)) THEN
3324 XMLAGG((SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE) FROM asset.copy_note WHERE owning_copy = cp.id AND pub))
3328 XMLELEMENT( name statcats,
3330 WHEN ('ascecm' = ANY ($4)) THEN
3331 XMLAGG((SELECT unapi.acpn( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE) FROM asset.stat_cat_entry_copy_map WHERE owning_copy = cp.id))
3338 GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition,
3339 deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost;
3342 CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3346 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3347 'tag:open-ils.org:U2@acn/' || acn.id AS id,
3349 o.opac_visible AS opac_visible,
3350 deleted, label, label_sortkey, label_class, record
3352 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
3353 XMLELEMENT( name copies,
3355 WHEN ('acp' = ANY ($4)) THEN
3356 (SELECT XMLAGG(acp) FROM (
3357 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE)
3359 JOIN actor.org_unit_descendants(
3360 (SELECT id FROM actor.org_unit WHERE shortname = $5),
3361 (COALESCE($6,(SELECT aout.depth FROM actor.org_unit_type aout JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.shortname = $5))))
3362 ) aoud ON (cp.circ_lib = aoud.id)
3363 WHERE cp.call_number = acn.id
3364 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
3373 (SELECT XMLAGG(auri) FROM (SELECT unapi.auri(uri,'xml','uri', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE call_number = acn.id)x)
3375 CASE WHEN ('acnp' = ANY ($4)) THEN unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3376 CASE WHEN ('acns' = ANY ($4)) THEN unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3377 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END
3379 FROM asset.call_number acn
3380 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
3382 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
3385 CREATE OR REPLACE FUNCTION unapi.acnp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3387 name call_number_prefix,
3389 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3394 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acnp'), $5, $6, $7, $8)
3396 FROM asset.call_number_prefix
3400 CREATE OR REPLACE FUNCTION unapi.acns ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3402 name call_number_suffix,
3404 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3409 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acns'), $5, $6, $7, $8)
3411 FROM asset.call_number_suffix
3415 CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3419 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3420 'tag:open-ils.org:U2@auri/' || uri.id AS id,
3425 XMLELEMENT( name copies,
3427 WHEN ('acn' = ANY ($4)) THEN
3428 (SELECT XMLAGG(acn) FROM (SELECT unapi.acn( call_number, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'auri'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE uri = uri.id)x)
3435 GROUP BY uri.id, use_restriction, href, label;
3438 DROP FUNCTION IF EXISTS public.array_remove_item_by_value(ANYARRAY,ANYELEMENT);
3440 DROP FUNCTION IF EXISTS public.lpad_number_substrings(TEXT,TEXT,INT);
3444 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
3448 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
3449 PERFORM * FROM asset.copy WHERE id = copy_id;
3451 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
3455 $F$ LANGUAGE PLPGSQL;
3457 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
3460 CREATE TABLE biblio.peer_type (
3461 id SERIAL PRIMARY KEY,
3462 name TEXT NOT NULL UNIQUE -- i18n
3465 CREATE TABLE biblio.peer_bib_copy_map (
3466 id SERIAL PRIMARY KEY,
3467 peer_type INT NOT NULL REFERENCES biblio.peer_type (id),
3468 peer_record BIGINT NOT NULL REFERENCES biblio.record_entry (id),
3469 target_copy BIGINT NOT NULL -- can't use fkey because of acp subtables
3471 CREATE INDEX peer_bib_copy_map_record_idx ON biblio.peer_bib_copy_map (peer_record);
3472 CREATE INDEX peer_bib_copy_map_copy_idx ON biblio.peer_bib_copy_map (target_copy);
3474 DROP TABLE asset.opac_visible_copies;
3475 CREATE TABLE asset.opac_visible_copies (
3476 id BIGSERIAL primary key,
3482 INSERT INTO biblio.peer_type (id,name) VALUES
3483 (1,oils_i18n_gettext(1,'Bound Volume','bpt','name')),
3484 (2,oils_i18n_gettext(2,'Bilingual','bpt','name')),
3485 (3,oils_i18n_gettext(3,'Back-to-back','bpt','name')),
3486 (4,oils_i18n_gettext(4,'Set','bpt','name')),
3487 (5,oils_i18n_gettext(5,'e-Reader Preload','bpt','name'));
3489 SELECT SETVAL('biblio.peer_type_id_seq'::TEXT, 100);
3491 CREATE OR REPLACE FUNCTION search.query_parser_fts (
3493 param_search_ou INT,
3496 param_statuses INT[],
3497 param_locations INT[],
3504 ) RETURNS SETOF search.search_result AS $func$
3507 current_res search.search_result%ROWTYPE;
3508 search_org_list INT[];
3516 core_cursor REFCURSOR;
3517 core_rel_query TEXT;
3519 total_count INT := 0;
3520 check_count INT := 0;
3521 deleted_count INT := 0;
3522 visible_count INT := 0;
3523 excluded_count INT := 0;
3527 check_limit := COALESCE( param_check, 1000 );
3528 core_limit := COALESCE( param_limit, 25000 );
3529 core_offset := COALESCE( param_offset, 0 );
3531 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
3533 IF param_search_ou > 0 THEN
3534 IF param_depth IS NOT NULL THEN
3535 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
3537 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
3539 ELSIF param_search_ou < 0 THEN
3540 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
3541 ELSIF param_search_ou = 0 THEN
3542 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
3545 OPEN core_cursor FOR EXECUTE param_query;
3549 FETCH core_cursor INTO core_result;
3550 EXIT WHEN NOT FOUND;
3551 EXIT WHEN total_count >= core_limit;
3553 total_count := total_count + 1;
3555 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
3557 check_count := check_count + 1;
3559 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
3561 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
3562 deleted_count := deleted_count + 1;
3567 FROM biblio.record_entry b
3568 JOIN config.bib_source s ON (b.source = s.id)
3569 WHERE s.transcendant
3570 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
3573 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
3574 visible_count := visible_count + 1;
3576 current_res.id = core_result.id;
3577 current_res.rel = core_result.rel;
3581 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3585 current_res.record = core_result.records[1];
3587 current_res.record = NULL;
3590 RETURN NEXT current_res;
3596 FROM asset.call_number cn
3597 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
3598 JOIN asset.uri uri ON (map.uri = uri.id)
3599 WHERE NOT cn.deleted
3600 AND cn.label = '##URI##'
3602 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
3603 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3604 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3608 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
3609 visible_count := visible_count + 1;
3611 current_res.id = core_result.id;
3612 current_res.rel = core_result.rel;
3616 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3620 current_res.record = core_result.records[1];
3622 current_res.record = NULL;
3625 RETURN NEXT current_res;
3630 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
3633 FROM asset.call_number cn
3634 JOIN asset.copy cp ON (cp.call_number = cn.id)
3635 WHERE NOT cn.deleted
3637 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
3638 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3639 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3644 FROM biblio.peer_bib_copy_map pr
3645 JOIN asset.copy cp ON (cp.id = pr.target_copy)
3646 WHERE NOT cp.deleted
3647 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
3648 AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3649 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3653 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
3654 excluded_count := excluded_count + 1;
3661 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
3664 FROM asset.call_number cn
3665 JOIN asset.copy cp ON (cp.call_number = cn.id)
3666 WHERE NOT cn.deleted
3668 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
3669 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3670 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3675 FROM biblio.peer_bib_copy_map pr
3676 JOIN asset.copy cp ON (cp.id = pr.target_copy)
3677 WHERE NOT cp.deleted
3678 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
3679 AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3680 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3684 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
3685 excluded_count := excluded_count + 1;
3692 IF staff IS NULL OR NOT staff THEN
3695 FROM asset.opac_visible_copies
3696 WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3697 AND record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3702 FROM biblio.peer_bib_copy_map pr
3703 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
3704 WHERE cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3705 AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3710 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
3711 excluded_count := excluded_count + 1;
3719 FROM asset.call_number cn
3720 JOIN asset.copy cp ON (cp.call_number = cn.id)
3721 WHERE NOT cn.deleted
3723 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3724 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3730 FROM biblio.peer_bib_copy_map pr
3731 JOIN asset.copy cp ON (cp.id = pr.target_copy)
3732 WHERE NOT cp.deleted
3733 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3734 AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3740 FROM asset.call_number cn
3741 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3745 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
3746 excluded_count := excluded_count + 1;
3755 visible_count := visible_count + 1;
3757 current_res.id = core_result.id;
3758 current_res.rel = core_result.rel;
3762 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3766 current_res.record = core_result.records[1];
3768 current_res.record = NULL;
3771 RETURN NEXT current_res;
3773 IF visible_count % 1000 = 0 THEN
3774 -- RAISE NOTICE ' % visible so far ... ', visible_count;
3779 current_res.id = NULL;
3780 current_res.rel = NULL;
3781 current_res.record = NULL;
3782 current_res.total = total_count;
3783 current_res.checked = check_count;
3784 current_res.deleted = deleted_count;
3785 current_res.visible = visible_count;
3786 current_res.excluded = excluded_count;
3790 RETURN NEXT current_res;
3793 $func$ LANGUAGE PLPGSQL;
3795 CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$
3799 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3800 CASE WHEN ('bre' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
3804 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
3807 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3809 FROM asset.opac_ou_record_copy_count($2, $1)
3813 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3815 FROM asset.staff_ou_record_copy_count($2, $1)
3820 WHEN ('bmp' = ANY ($5)) THEN
3821 XMLELEMENT( name monograph_parts,
3822 XMLAGG((SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE) FROM biblio.monograph_part WHERE record = $1))
3826 CASE WHEN ('acn' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN
3829 (SELECT XMLAGG(acn) FROM (
3830 SELECT unapi.acn(acn.id,'xml','volume',evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value('{acn,auri}'::TEXT[] || $5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE)
3831 FROM asset.call_number acn
3832 WHERE acn.record = $1
3836 JOIN actor.org_unit_descendants(
3841 FROM actor.org_unit_type aout
3842 JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2)
3845 ) aoud ON (acp.circ_lib = aoud.id)
3848 ORDER BY label_sortkey
3854 CASE WHEN ('ssub' = ANY ('{acn,auri}'::TEXT[] || $5)) THEN
3857 (SELECT XMLAGG(ssub) FROM (
3858 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
3859 FROM serial.subscription
3860 WHERE record_entry = $1
3864 CASE WHEN ('acp' = ANY ($5)) THEN
3866 name foreign_copies,
3867 (SELECT XMLAGG(acp) FROM (
3868 SELECT unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE)
3869 FROM biblio.peer_bib_copy_map p
3870 JOIN asset.copy c ON (p.target_copy = c.id)
3871 WHERE NOT c.deleted AND peer_record = $1
3878 CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
3882 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3883 'tag:open-ils.org:U2@acp/' || id AS id,
3884 create_date, edit_date, copy_number, circulate, deposit,
3885 ref, holdable, deleted, deposit_amount, price, barcode,
3886 circ_modifier, circ_as_type, opac_visible
3888 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
3889 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
3890 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
3891 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
3892 CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
3893 XMLELEMENT( name copy_notes,
3895 WHEN ('acpn' = ANY ($4)) THEN
3896 XMLAGG((SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.copy_note WHERE owning_copy = cp.id AND pub))
3900 XMLELEMENT( name statcats,
3902 WHEN ('ascecm' = ANY ($4)) THEN
3903 XMLAGG((SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.stat_cat_entry_copy_map WHERE owning_copy = cp.id))
3907 XMLELEMENT( name foreign_records,
3909 WHEN ('bre' = ANY ($4)) THEN
3910 XMLAGG((SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) FROM biblio.peer_bib_copy_map WHERE target_copy = cp.id))
3916 WHEN ('bmp' = ANY ($4)) THEN
3917 XMLELEMENT( name monograph_parts,
3918 XMLAGG((SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) FROM asset.copy_part_map WHERE target_copy = cp.id))
3925 GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible;
3928 CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$
3930 TRUNCATE TABLE asset.opac_visible_copies;
3932 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
3933 SELECT cp.id, cp.circ_lib, cn.record
3935 JOIN asset.call_number cn ON (cn.id = cp.call_number)
3936 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3937 JOIN asset.copy_location cl ON (cp.location = cl.id)
3938 JOIN config.copy_status cs ON (cp.status = cs.id)
3939 JOIN biblio.record_entry b ON (cn.record = b.id)
3940 WHERE NOT cp.deleted
3948 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record
3950 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
3951 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3952 JOIN asset.copy_location cl ON (cp.location = cl.id)
3953 JOIN config.copy_status cs ON (cp.status = cs.id)
3954 WHERE NOT cp.deleted
3961 COMMENT ON FUNCTION asset.refresh_opac_visible_copies_mat_view() IS $$
3962 Rebuild the copy OPAC visibility cache. Useful during migrations.
3965 SELECT asset.refresh_opac_visible_copies_mat_view();
3966 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
3967 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
3968 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
3970 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
3974 do_add BOOLEAN := false;
3975 do_remove BOOLEAN := false;
3978 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
3979 SELECT id, circ_lib, record FROM (
3980 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number
3982 JOIN asset.call_number cn ON (cn.id = cp.call_number)
3983 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3984 JOIN asset.copy_location cl ON (cp.location = cl.id)
3985 JOIN config.copy_status cs ON (cp.status = cs.id)
3986 JOIN biblio.record_entry b ON (cn.record = b.id)
3987 WHERE NOT cp.deleted
3995 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number
3997 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
3998 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3999 JOIN asset.copy_location cl ON (cp.location = cl.id)
4000 JOIN config.copy_status cs ON (cp.status = cs.id)
4001 WHERE NOT cp.deleted
4010 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
4012 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
4013 IF TG_OP = 'INSERT' THEN
4014 add_query := add_query || 'WHERE x.id = ' || NEW.target_copy || ' AND x.record = ' || NEW.peer_record || ';';
4018 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
4019 EXECUTE remove_query;
4024 IF TG_OP = 'INSERT' THEN
4026 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
4027 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
4035 -- handle items first, since with circulation activity
4036 -- their statuses change frequently
4037 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
4039 IF OLD.location <> NEW.location OR
4040 OLD.call_number <> NEW.call_number OR
4041 OLD.status <> NEW.status OR
4042 OLD.circ_lib <> NEW.circ_lib THEN
4043 -- any of these could change visibility, but
4044 -- we'll save some queries and not try to calculate
4045 -- the change directly
4050 IF OLD.deleted <> NEW.deleted THEN
4058 IF OLD.opac_visible <> NEW.opac_visible THEN
4059 IF OLD.opac_visible THEN
4061 ELSIF NOT do_remove THEN -- handle edge case where deleted item
4062 -- is also marked opac_visible
4070 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
4073 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
4081 IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
4083 IF OLD.deleted AND NEW.deleted THEN -- do nothing
4087 ELSIF NEW.deleted THEN -- remove rows
4089 IF TG_TABLE_NAME = 'call_number' THEN
4090 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
4091 ELSIF TG_TABLE_NAME = 'record_entry' THEN
4092 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
4097 ELSIF OLD.deleted THEN -- add rows
4099 IF TG_TABLE_NAME IN ('copy','unit') THEN
4100 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
4101 ELSIF TG_TABLE_NAME = 'call_number' THEN
4102 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
4103 ELSIF TG_TABLE_NAME = 'record_entry' THEN
4104 add_query := add_query || 'WHERE x.record = ' || NEW.id || ';';
4114 IF TG_TABLE_NAME = 'call_number' THEN
4116 IF OLD.record <> NEW.record THEN
4117 -- call number is linked to different bib
4118 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
4119 EXECUTE remove_query;
4120 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
4128 IF TG_TABLE_NAME IN ('record_entry') THEN
4129 RETURN NEW; -- don't have 'opac_visible'
4132 -- actor.org_unit, asset.copy_location, asset.copy_status
4133 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
4137 ELSIF NEW.opac_visible THEN -- add rows
4139 IF TG_TABLE_NAME = 'org_unit' THEN
4140 add_query := add_query || 'AND cp.circ_lib = ' || NEW.id || ';';
4141 ELSIF TG_TABLE_NAME = 'copy_location' THEN
4142 add_query := add_query || 'AND cp.location = ' || NEW.id || ';';
4143 ELSIF TG_TABLE_NAME = 'copy_status' THEN
4144 add_query := add_query || 'AND cp.status = ' || NEW.id || ';';
4151 IF TG_TABLE_NAME = 'org_unit' THEN
4152 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
4153 ELSIF TG_TABLE_NAME = 'copy_location' THEN
4154 remove_query := remove_query || 'location = ' || NEW.id || ');';
4155 ELSIF TG_TABLE_NAME = 'copy_status' THEN
4156 remove_query := remove_query || 'status = ' || NEW.id || ');';
4159 EXECUTE remove_query;
4165 $func$ LANGUAGE PLPGSQL;
4166 COMMENT ON FUNCTION asset.cache_copy_visibility() IS $$
4167 Trigger function to update the copy OPAC visiblity cache.
4170 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR DELETE ON biblio.peer_bib_copy_map FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
4172 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
4174 transformed_xml TEXT;
4177 xfrm config.xml_transform%ROWTYPE;
4179 new_attrs HSTORE := ''::HSTORE;
4180 attr_def config.record_attr_definition%ROWTYPE;
4183 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
4184 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
4185 DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records
4186 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
4187 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
4188 RETURN NEW; -- and we're done
4191 IF TG_OP = 'UPDATE' THEN -- re-ingest?
4192 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
4194 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
4199 -- Record authority linking
4200 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
4202 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
4205 -- Flatten and insert the mfr data
4206 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
4208 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
4210 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
4211 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
4213 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
4215 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
4216 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
4217 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
4218 WHERE record = NEW.id
4219 AND tag LIKE attr_def.tag
4221 WHEN attr_def.sf_list IS NOT NULL
4222 THEN POSITION(subfield IN attr_def.sf_list) > 0
4229 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
4230 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
4232 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
4234 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
4236 -- See if we can skip the XSLT ... it's expensive
4237 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
4238 -- Can't skip the transform
4239 IF xfrm.xslt <> '---' THEN
4240 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
4242 transformed_xml := NEW.marc;
4245 prev_xfrm := xfrm.name;
4248 IF xfrm.name IS NULL THEN
4249 -- just grab the marcxml (empty) transform
4250 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
4251 prev_xfrm := xfrm.name;
4254 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
4256 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
4257 SELECT value::TEXT INTO attr_value
4258 FROM biblio.marc21_physical_characteristics(NEW.id)
4259 WHERE subfield = attr_def.phys_char_sf
4260 LIMIT 1; -- Just in case ...
4264 -- apply index normalizers to attr_value
4266 SELECT n.func AS func,
4267 n.param_count AS param_count,
4269 FROM config.index_normalizer n
4270 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
4271 WHERE attr = attr_def.name
4273 EXECUTE 'SELECT ' || normalizer.func || '(' ||
4274 quote_literal( attr_value ) ||
4276 WHEN normalizer.param_count > 0
4277 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
4280 ')' INTO attr_value;
4284 -- Add the new value to the hstore
4285 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
4289 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
4290 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
4292 UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id;
4298 -- Gather and insert the field entry data
4299 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
4301 -- Located URI magic
4302 IF TG_OP = 'INSERT' THEN
4303 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4305 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4308 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4310 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4314 -- (re)map metarecord-bib linking
4315 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
4316 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
4318 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4320 ELSE -- we're doing an update, and we're not deleted, remap
4321 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
4323 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4329 $func$ LANGUAGE PLPGSQL;
4332 CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
4336 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
4337 'tag:open-ils.org:U2@mra/' || mra.id AS id,
4338 'tag:open-ils.org:U2@bre/' || mra.id AS record
4340 (SELECT XMLAGG(foo.y)
4341 FROM (SELECT XMLELEMENT(
4345 cvm.value AS "coded-value",
4351 FROM EACH(mra.attrs) AS x
4352 JOIN config.record_attr_definition rad ON (x.key = rad.name)
4353 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value)
4357 FROM metabib.record_attr mra
4361 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
4363 me biblio.record_entry%ROWTYPE;
4364 layout unapi.bre_output_layout%ROWTYPE;
4365 xfrm config.xml_transform%ROWTYPE;
4374 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
4376 IF ouid IS NULL THEN
4380 IF format = 'holdings_xml' THEN -- the special case
4381 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
4385 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
4387 IF layout.name IS NULL THEN
4391 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
4393 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
4395 -- grab SVF if we need them
4396 IF ('mra' = ANY (includes)) THEN
4397 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
4402 -- grab hodlings if we need them
4403 IF ('holdings_xml' = ANY (includes)) THEN
4404 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns);
4410 -- generate our item node
4413 IF format = 'marcxml' THEN
4415 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
4416 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
4419 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
4422 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
4424 IF axml IS NOT NULL THEN
4425 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
4428 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
4429 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
4432 IF ('bre.unapi' = ANY (includes)) THEN
4433 output := REGEXP_REPLACE(
4435 '</' || top_el || '>(.*?)',
4439 'http://www.w3.org/1999/xhtml' AS xmlns,
4440 'unapi-id' AS class,
4441 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
4443 )::TEXT || '</' || top_el || E'>\\1'
4451 $F$ LANGUAGE PLPGSQL;
4455 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
4457 me biblio.record_entry%ROWTYPE;
4458 layout unapi.bre_output_layout%ROWTYPE;
4459 xfrm config.xml_transform%ROWTYPE;
4468 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
4470 IF ouid IS NULL THEN
4474 IF format = 'holdings_xml' THEN -- the special case
4475 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
4479 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
4481 IF layout.name IS NULL THEN
4485 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
4487 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
4489 -- grab SVF if we need them
4490 IF ('mra' = ANY (includes)) THEN
4491 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
4496 -- grab hodlings if we need them
4497 IF ('holdings_xml' = ANY (includes)) THEN
4498 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns);
4504 -- generate our item node
4507 IF format = 'marcxml' THEN
4509 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
4510 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
4513 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
4516 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
4518 IF axml IS NOT NULL THEN
4519 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
4522 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
4523 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
4526 IF ('bre.unapi' = ANY (includes)) THEN
4527 output := REGEXP_REPLACE(
4529 '</' || top_el || '>(.*?)',
4533 'http://www.w3.org/1999/xhtml' AS xmlns,
4534 'unapi-id' AS class,
4535 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
4537 )::TEXT || '</' || top_el || E'>\\1'
4543 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
4546 $F$ LANGUAGE PLPGSQL;
4551 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
4552 SELECT extract_marc_field('acq.lineitem', $1, $2, $3);
4556 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
4563 rtype := (vandelay.marc21_record_type( marc )).code;
4564 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
4565 IF ff_pos.tag = 'ldr' THEN
4566 val := oils_xpath_string('//*[local-name()="leader"]', marc);
4567 IF val IS NOT NULL THEN
4568 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
4572 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
4573 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
4577 val := REPEAT( ff_pos.default_val, ff_pos.length );
4583 $func$ LANGUAGE PLPGSQL;
4587 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
4592 output biblio.record_ff_map%ROWTYPE;
4594 rtype := (vandelay.marc21_record_type( marc )).code;
4596 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
4597 output.ff_name := ff_pos.fixed_field;
4598 output.ff_value := NULL;
4600 IF ff_pos.tag = 'ldr' THEN
4601 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
4602 IF output.ff_value IS NOT NULL THEN
4603 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
4605 output.ff_value := NULL;
4608 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
4609 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
4610 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
4612 output.ff_value := NULL;
4620 $func$ LANGUAGE PLPGSQL;
4624 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
4631 uri_owner_list TEXT[];
4639 -- Clear any URI mappings and call numbers for this bib.
4640 -- This leads to acn / auricnm inflation, but also enables
4641 -- old acn/auricnm's to go away and for bibs to be deleted.
4642 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
4643 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
4644 DELETE FROM asset.call_number WHERE id = uri_cn_id;
4647 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
4648 IF ARRAY_UPPER(uris,1) > 0 THEN
4649 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
4650 -- First we pull info out of the 856
4653 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
4654 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
4655 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
4656 CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
4658 -- Get the distinct list of libraries wanting to use
4660 DISTINCT REGEXP_REPLACE(
4662 $re$^.*?\((\w+)\).*$$re$,
4665 ) INTO uri_owner_list
4668 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
4673 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
4675 -- look for a matching uri
4676 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
4677 IF NOT FOUND THEN -- create one
4678 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
4679 IF uri_use IS NULL THEN
4680 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
4682 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
4686 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
4687 uri_owner := uri_owner_list[j];
4689 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
4690 CONTINUE WHEN NOT FOUND;
4692 -- we need a call number to link through
4693 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
4695 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
4696 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
4697 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
4700 -- now, link them if they're not already
4701 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
4703 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
4715 $func$ LANGUAGE PLPGSQL;
4719 UPDATE config.org_unit_setting_type SET datatype = 'string' WHERE name = 'ui.general.button_bar';
4721 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype) VALUES ('ui.general.hotkeyset', 'GUI: Default Hotkeyset', 'Default Hotkeyset for clients (filename without the .keyset). Examples: Default, Minimal, and None', 'string');
4723 UPDATE actor.org_unit_setting SET value='"circ"' WHERE name = 'ui.general.button_bar' AND value='true';
4725 UPDATE actor.org_unit_setting SET value='"none"' WHERE name = 'ui.general.button_bar' AND value='false';
4729 INSERT into config.org_unit_setting_type
4730 ( name, label, description, datatype, fm_class ) VALUES
4731 ( 'cat.default_copy_status_fast',
4732 oils_i18n_gettext( 'cat.default_copy_status_fast', 'Cataloging: Default copy status (fast add)', 'coust', 'label'),
4733 oils_i18n_gettext( 'cat.default_copy_status_fast', 'Default status when a copy is created using the "Fast Add" interface.', 'coust', 'description'),
4737 INSERT into config.org_unit_setting_type
4738 ( name, label, description, datatype, fm_class ) VALUES
4739 ( 'cat.default_copy_status_normal',
4740 oils_i18n_gettext( 'cat.default_copy_status_normal', 'Cataloging: Default copy status (normal)', 'coust', 'label'),
4741 oils_i18n_gettext( 'cat.default_copy_status_normal', 'Default status when a copy is created using the normal volume/copy creator interface.', 'coust', 'description'),
4746 INSERT into config.org_unit_setting_type
4747 ( name, label, description, datatype ) VALUES
4748 ( 'ui.unified_volume_copy_editor',
4749 oils_i18n_gettext( 'ui.unified_volume_copy_editor', 'GUI: Unified Volume/Item Creator/Editor', 'coust', 'label'),
4750 oils_i18n_gettext( 'ui.unified_volume_copy_editor', 'If true combines the Volume/Copy Creator and Item Attribute Editor in some instances.', 'coust', 'description'),
4755 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
4757 transformed_xml TEXT;
4760 xfrm config.xml_transform%ROWTYPE;
4762 new_attrs HSTORE := ''::HSTORE;
4763 attr_def config.record_attr_definition%ROWTYPE;
4766 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
4767 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
4768 DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records
4769 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
4770 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
4771 RETURN NEW; -- and we're done
4774 IF TG_OP = 'UPDATE' THEN -- re-ingest?
4775 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
4777 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
4782 -- Record authority linking
4783 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
4785 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
4788 -- Flatten and insert the mfr data
4789 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
4791 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
4793 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
4794 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
4796 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
4798 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
4799 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
4800 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
4801 WHERE record = NEW.id
4802 AND tag LIKE attr_def.tag
4804 WHEN attr_def.sf_list IS NOT NULL
4805 THEN POSITION(subfield IN attr_def.sf_list) > 0
4812 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
4813 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
4815 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
4817 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
4819 -- See if we can skip the XSLT ... it's expensive
4820 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
4821 -- Can't skip the transform
4822 IF xfrm.xslt <> '---' THEN
4823 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
4825 transformed_xml := NEW.marc;
4828 prev_xfrm := xfrm.name;
4831 IF xfrm.name IS NULL THEN
4832 -- just grab the marcxml (empty) transform
4833 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
4834 prev_xfrm := xfrm.name;
4837 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
4839 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
4840 SELECT m.value INTO attr_value
4841 FROM biblio.marc21_physical_characteristics(NEW.id) v
4842 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
4843 WHERE v.subfield = attr_def.phys_char_sf
4844 LIMIT 1; -- Just in case ...
4848 -- apply index normalizers to attr_value
4850 SELECT n.func AS func,
4851 n.param_count AS param_count,
4853 FROM config.index_normalizer n
4854 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
4855 WHERE attr = attr_def.name
4857 EXECUTE 'SELECT ' || normalizer.func || '(' ||
4858 quote_literal( attr_value ) ||
4860 WHEN normalizer.param_count > 0
4861 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
4864 ')' INTO attr_value;
4868 -- Add the new value to the hstore
4869 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
4873 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
4874 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
4876 UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id;
4882 -- Gather and insert the field entry data
4883 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
4885 -- Located URI magic
4886 IF TG_OP = 'INSERT' THEN
4887 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4889 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4892 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4894 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4898 -- (re)map metarecord-bib linking
4899 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
4900 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
4902 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4904 ELSE -- we're doing an update, and we're not deleted, remap
4905 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
4907 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4913 $func$ LANGUAGE PLPGSQL;
4915 ALTER TABLE config.circ_matrix_weights ADD COLUMN marc_bib_level NUMERIC(6,2) NOT NULL DEFAULT 0.0;
4917 UPDATE config.circ_matrix_weights
4918 SET marc_bib_level = marc_vr_format;
4920 ALTER TABLE config.hold_matrix_weights ADD COLUMN marc_bib_level NUMERIC(6, 2) NOT NULL DEFAULT 0.0;
4922 UPDATE config.hold_matrix_weights
4923 SET marc_bib_level = marc_vr_format;
4925 ALTER TABLE config.circ_matrix_weights ALTER COLUMN marc_bib_level DROP DEFAULT;
4927 ALTER TABLE config.hold_matrix_weights ALTER COLUMN marc_bib_level DROP DEFAULT;
4929 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN marc_bib_level text;
4931 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN marc_bib_level text;
4933 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$
4935 cn_object asset.call_number%ROWTYPE;
4936 rec_descriptor metabib.rec_descriptor%ROWTYPE;
4937 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
4938 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
4939 weights config.circ_matrix_weights%ROWTYPE;
4941 denominator NUMERIC(6,2);
4943 result action.found_circ_matrix_matchpoint;
4946 result.success = false;
4948 -- Fetch useful data
4949 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4950 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
4952 -- Pre-generate this so we only calc it once
4953 IF user_object.dob IS NOT NULL THEN
4954 SELECT INTO user_age age(user_object.dob);
4957 -- Grab the closest set circ weight setting.
4958 SELECT INTO weights cw.*
4959 FROM config.weight_assoc wa
4960 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
4961 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
4966 -- No weights? Bad admin! Defaults to handle that anyway.
4967 IF weights.id IS NULL THEN
4968 weights.grp := 11.0;
4969 weights.org_unit := 10.0;
4970 weights.circ_modifier := 5.0;
4971 weights.marc_type := 4.0;
4972 weights.marc_form := 3.0;
4973 weights.marc_bib_level := 2.0;
4974 weights.marc_vr_format := 2.0;
4975 weights.copy_circ_lib := 8.0;
4976 weights.copy_owning_lib := 8.0;
4977 weights.user_home_ou := 8.0;
4978 weights.ref_flag := 1.0;
4979 weights.juvenile_flag := 6.0;
4980 weights.is_renewal := 7.0;
4981 weights.usr_age_lower_bound := 0.0;
4982 weights.usr_age_upper_bound := 0.0;
4985 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
4986 -- If you break your org tree with funky parenting this may be wrong
4987 -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
4988 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
4989 WITH all_distance(distance) AS (
4990 SELECT depth AS distance FROM actor.org_unit_type
4992 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
4994 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
4996 -- Loop over all the potential matchpoints
4997 FOR cur_matchpoint IN
4999 FROM config.circ_matrix_matchpoint m
5000 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
5001 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
5002 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
5003 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
5004 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
5006 -- Permission Groups
5007 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
5009 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
5010 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
5011 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
5012 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
5014 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
5015 -- Static User Checks
5016 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
5017 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
5018 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
5019 -- Static Item Checks
5020 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
5021 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
5022 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
5023 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
5024 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
5025 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
5027 -- Permission Groups
5028 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
5030 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
5031 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
5032 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
5033 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
5034 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
5035 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
5036 -- Static User Checks
5037 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
5038 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
5039 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
5040 -- Static Item Checks
5041 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
5042 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
5043 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
5044 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
5045 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END DESC,
5046 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
5047 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
5050 -- Record the full matching row list
5051 row_list := row_list || cur_matchpoint.id;
5053 -- No matchpoint yet?
5054 IF matchpoint.id IS NULL THEN
5055 -- Take the entire matchpoint as a starting point
5056 matchpoint := cur_matchpoint;
5057 CONTINUE; -- No need to look at this row any more.
5060 -- Incomplete matchpoint?
5061 IF matchpoint.circulate IS NULL THEN
5062 matchpoint.circulate := cur_matchpoint.circulate;
5064 IF matchpoint.duration_rule IS NULL THEN
5065 matchpoint.duration_rule := cur_matchpoint.duration_rule;
5067 IF matchpoint.recurring_fine_rule IS NULL THEN
5068 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
5070 IF matchpoint.max_fine_rule IS NULL THEN
5071 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
5073 IF matchpoint.hard_due_date IS NULL THEN
5074 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
5076 IF matchpoint.total_copy_hold_ratio IS NULL THEN
5077 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
5079 IF matchpoint.available_copy_hold_ratio IS NULL THEN
5080 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
5082 IF matchpoint.renewals IS NULL THEN
5083 matchpoint.renewals := cur_matchpoint.renewals;
5085 IF matchpoint.grace_period IS NULL THEN
5086 matchpoint.grace_period := cur_matchpoint.grace_period;
5090 -- Check required fields
5091 IF matchpoint.circulate IS NOT NULL AND
5092 matchpoint.duration_rule IS NOT NULL AND
5093 matchpoint.recurring_fine_rule IS NOT NULL AND
5094 matchpoint.max_fine_rule IS NOT NULL THEN
5095 -- All there? We have a completed match.
5096 result.success := true;
5099 -- Include the assembled matchpoint, even if it isn't complete
5100 result.matchpoint := matchpoint;
5102 -- Include (for debugging) the full list of matching rows
5103 result.buildrows := row_list;
5105 -- Hand the result back to caller
5108 $func$ LANGUAGE plpgsql;
5110 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer)
5114 requestor_object actor.usr%ROWTYPE;
5115 user_object actor.usr%ROWTYPE;
5116 item_object asset.copy%ROWTYPE;
5117 item_cn_object asset.call_number%ROWTYPE;
5118 rec_descriptor metabib.rec_descriptor%ROWTYPE;
5119 matchpoint config.hold_matrix_matchpoint%ROWTYPE;
5120 weights config.hold_matrix_weights%ROWTYPE;
5121 denominator NUMERIC(6,2);
5123 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
5124 SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
5125 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
5126 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
5127 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = item_cn_object.record;
5129 -- The item's owner should probably be the one determining if the item is holdable
5130 -- How to decide that is debatable. Decided to default to the circ library (where the item lives)
5131 -- This flag will allow for setting it to the owning library (where the call number "lives")
5132 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled;
5134 -- Grab the closest set circ weight setting.
5136 -- Default to circ library
5137 SELECT INTO weights hw.*
5138 FROM config.weight_assoc wa
5139 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
5140 JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id)
5145 -- Flag is set, use owning library
5146 SELECT INTO weights hw.*
5147 FROM config.weight_assoc wa
5148 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
5149 JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) d ON (wa.org_unit = d.id)
5155 -- No weights? Bad admin! Defaults to handle that anyway.
5156 IF weights.id IS NULL THEN
5157 weights.user_home_ou := 5.0;
5158 weights.request_ou := 5.0;
5159 weights.pickup_ou := 5.0;
5160 weights.item_owning_ou := 5.0;
5161 weights.item_circ_ou := 5.0;
5162 weights.usr_grp := 7.0;
5163 weights.requestor_grp := 8.0;
5164 weights.circ_modifier := 4.0;
5165 weights.marc_type := 3.0;
5166 weights.marc_form := 2.0;
5167 weights.marc_bib_level := 1.0;
5168 weights.marc_vr_format := 1.0;
5169 weights.juvenile_flag := 4.0;
5170 weights.ref_flag := 0.0;
5173 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
5174 -- If you break your org tree with funky parenting this may be wrong
5175 -- Note: This CTE is duplicated in the find_circ_matrix_matchpoint function, and it may be a good idea to split it off to a function
5176 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
5177 WITH all_distance(distance) AS (
5178 SELECT depth AS distance FROM actor.org_unit_type
5180 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
5182 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
5184 -- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids.
5185 -- This may be better implemented as part of the upgrade script?
5186 -- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set
5187 -- Then remove this flag, of course.
5188 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;
5191 -- Note: This, to me, is REALLY hacky. I put it in anyway.
5192 -- If you can't tell, this is a single call swap on two variables.
5193 SELECT INTO user_object.profile, requestor_object.profile
5194 requestor_object.profile, user_object.profile;
5197 -- Select the winning matchpoint into the matchpoint variable for returning
5198 SELECT INTO matchpoint m.*
5199 FROM config.hold_matrix_matchpoint m
5200 /*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id
5201 LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id
5202 LEFT JOIN actor.org_unit_ancestors_distance( pickup_ou ) puoua ON m.pickup_ou = puoua.id
5203 LEFT JOIN actor.org_unit_ancestors_distance( request_ou ) rqoua ON m.request_ou = rqoua.id
5204 LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id
5205 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id
5206 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
5208 -- Permission Groups
5209 -- AND (m.requestor_grp IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group?
5210 AND (m.usr_grp IS NULL OR upgad.id IS NOT NULL)
5212 AND (m.pickup_ou IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match)))
5213 AND (m.request_ou IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match)))
5214 AND (m.item_owning_ou IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match)))
5215 AND (m.item_circ_ou IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match)))
5216 AND (m.user_home_ou IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match)))
5217 -- Static User Checks
5218 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
5219 -- Static Item Checks
5220 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
5221 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
5222 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
5223 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
5224 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
5225 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
5227 -- Permission Groups
5228 CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END +
5229 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END +
5231 CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END +
5232 CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END +
5233 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END +
5234 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END +
5235 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
5236 -- Static User Checks -- Note: 4^x is equiv to 2^(2*x)
5237 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
5238 -- Static Item Checks
5239 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
5240 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
5241 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
5242 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
5243 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END DESC,
5244 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
5245 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
5248 -- Return just the ID for now
5249 RETURN matchpoint.id;
5251 $func$ LANGUAGE 'plpgsql';
5254 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
5257 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5260 use Unicode::Normalize;
5262 MARC::Charset->assume_unicode(1);
5264 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
5265 my $schema = $_TD->{table_schema};
5266 my $rec_id = $_TD->{new}{id};
5268 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
5269 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
5270 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
5274 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
5275 my $ou_cni = 'EVRGRN';
5278 if ($schema eq 'serial') {
5279 $owner = $_TD->{new}{owning_lib};
5281 # are.owner and bre.owner can be null, so fall back to the consortial setting
5282 $owner = $_TD->{new}{owner} || 1;
5285 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
5286 if ($ous_rv->{processed}) {
5287 $ou_cni = $ous_rv->{rows}[0]->{value};
5288 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
5290 # Fall back to the shortname of the OU if there was no OU setting
5291 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
5292 if ($ous_rv->{processed}) {
5293 $ou_cni = $ous_rv->{rows}[0]->{shortname};
5297 my ($create, $munge) = (0, 0);
5299 my @scns = $record->field('035');
5301 foreach my $id_field ('001', '003') {
5303 my @controls = $record->field($id_field);
5305 if ($id_field eq '001') {
5306 $spec_value = $rec_id;
5308 $spec_value = $ou_cni;
5311 # Create the 001/003 if none exist
5312 if (scalar(@controls) == 1) {
5313 # Only one field; check to see if we need to munge it
5314 unless (grep $_->data() eq $spec_value, @controls) {
5318 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
5319 foreach my $control (@controls) {
5320 unless ($control->data() eq $spec_value) {
5321 $record->delete_field($control);
5324 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
5329 # Now, if we need to munge the 001, we will first push the existing 001/003
5330 # into the 035; but if the record did not have one (and one only) 001 and 003
5331 # to begin with, skip this process
5332 if ($munge and not $create) {
5333 my $scn = "(" . $record->field('003')->data() . ")" . $record->field('001')->data();
5335 # Do not create duplicate 035 fields
5336 unless (grep $_->subfield('a') eq $scn, @scns) {
5337 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
5341 # Set the 001/003 and update the MARC
5342 if ($create or $munge) {
5343 $record->field('001')->data($rec_id);
5344 $record->field('003')->data($ou_cni);
5346 my $xml = $record->as_xml_record();
5348 $xml =~ s/^<\?xml.+\?\s*>//go;
5349 $xml =~ s/>\s+</></go;
5350 $xml =~ s/\p{Cc}//go;
5352 # Embed a version of OpenILS::Application::AppUtils->entityize()
5353 # to avoid having to set PERL5LIB for PostgreSQL as well
5355 # If we are going to convert non-ASCII characters to XML entities,
5356 # we had better be dealing with a UTF8 string to begin with
5357 $xml = decode_utf8($xml);
5361 # Convert raw ampersands to entities
5362 $xml =~ s/&(?!\S+;)/&/gso;
5364 # Convert Unicode characters to entities
5365 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
5367 $xml =~ s/[\x00-\x1f]//go;
5368 $_TD->{new}{marc} = $xml;
5374 $func$ LANGUAGE PLPERLU;
5376 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
5379 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5382 MARC::Charset->assume_unicode(1);
5385 my $r = MARC::Record->new_from_xml( $xml );
5387 return undef unless ($r);
5389 my $id = shift() || $r->subfield( '901' => 'c' );
5390 $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
5391 return undef unless ($id); # We need an ID!
5393 my $tmpl = MARC::Record->new();
5394 $tmpl->encoding( 'UTF-8' );
5397 for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
5399 my $tag = $field->tag;
5400 my $i1 = $field->indicator(1);
5401 my $i2 = $field->indicator(2);
5402 my $sf = join '', map { $_->[0] } $field->subfields;
5403 my @data = map { @$_ } $field->subfields;
5407 # Map the authority field to bib fields it can control.
5408 if ($tag >= 100 and $tag <= 111) { # names
5409 @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
5410 } elsif ($tag eq '130') { # uniform title
5411 @replace_them = qw/130 240 440 730 830/;
5412 } elsif ($tag >= 150 and $tag <= 155) { # subjects
5413 @replace_them = ($tag + 500);
5414 } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions
5415 @replace_them = qw/100 400 600 700 800 110 410 610 710 810 111 411 611 711 811 130 240 440 730 830 650 651 655/;
5420 # Dummy up the bib-side data
5421 $tmpl->append_fields(
5423 MARC::Field->new( $_, $i1, $i2, @data )
5427 # Construct some 'replace' rules
5428 push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
5431 # Insert the replace rules into the template
5432 $tmpl->append_fields(
5433 MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
5436 $xml = $tmpl->as_xml_record;
5437 $xml =~ s/^<\?.+?\?>$//mo;
5439 $xml =~ s/>\s+</></sgo;
5443 $func$ LANGUAGE PLPERLU;
5445 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
5448 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5452 MARC::Charset->assume_unicode(1);
5454 my $target_xml = shift;
5455 my $source_xml = shift;
5456 my $field_spec = shift;
5457 my $force_add = shift || 0;
5459 my $target_r = MARC::Record->new_from_xml( $target_xml );
5460 my $source_r = MARC::Record->new_from_xml( $source_xml );
5462 return $target_xml unless ($target_r && $source_r);
5464 my @field_list = split(',', $field_spec);
5467 for my $f (@field_list) {
5468 $f =~ s/^\s*//; $f =~ s/\s*$//;
5469 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
5475 $match =~ s/^\s*//; $match =~ s/\s*$//;
5476 $fields{$field} = { sf => [ split('', $sf) ] };
5478 my ($msf,$mre) = split('~', $match);
5479 if (length($msf) > 0 and length($mre) > 0) {
5480 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
5481 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
5482 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
5488 for my $f ( keys %fields) {
5489 if ( @{$fields{$f}{sf}} ) {
5490 for my $from_field ($source_r->field( $f )) {
5491 my @tos = $target_r->field( $f );
5493 next if (exists($fields{$f}{match}) and !$force_add);
5494 my @new_fields = map { $_->clone } $source_r->field( $f );
5495 $target_r->insert_fields_ordered( @new_fields );
5497 for my $to_field (@tos) {
5498 if (exists($fields{$f}{match})) {
5499 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
5501 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
5502 $to_field->add_subfields( @new_sf );
5507 my @new_fields = map { $_->clone } $source_r->field( $f );
5508 $target_r->insert_fields_ordered( @new_fields );
5512 $target_xml = $target_r->as_xml_record;
5513 $target_xml =~ s/^<\?.+?\?>$//mo;
5514 $target_xml =~ s/\n//sgo;
5515 $target_xml =~ s/>\s+</></sgo;
5519 $_$ LANGUAGE PLPERLU;
5521 CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $func$
5527 use MARC::File::XML (BinaryEncoding => 'UTF8');
5529 use UUID::Tiny ':std';
5531 MARC::Charset->assume_unicode(1);
5533 my $xml = shift() or return undef;
5537 # Prevent errors in XML parsing from blowing out ungracefully
5539 $r = MARC::Record->new_from_xml( $xml );
5542 return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
5546 return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
5549 # From http://www.loc.gov/standards/sourcelist/subject.html
5550 my $thes_code_map = {
5556 n => 'notapplicable',
5562 # Default to "No attempt to code" if the leader is horribly broken
5563 my $fixed_field = $r->field('008');
5564 my $thes_char = '|';
5566 $thes_char = substr($fixed_field->data(), 11, 1) || '|';
5569 my $thes_code = 'UNDEFINED';
5571 if ($thes_char eq 'z') {
5572 # Grab the 040 $f per http://www.loc.gov/marc/authority/ad040.html
5573 $thes_code = $r->subfield('040', 'f') || 'UNDEFINED';
5574 } elsif ($thes_code_map->{$thes_char}) {
5575 $thes_code = $thes_code_map->{$thes_char};
5579 my $head = $r->field('1..');
5581 # Concatenate all of these subfields together, prefixed by their code
5582 # to prevent collisions along the lines of "Fiction, North Carolina"
5583 foreach my $sf ($head->subfields()) {
5584 $auth_txt .= '‡' . $sf->[0] . ' ' . $sf->[1];
5589 my $stmt = spi_prepare('SELECT public.naco_normalize($1) AS norm_text', 'TEXT');
5590 my $result = spi_exec_prepared($stmt, $auth_txt);
5591 my $norm_txt = $result->{rows}[0]->{norm_text};
5592 spi_freeplan($stmt);
5594 return $head->tag() . "_" . $thes_code . " " . $norm_txt;
5597 return 'NOHEADING_' . $thes_code . ' ' . create_uuid_as_string(UUID_MD5, $xml);
5598 $func$ LANGUAGE 'plperlu' IMMUTABLE;
5600 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
5603 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5607 MARC::Charset->assume_unicode(1);
5610 my $r = MARC::Record->new_from_xml( $xml );
5612 return $xml unless ($r);
5614 my $field_spec = shift;
5615 my @field_list = split(',', $field_spec);
5618 for my $f (@field_list) {
5619 $f =~ s/^\s*//; $f =~ s/\s*$//;
5620 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
5626 $match =~ s/^\s*//; $match =~ s/\s*$//;
5627 $fields{$field} = { sf => [ split('', $sf) ] };
5629 my ($msf,$mre) = split('~', $match);
5630 if (length($msf) > 0 and length($mre) > 0) {
5631 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
5632 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
5633 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
5639 for my $f ( keys %fields) {
5640 for my $to_field ($r->field( $f )) {
5641 if (exists($fields{$f}{match})) {
5642 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
5645 if ( @{$fields{$f}{sf}} ) {
5646 $to_field->delete_subfield(code => $fields{$f}{sf});
5648 $r->delete_field( $to_field );
5653 $xml = $r->as_xml_record;
5654 $xml =~ s/^<\?.+?\?>$//mo;
5656 $xml =~ s/>\s+</></sgo;
5660 $_$ LANGUAGE PLPERLU;
5662 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( TEXT ) RETURNS SETOF metabib.full_rec AS $func$
5665 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5668 MARC::Charset->assume_unicode(1);
5671 my $r = MARC::Record->new_from_xml( $xml );
5673 return_next( { tag => 'LDR', value => $r->leader } );
5675 for my $f ( $r->fields ) {
5676 if ($f->is_control_field) {
5677 return_next({ tag => $f->tag, value => $f->data });
5679 for my $s ($f->subfields) {
5682 ind1 => $f->indicator(1),
5683 ind2 => $f->indicator(2),
5684 subfield => $s->[0],
5688 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
5689 my $trim = $f->indicator(2) || 0;
5692 ind1 => $f->indicator(1),
5693 ind2 => $f->indicator(2),
5695 value => substr( $s->[1], $trim )
5704 $func$ LANGUAGE PLPERLU;
5706 CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$
5709 use MARC::File::XML (BinaryEncoding => 'UTF-8');
5712 MARC::Charset->assume_unicode(1);
5715 my $r = MARC::Record->new_from_xml( $xml );
5717 return_next( { tag => 'LDR', value => $r->leader } );
5719 for my $f ( $r->fields ) {
5720 if ($f->is_control_field) {
5721 return_next({ tag => $f->tag, value => $f->data });
5723 for my $s ($f->subfields) {
5726 ind1 => $f->indicator(1),
5727 ind2 => $f->indicator(2),
5728 subfield => $s->[0],
5738 $func$ LANGUAGE PLPERLU;
5741 INSERT INTO config.org_unit_setting_type
5742 ( name, label, description, datatype ) VALUES
5743 ( 'circ.user_merge.delete_addresses',
5744 'Circ: Patron Merge Address Delete',
5745 'Delete address(es) of subordinate user(s) in a patron merge',
5749 INSERT INTO config.org_unit_setting_type
5750 ( name, label, description, datatype ) VALUES
5751 ( 'circ.user_merge.delete_cards',
5752 'Circ: Patron Merge Barcode Delete',
5753 'Delete barcode(s) of subordinate user(s) in a patron merge',
5757 INSERT INTO config.org_unit_setting_type
5758 ( name, label, description, datatype ) VALUES
5759 ( 'circ.user_merge.deactivate_cards',
5760 'Circ: Patron Merge Deactivate Card',
5761 'Mark barcode(s) of subordinate user(s) in a patron merge as inactive',
5766 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
5767 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
5769 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
5770 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
5772 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
5773 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
5776 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
5781 -- If there are any renewals for this circulation, don't archive or delete
5782 -- it yet. We'll do so later, when we archive and delete the renewals.
5784 SELECT 'Y' INTO found
5785 FROM action.circulation
5786 WHERE parent_circ = OLD.id
5790 RETURN NULL; -- don't delete
5793 -- Archive a copy of the old row to action.aged_circulation
5795 INSERT INTO action.aged_circulation
5796 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
5797 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
5798 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
5799 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
5800 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
5801 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
5803 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
5804 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
5805 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
5806 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
5807 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
5808 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
5809 FROM action.all_circulation WHERE id = OLD.id;
5813 $$ LANGUAGE 'plpgsql';
5815 -- do potentially large updates last to save time if upgrader needs
5816 -- to manually tweak the upgrade script to resolve errors
5819 UPDATE metabib.facet_entry SET value = evergreen.force_unicode_normal_form(value,'NFC');
5821 UPDATE asset.call_number SET id = id;
5823 -- Update reporter.materialized_simple_record with normalized ISBN values
5824 -- This might not get all of them, but most ISBNs will have more than one hyphen
5825 DELETE FROM reporter.materialized_simple_record WHERE id IN (
5826 SELECT record FROM metabib.full_rec WHERE tag = '020' AND subfield IN ('a', 'z') AND value LIKE '%-%-%'
5829 INSERT INTO reporter.materialized_simple_record
5830 SELECT DISTINCT rossr.* FROM reporter.old_super_simple_record rossr INNER JOIN metabib.full_rec mfr ON mfr.record = rossr.id
5831 WHERE mfr.tag = '020' AND mfr.subfield IN ('a', 'z') AND mfr.value LIKE '%-%-%'