From 9560b33ec003fd041e761e3377b740ec4a11313e Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Mon, 4 Jun 2018 09:07:33 -0400 Subject: [PATCH] Forward port 3.1.2 upgrade script Signed-off-by: Dan Wells --- .../3.1.1-3.1.2-upgrade-db.sql | 164 ++++++++++++++++++ 1 file changed, 164 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/3.1.1-3.1.2-upgrade-db.sql diff --git a/Open-ILS/src/sql/Pg/version-upgrade/3.1.1-3.1.2-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/3.1.1-3.1.2-upgrade-db.sql new file mode 100644 index 0000000000..ab84919279 --- /dev/null +++ b/Open-ILS/src/sql/Pg/version-upgrade/3.1.1-3.1.2-upgrade-db.sql @@ -0,0 +1,164 @@ +--Upgrade Script for 3.1.1 to 3.1.2 +\set eg_version '''3.1.2''' +BEGIN; +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.1.2', :eg_version); + +SELECT evergreen.upgrade_deps_block_check('1110', :eg_version); + +DROP FUNCTION IF EXISTS unapi.biblio_record_entry_feed (BIGINT[], TEXT, TEXT[], TEXT, INT, HSTORE, HSTORE, + BOOL, TEXT, TEXT, TEXT, TEXT, TEXT, XML); +DROP FUNCTION IF EXISTS unapi.metabib_virtual_record_feed (BIGINT[], TEXT, TEXT[], TEXT, INT, HSTORE, HSTORE, + BOOL, TEXT, TEXT, TEXT, TEXT, TEXT, XML); + +CREATE OR REPLACE FUNCTION unapi.biblio_record_entry_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE 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, pref_lib INT DEFAULT NULL ) RETURNS XML AS $F$ +DECLARE + layout unapi.bre_output_layout%ROWTYPE; + transform config.xml_transform%ROWTYPE; + item_format TEXT; + tmp_xml TEXT; + xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1'; + ouid INT; + element_list TEXT[]; +BEGIN + + IF org = '-' OR org IS NULL THEN + SELECT shortname INTO org FROM evergreen.org_top(); + END IF; + + SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; + SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format; + + IF layout.name IS NULL THEN + RETURN NULL::XML; + END IF; + + SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform; + xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri); + + -- Gather the bib xml + SELECT XMLAGG( unapi.bre(i, format, '', includes, org, depth, slimit, soffset, include_xmlns, pref_lib)) INTO tmp_xml FROM UNNEST( id_list ) i; + + IF layout.title_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title; + END IF; + + IF layout.description_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description; + END IF; + + IF layout.creator_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator; + END IF; + + IF layout.update_ts_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts; + END IF; + + IF unapi_url IS NOT NULL THEN + EXECUTE $$SELECT XMLCONCAT( XMLELEMENT( name link, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-server' AS rel, $1 AS href, 'unapi' AS title)), $2)$$ INTO tmp_xml USING unapi_url, tmp_xml::XML; + END IF; + + IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF; + + element_list := regexp_split_to_array(layout.feed_top,E'\\.'); + FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP + EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML; + END LOOP; + + RETURN tmp_xml::XML; +END; +$F$ LANGUAGE PLPGSQL STABLE; + +CREATE OR REPLACE FUNCTION unapi.metabib_virtual_record_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE 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, pref_lib INT DEFAULT NULL ) RETURNS XML AS $F$ +DECLARE + layout unapi.bre_output_layout%ROWTYPE; + transform config.xml_transform%ROWTYPE; + item_format TEXT; + tmp_xml TEXT; + xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1'; + ouid INT; + element_list TEXT[]; +BEGIN + + IF org = '-' OR org IS NULL THEN + SELECT shortname INTO org FROM evergreen.org_top(); + END IF; + + SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; + SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format; + + IF layout.name IS NULL THEN + RETURN NULL::XML; + END IF; + + SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform; + xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri); + + -- Gather the bib xml + SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns, pref_lib)) INTO tmp_xml FROM UNNEST( id_list ) i; + + IF layout.title_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title; + END IF; + + IF layout.description_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description; + END IF; + + IF layout.creator_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator; + END IF; + + IF layout.update_ts_element IS NOT NULL THEN + EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts; + END IF; + + IF unapi_url IS NOT NULL THEN + EXECUTE $$SELECT XMLCONCAT( XMLELEMENT( name link, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-server' AS rel, $1 AS href, 'unapi' AS title)), $2)$$ INTO tmp_xml USING unapi_url, tmp_xml::XML; + END IF; + + IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF; + + element_list := regexp_split_to_array(layout.feed_top,E'\\.'); + FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP + EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML; + END LOOP; + + RETURN tmp_xml::XML; +END; +$F$ LANGUAGE PLPGSQL STABLE; + + +SELECT evergreen.upgrade_deps_block_check('1111', :eg_version); + +INSERT INTO permission.perm_list ( id, code, description ) VALUES + ( 594, 'ADMIN_COPY_ALERT_TYPE', oils_i18n_gettext( 594, + 'Administer copy alert types', 'ppl', 'description' )), + ( 595, 'CREATE_COPY_ALERT_TYPE', oils_i18n_gettext( 595, + 'Create copy alert types', 'ppl', 'description' )), + ( 596, 'UPDATE_COPY_ALERT_TYPE', oils_i18n_gettext( 596, + 'Update copy alert types', 'ppl', 'description' )), + ( 597, 'DELETE_COPY_ALERT_TYPE', oils_i18n_gettext( 597, + 'Delete copy alert types', 'ppl', 'description' )), + ( 598, 'ADMIN_COPY_ALERT_SUPPRESS', oils_i18n_gettext( 598, + 'Administer copy alert suppression', 'ppl', 'description' )), + ( 599, 'CREATE_COPY_ALERT_SUPPRESS', oils_i18n_gettext( 599, + 'Create copy alert suppression', 'ppl', 'description' )), + ( 600, 'UPDATE_COPY_ALERT_SUPPRESS', oils_i18n_gettext( 600, + 'Update copy alert suppression', 'ppl', 'description' )), + ( 601, 'DELETE_COPY_ALERT_SUPPRESS', oils_i18n_gettext( 601, + 'Delete copy alert suppression', 'ppl', 'description' )), + ( 602, 'ADMIN_COPY_ALERT', oils_i18n_gettext( 602, + 'Administer copy alerts', 'ppl', 'description' )), + ( 603, 'CREATE_COPY_ALERT', oils_i18n_gettext( 603, + 'Create copy alerts', 'ppl', 'description' )), + ( 604, 'VIEW_COPY_ALERT', oils_i18n_gettext( 604, + 'View copy alerts', 'ppl', 'description' )), + ( 605, 'UPDATE_COPY_ALERT', oils_i18n_gettext( 605, + 'Update copy alerts', 'ppl', 'description' )), + ( 606, 'DELETE_COPY_ALERT', oils_i18n_gettext( 606, + 'Delete copy alerts', 'ppl', 'description' )) +; + + +COMMIT; -- 2.43.2