From 27124a4e6bd93cc4fe55a9867f759a708dd434e5 Mon Sep 17 00:00:00 2001 From: Doug Kyle Date: Thu, 3 Apr 2014 14:24:34 -0400 Subject: [PATCH] Added loc_or_locgroup function Intended to to make the shelf_is_group config attribute usefull by replacing joins to smart_float.config based on copy.location with joins based on loc_or_locgroup(copy.location). The mapping of multiple locations to a single group config record works but performance was significantly slower. Need to speed it up or do something else. Signed-off-by: Doug Kyle --- Open-ILS/src/sql/Pg/smart_float.sql | 25 ++++++++++++++++++++++++- 1 file changed, 24 insertions(+), 1 deletion(-) diff --git a/Open-ILS/src/sql/Pg/smart_float.sql b/Open-ILS/src/sql/Pg/smart_float.sql index b2db38c26d..e7e0202e54 100644 --- a/Open-ILS/src/sql/Pg/smart_float.sql +++ b/Open-ILS/src/sql/Pg/smart_float.sql @@ -14,7 +14,6 @@ items_allowed INT, homing_threshold INT NOT NULL DEFAULT 0, homing_lifespan TEXT DEFAULT '1 day', CONSTRAINT org_unit_fkey FOREIGN KEY (org_unit) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -CONSTRAINT shelf_location_fkey FOREIGN KEY (shelf_location) REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT org_plus_loc UNIQUE(org_unit,shelf_location) ); @@ -228,6 +227,7 @@ select orgs.id as lib,coalesce(from_count,0),coalesce(to_count,0),coalesce(to_co END; $function$; +-- !!! smart_float.libs !!! CREATE OR REPLACE FUNCTION smart_float.libs(chkin_lib INT) RETURNS BOOLEAN LANGUAGE plpgsql @@ -236,6 +236,7 @@ BEGIN RETURN chkin_lib in (select org_unit from config.smart_float where active is true group by 1); END; +-- !!! smart_float.in_use !!! CREATE OR REPLACE FUNCTION smart_float.in_use() RETURNS boolean LANGUAGE plpgsql @@ -245,5 +246,27 @@ BEGIN END; $function$; +-- !!! smart_float.loc_or_locgroup !!! +CREATE OR REPLACE FUNCTION smart_float.loc_or_locgroup(loc integer) + RETURNS integer + LANGUAGE plpgsql +AS $function$ +DECLARE + found_loc BOOLEAN; + loc_group INT; +BEGIN + SELECT INTO found_loc loc IN (select distinct(shelf_location) from config.smart_float where shelf_is_group is FALSE); + IF found_loc IS FALSE THEN + SELECT INTO loc_group lgroup from asset.copy_location_group_map where location = loc + and lgroup in (select distinct(shelf_location) from config.smart_float where shelf_is_group is TRUE); + IF FOUND THEN + RETURN loc_group; + END IF; + END IF; + RETURN loc; +END; +$function$; + + -- !!! need some new indexes create index concurrently cp_location_idx on asset.copy (location); -- 2.11.0