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 /* XXX See LP 1714589 for a likely revision to this view definition XXX */
733 SELECT evergreen.upgrade_deps_block_check('1051', :eg_version);
735 CREATE OR REPLACE VIEW action.all_circulation_slim AS
736 SELECT * FROM action.circulation
771 FROM action.aged_circulation
774 DROP FUNCTION action.summarize_all_circ_chain(INTEGER);
775 DROP FUNCTION action.all_circ_chain(INTEGER);
777 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
778 RETURNS SETOF action.all_circulation_slim AS $$
780 tmp_circ action.all_circulation_slim%ROWTYPE;
781 circ_0 action.all_circulation_slim%ROWTYPE;
784 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
786 IF tmp_circ IS NULL THEN
787 RETURN NEXT tmp_circ;
791 -- find the front of the chain
793 SELECT INTO tmp_circ * FROM action.all_circulation_slim
794 WHERE id = tmp_circ.parent_circ;
795 IF tmp_circ IS NULL THEN
801 -- now send the circs to the caller, oldest to newest
804 IF tmp_circ IS NULL THEN
807 RETURN NEXT tmp_circ;
808 SELECT INTO tmp_circ * FROM action.all_circulation_slim
809 WHERE parent_circ = tmp_circ.id;
813 $$ LANGUAGE 'plpgsql';
815 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
816 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
820 -- first circ in the chain
821 circ_0 action.all_circulation_slim%ROWTYPE;
823 -- last circ in the chain
824 circ_n action.all_circulation_slim%ROWTYPE;
826 -- circ chain under construction
827 chain action.circ_chain_summary;
828 tmp_circ action.all_circulation_slim%ROWTYPE;
832 chain.num_circs := 0;
833 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
835 IF chain.num_circs = 0 THEN
839 chain.num_circs := chain.num_circs + 1;
843 chain.start_time := circ_0.xact_start;
844 chain.last_stop_fines := circ_n.stop_fines;
845 chain.last_stop_fines_time := circ_n.stop_fines_time;
846 chain.last_checkin_time := circ_n.checkin_time;
847 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
848 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
849 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
851 IF chain.num_circs > 1 THEN
852 chain.last_renewal_time := circ_n.xact_start;
853 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
859 $$ LANGUAGE 'plpgsql';
861 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
862 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
864 badge rating.badge_with_orgs%ROWTYPE;
867 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
869 PERFORM rating.precalc_bibs_by_copy(badge_id);
871 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
872 SELECT id FROM precalc_filter_bib_list
874 SELECT id FROM precalc_bibs_by_copy_list
877 ANALYZE precalc_copy_filter_bib_list;
881 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
882 FROM (SELECT cn.record AS bib,
884 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
885 SUM( -- time copy spent circulating
889 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
893 )::NUMERIC AS circ_time
895 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
896 JOIN asset.call_number cn ON (cn.id = cp.call_number)
897 LEFT JOIN action.all_circulation_slim circ ON (
898 circ.target_copy = cp.id
899 AND stop_fines NOT IN (
906 checkin_time IS NULL AND
907 stop_fines = 'MAXFINES'
910 WHERE cn.owning_lib = ANY (badge.orgs)
911 AND cp.active_date IS NOT NULL
912 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
913 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
918 $f$ LANGUAGE PLPGSQL STRICT;
924 SELECT evergreen.upgrade_deps_block_check('1052', :eg_version);
926 CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
927 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
929 badge rating.badge_with_orgs%ROWTYPE;
932 iscale NUMERIC := NULL;
935 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
937 IF badge.horizon_age IS NULL THEN
938 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
943 PERFORM rating.precalc_bibs_by_copy(badge_id);
945 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
946 SELECT id FROM precalc_filter_bib_list
948 SELECT id FROM precalc_bibs_by_copy_list
951 ANALYZE precalc_copy_filter_bib_list;
953 iint := EXTRACT(EPOCH FROM badge.importance_interval);
954 IF badge.importance_age IS NOT NULL THEN
955 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
958 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
959 iscale := COALESCE(badge.importance_scale, 1.0);
963 SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
965 SELECT cn.record AS bib,
966 (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
967 COUNT(u.id)::INT AS uses
968 FROM action.in_house_use u
969 JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
970 JOIN asset.copy cp ON (cp.id = u.item)
971 JOIN asset.call_number cn ON (cn.id = cp.call_number)
972 WHERE u.use_time >= NOW() - badge.horizon_age
973 AND cn.owning_lib = ANY (badge.orgs)
978 $f$ LANGUAGE PLPGSQL STRICT;
980 INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
981 (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);
985 SELECT evergreen.upgrade_deps_block_check('1053', :eg_version);
987 CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
988 RETURNS TABLE (record INT, value NUMERIC) AS $f$
990 badge rating.badge_with_orgs%ROWTYPE;
993 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
995 PERFORM rating.precalc_bibs_by_copy(badge_id);
997 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
998 SELECT id FROM precalc_filter_bib_list
1000 SELECT id FROM precalc_bibs_by_copy_list
1002 ANALYZE precalc_copy_filter_bib_list;
1004 -- Use circ rather than owning lib here as that means "on the shelf at..."
1006 SELECT f.id::INT AS bib,
1007 COUNT(DISTINCT cp.circ_lib)::NUMERIC
1009 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
1010 WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;
1013 $f$ LANGUAGE PLPGSQL STRICT;
1015 INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES
1016 (17,'Circulation Library Count', 'rating.org_unit_count', TRUE);
1020 SELECT evergreen.upgrade_deps_block_check('1054', :eg_version);
1022 INSERT into config.org_unit_setting_type
1023 ( name, grp, label, description, datatype ) VALUES
1025 ( 'lib.timezone', 'lib',
1026 oils_i18n_gettext('lib.timezone',
1027 'Library time zone',
1029 oils_i18n_gettext('lib.timezone',
1030 'Define the time zone in which a library physically resides',
1031 'coust', 'description'),
1034 ALTER TABLE actor.org_unit_closed ADD COLUMN full_day BOOLEAN DEFAULT FALSE;
1035 ALTER TABLE actor.org_unit_closed ADD COLUMN multi_day BOOLEAN DEFAULT FALSE;
1037 UPDATE actor.org_unit_closed SET multi_day = TRUE
1038 WHERE close_start::DATE <> close_end::DATE;
1040 UPDATE actor.org_unit_closed SET full_day = TRUE
1041 WHERE close_start::DATE = close_end::DATE
1042 AND SUBSTRING(close_start::time::text FROM 1 FOR 8) = '00:00:00'
1043 AND SUBSTRING(close_end::time::text FROM 1 FOR 8) = '23:59:59';
1045 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
1047 proper_tz TEXT := COALESCE(
1050 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
1053 CURRENT_SETTING('timezone')
1057 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
1058 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
1059 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
1064 $$ LANGUAGE PLPGSQL;
1067 \qecho The following query will adjust all historical, unaged circulations so
1068 \qecho that if their due date field is pushed to the end of the day, it is done
1069 \qecho in the circulating library''''s time zone, and not the server time zone.
1071 \qecho It is safe to run this after any change to library time zones.
1073 \qecho Running this is not required, as no code before this change has
1074 \qecho depended on the time string of '''23:59:59'''. It is also not necessary
1075 \qecho if all of your libraries are in the same time zone, and that time zone
1076 \qecho is the same as the database''''s configured time zone.
1080 \qecho ' new_tz text;'
1081 \qecho ' ou_id int;'
1083 \qecho ' for ou_id in select id from actor.org_unit loop'
1084 \qecho ' for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('''lib.timezone''',ou_id) loop'
1085 \qecho ' if new_tz is not null then'
1086 \qecho ' update action.circulation'
1087 \qecho ' set due_date = (due_date::timestamp || ''' ''' || new_tz)::timestamptz'
1088 \qecho ' where circ_lib = ou_id'
1089 \qecho ' and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '''23:59:59''';'
1097 SELECT evergreen.upgrade_deps_block_check('1055', :eg_version);
1099 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 $$
1103 picklist_row RECORD;
1108 -- do some initial cleanup
1109 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
1110 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
1111 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
1115 DELETE FROM actor.card where usr = src_usr;
1117 IF deactivate_cards THEN
1118 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
1120 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
1125 DELETE FROM actor.usr_address WHERE usr = src_usr;
1127 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
1130 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
1131 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
1132 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
1133 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
1134 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
1137 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
1138 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
1139 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
1140 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
1145 -- For each *_bucket table: transfer every bucket belonging to src_usr
1146 -- into the custody of dest_usr.
1148 -- In order to avoid colliding with an existing bucket owned by
1149 -- the destination user, append the source user's id (in parenthesese)
1150 -- to the name. If you still get a collision, add successive
1151 -- spaces to the name and keep trying until you succeed.
1155 FROM container.biblio_record_entry_bucket
1156 WHERE owner = src_usr
1158 suffix := ' (' || src_usr || ')';
1161 UPDATE container.biblio_record_entry_bucket
1162 SET owner = dest_usr, name = name || suffix
1163 WHERE id = bucket_row.id;
1164 EXCEPTION WHEN unique_violation THEN
1165 suffix := suffix || ' ';
1174 FROM container.call_number_bucket
1175 WHERE owner = src_usr
1177 suffix := ' (' || src_usr || ')';
1180 UPDATE container.call_number_bucket
1181 SET owner = dest_usr, name = name || suffix
1182 WHERE id = bucket_row.id;
1183 EXCEPTION WHEN unique_violation THEN
1184 suffix := suffix || ' ';
1193 FROM container.copy_bucket
1194 WHERE owner = src_usr
1196 suffix := ' (' || src_usr || ')';
1199 UPDATE container.copy_bucket
1200 SET owner = dest_usr, name = name || suffix
1201 WHERE id = bucket_row.id;
1202 EXCEPTION WHEN unique_violation THEN
1203 suffix := suffix || ' ';
1212 FROM container.user_bucket
1213 WHERE owner = src_usr
1215 suffix := ' (' || src_usr || ')';
1218 UPDATE container.user_bucket
1219 SET owner = dest_usr, name = name || suffix
1220 WHERE id = bucket_row.id;
1221 EXCEPTION WHEN unique_violation THEN
1222 suffix := suffix || ' ';
1229 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
1232 -- transfer queues the same way we transfer buckets (see above)
1236 WHERE owner = src_usr
1238 suffix := ' (' || src_usr || ')';
1241 UPDATE vandelay.queue
1242 SET owner = dest_usr, name = name || suffix
1243 WHERE id = queue_row.id;
1244 EXCEPTION WHEN unique_violation THEN
1245 suffix := suffix || ' ';
1253 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
1254 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
1255 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
1256 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
1257 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
1260 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
1261 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
1262 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
1263 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
1265 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
1266 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
1267 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
1268 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
1270 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
1271 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
1272 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
1273 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
1274 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
1277 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
1278 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
1280 -- transfer picklists the same way we transfer buckets (see above)
1284 WHERE owner = src_usr
1286 suffix := ' (' || src_usr || ')';
1290 SET owner = dest_usr, name = name || suffix
1291 WHERE id = picklist_row.id;
1292 EXCEPTION WHEN unique_violation THEN
1293 suffix := suffix || ' ';
1300 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
1301 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
1302 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
1303 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
1304 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
1305 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
1306 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
1307 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
1310 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
1311 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
1312 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
1313 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
1314 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
1315 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
1318 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
1319 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
1322 -- It's not uncommon to define the reporter schema in a replica
1323 -- DB only, so don't assume these tables exist in the write DB.
1325 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
1326 EXCEPTION WHEN undefined_table THEN
1330 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
1331 EXCEPTION WHEN undefined_table THEN
1335 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
1336 EXCEPTION WHEN undefined_table THEN
1340 -- transfer folders the same way we transfer buckets (see above)
1343 FROM reporter.template_folder
1344 WHERE owner = src_usr
1346 suffix := ' (' || src_usr || ')';
1349 UPDATE reporter.template_folder
1350 SET owner = dest_usr, name = name || suffix
1351 WHERE id = folder_row.id;
1352 EXCEPTION WHEN unique_violation THEN
1353 suffix := suffix || ' ';
1359 EXCEPTION WHEN undefined_table THEN
1363 -- transfer folders the same way we transfer buckets (see above)
1366 FROM reporter.report_folder
1367 WHERE owner = src_usr
1369 suffix := ' (' || src_usr || ')';
1372 UPDATE reporter.report_folder
1373 SET owner = dest_usr, name = name || suffix
1374 WHERE id = folder_row.id;
1375 EXCEPTION WHEN unique_violation THEN
1376 suffix := suffix || ' ';
1382 EXCEPTION WHEN undefined_table THEN
1386 -- transfer folders the same way we transfer buckets (see above)
1389 FROM reporter.output_folder
1390 WHERE owner = src_usr
1392 suffix := ' (' || src_usr || ')';
1395 UPDATE reporter.output_folder
1396 SET owner = dest_usr, name = name || suffix
1397 WHERE id = folder_row.id;
1398 EXCEPTION WHEN unique_violation THEN
1399 suffix := suffix || ' ';
1405 EXCEPTION WHEN undefined_table THEN
1409 -- Finally, delete the source user
1410 DELETE FROM actor.usr WHERE id = src_usr;
1413 $$ LANGUAGE plpgsql;
1419 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1056', :eg_version); -- miker/gmcharlt
1421 INSERT INTO permission.perm_list (id,code,description) VALUES (592,'CONTAINER_BATCH_UPDATE','Allow batch update via buckets');
1423 INSERT INTO container.user_bucket_type (code,label) SELECT code,label FROM container.copy_bucket_type where code = 'staff_client';
1425 CREATE TABLE action.fieldset_group (
1426 id SERIAL PRIMARY KEY,
1428 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1429 complete_time TIMESTAMPTZ,
1430 container INT, -- Points to a container of some type ...
1431 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
1432 can_rollback BOOL DEFAULT TRUE,
1433 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1434 rollback_time TIMESTAMPTZ,
1435 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1436 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1439 ALTER TABLE action.fieldset ADD COLUMN fieldset_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1440 ALTER TABLE action.fieldset ADD COLUMN error_msg TEXT;
1441 ALTER TABLE container.biblio_record_entry_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1442 ALTER TABLE container.user_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1443 ALTER TABLE container.call_number_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1444 ALTER TABLE container.copy_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
1446 UPDATE query.stored_query SET id = id + 1000 WHERE id < 1000;
1447 UPDATE query.from_relation SET id = id + 1000 WHERE id < 1000;
1448 UPDATE query.expression SET id = id + 1000 WHERE id < 1000;
1450 SELECT SETVAL('query.stored_query_id_seq', 1, FALSE);
1451 SELECT SETVAL('query.from_relation_id_seq', 1, FALSE);
1452 SELECT SETVAL('query.expression_id_seq', 1, FALSE);
1454 INSERT INTO query.bind_variable (name,type,description,label)
1455 SELECT 'bucket','number','ID of the bucket to pull items from','Bucket ID'
1456 WHERE NOT EXISTS (SELECT 1 FROM query.bind_variable WHERE name = 'bucket');
1458 -- Assumes completely empty 'query' schema
1459 INSERT INTO query.stored_query (type, use_distinct) VALUES ('SELECT', TRUE); -- 1
1461 INSERT INTO query.from_relation (type, table_name, class_name, table_alias) VALUES ('RELATION', 'container.user_bucket_item', 'cubi', 'cubi'); -- 1
1462 UPDATE query.stored_query SET from_clause = 1;
1464 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'target_user'); -- 1
1465 INSERT INTO query.select_item (stored_query,seq_no,expression) VALUES (1,1,1);
1467 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'bucket'); -- 2
1468 INSERT INTO query.expr_xbind (bind_variable) VALUES ('bucket'); -- 3
1470 INSERT INTO query.expr_xop (left_operand, operator, right_operand) VALUES (2, '=', 3); -- 4
1471 UPDATE query.stored_query SET where_clause = 4;
1473 SELECT SETVAL('query.stored_query_id_seq', 1000, TRUE) FROM query.stored_query;
1474 SELECT SETVAL('query.from_relation_id_seq', 1000, TRUE) FROM query.from_relation;
1475 SELECT SETVAL('query.expression_id_seq', 10000, TRUE) FROM query.expression;
1477 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1478 fieldset_id IN INT, -- id from action.fieldset
1479 table_name IN TEXT, -- table to be updated
1480 pkey_name IN TEXT, -- name of primary key column in that table
1481 query IN TEXT -- query constructed by qstore (for query-based
1482 -- fieldsets only; otherwise null
1498 fs_obj action.fieldset%ROWTYPE;
1499 fs_group action.fieldset_group%ROWTYPE;
1503 IF fieldset_id IS NULL THEN
1504 RETURN 'Fieldset ID parameter is NULL';
1506 IF table_name IS NULL THEN
1507 RETURN 'Table name parameter is NULL';
1509 IF pkey_name IS NULL THEN
1510 RETURN 'Primary key name parameter is NULL';
1515 quote_literal( pkey_value )
1525 -- Build the WHERE clause. This differs according to whether it's a
1526 -- single-row fieldset or a query-based fieldset.
1528 IF query IS NULL AND fs_pkey_value IS NULL THEN
1529 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1530 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1531 fs_query := rtrim( query, ';' );
1532 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1533 || fs_query || ' )';
1534 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1535 where_clause := 'WHERE ' || pkey_name || ' = ';
1536 IF pkey_name = 'id' THEN
1537 where_clause := where_clause || fs_pkey_value;
1538 ELSIF pkey_name = 'code' THEN
1539 where_clause := where_clause || quote_literal(fs_pkey_value);
1541 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1543 ELSE -- both are not null
1544 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1547 IF fs_status IS NULL THEN
1548 RETURN 'No fieldset found for id = ' || fieldset_id;
1549 ELSIF fs_status = 'APPLIED' THEN
1550 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1553 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1554 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1556 IF fs_group.can_rollback THEN
1557 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1559 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1560 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1562 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1564 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1565 IF pkey_name = 'id' THEN
1566 fs_pkey_value := rb_row.id;
1567 ELSIF pkey_name = 'code' THEN
1568 fs_pkey_value := rb_row.code;
1570 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1572 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1573 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);
1575 fs_id := CURRVAL('action.fieldset_id_seq');
1579 FROM action.fieldset_col_val
1580 WHERE fieldset = fieldset_id
1582 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1583 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1588 statement := 'UPDATE ' || table_name || ' SET';
1594 FROM action.fieldset_col_val
1595 WHERE fieldset = fieldset_id
1597 statement := statement || sep || ' ' || cv.col
1598 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1603 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1605 statement := statement || ' ' || where_clause;
1608 -- Execute the update
1612 GET DIAGNOSTICS update_count = ROW_COUNT;
1614 IF update_count = 0 THEN
1615 RAISE data_exception;
1618 IF fsg_id IS NOT NULL THEN
1619 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1622 IF fs_group.id IS NOT NULL THEN
1623 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1626 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1628 EXCEPTION WHEN data_exception THEN
1629 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1630 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1637 EXCEPTION WHEN OTHERS THEN
1638 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1639 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1643 $$ LANGUAGE plpgsql;
1647 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1057', :eg_version); -- miker/gmcharlt/kmlussier
1649 -- Thist change drops a needless join and saves 10-15% in time cost
1650 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
1652 SELECT id, value, count
1654 SELECT mfae.field AS id,
1656 COUNT(DISTINCT mfae.source),
1658 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
1660 FROM metabib.facet_entry mfae
1661 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
1662 WHERE mfae.source = ANY ($2)
1664 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
1669 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
1675 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$
1677 layout unapi.bre_output_layout%ROWTYPE;
1678 transform config.xml_transform%ROWTYPE;
1681 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
1683 element_list TEXT[];
1686 IF org = '-' OR org IS NULL THEN
1687 SELECT shortname INTO org FROM evergreen.org_top();
1690 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1691 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1693 IF layout.name IS NULL THEN
1697 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
1698 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
1700 -- Gather the bib xml
1701 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
1703 IF layout.title_element IS NOT NULL THEN
1704 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
1707 IF layout.description_element IS NOT NULL THEN
1708 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
1711 IF layout.creator_element IS NOT NULL THEN
1712 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
1715 IF layout.update_ts_element IS NOT NULL THEN
1716 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;
1719 IF unapi_url IS NOT NULL THEN
1720 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;
1723 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
1725 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
1726 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
1727 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
1730 RETURN tmp_xml::XML;
1732 $F$ LANGUAGE PLPGSQL STABLE;
1734 CREATE TABLE asset.copy_vis_attr_cache (
1735 id BIGSERIAL PRIMARY KEY,
1736 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
1737 target_copy BIGINT NOT NULL,
1738 vis_attr_vector INT[]
1740 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
1741 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
1743 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
1745 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
1748 WHEN 'luri_org' THEN 0 -- "b" attr
1749 WHEN 'bib_source' THEN 1 -- "b" attr
1751 WHEN 'copy_flags' THEN 0 -- "c" attr
1752 WHEN 'owning_lib' THEN 1 -- "c" attr
1753 WHEN 'circ_lib' THEN 2 -- "c" attr
1754 WHEN 'status' THEN 3 -- "c" attr
1755 WHEN 'location' THEN 4 -- "c" attr
1756 WHEN 'location_group' THEN 5 -- "c" attr
1760 /* copy_flags bit positions, LSB-first:
1762 0: asset.copy.opac_visible
1765 When adding flags, you must update asset.all_visible_flags()
1767 Because bib and copy values are stored separately, we can reuse
1768 shifts, saving us some space. We could probably take back a bit
1769 too, but I'm not sure its worth squeezing that last one out. We'd
1770 be left with just 2 slots for copy attrs, rather than 10.
1773 $f$ LANGUAGE SQL IMMUTABLE;
1775 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
1776 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
1777 $f$ LANGUAGE SQL IMMUTABLE;
1779 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
1780 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
1781 $f$ LANGUAGE SQL IMMUTABLE;
1783 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
1785 copy_row asset.copy%ROWTYPE;
1786 lgroup_map asset.copy_location_group_map%ROWTYPE;
1789 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
1791 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
1792 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
1793 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
1794 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
1796 SELECT ARRAY_APPEND(
1798 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
1800 FROM asset.call_number
1801 WHERE id = copy_row.call_number;
1803 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
1804 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
1809 $f$ LANGUAGE PLPGSQL;
1811 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
1813 bib_row biblio.record_entry%ROWTYPE;
1814 cn_row asset.call_number%ROWTYPE;
1817 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
1819 IF bib_row.source IS NOT NULL THEN
1820 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
1825 FROM asset.call_number cn
1826 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
1827 JOIN asset.uri u ON (u.id = m.uri)
1828 WHERE cn.record = bib_id
1829 AND cn.label = '##URI##'
1832 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
1837 $f$ LANGUAGE PLPGSQL;
1839 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
1841 ocn asset.call_number%ROWTYPE;
1842 ncn asset.call_number%ROWTYPE;
1846 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
1847 IF TG_OP = 'INSERT' THEN
1848 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1851 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
1855 ELSIF TG_OP = 'DELETE' THEN
1856 DELETE FROM asset.copy_vis_attr_cache
1857 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
1863 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
1864 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
1865 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1866 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1869 asset.calculate_copy_visibility_attribute_set(NEW.id)
1871 ELSIF TG_TABLE_NAME = 'record_entry' THEN
1872 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1878 -- handle items first, since with circulation activity
1879 -- their statuses change frequently
1880 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
1882 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1883 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1887 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
1889 IF OLD.deleted <> NEW.deleted THEN
1891 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
1893 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
1896 asset.calculate_copy_visibility_attribute_set(NEW.id)
1901 ELSIF OLD.call_number <> NEW.call_number THEN
1902 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
1904 IF ncn.record <> ocn.record THEN
1905 UPDATE biblio.record_entry
1906 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
1907 WHERE id = ocn.record;
1911 IF OLD.location <> NEW.location OR
1912 OLD.status <> NEW.status OR
1913 OLD.opac_visible <> NEW.opac_visible OR
1914 OLD.circ_lib <> NEW.circ_lib
1916 -- any of these could change visibility, but
1917 -- we'll save some queries and not try to calculate
1918 -- the change directly
1919 UPDATE asset.copy_vis_attr_cache
1920 SET target_copy = NEW.id,
1921 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
1922 WHERE target_copy = OLD.id;
1926 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
1928 IF OLD.record <> NEW.record THEN
1929 IF NEW.label = '##URI##' THEN
1930 UPDATE biblio.record_entry
1931 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1932 WHERE id = OLD.record;
1934 UPDATE biblio.record_entry
1935 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
1936 WHERE id = NEW.record;
1939 UPDATE asset.copy_vis_attr_cache
1940 SET record = NEW.record,
1941 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1942 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1943 AND record = OLD.record;
1945 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
1946 UPDATE asset.copy_vis_attr_cache
1947 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
1948 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
1949 AND record = NEW.record;
1951 IF NEW.label = '##URI##' THEN
1952 UPDATE biblio.record_entry
1953 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
1954 WHERE id = OLD.record;
1958 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
1960 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
1961 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
1963 ELSIF OLD.source <> NEW.source THEN
1964 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
1971 $func$ LANGUAGE PLPGSQL;
1974 -- Helper functions for use in constructing searches --
1976 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
1977 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
1978 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
1979 $f$ LANGUAGE SQL STABLE;
1981 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
1982 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
1985 $f$ LANGUAGE SQL STABLE;
1987 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
1988 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
1990 WHERE NOT opac_visible;
1991 $f$ LANGUAGE SQL STABLE;
1993 -- Bib-oriented defaults for search
1994 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
1995 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
1996 FROM config.bib_source
1998 $f$ LANGUAGE SQL IMMUTABLE;
2000 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
2001 SELECT * FROM asset.invisible_orgs('luri_org');
2002 $f$ LANGUAGE SQL STABLE;
2004 -- Copy-oriented defaults for search
2005 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
2006 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
2007 FROM asset.copy_location_group
2008 WHERE NOT opac_visible;
2009 $f$ LANGUAGE SQL STABLE;
2011 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
2012 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
2013 FROM asset.copy_location
2014 WHERE NOT opac_visible;
2015 $f$ LANGUAGE SQL STABLE;
2017 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
2018 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
2019 FROM config.copy_status
2020 WHERE NOT opac_visible;
2021 $f$ LANGUAGE SQL STABLE;
2023 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
2024 SELECT * FROM asset.invisible_orgs('owning_lib');
2025 $f$ LANGUAGE SQL STABLE;
2027 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
2028 SELECT * FROM asset.invisible_orgs('circ_lib');
2029 $f$ LANGUAGE SQL STABLE;
2031 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
2033 copy_flags TEXT; -- "c" attr
2035 owning_lib TEXT; -- "c" attr
2036 circ_lib TEXT; -- "c" attr
2037 status TEXT; -- "c" attr
2038 location TEXT; -- "c" attr
2039 location_group TEXT; -- "c" attr
2041 luri_org TEXT; -- "b" attr
2042 bib_sources TEXT; -- "b" attr
2044 copy_flags := asset.all_visible_flags(); -- Will always have at least one
2046 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
2048 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
2049 status := NULLIF(asset.status_default(),'!()');
2050 location := NULLIF(asset.location_default(),'!()');
2051 location_group := NULLIF(asset.location_group_default(),'!()');
2053 luri_org := NULLIF(asset.luri_org_default(),'!()');
2054 bib_sources := NULLIF(asset.bib_source_default(),'()');
2057 '('||ARRAY_TO_STRING(
2058 ARRAY[luri_org,bib_sources],
2061 '('||ARRAY_TO_STRING(
2062 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
2066 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
2068 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)
2069 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
2072 prepared_query_texts TEXT[];
2074 plain_query TSQUERY;
2075 opac_visibility_join TEXT;
2076 search_class_join TEXT;
2079 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
2081 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
2082 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
2084 visibility_org := NULLIF(visibility_org,-1);
2085 IF visibility_org IS NOT NULL THEN
2086 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
2088 opac_visibility_join := '';
2090 opac_visibility_join := '
2091 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
2092 JOIN vm ON (acvac.vis_attr_vector @@
2093 (vm.c_attrs || $$&$$ ||
2094 search.calculate_visibility_attribute_test(
2096 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
2103 opac_visibility_join := '';
2106 -- The following determines whether we only provide suggestsons matching
2107 -- the user's selected search_class, or whether we show other suggestions
2108 -- too. The reason for MIN() is that for search_classes like
2109 -- 'title|proper|uniform' you would otherwise get multiple rows. The
2110 -- implication is that if title as a class doesn't have restrict,
2111 -- nor does the proper field, but the uniform field does, you're going
2112 -- to get 'false' for your overall evaluation of 'should we restrict?'
2113 -- To invert that, change from MIN() to MAX().
2117 MIN(cmc.restrict::INT) AS restrict_class,
2118 MIN(cmf.restrict::INT) AS restrict_field
2119 FROM metabib.search_class_to_registered_components(search_class)
2120 AS _registered (field_class TEXT, field INT)
2122 config.metabib_class cmc ON (cmc.name = _registered.field_class)
2124 config.metabib_field cmf ON (cmf.id = _registered.field);
2126 -- evaluate 'should we restrict?'
2127 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
2128 search_class_join := '
2130 metabib.search_class_to_registered_components($2)
2131 AS _registered (field_class TEXT, field INT) ON (
2132 (_registered.field IS NULL AND
2133 _registered.field_class = cmf.field_class) OR
2134 (_registered.field = cmf.id)
2138 search_class_join := '
2140 metabib.search_class_to_registered_components($2)
2141 AS _registered (field_class TEXT, field INT) ON (
2142 _registered.field_class = cmc.name
2147 RETURN QUERY EXECUTE '
2148 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
2149 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
2158 TS_HEADLINE(value, $7, $3)
2159 FROM (SELECT DISTINCT
2162 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
2163 _registered.field = cmf.id AS restrict,
2165 TS_RANK_CD(mbe.index_vector, $1, $6),
2168 FROM metabib.browse_entry_def_map mbedm
2169 JOIN mbe ON (mbe.id = mbedm.entry)
2170 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
2171 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
2172 ' || search_class_join || '
2173 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2175 ' || opac_visibility_join || '
2176 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
2178 ' -- sic, repeat the order by clause in the outer select too
2180 query, search_class, headline_opts,
2181 visibility_org, query_limit, normalization, plain_query
2185 -- buoyant AND chosen class = match class
2186 -- chosen field = match field
2193 $f$ LANGUAGE plpgsql ROWS 10;
2195 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)
2196 RETURNS SETOF metabib.flat_browse_entry_appearance
2202 pivot_sort_value TEXT;
2203 pivot_sort_fallback TEXT;
2204 context_locations INT[];
2205 browse_superpage_size INT;
2206 results_skipped INT := 0;
2210 forward_to_pivot INT;
2212 -- First, find the pivot if we were given a browse term but not a pivot.
2213 IF pivot_id IS NULL THEN
2214 pivot_id := metabib.browse_pivot(search_field, browse_term);
2217 SELECT INTO pivot_sort_value, pivot_sort_fallback
2218 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
2220 -- Bail if we couldn't find a pivot.
2221 IF pivot_sort_value IS NULL THEN
2225 -- Transform the context_loc_group argument (if any) (logc at the
2226 -- TPAC layer) into a form we'll be able to use.
2227 IF context_loc_group IS NOT NULL THEN
2228 SELECT INTO context_locations ARRAY_AGG(location)
2229 FROM asset.copy_location_group_map
2230 WHERE lgroup = context_loc_group;
2233 -- Get the configured size of browse superpages.
2234 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
2235 FROM config.global_flag
2236 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
2238 -- First we're going to search backward from the pivot, then we're going
2239 -- to search forward. In each direction, we need two limits. At the
2240 -- lesser of the two limits, we delineate the edge of the result set
2241 -- we're going to return. At the greater of the two limits, we find the
2242 -- pivot value that would represent an offset from the current pivot
2243 -- at a distance of one "page" in either direction, where a "page" is a
2244 -- result set of the size specified in the "result_limit" argument.
2246 -- The two limits in each direction make four derived values in total,
2247 -- and we calculate them now.
2248 back_limit := CEIL(result_limit::FLOAT / 2);
2249 back_to_pivot := result_limit;
2250 forward_limit := result_limit / 2;
2251 forward_to_pivot := result_limit - 1;
2253 -- This is the meat of the SQL query that finds browse entries. We'll
2254 -- pass this to a function which uses it with a cursor, so that individual
2255 -- rows may be fetched in a loop until some condition is satisfied, without
2256 -- waiting for a result set of fixed size to be collected all at once.
2261 FROM metabib.browse_entry mbe
2263 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2265 FROM metabib.browse_entry_def_map mbedm
2266 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2267 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2269 FROM metabib.browse_entry_simple_heading_map mbeshm
2270 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2271 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2272 ash.atag = map.authority_field
2273 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2275 WHERE mbeshm.entry = mbe.id
2279 -- This is the variant of the query for browsing backward.
2280 back_query := core_query ||
2281 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2282 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
2284 -- This variant browses forward.
2285 forward_query := core_query ||
2286 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2287 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
2289 -- We now call the function which applies a cursor to the provided
2290 -- queries, stopping at the appropriate limits and also giving us
2291 -- the next page's pivot.
2293 SELECT * FROM metabib.staged_browse(
2294 back_query, search_field, context_org, context_locations,
2295 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2297 SELECT * FROM metabib.staged_browse(
2298 forward_query, search_field, context_org, context_locations,
2299 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2300 ) ORDER BY row_number DESC;
2303 $f$ LANGUAGE plpgsql ROWS 10;
2305 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)
2306 RETURNS SETOF metabib.flat_browse_entry_appearance
2315 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2316 results_skipped INT := 0;
2317 row_counter INT := 0;
2322 all_records BIGINT[];
2323 all_brecords BIGINT[];
2324 all_arecords BIGINT[];
2325 superpage_of_records BIGINT[];
2331 IF count_up_from_zero THEN
2338 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
2341 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2342 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
2344 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
2346 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
2347 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
2349 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
2351 b_tests := b_tests || search.calculate_visibility_attribute_test(
2353 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
2356 b_tests := b_tests || search.calculate_visibility_attribute_test(
2358 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
2362 IF context_locations THEN
2363 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
2364 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
2367 OPEN curs NO SCROLL FOR EXECUTE query;
2370 FETCH curs INTO rec;
2372 IF result_row.pivot_point IS NOT NULL THEN
2373 RETURN NEXT result_row;
2378 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2379 SELECT INTO all_arecords, result_row.sees, afields
2380 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2381 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2382 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2384 FROM metabib.browse_entry_simple_heading_map mbeshm
2385 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2386 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2387 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2388 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2389 ash.atag = map.authority_field
2390 AND map.metabib_field = ANY(fields)
2392 WHERE mbeshm.entry = rec.id;
2394 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2395 SELECT INTO all_brecords, result_row.authorities, bfields
2396 ARRAY_AGG(DISTINCT source),
2397 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2398 ARRAY_AGG(DISTINCT def)
2399 FROM metabib.browse_entry_def_map
2400 WHERE entry = rec.id
2401 AND def = ANY(fields);
2403 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2405 result_row.sources := 0;
2406 result_row.asources := 0;
2408 -- Bib-linked vis checking
2409 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2411 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
2412 FROM biblio.record_entry b
2413 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2414 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
2416 acvac.vis_attr_vector @@ c_tests::query_int
2417 OR b.vis_attr_vector @@ b_tests::query_int
2420 result_row.accurate := TRUE;
2424 -- Authority-linked vis checking
2425 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2427 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
2428 FROM biblio.record_entry b
2429 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
2430 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
2432 acvac.vis_attr_vector @@ c_tests::query_int
2433 OR b.vis_attr_vector @@ b_tests::query_int
2436 result_row.aaccurate := TRUE;
2440 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2442 -- The function that calls this function needs row_number in order
2443 -- to correctly order results from two different runs of this
2445 result_row.row_number := row_number;
2447 -- Now, if row_counter is still less than limit, return a row. If
2448 -- not, but it is less than next_pivot_pos, continue on without
2449 -- returning actual result rows until we find
2450 -- that next pivot, and return it.
2452 IF row_counter < result_limit THEN
2453 result_row.browse_entry := rec.id;
2454 result_row.value := rec.value;
2456 RETURN NEXT result_row;
2458 result_row.browse_entry := NULL;
2459 result_row.authorities := NULL;
2460 result_row.fields := NULL;
2461 result_row.value := NULL;
2462 result_row.sources := NULL;
2463 result_row.sees := NULL;
2464 result_row.accurate := NULL;
2465 result_row.aaccurate := NULL;
2466 result_row.pivot_point := rec.id;
2468 IF row_counter >= next_pivot_pos THEN
2469 RETURN NEXT result_row;
2474 IF count_up_from_zero THEN
2475 row_number := row_number + 1;
2477 row_number := row_number - 1;
2480 -- row_counter is different from row_number.
2481 -- It simply counts up from zero so that we know when
2482 -- we've reached our limit.
2483 row_counter := row_counter + 1;
2487 $f$ LANGUAGE plpgsql ROWS 10;
2489 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
2490 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
2491 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
2492 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
2493 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
2494 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
2495 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
2496 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
2498 -- Upgrade the data!
2499 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
2502 asset.calculate_copy_visibility_attribute_set(cp.id)
2504 JOIN asset.call_number cn ON (cp.call_number = cn.id);
2506 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
2508 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();
2509 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();
2510 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2511 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2512 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2513 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2514 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
2516 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$
2521 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;
2523 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
2525 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2526 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2527 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2531 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2537 asset.copy_vis_attr_cache av
2538 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2539 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2543 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2550 $f$ LANGUAGE PLPGSQL;
2552 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$
2557 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;
2559 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2561 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2562 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2563 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2567 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2572 asset.copy_vis_attr_cache av
2573 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
2574 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2578 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2585 $f$ LANGUAGE PLPGSQL;
2587 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$
2592 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;
2594 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
2596 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2597 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2598 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2602 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2608 asset.copy_vis_attr_cache av
2609 JOIN asset.copy cp ON (cp.id = av.target_copy)
2610 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2611 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2615 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2622 $f$ LANGUAGE PLPGSQL;
2624 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$
2629 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;
2631 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
2633 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
2634 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
2635 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
2639 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
2645 asset.copy_vis_attr_cache av
2646 JOIN asset.copy cp ON (cp.id = av.target_copy)
2647 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
2648 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
2652 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
2659 $f$ LANGUAGE PLPGSQL;
2661 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
2667 depth INT DEFAULT NULL,
2668 slimit HSTORE DEFAULT NULL,
2669 soffset HSTORE DEFAULT NULL,
2670 include_xmlns BOOL DEFAULT TRUE,
2671 pref_lib INT DEFAULT NULL
2672 ) RETURNS XML AS $F$
2676 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
2677 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
2679 (SELECT XMLAGG(foo.y)
2681 WITH sourcelist AS (
2682 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
2683 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2684 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
2685 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
2687 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
2688 WHERE mmsm.metarecord = $1 AND (
2691 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
2692 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2693 AND acvac.record = mmsm.source
2695 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
2696 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)
2704 cmra.value AS "coded-value",
2705 cmra.aid AS "cvmid",
2715 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
2717 SELECT v.source AS id,
2721 FROM metabib.record_attr_vector_list v
2722 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
2724 JOIN sourcelist ON (x.id = sourcelist.source)
2727 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
2742 SELECT DISTINCT aid, attr, value
2744 SELECT v.source AS id,
2748 FROM metabib.record_attr_vector_list v
2749 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
2751 JOIN sourcelist ON (x.id = sourcelist.source)
2753 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
2759 $F$ LANGUAGE SQL STABLE;
2761 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
2764 depth INT DEFAULT NULL,
2765 slimit HSTORE DEFAULT NULL,
2766 soffset HSTORE DEFAULT NULL,
2767 pref_lib INT DEFAULT NULL,
2768 includes TEXT[] DEFAULT NULL::TEXT[]
2769 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
2770 WITH RECURSIVE ou_depth AS (
2775 FROM actor.org_unit_type aout
2776 INNER JOIN actor.org_unit ou ON ou_type = aout.id
2780 ), descendant_depth AS (
2784 FROM actor.org_unit ou
2785 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2786 JOIN anscestor_depth ad ON (ad.id = ou.id),
2788 WHERE ad.depth = ou_depth.depth
2793 FROM actor.org_unit ou
2794 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2795 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2796 ), anscestor_depth AS (
2800 FROM actor.org_unit ou
2801 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2807 FROM actor.org_unit ou
2808 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2809 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
2811 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
2814 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
2815 SELECT acn.id, owning_lib.name, acn.label_sortkey,
2816 evergreen.rank_cp(acp),
2818 FROM asset.call_number acn
2819 JOIN asset.copy acp ON (acn.id = acp.call_number)
2820 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
2821 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
2822 WHERE acn.record = ANY ($1)
2823 AND acn.deleted IS FALSE
2824 AND acp.deleted IS FALSE
2825 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
2827 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
2828 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
2830 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
2831 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
2832 AND acvac.target_copy = acp.id
2833 AND acvac.record = acn.record
2835 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
2839 CASE WHEN aou.id = $2 THEN -20000 END,
2840 CASE WHEN aou.id = $6 THEN -10000 END,
2841 (SELECT distance - 5000
2842 FROM actor.org_unit_descendants_distance($6) as x
2843 WHERE x.id = aou.id AND $6 IN (
2844 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
2845 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
2848 evergreen.rank_cp(acp)
2851 GROUP BY ua.id, ua.name, ua.label_sortkey
2852 ORDER BY rank, ua.name, ua.label_sortkey
2853 LIMIT ($4 -> 'acn')::INT
2854 OFFSET ($5 -> 'acn')::INT;
2855 $$ LANGUAGE SQL STABLE ROWS 10;
2858 -- Evergreen DB patch XXXX.schema.action-trigger.event_definition.sms_preminder.sql
2860 -- New action trigger event definition: 3 Day Courtesy Notice by SMS
2863 -- check whether patch can be applied
2864 SELECT evergreen.upgrade_deps_block_check('1058', :eg_version); -- mccanna/csharp/gmcharlt
2866 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook,
2867 validator, reactor, delay, max_delay, delay_field, group_field, template)
2868 VALUES (54, FALSE, 1,
2869 '3 Day Courtesy Notice by SMS',
2871 'CircIsOpen', 'SendSMS', '-3 days', '-2 days', 'due_date', 'usr',
2874 [%- user = target.0.usr -%]
2875 [%- homelib = user.home_ou -%]
2876 [%- sms_number = helpers.get_user_setting(user.id, 'opac.default_sms_notify') -%]
2877 [%- sms_carrier = helpers.get_user_setting(user.id, 'opac.default_sms_carrier') -%]
2878 From: [%- helpers.get_org_setting(homelib.id, 'org.bounced_emails') || homelib.email || params.sender_email || default_sender %]
2879 To: [%- helpers.get_sms_gateway_email(sms_carrier,sms_number) %]
2880 Subject: Library Materials Due Soon
2882 You have items due soon:
2884 [% FOR circ IN target %]
2885 [%- copy_details = helpers.get_copy_bib_basics(circ.target_copy.id) -%]
2886 [% copy_details.title FILTER ucfirst %] by [% copy_details.author FILTER ucfirst %] due on [% date.format(helpers.format_date(circ.due_date), '%m-%d-%Y') %]
2892 INSERT INTO action_trigger.environment (event_def, path) VALUES
2893 (54, 'circ_lib.billing_address'),
2894 (54, 'target_copy.call_number'),
2896 (54, 'usr.home_ou');
2899 -- check whether patch can be applied
2900 SELECT evergreen.upgrade_deps_block_check('1059', :eg_version); --Stompro/DPearl/kmlussier
2902 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
2908 CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title,
2909 FIRST(author.value) AS author,
2910 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
2911 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
2912 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
2914 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
2916 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
2918 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
2920 FROM biblio.record_entry r
2921 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
2922 LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear.
2923 SELECT b.record, string_agg(val, ' ') AS val FROM (
2924 SELECT title_np.record, title_np.value AS val
2925 FROM metabib.full_rec title_np
2927 title_np.tag = '245'
2928 AND title_np.subfield IN ('p','n')
2929 ORDER BY title_np.id
2932 ) title_np ON (title_np.record=r.id)
2933 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
2934 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')
2935 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')
2936 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
2937 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
2941 -- Remove trigger on biblio.record_entry
2942 SELECT reporter.disable_materialized_simple_record_trigger();
2944 -- Rebuild reporter.materialized_simple_record
2945 SELECT reporter.enable_materialized_simple_record_trigger();
2948 SELECT evergreen.upgrade_deps_block_check('1060', :eg_version);
2950 DROP VIEW IF EXISTS extend_reporter.copy_count_per_org;
2953 CREATE OR REPLACE VIEW extend_reporter.copy_count_per_org AS
2954 SELECT acn.record AS bibid,
2957 max(ac.edit_date) AS last_edit_time,
2958 min(ac.deleted::integer) AS has_only_deleted_copies,
2961 WHEN ac.deleted THEN ac.id
2963 END) AS deleted_count,
2966 WHEN NOT ac.deleted THEN ac.id
2968 END) AS visible_count,
2969 count(*) AS total_count
2970 FROM asset.call_number acn,
2972 WHERE ac.call_number = acn.id
2973 GROUP BY acn.record, acn.owning_lib, ac.circ_lib;
2977 SELECT evergreen.upgrade_deps_block_check('1061', :eg_version);
2979 INSERT INTO config.org_unit_setting_type
2980 (name, label, description, grp, datatype)
2982 'ui.staff.max_recent_patrons',
2984 'ui.staff.max_recent_patrons',
2985 'Number of Retrievable Recent Patrons',
2990 'ui.staff.max_recent_patrons',
2991 'Number of most recently accessed patrons that can be re-retrieved ' ||
2992 'in the staff client. A value of 0 or less disables the feature. Defaults to 1.',
3001 SELECT evergreen.upgrade_deps_block_check('1062', :eg_version);
3003 CREATE TABLE acq.edi_attr (
3004 key TEXT PRIMARY KEY,
3005 label TEXT NOT NULL UNIQUE
3008 CREATE TABLE acq.edi_attr_set (
3009 id SERIAL PRIMARY KEY,
3010 label TEXT NOT NULL UNIQUE
3013 CREATE TABLE acq.edi_attr_set_map (
3014 id SERIAL PRIMARY KEY,
3015 attr_set INTEGER NOT NULL REFERENCES acq.edi_attr_set(id)
3016 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3017 attr TEXT NOT NULL REFERENCES acq.edi_attr(key)
3018 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3019 CONSTRAINT edi_attr_set_map_attr_once UNIQUE (attr_set, attr)
3022 -- An attr_set is not strictly required, since some edi_accounts/vendors
3023 -- may not need to apply any attributes.
3024 ALTER TABLE acq.edi_account
3025 ADD COLUMN attr_set INTEGER REFERENCES acq.edi_attr_set(id),
3026 ADD COLUMN use_attrs BOOLEAN NOT NULL DEFAULT FALSE;
3031 SELECT evergreen.upgrade_deps_block_check('1063', :eg_version);
3038 FOR r IN SELECT t.table_schema AS sname,
3039 t.table_name AS tname,
3040 t.column_name AS colname,
3042 FROM information_schema.referential_constraints ref
3043 JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
3044 WHERE ref.unique_constraint_schema = 'asset'
3045 AND ref.unique_constraint_name = 'copy_pkey'
3048 EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
3051 CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
3053 PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
3055 RAISE foreign_key_violation USING MESSAGE = FORMAT(
3056 $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
3061 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
3065 CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
3066 AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
3067 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
3075 SELECT evergreen.upgrade_deps_block_check('1064', :eg_version);
3077 ALTER TABLE serial.issuance DROP CONSTRAINT IF EXISTS issuance_caption_and_pattern_fkey;
3079 -- Using NOT VALID and VALIDATE CONSTRAINT limits the impact to concurrent work.
3080 -- For details, see: https://www.postgresql.org/docs/current/static/sql-altertable.html
3082 ALTER TABLE serial.issuance ADD CONSTRAINT issuance_caption_and_pattern_fkey
3083 FOREIGN KEY (caption_and_pattern)
3084 REFERENCES serial.caption_and_pattern (id)
3086 DEFERRABLE INITIALLY DEFERRED
3089 ALTER TABLE serial.issuance VALIDATE CONSTRAINT issuance_caption_and_pattern_fkey;
3093 SELECT evergreen.upgrade_deps_block_check('1065', :eg_version);
3095 CREATE TABLE serial.pattern_template (
3096 id SERIAL PRIMARY KEY,
3098 pattern_code TEXT NOT NULL,
3099 owning_lib INTEGER REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
3100 share_depth INTEGER NOT NULL DEFAULT 0
3102 CREATE INDEX serial_pattern_template_name_idx ON serial.pattern_template (evergreen.lowercase(name));
3104 CREATE OR REPLACE FUNCTION serial.pattern_templates_visible_to(org_unit INT) RETURNS SETOF serial.pattern_template AS $func$
3106 RETURN QUERY SELECT *
3107 FROM serial.pattern_template spt
3109 SELECT ARRAY_AGG(id)
3110 FROM actor.org_unit_descendants(spt.owning_lib, spt.share_depth)
3111 ) @@ org_unit::TEXT::QUERY_INT;
3113 $func$ LANGUAGE PLPGSQL;
3116 SELECT evergreen.upgrade_deps_block_check('1066', :eg_version);
3118 INSERT INTO permission.perm_list ( id, code, description ) VALUES
3119 ( 593, 'ADMIN_SERIAL_PATTERN_TEMPLATE', oils_i18n_gettext( 593,
3120 'Administer serial prediction pattern templates', 'ppl', 'description' ))
3123 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
3125 pgt.id, perm.id, aout.depth, FALSE
3127 permission.grp_tree pgt,
3128 permission.perm_list perm,
3129 actor.org_unit_type aout
3131 pgt.name = 'Serials' AND
3132 aout.name = 'System' AND
3134 'ADMIN_SERIAL_PATTERN_TEMPLATE'
3138 SELECT evergreen.upgrade_deps_block_check('1067', :eg_version);
3140 INSERT INTO acq.edi_attr (key, label) VALUES
3142 oils_i18n_gettext('INCLUDE_PO_NAME',
3143 'Orders Include PO Name', 'aea', 'label')),
3145 oils_i18n_gettext('INCLUDE_COPIES',
3146 'Orders Include Copy Data', 'aea', 'label')),
3148 oils_i18n_gettext('INCLUDE_FUND',
3149 'Orders Include Copy Funds', 'aea', 'label')),
3150 ('INCLUDE_CALL_NUMBER',
3151 oils_i18n_gettext('INCLUDE_CALL_NUMBER',
3152 'Orders Include Copy Call Numbers', 'aea', 'label')),
3153 ('INCLUDE_ITEM_TYPE',
3154 oils_i18n_gettext('INCLUDE_ITEM_TYPE',
3155 'Orders Include Copy Item Types', 'aea', 'label')),
3156 ('INCLUDE_ITEM_BARCODE',
3157 oils_i18n_gettext('INCLUDE_ITEM_BARCODE',
3158 'Orders Include Copy Barcodes', 'aea', 'label')),
3159 ('INCLUDE_LOCATION',
3160 oils_i18n_gettext('INCLUDE_LOCATION',
3161 'Orders Include Copy Locations', 'aea', 'label')),
3162 ('INCLUDE_COLLECTION_CODE',
3163 oils_i18n_gettext('INCLUDE_COLLECTION_CODE',
3164 'Orders Include Copy Collection Codes', 'aea', 'label')),
3165 ('INCLUDE_OWNING_LIB',
3166 oils_i18n_gettext('INCLUDE_OWNING_LIB',
3167 'Orders Include Copy Owning Library', 'aea', 'label')),
3168 ('USE_ID_FOR_OWNING_LIB',
3169 oils_i18n_gettext('USE_ID_FOR_OWNING_LIB',
3170 'Emit Owning Library ID Rather Than Short Name. Takes effect only if INCLUDE_OWNING_LIB is in use', 'aea', 'label')),
3171 ('INCLUDE_QUANTITY',
3172 oils_i18n_gettext('INCLUDE_QUANTITY',
3173 'Orders Include Copy Quantities', 'aea', 'label')),
3175 oils_i18n_gettext('INCLUDE_COPY_ID',
3176 'Orders Include Copy IDs', 'aea', 'label')),
3177 ('BUYER_ID_INCLUDE_VENDCODE',
3178 oils_i18n_gettext('BUYER_ID_INCLUDE_VENDCODE',
3179 'Buyer ID Qualifier Includes Vendcode', 'aea', 'label')),
3180 ('BUYER_ID_ONLY_VENDCODE',
3181 oils_i18n_gettext('BUYER_ID_ONLY_VENDCODE',
3182 'Buyer ID Qualifier Only Contains Vendcode', 'aea', 'label')),
3183 ('INCLUDE_BIB_EDITION',
3184 oils_i18n_gettext('INCLUDE_BIB_EDITION',
3185 'Order Lineitems Include Edition Info', 'aea', 'label')),
3186 ('INCLUDE_BIB_AUTHOR',
3187 oils_i18n_gettext('INCLUDE_BIB_AUTHOR',
3188 'Order Lineitems Include Author Info', 'aea', 'label')),
3189 ('INCLUDE_BIB_PAGINATION',
3190 oils_i18n_gettext('INCLUDE_BIB_PAGINATION',
3191 'Order Lineitems Include Pagination Info', 'aea', 'label')),
3193 oils_i18n_gettext('COPY_SPEC_CODES',
3194 'Order Lineitem Notes Include Copy Spec Codes', 'aea', 'label')),
3195 ('INCLUDE_EMPTY_IMD_VALUES',
3196 oils_i18n_gettext('INCLUDE_EMPTY_IMD_VALUES',
3197 'Lineitem Title, Author, etc. Fields Are Present Even if Empty', 'aea', 'label')),
3198 ('INCLUDE_EMPTY_LI_NOTE',
3199 oils_i18n_gettext('INCLUDE_EMPTY_LI_NOTE',
3200 'Order Lineitem Notes Always Present (Even if Empty)', 'aea', 'label')),
3201 ('INCLUDE_EMPTY_CALL_NUMBER',
3202 oils_i18n_gettext('INCLUDE_EMPTY_CALL_NUMBER',
3203 'Order Copies Always Include Call Number (Even if Empty)', 'aea', 'label')),
3204 ('INCLUDE_EMPTY_ITEM_TYPE',
3205 oils_i18n_gettext('INCLUDE_EMPTY_ITEM_TYPE',
3206 'Order Copies Always Include Item Type (Even if Empty)', 'aea', 'label')),
3207 ('INCLUDE_EMPTY_LOCATION',
3208 oils_i18n_gettext('INCLUDE_EMPTY_LOCATION',
3209 'Order Copies Always Include Location (Even if Empty)', 'aea', 'label')),
3210 ('INCLUDE_EMPTY_COLLECTION_CODE',
3211 oils_i18n_gettext('INCLUDE_EMPTY_COLLECTION_CODE',
3212 'Order Copies Always Include Collection Code (Even if Empty)', 'aea', 'label')),
3213 ('LINEITEM_IDENT_VENDOR_NUMBER',
3214 oils_i18n_gettext('LINEITEM_IDENT_VENDOR_NUMBER',
3215 'Lineitem Identifier Fields (LIN/PIA) Use Vendor-Encoded ID Value When Available', 'aea', 'label')),
3216 ('LINEITEM_REF_ID_ONLY',
3217 oils_i18n_gettext('LINEITEM_REF_ID_ONLY',
3218 'Lineitem Reference Field (RFF) Uses Lineitem ID Only', 'aea', 'label'))
3222 INSERT INTO acq.edi_attr_set (id, label) VALUES (1, 'Ingram Default');
3223 INSERT INTO acq.edi_attr_set (id, label) VALUES (2, 'Baker & Taylor Default');
3224 INSERT INTO acq.edi_attr_set (id, label) VALUES (3, 'Brodart Default');
3225 INSERT INTO acq.edi_attr_set (id, label) VALUES (4, 'Midwest Tape Default');
3226 INSERT INTO acq.edi_attr_set (id, label) VALUES (5, 'ULS Default');
3227 INSERT INTO acq.edi_attr_set (id, label) VALUES (6, 'Recorded Books Default');
3228 INSERT INTO acq.edi_attr_set (id, label) VALUES (7, 'Midwest Library Service');
3230 -- carve out space for mucho defaults
3231 SELECT SETVAL('acq.edi_attr_set_id_seq'::TEXT, 1000);
3233 INSERT INTO acq.edi_attr_set_map (attr_set, attr) VALUES
3236 (1, 'INCLUDE_PO_NAME'),
3237 (1, 'INCLUDE_COPIES'),
3238 (1, 'INCLUDE_ITEM_TYPE'),
3239 (1, 'INCLUDE_COLLECTION_CODE'),
3240 (1, 'INCLUDE_OWNING_LIB'),
3241 (1, 'INCLUDE_QUANTITY'),
3242 (1, 'INCLUDE_BIB_PAGINATION'),
3245 (2, 'INCLUDE_COPIES'),
3246 (2, 'INCLUDE_ITEM_TYPE'),
3247 (2, 'INCLUDE_COLLECTION_CODE'),
3248 (2, 'INCLUDE_CALL_NUMBER'),
3249 (2, 'INCLUDE_OWNING_LIB'),
3250 (2, 'INCLUDE_QUANTITY'),
3251 (2, 'INCLUDE_BIB_PAGINATION'),
3252 (2, 'BUYER_ID_INCLUDE_VENDCODE'),
3253 (2, 'INCLUDE_EMPTY_LI_NOTE'),
3254 (2, 'INCLUDE_EMPTY_CALL_NUMBER'),
3255 (2, 'INCLUDE_EMPTY_ITEM_TYPE'),
3256 (2, 'INCLUDE_EMPTY_COLLECTION_CODE'),
3257 (2, 'INCLUDE_EMPTY_LOCATION'),
3258 (2, 'LINEITEM_IDENT_VENDOR_NUMBER'),
3259 (2, 'LINEITEM_REF_ID_ONLY'),
3262 (3, 'INCLUDE_COPIES'),
3263 (3, 'INCLUDE_FUND'),
3264 (3, 'INCLUDE_ITEM_TYPE'),
3265 (3, 'INCLUDE_COLLECTION_CODE'),
3266 (3, 'INCLUDE_OWNING_LIB'),
3267 (3, 'INCLUDE_QUANTITY'),
3268 (3, 'INCLUDE_BIB_PAGINATION'),
3269 (3, 'COPY_SPEC_CODES'),
3272 (4, 'INCLUDE_COPIES'),
3273 (4, 'INCLUDE_FUND'),
3274 (4, 'INCLUDE_OWNING_LIB'),
3275 (4, 'INCLUDE_QUANTITY'),
3276 (4, 'INCLUDE_BIB_PAGINATION'),
3279 (5, 'INCLUDE_COPIES'),
3280 (5, 'INCLUDE_ITEM_TYPE'),
3281 (5, 'INCLUDE_COLLECTION_CODE'),
3282 (5, 'INCLUDE_OWNING_LIB'),
3283 (5, 'INCLUDE_QUANTITY'),
3284 (5, 'INCLUDE_BIB_AUTHOR'),
3285 (5, 'INCLUDE_BIB_EDITION'),
3286 (5, 'INCLUDE_EMPTY_LI_NOTE'),
3289 (6, 'INCLUDE_COPIES'),
3290 (6, 'INCLUDE_ITEM_TYPE'),
3291 (6, 'INCLUDE_COLLECTION_CODE'),
3292 (6, 'INCLUDE_OWNING_LIB'),
3293 (6, 'INCLUDE_QUANTITY'),
3294 (6, 'INCLUDE_BIB_PAGINATION'),
3296 -- Midwest Library Service
3297 (7, 'INCLUDE_BIB_AUTHOR'),
3298 (7, 'INCLUDE_BIB_EDITION'),
3299 (7, 'BUYER_ID_ONLY_VENDCODE'),
3300 (7, 'INCLUDE_EMPTY_IMD_VALUES')
3307 SELECT evergreen.upgrade_deps_block_check('1068', :eg_version); --miker/gmcharlt/kmlussier
3309 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"?>
3310 <xsl:stylesheet version="1.0" xmlns:mads="http://www.loc.gov/mads/v2"
3311 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:marc="http://www.loc.gov/MARC21/slim"
3312 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="marc">
3313 <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
3314 <xsl:strip-space elements="*"/>
3316 <xsl:variable name="ascii">
3317 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
3320 <xsl:variable name="latin1">
3321 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
3323 <!-- Characters that usually don't need to be escaped -->
3324 <xsl:variable name="safe">
3325 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
3328 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
3331 <xsl:template name="datafield">
3332 <xsl:param name="tag"/>
3333 <xsl:param name="ind1">
3334 <xsl:text> </xsl:text>
3336 <xsl:param name="ind2">
3337 <xsl:text> </xsl:text>
3339 <xsl:param name="subfields"/>
3340 <xsl:element name="marc:datafield">
3341 <xsl:attribute name="tag">
3342 <xsl:value-of select="$tag"/>
3344 <xsl:attribute name="ind1">
3345 <xsl:value-of select="$ind1"/>
3347 <xsl:attribute name="ind2">
3348 <xsl:value-of select="$ind2"/>
3350 <xsl:copy-of select="$subfields"/>
3354 <xsl:template name="subfieldSelect">
3355 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
3356 <xsl:param name="delimeter">
3357 <xsl:text> </xsl:text>
3359 <xsl:variable name="str">
3360 <xsl:for-each select="marc:subfield">
3361 <xsl:if test="contains($codes, @code)">
3362 <xsl:value-of select="text()"/>
3363 <xsl:value-of select="$delimeter"/>
3367 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
3370 <xsl:template name="buildSpaces">
3371 <xsl:param name="spaces"/>
3372 <xsl:param name="char">
3373 <xsl:text> </xsl:text>
3375 <xsl:if test="$spaces>0">
3376 <xsl:value-of select="$char"/>
3377 <xsl:call-template name="buildSpaces">
3378 <xsl:with-param name="spaces" select="$spaces - 1"/>
3379 <xsl:with-param name="char" select="$char"/>
3380 </xsl:call-template>
3384 <xsl:template name="chopPunctuation">
3385 <xsl:param name="chopString"/>
3386 <xsl:param name="punctuation">
3387 <xsl:text>.:,;/ </xsl:text>
3389 <xsl:variable name="length" select="string-length($chopString)"/>
3391 <xsl:when test="$length=0"/>
3392 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3393 <xsl:call-template name="chopPunctuation">
3394 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3395 <xsl:with-param name="punctuation" select="$punctuation"/>
3396 </xsl:call-template>
3398 <xsl:when test="not($chopString)"/>
3400 <xsl:value-of select="$chopString"/>
3405 <xsl:template name="chopPunctuationFront">
3406 <xsl:param name="chopString"/>
3407 <xsl:variable name="length" select="string-length($chopString)"/>
3409 <xsl:when test="$length=0"/>
3410 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
3411 <xsl:call-template name="chopPunctuationFront">
3412 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
3414 </xsl:call-template>
3416 <xsl:when test="not($chopString)"/>
3418 <xsl:value-of select="$chopString"/>
3423 <xsl:template name="chopPunctuationBack">
3424 <xsl:param name="chopString"/>
3425 <xsl:param name="punctuation">
3426 <xsl:text>.:,;/] </xsl:text>
3428 <xsl:variable name="length" select="string-length($chopString)"/>
3430 <xsl:when test="$length=0"/>
3431 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
3432 <xsl:call-template name="chopPunctuation">
3433 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
3434 <xsl:with-param name="punctuation" select="$punctuation"/>
3435 </xsl:call-template>
3437 <xsl:when test="not($chopString)"/>
3439 <xsl:value-of select="$chopString"/>
3444 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
3445 <xsl:template name="url-encode">
3447 <xsl:param name="str"/>
3449 <xsl:if test="$str">
3450 <xsl:variable name="first-char" select="substring($str,1,1)"/>
3452 <xsl:when test="contains($safe,$first-char)">
3453 <xsl:value-of select="$first-char"/>
3456 <xsl:variable name="codepoint">
3458 <xsl:when test="contains($ascii,$first-char)">
3460 select="string-length(substring-before($ascii,$first-char)) + 32"
3463 <xsl:when test="contains($latin1,$first-char)">
3465 select="string-length(substring-before($latin1,$first-char)) + 160"/>
3469 <xsl:message terminate="no">Warning: string contains a character
3470 that is out of range! Substituting "?".</xsl:message>
3471 <xsl:text>63</xsl:text>
3475 <xsl:variable name="hex-digit1"
3476 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
3477 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
3478 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
3479 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
3482 <xsl:if test="string-length($str) > 1">
3483 <xsl:call-template name="url-encode">
3484 <xsl:with-param name="str" select="substring($str,2)"/>
3485 </xsl:call-template>
3492 2.14 Fixed bug in mads:geographic attributes syntax ws 05/04/2016
3493 2.13 fixed repeating <geographic> tmee 01/31/2014
3494 2.12 added $2 authority for <classification> tmee 09/18/2012
3495 2.11 added delimiters between <classification> subfields tmee 09/18/2012
3496 2.10 fixed type="other" and type="otherType" for mads:related tmee 09/16/2011
3497 2.09 fixed professionTerm and genreTerm empty tag error tmee 09/16/2011
3498 2.08 fixed marc:subfield @code='i' matching error tmee 09/16/2011
3499 2.07 fixed 555 duplication error tmee 08/10/2011
3500 2.06 fixed topic subfield error tmee 08/10/2011
3501 2.05 fixed title subfield error tmee 06/20/2011
3502 2.04 fixed geographicSubdivision mapping for authority element tmee 06/16/2011
3503 2.03 added classification for 053, 055, 060, 065, 070, 080, 082, 083, 086, 087 tmee 06/03/2011
3504 2.02 added descriptionStandard for 008/10 tmee 04/27/2011
3505 2.01 added extensions for 046, 336, 370, 374, 375, 376 tmee 04/08/2011
3506 2.00 redefined imported MODS elements in version 1.0 to MADS elements in version 2.0 tmee 02/08/2011
3507 1.08 added 372 subfields $a $s $t for <fieldOfActivity> tmee 06/24/2010
3508 1.07 removed role/roleTerm 100, 110, 111, 400, 410, 411, 500, 510, 511, 700, 710, 711 tmee 06/24/2010
3509 1.06 added strip-space tmee 06/24/2010
3510 1.05 added subfield $a for 130, 430, 530 tmee 06/21/2010
3511 1.04 fixed 550 z omission ntra 08/11/2008
3512 1.03 removed duplication of 550 $a text tmee 11/01/2006
3513 1.02 fixed namespace references between mads and mods ntra 10/06/2006
3514 1.01 revised rgue/jrad 11/29/05
3515 1.00 adapted from MARC21Slim2MODS3.xsl ntra 07/06/05
3518 <!-- authority attribute defaults to 'naf' if not set using this authority parameter, for <authority> descriptors: name, titleInfo, geographic -->
3519 <xsl:param name="authority"/>
3520 <xsl:variable name="auth">
3522 <xsl:when test="$authority">
3523 <xsl:value-of select="$authority"/>
3525 <xsl:otherwise>naf</xsl:otherwise>
3528 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
3529 <xsl:variable name="controlField008-06"
3530 select="substring(descendant-or-self::marc:controlfield[@tag=008],7,1)"/>
3531 <xsl:variable name="controlField008-11"
3532 select="substring(descendant-or-self::marc:controlfield[@tag=008],12,1)"/>
3533 <xsl:variable name="controlField008-14"
3534 select="substring(descendant-or-self::marc:controlfield[@tag=008],15,1)"/>
3535 <xsl:template match="/">
3537 <xsl:when test="descendant-or-self::marc:collection">
3538 <mads:madsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3539 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/v2/mads-2-0.xsd">
3540 <xsl:for-each select="descendant-or-self::marc:collection/marc:record">
3541 <mads:mads version="2.0">
3542 <xsl:call-template name="marcRecord"/>
3545 </mads:madsCollection>
3548 <mads:mads version="2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3549 xsi:schemaLocation="http://www.loc.gov/mads/v2 http://www.loc.gov/standards/mads/mads-2-0.xsd">
3550 <xsl:for-each select="descendant-or-self::marc:record">
3551 <xsl:call-template name="marcRecord"/>
3558 <xsl:template name="marcRecord">
3562 <xsl:when test="$controlField008-06='d'">
3563 <xsl:attribute name="geographicSubdivision">
3564 <xsl:text>direct</xsl:text>
3567 <xsl:when test="$controlField008-06='i'">
3568 <xsl:attribute name="geographicSubdivision">
3569 <xsl:text>indirect</xsl:text>
3572 <xsl:when test="$controlField008-06='n'">
3573 <xsl:attribute name="geographicSubdivision">
3574 <xsl:text>not applicable</xsl:text>
3579 <xsl:apply-templates select="marc:datafield[100 <= @tag and @tag < 200]"/>
3583 <xsl:apply-templates
3584 select="marc:datafield[500 <= @tag and @tag <= 585]|marc:datafield[700 <= @tag and @tag <= 785]"/>
3587 <xsl:apply-templates select="marc:datafield[400 <= @tag and @tag <= 485]"/>
3590 <xsl:apply-templates select="marc:datafield[667 <= @tag and @tag <= 688]"/>
3593 <xsl:apply-templates select="marc:datafield[@tag=856]"/>
3594 <xsl:apply-templates select="marc:datafield[@tag=010]"/>
3595 <xsl:apply-templates select="marc:datafield[@tag=024]"/>
3596 <xsl:apply-templates select="marc:datafield[@tag=372]"/>
3598 <!-- classification -->
3599 <xsl:apply-templates select="marc:datafield[@tag=053]"/>
3600 <xsl:apply-templates select="marc:datafield[@tag=055]"/>
3601 <xsl:apply-templates select="marc:datafield[@tag=060]"/>
3602 <xsl:apply-templates select="marc:datafield[@tag=065]"/>
3603 <xsl:apply-templates select="marc:datafield[@tag=070]"/>
3604 <xsl:apply-templates select="marc:datafield[@tag=080]"/>
3605 <xsl:apply-templates select="marc:datafield[@tag=082]"/>
3606 <xsl:apply-templates select="marc:datafield[@tag=083]"/>
3607 <xsl:apply-templates select="marc:datafield[@tag=086]"/>
3608 <xsl:apply-templates select="marc:datafield[@tag=087]"/>
3611 <xsl:for-each select="marc:datafield[@tag=373]">
3614 <xsl:value-of select="marc:subfield[@code='a']"/>
3616 <mads:dateValid point="start">
3617 <xsl:value-of select="marc:subfield[@code='s']"/>
3619 <mads:dateValid point="end">
3620 <xsl:value-of select="marc:subfield[@code='t']"/>
3624 <xsl:for-each select="marc:datafield[@tag=371]">
3628 <xsl:value-of select="marc:subfield[@code='a']"/>
3631 <xsl:value-of select="marc:subfield[@code='b']"/>
3634 <xsl:value-of select="marc:subfield[@code='c']"/>
3637 <xsl:value-of select="marc:subfield[@code='d']"/>
3640 <xsl:value-of select="marc:subfield[@code='e']"/>
3644 <xsl:value-of select="marc:subfield[@code='m']"/>
3650 <xsl:for-each select="marc:datafield[@tag=336]">
3653 <mads:contentType type="text">
3654 <xsl:value-of select="marc:subfield[@code='a']"/>
3656 <mads:contentType type="code">
3657 <xsl:value-of select="marc:subfield[@code='b']"/>
3663 <xsl:for-each select="marc:datafield[@tag=374]">
3667 <xsl:when test="marc:subfield[@code='a']">
3668 <mads:professionTerm>
3669 <xsl:value-of select="marc:subfield[@code='a']"/>
3670 </mads:professionTerm>
3672 <xsl:when test="marc:subfield[@code='s']">
3673 <mads:dateValid point="start">
3674 <xsl:value-of select="marc:subfield[@code='s']"/>
3677 <xsl:when test="marc:subfield[@code='t']">
3678 <mads:dateValid point="end">
3679 <xsl:value-of select="marc:subfield[@code='t']"/>
3687 <xsl:for-each select="marc:datafield[@tag=375]">
3691 <xsl:when test="marc:subfield[@code='a']">
3693 <xsl:value-of select="marc:subfield[@code='a']"/>
3696 <xsl:when test="marc:subfield[@code='s']">
3697 <mads:dateValid point="start">
3698 <xsl:value-of select="marc:subfield[@code='s']"/>
3701 <xsl:when test="marc:subfield[@code='t']">
3702 <mads:dateValid point="end">
3703 <xsl:value-of select="marc:subfield[@code='t']"/>
3711 <xsl:for-each select="marc:datafield[@tag=376]">
3713 <mads:familyInformation>
3715 <xsl:value-of select="marc:subfield[@code='a']"/>
3716 </mads:typeOfFamily>
3717 <mads:nameOfProminentMember>
3718 <xsl:value-of select="marc:subfield[@code='b']"/>
3719 </mads:nameOfProminentMember>
3720 <mads:hereditaryTitle>
3721 <xsl:value-of select="marc:subfield[@code='c']"/>
3722 </mads:hereditaryTitle>
3723 <mads:dateValid point="start">
3724 <xsl:value-of select="marc:subfield[@code='s']"/>
3726 <mads:dateValid point="end">
3727 <xsl:value-of select="marc:subfield[@code='t']"/>
3729 </mads:familyInformation>
3734 <mads:recordOrigin>Converted from MARCXML to MADS version 2.0 (Revision 2.13)</mads:recordOrigin>
3735 <!-- <xsl:apply-templates select="marc:datafield[@tag=024]"/> -->
3737 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='a']"/>
3738 <xsl:apply-templates select="marc:controlfield[@tag=005]"/>
3739 <xsl:apply-templates select="marc:controlfield[@tag=001]"/>
3740 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='b']"/>
3741 <xsl:apply-templates select="marc:datafield[@tag=040]/marc:subfield[@code='e']"/>
3742 <xsl:for-each select="marc:controlfield[@tag=008]">
3743 <xsl:if test="substring(.,11,1)='a'">
3744 <mads:descriptionStandard>
3745 <xsl:text>earlier rules</xsl:text>
3746 </mads:descriptionStandard>
3748 <xsl:if test="substring(.,11,1)='b'">
3749 <mads:descriptionStandard>
3750 <xsl:text>aacr1</xsl:text>
3751 </mads:descriptionStandard>
3753 <xsl:if test="substring(.,11,1)='c'">
3754 <mads:descriptionStandard>
3755 <xsl:text>aacr2</xsl:text>
3756 </mads:descriptionStandard>
3758 <xsl:if test="substring(.,11,1)='d'">
3759 <mads:descriptionStandard>
3760 <xsl:text>aacr2 compatible</xsl:text>
3761 </mads:descriptionStandard>
3763 <xsl:if test="substring(.,11,1)='z'">
3764 <mads:descriptionStandard>
3765 <xsl:text>other rules</xsl:text>
3766 </mads:descriptionStandard>
3772 <!-- start of secondary templates -->
3774 <!-- ======== xlink ======== -->
3776 <!-- <xsl:template name="uri">
3777 <xsl:for-each select="marc:subfield[@code='0']">
3778 <xsl:attribute name="xlink:href">
3779 <xsl:value-of select="."/>
3784 <xsl:template match="marc:subfield[@code='i']">
3785 <xsl:attribute name="otherType">
3786 <xsl:value-of select="."/>
3790 <!-- No role/roleTerm mapped in MADS 06/24/2010
3791 <xsl:template name="role">
3792 <xsl:for-each select="marc:subfield[@code='e']">
3794 <mads:roleTerm type="text">
3795 <xsl:value-of select="."/>
3802 <xsl:template name="part">
3803 <xsl:variable name="partNumber">
3804 <xsl:call-template name="specialSubfieldSelect">
3805 <xsl:with-param name="axis">n</xsl:with-param>
3806 <xsl:with-param name="anyCodes">n</xsl:with-param>
3807 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3808 </xsl:call-template>
3810 <xsl:variable name="partName">
3811 <xsl:call-template name="specialSubfieldSelect">
3812 <xsl:with-param name="axis">p</xsl:with-param>
3813 <xsl:with-param name="anyCodes">p</xsl:with-param>
3814 <xsl:with-param name="afterCodes">fghkdlmor</xsl:with-param>
3815 </xsl:call-template>
3817 <xsl:if test="string-length(normalize-space($partNumber))">
3819 <xsl:call-template name="chopPunctuation">
3820 <xsl:with-param name="chopString" select="$partNumber"/>
3821 </xsl:call-template>
3824 <xsl:if test="string-length(normalize-space($partName))">
3826 <xsl:call-template name="chopPunctuation">
3827 <xsl:with-param name="chopString" select="$partName"/>
3828 </xsl:call-template>
3833 <xsl:template name="nameABCDN">
3834 <xsl:for-each select="marc:subfield[@code='a']">
3836 <xsl:call-template name="chopPunctuation">
3837 <xsl:with-param name="chopString" select="."/>
3838 </xsl:call-template>
3841 <xsl:for-each select="marc:subfield[@code='b']">
3843 <xsl:value-of select="."/>
3847 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
3849 <xsl:call-template name="subfieldSelect">
3850 <xsl:with-param name="codes">cdn</xsl:with-param>
3851 </xsl:call-template>
3856 <xsl:template name="nameABCDQ">
3858 <xsl:call-template name="chopPunctuation">
3859 <xsl:with-param name="chopString">
3860 <xsl:call-template name="subfieldSelect">
3861 <xsl:with-param name="codes">aq</xsl:with-param>
3862 </xsl:call-template>
3864 </xsl:call-template>
3866 <xsl:call-template name="termsOfAddress"/>
3867 <xsl:call-template name="nameDate"/>
3870 <xsl:template name="nameACDENQ">
3872 <xsl:call-template name="subfieldSelect">
3873 <xsl:with-param name="codes">acdenq</xsl:with-param>
3874 </xsl:call-template>
3878 <xsl:template name="nameDate">
3879 <xsl:for-each select="marc:subfield[@code='d']">
3880 <mads:namePart type="date">
3881 <xsl:call-template name="chopPunctuation">
3882 <xsl:with-param name="chopString" select="."/>
3883 </xsl:call-template>
3888 <xsl:template name="specialSubfieldSelect">
3889 <xsl:param name="anyCodes"/>
3890 <xsl:param name="axis"/>
3891 <xsl:param name="beforeCodes"/>
3892 <xsl:param name="afterCodes"/>
3893 <xsl:variable name="str">
3894 <xsl:for-each select="marc:subfield">
3896 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])">
3897 <xsl:value-of select="text()"/>
3898 <xsl:text> </xsl:text>
3902 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
3905 <xsl:template name="termsOfAddress">
3906 <xsl:if test="marc:subfield[@code='b' or @code='c']">
3907 <mads:namePart type="termsOfAddress">
3908 <xsl:call-template name="chopPunctuation">
3909 <xsl:with-param name="chopString">
3910 <xsl:call-template name="subfieldSelect">
3911 <xsl:with-param name="codes">bc</xsl:with-param>
3912 </xsl:call-template>
3914 </xsl:call-template>
3919 <xsl:template name="displayLabel">
3920 <xsl:if test="marc:subfield[@code='z']">
3921 <xsl:attribute name="displayLabel">
3922 <xsl:value-of select="marc:subfield[@code='z']"/>
3925 <xsl:if test="marc:subfield[@code='3']">
3926 <xsl:attribute name="displayLabel">
3927 <xsl:value-of select="marc:subfield[@code='3']"/>
3932 <xsl:template name="isInvalid">
3933 <xsl:if test="@code='z'">
3934 <xsl:attribute name="invalid">yes</xsl:attribute>
3938 <xsl:template name="sub2Attribute">
3940 <xsl:if test="../marc:subfield[@code='2']">
3941 <xsl:attribute name="type">
3942 <xsl:value-of select="../marc:subfield[@code='2']"/>
3947 <xsl:template match="marc:controlfield[@tag=001]">
3948 <mads:recordIdentifier>
3949 <xsl:if test="../marc:controlfield[@tag=003]">
3950 <xsl:attribute name="source">
3951 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
3954 <xsl:value-of select="."/>
3955 </mads:recordIdentifier>
3958 <xsl:template match="marc:controlfield[@tag=005]">
3959 <mads:recordChangeDate encoding="iso8601">
3960 <xsl:value-of select="."/>
3961 </mads:recordChangeDate>
3964 <xsl:template match="marc:controlfield[@tag=008]">
3965 <mads:recordCreationDate encoding="marc">
3966 <xsl:value-of select="substring(.,1,6)"/>
3967 </mads:recordCreationDate>
3970 <xsl:template match="marc:datafield[@tag=010]">
3971 <xsl:for-each select="marc:subfield">
3972 <mads:identifier type="lccn">
3973 <xsl:call-template name="isInvalid"/>
3974 <xsl:value-of select="."/>
3979 <xsl:template match="marc:datafield[@tag=024]">
3980 <xsl:for-each select="marc:subfield[not(@code=2)]">
3982 <xsl:call-template name="isInvalid"/>
3983 <xsl:call-template name="sub2Attribute"/>
3984 <xsl:value-of select="."/>
3989 <!-- ========== 372 ========== -->
3990 <xsl:template match="marc:datafield[@tag=372]">
3991 <mads:fieldOfActivity>
3992 <xsl:call-template name="subfieldSelect">
3993 <xsl:with-param name="codes">a</xsl:with-param>
3994 </xsl:call-template>
3995 <xsl:text>-</xsl:text>
3996 <xsl:call-template name="subfieldSelect">
3997 <xsl:with-param name="codes">st</xsl:with-param>
3998 </xsl:call-template>
3999 </mads:fieldOfActivity>
4003 <!-- ========== 040 ========== -->
4004 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='a']">
4005 <mads:recordContentSource authority="marcorg">
4006 <xsl:value-of select="."/>
4007 </mads:recordContentSource>
4010 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='b']">
4011 <mads:languageOfCataloging>
4012 <mads:languageTerm authority="iso639-2b" type="code">
4013 <xsl:value-of select="."/>
4014 </mads:languageTerm>
4015 </mads:languageOfCataloging>
4018 <xsl:template match="marc:datafield[@tag=040]/marc:subfield[@code='e']">
4019 <mads:descriptionStandard>
4020 <xsl:value-of select="."/>
4021 </mads:descriptionStandard>
4024 <!-- ========== classification 2.03 ========== -->
4026 <xsl:template match="marc:datafield[@tag=053]">
4027 <mads:classification>
4028 <xsl:call-template name="subfieldSelect">
4029 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4030 <xsl:with-param name="delimeter">-</xsl:with-param>
4031 </xsl:call-template>
4032 </mads:classification>
4035 <xsl:template match="marc:datafield[@tag=055]">
4036 <mads:classification>
4037 <xsl:call-template name="subfieldSelect">
4038 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4039 <xsl:with-param name="delimeter">-</xsl:with-param>
4040 </xsl:call-template>
4041 </mads:classification>
4044 <xsl:template match="marc:datafield[@tag=060]">
4045 <mads:classification>
4046 <xsl:call-template name="subfieldSelect">
4047 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4048 <xsl:with-param name="delimeter">-</xsl:with-param>
4049 </xsl:call-template>
4050 </mads:classification>
4052 <xsl:template match="marc:datafield[@tag=065]">
4053 <mads:classification>
4054 <xsl:attribute name="authority">
4055 <xsl:value-of select="marc:subfield[@code='2']"/>
4057 <xsl:call-template name="subfieldSelect">
4058 <xsl:with-param name="codes">abcdxyz</xsl:with-param>
4059 <xsl:with-param name="delimeter">-</xsl:with-param>
4060 </xsl:call-template>
4061 </mads:classification>
4063 <xsl:template match="marc:datafield[@tag=070]">
4064 <mads:classification>
4065 <xsl:call-template name="subfieldSelect">
4066 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4067 <xsl:with-param name="delimeter">-</xsl:with-param>
4068 </xsl:call-template>
4069 </mads:classification>
4071 <xsl:template match="marc:datafield[@tag=080]">
4072 <mads:classification>
4073 <xsl:attribute name="authority">
4074 <xsl:value-of select="marc:subfield[@code='2']"/>
4076 <xsl:call-template name="subfieldSelect">
4077 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4078 <xsl:with-param name="delimeter">-</xsl:with-param>
4079 </xsl:call-template>
4080 </mads:classification>
4082 <xsl:template match="marc:datafield[@tag=082]">
4083 <mads:classification>
4084 <xsl:attribute name="authority">
4085 <xsl:value-of select="marc:subfield[@code='2']"/>
4087 <xsl:call-template name="subfieldSelect">
4088 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4089 <xsl:with-param name="delimeter">-</xsl:with-param>
4090 </xsl:call-template>
4091 </mads:classification>
4093 <xsl:template match="marc:datafield[@tag=083]">
4094 <mads:classification>
4095 <xsl:attribute name="authority">
4096 <xsl:value-of select="marc:subfield[@code='2']"/>
4098 <xsl:call-template name="subfieldSelect">
4099 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4100 <xsl:with-param name="delimeter">-</xsl:with-param>
4101 </xsl:call-template>
4102 </mads:classification>
4104 <xsl:template match="marc:datafield[@tag=086]">
4105 <mads:classification>
4106 <xsl:attribute name="authority">
4107 <xsl:value-of select="marc:subfield[@code='2']"/>
4109 <xsl:call-template name="subfieldSelect">
4110 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4111 <xsl:with-param name="delimeter">-</xsl:with-param>
4112 </xsl:call-template>
4113 </mads:classification>
4115 <xsl:template match="marc:datafield[@tag=087]">
4116 <mads:classification>
4117 <xsl:attribute name="authority">
4118 <xsl:value-of select="marc:subfield[@code='2']"/>
4120 <xsl:call-template name="subfieldSelect">
4121 <xsl:with-param name="codes">abcdxyz5</xsl:with-param>
4122 <xsl:with-param name="delimeter">-</xsl:with-param>
4123 </xsl:call-template>
4124 </mads:classification>
4128 <!-- ========== names ========== -->
4129 <xsl:template match="marc:datafield[@tag=100]">
4130 <mads:name type="personal">
4131 <xsl:call-template name="setAuthority"/>
4132 <xsl:call-template name="nameABCDQ"/>
4134 <xsl:apply-templates select="*[marc:subfield[not(contains('abcdeq',@code))]]"/>
4135 <xsl:call-template name="title"/>
4136 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4139 <xsl:template match="marc:datafield[@tag=110]">
4140 <mads:name type="corporate">
4141 <xsl:call-template name="setAuthority"/>
4142 <xsl:call-template name="nameABCDN"/>
4144 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4147 <xsl:template match="marc:datafield[@tag=111]">
4148 <mads:name type="conference">
4149 <xsl:call-template name="setAuthority"/>
4150 <xsl:call-template name="nameACDENQ"/>
4152 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4155 <xsl:template match="marc:datafield[@tag=400]">
4157 <xsl:call-template name="variantTypeAttribute"/>
4158 <mads:name type="personal">
4159 <xsl:call-template name="nameABCDQ"/>
4161 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4162 <xsl:call-template name="title"/>
4166 <xsl:template match="marc:datafield[@tag=410]">
4168 <xsl:call-template name="variantTypeAttribute"/>
4169 <mads:name type="corporate">
4170 <xsl:call-template name="nameABCDN"/>
4172 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4176 <xsl:template match="marc:datafield[@tag=411]">
4178 <xsl:call-template name="variantTypeAttribute"/>
4179 <mads:name type="conference">
4180 <xsl:call-template name="nameACDENQ"/>
4182 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4186 <xsl:template match="marc:datafield[@tag=500]|marc:datafield[@tag=700]">
4188 <xsl:call-template name="relatedTypeAttribute"/>
4189 <!-- <xsl:call-template name="uri"/> -->
4190 <mads:name type="personal">
4191 <xsl:call-template name="setAuthority"/>
4192 <xsl:call-template name="nameABCDQ"/>
4194 <xsl:call-template name="title"/>
4195 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4199 <xsl:template match="marc:datafield[@tag=510]|marc:datafield[@tag=710]">
4201 <xsl:call-template name="relatedTypeAttribute"/>
4202 <!-- <xsl:call-template name="uri"/> -->
4203 <mads:name type="corporate">
4204 <xsl:call-template name="setAuthority"/>
4205 <xsl:call-template name="nameABCDN"/>
4207 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4211 <xsl:template match="marc:datafield[@tag=511]|marc:datafield[@tag=711]">
4213 <xsl:call-template name="relatedTypeAttribute"/>
4214 <!-- <xsl:call-template name="uri"/> -->
4215 <mads:name type="conference">
4216 <xsl:call-template name="setAuthority"/>
4217 <xsl:call-template name="nameACDENQ"/>
4219 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4223 <!-- ========== titles ========== -->
4224 <xsl:template match="marc:datafield[@tag=130]">
4225 <xsl:call-template name="uniform-title"/>
4226 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4229 <xsl:template match="marc:datafield[@tag=430]">
4231 <xsl:call-template name="variantTypeAttribute"/>
4232 <xsl:call-template name="uniform-title"/>
4233 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4237 <xsl:template match="marc:datafield[@tag=530]|marc:datafield[@tag=730]">
4239 <xsl:call-template name="relatedTypeAttribute"/>
4240 <xsl:call-template name="uniform-title"/>
4241 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4245 <xsl:template name="title">
4246 <xsl:variable name="hasTitle">
4247 <xsl:for-each select="marc:subfield">
4248 <xsl:if test="(contains('tfghklmors',@code) )">
4249 <xsl:value-of select="@code"/>
4253 <xsl:if test="string-length($hasTitle) > 0 ">
4255 <xsl:call-template name="setAuthority"/>
4257 <xsl:variable name="str">
4258 <xsl:for-each select="marc:subfield">
4259 <xsl:if test="(contains('atfghklmors',@code) )">
4260 <xsl:value-of select="text()"/>
4261 <xsl:text> </xsl:text>
4265 <xsl:call-template name="chopPunctuation">
4266 <xsl:with-param name="chopString">
4267 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4269 </xsl:call-template>
4271 <xsl:call-template name="part"/>
4272 <!-- <xsl:call-template name="uri"/> -->
4277 <xsl:template name="uniform-title">
4278 <xsl:variable name="hasTitle">
4279 <xsl:for-each select="marc:subfield">
4280 <xsl:if test="(contains('atfghklmors',@code) )">
4281 <xsl:value-of select="@code"/>
4285 <xsl:if test="string-length($hasTitle) > 0 ">
4287 <xsl:call-template name="setAuthority"/>
4289 <xsl:variable name="str">
4290 <xsl:for-each select="marc:subfield">
4291 <xsl:if test="(contains('adfghklmors',@code) )">
4292 <xsl:value-of select="text()"/>
4293 <xsl:text> </xsl:text>
4297 <xsl:call-template name="chopPunctuation">
4298 <xsl:with-param name="chopString">
4299 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4301 </xsl:call-template>
4303 <xsl:call-template name="part"/>
4304 <!-- <xsl:call-template name="uri"/> -->
4310 <!-- ========== topics ========== -->
4311 <xsl:template match="marc:subfield[@code='x']">
4313 <xsl:call-template name="chopPunctuation">
4314 <xsl:with-param name="chopString">
4315 <xsl:value-of select="."/>
4317 </xsl:call-template>
4323 match="marc:datafield[@tag=150][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=180][marc:subfield[@code='x']]">
4324 <xsl:call-template name="topic"/>
4325 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4328 match="marc:datafield[@tag=450][marc:subfield[@code='a' or @code='b']]|marc:datafield[@tag=480][marc:subfield[@code='x']]">
4330 <xsl:call-template name="variantTypeAttribute"/>
4331 <xsl:call-template name="topic"/>
4335 match="marc:datafield[@tag=550 or @tag=750][marc:subfield[@code='a' or @code='b']]">
4337 <xsl:call-template name="relatedTypeAttribute"/>
4338 <!-- <xsl:call-template name="uri"/> -->
4339 <xsl:call-template name="topic"/>
4340 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4343 <xsl:template name="topic">
4345 <xsl:call-template name="setAuthority"/>
4346 <!-- tmee2006 dedupe 550a
4347 <xsl:if test="@tag=550 or @tag=750">
4348 <xsl:call-template name="subfieldSelect">
4349 <xsl:with-param name="codes">ab</xsl:with-param>
4350 </xsl:call-template>
4354 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4355 <xsl:call-template name="chopPunctuation">
4356 <xsl:with-param name="chopString">
4357 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4359 </xsl:call-template>
4362 <xsl:call-template name="chopPunctuation">
4363 <xsl:with-param name="chopString">
4365 <xsl:when test="@tag=180 or @tag=480 or @tag=580 or @tag=780">
4366 <xsl:apply-templates select="marc:subfield[@code='x']"/>
4369 <xsl:call-template name="subfieldSelect">
4370 <xsl:with-param name="codes">ab</xsl:with-param>
4371 </xsl:call-template>
4375 </xsl:call-template>
4379 <!-- ========= temporals ========== -->
4380 <xsl:template match="marc:subfield[@code='y']">
4382 <xsl:call-template name="chopPunctuation">
4383 <xsl:with-param name="chopString">
4384 <xsl:value-of select="."/>
4386 </xsl:call-template>
4390 match="marc:datafield[@tag=148][marc:subfield[@code='a']]|marc:datafield[@tag=182 ][marc:subfield[@code='y']]">
4391 <xsl:call-template name="temporal"/>
4394 match="marc:datafield[@tag=448][marc:subfield[@code='a']]|marc:datafield[@tag=482][marc:subfield[@code='y']]">
4396 <xsl:call-template name="variantTypeAttribute"/>
4397 <xsl:call-template name="temporal"/>
4401 match="marc:datafield[@tag=548 or @tag=748][marc:subfield[@code='a']]|marc:datafield[@tag=582 or @tag=782][marc:subfield[@code='y']]">
4403 <xsl:call-template name="relatedTypeAttribute"/>
4404 <!-- <xsl:call-template name="uri"/> -->
4405 <xsl:call-template name="temporal"/>
4408 <xsl:template name="temporal">
4410 <xsl:call-template name="setAuthority"/>
4411 <xsl:if test="@tag=548 or @tag=748">
4412 <xsl:value-of select="marc:subfield[@code='a']"/>
4414 <xsl:call-template name="chopPunctuation">
4415 <xsl:with-param name="chopString">
4417 <xsl:when test="@tag=182 or @tag=482 or @tag=582 or @tag=782">
4418 <xsl:apply-templates select="marc:subfield[@code='y']"/>
4421 <xsl:value-of select="marc:subfield[@code='a']"/>
4425 </xsl:call-template>
4427 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4430 <!-- ========== genre ========== -->
4431 <xsl:template match="marc:subfield[@code='v']">
4433 <xsl:call-template name="chopPunctuation">
4434 <xsl:with-param name="chopString">
4435 <xsl:value-of select="."/>
4437 </xsl:call-template>
4441 match="marc:datafield[@tag=155][marc:subfield[@code='a']]|marc:datafield[@tag=185][marc:subfield[@code='v']]">
4442 <xsl:call-template name="genre"/>
4445 match="marc:datafield[@tag=455][marc:subfield[@code='a']]|marc:datafield[@tag=485 ][marc:subfield[@code='v']]">
4447 <xsl:call-template name="variantTypeAttribute"/>
4448 <xsl:call-template name="genre"/>
4452 <xsl:template match="marc:datafield[@tag=555]">
4454 <xsl:call-template name="relatedTypeAttribute"/>
4455 <xsl:call-template name="uri"/>
4456 <xsl:call-template name="genre"/>
4461 match="marc:datafield[@tag=555 or @tag=755][marc:subfield[@code='a']]|marc:datafield[@tag=585][marc:subfield[@code='v']]">
4463 <xsl:call-template name="relatedTypeAttribute"/>
4464 <xsl:call-template name="genre"/>
4467 <xsl:template name="genre">
4469 <xsl:if test="@tag=555">
4470 <xsl:value-of select="marc:subfield[@code='a']"/>
4472 <xsl:call-template name="setAuthority"/>
4473 <xsl:call-template name="chopPunctuation">
4474 <xsl:with-param name="chopString">
4477 <xsl:when test="@tag='555'"/>
4478 <xsl:when test="@tag=185 or @tag=485 or @tag=585">
4479 <xsl:apply-templates select="marc:subfield[@code='v']"/>
4482 <xsl:value-of select="marc:subfield[@code='a']"/>
4486 </xsl:call-template>
4488 <xsl:apply-templates/>
4491 <!-- ========= geographic ========== -->
4492 <xsl:template match="marc:subfield[@code='z']">
4494 <xsl:call-template name="chopPunctuation">
4495 <xsl:with-param name="chopString">
4496 <xsl:value-of select="."/>
4498 </xsl:call-template>
4501 <xsl:template name="geographic">
4504 <xsl:call-template name="setAuthority"/>
4506 <xsl:if test="@tag=151 or @tag=551">
4507 <xsl:value-of select="marc:subfield[@code='a']"/>
4509 <xsl:call-template name="chopPunctuation">
4510 <xsl:with-param name="chopString">
4511 <xsl:if test="@tag=181 or @tag=481 or @tag=581">
4512 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4516 <xsl:when test="@tag=181 or @tag=481 or @tag=581">
4517 <xsl:apply-templates select="marc:subfield[@code='z']"/>
4521 <xsl:value-of select="marc:subfield[@code='a']"/>
4526 </xsl:call-template>
4528 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4531 match="marc:datafield[@tag=151][marc:subfield[@code='a']]|marc:datafield[@tag=181][marc:subfield[@code='z']]">
4532 <xsl:call-template name="geographic"/>
4535 match="marc:datafield[@tag=451][marc:subfield[@code='a']]|marc:datafield[@tag=481][marc:subfield[@code='z']]">
4537 <xsl:call-template name="variantTypeAttribute"/>
4538 <xsl:call-template name="geographic"/>
4542 match="marc:datafield[@tag=551]|marc:datafield[@tag=581][marc:subfield[@code='z']]">
4544 <xsl:call-template name="relatedTypeAttribute"/>
4545 <!-- <xsl:call-template name="uri"/> -->
4546 <xsl:call-template name="geographic"/>
4549 <xsl:template match="marc:datafield[@tag=580]">
4551 <xsl:call-template name="relatedTypeAttribute"/>
4552 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4556 match="marc:datafield[@tag=751][marc:subfield[@code='z']]|marc:datafield[@tag=781][marc:subfield[@code='z']]">
4558 <xsl:call-template name="relatedTypeAttribute"/>
4559 <xsl:call-template name="geographic"/>
4562 <xsl:template match="marc:datafield[@tag=755]">
4564 <xsl:call-template name="relatedTypeAttribute"/>
4565 <xsl:call-template name="genre"/>
4566 <xsl:call-template name="setAuthority"/>
4567 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4570 <xsl:template match="marc:datafield[@tag=780]">
4572 <xsl:call-template name="relatedTypeAttribute"/>
4573 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4576 <xsl:template match="marc:datafield[@tag=785]">
4578 <xsl:call-template name="relatedTypeAttribute"/>
4579 <xsl:apply-templates select="marc:subfield[@code!='i']"/>
4583 <!-- ========== notes ========== -->
4584 <xsl:template match="marc:datafield[667 <= @tag and @tag <= 688]">
4587 <xsl:when test="@tag=667">
4588 <xsl:attribute name="type">nonpublic</xsl:attribute>
4590 <xsl:when test="@tag=670">
4591 <xsl:attribute name="type">source</xsl:attribute>
4593 <xsl:when test="@tag=675">
4594 <xsl:attribute name="type">notFound</xsl:attribute>
4596 <xsl:when test="@tag=678">
4597 <xsl:attribute name="type">history</xsl:attribute>
4599 <xsl:when test="@tag=681">
4600 <xsl:attribute name="type">subject example</xsl:attribute>
4602 <xsl:when test="@tag=682">
4603 <xsl:attribute name="type">deleted heading information</xsl:attribute>
4605 <xsl:when test="@tag=688">
4606 <xsl:attribute name="type">application history</xsl:attribute>
4609 <xsl:call-template name="chopPunctuation">
4610 <xsl:with-param name="chopString">
4612 <xsl:when test="@tag=667 or @tag=675">
4613 <xsl:value-of select="marc:subfield[@code='a']"/>
4615 <xsl:when test="@tag=670 or @tag=678">
4616 <xsl:call-template name="subfieldSelect">
4617 <xsl:with-param name="codes">ab</xsl:with-param>
4618 </xsl:call-template>
4620 <xsl:when test="680 <= @tag and @tag <=688">
4621 <xsl:call-template name="subfieldSelect">
4622 <xsl:with-param name="codes">ai</xsl:with-param>
4623 </xsl:call-template>
4627 </xsl:call-template>
4631 <!-- ========== url ========== -->
4632 <xsl:template match="marc:datafield[@tag=856][marc:subfield[@code='u']]">
4634 <xsl:if test="marc:subfield[@code='z' or @code='3']">
4635 <xsl:attribute name="displayLabel">
4636 <xsl:call-template name="subfieldSelect">
4637 <xsl:with-param name="codes">z3</xsl:with-param>
4638 </xsl:call-template>
4641 <xsl:value-of select="marc:subfield[@code='u']"/>
4645 <xsl:template name="relatedTypeAttribute">
4648 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">
4649 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='a'">
4650 <xsl:attribute name="type">earlier</xsl:attribute>
4652 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='b'">
4653 <xsl:attribute name="type">later</xsl:attribute>
4655 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='t'">
4656 <xsl:attribute name="type">parentOrg</xsl:attribute>
4658 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='g'">
4659 <xsl:attribute name="type">broader</xsl:attribute>
4661 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='h'">
4662 <xsl:attribute name="type">narrower</xsl:attribute>
4664 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='r'">
4665 <xsl:attribute name="type">other</xsl:attribute>
4667 <xsl:if test="contains('fin|', substring(marc:subfield[@code='w'],1,1))">
4668 <xsl:attribute name="type">other</xsl:attribute>
4671 <xsl:when test="@tag=530 or @tag=730">
4672 <xsl:attribute name="type">other</xsl:attribute>
4676 <xsl:attribute name="type">equivalent</xsl:attribute>
4679 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4684 <xsl:template name="variantTypeAttribute">
4687 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">
4688 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='d'">
4689 <xsl:attribute name="type">acronym</xsl:attribute>
4691 <xsl:if test="substring(marc:subfield[@code='w'],1,1)='n'">
4692 <xsl:attribute name="type">other</xsl:attribute>
4694 <xsl:if test="contains('fit', substring(marc:subfield[@code='w'],1,1))">
4695 <xsl:attribute name="type">other</xsl:attribute>
4700 <xsl:attribute name="type">other</xsl:attribute>
4703 <xsl:apply-templates select="marc:subfield[@code='i']"/>
4706 <xsl:template name="setAuthority">
4708 <!-- can be called from the datafield or subfield level, so "..//@tag" means
4709 the tag can be at the subfield's parent level or at the datafields own level -->
4712 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='a'">
4713 <xsl:attribute name="authority">
4714 <xsl:text>naf</xsl:text>
4718 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='a' and $controlField008-14='b'">
4719 <xsl:attribute name="authority">
4720 <xsl:text>lcsh</xsl:text>
4724 test="ancestor-or-self::marc:datafield/@tag=100 and (@ind1=0 or @ind1=1) and $controlField008-11='k'">
4725 <xsl:attribute name="authority">
4726 <xsl:text>lacnaf</xsl:text>
4730 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='a' and $controlField008-14='b'">
4731 <xsl:attribute name="authority">
4732 <xsl:text>lcsh</xsl:text>
4736 test="ancestor-or-self::marc:datafield/@tag=100 and @ind1=3 and $controlField008-11='k' and $controlField008-14='b'">
4737 <xsl:attribute name="authority">cash</xsl:attribute>
4740 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='a'">
4741 <xsl:attribute name="authority">naf</xsl:attribute>
4744 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='a' and $controlField008-14='b'">
4745 <xsl:attribute name="authority">lcsh</xsl:attribute>
4748 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='a'">
4749 <xsl:attribute name="authority">
4750 <xsl:text>lacnaf</xsl:text>
4754 test="ancestor-or-self::marc:datafield/@tag=110 and $controlField008-11='k' and $controlField008-14='b'">
4755 <xsl:attribute name="authority">
4756 <xsl:text>cash</xsl:text>
4760 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='b'">
4761 <xsl:attribute name="authority">
4762 <xsl:text>lcshcl</xsl:text>
4766 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'">
4767 <xsl:attribute name="authority">
4768 <xsl:text>nlmnaf</xsl:text>
4772 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'">
4773 <xsl:attribute name="authority">
4774 <xsl:text>nalnaf</xsl:text>
4778 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='r'">
4779 <xsl:attribute name="authority">
4780 <xsl:text>aat</xsl:text>
4784 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='s'">
4785 <xsl:attribute name="authority">sears</xsl:attribute>
4788 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='v'">
4789 <xsl:attribute name="authority">rvm</xsl:attribute>
4792 test="100 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 155 and $controlField008-11='z'">
4793 <xsl:attribute name="authority">
4795 select="../marc:datafield[ancestor-or-self::marc:datafield/@tag=040]/marc:subfield[@code='f']"
4800 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='a'">
4801 <xsl:attribute name="authority">
4802 <xsl:text>naf</xsl:text>
4806 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='a' and $controlField008-14='b'">
4807 <xsl:attribute name="authority">
4808 <xsl:text>lcsh</xsl:text>
4812 test="(ancestor-or-self::marc:datafield/@tag=111 or ancestor-or-self::marc:datafield/@tag=130) and $controlField008-11='k' ">
4813 <xsl:attribute name="authority">
4814 <xsl:text>lacnaf</xsl:text>
4818 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' ">
4819 <xsl:attribute name="authority">
4820 <xsl:text>lcsh</xsl:text>
4824 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' ">
4825 <xsl:attribute name="authority">
4826 <xsl:text>lcsh</xsl:text>
4830 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' ">
4831 <xsl:attribute name="authority">
4832 <xsl:text>mesh</xsl:text>
4836 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' ">
4837 <xsl:attribute name="authority">
4838 <xsl:text>nal</xsl:text>
4842 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' ">
4843 <xsl:attribute name="authority">
4844 <xsl:text>cash</xsl:text>
4848 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='a'">
4849 <xsl:attribute name="authority">
4850 <xsl:text>naf</xsl:text>
4854 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='a' and $controlField008-14='b'">
4855 <xsl:attribute name="authority">lcsh</xsl:attribute>
4858 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='a'">
4859 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4862 test="ancestor-or-self::marc:datafield/@tag=151 and $controlField008-11='k' and $controlField008-14='b'">
4863 <xsl:attribute name="authority">cash</xsl:attribute>
4866 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'">
4867 <xsl:attribute name="authority">lcsh</xsl:attribute>
4870 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='0'">
4871 <xsl:attribute name="authority">naf</xsl:attribute>
4874 test="ancestor-or-self::marc:datafield/@tag=700 and (@ind1='0' or @ind1='1') and @ind2='5'">
4875 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4877 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='0'">
4878 <xsl:attribute name="authority">lcsh</xsl:attribute>
4880 <xsl:when test="ancestor-or-self::marc:datafield/@tag=700 and @ind1='3' and @ind2='5'">
4881 <xsl:attribute name="authority">cash</xsl:attribute>
4884 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='1'">
4885 <xsl:attribute name="authority">lcshcl</xsl:attribute>
4888 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'">
4889 <xsl:attribute name="authority">nlmnaf</xsl:attribute>
4892 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'">
4893 <xsl:attribute name="authority">nalnaf</xsl:attribute>
4896 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='6'">
4897 <xsl:attribute name="authority">rvm</xsl:attribute>
4900 test="(700 <= ancestor-or-self::marc:datafield/@tag and ancestor-or-self::marc:datafield/@tag <= 755 ) and @ind2='7'">
4901 <xsl:attribute name="authority">
4902 <xsl:value-of select="marc:subfield[@code='2']"/>
4906 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'">
4907 <xsl:attribute name="authority">lacnaf</xsl:attribute>
4910 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'">
4911 <xsl:attribute name="authority">naf</xsl:attribute>
4914 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'">
4915 <xsl:attribute name="authority">lcsh</xsl:attribute>
4918 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'">
4919 <xsl:attribute name="authority">mesh</xsl:attribute>
4922 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'">
4923 <xsl:attribute name="authority">nal</xsl:attribute>
4926 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'">
4927 <xsl:attribute name="authority">cash</xsl:attribute>
4931 <xsl:template match="*"/>
4932 </xsl:stylesheet>$XSLT$);
4935 SELECT evergreen.upgrade_deps_block_check('1069', :eg_version); --gmcharlt/kmlussier
4937 -- subset of types listed in https://www.loc.gov/marc/authority/ad1xx3xx.html
4938 -- for now, ignoring subdivisions
4939 CREATE TYPE authority.heading_type AS ENUM (
4945 'chronological_term',
4949 'medium_of_performance_term'
4952 CREATE TYPE authority.variant_heading_type AS ENUM (
4961 CREATE TYPE authority.related_heading_type AS ENUM (
4964 'parent organization',
4971 CREATE TYPE authority.heading_purpose AS ENUM (
4977 CREATE TABLE authority.heading_field (
4978 id SERIAL PRIMARY KEY,
4979 heading_type authority.heading_type NOT NULL,
4980 heading_purpose authority.heading_purpose NOT NULL,
4981 label TEXT NOT NULL,
4982 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mads21',
4983 heading_xpath TEXT NOT NULL,
4984 component_xpath TEXT NOT NULL,
4985 type_xpath TEXT NULL, -- to extract related or variant type
4986 thesaurus_xpath TEXT NULL,
4987 thesaurus_override_xpath TEXT NULL,
4991 CREATE TABLE authority.heading_field_norm_map (
4992 id SERIAL PRIMARY KEY,
4993 field INT NOT NULL REFERENCES authority.heading_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
4994 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
4996 pos INT NOT NULL DEFAULT 0
4999 INSERT INTO authority.heading_field(heading_type, heading_purpose, label, heading_xpath, component_xpath, type_xpath, thesaurus_xpath, thesaurus_override_xpath) VALUES
5000 ( 'topical_term', 'main', 'Main Topical Term', '/mads21:mads/mads21:authority', '//mads21:topic', NULL, '/mads21:mads/mads21:authority/mads21:topic[1]/@authority', NULL )
5001 ,( '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')
5002 ,( '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')
5003 ,( 'personal_name', 'main', 'Main Personal Name', '/mads21:mads/mads21:authority', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5004 ,( 'personal_name', 'variant', 'Variant Personal Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="personal"]', NULL, NULL, NULL )
5005 ,( 'personal_name', 'related', 'Related Personal Name', '/mads21:mads/mads21:related', '//mads21:name[@type="personal"]', '/mads21:related/@type', NULL, NULL )
5006 ,( 'corporate_name', 'main', 'Main Corporate name', '/mads21:mads/mads21:authority', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5007 ,( 'corporate_name', 'variant', 'Variant Corporate Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="corporate"]', NULL, NULL, NULL )
5008 ,( 'corporate_name', 'related', 'Related Corporate Name', '/mads21:mads/mads21:related', '//mads21:name[@type="corporate"]', '/mads21:related/@type', NULL, NULL )
5009 ,( 'meeting_name', 'main', 'Main Meeting name', '/mads21:mads/mads21:authority', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5010 ,( 'meeting_name', 'variant', 'Variant Meeting Name', '/mads21:mads/mads21:variant', '//mads21:name[@type="conference"]', NULL, NULL, NULL )
5011 ,( 'meeting_name', 'related', 'Related Meeting Name', '/mads21:mads/mads21:related', '//mads21:name[@type="meeting"]', '/mads21:related/@type', NULL, NULL )
5012 ,( 'geographic_name', 'main', 'Main Geographic Term', '/mads21:mads/mads21:authority', '//mads21:geographic', NULL, '/mads21:mads/mads21:authority/mads21:geographic[1]/@authority', NULL )
5013 ,( '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')
5014 ,( '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')
5015 ,( 'genre_form_term', 'main', 'Main Genre/Form Term', '/mads21:mads/mads21:authority', '//mads21:genre', NULL, '/mads21:mads/mads21:authority/mads21:genre[1]/@authority', NULL )
5016 ,( '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')
5017 ,( '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')
5018 ,( 'chronological_term', 'main', 'Main Chronological Term', '/mads21:mads/mads21:authority', '//mads21:temporal', NULL, '/mads21:mads/mads21:authority/mads21:temporal[1]/@authority', NULL )
5019 ,( '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')
5020 ,( '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')
5021 ,( 'uniform_title', 'main', 'Main Uniform Title', '/mads21:mads/mads21:authority', '//mads21:title', NULL, '/mads21:mads/mads21:authority/mads21:title[1]/@authority', NULL )
5022 ,( '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')
5023 ,( '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')
5026 -- NACO normalize all the things
5027 INSERT INTO authority.heading_field_norm_map (field, norm, pos)
5029 FROM authority.heading_field;
5031 CREATE TYPE authority.heading AS (
5033 type authority.heading_type,
5034 purpose authority.heading_purpose,
5035 variant_type authority.variant_heading_type,
5036 related_type authority.related_heading_type,
5039 normalized_heading TEXT
5042 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5044 idx authority.heading_field%ROWTYPE;
5045 xfrm config.xml_transform%ROWTYPE;
5047 transformed_xml TEXT;
5049 heading_node_list TEXT[];
5050 component_node TEXT;
5051 component_node_list TEXT[];
5053 normalized_text TEXT;
5058 base_thesaurus TEXT := NULL;
5059 output_row authority.heading;
5062 -- Loop over the indexing entries
5063 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
5065 output_row.field := idx.id;
5066 output_row.type := idx.heading_type;
5067 output_row.purpose := idx.heading_purpose;
5069 joiner := COALESCE(idx.joiner, ' ');
5071 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
5073 -- See if we can skip the XSLT ... it's expensive
5074 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5075 -- Can't skip the transform
5076 IF xfrm.xslt <> '---' THEN
5077 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
5079 transformed_xml := marc;
5082 prev_xfrm := xfrm.name;
5085 IF idx.thesaurus_xpath IS NOT NULL THEN
5086 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5089 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5091 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
5093 CONTINUE WHEN heading_node !~ E'^\\s*<';
5095 output_row.variant_type := NULL;
5096 output_row.related_type := NULL;
5097 output_row.thesaurus := NULL;
5098 output_row.heading := NULL;
5100 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
5101 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5103 output_row.variant_type := type_value;
5104 EXCEPTION WHEN invalid_text_representation THEN
5105 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
5108 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
5109 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5111 output_row.related_type := type_value;
5112 EXCEPTION WHEN invalid_text_representation THEN
5113 RAISE NOTICE 'Do not recognize related heading type %', type_value;
5117 IF idx.thesaurus_override_xpath IS NOT NULL THEN
5118 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
5120 IF output_row.thesaurus IS NULL THEN
5121 output_row.thesaurus := base_thesaurus;
5126 -- now iterate over components of heading
5127 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5128 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
5129 -- XXX much of this should be moved into oils_xpath_string...
5130 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
5131 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
5132 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
5133 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
5137 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
5139 IF raw_text IS NOT NULL THEN
5140 raw_text := raw_text || joiner;
5143 raw_text := COALESCE(raw_text,'') || curr_text;
5146 IF raw_text IS NOT NULL THEN
5147 output_row.heading := raw_text;
5148 normalized_text := raw_text;
5151 SELECT n.func AS func,
5152 n.param_count AS param_count,
5154 FROM config.index_normalizer n
5155 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
5156 WHERE m.field = idx.id
5159 EXECUTE 'SELECT ' || normalizer.func || '(' ||
5160 quote_literal( normalized_text ) ||
5162 WHEN normalizer.param_count > 0
5163 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
5166 ')' INTO normalized_text;
5170 output_row.normalized_heading := normalized_text;
5172 RETURN NEXT output_row;
5178 $func$ LANGUAGE PLPGSQL;
5180 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
5182 auth authority.record_entry%ROWTYPE;
5183 output_row authority.heading;
5186 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
5188 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
5190 $func$ LANGUAGE PLPGSQL;
5192 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5194 res authority.simple_heading%ROWTYPE;
5195 acsaf authority.control_set_authority_field%ROWTYPE;
5196 heading_row authority.heading%ROWTYPE;
5207 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
5210 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5212 IF cset IS NULL THEN
5213 SELECT control_set INTO cset
5214 FROM authority.control_set_authority_field
5215 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5219 res.record := auth_id;
5220 res.thesaurus := authority.extract_thesaurus(marcxml);
5222 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5223 res.atag := acsaf.id;
5225 IF acsaf.heading_field IS NULL THEN
5226 tag_used := acsaf.tag;
5227 nfi_used := acsaf.nfi;
5228 joiner_text := COALESCE(acsaf.joiner, ' ');
5230 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
5232 heading_text := COALESCE(
5233 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
5237 IF nfi_used IS NOT NULL THEN
5239 sort_text := SUBSTRING(
5244 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
5256 sort_text := heading_text;
5259 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5260 res.value := heading_text;
5261 res.sort_value := public.naco_normalize(sort_text);
5262 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5268 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
5269 res.value := heading_row.heading;
5270 res.sort_value := heading_row.normalized_heading;
5271 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5279 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5281 ALTER TABLE authority.control_set_authority_field ADD COLUMN heading_field INTEGER REFERENCES authority.heading_field(id);
5283 UPDATE authority.control_set_authority_field acsaf
5284 SET heading_field = ahf.id
5285 FROM authority.heading_field ahf
5288 AND ahf.heading_purpose = 'main'
5289 AND ahf.heading_type = 'personal_name';
5290 UPDATE authority.control_set_authority_field acsaf
5291 SET heading_field = ahf.id
5292 FROM authority.heading_field ahf
5295 AND ahf.heading_purpose = 'variant'
5296 AND ahf.heading_type = 'personal_name';
5297 UPDATE authority.control_set_authority_field acsaf
5298 SET heading_field = ahf.id
5299 FROM authority.heading_field ahf
5302 AND ahf.heading_purpose = 'related'
5303 AND ahf.heading_type = 'personal_name';
5305 UPDATE authority.control_set_authority_field acsaf
5306 SET heading_field = ahf.id
5307 FROM authority.heading_field ahf
5310 AND ahf.heading_purpose = 'main'
5311 AND ahf.heading_type = 'corporate_name';
5312 UPDATE authority.control_set_authority_field acsaf
5313 SET heading_field = ahf.id
5314 FROM authority.heading_field ahf
5317 AND ahf.heading_purpose = 'variant'
5318 AND ahf.heading_type = 'corporate_name';
5319 UPDATE authority.control_set_authority_field acsaf
5320 SET heading_field = ahf.id
5321 FROM authority.heading_field ahf
5324 AND ahf.heading_purpose = 'related'
5325 AND ahf.heading_type = 'corporate_name';
5327 UPDATE authority.control_set_authority_field acsaf
5328 SET heading_field = ahf.id
5329 FROM authority.heading_field ahf
5332 AND ahf.heading_purpose = 'main'
5333 AND ahf.heading_type = 'meeting_name';
5334 UPDATE authority.control_set_authority_field acsaf
5335 SET heading_field = ahf.id
5336 FROM authority.heading_field ahf
5339 AND ahf.heading_purpose = 'variant'
5340 AND ahf.heading_type = 'meeting_name';
5341 UPDATE authority.control_set_authority_field acsaf
5342 SET heading_field = ahf.id
5343 FROM authority.heading_field ahf
5346 AND ahf.heading_purpose = 'related'
5347 AND ahf.heading_type = 'meeting_name';
5349 UPDATE authority.control_set_authority_field acsaf
5350 SET heading_field = ahf.id
5351 FROM authority.heading_field ahf
5354 AND ahf.heading_purpose = 'main'
5355 AND ahf.heading_type = 'uniform_title';
5356 UPDATE authority.control_set_authority_field acsaf
5357 SET heading_field = ahf.id
5358 FROM authority.heading_field ahf
5361 AND ahf.heading_purpose = 'variant'
5362 AND ahf.heading_type = 'uniform_title';
5363 UPDATE authority.control_set_authority_field acsaf
5364 SET heading_field = ahf.id
5365 FROM authority.heading_field ahf
5368 AND ahf.heading_purpose = 'related'
5369 AND ahf.heading_type = 'uniform_title';
5371 UPDATE authority.control_set_authority_field acsaf
5372 SET heading_field = ahf.id
5373 FROM authority.heading_field ahf
5376 AND ahf.heading_purpose = 'main'
5377 AND ahf.heading_type = 'topical_term';
5378 UPDATE authority.control_set_authority_field acsaf
5379 SET heading_field = ahf.id
5380 FROM authority.heading_field ahf
5383 AND ahf.heading_purpose = 'variant'
5384 AND ahf.heading_type = 'topical_term';
5385 UPDATE authority.control_set_authority_field acsaf
5386 SET heading_field = ahf.id
5387 FROM authority.heading_field ahf
5390 AND ahf.heading_purpose = 'related'
5391 AND ahf.heading_type = 'topical_term';
5393 UPDATE authority.control_set_authority_field acsaf
5394 SET heading_field = ahf.id
5395 FROM authority.heading_field ahf
5398 AND ahf.heading_purpose = 'main'
5399 AND ahf.heading_type = 'geographic_name';
5400 UPDATE authority.control_set_authority_field acsaf
5401 SET heading_field = ahf.id
5402 FROM authority.heading_field ahf
5405 AND ahf.heading_purpose = 'variant'
5406 AND ahf.heading_type = 'geographic_name';
5407 UPDATE authority.control_set_authority_field acsaf
5408 SET heading_field = ahf.id
5409 FROM authority.heading_field ahf
5412 AND ahf.heading_purpose = 'related'
5413 AND ahf.heading_type = 'geographic_name';
5415 UPDATE authority.control_set_authority_field acsaf
5416 SET heading_field = ahf.id
5417 FROM authority.heading_field ahf
5420 AND ahf.heading_purpose = 'main'
5421 AND ahf.heading_type = 'genre_form_term';
5422 UPDATE authority.control_set_authority_field acsaf
5423 SET heading_field = ahf.id
5424 FROM authority.heading_field ahf
5427 AND ahf.heading_purpose = 'variant'
5428 AND ahf.heading_type = 'genre_form_term';
5429 UPDATE authority.control_set_authority_field acsaf
5430 SET heading_field = ahf.id
5431 FROM authority.heading_field ahf
5434 AND ahf.heading_purpose = 'related'
5435 AND ahf.heading_type = 'genre_form_term';
5438 SELECT evergreen.upgrade_deps_block_check('1070', :eg_version); --miker/gmcharlt/kmlussier
5440 CREATE TRIGGER thes_code_tracking_trigger
5441 AFTER UPDATE ON authority.thesaurus
5442 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
5444 ALTER TABLE authority.thesaurus ADD COLUMN short_code TEXT, ADD COLUMN uri TEXT;
5446 DELETE FROM authority.thesaurus WHERE control_set = 1 AND code NOT IN ('n',' ','|');
5447 UPDATE authority.thesaurus SET short_code = code;
5449 CREATE TEMP TABLE thesauri (code text, uri text, name text, xlate hstore);
5450 COPY thesauri (code, uri, name, xlate) FROM STDIN;
5451 migfg http://id.loc.gov/vocabulary/genreFormSchemes/migfg Moving image genre-form guide
5452 reveal http://id.loc.gov/vocabulary/genreFormSchemes/reveal REVEAL: fiction indexing and genre headings
5453 dct http://id.loc.gov/vocabulary/genreFormSchemes/dct Dublin Core list of resource types
5454 gmgpc http://id.loc.gov/vocabulary/genreFormSchemes/gmgpc Thesaurus for graphic materials: TGM II, Genre and physical characteristic terms
5455 rbgenr http://id.loc.gov/vocabulary/genreFormSchemes/rbgenr Genre terms: a thesaurus for use in rare book and special collections cataloguing
5456 sgp http://id.loc.gov/vocabulary/genreFormSchemes/sgp Svenska genrebeteckningar fr periodika "sv"=>"Svenska genrebeteckningar fr periodika"
5457 estc http://id.loc.gov/vocabulary/genreFormSchemes/estc Eighteenth century short title catalogue, the cataloguing rules. New ed.
5458 ftamc http://id.loc.gov/vocabulary/genreFormSchemes/ftamc Form terms for archival and manuscripts control
5459 alett http://id.loc.gov/vocabulary/genreFormSchemes/alett An alphabetical list of English text types
5460 gtlm http://id.loc.gov/vocabulary/genreFormSchemes/gtlm Genre terms for law materials: a thesaurus
5461 rbprov http://id.loc.gov/vocabulary/genreFormSchemes/rbprov Provenance evidence: a thesaurus for use in rare book and special collections cataloging
5462 rbbin http://id.loc.gov/vocabulary/genreFormSchemes/rbbin Binding terms: a thesaurus for use in rare book and special collections cataloguing
5463 fbg http://id.loc.gov/vocabulary/genreFormSchemes/fbg Films by genre /dd>
5464 isbdmedia http://id.loc.gov/vocabulary/genreFormSchemes/isbdmedia ISBD Area 0 [media]
5465 marccategory http://id.loc.gov/vocabulary/genreFormSchemes/marccategory MARC form category term list
5466 gnd-music http://id.loc.gov/vocabulary/genreFormSchemes/gnd-music Gemeinsame Normdatei: Musikalische Ausgabeform
5467 proysen http://id.loc.gov/vocabulary/genreFormSchemes/proysen Prøysen: emneord for Prøysen-bibliografien
5468 rdacarrier http://id.loc.gov/vocabulary/genreFormSchemes/rdacarrier Term and code list for RDA carrier types
5469 gnd http://id.loc.gov/vocabulary/genreFormSchemes/gnd Gemeinsame Normdatei
5470 cjh http://id.loc.gov/vocabulary/genreFormSchemes/cjh Center for Jewish History thesaurus
5471 rbpri http://id.loc.gov/vocabulary/genreFormSchemes/rbpri Printing & publishing evidence: a thesaurus for use in rare book and special collections cataloging
5472 fgtpcm http://id.loc.gov/vocabulary/genreFormSchemes/fgtpcm Form/genre terms for printed cartoon material
5473 rbpub http://id.loc.gov/vocabulary/genreFormSchemes/rbpub Printing and publishing evidence: a thesaurus for use in rare book and special collections cataloging
5474 gmd http://id.loc.gov/vocabulary/genreFormSchemes/gmd Anglo-American Cataloguing Rules general material designation
5475 rbpap http://id.loc.gov/vocabulary/genreFormSchemes/rbpap Paper terms: a thesaurus for use in rare book and special collections cataloging
5476 rdamedia http://id.loc.gov/vocabulary/genreFormSchemes/rdamedia Term and code list for RDA media types
5477 marcsmd http://id.loc.gov/vocabulary/genreFormSchemes/marcsmd MARC specific material form term list
5478 saogf http://id.loc.gov/vocabulary/genreFormSchemes/saogf Svenska ämnesord - Genre/Form "sv"=>"Svenska ämnesord - Genre/Form"
5479 lcgft http://id.loc.gov/vocabulary/genreFormSchemes/lcgft Library of Congress genre/form terms for library and archival materials
5480 muzeukv http://id.loc.gov/vocabulary/genreFormSchemes/muzeukv MuzeVideo UK DVD and UMD film genre classification
5481 mim http://id.loc.gov/vocabulary/genreFormSchemes/mim Moving image materials: genre terms
5482 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
5483 gnd-content http://id.loc.gov/vocabulary/genreFormSchemes/gnd-content Gemeinsame Normdatei: Beschreibung des Inhalts
5484 bgtchm http://id.loc.gov/vocabulary/genreFormSchemes/bgtchm Basic genre terms for cultural heritage materials
5485 gsafd http://id.loc.gov/vocabulary/genreFormSchemes/gsafd Guidelines on subject access to individual works of fiction, drama, etc
5486 marcform http://id.loc.gov/vocabulary/genreFormSchemes/marcform MARC form of item term list
5487 marcgt http://id.loc.gov/vocabulary/genreFormSchemes/marcgt MARC genre terms
5488 barngf http://id.loc.gov/vocabulary/genreFormSchemes/barngf Svenska ämnesord för barn - Genre/Form "sv"=>"Svenska ämnesord för barn - Genre/Form"
5489 ngl http://id.loc.gov/vocabulary/genreFormSchemes/ngl Newspaper genre list
5490 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"
5491 tgfbne http://id.loc.gov/vocabulary/genreFormSchemes/tgfbne Términos de género/forma de la Biblioteca Nacional de España
5492 nbdbgf http://id.loc.gov/vocabulary/genreFormSchemes/nbdbgf NBD Biblion Genres Fictie
5493 rbtyp http://id.loc.gov/vocabulary/genreFormSchemes/rbtyp Type evidence: a thesaurus for use in rare book and special collections cataloging
5494 radfg http://id.loc.gov/vocabulary/genreFormSchemes/radfg Radio form / genre terms guide
5495 gnd-carrier http://id.loc.gov/vocabulary/genreFormSchemes/gnd-carrier Gemeinsame Normdatei: Datenträgertyp
5496 gatbeg http://id.loc.gov/vocabulary/genreFormSchemes/gatbeg Gattungsbegriffe "de"=>"Gattungsbegriffe"
5497 rdacontent http://id.loc.gov/vocabulary/genreFormSchemes/rdacontent Term and code list for RDA content types
5498 isbdcontent http://id.loc.gov/vocabulary/genreFormSchemes/isbdcontent ISBD Area 0 [content]
5499 nimafc http://id.loc.gov/vocabulary/genreFormSchemes/nimafc NIMA form codes
5500 amg http://id.loc.gov/vocabulary/genreFormSchemes/amg Audiovisual material glossary
5501 local http://id.loc.gov/vocabulary/subjectSchemes/local Locally assigned term
5502 taika http://id.loc.gov/vocabulary/subjectSchemes/taika Taideteollisuuden asiasanasto "fi"=>"Taideteollisuuden asiasanasto"
5503 nasat http://id.loc.gov/vocabulary/subjectSchemes/nasat NASA thesaurus
5504 rswkaf http://id.loc.gov/vocabulary/subjectSchemes/rswkaf Alternativform zum Hauptschlagwort "de"=>"Alternativform zum Hauptschlagwort"
5505 jhpk http://id.loc.gov/vocabulary/subjectSchemes/jhpk JÄ
\99zyk haseÅ
\82 przedmiotowych KABA "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych KABA"
5506 asrcrfcd http://id.loc.gov/vocabulary/subjectSchemes/asrcrfcd Australian Standard Research Classification: Research Fields, Courses and Disciplines (RFCD) classification
5507 bt http://id.loc.gov/vocabulary/subjectSchemes/bt Bioethics thesaurus
5508 lcstt http://id.loc.gov/vocabulary/subjectSchemes/lcstt List of Chinese subject terms
5509 netc http://id.loc.gov/vocabulary/subjectSchemes/netc National Emergency Training Center Thesaurus (NETC)
5510 aat http://id.loc.gov/vocabulary/subjectSchemes/aat Art & architecture thesaurus
5511 bet http://id.loc.gov/vocabulary/subjectSchemes/bet British education thesaurus
5512 ncjt http://id.loc.gov/vocabulary/subjectSchemes/ncjt National criminal justice thesaurus
5513 samisk http://id.loc.gov/vocabulary/subjectSchemes/samisk Sami bibliography "no"=>"Sámi bibliografia = Samisk bibliografi (Norge)"
5514 tips http://id.loc.gov/vocabulary/subjectSchemes/tips Tesauro ISOC de psicologÃa "es"=>"Tesauro ISOC de psicologÃa"
5515 ukslc http://id.loc.gov/vocabulary/subjectSchemes/ukslc UK Standard Library Categories
5516 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"
5517 umitrist http://id.loc.gov/vocabulary/subjectSchemes/umitrist University of Michigan Transportation Research Institute structured thesaurus
5518 wgst http://id.loc.gov/vocabulary/subjectSchemes/wgst Washington GILS Subject Tree
5519 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"
5520 ntids http://id.loc.gov/vocabulary/subjectSchemes/ntids Norske tidsskrifter 1700-1820: emneord "no"=>"Norske tidsskrifter 1700-1820: emneord"
5521 kaa http://id.loc.gov/vocabulary/subjectSchemes/kaa Kasvatusalan asiasanasto "fi"=>"Kasvatusalan asiasanasto"
5522 yso http://id.loc.gov/vocabulary/subjectSchemes/yso YSO - Yleinen suomalainen ontologia "fi"=>"YSO - Yleinen suomalainen ontologia"
5523 gcipmedia http://id.loc.gov/vocabulary/subjectSchemes/gcipmedia GAMECIP - Computer Game Media Formats (GAMECIP (Game Metadata and Citation Project))
5524 inspect http://id.loc.gov/vocabulary/subjectSchemes/inspect INSPEC thesaurus
5525 ordnok http://id.loc.gov/vocabulary/subjectSchemes/ordnok Ordnokkelen: tesaurus for kulturminnevern "no"=>"Ordnokkelen: tesaurus for kulturminnevern"
5526 helecon http://id.loc.gov/vocabulary/subjectSchemes/helecon Asiasanasto HELECON-tietikantoihin "fi"=>"Asiasanasto HELECON-tietikantoihin"
5527 dltlt http://id.loc.gov/vocabulary/subjectSchemes/dltlt Cuddon, J. A. A dictionary of literary terms and literary theory
5528 csapa http://id.loc.gov/vocabulary/subjectSchemes/csapa "Controlled vocabulary" in Pollution abstracts
5529 gtt http://id.loc.gov/vocabulary/subjectSchemes/gtt GOO-trefwoorden thesaurus "nl"=>"GOO-trefwoorden thesaurus"
5530 iescs http://id.loc.gov/vocabulary/subjectSchemes/iescs International energy subject categories and scope
5531 itrt http://id.loc.gov/vocabulary/subjectSchemes/itrt International Thesaurus of Refugee Terminology
5532 sanb http://id.loc.gov/vocabulary/subjectSchemes/sanb South African national bibliography authority file
5533 blmlsh http://id.loc.gov/vocabulary/subjectSchemes/blmlsh British Library - Map library subject headings
5534 bhb http://id.loc.gov/vocabulary/subjectSchemes/bhb Bibliography of the Hebrew Book
5535 csh http://id.loc.gov/vocabulary/subjectSchemes/csh Kapsner, Oliver Leonard. Catholic subject headings
5536 fire http://id.loc.gov/vocabulary/subjectSchemes/fire FireTalk, IFSI thesaurus
5537 jlabsh http://id.loc.gov/vocabulary/subjectSchemes/jlabsh Basic subject headings "ja"=>"Kihon kenmei hyômokuhyô"
5538 udc http://id.loc.gov/vocabulary/subjectSchemes/udc Universal decimal classification
5539 lcshac http://id.loc.gov/vocabulary/subjectSchemes/lcshac Children's subject headings in Library of Congress subject headings: supplementary vocabularies
5540 geonet http://id.loc.gov/vocabulary/subjectSchemes/geonet NGA GEOnet Names Server (GNS)
5541 humord http://id.loc.gov/vocabulary/subjectSchemes/humord HUMORD "no"=>"HUMORD"
5542 no-ubo-mr http://id.loc.gov/vocabulary/subjectSchemes/no-ubo-mr Menneskerettighets-tesaurus "no"=>"Menneskerettighets-tesaurus"
5543 sgce http://id.loc.gov/vocabulary/subjectSchemes/sgce COBISS.SI General List of subject headings (English subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5544 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"
5545 thesoz http://id.loc.gov/vocabulary/subjectSchemes/thesoz Thesaurus for the Social Sciences
5546 asth http://id.loc.gov/vocabulary/subjectSchemes/asth Astronomy thesaurus
5547 muzeukc http://id.loc.gov/vocabulary/subjectSchemes/muzeukc MuzeMusic UK classical music classification
5548 norbok http://id.loc.gov/vocabulary/subjectSchemes/norbok Norbok: emneord i Norsk bokfortegnelse "no"=>"Norbok: emneord i Norsk bokfortegnelse"
5549 masa http://id.loc.gov/vocabulary/subjectSchemes/masa Museoalan asiasanasto "fi"=>"Museoalan asiasanasto"
5550 conorsi http://id.loc.gov/vocabulary/subjectSchemes/conorsi CONOR.SI (name authority file) (Maribor, Slovenia: Institut informacijskih znanosti (IZUM))
5551 eurovocen http://id.loc.gov/vocabulary/subjectSchemes/eurovocen Eurovoc thesaurus (English)
5552 kto http://id.loc.gov/vocabulary/subjectSchemes/kto KTO - Kielitieteen ontologia "fi"=>"KTO - Kielitieteen ontologia"
5553 muzvukci http://id.loc.gov/vocabulary/subjectSchemes/muzvukci MuzeVideo UK contributor index
5554 kaunokki http://id.loc.gov/vocabulary/subjectSchemes/kaunokki Kaunokki: kaunokirjallisuuden asiasanasto "fi"=>"Kaunokki: kaunokirjallisuuden asiasanasto"
5555 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"
5556 psychit http://id.loc.gov/vocabulary/subjectSchemes/psychit Thesaurus of psychological index terms.
5557 tlsh http://id.loc.gov/vocabulary/subjectSchemes/tlsh Subject heading authority list
5558 csalsct http://id.loc.gov/vocabulary/subjectSchemes/csalsct CSA life sciences collection thesaurus
5559 ciesiniv http://id.loc.gov/vocabulary/subjectSchemes/ciesiniv CIESIN indexing vocabulary
5560 ebfem http://id.loc.gov/vocabulary/subjectSchemes/ebfem Encabezamientos bilingües de la Fundación Educativa Ana G. Mendez
5561 mero http://id.loc.gov/vocabulary/subjectSchemes/mero MERO - Merenkulkualan ontologia "fi"=>"MERO - Merenkulkualan ontologia"
5562 mmm http://id.loc.gov/vocabulary/subjectSchemes/mmm "Subject key" in Marxism and the mass media
5563 pascal http://id.loc.gov/vocabulary/subjectSchemes/pascal PASCAL database classification scheme "fr"=>"Base de donneés PASCAL: plan de classement"
5564 chirosh http://id.loc.gov/vocabulary/subjectSchemes/chirosh Chiropractic Subject Headings
5565 cilla http://id.loc.gov/vocabulary/subjectSchemes/cilla Cilla: specialtesaurus för musik "fi"=>"Cilla: specialtesaurus för musik"
5566 aiatsisl http://id.loc.gov/vocabulary/subjectSchemes/aiatsisl AIATSIS language thesaurus
5567 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"
5568 lctgm http://id.loc.gov/vocabulary/subjectSchemes/lctgm Thesaurus for graphic materials: TGM I, Subject terms
5569 muso http://id.loc.gov/vocabulary/subjectSchemes/muso MUSO - Ontologi för musik "fi"=>"MUSO - Ontologi för musik"
5570 blcpss http://id.loc.gov/vocabulary/subjectSchemes/blcpss COMPASS subject authority system
5571 fast http://id.loc.gov/vocabulary/subjectSchemes/fast Faceted application of subject terminology
5572 bisacmt http://id.loc.gov/vocabulary/subjectSchemes/bisacmt BISAC Merchandising Themes
5573 lapponica http://id.loc.gov/vocabulary/subjectSchemes/lapponica Lapponica "fi"=>"Lapponica"
5574 juho http://id.loc.gov/vocabulary/subjectSchemes/juho JUHO - Julkishallinnon ontologia "fi"=>"JUHO - Julkishallinnon ontologia"
5575 idas http://id.loc.gov/vocabulary/subjectSchemes/idas ID-Archivschlüssel "de"=>"ID-Archivschlüssel"
5576 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."
5577 test http://id.loc.gov/vocabulary/subjectSchemes/test Thesaurus of engineering and scientific terms
5578 finmesh http://id.loc.gov/vocabulary/subjectSchemes/finmesh FinMeSH "fi"=>"FinMeSH"
5579 kssbar http://id.loc.gov/vocabulary/subjectSchemes/kssbar Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del "sv"=>"Klassifikationssystem for svenska bibliotek. Ã
\84mnesordregister. Alfabetisk del"
5580 kupu http://id.loc.gov/vocabulary/subjectSchemes/kupu Maori Wordnet "mi"=>"He puna kupu"
5581 rpe http://id.loc.gov/vocabulary/subjectSchemes/rpe Rubricator on economics "ru"=>"Rubrikator po ekonomike"
5582 dit http://id.loc.gov/vocabulary/subjectSchemes/dit Defense intelligence thesaurus
5583 she http://id.loc.gov/vocabulary/subjectSchemes/she SHE: subject headings for engineering
5584 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"
5585 msc http://id.loc.gov/vocabulary/subjectSchemes/msc Mathematical subject classification
5586 muzeukn http://id.loc.gov/vocabulary/subjectSchemes/muzeukn MuzeMusic UK non-classical music classification
5587 ipsp http://id.loc.gov/vocabulary/subjectSchemes/ipsp Defense intelligence production schedule.
5588 sthus http://id.loc.gov/vocabulary/subjectSchemes/sthus Subject Taxonomy of the History of U.S. Foreign Relations
5589 poliscit http://id.loc.gov/vocabulary/subjectSchemes/poliscit Political science thesaurus II
5590 qtglit http://id.loc.gov/vocabulary/subjectSchemes/qtglit A queer thesaurus : an international thesaurus of gay and lesbian index terms
5591 unbist http://id.loc.gov/vocabulary/subjectSchemes/unbist UNBIS thesaurus
5592 gcipplatform http://id.loc.gov/vocabulary/subjectSchemes/gcipplatform GAMECIP - Computer Game Platforms (GAMECIP (Game Metadata and Citation Project))
5593 puho http://id.loc.gov/vocabulary/subjectSchemes/puho PUHO - Puolustushallinnon ontologia "fi"=>"PUHO - Puolustushallinnon ontologia"
5594 thub http://id.loc.gov/vocabulary/subjectSchemes/thub Thesaurus de la Universitat de Barcelona "ca"=>"Thesaurus de la Universitat de Barcelona"
5595 ndlsh http://id.loc.gov/vocabulary/subjectSchemes/ndlsh National Diet Library list of subject headings "ja"=>"Koktsu Kokkai Toshokan kenmei hyômokuhyô"
5596 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"
5597 idszbzzh http://id.loc.gov/vocabulary/subjectSchemes/idszbzzh Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Handschriftenabteilung"
5598 unbisn http://id.loc.gov/vocabulary/subjectSchemes/unbisn UNBIS name authority list (New York, NY: Dag Hammarskjld Library, United Nations; : Chadwyck-Healey)
5599 rswk http://id.loc.gov/vocabulary/subjectSchemes/rswk Regeln für den Schlagwortkatalog "de"=>"Regeln für den Schlagwortkatalog"
5600 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"
5601 biccbmc http://id.loc.gov/vocabulary/subjectSchemes/biccbmc BIC Children's Books Marketing Classifications
5602 kulo http://id.loc.gov/vocabulary/subjectSchemes/kulo KULO - Kulttuurien tutkimuksen ontologia "fi"=>"KULO - Kulttuurien tutkimuksen ontologia"
5603 popinte http://id.loc.gov/vocabulary/subjectSchemes/popinte POPIN thesaurus: population multilingual thesaurus
5604 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"
5605 atg http://id.loc.gov/vocabulary/subjectSchemes/atg Agricultural thesaurus and glossary
5606 eflch http://id.loc.gov/vocabulary/subjectSchemes/eflch E4Libraries Category Headings
5607 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"
5608 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"
5609 csahssa http://id.loc.gov/vocabulary/subjectSchemes/csahssa "Controlled vocabulary" in Health and safety science abstracts
5610 sigle http://id.loc.gov/vocabulary/subjectSchemes/sigle SIGLE manual, Part 2, Subject category list
5611 blnpn http://id.loc.gov/vocabulary/subjectSchemes/blnpn British Library newspaper place names
5612 asrctoa http://id.loc.gov/vocabulary/subjectSchemes/asrctoa Australian Standard Research Classification: Type of Activity (TOA) classification
5613 lcdgt http://id.loc.gov/vocabulary/subjectSchemes/lcdgt Library of Congress demographic group term and code List
5614 bokbas http://id.loc.gov/vocabulary/subjectSchemes/bokbas Bokbasen "no"=>"Bokbasen"
5615 gnis http://id.loc.gov/vocabulary/subjectSchemes/gnis Geographic Names Information System (GNIS)
5616 nbiemnfag http://id.loc.gov/vocabulary/subjectSchemes/nbiemnfag NBIs emneordsliste for faglitteratur "no"=>"NBIs emneordsliste for faglitteratur"
5617 nlgaf http://id.loc.gov/vocabulary/subjectSchemes/nlgaf Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn "el"=>"Archeio KathierÅ
\8dmenÅ
\8dn EpikephalidÅ
\8dn"
5618 bhashe http://id.loc.gov/vocabulary/subjectSchemes/bhashe BHA, Bibliography of the history of art, subject headings/English
5619 tsht http://id.loc.gov/vocabulary/subjectSchemes/tsht Thesaurus of subject headings for television
5620 scbi http://id.loc.gov/vocabulary/subjectSchemes/scbi Soggettario per i cataloghi delle biblioteche italiane "it"=>"Soggettario per i cataloghi delle biblioteche italiane"
5621 valo http://id.loc.gov/vocabulary/subjectSchemes/valo VALO - Fotografiska ontologin "fi"=>"VALO - Fotografiska ontologin"
5622 wpicsh http://id.loc.gov/vocabulary/subjectSchemes/wpicsh WPIC Library thesaurus of subject headings
5623 aktp http://id.loc.gov/vocabulary/subjectSchemes/aktp AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn "el"=>"AlphavÄ
\93tikos Katalogos ThematikÅ
\8dn PerigrapheÅ
\8dn"
5624 stw http://id.loc.gov/vocabulary/subjectSchemes/stw STW Thesaurus for Economics "de"=>"Standard-Thesaurus Wirtschaft"
5625 mesh http://id.loc.gov/vocabulary/subjectSchemes/mesh Medical subject headings
5626 ica http://id.loc.gov/vocabulary/subjectSchemes/ica Index of Christian art
5627 emnmus http://id.loc.gov/vocabulary/subjectSchemes/emnmus Emneord for musikkdokument i EDB-kataloger "no"=>"Emneord for musikkdokument i EDB-kataloger"
5628 sao http://id.loc.gov/vocabulary/subjectSchemes/sao Svenska ämnesord "sv"=>"Svenska ämnesord"
5629 sgc http://id.loc.gov/vocabulary/subjectSchemes/sgc COBISS.SI General List of subject headings (Slovenian subject headings) "sl"=>"Splošni geslovnik COBISS.SI"
5630 bib1814 http://id.loc.gov/vocabulary/subjectSchemes/bib1814 1814-bibliografi: emneord for 1814-bibliografi "no"=>"1814-bibliografi: emneord for 1814-bibliografi"
5631 bjornson http://id.loc.gov/vocabulary/subjectSchemes/bjornson Bjornson: emneord for Bjornsonbibliografien "no"=>"Bjornson: emneord for Bjornsonbibliografien"
5632 liito http://id.loc.gov/vocabulary/subjectSchemes/liito LIITO - Liiketoimintaontologia "fi"=>"LIITO - Liiketoimintaontologia"
5633 apaist http://id.loc.gov/vocabulary/subjectSchemes/apaist APAIS thesaurus: a list of subject terms used in the Australian Public Affairs Information Service
5634 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)
5635 ntcsd http://id.loc.gov/vocabulary/subjectSchemes/ntcsd "National Translations Center secondary descriptors" in National Translation Center primary subject classification and secondary descriptor
5636 scisshl http://id.loc.gov/vocabulary/subjectSchemes/scisshl SCIS subject headings
5637 opms http://id.loc.gov/vocabulary/subjectSchemes/opms Opetusministeriön asiasanasto "fi"=>"Opetusministeriön asiasanasto"
5638 ttka http://id.loc.gov/vocabulary/subjectSchemes/ttka Teologisen tiedekunnan kirjaston asiasanasto "fi"=>"Teologisen tiedekunnan kirjaston asiasanasto"
5639 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
5640 ysa http://id.loc.gov/vocabulary/subjectSchemes/ysa Yleinen suomalainen asiasanasto "fi"=>"Yleinen suomalainen asiasanasto"
5641 kitu http://id.loc.gov/vocabulary/subjectSchemes/kitu Kirjallisuudentutkimuksen asiasanasto "fi"=>"Kirjallisuudentutkimuksen asiasanasto"
5642 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"
5643 aiatsisp http://id.loc.gov/vocabulary/subjectSchemes/aiatsisp AIATSIS place thesaurus
5644 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é"
5645 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"
5646 ated http://id.loc.gov/vocabulary/subjectSchemes/ated Australian Thesaurus of Education Descriptors (ATED)
5647 cabt http://id.loc.gov/vocabulary/subjectSchemes/cabt CAB thesaurus (Slough [England]: Commonwealth Agricultural Bureaux)
5648 kassu http://id.loc.gov/vocabulary/subjectSchemes/kassu Kassu - Kasvien suomenkieliset nimet "fi"=>"Kassu - Kasvien suomenkieliset nimet"
5649 nbdbt http://id.loc.gov/vocabulary/subjectSchemes/nbdbt NBD Biblion Trefwoordenthesaurus "nl"=>"NBD Biblion Trefwoordenthesaurus"
5650 jhpb http://id.loc.gov/vocabulary/subjectSchemes/jhpb JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej "pl"=>"JÄ
\99zyk haseÅ
\82 przedmiotowych Biblioteki Narodowej"
5651 bidex http://id.loc.gov/vocabulary/subjectSchemes/bidex Bilindex: a bilingual Spanish-English subject heading list
5652 ccsa http://id.loc.gov/vocabulary/subjectSchemes/ccsa Catalogue collectif suisse des affiches "fr"=>"Catalogue collectif suisse des affiches"
5653 noraf http://id.loc.gov/vocabulary/subjectSchemes/noraf Norwegian Authority File
5654 kito http://id.loc.gov/vocabulary/subjectSchemes/kito KITO - Kirjallisuudentutkimuksen ontologia "fi"=>"KITO - Kirjallisuudentutkimuksen ontologia"
5655 tho http://id.loc.gov/vocabulary/subjectSchemes/tho Thesauros HellÄ
\93nikÅ
\8dn Oron "el"=>"Thesauros HellÄ
\93nikÅ
\8dn Oron"
5656 pmont http://id.loc.gov/vocabulary/subjectSchemes/pmont Powerhouse Museum Object Name Thesaurus
5657 ssg http://id.loc.gov/vocabulary/subjectSchemes/ssg Splošni slovenski geslovnik "sl"=>"Splošni slovenski geslovnik"
5658 huc http://id.loc.gov/vocabulary/subjectSchemes/huc U.S. Geological Survey water-supply paper 2294: hydrologic basins unit codes
5659 isis http://id.loc.gov/vocabulary/subjectSchemes/isis "Classification scheme" in Isis
5660 ibsen http://id.loc.gov/vocabulary/subjectSchemes/ibsen Ibsen: emneord for Den internasjonale Ibsen-bibliografien "no"=>"Ibsen: emneord for Den internasjonale Ibsen-bibliografien"
5661 lacnaf http://id.loc.gov/vocabulary/subjectSchemes/lacnaf Library and Archives Canada name authority file
5662 swemesh http://id.loc.gov/vocabulary/subjectSchemes/swemesh Swedish MeSH "sv"=>"Svenska MeSH"
5663 hamsun http://id.loc.gov/vocabulary/subjectSchemes/hamsun Hamsun: emneord for Hamsunbibliografien "no"=>"Hamsun: emneord for Hamsunbibliografien"
5664 qrma http://id.loc.gov/vocabulary/subjectSchemes/qrma List of Arabic subject headings "ar"=>"Qâ'imat ru'ûs al-mawdûât al-'Arabîyah"
5665 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"
5666 ceeus http://id.loc.gov/vocabulary/subjectSchemes/ceeus Counties and equivalent entities of the United States its possessions, and associated areas
5667 taxhs http://id.loc.gov/vocabulary/subjectSchemes/taxhs A taxonomy or human services: a conceptual framework with standardized terminology and definitions for the field
5668 noram http://id.loc.gov/vocabulary/subjectSchemes/noram Noram: emneord for Norsk-amerikansk samling "no"=>"Noram: emneord for Norsk-amerikansk samling"
5669 eurovocfr http://id.loc.gov/vocabulary/subjectSchemes/eurovocfr Eurovoc thesaurus (French)
5670 jurivoc http://id.loc.gov/vocabulary/subjectSchemes/jurivoc JURIVOC
5671 agrifors http://id.loc.gov/vocabulary/subjectSchemes/agrifors AGRIFOREST-sanasto "fi"=>"AGRIFOREST-sanasto"
5672 noubojur http://id.loc.gov/vocabulary/subjectSchemes/noubojur Thesaurus of Law "no"=>"Thesaurus of Law"
5673 pha http://id.loc.gov/vocabulary/subjectSchemes/pha Puolostushallinnon asiasanasto "fi"=>"Puolostushallinnon asiasanasto"
5674 ddcrit http://id.loc.gov/vocabulary/subjectSchemes/ddcrit DDC retrieval and indexing terminology; posting terms with hierarchy and KWOC
5675 mar http://id.loc.gov/vocabulary/subjectSchemes/mar Merenkulun asiasanasto "fi"=>"Merenkulun asiasanasto"
5676 sbt http://id.loc.gov/vocabulary/subjectSchemes/sbt Soggettario Sistema Bibliotecario Ticinese "it"=>"Soggettario Sistema Bibliotecario Ticinese"
5677 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))
5678 kta http://id.loc.gov/vocabulary/subjectSchemes/kta Kielitieteen asiasanasto "fi"=>"Kielitieteen asiasanasto"
5679 snt http://id.loc.gov/vocabulary/subjectSchemes/snt Sexual nomenclature : a thesaurus
5680 francis http://id.loc.gov/vocabulary/subjectSchemes/francis FRANCIS database classification scheme "fr"=>"Base de donneés FRANCIS: plan de classement"
5681 eurovocsl http://id.loc.gov/vocabulary/subjectSchemes/eurovocsl Eurovoc thesaurus "sl"=>"Eurovoc thesaurus"
5682 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"
5683 nlmnaf http://id.loc.gov/vocabulary/subjectSchemes/nlmnaf National Library of Medicine name authority file
5684 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"
5685 sipri http://id.loc.gov/vocabulary/subjectSchemes/sipri SIPRI library thesaurus
5686 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"
5687 tucua http://id.loc.gov/vocabulary/subjectSchemes/tucua Thesaurus for use in college and university archives
5688 pmbok http://id.loc.gov/vocabulary/subjectSchemes/pmbok Guide to the project management body of knowledge (PMBOK Guide)
5689 agrovoc http://id.loc.gov/vocabulary/subjectSchemes/agrovoc AGROVOC multilingual agricultural thesaurus
5690 nal http://id.loc.gov/vocabulary/subjectSchemes/nal National Agricultural Library subject headings
5691 lnmmbr http://id.loc.gov/vocabulary/subjectSchemes/lnmmbr Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas "lt"=>"Lietuvos nacionalines Martyno Mazvydo bibliotekos rubrikynas"
5692 vmj http://id.loc.gov/vocabulary/subjectSchemes/vmj Vedettes-matière jeunesse "fr"=>"Vedettes-matière jeunesse"
5693 ddcut http://id.loc.gov/vocabulary/subjectSchemes/ddcut Dewey Decimal Classification user terms
5694 eks http://id.loc.gov/vocabulary/subjectSchemes/eks Eduskunnan kirjaston asiasanasto "fi"=>"Eduskunnan kirjaston asiasanasto"
5695 wot http://id.loc.gov/vocabulary/subjectSchemes/wot A Women's thesaurus
5696 noubomn http://id.loc.gov/vocabulary/subjectSchemes/noubomn University of Oslo Library Thesaurus of Science "no"=>"University of Oslo Library Thesaurus of Science"
5697 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"
5698 precis http://id.loc.gov/vocabulary/subjectSchemes/precis PRECIS: a manual of concept analysis and subject indexing
5699 cstud http://id.loc.gov/vocabulary/subjectSchemes/cstud Classificatieschema's Bibliotheek TU Delft "nl"=>"Classificatieschema's Bibliotheek TU Delft"
5700 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"
5701 pmt http://id.loc.gov/vocabulary/subjectSchemes/pmt Project management terminology. Newtown Square, PA: Project Management Institute
5702 ericd http://id.loc.gov/vocabulary/subjectSchemes/ericd Thesaurus of ERIC descriptors
5703 rvm http://id.loc.gov/vocabulary/subjectSchemes/rvm Répertoire de vedettes-matière "fr"=>"Répertoire de vedettes-matière"
5704 sfit http://id.loc.gov/vocabulary/subjectSchemes/sfit Svenska filminstitutets tesaurus "sv"=>"Svenska filminstitutets tesaurus"
5705 trtsa http://id.loc.gov/vocabulary/subjectSchemes/trtsa Teatterin ja tanssin asiasanasto "fi"=>"Teatterin ja tanssin asiasanasto"
5706 ulan http://id.loc.gov/vocabulary/subjectSchemes/ulan Union list of artist names
5707 unescot http://id.loc.gov/vocabulary/subjectSchemes/unescot UNESCO thesaurus "fr"=>"Thésaurus de l'UNESCO","es"=>"Tesauro de la UNESCO"
5708 koko http://id.loc.gov/vocabulary/subjectSchemes/koko KOKO-ontologia "fi"=>"KOKO-ontologia"
5709 msh http://id.loc.gov/vocabulary/subjectSchemes/msh Trimboli, T., and Martyn S. Marianist subject headings
5710 trt http://id.loc.gov/vocabulary/subjectSchemes/trt Transportation resource thesaurus
5711 agrovocf http://id.loc.gov/vocabulary/subjectSchemes/agrovocf AGROVOC thésaurus agricole multilingue "fr"=>"AGROVOC thésaurus agricole multilingue"
5712 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"
5713 ddcri http://id.loc.gov/vocabulary/subjectSchemes/ddcri Dewey Decimal Classification Relative Index
5714 est http://id.loc.gov/vocabulary/subjectSchemes/est International energy: subject thesaurus (: International Energy Agency, Energy Technology Data Exchange)
5715 lua http://id.loc.gov/vocabulary/subjectSchemes/lua Liikunnan ja urheilun asiasanasto "fi"=>"Liikunnan ja urheilun asiasanasto"
5716 mipfesd http://id.loc.gov/vocabulary/subjectSchemes/mipfesd Macrothesaurus for information processing in the field of economic and social development
5717 rurkp http://id.loc.gov/vocabulary/subjectSchemes/rurkp Predmetnye rubriki Rossiiskoi knizhnoi palaty "ru"=>"Predmetnye rubriki Rossiiskoi knizhnoi palaty"
5718 albt http://id.loc.gov/vocabulary/subjectSchemes/albt Arbetslivsbibliotekets tesaurus "sv"=>"Arbetslivsbibliotekets tesaurus"
5719 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"
5720 bicssc http://id.loc.gov/vocabulary/subjectSchemes/bicssc BIC standard subject categories
5721 cctf http://id.loc.gov/vocabulary/subjectSchemes/cctf Carto-Canadiana thésaurus - Français "fr"=>"Carto-Canadiana thésaurus - Français"
5722 reo http://id.loc.gov/vocabulary/subjectSchemes/reo Māori Subject Headings thesaurus "mi"=>"Ngā Ūpoko Tukutuku"
5723 icpsr http://id.loc.gov/vocabulary/subjectSchemes/icpsr ICPSR controlled vocabulary system
5724 kao http://id.loc.gov/vocabulary/subjectSchemes/kao KVINNSAM ämnesordsregister "sv"=>"KVINNSAM ämnesordsregister"
5725 asrcseo http://id.loc.gov/vocabulary/subjectSchemes/asrcseo Australian Standard Research Classification: Socio-Economic Objective (SEO) classification
5726 georeft http://id.loc.gov/vocabulary/subjectSchemes/georeft GeoRef thesaurus
5727 cct http://id.loc.gov/vocabulary/subjectSchemes/cct Chinese Classified Thesaurus "zh"=>"Zhong guo fen lei zhu ti ci biao"
5728 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"
5729 musa http://id.loc.gov/vocabulary/subjectSchemes/musa Musiikin asiasanasto: erikoissanasto "fi"=>"Musiikin asiasanasto: erikoissanasto"
5730 ntissc http://id.loc.gov/vocabulary/subjectSchemes/ntissc NTIS subject categories
5731 idszbz http://id.loc.gov/vocabulary/subjectSchemes/idszbz Thesaurus IDS Nebis Zentralbibliothek Zürich "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich"
5732 tlka http://id.loc.gov/vocabulary/subjectSchemes/tlka Investigació, Procés Tècnicn kirjaston asiasanasto "fi"=>"Investigació, Procés Tècnicn kirjaston asiasanasto"
5733 usaidt http://id.loc.gov/vocabulary/subjectSchemes/usaidt USAID thesaurus: Keywords used to index documents included in the USAID Development Experience System.
5734 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"
5735 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"
5736 ntcpsc http://id.loc.gov/vocabulary/subjectSchemes/ntcpsc "National Translations Center primary subject classification" in National Translations Center primary subject classification and secondary descriptors
5737 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"
5738 allars http://id.loc.gov/vocabulary/subjectSchemes/allars Allärs: allmän tesaurus pä svenska "fi"=>"Allärs: allmän tesaurus pä svenska"
5739 ogst http://id.loc.gov/vocabulary/subjectSchemes/ogst Oregon GILS Subject Tree (Oregon: Oregon State Library and Oregon Information Resource Management Division (IRMD))
5740 bella http://id.loc.gov/vocabulary/subjectSchemes/bella Bella: specialtesaurus för skönlitteratur "fi"=>"Bella: specialtesaurus för skönlitteratur"
5741 bibalex http://id.loc.gov/vocabulary/subjectSchemes/bibalex Bibliotheca Alexandrina name and subject authority file
5742 pepp http://id.loc.gov/vocabulary/subjectSchemes/pepp The Princeton encyclopedia of poetry and poetics
5743 hkcan http://id.loc.gov/vocabulary/subjectSchemes/hkcan Hong Kong Chinese Authority File (Name) - HKCAN
5744 dissao http://id.loc.gov/vocabulary/subjectSchemes/dissao "Dissertation abstracts online" in Search tools: the guide to UNI/Data Courier Online
5745 ltcsh http://id.loc.gov/vocabulary/subjectSchemes/ltcsh Land Tenure Center Library list of subject headings
5746 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"
5747 asft http://id.loc.gov/vocabulary/subjectSchemes/asft Aquatic sciences and fisheries thesaurus
5748 naf http://id.loc.gov/vocabulary/subjectSchemes/naf NACO authority file
5749 nimacsc http://id.loc.gov/vocabulary/subjectSchemes/nimacsc NIMA cartographic subject categories
5750 khib http://id.loc.gov/vocabulary/subjectSchemes/khib Emneord, KHiB Biblioteket "no"=>"Emneord, KHiB Biblioteket"
5751 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"
5752 afset http://id.loc.gov/vocabulary/subjectSchemes/afset American Folklore Society Ethnographic Thesaurus
5753 erfemn http://id.loc.gov/vocabulary/subjectSchemes/erfemn Erfaringskompetanses emneord "no"=>"Erfaringskompetanses emneord"
5754 sbiao http://id.loc.gov/vocabulary/subjectSchemes/sbiao Svenska barnboksinstitutets ämnesordslista "sv"=>"Svenska barnboksinstitutets ämnesordslista"
5755 socio http://id.loc.gov/vocabulary/subjectSchemes/socio Sociological Abstracts Thesaurus
5756 bisacrt http://id.loc.gov/vocabulary/subjectSchemes/bisacrt BISAC Regional Themes
5757 eum http://id.loc.gov/vocabulary/subjectSchemes/eum Eesti uldine märksonastik "et"=>"Eesti uldine märksonastik"
5758 kula http://id.loc.gov/vocabulary/subjectSchemes/kula Kulttuurien tutkimuksen asiasanasto "fi"=>"Kulttuurien tutkimuksen asiasanasto"
5759 odlt http://id.loc.gov/vocabulary/subjectSchemes/odlt Baldick, C. The Oxford dictionary of literary terms
5760 rerovoc http://id.loc.gov/vocabulary/subjectSchemes/rerovoc Indexation matiéres RERO autoritès "fr"=>"Indexation matiéres RERO autoritès"
5761 tsr http://id.loc.gov/vocabulary/subjectSchemes/tsr TSR-ontologia "fi"=>"TSR-ontologia"
5762 czmesh http://id.loc.gov/vocabulary/subjectSchemes/czmesh Czech MeSH "cs"=>"Czech MeSH"
5763 dltt http://id.loc.gov/vocabulary/subjectSchemes/dltt Quinn, E. A dictionary of literary and thematic terms
5764 idsbb http://id.loc.gov/vocabulary/subjectSchemes/idsbb Thesaurus IDS Basel Bern "de"=>"Thesaurus IDS Basel Bern"
5765 inist http://id.loc.gov/vocabulary/subjectSchemes/inist INIS: thesaurus
5766 idszbzzk http://id.loc.gov/vocabulary/subjectSchemes/idszbzzk Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung "de"=>"Thesaurus IDS Nebis Zentralbibliothek Zürich, Kartensammlung"
5767 tesa http://id.loc.gov/vocabulary/subjectSchemes/tesa Tesauro AgrÃcola "es"=>"Tesauro AgrÃcola"
5768 liv http://id.loc.gov/vocabulary/subjectSchemes/liv Legislative indexing vocabulary
5769 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"
5770 nsbncf http://id.loc.gov/vocabulary/subjectSchemes/nsbncf Nuovo Soggettario "it"=>"Nuovo Soggettario"
5771 ipat http://id.loc.gov/vocabulary/subjectSchemes/ipat IPA thesaurus and frequency list
5772 skon http://id.loc.gov/vocabulary/subjectSchemes/skon Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur "sv"=>"Att indexera skönlitteratur: Ã
\84mnesordslista, vuxenlitteratur"
5773 renib http://id.loc.gov/vocabulary/subjectSchemes/renib Renib "es"=>"Renib"
5774 hrvmesh http://id.loc.gov/vocabulary/subjectSchemes/hrvmesh Croatian MeSH / Hrvatski MeSH "no"=>"Croatian MeSH / Hrvatski MeSH"
5775 swd http://id.loc.gov/vocabulary/subjectSchemes/swd Schlagwortnormdatei "de"=>"Schlagwortnormdatei"
5776 aass http://id.loc.gov/vocabulary/subjectSchemes/aass "Asian American Studies Library subject headings" in A Guide for establishing Asian American core collections
5777 cht http://id.loc.gov/vocabulary/subjectSchemes/cht Chicano thesaurus for indexing Chicano materials in Chicano periodical index
5778 galestne http://id.loc.gov/vocabulary/subjectSchemes/galestne Gale Group subject thesaurus and named entity vocabulary
5779 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"
5780 hoidokki http://id.loc.gov/vocabulary/subjectSchemes/hoidokki Hoitotieteellinen asiasanasto
5781 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"
5782 kubikat http://id.loc.gov/vocabulary/subjectSchemes/kubikat kubikat "de"=>"kubikat"
5783 waqaf http://id.loc.gov/vocabulary/subjectSchemes/waqaf Maknas Uloom Al Waqaf "ar"=>"Maknas Uloom Al Waqaf"
5784 hapi http://id.loc.gov/vocabulary/subjectSchemes/hapi HAPI thesaurus and name authority, 1970-2000
5785 drama http://id.loc.gov/vocabulary/subjectSchemes/drama Drama: specialtesaurus för teater och dans
5786 sosa http://id.loc.gov/vocabulary/subjectSchemes/sosa Sociaalialan asiasanasto "fi"=>"Sociaalialan asiasanasto"
5787 ilpt http://id.loc.gov/vocabulary/subjectSchemes/ilpt Index to legal periodicals: thesaurus
5788 nicem http://id.loc.gov/vocabulary/subjectSchemes/nicem NICEM subject headings and classification system
5789 qlsp http://id.loc.gov/vocabulary/subjectSchemes/qlsp Queens Library Spanish language subject headings
5790 eet http://id.loc.gov/vocabulary/subjectSchemes/eet European education thesaurus
5791 nalnaf http://id.loc.gov/vocabulary/subjectSchemes/nalnaf National Agricultural Library name authority file
5792 eclas http://id.loc.gov/vocabulary/subjectSchemes/eclas ECLAS thesaurus
5793 agrovocs http://id.loc.gov/vocabulary/subjectSchemes/agrovocs AGROVOC tesauro agrÃcola multilingée "es"=>"AGROVOC tesauro agrÃcola multilingée"
5794 shbe http://id.loc.gov/vocabulary/subjectSchemes/shbe Subject headings in business and economics "sv"=>"Subject headings in business and economics"
5795 barn http://id.loc.gov/vocabulary/subjectSchemes/barn Svenska ämnesord för barn "sv"=>"Svenska ämnesord för barn"
5796 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"
5797 gccst http://id.loc.gov/vocabulary/subjectSchemes/gccst Government of Canada core subject thesaurus (Gatineau : Library and Archives Canada)
5798 fnhl http://id.loc.gov/vocabulary/subjectSchemes/fnhl First Nations House of Learning Subject Headings
5799 kauno http://id.loc.gov/vocabulary/subjectSchemes/kauno KAUNO - Kaunokki-ontologin "fi"=>"KAUNO - Kaunokki-ontologin"
5800 dtict http://id.loc.gov/vocabulary/subjectSchemes/dtict Defense Technical Information Center thesaurus
5801 mech http://id.loc.gov/vocabulary/subjectSchemes/mech Iskanje po zbirki MECH "sl"=>"Iskanje po zbirki MECH"
5802 jupo http://id.loc.gov/vocabulary/subjectSchemes/jupo JUPO - Julkisen hallinnon palveluontologia "fi"=>"JUPO - Julkisen hallinnon palveluontologia"
5803 ktpt http://id.loc.gov/vocabulary/subjectSchemes/ktpt Kirjasto- ja tietopalvelualan tesaurus "fi"=>"Kirjasto- ja tietopalvelualan tesaurus"
5804 aiatsiss http://id.loc.gov/vocabulary/subjectSchemes/aiatsiss AIATSIS subject Thesaurus
5805 lcac http://id.loc.gov/vocabulary/subjectSchemes/lcac Library of Congress Annotated Children's Cataloging Program subject headings
5806 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à "
5807 lemb http://id.loc.gov/vocabulary/subjectSchemes/lemb Lista de encabezamientos de materia para bibliotecas "es"=>"Lista de encabezamientos de materia para bibliotecas"
5808 henn http://id.loc.gov/vocabulary/subjectSchemes/henn Hennepin County Library cumulative authority list
5809 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"
5810 cash http://id.loc.gov/vocabulary/subjectSchemes/cash Canadian subject headings
5811 nznb http://id.loc.gov/vocabulary/subjectSchemes/nznb New Zealand national bibliographic
5812 prvt http://id.loc.gov/vocabulary/subjectSchemes/prvt Patent- och registreringsverkets tesaurus "sv"=>"Patent- och registreringsverkets tesaurus"
5813 scgdst http://id.loc.gov/vocabulary/subjectSchemes/scgdst Subject categorization guide for defense science and technology
5814 gem http://id.loc.gov/vocabulary/subjectSchemes/gem GEM controlled vocabularies
5815 lcsh http://id.loc.gov/vocabulary/subjectSchemes/lcsh Library of Congress subject headings
5816 rero http://id.loc.gov/vocabulary/subjectSchemes/rero Indexation matires RERO "fr"=>"Indexation matires RERO"
5817 peri http://id.loc.gov/vocabulary/subjectSchemes/peri Perinnetieteiden asiasanasto "fi"=>"Perinnetieteiden asiasanasto"
5818 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"
5819 slem http://id.loc.gov/vocabulary/subjectSchemes/slem Sears: lista de encabezamientos de materia "es"=>"Sears: lista de encabezamientos de materia"
5820 afo http://id.loc.gov/vocabulary/subjectSchemes/afo AFO - Viikin kampuskirjaston ontologia "fi"=>"AFO - Viikin kampuskirjaston ontologia"
5821 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
5822 hlasstg http://id.loc.gov/vocabulary/subjectSchemes/hlasstg HLAS subject term glossary
5823 iest http://id.loc.gov/vocabulary/subjectSchemes/iest International energy: subject thesaurus
5824 pkk http://id.loc.gov/vocabulary/subjectSchemes/pkk Predmetnik za katoliške knjižnice "sl"=>"Predmetnik za katoliške knjižnice"
5825 atla http://id.loc.gov/vocabulary/subjectSchemes/atla Religion indexes: thesaurus
5826 scot http://id.loc.gov/vocabulary/subjectSchemes/scot Schools Online Thesaurus (ScOT)
5827 smda http://id.loc.gov/vocabulary/subjectSchemes/smda Smithsonian National Air and Space Museum Directory of Airplanes
5828 solstad http://id.loc.gov/vocabulary/subjectSchemes/solstad Solstad: emneord for Solstadbibliografien "no"=>"Solstad: emneord for Solstadbibliografien"
5829 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"
5830 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"
5831 ktta http://id.loc.gov/vocabulary/subjectSchemes/ktta Käsi - ja taideteollisuuden asiasanasto "fi"=>"Käsi - ja taideteollisuuden asiasanasto"
5832 ccte http://id.loc.gov/vocabulary/subjectSchemes/ccte Carto-Canadiana thesaurus - English
5833 pmcsg http://id.loc.gov/vocabulary/subjectSchemes/pmcsg Combined standards glossary
5834 bisacsh http://id.loc.gov/vocabulary/subjectSchemes/bisacsh BISAC Subject Headings
5835 fssh http://id.loc.gov/vocabulary/subjectSchemes/fssh FamilySearch Subject Headings (FamilySearch)
5836 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"
5837 tero http://id.loc.gov/vocabulary/subjectSchemes/tero TERO - Terveyden ja hyvinvoinnin ontologia "fi"=>"TERO - Terveyden ja hyvinvoinnin ontologia"
5838 rma http://id.loc.gov/vocabulary/subjectSchemes/rma Ru'us al-mawdu'at al-'Arabiyah "ar"=>"Ru'us al-mawdu'at al-'Arabiyah"
5839 tgn http://id.loc.gov/vocabulary/subjectSchemes/tgn Getty thesaurus of geographic names
5840 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"
5841 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"
5842 sears http://id.loc.gov/vocabulary/subjectSchemes/sears Sears list of subject headings
5843 csht http://id.loc.gov/vocabulary/subjectSchemes/csht Chinese subject headings
5848 INSERT INTO authority.thesaurus (code, uri, name, control_set)
5849 SELECT code, uri, name, 1 FROM thesauri;
5851 UPDATE authority.thesaurus SET short_code = 'a' WHERE code = 'lcsh';
5852 UPDATE authority.thesaurus SET short_code = 'b' WHERE code = 'lcshac';
5853 UPDATE authority.thesaurus SET short_code = 'c' WHERE code = 'mesh';
5854 UPDATE authority.thesaurus SET short_code = 'd' WHERE code = 'nal';
5855 UPDATE authority.thesaurus SET short_code = 'k' WHERE code = 'cash';
5856 UPDATE authority.thesaurus SET short_code = 'r' WHERE code = 'aat';
5857 UPDATE authority.thesaurus SET short_code = 's' WHERE code = 'sears';
5858 UPDATE authority.thesaurus SET short_code = 'v' WHERE code = 'rvm';
5860 UPDATE authority.thesaurus
5861 SET short_code = 'z'
5862 WHERE short_code IS NULL
5863 AND control_set = 1;
5865 INSERT INTO config.i18n_core (fq_field, identity_value, translation, string )
5866 SELECT 'at.name', t.code, xlate->key, xlate->value
5868 JOIN LATERAL each(t.xlate) AS xlate ON TRUE
5871 FROM config.i18n_core
5872 WHERE fq_field = 'at.name'
5873 AND identity_value = t.code
5874 AND translation = xlate->key)
5875 AND t.xlate IS NOT NULL
5876 AND t.name <> (xlate->value);
5878 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
5882 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
5883 IF thes_code IS NULL THEN
5885 ELSIF thes_code = 'z' THEN
5886 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), 'z' );
5888 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
5890 thes_code := '|'; -- default
5895 $func$ LANGUAGE PLPGSQL STABLE STRICT;
5897 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
5899 IF NEW.control_set IS NULL THEN
5900 SELECT control_set INTO NEW.control_set
5901 FROM authority.thesaurus
5902 WHERE code = authority.extract_thesaurus(NEW.marc);
5907 $func$ LANGUAGE PLPGSQL;
5909 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
5911 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
5912 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
5914 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
5915 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
5916 authority.extract_thesaurus(marc)
5917 FROM authority.record_entry
5921 $func$ LANGUAGE PLPGSQL;
5925 SELECT evergreen.upgrade_deps_block_check('1071', :eg_version); --gmcharlt/kmlussier
5927 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)
5928 RETURNS SETOF metabib.flat_browse_entry_appearance
5937 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
5938 results_skipped INT := 0;
5939 row_counter INT := 0;
5944 all_records BIGINT[];
5945 all_brecords BIGINT[];
5946 all_arecords BIGINT[];
5947 superpage_of_records BIGINT[];
5952 unauthorized_entry RECORD;
5954 IF count_up_from_zero THEN
5961 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
5964 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
5965 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
5967 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
5969 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
5970 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
5972 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
5974 b_tests := b_tests || search.calculate_visibility_attribute_test(
5976 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
5979 b_tests := b_tests || search.calculate_visibility_attribute_test(
5981 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
5985 IF context_locations THEN
5986 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
5987 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
5990 OPEN curs NO SCROLL FOR EXECUTE query;
5993 FETCH curs INTO rec;
5995 IF result_row.pivot_point IS NOT NULL THEN
5996 RETURN NEXT result_row;
6002 SELECT INTO unauthorized_entry *
6003 FROM metabib.browse_entry_simple_heading_map mbeshm
6004 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6005 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
6006 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6007 WHERE mbeshm.entry = rec.id
6008 AND ahf.heading_purpose = 'variant';
6010 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
6011 IF (unauthorized_entry.record IS NOT NULL) THEN
6012 --unauthorized term belongs to an auth linked to a bib?
6013 SELECT INTO all_arecords, result_row.sees, afields
6014 ARRAY_AGG(DISTINCT abl.bib),
6015 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
6016 ARRAY_AGG(DISTINCT map.metabib_field)
6017 FROM authority.bib_linking abl
6018 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6019 map.authority_field = unauthorized_entry.atag
6020 AND map.metabib_field = ANY(fields)
6022 WHERE abl.authority = unauthorized_entry.record;
6024 --do usual procedure
6025 SELECT INTO all_arecords, result_row.sees, afields
6026 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
6027 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
6028 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
6030 FROM metabib.browse_entry_simple_heading_map mbeshm
6031 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6032 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
6033 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
6034 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6035 ash.atag = map.authority_field
6036 AND map.metabib_field = ANY(fields)
6038 JOIN authority.control_set_authority_field acsaf ON (
6039 map.authority_field = acsaf.id
6041 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6042 WHERE mbeshm.entry = rec.id
6043 AND ahf.heading_purpose = 'variant';
6047 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
6048 SELECT INTO all_brecords, result_row.authorities, bfields
6049 ARRAY_AGG(DISTINCT source),
6050 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
6051 ARRAY_AGG(DISTINCT def)
6052 FROM metabib.browse_entry_def_map
6053 WHERE entry = rec.id
6054 AND def = ANY(fields);
6056 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
6058 result_row.sources := 0;
6059 result_row.asources := 0;
6061 -- Bib-linked vis checking
6062 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
6064 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
6065 FROM biblio.record_entry b
6066 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6067 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
6069 acvac.vis_attr_vector @@ c_tests::query_int
6070 OR b.vis_attr_vector @@ b_tests::query_int
6073 result_row.accurate := TRUE;
6077 -- Authority-linked vis checking
6078 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
6080 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
6081 FROM biblio.record_entry b
6082 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
6083 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
6085 acvac.vis_attr_vector @@ c_tests::query_int
6086 OR b.vis_attr_vector @@ b_tests::query_int
6089 result_row.aaccurate := TRUE;
6093 IF result_row.sources > 0 OR result_row.asources > 0 THEN
6095 -- The function that calls this function needs row_number in order
6096 -- to correctly order results from two different runs of this
6098 result_row.row_number := row_number;
6100 -- Now, if row_counter is still less than limit, return a row. If
6101 -- not, but it is less than next_pivot_pos, continue on without
6102 -- returning actual result rows until we find
6103 -- that next pivot, and return it.
6105 IF row_counter < result_limit THEN
6106 result_row.browse_entry := rec.id;
6107 result_row.value := rec.value;
6109 RETURN NEXT result_row;
6111 result_row.browse_entry := NULL;
6112 result_row.authorities := NULL;
6113 result_row.fields := NULL;
6114 result_row.value := NULL;
6115 result_row.sources := NULL;
6116 result_row.sees := NULL;
6117 result_row.accurate := NULL;
6118 result_row.aaccurate := NULL;
6119 result_row.pivot_point := rec.id;
6121 IF row_counter >= next_pivot_pos THEN
6122 RETURN NEXT result_row;
6127 IF count_up_from_zero THEN
6128 row_number := row_number + 1;
6130 row_number := row_number - 1;
6133 -- row_counter is different from row_number.
6134 -- It simply counts up from zero so that we know when
6135 -- we've reached our limit.
6136 row_counter := row_counter + 1;
6140 $f$ LANGUAGE plpgsql ROWS 10;
6142 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)
6143 RETURNS SETOF metabib.flat_browse_entry_appearance
6149 pivot_sort_value TEXT;
6150 pivot_sort_fallback TEXT;
6151 context_locations INT[];
6152 browse_superpage_size INT;
6153 results_skipped INT := 0;
6157 forward_to_pivot INT;
6159 -- First, find the pivot if we were given a browse term but not a pivot.
6160 IF pivot_id IS NULL THEN
6161 pivot_id := metabib.browse_pivot(search_field, browse_term);
6164 SELECT INTO pivot_sort_value, pivot_sort_fallback
6165 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
6167 -- Bail if we couldn't find a pivot.
6168 IF pivot_sort_value IS NULL THEN
6172 -- Transform the context_loc_group argument (if any) (logc at the
6173 -- TPAC layer) into a form we'll be able to use.
6174 IF context_loc_group IS NOT NULL THEN
6175 SELECT INTO context_locations ARRAY_AGG(location)
6176 FROM asset.copy_location_group_map
6177 WHERE lgroup = context_loc_group;
6180 -- Get the configured size of browse superpages.
6181 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
6182 FROM config.global_flag
6183 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
6185 -- First we're going to search backward from the pivot, then we're going
6186 -- to search forward. In each direction, we need two limits. At the
6187 -- lesser of the two limits, we delineate the edge of the result set
6188 -- we're going to return. At the greater of the two limits, we find the
6189 -- pivot value that would represent an offset from the current pivot
6190 -- at a distance of one "page" in either direction, where a "page" is a
6191 -- result set of the size specified in the "result_limit" argument.
6193 -- The two limits in each direction make four derived values in total,
6194 -- and we calculate them now.
6195 back_limit := CEIL(result_limit::FLOAT / 2);
6196 back_to_pivot := result_limit;
6197 forward_limit := result_limit / 2;
6198 forward_to_pivot := result_limit - 1;
6200 -- This is the meat of the SQL query that finds browse entries. We'll
6201 -- pass this to a function which uses it with a cursor, so that individual
6202 -- rows may be fetched in a loop until some condition is satisfied, without
6203 -- waiting for a result set of fixed size to be collected all at once.
6208 FROM metabib.browse_entry mbe
6210 EXISTS ( -- are there any bibs using this mbe via the requested fields?
6212 FROM metabib.browse_entry_def_map mbedm
6213 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
6214 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
6216 FROM metabib.browse_entry_simple_heading_map mbeshm
6217 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
6218 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
6219 ash.atag = map.authority_field
6220 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
6222 JOIN authority.control_set_authority_field acsaf ON (
6223 map.authority_field = acsaf.id
6225 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
6226 WHERE mbeshm.entry = mbe.id
6227 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
6228 -- and authority that variant is coming from is linked to a bib
6231 FROM metabib.browse_entry_def_map mbedm2
6232 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
6237 -- This is the variant of the query for browsing backward.
6238 back_query := core_query ||
6239 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
6240 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
6242 -- This variant browses forward.
6243 forward_query := core_query ||
6244 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
6245 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
6247 -- We now call the function which applies a cursor to the provided
6248 -- queries, stopping at the appropriate limits and also giving us
6249 -- the next page's pivot.
6251 SELECT * FROM metabib.staged_browse(
6252 back_query, search_field, context_org, context_locations,
6253 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
6255 SELECT * FROM metabib.staged_browse(
6256 forward_query, search_field, context_org, context_locations,
6257 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
6258 ) ORDER BY row_number DESC;
6261 $f$ LANGUAGE plpgsql ROWS 10;
6264 SELECT evergreen.upgrade_deps_block_check('1072', :eg_version); --gmcharlt/kmlussier
6266 INSERT INTO config.global_flag (name, label, enabled) VALUES (
6267 'opac.show_related_headings_in_browse',
6269 'opac.show_related_headings_in_browse',
6270 'Display related headings (see-also) in browse',
6279 SELECT evergreen.upgrade_deps_block_check('1073', :eg_version);
6281 ALTER TABLE config.metabib_field
6282 ADD COLUMN display_xpath TEXT,
6283 ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
6285 CREATE TABLE config.display_field_map (
6286 name TEXT PRIMARY KEY,
6287 field INTEGER REFERENCES config.metabib_field (id),
6288 multi BOOLEAN DEFAULT FALSE
6291 CREATE TABLE metabib.display_entry (
6292 id BIGSERIAL PRIMARY KEY,
6293 source BIGINT NOT NULL REFERENCES biblio.record_entry (id),
6294 field INT NOT NULL REFERENCES config.metabib_field (id),
6298 CREATE INDEX metabib_display_entry_field_idx ON metabib.display_entry (field);
6299 CREATE INDEX metabib_display_entry_source_idx ON metabib.display_entry (source);
6301 -- one row per display entry fleshed with field info
6302 CREATE VIEW metabib.flat_display_entry AS
6310 FROM metabib.display_entry mde
6311 JOIN config.metabib_field cmf ON (cmf.id = mde.field)
6312 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
6315 -- like flat_display_entry except values are compressed
6316 -- into one row per display_field_map and JSON-ified.
6317 CREATE VIEW metabib.compressed_display_entry AS
6324 CASE WHEN multi THEN
6325 TO_JSON(ARRAY_AGG(value))
6329 FROM metabib.flat_display_entry
6330 GROUP BY 1, 2, 3, 4, 5
6333 -- TODO: expand to encompass all well-known fields
6334 CREATE VIEW metabib.wide_display_entry AS
6337 COALESCE(mcde_title.value, 'null') AS title,
6338 COALESCE(mcde_author.value, 'null') AS author,
6339 COALESCE(mcde_subject.value, 'null') AS subject,
6340 COALESCE(mcde_creators.value, 'null') AS creators,
6341 COALESCE(mcde_isbn.value, 'null') AS isbn
6342 -- ensure one row per bre regardless of any display fields
6343 FROM biblio.record_entry bre
6344 LEFT JOIN metabib.compressed_display_entry mcde_title
6345 ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
6346 LEFT JOIN metabib.compressed_display_entry mcde_author
6347 ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
6348 LEFT JOIN metabib.compressed_display_entry mcde_subject
6349 ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
6350 LEFT JOIN metabib.compressed_display_entry mcde_creators
6351 ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
6352 LEFT JOIN metabib.compressed_display_entry mcde_isbn
6353 ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
6357 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
6358 RETURNS TRIGGER AS $$
6361 display_field_text TEXT;
6363 display_field_text := NEW.value;
6366 SELECT n.func AS func,
6367 n.param_count AS param_count,
6369 FROM config.index_normalizer n
6370 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
6371 WHERE m.field = NEW.field AND m.pos < 0
6374 EXECUTE 'SELECT ' || normalizer.func || '(' ||
6375 quote_literal( display_field_text ) ||
6377 WHEN normalizer.param_count > 0
6378 THEN ',' || REPLACE(REPLACE(BTRIM(
6379 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
6382 ')' INTO display_field_text;
6386 NEW.value = display_field_text;
6390 $$ LANGUAGE PLPGSQL;
6392 CREATE TRIGGER display_field_normalize_tgr
6393 BEFORE UPDATE OR INSERT ON metabib.display_entry
6394 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
6396 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
6397 RETURNS TRIGGER AS $$
6399 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
6402 $$ LANGUAGE PLPGSQL;
6404 CREATE TRIGGER display_field_force_nfc_tgr
6405 BEFORE UPDATE OR INSERT ON metabib.display_entry
6406 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
6408 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE display_field BOOL;
6410 DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL);
6411 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT);
6412 DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT, TEXT);
6414 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
6416 default_joiner TEXT,
6419 ) RETURNS SETOF metabib.field_entry_template AS $func$
6421 bib biblio.record_entry%ROWTYPE;
6422 idx config.metabib_field%ROWTYPE;
6423 xfrm config.xml_transform%ROWTYPE;
6425 transformed_xml TEXT;
6427 xml_node_list TEXT[];
6434 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
6435 authority_text TEXT;
6436 authority_link BIGINT;
6437 output_row metabib.field_entry_template%ROWTYPE;
6441 -- Start out with no field-use bools set
6442 output_row.browse_field = FALSE;
6443 output_row.facet_field = FALSE;
6444 output_row.display_field = FALSE;
6445 output_row.search_field = FALSE;
6448 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
6450 -- Loop over the indexing entries
6451 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
6453 process_idx := FALSE;
6454 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
6455 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
6456 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
6457 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
6458 CONTINUE WHEN process_idx = FALSE;
6460 joiner := COALESCE(idx.joiner, default_joiner);
6462 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
6464 -- See if we can skip the XSLT ... it's expensive
6465 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
6466 -- Can't skip the transform
6467 IF xfrm.xslt <> '---' THEN
6468 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
6470 transformed_xml := bib.marc;
6473 prev_xfrm := xfrm.name;
6476 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6479 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
6480 CONTINUE WHEN xml_node !~ E'^\\s*<';
6482 -- XXX much of this should be moved into oils_xpath_string...
6483 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
6484 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
6485 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
6486 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
6490 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
6492 IF raw_text IS NOT NULL THEN
6493 raw_text := raw_text || joiner;
6496 raw_text := COALESCE(raw_text,'') || curr_text;
6498 -- autosuggest/metabib.browse_entry
6499 IF idx.browse_field THEN
6501 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
6502 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6504 browse_text := curr_text;
6507 IF idx.browse_sort_xpath IS NOT NULL AND
6508 idx.browse_sort_xpath <> '' THEN
6510 sort_value := oils_xpath_string(
6511 idx.browse_sort_xpath, xml_node, joiner,
6512 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
6515 sort_value := browse_text;
6518 output_row.field_class = idx.field_class;
6519 output_row.field = idx.id;
6520 output_row.source = rid;
6521 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
6522 output_row.sort_value :=
6523 public.naco_normalize(sort_value);
6525 output_row.authority := NULL;
6527 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
6528 authority_text := oils_xpath_string(
6529 idx.authority_xpath, xml_node, joiner,
6531 ARRAY[xfrm.prefix, xfrm.namespace_uri],
6532 ARRAY['xlink','http://www.w3.org/1999/xlink']
6536 IF authority_text ~ '^\d+$' THEN
6537 authority_link := authority_text::BIGINT;
6538 PERFORM * FROM authority.record_entry WHERE id = authority_link;
6540 output_row.authority := authority_link;
6546 output_row.browse_field = TRUE;
6547 -- Returning browse rows with search_field = true for search+browse
6548 -- configs allows us to retain granularity of being able to search
6549 -- browse fields with "starts with" type operators (for example, for
6550 -- titles of songs in music albums)
6551 IF idx.search_field THEN
6552 output_row.search_field = TRUE;
6554 RETURN NEXT output_row;
6555 output_row.browse_field = FALSE;
6556 output_row.search_field = FALSE;
6557 output_row.sort_value := NULL;
6560 -- insert raw node text for faceting
6561 IF idx.facet_field THEN
6563 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
6564 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6566 facet_text := curr_text;
6569 output_row.field_class = idx.field_class;
6570 output_row.field = -1 * idx.id;
6571 output_row.source = rid;
6572 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
6574 output_row.facet_field = TRUE;
6575 RETURN NEXT output_row;
6576 output_row.facet_field = FALSE;
6579 -- insert raw node text for display
6580 IF idx.display_field THEN
6582 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
6583 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
6585 display_text := curr_text;
6588 output_row.field_class = idx.field_class;
6589 output_row.field = -1 * idx.id;
6590 output_row.source = rid;
6591 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
6593 output_row.display_field = TRUE;
6594 RETURN NEXT output_row;
6595 output_row.display_field = FALSE;
6600 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
6602 -- insert combined node text for searching
6603 IF idx.search_field THEN
6604 output_row.field_class = idx.field_class;
6605 output_row.field = idx.id;
6606 output_row.source = rid;
6607 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
6609 output_row.search_field = TRUE;
6610 RETURN NEXT output_row;
6611 output_row.search_field = FALSE;
6618 $func$ LANGUAGE PLPGSQL;
6620 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
6622 skip_facet BOOL DEFAULT FALSE,
6623 skip_display BOOL DEFAULT FALSE,
6624 skip_browse BOOL DEFAULT FALSE,
6625 skip_search BOOL DEFAULT FALSE,
6626 only_fields INT[] DEFAULT '{}'::INT[]
6627 ) RETURNS VOID AS $func$
6630 ind_data metabib.field_entry_template%ROWTYPE;
6631 mbe_row metabib.browse_entry%ROWTYPE;
6634 b_skip_display BOOL;
6638 field_list INT[] := only_fields;
6639 field_types TEXT[] := '{}'::TEXT[];
6642 IF field_list = '{}'::INT[] THEN
6643 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
6646 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;
6647 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;
6648 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;
6649 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;
6651 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
6652 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
6653 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
6654 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
6656 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
6658 IF NOT b_skip_search THEN
6659 FOR fclass IN SELECT * FROM config.metabib_class LOOP
6660 -- RAISE NOTICE 'Emptying out %', fclass.name;
6661 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
6664 IF NOT b_skip_facet THEN
6665 DELETE FROM metabib.facet_entry WHERE source = bib_id;
6667 IF NOT b_skip_display THEN
6668 DELETE FROM metabib.display_entry WHERE source = bib_id;
6670 IF NOT b_skip_browse THEN
6671 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
6675 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
6677 -- don't store what has been normalized away
6678 CONTINUE WHEN ind_data.value IS NULL;
6680 IF ind_data.field < 0 THEN
6681 ind_data.field = -1 * ind_data.field;
6684 IF ind_data.facet_field AND NOT b_skip_facet THEN
6685 INSERT INTO metabib.facet_entry (field, source, value)
6686 VALUES (ind_data.field, ind_data.source, ind_data.value);
6689 IF ind_data.display_field AND NOT b_skip_display THEN
6690 INSERT INTO metabib.display_entry (field, source, value)
6691 VALUES (ind_data.field, ind_data.source, ind_data.value);
6695 IF ind_data.browse_field AND NOT b_skip_browse THEN
6696 -- A caveat about this SELECT: this should take care of replacing
6697 -- old mbe rows when data changes, but not if normalization (by
6698 -- which I mean specifically the output of
6699 -- evergreen.oils_tsearch2()) changes. It may or may not be
6700 -- expensive to add a comparison of index_vector to index_vector
6701 -- to the WHERE clause below.
6703 CONTINUE WHEN ind_data.sort_value IS NULL;
6705 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
6706 SELECT INTO mbe_row * FROM metabib.browse_entry
6707 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
6710 mbe_id := mbe_row.id;
6712 INSERT INTO metabib.browse_entry
6713 ( value, sort_value ) VALUES
6714 ( value_prepped, ind_data.sort_value );
6716 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
6719 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
6720 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
6723 IF ind_data.search_field AND NOT b_skip_search THEN
6724 -- Avoid inserting duplicate rows
6725 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
6726 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
6727 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
6728 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
6729 IF mbe_id IS NULL THEN
6731 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
6733 quote_literal(ind_data.field) || $$, $$ ||
6734 quote_literal(ind_data.source) || $$, $$ ||
6735 quote_literal(ind_data.value) ||
6742 IF NOT b_skip_search THEN
6743 PERFORM metabib.update_combined_index_vectors(bib_id);
6748 $func$ LANGUAGE PLPGSQL;
6750 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
6751 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
6756 IF NEW.deleted THEN -- If this bib is deleted
6758 PERFORM * FROM config.internal_flag WHERE
6759 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
6761 tmp_bool := FOUND; -- Just in case this is changed by some other statement
6763 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
6765 IF NOT tmp_bool THEN
6766 -- One needs to keep these around to support searches
6767 -- with the #deleted modifier, so one should turn on the named
6768 -- internal flag for that functionality.
6769 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
6772 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
6773 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
6774 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
6775 RETURN NEW; -- and we're done
6778 IF TG_OP = 'UPDATE' THEN -- re-ingest?
6779 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
6781 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
6786 -- Record authority linking
6787 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
6789 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
6792 -- Flatten and insert the mfr data
6793 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
6795 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
6797 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
6798 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
6800 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
6804 -- Gather and insert the field entry data
6805 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
6807 -- Located URI magic
6808 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
6809 IF NOT FOUND THEN PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); END IF;
6811 -- (re)map metarecord-bib linking
6812 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
6813 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
6815 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6817 ELSE -- we're doing an update, and we're not deleted, remap
6818 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
6820 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
6826 $func$ LANGUAGE PLPGSQL;
6831 SELECT evergreen.upgrade_deps_block_check('1074', :eg_version);
6833 INSERT INTO config.internal_flag (name, enabled)
6834 VALUES ('ingest.skip_display_indexing', FALSE);
6836 -- Adds seed data to replace (for now) values from the 'mvr' class
6838 UPDATE config.metabib_field SET display_field = TRUE WHERE id IN (6, 8, 16, 18);
6840 INSERT INTO config.metabib_field ( id, field_class, name, label,
6841 format, xpath, display_field, display_xpath ) VALUES
6842 (37, 'author', 'creator', oils_i18n_gettext(37, 'All Creators', 'cmf', 'label'),
6843 'mods32', $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']]$$,
6844 TRUE, $$//*[local-name()='namePart']$$ ); -- /* to fool vim */;
6847 UPDATE config.metabib_field SET display_xpath =
6848 $$//*[local-name()='namePart']$$ -- /* to fool vim */
6851 INSERT INTO config.display_field_map (name, field, multi) VALUES
6852 ('title', 6, FALSE),
6853 ('author', 8, FALSE),
6854 ('creators', 37, TRUE),
6855 ('subject', 16, TRUE),
6861 SELECT evergreen.upgrade_deps_block_check('1075', :eg_version);
6863 CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$
6865 IF NEW.imported_as IS NULL THEN
6868 PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as;
6870 RAISE foreign_key_violation USING MESSAGE = FORMAT(
6871 $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as
6876 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
6881 \echo ---------------------------------------------------------------------
6882 \echo Reingest display fields. This can ban canceled via Ctrl-C and run at
6883 \echo a later time with the following (or similar) SQL:
6885 \echo 'SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE, '
6886 \echo ' (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))'
6887 \echo ' FROM biblio.record_entry WHERE NOT deleted AND id > 0;'
6890 -- REINGEST DISPLAY ENTRIES
6891 SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE, TRUE,
6892 (SELECT ARRAY_AGG(id)::INT[] FROM config.metabib_field WHERE display_field))
6893 FROM biblio.record_entry WHERE NOT deleted AND id > 0;