Evergreen 3.6 Import Issues

__import-records.sql

DROP TABLE IF EXISTS staging_records_import;
CREATE TABLE staging_records_import (id BIGSERIAL, dest BIGINT, marc TEXT);
ALTER SEQUENCE staging_records_import_id_seq RESTART WITH 1;

TRUNCATE TABLE biblio.record_entry CASCADE;
ALTER SEQUENCE biblio.record_entry_id_seq RESTART WITH 1;

CREATE OR REPLACE FUNCTION staging_importer() RETURNS VOID AS $$
DECLARE stage RECORD;
BEGIN
FOR stage IN SELECT * FROM staging_records_import ORDER BY id LOOP
      INSERT INTO biblio.record_entry (marc, last_xact_id) VALUES (stage.marc, 'IMPORT');
      UPDATE staging_records_import SET dest = currval('biblio.record_entry_id_seq')
      WHERE id = stage.id;
   END LOOP;
  END;
  $$ LANGUAGE plpgsql;

COPY staging_records_import (marc) FROM '/home/jeff/records.csv' WITH CSV HEADER;

SELECT staging_importer();

_import-holdings.sql

DROP TABLE IF EXISTS staging_materials;

TRUNCATE TABLE asset.call_number CASCADE;
ALTER SEQUENCE asset.call_number_id_seq RESTART WITH 1;

TRUNCATE TABLE asset.copy CASCADE;
ALTER SEQUENCE asset.copy_id_seq RESTART WITH 1;

TRUNCATE TABLE biblio.monograph_part CASCADE;
ALTER SEQUENCE biblio.monograph_part_id_seq RESTART WITH 1;

CREATE TABLE staging_materials (
  bibkey BIGINT,  -- biblio.record_entry_id
  callnum TEXT, -- call number label
  callnum_prefix TEXT, -- call number prefix
  callnum_suffix TEXT, -- call number suffix
  callnum_class TEXT, -- classification scheme
  create_date DATE,
  location TEXT, -- shelving location code
  item_type TEXT, -- circulation modifier code
  owning_lib TEXT, -- org unit code
  barcode TEXT, -- copy barcode
  part TEXT
);

COPY staging_materials (bibkey, callnum, callnum_prefix,
  callnum_suffix, callnum_class, create_date, location,
  item_type, owning_lib, barcode, part) FROM '/home/jeff/holdings.tsv';

INSERT INTO asset.copy_location (name, owning_lib)
  SELECT DISTINCT location, 1 FROM staging_materials
  WHERE NOT EXISTS (
    SELECT 1 FROM asset.copy_location
    WHERE name = location
  );

-- These are already in the database, no need to update them
--
--INSERT INTO config.circ_modifier (code, name, description, sip2_media_type)
--  SELECT DISTINCT item_type, item_type, item_type, '001'
--  FROM staging_materials
--  WHERE NOT EXISTS (
--    SELECT 1 FROM config.circ_modifier
--    WHERE item_type = code
--  );

INSERT INTO asset.call_number_prefix (owning_lib, label)
  SELECT DISTINCT aou.id, callnum_prefix
  FROM staging_materials sm
    INNER JOIN actor.org_unit aou
      ON aou.shortname = sm.owning_lib
  WHERE NOT EXISTS (
    SELECT 1 FROM asset.call_number_prefix acnp
    WHERE callnum_prefix = acnp.label
      AND aou.id = acnp.owning_lib
  ) AND callnum_prefix IS NOT NULL;

INSERT INTO asset.call_number_suffix (owning_lib, label)
  SELECT DISTINCT aou.id, callnum_suffix
  FROM staging_materials sm
    INNER JOIN actor.org_unit aou
      ON aou.shortname = sm.owning_lib
  WHERE NOT EXISTS (
    SELECT 1 FROM asset.call_number_suffix acns
    WHERE callnum_suffix = acns.label
      AND aou.id = acns.owning_lib
  ) AND callnum_suffix IS NOT NULL;

INSERT INTO asset.call_number (
  creator, editor, record, owning_lib, label, prefix, suffix, label_class
)
  SELECT DISTINCT 1, 1, bibkey, aou.id, callnum, acnp.id, acns.id,
  CASE WHEN callnum_class = 'LC' THEN 1
             WHEN callnum_class = 'DEWEY' THEN 2
  END
  FROM staging_materials sm
    INNER JOIN actor.org_unit aou
      ON aou.shortname = owning_lib
    INNER JOIN asset.call_number_prefix acnp
      ON COALESCE(acnp.label, '') = COALESCE(callnum_prefix, '')
    INNER JOIN asset.call_number_suffix acns
      ON COALESCE(acns.label, '') = COALESCE(callnum_suffix, '')
;

INSERT INTO asset.copy (
  circ_lib, creator, editor, call_number, location,
 loan_duration, fine_level, barcode
)
  SELECT DISTINCT aou.id, 1, 1, acn.id, acl.id, 2, 2, barcode
  FROM staging_materials sm
    INNER JOIN actor.org_unit aou
      ON aou.shortname = sm.owning_lib
    INNER JOIN asset.copy_location acl
      ON acl.name = sm.location
    INNER JOIN asset.call_number acn
      ON acn.label = sm.callnum
  WHERE acn.deleted IS FALSE
