1 --Upgrade Script for 2.12.6 to 3.0.0
2 \set eg_version '''3.0.0'''
4 -- verify that we're running a recent enough version of Pg
11 SELECT current_setting('server_version_num') INTO ver;
13 RAISE EXCEPTION 'Not running a new enough version of PostgreSQL. Minimum required is 9.4; you have %', ver;
19 \set ON_ERROR_STOP off
22 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0-beta1', :eg_version);
24 SELECT evergreen.upgrade_deps_block_check('1032', :eg_version); -- Bmagic/csharp/gmcharlt
26 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
27 SELECT acirc.id AS id,
32 ac_acirc.circ_modifier AS item_type,
33 'regular_circ'::text AS circ_type
34 FROM action.circulation acirc,
36 WHERE acirc.target_copy = ac_acirc.id
38 SELECT ancc.id::BIGINT AS id,
39 ancc.circ_time AS xact_start,
41 ancc.staff AS circ_staff,
42 ancc.circ_time AS create_time,
43 cnct_ancc.name AS item_type,
44 'non-cat_circ'::text AS circ_type
45 FROM action.non_cataloged_circulation ancc,
46 config.non_cataloged_type cnct_ancc
47 WHERE ancc.item_type = cnct_ancc.id
49 SELECT aihu.id::BIGINT AS id,
50 aihu.use_time AS xact_start,
51 aihu.org_unit AS circ_lib,
52 aihu.staff AS circ_staff,
53 aihu.use_time AS create_time,
54 ac_aihu.circ_modifier AS item_type,
55 'in-house_use'::text AS circ_type
56 FROM action.in_house_use aihu,
58 WHERE aihu.item = ac_aihu.id
60 SELECT ancihu.id::BIGINT AS id,
61 ancihu.use_time AS xact_start,
62 ancihu.org_unit AS circ_lib,
63 ancihu.staff AS circ_staff,
64 ancihu.use_time AS create_time,
65 cnct_ancihu.name AS item_type,
66 'non-cat_circ'::text AS circ_type
67 FROM action.non_cat_in_house_use ancihu,
68 config.non_cataloged_type cnct_ancihu
69 WHERE ancihu.item_type = cnct_ancihu.id
71 SELECT aacirc.id AS id,
76 ac_aacirc.circ_modifier AS item_type,
77 'aged_circ'::text AS circ_type
78 FROM action.aged_circulation aacirc,
80 WHERE aacirc.target_copy = ac_aacirc.id;
83 SELECT evergreen.upgrade_deps_block_check('1034', :eg_version);
85 ALTER TABLE config.hold_matrix_matchpoint
86 ADD COLUMN description TEXT;
88 ALTER TABLE config.circ_matrix_matchpoint
89 ADD COLUMN description TEXT;
92 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1035', :eg_version); -- dyrcona/gmcharlt
94 -- Per Lp bug 1684984, the config.internal_flag,
95 -- ingest.disable_metabib_field_entry, was made obsolete by the
96 -- addition of the ingest.skip_browse_indexing,
97 -- ingest.skip_search_indexing, and ingest.skip_facet_indexing flags.
98 -- Since it is not used in the database, we delete it.
99 DELETE FROM config.internal_flag
100 WHERE name = 'ingest.disable_metabib_field_entry';
103 SELECT evergreen.upgrade_deps_block_check('1036', :eg_version);
105 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
107 temp_value config.hard_due_date_values%ROWTYPE;
111 SELECT DISTINCT ON (hard_due_date) *
112 FROM config.hard_due_date_values
113 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
114 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
116 UPDATE config.hard_due_date
117 SET ceiling_date = temp_value.ceiling_date
118 WHERE id = temp_value.hard_due_date
119 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
120 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
123 updated := updated + 1;
129 $func$ LANGUAGE plpgsql;
132 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1041', :eg_version); -- stompro/csharp/gmcharlt
134 --delete all instances from permission.grp_perm_map first
135 DELETE FROM permission.grp_perm_map where perm in
136 (select id from permission.perm_list where code='SET_CIRC_MISSING');
138 --delete all instances from permission.usr_perm_map too
139 DELETE FROM permission.usr_perm_map where perm in
140 (select id from permission.perm_list where code='SET_CIRC_MISSING');
142 --delete from permission.perm_list
143 DELETE FROM permission.perm_list where code='SET_CIRC_MISSING';
146 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1042', :eg_version); -- mmorgan/gmcharlt
148 ALTER TABLE asset.copy_location
152 SELECT evergreen.upgrade_deps_block_check('1043', :eg_version);
154 ALTER TABLE action_trigger.event_definition
155 ADD COLUMN retention_interval INTERVAL;
157 CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval()
158 RETURNS TRIGGER AS $_$
162 * 1. Retention intervals are alwyas allowed on active hooks.
163 * 2. On passive hooks, retention intervals are only allowed
164 * when the event definition has a max_delay value and the
165 * retention_interval value is greater than the difference
166 * beteween the delay and max_delay values.
168 PERFORM TRUE FROM action_trigger.hook
169 WHERE key = NEW.hook AND NOT passive;
175 IF NEW.max_delay IS NOT NULL THEN
176 IF EXTRACT(EPOCH FROM NEW.retention_interval) >
177 ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
178 RETURN NEW; -- all good
180 RAISE EXCEPTION 'retention_interval is too short';
183 RAISE EXCEPTION 'retention_interval requires max_delay';
186 $_$ LANGUAGE PLPGSQL;
188 CREATE TRIGGER is_valid_retention_interval
189 BEFORE INSERT OR UPDATE ON action_trigger.event_definition
190 FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL)
191 EXECUTE PROCEDURE action_trigger.check_valid_retention_interval();
193 CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$
195 * Deleting expired events without simultaneously deleting their outputs
196 * creates orphaned outputs. Deleting their outputs and all of the events
197 * linking back to them, plus any outputs those events link to is messy and
198 * inefficient. It's simpler to handle them in 2 sweeping steps.
200 * 1. Delete expired events.
201 * 2. Delete orphaned event outputs.
203 * This has the added benefit of removing outputs that may have been
204 * orphaned by some other process. Such outputs are not usuable by
207 * This does not guarantee that all events within an event group are
208 * purged at the same time. In such cases, the remaining events will
209 * be purged with the next instance of the purge (or soon thereafter).
210 * This is another nod toward efficiency over completeness of old
211 * data that's circling the bit bucket anyway.
215 DELETE FROM action_trigger.event WHERE id IN (
217 FROM action_trigger.event evt
218 JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
219 WHERE def.retention_interval IS NOT NULL
220 AND evt.state <> 'pending'
221 AND evt.update_time < (NOW() - def.retention_interval)
224 WITH linked_outputs AS (
225 SELECT templates.id AS id FROM (
226 SELECT DISTINCT(template_output) AS id
227 FROM action_trigger.event WHERE template_output IS NOT NULL
229 SELECT DISTINCT(error_output) AS id
230 FROM action_trigger.event WHERE error_output IS NOT NULL
232 SELECT DISTINCT(async_output) AS id
233 FROM action_trigger.event WHERE async_output IS NOT NULL
235 ) DELETE FROM action_trigger.event_output
236 WHERE id NOT IN (SELECT id FROM linked_outputs);
239 $_$ LANGUAGE PLPGSQL;
244 DROP FUNCTION IF EXISTS action_trigger.purge_events();
245 DROP TRIGGER IF EXISTS is_valid_retention_interval ON action_trigger.event_definition;
246 DROP FUNCTION IF EXISTS action_trigger.check_valid_retention_interval();
247 ALTER TABLE action_trigger.event_definition DROP COLUMN retention_interval;
253 SELECT evergreen.upgrade_deps_block_check('1044', :eg_version);
255 UPDATE action_trigger.hook SET passive = FALSE WHERE key IN (
258 'format.selfcheck.checkout',
259 'format.selfcheck.items_out',
260 'format.selfcheck.holds',
261 'format.selfcheck.fines',
262 'format.acqcle.html',
263 'format.acqinv.html',
273 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1045', :eg_version); -- csharp/berick/gmcharlt
275 ALTER TABLE action.transit_copy
276 ADD COLUMN cancel_time TIMESTAMPTZ;
278 -- change "abort" to "cancel" in stock perm descriptions
279 UPDATE permission.perm_list
280 SET description = 'Allow a user to cancel a copy transit if the user is at the transit destination or source'
281 WHERE code = 'ABORT_TRANSIT'
282 AND description = 'Allow a user to abort a copy transit if the user is at the transit destination or source';
283 UPDATE permission.perm_list
284 SET description = 'Allow a user to cancel a copy transit if the user is not at the transit source or dest'
285 WHERE code = 'ABORT_REMOTE_TRANSIT'
286 AND description = 'Allow a user to abort a copy transit if the user is not at the transit source or dest';
287 UPDATE permission.perm_list
288 SET description = 'Allows a user to cancel a transit on a copy with status of LOST'
289 WHERE code = 'ABORT_TRANSIT_ON_LOST'
290 AND description = 'Allows a user to abort a transit on a copy with status of LOST';
291 UPDATE permission.perm_list
292 SET description = 'Allows a user to cancel a transit on a copy with status of MISSING'
293 WHERE code = 'ABORT_TRANSIT_ON_MISSING'
294 AND description = 'Allows a user to abort a transit on a copy with status of MISSING';
296 SELECT evergreen.upgrade_deps_block_check('1046', :eg_version); -- phasefx/berick/gmcharlt
298 INSERT into config.org_unit_setting_type (
304 ) VALUES ( ----------------------------------------
305 'webstaff.format.dates'
308 'webstaff.format.dates'
309 ,'Format Dates with this pattern'
314 'webstaff.format.dates'
315 ,'Format Dates with this pattern (examples: "yyyy-MM-dd" for "2010-04-26", "MMM d, yyyy" for "Apr 26, 2010"). This will be used in areas where a date without a timestamp is sufficient, like Date of Birth.'
320 ), ( ----------------------------------------
321 'webstaff.format.date_and_time'
324 'webstaff.format.date_and_time'
325 ,'Format Date+Time with this pattern'
330 'webstaff.format.date_and_time'
331 ,'Format Date+Time with this pattern (examples: "yy-MM-dd h:m:s.SSS a" for "16-04-05 2:07:20.666 PM", "yyyy-dd-MMM HH:mm" for "2016-05-Apr 14:07"). This will be used in areas of the client where a date with a timestamp is needed, like Checkout, Due Date, or Record Created.'
339 config.org_unit_setting_type
341 label = 'Deprecated: ' || label -- FIXME: Is this okay?
343 name IN ('format.date','format.time')
347 SELECT evergreen.upgrade_deps_block_check('1047', :eg_version); -- gmcharlt/stompro
349 CREATE TABLE config.copy_tag_type (
350 code TEXT NOT NULL PRIMARY KEY,
352 owner INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
355 CREATE INDEX config_copy_tag_type_owner_idx
356 ON config.copy_tag_type (owner);
358 CREATE TABLE asset.copy_tag (
359 id SERIAL PRIMARY KEY,
360 tag_type TEXT REFERENCES config.copy_tag_type (code)
361 ON UPDATE CASCADE ON DELETE CASCADE,
364 index_vector tsvector NOT NULL,
366 pub BOOLEAN DEFAULT TRUE,
367 owner INTEGER NOT NULL REFERENCES actor.org_unit (id)
370 CREATE INDEX asset_copy_tag_label_idx
371 ON asset.copy_tag (label);
372 CREATE INDEX asset_copy_tag_label_lower_idx
373 ON asset.copy_tag (evergreen.lowercase(label));
374 CREATE INDEX asset_copy_tag_index_vector_idx
376 USING GIN(index_vector);
377 CREATE INDEX asset_copy_tag_tag_type_idx
378 ON asset.copy_tag (tag_type);
379 CREATE INDEX asset_copy_tag_owner_idx
380 ON asset.copy_tag (owner);
382 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
384 IF NEW.value IS NULL THEN
385 NEW.value = NEW.label;
390 $$ LANGUAGE 'plpgsql';
392 -- name of following trigger chosen to ensure it runs first
393 CREATE TRIGGER asset_copy_tag_do_value
394 BEFORE INSERT OR UPDATE ON asset.copy_tag
395 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
396 CREATE TRIGGER asset_copy_tag_fti_trigger
397 BEFORE UPDATE OR INSERT ON asset.copy_tag
398 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
400 CREATE TABLE asset.copy_tag_copy_map (
401 id BIGSERIAL PRIMARY KEY,
402 copy BIGINT REFERENCES asset.copy (id)
403 ON UPDATE CASCADE ON DELETE CASCADE,
404 tag INTEGER REFERENCES asset.copy_tag (id)
405 ON UPDATE CASCADE ON DELETE CASCADE
408 CREATE INDEX asset_copy_tag_copy_map_copy_idx
409 ON asset.copy_tag_copy_map (copy);
410 CREATE INDEX asset_copy_tag_copy_map_tag_idx
411 ON asset.copy_tag_copy_map (tag);
413 INSERT INTO config.copy_tag_type (code, label, owner) VALUES ('bookplate', 'Digital Bookplate', 1);
415 INSERT INTO permission.perm_list ( id, code, description ) VALUES
416 ( 590, 'ADMIN_COPY_TAG_TYPES', oils_i18n_gettext( 590,
417 'Administer copy tag types', 'ppl', 'description' )),
418 ( 591, 'ADMIN_COPY_TAG', oils_i18n_gettext( 591,
419 'Administer copy tag', 'ppl', 'description' ))
422 INSERT INTO config.org_unit_setting_type
423 (name, label, description, grp, datatype)
425 'opac.search.enable_bookplate_search',
427 'opac.search.enable_bookplate_search',
428 'Enable Digital Bookplate Search',
433 'opac.search.enable_bookplate_search',
434 'If enabled, adds a "Digital Bookplate" option to the query type selectors in the public catalog for search on copy tags.',
443 SELECT evergreen.upgrade_deps_block_check('1048', :eg_version);
445 INSERT into config.org_unit_setting_type (
451 ) VALUES ( ----------------------------------------
452 'webstaff.cat.label.font.family'
455 'webstaff.cat.label.font.family'
456 ,'Item Print Label Font Family'
461 'webstaff.cat.label.font.family'
462 ,'Set the preferred font family for item print labels. You can specify a list of CSS fonts, separated by commas, in order of preference; the system will use the first font it finds with a matching name. For example, "Arial, Helvetica, serif"'
467 ), ( ----------------------------------------
468 'webstaff.cat.label.font.size'
471 'webstaff.cat.label.font.size'
472 ,'Item Print Label Font Size'
477 'webstaff.cat.label.font.size'
478 ,'Set the default font size for item print labels. Please include a unit of measurement that is valid CSS. For example, "12pt" or "16px" or "1em"'
483 ), ( ----------------------------------------
484 'webstaff.cat.label.font.weight'
487 'webstaff.cat.label.font.weight'
488 ,'Item Print Label Font Weight'
493 'webstaff.cat.label.font.weight'
494 ,'Set the default font weight for item print labels. Please use the CSS specification for values for font-weight. For example, "normal", "bold", "bolder", or "lighter"'
499 ), ( ----------------------------------------
500 'webstaff.cat.label.left_label.left_margin'
503 'webstaff.cat.label.left_label.left_margin'
504 ,'Item Print Label - Left Margin for Left Label'
509 'webstaff.cat.label.left_label.left_margin'
510 ,'Set the default left margin for the leftmost item print Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
515 ), ( ----------------------------------------
516 'webstaff.cat.label.right_label.left_margin'
519 'webstaff.cat.label.right_label.left_margin'
520 ,'Item Print Label - Left Margin for Right Label'
525 'webstaff.cat.label.right_label.left_margin'
526 ,'Set the default left margin for the rightmost item print label (or in other words, the desired space between the two labels). Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
531 ), ( ----------------------------------------
532 'webstaff.cat.label.left_label.height'
535 'webstaff.cat.label.left_label.height'
536 ,'Item Print Label - Height for Left Label'
541 'webstaff.cat.label.left_label.height'
542 ,'Set the default height for the leftmost item print label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
547 ), ( ----------------------------------------
548 'webstaff.cat.label.left_label.width'
551 'webstaff.cat.label.left_label.width'
552 ,'Item Print Label - Width for Left Label'
557 'webstaff.cat.label.left_label.width'
558 ,'Set the default width for the leftmost item print label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
563 ), ( ----------------------------------------
564 'webstaff.cat.label.right_label.height'
567 'webstaff.cat.label.right_label.height'
568 ,'Item Print Label - Height for Right Label'
573 'webstaff.cat.label.right_label.height'
574 ,'Set the default height for the rightmost item print label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
579 ), ( ----------------------------------------
580 'webstaff.cat.label.right_label.width'
583 'webstaff.cat.label.right_label.width'
584 ,'Item Print Label - Width for Right Label'
589 'webstaff.cat.label.right_label.width'
590 ,'Set the default width for the rightmost item print label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"'
596 'webstaff.cat.label.inline_css'
599 'webstaff.cat.label.inline_css'
600 ,'Item Print Label - Inline CSS'
605 'webstaff.cat.label.inline_css'
606 ,'This setting allows you to inject arbitrary CSS into the item print label template. For example, ".printlabel { text-transform: uppercase; }"'
612 'webstaff.cat.label.call_number_wrap_filter_height'
615 'webstaff.cat.label.call_number_wrap_filter_height'
616 ,'Item Print Label - Call Number Wrap Filter Height'
621 'webstaff.cat.label.call_number_wrap_filter_height'
622 ,'This setting is used to set the default height (in number of lines) to use for call number wrapping in the left print label.'
628 'webstaff.cat.label.call_number_wrap_filter_width'
631 'webstaff.cat.label.call_number_wrap_filter_width'
632 ,'Item Print Label - Call Number Wrap Filter Width'
637 'webstaff.cat.label.call_number_wrap_filter_width'
638 ,'This setting is used to set the default width (in number of characters) to use for call number wrapping in the left print label.'
647 -- for testing, setting removal:
648 --DELETE FROM actor.org_unit_setting WHERE name IN (
649 -- 'webstaff.cat.label.font.family'
650 -- ,'webstaff.cat.label.font.size'
651 -- ,'webstaff.cat.label.font.weight'
652 -- ,'webstaff.cat.label.left_label.height'
653 -- ,'webstaff.cat.label.left_label.width'
654 -- ,'webstaff.cat.label.left_label.left_margin'
655 -- ,'webstaff.cat.label.right_label.height'
656 -- ,'webstaff.cat.label.right_label.width'
657 -- ,'webstaff.cat.label.right_label.left_margin'
658 -- ,'webstaff.cat.label.inline_css'
659 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
660 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
662 --DELETE FROM config.org_unit_setting_type_log WHERE field_name IN (
663 -- 'webstaff.cat.label.font.family'
664 -- ,'webstaff.cat.label.font.size'
665 -- ,'webstaff.cat.label.font.weight'
666 -- ,'webstaff.cat.label.left_label.height'
667 -- ,'webstaff.cat.label.left_label.width'
668 -- ,'webstaff.cat.label.left_label.left_margin'
669 -- ,'webstaff.cat.label.right_label.height'
670 -- ,'webstaff.cat.label.right_label.width'
671 -- ,'webstaff.cat.label.right_label.left_margin'
672 -- ,'webstaff.cat.label.inline_css'
673 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
674 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
676 --DELETE FROM config.org_unit_setting_type WHERE name IN (
677 -- 'webstaff.cat.label.font.family'
678 -- ,'webstaff.cat.label.font.size'
679 -- ,'webstaff.cat.label.font.weight'
680 -- ,'webstaff.cat.label.left_label.height'
681 -- ,'webstaff.cat.label.left_label.width'
682 -- ,'webstaff.cat.label.left_label.left_margin'
683 -- ,'webstaff.cat.label.right_label.height'
684 -- ,'webstaff.cat.label.right_label.width'
685 -- ,'webstaff.cat.label.right_label.left_margin'
686 -- ,'webstaff.cat.label.inline_css'
687 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
688 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
693 SELECT evergreen.upgrade_deps_block_check('1049', :eg_version); -- mmorgan/stompro/gmcharlt
695 \echo -----------------------------------------------------------
696 \echo Setting invalid age_protect and circ_as_type entries to NULL,
697 \echo otherwise they will break the Serial Copy Templates editor.
698 \echo Please review any Serial Copy Templates listed below.
700 UPDATE asset.copy_template act
701 SET age_protect = NULL
702 FROM actor.org_unit aou
703 WHERE aou.id=act.owning_lib
704 AND act.age_protect NOT IN
706 SELECT id FROM config.rule_age_hold_protect
708 RETURNING act.id "Template ID", act.name "Template Name",
709 aou.shortname "Owning Lib",
710 'Age Protection value reset to null.' "Description";
712 UPDATE asset.copy_template act
713 SET circ_as_type = NULL
714 FROM actor.org_unit aou
715 WHERE aou.id=act.owning_lib
716 AND act.circ_as_type NOT IN
718 SELECT code FROM config.item_type_map
720 RETURNING act.id "Template ID", act.name "Template Name",
721 aou.shortname "Owning Lib",
722 'Circ as Type value reset to null.' as "Description";
724 \echo -----------End Serial Template Fix----------------
726 SELECT evergreen.upgrade_deps_block_check('1050', :eg_version); -- mmorgan/cesardv/gmcharlt
728 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
729 SELECT DISTINCT ON (usr,perm) *
731 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
733 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
734 FROM permission.grp_perm_map p
736 SELECT (permission.grp_ancestors(
737 (SELECT profile FROM actor.usr WHERE id = $1)
742 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
743 FROM permission.grp_perm_map p
744 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
746 ORDER BY 2, 3, 4 ASC, 5 DESC ;
747 $$ LANGUAGE SQL STABLE ROWS 10;
749 SELECT evergreen.upgrade_deps_block_check('1051', :eg_version);
751 CREATE OR REPLACE VIEW action.all_circulation_slim AS
785 FROM action.circulation
820 FROM action.aged_circulation
823 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
824 DROP FUNCTION action.all_circ_chain(INTEGER);
826 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
827 RETURNS SETOF action.all_circulation_slim AS $$
829 tmp_circ action.all_circulation_slim%ROWTYPE;
830 circ_0 action.all_circulation_slim%ROWTYPE;
833 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
835 IF tmp_circ IS NULL THEN
836 RETURN NEXT tmp_circ;
840 -- find the front of the chain
842 SELECT INTO tmp_circ * FROM action.all_circulation_slim
843 WHERE id = tmp_circ.parent_circ;
844 IF tmp_circ IS NULL THEN
850 -- now send the circs to the caller, oldest to newest
853 IF tmp_circ IS NULL THEN
856 RETURN NEXT tmp_circ;
857 SELECT INTO tmp_circ * FROM action.all_circulation_slim
858 WHERE parent_circ = tmp_circ.id;
862 $$ LANGUAGE 'plpgsql';
864 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
865 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
869 -- first circ in the chain
870 circ_0 action.all_circulation_slim%ROWTYPE;
872 -- last circ in the chain
873 circ_n action.all_circulation_slim%ROWTYPE;
875 -- circ chain under construction
876 chain action.circ_chain_summary;
877 tmp_circ action.all_circulation_slim%ROWTYPE;
881 chain.num_circs := 0;
882 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
884 IF chain.num_circs = 0 THEN
888 chain.num_circs := chain.num_circs + 1;
892 chain.start_time := circ_0.xact_start;
893 chain.last_stop_fines := circ_n.stop_fines;
894 chain.last_stop_fines_time := circ_n.stop_fines_time;
895 chain.last_checkin_time := circ_n.checkin_time;
896 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
897 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
898 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
900 IF chain.num_circs > 1 THEN
901 chain.last_renewal_time := circ_n.xact_start;
902 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
908 $$ LANGUAGE 'plpgsql';
910 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
911 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
913 badge rating.badge_with_orgs%ROWTYPE;
916 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
918 PERFORM rating.precalc_bibs_by_copy(badge_id);
920 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
921 SELECT id FROM precalc_filter_bib_list
923 SELECT id FROM precalc_bibs_by_copy_list
926 ANALYZE precalc_copy_filter_bib_list;
930 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
931 FROM (SELECT cn.record AS bib,
933 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
934 SUM( -- time copy spent circulating
938 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
942 )::NUMERIC AS circ_time
944 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
945 JOIN asset.call_number cn ON (cn.id = cp.call_number)
946 LEFT JOIN action.all_circulation_slim circ ON (
947 circ.target_copy = cp.id
948 AND stop_fines NOT IN (
955 checkin_time IS NULL AND
956 stop_fines = 'MAXFINES'
959 WHERE cn.owning_lib = ANY (badge.orgs)
960 AND cp.active_date IS NOT NULL
961 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
962 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
967 $f$ LANGUAGE PLPGSQL STRICT;
973 SELECT evergreen.upgrade_deps_block_check('1052', :eg_version);
975 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
976 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
978 badge rating.badge_with_orgs%ROWTYPE;
981 iscale NUMERIC := NULL;
984 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
986 IF badge.horizon_age IS NULL THEN
987 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
992 PERFORM rating.precalc_bibs_by_copy(badge_id);
994 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
995 SELECT id FROM precalc_filter_bib_list
997 SELECT id FROM precalc_bibs_by_copy_list
1000 ANALYZE precalc_copy_filter_bib_list;
1002 iint := EXTRACT(EPOCH FROM badge.importance_interval);
1003 IF badge.importance_age IS NOT NULL THEN
1004 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
1007 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
1008 iscale := COALESCE(badge.importance_scale, 1.0);
1012 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
1014 SELECT cn.record AS bib,
1015 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
1016 COUNT(u.id)::INT AS uses
1017 FROM action.in_house_use u
1018 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
1019 JOIN asset.copy cp ON (cp.id = u.item)
1020 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1021 WHERE u.use_time >= NOW() - badge.horizon_age
1022 AND cn.owning_lib = ANY (badge.orgs)
1027 $f$ LANGUAGE PLPGSQL STRICT;
1029 INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
1030 (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);
1034 SELECT evergreen.upgrade_deps_block_check('1053', :eg_version);
1036 CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
1037 RETURNS TABLE (record INT, value NUMERIC) AS $f$
1039 badge rating.badge_with_orgs%ROWTYPE;
1042 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1044 PERFORM rating.precalc_bibs_by_copy(badge_id);
1046 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1047 SELECT id FROM precalc_filter_bib_list
1049 SELECT id FROM precalc_bibs_by_copy_list
1051 ANALYZE precalc_copy_filter_bib_list;
1053 -- Use circ rather than owning lib here as that means "on the shelf at..."
1055 SELECT f.id::INT AS bib,
1056 COUNT(DISTINCT cp.circ_lib)::NUMERIC
1058 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
1059 WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;
1062 $f$ LANGUAGE PLPGSQL STRICT;
1064 INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES
1065 (17,'Circulation Library Count', 'rating.org_unit_count', TRUE);
1069 SELECT evergreen.upgrade_deps_block_check('1054', :eg_version);
1071 INSERT into config.org_unit_setting_type
1072 ( name, grp, label, description, datatype ) VALUES
1074 ( 'lib.timezone', 'lib',
1075 oils_i18n_gettext('lib.timezone',
1076 'Library time zone',
1078 oils_i18n_gettext('lib.timezone',
1079 'Define the time zone in which a library physically resides',
1080 'coust', 'description'),
1083 ALTER TABLE actor.org_unit_closed ADD COLUMN full_day BOOLEAN DEFAULT FALSE;
1084 ALTER TABLE actor.org_unit_closed ADD COLUMN multi_day BOOLEAN DEFAULT FALSE;
1086 UPDATE actor.org_unit_closed SET multi_day = TRUE
1087 WHERE close_start::DATE <> close_end::DATE;
1089 UPDATE actor.org_unit_closed SET full_day = TRUE
1090 WHERE close_start::DATE = close_end::DATE
1091 AND SUBSTRING(close_start::time::text FROM 1 FOR 8) = '00:00:00'
1092 AND SUBSTRING(close_end::time::text FROM 1 FOR 8) = '23:59:59';
1094 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
1096 proper_tz TEXT := COALESCE(
1099 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
1102 CURRENT_SETTING('timezone')
1106 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
1107 AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed
1108 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
1113 $$ LANGUAGE PLPGSQL;
1116 \qecho The following query will adjust all historical, unaged circulations so
1117 \qecho that if their due date field is pushed to the end of the day, it is done
1118 \qecho in the circulating library''''s time zone, and not the server time zone.
1120 \qecho It is safe to run this after any change to library time zones.
1122 \qecho Running this is not required, as no code before this change has
1123 \qecho depended on the time string of '''23:59:59'''. It is also not necessary
1124 \qecho if all of your libraries are in the same time zone, and that time zone
1125 \qecho is the same as the database''''s configured time zone.
1129 \qecho ' new_tz text;'
1130 \qecho ' ou_id int;'
1132 \qecho ' for ou_id in select id from actor.org_unit loop'
1133 \qecho ' for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('''lib.timezone''',ou_id) loop'
1134 \qecho ' if new_tz is not null then'
1135 \qecho ' update action.circulation'
1136 \qecho ' set due_date = (due_date::timestamp || ''' ''' || new_tz)::timestamptz'
1137 \qecho ' where circ_lib = ou_id'
1138 \qecho ' and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '''23:59:59''';'
1147 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1056', :eg_version); -- miker/gmcharlt
1149 INSERT INTO permission.perm_list (id,code,description) VALUES (592,'CONTAINER_BATCH_UPDATE','Allow batch update via buckets');
1151 INSERT INTO container.user_bucket_type (code,label) SELECT code,label FROM container.copy_bucket_type where code = 'staff_client';
1153 CREATE TABLE action.fieldset_group (
1154 id SERIAL PRIMARY KEY,
1156 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1157 complete_time TIMESTAMPTZ,
1158 container INT, -- Points to a container of some type ...
1159 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
1160 can_rollback BOOL DEFAULT TRUE,
1161 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1162 rollback_time TIMESTAMPTZ,
1163 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1164 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1167 ALTER TABLE action.fieldset ADD COLUMN fieldset_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1168 ALTER TABLE action.fieldset ADD COLUMN error_msg TEXT;
1169 ALTER TABLE container.biblio_record_entry_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1170 ALTER TABLE container.user_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1171 ALTER TABLE container.call_number_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1172 ALTER TABLE container.copy_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1174 UPDATE query.stored_query SET id = id + 1000 WHERE id < 1000;
1175 UPDATE query.from_relation SET id = id + 1000 WHERE id < 1000;
1176 UPDATE query.expression SET id = id + 1000 WHERE id < 1000;
1178 SELECT SETVAL('query.stored_query_id_seq', 1, FALSE);
1179 SELECT SETVAL('query.from_relation_id_seq', 1, FALSE);
1180 SELECT SETVAL('query.expression_id_seq', 1, FALSE);
1182 INSERT INTO query.bind_variable (name,type,description,label)
1183 SELECT 'bucket','number','ID of the bucket to pull items from','Bucket ID'
1184 WHERE NOT EXISTS (SELECT 1 FROM query.bind_variable WHERE name = 'bucket');
1186 -- Assumes completely empty 'query' schema
1187 INSERT INTO query.stored_query (type, use_distinct) VALUES ('SELECT', TRUE); -- 1
1189 INSERT INTO query.from_relation (type, table_name, class_name, table_alias) VALUES ('RELATION', 'container.user_bucket_item', 'cubi', 'cubi'); -- 1
1190 UPDATE query.stored_query SET from_clause = 1;
1192 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'target_user'); -- 1
1193 INSERT INTO query.select_item (stored_query,seq_no,expression) VALUES (1,1,1);
1195 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'bucket'); -- 2
1196 INSERT INTO query.expr_xbind (bind_variable) VALUES ('bucket'); -- 3
1198 INSERT INTO query.expr_xop (left_operand, operator, right_operand) VALUES (2, '=', 3); -- 4
1199 UPDATE query.stored_query SET where_clause = 4;
1201 SELECT SETVAL('query.stored_query_id_seq', 1000, TRUE) FROM query.stored_query;
1202 SELECT SETVAL('query.from_relation_id_seq', 1000, TRUE) FROM query.from_relation;
1203 SELECT SETVAL('query.expression_id_seq', 10000, TRUE) FROM query.expression;
1205 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1206 fieldset_id IN INT, -- id from action.fieldset
1207 table_name IN TEXT, -- table to be updated
1208 pkey_name IN TEXT, -- name of primary key column in that table
1209 query IN TEXT -- query constructed by qstore (for query-based
1210 -- fieldsets only; otherwise null
1226 fs_obj action.fieldset%ROWTYPE;
1227 fs_group action.fieldset_group%ROWTYPE;
1231 IF fieldset_id IS NULL THEN
1232 RETURN 'Fieldset ID parameter is NULL';
1234 IF table_name IS NULL THEN
1235 RETURN 'Table name parameter is NULL';
1237 IF pkey_name IS NULL THEN
1238 RETURN 'Primary key name parameter is NULL';
1243 quote_literal( pkey_value )
1253 -- Build the WHERE clause. This differs according to whether it's a
1254 -- single-row fieldset or a query-based fieldset.
1256 IF query IS NULL AND fs_pkey_value IS NULL THEN
1257 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1258 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1259 fs_query := rtrim( query, ';' );
1260 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1261 || fs_query || ' )';
1262 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1263 where_clause := 'WHERE ' || pkey_name || ' = ';
1264 IF pkey_name = 'id' THEN
1265 where_clause := where_clause || fs_pkey_value;
1266 ELSIF pkey_name = 'code' THEN
1267 where_clause := where_clause || quote_literal(fs_pkey_value);
1269 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1271 ELSE -- both are not null
1272 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1275 IF fs_status IS NULL THEN
1276 RETURN 'No fieldset found for id = ' || fieldset_id;
1277 ELSIF fs_status = 'APPLIED' THEN
1278 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1281 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1282 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1284 IF fs_group.can_rollback THEN
1285 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1287 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1288 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1290 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1292 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1293 IF pkey_name = 'id' THEN
1294 fs_pkey_value := rb_row.id;
1295 ELSIF pkey_name = 'code' THEN
1296 fs_pkey_value := rb_row.code;
1298 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1300 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1301 VALUES (fsg_id, fs_obj.owner, fs_obj.owning_lib, 'PENDING', fs_obj.classname, fs_obj.name || ' ROLLBACK FOR ' || fs_pkey_value, fs_pkey_value);
1303 fs_id := CURRVAL('action.fieldset_id_seq');
1307 FROM action.fieldset_col_val
1308 WHERE fieldset = fieldset_id
1310 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1311 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1316 statement := 'UPDATE ' || table_name || ' SET';
1322 FROM action.fieldset_col_val
1323 WHERE fieldset = fieldset_id
1325 statement := statement || sep || ' ' || cv.col
1326 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1331 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1333 statement := statement || ' ' || where_clause;
1336 -- Execute the update
1340 GET DIAGNOSTICS update_count = ROW_COUNT;
1342 IF update_count = 0 THEN
1343 RAISE data_exception;
1346 IF fsg_id IS NOT NULL THEN
1347 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1350 IF fs_group.id IS NOT NULL THEN
1351 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1354 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1356 EXCEPTION WHEN data_exception THEN
1357 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1358 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1365 EXCEPTION WHEN OTHERS THEN
1366 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1367 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1371 $$ LANGUAGE plpgsql;
1375 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1057', :eg_version); -- miker/gmcharlt/kmlussier
1377 -- Thist change drops a needless join and saves 10-15% in time cost
1378 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
1380 SELECT id, value, count
1382 SELECT mfae.field AS id,
1384 COUNT(DISTINCT mfae.source),
1386 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
1388 FROM metabib.facet_entry mfae
1389 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
1390 WHERE mfae.source = ANY ($2)
1392 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
1397 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
1403 CREATE OR REPLACE FUNCTION unapi.metabib_virtual_record_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE 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$
1405 layout unapi.bre_output_layout%ROWTYPE;
1406 transform config.xml_transform%ROWTYPE;
1409 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
1411 element_list TEXT[];
1414 IF org = '-' OR org IS NULL THEN
1415 SELECT shortname INTO org FROM evergreen.org_top();
1418 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1419 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1421 IF layout.name IS NULL THEN
1425 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
1426 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
1428 -- Gather the bib xml
1429 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
1431 IF layout.title_element IS NOT NULL THEN
1432 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
1435 IF layout.description_element IS NOT NULL THEN
1436 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
1439 IF layout.creator_element IS NOT NULL THEN
1440 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
1443 IF layout.update_ts_element IS NOT NULL THEN
1444 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts;
1447 IF unapi_url IS NOT NULL THEN
1448 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;
1451 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
1453 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
1454 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
1455 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
1458 RETURN tmp_xml::XML;
1460 $F$ LANGUAGE PLPGSQL STABLE;
1462 CREATE TABLE asset.copy_vis_attr_cache (
1463 id BIGSERIAL PRIMARY KEY,
1464 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
1465 target_copy BIGINT NOT NULL,
1466 vis_attr_vector INT[]
1468 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
1469 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
1471 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
1473 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
1476 WHEN 'luri_org' THEN 0 -- "b" attr
1477 WHEN 'bib_source' THEN 1 -- "b" attr
1479 WHEN 'copy_flags' THEN 0 -- "c" attr
1480 WHEN 'owning_lib' THEN 1 -- "c" attr
1481 WHEN 'circ_lib' THEN 2 -- "c" attr
1482 WHEN 'status' THEN 3 -- "c" attr
1483 WHEN 'location' THEN 4 -- "c" attr
1484 WHEN 'location_group' THEN 5 -- "c" attr
1488 /* copy_flags bit positions, LSB-first:
1490 0: asset.copy.opac_visible
1493 When adding flags, you must update asset.all_visible_flags()
1495 Because bib and copy values are stored separately, we can reuse
1496 shifts, saving us some space. We could probably take back a bit
1497 too, but I'm not sure its worth squeezing that last one out. We'd
1498 be left with just 2 slots for copy attrs, rather than 10.
1501 $f$ LANGUAGE SQL IMMUTABLE;
1503 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
1504 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
1505 $f$ LANGUAGE SQL IMMUTABLE;
1507 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
1508 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
1509 $f$ LANGUAGE SQL IMMUTABLE;
1511 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
1513 copy_row asset.copy%ROWTYPE;
1514 lgroup_map asset.copy_location_group_map%ROWTYPE;
1517 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
1519 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
1520 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
1521 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
1522 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
1524 SELECT ARRAY_APPEND(
1526 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
1528 FROM asset.call_number
1529 WHERE id = copy_row.call_number;
1531 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
1532 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
1537 $f$ LANGUAGE PLPGSQL;
1539 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
1541 bib_row biblio.record_entry%ROWTYPE;
1542 cn_row asset.call_number%ROWTYPE;
1545 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
1547 IF bib_row.source IS NOT NULL THEN
1548 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
1553 FROM asset.call_number cn
1554 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
1555 JOIN asset.uri u ON (u.id = m.uri)
1556 WHERE cn.record = bib_id
1557 AND cn.label = '##URI##'
1560 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
1565 $f$ LANGUAGE PLPGSQL;
1568 SELECT evergreen.upgrade_deps_block_check('1076', :eg_version); -- miker/gmcharlt
1570 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
1572 ocn asset.call_number%ROWTYPE;
1573 ncn asset.call_number%ROWTYPE;
1577 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
1578 IF TG_OP = 'INSERT' THEN
1579 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1582 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
1586 ELSIF TG_OP = 'DELETE' THEN
1587 DELETE FROM asset.copy_vis_attr_cache
1588 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
1594 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
1595 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
1596 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1597 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1600 asset.calculate_copy_visibility_attribute_set(NEW.id)
1602 ELSIF TG_TABLE_NAME = 'record_entry' THEN
1603 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1609 -- handle items first, since with circulation activity
1610 -- their statuses change frequently
1611 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
1613 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1614 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1618 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1620 IF OLD.deleted <> NEW.deleted THEN
1622 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1624 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1627 asset.calculate_copy_visibility_attribute_set(NEW.id)
1632 ELSIF OLD.call_number <> NEW.call_number THEN
1633 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
1635 IF ncn.record <> ocn.record THEN
1636 UPDATE biblio.record_entry
1637 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
1638 WHERE id = ocn.record;
1640 -- We have to use a record-specific WHERE clause
1641 -- to avoid modifying the entries for peer-bib copies.
1642 UPDATE asset.copy_vis_attr_cache
1643 SET target_copy = NEW.id,
1645 WHERE target_copy = OLD.id
1646 AND record = ocn.record;
1650 IF OLD.location <> NEW.location OR
1651 OLD.status <> NEW.status OR
1652 OLD.opac_visible <> NEW.opac_visible OR
1653 OLD.circ_lib <> NEW.circ_lib
1655 -- Any of these could change visibility, but
1656 -- we'll save some queries and not try to calculate
1657 -- the change directly. We want to update peer-bib
1658 -- entries in this case, unlike above.
1659 UPDATE asset.copy_vis_attr_cache
1660 SET target_copy = NEW.id,
1661 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
1662 WHERE target_copy = OLD.id;
1666 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
1668 IF OLD.record <> NEW.record THEN
1669 IF NEW.label = '##URI##' THEN
1670 UPDATE biblio.record_entry
1671 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1672 WHERE id = OLD.record;
1674 UPDATE biblio.record_entry
1675 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
1676 WHERE id = NEW.record;
1679 UPDATE asset.copy_vis_attr_cache
1680 SET record = NEW.record,
1681 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1682 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1683 AND record = OLD.record;
1685 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
1686 UPDATE asset.copy_vis_attr_cache
1687 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1688 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1689 AND record = NEW.record;
1691 IF NEW.label = '##URI##' THEN
1692 UPDATE biblio.record_entry
1693 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1694 WHERE id = OLD.record;
1698 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
1700 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1701 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
1703 ELSIF OLD.source <> NEW.source THEN
1704 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1711 $func$ LANGUAGE PLPGSQL;
1714 -- Helper functions for use in constructing searches --
1716 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
1717 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
1718 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
1719 $f$ LANGUAGE SQL STABLE;
1721 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
1722 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
1725 $f$ LANGUAGE SQL STABLE;
1727 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
1728 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
1730 WHERE NOT opac_visible;
1731 $f$ LANGUAGE SQL STABLE;
1733 -- Bib-oriented defaults for search
1734 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
1735 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
1736 FROM config.bib_source
1738 $f$ LANGUAGE SQL IMMUTABLE;
1740 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
1741 SELECT * FROM asset.invisible_orgs('luri_org');
1742 $f$ LANGUAGE SQL STABLE;
1744 -- Copy-oriented defaults for search
1745 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
1746 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
1747 FROM asset.copy_location_group
1748 WHERE NOT opac_visible;
1749 $f$ LANGUAGE SQL STABLE;
1751 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
1752 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
1753 FROM asset.copy_location
1754 WHERE NOT opac_visible;
1755 $f$ LANGUAGE SQL STABLE;
1757 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
1758 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
1759 FROM config.copy_status
1760 WHERE NOT opac_visible;
1761 $f$ LANGUAGE SQL STABLE;
1763 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
1764 SELECT * FROM asset.invisible_orgs('owning_lib');
1765 $f$ LANGUAGE SQL STABLE;
1767 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
1768 SELECT * FROM asset.invisible_orgs('circ_lib');
1769 $f$ LANGUAGE SQL STABLE;
1771 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
1773 copy_flags TEXT; -- "c" attr
1775 owning_lib TEXT; -- "c" attr
1776 circ_lib TEXT; -- "c" attr
1777 status TEXT; -- "c" attr
1778 location TEXT; -- "c" attr
1779 location_group TEXT; -- "c" attr
1781 luri_org TEXT; -- "b" attr
1782 bib_sources TEXT; -- "b" attr
1784 copy_flags := asset.all_visible_flags(); -- Will always have at least one
1786 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
1788 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
1789 status := NULLIF(asset.status_default(),'!()');
1790 location := NULLIF(asset.location_default(),'!()');
1791 location_group := NULLIF(asset.location_group_default(),'!()');
1793 luri_org := NULLIF(asset.luri_org_default(),'!()');
1794 bib_sources := NULLIF(asset.bib_source_default(),'()');
1797 '('||ARRAY_TO_STRING(
1798 ARRAY[luri_org,bib_sources],
1801 '('||ARRAY_TO_STRING(
1802 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
1806 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
1808 CREATE OR REPLACE FUNCTION metabib.suggest_browse_entries(raw_query_text text, search_class text, headline_opts text, visibility_org integer, query_limit integer, normalization integer)
1809 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
1812 prepared_query_texts TEXT[];
1814 plain_query TSQUERY;
1815 opac_visibility_join TEXT;
1816 search_class_join TEXT;
1819 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
1821 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
1822 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
1824 visibility_org := NULLIF(visibility_org,-1);
1825 IF visibility_org IS NOT NULL THEN
1826 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
1828 opac_visibility_join := '';
1830 opac_visibility_join := '
1831 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
1832 JOIN vm ON (acvac.vis_attr_vector @@
1833 (vm.c_attrs || $$&$$ ||
1834 search.calculate_visibility_attribute_test(
1836 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
1843 opac_visibility_join := '';
1846 -- The following determines whether we only provide suggestsons matching
1847 -- the user's selected search_class, or whether we show other suggestions
1848 -- too. The reason for MIN() is that for search_classes like
1849 -- 'title|proper|uniform' you would otherwise get multiple rows. The
1850 -- implication is that if title as a class doesn't have restrict,
1851 -- nor does the proper field, but the uniform field does, you're going
1852 -- to get 'false' for your overall evaluation of 'should we restrict?'
1853 -- To invert that, change from MIN() to MAX().
1857 MIN(cmc.restrict::INT) AS restrict_class,
1858 MIN(cmf.restrict::INT) AS restrict_field
1859 FROM metabib.search_class_to_registered_components(search_class)
1860 AS _registered (field_class TEXT, field INT)
1862 config.metabib_class cmc ON (cmc.name = _registered.field_class)
1864 config.metabib_field cmf ON (cmf.id = _registered.field);
1866 -- evaluate 'should we restrict?'
1867 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
1868 search_class_join := '
1870 metabib.search_class_to_registered_components($2)
1871 AS _registered (field_class TEXT, field INT) ON (
1872 (_registered.field IS NULL AND
1873 _registered.field_class = cmf.field_class) OR
1874 (_registered.field = cmf.id)
1878 search_class_join := '
1880 metabib.search_class_to_registered_components($2)
1881 AS _registered (field_class TEXT, field INT) ON (
1882 _registered.field_class = cmc.name
1887 RETURN QUERY EXECUTE '
1888 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
1889 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
1898 TS_HEADLINE(value, $7, $3)
1899 FROM (SELECT DISTINCT
1902 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
1903 _registered.field = cmf.id AS restrict,
1905 TS_RANK_CD(mbe.index_vector, $1, $6),
1908 FROM metabib.browse_entry_def_map mbedm
1909 JOIN mbe ON (mbe.id = mbedm.entry)
1910 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
1911 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
1912 ' || search_class_join || '
1913 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1915 ' || opac_visibility_join || '
1916 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1918 ' -- sic, repeat the order by clause in the outer select too
1920 query, search_class, headline_opts,
1921 visibility_org, query_limit, normalization, plain_query
1925 -- buoyant AND chosen class = match class
1926 -- chosen field = match field
1933 $f$ LANGUAGE plpgsql ROWS 10;
1935 CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10)
1936 RETURNS SETOF metabib.flat_browse_entry_appearance
1942 pivot_sort_value TEXT;
1943 pivot_sort_fallback TEXT;
1944 context_locations INT[];
1945 browse_superpage_size INT;
1946 results_skipped INT := 0;
1950 forward_to_pivot INT;
1952 -- First, find the pivot if we were given a browse term but not a pivot.
1953 IF pivot_id IS NULL THEN
1954 pivot_id := metabib.browse_pivot(search_field, browse_term);
1957 SELECT INTO pivot_sort_value, pivot_sort_fallback
1958 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1960 -- Bail if we couldn't find a pivot.
1961 IF pivot_sort_value IS NULL THEN
1965 -- Transform the context_loc_group argument (if any) (logc at the
1966 -- TPAC layer) into a form we'll be able to use.
1967 IF context_loc_group IS NOT NULL THEN
1968 SELECT INTO context_locations ARRAY_AGG(location)
1969 FROM asset.copy_location_group_map
1970 WHERE lgroup = context_loc_group;
1973 -- Get the configured size of browse superpages.
1974 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1975 FROM config.global_flag
1976 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1978 -- First we're going to search backward from the pivot, then we're going
1979 -- to search forward. In each direction, we need two limits. At the
1980 -- lesser of the two limits, we delineate the edge of the result set
1981 -- we're going to return. At the greater of the two limits, we find the
1982 -- pivot value that would represent an offset from the current pivot
1983 -- at a distance of one "page" in either direction, where a "page" is a
1984 -- result set of the size specified in the "result_limit" argument.
1986 -- The two limits in each direction make four derived values in total,
1987 -- and we calculate them now.
1988 back_limit := CEIL(result_limit::FLOAT / 2);
1989 back_to_pivot := result_limit;
1990 forward_limit := result_limit / 2;
1991 forward_to_pivot := result_limit - 1;
1993 -- This is the meat of the SQL query that finds browse entries. We'll
1994 -- pass this to a function which uses it with a cursor, so that individual
1995 -- rows may be fetched in a loop until some condition is satisfied, without
1996 -- waiting for a result set of fixed size to be collected all at once.
2001 FROM metabib.browse_entry mbe
2003 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2005 FROM metabib.browse_entry_def_map mbedm
2006 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2007 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2009 FROM metabib.browse_entry_simple_heading_map mbeshm
2010 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2011 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2012 ash.atag = map.authority_field
2013 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2015 WHERE mbeshm.entry = mbe.id
2019 -- This is the variant of the query for browsing backward.
2020 back_query := core_query ||
2021 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2022 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
2024 -- This variant browses forward.
2025 forward_query := core_query ||
2026 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2027 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
2029 -- We now call the function which applies a cursor to the provided
2030 -- queries, stopping at the appropriate limits and also giving us
2031 -- the next page's pivot.
2033 SELECT * FROM metabib.staged_browse(
2034 back_query, search_field, context_org, context_locations,
2035 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2037 SELECT * FROM metabib.staged_browse(
2038 forward_query, search_field, context_org, context_locations,
2039 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2040 ) ORDER BY row_number DESC;
2043 $f$ LANGUAGE plpgsql ROWS 10;
2045 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
2046 RETURNS SETOF metabib.flat_browse_entry_appearance
2055 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2056 results_skipped INT := 0;
2057 row_counter INT := 0;
2062 all_records BIGINT[];
2063 all_brecords BIGINT[];
2064 all_arecords BIGINT[];
2065 superpage_of_records BIGINT[];
2071 IF count_up_from_zero THEN
2078 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
2081 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2082 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
2084 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
2086 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
2087 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
2089 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
2091 b_tests := b_tests || search.calculate_visibility_attribute_test(
2093 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
2096 b_tests := b_tests || search.calculate_visibility_attribute_test(
2098 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
2102 IF context_locations THEN
2103 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2104 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
2107 OPEN curs NO SCROLL FOR EXECUTE query;
2110 FETCH curs INTO rec;
2112 IF result_row.pivot_point IS NOT NULL THEN
2113 RETURN NEXT result_row;
2118 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2119 SELECT INTO all_arecords, result_row.sees, afields
2120 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2121 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2122 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2124 FROM metabib.browse_entry_simple_heading_map mbeshm
2125 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2126 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2127 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2128 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2129 ash.atag = map.authority_field
2130 AND map.metabib_field = ANY(fields)
2132 WHERE mbeshm.entry = rec.id;
2134 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2135 SELECT INTO all_brecords, result_row.authorities, bfields
2136 ARRAY_AGG(DISTINCT source),
2137 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2138 ARRAY_AGG(DISTINCT def)
2139 FROM metabib.browse_entry_def_map
2140 WHERE entry = rec.id
2141 AND def = ANY(fields);
2143 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2145 result_row.sources := 0;
2146 result_row.asources := 0;
2148 -- Bib-linked vis checking
2149 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2151 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
2152 FROM biblio.record_entry b
2153 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2154 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
2156 acvac.vis_attr_vector @@ c_tests::query_int
2157 OR b.vis_attr_vector @@ b_tests::query_int
2160 result_row.accurate := TRUE;
2164 -- Authority-linked vis checking
2165 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2167 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
2168 FROM biblio.record_entry b
2169 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2170 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
2172 acvac.vis_attr_vector @@ c_tests::query_int
2173 OR b.vis_attr_vector @@ b_tests::query_int
2176 result_row.aaccurate := TRUE;
2180 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2182 -- The function that calls this function needs row_number in order
2183 -- to correctly order results from two different runs of this
2185 result_row.row_number := row_number;
2187 -- Now, if row_counter is still less than limit, return a row. If
2188 -- not, but it is less than next_pivot_pos, continue on without
2189 -- returning actual result rows until we find
2190 -- that next pivot, and return it.
2192 IF row_counter < result_limit THEN
2193 result_row.browse_entry := rec.id;
2194 result_row.value := rec.value;
2196 RETURN NEXT result_row;
2198 result_row.browse_entry := NULL;
2199 result_row.authorities := NULL;
2200 result_row.fields := NULL;
2201 result_row.value := NULL;
2202 result_row.sources := NULL;
2203 result_row.sees := NULL;
2204 result_row.accurate := NULL;
2205 result_row.aaccurate := NULL;
2206 result_row.pivot_point := rec.id;
2208 IF row_counter >= next_pivot_pos THEN
2209 RETURN NEXT result_row;
2214 IF count_up_from_zero THEN
2215 row_number := row_number + 1;
2217 row_number := row_number - 1;
2220 -- row_counter is different from row_number.
2221 -- It simply counts up from zero so that we know when
2222 -- we've reached our limit.
2223 row_counter := row_counter + 1;
2227 $f$ LANGUAGE plpgsql ROWS 10;
2229 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
2230 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
2231 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
2232 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
2233 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
2234 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
2235 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
2236 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
2238 -- Upgrade the data!
2239 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
2242 asset.calculate_copy_visibility_attribute_set(cp.id)
2244 JOIN asset.call_number cn ON (cp.call_number = cn.id);
2246 -- updating vis cache for biblio.record_entry deferred to end
2248 CREATE TRIGGER z_opac_vis_mat_view_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2249 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR DELETE ON biblio.peer_bib_copy_map FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2250 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2251 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2252 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2253 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2254 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2256 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$
2261 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;
2263 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
2265 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2266 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2267 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2271 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2277 asset.copy_vis_attr_cache av
2278 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2279 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2283 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2290 $f$ LANGUAGE PLPGSQL;
2292 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$
2297 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;
2299 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2301 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2302 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2303 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2307 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2312 asset.copy_vis_attr_cache av
2313 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2314 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2318 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2325 $f$ LANGUAGE PLPGSQL;
2327 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$
2332 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
2334 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
2336 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2337 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2338 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2342 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2348 asset.copy_vis_attr_cache av
2349 JOIN asset.copy cp ON (cp.id = av.target_copy)
2350 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2351 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2355 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2362 $f$ LANGUAGE PLPGSQL;
2364 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$
2369 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
2371 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2373 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2374 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2375 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2379 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2385 asset.copy_vis_attr_cache av
2386 JOIN asset.copy cp ON (cp.id = av.target_copy)
2387 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2388 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2392 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2399 $f$ LANGUAGE PLPGSQL;
2401 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
2407 depth INT DEFAULT NULL,
2408 slimit HSTORE DEFAULT NULL,
2409 soffset HSTORE DEFAULT NULL,
2410 include_xmlns BOOL DEFAULT TRUE,
2411 pref_lib INT DEFAULT NULL
2412 ) RETURNS XML AS $F$
2416 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
2417 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
2419 (SELECT XMLAGG(foo.y)
2421 WITH sourcelist AS (
2422 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
2423 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2424 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
2425 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
2427 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
2428 WHERE mmsm.metarecord = $1 AND (
2431 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
2432 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2433 AND acvac.record = mmsm.source
2435 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
2436 OR EXISTS (SELECT 1 FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = mmsm.source)
2444 cmra.value AS "coded-value",
2445 cmra.aid AS "cvmid",
2455 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
2457 SELECT v.source AS id,
2461 FROM metabib.record_attr_vector_list v
2462 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
2464 JOIN sourcelist ON (x.id = sourcelist.source)
2467 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
2482 SELECT DISTINCT aid, attr, value
2484 SELECT v.source AS id,
2488 FROM metabib.record_attr_vector_list v
2489 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
2491 JOIN sourcelist ON (x.id = sourcelist.source)
2493 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
2499 $F$ LANGUAGE SQL STABLE;
2501 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
2504 depth INT DEFAULT NULL,
2505 slimit HSTORE DEFAULT NULL,
2506 soffset HSTORE DEFAULT NULL,
2507 pref_lib INT DEFAULT NULL,
2508 includes TEXT[] DEFAULT NULL::TEXT[]
2509 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
2510 WITH RECURSIVE ou_depth AS (
2515 FROM actor.org_unit_type aout
2516 INNER JOIN actor.org_unit ou ON ou_type = aout.id
2520 ), descendant_depth AS (
2524 FROM actor.org_unit ou
2525 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2526 JOIN anscestor_depth ad ON (ad.id = ou.id),
2528 WHERE ad.depth = ou_depth.depth
2533 FROM actor.org_unit ou
2534 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2535 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2536 ), anscestor_depth AS (
2540 FROM actor.org_unit ou
2541 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2547 FROM actor.org_unit ou
2548 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2549 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
2551 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
2554 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
2555 SELECT acn.id, owning_lib.name, acn.label_sortkey,
2556 evergreen.rank_cp(acp),
2558 FROM asset.call_number acn
2559 JOIN asset.copy acp ON (acn.id = acp.call_number)
2560 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
2561 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
2562 WHERE acn.record = ANY ($1)
2563 AND acn.deleted IS FALSE
2564 AND acp.deleted IS FALSE
2565 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
2567 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2568 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
2570 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
2571 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2572 AND acvac.target_copy = acp.id
2573 AND acvac.record = acn.record
2575 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
2579 CASE WHEN aou.id = $2 THEN -20000 END,
2580 CASE WHEN aou.id = $6 THEN -10000 END,
2581 (SELECT distance - 5000
2582 FROM actor.org_unit_descendants_distance($6) as x
2583 WHERE x.id = aou.id AND $6 IN (
2584 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
2585 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
2588 evergreen.rank_cp(acp)
2591 GROUP BY ua.id, ua.name, ua.label_sortkey
2592 ORDER BY rank, ua.name, ua.label_sortkey
2593 LIMIT ($4 -> 'acn')::INT
2594 OFFSET ($5 -> 'acn')::INT;
2595 $$ LANGUAGE SQL STABLE ROWS 10;
2598 -- Evergreen DB patch XXXX.schema.action-trigger.event_definition.sms_preminder.sql
2600 -- New action trigger event definition: 3 Day Courtesy Notice by SMS
2603 -- check whether patch can be applied
2604 SELECT evergreen.upgrade_deps_block_check('1058', :eg_version); -- mccanna/csharp/gmcharlt
2606 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook,
2607 validator, reactor, delay, max_delay, delay_field, group_field, template)
2608 VALUES (54, FALSE, 1,
2609 '3 Day Courtesy Notice by SMS',
2611 'CircIsOpen', 'SendSMS', '-3 days', '-2 days', 'due_date', 'usr',
2614 [%- user = target.0.usr -%]
2615 [%- homelib = user.home_ou -%]
2616 [%- sms_number = helpers.get_user_setting(user.id, 'opac.default_sms_notify') -%]
2617 [%- sms_carrier = helpers.get_user_setting(user.id, 'opac.default_sms_carrier') -%]
2618 From: [%- helpers.get_org_setting(homelib.id, 'org.bounced_emails') || homelib.email || params.sender_email || default_sender %]
2619 To: [%- helpers.get_sms_gateway_email(sms_carrier,sms_number) %]
2620 Subject: Library Materials Due Soon
2622 You have items due soon:
2624 [% FOR circ IN target %]
2625 [%- copy_details = helpers.get_copy_bib_basics(circ.target_copy.id) -%]
2626 [% copy_details.title FILTER ucfirst %] by [% copy_details.author FILTER ucfirst %] due on [% date.format(helpers.format_date(circ.due_date), '%m-%d-%Y') %]
2632 INSERT INTO action_trigger.environment (event_def, path) VALUES
2633 (54, 'circ_lib.billing_address'),
2634 (54, 'target_copy.call_number'),
2636 (54, 'usr.home_ou');
2639 -- check whether patch can be applied
2640 SELECT evergreen.upgrade_deps_block_check('1059', :eg_version); --Stompro/DPearl/kmlussier
2642 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
2648 CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title,
2649 FIRST(author.value) AS author,
2650 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
2651 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
2652 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
2654 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
2656 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
2658 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
2660 FROM biblio.record_entry r
2661 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
2662 LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear.
2663 SELECT b.record, string_agg(val, ' ') AS val FROM (
2664 SELECT title_np.record, title_np.value AS val
2665 FROM metabib.full_rec title_np
2667 title_np.tag = '245'
2668 AND title_np.subfield IN ('p','n')
2669 ORDER BY title_np.id
2672 ) title_np ON (title_np.record=r.id)
2673 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
2674 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND (publisher.tag = '260' OR (publisher.tag = '264' AND publisher.ind2 = '1')) AND publisher.subfield = 'b')
2675 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND (pubdate.tag = '260' OR (pubdate.tag = '264' AND pubdate.ind2 = '1')) AND pubdate.subfield = 'c')
2676 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
2677 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
2681 -- Remove trigger on biblio.record_entry
2682 SELECT reporter.disable_materialized_simple_record_trigger();
2684 -- Rebuild reporter.materialized_simple_record
2685 SELECT reporter.enable_materialized_simple_record_trigger();
2688 SELECT evergreen.upgrade_deps_block_check('1060', :eg_version);
2690 DROP VIEW IF EXISTS extend_reporter.copy_count_per_org;
2693 CREATE OR REPLACE VIEW extend_reporter.copy_count_per_org AS
2694 SELECT acn.record AS bibid,
2697 max(ac.edit_date) AS last_edit_time,
2698 min(ac.deleted::integer) AS has_only_deleted_copies,
2701 WHEN ac.deleted THEN ac.id
2703 END) AS deleted_count,
2706 WHEN NOT ac.deleted THEN ac.id
2708 END) AS visible_count,
2709 count(*) AS total_count
2710 FROM asset.call_number acn,
2712 WHERE ac.call_number = acn.id
2713 GROUP BY acn.record, acn.owning_lib, ac.circ_lib;
2717 SELECT evergreen.upgrade_deps_block_check('1061', :eg_version);
2719 INSERT INTO config.org_unit_setting_type
2720 (name, label, description, grp, datatype)
2722 'ui.staff.max_recent_patrons',
2724 'ui.staff.max_recent_patrons',
2725 'Number of Retrievable Recent Patrons',
2730 'ui.staff.max_recent_patrons',
2731 'Number of most recently accessed patrons that can be re-retrieved ' ||
2732 'in the staff client. A value of 0 or less disables the feature. Defaults to 1.',
2741 SELECT evergreen.upgrade_deps_block_check('1062', :eg_version);
2743 CREATE TABLE acq.edi_attr (
2744 key TEXT PRIMARY KEY,
2745 label TEXT NOT NULL UNIQUE
2748 CREATE TABLE acq.edi_attr_set (
2749 id SERIAL PRIMARY KEY,
2750 label TEXT NOT NULL UNIQUE
2753 CREATE TABLE acq.edi_attr_set_map (
2754 id SERIAL PRIMARY KEY,
2755 attr_set INTEGER NOT NULL REFERENCES acq.edi_attr_set(id)
2756 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2757 attr TEXT NOT NULL REFERENCES acq.edi_attr(key)
2758 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2759 CONSTRAINT edi_attr_set_map_attr_once UNIQUE (attr_set, attr)
2762 -- An attr_set is not strictly required, since some edi_accounts/vendors
2763 -- may not need to apply any attributes.
2764 ALTER TABLE acq.edi_account
2765 ADD COLUMN attr_set INTEGER REFERENCES acq.edi_attr_set(id),
2766 ADD COLUMN use_attrs BOOLEAN NOT NULL DEFAULT FALSE;
2771 SELECT evergreen.upgrade_deps_block_check('1063', :eg_version);
2778 FOR r IN SELECT t.table_schema AS sname,
2779 t.table_name AS tname,
2780 t.column_name AS colname,
2782 FROM information_schema.referential_constraints ref
2783 JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
2784 WHERE ref.unique_constraint_schema = 'asset'
2785 AND ref.unique_constraint_name = 'copy_pkey'
2788 EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
2791 CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
2793 PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
2795 RAISE foreign_key_violation USING MESSAGE = FORMAT(
2796 $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
2801 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
2805 CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
2806 AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
2807 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
2815 SELECT evergreen.upgrade_deps_block_check('1064', :eg_version);
2817 ALTER TABLE serial.issuance DROP CONSTRAINT IF EXISTS issuance_caption_and_pattern_fkey;
2819 -- Using NOT VALID and VALIDATE CONSTRAINT limits the impact to concurrent work.
2820 -- For details, see: https://www.postgresql.org/docs/current/static/sql-altertable.html
2822 ALTER TABLE serial.issuance ADD CONSTRAINT issuance_caption_and_pattern_fkey
2823 FOREIGN KEY (caption_and_pattern)
2824 REFERENCES serial.caption_and_pattern (id)
2826 DEFERRABLE INITIALLY DEFERRED
2829 ALTER TABLE serial.issuance VALIDATE CONSTRAINT issuance_caption_and_pattern_fkey;
2833 SELECT evergreen.upgrade_deps_block_check('1065', :eg_version);
2835 CREATE TABLE serial.pattern_template (
2836 id SERIAL PRIMARY KEY,
2838 pattern_code TEXT NOT NULL,
2839 owning_lib INTEGER REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
2840 share_depth INTEGER NOT NULL DEFAULT 0
2842 CREATE INDEX serial_pattern_template_name_idx ON serial.pattern_template (evergreen.lowercase(name));
2844 CREATE OR REPLACE FUNCTION serial.pattern_templates_visible_to(org_unit INT) RETURNS SETOF serial.pattern_template AS $func$
2846 RETURN QUERY SELECT *
2847 FROM serial.pattern_template spt
2849 SELECT ARRAY_AGG(id)
2850 FROM actor.org_unit_descendants(spt.owning_lib, spt.share_depth)
2851 ) @@ org_unit::TEXT::QUERY_INT;
2853 $func$ LANGUAGE PLPGSQL;
2856 SELECT evergreen.upgrade_deps_block_check('1066', :eg_version);
2858 INSERT INTO permission.perm_list ( id, code, description ) VALUES
2859 ( 593, 'ADMIN_SERIAL_PATTERN_TEMPLATE', oils_i18n_gettext( 593,
2860 'Administer serial prediction pattern templates', 'ppl', 'description' ))
2863 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
2865 pgt.id, perm.id, aout.depth, FALSE
2867 permission.grp_tree pgt,
2868 permission.perm_list perm,
2869 actor.org_unit_type aout
2871 pgt.name = 'Serials' AND
2872 aout.name = 'System' AND
2874 'ADMIN_SERIAL_PATTERN_TEMPLATE'
2878 SELECT evergreen.upgrade_deps_block_check('1067', :eg_version);
2880 INSERT INTO acq.edi_attr (key, label) VALUES
2882 oils_i18n_gettext('INCLUDE_PO_NAME',
2883 'Orders Include PO Name', 'aea', 'label')),
2885 oils_i18n_gettext('INCLUDE_COPIES',
2886 'Orders Include Copy Data', 'aea', 'label')),
2888 oils_i18n_gettext('INCLUDE_FUND',
2889 'Orders Include Copy Funds', 'aea', 'label')),
2890 ('INCLUDE_CALL_NUMBER',
2891 oils_i18n_gettext('INCLUDE_CALL_NUMBER',
2892 'Orders Include Copy Call Numbers', 'aea', 'label')),
2893 ('INCLUDE_ITEM_TYPE',
2894 oils_i18n_gettext('INCLUDE_ITEM_TYPE',
2895 'Orders Include Copy Item Types', 'aea', 'label')),
2896 ('INCLUDE_ITEM_BARCODE',
2897 oils_i18n_gettext('INCLUDE_ITEM_BARCODE',
2898 'Orders Include Copy Barcodes', 'aea', 'label')),
2899 ('INCLUDE_LOCATION',
2900 oils_i18n_gettext('INCLUDE_LOCATION',
2901 'Orders Include Copy Locations', 'aea', 'label')),
2902 ('INCLUDE_COLLECTION_CODE',
2903 oils_i18n_gettext('INCLUDE_COLLECTION_CODE',
2904 'Orders Include Copy Collection Codes', 'aea', 'label')),
2905 ('INCLUDE_OWNING_LIB',
2906 oils_i18n_gettext('INCLUDE_OWNING_LIB',
2907 'Orders Include Copy Owning Library', 'aea', 'label')),
2908 ('USE_ID_FOR_OWNING_LIB',
2909 oils_i18n_gettext('USE_ID_FOR_OWNING_LIB',
2910 'Emit Owning Library ID Rather Than Short Name. Takes effect only if INCLUDE_OWNING_LIB is in use', 'aea', 'label')),
2911 ('INCLUDE_QUANTITY',
2912 oils_i18n_gettext('INCLUDE_QUANTITY',
2913 'Orders Include Copy Quantities', 'aea', 'label')),
2915 oils_i18n_gettext('INCLUDE_COPY_ID',
2916 'Orders Include Copy IDs', 'aea', 'label')),
2917 ('BUYER_ID_INCLUDE_VENDCODE',
2918 oils_i18n_gettext('BUYER_ID_INCLUDE_VENDCODE',
2919 'Buyer ID Qualifier Includes Vendcode', 'aea', 'label')),
2920 ('BUYER_ID_ONLY_VENDCODE',
2921 oils_i18n_gettext('BUYER_ID_ONLY_VENDCODE',
2922 'Buyer ID Qualifier Only Contains Vendcode', 'aea', 'label')),
2923 ('INCLUDE_BIB_EDITION',
2924 oils_i18n_gettext('INCLUDE_BIB_EDITION',
2925 'Order Lineitems Include Edition Info', 'aea', 'label')),
2926 ('INCLUDE_BIB_AUTHOR',
2927 oils_i18n_gettext('INCLUDE_BIB_AUTHOR',
2928 'Order Lineitems Include Author Info', 'aea', 'label')),
2929 ('INCLUDE_BIB_PAGINATION',
2930 oils_i18n_gettext('INCLUDE_BIB_PAGINATION',
2931 'Order Lineitems Include Pagination Info', 'aea', 'label')),
2933 oils_i18n_gettext('COPY_SPEC_CODES',
2934 'Order Lineitem Notes Include Copy Spec Codes', 'aea', 'label')),
2935 ('INCLUDE_EMPTY_IMD_VALUES',
2936 oils_i18n_gettext('INCLUDE_EMPTY_IMD_VALUES',
2937 'Lineitem Title, Author, etc. Fields Are Present Even if Empty', 'aea', 'label')),
2938 ('INCLUDE_EMPTY_LI_NOTE',
2939 oils_i18n_gettext('INCLUDE_EMPTY_LI_NOTE',
2940 'Order Lineitem Notes Always Present (Even if Empty)', 'aea', 'label')),
2941 ('INCLUDE_EMPTY_CALL_NUMBER',
2942 oils_i18n_gettext('INCLUDE_EMPTY_CALL_NUMBER',
2943 'Order Copies Always Include Call Number (Even if Empty)', 'aea', 'label')),
2944 ('INCLUDE_EMPTY_ITEM_TYPE',
2945 oils_i18n_gettext('INCLUDE_EMPTY_ITEM_TYPE',
2946 'Order Copies Always Include Item Type (Even if Empty)', 'aea', 'label')),
2947 ('INCLUDE_EMPTY_LOCATION',
2948 oils_i18n_gettext('INCLUDE_EMPTY_LOCATION',
2949 'Order Copies Always Include Location (Even if Empty)', 'aea', 'label')),
2950 ('INCLUDE_EMPTY_COLLECTION_CODE',
2951 oils_i18n_gettext('INCLUDE_EMPTY_COLLECTION_CODE',
2952 'Order Copies Always Include Collection Code (Even if Empty)', 'aea', 'label')),
2953 ('LINEITEM_IDENT_VENDOR_NUMBER',
2954 oils_i18n_gettext('LINEITEM_IDENT_VENDOR_NUMBER',
2955 'Lineitem Identifier Fields (LIN/PIA) Use Vendor-Encoded ID Value When Available', 'aea', 'label')),
2956 ('LINEITEM_REF_ID_ONLY',
2957 oils_i18n_gettext('LINEITEM_REF_ID_ONLY',
2958 'Lineitem Reference Field (RFF) Uses Lineitem ID Only', 'aea', 'label'))
2962 INSERT INTO acq.edi_attr_set (id, label) VALUES (1, 'Ingram Default');
2963 INSERT INTO acq.edi_attr_set (id, label) VALUES (2, 'Baker & Taylor Default');
2964 INSERT INTO acq.edi_attr_set (id, label) VALUES (3, 'Brodart Default');
2965 INSERT INTO acq.edi_attr_set (id, label) VALUES (4, 'Midwest Tape Default');
2966 INSERT INTO acq.edi_attr_set (id, label) VALUES (5, 'ULS Default');
2967 INSERT INTO acq.edi_attr_set (id, label) VALUES (6, 'Recorded Books Default');
2968 INSERT INTO acq.edi_attr_set (id, label) VALUES (7, 'Midwest Library Service');
2970 -- carve out space for mucho defaults
2971 SELECT SETVAL('acq.edi_attr_set_id_seq'::TEXT, 1000);
2973 INSERT INTO acq.edi_attr_set_map (attr_set, attr) VALUES
2976 (1, 'INCLUDE_PO_NAME'),
2977 (1, 'INCLUDE_COPIES'),
2978 (1, 'INCLUDE_ITEM_TYPE'),
2979 (1, 'INCLUDE_COLLECTION_CODE'),
2980 (1, 'INCLUDE_OWNING_LIB'),
2981 (1, 'INCLUDE_QUANTITY'),
2982 (1, 'INCLUDE_BIB_PAGINATION'),
2985 (2, 'INCLUDE_COPIES'),
2986 (2, 'INCLUDE_ITEM_TYPE'),
2987 (2, 'INCLUDE_COLLECTION_CODE'),
2988 (2, 'INCLUDE_CALL_NUMBER'),
2989 (2, 'INCLUDE_OWNING_LIB'),
2990 (2, 'INCLUDE_QUANTITY'),
2991 (2, 'INCLUDE_BIB_PAGINATION'),
2992 (2, 'BUYER_ID_INCLUDE_VENDCODE'),
2993 (2, 'INCLUDE_EMPTY_LI_NOTE'),
2994 (2, 'INCLUDE_EMPTY_CALL_NUMBER'),
2995 (2, 'INCLUDE_EMPTY_ITEM_TYPE'),
2996 (2, 'INCLUDE_EMPTY_COLLECTION_CODE'),
2997 (2, 'INCLUDE_EMPTY_LOCATION'),
2998 (2, 'LINEITEM_IDENT_VENDOR_NUMBER'),
2999 (2, 'LINEITEM_REF_ID_ONLY'),
3002 (3, 'INCLUDE_COPIES'),
3003 (3, 'INCLUDE_FUND'),
3004 (3, 'INCLUDE_ITEM_TYPE'),
3005 (3, 'INCLUDE_COLLECTION_CODE'),
3006 (3, 'INCLUDE_OWNING_LIB'),
3007 (3, 'INCLUDE_QUANTITY'),
3008 (3, 'INCLUDE_BIB_PAGINATION'),
3009 (3, 'COPY_SPEC_CODES'),
3012 (4, 'INCLUDE_COPIES'),
3013 (4, 'INCLUDE_FUND'),
3014 (4, 'INCLUDE_OWNING_LIB'),
3015 (4, 'INCLUDE_QUANTITY'),
3016 (4, 'INCLUDE_BIB_PAGINATION'),
3019 (5, 'INCLUDE_COPIES'),
3020 (5, 'INCLUDE_ITEM_TYPE'),
3021 (5, 'INCLUDE_COLLECTION_CODE'),
3022 (5, 'INCLUDE_OWNING_LIB'),
3023 (5, 'INCLUDE_QUANTITY'),
3024 (5, 'INCLUDE_BIB_AUTHOR'),
3025 (5, 'INCLUDE_BIB_EDITION'),
3026 (5, 'INCLUDE_EMPTY_LI_NOTE'),
3029 (6, 'INCLUDE_COPIES'),
3030 (6, 'INCLUDE_ITEM_TYPE'),
3031 (6, 'INCLUDE_COLLECTION_CODE'),
3032 (6, 'INCLUDE_OWNING_LIB'),
3033 (6, 'INCLUDE_QUANTITY'),
3034 (6, 'INCLUDE_BIB_PAGINATION'),
3036 -- Midwest Library Service
3037 (7, 'INCLUDE_BIB_AUTHOR'),
3038 (7, 'INCLUDE_BIB_EDITION'),
3039 (7, 'BUYER_ID_ONLY_VENDCODE'),
3040 (7, 'INCLUDE_EMPTY_IMD_VALUES')
3047 SELECT evergreen.upgrade_deps_block_check('1068', :eg_version); --miker/gmcharlt/kmlussier
3049 INSERT INTO config.xml_transform (name,namespace_uri,prefix,xslt) VALUES ('mads21','http://www.loc.gov/mads/v2','mads21',$XSLT$<?xml version="1.0" encoding="UTF-8"?>
3050 <xsl:stylesheet version="1.0" xmlns:mads="http://www.loc.gov/mads/v2"
3051 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:marc="http://www.loc.gov/MARC21/slim"
3052 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="marc">
3053 <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
3054 <xsl:strip-space elements="*"/>
3056 <xsl:variable name="ascii">
3057 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
3060 <xsl:variable name="latin1">
3061 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
3063 <!-- Characters that usually don't need to be escaped -->
3064 <xsl:variable name="safe">
3065 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
3068 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
3071 <xsl:template name="datafield">
3072 <xsl:param name="tag"/>
3073 <xsl:param name="ind1">
3074 <xsl:text> </xsl:text>
3076 <xsl:param name="ind2">
3077 <xsl:text> </xsl:text>
3079 <xsl:param name="subfields"/>
3080 <xsl:element name="marc:datafield">
3081 <xsl:attribute name="tag">
3082 <xsl:value-of select="$tag"/>
3084 <xsl:attribute name="ind1">
3085 <xsl:value-of select="$ind1"/>
3087 <xsl:attribute name="ind2">
3088 <xsl:value-of select="$ind2"/>
3090 <xsl:copy-of select="$subfields"/>
3094 <xsl:template name="subfieldSelect">
3095 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
3096 <xsl:param name="delimeter">
3097 <xsl:text> </xsl:text>
3099 <xsl:variable name="str">
3100 <xsl:for-each select="marc:subfield">
3101 <xsl:if test="contains($codes, @code)">
3102 <xsl:value-of select="text()"/>
3103 <xsl:value-of select="$delimeter"/>
3107 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
3110 <xsl:template name="buildSpaces">
3111 <xsl:param name="spaces"/>
3112 <xsl:param name="char">
3113 <xsl:text> </xsl:text>
3115 <xsl:if test="$spaces>0">
3116 <xsl:value-of select="$char"/>
3117 <xsl:call-template name="buildSpaces">
3118 <xsl:with-param name="spaces" select="$spaces - 1"/>
3119 <xsl:with-param name="char" select="$char"/>
3120 </xsl:call-template>
3124 <xsl:template name="chopPunctuation">
3125 <xsl:param name="chopString"/>
3126 <xsl:param name="punctuation">
3127 <xsl:text>.:,;/ </xsl:text>
3129 <xsl:variable name="length" select="string-length($chopString)"/>
3131 <xsl:when test="$length=0"/>
3132 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3133 <xsl:call-template name="chopPunctuation">
3134 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3135 <xsl:with-param name="punctuation" select="$punctuation"/>
3136 </xsl:call-template>
3138 <xsl:when test="not($chopString)"/>
3140 <xsl:value-of select="$chopString"/>
3145 <xsl:template name="chopPunctuationFront">
3146 <xsl:param name="chopString"/>
3147 <xsl:variable name="length" select="string-length($chopString)"/>
3149 <xsl:when test="$length=0"/>
3150 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
3151 <xsl:call-template name="chopPunctuationFront">
3152 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
3154 </xsl:call-template>
3156 <xsl:when test="not($chopString)"/>
3158 <xsl:value-of select="$chopString"/>
3163 <xsl:template name="chopPunctuationBack">
3164 <xsl:param name="chopString"/>
3165 <xsl:param name="punctuation">
3166 <xsl:text>.:,;/] </xsl:text>
3168 <xsl:variable name="length" select="string-length($chopString)"/>
3170 <xsl:when test="$length=0"/>
3171 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3172 <xsl:call-template name="chopPunctuation">
3173 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3174 <xsl:with-param name="punctuation" select="$punctuation"/>
3175 </xsl:call-template>
3177 <xsl:when test="not($chopString)"/>
3179 <xsl:value-of select="$chopString"/>
3184 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
3185 <xsl:template name="url-encode">
3187 <xsl:param name="str"/>
3189 <xsl:if test="$str">
3190 <xsl:variable name="first-char" select="substring($str,1,1)"/>
3192 <xsl:when test="contains($safe,$first-char)">
3193 <xsl:value-of select="$first-char"/>
3196 <xsl:variable name="codepoint">
3198 <xsl:when test="contains($ascii,$first-char)">
3200 select="string-length(substring-before($ascii,$first-char)) + 32"
3203 <xsl:when test="contains($latin1,$first-char)">
3205 select="string-length(substring-before($latin1,$first-char)) + 160"/>
3209 <xsl:message terminate="no">Warning: string contains a character
3210 that is out of range! Substituting "?".</xsl:message>
3211 <xsl:text>63</xsl:text>
3215 <xsl:variable name="hex-digit1"
3216 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
3217 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
3218 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
3219 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
3222 <xsl:if test="string-length($str) > 1">
3223 <xsl:call-template name="url-encode">
3224 <xsl:with-param name="str" select="substring($str,2)"/>
3225 </xsl:call-template>
3232 2.14 Fixed bug in mads:geographic attributes syntax ws 05/04/2016
3233 2.13 fixed repeating <geographic> tmee 01/31/2014
3234 2.12 added $2 authority for <classification> tmee 09/18/2012
3235 2.11 added delimiters between <classification> subfields tmee 09/18/2012
3236 2.10 fixed type="other" and type="otherType" for mads:related tmee 09/16/2011
3237 2.09 fixed professionTerm and genreTerm empty tag error tmee 09/16/2011
3238 2.08 fixed marc:subfield @code='i' matching error tmee 09/16/2011
3239 2.07 fixed 555 duplication error tmee 08/10/2011
3240 2.06 fixed topic subfield error tmee 08/10/2011
3241 2.05 fixed title subfield error tmee 06/20/2011
3242 2.04 fixed geographicSubdivision mapping for authority element tmee 06/16/2011
3243 2.03 added classification for 053, 055, 060, 065, 070, 080, 082, 083, 086, 087 tmee 06/03/2011
3244 2.02 added descriptionStandard for 008/10 tmee 04/27/2011
3245 2.01 added extensions for 046, 336, 370, 374, 375, 376 tmee 04/08/2011
3246 2.00 redefined imported MODS elements in version 1.0 to MADS elements in version 2.0 tmee 02/08/2011
3247 1.08 added 372 subfields $a $s $t for <fieldOfActivity> tmee 06/24/2010
3248 1.07 removed role/roleTerm 100, 110, 111, 400, 410, 411, 500, 510, 511, 700, 710, 711 tmee 06/24/2010
3249 1.06 added strip-space tmee 06/24/2010
3250 1.05 added subfield $a for 130, 430, 530 tmee 06/21/2010
3251 1.04 fixed 550 z omission ntra 08/11/2008
3252 1.03 removed duplication of 550 $a text tmee 11/01/2006
3253 1.02 fixed namespace references between mads and mods ntra 10/06/2006
3254 1.01 revised rgue/jrad 11/29/05
3255 1.00 adapted from MARC21Slim2MODS3.xsl ntra 07/06/05
3258 <!-- authority attribute defaults to 'naf' if not set using this authority parameter, for <authority> descriptors: name, titleInfo, geographic -->
3259 <xsl:param name="authority"/>
3260 <xsl:variable name="auth">
3262 <xsl:when test="$authority">
3263 <xsl:value-of select="$authority"/>
3265 <xsl:otherwise>naf</xsl:otherwise>
3268 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
3269 <xsl:variable name="controlField008-06"
3270 select="substring(descendant-or-self::marc:controlfield[@tag=008],7,1)"/>
3271 <xsl:variable name="controlField008-11"
3272 select="substring(descendant-or-self::marc:controlfield[@tag=008],12,1)"/>
3273 <xsl:variable name="controlField008-14"
3274 select="substring(descendant-or-self::marc:controlfield[@tag=008],15,1)"/>
3275 <xsl:template match="/">
3277 <xsl:when test="descendant-or-self::marc:collection">
3278 <mads:madsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3279 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/v2/mads-2-0.xsd">
3280 <xsl:for-each select="descendant-or-self::marc:collection/marc:record">
3281 <mads:mads version="2.0">
3282 <xsl:call-template name="marcRecord"/>
3285 </mads:madsCollection>
3288 <mads:mads version="2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3289 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/mads-2-0.xsd">
3290 <xsl:for-each select="descendant-or-self::marc:record">
3291 <xsl:call-template name="marcRecord"/>
3298 <xsl:template name="marcRecord">
3302 <xsl:when test="$controlField008-06='d'">
3303 <xsl:attribute name="geographicSubdivision">
3304 <xsl:text>direct</xsl:text>
3307 <xsl:when test="$controlField008-06='i'">
3308 <xsl:attribute name="geographicSubdivision">
3309 <xsl:text>indirect</xsl:text>
3312 <xsl:when test="$controlField008-06='n'">
3313 <xsl:attribute name="geographicSubdivision">
3314 <xsl:text>not applicable</xsl:text>
3319 <xsl:apply-templates select="marc:datafield[100 <= @tag and @tag < 200]"/>
3323 <xsl:apply-templates
3324 select="marc:datafield[500 <= @tag and @tag <= 585]|marc:datafield[700 <= @tag and @tag <= 785]"/>
3327 <xsl:apply-templates select="marc:datafield[400 <= @tag and @tag <= 485]"/>
3330 <xsl:apply-templates select="marc:datafield[667 <= @tag and @tag <= 688]"/>
3333 <xsl:apply-templates select="marc:datafield[@tag=856]"/>
3334 <xsl:apply-templates select="marc:datafield[@tag=010]"/>
3335 <xsl:apply-templates select="marc:datafield[@tag=024]"/>
3336 <xsl:apply-templates select="marc:datafield[@tag=372]"/>
3338 <!-- classification -->
3339 <xsl:apply-templates select="marc:datafield[@tag=053]"/>
3340 <xsl:apply-templates select="marc:datafield[@tag=055]"/>
3341 <xsl:apply-templates select="marc:datafield[@tag=060]"/>
3342 <xsl:apply-templates select="marc:datafield[@tag=065]"/>
3343 <xsl:apply-templates select="marc:datafield[@tag=070]"/>
3344 <xsl:apply-templates select="marc:datafield[@tag=080]"/>
3345 <xsl:apply-templates select="marc:datafield[@tag=082]"/>
3346 <xsl:apply-templates select="marc:datafield[@tag=083]"/>
3347 <xsl:apply-templates select="marc:datafield[@tag=086]"/>
3348 <xsl:apply-templates select="marc:datafield[@tag=087]"/>
3351 <xsl:for-each select="marc:datafield[@tag=373]">
3354 <xsl:value-of select="marc:subfield[@code='a']"/>
3356 <mads:dateValid point="start">
3357 <xsl:value-of select="marc:subfield[@code='s']"/>
3359 <mads:dateValid point="end">
3360 <xsl:value-of select="marc:subfield[@code='t']"/>
3364 <xsl:for-each select="marc:datafield[@tag=371]">
3368 <xsl:value-of select="marc:subfield[@code='a']"/>
3371 <xsl:value-of select="marc:subfield[@code='b']"/>
3374 <xsl:value-of select="marc:subfield[@code='c']"/>
3377 <xsl:value-of select="marc:subfield[@code='d']"/>
3380 <xsl:value-of select="marc:subfield[@code='e']"/>
3384 <xsl:value-of select="marc:subfield[@code='m']"/>
3390 <xsl:for-each select="marc:datafield[@tag=336]">
3393 <mads:contentType type="text">
3394 <xsl:value-of select="marc:subfield[@code='a']"/>
3396 <mads:contentType type="code">
3397 <xsl:value-of select="marc:subfield[@code='b']"/>
3403 <xsl:for-each select="marc:datafield[@tag=374]">
3407 <xsl:when test="marc:subfield[@code='a']">
3408 <mads:professionTerm>
3409 <xsl:value-of select="marc:subfield[@code='a']"/>
3410 </mads:professionTerm>
3412 <xsl:when test="marc:subfield[@code='s']">
3413 <mads:dateValid point="start">
3414 <xsl:value-of select="marc:subfield[@code='s']"/>
3417 <xsl:when test="marc:subfield[@code='t']">
3418 <mads:dateValid point="end">
3419 <xsl:value-of select="marc:subfield[@code='t']"/>
3427 <xsl:for-each select="marc:datafield[@tag=375]">
3431 <xsl:when test="marc:subfield[@code='a']">
3433 <xsl:value-of select="marc:subfield[@code='a']"/>
3436 <xsl:when test="marc:subfield[@code='s']">
3437 <mads:dateValid point="start">
3438 <xsl:value-of select="marc:subfield[@code='s']"/>
3441 <xsl:when test="marc:subfield[@code='t']">
3442 <mads:dateValid point="end">
3443 <xsl:value-of select="marc:subfield[@code='t']"/>
3451 <xsl:for-each select="marc:datafield[@tag=376]">
3453 <mads:familyInformation>
3455 <xsl:value-of select="marc:subfield[@code='a']"/>
3456 </mads:typeOfFamily>
3457 <mads:nameOfProminentMember>
3458 <xsl:value-of select="marc:subfield[@code='b']"/>
3459 </mads:nameOfProminentMember>
3460 <mads:hereditaryTitle>
3461 <xsl:value-of select="marc:subfield[@code='c']"/>
3462 </mads:hereditaryTitle>
3463 <mads:dateValid point="start">
3464 <xsl:value-of select="marc:subfield[@code='s']"/>
3466 <mads:dateValid point="end">
3467 <xsl:value-of select="marc:subfield[@code='t']"/>
3469 </mads:familyInformation>
3474 <mads:recordOrigin>Converted from MARCXML to MADS version 2.0 (Revision 2.13)</mads:recordOrigin>
3475 <!-- <xsl:apply-templates select="marc:datafield[@tag=024]"/> -->
3477 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='a']"/>
3478 <xsl:apply-templates select="marc:controlfield[@tag=005]"/>
3479 <xsl:apply-templates select="marc:controlfield[@tag=001]"/>
3480 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='b']"/>
3481 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='e']"/>
3482 <xsl:for-each select="marc:controlfield[@tag=008]">
3483 <xsl:if test="substring(.,11,1)='a'">
3484 <mads:descriptionStandard>
3485 <xsl:text>earlier rules</xsl:text>
3486 </mads:descriptionStandard>
3488 <xsl:if test="substring(.,11,1)='b'">
3489 <mads:descriptionStandard>
3490 <xsl:text>aacr1</xsl:text>
3491 </mads:descriptionStandard>
3493 <xsl:if test="substring(.,11,1)='c'">
3494 <mads:descriptionStandard>
3495 <xsl:text>aacr2</xsl:text>
3496 </mads:descriptionStandard>
3498 <xsl:if test="substring(.,11,1)='d'">
3499 <mads:descriptionStandard>
3500 <xsl:text>aacr2 compatible</xsl:text>
3501 </mads:descriptionStandard>
3503 <xsl:if test="substring(.,11,1)='z'">
3504 <mads:descriptionStandard>
3505 <xsl:text>other rules</xsl:text>
3506 </mads:descriptionStandard>
3512 <!-- start of secondary templates -->
3514 <!-- ======== xlink ======== -->
3516 <!-- <xsl:template name="uri">
3517 <xsl:for-each select="marc:subfield[@code='0']">
3518 <xsl:attribute name="xlink:href">
3519 <xsl:value-of select="."/>
3524 <xsl:template match="marc:subfield[@code='i']">
3525 <xsl:attribute name="otherType">
3526 <xsl:value-of select="."/>
3530 <!-- No role/roleTerm mapped in MADS 06/24/2010
3531 <xsl:template name="role">
3532 <xsl:for-each select="marc:subfield[@code='e']">
3534 <mads:roleTerm type="text">
3535 <xsl:value-of select="."/>
3542 <xsl:template name="part">
3543 <xsl:variable name="partNumber">
3544 <xsl:call-template name="specialSubfieldSelect">
3545 <xsl:with-param name="axis">n</xsl:with-param>
3546 <xsl:with-param name="anyCodes">n</xsl:with-param>
3547 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3548 </xsl:call-template>
3550 <xsl:variable name="partName">
3551 <xsl:call-template name="specialSubfieldSelect">
3552 <xsl:with-param name="axis">p</xsl:with-param>
3553 <xsl:with-param name="anyCodes">p</xsl:with-param>
3554 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3555 </xsl:call-template>
3557 <xsl:if test="string-length(normalize-space($partNumber))">
3559 <xsl:call-template name="chopPunctuation">
3560 <xsl:with-param name="chopString" select="$partNumber"/>
3561 </xsl:call-template>
3564 <xsl:if test="string-length(normalize-space($partName))">
3566 <xsl:call-template name="chopPunctuation">
3567 <xsl:with-param name="chopString" select="$partName"/>
3568 </xsl:call-template>
3573 <xsl:template name="nameABCDN">
3574 <xsl:for-each select="marc:subfield[@code='a']">
3576 <xsl:call-template name="chopPunctuation">
3577 <xsl:with-param name="chopString" select="."/>
3578 </xsl:call-template>
3581 <xsl:for-each select="marc:subfield[@code='b']">
3583 <xsl:value-of select="."/>
3587 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
3589 <xsl:call-template name="subfieldSelect">
3590 <xsl:with-param name="codes">cdn</xsl:with-param>
3591 </xsl:call-template>
3596 <xsl:template name="nameABCDQ">
3598 <xsl:call-template name="chopPunctuation">
3599 <xsl:with-param name="chopString">
3600 <xsl:call-template name="subfieldSelect">
3601 <xsl:with-param name="codes">aq</xsl:with-param>
3602 </xsl:call-template>
3604 </xsl:call-template>
3606 <xsl:call-template name="termsOfAddress"/>
3607 <xsl:call-template name="nameDate"/>
3610 <xsl:template name="nameACDENQ">
3612 <xsl:call-template name="subfieldSelect">
3613 <xsl:with-param name="codes">acdenq</xsl:with-param>
3614 </xsl:call-template>
3618 <xsl:template name="nameDate">
3619 <xsl:for-each select="marc:subfield[@code='d']">
3620 <mads:namePart type="date">
3621 <xsl:call-template name="chopPunctuation">
3622 <xsl:with-param name="chopString" select="."/>
3623 </xsl:call-template>
3628 <xsl:template name="specialSubfieldSelect">
3629 <xsl:param name="anyCodes"/>
3630 <xsl:param name="axis"/>
3631 <xsl:param name="beforeCodes"/>
3632 <xsl:param name="afterCodes"/>
3633 <xsl:variable name="str">
3634 <xsl:for-each select="marc:subfield">
3636 test="contains($anyCodes, @code) or (contains($beforeCodes,@code) and following-sibling::marc:subfield[@code=$axis]) or (contains($afterCodes,@code) and preceding-sibling::marc:subfield[@code=$axis])">
3637 <xsl:value-of select="text()"/>
3638 <xsl:text> </xsl:text>
3642 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
3645 <xsl:template name="termsOfAddress">
3646 <xsl:if test="marc:subfield[@code='b' or @code='c']">
3647 <mads:namePart type="termsOfAddress">
3648 <xsl:call-template name="chopPunctuation">
3649 <xsl:with-param name="chopString">
3650 <xsl:call-template name="subfieldSelect">
3651 <xsl:with-param name="codes">bc</xsl:with-param>
3652 </xsl:call-template>
3654 </xsl:call-template>
3659 <xsl:template name="displayLabel">
3660 <xsl:if test="marc:subfield[@code='z']">
3661 <xsl:attribute name="displayLabel">
3662 <xsl:value-of select="marc:subfield[@code='z']"/>
3665 <xsl:if test="marc:subfield[@code='3']">
3666 <xsl:attribute name="displayLabel">
3667 <xsl:value-of select="marc:subfield[@code='3']"/>
3672 <xsl:template name="isInvalid">
3673 <xsl:if test="@code='z'">
3674 <xsl:attribute name="invalid">yes</xsl:attribute>
3678 <xsl:template name="sub2Attribute">
3680 <xsl:if test="../marc:subfield[@code='2']">
3681 <xsl:attribute name="type">
3682 <xsl:value-of select="../marc:subfield[@code='2']"/>
3687 <xsl:template match="marc:controlfield[@tag=001]">
3688 <mads:recordIdentifier>
3689 <xsl:if test="../marc:controlfield[@tag=003]">
3690 <xsl:attribute name="source">
3691 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
3694 <xsl:value-of select="."/>
3695 </mads:recordIdentifier>
3698 <xsl:template match="marc:controlfield[@tag=005]">
3699 <mads:recordChangeDate encoding="iso8601">
3700 <xsl:value-of select="."/>
3701 </mads:recordChangeDate>
3704 <xsl:template match="marc:controlfield[@tag=008]">
3705 <mads:recordCreationDate encoding="marc">
3706 <xsl:value-of select="substring(.,1,6)"/>
3707 </mads:recordCreationDate>
3710 <xsl:template match="marc:datafield[@tag=010]">
3711 <xsl:for-each select="marc:subfield">
3712 <mads:identifier type="lccn">
3713 <xsl:call-template name="isInvalid"/>
3714 <xsl:value-of select="."/>
3719 <xsl:template match="marc:datafield[@tag=024]">
3720 <xsl:for-each select="marc:subfield[not(@code=2)]">
3722 <xsl:call-template name="isInvalid"/>
3723 <xsl:call-template name="sub2Attribute"/>
3724 <xsl:value-of select="."/>
3729 <!-- ========== 372 ========== -->
3730 <xsl:template match="marc:datafield[@tag=372]">
3731 <mads:fieldOfActivity>
3732 <xsl:call-template name="subfieldSelect">
3733 <xsl:with-param name="codes">a</xsl:with-param>
3734 </xsl:call-template>
3735 <xsl:text>-</xsl:text>
3736 <xsl:call-template name="subfieldSelect">
3737 <xsl:with-param name="codes">st</xsl:with-param>
3738 </xsl:call-template>
3739 </mads:fieldOfActivity>
3743 <!-- ========== 040 ========== -->
3744 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='a']">
3745 <mads:recordContentSource authority="marcorg">
3746 <xsl:value-of select="."/>
3747 </mads:recordContentSource>
3750 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='b']">
3751 <mads:languageOfCataloging>
3752 <mads:languageTerm authority="iso639-2b" type="code">
3753 <xsl:value-of select="."/>
3754 </mads:languageTerm>
3755 </mads:languageOfCataloging>
3758 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='e']">
3759 <mads:descriptionStandard>
3760 <xsl:value-of select="."/>
3761 </mads:descriptionStandard>
3764 <!-- ========== classification 2.03 ========== -->
3766 <xsl:template match="marc:datafield[@tag=053]">
3767 <mads:classification>
3768 <xsl:call-template name="subfieldSelect">
3769 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
3770 <xsl:with-param name="delimeter">-</xsl:with-param>
3771 </xsl:call-template>
3772 </mads:classification>
3775 <xsl:template match="marc:datafield[@tag=055]">
3776 <mads:classification>
3777 <xsl:call-template name="subfieldSelect">
3778 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
3779 <xsl:with-param name="delimeter">-</xsl:with-param>
3780 </xsl:call-template>
3781 </mads:classification>
3784 <xsl:template match="marc:datafield[@tag=060]">
3785 <mads:classification>
3786 <xsl:call-template name="subfieldSelect">
3787 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
3788 <xsl:with-param name="delimeter">-</xsl:with-param>
3789 </xsl:call-template>
3790 </mads:classification>
3792 <xsl:template match="marc:datafield[@tag=065]">
3793 <mads:classification>
3794 <xsl:attribute name="authority">
3795 <xsl:value-of select="marc:subfield[@code='2']"/>
3797 <xsl:call-template name="subfieldSelect">
3798 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
3799 <xsl:with-param name="delimeter">-</xsl:with-param>
3800 </xsl:call-template>
3801 </mads:classification>
3803 <xsl:template match="marc:datafield[@tag=070]">
3804 <mads:classification>
3805 <xsl:call-template name="subfieldSelect">
3806 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3807 <xsl:with-param name="delimeter">-</xsl:with-param>
3808 </xsl:call-template>
3809 </mads:classification>
3811 <xsl:template match="marc:datafield[@tag=080]">
3812 <mads:classification>
3813 <xsl:attribute name="authority">
3814 <xsl:value-of select="marc:subfield[@code='2']"/>
3816 <xsl:call-template name="subfieldSelect">
3817 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3818 <xsl:with-param name="delimeter">-</xsl:with-param>
3819 </xsl:call-template>
3820 </mads:classification>
3822 <xsl:template match="marc:datafield[@tag=082]">
3823 <mads:classification>
3824 <xsl:attribute name="authority">
3825 <xsl:value-of select="marc:subfield[@code='2']"/>
3827 <xsl:call-template name="subfieldSelect">
3828 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3829 <xsl:with-param name="delimeter">-</xsl:with-param>
3830 </xsl:call-template>
3831 </mads:classification>
3833 <xsl:template match="marc:datafield[@tag=083]">
3834 <mads:classification>
3835 <xsl:attribute name="authority">
3836 <xsl:value-of select="marc:subfield[@code='2']"/>
3838 <xsl:call-template name="subfieldSelect">
3839 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3840 <xsl:with-param name="delimeter">-</xsl:with-param>
3841 </xsl:call-template>
3842 </mads:classification>
3844 <xsl:template match="marc:datafield[@tag=086]">
3845 <mads:classification>
3846 <xsl:attribute name="authority">
3847 <xsl:value-of select="marc:subfield[@code='2']"/>
3849 <xsl:call-template name="subfieldSelect">
3850 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3851 <xsl:with-param name="delimeter">-</xsl:with-param>
3852 </xsl:call-template>
3853 </mads:classification>
3855 <xsl:template match="marc:datafield[@tag=087]">
3856 <mads:classification>
3857 <xsl:attribute name="authority">
3858 <xsl:value-of select="marc:subfield[@code='2']"/>
3860 <xsl:call-template name="subfieldSelect">
3861 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
3862 <xsl:with-param name="delimeter">-</xsl:with-param>
3863 </xsl:call-template>
3864 </mads:classification>
3868 <!-- ========== names ========== -->
3869 <xsl:template match="marc:datafield[@tag=100]">
3870 <mads:name type="personal">
3871 <xsl:call-template name="setAuthority"/>
3872 <xsl:call-template name="nameABCDQ"/>
3874 <xsl:apply-templates select="*[marc:subfield[not(contains('abcdeq',@code))]]"/>
3875 <xsl:call-template name="title"/>
3876 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3879 <xsl:template match="marc:datafield[@tag=110]">
3880 <mads:name type="corporate">
3881 <xsl:call-template name="setAuthority"/>
3882 <xsl:call-template name="nameABCDN"/>
3884 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3887 <xsl:template match="marc:datafield[@tag=111]">
3888 <mads:name type="conference">
3889 <xsl:call-template name="setAuthority"/>
3890 <xsl:call-template name="nameACDENQ"/>
3892 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3895 <xsl:template match="marc:datafield[@tag=400]">
3897 <xsl:call-template name="variantTypeAttribute"/>
3898 <mads:name type="personal">
3899 <xsl:call-template name="nameABCDQ"/>
3901 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3902 <xsl:call-template name="title"/>
3906 <xsl:template match="marc:datafield[@tag=410]">
3908 <xsl:call-template name="variantTypeAttribute"/>
3909 <mads:name type="corporate">
3910 <xsl:call-template name="nameABCDN"/>
3912 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3916 <xsl:template match="marc:datafield[@tag=411]">
3918 <xsl:call-template name="variantTypeAttribute"/>
3919 <mads:name type="conference">
3920 <xsl:call-template name="nameACDENQ"/>
3922 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3926 <xsl:template match="marc:datafield[@tag=500]|marc:datafield[@tag=700]">
3928 <xsl:call-template name="relatedTypeAttribute"/>
3929 <!-- <xsl:call-template name="uri"/> -->
3930 <mads:name type="personal">
3931 <xsl:call-template name="setAuthority"/>
3932 <xsl:call-template name="nameABCDQ"/>
3934 <xsl:call-template name="title"/>
3935 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3939 <xsl:template match="marc:datafield[@tag=510]|marc:datafield[@tag=710]">
3941 <xsl:call-template name="relatedTypeAttribute"/>
3942 <!-- <xsl:call-template name="uri"/> -->
3943 <mads:name type="corporate">
3944 <xsl:call-template name="setAuthority"/>
3945 <xsl:call-template name="nameABCDN"/>
3947 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3951 <xsl:template match="marc:datafield[@tag=511]|marc:datafield[@tag=711]">
3953 <xsl:call-template name="relatedTypeAttribute"/>
3954 <!-- <xsl:call-template name="uri"/> -->
3955 <mads:name type="conference">
3956 <xsl:call-template name="setAuthority"/>
3957 <xsl:call-template name="nameACDENQ"/>
3959 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3963 <!-- ========== titles ========== -->
3964 <xsl:template match="marc:datafield[@tag=130]">
3965 <xsl:call-template name="uniform-title"/>
3966 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3969 <xsl:template match="marc:datafield[@tag=430]">
3971 <xsl:call-template name="variantTypeAttribute"/>
3972 <xsl:call-template name="uniform-title"/>
3973 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3977 <xsl:template match="marc:datafield[@tag=530]|marc:datafield[@tag=730]">
3979 <xsl:call-template name="relatedTypeAttribute"/>
3980 <xsl:call-template name="uniform-title"/>
3981 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
3985 <xsl:template name="title">
3986 <xsl:variable name="hasTitle">
3987 <xsl:for-each select="marc:subfield">
3988 <xsl:if test="(contains('tfghklmors',@code) )">
3989 <xsl:value-of select="@code"/>
3993 <xsl:if test="string-length($hasTitle) > 0 ">
3995 <xsl:call-template name="setAuthority"/>
3997 <xsl:variable name="str">
3998 <xsl:for-each select="marc:subfield">
3999 <xsl:if test="(contains('atfghklmors',@code) )">
4000 <xsl:value-of select="text()"/>
4001 <xsl:text> </xsl:text>
4005 <xsl:call-template name="chopPunctuation">
4006 <xsl:with-param name="chopString">
4007 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4009 </xsl:call-template>
4011 <xsl:call-template name="part"/>
4012 <!-- <xsl:call-template name="uri"/> -->
4017 <xsl:template name="uniform-title">
4018 <xsl:variable name="hasTitle">
4019 <xsl:for-each select="marc:subfield">
4020 <xsl:if test="(contains('atfghklmors',@code) )">
4021 <xsl:value-of select="@code"/>
4025 <xsl:if test="string-length($hasTitle) > 0 ">
4027 <xsl:call-template name="setAuthority"/>
4029 <xsl:variable name="str">
4030 <xsl:for-each select="marc:subfield">
4031 <xsl:if test="(contains('adfghklmors',@code) )">
4032 <xsl:value-of select="text()"/>
4033 <xsl:text> </xsl:text>
4037 <xsl:call-template name="chopPunctuation">
4038 <xsl:with-param name="chopString">
4039 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4041 </xsl:call-template>
4043 <xsl:call-template name="part"/>
4044 <!-- <xsl:call-template name="uri"/> -->
4050 <!-- ========== topics ========== -->
4051 <xsl:template match="marc:subfield[@code='x']">
4053 <xsl:call-template name="chopPunctuation">
4054 <xsl:with-param name="chopString">
4055 <xsl:value-of select="."/>
4057 </xsl:call-template>
4063 match="marc:datafield[@tag=150][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=180][marc:subfield[@code='x']]">
4064 <xsl:call-template name="topic"/>
4065 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4068 match="marc:datafield[@tag=450][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=480][marc:subfield[@code='x']]">
4070 <xsl:call-template name="variantTypeAttribute"/>
4071 <xsl:call-template name="topic"/>
4075 match="marc:datafield[@tag=550 or @tag=750][marc:subfield[@code='a' or @code='b']]">
4077 <xsl:call-template name="relatedTypeAttribute"/>
4078 <!-- <xsl:call-template name="uri"/> -->
4079 <xsl:call-template name="topic"/>
4080 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4083 <xsl:template name="topic">
4085 <xsl:call-template name="setAuthority"/>
4086 <!-- tmee2006 dedupe 550a
4087 <xsl:if test="@tag=550 or @tag=750">
4088 <xsl:call-template name="subfieldSelect">
4089 <xsl:with-param name="codes">ab</xsl:with-param>
4090 </xsl:call-template>
4094 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4095 <xsl:call-template name="chopPunctuation">
4096 <xsl:with-param name="chopString">
4097 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4099 </xsl:call-template>
4102 <xsl:call-template name="chopPunctuation">
4103 <xsl:with-param name="chopString">
4105 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4106 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4109 <xsl:call-template name="subfieldSelect">
4110 <xsl:with-param name="codes">ab</xsl:with-param>
4111 </xsl:call-template>
4115 </xsl:call-template>
4119 <!-- ========= temporals ========== -->
4120 <xsl:template match="marc:subfield[@code='y']">
4122 <xsl:call-template name="chopPunctuation">
4123 <xsl:with-param name="chopString">
4124 <xsl:value-of select="."/>
4126 </xsl:call-template>
4130 match="marc:datafield[@tag=148][marc:subfield[@code='a']]|marc:datafield[@tag=182 ][marc:subfield[@code='y']]">
4131 <xsl:call-template name="temporal"/>
4134 match="marc:datafield[@tag=448][marc:subfield[@code='a']]|marc:datafield[@tag=482][marc:subfield[@code='y']]">
4136 <xsl:call-template name="variantTypeAttribute"/>
4137 <xsl:call-template name="temporal"/>
4141 match="marc:datafield[@tag=548 or @tag=748][marc:subfield[@code='a']]|marc:datafield[@tag=582 or @tag=782][marc:subfield[@code='y']]">
4143 <xsl:call-template name="relatedTypeAttribute"/>
4144 <!-- <xsl:call-template name="uri"/> -->
4145 <xsl:call-template name="temporal"/>
4148 <xsl:template name="temporal">
4150 <xsl:call-template name="setAuthority"/>
4151 <xsl:if test="@tag=548 or @tag=748">
4152 <xsl:value-of select="marc:subfield[@code='a']"/>
4154 <xsl:call-template name="chopPunctuation">
4155 <xsl:with-param name="chopString">
4157 <xsl:when test="@tag=182 or @tag=482 or @tag=582 or @tag=782">
4158 <xsl:apply-templates select="marc:subfield[@code='y']"/>
4161 <xsl:value-of select="marc:subfield[@code='a']"/>
4165 </xsl:call-template>
4167 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4170 <!-- ========== genre ========== -->
4171 <xsl:template match="marc:subfield[@code='v']">
4173 <xsl:call-template name="chopPunctuation">
4174 <xsl:with-param name="chopString">
4175 <xsl:value-of select="."/>
4177 </xsl:call-template>
4181 match="marc:datafield[@tag=155][marc:subfield[@code='a']]|marc:datafield[@tag=185][marc:subfield[@code='v']]">
4182 <xsl:call-template name="genre"/>
4185 match="marc:datafield[@tag=455][marc:subfield[@code='a']]|marc:datafield[@tag=485 ][marc:subfield[@code='v']]">
4187 <xsl:call-template name="variantTypeAttribute"/>
4188 <xsl:call-template name="genre"/>
4192 <xsl:template match="marc:datafield[@tag=555]">
4194 <xsl:call-template name="relatedTypeAttribute"/>
4195 <xsl:call-template name="uri"/>
4196 <xsl:call-template name="genre"/>
4201 match="marc:datafield[@tag=555 or @tag=755][marc:subfield[@code='a']]|marc:datafield[@tag=585][marc:subfield[@code='v']]">
4203 <xsl:call-template name="relatedTypeAttribute"/>
4204 <xsl:call-template name="genre"/>
4207 <xsl:template name="genre">
4209 <xsl:if test="@tag=555">
4210 <xsl:value-of select="marc:subfield[@code='a']"/>
4212 <xsl:call-template name="setAuthority"/>
4213 <xsl:call-template name="chopPunctuation">
4214 <xsl:with-param name="chopString">
4217 <xsl:when test="@tag='555'"/>
4218 <xsl:when test="@tag=185 or @tag=485 or @tag=585">
4219 <xsl:apply-templates select="marc:subfield[@code='v']"/>
4222 <xsl:value-of select="marc:subfield[@code='a']"/>
4226 </xsl:call-template>
4228 <xsl:apply-templates/>
4231 <!-- ========= geographic ========== -->
4232 <xsl:template match="marc:subfield[@code='z']">
4234 <xsl:call-template name="chopPunctuation">
4235 <xsl:with-param name="chopString">
4236 <xsl:value-of select="."/>
4238 </xsl:call-template>
4241 <xsl:template name="geographic">
4244 <xsl:call-template name="setAuthority"/>
4246 <xsl:if test="@tag=151 or @tag=551">
4247 <xsl:value-of select="marc:subfield[@code='a']"/>
4249 <xsl:call-template name="chopPunctuation">
4250 <xsl:with-param name="chopString">
4251 <xsl:if test="@tag=181 or @tag=481 or @tag=581">
4252 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4256 <xsl:when test="@tag=181 or @tag=481 or @tag=581">
4257 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4261 <xsl:value-of select="marc:subfield[@code='a']"/>
4266 </xsl:call-template>
4268 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4271 match="marc:datafield[@tag=151][marc:subfield[@code='a']]|marc:datafield[@tag=181][marc:subfield[@code='z']]">
4272 <xsl:call-template name="geographic"/>
4275 match="marc:datafield[@tag=451][marc:subfield[@code='a']]|marc:datafield[@tag=481][marc:subfield[@code='z']]">
4277 <xsl:call-template name="variantTypeAttribute"/>
4278 <xsl:call-template name="geographic"/>
4282 match="marc:datafield[@tag=551]|marc:datafield[@tag=581][marc:subfield[@code='z']]">
4284 <xsl:call-template name="relatedTypeAttribute"/>
4285 <!-- <xsl:call-template name="uri"/> -->
4286 <xsl:call-template name="geographic"/>
4289 <xsl:template match="marc:datafield[@tag=580]">
4291 <xsl:call-template name="relatedTypeAttribute"/>
4292 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4296 match="marc:datafield[@tag=751][marc:subfield[@code='z']]|marc:datafield[@tag=781][marc:subfield[@code='z']]">
4298 <xsl:call-template name="relatedTypeAttribute"/>
4299 <xsl:call-template name="geographic"/>
4302 <xsl:template match="marc:datafield[@tag=755]">
4304 <xsl:call-template name="relatedTypeAttribute"/>
4305 <xsl:call-template name="genre"/>
4306 <xsl:call-template name="setAuthority"/>
4307 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4310 <xsl:template match="marc:datafield[@tag=780]">
4312 <xsl:call-template name="relatedTypeAttribute"/>
4313 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4316 <xsl:template match="marc:datafield[@tag=785]">
4318 <xsl:call-template name="relatedTypeAttribute"/>
4319 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4323 <!-- ========== notes ========== -->
4324 <xsl:template match="marc:datafield[667 <= @tag and @tag <= 688]">
4327 <xsl:when test="@tag=667">
4328 <xsl:attribute name="type">nonpublic</xsl:attribute>
4330 <xsl:when test="@tag=670">
4331 <xsl:attribute name="type">source</xsl:attribute>
4333 <xsl:when test="@tag=675">
4334 <xsl:attribute name="type">notFound</xsl:attribute>
4336 <xsl:when test="@tag=678">
4337 <xsl:attribute name="type">history</xsl:attribute>
4339 <xsl:when test="@tag=681">
4340 <xsl:attribute name="type">subject example</xsl:attribute>
4342 <xsl:when test="@tag=682">
4343 <xsl:attribute name="type">deleted heading information</xsl:attribute>
4345 <xsl:when test="@tag=688">
4346 <xsl:attribute name="type">application history</xsl:attribute>
4349 <xsl:call-template name="chopPunctuation">
4350 <xsl:with-param name="chopString">
4352 <xsl:when test="@tag=667 or @tag=675">
4353 <xsl:value-of select="marc:subfield[@code='a']"/>
4355 <xsl:when test="@tag=670 or @tag=678">
4356 <xsl:call-template name="subfieldSelect">
4357 <xsl:with-param name="codes">ab</xsl:with-param>
4358 </xsl:call-template>
4360 <xsl:when test="680 <= @tag and @tag <=688">
4361 <xsl:call-template name="subfieldSelect">
4362 <xsl:with-param name="codes">ai</xsl:with-param>
4363 </xsl:call-template>
4367 </xsl:call-template>
4371 <!-- ========== url ========== -->
4372 <xsl:template match="marc:datafield[@tag=856][marc:subfield[@code='u']]">
4374 <xsl:if test="marc:subfield[@code='z' or @code='3']">
4375 <xsl:attribute name="displayLabel">
4376 <xsl:call-template name="subfieldSelect">
4377 <xsl:with-param name="codes">z3</xsl:with-param>
4378 </xsl:call-template>
4381 <xsl:value-of select="marc:subfield[@code='u']"/>
4385 <xsl:template name="relatedTypeAttribute">
4388 test="@tag=500 or @tag=510 or @tag=511 or @tag=548 or @tag=550 or @tag=551 or @tag=555 or @tag=580 or @tag=581 or @tag=582 or @tag=585">
4389 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='a'">
4390 <xsl:attribute name="type">earlier</xsl:attribute>
4392 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='b'">
4393 <xsl:attribute name="type">later</xsl:attribute>
4395 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='t'">
4396 <xsl:attribute name="type">parentOrg</xsl:attribute>
4398 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='g'">
4399 <xsl:attribute name="type">broader</xsl:attribute>
4401 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='h'">
4402 <xsl:attribute name="type">narrower</xsl:attribute>
4404 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='r'">
4405 <xsl:attribute name="type">other</xsl:attribute>
4407 <xsl:if test="contains('fin|', substring(marc:subfield[@code='w'],1,1))">
4408 <xsl:attribute name="type">other</xsl:attribute>
4411 <xsl:when test="@tag=530 or @tag=730">
4412 <xsl:attribute name="type">other</xsl:attribute>
4416 <xsl:attribute name="type">equivalent</xsl:attribute>
4419 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4424 <xsl:template name="variantTypeAttribute">
4427 test="@tag=400 or @tag=410 or @tag=411 or @tag=451 or @tag=455 or @tag=480 or @tag=481 or @tag=482 or @tag=485">
4428 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='d'">
4429 <xsl:attribute name="type">acronym</xsl:attribute>
4431 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='n'">
4432 <xsl:attribute name="type">other</xsl:attribute>
4434 <xsl:if test="contains('fit', substring(marc:subfield[@code='w'],1,1))">
4435 <xsl:attribute name="type">other</xsl:attribute>
4440 <xsl:attribute name="type">other</xsl:attribute>
4443 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4446 <xsl:template name="setAuthority">
4448 <!-- can be called from the datafield or subfield level, so "..//@tag" means
4449 the tag can be at the subfield's parent level or at the datafields own level -->
4452 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='a'">
4453 <xsl:attribute name="authority">
4454 <xsl:text>naf</xsl:text>
4458 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='b'">
4459 <xsl:attribute name="authority">
4460 <xsl:text>lcsh</xsl:text>
4464 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='k'">
4465 <xsl:attribute name="authority">
4466 <xsl:text>lacnaf</xsl:text>
4470 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='a' and $controlField008-14='b'">
4471 <xsl:attribute name="authority">
4472 <xsl:text>lcsh</xsl:text>
4476 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='k' and $controlField008-14='b'">
4477 <xsl:attribute name="authority">cash</xsl:attribute>
4480 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='a'">
4481 <xsl:attribute name="authority">naf</xsl:attribute>
4484 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='b'">
4485 <xsl:attribute name="authority">lcsh</xsl:attribute>
4488 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='a'">
4489 <xsl:attribute name="authority">
4490 <xsl:text>lacnaf</xsl:text>
4494 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='b'">
4495 <xsl:attribute name="authority">
4496 <xsl:text>cash</xsl:text>
4500 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='b'">
4501 <xsl:attribute name="authority">
4502 <xsl:text>lcshcl</xsl:text>
4506 test="(ancestor-or-self::marc:datafield/@tag=100 or ancestor-or-self::marc:datafield/@tag=110 or ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130 or ancestor-or-self::marc:datafield/@tag=151) and $controlField008-11='c'">
4507 <xsl:attribute name="authority">
4508 <xsl:text>nlmnaf</xsl:text>
4512 test="(ancestor-or-self::marc:datafield/@tag=100 or ancestor-or-self::marc:datafield/@tag=110 or ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130 or ancestor-or-self::marc:datafield/@tag=151) and $controlField008-11='d'">
4513 <xsl:attribute name="authority">
4514 <xsl:text>nalnaf</xsl:text>
4518 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='r'">
4519 <xsl:attribute name="authority">
4520 <xsl:text>aat</xsl:text>
4524 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='s'">
4525 <xsl:attribute name="authority">sears</xsl:attribute>
4528 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='v'">
4529 <xsl:attribute name="authority">rvm</xsl:attribute>
4532 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='z'">
4533 <xsl:attribute name="authority">
4535 select="../marc:datafield[ancestor-or-self::marc:datafield/@tag=040]/marc:subfield[@code='f']"
4540 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='a'">
4541 <xsl:attribute name="authority">
4542 <xsl:text>naf</xsl:text>
4546 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='b'">
4547 <xsl:attribute name="authority">
4548 <xsl:text>lcsh</xsl:text>
4552 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='k' ">
4553 <xsl:attribute name="authority">
4554 <xsl:text>lacnaf</xsl:text>
4558 test="(ancestor-or-self::marc:datafield/@tag=148 or ancestor-or-self::marc:datafield/@tag=150 or ancestor-or-self::marc:datafield/@tag=155) and $controlField008-11='a' ">
4559 <xsl:attribute name="authority">
4560 <xsl:text>lcsh</xsl:text>
4564 test="(ancestor-or-self::marc:datafield/@tag=148 or ancestor-or-self::marc:datafield/@tag=150 or ancestor-or-self::marc:datafield/@tag=155) and $controlField008-11='a' ">
4565 <xsl:attribute name="authority">
4566 <xsl:text>lcsh</xsl:text>
4570 test="(ancestor-or-self::marc:datafield/@tag=148 or ancestor-or-self::marc:datafield/@tag=150 or ancestor-or-self::marc:datafield/@tag=155) and $controlField008-11='c' ">
4571 <xsl:attribute name="authority">
4572 <xsl:text>mesh</xsl:text>
4576 test="(ancestor-or-self::marc:datafield/@tag=148 or ancestor-or-self::marc:datafield/@tag=150 or ancestor-or-self::marc:datafield/@tag=155) and $controlField008-11='d' ">
4577 <xsl:attribute name="authority">
4578 <xsl:text>nal</xsl:text>
4582 test="(ancestor-or-self::marc:datafield/@tag=148 or ancestor-or-self::marc:datafield/@tag=150 or ancestor-or-self::marc:datafield/@tag=155) and $controlField008-11='k' ">
4583 <xsl:attribute name="authority">
4584 <xsl:text>cash</xsl:text>
4588 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='a'">
4589 <xsl:attribute name="authority">
4590 <xsl:text>naf</xsl:text>
4594 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='b'">
4595 <xsl:attribute name="authority">lcsh</xsl:attribute>
4598 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='a'">
4599 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4602 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='b'">
4603 <xsl:attribute name="authority">cash</xsl:attribute>
4606 test="(..//ancestor-or-self::marc:datafield/@tag=180 or ..//ancestor-or-self::marc:datafield/@tag=181 or ..//ancestor-or-self::marc:datafield/@tag=182 or ..//ancestor-or-self::marc:datafield/@tag=185) and $controlField008-11='a'">
4607 <xsl:attribute name="authority">lcsh</xsl:attribute>
4610 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='0'">
4611 <xsl:attribute name="authority">naf</xsl:attribute>
4614 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='5'">
4615 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4617 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='0'">
4618 <xsl:attribute name="authority">lcsh</xsl:attribute>
4620 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='5'">
4621 <xsl:attribute name="authority">cash</xsl:attribute>
4624 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='1'">
4625 <xsl:attribute name="authority">lcshcl</xsl:attribute>
4628 test="(ancestor-or-self::marc:datafield/@tag=700 or ancestor-or-self::marc:datafield/@tag=710 or ancestor-or-self::marc:datafield/@tag=711 or ancestor-or-self::marc:datafield/@tag=730 or ancestor-or-self::marc:datafield/@tag=751) and @ind2='2'">
4629 <xsl:attribute name="authority">nlmnaf</xsl:attribute>
4632 test="(ancestor-or-self::marc:datafield/@tag=700 or ancestor-or-self::marc:datafield/@tag=710 or ancestor-or-self::marc:datafield/@tag=711 or ancestor-or-self::marc:datafield/@tag=730 or ancestor-or-self::marc:datafield/@tag=751) and @ind2='3'">
4633 <xsl:attribute name="authority">nalnaf</xsl:attribute>
4636 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='6'">
4637 <xsl:attribute name="authority">rvm</xsl:attribute>
4640 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='7'">
4641 <xsl:attribute name="authority">
4642 <xsl:value-of select="marc:subfield[@code='2']"/>
4646 test="(ancestor-or-self::marc:datafield/@tag=710 or ancestor-or-self::marc:datafield/@tag=711 or ancestor-or-self::marc:datafield/@tag=730 or ancestor-or-self::marc:datafield/@tag=751) and @ind2='5'">
4647 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4650 test="(ancestor-or-self::marc:datafield/@tag=710 or ancestor-or-self::marc:datafield/@tag=711 or ancestor-or-self::marc:datafield/@tag=730 or ancestor-or-self::marc:datafield/@tag=751) and @ind2='0'">
4651 <xsl:attribute name="authority">naf</xsl:attribute>
4654 test="(ancestor-or-self::marc:datafield/@tag=748 or ancestor-or-self::marc:datafield/@tag=750 or ancestor-or-self::marc:datafield/@tag=755) and @ind2='0'">
4655 <xsl:attribute name="authority">lcsh</xsl:attribute>
4658 test="(ancestor-or-self::marc:datafield/@tag=748 or ancestor-or-self::marc:datafield/@tag=750 or ancestor-or-self::marc:datafield/@tag=755) and @ind2='2'">
4659 <xsl:attribute name="authority">mesh</xsl:attribute>
4662 test="(ancestor-or-self::marc:datafield/@tag=748 or ancestor-or-self::marc:datafield/@tag=750 or ancestor-or-self::marc:datafield/@tag=755) and @ind2='3'">
4663 <xsl:attribute name="authority">nal</xsl:attribute>
4666 test="(ancestor-or-self::marc:datafield/@tag=748 or ancestor-or-self::marc:datafield/@tag=750 or ancestor-or-self::marc:datafield/@tag=755) and @ind2='5'">
4667 <xsl:attribute name="authority">cash</xsl:attribute>
4671 <xsl:template match="*"/>
4672 </xsl:stylesheet>$XSLT$);
4675 SELECT evergreen.upgrade_deps_block_check('1069', :eg_version); --gmcharlt/kmlussier
4677 -- subset of types listed in https://www.loc.gov/marc/authority/ad1xx3xx.html
4678 -- for now, ignoring subdivisions
4679 CREATE TYPE authority.heading_type AS ENUM (
4685 'chronological_term',
4689 'medium_of_performance_term'
4692 CREATE TYPE authority.variant_heading_type AS ENUM (
4701 CREATE TYPE authority.related_heading_type AS ENUM (
4704 'parent organization',
4711 CREATE TYPE authority.heading_purpose AS ENUM (
4717 CREATE TABLE authority.heading_field (
4718 id SERIAL PRIMARY KEY,
4719 heading_type authority.heading_type NOT NULL,
4720 heading_purpose authority.heading_purpose NOT NULL,
4721 label TEXT NOT NULL,
4722 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mads21',
4723 heading_xpath TEXT NOT NULL,
4724 component_xpath TEXT NOT NULL,
4725 type_xpath TEXT NULL, -- to extract related or variant type
4726 thesaurus_xpath TEXT NULL,
4727 thesaurus_override_xpath TEXT NULL,
4731 CREATE TABLE authority.heading_field_norm_map (
4732 id SERIAL PRIMARY KEY,
4733 field INT NOT NULL REFERENCES authority.heading_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
4734 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
4736 pos INT NOT NULL DEFAULT 0
4739 INSERT INTO authority.heading_field(heading_type, heading_purpose, label, heading_xpath, component_xpath, type_xpath, thesaurus_xpath, thesaurus_override_xpath) VALUES
4740 ( 'topical_term', 'main', 'Main Topical Term', '/mads21:mads/mads21:authority', '//mads21:topic', NULL, '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', NULL )
4741 ,( 'topical_term', 'variant', 'Variant Topical Term', '/mads21:mads/mads21:variant', '//mads21:topic', '/mads21:variant/@type', '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', '//mads21:topic[1]/@authority')
4742 ,( 'topical_term', 'related', 'Related Topical Term', '/mads21:mads/mads21:related', '//mads21:topic', '/mads21:related/@type', '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', '//mads21:topic[1]/@authority')
4743 ,( 'personal_name', 'main', 'Main Personal Name', '/mads21:mads/mads21:authority', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
4744 ,( 'personal_name', 'variant', 'Variant Personal Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
4745 ,( 'personal_name', 'related', 'Related Personal Name', '/mads21:mads/mads21:related', '//mads21:name[@type="personal"]', '/mads21:related/@type', NULL, NULL )
4746 ,( 'corporate_name', 'main', 'Main Corporate name', '/mads21:mads/mads21:authority', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
4747 ,( 'corporate_name', 'variant', 'Variant Corporate Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
4748 ,( 'corporate_name', 'related', 'Related Corporate Name', '/mads21:mads/mads21:related', '//mads21:name[@type="corporate"]', '/mads21:related/@type', NULL, NULL )
4749 ,( 'meeting_name', 'main', 'Main Meeting name', '/mads21:mads/mads21:authority', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
4750 ,( 'meeting_name', 'variant', 'Variant Meeting Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
4751 ,( 'meeting_name', 'related', 'Related Meeting Name', '/mads21:mads/mads21:related', '//mads21:name[@type="meeting"]', '/mads21:related/@type', NULL, NULL )
4752 ,( 'geographic_name', 'main', 'Main Geographic Term', '/mads21:mads/mads21:authority', '//mads21:geographic', NULL, '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', NULL )
4753 ,( 'geographic_name', 'variant', 'Variant Geographic Term', '/mads21:mads/mads21:variant', '//mads21:geographic', '/mads21:variant/@type', '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', '//mads21:geographic[1]/@authority')
4754 ,( 'geographic_name', 'related', 'Related Geographic Term', '/mads21:mads/mads21:related', '//mads21:geographic', '/mads21:related/@type', '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', '//mads21:geographic[1]/@authority')
4755 ,( 'genre_form_term', 'main', 'Main Genre/Form Term', '/mads21:mads/mads21:authority', '//mads21:genre', NULL, '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', NULL )
4756 ,( 'genre_form_term', 'variant', 'Variant Genre/Form Term', '/mads21:mads/mads21:variant', '//mads21:genre', '/mads21:variant/@type', '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', '//mads21:genre[1]/@authority')
4757 ,( 'genre_form_term', 'related', 'Related Genre/Form Term', '/mads21:mads/mads21:related', '//mads21:genre', '/mads21:related/@type', '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', '//mads21:genre[1]/@authority')
4758 ,( 'chronological_term', 'main', 'Main Chronological Term', '/mads21:mads/mads21:authority', '//mads21:temporal', NULL, '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', NULL )
4759 ,( 'chronological_term', 'variant', 'Variant Chronological Term', '/mads21:mads/mads21:variant', '//mads21:temporal', '/mads21:variant/@type', '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', '//mads21:temporal[1]/@authority')
4760 ,( 'chronological_term', 'related', 'Related Chronological Term', '/mads21:mads/mads21:related', '//mads21:temporal', '/mads21:related/@type', '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', '//mads21:temporal[1]/@authority')
4761 ,( 'uniform_title', 'main', 'Main Uniform Title', '/mads21:mads/mads21:authority', '//mads21:title', NULL, '/mads21:mads/mads21:authority/mads21:title[1]/@authority', NULL )
4762 ,( 'uniform_title', 'variant', 'Variant Uniform Title', '/mads21:mads/mads21:variant', '//mads21:title', '/mads21:variant/@type', '/mads21:mads/mads21:authority/mads21:title[1]/@authority', '//mads21:title[1]/@authority')
4763 ,( 'uniform_title', 'related', 'Related Uniform Title', '/mads21:mads/mads21:related', '//mads21:title', '/mads21:related/@type', '/mads21:mads/mads21:authority/mads21:title[1]/@authority', '//mads21:title[1]/@authority')
4766 -- NACO normalize all the things
4767 INSERT INTO authority.heading_field_norm_map (field, norm, pos)
4769 FROM authority.heading_field;
4771 CREATE TYPE authority.heading AS (
4773 type authority.heading_type,
4774 purpose authority.heading_purpose,
4775 variant_type authority.variant_heading_type,
4776 related_type authority.related_heading_type,
4779 normalized_heading TEXT
4782 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
4784 idx authority.heading_field%ROWTYPE;
4785 xfrm config.xml_transform%ROWTYPE;
4787 transformed_xml TEXT;
4789 heading_node_list TEXT[];
4790 component_node TEXT;
4791 component_node_list TEXT[];
4793 normalized_text TEXT;
4798 base_thesaurus TEXT := NULL;
4799 output_row authority.heading;
4802 -- Loop over the indexing entries
4803 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
4805 output_row.field := idx.id;
4806 output_row.type := idx.heading_type;
4807 output_row.purpose := idx.heading_purpose;
4809 joiner := COALESCE(idx.joiner, ' ');
4811 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
4813 -- See if we can skip the XSLT ... it's expensive
4814 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
4815 -- Can't skip the transform
4816 IF xfrm.xslt <> '---' THEN
4817 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
4819 transformed_xml := marc;
4822 prev_xfrm := xfrm.name;
4825 IF idx.thesaurus_xpath IS NOT NULL THEN
4826 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
4829 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
4831 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
4833 CONTINUE WHEN heading_node !~ E'^\\s*<';
4835 output_row.variant_type := NULL;
4836 output_row.related_type := NULL;
4837 output_row.thesaurus := NULL;
4838 output_row.heading := NULL;
4840 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
4841 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
4843 output_row.variant_type := type_value;
4844 EXCEPTION WHEN invalid_text_representation THEN
4845 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
4848 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
4849 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
4851 output_row.related_type := type_value;
4852 EXCEPTION WHEN invalid_text_representation THEN
4853 RAISE NOTICE 'Do not recognize related heading type %', type_value;
4857 IF idx.thesaurus_override_xpath IS NOT NULL THEN
4858 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
4860 IF output_row.thesaurus IS NULL THEN
4861 output_row.thesaurus := base_thesaurus;
4866 -- now iterate over components of heading
4867 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
4868 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
4869 -- XXX much of this should be moved into oils_xpath_string...
4870 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
4871 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
4872 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
4873 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
4877 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
4879 IF raw_text IS NOT NULL THEN
4880 raw_text := raw_text || joiner;
4883 raw_text := COALESCE(raw_text,'') || curr_text;
4886 IF raw_text IS NOT NULL THEN
4887 output_row.heading := raw_text;
4888 normalized_text := raw_text;
4891 SELECT n.func AS func,
4892 n.param_count AS param_count,
4894 FROM config.index_normalizer n
4895 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
4896 WHERE m.field = idx.id
4899 EXECUTE 'SELECT ' || normalizer.func || '(' ||
4900 quote_literal( normalized_text ) ||
4902 WHEN normalizer.param_count > 0
4903 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
4906 ')' INTO normalized_text;
4910 output_row.normalized_heading := normalized_text;
4912 RETURN NEXT output_row;
4918 $func$ LANGUAGE PLPGSQL;
4920 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
4922 auth authority.record_entry%ROWTYPE;
4923 output_row authority.heading;
4926 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
4928 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
4930 $func$ LANGUAGE PLPGSQL;
4932 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
4934 res authority.simple_heading%ROWTYPE;
4935 acsaf authority.control_set_authority_field%ROWTYPE;
4936 heading_row authority.heading%ROWTYPE;
4947 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
4950 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
4952 IF cset IS NULL THEN
4953 SELECT control_set INTO cset
4954 FROM authority.control_set_authority_field
4955 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
4959 res.record := auth_id;
4960 res.thesaurus := authority.extract_thesaurus(marcxml);
4962 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
4963 res.atag := acsaf.id;
4965 IF acsaf.heading_field IS NULL THEN
4966 tag_used := acsaf.tag;
4967 nfi_used := acsaf.nfi;
4968 joiner_text := COALESCE(acsaf.joiner, ' ');
4970 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
4972 heading_text := COALESCE(
4973 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
4977 IF nfi_used IS NOT NULL THEN
4979 sort_text := SUBSTRING(
4984 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
4996 sort_text := heading_text;
4999 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5000 res.value := heading_text;
5001 res.sort_value := public.naco_normalize(sort_text);
5002 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5008 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
5009 res.value := heading_row.heading;
5010 res.sort_value := heading_row.normalized_heading;
5011 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5019 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5021 ALTER TABLE authority.control_set_authority_field ADD COLUMN heading_field INTEGER REFERENCES authority.heading_field(id);
5023 UPDATE authority.control_set_authority_field acsaf
5024 SET heading_field = ahf.id
5025 FROM authority.heading_field ahf
5028 AND ahf.heading_purpose = 'main'
5029 AND ahf.heading_type = 'personal_name';
5030 UPDATE authority.control_set_authority_field acsaf
5031 SET heading_field = ahf.id
5032 FROM authority.heading_field ahf
5035 AND ahf.heading_purpose = 'variant'
5036 AND ahf.heading_type = 'personal_name';
5037 UPDATE authority.control_set_authority_field acsaf
5038 SET heading_field = ahf.id
5039 FROM authority.heading_field ahf
5042 AND ahf.heading_purpose = 'related'
5043 AND ahf.heading_type = 'personal_name';
5045 UPDATE authority.control_set_authority_field acsaf
5046 SET heading_field = ahf.id
5047 FROM authority.heading_field ahf
5050 AND ahf.heading_purpose = 'main'
5051 AND ahf.heading_type = 'corporate_name';
5052 UPDATE authority.control_set_authority_field acsaf
5053 SET heading_field = ahf.id
5054 FROM authority.heading_field ahf
5057 AND ahf.heading_purpose = 'variant'
5058 AND ahf.heading_type = 'corporate_name';
5059 UPDATE authority.control_set_authority_field acsaf
5060 SET heading_field = ahf.id
5061 FROM authority.heading_field ahf
5064 AND ahf.heading_purpose = 'related'
5065 AND ahf.heading_type = 'corporate_name';
5067 UPDATE authority.control_set_authority_field acsaf
5068 SET heading_field = ahf.id
5069 FROM authority.heading_field ahf
5072 AND ahf.heading_purpose = 'main'
5073 AND ahf.heading_type = 'meeting_name';
5074 UPDATE authority.control_set_authority_field acsaf
5075 SET heading_field = ahf.id
5076 FROM authority.heading_field ahf
5079 AND ahf.heading_purpose = 'variant'
5080 AND ahf.heading_type = 'meeting_name';
5081 UPDATE authority.control_set_authority_field acsaf
5082 SET heading_field = ahf.id
5083 FROM authority.heading_field ahf
5086 AND ahf.heading_purpose = 'related'
5087 AND ahf.heading_type = 'meeting_name';
5089 UPDATE authority.control_set_authority_field acsaf
5090 SET heading_field = ahf.id
5091 FROM authority.heading_field ahf
5094 AND ahf.heading_purpose = 'main'
5095 AND ahf.heading_type = 'uniform_title';
5096 UPDATE authority.control_set_authority_field acsaf
5097 SET heading_field = ahf.id
5098 FROM authority.heading_field ahf
5101 AND ahf.heading_purpose = 'variant'
5102 AND ahf.heading_type = 'uniform_title';
5103 UPDATE authority.control_set_authority_field acsaf
5104 SET heading_field = ahf.id
5105 FROM authority.heading_field ahf
5108 AND ahf.heading_purpose = 'related'
5109 AND ahf.heading_type = 'uniform_title';
5111 UPDATE authority.control_set_authority_field acsaf
5112 SET heading_field = ahf.id
5113 FROM authority.heading_field ahf
5116 AND ahf.heading_purpose = 'main'
5117 AND ahf.heading_type = 'topical_term';
5118 UPDATE authority.control_set_authority_field acsaf
5119 SET heading_field = ahf.id
5120 FROM authority.heading_field ahf
5123 AND ahf.heading_purpose = 'variant'
5124 AND ahf.heading_type = 'topical_term';
5125 UPDATE authority.control_set_authority_field acsaf
5126 SET heading_field = ahf.id
5127 FROM authority.heading_field ahf
5130 AND ahf.heading_purpose = 'related'
5131 AND ahf.heading_type = 'topical_term';
5133 UPDATE authority.control_set_authority_field acsaf
5134 SET heading_field = ahf.id
5135 FROM authority.heading_field ahf
5138 AND ahf.heading_purpose = 'main'
5139 AND ahf.heading_type = 'geographic_name';
5140 UPDATE authority.control_set_authority_field acsaf
5141 SET heading_field = ahf.id
5142 FROM authority.heading_field ahf
5145 AND ahf.heading_purpose = 'variant'
5146 AND ahf.heading_type = 'geographic_name';
5147 UPDATE authority.control_set_authority_field acsaf
5148 SET heading_field = ahf.id
5149 FROM authority.heading_field ahf
5152 AND ahf.heading_purpose = 'related'
5153 AND ahf.heading_type = 'geographic_name';
5155 UPDATE authority.control_set_authority_field acsaf
5156 SET heading_field = ahf.id
5157 FROM authority.heading_field ahf
5160 AND ahf.heading_purpose = 'main'
5161 AND ahf.heading_type = 'genre_form_term';
5162 UPDATE authority.control_set_authority_field acsaf
5163 SET heading_field = ahf.id
5164 FROM authority.heading_field ahf
5167 AND ahf.heading_purpose = 'variant'
5168 AND ahf.heading_type = 'genre_form_term';
5169 UPDATE authority.control_set_authority_field acsaf
5170 SET heading_field = ahf.id
5171 FROM authority.heading_field ahf
5174 AND ahf.heading_purpose = 'related'
5175 AND ahf.heading_type = 'genre_form_term';
5178 SELECT evergreen.upgrade_deps_block_check('1070', :eg_version); --miker/gmcharlt/kmlussier
5180 CREATE TRIGGER thes_code_tracking_trigger
5181 AFTER UPDATE ON authority.thesaurus
5182 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
5184 ALTER TABLE authority.thesaurus ADD COLUMN short_code TEXT, ADD COLUMN uri TEXT;
5186 DELETE FROM authority.thesaurus WHERE control_set = 1 AND code NOT IN ('n',' ','|');
5187 UPDATE authority.thesaurus SET short_code = code;
5189 CREATE TEMP TABLE thesauri (code text, uri text, name text, xlate hstore);
5190 COPY thesauri (code, uri, name, xlate) FROM STDIN;
5191 migfg http://id.loc.gov/vocabulary/genreFormSchemes/migfg Moving image genre-form guide
5192 reveal http://id.loc.gov/vocabulary/genreFormSchemes/reveal REVEAL: fiction indexing and genre headings
5193 dct http://id.loc.gov/vocabulary/genreFormSchemes/dct Dublin Core list of resource types
5194 gmgpc http://id.loc.gov/vocabulary/genreFormSchemes/gmgpc Thesaurus for graphic materials: TGM II, Genre and physical characteristic terms
5195 rbgenr http://id.loc.gov/vocabulary/genreFormSchemes/rbgenr Genre terms: a thesaurus for use in rare book and special collections cataloguing
5196 sgp http://id.loc.gov/vocabulary/genreFormSchemes/sgp Svenska genrebeteckningar fr periodika "sv"=>"Svenska genrebeteckningar fr periodika"
5197 estc http://id.loc.gov/vocabulary/genreFormSchemes/estc Eighteenth century short title catalogue, the cataloguing rules. New ed.
5198 ftamc http://id.loc.gov/vocabulary/genreFormSchemes/ftamc Form terms for archival and manuscripts control
5199 alett http://id.loc.gov/vocabulary/genreFormSchemes/alett An alphabetical list of English text types
5200 gtlm http://id.loc.gov/vocabulary/genreFormSchemes/gtlm Genre terms for law materials: a thesaurus
5201 rbprov http://id.loc.gov/vocabulary/genreFormSchemes/rbprov Provenance evidence: a thesaurus for use in rare book and special collections cataloging
5202 rbbin http://id.loc.gov/vocabulary/genreFormSchemes/rbbin Binding terms: a thesaurus for use in rare book and special collections cataloguing
5203 fbg http://id.loc.gov/vocabulary/genreFormSchemes/fbg Films by genre /dd>
5204 isbdmedia http://id.loc.gov/vocabulary/genreFormSchemes/isbdmedia ISBD Area 0 [media]
5205 marccategory http://id.loc.gov/vocabulary/genreFormSchemes/marccategory MARC form category term list
5206 gnd-music http://id.loc.gov/vocabulary/genreFormSchemes/gnd-music Gemeinsame Normdatei: Musikalische Ausgabeform
5207 proysen http://id.loc.gov/vocabulary/genreFormSchemes/proysen Prøysen: emneord for Prøysen-bibliografien
5208 rdacarrier http://id.loc.gov/vocabulary/genreFormSchemes/rdacarrier Term and code list for RDA carrier types
5209 gnd http://id.loc.gov/vocabulary/genreFormSchemes/gnd Gemeinsame Normdatei
5210 cjh http://id.loc.gov/vocabulary/genreFormSchemes/cjh Center for Jewish History thesaurus
5211 rbpri http://id.loc.gov/vocabulary/genreFormSchemes/rbpri Printing & publishing evidence: a thesaurus for use in rare book and special collections cataloging
5212 fgtpcm http://id.loc.gov/vocabulary/genreFormSchemes/fgtpcm Form/genre terms for printed cartoon material
5213 rbpub http://id.loc.gov/vocabulary/genreFormSchemes/rbpub Printing and publishing evidence: a thesaurus for use in rare book and special collections cataloging
5214 gmd http://id.loc.gov/vocabulary/genreFormSchemes/gmd Anglo-American Cataloguing Rules general material designation
5215 rbpap http://id.loc.gov/vocabulary/genreFormSchemes/rbpap Paper terms: a thesaurus for use in rare book and special collections cataloging
5216 rdamedia http://id.loc.gov/vocabulary/genreFormSchemes/rdamedia Term and code list for RDA media types
5217 marcsmd http://id.loc.gov/vocabulary/genreFormSchemes/marcsmd MARC specific material form term list
5218 saogf http://id.loc.gov/vocabulary/genreFormSchemes/saogf Svenska ämnesord - Genre/Form "sv"=>"Svenska ämnesord - Genre/Form"
5219 lcgft http://id.loc.gov/vocabulary/genreFormSchemes/lcgft Library of Congress genre/form terms for library and archival materials
5220 muzeukv http://id.loc.gov/vocabulary/genreFormSchemes/muzeukv MuzeVideo UK DVD and UMD film genre classification
5221 mim http://id.loc.gov/vocabulary/genreFormSchemes/mim Moving image materials: genre terms
5222 nmc http://id.loc.gov/vocabulary/genreFormSchemes/nmc Revised nomenclature for museum cataloging: a revised and expanded version of Robert C. Chenhall's system for classifying man-made objects
5223 gnd-content http://id.loc.gov/vocabulary/genreFormSchemes/gnd-content Gemeinsame Normdatei: Beschreibung des Inhalts
5224 bgtchm http://id.loc.gov/vocabulary/genreFormSchemes/bgtchm Basic genre terms for cultural heritage materials
5225 gsafd http://id.loc.gov/vocabulary/genreFormSchemes/gsafd Guidelines on subject access to individual works of fiction, drama, etc
5226 marcform http://id.loc.gov/vocabulary/genreFormSchemes/marcform MARC form of item term list
5227 marcgt http://id.loc.gov/vocabulary/genreFormSchemes/marcgt MARC genre terms
5228 barngf http://id.loc.gov/vocabulary/genreFormSchemes/barngf Svenska ämnesord för barn - Genre/Form "sv"=>"Svenska ämnesord för barn - Genre/Form"
5229 ngl http://id.loc.gov/vocabulary/genreFormSchemes/ngl Newspaper genre list
5230 rvmgf http://id.loc.gov/vocabulary/genreFormSchemes/rvmgf Thésaurus des descripteurs de genre/forme de l'Université Laval "fr"=>"Thésaurus des descripteurs de genre/forme de l'Université Laval"
5231 tgfbne http://id.loc.gov/vocabulary/genreFormSchemes/tgfbne Términos de género/forma de la Biblioteca Nacional de España
5232 nbdbgf http://id.loc.gov/vocabulary/genreFormSchemes/nbdbgf NBD Biblion Genres Fictie
5233 rbtyp http://id.loc.gov/vocabulary/genreFormSchemes/rbtyp Type evidence: a thesaurus for use in rare book and special collections cataloging
5234 radfg http://id.loc.gov/vocabulary/genreFormSchemes/radfg Radio form / genre terms guide
5235 gnd-carrier http://id.loc.gov/vocabulary/genreFormSchemes/gnd-carrier Gemeinsame Normdatei: Datenträgertyp
5236 gatbeg http://id.loc.gov/vocabulary/genreFormSchemes/gatbeg Gattungsbegriffe "de"=>"Gattungsbegriffe"
5237 rdacontent http://id.loc.gov/vocabulary/genreFormSchemes/rdacontent Term and code list for RDA content types
5238 isbdcontent http://id.loc.gov/vocabulary/genreFormSchemes/isbdcontent ISBD Area 0 [content]
5239 nimafc http://id.loc.gov/vocabulary/genreFormSchemes/nimafc NIMA form codes
5240 amg http://id.loc.gov/vocabulary/genreFormSchemes/amg Audiovisual material glossary
5241 local http://id.loc.gov/vocabulary/subjectSchemes/local Locally assigned term
5242 taika http://id.loc.gov/vocabulary/subjectSchemes/taika Taideteollisuuden asiasanasto "fi"=>"Taideteollisuuden asiasanasto"
5243 nasat http://id.loc.gov/vocabulary/subjectSchemes/nasat NASA thesaurus
5244 rswkaf http://id.loc.gov/vocabulary/subjectSchemes/rswkaf Alternativform zum Hauptschlagwort "de"=>"Alternativform zum Hauptschlagwort"
5245 jhpk http://id.loc.gov/vocabulary/subjectSchemes/jhpk JÄ
\99zyk haseÅ
\82 przedmiotowych KABA "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych KABA"
5246 asrcrfcd http://id.loc.gov/vocabulary/subjectSchemes/asrcrfcd Australian Standard Research Classification: Research Fields, Courses and Disciplines (RFCD) classification
5247 bt http://id.loc.gov/vocabulary/subjectSchemes/bt Bioethics thesaurus
5248 lcstt http://id.loc.gov/vocabulary/subjectSchemes/lcstt List of Chinese subject terms
5249 netc http://id.loc.gov/vocabulary/subjectSchemes/netc National Emergency Training Center Thesaurus (NETC)
5250 aat http://id.loc.gov/vocabulary/subjectSchemes/aat Art & architecture thesaurus
5251 bet http://id.loc.gov/vocabulary/subjectSchemes/bet British education thesaurus
5252 ncjt http://id.loc.gov/vocabulary/subjectSchemes/ncjt National criminal justice thesaurus
5253 samisk http://id.loc.gov/vocabulary/subjectSchemes/samisk Sami bibliography "no"=>"Sámi bibliografia = Samisk bibliografi (Norge)"
5254 tips http://id.loc.gov/vocabulary/subjectSchemes/tips Tesauro ISOC de psicologÃa "es"=>"Tesauro ISOC de psicologÃa"
5255 ukslc http://id.loc.gov/vocabulary/subjectSchemes/ukslc UK Standard Library Categories
5256 tekord http://id.loc.gov/vocabulary/subjectSchemes/tekord TEK-ord : UBiTs emneordliste for arkitektur, realfag, og teknolog "no"=>"TEK-ord : UBiTs emneordliste for arkitektur, realfag, og teknolog"
5257 umitrist http://id.loc.gov/vocabulary/subjectSchemes/umitrist University of Michigan Transportation Research Institute structured thesaurus
5258 wgst http://id.loc.gov/vocabulary/subjectSchemes/wgst Washington GILS Subject Tree
5259 rasuqam http://id.loc.gov/vocabulary/subjectSchemes/rasuqam Répertoire d'autorités-sujet de l'UQAM "fr"=>"Répertoire d'autorités-sujet de l'UQAM"
5260 ntids http://id.loc.gov/vocabulary/subjectSchemes/ntids Norske tidsskrifter 1700-1820: emneord "no"=>"Norske tidsskrifter 1700-1820: emneord"
5261 kaa http://id.loc.gov/vocabulary/subjectSchemes/kaa Kasvatusalan asiasanasto "fi"=>"Kasvatusalan asiasanasto"
5262 yso http://id.loc.gov/vocabulary/subjectSchemes/yso YSO - Yleinen suomalainen ontologia "fi"=>"YSO - Yleinen suomalainen ontologia"
5263 gcipmedia http://id.loc.gov/vocabulary/subjectSchemes/gcipmedia GAMECIP - Computer Game Media Formats (GAMECIP (Game Metadata and Citation Project))
5264 inspect http://id.loc.gov/vocabulary/subjectSchemes/inspect INSPEC thesaurus
5265 ordnok http://id.loc.gov/vocabulary/subjectSchemes/ordnok Ordnokkelen: tesaurus for kulturminnevern "no"=>"Ordnokkelen: tesaurus for kulturminnevern"
5266 helecon http://id.loc.gov/vocabulary/subjectSchemes/helecon Asiasanasto HELECON-tietikantoihin "fi"=>"Asiasanasto HELECON-tietikantoihin"
5267 dltlt http://id.loc.gov/vocabulary/subjectSchemes/dltlt Cuddon, J. A. A dictionary of literary terms and literary theory
5268 csapa http://id.loc.gov/vocabulary/subjectSchemes/csapa "Controlled vocabulary" in Pollution abstracts
5269 gtt http://id.loc.gov/vocabulary/subjectSchemes/gtt GOO-trefwoorden thesaurus "nl"=>"GOO-trefwoorden thesaurus"
5270 iescs http://id.loc.gov/vocabulary/subjectSchemes/iescs International energy subject categories and scope
5271 itrt http://id.loc.gov/vocabulary/subjectSchemes/itrt International Thesaurus of Refugee Terminology
5272 sanb http://id.loc.gov/vocabulary/subjectSchemes/sanb South African national bibliography authority file
5273 blmlsh http://id.loc.gov/vocabulary/subjectSchemes/blmlsh British Library - Map library subject headings
5274 bhb http://id.loc.gov/vocabulary/subjectSchemes/bhb Bibliography of the Hebrew Book
5275 csh http://id.loc.gov/vocabulary/subjectSchemes/csh Kapsner, Oliver Leonard. Catholic subject headings
5276 fire http://id.loc.gov/vocabulary/subjectSchemes/fire FireTalk, IFSI thesaurus
5277 jlabsh http://id.loc.gov/vocabulary/subjectSchemes/jlabsh Basic subject headings "ja"=>"Kihon kenmei hyômokuhyô"
5278 udc http://id.loc.gov/vocabulary/subjectSchemes/udc Universal decimal classification
5279 lcshac http://id.loc.gov/vocabulary/subjectSchemes/lcshac Children's subject headings in Library of Congress subject headings: supplementary vocabularies
5280 geonet http://id.loc.gov/vocabulary/subjectSchemes/geonet NGA GEOnet Names Server (GNS)
5281 humord http://id.loc.gov/vocabulary/subjectSchemes/humord HUMORD "no"=>"HUMORD"
5282 no-ubo-mr http://id.loc.gov/vocabulary/subjectSchemes/no-ubo-mr Menneskerettighets-tesaurus "no"=>"Menneskerettighets-tesaurus"
5283 sgce http://id.loc.gov/vocabulary/subjectSchemes/sgce COBISS.SI General List of subject headings (English subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5284 kdm http://id.loc.gov/vocabulary/subjectSchemes/kdm Khung dê muc hê thông thông tin khoa hoc và ky thuât quôc gia "vi"=>"Khung dê muc hê thông thông tin khoa hoc và ky thuât quôc gia"
5285 thesoz http://id.loc.gov/vocabulary/subjectSchemes/thesoz Thesaurus for the Social Sciences
5286 asth http://id.loc.gov/vocabulary/subjectSchemes/asth Astronomy thesaurus
5287 muzeukc http://id.loc.gov/vocabulary/subjectSchemes/muzeukc MuzeMusic UK classical music classification
5288 norbok http://id.loc.gov/vocabulary/subjectSchemes/norbok Norbok: emneord i Norsk bokfortegnelse "no"=>"Norbok: emneord i Norsk bokfortegnelse"
5289 masa http://id.loc.gov/vocabulary/subjectSchemes/masa Museoalan asiasanasto "fi"=>"Museoalan asiasanasto"
5290 conorsi http://id.loc.gov/vocabulary/subjectSchemes/conorsi CONOR.SI (name authority file) (Maribor, Slovenia: Institut informacijskih znanosti (IZUM))
5291 eurovocen http://id.loc.gov/vocabulary/subjectSchemes/eurovocen Eurovoc thesaurus (English)
5292 kto http://id.loc.gov/vocabulary/subjectSchemes/kto KTO - Kielitieteen ontologia "fi"=>"KTO - Kielitieteen ontologia"
5293 muzvukci http://id.loc.gov/vocabulary/subjectSchemes/muzvukci MuzeVideo UK contributor index
5294 kaunokki http://id.loc.gov/vocabulary/subjectSchemes/kaunokki Kaunokki: kaunokirjallisuuden asiasanasto "fi"=>"Kaunokki: kaunokirjallisuuden asiasanasto"
5295 maotao http://id.loc.gov/vocabulary/subjectSchemes/maotao MAO/TAO - Ontologi för museibranschen och Konstindustriella ontologin "fi"=>"MAO/TAO - Ontologi för museibranschen och Konstindustriella ontologin"
5296 psychit http://id.loc.gov/vocabulary/subjectSchemes/psychit Thesaurus of psychological index terms.
5297 tlsh http://id.loc.gov/vocabulary/subjectSchemes/tlsh Subject heading authority list
5298 csalsct http://id.loc.gov/vocabulary/subjectSchemes/csalsct CSA life sciences collection thesaurus
5299 ciesiniv http://id.loc.gov/vocabulary/subjectSchemes/ciesiniv CIESIN indexing vocabulary
5300 ebfem http://id.loc.gov/vocabulary/subjectSchemes/ebfem Encabezamientos bilingües de la Fundación Educativa Ana G. Mendez
5301 mero http://id.loc.gov/vocabulary/subjectSchemes/mero MERO - Merenkulkualan ontologia "fi"=>"MERO - Merenkulkualan ontologia"
5302 mmm http://id.loc.gov/vocabulary/subjectSchemes/mmm "Subject key" in Marxism and the mass media
5303 pascal http://id.loc.gov/vocabulary/subjectSchemes/pascal PASCAL database classification scheme "fr"=>"Base de donneés PASCAL: plan de classement"
5304 chirosh http://id.loc.gov/vocabulary/subjectSchemes/chirosh Chiropractic Subject Headings
5305 cilla http://id.loc.gov/vocabulary/subjectSchemes/cilla Cilla: specialtesaurus för musik "fi"=>"Cilla: specialtesaurus för musik"
5306 aiatsisl http://id.loc.gov/vocabulary/subjectSchemes/aiatsisl AIATSIS language thesaurus
5307 nskps http://id.loc.gov/vocabulary/subjectSchemes/nskps PriruÄ
\8dnik za izradu predmetnog kataloga u Nacionalnoj i sveuÄ
\8diliÅ¡noj knjiÄ
\8dnici u Zagrebu "hr"=>"PriruÄ
\8dnik za izradu predmetnog kataloga u Nacionalnoj i sveuÄ
\8diliÅ¡noj knjiÄ
\8dnici u Zagrebu"
5308 lctgm http://id.loc.gov/vocabulary/subjectSchemes/lctgm Thesaurus for graphic materials: TGM I, Subject terms
5309 muso http://id.loc.gov/vocabulary/subjectSchemes/muso MUSO - Ontologi för musik "fi"=>"MUSO - Ontologi för musik"
5310 blcpss http://id.loc.gov/vocabulary/subjectSchemes/blcpss COMPASS subject authority system
5311 fast http://id.loc.gov/vocabulary/subjectSchemes/fast Faceted application of subject terminology
5312 bisacmt http://id.loc.gov/vocabulary/subjectSchemes/bisacmt BISAC Merchandising Themes
5313 lapponica http://id.loc.gov/vocabulary/subjectSchemes/lapponica Lapponica "fi"=>"Lapponica"
5314 juho http://id.loc.gov/vocabulary/subjectSchemes/juho JUHO - Julkishallinnon ontologia "fi"=>"JUHO - Julkishallinnon ontologia"
5315 idas http://id.loc.gov/vocabulary/subjectSchemes/idas ID-Archivschlüssel "de"=>"ID-Archivschlüssel"
5316 tbjvp http://id.loc.gov/vocabulary/subjectSchemes/tbjvp Tesauro de la Biblioteca Dr. Jorge Villalobos Padilla, S.J. "es"=>"Tesauro de la Biblioteca Dr. Jorge Villalobos Padilla, S.J."
5317 test http://id.loc.gov/vocabulary/subjectSchemes/test Thesaurus of engineering and scientific terms
5318 finmesh http://id.loc.gov/vocabulary/subjectSchemes/finmesh FinMeSH "fi"=>"FinMeSH"
5319 kssbar http://id.loc.gov/vocabulary/subjectSchemes/kssbar Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del "sv"=>"Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del"
5320 kupu http://id.loc.gov/vocabulary/subjectSchemes/kupu Maori Wordnet "mi"=>"He puna kupu"
5321 rpe http://id.loc.gov/vocabulary/subjectSchemes/rpe Rubricator on economics "ru"=>"Rubrikator po ekonomike"
5322 dit http://id.loc.gov/vocabulary/subjectSchemes/dit Defense intelligence thesaurus
5323 she http://id.loc.gov/vocabulary/subjectSchemes/she SHE: subject headings for engineering
5324 idszbzna http://id.loc.gov/vocabulary/subjectSchemes/idszbzna Thesaurus IDS Nebis Zentralbibliothek Zürich, Nordamerika-Bibliothek "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Nordamerika-Bibliothek"
5325 msc http://id.loc.gov/vocabulary/subjectSchemes/msc Mathematical subject classification
5326 muzeukn http://id.loc.gov/vocabulary/subjectSchemes/muzeukn MuzeMusic UK non-classical music classification
5327 ipsp http://id.loc.gov/vocabulary/subjectSchemes/ipsp Defense intelligence production schedule.
5328 sthus http://id.loc.gov/vocabulary/subjectSchemes/sthus Subject Taxonomy of the History of U.S. Foreign Relations
5329 poliscit http://id.loc.gov/vocabulary/subjectSchemes/poliscit Political science thesaurus II
5330 qtglit http://id.loc.gov/vocabulary/subjectSchemes/qtglit A queer thesaurus : an international thesaurus of gay and lesbian index terms
5331 unbist http://id.loc.gov/vocabulary/subjectSchemes/unbist UNBIS thesaurus
5332 gcipplatform http://id.loc.gov/vocabulary/subjectSchemes/gcipplatform GAMECIP - Computer Game Platforms (GAMECIP (Game Metadata and Citation Project))
5333 puho http://id.loc.gov/vocabulary/subjectSchemes/puho PUHO - Puolustushallinnon ontologia "fi"=>"PUHO - Puolustushallinnon ontologia"
5334 thub http://id.loc.gov/vocabulary/subjectSchemes/thub Thesaurus de la Universitat de Barcelona "ca"=>"Thesaurus de la Universitat de Barcelona"
5335 ndlsh http://id.loc.gov/vocabulary/subjectSchemes/ndlsh National Diet Library list of subject headings "ja"=>"Koktsu Kokkai Toshokan kenmei hyômokuhyô"
5336 czenas http://id.loc.gov/vocabulary/subjectSchemes/czenas CZENAS thesaurus: a list of subject terms used in the National Library of the Czech Republic "cs"=>"Soubor vÄ
\95cných autorit Národnà knihovny Ä
\8cR"
5337 idszbzzh http://id.loc.gov/vocabulary/subjectSchemes/idszbzzh Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung"
5338 unbisn http://id.loc.gov/vocabulary/subjectSchemes/unbisn UNBIS name authority list (New York, NY: Dag Hammarskjld Library, United Nations; : Chadwyck-Healey)
5339 rswk http://id.loc.gov/vocabulary/subjectSchemes/rswk Regeln für den Schlagwortkatalog "de"=>"Regeln für den Schlagwortkatalog"
5340 larpcal http://id.loc.gov/vocabulary/subjectSchemes/larpcal Lista de assuntos referente ao programa de cadastramento automatizado de livros da USP "pt"=>"Lista de assuntos referente ao programa de cadastramento automatizado de livros da USP"
5341 biccbmc http://id.loc.gov/vocabulary/subjectSchemes/biccbmc BIC Children's Books Marketing Classifications
5342 kulo http://id.loc.gov/vocabulary/subjectSchemes/kulo KULO - Kulttuurien tutkimuksen ontologia "fi"=>"KULO - Kulttuurien tutkimuksen ontologia"
5343 popinte http://id.loc.gov/vocabulary/subjectSchemes/popinte POPIN thesaurus: population multilingual thesaurus
5344 tisa http://id.loc.gov/vocabulary/subjectSchemes/tisa Villagrá Rubio, Angel. Tesauro ISOC de sociologÃa autores "es"=>"Villagrá Rubio, Angel. Tesauro ISOC de sociologÃa autores"
5345 atg http://id.loc.gov/vocabulary/subjectSchemes/atg Agricultural thesaurus and glossary
5346 eflch http://id.loc.gov/vocabulary/subjectSchemes/eflch E4Libraries Category Headings
5347 maaq http://id.loc.gov/vocabulary/subjectSchemes/maaq Madâkhil al-asmâ' al-'arabîyah al-qadîmah "ar"=>"Madâkhil al-asmâ' al-'arabîyah al-qadîmah"
5348 rvmgd http://id.loc.gov/vocabulary/subjectSchemes/rvmgd Thésaurus des descripteurs de groupes démographiques de l'Université Laval "fr"=>"Thésaurus des descripteurs de groupes démographiques de l'Université Laval"
5349 csahssa http://id.loc.gov/vocabulary/subjectSchemes/csahssa "Controlled vocabulary" in Health and safety science abstracts
5350 sigle http://id.loc.gov/vocabulary/subjectSchemes/sigle SIGLE manual, Part 2, Subject category list
5351 blnpn http://id.loc.gov/vocabulary/subjectSchemes/blnpn British Library newspaper place names
5352 asrctoa http://id.loc.gov/vocabulary/subjectSchemes/asrctoa Australian Standard Research Classification: Type of Activity (TOA) classification
5353 lcdgt http://id.loc.gov/vocabulary/subjectSchemes/lcdgt Library of Congress demographic group term and code List
5354 bokbas http://id.loc.gov/vocabulary/subjectSchemes/bokbas Bokbasen "no"=>"Bokbasen"
5355 gnis http://id.loc.gov/vocabulary/subjectSchemes/gnis Geographic Names Information System (GNIS)
5356 nbiemnfag http://id.loc.gov/vocabulary/subjectSchemes/nbiemnfag NBIs emneordsliste for faglitteratur "no"=>"NBIs emneordsliste for faglitteratur"
5357 nlgaf http://id.loc.gov/vocabulary/subjectSchemes/nlgaf Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn "el"=>"Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn"
5358 bhashe http://id.loc.gov/vocabulary/subjectSchemes/bhashe BHA, Bibliography of the history of art, subject headings/English
5359 tsht http://id.loc.gov/vocabulary/subjectSchemes/tsht Thesaurus of subject headings for television
5360 scbi http://id.loc.gov/vocabulary/subjectSchemes/scbi Soggettario per i cataloghi delle biblioteche italiane "it"=>"Soggettario per i cataloghi delle biblioteche italiane"
5361 valo http://id.loc.gov/vocabulary/subjectSchemes/valo VALO - Fotografiska ontologin "fi"=>"VALO - Fotografiska ontologin"
5362 wpicsh http://id.loc.gov/vocabulary/subjectSchemes/wpicsh WPIC Library thesaurus of subject headings
5363 aktp http://id.loc.gov/vocabulary/subjectSchemes/aktp AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn "el"=>"AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn"
5364 stw http://id.loc.gov/vocabulary/subjectSchemes/stw STW Thesaurus for Economics "de"=>"Standard-Thesaurus Wirtschaft"
5365 mesh http://id.loc.gov/vocabulary/subjectSchemes/mesh Medical subject headings
5366 ica http://id.loc.gov/vocabulary/subjectSchemes/ica Index of Christian art
5367 emnmus http://id.loc.gov/vocabulary/subjectSchemes/emnmus Emneord for musikkdokument i EDB-kataloger "no"=>"Emneord for musikkdokument i EDB-kataloger"
5368 sao http://id.loc.gov/vocabulary/subjectSchemes/sao Svenska ämnesord "sv"=>"Svenska ämnesord"
5369 sgc http://id.loc.gov/vocabulary/subjectSchemes/sgc COBISS.SI General List of subject headings (Slovenian subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5370 bib1814 http://id.loc.gov/vocabulary/subjectSchemes/bib1814 1814-bibliografi: emneord for 1814-bibliografi "no"=>"1814-bibliografi: emneord for 1814-bibliografi"
5371 bjornson http://id.loc.gov/vocabulary/subjectSchemes/bjornson Bjornson: emneord for Bjornsonbibliografien "no"=>"Bjornson: emneord for Bjornsonbibliografien"
5372 liito http://id.loc.gov/vocabulary/subjectSchemes/liito LIITO - Liiketoimintaontologia "fi"=>"LIITO - Liiketoimintaontologia"
5373 apaist http://id.loc.gov/vocabulary/subjectSchemes/apaist APAIS thesaurus: a list of subject terms used in the Australian Public Affairs Information Service
5374 itglit http://id.loc.gov/vocabulary/subjectSchemes/itglit International thesaurus of gay and lesbian index terms (Chicago?: Thesaurus Committee, Gay and Lesbian Task Force, American Library Association)
5375 ntcsd http://id.loc.gov/vocabulary/subjectSchemes/ntcsd "National Translations Center secondary descriptors" in National Translation Center primary subject classification and secondary descriptor
5376 scisshl http://id.loc.gov/vocabulary/subjectSchemes/scisshl SCIS subject headings
5377 opms http://id.loc.gov/vocabulary/subjectSchemes/opms Opetusministeriön asiasanasto "fi"=>"Opetusministeriön asiasanasto"
5378 ttka http://id.loc.gov/vocabulary/subjectSchemes/ttka Teologisen tiedekunnan kirjaston asiasanasto "fi"=>"Teologisen tiedekunnan kirjaston asiasanasto"
5379 watrest http://id.loc.gov/vocabulary/subjectSchemes/watrest Thesaurus of water resources terms: a collection of water resources and related terms for use in indexing technical information
5380 ysa http://id.loc.gov/vocabulary/subjectSchemes/ysa Yleinen suomalainen asiasanasto "fi"=>"Yleinen suomalainen asiasanasto"
5381 kitu http://id.loc.gov/vocabulary/subjectSchemes/kitu Kirjallisuudentutkimuksen asiasanasto "fi"=>"Kirjallisuudentutkimuksen asiasanasto"
5382 sk http://id.loc.gov/vocabulary/subjectSchemes/sk 'Zhong guo gu ji shan ban shu zong mu' fen lei biao "zh"=>"'Zhong guo gu ji shan ban shu zong mu' fen lei biao"
5383 aiatsisp http://id.loc.gov/vocabulary/subjectSchemes/aiatsisp AIATSIS place thesaurus
5384 ram http://id.loc.gov/vocabulary/subjectSchemes/ram RAMEAU: répertoire d'authorité de matières encyclopédique unifié "fr"=>"RAMEAU: répertoire d'authorité de matières encyclopédique unifié"
5385 aedoml http://id.loc.gov/vocabulary/subjectSchemes/aedoml Listado de encabezamientos de materia de música "es"=>"Listado de encabezamientos de materia de música"
5386 ated http://id.loc.gov/vocabulary/subjectSchemes/ated Australian Thesaurus of Education Descriptors (ATED)
5387 cabt http://id.loc.gov/vocabulary/subjectSchemes/cabt CAB thesaurus (Slough [England]: Commonwealth Agricultural Bureaux)
5388 kassu http://id.loc.gov/vocabulary/subjectSchemes/kassu Kassu - Kasvien suomenkieliset nimet "fi"=>"Kassu - Kasvien suomenkieliset nimet"
5389 nbdbt http://id.loc.gov/vocabulary/subjectSchemes/nbdbt NBD Biblion Trefwoordenthesaurus "nl"=>"NBD Biblion Trefwoordenthesaurus"
5390 jhpb http://id.loc.gov/vocabulary/subjectSchemes/jhpb JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej"
5391 bidex http://id.loc.gov/vocabulary/subjectSchemes/bidex Bilindex: a bilingual Spanish-English subject heading list
5392 ccsa http://id.loc.gov/vocabulary/subjectSchemes/ccsa Catalogue collectif suisse des affiches "fr"=>"Catalogue collectif suisse des affiches"
5393 noraf http://id.loc.gov/vocabulary/subjectSchemes/noraf Norwegian Authority File
5394 kito http://id.loc.gov/vocabulary/subjectSchemes/kito KITO - Kirjallisuudentutkimuksen ontologia "fi"=>"KITO - Kirjallisuudentutkimuksen ontologia"
5395 tho http://id.loc.gov/vocabulary/subjectSchemes/tho Thesauros HellÄ
\93nikÅ
\8dn Oron "el"=>"Thesauros HellÄ
\93nikÅ
\8dn Oron"
5396 pmont http://id.loc.gov/vocabulary/subjectSchemes/pmont Powerhouse Museum Object Name Thesaurus
5397 ssg http://id.loc.gov/vocabulary/subjectSchemes/ssg Splošni slovenski geslovnik "sl"=>"Splošni slovenski geslovnik"
5398 huc http://id.loc.gov/vocabulary/subjectSchemes/huc U.S. Geological Survey water-supply paper 2294: hydrologic basins unit codes
5399 isis http://id.loc.gov/vocabulary/subjectSchemes/isis "Classification scheme" in Isis
5400 ibsen http://id.loc.gov/vocabulary/subjectSchemes/ibsen Ibsen: emneord for Den internasjonale Ibsen-bibliografien "no"=>"Ibsen: emneord for Den internasjonale Ibsen-bibliografien"
5401 lacnaf http://id.loc.gov/vocabulary/subjectSchemes/lacnaf Library and Archives Canada name authority file
5402 swemesh http://id.loc.gov/vocabulary/subjectSchemes/swemesh Swedish MeSH "sv"=>"Svenska MeSH"
5403 hamsun http://id.loc.gov/vocabulary/subjectSchemes/hamsun Hamsun: emneord for Hamsunbibliografien "no"=>"Hamsun: emneord for Hamsunbibliografien"
5404 qrma http://id.loc.gov/vocabulary/subjectSchemes/qrma List of Arabic subject headings "ar"=>"Qâ'imat ru'ûs al-mawdûât al-'Arabîyah"
5405 qrmak http://id.loc.gov/vocabulary/subjectSchemes/qrmak Qâ'imat ru'ûs al-mawdû'ât al-'Arabîyah al-qiyâsîyah al-maktabât wa-marâkaz al-ma'lûmât wa-qawâid al-bayânât "ar"=>"Qâ'imat ru'ûs al-mawdû'ât al-'Arabîyah al-qiyâsîyah al-maktabât wa-marâkaz al-ma'lûmât wa-qawâid al-bayânât"
5406 ceeus http://id.loc.gov/vocabulary/subjectSchemes/ceeus Counties and equivalent entities of the United States its possessions, and associated areas
5407 taxhs http://id.loc.gov/vocabulary/subjectSchemes/taxhs A taxonomy or human services: a conceptual framework with standardized terminology and definitions for the field
5408 noram http://id.loc.gov/vocabulary/subjectSchemes/noram Noram: emneord for Norsk-amerikansk samling "no"=>"Noram: emneord for Norsk-amerikansk samling"
5409 eurovocfr http://id.loc.gov/vocabulary/subjectSchemes/eurovocfr Eurovoc thesaurus (French)
5410 jurivoc http://id.loc.gov/vocabulary/subjectSchemes/jurivoc JURIVOC
5411 agrifors http://id.loc.gov/vocabulary/subjectSchemes/agrifors AGRIFOREST-sanasto "fi"=>"AGRIFOREST-sanasto"
5412 noubojur http://id.loc.gov/vocabulary/subjectSchemes/noubojur Thesaurus of Law "no"=>"Thesaurus of Law"
5413 pha http://id.loc.gov/vocabulary/subjectSchemes/pha Puolostushallinnon asiasanasto "fi"=>"Puolostushallinnon asiasanasto"
5414 ddcrit http://id.loc.gov/vocabulary/subjectSchemes/ddcrit DDC retrieval and indexing terminology; posting terms with hierarchy and KWOC
5415 mar http://id.loc.gov/vocabulary/subjectSchemes/mar Merenkulun asiasanasto "fi"=>"Merenkulun asiasanasto"
5416 sbt http://id.loc.gov/vocabulary/subjectSchemes/sbt Soggettario Sistema Bibliotecario Ticinese "it"=>"Soggettario Sistema Bibliotecario Ticinese"
5417 nzggn http://id.loc.gov/vocabulary/subjectSchemes/nzggn New Zealand gazetteer of official geographic names (New Zealand Geographic Board Ngā Pou Taunaha o Aotearoa (NZGB))
5418 kta http://id.loc.gov/vocabulary/subjectSchemes/kta Kielitieteen asiasanasto "fi"=>"Kielitieteen asiasanasto"
5419 snt http://id.loc.gov/vocabulary/subjectSchemes/snt Sexual nomenclature : a thesaurus
5420 francis http://id.loc.gov/vocabulary/subjectSchemes/francis FRANCIS database classification scheme "fr"=>"Base de donneés FRANCIS: plan de classement"
5421 eurovocsl http://id.loc.gov/vocabulary/subjectSchemes/eurovocsl Eurovoc thesaurus "sl"=>"Eurovoc thesaurus"
5422 idszbzes http://id.loc.gov/vocabulary/subjectSchemes/idszbzes Thesaurus IDS Nebis Bibliothek Englisches Seminar der Universität Zürich "de"=>"Thesaurus IDS Nebis Bibliothek Englisches Seminar der Universität Zürich"
5423 nlmnaf http://id.loc.gov/vocabulary/subjectSchemes/nlmnaf National Library of Medicine name authority file
5424 rugeo http://id.loc.gov/vocabulary/subjectSchemes/rugeo Natsional'nyi normativnyi fail geograficheskikh nazvanii Rossiiskoi Federatsii "ru"=>"Natsional'nyi normativnyi fail geograficheskikh nazvanii Rossiiskoi Federatsii"
5425 sipri http://id.loc.gov/vocabulary/subjectSchemes/sipri SIPRI library thesaurus
5426 kkts http://id.loc.gov/vocabulary/subjectSchemes/kkts Katalogos KathierÅ
\8dmenÅ
\8dn TypÅ
\8dn Syllogikou Katalogou Demosion Vivliothekon "el"=>"Katalogos KathierÅ
\8dmenÅ
\8dn TypÅ
\8dn Syllogikou Katalogou Demosion Vivliothekon"
5427 tucua http://id.loc.gov/vocabulary/subjectSchemes/tucua Thesaurus for use in college and university archives
5428 pmbok http://id.loc.gov/vocabulary/subjectSchemes/pmbok Guide to the project management body of knowledge (PMBOK Guide)
5429 agrovoc http://id.loc.gov/vocabulary/subjectSchemes/agrovoc AGROVOC multilingual agricultural thesaurus
5430 nal http://id.loc.gov/vocabulary/subjectSchemes/nal National Agricultural Library subject headings
5431 lnmmbr http://id.loc.gov/vocabulary/subjectSchemes/lnmmbr Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas "lt"=>"Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas"
5432 vmj http://id.loc.gov/vocabulary/subjectSchemes/vmj Vedettes-matière jeunesse "fr"=>"Vedettes-matière jeunesse"
5433 ddcut http://id.loc.gov/vocabulary/subjectSchemes/ddcut Dewey Decimal Classification user terms
5434 eks http://id.loc.gov/vocabulary/subjectSchemes/eks Eduskunnan kirjaston asiasanasto "fi"=>"Eduskunnan kirjaston asiasanasto"
5435 wot http://id.loc.gov/vocabulary/subjectSchemes/wot A Women's thesaurus
5436 noubomn http://id.loc.gov/vocabulary/subjectSchemes/noubomn University of Oslo Library Thesaurus of Science "no"=>"University of Oslo Library Thesaurus of Science"
5437 idszbzzg http://id.loc.gov/vocabulary/subjectSchemes/idszbzzg Thesaurus IDS Nebis Zentralbibliothek Zürich, Graphische Sammlung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Graphische Sammlung"
5438 precis http://id.loc.gov/vocabulary/subjectSchemes/precis PRECIS: a manual of concept analysis and subject indexing
5439 cstud http://id.loc.gov/vocabulary/subjectSchemes/cstud Classificatieschema's Bibliotheek TU Delft "nl"=>"Classificatieschema's Bibliotheek TU Delft"
5440 nlgkk http://id.loc.gov/vocabulary/subjectSchemes/nlgkk Katalogos kathierÅ
\8dmenÅ
\8dn onomatÅ
\8dn physikÅ
\8dn prosÅ
\8dpÅ
\8dn "el"=>"Katalogos kathierÅ
\8dmenÅ
\8dn onomatÅ
\8dn physikÅ
\8dn prosÅ
\8dpÅ
\8dn"
5441 pmt http://id.loc.gov/vocabulary/subjectSchemes/pmt Project management terminology. Newtown Square, PA: Project Management Institute
5442 ericd http://id.loc.gov/vocabulary/subjectSchemes/ericd Thesaurus of ERIC descriptors
5443 rvm http://id.loc.gov/vocabulary/subjectSchemes/rvm Répertoire de vedettes-matière "fr"=>"Répertoire de vedettes-matière"
5444 sfit http://id.loc.gov/vocabulary/subjectSchemes/sfit Svenska filminstitutets tesaurus "sv"=>"Svenska filminstitutets tesaurus"
5445 trtsa http://id.loc.gov/vocabulary/subjectSchemes/trtsa Teatterin ja tanssin asiasanasto "fi"=>"Teatterin ja tanssin asiasanasto"
5446 ulan http://id.loc.gov/vocabulary/subjectSchemes/ulan Union list of artist names
5447 unescot http://id.loc.gov/vocabulary/subjectSchemes/unescot UNESCO thesaurus "fr"=>"Thésaurus de l'UNESCO","es"=>"Tesauro de la UNESCO"
5448 koko http://id.loc.gov/vocabulary/subjectSchemes/koko KOKO-ontologia "fi"=>"KOKO-ontologia"
5449 msh http://id.loc.gov/vocabulary/subjectSchemes/msh Trimboli, T., and Martyn S. Marianist subject headings
5450 trt http://id.loc.gov/vocabulary/subjectSchemes/trt Transportation resource thesaurus
5451 agrovocf http://id.loc.gov/vocabulary/subjectSchemes/agrovocf AGROVOC thésaurus agricole multilingue "fr"=>"AGROVOC thésaurus agricole multilingue"
5452 aucsh http://id.loc.gov/vocabulary/subjectSchemes/aucsh Arabic Union Catalog Subject Headings "ar"=>"Qâ'imat ru'ûs mawdû'ât al-fahras al-'Arabîyah al-mowahad"
5453 ddcri http://id.loc.gov/vocabulary/subjectSchemes/ddcri Dewey Decimal Classification Relative Index
5454 est http://id.loc.gov/vocabulary/subjectSchemes/est International energy: subject thesaurus (: International Energy Agency, Energy Technology Data Exchange)
5455 lua http://id.loc.gov/vocabulary/subjectSchemes/lua Liikunnan ja urheilun asiasanasto "fi"=>"Liikunnan ja urheilun asiasanasto"
5456 mipfesd http://id.loc.gov/vocabulary/subjectSchemes/mipfesd Macrothesaurus for information processing in the field of economic and social development
5457 rurkp http://id.loc.gov/vocabulary/subjectSchemes/rurkp Predmetnye rubriki Rossiiskoi knizhnoi palaty "ru"=>"Predmetnye rubriki Rossiiskoi knizhnoi palaty"
5458 albt http://id.loc.gov/vocabulary/subjectSchemes/albt Arbetslivsbibliotekets tesaurus "sv"=>"Arbetslivsbibliotekets tesaurus"
5459 fmesh http://id.loc.gov/vocabulary/subjectSchemes/fmesh Liste systématique et liste permutée des descripteurs français MeSH "fr"=>"Liste systématique et liste permutée des descripteurs français MeSH"
5460 bicssc http://id.loc.gov/vocabulary/subjectSchemes/bicssc BIC standard subject categories
5461 cctf http://id.loc.gov/vocabulary/subjectSchemes/cctf Carto-Canadiana thésaurus - Français "fr"=>"Carto-Canadiana thésaurus - Français"
5462 reo http://id.loc.gov/vocabulary/subjectSchemes/reo Māori Subject Headings thesaurus "mi"=>"Ngā Ūpoko Tukutuku"
5463 icpsr http://id.loc.gov/vocabulary/subjectSchemes/icpsr ICPSR controlled vocabulary system
5464 kao http://id.loc.gov/vocabulary/subjectSchemes/kao KVINNSAM ämnesordsregister "sv"=>"KVINNSAM ämnesordsregister"
5465 asrcseo http://id.loc.gov/vocabulary/subjectSchemes/asrcseo Australian Standard Research Classification: Socio-Economic Objective (SEO) classification
5466 georeft http://id.loc.gov/vocabulary/subjectSchemes/georeft GeoRef thesaurus
5467 cct http://id.loc.gov/vocabulary/subjectSchemes/cct Chinese Classified Thesaurus "zh"=>"Zhong guo fen lei zhu ti ci biao"
5468 dcs http://id.loc.gov/vocabulary/subjectSchemes/dcs Health Sciences Descriptors "es"=>"Descriptores en Ciencias de la Salud","pt"=>"Descritores em Ciências da Saúde"
5469 musa http://id.loc.gov/vocabulary/subjectSchemes/musa Musiikin asiasanasto: erikoissanasto "fi"=>"Musiikin asiasanasto: erikoissanasto"
5470 ntissc http://id.loc.gov/vocabulary/subjectSchemes/ntissc NTIS subject categories
5471 idszbz http://id.loc.gov/vocabulary/subjectSchemes/idszbz Thesaurus IDS Nebis Zentralbibliothek Zürich "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich"
5472 tlka http://id.loc.gov/vocabulary/subjectSchemes/tlka Investigació, Procés Tècnicn kirjaston asiasanasto "fi"=>"Investigació, Procés Tècnicn kirjaston asiasanasto"
5473 usaidt http://id.loc.gov/vocabulary/subjectSchemes/usaidt USAID thesaurus: Keywords used to index documents included in the USAID Development Experience System.
5474 embne http://id.loc.gov/vocabulary/subjectSchemes/embne Encabezamientos de Materia de la Biblioteca Nacional de España "es"=>"Encabezamientos de Materia de la Biblioteca Nacional de España"
5475 vcaadu http://id.loc.gov/vocabulary/subjectSchemes/vcaadu Vocabulario controlado de arquitectura, arte, diseño y urbanismo "es"=>"Vocabulario controlado de arquitectura, arte, diseño y urbanismo"
5476 ntcpsc http://id.loc.gov/vocabulary/subjectSchemes/ntcpsc "National Translations Center primary subject classification" in National Translations Center primary subject classification and secondary descriptors
5477 quiding http://id.loc.gov/vocabulary/subjectSchemes/quiding Quiding, Nils Herman. Svenskt allmänt författningsregister för tiden från år 1522 till och med år 1862 "sv"=>"Quiding, Nils Herman. Svenskt allmänt författningsregister för tiden från år 1522 till och med år 1862"
5478 allars http://id.loc.gov/vocabulary/subjectSchemes/allars Allärs: allmän tesaurus pä svenska "fi"=>"Allärs: allmän tesaurus pä svenska"
5479 ogst http://id.loc.gov/vocabulary/subjectSchemes/ogst Oregon GILS Subject Tree (Oregon: Oregon State Library and Oregon Information Resource Management Division (IRMD))
5480 bella http://id.loc.gov/vocabulary/subjectSchemes/bella Bella: specialtesaurus för skönlitteratur "fi"=>"Bella: specialtesaurus för skönlitteratur"
5481 bibalex http://id.loc.gov/vocabulary/subjectSchemes/bibalex Bibliotheca Alexandrina name and subject authority file
5482 pepp http://id.loc.gov/vocabulary/subjectSchemes/pepp The Princeton encyclopedia of poetry and poetics
5483 hkcan http://id.loc.gov/vocabulary/subjectSchemes/hkcan Hong Kong Chinese Authority File (Name) - HKCAN
5484 dissao http://id.loc.gov/vocabulary/subjectSchemes/dissao "Dissertation abstracts online" in Search tools: the guide to UNI/Data Courier Online
5485 ltcsh http://id.loc.gov/vocabulary/subjectSchemes/ltcsh Land Tenure Center Library list of subject headings
5486 mpirdes http://id.loc.gov/vocabulary/subjectSchemes/mpirdes Macrothesaurus para el procesamiento de la información relativa al desarrollo económico y social "es"=>"Macrothesaurus para el procesamiento de la información relativa al desarrollo económico y social"
5487 asft http://id.loc.gov/vocabulary/subjectSchemes/asft Aquatic sciences and fisheries thesaurus
5488 naf http://id.loc.gov/vocabulary/subjectSchemes/naf NACO authority file
5489 nimacsc http://id.loc.gov/vocabulary/subjectSchemes/nimacsc NIMA cartographic subject categories
5490 khib http://id.loc.gov/vocabulary/subjectSchemes/khib Emneord, KHiB Biblioteket "no"=>"Emneord, KHiB Biblioteket"
5491 cdcng http://id.loc.gov/vocabulary/subjectSchemes/cdcng Catalogage des documents cartographiques: forme et structure des vedettes noms géographiques - NF Z 44-081 "fr"=>"Catalogage des documents cartographiques: forme et structure des vedettes noms géographiques - NF Z 44-081"
5492 afset http://id.loc.gov/vocabulary/subjectSchemes/afset American Folklore Society Ethnographic Thesaurus
5493 erfemn http://id.loc.gov/vocabulary/subjectSchemes/erfemn Erfaringskompetanses emneord "no"=>"Erfaringskompetanses emneord"
5494 sbiao http://id.loc.gov/vocabulary/subjectSchemes/sbiao Svenska barnboksinstitutets ämnesordslista "sv"=>"Svenska barnboksinstitutets ämnesordslista"
5495 socio http://id.loc.gov/vocabulary/subjectSchemes/socio Sociological Abstracts Thesaurus
5496 bisacrt http://id.loc.gov/vocabulary/subjectSchemes/bisacrt BISAC Regional Themes
5497 eum http://id.loc.gov/vocabulary/subjectSchemes/eum Eesti uldine märksonastik "et"=>"Eesti uldine märksonastik"
5498 kula http://id.loc.gov/vocabulary/subjectSchemes/kula Kulttuurien tutkimuksen asiasanasto "fi"=>"Kulttuurien tutkimuksen asiasanasto"
5499 odlt http://id.loc.gov/vocabulary/subjectSchemes/odlt Baldick, C. The Oxford dictionary of literary terms
5500 rerovoc http://id.loc.gov/vocabulary/subjectSchemes/rerovoc Indexation matiéres RERO autoritès "fr"=>"Indexation matiéres RERO autoritès"
5501 tsr http://id.loc.gov/vocabulary/subjectSchemes/tsr TSR-ontologia "fi"=>"TSR-ontologia"
5502 czmesh http://id.loc.gov/vocabulary/subjectSchemes/czmesh Czech MeSH "cs"=>"Czech MeSH"
5503 dltt http://id.loc.gov/vocabulary/subjectSchemes/dltt Quinn, E. A dictionary of literary and thematic terms
5504 idsbb http://id.loc.gov/vocabulary/subjectSchemes/idsbb Thesaurus IDS Basel Bern "de"=>"Thesaurus IDS Basel Bern"
5505 inist http://id.loc.gov/vocabulary/subjectSchemes/inist INIS: thesaurus
5506 idszbzzk http://id.loc.gov/vocabulary/subjectSchemes/idszbzzk Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung"
5507 tesa http://id.loc.gov/vocabulary/subjectSchemes/tesa Tesauro AgrÃcola "es"=>"Tesauro AgrÃcola"
5508 liv http://id.loc.gov/vocabulary/subjectSchemes/liv Legislative indexing vocabulary
5509 collett http://id.loc.gov/vocabulary/subjectSchemes/collett Collett-bibliografi: litteratur av og om Camilla Collett "no"=>"Collett-bibliografi: litteratur av og om Camilla Collett"
5510 nsbncf http://id.loc.gov/vocabulary/subjectSchemes/nsbncf Nuovo Soggettario "it"=>"Nuovo Soggettario"
5511 ipat http://id.loc.gov/vocabulary/subjectSchemes/ipat IPA thesaurus and frequency list
5512 skon http://id.loc.gov/vocabulary/subjectSchemes/skon Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur "sv"=>"Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur"
5513 renib http://id.loc.gov/vocabulary/subjectSchemes/renib Renib "es"=>"Renib"
5514 hrvmesh http://id.loc.gov/vocabulary/subjectSchemes/hrvmesh Croatian MeSH / Hrvatski MeSH "no"=>"Croatian MeSH / Hrvatski MeSH"
5515 swd http://id.loc.gov/vocabulary/subjectSchemes/swd Schlagwortnormdatei "de"=>"Schlagwortnormdatei"
5516 aass http://id.loc.gov/vocabulary/subjectSchemes/aass "Asian American Studies Library subject headings" in A Guide for establishing Asian American core collections
5517 cht http://id.loc.gov/vocabulary/subjectSchemes/cht Chicano thesaurus for indexing Chicano materials in Chicano periodical index
5518 galestne http://id.loc.gov/vocabulary/subjectSchemes/galestne Gale Group subject thesaurus and named entity vocabulary
5519 nlgsh http://id.loc.gov/vocabulary/subjectSchemes/nlgsh Katalogos HellÄ
\93nikÅ
\8dn thematikÅ
\8dn epikephalidÅ
\8dn "el"=>"Katalogos HellÄ
\93nikÅ
\8dn thematikÅ
\8dn epikephalidÅ
\8dn"
5520 hoidokki http://id.loc.gov/vocabulary/subjectSchemes/hoidokki Hoitotieteellinen asiasanasto
5521 vffyl http://id.loc.gov/vocabulary/subjectSchemes/vffyl Vocabulario de la Biblioteca Central de la FFyL "es"=>"Vocabulario de la Biblioteca Central de la FFyL"
5522 kubikat http://id.loc.gov/vocabulary/subjectSchemes/kubikat kubikat "de"=>"kubikat"
5523 waqaf http://id.loc.gov/vocabulary/subjectSchemes/waqaf Maknas Uloom Al Waqaf "ar"=>"Maknas Uloom Al Waqaf"
5524 hapi http://id.loc.gov/vocabulary/subjectSchemes/hapi HAPI thesaurus and name authority, 1970-2000
5525 drama http://id.loc.gov/vocabulary/subjectSchemes/drama Drama: specialtesaurus för teater och dans
5526 sosa http://id.loc.gov/vocabulary/subjectSchemes/sosa Sociaalialan asiasanasto "fi"=>"Sociaalialan asiasanasto"
5527 ilpt http://id.loc.gov/vocabulary/subjectSchemes/ilpt Index to legal periodicals: thesaurus
5528 nicem http://id.loc.gov/vocabulary/subjectSchemes/nicem NICEM subject headings and classification system
5529 qlsp http://id.loc.gov/vocabulary/subjectSchemes/qlsp Queens Library Spanish language subject headings
5530 eet http://id.loc.gov/vocabulary/subjectSchemes/eet European education thesaurus
5531 nalnaf http://id.loc.gov/vocabulary/subjectSchemes/nalnaf National Agricultural Library name authority file
5532 eclas http://id.loc.gov/vocabulary/subjectSchemes/eclas ECLAS thesaurus
5533 agrovocs http://id.loc.gov/vocabulary/subjectSchemes/agrovocs AGROVOC tesauro agrÃcola multilingée "es"=>"AGROVOC tesauro agrÃcola multilingée"
5534 shbe http://id.loc.gov/vocabulary/subjectSchemes/shbe Subject headings in business and economics "sv"=>"Subject headings in business and economics"
5535 barn http://id.loc.gov/vocabulary/subjectSchemes/barn Svenska ämnesord för barn "sv"=>"Svenska ämnesord för barn"
5536 bhammf http://id.loc.gov/vocabulary/subjectSchemes/bhammf BHA, Bibliographie d'histoire de l'art, mots-matière/français "fr"=>"BHA, Bibliographie d'histoire de l'art, mots-matière/français"
5537 gccst http://id.loc.gov/vocabulary/subjectSchemes/gccst Government of Canada core subject thesaurus (Gatineau : Library and Archives Canada)
5538 fnhl http://id.loc.gov/vocabulary/subjectSchemes/fnhl First Nations House of Learning Subject Headings
5539 kauno http://id.loc.gov/vocabulary/subjectSchemes/kauno KAUNO - Kaunokki-ontologin "fi"=>"KAUNO - Kaunokki-ontologin"
5540 dtict http://id.loc.gov/vocabulary/subjectSchemes/dtict Defense Technical Information Center thesaurus
5541 mech http://id.loc.gov/vocabulary/subjectSchemes/mech Iskanje po zbirki MECH "sl"=>"Iskanje po zbirki MECH"
5542 jupo http://id.loc.gov/vocabulary/subjectSchemes/jupo JUPO - Julkisen hallinnon palveluontologia "fi"=>"JUPO - Julkisen hallinnon palveluontologia"
5543 ktpt http://id.loc.gov/vocabulary/subjectSchemes/ktpt Kirjasto- ja tietopalvelualan tesaurus "fi"=>"Kirjasto- ja tietopalvelualan tesaurus"
5544 aiatsiss http://id.loc.gov/vocabulary/subjectSchemes/aiatsiss AIATSIS subject Thesaurus
5545 lcac http://id.loc.gov/vocabulary/subjectSchemes/lcac Library of Congress Annotated Children's Cataloging Program subject headings
5546 lemac http://id.loc.gov/vocabulary/subjectSchemes/lemac Llista d'encapçalaments de matèria en català "ca"=>"Llista d'encapçalaments de matèria en català "
5547 lemb http://id.loc.gov/vocabulary/subjectSchemes/lemb Lista de encabezamientos de materia para bibliotecas "es"=>"Lista de encabezamientos de materia para bibliotecas"
5548 henn http://id.loc.gov/vocabulary/subjectSchemes/henn Hennepin County Library cumulative authority list
5549 mtirdes http://id.loc.gov/vocabulary/subjectSchemes/mtirdes Macrothésaurus pour le traitement de l'information relative au développement économique et social "fr"=>"Macrothésaurus pour le traitement de l'information relative au développement économique et social"
5550 cash http://id.loc.gov/vocabulary/subjectSchemes/cash Canadian subject headings
5551 nznb http://id.loc.gov/vocabulary/subjectSchemes/nznb New Zealand national bibliographic
5552 prvt http://id.loc.gov/vocabulary/subjectSchemes/prvt Patent- och registreringsverkets tesaurus "sv"=>"Patent- och registreringsverkets tesaurus"
5553 scgdst http://id.loc.gov/vocabulary/subjectSchemes/scgdst Subject categorization guide for defense science and technology
5554 gem http://id.loc.gov/vocabulary/subjectSchemes/gem GEM controlled vocabularies
5555 lcsh http://id.loc.gov/vocabulary/subjectSchemes/lcsh Library of Congress subject headings
5556 rero http://id.loc.gov/vocabulary/subjectSchemes/rero Indexation matires RERO "fr"=>"Indexation matires RERO"
5557 peri http://id.loc.gov/vocabulary/subjectSchemes/peri Perinnetieteiden asiasanasto "fi"=>"Perinnetieteiden asiasanasto"
5558 shsples http://id.loc.gov/vocabulary/subjectSchemes/shsples Encabezamientos de materia para bibliotecas escolares y públicas "es"=>"Encabezamientos de materia para bibliotecas escolares y públicas"
5559 slem http://id.loc.gov/vocabulary/subjectSchemes/slem Sears: lista de encabezamientos de materia "es"=>"Sears: lista de encabezamientos de materia"
5560 afo http://id.loc.gov/vocabulary/subjectSchemes/afo AFO - Viikin kampuskirjaston ontologia "fi"=>"AFO - Viikin kampuskirjaston ontologia"
5561 gst http://id.loc.gov/vocabulary/subjectSchemes/gst Gay studies thesaurus: a controlled vocabulary for indexing and accessing materials of relevance to gay culture, history, politics and psychology
5562 hlasstg http://id.loc.gov/vocabulary/subjectSchemes/hlasstg HLAS subject term glossary
5563 iest http://id.loc.gov/vocabulary/subjectSchemes/iest International energy: subject thesaurus
5564 pkk http://id.loc.gov/vocabulary/subjectSchemes/pkk Predmetnik za katoliške knjižnice "sl"=>"Predmetnik za katoliške knjižnice"
5565 atla http://id.loc.gov/vocabulary/subjectSchemes/atla Religion indexes: thesaurus
5566 scot http://id.loc.gov/vocabulary/subjectSchemes/scot Schools Online Thesaurus (ScOT)
5567 smda http://id.loc.gov/vocabulary/subjectSchemes/smda Smithsonian National Air and Space Museum Directory of Airplanes
5568 solstad http://id.loc.gov/vocabulary/subjectSchemes/solstad Solstad: emneord for Solstadbibliografien "no"=>"Solstad: emneord for Solstadbibliografien"
5569 abne http://id.loc.gov/vocabulary/subjectSchemes/abne Autoridades de la Biblioteca Nacional de España "es"=>"Autoridades de la Biblioteca Nacional de España"
5570 spines http://id.loc.gov/vocabulary/subjectSchemes/spines Tesauro SPINES: un vocabulario controlado y estructurado para el tratamiento de información sobre ciencia y tecnologÃa para el desarrollo "es"=>"Tesauro SPINES: un vocabulario controlado y estructurado para el tratamiento de información sobre ciencia y tecnologÃa para el desarrollo"
5571 ktta http://id.loc.gov/vocabulary/subjectSchemes/ktta Käsi - ja taideteollisuuden asiasanasto "fi"=>"Käsi - ja taideteollisuuden asiasanasto"
5572 ccte http://id.loc.gov/vocabulary/subjectSchemes/ccte Carto-Canadiana thesaurus - English
5573 pmcsg http://id.loc.gov/vocabulary/subjectSchemes/pmcsg Combined standards glossary
5574 bisacsh http://id.loc.gov/vocabulary/subjectSchemes/bisacsh BISAC Subject Headings
5575 fssh http://id.loc.gov/vocabulary/subjectSchemes/fssh FamilySearch Subject Headings (FamilySearch)
5576 tasmas http://id.loc.gov/vocabulary/subjectSchemes/tasmas Tesaurus de Asuntos Sociales del Ministerio de Asuntos Sociales de España "es"=>"Tesaurus de Asuntos Sociales del Ministerio de Asuntos Sociales de España"
5577 tero http://id.loc.gov/vocabulary/subjectSchemes/tero TERO - Terveyden ja hyvinvoinnin ontologia "fi"=>"TERO - Terveyden ja hyvinvoinnin ontologia"
5578 rma http://id.loc.gov/vocabulary/subjectSchemes/rma Ru'us al-mawdu'at al-'Arabiyah "ar"=>"Ru'us al-mawdu'at al-'Arabiyah"
5579 tgn http://id.loc.gov/vocabulary/subjectSchemes/tgn Getty thesaurus of geographic names
5580 tha http://id.loc.gov/vocabulary/subjectSchemes/tha Barcala de Moyano, Graciela G., Cristina Voena. Tesauro de Historia Argentina "es"=>"Barcala de Moyano, Graciela G., Cristina Voena. Tesauro de Historia Argentina"
5581 ttll http://id.loc.gov/vocabulary/subjectSchemes/ttll Roggau, Zunilda. Tell. Tesauro de lengua y literatura "es"=>"Roggau, Zunilda. Tell. Tesauro de lengua y literatura"
5582 sears http://id.loc.gov/vocabulary/subjectSchemes/sears Sears list of subject headings
5583 csht http://id.loc.gov/vocabulary/subjectSchemes/csht Chinese subject headings
5588 INSERT INTO authority.thesaurus (code, uri, name, control_set)
5589 SELECT code, uri, name, 1 FROM thesauri;
5591 UPDATE authority.thesaurus SET short_code = 'a' WHERE code = 'lcsh';
5592 UPDATE authority.thesaurus SET short_code = 'b' WHERE code = 'lcshac';
5593 UPDATE authority.thesaurus SET short_code = 'c' WHERE code = 'mesh';
5594 UPDATE authority.thesaurus SET short_code = 'd' WHERE code = 'nal';
5595 UPDATE authority.thesaurus SET short_code = 'k' WHERE code = 'cash';
5596 UPDATE authority.thesaurus SET short_code = 'r' WHERE code = 'aat';
5597 UPDATE authority.thesaurus SET short_code = 's' WHERE code = 'sears';
5598 UPDATE authority.thesaurus SET short_code = 'v' WHERE code = 'rvm';
5600 UPDATE authority.thesaurus
5601 SET short_code = 'z'
5602 WHERE short_code IS NULL
5603 AND control_set = 1;
5605 INSERT INTO config.i18n_core (fq_field, identity_value, translation, string )
5606 SELECT 'at.name', t.code, xlate->key, xlate->value
5608 JOIN LATERAL each(t.xlate) AS xlate ON TRUE
5611 FROM config.i18n_core
5612 WHERE fq_field = 'at.name'
5613 AND identity_value = t.code
5614 AND translation = xlate->key)
5615 AND t.xlate IS NOT NULL
5616 AND t.name <> (xlate->value);
5618 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
5622 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
5623 IF thes_code IS NULL THEN
5625 ELSIF thes_code = 'z' THEN
5626 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), 'z' );
5628 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
5630 thes_code := '|'; -- default
5635 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5637 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
5639 IF NEW.control_set IS NULL THEN
5640 SELECT control_set INTO NEW.control_set
5641 FROM authority.thesaurus
5642 WHERE code = authority.extract_thesaurus(NEW.marc);
5647 $func$ LANGUAGE PLPGSQL;
5649 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
5651 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
5652 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
5654 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
5655 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
5656 authority.extract_thesaurus(marc)
5657 FROM authority.record_entry
5661 $func$ LANGUAGE PLPGSQL;
5665 SELECT evergreen.upgrade_deps_block_check('1071', :eg_version); --gmcharlt/kmlussier
5667 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
5668 RETURNS SETOF metabib.flat_browse_entry_appearance
5677 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
5678 results_skipped INT := 0;
5679 row_counter INT := 0;
5684 all_records BIGINT[];
5685 all_brecords BIGINT[];
5686 all_arecords BIGINT[];
5687 superpage_of_records BIGINT[];
5692 unauthorized_entry RECORD;
5694 IF count_up_from_zero THEN
5701 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
5704 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
5705 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
5707 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
5709 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
5710 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
5712 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
5714 b_tests := b_tests || search.calculate_visibility_attribute_test(
5716 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
5719 b_tests := b_tests || search.calculate_visibility_attribute_test(
5721 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
5725 IF context_locations THEN
5726 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
5727 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
5730 OPEN curs NO SCROLL FOR EXECUTE query;
5733 FETCH curs INTO rec;
5735 IF result_row.pivot_point IS NOT NULL THEN
5736 RETURN NEXT result_row;
5742 SELECT INTO unauthorized_entry *
5743 FROM metabib.browse_entry_simple_heading_map mbeshm
5744 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
5745 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
5746 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
5747 WHERE mbeshm.entry = rec.id
5748 AND ahf.heading_purpose = 'variant';
5750 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
5751 IF (unauthorized_entry.record IS NOT NULL) THEN
5752 --unauthorized term belongs to an auth linked to a bib?
5753 SELECT INTO all_arecords, result_row.sees, afields
5754 ARRAY_AGG(DISTINCT abl.bib),
5755 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
5756 ARRAY_AGG(DISTINCT map.metabib_field)
5757 FROM authority.bib_linking abl
5758 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
5759 map.authority_field = unauthorized_entry.atag
5760 AND map.metabib_field = ANY(fields)
5762 WHERE abl.authority = unauthorized_entry.record;
5764 --do usual procedure
5765 SELECT INTO all_arecords, result_row.sees, afields
5766 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
5767 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
5768 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
5770 FROM metabib.browse_entry_simple_heading_map mbeshm
5771 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
5772 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
5773 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
5774 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
5775 ash.atag = map.authority_field
5776 AND map.metabib_field = ANY(fields)
5778 JOIN authority.control_set_authority_field acsaf ON (
5779 map.authority_field = acsaf.id
5781 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
5782 WHERE mbeshm.entry = rec.id
5783 AND ahf.heading_purpose = 'variant';
5787 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
5788 SELECT INTO all_brecords, result_row.authorities, bfields
5789 ARRAY_AGG(DISTINCT source),
5790 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
5791 ARRAY_AGG(DISTINCT def)
5792 FROM metabib.browse_entry_def_map
5793 WHERE entry = rec.id
5794 AND def = ANY(fields);
5796 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
5798 result_row.sources := 0;
5799 result_row.asources := 0;
5801 -- Bib-linked vis checking
5802 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
5804 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
5805 FROM biblio.record_entry b
5806 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
5807 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
5809 acvac.vis_attr_vector @@ c_tests::query_int
5810 OR b.vis_attr_vector @@ b_tests::query_int
5813 result_row.accurate := TRUE;
5817 -- Authority-linked vis checking
5818 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
5820 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
5821 FROM biblio.record_entry b
5822 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
5823 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
5825 acvac.vis_attr_vector @@ c_tests::query_int
5826 OR b.vis_attr_vector @@ b_tests::query_int
5829 result_row.aaccurate := TRUE;
5833 IF result_row.sources > 0 OR result_row.asources > 0 THEN
5835 -- The function that calls this function needs row_number in order
5836 -- to correctly order results from two different runs of this
5838 result_row.row_number := row_number;
5840 -- Now, if row_counter is still less than limit, return a row. If
5841 -- not, but it is less than next_pivot_pos, continue on without
5842 -- returning actual result rows until we find
5843 -- that next pivot, and return it.
5845 IF row_counter < result_limit THEN
5846 result_row.browse_entry := rec.id;
5847 result_row.value := rec.value;
5849 RETURN NEXT result_row;
5851 result_row.browse_entry := NULL;
5852 result_row.authorities := NULL;
5853 result_row.fields := NULL;
5854 result_row.value := NULL;
5855 result_row.sources := NULL;
5856 result_row.sees := NULL;
5857 result_row.accurate := NULL;
5858 result_row.aaccurate := NULL;
5859 result_row.pivot_point := rec.id;
5861 IF row_counter >= next_pivot_pos THEN
5862 RETURN NEXT result_row;
5867 IF count_up_from_zero THEN
5868 row_number := row_number + 1;
5870 row_number := row_number - 1;
5873 -- row_counter is different from row_number.
5874 -- It simply counts up from zero so that we know when
5875 -- we've reached our limit.
5876 row_counter := row_counter + 1;
5880 $f$ LANGUAGE plpgsql ROWS 10;
5882 CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10)
5883 RETURNS SETOF metabib.flat_browse_entry_appearance
5889 pivot_sort_value TEXT;
5890 pivot_sort_fallback TEXT;
5891 context_locations INT[];
5892 browse_superpage_size INT;
5893 results_skipped INT := 0;
5897 forward_to_pivot INT;
5899 -- First, find the pivot if we were given a browse term but not a pivot.
5900 IF pivot_id IS NULL THEN
5901 pivot_id := metabib.browse_pivot(search_field, browse_term);
5904 SELECT INTO pivot_sort_value, pivot_sort_fallback
5905 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
5907 -- Bail if we couldn't find a pivot.
5908 IF pivot_sort_value IS NULL THEN
5912 -- Transform the context_loc_group argument (if any) (logc at the
5913 -- TPAC layer) into a form we'll be able to use.
5914 IF context_loc_group IS NOT NULL THEN
5915 SELECT INTO context_locations ARRAY_AGG(location)
5916 FROM asset.copy_location_group_map
5917 WHERE lgroup = context_loc_group;
5920 -- Get the configured size of browse superpages.
5921 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
5922 FROM config.global_flag
5923 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
5925 -- First we're going to search backward from the pivot, then we're going
5926 -- to search forward. In each direction, we need two limits. At the
5927 -- lesser of the two limits, we delineate the edge of the result set
5928 -- we're going to return. At the greater of the two limits, we find the
5929 -- pivot value that would represent an offset from the current pivot
5930 -- at a distance of one "page" in either direction, where a "page" is a
5931 -- result set of the size specified in the "result_limit" argument.
5933 -- The two limits in each direction make four derived values in total,
5934 -- and we calculate them now.
5935 back_limit := CEIL(result_limit::FLOAT / 2);
5936 back_to_pivot := result_limit;
5937 forward_limit := result_limit / 2;
5938 forward_to_pivot := result_limit - 1;
5940 -- This is the meat of the SQL query that finds browse entries. We'll
5941 -- pass this to a function which uses it with a cursor, so that individual
5942 -- rows may be fetched in a loop until some condition is satisfied, without
5943 -- waiting for a result set of fixed size to be collected all at once.
5948 FROM metabib.browse_entry mbe
5950 EXISTS ( -- are there any bibs using this mbe via the requested fields?
5952 FROM metabib.browse_entry_def_map mbedm
5953 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
5954 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
5956 FROM metabib.browse_entry_simple_heading_map mbeshm
5957 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
5958 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
5959 ash.atag = map.authority_field
5960 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
5962 JOIN authority.control_set_authority_field acsaf ON (
5963 map.authority_field = acsaf.id
5965 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
5966 WHERE mbeshm.entry = mbe.id
5967 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
5968 -- and authority that variant is coming from is linked to a bib
5971 FROM metabib.browse_entry_def_map mbedm2
5972 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
5977 -- This is the variant of the query for browsing backward.
5978 back_query := core_query ||
5979 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
5980 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
5982 -- This variant browses forward.
5983 forward_query := core_query ||
5984 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
5985 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
5987 -- We now call the function which applies a cursor to the provided
5988 -- queries, stopping at the appropriate limits and also giving us
5989 -- the next page's pivot.
5991 SELECT * FROM metabib.staged_browse(
5992 back_query, search_field, context_org, context_locations,
5993 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
5995 SELECT * FROM metabib.staged_browse(
5996 forward_query, search_field, context_org, context_locations,
5997 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
5998 ) ORDER BY row_number DESC;
6001 $f$ LANGUAGE plpgsql ROWS 10;
6004 SELECT evergreen.upgrade_deps_block_check('1072', :eg_version); --gmcharlt/kmlussier
6006 INSERT INTO config.global_flag (name, label, enabled) VALUES (
6007 'opac.show_related_headings_in_browse',
6009 'opac.show_related_headings_in_browse',
6010 'Display related headings (see-also) in browse',
6019 SELECT evergreen.upgrade_deps_block_check('1073', :eg_version);
6021 ALTER TABLE config.metabib_field
6022 ADD COLUMN display_xpath TEXT,
6023 ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
6025 CREATE TABLE config.display_field_map (
6026 name TEXT PRIMARY KEY,
6027 field INTEGER REFERENCES config.metabib_field (id),
6028 multi BOOLEAN DEFAULT FALSE
6031 CREATE TABLE metabib.display_entry (
6032 id BIGSERIAL PRIMARY KEY,
6033 source BIGINT NOT NULL REFERENCES biblio.record_entry (id),
6034 field INT NOT NULL REFERENCES config.metabib_field (id),
6038 CREATE INDEX metabib_display_entry_field_idx ON metabib.display_entry (field);
6039 CREATE INDEX metabib_display_entry_source_idx ON metabib.display_entry (source);
6041 -- one row per display entry fleshed with field info
6042 CREATE VIEW metabib.flat_display_entry AS
6050 FROM metabib.display_entry mde
6051 JOIN config.metabib_field cmf ON (cmf.id = mde.field)
6052 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
6055 -- like flat_display_entry except values are compressed
6056 -- into one row per display_field_map and JSON-ified.
6057 CREATE VIEW metabib.compressed_display_entry AS
6064 CASE WHEN multi THEN
6065 TO_JSON(ARRAY_AGG(value))
6069 FROM metabib.flat_display_entry
6070 GROUP BY 1, 2, 3, 4, 5
6073 -- TODO: expand to encompass all well-known fields
6074 CREATE VIEW metabib.wide_display_entry AS
6077 COALESCE(mcde_title.value, 'null') AS title,
6078 COALESCE(mcde_author.value, 'null') AS author,
6079 COALESCE(mcde_subject.value, 'null') AS subject,
6080 COALESCE(mcde_creators.value, 'null') AS creators,
6081 COALESCE(mcde_isbn.value, 'null') AS isbn
6082 -- ensure one row per bre regardless of any display fields
6083 FROM biblio.record_entry bre
6084 LEFT JOIN metabib.compressed_display_entry mcde_title
6085 ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
6086 LEFT JOIN metabib.compressed_display_entry mcde_author
6087 ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
6088 LEFT JOIN metabib.compressed_display_entry mcde_subject
6089 ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
6090 LEFT JOIN metabib.compressed_display_entry mcde_creators
6091 ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
6092 LEFT JOIN metabib.compressed_display_entry mcde_isbn
6093 ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
6097 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
6098 RETURNS TRIGGER AS $$
6101 display_field_text TEXT;
6103 display_field_text := NEW.value;
6106 SELECT n.func AS func,
6107 n.param_count AS param_count,
6109 FROM config.index_normalizer n
6110 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
6111 WHERE m.field = NEW.field AND m.pos < 0
6114 EXECUTE 'SELECT ' || normalizer.func || '(' ||
6115 quote_literal( display_field_text ) ||
6117 WHEN normalizer.param_count > 0
6118 THEN ',' || REPLACE(REPLACE(BTRIM(
6119 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
6122 ')' INTO display_field_text;
6126 NEW.value = display_field_text;
6130 $$ LANGUAGE PLPGSQL;
6132 CREATE TRIGGER display_field_normalize_tgr
6133 BEFORE UPDATE OR INSERT ON metabib.display_entry
6134 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
6136 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
6137 RETURNS TRIGGER AS $$
6139 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
6142 $$ LANGUAGE PLPGSQL;
6144 CREATE TRIGGER display_field_force_nfc_tgr
6145 BEFORE UPDATE OR INSERT ON metabib.display_entry
6146 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
6148 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE display_field BOOL;
6150 DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL);
6151 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT);
6152 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT, TEXT);
6154 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
6156 default_joiner TEXT,
6159 ) RETURNS SETOF metabib.field_entry_template AS $func$
6161 bib biblio.record_entry%ROWTYPE;
6162 idx config.metabib_field%ROWTYPE;
6163 xfrm config.xml_transform%ROWTYPE;
6165 transformed_xml TEXT;
6167 xml_node_list TEXT[];
6174 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
6175 authority_text TEXT;
6176 authority_link BIGINT;
6177 output_row metabib.field_entry_template%ROWTYPE;
6181 -- Start out with no field-use bools set
6182 output_row.browse_field = FALSE;
6183 output_row.facet_field = FALSE;
6184 output_row.display_field = FALSE;
6185 output_row.search_field = FALSE;
6188 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
6190 -- Loop over the indexing entries
6191 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
6193 process_idx := FALSE;
6194 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
6195 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
6196 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
6197 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
6198 CONTINUE WHEN process_idx = FALSE;
6200 joiner := COALESCE(idx.joiner, default_joiner);
6202 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
6204 -- See if we can skip the XSLT ... it's expensive
6205 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
6206 -- Can't skip the transform
6207 IF xfrm.xslt <> '---' THEN
6208 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
6210 transformed_xml := bib.marc;
6213 prev_xfrm := xfrm.name;
6216 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6219 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
6220 CONTINUE WHEN xml_node !~ E'^\\s*<';
6222 -- XXX much of this should be moved into oils_xpath_string...
6223 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
6224 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
6225 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
6226 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
6230 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
6232 IF raw_text IS NOT NULL THEN
6233 raw_text := raw_text || joiner;
6236 raw_text := COALESCE(raw_text,'') || curr_text;
6238 -- autosuggest/metabib.browse_entry
6239 IF idx.browse_field THEN
6241 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
6242 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6244 browse_text := curr_text;
6247 IF idx.browse_sort_xpath IS NOT NULL AND
6248 idx.browse_sort_xpath <> '' THEN
6250 sort_value := oils_xpath_string(
6251 idx.browse_sort_xpath, xml_node, joiner,
6252 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
6255 sort_value := browse_text;
6258 output_row.field_class = idx.field_class;
6259 output_row.field = idx.id;
6260 output_row.source = rid;
6261 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
6262 output_row.sort_value :=
6263 public.naco_normalize(sort_value);
6265 output_row.authority := NULL;
6267 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
6268 authority_text := oils_xpath_string(
6269 idx.authority_xpath, xml_node, joiner,
6271 ARRAY[xfrm.prefix, xfrm.namespace_uri],
6272 ARRAY['xlink','http://www.w3.org/1999/xlink']
6276 IF authority_text ~ '^\d+$' THEN
6277 authority_link := authority_text::BIGINT;
6278 PERFORM * FROM authority.record_entry WHERE id = authority_link;
6280 output_row.authority := authority_link;
6286 output_row.browse_field = TRUE;
6287 -- Returning browse rows with search_field = true for search+browse
6288 -- configs allows us to retain granularity of being able to search
6289 -- browse fields with "starts with" type operators (for example, for
6290 -- titles of songs in music albums)
6291 IF idx.search_field THEN
6292 output_row.search_field = TRUE;
6294 RETURN NEXT output_row;
6295 output_row.browse_field = FALSE;
6296 output_row.search_field = FALSE;
6297 output_row.sort_value := NULL;
6300 -- insert raw node text for faceting
6301 IF idx.facet_field THEN
6303 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
6304 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6306 facet_text := curr_text;
6309 output_row.field_class = idx.field_class;
6310 output_row.field = -1 * idx.id;
6311 output_row.source = rid;
6312 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
6314 output_row.facet_field = TRUE;
6315 RETURN NEXT output_row;
6316 output_row.facet_field = FALSE;
6319 -- insert raw node text for display
6320 IF idx.display_field THEN
6322 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
6323 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6325 display_text := curr_text;
6328 output_row.field_class = idx.field_class;
6329 output_row.field = -1 * idx.id;
6330 output_row.source = rid;
6331 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
6333 output_row.display_field = TRUE;
6334 RETURN NEXT output_row;
6335 output_row.display_field = FALSE;
6340 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
6342 -- insert combined node text for searching
6343 IF idx.search_field THEN
6344 output_row.field_class = idx.field_class;
6345 output_row.field = idx.id;
6346 output_row.source = rid;
6347 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
6349 output_row.search_field = TRUE;
6350 RETURN NEXT output_row;
6351 output_row.search_field = FALSE;
6358 $func$ LANGUAGE PLPGSQL;
6360 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
6362 skip_facet BOOL DEFAULT FALSE,
6363 skip_display BOOL DEFAULT FALSE,
6364 skip_browse BOOL DEFAULT FALSE,
6365 skip_search BOOL DEFAULT FALSE,
6366 only_fields INT[] DEFAULT '{}'::INT[]
6367 ) RETURNS VOID AS $func$
6370 ind_data metabib.field_entry_template%ROWTYPE;
6371 mbe_row metabib.browse_entry%ROWTYPE;
6374 b_skip_display BOOL;
6378 field_list INT[] := only_fields;
6379 field_types TEXT[] := '{}'::TEXT[];
6382 IF field_list = '{}'::INT[] THEN
6383 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
6386 SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet;
6387 SELECT COALESCE(NULLIF(skip_display, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_display_indexing' AND enabled)) INTO b_skip_display;
6388 SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse;
6389 SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search;
6391 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
6392 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
6393 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
6394 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
6396 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
6398 IF NOT b_skip_search THEN
6399 FOR fclass IN SELECT * FROM config.metabib_class LOOP
6400 -- RAISE NOTICE 'Emptying out %', fclass.name;
6401 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
6404 IF NOT b_skip_facet THEN
6405 DELETE FROM metabib.facet_entry WHERE source = bib_id;
6407 IF NOT b_skip_display THEN
6408 DELETE FROM metabib.display_entry WHERE source = bib_id;
6410 IF NOT b_skip_browse THEN
6411 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
6415 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
6417 -- don't store what has been normalized away
6418 CONTINUE WHEN ind_data.value IS NULL;
6420 IF ind_data.field < 0 THEN
6421 ind_data.field = -1 * ind_data.field;
6424 IF ind_data.facet_field AND NOT b_skip_facet THEN
6425 INSERT INTO metabib.facet_entry (field, source, value)
6426 VALUES (ind_data.field, ind_data.source, ind_data.value);
6429 IF ind_data.display_field AND NOT b_skip_display THEN
6430 INSERT INTO metabib.display_entry (field, source, value)
6431 VALUES (ind_data.field, ind_data.source, ind_data.value);
6435 IF ind_data.browse_field AND NOT b_skip_browse THEN
6436 -- A caveat about this SELECT: this should take care of replacing
6437 -- old mbe rows when data changes, but not if normalization (by
6438 -- which I mean specifically the output of
6439 -- evergreen.oils_tsearch2()) changes. It may or may not be
6440 -- expensive to add a comparison of index_vector to index_vector
6441 -- to the WHERE clause below.
6443 CONTINUE WHEN ind_data.sort_value IS NULL;
6445 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
6446 SELECT INTO mbe_row * FROM metabib.browse_entry
6447 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
6450 mbe_id := mbe_row.id;
6452 INSERT INTO metabib.browse_entry
6453 ( value, sort_value ) VALUES
6454 ( value_prepped, ind_data.sort_value );
6456 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
6459 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
6460 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
6463 IF ind_data.search_field AND NOT b_skip_search THEN
6464 -- Avoid inserting duplicate rows
6465 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
6466 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
6467 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
6468 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
6469 IF mbe_id IS NULL THEN
6471 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
6473 quote_literal(ind_data.field) || $$, $$ ||
6474 quote_literal(ind_data.source) || $$, $$ ||
6475 quote_literal(ind_data.value) ||
6482 IF NOT b_skip_search THEN
6483 PERFORM metabib.update_combined_index_vectors(bib_id);
6488 $func$ LANGUAGE PLPGSQL;
6490 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
6491 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
6496 IF NEW.deleted THEN -- If this bib is deleted
6498 PERFORM * FROM config.internal_flag WHERE
6499 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
6501 tmp_bool := FOUND; -- Just in case this is changed by some other statement
6503 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
6505 IF NOT tmp_bool THEN
6506 -- One needs to keep these around to support searches
6507 -- with the #deleted modifier, so one should turn on the named
6508 -- internal flag for that functionality.
6509 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
6512 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
6513 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
6514 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
6515 RETURN NEW; -- and we're done
6518 IF TG_OP = 'UPDATE' THEN -- re-ingest?
6519 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
6521 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
6526 -- Record authority linking
6527 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
6529 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
6532 -- Flatten and insert the mfr data
6533 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
6535 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
6537 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
6538 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
6540 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
6544 -- Gather and insert the field entry data
6545 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
6547 -- Located URI magic
6548 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
6549 IF NOT FOUND THEN PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); END IF;
6551 -- (re)map metarecord-bib linking
6552 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
6553 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
6555 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6557 ELSE -- we're doing an update, and we're not deleted, remap
6558 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
6560 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6566 $func$ LANGUAGE PLPGSQL;
6571 SELECT evergreen.upgrade_deps_block_check('1074', :eg_version);
6573 INSERT INTO config.internal_flag (name, enabled)
6574 VALUES ('ingest.skip_display_indexing', FALSE);
6576 -- Adds seed data to replace (for now) values from the 'mvr' class
6578 UPDATE config.metabib_field SET display_field = TRUE WHERE id IN (6, 8, 16, 18);
6580 INSERT INTO config.metabib_field ( id, field_class, name, label,
6581 format, xpath, display_field, display_xpath ) VALUES
6582 (37, 'author', 'creator', oils_i18n_gettext(37, 'All Creators', 'cmf', 'label'),
6583 'mods32', $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']]$$,
6584 TRUE, $$//*[local-name()='namePart']$$ ); -- /* to fool vim */;
6587 UPDATE config.metabib_field SET display_xpath =
6588 $$//*[local-name()='namePart']$$ -- /* to fool vim */
6591 INSERT INTO config.display_field_map (name, field, multi) VALUES
6592 ('title', 6, FALSE),
6593 ('author', 8, FALSE),
6594 ('creators', 37, TRUE),
6595 ('subject', 16, TRUE),
6601 SELECT evergreen.upgrade_deps_block_check('1075', :eg_version);
6603 CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$
6605 IF NEW.imported_as IS NULL THEN
6608 PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as;
6610 RAISE foreign_key_violation USING MESSAGE = FORMAT(
6611 $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as
6616 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
6618 SELECT evergreen.upgrade_deps_block_check('1077', :eg_version); -- csharp/gmcharlt
6620 -- if the "public" version of this function exists, drop it to prevent confusion/trouble
6622 -- drop triggers that depend on this first
6623 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON biblio.record_entry;
6624 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON serial.record_entry;
6625 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON authority.record_entry;
6627 DROP FUNCTION IF EXISTS public.maintain_control_numbers();
6629 -- create the function within the "evergreen" schema
6631 CREATE OR REPLACE FUNCTION evergreen.maintain_control_numbers() RETURNS TRIGGER AS $func$
6634 use MARC::File::XML (BinaryEncoding => 'UTF-8');
6637 use Unicode::Normalize;
6639 MARC::Charset->assume_unicode(1);
6641 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
6642 my $schema = $_TD->{table_schema};
6643 my $rec_id = $_TD->{new}{id};
6645 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
6646 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
6647 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
6651 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
6652 my $ou_cni = 'EVRGRN';
6655 if ($schema eq 'serial') {
6656 $owner = $_TD->{new}{owning_lib};
6658 # are.owner and bre.owner can be null, so fall back to the consortial setting
6659 $owner = $_TD->{new}{owner} || 1;
6662 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
6663 if ($ous_rv->{processed}) {
6664 $ou_cni = $ous_rv->{rows}[0]->{value};
6665 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
6667 # Fall back to the shortname of the OU if there was no OU setting
6668 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
6669 if ($ous_rv->{processed}) {
6670 $ou_cni = $ous_rv->{rows}[0]->{shortname};
6674 my ($create, $munge) = (0, 0);
6676 my @scns = $record->field('035');
6678 foreach my $id_field ('001', '003') {
6680 my @controls = $record->field($id_field);
6682 if ($id_field eq '001') {
6683 $spec_value = $rec_id;
6685 $spec_value = $ou_cni;
6688 # Create the 001/003 if none exist
6689 if (scalar(@controls) == 1) {
6690 # Only one field; check to see if we need to munge it
6691 unless (grep $_->data() eq $spec_value, @controls) {
6695 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
6696 foreach my $control (@controls) {
6697 $record->delete_field($control);
6699 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
6704 my $cn = $record->field('001')->data();
6705 # Special handling of OCLC numbers, often found in records that lack 003
6706 if ($cn =~ /^o(c[nm]|n)\d/) {
6707 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
6708 $record->field('003')->data('OCoLC');
6712 # Now, if we need to munge the 001, we will first push the existing 001/003
6713 # into the 035; but if the record did not have one (and one only) 001 and 003
6714 # to begin with, skip this process
6715 if ($munge and not $create) {
6717 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
6719 # Do not create duplicate 035 fields
6720 unless (grep $_->subfield('a') eq $scn, @scns) {
6721 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
6725 # Set the 001/003 and update the MARC
6726 if ($create or $munge) {
6727 $record->field('001')->data($rec_id);
6728 $record->field('003')->data($ou_cni);
6730 my $xml = $record->as_xml_record();
6732 $xml =~ s/^<\?xml.+\?\s*>//go;
6733 $xml =~ s/>\s+</></go;
6734 $xml =~ s/\p{Cc}//go;
6736 # Embed a version of OpenILS::Application::AppUtils->entityize()
6737 # to avoid having to set PERL5LIB for PostgreSQL as well
6741 # Convert raw ampersands to entities
6742 $xml =~ s/&(?!\S+;)/&/gso;
6744 # Convert Unicode characters to entities
6745 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
6747 $xml =~ s/[\x00-\x1f]//go;
6748 $_TD->{new}{marc} = $xml;
6754 $func$ LANGUAGE PLPERLU;
6756 -- re-create the triggers
6757 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
6758 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
6759 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
6763 \echo ---------------------------------------------------------------------
6764 \echo Updating visibility attribute vector for biblio.record_entry
6767 ALTER TABLE biblio.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
6768 ALTER TABLE biblio.record_entry DISABLE TRIGGER aaa_indexing_ingest_or_delete;
6769 ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update_trigger;
6770 ALTER TABLE biblio.record_entry DISABLE TRIGGER b_maintain_901;
6771 ALTER TABLE biblio.record_entry DISABLE TRIGGER bbb_simple_rec_trigger;
6772 ALTER TABLE biblio.record_entry DISABLE TRIGGER c_maintain_control_numbers;
6773 ALTER TABLE biblio.record_entry DISABLE TRIGGER fingerprint_tgr;
6774 ALTER TABLE biblio.record_entry DISABLE TRIGGER z_opac_vis_mat_view_tgr;
6776 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id) WHERE NOT DELETED;
6778 ALTER TABLE biblio.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
6779 ALTER TABLE biblio.record_entry ENABLE TRIGGER aaa_indexing_ingest_or_delete;
6780 ALTER TABLE biblio.record_entry ENABLE TRIGGER audit_biblio_record_entry_update_trigger;
6781 ALTER TABLE biblio.record_entry ENABLE TRIGGER b_maintain_901;
6782 ALTER TABLE biblio.record_entry ENABLE TRIGGER bbb_simple_rec_trigger;
6783 ALTER TABLE biblio.record_entry ENABLE TRIGGER c_maintain_control_numbers;
6784 ALTER TABLE biblio.record_entry ENABLE TRIGGER fingerprint_tgr;
6785 ALTER TABLE biblio.record_entry ENABLE TRIGGER z_opac_vis_mat_view_tgr;
6789 \echo ---------------------------------------------------------------------
6790 \echo Reingest display fields. This can be canceled via Ctrl-C and run at
6791 \echo a later time with the following (or similar) SQL:
6793 \echo 'SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE, '
6794 \echo ' (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))'
6795 \echo ' FROM biblio.record_entry WHERE NOT deleted AND id > 0;'
6797 \echo Note that if you cancel now, you will also need to do the authority reingest
6798 \echo further down in the upgrade script.
6800 -- REINGEST DISPLAY ENTRIES
6801 SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE,
6802 (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))
6803 FROM biblio.record_entry WHERE NOT deleted AND id > 0;
6806 \echo ---------------------------------------------------------------------
6807 \echo Reingest authority records. This can be canceled via Ctrl-C and run
6808 \echo at a later time; see the upgrade script. Note that if you cancel now,
6809 \echo you should consult this upgrade script for the reingest actions required.
6812 -- add the flag ingest.disable_authority_full_rec if it does not exist
6813 INSERT INTO config.internal_flag (name, enabled)
6814 SELECT 'ingest.disable_authority_full_rec', FALSE
6815 WHERE NOT EXISTS (SELECT 1 FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec');
6817 CREATE TEMPORARY TABLE internal_flag_state AS
6818 SELECT name, enabled
6819 FROM config.internal_flag
6821 'ingest.reingest.force_on_same_marc',
6822 'ingest.disable_authority_auto_update',
6823 'ingest.disable_authority_full_rec'
6826 UPDATE config.internal_flag
6829 'ingest.reingest.force_on_same_marc',
6830 'ingest.disable_authority_auto_update',
6831 'ingest.disable_authority_full_rec'
6834 ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
6835 ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
6836 ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
6837 ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;
6839 UPDATE authority.record_entry SET id = id WHERE NOT DELETED;
6841 ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
6842 ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
6843 ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
6844 ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;
6847 UPDATE config.internal_flag a
6848 SET enabled = b.enabled
6849 FROM internal_flag_state b
6850 WHERE a.name = b.name;