From: miker Date: Wed, 13 Apr 2011 15:14:40 +0000 (+0000) Subject: add SVF data to in-db unAPI X-Git-Url: http://git.equinoxoli.org/?p=evergreen-equinox.git;a=commitdiff_plain;h=6d7c451e997bf8f83323563ac4997580c64936a7 add SVF data to in-db unAPI git-svn-id: svn://svn.open-ils.org/ILS/trunk@20063 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 55bff9f..306c58b 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0512'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0513'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index ae69074..d0e11c2 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -47,6 +47,7 @@ CREATE OR REPLACE FUNCTION unapi.ccs ( obj_id BIGINT, format TEXT, ename TEXT CREATE OR REPLACE FUNCTION unapi.ascecm ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.holdings_xml ( bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.biblio_record_entry_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, title TEXT DEFAULT NULL, description TEXT DEFAULT NULL, creator TEXT DEFAULT NULL, update_ts TEXT DEFAULT NULL, unapi_url TEXT DEFAULT NULL, header_xml XML DEFAULT NULL ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; @@ -143,6 +144,7 @@ DECLARE top_el TEXT; output XML; hxml XML; + axml XML; BEGIN SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; @@ -166,6 +168,13 @@ BEGIN SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id; + -- grab SVF if we need them + IF ('mra' = ANY (includes)) THEN + axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL); + ELSE + axml := NULL::XML; + END IF; + -- grab hodlings if we need them IF ('holdings_xml' = ANY (includes)) THEN hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns); @@ -188,6 +197,10 @@ BEGIN top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1'); + IF axml IS NOT NULL THEN + tmp_xml := REGEXP_REPLACE(tmp_xml, '(.*?)$', axml || '\\1'); + END IF; + IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position? tmp_xml := REGEXP_REPLACE(tmp_xml, '(.*?)$', hxml || '\\1'); END IF; @@ -781,6 +794,35 @@ CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, GROUP BY uri.id, use_restriction, href, label; $F$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) 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@mra/' || mra.id AS id, + 'tag:open-ils.org:U2@bre/' || mra.id AS record + ), + (SELECT XMLAGG(foo.y) + FROM (SELECT XMLELEMENT( + name field, + XMLATTRIBUTES( + key AS name, + cvm.value AS "coded-value", + rad.filter, + rad.sorter + ), + x.value + ) + FROM EACH(mra.attrs) AS x + JOIN config.record_attr_definition rad ON (x.key = rad.name) + LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value) + )foo(y) + ) + ) + FROM metabib.record_attr mra + WHERE mra.id = $1; +$F$ LANGUAGE SQL; + /* -- Some test queries diff --git a/Open-ILS/src/sql/Pg/upgrade/0513.schema.svf-in-unapi2.sql b/Open-ILS/src/sql/Pg/upgrade/0513.schema.svf-in-unapi2.sql new file mode 100644 index 0000000..ceafe3c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0513.schema.svf-in-unapi2.sql @@ -0,0 +1,127 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0513'); -- miker + +CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) 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@mra/' || mra.id AS id, + 'tag:open-ils.org:U2@bre/' || mra.id AS record + ), + (SELECT XMLAGG(foo.y) + FROM (SELECT XMLELEMENT( + name field, + XMLATTRIBUTES( + key AS name, + cvm.value AS "coded-value", + rad.filter, + rad.sorter + ), + x.value + ) + FROM EACH(mra.attrs) AS x + JOIN config.record_attr_definition rad ON (x.key = rad.name) + LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value) + )foo(y) + ) + ) + FROM metabib.record_attr mra + WHERE mra.id = $1; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ +DECLARE + me biblio.record_entry%ROWTYPE; + layout unapi.bre_output_layout%ROWTYPE; + xfrm config.xml_transform%ROWTYPE; + ouid INT; + tmp_xml TEXT; + top_el TEXT; + output XML; + hxml XML; + axml XML; +BEGIN + + SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; + + IF ouid IS NULL THEN + RETURN NULL::XML; + END IF; + + IF format = 'holdings_xml' THEN -- the special case + output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns); + RETURN output; + END IF; + + SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format; + + IF layout.name IS NULL THEN + RETURN NULL::XML; + END IF; + + SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform; + + SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id; + + -- grab SVF if we need them + IF ('mra' = ANY (includes)) THEN + axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL); + ELSE + axml := NULL::XML; + END IF; + + -- grab hodlings if we need them + IF ('holdings_xml' = ANY (includes)) THEN + hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns); + ELSE + hxml := NULL::XML; + END IF; + + + -- generate our item node + + + IF format = 'marcxml' THEN + tmp_xml := me.marc; + IF tmp_xml !~ E'(.*?)$', axml || '\\1'); + END IF; + + IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position? + tmp_xml := REGEXP_REPLACE(tmp_xml, '(.*?)$', hxml || '\\1'); + END IF; + + IF ('bre.unapi' = ANY (includes)) THEN + output := REGEXP_REPLACE( + tmp_xml, + '(.*?)', + XMLELEMENT( + name abbr, + XMLATTRIBUTES( + 'http://www.w3.org/1999/xhtml' AS xmlns, + 'unapi-id' AS class, + 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title + ) + )::TEXT || '\\1' + ); + ELSE + output := tmp_xml; + END IF; + + RETURN output; +END; +$F$ LANGUAGE PLPGSQL; + +COMMIT; +