From 4e9eff2d2c898a0705dccd9884b6b17683eca86c Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Thu, 19 Feb 2015 21:25:32 +0000 Subject: [PATCH] LP#1410369: fix issue with the order of the table creation statements Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 126 +++++++++++++++--------------- 1 file changed, 63 insertions(+), 63 deletions(-) diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index cced60c91a..af27c3a21b 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -152,69 +152,6 @@ 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 - ); - -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(); - --- limited view to ensure that a library user who somehow --- manages to figure out how to access pcrud cannot change --- the text of messages sent them -CREATE VIEW actor.usr_message_limited -AS SELECT * FROM actor.usr_message; - -CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$ -BEGIN - IF TG_OP = 'UPDATE' THEN - UPDATE actor.usr_message - SET read_date = NEW.read_date, - deleted = NEW.deleted - WHERE id = NEW.id; - RETURN NEW; - END IF; - RETURN NULL; -END; -$$ LANGUAGE PLPGSQL; - -CREATE TRIGGER restrict_usr_message_limited_tgr - INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited - FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited(); - CREATE TABLE actor.usr_setting ( id BIGSERIAL PRIMARY KEY, usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, @@ -802,4 +739,67 @@ CREATE TABLE actor.search_filter_group_entry ( CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query) ); +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 + ); + +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(); + +-- limited view to ensure that a library user who somehow +-- manages to figure out how to access pcrud cannot change +-- the text of messages sent them +CREATE VIEW actor.usr_message_limited +AS SELECT * FROM actor.usr_message; + +CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'UPDATE' THEN + UPDATE actor.usr_message + SET read_date = NEW.read_date, + deleted = NEW.deleted + WHERE id = NEW.id; + RETURN NEW; + END IF; + RETURN NULL; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER restrict_usr_message_limited_tgr + INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited + FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited(); + COMMIT; -- 2.11.0