From 8d06574b4110fdb00fd27e283ebc39937adad09e Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 8 Dec 2011 16:58:14 -0500 Subject: [PATCH] address alert : DB; match proc and upgrade script Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 4 +- .../sql/Pg/upgrade/XXXX.schema.address-alert.sql | 58 ++++++++++++++++++++++ 2 files changed, 60 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index cdcee255ad..0148bb60f7 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -610,13 +610,13 @@ CREATE TABLE actor.address_alert ( owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, match_all BOOL NOT NULL DEFAULT TRUE, alert_message TEXT NOT NULL, - street1 TEXT, + street1 TEXT, street2 TEXT, city TEXT, county TEXT, state TEXT, country TEXT, - post_code TEXT, + post_code TEXT ); COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql new file mode 100644 index 0000000000..6085b3f517 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.address-alert.sql @@ -0,0 +1,58 @@ +-- Evergreen DB patch XXXX.schema.address-alert.sql +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE TABLE actor.address_alert ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + match_all BOOL NOT NULL DEFAULT TRUE, + alert_message TEXT NOT NULL, + street1 TEXT, + street2 TEXT, + city TEXT, + county TEXT, + state TEXT, + country TEXT, + post_code TEXT +); + +CREATE OR REPLACE FUNCTION actor.address_alert_matches + (org_unit INT, street1 TEXT, street2 TEXT, city TEXT, county TEXT, state TEXT, country TEXT, post_code TEXT) + RETURNS SETOF actor.address_alert AS $$ +SELECT * +FROM actor.address_alert +WHERE + owner IN (SELECT id FROM actor.org_unit_ancestors($1)) AND ( + ( + match_all + AND LOWER(COALESCE($2, '')) ~ LOWER(COALESCE(street1, '.*')) + AND LOWER(COALESCE($3, '')) ~ LOWER(COALESCE(street2, '.*')) + AND LOWER(COALESCE($4, '')) ~ LOWER(COALESCE(city, '.*')) + AND LOWER(COALESCE($5, '')) ~ LOWER(COALESCE(county, '.*')) + AND LOWER(COALESCE($6, '')) ~ LOWER(COALESCE(state, '.*')) + AND LOWER(COALESCE($7, '')) ~ LOWER(COALESCE(country, '.*')) + AND LOWER(COALESCE($8, '')) ~ LOWER(COALESCE(post_code, '.*')) + ) OR ( + NOT match_all AND ( + LOWER($2) ~ LOWER(street1) + OR LOWER($3) ~ LOWER(street2) + OR LOWER($4) ~ LOWER(city) + OR LOWER($5) ~ LOWER(county) + OR LOWER($6) ~ LOWER(state) + OR LOWER($7) ~ LOWER(country) + OR LOWER($8) ~ LOWER(post_code) + ) + ) + ) + ORDER BY actor.org_unit_proximity(owner, $1) +$$ LANGUAGE SQL; + +COMMIT; + +/* UNDO +DROP FUNCTION actor.address_alert_matches(INT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT); +DROP TABLE actor.address_alert; +*/ -- 2.11.0