From fc3697e161c45323fb7ea3533c87b219d377932e Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Thu, 29 Nov 2012 15:48:28 -0500 Subject: [PATCH] Important (I hope?) parts of FF r84 and all r144 git-svn-id: svn://fulfillment-ill.org/FulfILLment/trunk@144 c09e901b-359b-4c48-93b1-c7450cb1b5b7 Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 21 +++++ Open-ILS/src/sql/Pg/020.schema.functions.sql | 11 +++ Open-ILS/src/sql/Pg/090.schema.action.sql | 64 +++++++++++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 2 + .../sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql | 105 +++++++++++++++++++++ 5 files changed, 203 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 86958525ce..176f4654d1 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -380,6 +380,27 @@ default entry. $$; +CREATE TABLE actor.org_unit_proximity_adjustment ( + id SERIAL PRIMARY KEY, + item_circ_lib INT REFERENCES actor.org_unit (id), + item_owning_lib INT REFERENCES actor.org_unit (id), + copy_location INT REFERENCES asset.copy_location (id), + hold_pickup_lib INT REFERENCES actor.org_unit (id), + hold_request_lib INT REFERENCES actor.org_unit (id), + pos INT NOT NULL DEFAULT 0, + absolute_adjustment BOOL NOT NULL DEFAULT FALSE, + prox_adjustment NUMERIC, + circ_mod TEXT, -- REFERENCES config.circ_modifier (code), + CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL) +); +CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod); +CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib); +CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib); +CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location); +CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib); +CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib); +CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod); + CREATE TABLE actor.hours_of_operation ( id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, dow_0_open TIME NOT NULL DEFAULT '09:00', diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index f69bfa4aa0..739b3177db 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -193,6 +193,17 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABL SELECT * FROM org_unit_ancestors_distance; $$ LANGUAGE SQL STABLE ROWS 1; +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT * FROM org_unit_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_ancestors($1) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 1af6e89ead..55eccb42a3 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -972,5 +972,69 @@ query-based fieldsets. Returns NULL if successful, or an error message if not. $$; +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(ahr_id INT, acp_id BIGINT, context_ou INT) RETURNS NUMERIC AS $f$ +DECLARE + aoupa actor.org_unit_proximity_adjustment%ROWTYPE; + ahr action.hold_request%ROWTYPE; + acp asset.copy%ROWTYPE; + acn asset.call_number%ROWTYPE; + acl asset.copy_location%ROWTYPE; + baseline_prox NUMERIC; + + icl_list INT[]; + iol_list INT[]; + isl_list INT[]; + hpl_list INT[]; + hrl_list INT[]; + +BEGIN + + SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id; + SELECT * INTO acp FROM asset.copy WHERE id = acp_id; + SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number; + SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location; + + -- First, gather the baseline proximity of "here" to pickup lib + SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib; + + -- Find any absolute adjustments, and set the baseline prox to that + SELECT adj.* INTO aoupa + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_anscestors_distance(acp.circ_lib) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND absolute_adjustment + ORDER BY + COALESCE(acp_cl.distance,999) + + COALESCE(acn_ol.distance,999) + + COALESCE(acl_ol.distance,999) + + COALESCE(ahr_pl.distance,999) + + COALESCE(ahr_rl.distance,999), + adj.pos + LIMIT 1; + + IF FOUND THEN + baseline_prox := aoupa.prox_adjustment; + END IF; + + -- Now find any relative adjustments, and change the baseline prox based on them + FOR aoupa IN + SELECT adj.* + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_anscestors_distance(acp.circ_lib) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND NOT absolute_adjustment + LOOP + baseline_prox := baseline_prox + aoupa.prox_adjustment; + END LOOP; + + RETURN baseline_prox; +END; +$f$ LANGUAGE PLPGSQL; COMMIT; diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 53be2e1a9e..6cd6740f16 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -39,6 +39,8 @@ ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_billing_address_fkey FO ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_holds_address_fkey FOREIGN KEY (holds_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_ill_address_fkey FOREIGN KEY (ill_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.org_unit_proximity_adjustment ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE acq.provider ADD CONSTRAINT acq_provider_edi_default_fkey FOREIGN KEY (edi_default) REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE biblio.record_note ADD CONSTRAINT biblio_record_note_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql new file mode 100644 index 0000000000..da0209460b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.org_prox_adjust.sql @@ -0,0 +1,105 @@ +BEGIN; + +CREATE TABLE actor.org_unit_proximity_adjustment ( + id SERIAL PRIMARY KEY, + item_circ_lib INT REFERENCES actor.org_unit (id), + item_owning_lib INT REFERENCES actor.org_unit (id), + copy_location INT REFERENCES asset.copy_location (id), + hold_pickup_lib INT REFERENCES actor.org_unit (id), + hold_request_lib INT REFERENCES actor.org_unit (id), + pos INT NOT NULL DEFAULT 0, + absolute_adjustment BOOL NOT NULL DEFAULT FALSE, + prox_adjustment NUMERIC, + circ_mod TEXT, -- REFERENCES config.circ_modifier (code), + CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL) +); +CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod); +CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib); +CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib); +CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location); +CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib); +CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib); +CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod); + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT * FROM org_unit_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(ahr_id INT, acp_id BIGINT, context_ou INT) RETURNS NUMERIC AS $f$ +DECLARE + aoupa actor.org_unit_proximity_adjustment%ROWTYPE; + ahr action.hold_request%ROWTYPE; + acp asset.copy%ROWTYPE; + acn asset.call_number%ROWTYPE; + acl asset.copy_location%ROWTYPE; + baseline_prox NUMERIC; + + icl_list INT[]; + iol_list INT[]; + isl_list INT[]; + hpl_list INT[]; + hrl_list INT[]; + +BEGIN + + SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id; + SELECT * INTO acp FROM asset.copy WHERE id = acp_id; + SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number; + SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location; + + -- First, gather the baseline proximity of "here" to pickup lib + SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib; + + -- Find any absolute adjustments, and set the baseline prox to that + SELECT adj.* INTO aoupa + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_anscestors_distance(acp.circ_lib) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND absolute_adjustment + ORDER BY + COALESCE(acp_cl.distance,999) + + COALESCE(acn_ol.distance,999) + + COALESCE(acl_ol.distance,999) + + COALESCE(ahr_pl.distance,999) + + COALESCE(ahr_rl.distance,999), + adj.pos + LIMIT 1; + + IF FOUND THEN + baseline_prox := aoupa.prox_adjustment; + END IF; + + -- Now find any relative adjustments, and change the baseline prox based on them + FOR aoupa IN + SELECT adj.* + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_anscestors_distance(acp.circ_lib) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_anscestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_anscestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND NOT absolute_adjustment + LOOP + baseline_prox := baseline_prox + aoupa.prox_adjustment; + END LOOP; + + RETURN baseline_prox; +END; +$f$ LANGUAGE PLPGSQL; + +ALTER TABLE actor.org_unit_proximity_adjustment + ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey + FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code) + DEFERRABLE INITIALLY DEFERRED; + +COMMIT; -- 2.11.0