From 712d0985a1d57a30082cd66e07c928784b268c81 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 22 Aug 2011 12:47:34 -0400 Subject: [PATCH] Further upgrade script cleanup: address hold matrix DDL/DML ordering; use code instead of id for permission addition; include recent updates for lasso visiblity Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql | 97 ++++++++++++++++++++++++++---- 1 file changed, 85 insertions(+), 12 deletions(-) diff --git a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql index e436bc64dc..23bf41a9b6 100644 --- a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql @@ -10,8 +10,16 @@ BEGIN; ALTER TABLE permission.grp_tree ADD COLUMN hold_priority INT NOT NULL DEFAULT 0; --- 0430 -ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN strict_ou_match BOOL NOT NULL DEFAULT FALSE; +-- 0430 and friends +ALTER TABLE config.hold_matrix_matchpoint + ADD COLUMN strict_ou_match BOOL NOT NULL DEFAULT FALSE, + ADD COLUMN marc_bib_level text, + DROP CONSTRAINT hous_once_per_grp_loc_mod_marc, + DROP CONSTRAINT hold_matrix_matchpoint_marc_form_fkey, + DROP CONSTRAINT hold_matrix_matchpoint_marc_type_fkey, + DROP CONSTRAINT hold_matrix_matchpoint_marc_vr_format_fkey; + + -- Replace all uses of PostgreSQL's built-in LOWER() function with -- a more locale-savvy PLPERLU evergreen.lowercase() function @@ -644,9 +652,6 @@ ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_type_fkey, DROP CONSTRAINT circ_matrix_matchpoint_marc_vr_format_fkey; -ALTER TABLE config.hold_matrix_matchpoint - DROP CONSTRAINT hous_once_per_grp_loc_mod_marc; - -- Clean up tables before making normalized index CREATE OR REPLACE FUNCTION action.cleanup_matrix_matchpoints() RETURNS void AS $func$ @@ -1420,10 +1425,6 @@ INSERT INTO config.coded_value_map (ctype,code,value,description) ALTER TABLE config.i18n_locale DROP CONSTRAINT i18n_locale_marc_code_fkey; -ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_form_fkey; -ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_type_fkey; -ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_vr_format_fkey; - DROP TABLE config.language_map; DROP TABLE config.bib_level_map; DROP TABLE config.item_form_map; @@ -4920,8 +4921,6 @@ ALTER TABLE config.circ_matrix_weights ALTER COLUMN marc_bib_level DROP DEFAULT; ALTER TABLE config.hold_matrix_weights ALTER COLUMN marc_bib_level DROP DEFAULT; -ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN marc_bib_level text; - CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$ DECLARE cn_object asset.call_number%ROWTYPE; @@ -6006,7 +6005,7 @@ INSERT INTO permission.perm_list ,(505, 'UPDATE_WORKSTATION', oils_i18n_gettext(505, 'Allows update of a workstation during workstation registration override.', 'ppl', 'description')) ,(506, 'VIEW_USER_SETTING_TYPE', oils_i18n_gettext(506, 'Allows viewing of configurable user setting types.', 'ppl', 'description')) ) AS np(id,code,description) - LEFT JOIN permission.perm_list pl USING (id) + LEFT JOIN permission.perm_list pl USING (code) WHERE pl.id IS NULL; ; @@ -8459,6 +8458,79 @@ AND format = 'mods32'; \qecho AND subfield IN ('4', 'e') \qecho ) a; +-- Resolves an error in calculating copy counts for org lassos +-- Per LP 790329 +INSERT INTO config.upgrade_log (version) VALUES ('0603'); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.copy_id) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + + +-- Staff record copy counts also triggered an SQL error for org lassos +-- Per LP790329 +-- +INSERT INTO config.upgrade_log (version) VALUES ('0604'); + +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + COMMIT; -- 0529 @@ -8503,3 +8575,4 @@ CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.check_payment FOR EACH CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('check_payment'); + -- 2.11.0