3 SELECT evergreen.upgrade_deps_block_check('0910', :eg_version);
5 CREATE TABLE actor.usr_message (
7 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
10 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
11 deleted BOOL NOT NULL DEFAULT FALSE,
12 read_date TIMESTAMP WITH TIME ZONE,
13 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
15 CREATE INDEX aum_usr ON actor.usr_message (usr);
17 CREATE RULE protect_usr_message_delete AS
18 ON DELETE TO actor.usr_message DO INSTEAD (
19 UPDATE actor.usr_message
21 WHERE OLD.id = actor.usr_message.id
24 ALTER TABLE action_trigger.event_definition
25 ADD COLUMN message_template TEXT,
26 ADD COLUMN message_usr_path TEXT,
27 ADD COLUMN message_library_path TEXT,
28 ADD COLUMN message_title TEXT;
30 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
33 IF TG_OP = 'UPDATE' THEN
34 IF OLD.pub = TRUE THEN
39 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
40 VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator));
47 CREATE TRIGGER convert_usr_note_to_message_tgr
48 AFTER INSERT OR UPDATE ON actor.usr_note
49 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
51 CREATE VIEW actor.usr_message_limited
52 AS SELECT * FROM actor.usr_message;
54 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
56 IF TG_OP = 'UPDATE' THEN
57 UPDATE actor.usr_message
58 SET read_date = NEW.read_date,
67 CREATE TRIGGER restrict_usr_message_limited_tgr
68 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
69 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
71 -- and copy over existing public user notes as (read) patron messages
72 INSERT INTO actor.usr_message (usr, title, message, sending_lib, create_date, read_date)
73 SELECT aun.usr, title, value, home_ou, aun.create_date, NOW()
74 FROM actor.usr_note aun
75 JOIN actor.usr au ON (au.id = aun.usr)