notifications: Add backend for subscriptions and new notification types

+ (un)subscription for new posts/edits on individual threads, reviews
  and DB entries.
+ Subscribing to VNs to get notifications for new reviews.
+ The users.notify_posts setting now also includes comments on reviews
  the user has commented on.

While this is all very nice and such, it's just the data model and
backend. The hard part is the user interface.

1 files changed, 15 insertions, 0 deletions

diff --git a/util/updates/wip-notifications.sql b/util/updates/wip-notifications.sql
index 31dababa..1a817b33 100644
--- a/util/updates/wip-notifications.sql
+++ b/util/updates/wip-notifications.sql
@@ -13,5 +13,20 @@ ALTER TABLE notifications ALTER COLUMN ntype TYPE notification_ntype[] USING ARR
 ALTER TABLE notifications DROP COLUMN c_title;
 ALTER TABLE notifications DROP COLUMN c_byuser;
 
+-- Subscriptions
+ALTER TYPE notification_ntype ADD VALUE 'subpost' AFTER 'comment';
+ALTER TYPE notification_ntype ADD VALUE 'subedit' AFTER 'subpost';
+ALTER TYPE notification_ntype ADD VALUE 'subreview' AFTER 'subedit';
+
+CREATE TABLE notification_subs (
+  uid         integer NOT NULL,
+  iid         vndbid NOT NULL,
+  subnum      boolean,
+  subreview   boolean NOT NULL DEFAULT false,
+  PRIMARY KEY(iid,uid)
+);
+ALTER TABLE notification_subs        ADD CONSTRAINT notification_subs_uid_fkey         FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+
 \i sql/func.sql
 \i sql/triggers.sql
+\i sql/perms.sql