RFC-01: Implemented (most) of the SQL part

- Created vnlists table
- Converted rlists.vstat into that table
- Added triggers to make sure that there is always a corresponding row
  in vnlists for every row in rlists.
- Added a check on vnlists for the 'listdel' notify

1 files changed, 12 insertions, 0 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 48367e4c..2ea03f4a 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -340,6 +340,16 @@ CREATE TABLE vn_screenshots (
   PRIMARY KEY(vid, scr)
 );
 
+
+-- vnlists
+CREATE TABLE vnlists (
+  uid integer NOT NULL,
+  vid integer NOT NULL,
+  status smallint NOT NULL DEFAULT 0,
+  added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  PRIMARY KEY(uid, vid)
+);
+
 -- votes
 CREATE TABLE votes (
   vid integer NOT NULL DEFAULT 0,
@@ -405,6 +415,8 @@ ALTER TABLE vn_rev              ADD FOREIGN KEY (vid)       REFERENCES vn
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (scr)       REFERENCES screenshots   (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (rid)       REFERENCES releases      (id);
+ALTER TABLE vnlists             ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE vnlists             ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE votes               ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;