1 --Upgrade Script for 2.12.5 to 3.0-beta1
2 \set eg_version '''3.0-beta1'''
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''';'
1146 SELECT evergreen.upgrade_deps_block_check('1055', :eg_version);
1148 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
1152 picklist_row RECORD;
1157 -- do some initial cleanup
1158 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
1159 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
1160 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
1164 DELETE FROM actor.card where usr = src_usr;
1166 IF deactivate_cards THEN
1167 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
1169 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
1174 DELETE FROM actor.usr_address WHERE usr = src_usr;
1176 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
1179 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
1180 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
1181 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
1182 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
1183 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
1186 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
1187 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
1188 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
1189 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
1194 -- For each *_bucket table: transfer every bucket belonging to src_usr
1195 -- into the custody of dest_usr.
1197 -- In order to avoid colliding with an existing bucket owned by
1198 -- the destination user, append the source user's id (in parenthesese)
1199 -- to the name. If you still get a collision, add successive
1200 -- spaces to the name and keep trying until you succeed.
1204 FROM container.biblio_record_entry_bucket
1205 WHERE owner = src_usr
1207 suffix := ' (' || src_usr || ')';
1210 UPDATE container.biblio_record_entry_bucket
1211 SET owner = dest_usr, name = name || suffix
1212 WHERE id = bucket_row.id;
1213 EXCEPTION WHEN unique_violation THEN
1214 suffix := suffix || ' ';
1223 FROM container.call_number_bucket
1224 WHERE owner = src_usr
1226 suffix := ' (' || src_usr || ')';
1229 UPDATE container.call_number_bucket
1230 SET owner = dest_usr, name = name || suffix
1231 WHERE id = bucket_row.id;
1232 EXCEPTION WHEN unique_violation THEN
1233 suffix := suffix || ' ';
1242 FROM container.copy_bucket
1243 WHERE owner = src_usr
1245 suffix := ' (' || src_usr || ')';
1248 UPDATE container.copy_bucket
1249 SET owner = dest_usr, name = name || suffix
1250 WHERE id = bucket_row.id;
1251 EXCEPTION WHEN unique_violation THEN
1252 suffix := suffix || ' ';
1261 FROM container.user_bucket
1262 WHERE owner = src_usr
1264 suffix := ' (' || src_usr || ')';
1267 UPDATE container.user_bucket
1268 SET owner = dest_usr, name = name || suffix
1269 WHERE id = bucket_row.id;
1270 EXCEPTION WHEN unique_violation THEN
1271 suffix := suffix || ' ';
1278 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
1281 -- transfer queues the same way we transfer buckets (see above)
1285 WHERE owner = src_usr
1287 suffix := ' (' || src_usr || ')';
1290 UPDATE vandelay.queue
1291 SET owner = dest_usr, name = name || suffix
1292 WHERE id = queue_row.id;
1293 EXCEPTION WHEN unique_violation THEN
1294 suffix := suffix || ' ';
1302 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
1303 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
1304 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
1305 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
1306 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
1309 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
1310 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
1311 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
1312 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
1314 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
1315 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
1316 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
1317 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
1319 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
1320 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
1321 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
1322 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
1323 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
1326 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
1327 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
1329 -- transfer picklists the same way we transfer buckets (see above)
1333 WHERE owner = src_usr
1335 suffix := ' (' || src_usr || ')';
1339 SET owner = dest_usr, name = name || suffix
1340 WHERE id = picklist_row.id;
1341 EXCEPTION WHEN unique_violation THEN
1342 suffix := suffix || ' ';
1349 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
1350 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
1351 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
1352 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
1353 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
1354 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
1355 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
1356 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
1359 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
1360 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
1361 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
1362 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
1363 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
1364 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
1367 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
1368 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
1371 -- It's not uncommon to define the reporter schema in a replica
1372 -- DB only, so don't assume these tables exist in the write DB.
1374 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
1375 EXCEPTION WHEN undefined_table THEN
1379 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
1380 EXCEPTION WHEN undefined_table THEN
1384 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
1385 EXCEPTION WHEN undefined_table THEN
1389 -- transfer folders the same way we transfer buckets (see above)
1392 FROM reporter.template_folder
1393 WHERE owner = src_usr
1395 suffix := ' (' || src_usr || ')';
1398 UPDATE reporter.template_folder
1399 SET owner = dest_usr, name = name || suffix
1400 WHERE id = folder_row.id;
1401 EXCEPTION WHEN unique_violation THEN
1402 suffix := suffix || ' ';
1408 EXCEPTION WHEN undefined_table THEN
1412 -- transfer folders the same way we transfer buckets (see above)
1415 FROM reporter.report_folder
1416 WHERE owner = src_usr
1418 suffix := ' (' || src_usr || ')';
1421 UPDATE reporter.report_folder
1422 SET owner = dest_usr, name = name || suffix
1423 WHERE id = folder_row.id;
1424 EXCEPTION WHEN unique_violation THEN
1425 suffix := suffix || ' ';
1431 EXCEPTION WHEN undefined_table THEN
1435 -- transfer folders the same way we transfer buckets (see above)
1438 FROM reporter.output_folder
1439 WHERE owner = src_usr
1441 suffix := ' (' || src_usr || ')';
1444 UPDATE reporter.output_folder
1445 SET owner = dest_usr, name = name || suffix
1446 WHERE id = folder_row.id;
1447 EXCEPTION WHEN unique_violation THEN
1448 suffix := suffix || ' ';
1454 EXCEPTION WHEN undefined_table THEN
1458 -- Finally, delete the source user
1459 DELETE FROM actor.usr WHERE id = src_usr;
1462 $$ LANGUAGE plpgsql;
1468 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1056', :eg_version); -- miker/gmcharlt
1470 INSERT INTO permission.perm_list (id,code,description) VALUES (592,'CONTAINER_BATCH_UPDATE','Allow batch update via buckets');
1472 INSERT INTO container.user_bucket_type (code,label) SELECT code,label FROM container.copy_bucket_type where code = 'staff_client';
1474 CREATE TABLE action.fieldset_group (
1475 id SERIAL PRIMARY KEY,
1477 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1478 complete_time TIMESTAMPTZ,
1479 container INT, -- Points to a container of some type ...
1480 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
1481 can_rollback BOOL DEFAULT TRUE,
1482 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1483 rollback_time TIMESTAMPTZ,
1484 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1485 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1488 ALTER TABLE action.fieldset ADD COLUMN fieldset_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1489 ALTER TABLE action.fieldset ADD COLUMN error_msg TEXT;
1490 ALTER TABLE container.biblio_record_entry_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1491 ALTER TABLE container.user_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1492 ALTER TABLE container.call_number_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1493 ALTER TABLE container.copy_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1495 UPDATE query.stored_query SET id = id + 1000 WHERE id < 1000;
1496 UPDATE query.from_relation SET id = id + 1000 WHERE id < 1000;
1497 UPDATE query.expression SET id = id + 1000 WHERE id < 1000;
1499 SELECT SETVAL('query.stored_query_id_seq', 1, FALSE);
1500 SELECT SETVAL('query.from_relation_id_seq', 1, FALSE);
1501 SELECT SETVAL('query.expression_id_seq', 1, FALSE);
1503 INSERT INTO query.bind_variable (name,type,description,label)
1504 SELECT 'bucket','number','ID of the bucket to pull items from','Bucket ID'
1505 WHERE NOT EXISTS (SELECT 1 FROM query.bind_variable WHERE name = 'bucket');
1507 -- Assumes completely empty 'query' schema
1508 INSERT INTO query.stored_query (type, use_distinct) VALUES ('SELECT', TRUE); -- 1
1510 INSERT INTO query.from_relation (type, table_name, class_name, table_alias) VALUES ('RELATION', 'container.user_bucket_item', 'cubi', 'cubi'); -- 1
1511 UPDATE query.stored_query SET from_clause = 1;
1513 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'target_user'); -- 1
1514 INSERT INTO query.select_item (stored_query,seq_no,expression) VALUES (1,1,1);
1516 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'bucket'); -- 2
1517 INSERT INTO query.expr_xbind (bind_variable) VALUES ('bucket'); -- 3
1519 INSERT INTO query.expr_xop (left_operand, operator, right_operand) VALUES (2, '=', 3); -- 4
1520 UPDATE query.stored_query SET where_clause = 4;
1522 SELECT SETVAL('query.stored_query_id_seq', 1000, TRUE) FROM query.stored_query;
1523 SELECT SETVAL('query.from_relation_id_seq', 1000, TRUE) FROM query.from_relation;
1524 SELECT SETVAL('query.expression_id_seq', 10000, TRUE) FROM query.expression;
1526 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1527 fieldset_id IN INT, -- id from action.fieldset
1528 table_name IN TEXT, -- table to be updated
1529 pkey_name IN TEXT, -- name of primary key column in that table
1530 query IN TEXT -- query constructed by qstore (for query-based
1531 -- fieldsets only; otherwise null
1547 fs_obj action.fieldset%ROWTYPE;
1548 fs_group action.fieldset_group%ROWTYPE;
1552 IF fieldset_id IS NULL THEN
1553 RETURN 'Fieldset ID parameter is NULL';
1555 IF table_name IS NULL THEN
1556 RETURN 'Table name parameter is NULL';
1558 IF pkey_name IS NULL THEN
1559 RETURN 'Primary key name parameter is NULL';
1564 quote_literal( pkey_value )
1574 -- Build the WHERE clause. This differs according to whether it's a
1575 -- single-row fieldset or a query-based fieldset.
1577 IF query IS NULL AND fs_pkey_value IS NULL THEN
1578 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1579 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1580 fs_query := rtrim( query, ';' );
1581 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1582 || fs_query || ' )';
1583 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1584 where_clause := 'WHERE ' || pkey_name || ' = ';
1585 IF pkey_name = 'id' THEN
1586 where_clause := where_clause || fs_pkey_value;
1587 ELSIF pkey_name = 'code' THEN
1588 where_clause := where_clause || quote_literal(fs_pkey_value);
1590 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1592 ELSE -- both are not null
1593 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1596 IF fs_status IS NULL THEN
1597 RETURN 'No fieldset found for id = ' || fieldset_id;
1598 ELSIF fs_status = 'APPLIED' THEN
1599 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1602 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1603 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1605 IF fs_group.can_rollback THEN
1606 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1608 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1609 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1611 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1613 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1614 IF pkey_name = 'id' THEN
1615 fs_pkey_value := rb_row.id;
1616 ELSIF pkey_name = 'code' THEN
1617 fs_pkey_value := rb_row.code;
1619 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1621 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1622 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);
1624 fs_id := CURRVAL('action.fieldset_id_seq');
1628 FROM action.fieldset_col_val
1629 WHERE fieldset = fieldset_id
1631 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1632 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1637 statement := 'UPDATE ' || table_name || ' SET';
1643 FROM action.fieldset_col_val
1644 WHERE fieldset = fieldset_id
1646 statement := statement || sep || ' ' || cv.col
1647 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1652 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1654 statement := statement || ' ' || where_clause;
1657 -- Execute the update
1661 GET DIAGNOSTICS update_count = ROW_COUNT;
1663 IF update_count = 0 THEN
1664 RAISE data_exception;
1667 IF fsg_id IS NOT NULL THEN
1668 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1671 IF fs_group.id IS NOT NULL THEN
1672 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1675 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1677 EXCEPTION WHEN data_exception THEN
1678 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1679 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1686 EXCEPTION WHEN OTHERS THEN
1687 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1688 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1692 $$ LANGUAGE plpgsql;
1696 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1057', :eg_version); -- miker/gmcharlt/kmlussier
1698 -- Thist change drops a needless join and saves 10-15% in time cost
1699 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
1701 SELECT id, value, count
1703 SELECT mfae.field AS id,
1705 COUNT(DISTINCT mfae.source),
1707 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
1709 FROM metabib.facet_entry mfae
1710 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
1711 WHERE mfae.source = ANY ($2)
1713 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
1718 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
1724 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$
1726 layout unapi.bre_output_layout%ROWTYPE;
1727 transform config.xml_transform%ROWTYPE;
1730 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
1732 element_list TEXT[];
1735 IF org = '-' OR org IS NULL THEN
1736 SELECT shortname INTO org FROM evergreen.org_top();
1739 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1740 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1742 IF layout.name IS NULL THEN
1746 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
1747 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
1749 -- Gather the bib xml
1750 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
1752 IF layout.title_element IS NOT NULL THEN
1753 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
1756 IF layout.description_element IS NOT NULL THEN
1757 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
1760 IF layout.creator_element IS NOT NULL THEN
1761 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
1764 IF layout.update_ts_element IS NOT NULL THEN
1765 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;
1768 IF unapi_url IS NOT NULL THEN
1769 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;
1772 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
1774 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
1775 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
1776 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
1779 RETURN tmp_xml::XML;
1781 $F$ LANGUAGE PLPGSQL STABLE;
1783 CREATE TABLE asset.copy_vis_attr_cache (
1784 id BIGSERIAL PRIMARY KEY,
1785 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
1786 target_copy BIGINT NOT NULL,
1787 vis_attr_vector INT[]
1789 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
1790 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
1792 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
1794 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
1797 WHEN 'luri_org' THEN 0 -- "b" attr
1798 WHEN 'bib_source' THEN 1 -- "b" attr
1800 WHEN 'copy_flags' THEN 0 -- "c" attr
1801 WHEN 'owning_lib' THEN 1 -- "c" attr
1802 WHEN 'circ_lib' THEN 2 -- "c" attr
1803 WHEN 'status' THEN 3 -- "c" attr
1804 WHEN 'location' THEN 4 -- "c" attr
1805 WHEN 'location_group' THEN 5 -- "c" attr
1809 /* copy_flags bit positions, LSB-first:
1811 0: asset.copy.opac_visible
1814 When adding flags, you must update asset.all_visible_flags()
1816 Because bib and copy values are stored separately, we can reuse
1817 shifts, saving us some space. We could probably take back a bit
1818 too, but I'm not sure its worth squeezing that last one out. We'd
1819 be left with just 2 slots for copy attrs, rather than 10.
1822 $f$ LANGUAGE SQL IMMUTABLE;
1824 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
1825 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
1826 $f$ LANGUAGE SQL IMMUTABLE;
1828 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
1829 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
1830 $f$ LANGUAGE SQL IMMUTABLE;
1832 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
1834 copy_row asset.copy%ROWTYPE;
1835 lgroup_map asset.copy_location_group_map%ROWTYPE;
1838 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
1840 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
1841 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
1842 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
1843 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
1845 SELECT ARRAY_APPEND(
1847 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
1849 FROM asset.call_number
1850 WHERE id = copy_row.call_number;
1852 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
1853 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
1858 $f$ LANGUAGE PLPGSQL;
1860 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
1862 bib_row biblio.record_entry%ROWTYPE;
1863 cn_row asset.call_number%ROWTYPE;
1866 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
1868 IF bib_row.source IS NOT NULL THEN
1869 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
1874 FROM asset.call_number cn
1875 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
1876 JOIN asset.uri u ON (u.id = m.uri)
1877 WHERE cn.record = bib_id
1878 AND cn.label = '##URI##'
1881 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
1886 $f$ LANGUAGE PLPGSQL;
1889 SELECT evergreen.upgrade_deps_block_check('1076', :eg_version); -- miker/gmcharlt
1891 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
1893 ocn asset.call_number%ROWTYPE;
1894 ncn asset.call_number%ROWTYPE;
1898 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
1899 IF TG_OP = 'INSERT' THEN
1900 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1903 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
1907 ELSIF TG_OP = 'DELETE' THEN
1908 DELETE FROM asset.copy_vis_attr_cache
1909 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
1915 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
1916 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
1917 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1918 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1921 asset.calculate_copy_visibility_attribute_set(NEW.id)
1923 ELSIF TG_TABLE_NAME = 'record_entry' THEN
1924 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1930 -- handle items first, since with circulation activity
1931 -- their statuses change frequently
1932 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
1934 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1935 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1939 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1941 IF OLD.deleted <> NEW.deleted THEN
1943 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1945 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1948 asset.calculate_copy_visibility_attribute_set(NEW.id)
1953 ELSIF OLD.call_number <> NEW.call_number THEN
1954 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
1956 IF ncn.record <> ocn.record THEN
1957 UPDATE biblio.record_entry
1958 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
1959 WHERE id = ocn.record;
1961 -- We have to use a record-specific WHERE clause
1962 -- to avoid modifying the entries for peer-bib copies.
1963 UPDATE asset.copy_vis_attr_cache
1964 SET target_copy = NEW.id,
1966 WHERE target_copy = OLD.id
1967 AND record = ocn.record;
1971 IF OLD.location <> NEW.location OR
1972 OLD.status <> NEW.status OR
1973 OLD.opac_visible <> NEW.opac_visible OR
1974 OLD.circ_lib <> NEW.circ_lib
1976 -- Any of these could change visibility, but
1977 -- we'll save some queries and not try to calculate
1978 -- the change directly. We want to update peer-bib
1979 -- entries in this case, unlike above.
1980 UPDATE asset.copy_vis_attr_cache
1981 SET target_copy = NEW.id,
1982 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
1983 WHERE target_copy = OLD.id;
1987 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
1989 IF OLD.record <> NEW.record THEN
1990 IF NEW.label = '##URI##' THEN
1991 UPDATE biblio.record_entry
1992 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1993 WHERE id = OLD.record;
1995 UPDATE biblio.record_entry
1996 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
1997 WHERE id = NEW.record;
2000 UPDATE asset.copy_vis_attr_cache
2001 SET record = NEW.record,
2002 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
2003 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
2004 AND record = OLD.record;
2006 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
2007 UPDATE asset.copy_vis_attr_cache
2008 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
2009 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
2010 AND record = NEW.record;
2012 IF NEW.label = '##URI##' THEN
2013 UPDATE biblio.record_entry
2014 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
2015 WHERE id = OLD.record;
2019 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
2021 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
2022 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
2024 ELSIF OLD.source <> NEW.source THEN
2025 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
2032 $func$ LANGUAGE PLPGSQL;
2035 -- Helper functions for use in constructing searches --
2037 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
2038 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
2039 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
2040 $f$ LANGUAGE SQL STABLE;
2042 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
2043 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
2046 $f$ LANGUAGE SQL STABLE;
2048 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
2049 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
2051 WHERE NOT opac_visible;
2052 $f$ LANGUAGE SQL STABLE;
2054 -- Bib-oriented defaults for search
2055 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
2056 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
2057 FROM config.bib_source
2059 $f$ LANGUAGE SQL IMMUTABLE;
2061 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
2062 SELECT * FROM asset.invisible_orgs('luri_org');
2063 $f$ LANGUAGE SQL STABLE;
2065 -- Copy-oriented defaults for search
2066 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
2067 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
2068 FROM asset.copy_location_group
2069 WHERE NOT opac_visible;
2070 $f$ LANGUAGE SQL STABLE;
2072 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
2073 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
2074 FROM asset.copy_location
2075 WHERE NOT opac_visible;
2076 $f$ LANGUAGE SQL STABLE;
2078 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
2079 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
2080 FROM config.copy_status
2081 WHERE NOT opac_visible;
2082 $f$ LANGUAGE SQL STABLE;
2084 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
2085 SELECT * FROM asset.invisible_orgs('owning_lib');
2086 $f$ LANGUAGE SQL STABLE;
2088 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
2089 SELECT * FROM asset.invisible_orgs('circ_lib');
2090 $f$ LANGUAGE SQL STABLE;
2092 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
2094 copy_flags TEXT; -- "c" attr
2096 owning_lib TEXT; -- "c" attr
2097 circ_lib TEXT; -- "c" attr
2098 status TEXT; -- "c" attr
2099 location TEXT; -- "c" attr
2100 location_group TEXT; -- "c" attr
2102 luri_org TEXT; -- "b" attr
2103 bib_sources TEXT; -- "b" attr
2105 copy_flags := asset.all_visible_flags(); -- Will always have at least one
2107 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
2109 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
2110 status := NULLIF(asset.status_default(),'!()');
2111 location := NULLIF(asset.location_default(),'!()');
2112 location_group := NULLIF(asset.location_group_default(),'!()');
2114 luri_org := NULLIF(asset.luri_org_default(),'!()');
2115 bib_sources := NULLIF(asset.bib_source_default(),'()');
2118 '('||ARRAY_TO_STRING(
2119 ARRAY[luri_org,bib_sources],
2122 '('||ARRAY_TO_STRING(
2123 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
2127 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
2129 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)
2130 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
2133 prepared_query_texts TEXT[];
2135 plain_query TSQUERY;
2136 opac_visibility_join TEXT;
2137 search_class_join TEXT;
2140 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
2142 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
2143 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
2145 visibility_org := NULLIF(visibility_org,-1);
2146 IF visibility_org IS NOT NULL THEN
2147 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
2149 opac_visibility_join := '';
2151 opac_visibility_join := '
2152 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
2153 JOIN vm ON (acvac.vis_attr_vector @@
2154 (vm.c_attrs || $$&$$ ||
2155 search.calculate_visibility_attribute_test(
2157 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
2164 opac_visibility_join := '';
2167 -- The following determines whether we only provide suggestsons matching
2168 -- the user's selected search_class, or whether we show other suggestions
2169 -- too. The reason for MIN() is that for search_classes like
2170 -- 'title|proper|uniform' you would otherwise get multiple rows. The
2171 -- implication is that if title as a class doesn't have restrict,
2172 -- nor does the proper field, but the uniform field does, you're going
2173 -- to get 'false' for your overall evaluation of 'should we restrict?'
2174 -- To invert that, change from MIN() to MAX().
2178 MIN(cmc.restrict::INT) AS restrict_class,
2179 MIN(cmf.restrict::INT) AS restrict_field
2180 FROM metabib.search_class_to_registered_components(search_class)
2181 AS _registered (field_class TEXT, field INT)
2183 config.metabib_class cmc ON (cmc.name = _registered.field_class)
2185 config.metabib_field cmf ON (cmf.id = _registered.field);
2187 -- evaluate 'should we restrict?'
2188 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
2189 search_class_join := '
2191 metabib.search_class_to_registered_components($2)
2192 AS _registered (field_class TEXT, field INT) ON (
2193 (_registered.field IS NULL AND
2194 _registered.field_class = cmf.field_class) OR
2195 (_registered.field = cmf.id)
2199 search_class_join := '
2201 metabib.search_class_to_registered_components($2)
2202 AS _registered (field_class TEXT, field INT) ON (
2203 _registered.field_class = cmc.name
2208 RETURN QUERY EXECUTE '
2209 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
2210 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
2219 TS_HEADLINE(value, $7, $3)
2220 FROM (SELECT DISTINCT
2223 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
2224 _registered.field = cmf.id AS restrict,
2226 TS_RANK_CD(mbe.index_vector, $1, $6),
2229 FROM metabib.browse_entry_def_map mbedm
2230 JOIN mbe ON (mbe.id = mbedm.entry)
2231 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
2232 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
2233 ' || search_class_join || '
2234 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2236 ' || opac_visibility_join || '
2237 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2239 ' -- sic, repeat the order by clause in the outer select too
2241 query, search_class, headline_opts,
2242 visibility_org, query_limit, normalization, plain_query
2246 -- buoyant AND chosen class = match class
2247 -- chosen field = match field
2254 $f$ LANGUAGE plpgsql ROWS 10;
2256 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)
2257 RETURNS SETOF metabib.flat_browse_entry_appearance
2263 pivot_sort_value TEXT;
2264 pivot_sort_fallback TEXT;
2265 context_locations INT[];
2266 browse_superpage_size INT;
2267 results_skipped INT := 0;
2271 forward_to_pivot INT;
2273 -- First, find the pivot if we were given a browse term but not a pivot.
2274 IF pivot_id IS NULL THEN
2275 pivot_id := metabib.browse_pivot(search_field, browse_term);
2278 SELECT INTO pivot_sort_value, pivot_sort_fallback
2279 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
2281 -- Bail if we couldn't find a pivot.
2282 IF pivot_sort_value IS NULL THEN
2286 -- Transform the context_loc_group argument (if any) (logc at the
2287 -- TPAC layer) into a form we'll be able to use.
2288 IF context_loc_group IS NOT NULL THEN
2289 SELECT INTO context_locations ARRAY_AGG(location)
2290 FROM asset.copy_location_group_map
2291 WHERE lgroup = context_loc_group;
2294 -- Get the configured size of browse superpages.
2295 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
2296 FROM config.global_flag
2297 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
2299 -- First we're going to search backward from the pivot, then we're going
2300 -- to search forward. In each direction, we need two limits. At the
2301 -- lesser of the two limits, we delineate the edge of the result set
2302 -- we're going to return. At the greater of the two limits, we find the
2303 -- pivot value that would represent an offset from the current pivot
2304 -- at a distance of one "page" in either direction, where a "page" is a
2305 -- result set of the size specified in the "result_limit" argument.
2307 -- The two limits in each direction make four derived values in total,
2308 -- and we calculate them now.
2309 back_limit := CEIL(result_limit::FLOAT / 2);
2310 back_to_pivot := result_limit;
2311 forward_limit := result_limit / 2;
2312 forward_to_pivot := result_limit - 1;
2314 -- This is the meat of the SQL query that finds browse entries. We'll
2315 -- pass this to a function which uses it with a cursor, so that individual
2316 -- rows may be fetched in a loop until some condition is satisfied, without
2317 -- waiting for a result set of fixed size to be collected all at once.
2322 FROM metabib.browse_entry mbe
2324 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2326 FROM metabib.browse_entry_def_map mbedm
2327 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2328 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2330 FROM metabib.browse_entry_simple_heading_map mbeshm
2331 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2332 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2333 ash.atag = map.authority_field
2334 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2336 WHERE mbeshm.entry = mbe.id
2340 -- This is the variant of the query for browsing backward.
2341 back_query := core_query ||
2342 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2343 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
2345 -- This variant browses forward.
2346 forward_query := core_query ||
2347 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2348 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
2350 -- We now call the function which applies a cursor to the provided
2351 -- queries, stopping at the appropriate limits and also giving us
2352 -- the next page's pivot.
2354 SELECT * FROM metabib.staged_browse(
2355 back_query, search_field, context_org, context_locations,
2356 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2358 SELECT * FROM metabib.staged_browse(
2359 forward_query, search_field, context_org, context_locations,
2360 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2361 ) ORDER BY row_number DESC;
2364 $f$ LANGUAGE plpgsql ROWS 10;
2366 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)
2367 RETURNS SETOF metabib.flat_browse_entry_appearance
2376 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2377 results_skipped INT := 0;
2378 row_counter INT := 0;
2383 all_records BIGINT[];
2384 all_brecords BIGINT[];
2385 all_arecords BIGINT[];
2386 superpage_of_records BIGINT[];
2392 IF count_up_from_zero THEN
2399 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
2402 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2403 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
2405 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
2407 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
2408 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
2410 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
2412 b_tests := b_tests || search.calculate_visibility_attribute_test(
2414 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
2417 b_tests := b_tests || search.calculate_visibility_attribute_test(
2419 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
2423 IF context_locations THEN
2424 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2425 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
2428 OPEN curs NO SCROLL FOR EXECUTE query;
2431 FETCH curs INTO rec;
2433 IF result_row.pivot_point IS NOT NULL THEN
2434 RETURN NEXT result_row;
2439 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2440 SELECT INTO all_arecords, result_row.sees, afields
2441 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2442 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2443 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2445 FROM metabib.browse_entry_simple_heading_map mbeshm
2446 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2447 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2448 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2449 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2450 ash.atag = map.authority_field
2451 AND map.metabib_field = ANY(fields)
2453 WHERE mbeshm.entry = rec.id;
2455 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2456 SELECT INTO all_brecords, result_row.authorities, bfields
2457 ARRAY_AGG(DISTINCT source),
2458 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2459 ARRAY_AGG(DISTINCT def)
2460 FROM metabib.browse_entry_def_map
2461 WHERE entry = rec.id
2462 AND def = ANY(fields);
2464 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2466 result_row.sources := 0;
2467 result_row.asources := 0;
2469 -- Bib-linked vis checking
2470 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2472 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
2473 FROM biblio.record_entry b
2474 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2475 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
2477 acvac.vis_attr_vector @@ c_tests::query_int
2478 OR b.vis_attr_vector @@ b_tests::query_int
2481 result_row.accurate := TRUE;
2485 -- Authority-linked vis checking
2486 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2488 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
2489 FROM biblio.record_entry b
2490 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2491 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
2493 acvac.vis_attr_vector @@ c_tests::query_int
2494 OR b.vis_attr_vector @@ b_tests::query_int
2497 result_row.aaccurate := TRUE;
2501 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2503 -- The function that calls this function needs row_number in order
2504 -- to correctly order results from two different runs of this
2506 result_row.row_number := row_number;
2508 -- Now, if row_counter is still less than limit, return a row. If
2509 -- not, but it is less than next_pivot_pos, continue on without
2510 -- returning actual result rows until we find
2511 -- that next pivot, and return it.
2513 IF row_counter < result_limit THEN
2514 result_row.browse_entry := rec.id;
2515 result_row.value := rec.value;
2517 RETURN NEXT result_row;
2519 result_row.browse_entry := NULL;
2520 result_row.authorities := NULL;
2521 result_row.fields := NULL;
2522 result_row.value := NULL;
2523 result_row.sources := NULL;
2524 result_row.sees := NULL;
2525 result_row.accurate := NULL;
2526 result_row.aaccurate := NULL;
2527 result_row.pivot_point := rec.id;
2529 IF row_counter >= next_pivot_pos THEN
2530 RETURN NEXT result_row;
2535 IF count_up_from_zero THEN
2536 row_number := row_number + 1;
2538 row_number := row_number - 1;
2541 -- row_counter is different from row_number.
2542 -- It simply counts up from zero so that we know when
2543 -- we've reached our limit.
2544 row_counter := row_counter + 1;
2548 $f$ LANGUAGE plpgsql ROWS 10;
2550 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
2551 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
2552 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
2553 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
2554 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
2555 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
2556 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
2557 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
2559 -- Upgrade the data!
2560 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
2563 asset.calculate_copy_visibility_attribute_set(cp.id)
2565 JOIN asset.call_number cn ON (cp.call_number = cn.id);
2567 -- updating vis cache for biblio.record_entry deferred to end
2569 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();
2570 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();
2571 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2572 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2573 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2574 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2575 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2577 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$
2582 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;
2584 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
2586 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2587 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2588 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2592 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2598 asset.copy_vis_attr_cache av
2599 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2600 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2604 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2611 $f$ LANGUAGE PLPGSQL;
2613 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$
2618 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;
2620 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2622 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2623 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2624 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2628 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2633 asset.copy_vis_attr_cache av
2634 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2635 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2639 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2646 $f$ LANGUAGE PLPGSQL;
2648 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$
2653 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;
2655 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
2657 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2658 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2659 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2663 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2669 asset.copy_vis_attr_cache av
2670 JOIN asset.copy cp ON (cp.id = av.target_copy)
2671 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2672 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2676 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2683 $f$ LANGUAGE PLPGSQL;
2685 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$
2690 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;
2692 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2694 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2695 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2696 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2700 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2706 asset.copy_vis_attr_cache av
2707 JOIN asset.copy cp ON (cp.id = av.target_copy)
2708 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2709 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2713 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2720 $f$ LANGUAGE PLPGSQL;
2722 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
2728 depth INT DEFAULT NULL,
2729 slimit HSTORE DEFAULT NULL,
2730 soffset HSTORE DEFAULT NULL,
2731 include_xmlns BOOL DEFAULT TRUE,
2732 pref_lib INT DEFAULT NULL
2733 ) RETURNS XML AS $F$
2737 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
2738 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
2740 (SELECT XMLAGG(foo.y)
2742 WITH sourcelist AS (
2743 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
2744 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2745 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
2746 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
2748 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
2749 WHERE mmsm.metarecord = $1 AND (
2752 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
2753 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2754 AND acvac.record = mmsm.source
2756 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
2757 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)
2765 cmra.value AS "coded-value",
2766 cmra.aid AS "cvmid",
2776 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
2778 SELECT v.source AS id,
2782 FROM metabib.record_attr_vector_list v
2783 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
2785 JOIN sourcelist ON (x.id = sourcelist.source)
2788 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
2803 SELECT DISTINCT aid, attr, value
2805 SELECT v.source AS id,
2809 FROM metabib.record_attr_vector_list v
2810 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
2812 JOIN sourcelist ON (x.id = sourcelist.source)
2814 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
2820 $F$ LANGUAGE SQL STABLE;
2822 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
2825 depth INT DEFAULT NULL,
2826 slimit HSTORE DEFAULT NULL,
2827 soffset HSTORE DEFAULT NULL,
2828 pref_lib INT DEFAULT NULL,
2829 includes TEXT[] DEFAULT NULL::TEXT[]
2830 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
2831 WITH RECURSIVE ou_depth AS (
2836 FROM actor.org_unit_type aout
2837 INNER JOIN actor.org_unit ou ON ou_type = aout.id
2841 ), descendant_depth AS (
2845 FROM actor.org_unit ou
2846 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2847 JOIN anscestor_depth ad ON (ad.id = ou.id),
2849 WHERE ad.depth = ou_depth.depth
2854 FROM actor.org_unit ou
2855 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2856 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2857 ), anscestor_depth AS (
2861 FROM actor.org_unit ou
2862 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2868 FROM actor.org_unit ou
2869 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2870 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
2872 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
2875 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
2876 SELECT acn.id, owning_lib.name, acn.label_sortkey,
2877 evergreen.rank_cp(acp),
2879 FROM asset.call_number acn
2880 JOIN asset.copy acp ON (acn.id = acp.call_number)
2881 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
2882 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
2883 WHERE acn.record = ANY ($1)
2884 AND acn.deleted IS FALSE
2885 AND acp.deleted IS FALSE
2886 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
2888 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2889 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
2891 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
2892 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2893 AND acvac.target_copy = acp.id
2894 AND acvac.record = acn.record
2896 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
2900 CASE WHEN aou.id = $2 THEN -20000 END,
2901 CASE WHEN aou.id = $6 THEN -10000 END,
2902 (SELECT distance - 5000
2903 FROM actor.org_unit_descendants_distance($6) as x
2904 WHERE x.id = aou.id AND $6 IN (
2905 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
2906 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
2909 evergreen.rank_cp(acp)
2912 GROUP BY ua.id, ua.name, ua.label_sortkey
2913 ORDER BY rank, ua.name, ua.label_sortkey
2914 LIMIT ($4 -> 'acn')::INT
2915 OFFSET ($5 -> 'acn')::INT;
2916 $$ LANGUAGE SQL STABLE ROWS 10;
2919 -- Evergreen DB patch XXXX.schema.action-trigger.event_definition.sms_preminder.sql
2921 -- New action trigger event definition: 3 Day Courtesy Notice by SMS
2924 -- check whether patch can be applied
2925 SELECT evergreen.upgrade_deps_block_check('1058', :eg_version); -- mccanna/csharp/gmcharlt
2927 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook,
2928 validator, reactor, delay, max_delay, delay_field, group_field, template)
2929 VALUES (54, FALSE, 1,
2930 '3 Day Courtesy Notice by SMS',
2932 'CircIsOpen', 'SendSMS', '-3 days', '-2 days', 'due_date', 'usr',
2935 [%- user = target.0.usr -%]
2936 [%- homelib = user.home_ou -%]
2937 [%- sms_number = helpers.get_user_setting(user.id, 'opac.default_sms_notify') -%]
2938 [%- sms_carrier = helpers.get_user_setting(user.id, 'opac.default_sms_carrier') -%]
2939 From: [%- helpers.get_org_setting(homelib.id, 'org.bounced_emails') || homelib.email || params.sender_email || default_sender %]
2940 To: [%- helpers.get_sms_gateway_email(sms_carrier,sms_number) %]
2941 Subject: Library Materials Due Soon
2943 You have items due soon:
2945 [% FOR circ IN target %]
2946 [%- copy_details = helpers.get_copy_bib_basics(circ.target_copy.id) -%]
2947 [% copy_details.title FILTER ucfirst %] by [% copy_details.author FILTER ucfirst %] due on [% date.format(helpers.format_date(circ.due_date), '%m-%d-%Y') %]
2953 INSERT INTO action_trigger.environment (event_def, path) VALUES
2954 (54, 'circ_lib.billing_address'),
2955 (54, 'target_copy.call_number'),
2957 (54, 'usr.home_ou');
2960 -- check whether patch can be applied
2961 SELECT evergreen.upgrade_deps_block_check('1059', :eg_version); --Stompro/DPearl/kmlussier
2963 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
2969 CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title,
2970 FIRST(author.value) AS author,
2971 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
2972 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
2973 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
2975 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
2977 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
2979 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
2981 FROM biblio.record_entry r
2982 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
2983 LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear.
2984 SELECT b.record, string_agg(val, ' ') AS val FROM (
2985 SELECT title_np.record, title_np.value AS val
2986 FROM metabib.full_rec title_np
2988 title_np.tag = '245'
2989 AND title_np.subfield IN ('p','n')
2990 ORDER BY title_np.id
2993 ) title_np ON (title_np.record=r.id)
2994 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
2995 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')
2996 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')
2997 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
2998 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
3002 -- Remove trigger on biblio.record_entry
3003 SELECT reporter.disable_materialized_simple_record_trigger();
3005 -- Rebuild reporter.materialized_simple_record
3006 SELECT reporter.enable_materialized_simple_record_trigger();
3009 SELECT evergreen.upgrade_deps_block_check('1060', :eg_version);
3011 DROP VIEW IF EXISTS extend_reporter.copy_count_per_org;
3014 CREATE OR REPLACE VIEW extend_reporter.copy_count_per_org AS
3015 SELECT acn.record AS bibid,
3018 max(ac.edit_date) AS last_edit_time,
3019 min(ac.deleted::integer) AS has_only_deleted_copies,
3022 WHEN ac.deleted THEN ac.id
3024 END) AS deleted_count,
3027 WHEN NOT ac.deleted THEN ac.id
3029 END) AS visible_count,
3030 count(*) AS total_count
3031 FROM asset.call_number acn,
3033 WHERE ac.call_number = acn.id
3034 GROUP BY acn.record, acn.owning_lib, ac.circ_lib;
3038 SELECT evergreen.upgrade_deps_block_check('1061', :eg_version);
3040 INSERT INTO config.org_unit_setting_type
3041 (name, label, description, grp, datatype)
3043 'ui.staff.max_recent_patrons',
3045 'ui.staff.max_recent_patrons',
3046 'Number of Retrievable Recent Patrons',
3051 'ui.staff.max_recent_patrons',
3052 'Number of most recently accessed patrons that can be re-retrieved ' ||
3053 'in the staff client. A value of 0 or less disables the feature. Defaults to 1.',
3062 SELECT evergreen.upgrade_deps_block_check('1062', :eg_version);
3064 CREATE TABLE acq.edi_attr (
3065 key TEXT PRIMARY KEY,
3066 label TEXT NOT NULL UNIQUE
3069 CREATE TABLE acq.edi_attr_set (
3070 id SERIAL PRIMARY KEY,
3071 label TEXT NOT NULL UNIQUE
3074 CREATE TABLE acq.edi_attr_set_map (
3075 id SERIAL PRIMARY KEY,
3076 attr_set INTEGER NOT NULL REFERENCES acq.edi_attr_set(id)
3077 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3078 attr TEXT NOT NULL REFERENCES acq.edi_attr(key)
3079 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3080 CONSTRAINT edi_attr_set_map_attr_once UNIQUE (attr_set, attr)
3083 -- An attr_set is not strictly required, since some edi_accounts/vendors
3084 -- may not need to apply any attributes.
3085 ALTER TABLE acq.edi_account
3086 ADD COLUMN attr_set INTEGER REFERENCES acq.edi_attr_set(id),
3087 ADD COLUMN use_attrs BOOLEAN NOT NULL DEFAULT FALSE;
3092 SELECT evergreen.upgrade_deps_block_check('1063', :eg_version);
3099 FOR r IN SELECT t.table_schema AS sname,
3100 t.table_name AS tname,
3101 t.column_name AS colname,
3103 FROM information_schema.referential_constraints ref
3104 JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
3105 WHERE ref.unique_constraint_schema = 'asset'
3106 AND ref.unique_constraint_name = 'copy_pkey'
3109 EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
3112 CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
3114 PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
3116 RAISE foreign_key_violation USING MESSAGE = FORMAT(
3117 $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
3122 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
3126 CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
3127 AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
3128 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
3136 SELECT evergreen.upgrade_deps_block_check('1064', :eg_version);
3138 ALTER TABLE serial.issuance DROP CONSTRAINT IF EXISTS issuance_caption_and_pattern_fkey;
3140 -- Using NOT VALID and VALIDATE CONSTRAINT limits the impact to concurrent work.
3141 -- For details, see: https://www.postgresql.org/docs/current/static/sql-altertable.html
3143 ALTER TABLE serial.issuance ADD CONSTRAINT issuance_caption_and_pattern_fkey
3144 FOREIGN KEY (caption_and_pattern)
3145 REFERENCES serial.caption_and_pattern (id)
3147 DEFERRABLE INITIALLY DEFERRED
3150 ALTER TABLE serial.issuance VALIDATE CONSTRAINT issuance_caption_and_pattern_fkey;
3154 SELECT evergreen.upgrade_deps_block_check('1065', :eg_version);
3156 CREATE TABLE serial.pattern_template (
3157 id SERIAL PRIMARY KEY,
3159 pattern_code TEXT NOT NULL,
3160 owning_lib INTEGER REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
3161 share_depth INTEGER NOT NULL DEFAULT 0
3163 CREATE INDEX serial_pattern_template_name_idx ON serial.pattern_template (evergreen.lowercase(name));
3165 CREATE OR REPLACE FUNCTION serial.pattern_templates_visible_to(org_unit INT) RETURNS SETOF serial.pattern_template AS $func$
3167 RETURN QUERY SELECT *
3168 FROM serial.pattern_template spt
3170 SELECT ARRAY_AGG(id)
3171 FROM actor.org_unit_descendants(spt.owning_lib, spt.share_depth)
3172 ) @@ org_unit::TEXT::QUERY_INT;
3174 $func$ LANGUAGE PLPGSQL;
3177 SELECT evergreen.upgrade_deps_block_check('1066', :eg_version);
3179 INSERT INTO permission.perm_list ( id, code, description ) VALUES
3180 ( 593, 'ADMIN_SERIAL_PATTERN_TEMPLATE', oils_i18n_gettext( 593,
3181 'Administer serial prediction pattern templates', 'ppl', 'description' ))
3184 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
3186 pgt.id, perm.id, aout.depth, FALSE
3188 permission.grp_tree pgt,
3189 permission.perm_list perm,
3190 actor.org_unit_type aout
3192 pgt.name = 'Serials' AND
3193 aout.name = 'System' AND
3195 'ADMIN_SERIAL_PATTERN_TEMPLATE'
3199 SELECT evergreen.upgrade_deps_block_check('1067', :eg_version);
3201 INSERT INTO acq.edi_attr (key, label) VALUES
3203 oils_i18n_gettext('INCLUDE_PO_NAME',
3204 'Orders Include PO Name', 'aea', 'label')),
3206 oils_i18n_gettext('INCLUDE_COPIES',
3207 'Orders Include Copy Data', 'aea', 'label')),
3209 oils_i18n_gettext('INCLUDE_FUND',
3210 'Orders Include Copy Funds', 'aea', 'label')),
3211 ('INCLUDE_CALL_NUMBER',
3212 oils_i18n_gettext('INCLUDE_CALL_NUMBER',
3213 'Orders Include Copy Call Numbers', 'aea', 'label')),
3214 ('INCLUDE_ITEM_TYPE',
3215 oils_i18n_gettext('INCLUDE_ITEM_TYPE',
3216 'Orders Include Copy Item Types', 'aea', 'label')),
3217 ('INCLUDE_ITEM_BARCODE',
3218 oils_i18n_gettext('INCLUDE_ITEM_BARCODE',
3219 'Orders Include Copy Barcodes', 'aea', 'label')),
3220 ('INCLUDE_LOCATION',
3221 oils_i18n_gettext('INCLUDE_LOCATION',
3222 'Orders Include Copy Locations', 'aea', 'label')),
3223 ('INCLUDE_COLLECTION_CODE',
3224 oils_i18n_gettext('INCLUDE_COLLECTION_CODE',
3225 'Orders Include Copy Collection Codes', 'aea', 'label')),
3226 ('INCLUDE_OWNING_LIB',
3227 oils_i18n_gettext('INCLUDE_OWNING_LIB',
3228 'Orders Include Copy Owning Library', 'aea', 'label')),
3229 ('USE_ID_FOR_OWNING_LIB',
3230 oils_i18n_gettext('USE_ID_FOR_OWNING_LIB',
3231 'Emit Owning Library ID Rather Than Short Name. Takes effect only if INCLUDE_OWNING_LIB is in use', 'aea', 'label')),
3232 ('INCLUDE_QUANTITY',
3233 oils_i18n_gettext('INCLUDE_QUANTITY',
3234 'Orders Include Copy Quantities', 'aea', 'label')),
3236 oils_i18n_gettext('INCLUDE_COPY_ID',
3237 'Orders Include Copy IDs', 'aea', 'label')),
3238 ('BUYER_ID_INCLUDE_VENDCODE',
3239 oils_i18n_gettext('BUYER_ID_INCLUDE_VENDCODE',
3240 'Buyer ID Qualifier Includes Vendcode', 'aea', 'label')),
3241 ('BUYER_ID_ONLY_VENDCODE',
3242 oils_i18n_gettext('BUYER_ID_ONLY_VENDCODE',
3243 'Buyer ID Qualifier Only Contains Vendcode', 'aea', 'label')),
3244 ('INCLUDE_BIB_EDITION',
3245 oils_i18n_gettext('INCLUDE_BIB_EDITION',
3246 'Order Lineitems Include Edition Info', 'aea', 'label')),
3247 ('INCLUDE_BIB_AUTHOR',
3248 oils_i18n_gettext('INCLUDE_BIB_AUTHOR',
3249 'Order Lineitems Include Author Info', 'aea', 'label')),
3250 ('INCLUDE_BIB_PAGINATION',
3251 oils_i18n_gettext('INCLUDE_BIB_PAGINATION',
3252 'Order Lineitems Include Pagination Info', 'aea', 'label')),
3254 oils_i18n_gettext('COPY_SPEC_CODES',
3255 'Order Lineitem Notes Include Copy Spec Codes', 'aea', 'label')),
3256 ('INCLUDE_EMPTY_IMD_VALUES',
3257 oils_i18n_gettext('INCLUDE_EMPTY_IMD_VALUES',
3258 'Lineitem Title, Author, etc. Fields Are Present Even if Empty', 'aea', 'label')),
3259 ('INCLUDE_EMPTY_LI_NOTE',
3260 oils_i18n_gettext('INCLUDE_EMPTY_LI_NOTE',
3261 'Order Lineitem Notes Always Present (Even if Empty)', 'aea', 'label')),
3262 ('INCLUDE_EMPTY_CALL_NUMBER',
3263 oils_i18n_gettext('INCLUDE_EMPTY_CALL_NUMBER',
3264 'Order Copies Always Include Call Number (Even if Empty)', 'aea', 'label')),
3265 ('INCLUDE_EMPTY_ITEM_TYPE',
3266 oils_i18n_gettext('INCLUDE_EMPTY_ITEM_TYPE',
3267 'Order Copies Always Include Item Type (Even if Empty)', 'aea', 'label')),
3268 ('INCLUDE_EMPTY_LOCATION',
3269 oils_i18n_gettext('INCLUDE_EMPTY_LOCATION',
3270 'Order Copies Always Include Location (Even if Empty)', 'aea', 'label')),
3271 ('INCLUDE_EMPTY_COLLECTION_CODE',
3272 oils_i18n_gettext('INCLUDE_EMPTY_COLLECTION_CODE',
3273 'Order Copies Always Include Collection Code (Even if Empty)', 'aea', 'label')),
3274 ('LINEITEM_IDENT_VENDOR_NUMBER',
3275 oils_i18n_gettext('LINEITEM_IDENT_VENDOR_NUMBER',
3276 'Lineitem Identifier Fields (LIN/PIA) Use Vendor-Encoded ID Value When Available', 'aea', 'label')),
3277 ('LINEITEM_REF_ID_ONLY',
3278 oils_i18n_gettext('LINEITEM_REF_ID_ONLY',
3279 'Lineitem Reference Field (RFF) Uses Lineitem ID Only', 'aea', 'label'))
3283 INSERT INTO acq.edi_attr_set (id, label) VALUES (1, 'Ingram Default');
3284 INSERT INTO acq.edi_attr_set (id, label) VALUES (2, 'Baker & Taylor Default');
3285 INSERT INTO acq.edi_attr_set (id, label) VALUES (3, 'Brodart Default');
3286 INSERT INTO acq.edi_attr_set (id, label) VALUES (4, 'Midwest Tape Default');
3287 INSERT INTO acq.edi_attr_set (id, label) VALUES (5, 'ULS Default');
3288 INSERT INTO acq.edi_attr_set (id, label) VALUES (6, 'Recorded Books Default');
3289 INSERT INTO acq.edi_attr_set (id, label) VALUES (7, 'Midwest Library Service');
3291 -- carve out space for mucho defaults
3292 SELECT SETVAL('acq.edi_attr_set_id_seq'::TEXT, 1000);
3294 INSERT INTO acq.edi_attr_set_map (attr_set, attr) VALUES
3297 (1, 'INCLUDE_PO_NAME'),
3298 (1, 'INCLUDE_COPIES'),
3299 (1, 'INCLUDE_ITEM_TYPE'),
3300 (1, 'INCLUDE_COLLECTION_CODE'),
3301 (1, 'INCLUDE_OWNING_LIB'),
3302 (1, 'INCLUDE_QUANTITY'),
3303 (1, 'INCLUDE_BIB_PAGINATION'),
3306 (2, 'INCLUDE_COPIES'),
3307 (2, 'INCLUDE_ITEM_TYPE'),
3308 (2, 'INCLUDE_COLLECTION_CODE'),
3309 (2, 'INCLUDE_CALL_NUMBER'),
3310 (2, 'INCLUDE_OWNING_LIB'),
3311 (2, 'INCLUDE_QUANTITY'),
3312 (2, 'INCLUDE_BIB_PAGINATION'),
3313 (2, 'BUYER_ID_INCLUDE_VENDCODE'),
3314 (2, 'INCLUDE_EMPTY_LI_NOTE'),
3315 (2, 'INCLUDE_EMPTY_CALL_NUMBER'),
3316 (2, 'INCLUDE_EMPTY_ITEM_TYPE'),
3317 (2, 'INCLUDE_EMPTY_COLLECTION_CODE'),
3318 (2, 'INCLUDE_EMPTY_LOCATION'),
3319 (2, 'LINEITEM_IDENT_VENDOR_NUMBER'),
3320 (2, 'LINEITEM_REF_ID_ONLY'),
3323 (3, 'INCLUDE_COPIES'),
3324 (3, 'INCLUDE_FUND'),
3325 (3, 'INCLUDE_ITEM_TYPE'),
3326 (3, 'INCLUDE_COLLECTION_CODE'),
3327 (3, 'INCLUDE_OWNING_LIB'),
3328 (3, 'INCLUDE_QUANTITY'),
3329 (3, 'INCLUDE_BIB_PAGINATION'),
3330 (3, 'COPY_SPEC_CODES'),
3333 (4, 'INCLUDE_COPIES'),
3334 (4, 'INCLUDE_FUND'),
3335 (4, 'INCLUDE_OWNING_LIB'),
3336 (4, 'INCLUDE_QUANTITY'),
3337 (4, 'INCLUDE_BIB_PAGINATION'),
3340 (5, 'INCLUDE_COPIES'),
3341 (5, 'INCLUDE_ITEM_TYPE'),
3342 (5, 'INCLUDE_COLLECTION_CODE'),
3343 (5, 'INCLUDE_OWNING_LIB'),
3344 (5, 'INCLUDE_QUANTITY'),
3345 (5, 'INCLUDE_BIB_AUTHOR'),
3346 (5, 'INCLUDE_BIB_EDITION'),
3347 (5, 'INCLUDE_EMPTY_LI_NOTE'),
3350 (6, 'INCLUDE_COPIES'),
3351 (6, 'INCLUDE_ITEM_TYPE'),
3352 (6, 'INCLUDE_COLLECTION_CODE'),
3353 (6, 'INCLUDE_OWNING_LIB'),
3354 (6, 'INCLUDE_QUANTITY'),
3355 (6, 'INCLUDE_BIB_PAGINATION'),
3357 -- Midwest Library Service
3358 (7, 'INCLUDE_BIB_AUTHOR'),
3359 (7, 'INCLUDE_BIB_EDITION'),
3360 (7, 'BUYER_ID_ONLY_VENDCODE'),
3361 (7, 'INCLUDE_EMPTY_IMD_VALUES')
3368 SELECT evergreen.upgrade_deps_block_check('1068', :eg_version); --miker/gmcharlt/kmlussier
3370 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"?>
3371 <xsl:stylesheet version="1.0" xmlns:mads="http://www.loc.gov/mads/v2"
3372 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:marc="http://www.loc.gov/MARC21/slim"
3373 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="marc">
3374 <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
3375 <xsl:strip-space elements="*"/>
3377 <xsl:variable name="ascii">
3378 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
3381 <xsl:variable name="latin1">
3382 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
3384 <!-- Characters that usually don't need to be escaped -->
3385 <xsl:variable name="safe">
3386 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
3389 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
3392 <xsl:template name="datafield">
3393 <xsl:param name="tag"/>
3394 <xsl:param name="ind1">
3395 <xsl:text> </xsl:text>
3397 <xsl:param name="ind2">
3398 <xsl:text> </xsl:text>
3400 <xsl:param name="subfields"/>
3401 <xsl:element name="marc:datafield">
3402 <xsl:attribute name="tag">
3403 <xsl:value-of select="$tag"/>
3405 <xsl:attribute name="ind1">
3406 <xsl:value-of select="$ind1"/>
3408 <xsl:attribute name="ind2">
3409 <xsl:value-of select="$ind2"/>
3411 <xsl:copy-of select="$subfields"/>
3415 <xsl:template name="subfieldSelect">
3416 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
3417 <xsl:param name="delimeter">
3418 <xsl:text> </xsl:text>
3420 <xsl:variable name="str">
3421 <xsl:for-each select="marc:subfield">
3422 <xsl:if test="contains($codes, @code)">
3423 <xsl:value-of select="text()"/>
3424 <xsl:value-of select="$delimeter"/>
3428 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
3431 <xsl:template name="buildSpaces">
3432 <xsl:param name="spaces"/>
3433 <xsl:param name="char">
3434 <xsl:text> </xsl:text>
3436 <xsl:if test="$spaces>0">
3437 <xsl:value-of select="$char"/>
3438 <xsl:call-template name="buildSpaces">
3439 <xsl:with-param name="spaces" select="$spaces - 1"/>
3440 <xsl:with-param name="char" select="$char"/>
3441 </xsl:call-template>
3445 <xsl:template name="chopPunctuation">
3446 <xsl:param name="chopString"/>
3447 <xsl:param name="punctuation">
3448 <xsl:text>.:,;/ </xsl:text>
3450 <xsl:variable name="length" select="string-length($chopString)"/>
3452 <xsl:when test="$length=0"/>
3453 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3454 <xsl:call-template name="chopPunctuation">
3455 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3456 <xsl:with-param name="punctuation" select="$punctuation"/>
3457 </xsl:call-template>
3459 <xsl:when test="not($chopString)"/>
3461 <xsl:value-of select="$chopString"/>
3466 <xsl:template name="chopPunctuationFront">
3467 <xsl:param name="chopString"/>
3468 <xsl:variable name="length" select="string-length($chopString)"/>
3470 <xsl:when test="$length=0"/>
3471 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
3472 <xsl:call-template name="chopPunctuationFront">
3473 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
3475 </xsl:call-template>
3477 <xsl:when test="not($chopString)"/>
3479 <xsl:value-of select="$chopString"/>
3484 <xsl:template name="chopPunctuationBack">
3485 <xsl:param name="chopString"/>
3486 <xsl:param name="punctuation">
3487 <xsl:text>.:,;/] </xsl:text>
3489 <xsl:variable name="length" select="string-length($chopString)"/>
3491 <xsl:when test="$length=0"/>
3492 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3493 <xsl:call-template name="chopPunctuation">
3494 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3495 <xsl:with-param name="punctuation" select="$punctuation"/>
3496 </xsl:call-template>
3498 <xsl:when test="not($chopString)"/>
3500 <xsl:value-of select="$chopString"/>
3505 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
3506 <xsl:template name="url-encode">
3508 <xsl:param name="str"/>
3510 <xsl:if test="$str">
3511 <xsl:variable name="first-char" select="substring($str,1,1)"/>
3513 <xsl:when test="contains($safe,$first-char)">
3514 <xsl:value-of select="$first-char"/>
3517 <xsl:variable name="codepoint">
3519 <xsl:when test="contains($ascii,$first-char)">
3521 select="string-length(substring-before($ascii,$first-char)) + 32"
3524 <xsl:when test="contains($latin1,$first-char)">
3526 select="string-length(substring-before($latin1,$first-char)) + 160"/>
3530 <xsl:message terminate="no">Warning: string contains a character
3531 that is out of range! Substituting "?".</xsl:message>
3532 <xsl:text>63</xsl:text>
3536 <xsl:variable name="hex-digit1"
3537 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
3538 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
3539 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
3540 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
3543 <xsl:if test="string-length($str) > 1">
3544 <xsl:call-template name="url-encode">
3545 <xsl:with-param name="str" select="substring($str,2)"/>
3546 </xsl:call-template>
3553 2.14 Fixed bug in mads:geographic attributes syntax ws 05/04/2016
3554 2.13 fixed repeating <geographic> tmee 01/31/2014
3555 2.12 added $2 authority for <classification> tmee 09/18/2012
3556 2.11 added delimiters between <classification> subfields tmee 09/18/2012
3557 2.10 fixed type="other" and type="otherType" for mads:related tmee 09/16/2011
3558 2.09 fixed professionTerm and genreTerm empty tag error tmee 09/16/2011
3559 2.08 fixed marc:subfield @code='i' matching error tmee 09/16/2011
3560 2.07 fixed 555 duplication error tmee 08/10/2011
3561 2.06 fixed topic subfield error tmee 08/10/2011
3562 2.05 fixed title subfield error tmee 06/20/2011
3563 2.04 fixed geographicSubdivision mapping for authority element tmee 06/16/2011
3564 2.03 added classification for 053, 055, 060, 065, 070, 080, 082, 083, 086, 087 tmee 06/03/2011
3565 2.02 added descriptionStandard for 008/10 tmee 04/27/2011
3566 2.01 added extensions for 046, 336, 370, 374, 375, 376 tmee 04/08/2011
3567 2.00 redefined imported MODS elements in version 1.0 to MADS elements in version 2.0 tmee 02/08/2011
3568 1.08 added 372 subfields $a $s $t for <fieldOfActivity> tmee 06/24/2010
3569 1.07 removed role/roleTerm 100, 110, 111, 400, 410, 411, 500, 510, 511, 700, 710, 711 tmee 06/24/2010
3570 1.06 added strip-space tmee 06/24/2010
3571 1.05 added subfield $a for 130, 430, 530 tmee 06/21/2010
3572 1.04 fixed 550 z omission ntra 08/11/2008
3573 1.03 removed duplication of 550 $a text tmee 11/01/2006
3574 1.02 fixed namespace references between mads and mods ntra 10/06/2006
3575 1.01 revised rgue/jrad 11/29/05
3576 1.00 adapted from MARC21Slim2MODS3.xsl ntra 07/06/05
3579 <!-- authority attribute defaults to 'naf' if not set using this authority parameter, for <authority> descriptors: name, titleInfo, geographic -->
3580 <xsl:param name="authority"/>
3581 <xsl:variable name="auth">
3583 <xsl:when test="$authority">
3584 <xsl:value-of select="$authority"/>
3586 <xsl:otherwise>naf</xsl:otherwise>
3589 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
3590 <xsl:variable name="controlField008-06"
3591 select="substring(descendant-or-self::marc:controlfield[@tag=008],7,1)"/>
3592 <xsl:variable name="controlField008-11"
3593 select="substring(descendant-or-self::marc:controlfield[@tag=008],12,1)"/>
3594 <xsl:variable name="controlField008-14"
3595 select="substring(descendant-or-self::marc:controlfield[@tag=008],15,1)"/>
3596 <xsl:template match="/">
3598 <xsl:when test="descendant-or-self::marc:collection">
3599 <mads:madsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3600 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/v2/mads-2-0.xsd">
3601 <xsl:for-each select="descendant-or-self::marc:collection/marc:record">
3602 <mads:mads version="2.0">
3603 <xsl:call-template name="marcRecord"/>
3606 </mads:madsCollection>
3609 <mads:mads version="2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3610 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/mads-2-0.xsd">
3611 <xsl:for-each select="descendant-or-self::marc:record">
3612 <xsl:call-template name="marcRecord"/>
3619 <xsl:template name="marcRecord">
3623 <xsl:when test="$controlField008-06='d'">
3624 <xsl:attribute name="geographicSubdivision">
3625 <xsl:text>direct</xsl:text>
3628 <xsl:when test="$controlField008-06='i'">
3629 <xsl:attribute name="geographicSubdivision">
3630 <xsl:text>indirect</xsl:text>
3633 <xsl:when test="$controlField008-06='n'">
3634 <xsl:attribute name="geographicSubdivision">
3635 <xsl:text>not applicable</xsl:text>
3640 <xsl:apply-templates select="marc:datafield[100 <= @tag and @tag < 200]"/>
3644 <xsl:apply-templates
3645 select="marc:datafield[500 <= @tag and @tag <= 585]|marc:datafield[700 <= @tag and @tag <= 785]"/>
3648 <xsl:apply-templates select="marc:datafield[400 <= @tag and @tag <= 485]"/>
3651 <xsl:apply-templates select="marc:datafield[667 <= @tag and @tag <= 688]"/>
3654 <xsl:apply-templates select="marc:datafield[@tag=856]"/>
3655 <xsl:apply-templates select="marc:datafield[@tag=010]"/>
3656 <xsl:apply-templates select="marc:datafield[@tag=024]"/>
3657 <xsl:apply-templates select="marc:datafield[@tag=372]"/>
3659 <!-- classification -->
3660 <xsl:apply-templates select="marc:datafield[@tag=053]"/>
3661 <xsl:apply-templates select="marc:datafield[@tag=055]"/>
3662 <xsl:apply-templates select="marc:datafield[@tag=060]"/>
3663 <xsl:apply-templates select="marc:datafield[@tag=065]"/>
3664 <xsl:apply-templates select="marc:datafield[@tag=070]"/>
3665 <xsl:apply-templates select="marc:datafield[@tag=080]"/>
3666 <xsl:apply-templates select="marc:datafield[@tag=082]"/>
3667 <xsl:apply-templates select="marc:datafield[@tag=083]"/>
3668 <xsl:apply-templates select="marc:datafield[@tag=086]"/>
3669 <xsl:apply-templates select="marc:datafield[@tag=087]"/>
3672 <xsl:for-each select="marc:datafield[@tag=373]">
3675 <xsl:value-of select="marc:subfield[@code='a']"/>
3677 <mads:dateValid point="start">
3678 <xsl:value-of select="marc:subfield[@code='s']"/>
3680 <mads:dateValid point="end">
3681 <xsl:value-of select="marc:subfield[@code='t']"/>
3685 <xsl:for-each select="marc:datafield[@tag=371]">
3689 <xsl:value-of select="marc:subfield[@code='a']"/>
3692 <xsl:value-of select="marc:subfield[@code='b']"/>
3695 <xsl:value-of select="marc:subfield[@code='c']"/>
3698 <xsl:value-of select="marc:subfield[@code='d']"/>
3701 <xsl:value-of select="marc:subfield[@code='e']"/>
3705 <xsl:value-of select="marc:subfield[@code='m']"/>
3711 <xsl:for-each select="marc:datafield[@tag=336]">
3714 <mads:contentType type="text">
3715 <xsl:value-of select="marc:subfield[@code='a']"/>
3717 <mads:contentType type="code">
3718 <xsl:value-of select="marc:subfield[@code='b']"/>
3724 <xsl:for-each select="marc:datafield[@tag=374]">
3728 <xsl:when test="marc:subfield[@code='a']">
3729 <mads:professionTerm>
3730 <xsl:value-of select="marc:subfield[@code='a']"/>
3731 </mads:professionTerm>
3733 <xsl:when test="marc:subfield[@code='s']">
3734 <mads:dateValid point="start">
3735 <xsl:value-of select="marc:subfield[@code='s']"/>
3738 <xsl:when test="marc:subfield[@code='t']">
3739 <mads:dateValid point="end">
3740 <xsl:value-of select="marc:subfield[@code='t']"/>
3748 <xsl:for-each select="marc:datafield[@tag=375]">
3752 <xsl:when test="marc:subfield[@code='a']">
3754 <xsl:value-of select="marc:subfield[@code='a']"/>
3757 <xsl:when test="marc:subfield[@code='s']">
3758 <mads:dateValid point="start">
3759 <xsl:value-of select="marc:subfield[@code='s']"/>
3762 <xsl:when test="marc:subfield[@code='t']">
3763 <mads:dateValid point="end">
3764 <xsl:value-of select="marc:subfield[@code='t']"/>
3772 <xsl:for-each select="marc:datafield[@tag=376]">
3774 <mads:familyInformation>
3776 <xsl:value-of select="marc:subfield[@code='a']"/>
3777 </mads:typeOfFamily>
3778 <mads:nameOfProminentMember>
3779 <xsl:value-of select="marc:subfield[@code='b']"/>
3780 </mads:nameOfProminentMember>
3781 <mads:hereditaryTitle>
3782 <xsl:value-of select="marc:subfield[@code='c']"/>
3783 </mads:hereditaryTitle>
3784 <mads:dateValid point="start">
3785 <xsl:value-of select="marc:subfield[@code='s']"/>
3787 <mads:dateValid point="end">
3788 <xsl:value-of select="marc:subfield[@code='t']"/>
3790 </mads:familyInformation>
3795 <mads:recordOrigin>Converted from MARCXML to MADS version 2.0 (Revision 2.13)</mads:recordOrigin>
3796 <!-- <xsl:apply-templates select="marc:datafield[@tag=024]"/> -->
3798 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='a']"/>
3799 <xsl:apply-templates select="marc:controlfield[@tag=005]"/>
3800 <xsl:apply-templates select="marc:controlfield[@tag=001]"/>
3801 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='b']"/>
3802 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='e']"/>
3803 <xsl:for-each select="marc:controlfield[@tag=008]">
3804 <xsl:if test="substring(.,11,1)='a'">
3805 <mads:descriptionStandard>
3806 <xsl:text>earlier rules</xsl:text>
3807 </mads:descriptionStandard>
3809 <xsl:if test="substring(.,11,1)='b'">
3810 <mads:descriptionStandard>
3811 <xsl:text>aacr1</xsl:text>
3812 </mads:descriptionStandard>
3814 <xsl:if test="substring(.,11,1)='c'">
3815 <mads:descriptionStandard>
3816 <xsl:text>aacr2</xsl:text>
3817 </mads:descriptionStandard>
3819 <xsl:if test="substring(.,11,1)='d'">
3820 <mads:descriptionStandard>
3821 <xsl:text>aacr2 compatible</xsl:text>
3822 </mads:descriptionStandard>
3824 <xsl:if test="substring(.,11,1)='z'">
3825 <mads:descriptionStandard>
3826 <xsl:text>other rules</xsl:text>
3827 </mads:descriptionStandard>
3833 <!-- start of secondary templates -->
3835 <!-- ======== xlink ======== -->
3837 <!-- <xsl:template name="uri">
3838 <xsl:for-each select="marc:subfield[@code='0']">
3839 <xsl:attribute name="xlink:href">
3840 <xsl:value-of select="."/>
3845 <xsl:template match="marc:subfield[@code='i']">
3846 <xsl:attribute name="otherType">
3847 <xsl:value-of select="."/>
3851 <!-- No role/roleTerm mapped in MADS 06/24/2010
3852 <xsl:template name="role">
3853 <xsl:for-each select="marc:subfield[@code='e']">
3855 <mads:roleTerm type="text">
3856 <xsl:value-of select="."/>
3863 <xsl:template name="part">
3864 <xsl:variable name="partNumber">
3865 <xsl:call-template name="specialSubfieldSelect">
3866 <xsl:with-param name="axis">n</xsl:with-param>
3867 <xsl:with-param name="anyCodes">n</xsl:with-param>
3868 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3869 </xsl:call-template>
3871 <xsl:variable name="partName">
3872 <xsl:call-template name="specialSubfieldSelect">
3873 <xsl:with-param name="axis">p</xsl:with-param>
3874 <xsl:with-param name="anyCodes">p</xsl:with-param>
3875 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3876 </xsl:call-template>
3878 <xsl:if test="string-length(normalize-space($partNumber))">
3880 <xsl:call-template name="chopPunctuation">
3881 <xsl:with-param name="chopString" select="$partNumber"/>
3882 </xsl:call-template>
3885 <xsl:if test="string-length(normalize-space($partName))">
3887 <xsl:call-template name="chopPunctuation">
3888 <xsl:with-param name="chopString" select="$partName"/>
3889 </xsl:call-template>
3894 <xsl:template name="nameABCDN">
3895 <xsl:for-each select="marc:subfield[@code='a']">
3897 <xsl:call-template name="chopPunctuation">
3898 <xsl:with-param name="chopString" select="."/>
3899 </xsl:call-template>
3902 <xsl:for-each select="marc:subfield[@code='b']">
3904 <xsl:value-of select="."/>
3908 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
3910 <xsl:call-template name="subfieldSelect">
3911 <xsl:with-param name="codes">cdn</xsl:with-param>
3912 </xsl:call-template>
3917 <xsl:template name="nameABCDQ">
3919 <xsl:call-template name="chopPunctuation">
3920 <xsl:with-param name="chopString">
3921 <xsl:call-template name="subfieldSelect">
3922 <xsl:with-param name="codes">aq</xsl:with-param>
3923 </xsl:call-template>
3925 </xsl:call-template>
3927 <xsl:call-template name="termsOfAddress"/>
3928 <xsl:call-template name="nameDate"/>
3931 <xsl:template name="nameACDENQ">
3933 <xsl:call-template name="subfieldSelect">
3934 <xsl:with-param name="codes">acdenq</xsl:with-param>
3935 </xsl:call-template>
3939 <xsl:template name="nameDate">
3940 <xsl:for-each select="marc:subfield[@code='d']">
3941 <mads:namePart type="date">
3942 <xsl:call-template name="chopPunctuation">
3943 <xsl:with-param name="chopString" select="."/>
3944 </xsl:call-template>
3949 <xsl:template name="specialSubfieldSelect">
3950 <xsl:param name="anyCodes"/>
3951 <xsl:param name="axis"/>
3952 <xsl:param name="beforeCodes"/>
3953 <xsl:param name="afterCodes"/>
3954 <xsl:variable name="str">
3955 <xsl:for-each select="marc:subfield">
3957 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])">
3958 <xsl:value-of select="text()"/>
3959 <xsl:text> </xsl:text>
3963 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
3966 <xsl:template name="termsOfAddress">
3967 <xsl:if test="marc:subfield[@code='b' or @code='c']">
3968 <mads:namePart type="termsOfAddress">
3969 <xsl:call-template name="chopPunctuation">
3970 <xsl:with-param name="chopString">
3971 <xsl:call-template name="subfieldSelect">
3972 <xsl:with-param name="codes">bc</xsl:with-param>
3973 </xsl:call-template>
3975 </xsl:call-template>
3980 <xsl:template name="displayLabel">
3981 <xsl:if test="marc:subfield[@code='z']">
3982 <xsl:attribute name="displayLabel">
3983 <xsl:value-of select="marc:subfield[@code='z']"/>
3986 <xsl:if test="marc:subfield[@code='3']">
3987 <xsl:attribute name="displayLabel">
3988 <xsl:value-of select="marc:subfield[@code='3']"/>
3993 <xsl:template name="isInvalid">
3994 <xsl:if test="@code='z'">
3995 <xsl:attribute name="invalid">yes</xsl:attribute>
3999 <xsl:template name="sub2Attribute">
4001 <xsl:if test="../marc:subfield[@code='2']">
4002 <xsl:attribute name="type">
4003 <xsl:value-of select="../marc:subfield[@code='2']"/>
4008 <xsl:template match="marc:controlfield[@tag=001]">
4009 <mads:recordIdentifier>
4010 <xsl:if test="../marc:controlfield[@tag=003]">
4011 <xsl:attribute name="source">
4012 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
4015 <xsl:value-of select="."/>
4016 </mads:recordIdentifier>
4019 <xsl:template match="marc:controlfield[@tag=005]">
4020 <mads:recordChangeDate encoding="iso8601">
4021 <xsl:value-of select="."/>
4022 </mads:recordChangeDate>
4025 <xsl:template match="marc:controlfield[@tag=008]">
4026 <mads:recordCreationDate encoding="marc">
4027 <xsl:value-of select="substring(.,1,6)"/>
4028 </mads:recordCreationDate>
4031 <xsl:template match="marc:datafield[@tag=010]">
4032 <xsl:for-each select="marc:subfield">
4033 <mads:identifier type="lccn">
4034 <xsl:call-template name="isInvalid"/>
4035 <xsl:value-of select="."/>
4040 <xsl:template match="marc:datafield[@tag=024]">
4041 <xsl:for-each select="marc:subfield[not(@code=2)]">
4043 <xsl:call-template name="isInvalid"/>
4044 <xsl:call-template name="sub2Attribute"/>
4045 <xsl:value-of select="."/>
4050 <!-- ========== 372 ========== -->
4051 <xsl:template match="marc:datafield[@tag=372]">
4052 <mads:fieldOfActivity>
4053 <xsl:call-template name="subfieldSelect">
4054 <xsl:with-param name="codes">a</xsl:with-param>
4055 </xsl:call-template>
4056 <xsl:text>-</xsl:text>
4057 <xsl:call-template name="subfieldSelect">
4058 <xsl:with-param name="codes">st</xsl:with-param>
4059 </xsl:call-template>
4060 </mads:fieldOfActivity>
4064 <!-- ========== 040 ========== -->
4065 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='a']">
4066 <mads:recordContentSource authority="marcorg">
4067 <xsl:value-of select="."/>
4068 </mads:recordContentSource>
4071 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='b']">
4072 <mads:languageOfCataloging>
4073 <mads:languageTerm authority="iso639-2b" type="code">
4074 <xsl:value-of select="."/>
4075 </mads:languageTerm>
4076 </mads:languageOfCataloging>
4079 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='e']">
4080 <mads:descriptionStandard>
4081 <xsl:value-of select="."/>
4082 </mads:descriptionStandard>
4085 <!-- ========== classification 2.03 ========== -->
4087 <xsl:template match="marc:datafield[@tag=053]">
4088 <mads:classification>
4089 <xsl:call-template name="subfieldSelect">
4090 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4091 <xsl:with-param name="delimeter">-</xsl:with-param>
4092 </xsl:call-template>
4093 </mads:classification>
4096 <xsl:template match="marc:datafield[@tag=055]">
4097 <mads:classification>
4098 <xsl:call-template name="subfieldSelect">
4099 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4100 <xsl:with-param name="delimeter">-</xsl:with-param>
4101 </xsl:call-template>
4102 </mads:classification>
4105 <xsl:template match="marc:datafield[@tag=060]">
4106 <mads:classification>
4107 <xsl:call-template name="subfieldSelect">
4108 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4109 <xsl:with-param name="delimeter">-</xsl:with-param>
4110 </xsl:call-template>
4111 </mads:classification>
4113 <xsl:template match="marc:datafield[@tag=065]">
4114 <mads:classification>
4115 <xsl:attribute name="authority">
4116 <xsl:value-of select="marc:subfield[@code='2']"/>
4118 <xsl:call-template name="subfieldSelect">
4119 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4120 <xsl:with-param name="delimeter">-</xsl:with-param>
4121 </xsl:call-template>
4122 </mads:classification>
4124 <xsl:template match="marc:datafield[@tag=070]">
4125 <mads:classification>
4126 <xsl:call-template name="subfieldSelect">
4127 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4128 <xsl:with-param name="delimeter">-</xsl:with-param>
4129 </xsl:call-template>
4130 </mads:classification>
4132 <xsl:template match="marc:datafield[@tag=080]">
4133 <mads:classification>
4134 <xsl:attribute name="authority">
4135 <xsl:value-of select="marc:subfield[@code='2']"/>
4137 <xsl:call-template name="subfieldSelect">
4138 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4139 <xsl:with-param name="delimeter">-</xsl:with-param>
4140 </xsl:call-template>
4141 </mads:classification>
4143 <xsl:template match="marc:datafield[@tag=082]">
4144 <mads:classification>
4145 <xsl:attribute name="authority">
4146 <xsl:value-of select="marc:subfield[@code='2']"/>
4148 <xsl:call-template name="subfieldSelect">
4149 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4150 <xsl:with-param name="delimeter">-</xsl:with-param>
4151 </xsl:call-template>
4152 </mads:classification>
4154 <xsl:template match="marc:datafield[@tag=083]">
4155 <mads:classification>
4156 <xsl:attribute name="authority">
4157 <xsl:value-of select="marc:subfield[@code='2']"/>
4159 <xsl:call-template name="subfieldSelect">
4160 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4161 <xsl:with-param name="delimeter">-</xsl:with-param>
4162 </xsl:call-template>
4163 </mads:classification>
4165 <xsl:template match="marc:datafield[@tag=086]">
4166 <mads:classification>
4167 <xsl:attribute name="authority">
4168 <xsl:value-of select="marc:subfield[@code='2']"/>
4170 <xsl:call-template name="subfieldSelect">
4171 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4172 <xsl:with-param name="delimeter">-</xsl:with-param>
4173 </xsl:call-template>
4174 </mads:classification>
4176 <xsl:template match="marc:datafield[@tag=087]">
4177 <mads:classification>
4178 <xsl:attribute name="authority">
4179 <xsl:value-of select="marc:subfield[@code='2']"/>
4181 <xsl:call-template name="subfieldSelect">
4182 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4183 <xsl:with-param name="delimeter">-</xsl:with-param>
4184 </xsl:call-template>
4185 </mads:classification>
4189 <!-- ========== names ========== -->
4190 <xsl:template match="marc:datafield[@tag=100]">
4191 <mads:name type="personal">
4192 <xsl:call-template name="setAuthority"/>
4193 <xsl:call-template name="nameABCDQ"/>
4195 <xsl:apply-templates select="*[marc:subfield[not(contains('abcdeq',@code))]]"/>
4196 <xsl:call-template name="title"/>
4197 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4200 <xsl:template match="marc:datafield[@tag=110]">
4201 <mads:name type="corporate">
4202 <xsl:call-template name="setAuthority"/>
4203 <xsl:call-template name="nameABCDN"/>
4205 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4208 <xsl:template match="marc:datafield[@tag=111]">
4209 <mads:name type="conference">
4210 <xsl:call-template name="setAuthority"/>
4211 <xsl:call-template name="nameACDENQ"/>
4213 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4216 <xsl:template match="marc:datafield[@tag=400]">
4218 <xsl:call-template name="variantTypeAttribute"/>
4219 <mads:name type="personal">
4220 <xsl:call-template name="nameABCDQ"/>
4222 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4223 <xsl:call-template name="title"/>
4227 <xsl:template match="marc:datafield[@tag=410]">
4229 <xsl:call-template name="variantTypeAttribute"/>
4230 <mads:name type="corporate">
4231 <xsl:call-template name="nameABCDN"/>
4233 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4237 <xsl:template match="marc:datafield[@tag=411]">
4239 <xsl:call-template name="variantTypeAttribute"/>
4240 <mads:name type="conference">
4241 <xsl:call-template name="nameACDENQ"/>
4243 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4247 <xsl:template match="marc:datafield[@tag=500]|marc:datafield[@tag=700]">
4249 <xsl:call-template name="relatedTypeAttribute"/>
4250 <!-- <xsl:call-template name="uri"/> -->
4251 <mads:name type="personal">
4252 <xsl:call-template name="setAuthority"/>
4253 <xsl:call-template name="nameABCDQ"/>
4255 <xsl:call-template name="title"/>
4256 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4260 <xsl:template match="marc:datafield[@tag=510]|marc:datafield[@tag=710]">
4262 <xsl:call-template name="relatedTypeAttribute"/>
4263 <!-- <xsl:call-template name="uri"/> -->
4264 <mads:name type="corporate">
4265 <xsl:call-template name="setAuthority"/>
4266 <xsl:call-template name="nameABCDN"/>
4268 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4272 <xsl:template match="marc:datafield[@tag=511]|marc:datafield[@tag=711]">
4274 <xsl:call-template name="relatedTypeAttribute"/>
4275 <!-- <xsl:call-template name="uri"/> -->
4276 <mads:name type="conference">
4277 <xsl:call-template name="setAuthority"/>
4278 <xsl:call-template name="nameACDENQ"/>
4280 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4284 <!-- ========== titles ========== -->
4285 <xsl:template match="marc:datafield[@tag=130]">
4286 <xsl:call-template name="uniform-title"/>
4287 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4290 <xsl:template match="marc:datafield[@tag=430]">
4292 <xsl:call-template name="variantTypeAttribute"/>
4293 <xsl:call-template name="uniform-title"/>
4294 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4298 <xsl:template match="marc:datafield[@tag=530]|marc:datafield[@tag=730]">
4300 <xsl:call-template name="relatedTypeAttribute"/>
4301 <xsl:call-template name="uniform-title"/>
4302 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4306 <xsl:template name="title">
4307 <xsl:variable name="hasTitle">
4308 <xsl:for-each select="marc:subfield">
4309 <xsl:if test="(contains('tfghklmors',@code) )">
4310 <xsl:value-of select="@code"/>
4314 <xsl:if test="string-length($hasTitle) > 0 ">
4316 <xsl:call-template name="setAuthority"/>
4318 <xsl:variable name="str">
4319 <xsl:for-each select="marc:subfield">
4320 <xsl:if test="(contains('atfghklmors',@code) )">
4321 <xsl:value-of select="text()"/>
4322 <xsl:text> </xsl:text>
4326 <xsl:call-template name="chopPunctuation">
4327 <xsl:with-param name="chopString">
4328 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4330 </xsl:call-template>
4332 <xsl:call-template name="part"/>
4333 <!-- <xsl:call-template name="uri"/> -->
4338 <xsl:template name="uniform-title">
4339 <xsl:variable name="hasTitle">
4340 <xsl:for-each select="marc:subfield">
4341 <xsl:if test="(contains('atfghklmors',@code) )">
4342 <xsl:value-of select="@code"/>
4346 <xsl:if test="string-length($hasTitle) > 0 ">
4348 <xsl:call-template name="setAuthority"/>
4350 <xsl:variable name="str">
4351 <xsl:for-each select="marc:subfield">
4352 <xsl:if test="(contains('adfghklmors',@code) )">
4353 <xsl:value-of select="text()"/>
4354 <xsl:text> </xsl:text>
4358 <xsl:call-template name="chopPunctuation">
4359 <xsl:with-param name="chopString">
4360 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4362 </xsl:call-template>
4364 <xsl:call-template name="part"/>
4365 <!-- <xsl:call-template name="uri"/> -->
4371 <!-- ========== topics ========== -->
4372 <xsl:template match="marc:subfield[@code='x']">
4374 <xsl:call-template name="chopPunctuation">
4375 <xsl:with-param name="chopString">
4376 <xsl:value-of select="."/>
4378 </xsl:call-template>
4384 match="marc:datafield[@tag=150][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=180][marc:subfield[@code='x']]">
4385 <xsl:call-template name="topic"/>
4386 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4389 match="marc:datafield[@tag=450][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=480][marc:subfield[@code='x']]">
4391 <xsl:call-template name="variantTypeAttribute"/>
4392 <xsl:call-template name="topic"/>
4396 match="marc:datafield[@tag=550 or @tag=750][marc:subfield[@code='a' or @code='b']]">
4398 <xsl:call-template name="relatedTypeAttribute"/>
4399 <!-- <xsl:call-template name="uri"/> -->
4400 <xsl:call-template name="topic"/>
4401 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4404 <xsl:template name="topic">
4406 <xsl:call-template name="setAuthority"/>
4407 <!-- tmee2006 dedupe 550a
4408 <xsl:if test="@tag=550 or @tag=750">
4409 <xsl:call-template name="subfieldSelect">
4410 <xsl:with-param name="codes">ab</xsl:with-param>
4411 </xsl:call-template>
4415 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4416 <xsl:call-template name="chopPunctuation">
4417 <xsl:with-param name="chopString">
4418 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4420 </xsl:call-template>
4423 <xsl:call-template name="chopPunctuation">
4424 <xsl:with-param name="chopString">
4426 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4427 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4430 <xsl:call-template name="subfieldSelect">
4431 <xsl:with-param name="codes">ab</xsl:with-param>
4432 </xsl:call-template>
4436 </xsl:call-template>
4440 <!-- ========= temporals ========== -->
4441 <xsl:template match="marc:subfield[@code='y']">
4443 <xsl:call-template name="chopPunctuation">
4444 <xsl:with-param name="chopString">
4445 <xsl:value-of select="."/>
4447 </xsl:call-template>
4451 match="marc:datafield[@tag=148][marc:subfield[@code='a']]|marc:datafield[@tag=182 ][marc:subfield[@code='y']]">
4452 <xsl:call-template name="temporal"/>
4455 match="marc:datafield[@tag=448][marc:subfield[@code='a']]|marc:datafield[@tag=482][marc:subfield[@code='y']]">
4457 <xsl:call-template name="variantTypeAttribute"/>
4458 <xsl:call-template name="temporal"/>
4462 match="marc:datafield[@tag=548 or @tag=748][marc:subfield[@code='a']]|marc:datafield[@tag=582 or @tag=782][marc:subfield[@code='y']]">
4464 <xsl:call-template name="relatedTypeAttribute"/>
4465 <!-- <xsl:call-template name="uri"/> -->
4466 <xsl:call-template name="temporal"/>
4469 <xsl:template name="temporal">
4471 <xsl:call-template name="setAuthority"/>
4472 <xsl:if test="@tag=548 or @tag=748">
4473 <xsl:value-of select="marc:subfield[@code='a']"/>
4475 <xsl:call-template name="chopPunctuation">
4476 <xsl:with-param name="chopString">
4478 <xsl:when test="@tag=182 or @tag=482 or @tag=582 or @tag=782">
4479 <xsl:apply-templates select="marc:subfield[@code='y']"/>
4482 <xsl:value-of select="marc:subfield[@code='a']"/>
4486 </xsl:call-template>
4488 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4491 <!-- ========== genre ========== -->
4492 <xsl:template match="marc:subfield[@code='v']">
4494 <xsl:call-template name="chopPunctuation">
4495 <xsl:with-param name="chopString">
4496 <xsl:value-of select="."/>
4498 </xsl:call-template>
4502 match="marc:datafield[@tag=155][marc:subfield[@code='a']]|marc:datafield[@tag=185][marc:subfield[@code='v']]">
4503 <xsl:call-template name="genre"/>
4506 match="marc:datafield[@tag=455][marc:subfield[@code='a']]|marc:datafield[@tag=485 ][marc:subfield[@code='v']]">
4508 <xsl:call-template name="variantTypeAttribute"/>
4509 <xsl:call-template name="genre"/>
4513 <xsl:template match="marc:datafield[@tag=555]">
4515 <xsl:call-template name="relatedTypeAttribute"/>
4516 <xsl:call-template name="uri"/>
4517 <xsl:call-template name="genre"/>
4522 match="marc:datafield[@tag=555 or @tag=755][marc:subfield[@code='a']]|marc:datafield[@tag=585][marc:subfield[@code='v']]">
4524 <xsl:call-template name="relatedTypeAttribute"/>
4525 <xsl:call-template name="genre"/>
4528 <xsl:template name="genre">
4530 <xsl:if test="@tag=555">
4531 <xsl:value-of select="marc:subfield[@code='a']"/>
4533 <xsl:call-template name="setAuthority"/>
4534 <xsl:call-template name="chopPunctuation">
4535 <xsl:with-param name="chopString">
4538 <xsl:when test="@tag='555'"/>
4539 <xsl:when test="@tag=185 or @tag=485 or @tag=585">
4540 <xsl:apply-templates select="marc:subfield[@code='v']"/>
4543 <xsl:value-of select="marc:subfield[@code='a']"/>
4547 </xsl:call-template>
4549 <xsl:apply-templates/>
4552 <!-- ========= geographic ========== -->
4553 <xsl:template match="marc:subfield[@code='z']">
4555 <xsl:call-template name="chopPunctuation">
4556 <xsl:with-param name="chopString">
4557 <xsl:value-of select="."/>
4559 </xsl:call-template>
4562 <xsl:template name="geographic">
4565 <xsl:call-template name="setAuthority"/>
4567 <xsl:if test="@tag=151 or @tag=551">
4568 <xsl:value-of select="marc:subfield[@code='a']"/>
4570 <xsl:call-template name="chopPunctuation">
4571 <xsl:with-param name="chopString">
4572 <xsl:if test="@tag=181 or @tag=481 or @tag=581">
4573 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4577 <xsl:when test="@tag=181 or @tag=481 or @tag=581">
4578 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4582 <xsl:value-of select="marc:subfield[@code='a']"/>
4587 </xsl:call-template>
4589 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4592 match="marc:datafield[@tag=151][marc:subfield[@code='a']]|marc:datafield[@tag=181][marc:subfield[@code='z']]">
4593 <xsl:call-template name="geographic"/>
4596 match="marc:datafield[@tag=451][marc:subfield[@code='a']]|marc:datafield[@tag=481][marc:subfield[@code='z']]">
4598 <xsl:call-template name="variantTypeAttribute"/>
4599 <xsl:call-template name="geographic"/>
4603 match="marc:datafield[@tag=551]|marc:datafield[@tag=581][marc:subfield[@code='z']]">
4605 <xsl:call-template name="relatedTypeAttribute"/>
4606 <!-- <xsl:call-template name="uri"/> -->
4607 <xsl:call-template name="geographic"/>
4610 <xsl:template match="marc:datafield[@tag=580]">
4612 <xsl:call-template name="relatedTypeAttribute"/>
4613 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4617 match="marc:datafield[@tag=751][marc:subfield[@code='z']]|marc:datafield[@tag=781][marc:subfield[@code='z']]">
4619 <xsl:call-template name="relatedTypeAttribute"/>
4620 <xsl:call-template name="geographic"/>
4623 <xsl:template match="marc:datafield[@tag=755]">
4625 <xsl:call-template name="relatedTypeAttribute"/>
4626 <xsl:call-template name="genre"/>
4627 <xsl:call-template name="setAuthority"/>
4628 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4631 <xsl:template match="marc:datafield[@tag=780]">
4633 <xsl:call-template name="relatedTypeAttribute"/>
4634 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4637 <xsl:template match="marc:datafield[@tag=785]">
4639 <xsl:call-template name="relatedTypeAttribute"/>
4640 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4644 <!-- ========== notes ========== -->
4645 <xsl:template match="marc:datafield[667 <= @tag and @tag <= 688]">
4648 <xsl:when test="@tag=667">
4649 <xsl:attribute name="type">nonpublic</xsl:attribute>
4651 <xsl:when test="@tag=670">
4652 <xsl:attribute name="type">source</xsl:attribute>
4654 <xsl:when test="@tag=675">
4655 <xsl:attribute name="type">notFound</xsl:attribute>
4657 <xsl:when test="@tag=678">
4658 <xsl:attribute name="type">history</xsl:attribute>
4660 <xsl:when test="@tag=681">
4661 <xsl:attribute name="type">subject example</xsl:attribute>
4663 <xsl:when test="@tag=682">
4664 <xsl:attribute name="type">deleted heading information</xsl:attribute>
4666 <xsl:when test="@tag=688">
4667 <xsl:attribute name="type">application history</xsl:attribute>
4670 <xsl:call-template name="chopPunctuation">
4671 <xsl:with-param name="chopString">
4673 <xsl:when test="@tag=667 or @tag=675">
4674 <xsl:value-of select="marc:subfield[@code='a']"/>
4676 <xsl:when test="@tag=670 or @tag=678">
4677 <xsl:call-template name="subfieldSelect">
4678 <xsl:with-param name="codes">ab</xsl:with-param>
4679 </xsl:call-template>
4681 <xsl:when test="680 <= @tag and @tag <=688">
4682 <xsl:call-template name="subfieldSelect">
4683 <xsl:with-param name="codes">ai</xsl:with-param>
4684 </xsl:call-template>
4688 </xsl:call-template>
4692 <!-- ========== url ========== -->
4693 <xsl:template match="marc:datafield[@tag=856][marc:subfield[@code='u']]">
4695 <xsl:if test="marc:subfield[@code='z' or @code='3']">
4696 <xsl:attribute name="displayLabel">
4697 <xsl:call-template name="subfieldSelect">
4698 <xsl:with-param name="codes">z3</xsl:with-param>
4699 </xsl:call-template>
4702 <xsl:value-of select="marc:subfield[@code='u']"/>
4706 <xsl:template name="relatedTypeAttribute">
4709 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">
4710 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='a'">
4711 <xsl:attribute name="type">earlier</xsl:attribute>
4713 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='b'">
4714 <xsl:attribute name="type">later</xsl:attribute>
4716 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='t'">
4717 <xsl:attribute name="type">parentOrg</xsl:attribute>
4719 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='g'">
4720 <xsl:attribute name="type">broader</xsl:attribute>
4722 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='h'">
4723 <xsl:attribute name="type">narrower</xsl:attribute>
4725 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='r'">
4726 <xsl:attribute name="type">other</xsl:attribute>
4728 <xsl:if test="contains('fin|', substring(marc:subfield[@code='w'],1,1))">
4729 <xsl:attribute name="type">other</xsl:attribute>
4732 <xsl:when test="@tag=530 or @tag=730">
4733 <xsl:attribute name="type">other</xsl:attribute>
4737 <xsl:attribute name="type">equivalent</xsl:attribute>
4740 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4745 <xsl:template name="variantTypeAttribute">
4748 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">
4749 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='d'">
4750 <xsl:attribute name="type">acronym</xsl:attribute>
4752 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='n'">
4753 <xsl:attribute name="type">other</xsl:attribute>
4755 <xsl:if test="contains('fit', substring(marc:subfield[@code='w'],1,1))">
4756 <xsl:attribute name="type">other</xsl:attribute>
4761 <xsl:attribute name="type">other</xsl:attribute>
4764 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4767 <xsl:template name="setAuthority">
4769 <!-- can be called from the datafield or subfield level, so "..//@tag" means
4770 the tag can be at the subfield's parent level or at the datafields own level -->
4773 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='a'">
4774 <xsl:attribute name="authority">
4775 <xsl:text>naf</xsl:text>
4779 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='b'">
4780 <xsl:attribute name="authority">
4781 <xsl:text>lcsh</xsl:text>
4785 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='k'">
4786 <xsl:attribute name="authority">
4787 <xsl:text>lacnaf</xsl:text>
4791 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='a' and $controlField008-14='b'">
4792 <xsl:attribute name="authority">
4793 <xsl:text>lcsh</xsl:text>
4797 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='k' and $controlField008-14='b'">
4798 <xsl:attribute name="authority">cash</xsl:attribute>
4801 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='a'">
4802 <xsl:attribute name="authority">naf</xsl:attribute>
4805 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='b'">
4806 <xsl:attribute name="authority">lcsh</xsl:attribute>
4809 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='a'">
4810 <xsl:attribute name="authority">
4811 <xsl:text>lacnaf</xsl:text>
4815 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='b'">
4816 <xsl:attribute name="authority">
4817 <xsl:text>cash</xsl:text>
4821 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='b'">
4822 <xsl:attribute name="authority">
4823 <xsl:text>lcshcl</xsl:text>
4827 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'">
4828 <xsl:attribute name="authority">
4829 <xsl:text>nlmnaf</xsl:text>
4833 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'">
4834 <xsl:attribute name="authority">
4835 <xsl:text>nalnaf</xsl:text>
4839 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='r'">
4840 <xsl:attribute name="authority">
4841 <xsl:text>aat</xsl:text>
4845 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='s'">
4846 <xsl:attribute name="authority">sears</xsl:attribute>
4849 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='v'">
4850 <xsl:attribute name="authority">rvm</xsl:attribute>
4853 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='z'">
4854 <xsl:attribute name="authority">
4856 select="../marc:datafield[ancestor-or-self::marc:datafield/@tag=040]/marc:subfield[@code='f']"
4861 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='a'">
4862 <xsl:attribute name="authority">
4863 <xsl:text>naf</xsl:text>
4867 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='b'">
4868 <xsl:attribute name="authority">
4869 <xsl:text>lcsh</xsl:text>
4873 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='k' ">
4874 <xsl:attribute name="authority">
4875 <xsl:text>lacnaf</xsl:text>
4879 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' ">
4880 <xsl:attribute name="authority">
4881 <xsl:text>lcsh</xsl:text>
4885 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' ">
4886 <xsl:attribute name="authority">
4887 <xsl:text>lcsh</xsl:text>
4891 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' ">
4892 <xsl:attribute name="authority">
4893 <xsl:text>mesh</xsl:text>
4897 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' ">
4898 <xsl:attribute name="authority">
4899 <xsl:text>nal</xsl:text>
4903 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' ">
4904 <xsl:attribute name="authority">
4905 <xsl:text>cash</xsl:text>
4909 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='a'">
4910 <xsl:attribute name="authority">
4911 <xsl:text>naf</xsl:text>
4915 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='b'">
4916 <xsl:attribute name="authority">lcsh</xsl:attribute>
4919 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='a'">
4920 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4923 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='b'">
4924 <xsl:attribute name="authority">cash</xsl:attribute>
4927 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'">
4928 <xsl:attribute name="authority">lcsh</xsl:attribute>
4931 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='0'">
4932 <xsl:attribute name="authority">naf</xsl:attribute>
4935 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='5'">
4936 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4938 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='0'">
4939 <xsl:attribute name="authority">lcsh</xsl:attribute>
4941 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='5'">
4942 <xsl:attribute name="authority">cash</xsl:attribute>
4945 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='1'">
4946 <xsl:attribute name="authority">lcshcl</xsl:attribute>
4949 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'">
4950 <xsl:attribute name="authority">nlmnaf</xsl:attribute>
4953 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'">
4954 <xsl:attribute name="authority">nalnaf</xsl:attribute>
4957 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='6'">
4958 <xsl:attribute name="authority">rvm</xsl:attribute>
4961 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='7'">
4962 <xsl:attribute name="authority">
4963 <xsl:value-of select="marc:subfield[@code='2']"/>
4967 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'">
4968 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4971 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'">
4972 <xsl:attribute name="authority">naf</xsl:attribute>
4975 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'">
4976 <xsl:attribute name="authority">lcsh</xsl:attribute>
4979 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'">
4980 <xsl:attribute name="authority">mesh</xsl:attribute>
4983 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'">
4984 <xsl:attribute name="authority">nal</xsl:attribute>
4987 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'">
4988 <xsl:attribute name="authority">cash</xsl:attribute>
4992 <xsl:template match="*"/>
4993 </xsl:stylesheet>$XSLT$);
4996 SELECT evergreen.upgrade_deps_block_check('1069', :eg_version); --gmcharlt/kmlussier
4998 -- subset of types listed in https://www.loc.gov/marc/authority/ad1xx3xx.html
4999 -- for now, ignoring subdivisions
5000 CREATE TYPE authority.heading_type AS ENUM (
5006 'chronological_term',
5010 'medium_of_performance_term'
5013 CREATE TYPE authority.variant_heading_type AS ENUM (
5022 CREATE TYPE authority.related_heading_type AS ENUM (
5025 'parent organization',
5032 CREATE TYPE authority.heading_purpose AS ENUM (
5038 CREATE TABLE authority.heading_field (
5039 id SERIAL PRIMARY KEY,
5040 heading_type authority.heading_type NOT NULL,
5041 heading_purpose authority.heading_purpose NOT NULL,
5042 label TEXT NOT NULL,
5043 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mads21',
5044 heading_xpath TEXT NOT NULL,
5045 component_xpath TEXT NOT NULL,
5046 type_xpath TEXT NULL, -- to extract related or variant type
5047 thesaurus_xpath TEXT NULL,
5048 thesaurus_override_xpath TEXT NULL,
5052 CREATE TABLE authority.heading_field_norm_map (
5053 id SERIAL PRIMARY KEY,
5054 field INT NOT NULL REFERENCES authority.heading_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
5055 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
5057 pos INT NOT NULL DEFAULT 0
5060 INSERT INTO authority.heading_field(heading_type, heading_purpose, label, heading_xpath, component_xpath, type_xpath, thesaurus_xpath, thesaurus_override_xpath) VALUES
5061 ( 'topical_term', 'main', 'Main Topical Term', '/mads21:mads/mads21:authority', '//mads21:topic', NULL, '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', NULL )
5062 ,( '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')
5063 ,( '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')
5064 ,( 'personal_name', 'main', 'Main Personal Name', '/mads21:mads/mads21:authority', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5065 ,( 'personal_name', 'variant', 'Variant Personal Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5066 ,( 'personal_name', 'related', 'Related Personal Name', '/mads21:mads/mads21:related', '//mads21:name[@type="personal"]', '/mads21:related/@type', NULL, NULL )
5067 ,( 'corporate_name', 'main', 'Main Corporate name', '/mads21:mads/mads21:authority', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5068 ,( 'corporate_name', 'variant', 'Variant Corporate Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5069 ,( 'corporate_name', 'related', 'Related Corporate Name', '/mads21:mads/mads21:related', '//mads21:name[@type="corporate"]', '/mads21:related/@type', NULL, NULL )
5070 ,( 'meeting_name', 'main', 'Main Meeting name', '/mads21:mads/mads21:authority', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5071 ,( 'meeting_name', 'variant', 'Variant Meeting Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5072 ,( 'meeting_name', 'related', 'Related Meeting Name', '/mads21:mads/mads21:related', '//mads21:name[@type="meeting"]', '/mads21:related/@type', NULL, NULL )
5073 ,( 'geographic_name', 'main', 'Main Geographic Term', '/mads21:mads/mads21:authority', '//mads21:geographic', NULL, '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', NULL )
5074 ,( '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')
5075 ,( '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')
5076 ,( 'genre_form_term', 'main', 'Main Genre/Form Term', '/mads21:mads/mads21:authority', '//mads21:genre', NULL, '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', NULL )
5077 ,( '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')
5078 ,( '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')
5079 ,( 'chronological_term', 'main', 'Main Chronological Term', '/mads21:mads/mads21:authority', '//mads21:temporal', NULL, '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', NULL )
5080 ,( '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')
5081 ,( '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')
5082 ,( 'uniform_title', 'main', 'Main Uniform Title', '/mads21:mads/mads21:authority', '//mads21:title', NULL, '/mads21:mads/mads21:authority/mads21:title[1]/@authority', NULL )
5083 ,( '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')
5084 ,( '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')
5087 -- NACO normalize all the things
5088 INSERT INTO authority.heading_field_norm_map (field, norm, pos)
5090 FROM authority.heading_field;
5092 CREATE TYPE authority.heading AS (
5094 type authority.heading_type,
5095 purpose authority.heading_purpose,
5096 variant_type authority.variant_heading_type,
5097 related_type authority.related_heading_type,
5100 normalized_heading TEXT
5103 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5105 idx authority.heading_field%ROWTYPE;
5106 xfrm config.xml_transform%ROWTYPE;
5108 transformed_xml TEXT;
5110 heading_node_list TEXT[];
5111 component_node TEXT;
5112 component_node_list TEXT[];
5114 normalized_text TEXT;
5119 base_thesaurus TEXT := NULL;
5120 output_row authority.heading;
5123 -- Loop over the indexing entries
5124 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
5126 output_row.field := idx.id;
5127 output_row.type := idx.heading_type;
5128 output_row.purpose := idx.heading_purpose;
5130 joiner := COALESCE(idx.joiner, ' ');
5132 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
5134 -- See if we can skip the XSLT ... it's expensive
5135 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5136 -- Can't skip the transform
5137 IF xfrm.xslt <> '---' THEN
5138 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
5140 transformed_xml := marc;
5143 prev_xfrm := xfrm.name;
5146 IF idx.thesaurus_xpath IS NOT NULL THEN
5147 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5150 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5152 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
5154 CONTINUE WHEN heading_node !~ E'^\\s*<';
5156 output_row.variant_type := NULL;
5157 output_row.related_type := NULL;
5158 output_row.thesaurus := NULL;
5159 output_row.heading := NULL;
5161 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
5162 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5164 output_row.variant_type := type_value;
5165 EXCEPTION WHEN invalid_text_representation THEN
5166 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
5169 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
5170 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5172 output_row.related_type := type_value;
5173 EXCEPTION WHEN invalid_text_representation THEN
5174 RAISE NOTICE 'Do not recognize related heading type %', type_value;
5178 IF idx.thesaurus_override_xpath IS NOT NULL THEN
5179 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5181 IF output_row.thesaurus IS NULL THEN
5182 output_row.thesaurus := base_thesaurus;
5187 -- now iterate over components of heading
5188 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5189 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
5190 -- XXX much of this should be moved into oils_xpath_string...
5191 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
5192 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
5193 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
5194 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
5198 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
5200 IF raw_text IS NOT NULL THEN
5201 raw_text := raw_text || joiner;
5204 raw_text := COALESCE(raw_text,'') || curr_text;
5207 IF raw_text IS NOT NULL THEN
5208 output_row.heading := raw_text;
5209 normalized_text := raw_text;
5212 SELECT n.func AS func,
5213 n.param_count AS param_count,
5215 FROM config.index_normalizer n
5216 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
5217 WHERE m.field = idx.id
5220 EXECUTE 'SELECT ' || normalizer.func || '(' ||
5221 quote_literal( normalized_text ) ||
5223 WHEN normalizer.param_count > 0
5224 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
5227 ')' INTO normalized_text;
5231 output_row.normalized_heading := normalized_text;
5233 RETURN NEXT output_row;
5239 $func$ LANGUAGE PLPGSQL;
5241 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5243 auth authority.record_entry%ROWTYPE;
5244 output_row authority.heading;
5247 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
5249 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
5251 $func$ LANGUAGE PLPGSQL;
5253 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5255 res authority.simple_heading%ROWTYPE;
5256 acsaf authority.control_set_authority_field%ROWTYPE;
5257 heading_row authority.heading%ROWTYPE;
5268 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
5271 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5273 IF cset IS NULL THEN
5274 SELECT control_set INTO cset
5275 FROM authority.control_set_authority_field
5276 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5280 res.record := auth_id;
5281 res.thesaurus := authority.extract_thesaurus(marcxml);
5283 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5284 res.atag := acsaf.id;
5286 IF acsaf.heading_field IS NULL THEN
5287 tag_used := acsaf.tag;
5288 nfi_used := acsaf.nfi;
5289 joiner_text := COALESCE(acsaf.joiner, ' ');
5291 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
5293 heading_text := COALESCE(
5294 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
5298 IF nfi_used IS NOT NULL THEN
5300 sort_text := SUBSTRING(
5305 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
5317 sort_text := heading_text;
5320 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5321 res.value := heading_text;
5322 res.sort_value := public.naco_normalize(sort_text);
5323 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5329 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
5330 res.value := heading_row.heading;
5331 res.sort_value := heading_row.normalized_heading;
5332 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5340 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5342 ALTER TABLE authority.control_set_authority_field ADD COLUMN heading_field INTEGER REFERENCES authority.heading_field(id);
5344 UPDATE authority.control_set_authority_field acsaf
5345 SET heading_field = ahf.id
5346 FROM authority.heading_field ahf
5349 AND ahf.heading_purpose = 'main'
5350 AND ahf.heading_type = 'personal_name';
5351 UPDATE authority.control_set_authority_field acsaf
5352 SET heading_field = ahf.id
5353 FROM authority.heading_field ahf
5356 AND ahf.heading_purpose = 'variant'
5357 AND ahf.heading_type = 'personal_name';
5358 UPDATE authority.control_set_authority_field acsaf
5359 SET heading_field = ahf.id
5360 FROM authority.heading_field ahf
5363 AND ahf.heading_purpose = 'related'
5364 AND ahf.heading_type = 'personal_name';
5366 UPDATE authority.control_set_authority_field acsaf
5367 SET heading_field = ahf.id
5368 FROM authority.heading_field ahf
5371 AND ahf.heading_purpose = 'main'
5372 AND ahf.heading_type = 'corporate_name';
5373 UPDATE authority.control_set_authority_field acsaf
5374 SET heading_field = ahf.id
5375 FROM authority.heading_field ahf
5378 AND ahf.heading_purpose = 'variant'
5379 AND ahf.heading_type = 'corporate_name';
5380 UPDATE authority.control_set_authority_field acsaf
5381 SET heading_field = ahf.id
5382 FROM authority.heading_field ahf
5385 AND ahf.heading_purpose = 'related'
5386 AND ahf.heading_type = 'corporate_name';
5388 UPDATE authority.control_set_authority_field acsaf
5389 SET heading_field = ahf.id
5390 FROM authority.heading_field ahf
5393 AND ahf.heading_purpose = 'main'
5394 AND ahf.heading_type = 'meeting_name';
5395 UPDATE authority.control_set_authority_field acsaf
5396 SET heading_field = ahf.id
5397 FROM authority.heading_field ahf
5400 AND ahf.heading_purpose = 'variant'
5401 AND ahf.heading_type = 'meeting_name';
5402 UPDATE authority.control_set_authority_field acsaf
5403 SET heading_field = ahf.id
5404 FROM authority.heading_field ahf
5407 AND ahf.heading_purpose = 'related'
5408 AND ahf.heading_type = 'meeting_name';
5410 UPDATE authority.control_set_authority_field acsaf
5411 SET heading_field = ahf.id
5412 FROM authority.heading_field ahf
5415 AND ahf.heading_purpose = 'main'
5416 AND ahf.heading_type = 'uniform_title';
5417 UPDATE authority.control_set_authority_field acsaf
5418 SET heading_field = ahf.id
5419 FROM authority.heading_field ahf
5422 AND ahf.heading_purpose = 'variant'
5423 AND ahf.heading_type = 'uniform_title';
5424 UPDATE authority.control_set_authority_field acsaf
5425 SET heading_field = ahf.id
5426 FROM authority.heading_field ahf
5429 AND ahf.heading_purpose = 'related'
5430 AND ahf.heading_type = 'uniform_title';
5432 UPDATE authority.control_set_authority_field acsaf
5433 SET heading_field = ahf.id
5434 FROM authority.heading_field ahf
5437 AND ahf.heading_purpose = 'main'
5438 AND ahf.heading_type = 'topical_term';
5439 UPDATE authority.control_set_authority_field acsaf
5440 SET heading_field = ahf.id
5441 FROM authority.heading_field ahf
5444 AND ahf.heading_purpose = 'variant'
5445 AND ahf.heading_type = 'topical_term';
5446 UPDATE authority.control_set_authority_field acsaf
5447 SET heading_field = ahf.id
5448 FROM authority.heading_field ahf
5451 AND ahf.heading_purpose = 'related'
5452 AND ahf.heading_type = 'topical_term';
5454 UPDATE authority.control_set_authority_field acsaf
5455 SET heading_field = ahf.id
5456 FROM authority.heading_field ahf
5459 AND ahf.heading_purpose = 'main'
5460 AND ahf.heading_type = 'geographic_name';
5461 UPDATE authority.control_set_authority_field acsaf
5462 SET heading_field = ahf.id
5463 FROM authority.heading_field ahf
5466 AND ahf.heading_purpose = 'variant'
5467 AND ahf.heading_type = 'geographic_name';
5468 UPDATE authority.control_set_authority_field acsaf
5469 SET heading_field = ahf.id
5470 FROM authority.heading_field ahf
5473 AND ahf.heading_purpose = 'related'
5474 AND ahf.heading_type = 'geographic_name';
5476 UPDATE authority.control_set_authority_field acsaf
5477 SET heading_field = ahf.id
5478 FROM authority.heading_field ahf
5481 AND ahf.heading_purpose = 'main'
5482 AND ahf.heading_type = 'genre_form_term';
5483 UPDATE authority.control_set_authority_field acsaf
5484 SET heading_field = ahf.id
5485 FROM authority.heading_field ahf
5488 AND ahf.heading_purpose = 'variant'
5489 AND ahf.heading_type = 'genre_form_term';
5490 UPDATE authority.control_set_authority_field acsaf
5491 SET heading_field = ahf.id
5492 FROM authority.heading_field ahf
5495 AND ahf.heading_purpose = 'related'
5496 AND ahf.heading_type = 'genre_form_term';
5499 SELECT evergreen.upgrade_deps_block_check('1070', :eg_version); --miker/gmcharlt/kmlussier
5501 CREATE TRIGGER thes_code_tracking_trigger
5502 AFTER UPDATE ON authority.thesaurus
5503 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
5505 ALTER TABLE authority.thesaurus ADD COLUMN short_code TEXT, ADD COLUMN uri TEXT;
5507 DELETE FROM authority.thesaurus WHERE control_set = 1 AND code NOT IN ('n',' ','|');
5508 UPDATE authority.thesaurus SET short_code = code;
5510 CREATE TEMP TABLE thesauri (code text, uri text, name text, xlate hstore);
5511 COPY thesauri (code, uri, name, xlate) FROM STDIN;
5512 migfg http://id.loc.gov/vocabulary/genreFormSchemes/migfg Moving image genre-form guide
5513 reveal http://id.loc.gov/vocabulary/genreFormSchemes/reveal REVEAL: fiction indexing and genre headings
5514 dct http://id.loc.gov/vocabulary/genreFormSchemes/dct Dublin Core list of resource types
5515 gmgpc http://id.loc.gov/vocabulary/genreFormSchemes/gmgpc Thesaurus for graphic materials: TGM II, Genre and physical characteristic terms
5516 rbgenr http://id.loc.gov/vocabulary/genreFormSchemes/rbgenr Genre terms: a thesaurus for use in rare book and special collections cataloguing
5517 sgp http://id.loc.gov/vocabulary/genreFormSchemes/sgp Svenska genrebeteckningar fr periodika "sv"=>"Svenska genrebeteckningar fr periodika"
5518 estc http://id.loc.gov/vocabulary/genreFormSchemes/estc Eighteenth century short title catalogue, the cataloguing rules. New ed.
5519 ftamc http://id.loc.gov/vocabulary/genreFormSchemes/ftamc Form terms for archival and manuscripts control
5520 alett http://id.loc.gov/vocabulary/genreFormSchemes/alett An alphabetical list of English text types
5521 gtlm http://id.loc.gov/vocabulary/genreFormSchemes/gtlm Genre terms for law materials: a thesaurus
5522 rbprov http://id.loc.gov/vocabulary/genreFormSchemes/rbprov Provenance evidence: a thesaurus for use in rare book and special collections cataloging
5523 rbbin http://id.loc.gov/vocabulary/genreFormSchemes/rbbin Binding terms: a thesaurus for use in rare book and special collections cataloguing
5524 fbg http://id.loc.gov/vocabulary/genreFormSchemes/fbg Films by genre /dd>
5525 isbdmedia http://id.loc.gov/vocabulary/genreFormSchemes/isbdmedia ISBD Area 0 [media]
5526 marccategory http://id.loc.gov/vocabulary/genreFormSchemes/marccategory MARC form category term list
5527 gnd-music http://id.loc.gov/vocabulary/genreFormSchemes/gnd-music Gemeinsame Normdatei: Musikalische Ausgabeform
5528 proysen http://id.loc.gov/vocabulary/genreFormSchemes/proysen Prøysen: emneord for Prøysen-bibliografien
5529 rdacarrier http://id.loc.gov/vocabulary/genreFormSchemes/rdacarrier Term and code list for RDA carrier types
5530 gnd http://id.loc.gov/vocabulary/genreFormSchemes/gnd Gemeinsame Normdatei
5531 cjh http://id.loc.gov/vocabulary/genreFormSchemes/cjh Center for Jewish History thesaurus
5532 rbpri http://id.loc.gov/vocabulary/genreFormSchemes/rbpri Printing & publishing evidence: a thesaurus for use in rare book and special collections cataloging
5533 fgtpcm http://id.loc.gov/vocabulary/genreFormSchemes/fgtpcm Form/genre terms for printed cartoon material
5534 rbpub http://id.loc.gov/vocabulary/genreFormSchemes/rbpub Printing and publishing evidence: a thesaurus for use in rare book and special collections cataloging
5535 gmd http://id.loc.gov/vocabulary/genreFormSchemes/gmd Anglo-American Cataloguing Rules general material designation
5536 rbpap http://id.loc.gov/vocabulary/genreFormSchemes/rbpap Paper terms: a thesaurus for use in rare book and special collections cataloging
5537 rdamedia http://id.loc.gov/vocabulary/genreFormSchemes/rdamedia Term and code list for RDA media types
5538 marcsmd http://id.loc.gov/vocabulary/genreFormSchemes/marcsmd MARC specific material form term list
5539 saogf http://id.loc.gov/vocabulary/genreFormSchemes/saogf Svenska ämnesord - Genre/Form "sv"=>"Svenska ämnesord - Genre/Form"
5540 lcgft http://id.loc.gov/vocabulary/genreFormSchemes/lcgft Library of Congress genre/form terms for library and archival materials
5541 muzeukv http://id.loc.gov/vocabulary/genreFormSchemes/muzeukv MuzeVideo UK DVD and UMD film genre classification
5542 mim http://id.loc.gov/vocabulary/genreFormSchemes/mim Moving image materials: genre terms
5543 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
5544 gnd-content http://id.loc.gov/vocabulary/genreFormSchemes/gnd-content Gemeinsame Normdatei: Beschreibung des Inhalts
5545 bgtchm http://id.loc.gov/vocabulary/genreFormSchemes/bgtchm Basic genre terms for cultural heritage materials
5546 gsafd http://id.loc.gov/vocabulary/genreFormSchemes/gsafd Guidelines on subject access to individual works of fiction, drama, etc
5547 marcform http://id.loc.gov/vocabulary/genreFormSchemes/marcform MARC form of item term list
5548 marcgt http://id.loc.gov/vocabulary/genreFormSchemes/marcgt MARC genre terms
5549 barngf http://id.loc.gov/vocabulary/genreFormSchemes/barngf Svenska ämnesord för barn - Genre/Form "sv"=>"Svenska ämnesord för barn - Genre/Form"
5550 ngl http://id.loc.gov/vocabulary/genreFormSchemes/ngl Newspaper genre list
5551 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"
5552 tgfbne http://id.loc.gov/vocabulary/genreFormSchemes/tgfbne Términos de género/forma de la Biblioteca Nacional de España
5553 nbdbgf http://id.loc.gov/vocabulary/genreFormSchemes/nbdbgf NBD Biblion Genres Fictie
5554 rbtyp http://id.loc.gov/vocabulary/genreFormSchemes/rbtyp Type evidence: a thesaurus for use in rare book and special collections cataloging
5555 radfg http://id.loc.gov/vocabulary/genreFormSchemes/radfg Radio form / genre terms guide
5556 gnd-carrier http://id.loc.gov/vocabulary/genreFormSchemes/gnd-carrier Gemeinsame Normdatei: Datenträgertyp
5557 gatbeg http://id.loc.gov/vocabulary/genreFormSchemes/gatbeg Gattungsbegriffe "de"=>"Gattungsbegriffe"
5558 rdacontent http://id.loc.gov/vocabulary/genreFormSchemes/rdacontent Term and code list for RDA content types
5559 isbdcontent http://id.loc.gov/vocabulary/genreFormSchemes/isbdcontent ISBD Area 0 [content]
5560 nimafc http://id.loc.gov/vocabulary/genreFormSchemes/nimafc NIMA form codes
5561 amg http://id.loc.gov/vocabulary/genreFormSchemes/amg Audiovisual material glossary
5562 local http://id.loc.gov/vocabulary/subjectSchemes/local Locally assigned term
5563 taika http://id.loc.gov/vocabulary/subjectSchemes/taika Taideteollisuuden asiasanasto "fi"=>"Taideteollisuuden asiasanasto"
5564 nasat http://id.loc.gov/vocabulary/subjectSchemes/nasat NASA thesaurus
5565 rswkaf http://id.loc.gov/vocabulary/subjectSchemes/rswkaf Alternativform zum Hauptschlagwort "de"=>"Alternativform zum Hauptschlagwort"
5566 jhpk http://id.loc.gov/vocabulary/subjectSchemes/jhpk JÄ
\99zyk haseÅ
\82 przedmiotowych KABA "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych KABA"
5567 asrcrfcd http://id.loc.gov/vocabulary/subjectSchemes/asrcrfcd Australian Standard Research Classification: Research Fields, Courses and Disciplines (RFCD) classification
5568 bt http://id.loc.gov/vocabulary/subjectSchemes/bt Bioethics thesaurus
5569 lcstt http://id.loc.gov/vocabulary/subjectSchemes/lcstt List of Chinese subject terms
5570 netc http://id.loc.gov/vocabulary/subjectSchemes/netc National Emergency Training Center Thesaurus (NETC)
5571 aat http://id.loc.gov/vocabulary/subjectSchemes/aat Art & architecture thesaurus
5572 bet http://id.loc.gov/vocabulary/subjectSchemes/bet British education thesaurus
5573 ncjt http://id.loc.gov/vocabulary/subjectSchemes/ncjt National criminal justice thesaurus
5574 samisk http://id.loc.gov/vocabulary/subjectSchemes/samisk Sami bibliography "no"=>"Sámi bibliografia = Samisk bibliografi (Norge)"
5575 tips http://id.loc.gov/vocabulary/subjectSchemes/tips Tesauro ISOC de psicologÃa "es"=>"Tesauro ISOC de psicologÃa"
5576 ukslc http://id.loc.gov/vocabulary/subjectSchemes/ukslc UK Standard Library Categories
5577 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"
5578 umitrist http://id.loc.gov/vocabulary/subjectSchemes/umitrist University of Michigan Transportation Research Institute structured thesaurus
5579 wgst http://id.loc.gov/vocabulary/subjectSchemes/wgst Washington GILS Subject Tree
5580 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"
5581 ntids http://id.loc.gov/vocabulary/subjectSchemes/ntids Norske tidsskrifter 1700-1820: emneord "no"=>"Norske tidsskrifter 1700-1820: emneord"
5582 kaa http://id.loc.gov/vocabulary/subjectSchemes/kaa Kasvatusalan asiasanasto "fi"=>"Kasvatusalan asiasanasto"
5583 yso http://id.loc.gov/vocabulary/subjectSchemes/yso YSO - Yleinen suomalainen ontologia "fi"=>"YSO - Yleinen suomalainen ontologia"
5584 gcipmedia http://id.loc.gov/vocabulary/subjectSchemes/gcipmedia GAMECIP - Computer Game Media Formats (GAMECIP (Game Metadata and Citation Project))
5585 inspect http://id.loc.gov/vocabulary/subjectSchemes/inspect INSPEC thesaurus
5586 ordnok http://id.loc.gov/vocabulary/subjectSchemes/ordnok Ordnokkelen: tesaurus for kulturminnevern "no"=>"Ordnokkelen: tesaurus for kulturminnevern"
5587 helecon http://id.loc.gov/vocabulary/subjectSchemes/helecon Asiasanasto HELECON-tietikantoihin "fi"=>"Asiasanasto HELECON-tietikantoihin"
5588 dltlt http://id.loc.gov/vocabulary/subjectSchemes/dltlt Cuddon, J. A. A dictionary of literary terms and literary theory
5589 csapa http://id.loc.gov/vocabulary/subjectSchemes/csapa "Controlled vocabulary" in Pollution abstracts
5590 gtt http://id.loc.gov/vocabulary/subjectSchemes/gtt GOO-trefwoorden thesaurus "nl"=>"GOO-trefwoorden thesaurus"
5591 iescs http://id.loc.gov/vocabulary/subjectSchemes/iescs International energy subject categories and scope
5592 itrt http://id.loc.gov/vocabulary/subjectSchemes/itrt International Thesaurus of Refugee Terminology
5593 sanb http://id.loc.gov/vocabulary/subjectSchemes/sanb South African national bibliography authority file
5594 blmlsh http://id.loc.gov/vocabulary/subjectSchemes/blmlsh British Library - Map library subject headings
5595 bhb http://id.loc.gov/vocabulary/subjectSchemes/bhb Bibliography of the Hebrew Book
5596 csh http://id.loc.gov/vocabulary/subjectSchemes/csh Kapsner, Oliver Leonard. Catholic subject headings
5597 fire http://id.loc.gov/vocabulary/subjectSchemes/fire FireTalk, IFSI thesaurus
5598 jlabsh http://id.loc.gov/vocabulary/subjectSchemes/jlabsh Basic subject headings "ja"=>"Kihon kenmei hyômokuhyô"
5599 udc http://id.loc.gov/vocabulary/subjectSchemes/udc Universal decimal classification
5600 lcshac http://id.loc.gov/vocabulary/subjectSchemes/lcshac Children's subject headings in Library of Congress subject headings: supplementary vocabularies
5601 geonet http://id.loc.gov/vocabulary/subjectSchemes/geonet NGA GEOnet Names Server (GNS)
5602 humord http://id.loc.gov/vocabulary/subjectSchemes/humord HUMORD "no"=>"HUMORD"
5603 no-ubo-mr http://id.loc.gov/vocabulary/subjectSchemes/no-ubo-mr Menneskerettighets-tesaurus "no"=>"Menneskerettighets-tesaurus"
5604 sgce http://id.loc.gov/vocabulary/subjectSchemes/sgce COBISS.SI General List of subject headings (English subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5605 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"
5606 thesoz http://id.loc.gov/vocabulary/subjectSchemes/thesoz Thesaurus for the Social Sciences
5607 asth http://id.loc.gov/vocabulary/subjectSchemes/asth Astronomy thesaurus
5608 muzeukc http://id.loc.gov/vocabulary/subjectSchemes/muzeukc MuzeMusic UK classical music classification
5609 norbok http://id.loc.gov/vocabulary/subjectSchemes/norbok Norbok: emneord i Norsk bokfortegnelse "no"=>"Norbok: emneord i Norsk bokfortegnelse"
5610 masa http://id.loc.gov/vocabulary/subjectSchemes/masa Museoalan asiasanasto "fi"=>"Museoalan asiasanasto"
5611 conorsi http://id.loc.gov/vocabulary/subjectSchemes/conorsi CONOR.SI (name authority file) (Maribor, Slovenia: Institut informacijskih znanosti (IZUM))
5612 eurovocen http://id.loc.gov/vocabulary/subjectSchemes/eurovocen Eurovoc thesaurus (English)
5613 kto http://id.loc.gov/vocabulary/subjectSchemes/kto KTO - Kielitieteen ontologia "fi"=>"KTO - Kielitieteen ontologia"
5614 muzvukci http://id.loc.gov/vocabulary/subjectSchemes/muzvukci MuzeVideo UK contributor index
5615 kaunokki http://id.loc.gov/vocabulary/subjectSchemes/kaunokki Kaunokki: kaunokirjallisuuden asiasanasto "fi"=>"Kaunokki: kaunokirjallisuuden asiasanasto"
5616 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"
5617 psychit http://id.loc.gov/vocabulary/subjectSchemes/psychit Thesaurus of psychological index terms.
5618 tlsh http://id.loc.gov/vocabulary/subjectSchemes/tlsh Subject heading authority list
5619 csalsct http://id.loc.gov/vocabulary/subjectSchemes/csalsct CSA life sciences collection thesaurus
5620 ciesiniv http://id.loc.gov/vocabulary/subjectSchemes/ciesiniv CIESIN indexing vocabulary
5621 ebfem http://id.loc.gov/vocabulary/subjectSchemes/ebfem Encabezamientos bilingües de la Fundación Educativa Ana G. Mendez
5622 mero http://id.loc.gov/vocabulary/subjectSchemes/mero MERO - Merenkulkualan ontologia "fi"=>"MERO - Merenkulkualan ontologia"
5623 mmm http://id.loc.gov/vocabulary/subjectSchemes/mmm "Subject key" in Marxism and the mass media
5624 pascal http://id.loc.gov/vocabulary/subjectSchemes/pascal PASCAL database classification scheme "fr"=>"Base de donneés PASCAL: plan de classement"
5625 chirosh http://id.loc.gov/vocabulary/subjectSchemes/chirosh Chiropractic Subject Headings
5626 cilla http://id.loc.gov/vocabulary/subjectSchemes/cilla Cilla: specialtesaurus för musik "fi"=>"Cilla: specialtesaurus för musik"
5627 aiatsisl http://id.loc.gov/vocabulary/subjectSchemes/aiatsisl AIATSIS language thesaurus
5628 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"
5629 lctgm http://id.loc.gov/vocabulary/subjectSchemes/lctgm Thesaurus for graphic materials: TGM I, Subject terms
5630 muso http://id.loc.gov/vocabulary/subjectSchemes/muso MUSO - Ontologi för musik "fi"=>"MUSO - Ontologi för musik"
5631 blcpss http://id.loc.gov/vocabulary/subjectSchemes/blcpss COMPASS subject authority system
5632 fast http://id.loc.gov/vocabulary/subjectSchemes/fast Faceted application of subject terminology
5633 bisacmt http://id.loc.gov/vocabulary/subjectSchemes/bisacmt BISAC Merchandising Themes
5634 lapponica http://id.loc.gov/vocabulary/subjectSchemes/lapponica Lapponica "fi"=>"Lapponica"
5635 juho http://id.loc.gov/vocabulary/subjectSchemes/juho JUHO - Julkishallinnon ontologia "fi"=>"JUHO - Julkishallinnon ontologia"
5636 idas http://id.loc.gov/vocabulary/subjectSchemes/idas ID-Archivschlüssel "de"=>"ID-Archivschlüssel"
5637 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."
5638 test http://id.loc.gov/vocabulary/subjectSchemes/test Thesaurus of engineering and scientific terms
5639 finmesh http://id.loc.gov/vocabulary/subjectSchemes/finmesh FinMeSH "fi"=>"FinMeSH"
5640 kssbar http://id.loc.gov/vocabulary/subjectSchemes/kssbar Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del "sv"=>"Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del"
5641 kupu http://id.loc.gov/vocabulary/subjectSchemes/kupu Maori Wordnet "mi"=>"He puna kupu"
5642 rpe http://id.loc.gov/vocabulary/subjectSchemes/rpe Rubricator on economics "ru"=>"Rubrikator po ekonomike"
5643 dit http://id.loc.gov/vocabulary/subjectSchemes/dit Defense intelligence thesaurus
5644 she http://id.loc.gov/vocabulary/subjectSchemes/she SHE: subject headings for engineering
5645 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"
5646 msc http://id.loc.gov/vocabulary/subjectSchemes/msc Mathematical subject classification
5647 muzeukn http://id.loc.gov/vocabulary/subjectSchemes/muzeukn MuzeMusic UK non-classical music classification
5648 ipsp http://id.loc.gov/vocabulary/subjectSchemes/ipsp Defense intelligence production schedule.
5649 sthus http://id.loc.gov/vocabulary/subjectSchemes/sthus Subject Taxonomy of the History of U.S. Foreign Relations
5650 poliscit http://id.loc.gov/vocabulary/subjectSchemes/poliscit Political science thesaurus II
5651 qtglit http://id.loc.gov/vocabulary/subjectSchemes/qtglit A queer thesaurus : an international thesaurus of gay and lesbian index terms
5652 unbist http://id.loc.gov/vocabulary/subjectSchemes/unbist UNBIS thesaurus
5653 gcipplatform http://id.loc.gov/vocabulary/subjectSchemes/gcipplatform GAMECIP - Computer Game Platforms (GAMECIP (Game Metadata and Citation Project))
5654 puho http://id.loc.gov/vocabulary/subjectSchemes/puho PUHO - Puolustushallinnon ontologia "fi"=>"PUHO - Puolustushallinnon ontologia"
5655 thub http://id.loc.gov/vocabulary/subjectSchemes/thub Thesaurus de la Universitat de Barcelona "ca"=>"Thesaurus de la Universitat de Barcelona"
5656 ndlsh http://id.loc.gov/vocabulary/subjectSchemes/ndlsh National Diet Library list of subject headings "ja"=>"Koktsu Kokkai Toshokan kenmei hyômokuhyô"
5657 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"
5658 idszbzzh http://id.loc.gov/vocabulary/subjectSchemes/idszbzzh Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung"
5659 unbisn http://id.loc.gov/vocabulary/subjectSchemes/unbisn UNBIS name authority list (New York, NY: Dag Hammarskjld Library, United Nations; : Chadwyck-Healey)
5660 rswk http://id.loc.gov/vocabulary/subjectSchemes/rswk Regeln für den Schlagwortkatalog "de"=>"Regeln für den Schlagwortkatalog"
5661 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"
5662 biccbmc http://id.loc.gov/vocabulary/subjectSchemes/biccbmc BIC Children's Books Marketing Classifications
5663 kulo http://id.loc.gov/vocabulary/subjectSchemes/kulo KULO - Kulttuurien tutkimuksen ontologia "fi"=>"KULO - Kulttuurien tutkimuksen ontologia"
5664 popinte http://id.loc.gov/vocabulary/subjectSchemes/popinte POPIN thesaurus: population multilingual thesaurus
5665 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"
5666 atg http://id.loc.gov/vocabulary/subjectSchemes/atg Agricultural thesaurus and glossary
5667 eflch http://id.loc.gov/vocabulary/subjectSchemes/eflch E4Libraries Category Headings
5668 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"
5669 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"
5670 csahssa http://id.loc.gov/vocabulary/subjectSchemes/csahssa "Controlled vocabulary" in Health and safety science abstracts
5671 sigle http://id.loc.gov/vocabulary/subjectSchemes/sigle SIGLE manual, Part 2, Subject category list
5672 blnpn http://id.loc.gov/vocabulary/subjectSchemes/blnpn British Library newspaper place names
5673 asrctoa http://id.loc.gov/vocabulary/subjectSchemes/asrctoa Australian Standard Research Classification: Type of Activity (TOA) classification
5674 lcdgt http://id.loc.gov/vocabulary/subjectSchemes/lcdgt Library of Congress demographic group term and code List
5675 bokbas http://id.loc.gov/vocabulary/subjectSchemes/bokbas Bokbasen "no"=>"Bokbasen"
5676 gnis http://id.loc.gov/vocabulary/subjectSchemes/gnis Geographic Names Information System (GNIS)
5677 nbiemnfag http://id.loc.gov/vocabulary/subjectSchemes/nbiemnfag NBIs emneordsliste for faglitteratur "no"=>"NBIs emneordsliste for faglitteratur"
5678 nlgaf http://id.loc.gov/vocabulary/subjectSchemes/nlgaf Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn "el"=>"Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn"
5679 bhashe http://id.loc.gov/vocabulary/subjectSchemes/bhashe BHA, Bibliography of the history of art, subject headings/English
5680 tsht http://id.loc.gov/vocabulary/subjectSchemes/tsht Thesaurus of subject headings for television
5681 scbi http://id.loc.gov/vocabulary/subjectSchemes/scbi Soggettario per i cataloghi delle biblioteche italiane "it"=>"Soggettario per i cataloghi delle biblioteche italiane"
5682 valo http://id.loc.gov/vocabulary/subjectSchemes/valo VALO - Fotografiska ontologin "fi"=>"VALO - Fotografiska ontologin"
5683 wpicsh http://id.loc.gov/vocabulary/subjectSchemes/wpicsh WPIC Library thesaurus of subject headings
5684 aktp http://id.loc.gov/vocabulary/subjectSchemes/aktp AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn "el"=>"AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn"
5685 stw http://id.loc.gov/vocabulary/subjectSchemes/stw STW Thesaurus for Economics "de"=>"Standard-Thesaurus Wirtschaft"
5686 mesh http://id.loc.gov/vocabulary/subjectSchemes/mesh Medical subject headings
5687 ica http://id.loc.gov/vocabulary/subjectSchemes/ica Index of Christian art
5688 emnmus http://id.loc.gov/vocabulary/subjectSchemes/emnmus Emneord for musikkdokument i EDB-kataloger "no"=>"Emneord for musikkdokument i EDB-kataloger"
5689 sao http://id.loc.gov/vocabulary/subjectSchemes/sao Svenska ämnesord "sv"=>"Svenska ämnesord"
5690 sgc http://id.loc.gov/vocabulary/subjectSchemes/sgc COBISS.SI General List of subject headings (Slovenian subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5691 bib1814 http://id.loc.gov/vocabulary/subjectSchemes/bib1814 1814-bibliografi: emneord for 1814-bibliografi "no"=>"1814-bibliografi: emneord for 1814-bibliografi"
5692 bjornson http://id.loc.gov/vocabulary/subjectSchemes/bjornson Bjornson: emneord for Bjornsonbibliografien "no"=>"Bjornson: emneord for Bjornsonbibliografien"
5693 liito http://id.loc.gov/vocabulary/subjectSchemes/liito LIITO - Liiketoimintaontologia "fi"=>"LIITO - Liiketoimintaontologia"
5694 apaist http://id.loc.gov/vocabulary/subjectSchemes/apaist APAIS thesaurus: a list of subject terms used in the Australian Public Affairs Information Service
5695 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)
5696 ntcsd http://id.loc.gov/vocabulary/subjectSchemes/ntcsd "National Translations Center secondary descriptors" in National Translation Center primary subject classification and secondary descriptor
5697 scisshl http://id.loc.gov/vocabulary/subjectSchemes/scisshl SCIS subject headings
5698 opms http://id.loc.gov/vocabulary/subjectSchemes/opms Opetusministeriön asiasanasto "fi"=>"Opetusministeriön asiasanasto"
5699 ttka http://id.loc.gov/vocabulary/subjectSchemes/ttka Teologisen tiedekunnan kirjaston asiasanasto "fi"=>"Teologisen tiedekunnan kirjaston asiasanasto"
5700 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
5701 ysa http://id.loc.gov/vocabulary/subjectSchemes/ysa Yleinen suomalainen asiasanasto "fi"=>"Yleinen suomalainen asiasanasto"
5702 kitu http://id.loc.gov/vocabulary/subjectSchemes/kitu Kirjallisuudentutkimuksen asiasanasto "fi"=>"Kirjallisuudentutkimuksen asiasanasto"
5703 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"
5704 aiatsisp http://id.loc.gov/vocabulary/subjectSchemes/aiatsisp AIATSIS place thesaurus
5705 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é"
5706 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"
5707 ated http://id.loc.gov/vocabulary/subjectSchemes/ated Australian Thesaurus of Education Descriptors (ATED)
5708 cabt http://id.loc.gov/vocabulary/subjectSchemes/cabt CAB thesaurus (Slough [England]: Commonwealth Agricultural Bureaux)
5709 kassu http://id.loc.gov/vocabulary/subjectSchemes/kassu Kassu - Kasvien suomenkieliset nimet "fi"=>"Kassu - Kasvien suomenkieliset nimet"
5710 nbdbt http://id.loc.gov/vocabulary/subjectSchemes/nbdbt NBD Biblion Trefwoordenthesaurus "nl"=>"NBD Biblion Trefwoordenthesaurus"
5711 jhpb http://id.loc.gov/vocabulary/subjectSchemes/jhpb JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej"
5712 bidex http://id.loc.gov/vocabulary/subjectSchemes/bidex Bilindex: a bilingual Spanish-English subject heading list
5713 ccsa http://id.loc.gov/vocabulary/subjectSchemes/ccsa Catalogue collectif suisse des affiches "fr"=>"Catalogue collectif suisse des affiches"
5714 noraf http://id.loc.gov/vocabulary/subjectSchemes/noraf Norwegian Authority File
5715 kito http://id.loc.gov/vocabulary/subjectSchemes/kito KITO - Kirjallisuudentutkimuksen ontologia "fi"=>"KITO - Kirjallisuudentutkimuksen ontologia"
5716 tho http://id.loc.gov/vocabulary/subjectSchemes/tho Thesauros HellÄ
\93nikÅ
\8dn Oron "el"=>"Thesauros HellÄ
\93nikÅ
\8dn Oron"
5717 pmont http://id.loc.gov/vocabulary/subjectSchemes/pmont Powerhouse Museum Object Name Thesaurus
5718 ssg http://id.loc.gov/vocabulary/subjectSchemes/ssg Splošni slovenski geslovnik "sl"=>"Splošni slovenski geslovnik"
5719 huc http://id.loc.gov/vocabulary/subjectSchemes/huc U.S. Geological Survey water-supply paper 2294: hydrologic basins unit codes
5720 isis http://id.loc.gov/vocabulary/subjectSchemes/isis "Classification scheme" in Isis
5721 ibsen http://id.loc.gov/vocabulary/subjectSchemes/ibsen Ibsen: emneord for Den internasjonale Ibsen-bibliografien "no"=>"Ibsen: emneord for Den internasjonale Ibsen-bibliografien"
5722 lacnaf http://id.loc.gov/vocabulary/subjectSchemes/lacnaf Library and Archives Canada name authority file
5723 swemesh http://id.loc.gov/vocabulary/subjectSchemes/swemesh Swedish MeSH "sv"=>"Svenska MeSH"
5724 hamsun http://id.loc.gov/vocabulary/subjectSchemes/hamsun Hamsun: emneord for Hamsunbibliografien "no"=>"Hamsun: emneord for Hamsunbibliografien"
5725 qrma http://id.loc.gov/vocabulary/subjectSchemes/qrma List of Arabic subject headings "ar"=>"Qâ'imat ru'ûs al-mawdûât al-'Arabîyah"
5726 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"
5727 ceeus http://id.loc.gov/vocabulary/subjectSchemes/ceeus Counties and equivalent entities of the United States its possessions, and associated areas
5728 taxhs http://id.loc.gov/vocabulary/subjectSchemes/taxhs A taxonomy or human services: a conceptual framework with standardized terminology and definitions for the field
5729 noram http://id.loc.gov/vocabulary/subjectSchemes/noram Noram: emneord for Norsk-amerikansk samling "no"=>"Noram: emneord for Norsk-amerikansk samling"
5730 eurovocfr http://id.loc.gov/vocabulary/subjectSchemes/eurovocfr Eurovoc thesaurus (French)
5731 jurivoc http://id.loc.gov/vocabulary/subjectSchemes/jurivoc JURIVOC
5732 agrifors http://id.loc.gov/vocabulary/subjectSchemes/agrifors AGRIFOREST-sanasto "fi"=>"AGRIFOREST-sanasto"
5733 noubojur http://id.loc.gov/vocabulary/subjectSchemes/noubojur Thesaurus of Law "no"=>"Thesaurus of Law"
5734 pha http://id.loc.gov/vocabulary/subjectSchemes/pha Puolostushallinnon asiasanasto "fi"=>"Puolostushallinnon asiasanasto"
5735 ddcrit http://id.loc.gov/vocabulary/subjectSchemes/ddcrit DDC retrieval and indexing terminology; posting terms with hierarchy and KWOC
5736 mar http://id.loc.gov/vocabulary/subjectSchemes/mar Merenkulun asiasanasto "fi"=>"Merenkulun asiasanasto"
5737 sbt http://id.loc.gov/vocabulary/subjectSchemes/sbt Soggettario Sistema Bibliotecario Ticinese "it"=>"Soggettario Sistema Bibliotecario Ticinese"
5738 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))
5739 kta http://id.loc.gov/vocabulary/subjectSchemes/kta Kielitieteen asiasanasto "fi"=>"Kielitieteen asiasanasto"
5740 snt http://id.loc.gov/vocabulary/subjectSchemes/snt Sexual nomenclature : a thesaurus
5741 francis http://id.loc.gov/vocabulary/subjectSchemes/francis FRANCIS database classification scheme "fr"=>"Base de donneés FRANCIS: plan de classement"
5742 eurovocsl http://id.loc.gov/vocabulary/subjectSchemes/eurovocsl Eurovoc thesaurus "sl"=>"Eurovoc thesaurus"
5743 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"
5744 nlmnaf http://id.loc.gov/vocabulary/subjectSchemes/nlmnaf National Library of Medicine name authority file
5745 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"
5746 sipri http://id.loc.gov/vocabulary/subjectSchemes/sipri SIPRI library thesaurus
5747 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"
5748 tucua http://id.loc.gov/vocabulary/subjectSchemes/tucua Thesaurus for use in college and university archives
5749 pmbok http://id.loc.gov/vocabulary/subjectSchemes/pmbok Guide to the project management body of knowledge (PMBOK Guide)
5750 agrovoc http://id.loc.gov/vocabulary/subjectSchemes/agrovoc AGROVOC multilingual agricultural thesaurus
5751 nal http://id.loc.gov/vocabulary/subjectSchemes/nal National Agricultural Library subject headings
5752 lnmmbr http://id.loc.gov/vocabulary/subjectSchemes/lnmmbr Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas "lt"=>"Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas"
5753 vmj http://id.loc.gov/vocabulary/subjectSchemes/vmj Vedettes-matière jeunesse "fr"=>"Vedettes-matière jeunesse"
5754 ddcut http://id.loc.gov/vocabulary/subjectSchemes/ddcut Dewey Decimal Classification user terms
5755 eks http://id.loc.gov/vocabulary/subjectSchemes/eks Eduskunnan kirjaston asiasanasto "fi"=>"Eduskunnan kirjaston asiasanasto"
5756 wot http://id.loc.gov/vocabulary/subjectSchemes/wot A Women's thesaurus
5757 noubomn http://id.loc.gov/vocabulary/subjectSchemes/noubomn University of Oslo Library Thesaurus of Science "no"=>"University of Oslo Library Thesaurus of Science"
5758 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"
5759 precis http://id.loc.gov/vocabulary/subjectSchemes/precis PRECIS: a manual of concept analysis and subject indexing
5760 cstud http://id.loc.gov/vocabulary/subjectSchemes/cstud Classificatieschema's Bibliotheek TU Delft "nl"=>"Classificatieschema's Bibliotheek TU Delft"
5761 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"
5762 pmt http://id.loc.gov/vocabulary/subjectSchemes/pmt Project management terminology. Newtown Square, PA: Project Management Institute
5763 ericd http://id.loc.gov/vocabulary/subjectSchemes/ericd Thesaurus of ERIC descriptors
5764 rvm http://id.loc.gov/vocabulary/subjectSchemes/rvm Répertoire de vedettes-matière "fr"=>"Répertoire de vedettes-matière"
5765 sfit http://id.loc.gov/vocabulary/subjectSchemes/sfit Svenska filminstitutets tesaurus "sv"=>"Svenska filminstitutets tesaurus"
5766 trtsa http://id.loc.gov/vocabulary/subjectSchemes/trtsa Teatterin ja tanssin asiasanasto "fi"=>"Teatterin ja tanssin asiasanasto"
5767 ulan http://id.loc.gov/vocabulary/subjectSchemes/ulan Union list of artist names
5768 unescot http://id.loc.gov/vocabulary/subjectSchemes/unescot UNESCO thesaurus "fr"=>"Thésaurus de l'UNESCO","es"=>"Tesauro de la UNESCO"
5769 koko http://id.loc.gov/vocabulary/subjectSchemes/koko KOKO-ontologia "fi"=>"KOKO-ontologia"
5770 msh http://id.loc.gov/vocabulary/subjectSchemes/msh Trimboli, T., and Martyn S. Marianist subject headings
5771 trt http://id.loc.gov/vocabulary/subjectSchemes/trt Transportation resource thesaurus
5772 agrovocf http://id.loc.gov/vocabulary/subjectSchemes/agrovocf AGROVOC thésaurus agricole multilingue "fr"=>"AGROVOC thésaurus agricole multilingue"
5773 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"
5774 ddcri http://id.loc.gov/vocabulary/subjectSchemes/ddcri Dewey Decimal Classification Relative Index
5775 est http://id.loc.gov/vocabulary/subjectSchemes/est International energy: subject thesaurus (: International Energy Agency, Energy Technology Data Exchange)
5776 lua http://id.loc.gov/vocabulary/subjectSchemes/lua Liikunnan ja urheilun asiasanasto "fi"=>"Liikunnan ja urheilun asiasanasto"
5777 mipfesd http://id.loc.gov/vocabulary/subjectSchemes/mipfesd Macrothesaurus for information processing in the field of economic and social development
5778 rurkp http://id.loc.gov/vocabulary/subjectSchemes/rurkp Predmetnye rubriki Rossiiskoi knizhnoi palaty "ru"=>"Predmetnye rubriki Rossiiskoi knizhnoi palaty"
5779 albt http://id.loc.gov/vocabulary/subjectSchemes/albt Arbetslivsbibliotekets tesaurus "sv"=>"Arbetslivsbibliotekets tesaurus"
5780 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"
5781 bicssc http://id.loc.gov/vocabulary/subjectSchemes/bicssc BIC standard subject categories
5782 cctf http://id.loc.gov/vocabulary/subjectSchemes/cctf Carto-Canadiana thésaurus - Français "fr"=>"Carto-Canadiana thésaurus - Français"
5783 reo http://id.loc.gov/vocabulary/subjectSchemes/reo Māori Subject Headings thesaurus "mi"=>"Ngā Ūpoko Tukutuku"
5784 icpsr http://id.loc.gov/vocabulary/subjectSchemes/icpsr ICPSR controlled vocabulary system
5785 kao http://id.loc.gov/vocabulary/subjectSchemes/kao KVINNSAM ämnesordsregister "sv"=>"KVINNSAM ämnesordsregister"
5786 asrcseo http://id.loc.gov/vocabulary/subjectSchemes/asrcseo Australian Standard Research Classification: Socio-Economic Objective (SEO) classification
5787 georeft http://id.loc.gov/vocabulary/subjectSchemes/georeft GeoRef thesaurus
5788 cct http://id.loc.gov/vocabulary/subjectSchemes/cct Chinese Classified Thesaurus "zh"=>"Zhong guo fen lei zhu ti ci biao"
5789 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"
5790 musa http://id.loc.gov/vocabulary/subjectSchemes/musa Musiikin asiasanasto: erikoissanasto "fi"=>"Musiikin asiasanasto: erikoissanasto"
5791 ntissc http://id.loc.gov/vocabulary/subjectSchemes/ntissc NTIS subject categories
5792 idszbz http://id.loc.gov/vocabulary/subjectSchemes/idszbz Thesaurus IDS Nebis Zentralbibliothek Zürich "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich"
5793 tlka http://id.loc.gov/vocabulary/subjectSchemes/tlka Investigació, Procés Tècnicn kirjaston asiasanasto "fi"=>"Investigació, Procés Tècnicn kirjaston asiasanasto"
5794 usaidt http://id.loc.gov/vocabulary/subjectSchemes/usaidt USAID thesaurus: Keywords used to index documents included in the USAID Development Experience System.
5795 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"
5796 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"
5797 ntcpsc http://id.loc.gov/vocabulary/subjectSchemes/ntcpsc "National Translations Center primary subject classification" in National Translations Center primary subject classification and secondary descriptors
5798 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"
5799 allars http://id.loc.gov/vocabulary/subjectSchemes/allars Allärs: allmän tesaurus pä svenska "fi"=>"Allärs: allmän tesaurus pä svenska"
5800 ogst http://id.loc.gov/vocabulary/subjectSchemes/ogst Oregon GILS Subject Tree (Oregon: Oregon State Library and Oregon Information Resource Management Division (IRMD))
5801 bella http://id.loc.gov/vocabulary/subjectSchemes/bella Bella: specialtesaurus för skönlitteratur "fi"=>"Bella: specialtesaurus för skönlitteratur"
5802 bibalex http://id.loc.gov/vocabulary/subjectSchemes/bibalex Bibliotheca Alexandrina name and subject authority file
5803 pepp http://id.loc.gov/vocabulary/subjectSchemes/pepp The Princeton encyclopedia of poetry and poetics
5804 hkcan http://id.loc.gov/vocabulary/subjectSchemes/hkcan Hong Kong Chinese Authority File (Name) - HKCAN
5805 dissao http://id.loc.gov/vocabulary/subjectSchemes/dissao "Dissertation abstracts online" in Search tools: the guide to UNI/Data Courier Online
5806 ltcsh http://id.loc.gov/vocabulary/subjectSchemes/ltcsh Land Tenure Center Library list of subject headings
5807 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"
5808 asft http://id.loc.gov/vocabulary/subjectSchemes/asft Aquatic sciences and fisheries thesaurus
5809 naf http://id.loc.gov/vocabulary/subjectSchemes/naf NACO authority file
5810 nimacsc http://id.loc.gov/vocabulary/subjectSchemes/nimacsc NIMA cartographic subject categories
5811 khib http://id.loc.gov/vocabulary/subjectSchemes/khib Emneord, KHiB Biblioteket "no"=>"Emneord, KHiB Biblioteket"
5812 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"
5813 afset http://id.loc.gov/vocabulary/subjectSchemes/afset American Folklore Society Ethnographic Thesaurus
5814 erfemn http://id.loc.gov/vocabulary/subjectSchemes/erfemn Erfaringskompetanses emneord "no"=>"Erfaringskompetanses emneord"
5815 sbiao http://id.loc.gov/vocabulary/subjectSchemes/sbiao Svenska barnboksinstitutets ämnesordslista "sv"=>"Svenska barnboksinstitutets ämnesordslista"
5816 socio http://id.loc.gov/vocabulary/subjectSchemes/socio Sociological Abstracts Thesaurus
5817 bisacrt http://id.loc.gov/vocabulary/subjectSchemes/bisacrt BISAC Regional Themes
5818 eum http://id.loc.gov/vocabulary/subjectSchemes/eum Eesti uldine märksonastik "et"=>"Eesti uldine märksonastik"
5819 kula http://id.loc.gov/vocabulary/subjectSchemes/kula Kulttuurien tutkimuksen asiasanasto "fi"=>"Kulttuurien tutkimuksen asiasanasto"
5820 odlt http://id.loc.gov/vocabulary/subjectSchemes/odlt Baldick, C. The Oxford dictionary of literary terms
5821 rerovoc http://id.loc.gov/vocabulary/subjectSchemes/rerovoc Indexation matiéres RERO autoritès "fr"=>"Indexation matiéres RERO autoritès"
5822 tsr http://id.loc.gov/vocabulary/subjectSchemes/tsr TSR-ontologia "fi"=>"TSR-ontologia"
5823 czmesh http://id.loc.gov/vocabulary/subjectSchemes/czmesh Czech MeSH "cs"=>"Czech MeSH"
5824 dltt http://id.loc.gov/vocabulary/subjectSchemes/dltt Quinn, E. A dictionary of literary and thematic terms
5825 idsbb http://id.loc.gov/vocabulary/subjectSchemes/idsbb Thesaurus IDS Basel Bern "de"=>"Thesaurus IDS Basel Bern"
5826 inist http://id.loc.gov/vocabulary/subjectSchemes/inist INIS: thesaurus
5827 idszbzzk http://id.loc.gov/vocabulary/subjectSchemes/idszbzzk Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung"
5828 tesa http://id.loc.gov/vocabulary/subjectSchemes/tesa Tesauro AgrÃcola "es"=>"Tesauro AgrÃcola"
5829 liv http://id.loc.gov/vocabulary/subjectSchemes/liv Legislative indexing vocabulary
5830 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"
5831 nsbncf http://id.loc.gov/vocabulary/subjectSchemes/nsbncf Nuovo Soggettario "it"=>"Nuovo Soggettario"
5832 ipat http://id.loc.gov/vocabulary/subjectSchemes/ipat IPA thesaurus and frequency list
5833 skon http://id.loc.gov/vocabulary/subjectSchemes/skon Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur "sv"=>"Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur"
5834 renib http://id.loc.gov/vocabulary/subjectSchemes/renib Renib "es"=>"Renib"
5835 hrvmesh http://id.loc.gov/vocabulary/subjectSchemes/hrvmesh Croatian MeSH / Hrvatski MeSH "no"=>"Croatian MeSH / Hrvatski MeSH"
5836 swd http://id.loc.gov/vocabulary/subjectSchemes/swd Schlagwortnormdatei "de"=>"Schlagwortnormdatei"
5837 aass http://id.loc.gov/vocabulary/subjectSchemes/aass "Asian American Studies Library subject headings" in A Guide for establishing Asian American core collections
5838 cht http://id.loc.gov/vocabulary/subjectSchemes/cht Chicano thesaurus for indexing Chicano materials in Chicano periodical index
5839 galestne http://id.loc.gov/vocabulary/subjectSchemes/galestne Gale Group subject thesaurus and named entity vocabulary
5840 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"
5841 hoidokki http://id.loc.gov/vocabulary/subjectSchemes/hoidokki Hoitotieteellinen asiasanasto
5842 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"
5843 kubikat http://id.loc.gov/vocabulary/subjectSchemes/kubikat kubikat "de"=>"kubikat"
5844 waqaf http://id.loc.gov/vocabulary/subjectSchemes/waqaf Maknas Uloom Al Waqaf "ar"=>"Maknas Uloom Al Waqaf"
5845 hapi http://id.loc.gov/vocabulary/subjectSchemes/hapi HAPI thesaurus and name authority, 1970-2000
5846 drama http://id.loc.gov/vocabulary/subjectSchemes/drama Drama: specialtesaurus för teater och dans
5847 sosa http://id.loc.gov/vocabulary/subjectSchemes/sosa Sociaalialan asiasanasto "fi"=>"Sociaalialan asiasanasto"
5848 ilpt http://id.loc.gov/vocabulary/subjectSchemes/ilpt Index to legal periodicals: thesaurus
5849 nicem http://id.loc.gov/vocabulary/subjectSchemes/nicem NICEM subject headings and classification system
5850 qlsp http://id.loc.gov/vocabulary/subjectSchemes/qlsp Queens Library Spanish language subject headings
5851 eet http://id.loc.gov/vocabulary/subjectSchemes/eet European education thesaurus
5852 nalnaf http://id.loc.gov/vocabulary/subjectSchemes/nalnaf National Agricultural Library name authority file
5853 eclas http://id.loc.gov/vocabulary/subjectSchemes/eclas ECLAS thesaurus
5854 agrovocs http://id.loc.gov/vocabulary/subjectSchemes/agrovocs AGROVOC tesauro agrÃcola multilingée "es"=>"AGROVOC tesauro agrÃcola multilingée"
5855 shbe http://id.loc.gov/vocabulary/subjectSchemes/shbe Subject headings in business and economics "sv"=>"Subject headings in business and economics"
5856 barn http://id.loc.gov/vocabulary/subjectSchemes/barn Svenska ämnesord för barn "sv"=>"Svenska ämnesord för barn"
5857 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"
5858 gccst http://id.loc.gov/vocabulary/subjectSchemes/gccst Government of Canada core subject thesaurus (Gatineau : Library and Archives Canada)
5859 fnhl http://id.loc.gov/vocabulary/subjectSchemes/fnhl First Nations House of Learning Subject Headings
5860 kauno http://id.loc.gov/vocabulary/subjectSchemes/kauno KAUNO - Kaunokki-ontologin "fi"=>"KAUNO - Kaunokki-ontologin"
5861 dtict http://id.loc.gov/vocabulary/subjectSchemes/dtict Defense Technical Information Center thesaurus
5862 mech http://id.loc.gov/vocabulary/subjectSchemes/mech Iskanje po zbirki MECH "sl"=>"Iskanje po zbirki MECH"
5863 jupo http://id.loc.gov/vocabulary/subjectSchemes/jupo JUPO - Julkisen hallinnon palveluontologia "fi"=>"JUPO - Julkisen hallinnon palveluontologia"
5864 ktpt http://id.loc.gov/vocabulary/subjectSchemes/ktpt Kirjasto- ja tietopalvelualan tesaurus "fi"=>"Kirjasto- ja tietopalvelualan tesaurus"
5865 aiatsiss http://id.loc.gov/vocabulary/subjectSchemes/aiatsiss AIATSIS subject Thesaurus
5866 lcac http://id.loc.gov/vocabulary/subjectSchemes/lcac Library of Congress Annotated Children's Cataloging Program subject headings
5867 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à "
5868 lemb http://id.loc.gov/vocabulary/subjectSchemes/lemb Lista de encabezamientos de materia para bibliotecas "es"=>"Lista de encabezamientos de materia para bibliotecas"
5869 henn http://id.loc.gov/vocabulary/subjectSchemes/henn Hennepin County Library cumulative authority list
5870 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"
5871 cash http://id.loc.gov/vocabulary/subjectSchemes/cash Canadian subject headings
5872 nznb http://id.loc.gov/vocabulary/subjectSchemes/nznb New Zealand national bibliographic
5873 prvt http://id.loc.gov/vocabulary/subjectSchemes/prvt Patent- och registreringsverkets tesaurus "sv"=>"Patent- och registreringsverkets tesaurus"
5874 scgdst http://id.loc.gov/vocabulary/subjectSchemes/scgdst Subject categorization guide for defense science and technology
5875 gem http://id.loc.gov/vocabulary/subjectSchemes/gem GEM controlled vocabularies
5876 lcsh http://id.loc.gov/vocabulary/subjectSchemes/lcsh Library of Congress subject headings
5877 rero http://id.loc.gov/vocabulary/subjectSchemes/rero Indexation matires RERO "fr"=>"Indexation matires RERO"
5878 peri http://id.loc.gov/vocabulary/subjectSchemes/peri Perinnetieteiden asiasanasto "fi"=>"Perinnetieteiden asiasanasto"
5879 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"
5880 slem http://id.loc.gov/vocabulary/subjectSchemes/slem Sears: lista de encabezamientos de materia "es"=>"Sears: lista de encabezamientos de materia"
5881 afo http://id.loc.gov/vocabulary/subjectSchemes/afo AFO - Viikin kampuskirjaston ontologia "fi"=>"AFO - Viikin kampuskirjaston ontologia"
5882 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
5883 hlasstg http://id.loc.gov/vocabulary/subjectSchemes/hlasstg HLAS subject term glossary
5884 iest http://id.loc.gov/vocabulary/subjectSchemes/iest International energy: subject thesaurus
5885 pkk http://id.loc.gov/vocabulary/subjectSchemes/pkk Predmetnik za katoliške knjižnice "sl"=>"Predmetnik za katoliške knjižnice"
5886 atla http://id.loc.gov/vocabulary/subjectSchemes/atla Religion indexes: thesaurus
5887 scot http://id.loc.gov/vocabulary/subjectSchemes/scot Schools Online Thesaurus (ScOT)
5888 smda http://id.loc.gov/vocabulary/subjectSchemes/smda Smithsonian National Air and Space Museum Directory of Airplanes
5889 solstad http://id.loc.gov/vocabulary/subjectSchemes/solstad Solstad: emneord for Solstadbibliografien "no"=>"Solstad: emneord for Solstadbibliografien"
5890 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"
5891 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"
5892 ktta http://id.loc.gov/vocabulary/subjectSchemes/ktta Käsi - ja taideteollisuuden asiasanasto "fi"=>"Käsi - ja taideteollisuuden asiasanasto"
5893 ccte http://id.loc.gov/vocabulary/subjectSchemes/ccte Carto-Canadiana thesaurus - English
5894 pmcsg http://id.loc.gov/vocabulary/subjectSchemes/pmcsg Combined standards glossary
5895 bisacsh http://id.loc.gov/vocabulary/subjectSchemes/bisacsh BISAC Subject Headings
5896 fssh http://id.loc.gov/vocabulary/subjectSchemes/fssh FamilySearch Subject Headings (FamilySearch)
5897 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"
5898 tero http://id.loc.gov/vocabulary/subjectSchemes/tero TERO - Terveyden ja hyvinvoinnin ontologia "fi"=>"TERO - Terveyden ja hyvinvoinnin ontologia"
5899 rma http://id.loc.gov/vocabulary/subjectSchemes/rma Ru'us al-mawdu'at al-'Arabiyah "ar"=>"Ru'us al-mawdu'at al-'Arabiyah"
5900 tgn http://id.loc.gov/vocabulary/subjectSchemes/tgn Getty thesaurus of geographic names
5901 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"
5902 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"
5903 sears http://id.loc.gov/vocabulary/subjectSchemes/sears Sears list of subject headings
5904 csht http://id.loc.gov/vocabulary/subjectSchemes/csht Chinese subject headings
5909 INSERT INTO authority.thesaurus (code, uri, name, control_set)
5910 SELECT code, uri, name, 1 FROM thesauri;
5912 UPDATE authority.thesaurus SET short_code = 'a' WHERE code = 'lcsh';
5913 UPDATE authority.thesaurus SET short_code = 'b' WHERE code = 'lcshac';
5914 UPDATE authority.thesaurus SET short_code = 'c' WHERE code = 'mesh';
5915 UPDATE authority.thesaurus SET short_code = 'd' WHERE code = 'nal';
5916 UPDATE authority.thesaurus SET short_code = 'k' WHERE code = 'cash';
5917 UPDATE authority.thesaurus SET short_code = 'r' WHERE code = 'aat';
5918 UPDATE authority.thesaurus SET short_code = 's' WHERE code = 'sears';
5919 UPDATE authority.thesaurus SET short_code = 'v' WHERE code = 'rvm';
5921 UPDATE authority.thesaurus
5922 SET short_code = 'z'
5923 WHERE short_code IS NULL
5924 AND control_set = 1;
5926 INSERT INTO config.i18n_core (fq_field, identity_value, translation, string )
5927 SELECT 'at.name', t.code, xlate->key, xlate->value
5929 JOIN LATERAL each(t.xlate) AS xlate ON TRUE
5932 FROM config.i18n_core
5933 WHERE fq_field = 'at.name'
5934 AND identity_value = t.code
5935 AND translation = xlate->key)
5936 AND t.xlate IS NOT NULL
5937 AND t.name <> (xlate->value);
5939 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
5943 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
5944 IF thes_code IS NULL THEN
5946 ELSIF thes_code = 'z' THEN
5947 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), 'z' );
5949 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
5951 thes_code := '|'; -- default
5956 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5958 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
5960 IF NEW.control_set IS NULL THEN
5961 SELECT control_set INTO NEW.control_set
5962 FROM authority.thesaurus
5963 WHERE code = authority.extract_thesaurus(NEW.marc);
5968 $func$ LANGUAGE PLPGSQL;
5970 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
5972 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
5973 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
5975 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
5976 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
5977 authority.extract_thesaurus(marc)
5978 FROM authority.record_entry
5982 $func$ LANGUAGE PLPGSQL;
5986 SELECT evergreen.upgrade_deps_block_check('1071', :eg_version); --gmcharlt/kmlussier
5988 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)
5989 RETURNS SETOF metabib.flat_browse_entry_appearance
5998 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
5999 results_skipped INT := 0;
6000 row_counter INT := 0;
6005 all_records BIGINT[];
6006 all_brecords BIGINT[];
6007 all_arecords BIGINT[];
6008 superpage_of_records BIGINT[];
6013 unauthorized_entry RECORD;
6015 IF count_up_from_zero THEN
6022 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
6025 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
6026 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
6028 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
6030 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
6031 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
6033 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
6035 b_tests := b_tests || search.calculate_visibility_attribute_test(
6037 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
6040 b_tests := b_tests || search.calculate_visibility_attribute_test(
6042 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
6046 IF context_locations THEN
6047 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
6048 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
6051 OPEN curs NO SCROLL FOR EXECUTE query;
6054 FETCH curs INTO rec;
6056 IF result_row.pivot_point IS NOT NULL THEN
6057 RETURN NEXT result_row;
6063 SELECT INTO unauthorized_entry *
6064 FROM metabib.browse_entry_simple_heading_map mbeshm
6065 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6066 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
6067 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6068 WHERE mbeshm.entry = rec.id
6069 AND ahf.heading_purpose = 'variant';
6071 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
6072 IF (unauthorized_entry.record IS NOT NULL) THEN
6073 --unauthorized term belongs to an auth linked to a bib?
6074 SELECT INTO all_arecords, result_row.sees, afields
6075 ARRAY_AGG(DISTINCT abl.bib),
6076 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
6077 ARRAY_AGG(DISTINCT map.metabib_field)
6078 FROM authority.bib_linking abl
6079 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6080 map.authority_field = unauthorized_entry.atag
6081 AND map.metabib_field = ANY(fields)
6083 WHERE abl.authority = unauthorized_entry.record;
6085 --do usual procedure
6086 SELECT INTO all_arecords, result_row.sees, afields
6087 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
6088 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
6089 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
6091 FROM metabib.browse_entry_simple_heading_map mbeshm
6092 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6093 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
6094 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
6095 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6096 ash.atag = map.authority_field
6097 AND map.metabib_field = ANY(fields)
6099 JOIN authority.control_set_authority_field acsaf ON (
6100 map.authority_field = acsaf.id
6102 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6103 WHERE mbeshm.entry = rec.id
6104 AND ahf.heading_purpose = 'variant';
6108 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
6109 SELECT INTO all_brecords, result_row.authorities, bfields
6110 ARRAY_AGG(DISTINCT source),
6111 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
6112 ARRAY_AGG(DISTINCT def)
6113 FROM metabib.browse_entry_def_map
6114 WHERE entry = rec.id
6115 AND def = ANY(fields);
6117 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
6119 result_row.sources := 0;
6120 result_row.asources := 0;
6122 -- Bib-linked vis checking
6123 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
6125 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
6126 FROM biblio.record_entry b
6127 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6128 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
6130 acvac.vis_attr_vector @@ c_tests::query_int
6131 OR b.vis_attr_vector @@ b_tests::query_int
6134 result_row.accurate := TRUE;
6138 -- Authority-linked vis checking
6139 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
6141 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
6142 FROM biblio.record_entry b
6143 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6144 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
6146 acvac.vis_attr_vector @@ c_tests::query_int
6147 OR b.vis_attr_vector @@ b_tests::query_int
6150 result_row.aaccurate := TRUE;
6154 IF result_row.sources > 0 OR result_row.asources > 0 THEN
6156 -- The function that calls this function needs row_number in order
6157 -- to correctly order results from two different runs of this
6159 result_row.row_number := row_number;
6161 -- Now, if row_counter is still less than limit, return a row. If
6162 -- not, but it is less than next_pivot_pos, continue on without
6163 -- returning actual result rows until we find
6164 -- that next pivot, and return it.
6166 IF row_counter < result_limit THEN
6167 result_row.browse_entry := rec.id;
6168 result_row.value := rec.value;
6170 RETURN NEXT result_row;
6172 result_row.browse_entry := NULL;
6173 result_row.authorities := NULL;
6174 result_row.fields := NULL;
6175 result_row.value := NULL;
6176 result_row.sources := NULL;
6177 result_row.sees := NULL;
6178 result_row.accurate := NULL;
6179 result_row.aaccurate := NULL;
6180 result_row.pivot_point := rec.id;
6182 IF row_counter >= next_pivot_pos THEN
6183 RETURN NEXT result_row;
6188 IF count_up_from_zero THEN
6189 row_number := row_number + 1;
6191 row_number := row_number - 1;
6194 -- row_counter is different from row_number.
6195 -- It simply counts up from zero so that we know when
6196 -- we've reached our limit.
6197 row_counter := row_counter + 1;
6201 $f$ LANGUAGE plpgsql ROWS 10;
6203 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)
6204 RETURNS SETOF metabib.flat_browse_entry_appearance
6210 pivot_sort_value TEXT;
6211 pivot_sort_fallback TEXT;
6212 context_locations INT[];
6213 browse_superpage_size INT;
6214 results_skipped INT := 0;
6218 forward_to_pivot INT;
6220 -- First, find the pivot if we were given a browse term but not a pivot.
6221 IF pivot_id IS NULL THEN
6222 pivot_id := metabib.browse_pivot(search_field, browse_term);
6225 SELECT INTO pivot_sort_value, pivot_sort_fallback
6226 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
6228 -- Bail if we couldn't find a pivot.
6229 IF pivot_sort_value IS NULL THEN
6233 -- Transform the context_loc_group argument (if any) (logc at the
6234 -- TPAC layer) into a form we'll be able to use.
6235 IF context_loc_group IS NOT NULL THEN
6236 SELECT INTO context_locations ARRAY_AGG(location)
6237 FROM asset.copy_location_group_map
6238 WHERE lgroup = context_loc_group;
6241 -- Get the configured size of browse superpages.
6242 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
6243 FROM config.global_flag
6244 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
6246 -- First we're going to search backward from the pivot, then we're going
6247 -- to search forward. In each direction, we need two limits. At the
6248 -- lesser of the two limits, we delineate the edge of the result set
6249 -- we're going to return. At the greater of the two limits, we find the
6250 -- pivot value that would represent an offset from the current pivot
6251 -- at a distance of one "page" in either direction, where a "page" is a
6252 -- result set of the size specified in the "result_limit" argument.
6254 -- The two limits in each direction make four derived values in total,
6255 -- and we calculate them now.
6256 back_limit := CEIL(result_limit::FLOAT / 2);
6257 back_to_pivot := result_limit;
6258 forward_limit := result_limit / 2;
6259 forward_to_pivot := result_limit - 1;
6261 -- This is the meat of the SQL query that finds browse entries. We'll
6262 -- pass this to a function which uses it with a cursor, so that individual
6263 -- rows may be fetched in a loop until some condition is satisfied, without
6264 -- waiting for a result set of fixed size to be collected all at once.
6269 FROM metabib.browse_entry mbe
6271 EXISTS ( -- are there any bibs using this mbe via the requested fields?
6273 FROM metabib.browse_entry_def_map mbedm
6274 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
6275 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
6277 FROM metabib.browse_entry_simple_heading_map mbeshm
6278 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6279 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6280 ash.atag = map.authority_field
6281 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
6283 JOIN authority.control_set_authority_field acsaf ON (
6284 map.authority_field = acsaf.id
6286 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6287 WHERE mbeshm.entry = mbe.id
6288 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
6289 -- and authority that variant is coming from is linked to a bib
6292 FROM metabib.browse_entry_def_map mbedm2
6293 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
6298 -- This is the variant of the query for browsing backward.
6299 back_query := core_query ||
6300 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
6301 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
6303 -- This variant browses forward.
6304 forward_query := core_query ||
6305 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
6306 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
6308 -- We now call the function which applies a cursor to the provided
6309 -- queries, stopping at the appropriate limits and also giving us
6310 -- the next page's pivot.
6312 SELECT * FROM metabib.staged_browse(
6313 back_query, search_field, context_org, context_locations,
6314 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
6316 SELECT * FROM metabib.staged_browse(
6317 forward_query, search_field, context_org, context_locations,
6318 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
6319 ) ORDER BY row_number DESC;
6322 $f$ LANGUAGE plpgsql ROWS 10;
6325 SELECT evergreen.upgrade_deps_block_check('1072', :eg_version); --gmcharlt/kmlussier
6327 INSERT INTO config.global_flag (name, label, enabled) VALUES (
6328 'opac.show_related_headings_in_browse',
6330 'opac.show_related_headings_in_browse',
6331 'Display related headings (see-also) in browse',
6340 SELECT evergreen.upgrade_deps_block_check('1073', :eg_version);
6342 ALTER TABLE config.metabib_field
6343 ADD COLUMN display_xpath TEXT,
6344 ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
6346 CREATE TABLE config.display_field_map (
6347 name TEXT PRIMARY KEY,
6348 field INTEGER REFERENCES config.metabib_field (id),
6349 multi BOOLEAN DEFAULT FALSE
6352 CREATE TABLE metabib.display_entry (
6353 id BIGSERIAL PRIMARY KEY,
6354 source BIGINT NOT NULL REFERENCES biblio.record_entry (id),
6355 field INT NOT NULL REFERENCES config.metabib_field (id),
6359 CREATE INDEX metabib_display_entry_field_idx ON metabib.display_entry (field);
6360 CREATE INDEX metabib_display_entry_source_idx ON metabib.display_entry (source);
6362 -- one row per display entry fleshed with field info
6363 CREATE VIEW metabib.flat_display_entry AS
6371 FROM metabib.display_entry mde
6372 JOIN config.metabib_field cmf ON (cmf.id = mde.field)
6373 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
6376 -- like flat_display_entry except values are compressed
6377 -- into one row per display_field_map and JSON-ified.
6378 CREATE VIEW metabib.compressed_display_entry AS
6385 CASE WHEN multi THEN
6386 TO_JSON(ARRAY_AGG(value))
6390 FROM metabib.flat_display_entry
6391 GROUP BY 1, 2, 3, 4, 5
6394 -- TODO: expand to encompass all well-known fields
6395 CREATE VIEW metabib.wide_display_entry AS
6398 COALESCE(mcde_title.value, 'null') AS title,
6399 COALESCE(mcde_author.value, 'null') AS author,
6400 COALESCE(mcde_subject.value, 'null') AS subject,
6401 COALESCE(mcde_creators.value, 'null') AS creators,
6402 COALESCE(mcde_isbn.value, 'null') AS isbn
6403 -- ensure one row per bre regardless of any display fields
6404 FROM biblio.record_entry bre
6405 LEFT JOIN metabib.compressed_display_entry mcde_title
6406 ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
6407 LEFT JOIN metabib.compressed_display_entry mcde_author
6408 ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
6409 LEFT JOIN metabib.compressed_display_entry mcde_subject
6410 ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
6411 LEFT JOIN metabib.compressed_display_entry mcde_creators
6412 ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
6413 LEFT JOIN metabib.compressed_display_entry mcde_isbn
6414 ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
6418 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
6419 RETURNS TRIGGER AS $$
6422 display_field_text TEXT;
6424 display_field_text := NEW.value;
6427 SELECT n.func AS func,
6428 n.param_count AS param_count,
6430 FROM config.index_normalizer n
6431 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
6432 WHERE m.field = NEW.field AND m.pos < 0
6435 EXECUTE 'SELECT ' || normalizer.func || '(' ||
6436 quote_literal( display_field_text ) ||
6438 WHEN normalizer.param_count > 0
6439 THEN ',' || REPLACE(REPLACE(BTRIM(
6440 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
6443 ')' INTO display_field_text;
6447 NEW.value = display_field_text;
6451 $$ LANGUAGE PLPGSQL;
6453 CREATE TRIGGER display_field_normalize_tgr
6454 BEFORE UPDATE OR INSERT ON metabib.display_entry
6455 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
6457 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
6458 RETURNS TRIGGER AS $$
6460 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
6463 $$ LANGUAGE PLPGSQL;
6465 CREATE TRIGGER display_field_force_nfc_tgr
6466 BEFORE UPDATE OR INSERT ON metabib.display_entry
6467 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
6469 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE display_field BOOL;
6471 DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL);
6472 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT);
6473 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT, TEXT);
6475 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
6477 default_joiner TEXT,
6480 ) RETURNS SETOF metabib.field_entry_template AS $func$
6482 bib biblio.record_entry%ROWTYPE;
6483 idx config.metabib_field%ROWTYPE;
6484 xfrm config.xml_transform%ROWTYPE;
6486 transformed_xml TEXT;
6488 xml_node_list TEXT[];
6495 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
6496 authority_text TEXT;
6497 authority_link BIGINT;
6498 output_row metabib.field_entry_template%ROWTYPE;
6502 -- Start out with no field-use bools set
6503 output_row.browse_field = FALSE;
6504 output_row.facet_field = FALSE;
6505 output_row.display_field = FALSE;
6506 output_row.search_field = FALSE;
6509 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
6511 -- Loop over the indexing entries
6512 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
6514 process_idx := FALSE;
6515 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
6516 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
6517 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
6518 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
6519 CONTINUE WHEN process_idx = FALSE;
6521 joiner := COALESCE(idx.joiner, default_joiner);
6523 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
6525 -- See if we can skip the XSLT ... it's expensive
6526 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
6527 -- Can't skip the transform
6528 IF xfrm.xslt <> '---' THEN
6529 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
6531 transformed_xml := bib.marc;
6534 prev_xfrm := xfrm.name;
6537 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6540 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
6541 CONTINUE WHEN xml_node !~ E'^\\s*<';
6543 -- XXX much of this should be moved into oils_xpath_string...
6544 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
6545 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
6546 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
6547 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
6551 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
6553 IF raw_text IS NOT NULL THEN
6554 raw_text := raw_text || joiner;
6557 raw_text := COALESCE(raw_text,'') || curr_text;
6559 -- autosuggest/metabib.browse_entry
6560 IF idx.browse_field THEN
6562 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
6563 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6565 browse_text := curr_text;
6568 IF idx.browse_sort_xpath IS NOT NULL AND
6569 idx.browse_sort_xpath <> '' THEN
6571 sort_value := oils_xpath_string(
6572 idx.browse_sort_xpath, xml_node, joiner,
6573 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
6576 sort_value := browse_text;
6579 output_row.field_class = idx.field_class;
6580 output_row.field = idx.id;
6581 output_row.source = rid;
6582 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
6583 output_row.sort_value :=
6584 public.naco_normalize(sort_value);
6586 output_row.authority := NULL;
6588 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
6589 authority_text := oils_xpath_string(
6590 idx.authority_xpath, xml_node, joiner,
6592 ARRAY[xfrm.prefix, xfrm.namespace_uri],
6593 ARRAY['xlink','http://www.w3.org/1999/xlink']
6597 IF authority_text ~ '^\d+$' THEN
6598 authority_link := authority_text::BIGINT;
6599 PERFORM * FROM authority.record_entry WHERE id = authority_link;
6601 output_row.authority := authority_link;
6607 output_row.browse_field = TRUE;
6608 -- Returning browse rows with search_field = true for search+browse
6609 -- configs allows us to retain granularity of being able to search
6610 -- browse fields with "starts with" type operators (for example, for
6611 -- titles of songs in music albums)
6612 IF idx.search_field THEN
6613 output_row.search_field = TRUE;
6615 RETURN NEXT output_row;
6616 output_row.browse_field = FALSE;
6617 output_row.search_field = FALSE;
6618 output_row.sort_value := NULL;
6621 -- insert raw node text for faceting
6622 IF idx.facet_field THEN
6624 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
6625 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6627 facet_text := curr_text;
6630 output_row.field_class = idx.field_class;
6631 output_row.field = -1 * idx.id;
6632 output_row.source = rid;
6633 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
6635 output_row.facet_field = TRUE;
6636 RETURN NEXT output_row;
6637 output_row.facet_field = FALSE;
6640 -- insert raw node text for display
6641 IF idx.display_field THEN
6643 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
6644 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6646 display_text := curr_text;
6649 output_row.field_class = idx.field_class;
6650 output_row.field = -1 * idx.id;
6651 output_row.source = rid;
6652 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
6654 output_row.display_field = TRUE;
6655 RETURN NEXT output_row;
6656 output_row.display_field = FALSE;
6661 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
6663 -- insert combined node text for searching
6664 IF idx.search_field THEN
6665 output_row.field_class = idx.field_class;
6666 output_row.field = idx.id;
6667 output_row.source = rid;
6668 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
6670 output_row.search_field = TRUE;
6671 RETURN NEXT output_row;
6672 output_row.search_field = FALSE;
6679 $func$ LANGUAGE PLPGSQL;
6681 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
6683 skip_facet BOOL DEFAULT FALSE,
6684 skip_display BOOL DEFAULT FALSE,
6685 skip_browse BOOL DEFAULT FALSE,
6686 skip_search BOOL DEFAULT FALSE,
6687 only_fields INT[] DEFAULT '{}'::INT[]
6688 ) RETURNS VOID AS $func$
6691 ind_data metabib.field_entry_template%ROWTYPE;
6692 mbe_row metabib.browse_entry%ROWTYPE;
6695 b_skip_display BOOL;
6699 field_list INT[] := only_fields;
6700 field_types TEXT[] := '{}'::TEXT[];
6703 IF field_list = '{}'::INT[] THEN
6704 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
6707 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;
6708 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;
6709 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;
6710 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;
6712 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
6713 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
6714 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
6715 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
6717 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
6719 IF NOT b_skip_search THEN
6720 FOR fclass IN SELECT * FROM config.metabib_class LOOP
6721 -- RAISE NOTICE 'Emptying out %', fclass.name;
6722 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
6725 IF NOT b_skip_facet THEN
6726 DELETE FROM metabib.facet_entry WHERE source = bib_id;
6728 IF NOT b_skip_display THEN
6729 DELETE FROM metabib.display_entry WHERE source = bib_id;
6731 IF NOT b_skip_browse THEN
6732 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
6736 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
6738 -- don't store what has been normalized away
6739 CONTINUE WHEN ind_data.value IS NULL;
6741 IF ind_data.field < 0 THEN
6742 ind_data.field = -1 * ind_data.field;
6745 IF ind_data.facet_field AND NOT b_skip_facet THEN
6746 INSERT INTO metabib.facet_entry (field, source, value)
6747 VALUES (ind_data.field, ind_data.source, ind_data.value);
6750 IF ind_data.display_field AND NOT b_skip_display THEN
6751 INSERT INTO metabib.display_entry (field, source, value)
6752 VALUES (ind_data.field, ind_data.source, ind_data.value);
6756 IF ind_data.browse_field AND NOT b_skip_browse THEN
6757 -- A caveat about this SELECT: this should take care of replacing
6758 -- old mbe rows when data changes, but not if normalization (by
6759 -- which I mean specifically the output of
6760 -- evergreen.oils_tsearch2()) changes. It may or may not be
6761 -- expensive to add a comparison of index_vector to index_vector
6762 -- to the WHERE clause below.
6764 CONTINUE WHEN ind_data.sort_value IS NULL;
6766 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
6767 SELECT INTO mbe_row * FROM metabib.browse_entry
6768 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
6771 mbe_id := mbe_row.id;
6773 INSERT INTO metabib.browse_entry
6774 ( value, sort_value ) VALUES
6775 ( value_prepped, ind_data.sort_value );
6777 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
6780 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
6781 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
6784 IF ind_data.search_field AND NOT b_skip_search THEN
6785 -- Avoid inserting duplicate rows
6786 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
6787 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
6788 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
6789 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
6790 IF mbe_id IS NULL THEN
6792 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
6794 quote_literal(ind_data.field) || $$, $$ ||
6795 quote_literal(ind_data.source) || $$, $$ ||
6796 quote_literal(ind_data.value) ||
6803 IF NOT b_skip_search THEN
6804 PERFORM metabib.update_combined_index_vectors(bib_id);
6809 $func$ LANGUAGE PLPGSQL;
6811 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
6812 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
6817 IF NEW.deleted THEN -- If this bib is deleted
6819 PERFORM * FROM config.internal_flag WHERE
6820 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
6822 tmp_bool := FOUND; -- Just in case this is changed by some other statement
6824 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
6826 IF NOT tmp_bool THEN
6827 -- One needs to keep these around to support searches
6828 -- with the #deleted modifier, so one should turn on the named
6829 -- internal flag for that functionality.
6830 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
6833 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
6834 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
6835 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
6836 RETURN NEW; -- and we're done
6839 IF TG_OP = 'UPDATE' THEN -- re-ingest?
6840 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
6842 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
6847 -- Record authority linking
6848 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
6850 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
6853 -- Flatten and insert the mfr data
6854 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
6856 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
6858 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
6859 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
6861 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
6865 -- Gather and insert the field entry data
6866 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
6868 -- Located URI magic
6869 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
6870 IF NOT FOUND THEN PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); END IF;
6872 -- (re)map metarecord-bib linking
6873 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
6874 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
6876 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6878 ELSE -- we're doing an update, and we're not deleted, remap
6879 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
6881 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6887 $func$ LANGUAGE PLPGSQL;
6892 SELECT evergreen.upgrade_deps_block_check('1074', :eg_version);
6894 INSERT INTO config.internal_flag (name, enabled)
6895 VALUES ('ingest.skip_display_indexing', FALSE);
6897 -- Adds seed data to replace (for now) values from the 'mvr' class
6899 UPDATE config.metabib_field SET display_field = TRUE WHERE id IN (6, 8, 16, 18);
6901 INSERT INTO config.metabib_field ( id, field_class, name, label,
6902 format, xpath, display_field, display_xpath ) VALUES
6903 (37, 'author', 'creator', oils_i18n_gettext(37, 'All Creators', 'cmf', 'label'),
6904 'mods32', $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']]$$,
6905 TRUE, $$//*[local-name()='namePart']$$ ); -- /* to fool vim */;
6908 UPDATE config.metabib_field SET display_xpath =
6909 $$//*[local-name()='namePart']$$ -- /* to fool vim */
6912 INSERT INTO config.display_field_map (name, field, multi) VALUES
6913 ('title', 6, FALSE),
6914 ('author', 8, FALSE),
6915 ('creators', 37, TRUE),
6916 ('subject', 16, TRUE),
6922 SELECT evergreen.upgrade_deps_block_check('1075', :eg_version);
6924 CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$
6926 IF NEW.imported_as IS NULL THEN
6929 PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as;
6931 RAISE foreign_key_violation USING MESSAGE = FORMAT(
6932 $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as
6937 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
6939 SELECT evergreen.upgrade_deps_block_check('1077', :eg_version); -- csharp/gmcharlt
6941 -- if the "public" version of this function exists, drop it to prevent confusion/trouble
6943 -- drop triggers that depend on this first
6944 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON biblio.record_entry;
6945 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON serial.record_entry;
6946 DROP TRIGGER IF EXISTS c_maintain_control_numbers ON authority.record_entry;
6948 DROP FUNCTION IF EXISTS public.maintain_control_numbers();
6950 -- create the function within the "evergreen" schema
6952 CREATE OR REPLACE FUNCTION evergreen.maintain_control_numbers() RETURNS TRIGGER AS $func$
6955 use MARC::File::XML (BinaryEncoding => 'UTF-8');
6958 use Unicode::Normalize;
6960 MARC::Charset->assume_unicode(1);
6962 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
6963 my $schema = $_TD->{table_schema};
6964 my $rec_id = $_TD->{new}{id};
6966 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
6967 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
6968 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
6972 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
6973 my $ou_cni = 'EVRGRN';
6976 if ($schema eq 'serial') {
6977 $owner = $_TD->{new}{owning_lib};
6979 # are.owner and bre.owner can be null, so fall back to the consortial setting
6980 $owner = $_TD->{new}{owner} || 1;
6983 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
6984 if ($ous_rv->{processed}) {
6985 $ou_cni = $ous_rv->{rows}[0]->{value};
6986 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
6988 # Fall back to the shortname of the OU if there was no OU setting
6989 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
6990 if ($ous_rv->{processed}) {
6991 $ou_cni = $ous_rv->{rows}[0]->{shortname};
6995 my ($create, $munge) = (0, 0);
6997 my @scns = $record->field('035');
6999 foreach my $id_field ('001', '003') {
7001 my @controls = $record->field($id_field);
7003 if ($id_field eq '001') {
7004 $spec_value = $rec_id;
7006 $spec_value = $ou_cni;
7009 # Create the 001/003 if none exist
7010 if (scalar(@controls) == 1) {
7011 # Only one field; check to see if we need to munge it
7012 unless (grep $_->data() eq $spec_value, @controls) {
7016 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
7017 foreach my $control (@controls) {
7018 $record->delete_field($control);
7020 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
7025 my $cn = $record->field('001')->data();
7026 # Special handling of OCLC numbers, often found in records that lack 003
7027 if ($cn =~ /^o(c[nm]|n)\d/) {
7028 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
7029 $record->field('003')->data('OCoLC');
7033 # Now, if we need to munge the 001, we will first push the existing 001/003
7034 # into the 035; but if the record did not have one (and one only) 001 and 003
7035 # to begin with, skip this process
7036 if ($munge and not $create) {
7038 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
7040 # Do not create duplicate 035 fields
7041 unless (grep $_->subfield('a') eq $scn, @scns) {
7042 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
7046 # Set the 001/003 and update the MARC
7047 if ($create or $munge) {
7048 $record->field('001')->data($rec_id);
7049 $record->field('003')->data($ou_cni);
7051 my $xml = $record->as_xml_record();
7053 $xml =~ s/^<\?xml.+\?\s*>//go;
7054 $xml =~ s/>\s+</></go;
7055 $xml =~ s/\p{Cc}//go;
7057 # Embed a version of OpenILS::Application::AppUtils->entityize()
7058 # to avoid having to set PERL5LIB for PostgreSQL as well
7062 # Convert raw ampersands to entities
7063 $xml =~ s/&(?!\S+;)/&/gso;
7065 # Convert Unicode characters to entities
7066 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
7068 $xml =~ s/[\x00-\x1f]//go;
7069 $_TD->{new}{marc} = $xml;
7075 $func$ LANGUAGE PLPERLU;
7077 -- re-create the triggers
7078 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
7079 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
7080 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
7084 \echo ---------------------------------------------------------------------
7085 \echo Updating visibility attribute vector for biblio.record_entry
7088 ALTER TABLE biblio.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
7089 ALTER TABLE biblio.record_entry DISABLE TRIGGER aaa_indexing_ingest_or_delete;
7090 ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update_trigger;
7091 ALTER TABLE biblio.record_entry DISABLE TRIGGER b_maintain_901;
7092 ALTER TABLE biblio.record_entry DISABLE TRIGGER bbb_simple_rec_trigger;
7093 ALTER TABLE biblio.record_entry DISABLE TRIGGER c_maintain_control_numbers;
7094 ALTER TABLE biblio.record_entry DISABLE TRIGGER fingerprint_tgr;
7096 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id) WHERE NOT DELETED;
7098 ALTER TABLE biblio.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
7099 ALTER TABLE biblio.record_entry ENABLE TRIGGER aaa_indexing_ingest_or_delete;
7100 ALTER TABLE biblio.record_entry ENABLE TRIGGER audit_biblio_record_entry_update_trigger;
7101 ALTER TABLE biblio.record_entry ENABLE TRIGGER b_maintain_901;
7102 ALTER TABLE biblio.record_entry ENABLE TRIGGER bbb_simple_rec_trigger;
7103 ALTER TABLE biblio.record_entry ENABLE TRIGGER c_maintain_control_numbers;
7104 ALTER TABLE biblio.record_entry ENABLE TRIGGER fingerprint_tgr;
7108 \echo ---------------------------------------------------------------------
7109 \echo Reingest display fields. This can be canceled via Ctrl-C and run at
7110 \echo a later time with the following (or similar) SQL:
7112 \echo 'SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE, '
7113 \echo ' (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))'
7114 \echo ' FROM biblio.record_entry WHERE NOT deleted AND id > 0;'
7116 \echo Note that if you cancel now, you will also need to do the authority reingest
7117 \echo further down in the upgrade script.
7119 -- REINGEST DISPLAY ENTRIES
7120 SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE,
7121 (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))
7122 FROM biblio.record_entry WHERE NOT deleted AND id > 0;
7125 \echo ---------------------------------------------------------------------
7126 \echo Reingest authority records. This can be canceled via Ctrl-C and run
7127 \echo at a later time; see the upgrade script. Note that if you cancel now,
7128 \echo you should consult this upgrade script for the reingest actions required.
7131 -- add the flag ingest.disable_authority_full_rec if it does not exist
7132 INSERT INTO config.internal_flag (name, enabled)
7133 SELECT 'ingest.disable_authority_full_rec', FALSE
7134 WHERE NOT EXISTS (SELECT 1 FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec');
7136 CREATE TEMPORARY TABLE internal_flag_state AS
7137 SELECT name, enabled
7138 FROM config.internal_flag
7140 'ingest.reingest.force_on_same_marc',
7141 'ingest.disable_authority_auto_update',
7142 'ingest.disable_authority_full_rec'
7145 UPDATE config.internal_flag
7148 'ingest.reingest.force_on_same_marc',
7149 'ingest.disable_authority_auto_update',
7150 'ingest.disable_authority_full_rec'
7153 ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
7154 ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
7155 ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
7156 ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;
7158 UPDATE authority.record_entry SET id = id WHERE NOT DELETED;
7160 ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
7161 ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
7162 ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
7163 ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;
7166 UPDATE config.internal_flag a
7167 SET enabled = b.enabled
7168 FROM internal_flag_state b
7169 WHERE a.name = b.name;