diff options
Diffstat (limited to 'util/updates/update_2.11.sql')
-rw-r--r-- | util/updates/update_2.11.sql | 33 |
1 files changed, 31 insertions, 2 deletions
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql index d311165c..bf999cdc 100644 --- a/util/updates/update_2.11.sql +++ b/util/updates/update_2.11.sql @@ -1,4 +1,33 @@ + +CREATE TYPE notification_ntype AS ENUM ('pm'); +CREATE TYPE notification_ltype AS ENUM ('t'); + +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL REFERENCES users (id), + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer +); + +-- convert the "unread messages" count into notifications +INSERT INTO notifications (uid, date, ntype, ltype, iid, subid) + SELECT tb.iid, tp.date, 'pm', 't', t.id, tp.num + FROM threads_boards tb + JOIN threads t ON t.id = tb.tid + JOIN threads_posts tp ON tp.tid = t.id AND tp.num = COALESCE(tb.lastread, 1) + WHERE tb.type = 'u' AND NOT t.hidden AND (tb.lastread IS NULL OR t.count <> tb.lastread); + +-- ...and drop the now unused lastread column +ALTER TABLE threads_boards DROP COLUMN lastread; + + + + ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE; ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE; @@ -8,6 +37,8 @@ CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); + CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ BEGIN @@ -50,5 +81,3 @@ UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x; DROP FUNCTION tmp_edit_hidlock(text, integer); - - |