1 --Upgrade Script for 2.12.5 to 3.0-beta1
2 \set eg_version '''3.0-beta1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0-beta1', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1032', :eg_version); -- Bmagic/csharp/gmcharlt
8 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
14 ac_acirc.circ_modifier AS item_type,
15 'regular_circ'::text AS circ_type
16 FROM action.circulation acirc,
18 WHERE acirc.target_copy = ac_acirc.id
20 SELECT ancc.id::BIGINT AS id,
21 ancc.circ_time AS xact_start,
23 ancc.staff AS circ_staff,
24 ancc.circ_time AS create_time,
25 cnct_ancc.name AS item_type,
26 'non-cat_circ'::text AS circ_type
27 FROM action.non_cataloged_circulation ancc,
28 config.non_cataloged_type cnct_ancc
29 WHERE ancc.item_type = cnct_ancc.id
31 SELECT aihu.id::BIGINT AS id,
32 aihu.use_time AS xact_start,
33 aihu.org_unit AS circ_lib,
34 aihu.staff AS circ_staff,
35 aihu.use_time AS create_time,
36 ac_aihu.circ_modifier AS item_type,
37 'in-house_use'::text AS circ_type
38 FROM action.in_house_use aihu,
40 WHERE aihu.item = ac_aihu.id
42 SELECT ancihu.id::BIGINT AS id,
43 ancihu.use_time AS xact_start,
44 ancihu.org_unit AS circ_lib,
45 ancihu.staff AS circ_staff,
46 ancihu.use_time AS create_time,
47 cnct_ancihu.name AS item_type,
48 'non-cat_circ'::text AS circ_type
49 FROM action.non_cat_in_house_use ancihu,
50 config.non_cataloged_type cnct_ancihu
51 WHERE ancihu.item_type = cnct_ancihu.id
53 SELECT aacirc.id AS id,
58 ac_aacirc.circ_modifier AS item_type,
59 'aged_circ'::text AS circ_type
60 FROM action.aged_circulation aacirc,
62 WHERE aacirc.target_copy = ac_aacirc.id;
65 SELECT evergreen.upgrade_deps_block_check('1034', :eg_version);
67 ALTER TABLE config.hold_matrix_matchpoint
68 ADD COLUMN description TEXT;
70 ALTER TABLE config.circ_matrix_matchpoint
71 ADD COLUMN description TEXT;
74 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1035', :eg_version); -- dyrcona/gmcharlt
76 -- Per Lp bug 1684984, the config.internal_flag,
77 -- ingest.disable_metabib_field_entry, was made obsolete by the
78 -- addition of the ingest.skip_browse_indexing,
79 -- ingest.skip_search_indexing, and ingest.skip_facet_indexing flags.
80 -- Since it is not used in the database, we delete it.
81 DELETE FROM config.internal_flag
82 WHERE name = 'ingest.disable_metabib_field_entry';
85 SELECT evergreen.upgrade_deps_block_check('1036', :eg_version);
87 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
89 temp_value config.hard_due_date_values%ROWTYPE;
93 SELECT DISTINCT ON (hard_due_date) *
94 FROM config.hard_due_date_values
95 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
96 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
98 UPDATE config.hard_due_date
99 SET ceiling_date = temp_value.ceiling_date
100 WHERE id = temp_value.hard_due_date
101 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
102 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
105 updated := updated + 1;
111 $func$ LANGUAGE plpgsql;
114 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1041', :eg_version); -- stompro/csharp/gmcharlt
116 --delete all instances from permission.grp_perm_map first
117 DELETE FROM permission.grp_perm_map where perm in
118 (select id from permission.perm_list where code='SET_CIRC_MISSING');
120 --delete all instances from permission.usr_perm_map too
121 DELETE FROM permission.usr_perm_map where perm in
122 (select id from permission.perm_list where code='SET_CIRC_MISSING');
124 --delete from permission.perm_list
125 DELETE FROM permission.perm_list where code='SET_CIRC_MISSING';
128 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1042', :eg_version); -- mmorgan/gmcharlt
130 ALTER TABLE asset.copy_location
134 SELECT evergreen.upgrade_deps_block_check('1043', :eg_version);
136 ALTER TABLE action_trigger.event_definition
137 ADD COLUMN retention_interval INTERVAL;
139 CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval()
140 RETURNS TRIGGER AS $_$
144 * 1. Retention intervals are alwyas allowed on active hooks.
145 * 2. On passive hooks, retention intervals are only allowed
146 * when the event definition has a max_delay value and the
147 * retention_interval value is greater than the difference
148 * beteween the delay and max_delay values.
150 PERFORM TRUE FROM action_trigger.hook
151 WHERE key = NEW.hook AND NOT passive;
157 IF NEW.max_delay IS NOT NULL THEN
158 IF EXTRACT(EPOCH FROM NEW.retention_interval) >
159 ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
160 RETURN NEW; -- all good
162 RAISE EXCEPTION 'retention_interval is too short';
165 RAISE EXCEPTION 'retention_interval requires max_delay';
168 $_$ LANGUAGE PLPGSQL;
170 CREATE TRIGGER is_valid_retention_interval
171 BEFORE INSERT OR UPDATE ON action_trigger.event_definition
172 FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL)
173 EXECUTE PROCEDURE action_trigger.check_valid_retention_interval();
175 CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$
177 * Deleting expired events without simultaneously deleting their outputs
178 * creates orphaned outputs. Deleting their outputs and all of the events
179 * linking back to them, plus any outputs those events link to is messy and
180 * inefficient. It's simpler to handle them in 2 sweeping steps.
182 * 1. Delete expired events.
183 * 2. Delete orphaned event outputs.
185 * This has the added benefit of removing outputs that may have been
186 * orphaned by some other process. Such outputs are not usuable by
189 * This does not guarantee that all events within an event group are
190 * purged at the same time. In such cases, the remaining events will
191 * be purged with the next instance of the purge (or soon thereafter).
192 * This is another nod toward efficiency over completeness of old
193 * data that's circling the bit bucket anyway.
197 DELETE FROM action_trigger.event WHERE id IN (
199 FROM action_trigger.event evt
200 JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
201 WHERE def.retention_interval IS NOT NULL
202 AND evt.state <> 'pending'
203 AND evt.update_time < (NOW() - def.retention_interval)
206 WITH linked_outputs AS (
207 SELECT templates.id AS id FROM (
208 SELECT DISTINCT(template_output) AS id
209 FROM action_trigger.event WHERE template_output IS NOT NULL
211 SELECT DISTINCT(error_output) AS id
212 FROM action_trigger.event WHERE error_output IS NOT NULL
214 SELECT DISTINCT(async_output) AS id
215 FROM action_trigger.event WHERE async_output IS NOT NULL
217 ) DELETE FROM action_trigger.event_output
218 WHERE id NOT IN (SELECT id FROM linked_outputs);
221 $_$ LANGUAGE PLPGSQL;
226 DROP FUNCTION IF EXISTS action_trigger.purge_events();
227 DROP TRIGGER IF EXISTS is_valid_retention_interval ON action_trigger.event_definition;
228 DROP FUNCTION IF EXISTS action_trigger.check_valid_retention_interval();
229 ALTER TABLE action_trigger.event_definition DROP COLUMN retention_interval;
235 SELECT evergreen.upgrade_deps_block_check('1044', :eg_version);
237 UPDATE action_trigger.hook SET passive = FALSE WHERE key IN (
240 'format.selfcheck.checkout',
241 'format.selfcheck.items_out',
242 'format.selfcheck.holds',
243 'format.selfcheck.fines',
244 'format.acqcle.html',
245 'format.acqinv.html',
255 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1045', :eg_version); -- csharp/berick/gmcharlt
257 ALTER TABLE action.transit_copy
258 ADD COLUMN cancel_time TIMESTAMPTZ;
260 -- change "abort" to "cancel" in stock perm descriptions
261 UPDATE permission.perm_list
262 SET description = 'Allow a user to cancel a copy transit if the user is at the transit destination or source'
263 WHERE code = 'ABORT_TRANSIT'
264 AND description = 'Allow a user to abort a copy transit if the user is at the transit destination or source';
265 UPDATE permission.perm_list
266 SET description = 'Allow a user to cancel a copy transit if the user is not at the transit source or dest'
267 WHERE code = 'ABORT_REMOTE_TRANSIT'
268 AND description = 'Allow a user to abort a copy transit if the user is not at the transit source or dest';
269 UPDATE permission.perm_list
270 SET description = 'Allows a user to cancel a transit on a copy with status of LOST'
271 WHERE code = 'ABORT_TRANSIT_ON_LOST'
272 AND description = 'Allows a user to abort a transit on a copy with status of LOST';
273 UPDATE permission.perm_list
274 SET description = 'Allows a user to cancel a transit on a copy with status of MISSING'
275 WHERE code = 'ABORT_TRANSIT_ON_MISSING'
276 AND description = 'Allows a user to abort a transit on a copy with status of MISSING';
278 SELECT evergreen.upgrade_deps_block_check('1046', :eg_version); -- phasefx/berick/gmcharlt
280 INSERT into config.org_unit_setting_type (
286 ) VALUES ( ----------------------------------------
287 'webstaff.format.dates'
290 'webstaff.format.dates'
291 ,'Format Dates with this pattern'
296 'webstaff.format.dates'
297 ,'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.'
302 ), ( ----------------------------------------
303 'webstaff.format.date_and_time'
306 'webstaff.format.date_and_time'
307 ,'Format Date+Time with this pattern'
312 'webstaff.format.date_and_time'
313 ,'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.'
321 config.org_unit_setting_type
323 label = 'Deprecated: ' || label -- FIXME: Is this okay?
325 name IN ('format.date','format.time')
329 SELECT evergreen.upgrade_deps_block_check('1047', :eg_version); -- gmcharlt/stompro
331 CREATE TABLE config.copy_tag_type (
332 code TEXT NOT NULL PRIMARY KEY,
334 owner INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
337 CREATE INDEX config_copy_tag_type_owner_idx
338 ON config.copy_tag_type (owner);
340 CREATE TABLE asset.copy_tag (
341 id SERIAL PRIMARY KEY,
342 tag_type TEXT REFERENCES config.copy_tag_type (code)
343 ON UPDATE CASCADE ON DELETE CASCADE,
346 index_vector tsvector NOT NULL,
348 pub BOOLEAN DEFAULT TRUE,
349 owner INTEGER NOT NULL REFERENCES actor.org_unit (id)
352 CREATE INDEX asset_copy_tag_label_idx
353 ON asset.copy_tag (label);
354 CREATE INDEX asset_copy_tag_label_lower_idx
355 ON asset.copy_tag (evergreen.lowercase(label));
356 CREATE INDEX asset_copy_tag_index_vector_idx
358 USING GIN(index_vector);
359 CREATE INDEX asset_copy_tag_tag_type_idx
360 ON asset.copy_tag (tag_type);
361 CREATE INDEX asset_copy_tag_owner_idx
362 ON asset.copy_tag (owner);
364 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
366 IF NEW.value IS NULL THEN
367 NEW.value = NEW.label;
372 $$ LANGUAGE 'plpgsql';
374 -- name of following trigger chosen to ensure it runs first
375 CREATE TRIGGER asset_copy_tag_do_value
376 BEFORE INSERT OR UPDATE ON asset.copy_tag
377 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
378 CREATE TRIGGER asset_copy_tag_fti_trigger
379 BEFORE UPDATE OR INSERT ON asset.copy_tag
380 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
382 CREATE TABLE asset.copy_tag_copy_map (
383 id BIGSERIAL PRIMARY KEY,
384 copy BIGINT REFERENCES asset.copy (id)
385 ON UPDATE CASCADE ON DELETE CASCADE,
386 tag INTEGER REFERENCES asset.copy_tag (id)
387 ON UPDATE CASCADE ON DELETE CASCADE
390 CREATE INDEX asset_copy_tag_copy_map_copy_idx
391 ON asset.copy_tag_copy_map (copy);
392 CREATE INDEX asset_copy_tag_copy_map_tag_idx
393 ON asset.copy_tag_copy_map (tag);
395 INSERT INTO config.copy_tag_type (code, label, owner) VALUES ('bookplate', 'Digital Bookplate', 1);
397 INSERT INTO permission.perm_list ( id, code, description ) VALUES
398 ( 590, 'ADMIN_COPY_TAG_TYPES', oils_i18n_gettext( 590,
399 'Administer copy tag types', 'ppl', 'description' )),
400 ( 591, 'ADMIN_COPY_TAG', oils_i18n_gettext( 591,
401 'Administer copy tag', 'ppl', 'description' ))
404 INSERT INTO config.org_unit_setting_type
405 (name, label, description, grp, datatype)
407 'opac.search.enable_bookplate_search',
409 'opac.search.enable_bookplate_search',
410 'Enable Digital Bookplate Search',
415 'opac.search.enable_bookplate_search',
416 'If enabled, adds a "Digital Bookplate" option to the query type selectors in the public catalog for search on copy tags.',
425 SELECT evergreen.upgrade_deps_block_check('1048', :eg_version);
427 INSERT into config.org_unit_setting_type (
433 ) VALUES ( ----------------------------------------
434 'webstaff.cat.label.font.family'
437 'webstaff.cat.label.font.family'
438 ,'Item Print Label Font Family'
443 'webstaff.cat.label.font.family'
444 ,'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"'
449 ), ( ----------------------------------------
450 'webstaff.cat.label.font.size'
453 'webstaff.cat.label.font.size'
454 ,'Item Print Label Font Size'
459 'webstaff.cat.label.font.size'
460 ,'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"'
465 ), ( ----------------------------------------
466 'webstaff.cat.label.font.weight'
469 'webstaff.cat.label.font.weight'
470 ,'Item Print Label Font Weight'
475 'webstaff.cat.label.font.weight'
476 ,'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"'
481 ), ( ----------------------------------------
482 'webstaff.cat.label.left_label.left_margin'
485 'webstaff.cat.label.left_label.left_margin'
486 ,'Item Print Label - Left Margin for Left Label'
491 'webstaff.cat.label.left_label.left_margin'
492 ,'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"'
497 ), ( ----------------------------------------
498 'webstaff.cat.label.right_label.left_margin'
501 'webstaff.cat.label.right_label.left_margin'
502 ,'Item Print Label - Left Margin for Right Label'
507 'webstaff.cat.label.right_label.left_margin'
508 ,'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"'
513 ), ( ----------------------------------------
514 'webstaff.cat.label.left_label.height'
517 'webstaff.cat.label.left_label.height'
518 ,'Item Print Label - Height for Left Label'
523 'webstaff.cat.label.left_label.height'
524 ,'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"'
529 ), ( ----------------------------------------
530 'webstaff.cat.label.left_label.width'
533 'webstaff.cat.label.left_label.width'
534 ,'Item Print Label - Width for Left Label'
539 'webstaff.cat.label.left_label.width'
540 ,'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"'
545 ), ( ----------------------------------------
546 'webstaff.cat.label.right_label.height'
549 'webstaff.cat.label.right_label.height'
550 ,'Item Print Label - Height for Right Label'
555 'webstaff.cat.label.right_label.height'
556 ,'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"'
561 ), ( ----------------------------------------
562 'webstaff.cat.label.right_label.width'
565 'webstaff.cat.label.right_label.width'
566 ,'Item Print Label - Width for Right Label'
571 'webstaff.cat.label.right_label.width'
572 ,'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"'
578 'webstaff.cat.label.inline_css'
581 'webstaff.cat.label.inline_css'
582 ,'Item Print Label - Inline CSS'
587 'webstaff.cat.label.inline_css'
588 ,'This setting allows you to inject arbitrary CSS into the item print label template. For example, ".printlabel { text-transform: uppercase; }"'
594 'webstaff.cat.label.call_number_wrap_filter_height'
597 'webstaff.cat.label.call_number_wrap_filter_height'
598 ,'Item Print Label - Call Number Wrap Filter Height'
603 'webstaff.cat.label.call_number_wrap_filter_height'
604 ,'This setting is used to set the default height (in number of lines) to use for call number wrapping in the left print label.'
610 'webstaff.cat.label.call_number_wrap_filter_width'
613 'webstaff.cat.label.call_number_wrap_filter_width'
614 ,'Item Print Label - Call Number Wrap Filter Width'
619 'webstaff.cat.label.call_number_wrap_filter_width'
620 ,'This setting is used to set the default width (in number of characters) to use for call number wrapping in the left print label.'
629 -- for testing, setting removal:
630 --DELETE FROM actor.org_unit_setting WHERE name IN (
631 -- 'webstaff.cat.label.font.family'
632 -- ,'webstaff.cat.label.font.size'
633 -- ,'webstaff.cat.label.font.weight'
634 -- ,'webstaff.cat.label.left_label.height'
635 -- ,'webstaff.cat.label.left_label.width'
636 -- ,'webstaff.cat.label.left_label.left_margin'
637 -- ,'webstaff.cat.label.right_label.height'
638 -- ,'webstaff.cat.label.right_label.width'
639 -- ,'webstaff.cat.label.right_label.left_margin'
640 -- ,'webstaff.cat.label.inline_css'
641 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
642 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
644 --DELETE FROM config.org_unit_setting_type_log WHERE field_name IN (
645 -- 'webstaff.cat.label.font.family'
646 -- ,'webstaff.cat.label.font.size'
647 -- ,'webstaff.cat.label.font.weight'
648 -- ,'webstaff.cat.label.left_label.height'
649 -- ,'webstaff.cat.label.left_label.width'
650 -- ,'webstaff.cat.label.left_label.left_margin'
651 -- ,'webstaff.cat.label.right_label.height'
652 -- ,'webstaff.cat.label.right_label.width'
653 -- ,'webstaff.cat.label.right_label.left_margin'
654 -- ,'webstaff.cat.label.inline_css'
655 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
656 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
658 --DELETE FROM config.org_unit_setting_type WHERE name IN (
659 -- 'webstaff.cat.label.font.family'
660 -- ,'webstaff.cat.label.font.size'
661 -- ,'webstaff.cat.label.font.weight'
662 -- ,'webstaff.cat.label.left_label.height'
663 -- ,'webstaff.cat.label.left_label.width'
664 -- ,'webstaff.cat.label.left_label.left_margin'
665 -- ,'webstaff.cat.label.right_label.height'
666 -- ,'webstaff.cat.label.right_label.width'
667 -- ,'webstaff.cat.label.right_label.left_margin'
668 -- ,'webstaff.cat.label.inline_css'
669 -- ,'webstaff.cat.label.call_number_wrap_filter_height'
670 -- ,'webstaff.cat.label.call_number_wrap_filter_width'
675 SELECT evergreen.upgrade_deps_block_check('1049', :eg_version); -- mmorgan/stompro/gmcharlt
677 \echo -----------------------------------------------------------
678 \echo Setting invalid age_protect and circ_as_type entries to NULL,
679 \echo otherwise they will break the Serial Copy Templates editor.
680 \echo Please review any Serial Copy Templates listed below.
682 UPDATE asset.copy_template act
683 SET age_protect = NULL
684 FROM actor.org_unit aou
685 WHERE aou.id=act.owning_lib
686 AND act.age_protect NOT IN
688 SELECT id FROM config.rule_age_hold_protect
690 RETURNING act.id "Template ID", act.name "Template Name",
691 aou.shortname "Owning Lib",
692 'Age Protection value reset to null.' "Description";
694 UPDATE asset.copy_template act
695 SET circ_as_type = NULL
696 FROM actor.org_unit aou
697 WHERE aou.id=act.owning_lib
698 AND act.circ_as_type NOT IN
700 SELECT code FROM config.item_type_map
702 RETURNING act.id "Template ID", act.name "Template Name",
703 aou.shortname "Owning Lib",
704 'Circ as Type value reset to null.' as "Description";
706 \echo -----------End Serial Template Fix----------------
708 SELECT evergreen.upgrade_deps_block_check('1050', :eg_version); -- mmorgan/cesardv/gmcharlt
710 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
711 SELECT DISTINCT ON (usr,perm) *
713 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
715 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
716 FROM permission.grp_perm_map p
718 SELECT (permission.grp_ancestors(
719 (SELECT profile FROM actor.usr WHERE id = $1)
724 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
725 FROM permission.grp_perm_map p
726 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
728 ORDER BY 2, 3, 4 ASC, 5 DESC ;
729 $$ LANGUAGE SQL STABLE ROWS 10;
731 SELECT evergreen.upgrade_deps_block_check('1051', :eg_version);
733 CREATE OR REPLACE VIEW action.all_circulation_slim AS
767 FROM action.circulation
802 FROM action.aged_circulation
805 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
806 DROP FUNCTION action.all_circ_chain(INTEGER);
808 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
809 RETURNS SETOF action.all_circulation_slim AS $$
811 tmp_circ action.all_circulation_slim%ROWTYPE;
812 circ_0 action.all_circulation_slim%ROWTYPE;
815 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
817 IF tmp_circ IS NULL THEN
818 RETURN NEXT tmp_circ;
822 -- find the front of the chain
824 SELECT INTO tmp_circ * FROM action.all_circulation_slim
825 WHERE id = tmp_circ.parent_circ;
826 IF tmp_circ IS NULL THEN
832 -- now send the circs to the caller, oldest to newest
835 IF tmp_circ IS NULL THEN
838 RETURN NEXT tmp_circ;
839 SELECT INTO tmp_circ * FROM action.all_circulation_slim
840 WHERE parent_circ = tmp_circ.id;
844 $$ LANGUAGE 'plpgsql';
846 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
847 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
851 -- first circ in the chain
852 circ_0 action.all_circulation_slim%ROWTYPE;
854 -- last circ in the chain
855 circ_n action.all_circulation_slim%ROWTYPE;
857 -- circ chain under construction
858 chain action.circ_chain_summary;
859 tmp_circ action.all_circulation_slim%ROWTYPE;
863 chain.num_circs := 0;
864 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
866 IF chain.num_circs = 0 THEN
870 chain.num_circs := chain.num_circs + 1;
874 chain.start_time := circ_0.xact_start;
875 chain.last_stop_fines := circ_n.stop_fines;
876 chain.last_stop_fines_time := circ_n.stop_fines_time;
877 chain.last_checkin_time := circ_n.checkin_time;
878 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
879 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
880 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
882 IF chain.num_circs > 1 THEN
883 chain.last_renewal_time := circ_n.xact_start;
884 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
890 $$ LANGUAGE 'plpgsql';
892 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
893 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
895 badge rating.badge_with_orgs%ROWTYPE;
898 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
900 PERFORM rating.precalc_bibs_by_copy(badge_id);
902 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
903 SELECT id FROM precalc_filter_bib_list
905 SELECT id FROM precalc_bibs_by_copy_list
908 ANALYZE precalc_copy_filter_bib_list;
912 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
913 FROM (SELECT cn.record AS bib,
915 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
916 SUM( -- time copy spent circulating
920 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
924 )::NUMERIC AS circ_time
926 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
927 JOIN asset.call_number cn ON (cn.id = cp.call_number)
928 LEFT JOIN action.all_circulation_slim circ ON (
929 circ.target_copy = cp.id
930 AND stop_fines NOT IN (
937 checkin_time IS NULL AND
938 stop_fines = 'MAXFINES'
941 WHERE cn.owning_lib = ANY (badge.orgs)
942 AND cp.active_date IS NOT NULL
943 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
944 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
949 $f$ LANGUAGE PLPGSQL STRICT;
955 SELECT evergreen.upgrade_deps_block_check('1052', :eg_version);
957 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
958 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
960 badge rating.badge_with_orgs%ROWTYPE;
963 iscale NUMERIC := NULL;
966 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
968 IF badge.horizon_age IS NULL THEN
969 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
974 PERFORM rating.precalc_bibs_by_copy(badge_id);
976 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
977 SELECT id FROM precalc_filter_bib_list
979 SELECT id FROM precalc_bibs_by_copy_list
982 ANALYZE precalc_copy_filter_bib_list;
984 iint := EXTRACT(EPOCH FROM badge.importance_interval);
985 IF badge.importance_age IS NOT NULL THEN
986 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
989 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
990 iscale := COALESCE(badge.importance_scale, 1.0);
994 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
996 SELECT cn.record AS bib,
997 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
998 COUNT(u.id)::INT AS uses
999 FROM action.in_house_use u
1000 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
1001 JOIN asset.copy cp ON (cp.id = u.item)
1002 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1003 WHERE u.use_time >= NOW() - badge.horizon_age
1004 AND cn.owning_lib = ANY (badge.orgs)
1009 $f$ LANGUAGE PLPGSQL STRICT;
1011 INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
1012 (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);
1016 SELECT evergreen.upgrade_deps_block_check('1053', :eg_version);
1018 CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
1019 RETURNS TABLE (record INT, value NUMERIC) AS $f$
1021 badge rating.badge_with_orgs%ROWTYPE;
1024 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1026 PERFORM rating.precalc_bibs_by_copy(badge_id);
1028 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1029 SELECT id FROM precalc_filter_bib_list
1031 SELECT id FROM precalc_bibs_by_copy_list
1033 ANALYZE precalc_copy_filter_bib_list;
1035 -- Use circ rather than owning lib here as that means "on the shelf at..."
1037 SELECT f.id::INT AS bib,
1038 COUNT(DISTINCT cp.circ_lib)::NUMERIC
1040 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
1041 WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;
1044 $f$ LANGUAGE PLPGSQL STRICT;
1046 INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES
1047 (17,'Circulation Library Count', 'rating.org_unit_count', TRUE);
1051 SELECT evergreen.upgrade_deps_block_check('1054', :eg_version);
1053 INSERT into config.org_unit_setting_type
1054 ( name, grp, label, description, datatype ) VALUES
1056 ( 'lib.timezone', 'lib',
1057 oils_i18n_gettext('lib.timezone',
1058 'Library time zone',
1060 oils_i18n_gettext('lib.timezone',
1061 'Define the time zone in which a library physically resides',
1062 'coust', 'description'),
1065 ALTER TABLE actor.org_unit_closed ADD COLUMN full_day BOOLEAN DEFAULT FALSE;
1066 ALTER TABLE actor.org_unit_closed ADD COLUMN multi_day BOOLEAN DEFAULT FALSE;
1068 UPDATE actor.org_unit_closed SET multi_day = TRUE
1069 WHERE close_start::DATE <> close_end::DATE;
1071 UPDATE actor.org_unit_closed SET full_day = TRUE
1072 WHERE close_start::DATE = close_end::DATE
1073 AND SUBSTRING(close_start::time::text FROM 1 FOR 8) = '00:00:00'
1074 AND SUBSTRING(close_end::time::text FROM 1 FOR 8) = '23:59:59';
1076 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
1078 proper_tz TEXT := COALESCE(
1081 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
1084 CURRENT_SETTING('timezone')
1088 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
1089 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
1090 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
1095 $$ LANGUAGE PLPGSQL;
1098 \qecho The following query will adjust all historical, unaged circulations so
1099 \qecho that if their due date field is pushed to the end of the day, it is done
1100 \qecho in the circulating library''''s time zone, and not the server time zone.
1102 \qecho It is safe to run this after any change to library time zones.
1104 \qecho Running this is not required, as no code before this change has
1105 \qecho depended on the time string of '''23:59:59'''. It is also not necessary
1106 \qecho if all of your libraries are in the same time zone, and that time zone
1107 \qecho is the same as the database''''s configured time zone.
1111 \qecho ' new_tz text;'
1112 \qecho ' ou_id int;'
1114 \qecho ' for ou_id in select id from actor.org_unit loop'
1115 \qecho ' for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('''lib.timezone''',ou_id) loop'
1116 \qecho ' if new_tz is not null then'
1117 \qecho ' update action.circulation'
1118 \qecho ' set due_date = (due_date::timestamp || ''' ''' || new_tz)::timestamptz'
1119 \qecho ' where circ_lib = ou_id'
1120 \qecho ' and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '''23:59:59''';'
1128 SELECT evergreen.upgrade_deps_block_check('1055', :eg_version);
1130 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 $$
1134 picklist_row RECORD;
1139 -- do some initial cleanup
1140 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
1141 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
1142 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
1146 DELETE FROM actor.card where usr = src_usr;
1148 IF deactivate_cards THEN
1149 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
1151 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
1156 DELETE FROM actor.usr_address WHERE usr = src_usr;
1158 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
1161 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
1162 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
1163 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
1164 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
1165 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
1168 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
1169 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
1170 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
1171 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
1176 -- For each *_bucket table: transfer every bucket belonging to src_usr
1177 -- into the custody of dest_usr.
1179 -- In order to avoid colliding with an existing bucket owned by
1180 -- the destination user, append the source user's id (in parenthesese)
1181 -- to the name. If you still get a collision, add successive
1182 -- spaces to the name and keep trying until you succeed.
1186 FROM container.biblio_record_entry_bucket
1187 WHERE owner = src_usr
1189 suffix := ' (' || src_usr || ')';
1192 UPDATE container.biblio_record_entry_bucket
1193 SET owner = dest_usr, name = name || suffix
1194 WHERE id = bucket_row.id;
1195 EXCEPTION WHEN unique_violation THEN
1196 suffix := suffix || ' ';
1205 FROM container.call_number_bucket
1206 WHERE owner = src_usr
1208 suffix := ' (' || src_usr || ')';
1211 UPDATE container.call_number_bucket
1212 SET owner = dest_usr, name = name || suffix
1213 WHERE id = bucket_row.id;
1214 EXCEPTION WHEN unique_violation THEN
1215 suffix := suffix || ' ';
1224 FROM container.copy_bucket
1225 WHERE owner = src_usr
1227 suffix := ' (' || src_usr || ')';
1230 UPDATE container.copy_bucket
1231 SET owner = dest_usr, name = name || suffix
1232 WHERE id = bucket_row.id;
1233 EXCEPTION WHEN unique_violation THEN
1234 suffix := suffix || ' ';
1243 FROM container.user_bucket
1244 WHERE owner = src_usr
1246 suffix := ' (' || src_usr || ')';
1249 UPDATE container.user_bucket
1250 SET owner = dest_usr, name = name || suffix
1251 WHERE id = bucket_row.id;
1252 EXCEPTION WHEN unique_violation THEN
1253 suffix := suffix || ' ';
1260 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
1263 -- transfer queues the same way we transfer buckets (see above)
1267 WHERE owner = src_usr
1269 suffix := ' (' || src_usr || ')';
1272 UPDATE vandelay.queue
1273 SET owner = dest_usr, name = name || suffix
1274 WHERE id = queue_row.id;
1275 EXCEPTION WHEN unique_violation THEN
1276 suffix := suffix || ' ';
1284 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
1285 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
1286 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
1287 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
1288 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
1291 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
1292 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
1293 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
1294 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
1296 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
1297 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
1298 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
1299 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
1301 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
1302 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
1303 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
1304 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
1305 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
1308 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
1309 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
1311 -- transfer picklists the same way we transfer buckets (see above)
1315 WHERE owner = src_usr
1317 suffix := ' (' || src_usr || ')';
1321 SET owner = dest_usr, name = name || suffix
1322 WHERE id = picklist_row.id;
1323 EXCEPTION WHEN unique_violation THEN
1324 suffix := suffix || ' ';
1331 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
1332 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
1333 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
1334 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
1335 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
1336 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
1337 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
1338 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
1341 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
1342 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
1343 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
1344 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
1345 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
1346 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
1349 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
1350 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
1353 -- It's not uncommon to define the reporter schema in a replica
1354 -- DB only, so don't assume these tables exist in the write DB.
1356 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
1357 EXCEPTION WHEN undefined_table THEN
1361 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
1362 EXCEPTION WHEN undefined_table THEN
1366 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
1367 EXCEPTION WHEN undefined_table THEN
1371 -- transfer folders the same way we transfer buckets (see above)
1374 FROM reporter.template_folder
1375 WHERE owner = src_usr
1377 suffix := ' (' || src_usr || ')';
1380 UPDATE reporter.template_folder
1381 SET owner = dest_usr, name = name || suffix
1382 WHERE id = folder_row.id;
1383 EXCEPTION WHEN unique_violation THEN
1384 suffix := suffix || ' ';
1390 EXCEPTION WHEN undefined_table THEN
1394 -- transfer folders the same way we transfer buckets (see above)
1397 FROM reporter.report_folder
1398 WHERE owner = src_usr
1400 suffix := ' (' || src_usr || ')';
1403 UPDATE reporter.report_folder
1404 SET owner = dest_usr, name = name || suffix
1405 WHERE id = folder_row.id;
1406 EXCEPTION WHEN unique_violation THEN
1407 suffix := suffix || ' ';
1413 EXCEPTION WHEN undefined_table THEN
1417 -- transfer folders the same way we transfer buckets (see above)
1420 FROM reporter.output_folder
1421 WHERE owner = src_usr
1423 suffix := ' (' || src_usr || ')';
1426 UPDATE reporter.output_folder
1427 SET owner = dest_usr, name = name || suffix
1428 WHERE id = folder_row.id;
1429 EXCEPTION WHEN unique_violation THEN
1430 suffix := suffix || ' ';
1436 EXCEPTION WHEN undefined_table THEN
1440 -- Finally, delete the source user
1441 DELETE FROM actor.usr WHERE id = src_usr;
1444 $$ LANGUAGE plpgsql;
1450 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1056', :eg_version); -- miker/gmcharlt
1452 INSERT INTO permission.perm_list (id,code,description) VALUES (592,'CONTAINER_BATCH_UPDATE','Allow batch update via buckets');
1454 INSERT INTO container.user_bucket_type (code,label) SELECT code,label FROM container.copy_bucket_type where code = 'staff_client';
1456 CREATE TABLE action.fieldset_group (
1457 id SERIAL PRIMARY KEY,
1459 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1460 complete_time TIMESTAMPTZ,
1461 container INT, -- Points to a container of some type ...
1462 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
1463 can_rollback BOOL DEFAULT TRUE,
1464 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1465 rollback_time TIMESTAMPTZ,
1466 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1467 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1470 ALTER TABLE action.fieldset ADD COLUMN fieldset_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1471 ALTER TABLE action.fieldset ADD COLUMN error_msg TEXT;
1472 ALTER TABLE container.biblio_record_entry_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1473 ALTER TABLE container.user_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1474 ALTER TABLE container.call_number_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1475 ALTER TABLE container.copy_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1477 UPDATE query.stored_query SET id = id + 1000 WHERE id < 1000;
1478 UPDATE query.from_relation SET id = id + 1000 WHERE id < 1000;
1479 UPDATE query.expression SET id = id + 1000 WHERE id < 1000;
1481 SELECT SETVAL('query.stored_query_id_seq', 1, FALSE);
1482 SELECT SETVAL('query.from_relation_id_seq', 1, FALSE);
1483 SELECT SETVAL('query.expression_id_seq', 1, FALSE);
1485 INSERT INTO query.bind_variable (name,type,description,label)
1486 SELECT 'bucket','number','ID of the bucket to pull items from','Bucket ID'
1487 WHERE NOT EXISTS (SELECT 1 FROM query.bind_variable WHERE name = 'bucket');
1489 -- Assumes completely empty 'query' schema
1490 INSERT INTO query.stored_query (type, use_distinct) VALUES ('SELECT', TRUE); -- 1
1492 INSERT INTO query.from_relation (type, table_name, class_name, table_alias) VALUES ('RELATION', 'container.user_bucket_item', 'cubi', 'cubi'); -- 1
1493 UPDATE query.stored_query SET from_clause = 1;
1495 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'target_user'); -- 1
1496 INSERT INTO query.select_item (stored_query,seq_no,expression) VALUES (1,1,1);
1498 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'bucket'); -- 2
1499 INSERT INTO query.expr_xbind (bind_variable) VALUES ('bucket'); -- 3
1501 INSERT INTO query.expr_xop (left_operand, operator, right_operand) VALUES (2, '=', 3); -- 4
1502 UPDATE query.stored_query SET where_clause = 4;
1504 SELECT SETVAL('query.stored_query_id_seq', 1000, TRUE) FROM query.stored_query;
1505 SELECT SETVAL('query.from_relation_id_seq', 1000, TRUE) FROM query.from_relation;
1506 SELECT SETVAL('query.expression_id_seq', 10000, TRUE) FROM query.expression;
1508 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1509 fieldset_id IN INT, -- id from action.fieldset
1510 table_name IN TEXT, -- table to be updated
1511 pkey_name IN TEXT, -- name of primary key column in that table
1512 query IN TEXT -- query constructed by qstore (for query-based
1513 -- fieldsets only; otherwise null
1529 fs_obj action.fieldset%ROWTYPE;
1530 fs_group action.fieldset_group%ROWTYPE;
1534 IF fieldset_id IS NULL THEN
1535 RETURN 'Fieldset ID parameter is NULL';
1537 IF table_name IS NULL THEN
1538 RETURN 'Table name parameter is NULL';
1540 IF pkey_name IS NULL THEN
1541 RETURN 'Primary key name parameter is NULL';
1546 quote_literal( pkey_value )
1556 -- Build the WHERE clause. This differs according to whether it's a
1557 -- single-row fieldset or a query-based fieldset.
1559 IF query IS NULL AND fs_pkey_value IS NULL THEN
1560 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1561 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1562 fs_query := rtrim( query, ';' );
1563 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1564 || fs_query || ' )';
1565 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1566 where_clause := 'WHERE ' || pkey_name || ' = ';
1567 IF pkey_name = 'id' THEN
1568 where_clause := where_clause || fs_pkey_value;
1569 ELSIF pkey_name = 'code' THEN
1570 where_clause := where_clause || quote_literal(fs_pkey_value);
1572 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1574 ELSE -- both are not null
1575 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1578 IF fs_status IS NULL THEN
1579 RETURN 'No fieldset found for id = ' || fieldset_id;
1580 ELSIF fs_status = 'APPLIED' THEN
1581 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1584 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1585 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1587 IF fs_group.can_rollback THEN
1588 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1590 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1591 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1593 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1595 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1596 IF pkey_name = 'id' THEN
1597 fs_pkey_value := rb_row.id;
1598 ELSIF pkey_name = 'code' THEN
1599 fs_pkey_value := rb_row.code;
1601 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1603 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1604 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);
1606 fs_id := CURRVAL('action.fieldset_id_seq');
1610 FROM action.fieldset_col_val
1611 WHERE fieldset = fieldset_id
1613 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1614 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1619 statement := 'UPDATE ' || table_name || ' SET';
1625 FROM action.fieldset_col_val
1626 WHERE fieldset = fieldset_id
1628 statement := statement || sep || ' ' || cv.col
1629 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1634 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1636 statement := statement || ' ' || where_clause;
1639 -- Execute the update
1643 GET DIAGNOSTICS update_count = ROW_COUNT;
1645 IF update_count = 0 THEN
1646 RAISE data_exception;
1649 IF fsg_id IS NOT NULL THEN
1650 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1653 IF fs_group.id IS NOT NULL THEN
1654 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1657 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1659 EXCEPTION WHEN data_exception THEN
1660 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1661 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1668 EXCEPTION WHEN OTHERS THEN
1669 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1670 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1674 $$ LANGUAGE plpgsql;
1678 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1057', :eg_version); -- miker/gmcharlt/kmlussier
1680 -- Thist change drops a needless join and saves 10-15% in time cost
1681 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
1683 SELECT id, value, count
1685 SELECT mfae.field AS id,
1687 COUNT(DISTINCT mfae.source),
1689 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
1691 FROM metabib.facet_entry mfae
1692 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
1693 WHERE mfae.source = ANY ($2)
1695 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
1700 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
1706 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$
1708 layout unapi.bre_output_layout%ROWTYPE;
1709 transform config.xml_transform%ROWTYPE;
1712 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
1714 element_list TEXT[];
1717 IF org = '-' OR org IS NULL THEN
1718 SELECT shortname INTO org FROM evergreen.org_top();
1721 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1722 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1724 IF layout.name IS NULL THEN
1728 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
1729 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
1731 -- Gather the bib xml
1732 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
1734 IF layout.title_element IS NOT NULL THEN
1735 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
1738 IF layout.description_element IS NOT NULL THEN
1739 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
1742 IF layout.creator_element IS NOT NULL THEN
1743 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
1746 IF layout.update_ts_element IS NOT NULL THEN
1747 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;
1750 IF unapi_url IS NOT NULL THEN
1751 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;
1754 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
1756 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
1757 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
1758 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
1761 RETURN tmp_xml::XML;
1763 $F$ LANGUAGE PLPGSQL STABLE;
1765 CREATE TABLE asset.copy_vis_attr_cache (
1766 id BIGSERIAL PRIMARY KEY,
1767 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
1768 target_copy BIGINT NOT NULL,
1769 vis_attr_vector INT[]
1771 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
1772 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
1774 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
1776 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
1779 WHEN 'luri_org' THEN 0 -- "b" attr
1780 WHEN 'bib_source' THEN 1 -- "b" attr
1782 WHEN 'copy_flags' THEN 0 -- "c" attr
1783 WHEN 'owning_lib' THEN 1 -- "c" attr
1784 WHEN 'circ_lib' THEN 2 -- "c" attr
1785 WHEN 'status' THEN 3 -- "c" attr
1786 WHEN 'location' THEN 4 -- "c" attr
1787 WHEN 'location_group' THEN 5 -- "c" attr
1791 /* copy_flags bit positions, LSB-first:
1793 0: asset.copy.opac_visible
1796 When adding flags, you must update asset.all_visible_flags()
1798 Because bib and copy values are stored separately, we can reuse
1799 shifts, saving us some space. We could probably take back a bit
1800 too, but I'm not sure its worth squeezing that last one out. We'd
1801 be left with just 2 slots for copy attrs, rather than 10.
1804 $f$ LANGUAGE SQL IMMUTABLE;
1806 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
1807 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
1808 $f$ LANGUAGE SQL IMMUTABLE;
1810 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
1811 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
1812 $f$ LANGUAGE SQL IMMUTABLE;
1814 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
1816 copy_row asset.copy%ROWTYPE;
1817 lgroup_map asset.copy_location_group_map%ROWTYPE;
1820 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
1822 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
1823 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
1824 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
1825 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
1827 SELECT ARRAY_APPEND(
1829 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
1831 FROM asset.call_number
1832 WHERE id = copy_row.call_number;
1834 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
1835 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
1840 $f$ LANGUAGE PLPGSQL;
1842 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
1844 bib_row biblio.record_entry%ROWTYPE;
1845 cn_row asset.call_number%ROWTYPE;
1848 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
1850 IF bib_row.source IS NOT NULL THEN
1851 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
1856 FROM asset.call_number cn
1857 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
1858 JOIN asset.uri u ON (u.id = m.uri)
1859 WHERE cn.record = bib_id
1860 AND cn.label = '##URI##'
1863 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
1868 $f$ LANGUAGE PLPGSQL;
1871 SELECT evergreen.upgrade_deps_block_check('1076', :eg_version); -- miker/gmcharlt
1873 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
1875 ocn asset.call_number%ROWTYPE;
1876 ncn asset.call_number%ROWTYPE;
1880 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
1881 IF TG_OP = 'INSERT' THEN
1882 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1885 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
1889 ELSIF TG_OP = 'DELETE' THEN
1890 DELETE FROM asset.copy_vis_attr_cache
1891 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
1897 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
1898 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
1899 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1900 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1903 asset.calculate_copy_visibility_attribute_set(NEW.id)
1905 ELSIF TG_TABLE_NAME = 'record_entry' THEN
1906 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1912 -- handle items first, since with circulation activity
1913 -- their statuses change frequently
1914 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
1916 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1917 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1921 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1923 IF OLD.deleted <> NEW.deleted THEN
1925 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1927 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1930 asset.calculate_copy_visibility_attribute_set(NEW.id)
1935 ELSIF OLD.call_number <> NEW.call_number THEN
1936 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
1938 IF ncn.record <> ocn.record THEN
1939 UPDATE biblio.record_entry
1940 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
1941 WHERE id = ocn.record;
1943 -- We have to use a record-specific WHERE clause
1944 -- to avoid modifying the entries for peer-bib copies.
1945 UPDATE asset.copy_vis_attr_cache
1946 SET target_copy = NEW.id,
1948 WHERE target_copy = OLD.id
1949 AND record = ocn.record;
1953 IF OLD.location <> NEW.location OR
1954 OLD.status <> NEW.status OR
1955 OLD.opac_visible <> NEW.opac_visible OR
1956 OLD.circ_lib <> NEW.circ_lib
1958 -- Any of these could change visibility, but
1959 -- we'll save some queries and not try to calculate
1960 -- the change directly. We want to update peer-bib
1961 -- entries in this case, unlike above.
1962 UPDATE asset.copy_vis_attr_cache
1963 SET target_copy = NEW.id,
1964 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
1965 WHERE target_copy = OLD.id;
1969 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
1971 IF OLD.record <> NEW.record THEN
1972 IF NEW.label = '##URI##' THEN
1973 UPDATE biblio.record_entry
1974 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1975 WHERE id = OLD.record;
1977 UPDATE biblio.record_entry
1978 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
1979 WHERE id = NEW.record;
1982 UPDATE asset.copy_vis_attr_cache
1983 SET record = NEW.record,
1984 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1985 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1986 AND record = OLD.record;
1988 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
1989 UPDATE asset.copy_vis_attr_cache
1990 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1991 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1992 AND record = NEW.record;
1994 IF NEW.label = '##URI##' THEN
1995 UPDATE biblio.record_entry
1996 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1997 WHERE id = OLD.record;
2001 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
2003 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
2004 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
2006 ELSIF OLD.source <> NEW.source THEN
2007 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
2014 $func$ LANGUAGE PLPGSQL;
2017 -- Helper functions for use in constructing searches --
2019 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
2020 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
2021 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
2022 $f$ LANGUAGE SQL STABLE;
2024 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
2025 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
2028 $f$ LANGUAGE SQL STABLE;
2030 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
2031 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
2033 WHERE NOT opac_visible;
2034 $f$ LANGUAGE SQL STABLE;
2036 -- Bib-oriented defaults for search
2037 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
2038 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
2039 FROM config.bib_source
2041 $f$ LANGUAGE SQL IMMUTABLE;
2043 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
2044 SELECT * FROM asset.invisible_orgs('luri_org');
2045 $f$ LANGUAGE SQL STABLE;
2047 -- Copy-oriented defaults for search
2048 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
2049 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
2050 FROM asset.copy_location_group
2051 WHERE NOT opac_visible;
2052 $f$ LANGUAGE SQL STABLE;
2054 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
2055 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
2056 FROM asset.copy_location
2057 WHERE NOT opac_visible;
2058 $f$ LANGUAGE SQL STABLE;
2060 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
2061 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
2062 FROM config.copy_status
2063 WHERE NOT opac_visible;
2064 $f$ LANGUAGE SQL STABLE;
2066 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
2067 SELECT * FROM asset.invisible_orgs('owning_lib');
2068 $f$ LANGUAGE SQL STABLE;
2070 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
2071 SELECT * FROM asset.invisible_orgs('circ_lib');
2072 $f$ LANGUAGE SQL STABLE;
2074 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
2076 copy_flags TEXT; -- "c" attr
2078 owning_lib TEXT; -- "c" attr
2079 circ_lib TEXT; -- "c" attr
2080 status TEXT; -- "c" attr
2081 location TEXT; -- "c" attr
2082 location_group TEXT; -- "c" attr
2084 luri_org TEXT; -- "b" attr
2085 bib_sources TEXT; -- "b" attr
2087 copy_flags := asset.all_visible_flags(); -- Will always have at least one
2089 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
2091 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
2092 status := NULLIF(asset.status_default(),'!()');
2093 location := NULLIF(asset.location_default(),'!()');
2094 location_group := NULLIF(asset.location_group_default(),'!()');
2096 luri_org := NULLIF(asset.luri_org_default(),'!()');
2097 bib_sources := NULLIF(asset.bib_source_default(),'()');
2100 '('||ARRAY_TO_STRING(
2101 ARRAY[luri_org,bib_sources],
2104 '('||ARRAY_TO_STRING(
2105 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
2109 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
2111 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)
2112 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
2115 prepared_query_texts TEXT[];
2117 plain_query TSQUERY;
2118 opac_visibility_join TEXT;
2119 search_class_join TEXT;
2122 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
2124 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
2125 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
2127 visibility_org := NULLIF(visibility_org,-1);
2128 IF visibility_org IS NOT NULL THEN
2129 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
2131 opac_visibility_join := '';
2133 opac_visibility_join := '
2134 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
2135 JOIN vm ON (acvac.vis_attr_vector @@
2136 (vm.c_attrs || $$&$$ ||
2137 search.calculate_visibility_attribute_test(
2139 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
2146 opac_visibility_join := '';
2149 -- The following determines whether we only provide suggestsons matching
2150 -- the user's selected search_class, or whether we show other suggestions
2151 -- too. The reason for MIN() is that for search_classes like
2152 -- 'title|proper|uniform' you would otherwise get multiple rows. The
2153 -- implication is that if title as a class doesn't have restrict,
2154 -- nor does the proper field, but the uniform field does, you're going
2155 -- to get 'false' for your overall evaluation of 'should we restrict?'
2156 -- To invert that, change from MIN() to MAX().
2160 MIN(cmc.restrict::INT) AS restrict_class,
2161 MIN(cmf.restrict::INT) AS restrict_field
2162 FROM metabib.search_class_to_registered_components(search_class)
2163 AS _registered (field_class TEXT, field INT)
2165 config.metabib_class cmc ON (cmc.name = _registered.field_class)
2167 config.metabib_field cmf ON (cmf.id = _registered.field);
2169 -- evaluate 'should we restrict?'
2170 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
2171 search_class_join := '
2173 metabib.search_class_to_registered_components($2)
2174 AS _registered (field_class TEXT, field INT) ON (
2175 (_registered.field IS NULL AND
2176 _registered.field_class = cmf.field_class) OR
2177 (_registered.field = cmf.id)
2181 search_class_join := '
2183 metabib.search_class_to_registered_components($2)
2184 AS _registered (field_class TEXT, field INT) ON (
2185 _registered.field_class = cmc.name
2190 RETURN QUERY EXECUTE '
2191 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
2192 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
2201 TS_HEADLINE(value, $7, $3)
2202 FROM (SELECT DISTINCT
2205 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
2206 _registered.field = cmf.id AS restrict,
2208 TS_RANK_CD(mbe.index_vector, $1, $6),
2211 FROM metabib.browse_entry_def_map mbedm
2212 JOIN mbe ON (mbe.id = mbedm.entry)
2213 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
2214 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
2215 ' || search_class_join || '
2216 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2218 ' || opac_visibility_join || '
2219 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2221 ' -- sic, repeat the order by clause in the outer select too
2223 query, search_class, headline_opts,
2224 visibility_org, query_limit, normalization, plain_query
2228 -- buoyant AND chosen class = match class
2229 -- chosen field = match field
2236 $f$ LANGUAGE plpgsql ROWS 10;
2238 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)
2239 RETURNS SETOF metabib.flat_browse_entry_appearance
2245 pivot_sort_value TEXT;
2246 pivot_sort_fallback TEXT;
2247 context_locations INT[];
2248 browse_superpage_size INT;
2249 results_skipped INT := 0;
2253 forward_to_pivot INT;
2255 -- First, find the pivot if we were given a browse term but not a pivot.
2256 IF pivot_id IS NULL THEN
2257 pivot_id := metabib.browse_pivot(search_field, browse_term);
2260 SELECT INTO pivot_sort_value, pivot_sort_fallback
2261 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
2263 -- Bail if we couldn't find a pivot.
2264 IF pivot_sort_value IS NULL THEN
2268 -- Transform the context_loc_group argument (if any) (logc at the
2269 -- TPAC layer) into a form we'll be able to use.
2270 IF context_loc_group IS NOT NULL THEN
2271 SELECT INTO context_locations ARRAY_AGG(location)
2272 FROM asset.copy_location_group_map
2273 WHERE lgroup = context_loc_group;
2276 -- Get the configured size of browse superpages.
2277 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
2278 FROM config.global_flag
2279 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
2281 -- First we're going to search backward from the pivot, then we're going
2282 -- to search forward. In each direction, we need two limits. At the
2283 -- lesser of the two limits, we delineate the edge of the result set
2284 -- we're going to return. At the greater of the two limits, we find the
2285 -- pivot value that would represent an offset from the current pivot
2286 -- at a distance of one "page" in either direction, where a "page" is a
2287 -- result set of the size specified in the "result_limit" argument.
2289 -- The two limits in each direction make four derived values in total,
2290 -- and we calculate them now.
2291 back_limit := CEIL(result_limit::FLOAT / 2);
2292 back_to_pivot := result_limit;
2293 forward_limit := result_limit / 2;
2294 forward_to_pivot := result_limit - 1;
2296 -- This is the meat of the SQL query that finds browse entries. We'll
2297 -- pass this to a function which uses it with a cursor, so that individual
2298 -- rows may be fetched in a loop until some condition is satisfied, without
2299 -- waiting for a result set of fixed size to be collected all at once.
2304 FROM metabib.browse_entry mbe
2306 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2308 FROM metabib.browse_entry_def_map mbedm
2309 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2310 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2312 FROM metabib.browse_entry_simple_heading_map mbeshm
2313 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2314 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2315 ash.atag = map.authority_field
2316 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2318 WHERE mbeshm.entry = mbe.id
2322 -- This is the variant of the query for browsing backward.
2323 back_query := core_query ||
2324 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2325 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
2327 -- This variant browses forward.
2328 forward_query := core_query ||
2329 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2330 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
2332 -- We now call the function which applies a cursor to the provided
2333 -- queries, stopping at the appropriate limits and also giving us
2334 -- the next page's pivot.
2336 SELECT * FROM metabib.staged_browse(
2337 back_query, search_field, context_org, context_locations,
2338 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2340 SELECT * FROM metabib.staged_browse(
2341 forward_query, search_field, context_org, context_locations,
2342 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2343 ) ORDER BY row_number DESC;
2346 $f$ LANGUAGE plpgsql ROWS 10;
2348 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)
2349 RETURNS SETOF metabib.flat_browse_entry_appearance
2358 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2359 results_skipped INT := 0;
2360 row_counter INT := 0;
2365 all_records BIGINT[];
2366 all_brecords BIGINT[];
2367 all_arecords BIGINT[];
2368 superpage_of_records BIGINT[];
2374 IF count_up_from_zero THEN
2381 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
2384 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2385 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
2387 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
2389 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
2390 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
2392 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
2394 b_tests := b_tests || search.calculate_visibility_attribute_test(
2396 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
2399 b_tests := b_tests || search.calculate_visibility_attribute_test(
2401 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
2405 IF context_locations THEN
2406 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2407 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
2410 OPEN curs NO SCROLL FOR EXECUTE query;
2413 FETCH curs INTO rec;
2415 IF result_row.pivot_point IS NOT NULL THEN
2416 RETURN NEXT result_row;
2421 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2422 SELECT INTO all_arecords, result_row.sees, afields
2423 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2424 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2425 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2427 FROM metabib.browse_entry_simple_heading_map mbeshm
2428 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2429 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2430 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2431 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2432 ash.atag = map.authority_field
2433 AND map.metabib_field = ANY(fields)
2435 WHERE mbeshm.entry = rec.id;
2437 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2438 SELECT INTO all_brecords, result_row.authorities, bfields
2439 ARRAY_AGG(DISTINCT source),
2440 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2441 ARRAY_AGG(DISTINCT def)
2442 FROM metabib.browse_entry_def_map
2443 WHERE entry = rec.id
2444 AND def = ANY(fields);
2446 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2448 result_row.sources := 0;
2449 result_row.asources := 0;
2451 -- Bib-linked vis checking
2452 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2454 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
2455 FROM biblio.record_entry b
2456 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2457 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
2459 acvac.vis_attr_vector @@ c_tests::query_int
2460 OR b.vis_attr_vector @@ b_tests::query_int
2463 result_row.accurate := TRUE;
2467 -- Authority-linked vis checking
2468 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2470 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
2471 FROM biblio.record_entry b
2472 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2473 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
2475 acvac.vis_attr_vector @@ c_tests::query_int
2476 OR b.vis_attr_vector @@ b_tests::query_int
2479 result_row.aaccurate := TRUE;
2483 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2485 -- The function that calls this function needs row_number in order
2486 -- to correctly order results from two different runs of this
2488 result_row.row_number := row_number;
2490 -- Now, if row_counter is still less than limit, return a row. If
2491 -- not, but it is less than next_pivot_pos, continue on without
2492 -- returning actual result rows until we find
2493 -- that next pivot, and return it.
2495 IF row_counter < result_limit THEN
2496 result_row.browse_entry := rec.id;
2497 result_row.value := rec.value;
2499 RETURN NEXT result_row;
2501 result_row.browse_entry := NULL;
2502 result_row.authorities := NULL;
2503 result_row.fields := NULL;
2504 result_row.value := NULL;
2505 result_row.sources := NULL;
2506 result_row.sees := NULL;
2507 result_row.accurate := NULL;
2508 result_row.aaccurate := NULL;
2509 result_row.pivot_point := rec.id;
2511 IF row_counter >= next_pivot_pos THEN
2512 RETURN NEXT result_row;
2517 IF count_up_from_zero THEN
2518 row_number := row_number + 1;
2520 row_number := row_number - 1;
2523 -- row_counter is different from row_number.
2524 -- It simply counts up from zero so that we know when
2525 -- we've reached our limit.
2526 row_counter := row_counter + 1;
2530 $f$ LANGUAGE plpgsql ROWS 10;
2532 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
2533 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
2534 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
2535 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
2536 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
2537 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
2538 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
2539 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
2541 -- Upgrade the data!
2542 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
2545 asset.calculate_copy_visibility_attribute_set(cp.id)
2547 JOIN asset.call_number cn ON (cp.call_number = cn.id);
2549 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
2551 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();
2552 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();
2553 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2554 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2555 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2556 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2557 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2559 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$
2564 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;
2566 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
2568 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2569 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2570 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2574 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2580 asset.copy_vis_attr_cache av
2581 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2582 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2586 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2593 $f$ LANGUAGE PLPGSQL;
2595 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$
2600 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;
2602 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2604 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2605 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2606 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2610 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2615 asset.copy_vis_attr_cache av
2616 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2617 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2621 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2628 $f$ LANGUAGE PLPGSQL;
2630 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$
2635 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;
2637 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
2639 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2640 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2641 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2645 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2651 asset.copy_vis_attr_cache av
2652 JOIN asset.copy cp ON (cp.id = av.target_copy)
2653 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2654 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2658 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2665 $f$ LANGUAGE PLPGSQL;
2667 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$
2672 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;
2674 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2676 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2677 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2678 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2682 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2688 asset.copy_vis_attr_cache av
2689 JOIN asset.copy cp ON (cp.id = av.target_copy)
2690 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2691 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2695 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2702 $f$ LANGUAGE PLPGSQL;
2704 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
2710 depth INT DEFAULT NULL,
2711 slimit HSTORE DEFAULT NULL,
2712 soffset HSTORE DEFAULT NULL,
2713 include_xmlns BOOL DEFAULT TRUE,
2714 pref_lib INT DEFAULT NULL
2715 ) RETURNS XML AS $F$
2719 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
2720 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
2722 (SELECT XMLAGG(foo.y)
2724 WITH sourcelist AS (
2725 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
2726 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2727 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
2728 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
2730 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
2731 WHERE mmsm.metarecord = $1 AND (
2734 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
2735 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2736 AND acvac.record = mmsm.source
2738 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
2739 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)
2747 cmra.value AS "coded-value",
2748 cmra.aid AS "cvmid",
2758 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
2760 SELECT v.source AS id,
2764 FROM metabib.record_attr_vector_list v
2765 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
2767 JOIN sourcelist ON (x.id = sourcelist.source)
2770 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
2785 SELECT DISTINCT aid, attr, value
2787 SELECT v.source AS id,
2791 FROM metabib.record_attr_vector_list v
2792 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
2794 JOIN sourcelist ON (x.id = sourcelist.source)
2796 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
2802 $F$ LANGUAGE SQL STABLE;
2804 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
2807 depth INT DEFAULT NULL,
2808 slimit HSTORE DEFAULT NULL,
2809 soffset HSTORE DEFAULT NULL,
2810 pref_lib INT DEFAULT NULL,
2811 includes TEXT[] DEFAULT NULL::TEXT[]
2812 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
2813 WITH RECURSIVE ou_depth AS (
2818 FROM actor.org_unit_type aout
2819 INNER JOIN actor.org_unit ou ON ou_type = aout.id
2823 ), descendant_depth AS (
2827 FROM actor.org_unit ou
2828 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2829 JOIN anscestor_depth ad ON (ad.id = ou.id),
2831 WHERE ad.depth = ou_depth.depth
2836 FROM actor.org_unit ou
2837 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2838 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2839 ), anscestor_depth AS (
2843 FROM actor.org_unit ou
2844 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2850 FROM actor.org_unit ou
2851 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2852 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
2854 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
2857 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
2858 SELECT acn.id, owning_lib.name, acn.label_sortkey,
2859 evergreen.rank_cp(acp),
2861 FROM asset.call_number acn
2862 JOIN asset.copy acp ON (acn.id = acp.call_number)
2863 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
2864 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
2865 WHERE acn.record = ANY ($1)
2866 AND acn.deleted IS FALSE
2867 AND acp.deleted IS FALSE
2868 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
2870 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2871 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
2873 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
2874 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2875 AND acvac.target_copy = acp.id
2876 AND acvac.record = acn.record
2878 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
2882 CASE WHEN aou.id = $2 THEN -20000 END,
2883 CASE WHEN aou.id = $6 THEN -10000 END,
2884 (SELECT distance - 5000
2885 FROM actor.org_unit_descendants_distance($6) as x
2886 WHERE x.id = aou.id AND $6 IN (
2887 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
2888 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
2891 evergreen.rank_cp(acp)
2894 GROUP BY ua.id, ua.name, ua.label_sortkey
2895 ORDER BY rank, ua.name, ua.label_sortkey
2896 LIMIT ($4 -> 'acn')::INT
2897 OFFSET ($5 -> 'acn')::INT;
2898 $$ LANGUAGE SQL STABLE ROWS 10;
2901 -- Evergreen DB patch XXXX.schema.action-trigger.event_definition.sms_preminder.sql
2903 -- New action trigger event definition: 3 Day Courtesy Notice by SMS
2906 -- check whether patch can be applied
2907 SELECT evergreen.upgrade_deps_block_check('1058', :eg_version); -- mccanna/csharp/gmcharlt
2909 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook,
2910 validator, reactor, delay, max_delay, delay_field, group_field, template)
2911 VALUES (54, FALSE, 1,
2912 '3 Day Courtesy Notice by SMS',
2914 'CircIsOpen', 'SendSMS', '-3 days', '-2 days', 'due_date', 'usr',
2917 [%- user = target.0.usr -%]
2918 [%- homelib = user.home_ou -%]
2919 [%- sms_number = helpers.get_user_setting(user.id, 'opac.default_sms_notify') -%]
2920 [%- sms_carrier = helpers.get_user_setting(user.id, 'opac.default_sms_carrier') -%]
2921 From: [%- helpers.get_org_setting(homelib.id, 'org.bounced_emails') || homelib.email || params.sender_email || default_sender %]
2922 To: [%- helpers.get_sms_gateway_email(sms_carrier,sms_number) %]
2923 Subject: Library Materials Due Soon
2925 You have items due soon:
2927 [% FOR circ IN target %]
2928 [%- copy_details = helpers.get_copy_bib_basics(circ.target_copy.id) -%]
2929 [% copy_details.title FILTER ucfirst %] by [% copy_details.author FILTER ucfirst %] due on [% date.format(helpers.format_date(circ.due_date), '%m-%d-%Y') %]
2935 INSERT INTO action_trigger.environment (event_def, path) VALUES
2936 (54, 'circ_lib.billing_address'),
2937 (54, 'target_copy.call_number'),
2939 (54, 'usr.home_ou');
2942 -- check whether patch can be applied
2943 SELECT evergreen.upgrade_deps_block_check('1059', :eg_version); --Stompro/DPearl/kmlussier
2945 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
2951 CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title,
2952 FIRST(author.value) AS author,
2953 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
2954 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
2955 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
2957 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
2959 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
2961 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
2963 FROM biblio.record_entry r
2964 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
2965 LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear.
2966 SELECT b.record, string_agg(val, ' ') AS val FROM (
2967 SELECT title_np.record, title_np.value AS val
2968 FROM metabib.full_rec title_np
2970 title_np.tag = '245'
2971 AND title_np.subfield IN ('p','n')
2972 ORDER BY title_np.id
2975 ) title_np ON (title_np.record=r.id)
2976 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
2977 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')
2978 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')
2979 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
2980 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
2984 -- Remove trigger on biblio.record_entry
2985 SELECT reporter.disable_materialized_simple_record_trigger();
2987 -- Rebuild reporter.materialized_simple_record
2988 SELECT reporter.enable_materialized_simple_record_trigger();
2991 SELECT evergreen.upgrade_deps_block_check('1060', :eg_version);
2993 DROP VIEW IF EXISTS extend_reporter.copy_count_per_org;
2996 CREATE OR REPLACE VIEW extend_reporter.copy_count_per_org AS
2997 SELECT acn.record AS bibid,
3000 max(ac.edit_date) AS last_edit_time,
3001 min(ac.deleted::integer) AS has_only_deleted_copies,
3004 WHEN ac.deleted THEN ac.id
3006 END) AS deleted_count,
3009 WHEN NOT ac.deleted THEN ac.id
3011 END) AS visible_count,
3012 count(*) AS total_count
3013 FROM asset.call_number acn,
3015 WHERE ac.call_number = acn.id
3016 GROUP BY acn.record, acn.owning_lib, ac.circ_lib;
3020 SELECT evergreen.upgrade_deps_block_check('1061', :eg_version);
3022 INSERT INTO config.org_unit_setting_type
3023 (name, label, description, grp, datatype)
3025 'ui.staff.max_recent_patrons',
3027 'ui.staff.max_recent_patrons',
3028 'Number of Retrievable Recent Patrons',
3033 'ui.staff.max_recent_patrons',
3034 'Number of most recently accessed patrons that can be re-retrieved ' ||
3035 'in the staff client. A value of 0 or less disables the feature. Defaults to 1.',
3044 SELECT evergreen.upgrade_deps_block_check('1062', :eg_version);
3046 CREATE TABLE acq.edi_attr (
3047 key TEXT PRIMARY KEY,
3048 label TEXT NOT NULL UNIQUE
3051 CREATE TABLE acq.edi_attr_set (
3052 id SERIAL PRIMARY KEY,
3053 label TEXT NOT NULL UNIQUE
3056 CREATE TABLE acq.edi_attr_set_map (
3057 id SERIAL PRIMARY KEY,
3058 attr_set INTEGER NOT NULL REFERENCES acq.edi_attr_set(id)
3059 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3060 attr TEXT NOT NULL REFERENCES acq.edi_attr(key)
3061 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3062 CONSTRAINT edi_attr_set_map_attr_once UNIQUE (attr_set, attr)
3065 -- An attr_set is not strictly required, since some edi_accounts/vendors
3066 -- may not need to apply any attributes.
3067 ALTER TABLE acq.edi_account
3068 ADD COLUMN attr_set INTEGER REFERENCES acq.edi_attr_set(id),
3069 ADD COLUMN use_attrs BOOLEAN NOT NULL DEFAULT FALSE;
3074 SELECT evergreen.upgrade_deps_block_check('1063', :eg_version);
3081 FOR r IN SELECT t.table_schema AS sname,
3082 t.table_name AS tname,
3083 t.column_name AS colname,
3085 FROM information_schema.referential_constraints ref
3086 JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
3087 WHERE ref.unique_constraint_schema = 'asset'
3088 AND ref.unique_constraint_name = 'copy_pkey'
3091 EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
3094 CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
3096 PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
3098 RAISE foreign_key_violation USING MESSAGE = FORMAT(
3099 $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
3104 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
3108 CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
3109 AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
3110 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
3118 SELECT evergreen.upgrade_deps_block_check('1064', :eg_version);
3120 ALTER TABLE serial.issuance DROP CONSTRAINT IF EXISTS issuance_caption_and_pattern_fkey;
3122 -- Using NOT VALID and VALIDATE CONSTRAINT limits the impact to concurrent work.
3123 -- For details, see: https://www.postgresql.org/docs/current/static/sql-altertable.html
3125 ALTER TABLE serial.issuance ADD CONSTRAINT issuance_caption_and_pattern_fkey
3126 FOREIGN KEY (caption_and_pattern)
3127 REFERENCES serial.caption_and_pattern (id)
3129 DEFERRABLE INITIALLY DEFERRED
3132 ALTER TABLE serial.issuance VALIDATE CONSTRAINT issuance_caption_and_pattern_fkey;
3136 SELECT evergreen.upgrade_deps_block_check('1065', :eg_version);
3138 CREATE TABLE serial.pattern_template (
3139 id SERIAL PRIMARY KEY,
3141 pattern_code TEXT NOT NULL,
3142 owning_lib INTEGER REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
3143 share_depth INTEGER NOT NULL DEFAULT 0
3145 CREATE INDEX serial_pattern_template_name_idx ON serial.pattern_template (evergreen.lowercase(name));
3147 CREATE OR REPLACE FUNCTION serial.pattern_templates_visible_to(org_unit INT) RETURNS SETOF serial.pattern_template AS $func$
3149 RETURN QUERY SELECT *
3150 FROM serial.pattern_template spt
3152 SELECT ARRAY_AGG(id)
3153 FROM actor.org_unit_descendants(spt.owning_lib, spt.share_depth)
3154 ) @@ org_unit::TEXT::QUERY_INT;
3156 $func$ LANGUAGE PLPGSQL;
3159 SELECT evergreen.upgrade_deps_block_check('1066', :eg_version);
3161 INSERT INTO permission.perm_list ( id, code, description ) VALUES
3162 ( 593, 'ADMIN_SERIAL_PATTERN_TEMPLATE', oils_i18n_gettext( 593,
3163 'Administer serial prediction pattern templates', 'ppl', 'description' ))
3166 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
3168 pgt.id, perm.id, aout.depth, FALSE
3170 permission.grp_tree pgt,
3171 permission.perm_list perm,
3172 actor.org_unit_type aout
3174 pgt.name = 'Serials' AND
3175 aout.name = 'System' AND
3177 'ADMIN_SERIAL_PATTERN_TEMPLATE'
3181 SELECT evergreen.upgrade_deps_block_check('1067', :eg_version);
3183 INSERT INTO acq.edi_attr (key, label) VALUES
3185 oils_i18n_gettext('INCLUDE_PO_NAME',
3186 'Orders Include PO Name', 'aea', 'label')),
3188 oils_i18n_gettext('INCLUDE_COPIES',
3189 'Orders Include Copy Data', 'aea', 'label')),
3191 oils_i18n_gettext('INCLUDE_FUND',
3192 'Orders Include Copy Funds', 'aea', 'label')),
3193 ('INCLUDE_CALL_NUMBER',
3194 oils_i18n_gettext('INCLUDE_CALL_NUMBER',
3195 'Orders Include Copy Call Numbers', 'aea', 'label')),
3196 ('INCLUDE_ITEM_TYPE',
3197 oils_i18n_gettext('INCLUDE_ITEM_TYPE',
3198 'Orders Include Copy Item Types', 'aea', 'label')),
3199 ('INCLUDE_ITEM_BARCODE',
3200 oils_i18n_gettext('INCLUDE_ITEM_BARCODE',
3201 'Orders Include Copy Barcodes', 'aea', 'label')),
3202 ('INCLUDE_LOCATION',
3203 oils_i18n_gettext('INCLUDE_LOCATION',
3204 'Orders Include Copy Locations', 'aea', 'label')),
3205 ('INCLUDE_COLLECTION_CODE',
3206 oils_i18n_gettext('INCLUDE_COLLECTION_CODE',
3207 'Orders Include Copy Collection Codes', 'aea', 'label')),
3208 ('INCLUDE_OWNING_LIB',
3209 oils_i18n_gettext('INCLUDE_OWNING_LIB',
3210 'Orders Include Copy Owning Library', 'aea', 'label')),
3211 ('USE_ID_FOR_OWNING_LIB',
3212 oils_i18n_gettext('USE_ID_FOR_OWNING_LIB',
3213 'Emit Owning Library ID Rather Than Short Name. Takes effect only if INCLUDE_OWNING_LIB is in use', 'aea', 'label')),
3214 ('INCLUDE_QUANTITY',
3215 oils_i18n_gettext('INCLUDE_QUANTITY',
3216 'Orders Include Copy Quantities', 'aea', 'label')),
3218 oils_i18n_gettext('INCLUDE_COPY_ID',
3219 'Orders Include Copy IDs', 'aea', 'label')),
3220 ('BUYER_ID_INCLUDE_VENDCODE',
3221 oils_i18n_gettext('BUYER_ID_INCLUDE_VENDCODE',
3222 'Buyer ID Qualifier Includes Vendcode', 'aea', 'label')),
3223 ('BUYER_ID_ONLY_VENDCODE',
3224 oils_i18n_gettext('BUYER_ID_ONLY_VENDCODE',
3225 'Buyer ID Qualifier Only Contains Vendcode', 'aea', 'label')),
3226 ('INCLUDE_BIB_EDITION',
3227 oils_i18n_gettext('INCLUDE_BIB_EDITION',
3228 'Order Lineitems Include Edition Info', 'aea', 'label')),
3229 ('INCLUDE_BIB_AUTHOR',
3230 oils_i18n_gettext('INCLUDE_BIB_AUTHOR',
3231 'Order Lineitems Include Author Info', 'aea', 'label')),
3232 ('INCLUDE_BIB_PAGINATION',
3233 oils_i18n_gettext('INCLUDE_BIB_PAGINATION',
3234 'Order Lineitems Include Pagination Info', 'aea', 'label')),
3236 oils_i18n_gettext('COPY_SPEC_CODES',
3237 'Order Lineitem Notes Include Copy Spec Codes', 'aea', 'label')),
3238 ('INCLUDE_EMPTY_IMD_VALUES',
3239 oils_i18n_gettext('INCLUDE_EMPTY_IMD_VALUES',
3240 'Lineitem Title, Author, etc. Fields Are Present Even if Empty', 'aea', 'label')),
3241 ('INCLUDE_EMPTY_LI_NOTE',
3242 oils_i18n_gettext('INCLUDE_EMPTY_LI_NOTE',
3243 'Order Lineitem Notes Always Present (Even if Empty)', 'aea', 'label')),
3244 ('INCLUDE_EMPTY_CALL_NUMBER',
3245 oils_i18n_gettext('INCLUDE_EMPTY_CALL_NUMBER',
3246 'Order Copies Always Include Call Number (Even if Empty)', 'aea', 'label')),
3247 ('INCLUDE_EMPTY_ITEM_TYPE',
3248 oils_i18n_gettext('INCLUDE_EMPTY_ITEM_TYPE',
3249 'Order Copies Always Include Item Type (Even if Empty)', 'aea', 'label')),
3250 ('INCLUDE_EMPTY_LOCATION',
3251 oils_i18n_gettext('INCLUDE_EMPTY_LOCATION',
3252 'Order Copies Always Include Location (Even if Empty)', 'aea', 'label')),
3253 ('INCLUDE_EMPTY_COLLECTION_CODE',
3254 oils_i18n_gettext('INCLUDE_EMPTY_COLLECTION_CODE',
3255 'Order Copies Always Include Collection Code (Even if Empty)', 'aea', 'label')),
3256 ('LINEITEM_IDENT_VENDOR_NUMBER',
3257 oils_i18n_gettext('LINEITEM_IDENT_VENDOR_NUMBER',
3258 'Lineitem Identifier Fields (LIN/PIA) Use Vendor-Encoded ID Value When Available', 'aea', 'label')),
3259 ('LINEITEM_REF_ID_ONLY',
3260 oils_i18n_gettext('LINEITEM_REF_ID_ONLY',
3261 'Lineitem Reference Field (RFF) Uses Lineitem ID Only', 'aea', 'label'))
3265 INSERT INTO acq.edi_attr_set (id, label) VALUES (1, 'Ingram Default');
3266 INSERT INTO acq.edi_attr_set (id, label) VALUES (2, 'Baker & Taylor Default');
3267 INSERT INTO acq.edi_attr_set (id, label) VALUES (3, 'Brodart Default');
3268 INSERT INTO acq.edi_attr_set (id, label) VALUES (4, 'Midwest Tape Default');
3269 INSERT INTO acq.edi_attr_set (id, label) VALUES (5, 'ULS Default');
3270 INSERT INTO acq.edi_attr_set (id, label) VALUES (6, 'Recorded Books Default');
3271 INSERT INTO acq.edi_attr_set (id, label) VALUES (7, 'Midwest Library Service');
3273 -- carve out space for mucho defaults
3274 SELECT SETVAL('acq.edi_attr_set_id_seq'::TEXT, 1000);
3276 INSERT INTO acq.edi_attr_set_map (attr_set, attr) VALUES
3279 (1, 'INCLUDE_PO_NAME'),
3280 (1, 'INCLUDE_COPIES'),
3281 (1, 'INCLUDE_ITEM_TYPE'),
3282 (1, 'INCLUDE_COLLECTION_CODE'),
3283 (1, 'INCLUDE_OWNING_LIB'),
3284 (1, 'INCLUDE_QUANTITY'),
3285 (1, 'INCLUDE_BIB_PAGINATION'),
3288 (2, 'INCLUDE_COPIES'),
3289 (2, 'INCLUDE_ITEM_TYPE'),
3290 (2, 'INCLUDE_COLLECTION_CODE'),
3291 (2, 'INCLUDE_CALL_NUMBER'),
3292 (2, 'INCLUDE_OWNING_LIB'),
3293 (2, 'INCLUDE_QUANTITY'),
3294 (2, 'INCLUDE_BIB_PAGINATION'),
3295 (2, 'BUYER_ID_INCLUDE_VENDCODE'),
3296 (2, 'INCLUDE_EMPTY_LI_NOTE'),
3297 (2, 'INCLUDE_EMPTY_CALL_NUMBER'),
3298 (2, 'INCLUDE_EMPTY_ITEM_TYPE'),
3299 (2, 'INCLUDE_EMPTY_COLLECTION_CODE'),
3300 (2, 'INCLUDE_EMPTY_LOCATION'),
3301 (2, 'LINEITEM_IDENT_VENDOR_NUMBER'),
3302 (2, 'LINEITEM_REF_ID_ONLY'),
3305 (3, 'INCLUDE_COPIES'),
3306 (3, 'INCLUDE_FUND'),
3307 (3, 'INCLUDE_ITEM_TYPE'),
3308 (3, 'INCLUDE_COLLECTION_CODE'),
3309 (3, 'INCLUDE_OWNING_LIB'),
3310 (3, 'INCLUDE_QUANTITY'),
3311 (3, 'INCLUDE_BIB_PAGINATION'),
3312 (3, 'COPY_SPEC_CODES'),
3315 (4, 'INCLUDE_COPIES'),
3316 (4, 'INCLUDE_FUND'),
3317 (4, 'INCLUDE_OWNING_LIB'),
3318 (4, 'INCLUDE_QUANTITY'),
3319 (4, 'INCLUDE_BIB_PAGINATION'),
3322 (5, 'INCLUDE_COPIES'),
3323 (5, 'INCLUDE_ITEM_TYPE'),
3324 (5, 'INCLUDE_COLLECTION_CODE'),
3325 (5, 'INCLUDE_OWNING_LIB'),
3326 (5, 'INCLUDE_QUANTITY'),
3327 (5, 'INCLUDE_BIB_AUTHOR'),
3328 (5, 'INCLUDE_BIB_EDITION'),
3329 (5, 'INCLUDE_EMPTY_LI_NOTE'),
3332 (6, 'INCLUDE_COPIES'),
3333 (6, 'INCLUDE_ITEM_TYPE'),
3334 (6, 'INCLUDE_COLLECTION_CODE'),
3335 (6, 'INCLUDE_OWNING_LIB'),
3336 (6, 'INCLUDE_QUANTITY'),
3337 (6, 'INCLUDE_BIB_PAGINATION'),
3339 -- Midwest Library Service
3340 (7, 'INCLUDE_BIB_AUTHOR'),
3341 (7, 'INCLUDE_BIB_EDITION'),
3342 (7, 'BUYER_ID_ONLY_VENDCODE'),
3343 (7, 'INCLUDE_EMPTY_IMD_VALUES')
3350 SELECT evergreen.upgrade_deps_block_check('1068', :eg_version); --miker/gmcharlt/kmlussier
3352 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"?>
3353 <xsl:stylesheet version="1.0" xmlns:mads="http://www.loc.gov/mads/v2"
3354 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:marc="http://www.loc.gov/MARC21/slim"
3355 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="marc">
3356 <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
3357 <xsl:strip-space elements="*"/>
3359 <xsl:variable name="ascii">
3360 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
3363 <xsl:variable name="latin1">
3364 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
3366 <!-- Characters that usually don't need to be escaped -->
3367 <xsl:variable name="safe">
3368 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
3371 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
3374 <xsl:template name="datafield">
3375 <xsl:param name="tag"/>
3376 <xsl:param name="ind1">
3377 <xsl:text> </xsl:text>
3379 <xsl:param name="ind2">
3380 <xsl:text> </xsl:text>
3382 <xsl:param name="subfields"/>
3383 <xsl:element name="marc:datafield">
3384 <xsl:attribute name="tag">
3385 <xsl:value-of select="$tag"/>
3387 <xsl:attribute name="ind1">
3388 <xsl:value-of select="$ind1"/>
3390 <xsl:attribute name="ind2">
3391 <xsl:value-of select="$ind2"/>
3393 <xsl:copy-of select="$subfields"/>
3397 <xsl:template name="subfieldSelect">
3398 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
3399 <xsl:param name="delimeter">
3400 <xsl:text> </xsl:text>
3402 <xsl:variable name="str">
3403 <xsl:for-each select="marc:subfield">
3404 <xsl:if test="contains($codes, @code)">
3405 <xsl:value-of select="text()"/>
3406 <xsl:value-of select="$delimeter"/>
3410 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
3413 <xsl:template name="buildSpaces">
3414 <xsl:param name="spaces"/>
3415 <xsl:param name="char">
3416 <xsl:text> </xsl:text>
3418 <xsl:if test="$spaces>0">
3419 <xsl:value-of select="$char"/>
3420 <xsl:call-template name="buildSpaces">
3421 <xsl:with-param name="spaces" select="$spaces - 1"/>
3422 <xsl:with-param name="char" select="$char"/>
3423 </xsl:call-template>
3427 <xsl:template name="chopPunctuation">
3428 <xsl:param name="chopString"/>
3429 <xsl:param name="punctuation">
3430 <xsl:text>.:,;/ </xsl:text>
3432 <xsl:variable name="length" select="string-length($chopString)"/>
3434 <xsl:when test="$length=0"/>
3435 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3436 <xsl:call-template name="chopPunctuation">
3437 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3438 <xsl:with-param name="punctuation" select="$punctuation"/>
3439 </xsl:call-template>
3441 <xsl:when test="not($chopString)"/>
3443 <xsl:value-of select="$chopString"/>
3448 <xsl:template name="chopPunctuationFront">
3449 <xsl:param name="chopString"/>
3450 <xsl:variable name="length" select="string-length($chopString)"/>
3452 <xsl:when test="$length=0"/>
3453 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
3454 <xsl:call-template name="chopPunctuationFront">
3455 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
3457 </xsl:call-template>
3459 <xsl:when test="not($chopString)"/>
3461 <xsl:value-of select="$chopString"/>
3466 <xsl:template name="chopPunctuationBack">
3467 <xsl:param name="chopString"/>
3468 <xsl:param name="punctuation">
3469 <xsl:text>.:,;/] </xsl:text>
3471 <xsl:variable name="length" select="string-length($chopString)"/>
3473 <xsl:when test="$length=0"/>
3474 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3475 <xsl:call-template name="chopPunctuation">
3476 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3477 <xsl:with-param name="punctuation" select="$punctuation"/>
3478 </xsl:call-template>
3480 <xsl:when test="not($chopString)"/>
3482 <xsl:value-of select="$chopString"/>
3487 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
3488 <xsl:template name="url-encode">
3490 <xsl:param name="str"/>
3492 <xsl:if test="$str">
3493 <xsl:variable name="first-char" select="substring($str,1,1)"/>
3495 <xsl:when test="contains($safe,$first-char)">
3496 <xsl:value-of select="$first-char"/>
3499 <xsl:variable name="codepoint">
3501 <xsl:when test="contains($ascii,$first-char)">
3503 select="string-length(substring-before($ascii,$first-char)) + 32"
3506 <xsl:when test="contains($latin1,$first-char)">
3508 select="string-length(substring-before($latin1,$first-char)) + 160"/>
3512 <xsl:message terminate="no">Warning: string contains a character
3513 that is out of range! Substituting "?".</xsl:message>
3514 <xsl:text>63</xsl:text>
3518 <xsl:variable name="hex-digit1"
3519 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
3520 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
3521 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
3522 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
3525 <xsl:if test="string-length($str) > 1">
3526 <xsl:call-template name="url-encode">
3527 <xsl:with-param name="str" select="substring($str,2)"/>
3528 </xsl:call-template>
3535 2.14 Fixed bug in mads:geographic attributes syntax ws 05/04/2016
3536 2.13 fixed repeating <geographic> tmee 01/31/2014
3537 2.12 added $2 authority for <classification> tmee 09/18/2012
3538 2.11 added delimiters between <classification> subfields tmee 09/18/2012
3539 2.10 fixed type="other" and type="otherType" for mads:related tmee 09/16/2011
3540 2.09 fixed professionTerm and genreTerm empty tag error tmee 09/16/2011
3541 2.08 fixed marc:subfield @code='i' matching error tmee 09/16/2011
3542 2.07 fixed 555 duplication error tmee 08/10/2011
3543 2.06 fixed topic subfield error tmee 08/10/2011
3544 2.05 fixed title subfield error tmee 06/20/2011
3545 2.04 fixed geographicSubdivision mapping for authority element tmee 06/16/2011
3546 2.03 added classification for 053, 055, 060, 065, 070, 080, 082, 083, 086, 087 tmee 06/03/2011
3547 2.02 added descriptionStandard for 008/10 tmee 04/27/2011
3548 2.01 added extensions for 046, 336, 370, 374, 375, 376 tmee 04/08/2011
3549 2.00 redefined imported MODS elements in version 1.0 to MADS elements in version 2.0 tmee 02/08/2011
3550 1.08 added 372 subfields $a $s $t for <fieldOfActivity> tmee 06/24/2010
3551 1.07 removed role/roleTerm 100, 110, 111, 400, 410, 411, 500, 510, 511, 700, 710, 711 tmee 06/24/2010
3552 1.06 added strip-space tmee 06/24/2010
3553 1.05 added subfield $a for 130, 430, 530 tmee 06/21/2010
3554 1.04 fixed 550 z omission ntra 08/11/2008
3555 1.03 removed duplication of 550 $a text tmee 11/01/2006
3556 1.02 fixed namespace references between mads and mods ntra 10/06/2006
3557 1.01 revised rgue/jrad 11/29/05
3558 1.00 adapted from MARC21Slim2MODS3.xsl ntra 07/06/05
3561 <!-- authority attribute defaults to 'naf' if not set using this authority parameter, for <authority> descriptors: name, titleInfo, geographic -->
3562 <xsl:param name="authority"/>
3563 <xsl:variable name="auth">
3565 <xsl:when test="$authority">
3566 <xsl:value-of select="$authority"/>
3568 <xsl:otherwise>naf</xsl:otherwise>
3571 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
3572 <xsl:variable name="controlField008-06"
3573 select="substring(descendant-or-self::marc:controlfield[@tag=008],7,1)"/>
3574 <xsl:variable name="controlField008-11"
3575 select="substring(descendant-or-self::marc:controlfield[@tag=008],12,1)"/>
3576 <xsl:variable name="controlField008-14"
3577 select="substring(descendant-or-self::marc:controlfield[@tag=008],15,1)"/>
3578 <xsl:template match="/">
3580 <xsl:when test="descendant-or-self::marc:collection">
3581 <mads:madsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3582 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/v2/mads-2-0.xsd">
3583 <xsl:for-each select="descendant-or-self::marc:collection/marc:record">
3584 <mads:mads version="2.0">
3585 <xsl:call-template name="marcRecord"/>
3588 </mads:madsCollection>
3591 <mads:mads version="2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3592 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/mads-2-0.xsd">
3593 <xsl:for-each select="descendant-or-self::marc:record">
3594 <xsl:call-template name="marcRecord"/>
3601 <xsl:template name="marcRecord">
3605 <xsl:when test="$controlField008-06='d'">
3606 <xsl:attribute name="geographicSubdivision">
3607 <xsl:text>direct</xsl:text>
3610 <xsl:when test="$controlField008-06='i'">
3611 <xsl:attribute name="geographicSubdivision">
3612 <xsl:text>indirect</xsl:text>
3615 <xsl:when test="$controlField008-06='n'">
3616 <xsl:attribute name="geographicSubdivision">
3617 <xsl:text>not applicable</xsl:text>
3622 <xsl:apply-templates select="marc:datafield[100 <= @tag and @tag < 200]"/>
3626 <xsl:apply-templates
3627 select="marc:datafield[500 <= @tag and @tag <= 585]|marc:datafield[700 <= @tag and @tag <= 785]"/>
3630 <xsl:apply-templates select="marc:datafield[400 <= @tag and @tag <= 485]"/>
3633 <xsl:apply-templates select="marc:datafield[667 <= @tag and @tag <= 688]"/>
3636 <xsl:apply-templates select="marc:datafield[@tag=856]"/>
3637 <xsl:apply-templates select="marc:datafield[@tag=010]"/>
3638 <xsl:apply-templates select="marc:datafield[@tag=024]"/>
3639 <xsl:apply-templates select="marc:datafield[@tag=372]"/>
3641 <!-- classification -->
3642 <xsl:apply-templates select="marc:datafield[@tag=053]"/>
3643 <xsl:apply-templates select="marc:datafield[@tag=055]"/>
3644 <xsl:apply-templates select="marc:datafield[@tag=060]"/>
3645 <xsl:apply-templates select="marc:datafield[@tag=065]"/>
3646 <xsl:apply-templates select="marc:datafield[@tag=070]"/>
3647 <xsl:apply-templates select="marc:datafield[@tag=080]"/>
3648 <xsl:apply-templates select="marc:datafield[@tag=082]"/>
3649 <xsl:apply-templates select="marc:datafield[@tag=083]"/>
3650 <xsl:apply-templates select="marc:datafield[@tag=086]"/>
3651 <xsl:apply-templates select="marc:datafield[@tag=087]"/>
3654 <xsl:for-each select="marc:datafield[@tag=373]">
3657 <xsl:value-of select="marc:subfield[@code='a']"/>
3659 <mads:dateValid point="start">
3660 <xsl:value-of select="marc:subfield[@code='s']"/>
3662 <mads:dateValid point="end">
3663 <xsl:value-of select="marc:subfield[@code='t']"/>
3667 <xsl:for-each select="marc:datafield[@tag=371]">
3671 <xsl:value-of select="marc:subfield[@code='a']"/>
3674 <xsl:value-of select="marc:subfield[@code='b']"/>
3677 <xsl:value-of select="marc:subfield[@code='c']"/>
3680 <xsl:value-of select="marc:subfield[@code='d']"/>
3683 <xsl:value-of select="marc:subfield[@code='e']"/>
3687 <xsl:value-of select="marc:subfield[@code='m']"/>
3693 <xsl:for-each select="marc:datafield[@tag=336]">
3696 <mads:contentType type="text">
3697 <xsl:value-of select="marc:subfield[@code='a']"/>
3699 <mads:contentType type="code">
3700 <xsl:value-of select="marc:subfield[@code='b']"/>
3706 <xsl:for-each select="marc:datafield[@tag=374]">
3710 <xsl:when test="marc:subfield[@code='a']">
3711 <mads:professionTerm>
3712 <xsl:value-of select="marc:subfield[@code='a']"/>
3713 </mads:professionTerm>
3715 <xsl:when test="marc:subfield[@code='s']">
3716 <mads:dateValid point="start">
3717 <xsl:value-of select="marc:subfield[@code='s']"/>
3720 <xsl:when test="marc:subfield[@code='t']">
3721 <mads:dateValid point="end">
3722 <xsl:value-of select="marc:subfield[@code='t']"/>
3730 <xsl:for-each select="marc:datafield[@tag=375]">
3734 <xsl:when test="marc:subfield[@code='a']">
3736 <xsl:value-of select="marc:subfield[@code='a']"/>
3739 <xsl:when test="marc:subfield[@code='s']">
3740 <mads:dateValid point="start">
3741 <xsl:value-of select="marc:subfield[@code='s']"/>
3744 <xsl:when test="marc:subfield[@code='t']">
3745 <mads:dateValid point="end">
3746 <xsl:value-of select="marc:subfield[@code='t']"/>
3754 <xsl:for-each select="marc:datafield[@tag=376]">
3756 <mads:familyInformation>
3758 <xsl:value-of select="marc:subfield[@code='a']"/>
3759 </mads:typeOfFamily>
3760 <mads:nameOfProminentMember>
3761 <xsl:value-of select="marc:subfield[@code='b']"/>
3762 </mads:nameOfProminentMember>
3763 <mads:hereditaryTitle>
3764 <xsl:value-of select="marc:subfield[@code='c']"/>
3765 </mads:hereditaryTitle>
3766 <mads:dateValid point="start">
3767 <xsl:value-of select="marc:subfield[@code='s']"/>
3769 <mads:dateValid point="end">
3770 <xsl:value-of select="marc:subfield[@code='t']"/>
3772 </mads:familyInformation>
3777 <mads:recordOrigin>Converted from MARCXML to MADS version 2.0 (Revision 2.13)</mads:recordOrigin>
3778 <!-- <xsl:apply-templates select="marc:datafield[@tag=024]"/> -->
3780 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='a']"/>
3781 <xsl:apply-templates select="marc:controlfield[@tag=005]"/>
3782 <xsl:apply-templates select="marc:controlfield[@tag=001]"/>
3783 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='b']"/>
3784 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='e']"/>
3785 <xsl:for-each select="marc:controlfield[@tag=008]">
3786 <xsl:if test="substring(.,11,1)='a'">
3787 <mads:descriptionStandard>
3788 <xsl:text>earlier rules</xsl:text>
3789 </mads:descriptionStandard>
3791 <xsl:if test="substring(.,11,1)='b'">
3792 <mads:descriptionStandard>
3793 <xsl:text>aacr1</xsl:text>
3794 </mads:descriptionStandard>
3796 <xsl:if test="substring(.,11,1)='c'">
3797 <mads:descriptionStandard>
3798 <xsl:text>aacr2</xsl:text>
3799 </mads:descriptionStandard>
3801 <xsl:if test="substring(.,11,1)='d'">
3802 <mads:descriptionStandard>
3803 <xsl:text>aacr2 compatible</xsl:text>
3804 </mads:descriptionStandard>
3806 <xsl:if test="substring(.,11,1)='z'">
3807 <mads:descriptionStandard>
3808 <xsl:text>other rules</xsl:text>
3809 </mads:descriptionStandard>
3815 <!-- start of secondary templates -->
3817 <!-- ======== xlink ======== -->
3819 <!-- <xsl:template name="uri">
3820 <xsl:for-each select="marc:subfield[@code='0']">
3821 <xsl:attribute name="xlink:href">
3822 <xsl:value-of select="."/>
3827 <xsl:template match="marc:subfield[@code='i']">
3828 <xsl:attribute name="otherType">
3829 <xsl:value-of select="."/>
3833 <!-- No role/roleTerm mapped in MADS 06/24/2010
3834 <xsl:template name="role">
3835 <xsl:for-each select="marc:subfield[@code='e']">
3837 <mads:roleTerm type="text">
3838 <xsl:value-of select="."/>
3845 <xsl:template name="part">
3846 <xsl:variable name="partNumber">
3847 <xsl:call-template name="specialSubfieldSelect">
3848 <xsl:with-param name="axis">n</xsl:with-param>
3849 <xsl:with-param name="anyCodes">n</xsl:with-param>
3850 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3851 </xsl:call-template>
3853 <xsl:variable name="partName">
3854 <xsl:call-template name="specialSubfieldSelect">
3855 <xsl:with-param name="axis">p</xsl:with-param>
3856 <xsl:with-param name="anyCodes">p</xsl:with-param>
3857 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3858 </xsl:call-template>
3860 <xsl:if test="string-length(normalize-space($partNumber))">
3862 <xsl:call-template name="chopPunctuation">
3863 <xsl:with-param name="chopString" select="$partNumber"/>
3864 </xsl:call-template>
3867 <xsl:if test="string-length(normalize-space($partName))">
3869 <xsl:call-template name="chopPunctuation">
3870 <xsl:with-param name="chopString" select="$partName"/>
3871 </xsl:call-template>
3876 <xsl:template name="nameABCDN">
3877 <xsl:for-each select="marc:subfield[@code='a']">
3879 <xsl:call-template name="chopPunctuation">
3880 <xsl:with-param name="chopString" select="."/>
3881 </xsl:call-template>
3884 <xsl:for-each select="marc:subfield[@code='b']">
3886 <xsl:value-of select="."/>
3890 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
3892 <xsl:call-template name="subfieldSelect">
3893 <xsl:with-param name="codes">cdn</xsl:with-param>
3894 </xsl:call-template>
3899 <xsl:template name="nameABCDQ">
3901 <xsl:call-template name="chopPunctuation">
3902 <xsl:with-param name="chopString">
3903 <xsl:call-template name="subfieldSelect">
3904 <xsl:with-param name="codes">aq</xsl:with-param>
3905 </xsl:call-template>
3907 </xsl:call-template>
3909 <xsl:call-template name="termsOfAddress"/>
3910 <xsl:call-template name="nameDate"/>
3913 <xsl:template name="nameACDENQ">
3915 <xsl:call-template name="subfieldSelect">
3916 <xsl:with-param name="codes">acdenq</xsl:with-param>
3917 </xsl:call-template>
3921 <xsl:template name="nameDate">
3922 <xsl:for-each select="marc:subfield[@code='d']">
3923 <mads:namePart type="date">
3924 <xsl:call-template name="chopPunctuation">
3925 <xsl:with-param name="chopString" select="."/>
3926 </xsl:call-template>
3931 <xsl:template name="specialSubfieldSelect">
3932 <xsl:param name="anyCodes"/>
3933 <xsl:param name="axis"/>
3934 <xsl:param name="beforeCodes"/>
3935 <xsl:param name="afterCodes"/>
3936 <xsl:variable name="str">
3937 <xsl:for-each select="marc:subfield">
3939 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])">
3940 <xsl:value-of select="text()"/>
3941 <xsl:text> </xsl:text>
3945 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
3948 <xsl:template name="termsOfAddress">
3949 <xsl:if test="marc:subfield[@code='b' or @code='c']">
3950 <mads:namePart type="termsOfAddress">
3951 <xsl:call-template name="chopPunctuation">
3952 <xsl:with-param name="chopString">
3953 <xsl:call-template name="subfieldSelect">
3954 <xsl:with-param name="codes">bc</xsl:with-param>
3955 </xsl:call-template>
3957 </xsl:call-template>
3962 <xsl:template name="displayLabel">
3963 <xsl:if test="marc:subfield[@code='z']">
3964 <xsl:attribute name="displayLabel">
3965 <xsl:value-of select="marc:subfield[@code='z']"/>
3968 <xsl:if test="marc:subfield[@code='3']">
3969 <xsl:attribute name="displayLabel">
3970 <xsl:value-of select="marc:subfield[@code='3']"/>
3975 <xsl:template name="isInvalid">
3976 <xsl:if test="@code='z'">
3977 <xsl:attribute name="invalid">yes</xsl:attribute>
3981 <xsl:template name="sub2Attribute">
3983 <xsl:if test="../marc:subfield[@code='2']">
3984 <xsl:attribute name="type">
3985 <xsl:value-of select="../marc:subfield[@code='2']"/>
3990 <xsl:template match="marc:controlfield[@tag=001]">
3991 <mads:recordIdentifier>
3992 <xsl:if test="../marc:controlfield[@tag=003]">
3993 <xsl:attribute name="source">
3994 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
3997 <xsl:value-of select="."/>
3998 </mads:recordIdentifier>
4001 <xsl:template match="marc:controlfield[@tag=005]">
4002 <mads:recordChangeDate encoding="iso8601">
4003 <xsl:value-of select="."/>
4004 </mads:recordChangeDate>
4007 <xsl:template match="marc:controlfield[@tag=008]">
4008 <mads:recordCreationDate encoding="marc">
4009 <xsl:value-of select="substring(.,1,6)"/>
4010 </mads:recordCreationDate>
4013 <xsl:template match="marc:datafield[@tag=010]">
4014 <xsl:for-each select="marc:subfield">
4015 <mads:identifier type="lccn">
4016 <xsl:call-template name="isInvalid"/>
4017 <xsl:value-of select="."/>
4022 <xsl:template match="marc:datafield[@tag=024]">
4023 <xsl:for-each select="marc:subfield[not(@code=2)]">
4025 <xsl:call-template name="isInvalid"/>
4026 <xsl:call-template name="sub2Attribute"/>
4027 <xsl:value-of select="."/>
4032 <!-- ========== 372 ========== -->
4033 <xsl:template match="marc:datafield[@tag=372]">
4034 <mads:fieldOfActivity>
4035 <xsl:call-template name="subfieldSelect">
4036 <xsl:with-param name="codes">a</xsl:with-param>
4037 </xsl:call-template>
4038 <xsl:text>-</xsl:text>
4039 <xsl:call-template name="subfieldSelect">
4040 <xsl:with-param name="codes">st</xsl:with-param>
4041 </xsl:call-template>
4042 </mads:fieldOfActivity>
4046 <!-- ========== 040 ========== -->
4047 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='a']">
4048 <mads:recordContentSource authority="marcorg">
4049 <xsl:value-of select="."/>
4050 </mads:recordContentSource>
4053 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='b']">
4054 <mads:languageOfCataloging>
4055 <mads:languageTerm authority="iso639-2b" type="code">
4056 <xsl:value-of select="."/>
4057 </mads:languageTerm>
4058 </mads:languageOfCataloging>
4061 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='e']">
4062 <mads:descriptionStandard>
4063 <xsl:value-of select="."/>
4064 </mads:descriptionStandard>
4067 <!-- ========== classification 2.03 ========== -->
4069 <xsl:template match="marc:datafield[@tag=053]">
4070 <mads:classification>
4071 <xsl:call-template name="subfieldSelect">
4072 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4073 <xsl:with-param name="delimeter">-</xsl:with-param>
4074 </xsl:call-template>
4075 </mads:classification>
4078 <xsl:template match="marc:datafield[@tag=055]">
4079 <mads:classification>
4080 <xsl:call-template name="subfieldSelect">
4081 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4082 <xsl:with-param name="delimeter">-</xsl:with-param>
4083 </xsl:call-template>
4084 </mads:classification>
4087 <xsl:template match="marc:datafield[@tag=060]">
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>
4095 <xsl:template match="marc:datafield[@tag=065]">
4096 <mads:classification>
4097 <xsl:attribute name="authority">
4098 <xsl:value-of select="marc:subfield[@code='2']"/>
4100 <xsl:call-template name="subfieldSelect">
4101 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4102 <xsl:with-param name="delimeter">-</xsl:with-param>
4103 </xsl:call-template>
4104 </mads:classification>
4106 <xsl:template match="marc:datafield[@tag=070]">
4107 <mads:classification>
4108 <xsl:call-template name="subfieldSelect">
4109 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4110 <xsl:with-param name="delimeter">-</xsl:with-param>
4111 </xsl:call-template>
4112 </mads:classification>
4114 <xsl:template match="marc:datafield[@tag=080]">
4115 <mads:classification>
4116 <xsl:attribute name="authority">
4117 <xsl:value-of select="marc:subfield[@code='2']"/>
4119 <xsl:call-template name="subfieldSelect">
4120 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4121 <xsl:with-param name="delimeter">-</xsl:with-param>
4122 </xsl:call-template>
4123 </mads:classification>
4125 <xsl:template match="marc:datafield[@tag=082]">
4126 <mads:classification>
4127 <xsl:attribute name="authority">
4128 <xsl:value-of select="marc:subfield[@code='2']"/>
4130 <xsl:call-template name="subfieldSelect">
4131 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4132 <xsl:with-param name="delimeter">-</xsl:with-param>
4133 </xsl:call-template>
4134 </mads:classification>
4136 <xsl:template match="marc:datafield[@tag=083]">
4137 <mads:classification>
4138 <xsl:attribute name="authority">
4139 <xsl:value-of select="marc:subfield[@code='2']"/>
4141 <xsl:call-template name="subfieldSelect">
4142 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4143 <xsl:with-param name="delimeter">-</xsl:with-param>
4144 </xsl:call-template>
4145 </mads:classification>
4147 <xsl:template match="marc:datafield[@tag=086]">
4148 <mads:classification>
4149 <xsl:attribute name="authority">
4150 <xsl:value-of select="marc:subfield[@code='2']"/>
4152 <xsl:call-template name="subfieldSelect">
4153 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4154 <xsl:with-param name="delimeter">-</xsl:with-param>
4155 </xsl:call-template>
4156 </mads:classification>
4158 <xsl:template match="marc:datafield[@tag=087]">
4159 <mads:classification>
4160 <xsl:attribute name="authority">
4161 <xsl:value-of select="marc:subfield[@code='2']"/>
4163 <xsl:call-template name="subfieldSelect">
4164 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4165 <xsl:with-param name="delimeter">-</xsl:with-param>
4166 </xsl:call-template>
4167 </mads:classification>
4171 <!-- ========== names ========== -->
4172 <xsl:template match="marc:datafield[@tag=100]">
4173 <mads:name type="personal">
4174 <xsl:call-template name="setAuthority"/>
4175 <xsl:call-template name="nameABCDQ"/>
4177 <xsl:apply-templates select="*[marc:subfield[not(contains('abcdeq',@code))]]"/>
4178 <xsl:call-template name="title"/>
4179 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4182 <xsl:template match="marc:datafield[@tag=110]">
4183 <mads:name type="corporate">
4184 <xsl:call-template name="setAuthority"/>
4185 <xsl:call-template name="nameABCDN"/>
4187 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4190 <xsl:template match="marc:datafield[@tag=111]">
4191 <mads:name type="conference">
4192 <xsl:call-template name="setAuthority"/>
4193 <xsl:call-template name="nameACDENQ"/>
4195 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4198 <xsl:template match="marc:datafield[@tag=400]">
4200 <xsl:call-template name="variantTypeAttribute"/>
4201 <mads:name type="personal">
4202 <xsl:call-template name="nameABCDQ"/>
4204 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4205 <xsl:call-template name="title"/>
4209 <xsl:template match="marc:datafield[@tag=410]">
4211 <xsl:call-template name="variantTypeAttribute"/>
4212 <mads:name type="corporate">
4213 <xsl:call-template name="nameABCDN"/>
4215 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4219 <xsl:template match="marc:datafield[@tag=411]">
4221 <xsl:call-template name="variantTypeAttribute"/>
4222 <mads:name type="conference">
4223 <xsl:call-template name="nameACDENQ"/>
4225 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4229 <xsl:template match="marc:datafield[@tag=500]|marc:datafield[@tag=700]">
4231 <xsl:call-template name="relatedTypeAttribute"/>
4232 <!-- <xsl:call-template name="uri"/> -->
4233 <mads:name type="personal">
4234 <xsl:call-template name="setAuthority"/>
4235 <xsl:call-template name="nameABCDQ"/>
4237 <xsl:call-template name="title"/>
4238 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4242 <xsl:template match="marc:datafield[@tag=510]|marc:datafield[@tag=710]">
4244 <xsl:call-template name="relatedTypeAttribute"/>
4245 <!-- <xsl:call-template name="uri"/> -->
4246 <mads:name type="corporate">
4247 <xsl:call-template name="setAuthority"/>
4248 <xsl:call-template name="nameABCDN"/>
4250 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4254 <xsl:template match="marc:datafield[@tag=511]|marc:datafield[@tag=711]">
4256 <xsl:call-template name="relatedTypeAttribute"/>
4257 <!-- <xsl:call-template name="uri"/> -->
4258 <mads:name type="conference">
4259 <xsl:call-template name="setAuthority"/>
4260 <xsl:call-template name="nameACDENQ"/>
4262 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4266 <!-- ========== titles ========== -->
4267 <xsl:template match="marc:datafield[@tag=130]">
4268 <xsl:call-template name="uniform-title"/>
4269 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4272 <xsl:template match="marc:datafield[@tag=430]">
4274 <xsl:call-template name="variantTypeAttribute"/>
4275 <xsl:call-template name="uniform-title"/>
4276 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4280 <xsl:template match="marc:datafield[@tag=530]|marc:datafield[@tag=730]">
4282 <xsl:call-template name="relatedTypeAttribute"/>
4283 <xsl:call-template name="uniform-title"/>
4284 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4288 <xsl:template name="title">
4289 <xsl:variable name="hasTitle">
4290 <xsl:for-each select="marc:subfield">
4291 <xsl:if test="(contains('tfghklmors',@code) )">
4292 <xsl:value-of select="@code"/>
4296 <xsl:if test="string-length($hasTitle) > 0 ">
4298 <xsl:call-template name="setAuthority"/>
4300 <xsl:variable name="str">
4301 <xsl:for-each select="marc:subfield">
4302 <xsl:if test="(contains('atfghklmors',@code) )">
4303 <xsl:value-of select="text()"/>
4304 <xsl:text> </xsl:text>
4308 <xsl:call-template name="chopPunctuation">
4309 <xsl:with-param name="chopString">
4310 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4312 </xsl:call-template>
4314 <xsl:call-template name="part"/>
4315 <!-- <xsl:call-template name="uri"/> -->
4320 <xsl:template name="uniform-title">
4321 <xsl:variable name="hasTitle">
4322 <xsl:for-each select="marc:subfield">
4323 <xsl:if test="(contains('atfghklmors',@code) )">
4324 <xsl:value-of select="@code"/>
4328 <xsl:if test="string-length($hasTitle) > 0 ">
4330 <xsl:call-template name="setAuthority"/>
4332 <xsl:variable name="str">
4333 <xsl:for-each select="marc:subfield">
4334 <xsl:if test="(contains('adfghklmors',@code) )">
4335 <xsl:value-of select="text()"/>
4336 <xsl:text> </xsl:text>
4340 <xsl:call-template name="chopPunctuation">
4341 <xsl:with-param name="chopString">
4342 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4344 </xsl:call-template>
4346 <xsl:call-template name="part"/>
4347 <!-- <xsl:call-template name="uri"/> -->
4353 <!-- ========== topics ========== -->
4354 <xsl:template match="marc:subfield[@code='x']">
4356 <xsl:call-template name="chopPunctuation">
4357 <xsl:with-param name="chopString">
4358 <xsl:value-of select="."/>
4360 </xsl:call-template>
4366 match="marc:datafield[@tag=150][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=180][marc:subfield[@code='x']]">
4367 <xsl:call-template name="topic"/>
4368 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4371 match="marc:datafield[@tag=450][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=480][marc:subfield[@code='x']]">
4373 <xsl:call-template name="variantTypeAttribute"/>
4374 <xsl:call-template name="topic"/>
4378 match="marc:datafield[@tag=550 or @tag=750][marc:subfield[@code='a' or @code='b']]">
4380 <xsl:call-template name="relatedTypeAttribute"/>
4381 <!-- <xsl:call-template name="uri"/> -->
4382 <xsl:call-template name="topic"/>
4383 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4386 <xsl:template name="topic">
4388 <xsl:call-template name="setAuthority"/>
4389 <!-- tmee2006 dedupe 550a
4390 <xsl:if test="@tag=550 or @tag=750">
4391 <xsl:call-template name="subfieldSelect">
4392 <xsl:with-param name="codes">ab</xsl:with-param>
4393 </xsl:call-template>
4397 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4398 <xsl:call-template name="chopPunctuation">
4399 <xsl:with-param name="chopString">
4400 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4402 </xsl:call-template>
4405 <xsl:call-template name="chopPunctuation">
4406 <xsl:with-param name="chopString">
4408 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4409 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4412 <xsl:call-template name="subfieldSelect">
4413 <xsl:with-param name="codes">ab</xsl:with-param>
4414 </xsl:call-template>
4418 </xsl:call-template>
4422 <!-- ========= temporals ========== -->
4423 <xsl:template match="marc:subfield[@code='y']">
4425 <xsl:call-template name="chopPunctuation">
4426 <xsl:with-param name="chopString">
4427 <xsl:value-of select="."/>
4429 </xsl:call-template>
4433 match="marc:datafield[@tag=148][marc:subfield[@code='a']]|marc:datafield[@tag=182 ][marc:subfield[@code='y']]">
4434 <xsl:call-template name="temporal"/>
4437 match="marc:datafield[@tag=448][marc:subfield[@code='a']]|marc:datafield[@tag=482][marc:subfield[@code='y']]">
4439 <xsl:call-template name="variantTypeAttribute"/>
4440 <xsl:call-template name="temporal"/>
4444 match="marc:datafield[@tag=548 or @tag=748][marc:subfield[@code='a']]|marc:datafield[@tag=582 or @tag=782][marc:subfield[@code='y']]">
4446 <xsl:call-template name="relatedTypeAttribute"/>
4447 <!-- <xsl:call-template name="uri"/> -->
4448 <xsl:call-template name="temporal"/>
4451 <xsl:template name="temporal">
4453 <xsl:call-template name="setAuthority"/>
4454 <xsl:if test="@tag=548 or @tag=748">
4455 <xsl:value-of select="marc:subfield[@code='a']"/>
4457 <xsl:call-template name="chopPunctuation">
4458 <xsl:with-param name="chopString">
4460 <xsl:when test="@tag=182 or @tag=482 or @tag=582 or @tag=782">
4461 <xsl:apply-templates select="marc:subfield[@code='y']"/>
4464 <xsl:value-of select="marc:subfield[@code='a']"/>
4468 </xsl:call-template>
4470 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4473 <!-- ========== genre ========== -->
4474 <xsl:template match="marc:subfield[@code='v']">
4476 <xsl:call-template name="chopPunctuation">
4477 <xsl:with-param name="chopString">
4478 <xsl:value-of select="."/>
4480 </xsl:call-template>
4484 match="marc:datafield[@tag=155][marc:subfield[@code='a']]|marc:datafield[@tag=185][marc:subfield[@code='v']]">
4485 <xsl:call-template name="genre"/>
4488 match="marc:datafield[@tag=455][marc:subfield[@code='a']]|marc:datafield[@tag=485 ][marc:subfield[@code='v']]">
4490 <xsl:call-template name="variantTypeAttribute"/>
4491 <xsl:call-template name="genre"/>
4495 <xsl:template match="marc:datafield[@tag=555]">
4497 <xsl:call-template name="relatedTypeAttribute"/>
4498 <xsl:call-template name="uri"/>
4499 <xsl:call-template name="genre"/>
4504 match="marc:datafield[@tag=555 or @tag=755][marc:subfield[@code='a']]|marc:datafield[@tag=585][marc:subfield[@code='v']]">
4506 <xsl:call-template name="relatedTypeAttribute"/>
4507 <xsl:call-template name="genre"/>
4510 <xsl:template name="genre">
4512 <xsl:if test="@tag=555">
4513 <xsl:value-of select="marc:subfield[@code='a']"/>
4515 <xsl:call-template name="setAuthority"/>
4516 <xsl:call-template name="chopPunctuation">
4517 <xsl:with-param name="chopString">
4520 <xsl:when test="@tag='555'"/>
4521 <xsl:when test="@tag=185 or @tag=485 or @tag=585">
4522 <xsl:apply-templates select="marc:subfield[@code='v']"/>
4525 <xsl:value-of select="marc:subfield[@code='a']"/>
4529 </xsl:call-template>
4531 <xsl:apply-templates/>
4534 <!-- ========= geographic ========== -->
4535 <xsl:template match="marc:subfield[@code='z']">
4537 <xsl:call-template name="chopPunctuation">
4538 <xsl:with-param name="chopString">
4539 <xsl:value-of select="."/>
4541 </xsl:call-template>
4544 <xsl:template name="geographic">
4547 <xsl:call-template name="setAuthority"/>
4549 <xsl:if test="@tag=151 or @tag=551">
4550 <xsl:value-of select="marc:subfield[@code='a']"/>
4552 <xsl:call-template name="chopPunctuation">
4553 <xsl:with-param name="chopString">
4554 <xsl:if test="@tag=181 or @tag=481 or @tag=581">
4555 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4559 <xsl:when test="@tag=181 or @tag=481 or @tag=581">
4560 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4564 <xsl:value-of select="marc:subfield[@code='a']"/>
4569 </xsl:call-template>
4571 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4574 match="marc:datafield[@tag=151][marc:subfield[@code='a']]|marc:datafield[@tag=181][marc:subfield[@code='z']]">
4575 <xsl:call-template name="geographic"/>
4578 match="marc:datafield[@tag=451][marc:subfield[@code='a']]|marc:datafield[@tag=481][marc:subfield[@code='z']]">
4580 <xsl:call-template name="variantTypeAttribute"/>
4581 <xsl:call-template name="geographic"/>
4585 match="marc:datafield[@tag=551]|marc:datafield[@tag=581][marc:subfield[@code='z']]">
4587 <xsl:call-template name="relatedTypeAttribute"/>
4588 <!-- <xsl:call-template name="uri"/> -->
4589 <xsl:call-template name="geographic"/>
4592 <xsl:template match="marc:datafield[@tag=580]">
4594 <xsl:call-template name="relatedTypeAttribute"/>
4595 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4599 match="marc:datafield[@tag=751][marc:subfield[@code='z']]|marc:datafield[@tag=781][marc:subfield[@code='z']]">
4601 <xsl:call-template name="relatedTypeAttribute"/>
4602 <xsl:call-template name="geographic"/>
4605 <xsl:template match="marc:datafield[@tag=755]">
4607 <xsl:call-template name="relatedTypeAttribute"/>
4608 <xsl:call-template name="genre"/>
4609 <xsl:call-template name="setAuthority"/>
4610 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4613 <xsl:template match="marc:datafield[@tag=780]">
4615 <xsl:call-template name="relatedTypeAttribute"/>
4616 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4619 <xsl:template match="marc:datafield[@tag=785]">
4621 <xsl:call-template name="relatedTypeAttribute"/>
4622 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4626 <!-- ========== notes ========== -->
4627 <xsl:template match="marc:datafield[667 <= @tag and @tag <= 688]">
4630 <xsl:when test="@tag=667">
4631 <xsl:attribute name="type">nonpublic</xsl:attribute>
4633 <xsl:when test="@tag=670">
4634 <xsl:attribute name="type">source</xsl:attribute>
4636 <xsl:when test="@tag=675">
4637 <xsl:attribute name="type">notFound</xsl:attribute>
4639 <xsl:when test="@tag=678">
4640 <xsl:attribute name="type">history</xsl:attribute>
4642 <xsl:when test="@tag=681">
4643 <xsl:attribute name="type">subject example</xsl:attribute>
4645 <xsl:when test="@tag=682">
4646 <xsl:attribute name="type">deleted heading information</xsl:attribute>
4648 <xsl:when test="@tag=688">
4649 <xsl:attribute name="type">application history</xsl:attribute>
4652 <xsl:call-template name="chopPunctuation">
4653 <xsl:with-param name="chopString">
4655 <xsl:when test="@tag=667 or @tag=675">
4656 <xsl:value-of select="marc:subfield[@code='a']"/>
4658 <xsl:when test="@tag=670 or @tag=678">
4659 <xsl:call-template name="subfieldSelect">
4660 <xsl:with-param name="codes">ab</xsl:with-param>
4661 </xsl:call-template>
4663 <xsl:when test="680 <= @tag and @tag <=688">
4664 <xsl:call-template name="subfieldSelect">
4665 <xsl:with-param name="codes">ai</xsl:with-param>
4666 </xsl:call-template>
4670 </xsl:call-template>
4674 <!-- ========== url ========== -->
4675 <xsl:template match="marc:datafield[@tag=856][marc:subfield[@code='u']]">
4677 <xsl:if test="marc:subfield[@code='z' or @code='3']">
4678 <xsl:attribute name="displayLabel">
4679 <xsl:call-template name="subfieldSelect">
4680 <xsl:with-param name="codes">z3</xsl:with-param>
4681 </xsl:call-template>
4684 <xsl:value-of select="marc:subfield[@code='u']"/>
4688 <xsl:template name="relatedTypeAttribute">
4691 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">
4692 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='a'">
4693 <xsl:attribute name="type">earlier</xsl:attribute>
4695 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='b'">
4696 <xsl:attribute name="type">later</xsl:attribute>
4698 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='t'">
4699 <xsl:attribute name="type">parentOrg</xsl:attribute>
4701 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='g'">
4702 <xsl:attribute name="type">broader</xsl:attribute>
4704 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='h'">
4705 <xsl:attribute name="type">narrower</xsl:attribute>
4707 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='r'">
4708 <xsl:attribute name="type">other</xsl:attribute>
4710 <xsl:if test="contains('fin|', substring(marc:subfield[@code='w'],1,1))">
4711 <xsl:attribute name="type">other</xsl:attribute>
4714 <xsl:when test="@tag=530 or @tag=730">
4715 <xsl:attribute name="type">other</xsl:attribute>
4719 <xsl:attribute name="type">equivalent</xsl:attribute>
4722 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4727 <xsl:template name="variantTypeAttribute">
4730 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">
4731 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='d'">
4732 <xsl:attribute name="type">acronym</xsl:attribute>
4734 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='n'">
4735 <xsl:attribute name="type">other</xsl:attribute>
4737 <xsl:if test="contains('fit', substring(marc:subfield[@code='w'],1,1))">
4738 <xsl:attribute name="type">other</xsl:attribute>
4743 <xsl:attribute name="type">other</xsl:attribute>
4746 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4749 <xsl:template name="setAuthority">
4751 <!-- can be called from the datafield or subfield level, so "..//@tag" means
4752 the tag can be at the subfield's parent level or at the datafields own level -->
4755 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='a'">
4756 <xsl:attribute name="authority">
4757 <xsl:text>naf</xsl:text>
4761 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='b'">
4762 <xsl:attribute name="authority">
4763 <xsl:text>lcsh</xsl:text>
4767 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='k'">
4768 <xsl:attribute name="authority">
4769 <xsl:text>lacnaf</xsl:text>
4773 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='a' and $controlField008-14='b'">
4774 <xsl:attribute name="authority">
4775 <xsl:text>lcsh</xsl:text>
4779 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='k' and $controlField008-14='b'">
4780 <xsl:attribute name="authority">cash</xsl:attribute>
4783 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='a'">
4784 <xsl:attribute name="authority">naf</xsl:attribute>
4787 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='b'">
4788 <xsl:attribute name="authority">lcsh</xsl:attribute>
4791 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='a'">
4792 <xsl:attribute name="authority">
4793 <xsl:text>lacnaf</xsl:text>
4797 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='b'">
4798 <xsl:attribute name="authority">
4799 <xsl:text>cash</xsl:text>
4803 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='b'">
4804 <xsl:attribute name="authority">
4805 <xsl:text>lcshcl</xsl:text>
4809 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'">
4810 <xsl:attribute name="authority">
4811 <xsl:text>nlmnaf</xsl:text>
4815 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'">
4816 <xsl:attribute name="authority">
4817 <xsl:text>nalnaf</xsl:text>
4821 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='r'">
4822 <xsl:attribute name="authority">
4823 <xsl:text>aat</xsl:text>
4827 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='s'">
4828 <xsl:attribute name="authority">sears</xsl:attribute>
4831 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='v'">
4832 <xsl:attribute name="authority">rvm</xsl:attribute>
4835 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='z'">
4836 <xsl:attribute name="authority">
4838 select="../marc:datafield[ancestor-or-self::marc:datafield/@tag=040]/marc:subfield[@code='f']"
4843 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='a'">
4844 <xsl:attribute name="authority">
4845 <xsl:text>naf</xsl:text>
4849 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='b'">
4850 <xsl:attribute name="authority">
4851 <xsl:text>lcsh</xsl:text>
4855 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='k' ">
4856 <xsl:attribute name="authority">
4857 <xsl:text>lacnaf</xsl:text>
4861 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' ">
4862 <xsl:attribute name="authority">
4863 <xsl:text>lcsh</xsl:text>
4867 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' ">
4868 <xsl:attribute name="authority">
4869 <xsl:text>lcsh</xsl:text>
4873 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' ">
4874 <xsl:attribute name="authority">
4875 <xsl:text>mesh</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='d' ">
4880 <xsl:attribute name="authority">
4881 <xsl:text>nal</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='k' ">
4886 <xsl:attribute name="authority">
4887 <xsl:text>cash</xsl:text>
4891 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='a'">
4892 <xsl:attribute name="authority">
4893 <xsl:text>naf</xsl:text>
4897 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='b'">
4898 <xsl:attribute name="authority">lcsh</xsl:attribute>
4901 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='a'">
4902 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4905 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='b'">
4906 <xsl:attribute name="authority">cash</xsl:attribute>
4909 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'">
4910 <xsl:attribute name="authority">lcsh</xsl:attribute>
4913 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='0'">
4914 <xsl:attribute name="authority">naf</xsl:attribute>
4917 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='5'">
4918 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4920 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='0'">
4921 <xsl:attribute name="authority">lcsh</xsl:attribute>
4923 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='5'">
4924 <xsl:attribute name="authority">cash</xsl:attribute>
4927 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='1'">
4928 <xsl:attribute name="authority">lcshcl</xsl:attribute>
4931 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'">
4932 <xsl:attribute name="authority">nlmnaf</xsl:attribute>
4935 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'">
4936 <xsl:attribute name="authority">nalnaf</xsl:attribute>
4939 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='6'">
4940 <xsl:attribute name="authority">rvm</xsl:attribute>
4943 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='7'">
4944 <xsl:attribute name="authority">
4945 <xsl:value-of select="marc:subfield[@code='2']"/>
4949 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'">
4950 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4953 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'">
4954 <xsl:attribute name="authority">naf</xsl:attribute>
4957 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'">
4958 <xsl:attribute name="authority">lcsh</xsl:attribute>
4961 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'">
4962 <xsl:attribute name="authority">mesh</xsl:attribute>
4965 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'">
4966 <xsl:attribute name="authority">nal</xsl:attribute>
4969 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'">
4970 <xsl:attribute name="authority">cash</xsl:attribute>
4974 <xsl:template match="*"/>
4975 </xsl:stylesheet>$XSLT$);
4978 SELECT evergreen.upgrade_deps_block_check('1069', :eg_version); --gmcharlt/kmlussier
4980 -- subset of types listed in https://www.loc.gov/marc/authority/ad1xx3xx.html
4981 -- for now, ignoring subdivisions
4982 CREATE TYPE authority.heading_type AS ENUM (
4988 'chronological_term',
4992 'medium_of_performance_term'
4995 CREATE TYPE authority.variant_heading_type AS ENUM (
5004 CREATE TYPE authority.related_heading_type AS ENUM (
5007 'parent organization',
5014 CREATE TYPE authority.heading_purpose AS ENUM (
5020 CREATE TABLE authority.heading_field (
5021 id SERIAL PRIMARY KEY,
5022 heading_type authority.heading_type NOT NULL,
5023 heading_purpose authority.heading_purpose NOT NULL,
5024 label TEXT NOT NULL,
5025 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mads21',
5026 heading_xpath TEXT NOT NULL,
5027 component_xpath TEXT NOT NULL,
5028 type_xpath TEXT NULL, -- to extract related or variant type
5029 thesaurus_xpath TEXT NULL,
5030 thesaurus_override_xpath TEXT NULL,
5034 CREATE TABLE authority.heading_field_norm_map (
5035 id SERIAL PRIMARY KEY,
5036 field INT NOT NULL REFERENCES authority.heading_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
5037 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
5039 pos INT NOT NULL DEFAULT 0
5042 INSERT INTO authority.heading_field(heading_type, heading_purpose, label, heading_xpath, component_xpath, type_xpath, thesaurus_xpath, thesaurus_override_xpath) VALUES
5043 ( 'topical_term', 'main', 'Main Topical Term', '/mads21:mads/mads21:authority', '//mads21:topic', NULL, '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', NULL )
5044 ,( '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')
5045 ,( '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')
5046 ,( 'personal_name', 'main', 'Main Personal Name', '/mads21:mads/mads21:authority', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5047 ,( 'personal_name', 'variant', 'Variant Personal Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5048 ,( 'personal_name', 'related', 'Related Personal Name', '/mads21:mads/mads21:related', '//mads21:name[@type="personal"]', '/mads21:related/@type', NULL, NULL )
5049 ,( 'corporate_name', 'main', 'Main Corporate name', '/mads21:mads/mads21:authority', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5050 ,( 'corporate_name', 'variant', 'Variant Corporate Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5051 ,( 'corporate_name', 'related', 'Related Corporate Name', '/mads21:mads/mads21:related', '//mads21:name[@type="corporate"]', '/mads21:related/@type', NULL, NULL )
5052 ,( 'meeting_name', 'main', 'Main Meeting name', '/mads21:mads/mads21:authority', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5053 ,( 'meeting_name', 'variant', 'Variant Meeting Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5054 ,( 'meeting_name', 'related', 'Related Meeting Name', '/mads21:mads/mads21:related', '//mads21:name[@type="meeting"]', '/mads21:related/@type', NULL, NULL )
5055 ,( 'geographic_name', 'main', 'Main Geographic Term', '/mads21:mads/mads21:authority', '//mads21:geographic', NULL, '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', NULL )
5056 ,( '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')
5057 ,( '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')
5058 ,( 'genre_form_term', 'main', 'Main Genre/Form Term', '/mads21:mads/mads21:authority', '//mads21:genre', NULL, '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', NULL )
5059 ,( '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')
5060 ,( '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')
5061 ,( 'chronological_term', 'main', 'Main Chronological Term', '/mads21:mads/mads21:authority', '//mads21:temporal', NULL, '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', NULL )
5062 ,( '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')
5063 ,( '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')
5064 ,( 'uniform_title', 'main', 'Main Uniform Title', '/mads21:mads/mads21:authority', '//mads21:title', NULL, '/mads21:mads/mads21:authority/mads21:title[1]/@authority', NULL )
5065 ,( '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')
5066 ,( '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')
5069 -- NACO normalize all the things
5070 INSERT INTO authority.heading_field_norm_map (field, norm, pos)
5072 FROM authority.heading_field;
5074 CREATE TYPE authority.heading AS (
5076 type authority.heading_type,
5077 purpose authority.heading_purpose,
5078 variant_type authority.variant_heading_type,
5079 related_type authority.related_heading_type,
5082 normalized_heading TEXT
5085 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5087 idx authority.heading_field%ROWTYPE;
5088 xfrm config.xml_transform%ROWTYPE;
5090 transformed_xml TEXT;
5092 heading_node_list TEXT[];
5093 component_node TEXT;
5094 component_node_list TEXT[];
5096 normalized_text TEXT;
5101 base_thesaurus TEXT := NULL;
5102 output_row authority.heading;
5105 -- Loop over the indexing entries
5106 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
5108 output_row.field := idx.id;
5109 output_row.type := idx.heading_type;
5110 output_row.purpose := idx.heading_purpose;
5112 joiner := COALESCE(idx.joiner, ' ');
5114 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
5116 -- See if we can skip the XSLT ... it's expensive
5117 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5118 -- Can't skip the transform
5119 IF xfrm.xslt <> '---' THEN
5120 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
5122 transformed_xml := marc;
5125 prev_xfrm := xfrm.name;
5128 IF idx.thesaurus_xpath IS NOT NULL THEN
5129 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5132 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5134 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
5136 CONTINUE WHEN heading_node !~ E'^\\s*<';
5138 output_row.variant_type := NULL;
5139 output_row.related_type := NULL;
5140 output_row.thesaurus := NULL;
5141 output_row.heading := NULL;
5143 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
5144 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5146 output_row.variant_type := type_value;
5147 EXCEPTION WHEN invalid_text_representation THEN
5148 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
5151 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
5152 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5154 output_row.related_type := type_value;
5155 EXCEPTION WHEN invalid_text_representation THEN
5156 RAISE NOTICE 'Do not recognize related heading type %', type_value;
5160 IF idx.thesaurus_override_xpath IS NOT NULL THEN
5161 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5163 IF output_row.thesaurus IS NULL THEN
5164 output_row.thesaurus := base_thesaurus;
5169 -- now iterate over components of heading
5170 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5171 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
5172 -- XXX much of this should be moved into oils_xpath_string...
5173 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
5174 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
5175 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
5176 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
5180 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
5182 IF raw_text IS NOT NULL THEN
5183 raw_text := raw_text || joiner;
5186 raw_text := COALESCE(raw_text,'') || curr_text;
5189 IF raw_text IS NOT NULL THEN
5190 output_row.heading := raw_text;
5191 normalized_text := raw_text;
5194 SELECT n.func AS func,
5195 n.param_count AS param_count,
5197 FROM config.index_normalizer n
5198 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
5199 WHERE m.field = idx.id
5202 EXECUTE 'SELECT ' || normalizer.func || '(' ||
5203 quote_literal( normalized_text ) ||
5205 WHEN normalizer.param_count > 0
5206 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
5209 ')' INTO normalized_text;
5213 output_row.normalized_heading := normalized_text;
5215 RETURN NEXT output_row;
5221 $func$ LANGUAGE PLPGSQL;
5223 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5225 auth authority.record_entry%ROWTYPE;
5226 output_row authority.heading;
5229 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
5231 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
5233 $func$ LANGUAGE PLPGSQL;
5235 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5237 res authority.simple_heading%ROWTYPE;
5238 acsaf authority.control_set_authority_field%ROWTYPE;
5239 heading_row authority.heading%ROWTYPE;
5250 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
5253 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5255 IF cset IS NULL THEN
5256 SELECT control_set INTO cset
5257 FROM authority.control_set_authority_field
5258 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5262 res.record := auth_id;
5263 res.thesaurus := authority.extract_thesaurus(marcxml);
5265 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5266 res.atag := acsaf.id;
5268 IF acsaf.heading_field IS NULL THEN
5269 tag_used := acsaf.tag;
5270 nfi_used := acsaf.nfi;
5271 joiner_text := COALESCE(acsaf.joiner, ' ');
5273 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
5275 heading_text := COALESCE(
5276 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
5280 IF nfi_used IS NOT NULL THEN
5282 sort_text := SUBSTRING(
5287 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
5299 sort_text := heading_text;
5302 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5303 res.value := heading_text;
5304 res.sort_value := public.naco_normalize(sort_text);
5305 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5311 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
5312 res.value := heading_row.heading;
5313 res.sort_value := heading_row.normalized_heading;
5314 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5322 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5324 ALTER TABLE authority.control_set_authority_field ADD COLUMN heading_field INTEGER REFERENCES authority.heading_field(id);
5326 UPDATE authority.control_set_authority_field acsaf
5327 SET heading_field = ahf.id
5328 FROM authority.heading_field ahf
5331 AND ahf.heading_purpose = 'main'
5332 AND ahf.heading_type = 'personal_name';
5333 UPDATE authority.control_set_authority_field acsaf
5334 SET heading_field = ahf.id
5335 FROM authority.heading_field ahf
5338 AND ahf.heading_purpose = 'variant'
5339 AND ahf.heading_type = 'personal_name';
5340 UPDATE authority.control_set_authority_field acsaf
5341 SET heading_field = ahf.id
5342 FROM authority.heading_field ahf
5345 AND ahf.heading_purpose = 'related'
5346 AND ahf.heading_type = 'personal_name';
5348 UPDATE authority.control_set_authority_field acsaf
5349 SET heading_field = ahf.id
5350 FROM authority.heading_field ahf
5353 AND ahf.heading_purpose = 'main'
5354 AND ahf.heading_type = 'corporate_name';
5355 UPDATE authority.control_set_authority_field acsaf
5356 SET heading_field = ahf.id
5357 FROM authority.heading_field ahf
5360 AND ahf.heading_purpose = 'variant'
5361 AND ahf.heading_type = 'corporate_name';
5362 UPDATE authority.control_set_authority_field acsaf
5363 SET heading_field = ahf.id
5364 FROM authority.heading_field ahf
5367 AND ahf.heading_purpose = 'related'
5368 AND ahf.heading_type = 'corporate_name';
5370 UPDATE authority.control_set_authority_field acsaf
5371 SET heading_field = ahf.id
5372 FROM authority.heading_field ahf
5375 AND ahf.heading_purpose = 'main'
5376 AND ahf.heading_type = 'meeting_name';
5377 UPDATE authority.control_set_authority_field acsaf
5378 SET heading_field = ahf.id
5379 FROM authority.heading_field ahf
5382 AND ahf.heading_purpose = 'variant'
5383 AND ahf.heading_type = 'meeting_name';
5384 UPDATE authority.control_set_authority_field acsaf
5385 SET heading_field = ahf.id
5386 FROM authority.heading_field ahf
5389 AND ahf.heading_purpose = 'related'
5390 AND ahf.heading_type = 'meeting_name';
5392 UPDATE authority.control_set_authority_field acsaf
5393 SET heading_field = ahf.id
5394 FROM authority.heading_field ahf
5397 AND ahf.heading_purpose = 'main'
5398 AND ahf.heading_type = 'uniform_title';
5399 UPDATE authority.control_set_authority_field acsaf
5400 SET heading_field = ahf.id
5401 FROM authority.heading_field ahf
5404 AND ahf.heading_purpose = 'variant'
5405 AND ahf.heading_type = 'uniform_title';
5406 UPDATE authority.control_set_authority_field acsaf
5407 SET heading_field = ahf.id
5408 FROM authority.heading_field ahf
5411 AND ahf.heading_purpose = 'related'
5412 AND ahf.heading_type = 'uniform_title';
5414 UPDATE authority.control_set_authority_field acsaf
5415 SET heading_field = ahf.id
5416 FROM authority.heading_field ahf
5419 AND ahf.heading_purpose = 'main'
5420 AND ahf.heading_type = 'topical_term';
5421 UPDATE authority.control_set_authority_field acsaf
5422 SET heading_field = ahf.id
5423 FROM authority.heading_field ahf
5426 AND ahf.heading_purpose = 'variant'
5427 AND ahf.heading_type = 'topical_term';
5428 UPDATE authority.control_set_authority_field acsaf
5429 SET heading_field = ahf.id
5430 FROM authority.heading_field ahf
5433 AND ahf.heading_purpose = 'related'
5434 AND ahf.heading_type = 'topical_term';
5436 UPDATE authority.control_set_authority_field acsaf
5437 SET heading_field = ahf.id
5438 FROM authority.heading_field ahf
5441 AND ahf.heading_purpose = 'main'
5442 AND ahf.heading_type = 'geographic_name';
5443 UPDATE authority.control_set_authority_field acsaf
5444 SET heading_field = ahf.id
5445 FROM authority.heading_field ahf
5448 AND ahf.heading_purpose = 'variant'
5449 AND ahf.heading_type = 'geographic_name';
5450 UPDATE authority.control_set_authority_field acsaf
5451 SET heading_field = ahf.id
5452 FROM authority.heading_field ahf
5455 AND ahf.heading_purpose = 'related'
5456 AND ahf.heading_type = 'geographic_name';
5458 UPDATE authority.control_set_authority_field acsaf
5459 SET heading_field = ahf.id
5460 FROM authority.heading_field ahf
5463 AND ahf.heading_purpose = 'main'
5464 AND ahf.heading_type = 'genre_form_term';
5465 UPDATE authority.control_set_authority_field acsaf
5466 SET heading_field = ahf.id
5467 FROM authority.heading_field ahf
5470 AND ahf.heading_purpose = 'variant'
5471 AND ahf.heading_type = 'genre_form_term';
5472 UPDATE authority.control_set_authority_field acsaf
5473 SET heading_field = ahf.id
5474 FROM authority.heading_field ahf
5477 AND ahf.heading_purpose = 'related'
5478 AND ahf.heading_type = 'genre_form_term';
5481 SELECT evergreen.upgrade_deps_block_check('1070', :eg_version); --miker/gmcharlt/kmlussier
5483 CREATE TRIGGER thes_code_tracking_trigger
5484 AFTER UPDATE ON authority.thesaurus
5485 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
5487 ALTER TABLE authority.thesaurus ADD COLUMN short_code TEXT, ADD COLUMN uri TEXT;
5489 DELETE FROM authority.thesaurus WHERE control_set = 1 AND code NOT IN ('n',' ','|');
5490 UPDATE authority.thesaurus SET short_code = code;
5492 CREATE TEMP TABLE thesauri (code text, uri text, name text, xlate hstore);
5493 COPY thesauri (code, uri, name, xlate) FROM STDIN;
5494 migfg http://id.loc.gov/vocabulary/genreFormSchemes/migfg Moving image genre-form guide
5495 reveal http://id.loc.gov/vocabulary/genreFormSchemes/reveal REVEAL: fiction indexing and genre headings
5496 dct http://id.loc.gov/vocabulary/genreFormSchemes/dct Dublin Core list of resource types
5497 gmgpc http://id.loc.gov/vocabulary/genreFormSchemes/gmgpc Thesaurus for graphic materials: TGM II, Genre and physical characteristic terms
5498 rbgenr http://id.loc.gov/vocabulary/genreFormSchemes/rbgenr Genre terms: a thesaurus for use in rare book and special collections cataloguing
5499 sgp http://id.loc.gov/vocabulary/genreFormSchemes/sgp Svenska genrebeteckningar fr periodika "sv"=>"Svenska genrebeteckningar fr periodika"
5500 estc http://id.loc.gov/vocabulary/genreFormSchemes/estc Eighteenth century short title catalogue, the cataloguing rules. New ed.
5501 ftamc http://id.loc.gov/vocabulary/genreFormSchemes/ftamc Form terms for archival and manuscripts control
5502 alett http://id.loc.gov/vocabulary/genreFormSchemes/alett An alphabetical list of English text types
5503 gtlm http://id.loc.gov/vocabulary/genreFormSchemes/gtlm Genre terms for law materials: a thesaurus
5504 rbprov http://id.loc.gov/vocabulary/genreFormSchemes/rbprov Provenance evidence: a thesaurus for use in rare book and special collections cataloging
5505 rbbin http://id.loc.gov/vocabulary/genreFormSchemes/rbbin Binding terms: a thesaurus for use in rare book and special collections cataloguing
5506 fbg http://id.loc.gov/vocabulary/genreFormSchemes/fbg Films by genre /dd>
5507 isbdmedia http://id.loc.gov/vocabulary/genreFormSchemes/isbdmedia ISBD Area 0 [media]
5508 marccategory http://id.loc.gov/vocabulary/genreFormSchemes/marccategory MARC form category term list
5509 gnd-music http://id.loc.gov/vocabulary/genreFormSchemes/gnd-music Gemeinsame Normdatei: Musikalische Ausgabeform
5510 proysen http://id.loc.gov/vocabulary/genreFormSchemes/proysen Prøysen: emneord for Prøysen-bibliografien
5511 rdacarrier http://id.loc.gov/vocabulary/genreFormSchemes/rdacarrier Term and code list for RDA carrier types
5512 gnd http://id.loc.gov/vocabulary/genreFormSchemes/gnd Gemeinsame Normdatei
5513 cjh http://id.loc.gov/vocabulary/genreFormSchemes/cjh Center for Jewish History thesaurus
5514 rbpri http://id.loc.gov/vocabulary/genreFormSchemes/rbpri Printing & publishing evidence: a thesaurus for use in rare book and special collections cataloging
5515 fgtpcm http://id.loc.gov/vocabulary/genreFormSchemes/fgtpcm Form/genre terms for printed cartoon material
5516 rbpub http://id.loc.gov/vocabulary/genreFormSchemes/rbpub Printing and publishing evidence: a thesaurus for use in rare book and special collections cataloging
5517 gmd http://id.loc.gov/vocabulary/genreFormSchemes/gmd Anglo-American Cataloguing Rules general material designation
5518 rbpap http://id.loc.gov/vocabulary/genreFormSchemes/rbpap Paper terms: a thesaurus for use in rare book and special collections cataloging
5519 rdamedia http://id.loc.gov/vocabulary/genreFormSchemes/rdamedia Term and code list for RDA media types
5520 marcsmd http://id.loc.gov/vocabulary/genreFormSchemes/marcsmd MARC specific material form term list
5521 saogf http://id.loc.gov/vocabulary/genreFormSchemes/saogf Svenska ämnesord - Genre/Form "sv"=>"Svenska ämnesord - Genre/Form"
5522 lcgft http://id.loc.gov/vocabulary/genreFormSchemes/lcgft Library of Congress genre/form terms for library and archival materials
5523 muzeukv http://id.loc.gov/vocabulary/genreFormSchemes/muzeukv MuzeVideo UK DVD and UMD film genre classification
5524 mim http://id.loc.gov/vocabulary/genreFormSchemes/mim Moving image materials: genre terms
5525 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
5526 gnd-content http://id.loc.gov/vocabulary/genreFormSchemes/gnd-content Gemeinsame Normdatei: Beschreibung des Inhalts
5527 bgtchm http://id.loc.gov/vocabulary/genreFormSchemes/bgtchm Basic genre terms for cultural heritage materials
5528 gsafd http://id.loc.gov/vocabulary/genreFormSchemes/gsafd Guidelines on subject access to individual works of fiction, drama, etc
5529 marcform http://id.loc.gov/vocabulary/genreFormSchemes/marcform MARC form of item term list
5530 marcgt http://id.loc.gov/vocabulary/genreFormSchemes/marcgt MARC genre terms
5531 barngf http://id.loc.gov/vocabulary/genreFormSchemes/barngf Svenska ämnesord för barn - Genre/Form "sv"=>"Svenska ämnesord för barn - Genre/Form"
5532 ngl http://id.loc.gov/vocabulary/genreFormSchemes/ngl Newspaper genre list
5533 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"
5534 tgfbne http://id.loc.gov/vocabulary/genreFormSchemes/tgfbne Términos de género/forma de la Biblioteca Nacional de España
5535 nbdbgf http://id.loc.gov/vocabulary/genreFormSchemes/nbdbgf NBD Biblion Genres Fictie
5536 rbtyp http://id.loc.gov/vocabulary/genreFormSchemes/rbtyp Type evidence: a thesaurus for use in rare book and special collections cataloging
5537 radfg http://id.loc.gov/vocabulary/genreFormSchemes/radfg Radio form / genre terms guide
5538 gnd-carrier http://id.loc.gov/vocabulary/genreFormSchemes/gnd-carrier Gemeinsame Normdatei: Datenträgertyp
5539 gatbeg http://id.loc.gov/vocabulary/genreFormSchemes/gatbeg Gattungsbegriffe "de"=>"Gattungsbegriffe"
5540 rdacontent http://id.loc.gov/vocabulary/genreFormSchemes/rdacontent Term and code list for RDA content types
5541 isbdcontent http://id.loc.gov/vocabulary/genreFormSchemes/isbdcontent ISBD Area 0 [content]
5542 nimafc http://id.loc.gov/vocabulary/genreFormSchemes/nimafc NIMA form codes
5543 amg http://id.loc.gov/vocabulary/genreFormSchemes/amg Audiovisual material glossary
5544 local http://id.loc.gov/vocabulary/subjectSchemes/local Locally assigned term
5545 taika http://id.loc.gov/vocabulary/subjectSchemes/taika Taideteollisuuden asiasanasto "fi"=>"Taideteollisuuden asiasanasto"
5546 nasat http://id.loc.gov/vocabulary/subjectSchemes/nasat NASA thesaurus
5547 rswkaf http://id.loc.gov/vocabulary/subjectSchemes/rswkaf Alternativform zum Hauptschlagwort "de"=>"Alternativform zum Hauptschlagwort"
5548 jhpk http://id.loc.gov/vocabulary/subjectSchemes/jhpk JÄ
\99zyk haseÅ
\82 przedmiotowych KABA "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych KABA"
5549 asrcrfcd http://id.loc.gov/vocabulary/subjectSchemes/asrcrfcd Australian Standard Research Classification: Research Fields, Courses and Disciplines (RFCD) classification
5550 bt http://id.loc.gov/vocabulary/subjectSchemes/bt Bioethics thesaurus
5551 lcstt http://id.loc.gov/vocabulary/subjectSchemes/lcstt List of Chinese subject terms
5552 netc http://id.loc.gov/vocabulary/subjectSchemes/netc National Emergency Training Center Thesaurus (NETC)
5553 aat http://id.loc.gov/vocabulary/subjectSchemes/aat Art & architecture thesaurus
5554 bet http://id.loc.gov/vocabulary/subjectSchemes/bet British education thesaurus
5555 ncjt http://id.loc.gov/vocabulary/subjectSchemes/ncjt National criminal justice thesaurus
5556 samisk http://id.loc.gov/vocabulary/subjectSchemes/samisk Sami bibliography "no"=>"Sámi bibliografia = Samisk bibliografi (Norge)"
5557 tips http://id.loc.gov/vocabulary/subjectSchemes/tips Tesauro ISOC de psicologÃa "es"=>"Tesauro ISOC de psicologÃa"
5558 ukslc http://id.loc.gov/vocabulary/subjectSchemes/ukslc UK Standard Library Categories
5559 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"
5560 umitrist http://id.loc.gov/vocabulary/subjectSchemes/umitrist University of Michigan Transportation Research Institute structured thesaurus
5561 wgst http://id.loc.gov/vocabulary/subjectSchemes/wgst Washington GILS Subject Tree
5562 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"
5563 ntids http://id.loc.gov/vocabulary/subjectSchemes/ntids Norske tidsskrifter 1700-1820: emneord "no"=>"Norske tidsskrifter 1700-1820: emneord"
5564 kaa http://id.loc.gov/vocabulary/subjectSchemes/kaa Kasvatusalan asiasanasto "fi"=>"Kasvatusalan asiasanasto"
5565 yso http://id.loc.gov/vocabulary/subjectSchemes/yso YSO - Yleinen suomalainen ontologia "fi"=>"YSO - Yleinen suomalainen ontologia"
5566 gcipmedia http://id.loc.gov/vocabulary/subjectSchemes/gcipmedia GAMECIP - Computer Game Media Formats (GAMECIP (Game Metadata and Citation Project))
5567 inspect http://id.loc.gov/vocabulary/subjectSchemes/inspect INSPEC thesaurus
5568 ordnok http://id.loc.gov/vocabulary/subjectSchemes/ordnok Ordnokkelen: tesaurus for kulturminnevern "no"=>"Ordnokkelen: tesaurus for kulturminnevern"
5569 helecon http://id.loc.gov/vocabulary/subjectSchemes/helecon Asiasanasto HELECON-tietikantoihin "fi"=>"Asiasanasto HELECON-tietikantoihin"
5570 dltlt http://id.loc.gov/vocabulary/subjectSchemes/dltlt Cuddon, J. A. A dictionary of literary terms and literary theory
5571 csapa http://id.loc.gov/vocabulary/subjectSchemes/csapa "Controlled vocabulary" in Pollution abstracts
5572 gtt http://id.loc.gov/vocabulary/subjectSchemes/gtt GOO-trefwoorden thesaurus "nl"=>"GOO-trefwoorden thesaurus"
5573 iescs http://id.loc.gov/vocabulary/subjectSchemes/iescs International energy subject categories and scope
5574 itrt http://id.loc.gov/vocabulary/subjectSchemes/itrt International Thesaurus of Refugee Terminology
5575 sanb http://id.loc.gov/vocabulary/subjectSchemes/sanb South African national bibliography authority file
5576 blmlsh http://id.loc.gov/vocabulary/subjectSchemes/blmlsh British Library - Map library subject headings
5577 bhb http://id.loc.gov/vocabulary/subjectSchemes/bhb Bibliography of the Hebrew Book
5578 csh http://id.loc.gov/vocabulary/subjectSchemes/csh Kapsner, Oliver Leonard. Catholic subject headings
5579 fire http://id.loc.gov/vocabulary/subjectSchemes/fire FireTalk, IFSI thesaurus
5580 jlabsh http://id.loc.gov/vocabulary/subjectSchemes/jlabsh Basic subject headings "ja"=>"Kihon kenmei hyômokuhyô"
5581 udc http://id.loc.gov/vocabulary/subjectSchemes/udc Universal decimal classification
5582 lcshac http://id.loc.gov/vocabulary/subjectSchemes/lcshac Children's subject headings in Library of Congress subject headings: supplementary vocabularies
5583 geonet http://id.loc.gov/vocabulary/subjectSchemes/geonet NGA GEOnet Names Server (GNS)
5584 humord http://id.loc.gov/vocabulary/subjectSchemes/humord HUMORD "no"=>"HUMORD"
5585 no-ubo-mr http://id.loc.gov/vocabulary/subjectSchemes/no-ubo-mr Menneskerettighets-tesaurus "no"=>"Menneskerettighets-tesaurus"
5586 sgce http://id.loc.gov/vocabulary/subjectSchemes/sgce COBISS.SI General List of subject headings (English subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5587 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"
5588 thesoz http://id.loc.gov/vocabulary/subjectSchemes/thesoz Thesaurus for the Social Sciences
5589 asth http://id.loc.gov/vocabulary/subjectSchemes/asth Astronomy thesaurus
5590 muzeukc http://id.loc.gov/vocabulary/subjectSchemes/muzeukc MuzeMusic UK classical music classification
5591 norbok http://id.loc.gov/vocabulary/subjectSchemes/norbok Norbok: emneord i Norsk bokfortegnelse "no"=>"Norbok: emneord i Norsk bokfortegnelse"
5592 masa http://id.loc.gov/vocabulary/subjectSchemes/masa Museoalan asiasanasto "fi"=>"Museoalan asiasanasto"
5593 conorsi http://id.loc.gov/vocabulary/subjectSchemes/conorsi CONOR.SI (name authority file) (Maribor, Slovenia: Institut informacijskih znanosti (IZUM))
5594 eurovocen http://id.loc.gov/vocabulary/subjectSchemes/eurovocen Eurovoc thesaurus (English)
5595 kto http://id.loc.gov/vocabulary/subjectSchemes/kto KTO - Kielitieteen ontologia "fi"=>"KTO - Kielitieteen ontologia"
5596 muzvukci http://id.loc.gov/vocabulary/subjectSchemes/muzvukci MuzeVideo UK contributor index
5597 kaunokki http://id.loc.gov/vocabulary/subjectSchemes/kaunokki Kaunokki: kaunokirjallisuuden asiasanasto "fi"=>"Kaunokki: kaunokirjallisuuden asiasanasto"
5598 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"
5599 psychit http://id.loc.gov/vocabulary/subjectSchemes/psychit Thesaurus of psychological index terms.
5600 tlsh http://id.loc.gov/vocabulary/subjectSchemes/tlsh Subject heading authority list
5601 csalsct http://id.loc.gov/vocabulary/subjectSchemes/csalsct CSA life sciences collection thesaurus
5602 ciesiniv http://id.loc.gov/vocabulary/subjectSchemes/ciesiniv CIESIN indexing vocabulary
5603 ebfem http://id.loc.gov/vocabulary/subjectSchemes/ebfem Encabezamientos bilingües de la Fundación Educativa Ana G. Mendez
5604 mero http://id.loc.gov/vocabulary/subjectSchemes/mero MERO - Merenkulkualan ontologia "fi"=>"MERO - Merenkulkualan ontologia"
5605 mmm http://id.loc.gov/vocabulary/subjectSchemes/mmm "Subject key" in Marxism and the mass media
5606 pascal http://id.loc.gov/vocabulary/subjectSchemes/pascal PASCAL database classification scheme "fr"=>"Base de donneés PASCAL: plan de classement"
5607 chirosh http://id.loc.gov/vocabulary/subjectSchemes/chirosh Chiropractic Subject Headings
5608 cilla http://id.loc.gov/vocabulary/subjectSchemes/cilla Cilla: specialtesaurus för musik "fi"=>"Cilla: specialtesaurus för musik"
5609 aiatsisl http://id.loc.gov/vocabulary/subjectSchemes/aiatsisl AIATSIS language thesaurus
5610 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"
5611 lctgm http://id.loc.gov/vocabulary/subjectSchemes/lctgm Thesaurus for graphic materials: TGM I, Subject terms
5612 muso http://id.loc.gov/vocabulary/subjectSchemes/muso MUSO - Ontologi för musik "fi"=>"MUSO - Ontologi för musik"
5613 blcpss http://id.loc.gov/vocabulary/subjectSchemes/blcpss COMPASS subject authority system
5614 fast http://id.loc.gov/vocabulary/subjectSchemes/fast Faceted application of subject terminology
5615 bisacmt http://id.loc.gov/vocabulary/subjectSchemes/bisacmt BISAC Merchandising Themes
5616 lapponica http://id.loc.gov/vocabulary/subjectSchemes/lapponica Lapponica "fi"=>"Lapponica"
5617 juho http://id.loc.gov/vocabulary/subjectSchemes/juho JUHO - Julkishallinnon ontologia "fi"=>"JUHO - Julkishallinnon ontologia"
5618 idas http://id.loc.gov/vocabulary/subjectSchemes/idas ID-Archivschlüssel "de"=>"ID-Archivschlüssel"
5619 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."
5620 test http://id.loc.gov/vocabulary/subjectSchemes/test Thesaurus of engineering and scientific terms
5621 finmesh http://id.loc.gov/vocabulary/subjectSchemes/finmesh FinMeSH "fi"=>"FinMeSH"
5622 kssbar http://id.loc.gov/vocabulary/subjectSchemes/kssbar Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del "sv"=>"Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del"
5623 kupu http://id.loc.gov/vocabulary/subjectSchemes/kupu Maori Wordnet "mi"=>"He puna kupu"
5624 rpe http://id.loc.gov/vocabulary/subjectSchemes/rpe Rubricator on economics "ru"=>"Rubrikator po ekonomike"
5625 dit http://id.loc.gov/vocabulary/subjectSchemes/dit Defense intelligence thesaurus
5626 she http://id.loc.gov/vocabulary/subjectSchemes/she SHE: subject headings for engineering
5627 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"
5628 msc http://id.loc.gov/vocabulary/subjectSchemes/msc Mathematical subject classification
5629 muzeukn http://id.loc.gov/vocabulary/subjectSchemes/muzeukn MuzeMusic UK non-classical music classification
5630 ipsp http://id.loc.gov/vocabulary/subjectSchemes/ipsp Defense intelligence production schedule.
5631 sthus http://id.loc.gov/vocabulary/subjectSchemes/sthus Subject Taxonomy of the History of U.S. Foreign Relations
5632 poliscit http://id.loc.gov/vocabulary/subjectSchemes/poliscit Political science thesaurus II
5633 qtglit http://id.loc.gov/vocabulary/subjectSchemes/qtglit A queer thesaurus : an international thesaurus of gay and lesbian index terms
5634 unbist http://id.loc.gov/vocabulary/subjectSchemes/unbist UNBIS thesaurus
5635 gcipplatform http://id.loc.gov/vocabulary/subjectSchemes/gcipplatform GAMECIP - Computer Game Platforms (GAMECIP (Game Metadata and Citation Project))
5636 puho http://id.loc.gov/vocabulary/subjectSchemes/puho PUHO - Puolustushallinnon ontologia "fi"=>"PUHO - Puolustushallinnon ontologia"
5637 thub http://id.loc.gov/vocabulary/subjectSchemes/thub Thesaurus de la Universitat de Barcelona "ca"=>"Thesaurus de la Universitat de Barcelona"
5638 ndlsh http://id.loc.gov/vocabulary/subjectSchemes/ndlsh National Diet Library list of subject headings "ja"=>"Koktsu Kokkai Toshokan kenmei hyômokuhyô"
5639 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"
5640 idszbzzh http://id.loc.gov/vocabulary/subjectSchemes/idszbzzh Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung"
5641 unbisn http://id.loc.gov/vocabulary/subjectSchemes/unbisn UNBIS name authority list (New York, NY: Dag Hammarskjld Library, United Nations; : Chadwyck-Healey)
5642 rswk http://id.loc.gov/vocabulary/subjectSchemes/rswk Regeln für den Schlagwortkatalog "de"=>"Regeln für den Schlagwortkatalog"
5643 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"
5644 biccbmc http://id.loc.gov/vocabulary/subjectSchemes/biccbmc BIC Children's Books Marketing Classifications
5645 kulo http://id.loc.gov/vocabulary/subjectSchemes/kulo KULO - Kulttuurien tutkimuksen ontologia "fi"=>"KULO - Kulttuurien tutkimuksen ontologia"
5646 popinte http://id.loc.gov/vocabulary/subjectSchemes/popinte POPIN thesaurus: population multilingual thesaurus
5647 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"
5648 atg http://id.loc.gov/vocabulary/subjectSchemes/atg Agricultural thesaurus and glossary
5649 eflch http://id.loc.gov/vocabulary/subjectSchemes/eflch E4Libraries Category Headings
5650 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"
5651 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"
5652 csahssa http://id.loc.gov/vocabulary/subjectSchemes/csahssa "Controlled vocabulary" in Health and safety science abstracts
5653 sigle http://id.loc.gov/vocabulary/subjectSchemes/sigle SIGLE manual, Part 2, Subject category list
5654 blnpn http://id.loc.gov/vocabulary/subjectSchemes/blnpn British Library newspaper place names
5655 asrctoa http://id.loc.gov/vocabulary/subjectSchemes/asrctoa Australian Standard Research Classification: Type of Activity (TOA) classification
5656 lcdgt http://id.loc.gov/vocabulary/subjectSchemes/lcdgt Library of Congress demographic group term and code List
5657 bokbas http://id.loc.gov/vocabulary/subjectSchemes/bokbas Bokbasen "no"=>"Bokbasen"
5658 gnis http://id.loc.gov/vocabulary/subjectSchemes/gnis Geographic Names Information System (GNIS)
5659 nbiemnfag http://id.loc.gov/vocabulary/subjectSchemes/nbiemnfag NBIs emneordsliste for faglitteratur "no"=>"NBIs emneordsliste for faglitteratur"
5660 nlgaf http://id.loc.gov/vocabulary/subjectSchemes/nlgaf Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn "el"=>"Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn"
5661 bhashe http://id.loc.gov/vocabulary/subjectSchemes/bhashe BHA, Bibliography of the history of art, subject headings/English
5662 tsht http://id.loc.gov/vocabulary/subjectSchemes/tsht Thesaurus of subject headings for television
5663 scbi http://id.loc.gov/vocabulary/subjectSchemes/scbi Soggettario per i cataloghi delle biblioteche italiane "it"=>"Soggettario per i cataloghi delle biblioteche italiane"
5664 valo http://id.loc.gov/vocabulary/subjectSchemes/valo VALO - Fotografiska ontologin "fi"=>"VALO - Fotografiska ontologin"
5665 wpicsh http://id.loc.gov/vocabulary/subjectSchemes/wpicsh WPIC Library thesaurus of subject headings
5666 aktp http://id.loc.gov/vocabulary/subjectSchemes/aktp AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn "el"=>"AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn"
5667 stw http://id.loc.gov/vocabulary/subjectSchemes/stw STW Thesaurus for Economics "de"=>"Standard-Thesaurus Wirtschaft"
5668 mesh http://id.loc.gov/vocabulary/subjectSchemes/mesh Medical subject headings
5669 ica http://id.loc.gov/vocabulary/subjectSchemes/ica Index of Christian art
5670 emnmus http://id.loc.gov/vocabulary/subjectSchemes/emnmus Emneord for musikkdokument i EDB-kataloger "no"=>"Emneord for musikkdokument i EDB-kataloger"
5671 sao http://id.loc.gov/vocabulary/subjectSchemes/sao Svenska ämnesord "sv"=>"Svenska ämnesord"
5672 sgc http://id.loc.gov/vocabulary/subjectSchemes/sgc COBISS.SI General List of subject headings (Slovenian subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5673 bib1814 http://id.loc.gov/vocabulary/subjectSchemes/bib1814 1814-bibliografi: emneord for 1814-bibliografi "no"=>"1814-bibliografi: emneord for 1814-bibliografi"
5674 bjornson http://id.loc.gov/vocabulary/subjectSchemes/bjornson Bjornson: emneord for Bjornsonbibliografien "no"=>"Bjornson: emneord for Bjornsonbibliografien"
5675 liito http://id.loc.gov/vocabulary/subjectSchemes/liito LIITO - Liiketoimintaontologia "fi"=>"LIITO - Liiketoimintaontologia"
5676 apaist http://id.loc.gov/vocabulary/subjectSchemes/apaist APAIS thesaurus: a list of subject terms used in the Australian Public Affairs Information Service
5677 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)
5678 ntcsd http://id.loc.gov/vocabulary/subjectSchemes/ntcsd "National Translations Center secondary descriptors" in National Translation Center primary subject classification and secondary descriptor
5679 scisshl http://id.loc.gov/vocabulary/subjectSchemes/scisshl SCIS subject headings
5680 opms http://id.loc.gov/vocabulary/subjectSchemes/opms Opetusministeriön asiasanasto "fi"=>"Opetusministeriön asiasanasto"
5681 ttka http://id.loc.gov/vocabulary/subjectSchemes/ttka Teologisen tiedekunnan kirjaston asiasanasto "fi"=>"Teologisen tiedekunnan kirjaston asiasanasto"
5682 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
5683 ysa http://id.loc.gov/vocabulary/subjectSchemes/ysa Yleinen suomalainen asiasanasto "fi"=>"Yleinen suomalainen asiasanasto"
5684 kitu http://id.loc.gov/vocabulary/subjectSchemes/kitu Kirjallisuudentutkimuksen asiasanasto "fi"=>"Kirjallisuudentutkimuksen asiasanasto"
5685 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"
5686 aiatsisp http://id.loc.gov/vocabulary/subjectSchemes/aiatsisp AIATSIS place thesaurus
5687 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é"
5688 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"
5689 ated http://id.loc.gov/vocabulary/subjectSchemes/ated Australian Thesaurus of Education Descriptors (ATED)
5690 cabt http://id.loc.gov/vocabulary/subjectSchemes/cabt CAB thesaurus (Slough [England]: Commonwealth Agricultural Bureaux)
5691 kassu http://id.loc.gov/vocabulary/subjectSchemes/kassu Kassu - Kasvien suomenkieliset nimet "fi"=>"Kassu - Kasvien suomenkieliset nimet"
5692 nbdbt http://id.loc.gov/vocabulary/subjectSchemes/nbdbt NBD Biblion Trefwoordenthesaurus "nl"=>"NBD Biblion Trefwoordenthesaurus"
5693 jhpb http://id.loc.gov/vocabulary/subjectSchemes/jhpb JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej"
5694 bidex http://id.loc.gov/vocabulary/subjectSchemes/bidex Bilindex: a bilingual Spanish-English subject heading list
5695 ccsa http://id.loc.gov/vocabulary/subjectSchemes/ccsa Catalogue collectif suisse des affiches "fr"=>"Catalogue collectif suisse des affiches"
5696 noraf http://id.loc.gov/vocabulary/subjectSchemes/noraf Norwegian Authority File
5697 kito http://id.loc.gov/vocabulary/subjectSchemes/kito KITO - Kirjallisuudentutkimuksen ontologia "fi"=>"KITO - Kirjallisuudentutkimuksen ontologia"
5698 tho http://id.loc.gov/vocabulary/subjectSchemes/tho Thesauros HellÄ
\93nikÅ
\8dn Oron "el"=>"Thesauros HellÄ
\93nikÅ
\8dn Oron"
5699 pmont http://id.loc.gov/vocabulary/subjectSchemes/pmont Powerhouse Museum Object Name Thesaurus
5700 ssg http://id.loc.gov/vocabulary/subjectSchemes/ssg Splošni slovenski geslovnik "sl"=>"Splošni slovenski geslovnik"
5701 huc http://id.loc.gov/vocabulary/subjectSchemes/huc U.S. Geological Survey water-supply paper 2294: hydrologic basins unit codes
5702 isis http://id.loc.gov/vocabulary/subjectSchemes/isis "Classification scheme" in Isis
5703 ibsen http://id.loc.gov/vocabulary/subjectSchemes/ibsen Ibsen: emneord for Den internasjonale Ibsen-bibliografien "no"=>"Ibsen: emneord for Den internasjonale Ibsen-bibliografien"
5704 lacnaf http://id.loc.gov/vocabulary/subjectSchemes/lacnaf Library and Archives Canada name authority file
5705 swemesh http://id.loc.gov/vocabulary/subjectSchemes/swemesh Swedish MeSH "sv"=>"Svenska MeSH"
5706 hamsun http://id.loc.gov/vocabulary/subjectSchemes/hamsun Hamsun: emneord for Hamsunbibliografien "no"=>"Hamsun: emneord for Hamsunbibliografien"
5707 qrma http://id.loc.gov/vocabulary/subjectSchemes/qrma List of Arabic subject headings "ar"=>"Qâ'imat ru'ûs al-mawdûât al-'Arabîyah"
5708 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"
5709 ceeus http://id.loc.gov/vocabulary/subjectSchemes/ceeus Counties and equivalent entities of the United States its possessions, and associated areas
5710 taxhs http://id.loc.gov/vocabulary/subjectSchemes/taxhs A taxonomy or human services: a conceptual framework with standardized terminology and definitions for the field
5711 noram http://id.loc.gov/vocabulary/subjectSchemes/noram Noram: emneord for Norsk-amerikansk samling "no"=>"Noram: emneord for Norsk-amerikansk samling"
5712 eurovocfr http://id.loc.gov/vocabulary/subjectSchemes/eurovocfr Eurovoc thesaurus (French)
5713 jurivoc http://id.loc.gov/vocabulary/subjectSchemes/jurivoc JURIVOC
5714 agrifors http://id.loc.gov/vocabulary/subjectSchemes/agrifors AGRIFOREST-sanasto "fi"=>"AGRIFOREST-sanasto"
5715 noubojur http://id.loc.gov/vocabulary/subjectSchemes/noubojur Thesaurus of Law "no"=>"Thesaurus of Law"
5716 pha http://id.loc.gov/vocabulary/subjectSchemes/pha Puolostushallinnon asiasanasto "fi"=>"Puolostushallinnon asiasanasto"
5717 ddcrit http://id.loc.gov/vocabulary/subjectSchemes/ddcrit DDC retrieval and indexing terminology; posting terms with hierarchy and KWOC
5718 mar http://id.loc.gov/vocabulary/subjectSchemes/mar Merenkulun asiasanasto "fi"=>"Merenkulun asiasanasto"
5719 sbt http://id.loc.gov/vocabulary/subjectSchemes/sbt Soggettario Sistema Bibliotecario Ticinese "it"=>"Soggettario Sistema Bibliotecario Ticinese"
5720 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))
5721 kta http://id.loc.gov/vocabulary/subjectSchemes/kta Kielitieteen asiasanasto "fi"=>"Kielitieteen asiasanasto"
5722 snt http://id.loc.gov/vocabulary/subjectSchemes/snt Sexual nomenclature : a thesaurus
5723 francis http://id.loc.gov/vocabulary/subjectSchemes/francis FRANCIS database classification scheme "fr"=>"Base de donneés FRANCIS: plan de classement"
5724 eurovocsl http://id.loc.gov/vocabulary/subjectSchemes/eurovocsl Eurovoc thesaurus "sl"=>"Eurovoc thesaurus"
5725 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"
5726 nlmnaf http://id.loc.gov/vocabulary/subjectSchemes/nlmnaf National Library of Medicine name authority file
5727 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"
5728 sipri http://id.loc.gov/vocabulary/subjectSchemes/sipri SIPRI library thesaurus
5729 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"
5730 tucua http://id.loc.gov/vocabulary/subjectSchemes/tucua Thesaurus for use in college and university archives
5731 pmbok http://id.loc.gov/vocabulary/subjectSchemes/pmbok Guide to the project management body of knowledge (PMBOK Guide)
5732 agrovoc http://id.loc.gov/vocabulary/subjectSchemes/agrovoc AGROVOC multilingual agricultural thesaurus
5733 nal http://id.loc.gov/vocabulary/subjectSchemes/nal National Agricultural Library subject headings
5734 lnmmbr http://id.loc.gov/vocabulary/subjectSchemes/lnmmbr Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas "lt"=>"Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas"
5735 vmj http://id.loc.gov/vocabulary/subjectSchemes/vmj Vedettes-matière jeunesse "fr"=>"Vedettes-matière jeunesse"
5736 ddcut http://id.loc.gov/vocabulary/subjectSchemes/ddcut Dewey Decimal Classification user terms
5737 eks http://id.loc.gov/vocabulary/subjectSchemes/eks Eduskunnan kirjaston asiasanasto "fi"=>"Eduskunnan kirjaston asiasanasto"
5738 wot http://id.loc.gov/vocabulary/subjectSchemes/wot A Women's thesaurus
5739 noubomn http://id.loc.gov/vocabulary/subjectSchemes/noubomn University of Oslo Library Thesaurus of Science "no"=>"University of Oslo Library Thesaurus of Science"
5740 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"
5741 precis http://id.loc.gov/vocabulary/subjectSchemes/precis PRECIS: a manual of concept analysis and subject indexing
5742 cstud http://id.loc.gov/vocabulary/subjectSchemes/cstud Classificatieschema's Bibliotheek TU Delft "nl"=>"Classificatieschema's Bibliotheek TU Delft"
5743 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"
5744 pmt http://id.loc.gov/vocabulary/subjectSchemes/pmt Project management terminology. Newtown Square, PA: Project Management Institute
5745 ericd http://id.loc.gov/vocabulary/subjectSchemes/ericd Thesaurus of ERIC descriptors
5746 rvm http://id.loc.gov/vocabulary/subjectSchemes/rvm Répertoire de vedettes-matière "fr"=>"Répertoire de vedettes-matière"
5747 sfit http://id.loc.gov/vocabulary/subjectSchemes/sfit Svenska filminstitutets tesaurus "sv"=>"Svenska filminstitutets tesaurus"
5748 trtsa http://id.loc.gov/vocabulary/subjectSchemes/trtsa Teatterin ja tanssin asiasanasto "fi"=>"Teatterin ja tanssin asiasanasto"
5749 ulan http://id.loc.gov/vocabulary/subjectSchemes/ulan Union list of artist names
5750 unescot http://id.loc.gov/vocabulary/subjectSchemes/unescot UNESCO thesaurus "fr"=>"Thésaurus de l'UNESCO","es"=>"Tesauro de la UNESCO"
5751 koko http://id.loc.gov/vocabulary/subjectSchemes/koko KOKO-ontologia "fi"=>"KOKO-ontologia"
5752 msh http://id.loc.gov/vocabulary/subjectSchemes/msh Trimboli, T., and Martyn S. Marianist subject headings
5753 trt http://id.loc.gov/vocabulary/subjectSchemes/trt Transportation resource thesaurus
5754 agrovocf http://id.loc.gov/vocabulary/subjectSchemes/agrovocf AGROVOC thésaurus agricole multilingue "fr"=>"AGROVOC thésaurus agricole multilingue"
5755 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"
5756 ddcri http://id.loc.gov/vocabulary/subjectSchemes/ddcri Dewey Decimal Classification Relative Index
5757 est http://id.loc.gov/vocabulary/subjectSchemes/est International energy: subject thesaurus (: International Energy Agency, Energy Technology Data Exchange)
5758 lua http://id.loc.gov/vocabulary/subjectSchemes/lua Liikunnan ja urheilun asiasanasto "fi"=>"Liikunnan ja urheilun asiasanasto"
5759 mipfesd http://id.loc.gov/vocabulary/subjectSchemes/mipfesd Macrothesaurus for information processing in the field of economic and social development
5760 rurkp http://id.loc.gov/vocabulary/subjectSchemes/rurkp Predmetnye rubriki Rossiiskoi knizhnoi palaty "ru"=>"Predmetnye rubriki Rossiiskoi knizhnoi palaty"
5761 albt http://id.loc.gov/vocabulary/subjectSchemes/albt Arbetslivsbibliotekets tesaurus "sv"=>"Arbetslivsbibliotekets tesaurus"
5762 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"
5763 bicssc http://id.loc.gov/vocabulary/subjectSchemes/bicssc BIC standard subject categories
5764 cctf http://id.loc.gov/vocabulary/subjectSchemes/cctf Carto-Canadiana thésaurus - Français "fr"=>"Carto-Canadiana thésaurus - Français"
5765 reo http://id.loc.gov/vocabulary/subjectSchemes/reo Māori Subject Headings thesaurus "mi"=>"Ngā Ūpoko Tukutuku"
5766 icpsr http://id.loc.gov/vocabulary/subjectSchemes/icpsr ICPSR controlled vocabulary system
5767 kao http://id.loc.gov/vocabulary/subjectSchemes/kao KVINNSAM ämnesordsregister "sv"=>"KVINNSAM ämnesordsregister"
5768 asrcseo http://id.loc.gov/vocabulary/subjectSchemes/asrcseo Australian Standard Research Classification: Socio-Economic Objective (SEO) classification
5769 georeft http://id.loc.gov/vocabulary/subjectSchemes/georeft GeoRef thesaurus
5770 cct http://id.loc.gov/vocabulary/subjectSchemes/cct Chinese Classified Thesaurus "zh"=>"Zhong guo fen lei zhu ti ci biao"
5771 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"
5772 musa http://id.loc.gov/vocabulary/subjectSchemes/musa Musiikin asiasanasto: erikoissanasto "fi"=>"Musiikin asiasanasto: erikoissanasto"
5773 ntissc http://id.loc.gov/vocabulary/subjectSchemes/ntissc NTIS subject categories
5774 idszbz http://id.loc.gov/vocabulary/subjectSchemes/idszbz Thesaurus IDS Nebis Zentralbibliothek Zürich "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich"
5775 tlka http://id.loc.gov/vocabulary/subjectSchemes/tlka Investigació, Procés Tècnicn kirjaston asiasanasto "fi"=>"Investigació, Procés Tècnicn kirjaston asiasanasto"
5776 usaidt http://id.loc.gov/vocabulary/subjectSchemes/usaidt USAID thesaurus: Keywords used to index documents included in the USAID Development Experience System.
5777 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"
5778 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"
5779 ntcpsc http://id.loc.gov/vocabulary/subjectSchemes/ntcpsc "National Translations Center primary subject classification" in National Translations Center primary subject classification and secondary descriptors
5780 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"
5781 allars http://id.loc.gov/vocabulary/subjectSchemes/allars Allärs: allmän tesaurus pä svenska "fi"=>"Allärs: allmän tesaurus pä svenska"
5782 ogst http://id.loc.gov/vocabulary/subjectSchemes/ogst Oregon GILS Subject Tree (Oregon: Oregon State Library and Oregon Information Resource Management Division (IRMD))
5783 bella http://id.loc.gov/vocabulary/subjectSchemes/bella Bella: specialtesaurus för skönlitteratur "fi"=>"Bella: specialtesaurus för skönlitteratur"
5784 bibalex http://id.loc.gov/vocabulary/subjectSchemes/bibalex Bibliotheca Alexandrina name and subject authority file
5785 pepp http://id.loc.gov/vocabulary/subjectSchemes/pepp The Princeton encyclopedia of poetry and poetics
5786 hkcan http://id.loc.gov/vocabulary/subjectSchemes/hkcan Hong Kong Chinese Authority File (Name) - HKCAN
5787 dissao http://id.loc.gov/vocabulary/subjectSchemes/dissao "Dissertation abstracts online" in Search tools: the guide to UNI/Data Courier Online
5788 ltcsh http://id.loc.gov/vocabulary/subjectSchemes/ltcsh Land Tenure Center Library list of subject headings
5789 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"
5790 asft http://id.loc.gov/vocabulary/subjectSchemes/asft Aquatic sciences and fisheries thesaurus
5791 naf http://id.loc.gov/vocabulary/subjectSchemes/naf NACO authority file
5792 nimacsc http://id.loc.gov/vocabulary/subjectSchemes/nimacsc NIMA cartographic subject categories
5793 khib http://id.loc.gov/vocabulary/subjectSchemes/khib Emneord, KHiB Biblioteket "no"=>"Emneord, KHiB Biblioteket"
5794 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"
5795 afset http://id.loc.gov/vocabulary/subjectSchemes/afset American Folklore Society Ethnographic Thesaurus
5796 erfemn http://id.loc.gov/vocabulary/subjectSchemes/erfemn Erfaringskompetanses emneord "no"=>"Erfaringskompetanses emneord"
5797 sbiao http://id.loc.gov/vocabulary/subjectSchemes/sbiao Svenska barnboksinstitutets ämnesordslista "sv"=>"Svenska barnboksinstitutets ämnesordslista"
5798 socio http://id.loc.gov/vocabulary/subjectSchemes/socio Sociological Abstracts Thesaurus
5799 bisacrt http://id.loc.gov/vocabulary/subjectSchemes/bisacrt BISAC Regional Themes
5800 eum http://id.loc.gov/vocabulary/subjectSchemes/eum Eesti uldine märksonastik "et"=>"Eesti uldine märksonastik"
5801 kula http://id.loc.gov/vocabulary/subjectSchemes/kula Kulttuurien tutkimuksen asiasanasto "fi"=>"Kulttuurien tutkimuksen asiasanasto"
5802 odlt http://id.loc.gov/vocabulary/subjectSchemes/odlt Baldick, C. The Oxford dictionary of literary terms
5803 rerovoc http://id.loc.gov/vocabulary/subjectSchemes/rerovoc Indexation matiéres RERO autoritès "fr"=>"Indexation matiéres RERO autoritès"
5804 tsr http://id.loc.gov/vocabulary/subjectSchemes/tsr TSR-ontologia "fi"=>"TSR-ontologia"
5805 czmesh http://id.loc.gov/vocabulary/subjectSchemes/czmesh Czech MeSH "cs"=>"Czech MeSH"
5806 dltt http://id.loc.gov/vocabulary/subjectSchemes/dltt Quinn, E. A dictionary of literary and thematic terms
5807 idsbb http://id.loc.gov/vocabulary/subjectSchemes/idsbb Thesaurus IDS Basel Bern "de"=>"Thesaurus IDS Basel Bern"
5808 inist http://id.loc.gov/vocabulary/subjectSchemes/inist INIS: thesaurus
5809 idszbzzk http://id.loc.gov/vocabulary/subjectSchemes/idszbzzk Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung"
5810 tesa http://id.loc.gov/vocabulary/subjectSchemes/tesa Tesauro AgrÃcola "es"=>"Tesauro AgrÃcola"
5811 liv http://id.loc.gov/vocabulary/subjectSchemes/liv Legislative indexing vocabulary
5812 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"
5813 nsbncf http://id.loc.gov/vocabulary/subjectSchemes/nsbncf Nuovo Soggettario "it"=>"Nuovo Soggettario"
5814 ipat http://id.loc.gov/vocabulary/subjectSchemes/ipat IPA thesaurus and frequency list
5815 skon http://id.loc.gov/vocabulary/subjectSchemes/skon Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur "sv"=>"Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur"
5816 renib http://id.loc.gov/vocabulary/subjectSchemes/renib Renib "es"=>"Renib"
5817 hrvmesh http://id.loc.gov/vocabulary/subjectSchemes/hrvmesh Croatian MeSH / Hrvatski MeSH "no"=>"Croatian MeSH / Hrvatski MeSH"
5818 swd http://id.loc.gov/vocabulary/subjectSchemes/swd Schlagwortnormdatei "de"=>"Schlagwortnormdatei"
5819 aass http://id.loc.gov/vocabulary/subjectSchemes/aass "Asian American Studies Library subject headings" in A Guide for establishing Asian American core collections
5820 cht http://id.loc.gov/vocabulary/subjectSchemes/cht Chicano thesaurus for indexing Chicano materials in Chicano periodical index
5821 galestne http://id.loc.gov/vocabulary/subjectSchemes/galestne Gale Group subject thesaurus and named entity vocabulary
5822 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"
5823 hoidokki http://id.loc.gov/vocabulary/subjectSchemes/hoidokki Hoitotieteellinen asiasanasto
5824 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"
5825 kubikat http://id.loc.gov/vocabulary/subjectSchemes/kubikat kubikat "de"=>"kubikat"
5826 waqaf http://id.loc.gov/vocabulary/subjectSchemes/waqaf Maknas Uloom Al Waqaf "ar"=>"Maknas Uloom Al Waqaf"
5827 hapi http://id.loc.gov/vocabulary/subjectSchemes/hapi HAPI thesaurus and name authority, 1970-2000
5828 drama http://id.loc.gov/vocabulary/subjectSchemes/drama Drama: specialtesaurus för teater och dans
5829 sosa http://id.loc.gov/vocabulary/subjectSchemes/sosa Sociaalialan asiasanasto "fi"=>"Sociaalialan asiasanasto"
5830 ilpt http://id.loc.gov/vocabulary/subjectSchemes/ilpt Index to legal periodicals: thesaurus
5831 nicem http://id.loc.gov/vocabulary/subjectSchemes/nicem NICEM subject headings and classification system
5832 qlsp http://id.loc.gov/vocabulary/subjectSchemes/qlsp Queens Library Spanish language subject headings
5833 eet http://id.loc.gov/vocabulary/subjectSchemes/eet European education thesaurus
5834 nalnaf http://id.loc.gov/vocabulary/subjectSchemes/nalnaf National Agricultural Library name authority file
5835 eclas http://id.loc.gov/vocabulary/subjectSchemes/eclas ECLAS thesaurus
5836 agrovocs http://id.loc.gov/vocabulary/subjectSchemes/agrovocs AGROVOC tesauro agrÃcola multilingée "es"=>"AGROVOC tesauro agrÃcola multilingée"
5837 shbe http://id.loc.gov/vocabulary/subjectSchemes/shbe Subject headings in business and economics "sv"=>"Subject headings in business and economics"
5838 barn http://id.loc.gov/vocabulary/subjectSchemes/barn Svenska ämnesord för barn "sv"=>"Svenska ämnesord för barn"
5839 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"
5840 gccst http://id.loc.gov/vocabulary/subjectSchemes/gccst Government of Canada core subject thesaurus (Gatineau : Library and Archives Canada)
5841 fnhl http://id.loc.gov/vocabulary/subjectSchemes/fnhl First Nations House of Learning Subject Headings
5842 kauno http://id.loc.gov/vocabulary/subjectSchemes/kauno KAUNO - Kaunokki-ontologin "fi"=>"KAUNO - Kaunokki-ontologin"
5843 dtict http://id.loc.gov/vocabulary/subjectSchemes/dtict Defense Technical Information Center thesaurus
5844 mech http://id.loc.gov/vocabulary/subjectSchemes/mech Iskanje po zbirki MECH "sl"=>"Iskanje po zbirki MECH"
5845 jupo http://id.loc.gov/vocabulary/subjectSchemes/jupo JUPO - Julkisen hallinnon palveluontologia "fi"=>"JUPO - Julkisen hallinnon palveluontologia"
5846 ktpt http://id.loc.gov/vocabulary/subjectSchemes/ktpt Kirjasto- ja tietopalvelualan tesaurus "fi"=>"Kirjasto- ja tietopalvelualan tesaurus"
5847 aiatsiss http://id.loc.gov/vocabulary/subjectSchemes/aiatsiss AIATSIS subject Thesaurus
5848 lcac http://id.loc.gov/vocabulary/subjectSchemes/lcac Library of Congress Annotated Children's Cataloging Program subject headings
5849 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à "
5850 lemb http://id.loc.gov/vocabulary/subjectSchemes/lemb Lista de encabezamientos de materia para bibliotecas "es"=>"Lista de encabezamientos de materia para bibliotecas"
5851 henn http://id.loc.gov/vocabulary/subjectSchemes/henn Hennepin County Library cumulative authority list
5852 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"
5853 cash http://id.loc.gov/vocabulary/subjectSchemes/cash Canadian subject headings
5854 nznb http://id.loc.gov/vocabulary/subjectSchemes/nznb New Zealand national bibliographic
5855 prvt http://id.loc.gov/vocabulary/subjectSchemes/prvt Patent- och registreringsverkets tesaurus "sv"=>"Patent- och registreringsverkets tesaurus"
5856 scgdst http://id.loc.gov/vocabulary/subjectSchemes/scgdst Subject categorization guide for defense science and technology
5857 gem http://id.loc.gov/vocabulary/subjectSchemes/gem GEM controlled vocabularies
5858 lcsh http://id.loc.gov/vocabulary/subjectSchemes/lcsh Library of Congress subject headings
5859 rero http://id.loc.gov/vocabulary/subjectSchemes/rero Indexation matires RERO "fr"=>"Indexation matires RERO"
5860 peri http://id.loc.gov/vocabulary/subjectSchemes/peri Perinnetieteiden asiasanasto "fi"=>"Perinnetieteiden asiasanasto"
5861 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"
5862 slem http://id.loc.gov/vocabulary/subjectSchemes/slem Sears: lista de encabezamientos de materia "es"=>"Sears: lista de encabezamientos de materia"
5863 afo http://id.loc.gov/vocabulary/subjectSchemes/afo AFO - Viikin kampuskirjaston ontologia "fi"=>"AFO - Viikin kampuskirjaston ontologia"
5864 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
5865 hlasstg http://id.loc.gov/vocabulary/subjectSchemes/hlasstg HLAS subject term glossary
5866 iest http://id.loc.gov/vocabulary/subjectSchemes/iest International energy: subject thesaurus
5867 pkk http://id.loc.gov/vocabulary/subjectSchemes/pkk Predmetnik za katoliške knjižnice "sl"=>"Predmetnik za katoliške knjižnice"
5868 atla http://id.loc.gov/vocabulary/subjectSchemes/atla Religion indexes: thesaurus
5869 scot http://id.loc.gov/vocabulary/subjectSchemes/scot Schools Online Thesaurus (ScOT)
5870 smda http://id.loc.gov/vocabulary/subjectSchemes/smda Smithsonian National Air and Space Museum Directory of Airplanes
5871 solstad http://id.loc.gov/vocabulary/subjectSchemes/solstad Solstad: emneord for Solstadbibliografien "no"=>"Solstad: emneord for Solstadbibliografien"
5872 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"
5873 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"
5874 ktta http://id.loc.gov/vocabulary/subjectSchemes/ktta Käsi - ja taideteollisuuden asiasanasto "fi"=>"Käsi - ja taideteollisuuden asiasanasto"
5875 ccte http://id.loc.gov/vocabulary/subjectSchemes/ccte Carto-Canadiana thesaurus - English
5876 pmcsg http://id.loc.gov/vocabulary/subjectSchemes/pmcsg Combined standards glossary
5877 bisacsh http://id.loc.gov/vocabulary/subjectSchemes/bisacsh BISAC Subject Headings
5878 fssh http://id.loc.gov/vocabulary/subjectSchemes/fssh FamilySearch Subject Headings (FamilySearch)
5879 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"
5880 tero http://id.loc.gov/vocabulary/subjectSchemes/tero TERO - Terveyden ja hyvinvoinnin ontologia "fi"=>"TERO - Terveyden ja hyvinvoinnin ontologia"
5881 rma http://id.loc.gov/vocabulary/subjectSchemes/rma Ru'us al-mawdu'at al-'Arabiyah "ar"=>"Ru'us al-mawdu'at al-'Arabiyah"
5882 tgn http://id.loc.gov/vocabulary/subjectSchemes/tgn Getty thesaurus of geographic names
5883 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"
5884 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"
5885 sears http://id.loc.gov/vocabulary/subjectSchemes/sears Sears list of subject headings
5886 csht http://id.loc.gov/vocabulary/subjectSchemes/csht Chinese subject headings
5891 INSERT INTO authority.thesaurus (code, uri, name, control_set)
5892 SELECT code, uri, name, 1 FROM thesauri;
5894 UPDATE authority.thesaurus SET short_code = 'a' WHERE code = 'lcsh';
5895 UPDATE authority.thesaurus SET short_code = 'b' WHERE code = 'lcshac';
5896 UPDATE authority.thesaurus SET short_code = 'c' WHERE code = 'mesh';
5897 UPDATE authority.thesaurus SET short_code = 'd' WHERE code = 'nal';
5898 UPDATE authority.thesaurus SET short_code = 'k' WHERE code = 'cash';
5899 UPDATE authority.thesaurus SET short_code = 'r' WHERE code = 'aat';
5900 UPDATE authority.thesaurus SET short_code = 's' WHERE code = 'sears';
5901 UPDATE authority.thesaurus SET short_code = 'v' WHERE code = 'rvm';
5903 UPDATE authority.thesaurus
5904 SET short_code = 'z'
5905 WHERE short_code IS NULL
5906 AND control_set = 1;
5908 INSERT INTO config.i18n_core (fq_field, identity_value, translation, string )
5909 SELECT 'at.name', t.code, xlate->key, xlate->value
5911 JOIN LATERAL each(t.xlate) AS xlate ON TRUE
5914 FROM config.i18n_core
5915 WHERE fq_field = 'at.name'
5916 AND identity_value = t.code
5917 AND translation = xlate->key)
5918 AND t.xlate IS NOT NULL
5919 AND t.name <> (xlate->value);
5921 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
5925 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
5926 IF thes_code IS NULL THEN
5928 ELSIF thes_code = 'z' THEN
5929 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), 'z' );
5931 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
5933 thes_code := '|'; -- default
5938 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5940 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
5942 IF NEW.control_set IS NULL THEN
5943 SELECT control_set INTO NEW.control_set
5944 FROM authority.thesaurus
5945 WHERE code = authority.extract_thesaurus(NEW.marc);
5950 $func$ LANGUAGE PLPGSQL;
5952 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
5954 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
5955 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
5957 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
5958 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
5959 authority.extract_thesaurus(marc)
5960 FROM authority.record_entry
5964 $func$ LANGUAGE PLPGSQL;
5968 SELECT evergreen.upgrade_deps_block_check('1071', :eg_version); --gmcharlt/kmlussier
5970 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)
5971 RETURNS SETOF metabib.flat_browse_entry_appearance
5980 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
5981 results_skipped INT := 0;
5982 row_counter INT := 0;
5987 all_records BIGINT[];
5988 all_brecords BIGINT[];
5989 all_arecords BIGINT[];
5990 superpage_of_records BIGINT[];
5995 unauthorized_entry RECORD;
5997 IF count_up_from_zero THEN
6004 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
6007 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
6008 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
6010 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
6012 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
6013 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
6015 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
6017 b_tests := b_tests || search.calculate_visibility_attribute_test(
6019 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
6022 b_tests := b_tests || search.calculate_visibility_attribute_test(
6024 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
6028 IF context_locations THEN
6029 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
6030 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
6033 OPEN curs NO SCROLL FOR EXECUTE query;
6036 FETCH curs INTO rec;
6038 IF result_row.pivot_point IS NOT NULL THEN
6039 RETURN NEXT result_row;
6045 SELECT INTO unauthorized_entry *
6046 FROM metabib.browse_entry_simple_heading_map mbeshm
6047 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6048 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
6049 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6050 WHERE mbeshm.entry = rec.id
6051 AND ahf.heading_purpose = 'variant';
6053 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
6054 IF (unauthorized_entry.record IS NOT NULL) THEN
6055 --unauthorized term belongs to an auth linked to a bib?
6056 SELECT INTO all_arecords, result_row.sees, afields
6057 ARRAY_AGG(DISTINCT abl.bib),
6058 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
6059 ARRAY_AGG(DISTINCT map.metabib_field)
6060 FROM authority.bib_linking abl
6061 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6062 map.authority_field = unauthorized_entry.atag
6063 AND map.metabib_field = ANY(fields)
6065 WHERE abl.authority = unauthorized_entry.record;
6067 --do usual procedure
6068 SELECT INTO all_arecords, result_row.sees, afields
6069 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
6070 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
6071 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
6073 FROM metabib.browse_entry_simple_heading_map mbeshm
6074 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6075 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
6076 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
6077 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6078 ash.atag = map.authority_field
6079 AND map.metabib_field = ANY(fields)
6081 JOIN authority.control_set_authority_field acsaf ON (
6082 map.authority_field = acsaf.id
6084 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6085 WHERE mbeshm.entry = rec.id
6086 AND ahf.heading_purpose = 'variant';
6090 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
6091 SELECT INTO all_brecords, result_row.authorities, bfields
6092 ARRAY_AGG(DISTINCT source),
6093 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
6094 ARRAY_AGG(DISTINCT def)
6095 FROM metabib.browse_entry_def_map
6096 WHERE entry = rec.id
6097 AND def = ANY(fields);
6099 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
6101 result_row.sources := 0;
6102 result_row.asources := 0;
6104 -- Bib-linked vis checking
6105 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
6107 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
6108 FROM biblio.record_entry b
6109 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6110 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
6112 acvac.vis_attr_vector @@ c_tests::query_int
6113 OR b.vis_attr_vector @@ b_tests::query_int
6116 result_row.accurate := TRUE;
6120 -- Authority-linked vis checking
6121 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
6123 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
6124 FROM biblio.record_entry b
6125 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6126 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
6128 acvac.vis_attr_vector @@ c_tests::query_int
6129 OR b.vis_attr_vector @@ b_tests::query_int
6132 result_row.aaccurate := TRUE;
6136 IF result_row.sources > 0 OR result_row.asources > 0 THEN
6138 -- The function that calls this function needs row_number in order
6139 -- to correctly order results from two different runs of this
6141 result_row.row_number := row_number;
6143 -- Now, if row_counter is still less than limit, return a row. If
6144 -- not, but it is less than next_pivot_pos, continue on without
6145 -- returning actual result rows until we find
6146 -- that next pivot, and return it.
6148 IF row_counter < result_limit THEN
6149 result_row.browse_entry := rec.id;
6150 result_row.value := rec.value;
6152 RETURN NEXT result_row;
6154 result_row.browse_entry := NULL;
6155 result_row.authorities := NULL;
6156 result_row.fields := NULL;
6157 result_row.value := NULL;
6158 result_row.sources := NULL;
6159 result_row.sees := NULL;
6160 result_row.accurate := NULL;
6161 result_row.aaccurate := NULL;
6162 result_row.pivot_point := rec.id;
6164 IF row_counter >= next_pivot_pos THEN
6165 RETURN NEXT result_row;
6170 IF count_up_from_zero THEN
6171 row_number := row_number + 1;
6173 row_number := row_number - 1;
6176 -- row_counter is different from row_number.
6177 -- It simply counts up from zero so that we know when
6178 -- we've reached our limit.
6179 row_counter := row_counter + 1;
6183 $f$ LANGUAGE plpgsql ROWS 10;
6185 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)
6186 RETURNS SETOF metabib.flat_browse_entry_appearance
6192 pivot_sort_value TEXT;
6193 pivot_sort_fallback TEXT;
6194 context_locations INT[];
6195 browse_superpage_size INT;
6196 results_skipped INT := 0;
6200 forward_to_pivot INT;
6202 -- First, find the pivot if we were given a browse term but not a pivot.
6203 IF pivot_id IS NULL THEN
6204 pivot_id := metabib.browse_pivot(search_field, browse_term);
6207 SELECT INTO pivot_sort_value, pivot_sort_fallback
6208 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
6210 -- Bail if we couldn't find a pivot.
6211 IF pivot_sort_value IS NULL THEN
6215 -- Transform the context_loc_group argument (if any) (logc at the
6216 -- TPAC layer) into a form we'll be able to use.
6217 IF context_loc_group IS NOT NULL THEN
6218 SELECT INTO context_locations ARRAY_AGG(location)
6219 FROM asset.copy_location_group_map
6220 WHERE lgroup = context_loc_group;
6223 -- Get the configured size of browse superpages.
6224 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
6225 FROM config.global_flag
6226 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
6228 -- First we're going to search backward from the pivot, then we're going
6229 -- to search forward. In each direction, we need two limits. At the
6230 -- lesser of the two limits, we delineate the edge of the result set
6231 -- we're going to return. At the greater of the two limits, we find the
6232 -- pivot value that would represent an offset from the current pivot
6233 -- at a distance of one "page" in either direction, where a "page" is a
6234 -- result set of the size specified in the "result_limit" argument.
6236 -- The two limits in each direction make four derived values in total,
6237 -- and we calculate them now.
6238 back_limit := CEIL(result_limit::FLOAT / 2);
6239 back_to_pivot := result_limit;
6240 forward_limit := result_limit / 2;
6241 forward_to_pivot := result_limit - 1;
6243 -- This is the meat of the SQL query that finds browse entries. We'll
6244 -- pass this to a function which uses it with a cursor, so that individual
6245 -- rows may be fetched in a loop until some condition is satisfied, without
6246 -- waiting for a result set of fixed size to be collected all at once.
6251 FROM metabib.browse_entry mbe
6253 EXISTS ( -- are there any bibs using this mbe via the requested fields?
6255 FROM metabib.browse_entry_def_map mbedm
6256 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
6257 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
6259 FROM metabib.browse_entry_simple_heading_map mbeshm
6260 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6261 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6262 ash.atag = map.authority_field
6263 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
6265 JOIN authority.control_set_authority_field acsaf ON (
6266 map.authority_field = acsaf.id
6268 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6269 WHERE mbeshm.entry = mbe.id
6270 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
6271 -- and authority that variant is coming from is linked to a bib
6274 FROM metabib.browse_entry_def_map mbedm2
6275 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
6280 -- This is the variant of the query for browsing backward.
6281 back_query := core_query ||
6282 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
6283 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
6285 -- This variant browses forward.
6286 forward_query := core_query ||
6287 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
6288 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
6290 -- We now call the function which applies a cursor to the provided
6291 -- queries, stopping at the appropriate limits and also giving us
6292 -- the next page's pivot.
6294 SELECT * FROM metabib.staged_browse(
6295 back_query, search_field, context_org, context_locations,
6296 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
6298 SELECT * FROM metabib.staged_browse(
6299 forward_query, search_field, context_org, context_locations,
6300 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
6301 ) ORDER BY row_number DESC;
6304 $f$ LANGUAGE plpgsql ROWS 10;
6307 SELECT evergreen.upgrade_deps_block_check('1072', :eg_version); --gmcharlt/kmlussier
6309 INSERT INTO config.global_flag (name, label, enabled) VALUES (
6310 'opac.show_related_headings_in_browse',
6312 'opac.show_related_headings_in_browse',
6313 'Display related headings (see-also) in browse',
6322 SELECT evergreen.upgrade_deps_block_check('1073', :eg_version);
6324 ALTER TABLE config.metabib_field
6325 ADD COLUMN display_xpath TEXT,
6326 ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
6328 CREATE TABLE config.display_field_map (
6329 name TEXT PRIMARY KEY,
6330 field INTEGER REFERENCES config.metabib_field (id),
6331 multi BOOLEAN DEFAULT FALSE
6334 CREATE TABLE metabib.display_entry (
6335 id BIGSERIAL PRIMARY KEY,
6336 source BIGINT NOT NULL REFERENCES biblio.record_entry (id),
6337 field INT NOT NULL REFERENCES config.metabib_field (id),
6341 CREATE INDEX metabib_display_entry_field_idx ON metabib.display_entry (field);
6342 CREATE INDEX metabib_display_entry_source_idx ON metabib.display_entry (source);
6344 -- one row per display entry fleshed with field info
6345 CREATE VIEW metabib.flat_display_entry AS
6353 FROM metabib.display_entry mde
6354 JOIN config.metabib_field cmf ON (cmf.id = mde.field)
6355 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
6358 -- like flat_display_entry except values are compressed
6359 -- into one row per display_field_map and JSON-ified.
6360 CREATE VIEW metabib.compressed_display_entry AS
6367 CASE WHEN multi THEN
6368 TO_JSON(ARRAY_AGG(value))
6372 FROM metabib.flat_display_entry
6373 GROUP BY 1, 2, 3, 4, 5
6376 -- TODO: expand to encompass all well-known fields
6377 CREATE VIEW metabib.wide_display_entry AS
6380 COALESCE(mcde_title.value, 'null') AS title,
6381 COALESCE(mcde_author.value, 'null') AS author,
6382 COALESCE(mcde_subject.value, 'null') AS subject,
6383 COALESCE(mcde_creators.value, 'null') AS creators,
6384 COALESCE(mcde_isbn.value, 'null') AS isbn
6385 -- ensure one row per bre regardless of any display fields
6386 FROM biblio.record_entry bre
6387 LEFT JOIN metabib.compressed_display_entry mcde_title
6388 ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
6389 LEFT JOIN metabib.compressed_display_entry mcde_author
6390 ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
6391 LEFT JOIN metabib.compressed_display_entry mcde_subject
6392 ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
6393 LEFT JOIN metabib.compressed_display_entry mcde_creators
6394 ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
6395 LEFT JOIN metabib.compressed_display_entry mcde_isbn
6396 ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
6400 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
6401 RETURNS TRIGGER AS $$
6404 display_field_text TEXT;
6406 display_field_text := NEW.value;
6409 SELECT n.func AS func,
6410 n.param_count AS param_count,
6412 FROM config.index_normalizer n
6413 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
6414 WHERE m.field = NEW.field AND m.pos < 0
6417 EXECUTE 'SELECT ' || normalizer.func || '(' ||
6418 quote_literal( display_field_text ) ||
6420 WHEN normalizer.param_count > 0
6421 THEN ',' || REPLACE(REPLACE(BTRIM(
6422 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
6425 ')' INTO display_field_text;
6429 NEW.value = display_field_text;
6433 $$ LANGUAGE PLPGSQL;
6435 CREATE TRIGGER display_field_normalize_tgr
6436 BEFORE UPDATE OR INSERT ON metabib.display_entry
6437 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
6439 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
6440 RETURNS TRIGGER AS $$
6442 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
6445 $$ LANGUAGE PLPGSQL;
6447 CREATE TRIGGER display_field_force_nfc_tgr
6448 BEFORE UPDATE OR INSERT ON metabib.display_entry
6449 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
6451 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE display_field BOOL;
6453 DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL);
6454 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT);
6455 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT, TEXT);
6457 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
6459 default_joiner TEXT,
6462 ) RETURNS SETOF metabib.field_entry_template AS $func$
6464 bib biblio.record_entry%ROWTYPE;
6465 idx config.metabib_field%ROWTYPE;
6466 xfrm config.xml_transform%ROWTYPE;
6468 transformed_xml TEXT;
6470 xml_node_list TEXT[];
6477 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
6478 authority_text TEXT;
6479 authority_link BIGINT;
6480 output_row metabib.field_entry_template%ROWTYPE;
6484 -- Start out with no field-use bools set
6485 output_row.browse_field = FALSE;
6486 output_row.facet_field = FALSE;
6487 output_row.display_field = FALSE;
6488 output_row.search_field = FALSE;
6491 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
6493 -- Loop over the indexing entries
6494 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
6496 process_idx := FALSE;
6497 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
6498 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
6499 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
6500 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
6501 CONTINUE WHEN process_idx = FALSE;
6503 joiner := COALESCE(idx.joiner, default_joiner);
6505 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
6507 -- See if we can skip the XSLT ... it's expensive
6508 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
6509 -- Can't skip the transform
6510 IF xfrm.xslt <> '---' THEN
6511 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
6513 transformed_xml := bib.marc;
6516 prev_xfrm := xfrm.name;
6519 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6522 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
6523 CONTINUE WHEN xml_node !~ E'^\\s*<';
6525 -- XXX much of this should be moved into oils_xpath_string...
6526 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
6527 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
6528 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
6529 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
6533 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
6535 IF raw_text IS NOT NULL THEN
6536 raw_text := raw_text || joiner;
6539 raw_text := COALESCE(raw_text,'') || curr_text;
6541 -- autosuggest/metabib.browse_entry
6542 IF idx.browse_field THEN
6544 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
6545 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6547 browse_text := curr_text;
6550 IF idx.browse_sort_xpath IS NOT NULL AND
6551 idx.browse_sort_xpath <> '' THEN
6553 sort_value := oils_xpath_string(
6554 idx.browse_sort_xpath, xml_node, joiner,
6555 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
6558 sort_value := browse_text;
6561 output_row.field_class = idx.field_class;
6562 output_row.field = idx.id;
6563 output_row.source = rid;
6564 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
6565 output_row.sort_value :=
6566 public.naco_normalize(sort_value);
6568 output_row.authority := NULL;
6570 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
6571 authority_text := oils_xpath_string(
6572 idx.authority_xpath, xml_node, joiner,
6574 ARRAY[xfrm.prefix, xfrm.namespace_uri],
6575 ARRAY['xlink','http://www.w3.org/1999/xlink']
6579 IF authority_text ~ '^\d+$' THEN
6580 authority_link := authority_text::BIGINT;
6581 PERFORM * FROM authority.record_entry WHERE id = authority_link;
6583 output_row.authority := authority_link;
6589 output_row.browse_field = TRUE;
6590 -- Returning browse rows with search_field = true for search+browse
6591 -- configs allows us to retain granularity of being able to search
6592 -- browse fields with "starts with" type operators (for example, for
6593 -- titles of songs in music albums)
6594 IF idx.search_field THEN
6595 output_row.search_field = TRUE;
6597 RETURN NEXT output_row;
6598 output_row.browse_field = FALSE;
6599 output_row.search_field = FALSE;
6600 output_row.sort_value := NULL;
6603 -- insert raw node text for faceting
6604 IF idx.facet_field THEN
6606 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
6607 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6609 facet_text := curr_text;
6612 output_row.field_class = idx.field_class;
6613 output_row.field = -1 * idx.id;
6614 output_row.source = rid;
6615 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
6617 output_row.facet_field = TRUE;
6618 RETURN NEXT output_row;
6619 output_row.facet_field = FALSE;
6622 -- insert raw node text for display
6623 IF idx.display_field THEN
6625 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
6626 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6628 display_text := curr_text;
6631 output_row.field_class = idx.field_class;
6632 output_row.field = -1 * idx.id;
6633 output_row.source = rid;
6634 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
6636 output_row.display_field = TRUE;
6637 RETURN NEXT output_row;
6638 output_row.display_field = FALSE;
6643 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
6645 -- insert combined node text for searching
6646 IF idx.search_field THEN
6647 output_row.field_class = idx.field_class;
6648 output_row.field = idx.id;
6649 output_row.source = rid;
6650 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
6652 output_row.search_field = TRUE;
6653 RETURN NEXT output_row;
6654 output_row.search_field = FALSE;
6661 $func$ LANGUAGE PLPGSQL;
6663 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
6665 skip_facet BOOL DEFAULT FALSE,
6666 skip_display BOOL DEFAULT FALSE,
6667 skip_browse BOOL DEFAULT FALSE,
6668 skip_search BOOL DEFAULT FALSE,
6669 only_fields INT[] DEFAULT '{}'::INT[]
6670 ) RETURNS VOID AS $func$
6673 ind_data metabib.field_entry_template%ROWTYPE;
6674 mbe_row metabib.browse_entry%ROWTYPE;
6677 b_skip_display BOOL;
6681 field_list INT[] := only_fields;
6682 field_types TEXT[] := '{}'::TEXT[];
6685 IF field_list = '{}'::INT[] THEN
6686 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
6689 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;
6690 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;
6691 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;
6692 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;
6694 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
6695 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
6696 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
6697 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
6699 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
6701 IF NOT b_skip_search THEN
6702 FOR fclass IN SELECT * FROM config.metabib_class LOOP
6703 -- RAISE NOTICE 'Emptying out %', fclass.name;
6704 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
6707 IF NOT b_skip_facet THEN
6708 DELETE FROM metabib.facet_entry WHERE source = bib_id;
6710 IF NOT b_skip_display THEN
6711 DELETE FROM metabib.display_entry WHERE source = bib_id;
6713 IF NOT b_skip_browse THEN
6714 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
6718 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
6720 -- don't store what has been normalized away
6721 CONTINUE WHEN ind_data.value IS NULL;
6723 IF ind_data.field < 0 THEN
6724 ind_data.field = -1 * ind_data.field;
6727 IF ind_data.facet_field AND NOT b_skip_facet THEN
6728 INSERT INTO metabib.facet_entry (field, source, value)
6729 VALUES (ind_data.field, ind_data.source, ind_data.value);
6732 IF ind_data.display_field AND NOT b_skip_display THEN
6733 INSERT INTO metabib.display_entry (field, source, value)
6734 VALUES (ind_data.field, ind_data.source, ind_data.value);
6738 IF ind_data.browse_field AND NOT b_skip_browse THEN
6739 -- A caveat about this SELECT: this should take care of replacing
6740 -- old mbe rows when data changes, but not if normalization (by
6741 -- which I mean specifically the output of
6742 -- evergreen.oils_tsearch2()) changes. It may or may not be
6743 -- expensive to add a comparison of index_vector to index_vector
6744 -- to the WHERE clause below.
6746 CONTINUE WHEN ind_data.sort_value IS NULL;
6748 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
6749 SELECT INTO mbe_row * FROM metabib.browse_entry
6750 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
6753 mbe_id := mbe_row.id;
6755 INSERT INTO metabib.browse_entry
6756 ( value, sort_value ) VALUES
6757 ( value_prepped, ind_data.sort_value );
6759 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
6762 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
6763 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
6766 IF ind_data.search_field AND NOT b_skip_search THEN
6767 -- Avoid inserting duplicate rows
6768 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
6769 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
6770 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
6771 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
6772 IF mbe_id IS NULL THEN
6774 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
6776 quote_literal(ind_data.field) || $$, $$ ||
6777 quote_literal(ind_data.source) || $$, $$ ||
6778 quote_literal(ind_data.value) ||
6785 IF NOT b_skip_search THEN
6786 PERFORM metabib.update_combined_index_vectors(bib_id);
6791 $func$ LANGUAGE PLPGSQL;
6793 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
6794 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
6799 IF NEW.deleted THEN -- If this bib is deleted
6801 PERFORM * FROM config.internal_flag WHERE
6802 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
6804 tmp_bool := FOUND; -- Just in case this is changed by some other statement
6806 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
6808 IF NOT tmp_bool THEN
6809 -- One needs to keep these around to support searches
6810 -- with the #deleted modifier, so one should turn on the named
6811 -- internal flag for that functionality.
6812 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
6815 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
6816 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
6817 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
6818 RETURN NEW; -- and we're done
6821 IF TG_OP = 'UPDATE' THEN -- re-ingest?
6822 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
6824 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
6829 -- Record authority linking
6830 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
6832 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
6835 -- Flatten and insert the mfr data
6836 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
6838 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
6840 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
6841 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
6843 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
6847 -- Gather and insert the field entry data
6848 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
6850 -- Located URI magic
6851 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
6852 IF NOT FOUND THEN PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); END IF;
6854 -- (re)map metarecord-bib linking
6855 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
6856 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
6858 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6860 ELSE -- we're doing an update, and we're not deleted, remap
6861 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
6863 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6869 $func$ LANGUAGE PLPGSQL;
6874 SELECT evergreen.upgrade_deps_block_check('1074', :eg_version);
6876 INSERT INTO config.internal_flag (name, enabled)
6877 VALUES ('ingest.skip_display_indexing', FALSE);
6879 -- Adds seed data to replace (for now) values from the 'mvr' class
6881 UPDATE config.metabib_field SET display_field = TRUE WHERE id IN (6, 8, 16, 18);
6883 INSERT INTO config.metabib_field ( id, field_class, name, label,
6884 format, xpath, display_field, display_xpath ) VALUES
6885 (37, 'author', 'creator', oils_i18n_gettext(37, 'All Creators', 'cmf', 'label'),
6886 'mods32', $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']]$$,
6887 TRUE, $$//*[local-name()='namePart']$$ ); -- /* to fool vim */;
6890 UPDATE config.metabib_field SET display_xpath =
6891 $$//*[local-name()='namePart']$$ -- /* to fool vim */
6894 INSERT INTO config.display_field_map (name, field, multi) VALUES
6895 ('title', 6, FALSE),
6896 ('author', 8, FALSE),
6897 ('creators', 37, TRUE),
6898 ('subject', 16, TRUE),
6904 SELECT evergreen.upgrade_deps_block_check('1075', :eg_version);
6906 CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$
6908 IF NEW.imported_as IS NULL THEN
6911 PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as;
6913 RAISE foreign_key_violation USING MESSAGE = FORMAT(
6914 $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as
6919 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
6924 \echo ---------------------------------------------------------------------
6925 \echo Reingest display fields. This can ban canceled via Ctrl-C and run at
6926 \echo a later time with the following (or similar) SQL:
6928 \echo 'SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE, '
6929 \echo ' (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))'
6930 \echo ' FROM biblio.record_entry WHERE NOT deleted AND id > 0;'
6933 -- REINGEST DISPLAY ENTRIES
6934 SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE,
6935 (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))
6936 FROM biblio.record_entry WHERE NOT deleted AND id > 0;