From 729362212ed46ab85b54a14a16ebae449abe1b70 Mon Sep 17 00:00:00 2001 From: "Joshua D. Drake" Date: Wed, 12 Aug 2015 21:53:19 +0000 Subject: [PATCH] JBAS-837 we are now a 2.5.7 alpha, still need to compare schema against prod --- KCLS/sqitch/deploy/02.collection-hq-items-2.5.sql | 131 +++++++++++++++++++++ ...1765-reingest-causing-false-browse-headings.sql | 93 +++++++++++++++ KCLS/sqitch/revert/02.collection-hq-items-2.5.sql | 7 ++ ...1765-reingest-causing-false-browse-headings.sql | 7 ++ KCLS/sqitch/sqitch.plan | 2 + KCLS/sqitch/verify/02.collection-hq-items-2.5.sql | 7 ++ ...1765-reingest-causing-false-browse-headings.sql | 7 ++ 7 files changed, 254 insertions(+) create mode 100644 KCLS/sqitch/deploy/02.collection-hq-items-2.5.sql create mode 100644 KCLS/sqitch/deploy/KMAIN-1765-reingest-causing-false-browse-headings.sql create mode 100644 KCLS/sqitch/revert/02.collection-hq-items-2.5.sql create mode 100644 KCLS/sqitch/revert/KMAIN-1765-reingest-causing-false-browse-headings.sql create mode 100644 KCLS/sqitch/verify/02.collection-hq-items-2.5.sql create mode 100644 KCLS/sqitch/verify/KMAIN-1765-reingest-causing-false-browse-headings.sql diff --git a/KCLS/sqitch/deploy/02.collection-hq-items-2.5.sql b/KCLS/sqitch/deploy/02.collection-hq-items-2.5.sql new file mode 100644 index 0000000000..8a42d57c92 --- /dev/null +++ b/KCLS/sqitch/deploy/02.collection-hq-items-2.5.sql @@ -0,0 +1,131 @@ +BEGIN; + +-- Deploy kcls-evergreen:02.collection-hq-items-2.5 to pg +-- requires: 01.2.4.1-2.5.7-upgrade + +CREATE OR REPLACE FUNCTION + collectionhq.write_item_rows_to_stdout(TEXT, INTEGER) RETURNS TEXT AS $$ + +DECLARE + item BIGINT; + authority_code ALIAS FOR $1; + org_unit_id ALIAS for $2; + lms_bib_id BIGINT; + library_code TEXT; + bar_code TEXT; + last_use_date TEXT; + cumulative_use_total TEXT; + cumulative_use_current TEXT; + status TEXT; + date_added TEXT; + price TEXT; + purchase_code TEXT; + rotating_stock TEXT; + lib_supsel_tag TEXT; + gen_supsel_tag TEXT; + notes TEXT; + extract_date TEXT; + collection_code TEXT; + collection_code_level_2 TEXT; + filter_level_1 TEXT; + filter_level_2 TEXT; + filter_level_3 TEXT; + filter_level_4 TEXT; + isbn TEXT := ''; + output TEXT := ''; + arrived TIMESTAMPTZ; + num_rows INTEGER := 0; + +BEGIN + + SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date; + FOR item, arrived, cumulative_use_total IN + SELECT cp.id, dest_recv_time, COALESCE(sum(DISTINCT c.circ_count), 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count + FROM asset.copy cp + LEFT JOIN extend_reporter.legacy_circ_count c USING (id) + LEFT JOIN (SELECT max(dest_recv_time) as dest_recv_time, target_copy, dest from action.transit_copy group by target_copy, dest) atc ON (cp.id = atc.target_copy AND cp.circ_lib = atc.dest) + LEFT JOIN action.circulation circ ON circ.target_copy = cp.id + LEFT JOIN action.aged_circulation acirc ON acirc.target_copy = cp.id + WHERE NOT cp.deleted AND cp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) GROUP BY cp.id, dest_recv_time ORDER BY cp.id + LOOP + + SELECT cn.record, cn.label + INTO lms_bib_id, filter_level_1 + FROM asset.call_number cn, asset.copy c + WHERE c.call_number = cn.id AND c.id = item; + SELECT r.isbn[1] INTO isbn + FROM reporter.materialized_simple_record r + WHERE id = lms_bib_id; + SELECT collectionHQ.attempt_price(ac.price::TEXT), barcode, ac.status, + REPLACE(create_date::DATE::TEXT, '-', ''), + CASE floating WHEN NULL THEN NULL ELSE 'Y' END + INTO price, bar_code, status, date_added, rotating_stock + FROM asset.copy ac + WHERE id = item; + IF price IS NULL OR price = '' THEN + SELECT collectionHQ.attempt_price((XPATH('//marc:datafield[@tag="020"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT) + INTO price + FROM biblio.record_entry + WHERE id = lms_bib_id; + END IF; + SELECT ou.shortname INTO library_code FROM actor.org_unit ou, asset.copy c WHERE ou.id = c.circ_lib AND c.id = item; + /* SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM action.circulation WHERE target_copy = item ORDER BY xact_start DESC LIMIT 1; */ + SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM ( SELECT action.aged_circulation.xact_start FROM action.aged_circulation where target_copy = item UNION SELECT action.circulation.xact_start FROM action.circulation where target_copy = item) as lu order by xact_start DESC limit 1; + + + IF arrived IS NOT NULL THEN + SELECT COUNT(*) INTO cumulative_use_current FROM action.circulation WHERE target_copy = item AND xact_start > arrived; + ELSE + cumulative_use_current := '0'; + END IF; + SELECT SUBSTRING(value FROM 1 FOR 100) INTO notes FROM asset.copy_note WHERE owning_copy = item AND title ILIKE '%collectionHQ%' ORDER BY id LIMIT 1; + SELECT l.name INTO collection_code FROM asset.copy c, asset.copy_location l WHERE c.location = l.id AND c.id = item; + + purchase_code := ''; -- FIXME do we want something else here? + lib_supsel_tag := ''; -- FIXME do we want something else here? + gen_supsel_tag := ''; -- FIXME do we want something else here? + collection_code_level_2 := ''; -- FIXME do we want something else here? + filter_level_2 := ''; -- FIXME do we want something else here? + filter_level_3 := ''; -- FIXME do we want something else here? + filter_level_4 := ''; -- FIXME do we want something else here? + + output := '##HOLD##,' + || lms_bib_id || ',' + || COALESCE(collectionHQ.quote(authority_code), '') || ',' + || COALESCE(collectionHQ.quote(library_code), '') || ',' + || COALESCE(collectionHQ.quote(bar_code), '') || ',' + || COALESCE(collectionHQ.quote(last_use_date), '') || ',' + || COALESCE(cumulative_use_total, '') || ',' + || COALESCE(cumulative_use_current, '') || ',' + || COALESCE(collectionHQ.quote(status), '') || ',' + || COALESCE(collectionHQ.quote(date_added), '') || ',' + || COALESCE(price, '') || ',' + || COALESCE(collectionHQ.quote(purchase_code), '') || ',' + || COALESCE(collectionHQ.quote(rotating_stock), '') || ',' + || COALESCE(collectionHQ.quote(lib_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(gen_supsel_tag), '') || ',' + || COALESCE(collectionHQ.quote(notes), '') || ',' + || COALESCE(collectionHQ.quote(extract_date), '') || ',' + || COALESCE(collectionHQ.quote(collection_code), '') || ',' + || COALESCE(collectionHQ.quote(collection_code_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_1), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_2), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_3), '') || ',' + || COALESCE(collectionHQ.quote(filter_level_4), '') || ',' + || COALESCE(collectionHQ.quote(isbn), ''); + + RAISE INFO '%', output; + + num_rows := num_rows + 1; + IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF; + + END LOOP; + + RAISE INFO '% rows written in total.', num_rows; + + RETURN ''; +END; + +$$ LANGUAGE plpgsql VOLATILE; + +COMMIT; \ No newline at end of file diff --git a/KCLS/sqitch/deploy/KMAIN-1765-reingest-causing-false-browse-headings.sql b/KCLS/sqitch/deploy/KMAIN-1765-reingest-causing-false-browse-headings.sql new file mode 100644 index 0000000000..2a87f140d8 --- /dev/null +++ b/KCLS/sqitch/deploy/KMAIN-1765-reingest-causing-false-browse-headings.sql @@ -0,0 +1,93 @@ +-- Deploy kcls-evergreen:KMAIN-1765-reingest-causing-false-browse-headings to pg +-- requires: 02.collection-hq-items-2.5 + +BEGIN; + +-- Function: authority.simple_heading_set(text) + +DROP FUNCTION authority.simple_heading_set(text); + +CREATE OR REPLACE FUNCTION authority.simple_heading_set(marcxml text) + RETURNS SETOF authority.simple_heading AS +$BODY$ +DECLARE + res authority.simple_heading%ROWTYPE; + acsaf authority.control_set_authority_field%ROWTYPE; + tag_used TEXT; + nfi_used TEXT; + sf TEXT; + cset INT; + heading_text TEXT; + joiner_text TEXT; + sort_text TEXT; + tmp_text TEXT; + tmp_xml TEXT; + first_sf BOOL; + auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; +BEGIN + --ver1.0 + SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; + + IF cset IS NULL THEN + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[])) + LIMIT 1; + END IF; + + res.record := auth_id; + + FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP + + res.atag := acsaf.id; + tag_used := acsaf.tag; + nfi_used := acsaf.nfi; + joiner_text := COALESCE(acsaf.joiner, ' '); + + FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP + + heading_text := COALESCE( + oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml::TEXT, joiner_text), + '' + ); + IF nfi_used IS NOT NULL THEN + sort_text := SUBSTRING( + heading_text FROM + COALESCE( + NULLIF( + REGEXP_REPLACE( + oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT), + $$\D+$$, + '', + 'g' + ), + '' + )::INT, + 0 + ) + 1 + ); + ELSE + sort_text := heading_text; + END IF; + IF heading_text IS NOT NULL AND heading_text <> '' THEN + res.value := heading_text; + res.sort_value := public.naco_normalize(sort_text); + res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value); + RETURN NEXT res; + END IF; + END LOOP; + END LOOP; + + RETURN; +END; +$BODY$ + LANGUAGE plpgsql IMMUTABLE + COST 100 + ROWS 1000; +ALTER FUNCTION authority.simple_heading_set(text) + OWNER TO evergreen; + + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sqitch/revert/02.collection-hq-items-2.5.sql b/KCLS/sqitch/revert/02.collection-hq-items-2.5.sql new file mode 100644 index 0000000000..03363fd644 --- /dev/null +++ b/KCLS/sqitch/revert/02.collection-hq-items-2.5.sql @@ -0,0 +1,7 @@ +-- Revert kcls-evergreen:02.collection-hq-items-2.5 from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sqitch/revert/KMAIN-1765-reingest-causing-false-browse-headings.sql b/KCLS/sqitch/revert/KMAIN-1765-reingest-causing-false-browse-headings.sql new file mode 100644 index 0000000000..0bc0518052 --- /dev/null +++ b/KCLS/sqitch/revert/KMAIN-1765-reingest-causing-false-browse-headings.sql @@ -0,0 +1,7 @@ +-- Revert kcls-evergreen:KMAIN-1765-reingest-causing-false-browse-headings from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sqitch/sqitch.plan b/KCLS/sqitch/sqitch.plan index 3892fed11d..255cc7a236 100644 --- a/KCLS/sqitch/sqitch.plan +++ b/KCLS/sqitch/sqitch.plan @@ -7,3 +7,5 @@ KMAIN_1729_Bug_from_KMAIN_268 [KMAIN_1448_Bug_from_KMAIN_268] 2015-08-12T19:01:3 01.2.4.1-2.5.7-deps [KMAIN_1729_Bug_from_KMAIN_268] 2015-08-12T20:08:37Z Joshua (JD) Drake # adding data to resolve foreign key constraints 01.2.4.1-2.5.7-upgrade [01.2.4.1-2.5.7-deps] 2015-08-12T19:50:18Z Joshua (JD) Drake # upgrade to 2.5.7 +02.collection-hq-items-2.5 [01.2.4.1-2.5.7-upgrade] 2015-08-12T21:42:53Z Joshua (JD) Drake # added +KMAIN-1765-reingest-causing-false-browse-headings [02.collection-hq-items-2.5] 2015-08-12T21:45:43Z Joshua (JD) Drake # added diff --git a/KCLS/sqitch/verify/02.collection-hq-items-2.5.sql b/KCLS/sqitch/verify/02.collection-hq-items-2.5.sql new file mode 100644 index 0000000000..5656516b23 --- /dev/null +++ b/KCLS/sqitch/verify/02.collection-hq-items-2.5.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:02.collection-hq-items-2.5 on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/KCLS/sqitch/verify/KMAIN-1765-reingest-causing-false-browse-headings.sql b/KCLS/sqitch/verify/KMAIN-1765-reingest-causing-false-browse-headings.sql new file mode 100644 index 0000000000..a1267be765 --- /dev/null +++ b/KCLS/sqitch/verify/KMAIN-1765-reingest-causing-false-browse-headings.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:KMAIN-1765-reingest-causing-false-browse-headings on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0