3 SELECT evergreen.upgrade_deps_block_check('1314', :eg_version);
5 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
8 main_entry authority.control_set_authority_field%ROWTYPE;
9 bib_field authority.control_set_bib_field%ROWTYPE;
10 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
12 replace_data XML[] DEFAULT '{}'::XML[];
13 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
18 IF auth_id IS NULL THEN
22 -- Default to the LoC controll set
23 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
25 -- if none, make a best guess
27 SELECT control_set INTO cset
28 FROM authority.control_set_authority_field
30 SELECT UNNEST(XPATH('//*[local-name()="datafield" and starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
31 FROM authority.record_entry
37 -- if STILL none, no-op change
41 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
42 XMLELEMENT( name leader, '00881nam a2200193 4500'),
45 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
48 XMLATTRIBUTES('d' AS code),
55 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
56 auth_field := XPATH('//*[local-name()="datafield" and @tag="'||main_entry.tag||'"][1]',source_xml::XML);
57 auth_i1 := (XPATH('//*[local-name()="datafield"]/@ind1',auth_field[1]))[1];
58 auth_i2 := (XPATH('//*[local-name()="datafield"]/@ind2',auth_field[1]))[1];
59 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
60 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
61 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
63 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
65 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
66 replace_data := replace_data || tmp_data;
67 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
74 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
78 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
79 XMLELEMENT( name leader, '00881nam a2200193 4500'),
83 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
86 XMLATTRIBUTES('r' AS code),
87 ARRAY_TO_STRING(replace_rules,',')
92 $f$ STABLE LANGUAGE PLPGSQL;
94 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
96 acsaf authority.control_set_authority_field%ROWTYPE;
107 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
109 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
112 SELECT control_set INTO cset
113 FROM authority.control_set_authority_field
114 WHERE tag IN (SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
119 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
120 tag_used := acsaf.tag;
121 nfi_used := acsaf.nfi;
124 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml))
126 FOR sf_node IN SELECT unnest(oils_xpath('//*[local-name() = "subfield" and contains("'||acsaf.sf_list||'",@code)]',tag_node))
129 tmp_text := oils_xpath_string('.', sf_node);
130 sf := oils_xpath_string('//*/@code', sf_node);
132 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
134 tmp_text := SUBSTRING(
139 oils_xpath_string('//*[local-name() = "datafield"]/@ind'||nfi_used, tag_node),
154 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
155 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
159 EXIT WHEN heading_text <> '';
162 EXIT WHEN heading_text <> '';
165 IF heading_text <> '' THEN
166 IF no_thesaurus IS TRUE THEN
167 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
169 thes_code := authority.extract_thesaurus(marcxml);
170 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
173 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
178 $func$ LANGUAGE PLPGSQL STABLE STRICT;
180 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
208 attr_set vandelay.import_item%ROWTYPE;
215 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
219 attr_set.definition := attr_def.id;
221 -- Build the combined XPath
225 WHEN attr_def.owning_lib IS NULL THEN 'null()'
226 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@code="' || attr_def.owning_lib || '"]'
227 ELSE '//*' || attr_def.owning_lib
232 WHEN attr_def.circ_lib IS NULL THEN 'null()'
233 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@code="' || attr_def.circ_lib || '"]'
234 ELSE '//*' || attr_def.circ_lib
239 WHEN attr_def.call_number IS NULL THEN 'null()'
240 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@code="' || attr_def.call_number || '"]'
241 ELSE '//*' || attr_def.call_number
246 WHEN attr_def.copy_number IS NULL THEN 'null()'
247 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@code="' || attr_def.copy_number || '"]'
248 ELSE '//*' || attr_def.copy_number
253 WHEN attr_def.status IS NULL THEN 'null()'
254 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@code="' || attr_def.status || '"]'
255 ELSE '//*' || attr_def.status
260 WHEN attr_def.location IS NULL THEN 'null()'
261 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@code="' || attr_def.location || '"]'
262 ELSE '//*' || attr_def.location
267 WHEN attr_def.circulate IS NULL THEN 'null()'
268 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@code="' || attr_def.circulate || '"]'
269 ELSE '//*' || attr_def.circulate
274 WHEN attr_def.deposit IS NULL THEN 'null()'
275 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@code="' || attr_def.deposit || '"]'
276 ELSE '//*' || attr_def.deposit
281 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
282 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@code="' || attr_def.deposit_amount || '"]'
283 ELSE '//*' || attr_def.deposit_amount
288 WHEN attr_def.ref IS NULL THEN 'null()'
289 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@code="' || attr_def.ref || '"]'
290 ELSE '//*' || attr_def.ref
295 WHEN attr_def.holdable IS NULL THEN 'null()'
296 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@code="' || attr_def.holdable || '"]'
297 ELSE '//*' || attr_def.holdable
302 WHEN attr_def.price IS NULL THEN 'null()'
303 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@code="' || attr_def.price || '"]'
304 ELSE '//*' || attr_def.price
309 WHEN attr_def.barcode IS NULL THEN 'null()'
310 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@code="' || attr_def.barcode || '"]'
311 ELSE '//*' || attr_def.barcode
316 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
317 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@code="' || attr_def.circ_modifier || '"]'
318 ELSE '//*' || attr_def.circ_modifier
323 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
324 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@code="' || attr_def.circ_as_type || '"]'
325 ELSE '//*' || attr_def.circ_as_type
330 WHEN attr_def.alert_message IS NULL THEN 'null()'
331 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@code="' || attr_def.alert_message || '"]'
332 ELSE '//*' || attr_def.alert_message
337 WHEN attr_def.opac_visible IS NULL THEN 'null()'
338 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@code="' || attr_def.opac_visible || '"]'
339 ELSE '//*' || attr_def.opac_visible
344 WHEN attr_def.pub_note IS NULL THEN 'null()'
345 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@code="' || attr_def.pub_note || '"]'
346 ELSE '//*' || attr_def.pub_note
350 WHEN attr_def.priv_note IS NULL THEN 'null()'
351 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@code="' || attr_def.priv_note || '"]'
352 ELSE '//*' || attr_def.priv_note
357 WHEN attr_def.internal_id IS NULL THEN 'null()'
358 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@code="' || attr_def.internal_id || '"]'
359 ELSE '//*' || attr_def.internal_id
364 WHEN attr_def.stat_cat_data IS NULL THEN 'null()'
365 WHEN LENGTH( attr_def.stat_cat_data ) = 1 THEN '//*[@code="' || attr_def.stat_cat_data || '"]'
366 ELSE '//*' || attr_def.stat_cat_data
371 WHEN attr_def.parts_data IS NULL THEN 'null()'
372 WHEN LENGTH( attr_def.parts_data ) = 1 THEN '//*[@code="' || attr_def.parts_data || '"]'
373 ELSE '//*' || attr_def.parts_data
378 xpaths := ARRAY[owning_lib, circ_lib, call_number, copy_number, status, location, circulate,
379 deposit, deposit_amount, ref, holdable, price, barcode, circ_modifier, circ_as_type,
380 alert_message, pub_note, priv_note, internal_id, stat_cat_data, parts_data, opac_visible];
384 FROM oils_xpath_tag_to_table( (SELECT marc FROM vandelay.queued_bib_record WHERE id = import_id), attr_def.tag, xpaths)
385 AS t( ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
386 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
387 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT,
388 stat_cat_data TEXT, parts_data TEXT, opac_vis TEXT )
391 attr_set.import_error := NULL;
392 attr_set.error_detail := NULL;
393 attr_set.deposit_amount := NULL;
394 attr_set.copy_number := NULL;
395 attr_set.price := NULL;
396 attr_set.circ_modifier := NULL;
397 attr_set.location := NULL;
398 attr_set.barcode := NULL;
399 attr_set.call_number := NULL;
401 IF tmp_attr_set.pr != '' THEN
402 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
404 attr_set.import_error := 'import.item.invalid.price';
405 attr_set.error_detail := tmp_attr_set.pr; -- original value
406 RETURN NEXT attr_set; CONTINUE;
408 attr_set.price := tmp_str::NUMERIC(8,2);
411 IF tmp_attr_set.dep_amount != '' THEN
412 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
414 attr_set.import_error := 'import.item.invalid.deposit_amount';
415 attr_set.error_detail := tmp_attr_set.dep_amount;
416 RETURN NEXT attr_set; CONTINUE;
418 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
421 IF tmp_attr_set.cnum != '' THEN
422 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
424 attr_set.import_error := 'import.item.invalid.copy_number';
425 attr_set.error_detail := tmp_attr_set.cnum;
426 RETURN NEXT attr_set; CONTINUE;
428 attr_set.copy_number := tmp_str::INT;
431 IF tmp_attr_set.ol != '' THEN
432 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
434 attr_set.import_error := 'import.item.invalid.owning_lib';
435 attr_set.error_detail := tmp_attr_set.ol;
436 RETURN NEXT attr_set; CONTINUE;
440 IF tmp_attr_set.clib != '' THEN
441 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
443 attr_set.import_error := 'import.item.invalid.circ_lib';
444 attr_set.error_detail := tmp_attr_set.clib;
445 RETURN NEXT attr_set; CONTINUE;
449 IF tmp_attr_set.cs != '' THEN
450 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
452 attr_set.import_error := 'import.item.invalid.status';
453 attr_set.error_detail := tmp_attr_set.cs;
454 RETURN NEXT attr_set; CONTINUE;
458 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
460 -- no circ mod defined, see if we should apply a default
461 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
462 FROM actor.org_unit_ancestor_setting(
463 'vandelay.item.circ_modifier.default',
467 -- make sure the value from the org setting is still valid
468 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
470 attr_set.import_error := 'import.item.invalid.circ_modifier';
471 attr_set.error_detail := tmp_attr_set.circ_mod;
472 RETURN NEXT attr_set; CONTINUE;
477 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
479 attr_set.import_error := 'import.item.invalid.circ_modifier';
480 attr_set.error_detail := tmp_attr_set.circ_mod;
481 RETURN NEXT attr_set; CONTINUE;
485 IF tmp_attr_set.circ_as != '' THEN
486 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
488 attr_set.import_error := 'import.item.invalid.circ_as_type';
489 attr_set.error_detail := tmp_attr_set.circ_as;
490 RETURN NEXT attr_set; CONTINUE;
494 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
495 -- no location specified, see if we should apply a default
497 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
498 FROM actor.org_unit_ancestor_setting(
499 'vandelay.item.copy_location.default',
503 -- make sure the value from the org setting is still valid
504 PERFORM 1 FROM asset.copy_location
505 WHERE id = attr_set.location AND NOT deleted;
507 attr_set.import_error := 'import.item.invalid.location';
508 attr_set.error_detail := tmp_attr_set.cs;
509 RETURN NEXT attr_set; CONTINUE;
513 -- search up the org unit tree for a matching copy location
514 WITH RECURSIVE anscestor_depth AS (
518 FROM actor.org_unit ou
519 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
520 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
525 FROM actor.org_unit ou
526 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
527 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
528 ) SELECT cpl.id INTO attr_set.location
529 FROM anscestor_depth a
530 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
531 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
533 ORDER BY a.depth DESC
537 attr_set.import_error := 'import.item.invalid.location';
538 attr_set.error_detail := tmp_attr_set.cs;
539 RETURN NEXT attr_set; CONTINUE;
543 attr_set.circulate :=
544 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
545 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
548 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
549 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
552 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
553 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
555 attr_set.opac_visible :=
556 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
557 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
560 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
561 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
563 attr_set.call_number := tmp_attr_set.cn; -- TEXT
564 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
565 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
566 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
567 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
568 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
569 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
570 attr_set.stat_cat_data := tmp_attr_set.stat_cat_data; -- TEXT,
571 attr_set.parts_data := tmp_attr_set.parts_data; -- TEXT,
573 RETURN NEXT attr_set;
584 CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$
597 IF marc IS NULL OR marc = '' THEN
601 -- First, the count of tags
602 qual := ARRAY_UPPER(oils_xpath('//*[local-name()="datafield"]', marc), 1);
604 -- now go through a bunch of pain to get the record type
605 IF best_type IS NOT NULL THEN
606 ldr := (oils_xpath('//*[local-name()="leader"]/text()', marc))[1];
608 IF ldr IS NOT NULL THEN
609 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
610 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
613 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
614 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
616 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
618 SELECT * INTO type_map FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
620 IF type_map.code IS NOT NULL THEN
621 IF best_type = type_map.code THEN
622 qual := qual + qual / 2;
625 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = 'Lang' AND rec_type = type_map.code ORDER BY tag DESC LOOP
626 ff_tag_data := SUBSTRING((oils_xpath('//*[@tag="' || ff_pos.tag || '"]/text()',marc))[1], ff_pos.start_pos + 1, ff_pos.length);
627 IF ff_tag_data = best_lang THEN
635 -- Now look for some quality metrics
637 IF ARRAY_UPPER(oils_xpath('//*[@tag="040"]/*[@code="a" and contains(.,"DLC")]', marc), 1) = 1 THEN
642 IF (oils_xpath('//*[@tag="003"]/text()', marc))[1] ~* E'oclo?c' THEN
649 $func$ LANGUAGE PLPGSQL;
651 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
653 res authority.simple_heading%ROWTYPE;
654 acsaf authority.control_set_authority_field%ROWTYPE;
655 heading_row authority.heading%ROWTYPE;
666 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
669 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
672 SELECT control_set INTO cset
673 FROM authority.control_set_authority_field
674 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
678 res.record := auth_id;
679 res.thesaurus := authority.extract_thesaurus(marcxml);
681 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
682 res.atag := acsaf.id;
684 IF acsaf.heading_field IS NULL THEN
685 tag_used := acsaf.tag;
686 nfi_used := acsaf.nfi;
687 joiner_text := COALESCE(acsaf.joiner, ' ');
689 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
691 heading_text := COALESCE(
692 oils_xpath_string('//*[local-name()="subfield" and contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
696 IF nfi_used IS NOT NULL THEN
698 sort_text := SUBSTRING(
703 oils_xpath_string('//*[local-name()="datafield"]/@ind'||nfi_used, tmp_xml::TEXT),
715 sort_text := heading_text;
718 IF heading_text IS NOT NULL AND heading_text <> '' THEN
719 res.value := heading_text;
720 res.sort_value := public.naco_normalize(sort_text);
721 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
727 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
728 res.value := heading_row.heading;
729 res.sort_value := heading_row.normalized_heading;
730 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
738 $func$ LANGUAGE PLPGSQL STABLE STRICT;
740 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib(
743 bib_is_deleted boolean DEFAULT false,
744 retain_deleted boolean DEFAULT false
745 ) RETURNS bigint AS $function$
747 new_mapping BOOL := TRUE;
750 tmp_mr metabib.metarecord%ROWTYPE;
751 deleted_mrs BIGINT[];
754 -- We need to make sure we're not a deleted master record of an MR
755 IF bib_is_deleted THEN
756 IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
757 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
760 FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
762 -- Now, are there any more sources on this MR?
763 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
765 IF source_count = 0 AND NOT retain_deleted THEN -- No other records
766 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
767 DELETE FROM metabib.metarecord WHERE id = old_mr;
769 ELSE -- indeed there are. Update it with a null cache and recalcualated master record
770 UPDATE metabib.metarecord
772 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
777 ELSE -- insert or update
779 FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP
781 -- Find the first fingerprint-matching
782 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
784 new_mapping := FALSE;
786 ELSE -- Our fingerprint changed ... maybe remove the old MR
787 DELETE FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id AND source = bib_id; -- remove the old source mapping
788 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
789 IF source_count = 0 THEN -- No other records
790 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
791 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
797 -- we found no suitable, preexisting MR based on old source maps
798 IF old_mr IS NULL THEN
799 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
801 IF old_mr IS NULL THEN -- nope, create one and grab its id
802 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
803 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
805 ELSE -- indeed there is. update it with a null cache and recalcualated master record
806 UPDATE metabib.metarecord
808 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
812 ELSE -- there was one we already attached to, update its mods cache and master_record
813 UPDATE metabib.metarecord
815 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
820 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
825 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
826 UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
832 $function$ LANGUAGE plpgsql;