From 0e19de7f0444458a61569fa060b438c4210d624e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 16 Feb 2015 12:54:40 -0500 Subject: [PATCH] LP#1410369: Schema and supporting IDL entries This commit adds the table actor.usr_message, a trigger to populate actor.usr_message as rows get adding to actor.usr_note (or get updated to make non-public notes become public), and new columns in action.event_definition for specifying how to generate user messages from A/T events. It also copies over existing public user notes as new, "read" user messages, as the current display of the old user notes will be removed from TPac. Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- Open-ILS/examples/fm_IDL.xml | 27 +++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 46 +++++++++++++++ .../src/sql/Pg/400.schema.action_trigger.sql | 6 ++ .../Pg/upgrade/XXXX.schema.message-center.sql | 59 +++++++++++++++++++ 4 files changed, 138 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 41177320a0..685dd51ad7 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1230,6 +1230,10 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + @@ -2021,6 +2025,29 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 4c6d156996..398736acde 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -152,6 +152,52 @@ CREATE TABLE actor.usr_note ( CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr); CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator ); +CREATE TABLE actor.usr_message ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + title TEXT, + message TEXT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + deleted BOOL NOT NULL DEFAULT FALSE, + read_date TIMESTAMP WITH TIME ZONE, + sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED +); +CREATE INDEX aum_usr ON actor.usr_message (usr); + +CREATE RULE protect_usr_message_delete AS + ON DELETE TO actor.usr_message DO INSTEAD ( + UPDATE actor.usr_message + SET deleted = TRUE + WHERE OLD.id = actor.usr_message.id + ); + +ALTER TALBE action_trigger.event_definition + ADD COLUMN message_template TEXT, + ADD COLUMN message_usr_path TEXT, + ADD COLUMN message_library_path TEXT, + ADD COLUMN message_title TEXT; + +CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$ +BEGIN + IF NEW.pub THEN + IF TG_OP = 'UPDATE' THEN + IF OLD.pub = TRUE THEN + RETURN NEW; + END IF; + END IF; + + INSERT INTO actor.usr_message (usr, title, message, sending_lib) + VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator)); + END IF; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER actor.convert_usr_note_to_message_tgr + AFTER INSERT OR UPDATE ON actor.usr_note + FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message(); + CREATE TABLE actor.usr_setting ( id BIGSERIAL PRIMARY KEY, usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, diff --git a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql index 396cd7d210..81fce60d45 100644 --- a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql +++ b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql @@ -190,6 +190,12 @@ CREATE TABLE action_trigger.event_definition ( group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time. template TEXT, -- the TT block. will have an 'environment' hash (or array of hashes, grouped events) built up by validator and collector(s), which can be modified. granularity TEXT, -- could specify a batch which is the only time these events should actually run + + message_template TEXT, + message_usr_path TEXT, + message_library_path TEXT, + message_title TEXT, + CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field), CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name) ); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql new file mode 100644 index 0000000000..3188441b57 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql @@ -0,0 +1,59 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE TABLE actor.usr_message ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + title TEXT, + message TEXT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + deleted BOOL NOT NULL DEFAULT FALSE, + read_date TIMESTAMP WITH TIME ZONE, + sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED +); +CREATE INDEX aum_usr ON actor.usr_message (usr); + +CREATE RULE protect_usr_message_delete AS + ON DELETE TO actor.usr_message DO INSTEAD ( + UPDATE actor.usr_message + SET deleted = TRUE + WHERE OLD.id = actor.usr_message.id + ); + +ALTER TABLE action_trigger.event_definition + ADD COLUMN message_template TEXT, + ADD COLUMN message_usr_path TEXT, + ADD COLUMN message_library_path TEXT, + ADD COLUMN message_title TEXT; + +CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$ +BEGIN + IF NEW.pub THEN + IF TG_OP = 'UPDATE' THEN + IF OLD.pub = TRUE THEN + RETURN NEW; + END IF; + END IF; + + INSERT INTO actor.usr_message (usr, title, message, sending_lib) + VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator)); + END IF; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER convert_usr_note_to_message_tgr + AFTER INSERT OR UPDATE ON actor.usr_note + FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message(); + +-- and copy over existing public user notes as (read) patron messages +INSERT INTO actor.usr_message (usr, title, message, sending_lib, create_date, read_date) +SELECT aun.usr, title, value, home_ou, aun.create_date, NOW() +FROM actor.usr_note aun +JOIN actor.usr au ON (au.id = aun.usr) +WHERE aun.pub; + +COMMIT; + -- 2.43.2