From 153d6578e4c1973f08694fe78259417912e3d5f3 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 16 Dec 2019 13:06:49 +0100 Subject: ulist: Integrate update script into SQL + update notify_listdel() --- util/sql/func.sql | 37 +++++++++++++++++++++++++++++++++---- util/sql/perms.sql | 11 ++++++++--- util/sql/schema.sql | 31 +++++++++++++++++++++++++++++++ util/sql/tableattrs.sql | 10 ++++++++++ util/updates/update_wip_lists.sql | 29 +---------------------------- 5 files changed, 83 insertions(+), 35 deletions(-) (limited to 'util') 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(); -- cgit v1.2.3