From 1bb10d44996351c362ac837dc8f9a0a641903e05 Mon Sep 17 00:00:00 2001 From: Josh Stompro Date: Fri, 1 Jul 2016 13:54:54 -0500 Subject: [PATCH] LP#1048822 - Fuller Title for reporter.super_simple_record The simplified pull list uses reporter.super_simple_record for title information. reporter.super_simple_record currently only pulls the first 245a value for the title. For titles that use 245(n|p) to fully identify the title this leaves out information that staff need to find the item. This patch adds all of the 245(n|p) to the 245a in the title. I've found this covers most cases where staff need a fuller title. The order of the 245(n|p) also matters, so steps are taken to keep the marc ordering of those fields. Testing: Before Patch - find a title that uses a 245(n|p) field to fully identify the title. Place a hold on the title. Use the simplified pull list to view the location that has that item on the shelf. Observe that only the 245a is in the title field. After Patch - Look at the simplified pull list again and see that the title now includes more information. Signed-off-by: Josh Stompro Signed-off-by: Dan Pearl Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/reporter-schema.sql | 13 ++++- ...XX.lp1048822_fuller_title_super_simple.sql | 48 +++++++++++++++++++ 2 files changed, 60 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lp1048822_fuller_title_super_simple.sql diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 985ca6fb47..28d9d44251 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -154,7 +154,7 @@ SELECT r.id, r.quality, r.tcn_source, r.tcn_value, - FIRST(title.value) AS title, + CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title, FIRST(author.value) AS author, STRING_AGG(DISTINCT publisher.value, ', ') AS publisher, STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate, @@ -168,6 +168,17 @@ SELECT r.id, END AS issn FROM biblio.record_entry r LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a') + LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear. + SELECT b.record, string_agg(val, ' ') AS val FROM ( + SELECT title_np.record, title_np.value AS val + FROM metabib.full_rec title_np + WHERE + title_np.tag = '245' + AND title_np.subfield IN ('p','n') + ORDER BY title_np.id + ) b + GROUP BY 1 + ) title_np ON (title_np.record=r.id) LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a') 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') 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') diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1048822_fuller_title_super_simple.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1048822_fuller_title_super_simple.sql new file mode 100644 index 0000000000..cffad4e7a6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp1048822_fuller_title_super_simple.sql @@ -0,0 +1,48 @@ +BEGIN; + +CREATE OR REPLACE VIEW reporter.old_super_simple_record AS +SELECT r.id, + r.fingerprint, + r.quality, + r.tcn_source, + r.tcn_value, + CONCAT_WS(' ', FIRST(title.value),FIRST(title_np.val)) AS title, + FIRST(author.value) AS author, + STRING_AGG(DISTINCT publisher.value, ', ') AS publisher, + STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate, + CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}' + THEN NULL + ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) + END AS isbn, + CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}' + THEN NULL + ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) + END AS issn + FROM biblio.record_entry r + LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a') + LEFT JOIN ( -- Grab 245 N and P subfields in the order that they appear. + SELECT b.record, string_agg(val, ' ') AS val FROM ( + SELECT title_np.record, title_np.value AS val + FROM metabib.full_rec title_np + WHERE + title_np.tag = '245' + AND title_np.subfield IN ('p','n') + ORDER BY title_np.id + ) b + GROUP BY 1 + ) title_np ON (title_np.record=r.id) + LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a') + 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') + 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') + LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z')) + LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a') + GROUP BY 1,2,3,4,5; + + + -- Remove trigger on biblio.record_entry + SELECT reporter.disable_materialized_simple_record_trigger(); + + -- Rebuild reporter.materialized_simple_record + SELECT reporter.enable_materialized_simple_record_trigger(); + + COMMIT; -- 2.43.2