;

INSERT INTO biblio.monograph_part (record, label)
  SELECT DISTINCT bibkey, part
  FROM staging_materials sm
  WHERE part IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM biblio.monograph_part bmp
    WHERE sm.part = bmp.label
      AND sm.bibkey = bmp.record
  );

INSERT INTO asset.copy_part_map (target_copy, part)
  SELECT DISTINCT acp.id, bmp.id
  FROM staging_materials sm
    INNER JOIN asset.copy acp
      ON acp.barcode = sm.barcode
    INNER JOIN biblio.monograph_part bmp
      ON bmp.record = sm.bibkey
  WHERE part IS NOT NULL
    AND part = bmp.label
    AND acp.deleted IS FALSE
    AND NOT EXISTS (
    SELECT 1 FROM asset.copy_part_map
    WHERE target_copy = acp.id
      AND part = bmp.id
  );

holdings.tsv

query-for-trade.sql

-- Web search for "trade"
WITH w AS (
	WITH x5638728a7ba8_keyword_xq AS (
		SELECT
			(to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_14705$trade$_14705$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_14705$trade$_14705$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq,
			(to_tsquery('simple', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_14705$trade$_14705$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), '')) || to_tsquery('english_nostop', COALESCE(NULLIF( '(' || btrim(regexp_replace(search_normalize(split_date_range($_14705$trade$_14705$)),E'(?:\\s+|:)','&','g'),'&|')  || ')', '()'), ''))) AS tsq_rank
	),
	lang_with AS (
		SELECT id FROM config.coded_value_map WHERE ctype = 'item_lang' AND code = $_14705$eng$_14705$
	),
	pop_with AS (
		SELECT
			record,
			ARRAY_AGG(badge) AS badges,
			SUM(s.score::NUMERIC*b.weight::NUMERIC)/SUM(b.weight::NUMERIC) AS total_score
		FROM rating.record_badge_score s
		JOIN rating.badge b ON (
                       	b.id = s.badge
			AND b.scope = ANY ('{1}'))
		GROUP BY 1
	),
	c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[search.calculate_visibility_attribute_test('circ_lib','{1}',FALSE)],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x),
	b_attr AS (SELECT (search.calculate_visibility_attribute_test('luri_org','{1}',FALSE))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)
	SELECT  id,
	        rel,
	        CASE WHEN cardinality(records) = 1 THEN records[1] ELSE NULL END AS record,
	        NULL::INT AS total,
	        NULL::INT AS checked,
	        NULL::INT AS visible,
	        NULL::INT AS deleted,
	        NULL::INT AS excluded,
	        badges,
	        popularity
	FROM (
		SELECT  m.source AS id,
			ARRAY[m.source] AS records,
	                (AVG(
				(COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', x5638728a7ba8_keyword.index_vector, x5638728a7ba8_keyword.tsq_rank, 14) * x5638728a7ba8_keyword.weight * 1000, 0.0)
				* COALESCE(evergreen.rel_bump(('{' || quote_literal(search_normalize($_14705$trade$_14705$)) || '}')::TEXT[], x5638728a7ba8_keyword.value, '{word_order}'::TEXT[], '{10}'::NUMERIC[]),1.0))
				)+1 * COALESCE( 5, 1))::NUMERIC AS rel,
	                1.0/((AVG(
				(COALESCE(ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', x5638728a7ba8_keyword.index_vector, x5638728a7ba8_keyword.tsq_rank, 14) * x5638728a7ba8_keyword.weight * 1000, 0.0)
				* COALESCE(evergreen.rel_bump(('{' || quote_literal(search_normalize($_14705$trade$_14705$)) || '}')::TEXT[], x5638728a7ba8_keyword.value, '{word_order}'::TEXT[], '{10}'::NUMERIC[]),1.0))
				)+1 * COALESCE( 5, 1)))::NUMERIC AS rank,
	                FIRST(pubdate_t.value) AS tie_break,
	                STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges,
	                AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity
		FROM metabib.metarecord_source_map m
			LEFT JOIN (
				SELECT fe.*, fe_weight.weight, x5638728a7ba8_keyword_xq.tsq, x5638728a7ba8_keyword_xq.tsq_rank /* search */
				FROM metabib.keyword_field_entry AS fe
				JOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)
				JOIN x5638728a7ba8_keyword_xq ON (fe.index_vector @@ x5638728a7ba8_keyword_xq.tsq)
				UNION ALL
					SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x5638728a7ba8_keyword_xq.tsq, x5638728a7ba8_keyword_xq.tsq_rank /* virtual field addition */
					FROM  metabib.title_field_entry AS fe
					JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (6,53) AND fe_weight.real = fe.field)
					JOIN x5638728a7ba8_keyword_xq ON (fe.index_vector @@ x5638728a7ba8_keyword_xq.tsq)
					UNION ALL
						SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x5638728a7ba8_keyword_xq.tsq, x5638728a7ba8_keyword_xq.tsq_rank /* virtual field addition */
						FROM metabib.subject_field_entry AS fe
						JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (16) AND fe_weight.real = fe.field)
						JOIN x5638728a7ba8_keyword_xq ON (fe.index_vector @@ x5638728a7ba8_keyword_xq.tsq)
						UNION ALL
							SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x5638728a7ba8_keyword_xq.tsq, x5638728a7ba8_keyword_xq.tsq_rank /* virtual field addition */
							FROM  metabib.keyword_field_entry AS fe
							JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (39,41,42,46,47,48,50) AND fe_weight.real = fe.field)
							JOIN x5638728a7ba8_keyword_xq ON (fe.index_vector @@ x5638728a7ba8_keyword_xq.tsq)
							UNION ALL
								SELECT fe.id, fe.source, fe.field, fe.value, fe.index_vector, fe_weight.weight, x5638728a7ba8_keyword_xq.tsq, x5638728a7ba8_keyword_xq.tsq_rank /* virtual field addition */
								FROM  metabib.author_field_entry AS fe
								JOIN config.metabib_field_virtual_map AS fe_weight ON (fe_weight.virtual = 45 AND fe_weight.real IN (8) AND fe_weight.real = fe.field)
								JOIN x5638728a7ba8_keyword_xq ON (fe.index_vector @@ x5638728a7ba8_keyword_xq.tsq)
			) AS x5638728a7ba8_keyword ON (m.source = x5638728a7ba8_keyword.source)
			INNER JOIN metabib.record_attr_vector_list mrv ON m.source = mrv.source
	                INNER JOIN biblio.record_entry bre ON m.source = bre.id AND NOT bre.deleted
	                LEFT JOIN pop_with ON ( m.source = pop_with.record )
	                LEFT JOIN metabib.record_sorter pubdate_t ON m.source = pubdate_t.source AND attr = 'pubdate'
	                ,lang_with
	                ,c_attr
	                ,b_attr
	          WHERE 1=1
	                AND (
	          (
	            (x5638728a7ba8_keyword.id IS NOT NULL)
	          )
	        )
	        AND (
	          ((EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source AND vis_attr_vector @@ c_attr.vis_test) OR (NOT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source) AND (bre.vis_attr_vector IS NULL OR NOT ( int4range(0,268435455,'[]') @> ANY(bre.vis_attr_vector) ))))) OR ((b_attr.vis_test IS NULL OR bre.vis_attr_vector @@ b_attr.vis_test))
	        )
	          GROUP BY 1
	          ORDER BY 4 ASC NULLS LAST,  5 DESC NULLS LAST, 3 DESC
	          LIMIT 100000
	        ) AS core_query
	) (SELECT * FROM w LIMIT 1000 OFFSET 0)
	        UNION ALL
	  SELECT NULL,NULL,NULL,COUNT(*),COUNT(*),COUNT(*),0,0,NULL,NULL FROM w;

