summaryrefslogtreecommitdiff
path: root/util/updates/update_2.11.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.11.sql')
-rw-r--r--util/updates/update_2.11.sql33
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);
-
-