From 4aa99504674c5c502d668b6415e3f128fcc414d7 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 23 Mar 2022 18:11:15 -0400 Subject: [PATCH] LP#1729620: Rebase and remove array_accum uses Signed-off-by: Mike Rylander Signed-off-by: Jane Sandberg --- Open-ILS/src/sql/Pg/600.schema.oai.sql | 27 ++++++++++++++++------ .../src/sql/Pg/upgrade/XXXX.schema.oai_views.sql | 4 ++-- 2 files changed, 22 insertions(+), 9 deletions(-) diff --git a/Open-ILS/src/sql/Pg/600.schema.oai.sql b/Open-ILS/src/sql/Pg/600.schema.oai.sql index 677e662cfa..658bf0aa5f 100644 --- a/Open-ILS/src/sql/Pg/600.schema.oai.sql +++ b/Open-ILS/src/sql/Pg/600.schema.oai.sql @@ -5,7 +5,7 @@ CREATE SCHEMA oai; CREATE VIEW oai.biblio AS SELECT bre.id AS rec_id, - bre.edit_date AS datestamp, + bre.edit_date AT TIME ZONE 'UTC' AS datestamp, bre.deleted AS deleted FROM biblio.record_entry bre @@ -15,16 +15,29 @@ CREATE VIEW oai.biblio AS -- The view presents a lean table with unique are.tc-numbers for oai paging; CREATE VIEW oai.authority AS SELECT - are.id AS rec_id, - are.edit_date AS datestamp, - are.deleted AS deleted + are.id AS rec_id, + are.edit_date AT TIME ZONE 'UTC' AS datestamp, + are.deleted AS deleted FROM authority.record_entry AS are ORDER BY are.id; -CREATE OR REPLACE function oai.bib_is_visible_at_org(bib BIGINT, org INT) RETURNS BOOL AS $F$ -WITH orgs AS (SELECT array_accum(id) id FROM actor.org_unit_descendants(org)) -SELECT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache, orgs WHERE vis_attr_vector @@ search.calculate_visibility_attribute_test('circ_lib', orgs.id)::query_int AND bib=record) +CREATE OR REPLACE function oai.bib_is_visible_at_org_by_copy(bib BIGINT, org INT) RETURNS BOOL AS $F$ +WITH corgs AS (SELECT array_agg(id) AS list FROM actor.org_unit_descendants(org)) + SELECT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache, corgs WHERE vis_attr_vector @@ search.calculate_visibility_attribute_test('circ_lib', corgs.list)::query_int AND bib=record) +$F$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE function oai.bib_is_visible_at_org_by_luri(bib BIGINT, org INT) RETURNS BOOL AS $F$ +WITH lorgs AS(SELECT array_agg(id) AS list FROM actor.org_unit_ancestors(org)) + SELECT EXISTS (SELECT 1 FROM biblio.record_entry, lorgs WHERE vis_attr_vector @@ search.calculate_visibility_attribute_test('luri_org', lorgs.list)::query_int AND bib=id) +$F$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE function oai.bib_is_visible_by_source(bib BIGINT, src TEXT) RETURNS BOOL AS $F$ + SELECT EXISTS (SELECT 1 FROM biblio.record_entry b JOIN config.bib_source s ON (b.source = s.id) WHERE transcendant AND s.source = src AND bib=b.id) +$F$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE function oai.auth_is_visible_by_axis(auth BIGINT, ax TEXT) RETURNS BOOL AS $F$ + SELECT EXISTS (SELECT 1 FROM authority.browse_axis_authority_field_map m JOIN authority.simple_heading r on (r.atag = m.field AND r.record = auth AND m.axis = ax)) $F$ LANGUAGE SQL STABLE; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.oai_views.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.oai_views.sql index 2b9d007d18..b81ffb6ec7 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.oai_views.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.oai_views.sql @@ -28,12 +28,12 @@ CREATE VIEW oai.authority AS are.id; CREATE OR REPLACE function oai.bib_is_visible_at_org_by_copy(bib BIGINT, org INT) RETURNS BOOL AS $F$ -WITH corgs AS (SELECT array_accum(id) AS list FROM actor.org_unit_descendants(org)) +WITH corgs AS (SELECT array_agg(id) AS list FROM actor.org_unit_descendants(org)) SELECT EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache, corgs WHERE vis_attr_vector @@ search.calculate_visibility_attribute_test('circ_lib', corgs.list)::query_int AND bib=record) $F$ LANGUAGE SQL STABLE; CREATE OR REPLACE function oai.bib_is_visible_at_org_by_luri(bib BIGINT, org INT) RETURNS BOOL AS $F$ -WITH lorgs AS(SELECT array_accum(id) AS list FROM actor.org_unit_ancestors(org)) +WITH lorgs AS(SELECT array_agg(id) AS list FROM actor.org_unit_ancestors(org)) SELECT EXISTS (SELECT 1 FROM biblio.record_entry, lorgs WHERE vis_attr_vector @@ search.calculate_visibility_attribute_test('luri_org', lorgs.list)::query_int AND bib=id) $F$ LANGUAGE SQL STABLE; -- 2.11.0