ulist: Integrate update script into SQL + update notify_listdel()

1 files changed, 31 insertions, 0 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index e644ecc3..b2bc3d04 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -703,6 +703,37 @@ CREATE TABLE traits_parents (
   PRIMARY KEY(trait, parent)
 );
 
+-- ulist_labels
+CREATE TABLE ulist_labels (
+  uid      integer NOT NULL, -- user.id
+  id       integer NOT NULL, -- 0 < builtin < 10 <= custom, ids are reused
+  label    text NOT NULL,
+  private  boolean NOT NULL,
+  PRIMARY KEY(uid, id)
+);
+
+-- ulist_vns
+CREATE TABLE ulist_vns (
+  uid       integer NOT NULL, -- users.id
+  vid       integer NOT NULL, -- vn.id
+  added     timestamptz NOT NULL DEFAULT NOW(),
+  lastmod   timestamptz NOT NULL DEFAULT NOW(), -- updated when anything in this row has changed?
+  vote_date timestamptz, -- Used for "recent votes" - also updated when vote has changed?
+  vote      smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100),
+  started   date,
+  finished  date,
+  notes     text NOT NULL DEFAULT '',
+  PRIMARY KEY(uid, vid)
+);
+
+-- ulist_vns_labels
+CREATE TABLE ulist_vns_labels (
+  uid integer NOT NULL, -- user.id
+  lbl integer NOT NULL,
+  vid integer NOT NULL, -- vn.id
+  PRIMARY KEY(uid, lbl, vid)
+);
+
 -- users
 CREATE TABLE users (
   id         SERIAL NOT NULL PRIMARY KEY, -- [pub]