From 95e6be5c2710f47bd60a9e6cda45796a5b83e65e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 4 Aug 2017 12:52:05 -0400 Subject: [PATCH] LP#1698206: Copy counts generated from new vis cache data The unAPI, erm, API was depending on old copy visibility caching tables. Here we teach it to use the new style. Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 59 ++++--- .../XXXX.schema.copy_vis_attr_cache.sql | 145 ++++++++++++++++++ 2 files changed, 184 insertions(+), 20 deletions(-) diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 364702eba6..0faba5f015 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -554,16 +554,21 @@ BEGIN FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) SELECT ans.depth, ans.id, COUNT( av.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), 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) + FROM mask, + available_statuses, + org_list, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int GROUP BY 1,2,6; IF NOT FOUND THEN @@ -585,16 +590,20 @@ BEGIN FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) SELECT -1, ans.id, COUNT( av.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), 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) + FROM mask, + org_list, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int GROUP BY 1,2,6; IF NOT FOUND THEN @@ -724,17 +733,22 @@ BEGIN FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) SELECT ans.depth, ans.id, COUNT( av.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), COUNT( av.id ), trans - FROM - actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id) - JOIN asset.copy cp ON (cp.id = av.copy_id) + FROM mask, + org_list, + available_statuses, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int GROUP BY 1,2,6; IF NOT FOUND THEN @@ -756,17 +770,22 @@ BEGIN FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) SELECT -1, ans.id, COUNT( av.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), COUNT( av.id ), trans - FROM - actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id) - JOIN asset.copy cp ON (cp.id = av.copy_id) + FROM mask, + org_list, + available_statuses, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int GROUP BY 1,2,6; IF NOT FOUND THEN diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_vis_attr_cache.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_vis_attr_cache.sql index e2e1fcd269..6afae2aa3a 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_vis_attr_cache.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_vis_attr_cache.sql @@ -867,5 +867,150 @@ CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org 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.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), + COUNT( av.id ), + trans + FROM mask, + available_statuses, + org_list, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +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 + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), + COUNT( av.id ), + trans + FROM mask, + org_list, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int + 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; + +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org 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) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), + COUNT( av.id ), + trans + FROM mask, + org_list, + available_statuses, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy) + JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_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) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x), + available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x) + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( (cp.status = ANY (available_statuses.ids))::INT ), + COUNT( av.id ), + trans + FROM mask, + org_list, + available_statuses, + asset.copy_vis_attr_cache av + JOIN asset.copy cp ON (cp.id = av.target_copy) + JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) + WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + COMMIT; -- 2.43.2