Notifications: Added 'announce' notification

This one is also configurable, but mainly because I want to avoid
generating several thousands of notifications for a single action...

4 files changed, 27 insertions, 3 deletions

diff --git a/util/sql/all.sql b/util/sql/all.sql
index 5bd314f5..9a9eace0 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -11,7 +11,7 @@ CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p');
 CREATE TYPE edit_rettype      AS (iid integer, cid integer, rev integer);
 CREATE TYPE language          AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh');
 CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit');
+CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
 CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
 CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
 CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
@@ -72,6 +72,7 @@ CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce();
 
 
 -- Sequences used for ID generation of items not in the DB
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 8a27a32c..a58bce9e 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -718,3 +718,23 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+
+-- called on INSERT INTO threads_posts
+CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
+BEGIN
+  -- new thread?
+  IF NEW.num = 1 THEN
+    INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+      SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid
+        FROM threads t
+        JOIN threads_boards tb ON tb.tid = t.id
+        -- get the users who want this announcement
+        JOIN users u ON u.notify_announce
+       WHERE t.id = NEW.tid
+         AND tb.type = 'an' -- announcement board
+         AND NOT t.hidden;
+  END IF;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 91d7f9bf..a9da0cbb 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -276,7 +276,8 @@ CREATE TABLE users (
   c_tags integer NOT NULL DEFAULT 0,
   salt character(9) NOT NULL DEFAULT '',
   ign_votes boolean NOT NULL DEFAULT FALSE,
-  notify_dbedit boolean NOT NULL DEFAULT TRUE
+  notify_dbedit boolean NOT NULL DEFAULT TRUE,
+  notify_announce boolean NOT NULL DEFAULT FALSE
 );
 
 -- vn
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql
index 9184dec9..65571fa9 100644
--- a/util/updates/update_2.11.sql
+++ b/util/updates/update_2.11.sql
@@ -1,6 +1,6 @@
 
 
-CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit');
+CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
 CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
 
 CREATE TABLE notifications (
@@ -28,6 +28,7 @@ INSERT INTO notifications (uid, date, ntype, ltype, iid, subid, c_title, c_byuse
 ALTER TABLE threads_boards DROP COLUMN lastread;
 
 ALTER TABLE users ADD COLUMN notify_dbedit boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN notify_announce boolean NOT NULL DEFAULT false;
 UPDATE users SET notify_dbedit = false WHERE id IN(0,1);
 
 
@@ -115,4 +116,5 @@ CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce();