From cca236be3c26cdeb9719b55505c53d4d77bdbf02 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 Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 27 +++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 46 +++++++++++++++++++ .../sql/Pg/upgrade/XXXX.schema.message-center.sql | 52 ++++++++++++++++++++++ 3 files changed, 125 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 273512f990..c1584a3e24 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1228,6 +1228,10 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + @@ -2019,6 +2023,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/upgrade/XXXX.schema.message-center.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql new file mode 100644 index 0000000000..68028e6da4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.message-center.sql @@ -0,0 +1,52 @@ +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(); + +COMMIT; + -- 2.11.0