summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-16 13:06:49 +0100
committerYorhel <git@yorhel.nl>2019-12-16 13:06:49 +0100
commit153d6578e4c1973f08694fe78259417912e3d5f3 (patch)
tree16682188c87e84bbe310fab3b8fd3e4d4d6fa008
parent829eb1f2472936d70a70839e55b16858dbcfb6af (diff)
ulist: Integrate update script into SQL + update notify_listdel()
-rw-r--r--util/sql/func.sql37
-rw-r--r--util/sql/perms.sql11
-rw-r--r--util/sql/schema.sql31
-rw-r--r--util/sql/tableattrs.sql10
-rw-r--r--util/updates/update_wip_lists.sql29
5 files changed, 83 insertions, 35 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index dab2ddbb..f326d9be 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -562,6 +562,37 @@ $$ LANGUAGE plpgsql;
+-- Create ulist labels for new users.
+CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS $$
+BEGIN
+ INSERT INTO ulist_labels (uid, id, label, private)
+ VALUES (NEW.id, 1, 'Playing', false),
+ (NEW.id, 2, 'Finished', false),
+ (NEW.id, 3, 'Stalled', false),
+ (NEW.id, 4, 'Dropped', false),
+ (NEW.id, 5, 'Wishlist', false),
+ (NEW.id, 6, 'Blacklist', false),
+ (NEW.id, 7, 'Voted', false);
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+
+
+-- Set/unset the 'Voted' label when voting.
+CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
+BEGIN
+ IF NEW.vote IS NULL THEN
+ DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
+ ELSE
+ INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
+ END IF;
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+
+
-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
BEGIN NOTIFY anime; RETURN NULL; END;
@@ -707,10 +738,8 @@ CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype
SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
-- look for users who should get this notify
FROM (
- SELECT uid FROM votes WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM wlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
+ UNION SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
+ UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
) u
-- fetch info about this edit
JOIN changes c ON c.id = xedit.chid
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index a038103e..34a6550e 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -61,9 +61,9 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_labels TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_vn_labels TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
-- users table is special; The 'perm', 'passwd' and 'mail' columns are
-- protected and can only be accessed through the user_* functions.
@@ -147,9 +147,14 @@ GRANT SELECT ON threads_posts TO vndb_multi;
GRANT SELECT, UPDATE ON traits TO vndb_multi;
GRANT SELECT ON traits_chars TO vndb_multi; -- traits_chars_calc() is SECURITY DEFINER
GRANT SELECT ON traits_parents TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
+
GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
UPDATE ( c_votes, c_changes, c_tags) ON users TO vndb_multi;
GRANT DELETE ON users TO vndb_multi;
+
GRANT SELECT, UPDATE ON vn TO vndb_multi;
GRANT SELECT ON vn_anime TO vndb_multi;
GRANT SELECT ON vn_hist TO vndb_multi;
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]
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index ed25bc98..13863b5b 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -67,6 +67,13 @@ ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey
ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id);
ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
+ALTER TABLE ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
@@ -189,6 +196,9 @@ CREATE TRIGGER insert_notify AFTER INSERT ON traits
CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
+CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+
CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index b453a406..1bbf700f 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -113,36 +113,9 @@ COMMIT;
\timing
-
-CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS $$
-BEGIN
- INSERT INTO ulist_labels (uid, id, label, private)
- VALUES (NEW.id, 1, 'Playing', false),
- (NEW.id, 2, 'Finished', false),
- (NEW.id, 3, 'Stalled', false),
- (NEW.id, 4, 'Dropped', false),
- (NEW.id, 5, 'Wishlist', false),
- (NEW.id, 6, 'Blacklist', false),
- (NEW.id, 7, 'Voted', false);
- RETURN NULL;
-END
-$$ LANGUAGE plpgsql;
+\i util/sql/func.sql
CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
-
-
-
-CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
-BEGIN
- IF NEW.vote IS NULL THEN
- DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
- ELSE
- INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
- END IF;
- RETURN NULL;
-END
-$$ LANGUAGE plpgsql;
-
CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();