From 55cc3fa36c1832743e1c54c1e20d03982f00409d Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 10 Dec 2014 13:51:51 -0500 Subject: [PATCH] LP#1401177: uniquify attrs across a metarecord Signed-off-by: Mike Rylander Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 52 +++++---- .../sql/Pg/upgrade/XXXX.unique_mr_attrs.sql | 101 ++++++++++++++++++ 2 files changed, 130 insertions(+), 23 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.unique_mr_attrs.sql diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 47b40e3434..8ad2ec413c 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -1283,20 +1283,20 @@ CREATE OR REPLACE FUNCTION unapi.mmr_mra ( ), (SELECT XMLAGG(foo.y) FROM ( - WITH sourcelist AS ( - WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id + WITH sourcelist AS ( + WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1) - SELECT source - FROM metabib.metarecord_source_map, aou + SELECT source + FROM metabib.metarecord_source_map, aou WHERE metarecord = $1 AND ( EXISTS ( - SELECT 1 FROM asset.opac_visible_copies + SELECT 1 FROM asset.opac_visible_copies WHERE record = source AND circ_lib IN ( SELECT id FROM actor.org_unit_descendants(aou.id, $6)) LIMIT 1 - ) + ) OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) - ) + ) ) SELECT cmra.aid, XMLELEMENT( @@ -1313,15 +1313,18 @@ CREATE OR REPLACE FUNCTION unapi.mmr_mra ( cmra.value ) FROM ( - SELECT v.source AS id, - c.id AS aid, - c.ctype AS attr, - c.code AS value - FROM metabib.record_attr_vector_list v - JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + SELECT DISTINCT aid, attr, value + FROM ( + SELECT v.source AS id, + c.id AS aid, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + ) AS x + JOIN sourcelist ON (x.id = sourcelist.source) ) AS cmra - JOIN config.record_attr_definition rad ON (cmra.attr = rad.name) - JOIN sourcelist ON (cmra.id = sourcelist.source) + JOIN config.record_attr_definition rad ON (cmra.attr = rad.name) UNION ALL SELECT umra.aid, XMLELEMENT( @@ -1336,15 +1339,18 @@ CREATE OR REPLACE FUNCTION unapi.mmr_mra ( umra.value ) FROM ( - SELECT v.source AS id, - m.id AS aid, - m.attr AS attr, - m.value AS value - FROM metabib.record_attr_vector_list v - JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + SELECT DISTINCT aid, attr, value + FROM ( + SELECT v.source AS id, + m.id AS aid, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + ) AS x + JOIN sourcelist ON (x.id = sourcelist.source) ) AS umra - JOIN config.record_attr_definition rad ON (umra.attr = rad.name) - JOIN sourcelist ON (umra.id = sourcelist.source) + JOIN config.record_attr_definition rad ON (umra.attr = rad.name) ORDER BY 1 )foo(id,y) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.unique_mr_attrs.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.unique_mr_attrs.sql new file mode 100644 index 0000000000..00a549b897 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.unique_mr_attrs.sql @@ -0,0 +1,101 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION unapi.mmr_mra ( + obj_id BIGINT, + format TEXT, + ename TEXT, + includes TEXT[], + org TEXT, + depth INT DEFAULT NULL, + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + include_xmlns BOOL DEFAULT TRUE, + pref_lib INT DEFAULT NULL +) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name attributes, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord + ), + (SELECT XMLAGG(foo.y) + FROM ( + WITH sourcelist AS ( + WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id + FROM actor.org_unit WHERE shortname = $5 LIMIT 1) + SELECT source + FROM metabib.metarecord_source_map, aou + WHERE metarecord = $1 AND ( + EXISTS ( + SELECT 1 FROM asset.opac_visible_copies + WHERE record = source AND circ_lib IN ( + SELECT id FROM actor.org_unit_descendants(aou.id, $6)) + LIMIT 1 + ) + OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) + ) + ) + SELECT cmra.aid, + XMLELEMENT( + name field, + XMLATTRIBUTES( + cmra.attr AS name, + cmra.value AS "coded-value", + cmra.aid AS "cvmid", + rad.composite, + rad.multi, + rad.filter, + rad.sorter + ), + cmra.value + ) + FROM ( + SELECT DISTINCT aid, attr, value + FROM ( + SELECT v.source AS id, + c.id AS aid, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + ) AS x + JOIN sourcelist ON (x.id = sourcelist.source) + ) AS cmra + JOIN config.record_attr_definition rad ON (cmra.attr = rad.name) + UNION ALL + SELECT umra.aid, + XMLELEMENT( + name field, + XMLATTRIBUTES( + umra.attr AS name, + rad.composite, + rad.multi, + rad.filter, + rad.sorter + ), + umra.value + ) + FROM ( + SELECT DISTINCT aid, attr, value + FROM ( + SELECT v.source AS id, + m.id AS aid, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + ) AS x + JOIN sourcelist ON (x.id = sourcelist.source) + ) AS umra + JOIN config.record_attr_definition rad ON (umra.attr = rad.name) + ORDER BY 1 + + )foo(id,y) + ) + ) +$F$ LANGUAGE SQL STABLE; + +COMMIT; + -- 2.43.2