records.csv

sample-marc-record-formatted.xml

<record>
    <leader>         a22        4500</leader>
    <datafield ind1=""1"" ind2=""#"" tag=""100"">
        <subfield code=""a"">United States. Congress.  House.  Committee on Ways and Means</subfield>
    </datafield>
    <datafield ind1=""0"" ind2=""#"" tag=""650"">
        <subfield code=""a"">Commercial policy</subfield>
    </datafield>
    <datafield ind1=""0"" ind2=""#"" tag=""050"">
        <subfield code=""a"">KF27</subfield>
    </datafield>
    <datafield ind1=""0"" ind2=""0"" tag=""245"">
        <subfield code=""a"">Trade and International Economic Policy Reform Act of 1987</subfield>
    </datafield>
    <datafield ind1=""#"" ind2=""#"" tag=""260"">
        <subfield code=""a"">Washington, D.C.</subfield>
        <subfield code=""b"">U.S. Congress, House Committee on Ways and Means</subfield>
        <subfield code=""c"">1987</subfield>
    </datafield>
    <datafield ind1=""#"" ind2=""#"" tag=""300"">
        <subfield code=""a"">6 v.</subfield>
    </datafield>
    <datafield ind1=""0"" ind2=""#"" tag=""490"">
        <subfield code=""a"">Report ;</subfield>
    </datafield>
    <datafield ind1=""#"" ind2=""0"" tag=""830"">
        <subfield code=""a"">100-40</subfield>
    </datafield>
    <datafield ind1=""#"" ind2=""#"" tag=""999"">
        <subfield code=""a"">Commercial policy.1000</subfield>
        <subfield code=""i"">1000</subfield>
        <subfield code=""k"">Available</subfield>
        <subfield code=""l"">File Cabinets</subfield>
        <subfield code=""m"">ARE</subfield>
        <subfield code=""t"">001</subfield>
        <subfield code=""u"">1</subfield>
        <subfield code=""x"">holdable</subfield>
        <subfield code=""y"">circulating</subfield>
        <subfield code=""z"">visible</subfield>
    </datafield>
</record>