diff options
-rwxr-xr-x | util/devdump.pl | 1 | ||||
-rw-r--r-- | util/sql/all.sql | 1 | ||||
-rw-r--r-- | util/sql/func.sql | 276 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 68 | ||||
-rw-r--r-- | util/sql/triggers.sql | 331 | ||||
-rw-r--r-- | util/updates/update_wip_lists.sql | 10 |
6 files changed, 351 insertions, 336 deletions
diff --git a/util/devdump.pl b/util/devdump.pl index e3d198c2..9e724619 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -157,6 +157,7 @@ sub copy_entry { copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases; print "\\i util/sql/tableattrs.sql\n"; + print "\\i util/sql/triggers.sql\n"; # Update some caches print "SELECT tag_vn_calc(NULL);\n"; diff --git a/util/sql/all.sql b/util/sql/all.sql index 8bf3ae63..1e01dd3f 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -6,5 +6,6 @@ \i util/sql/func.sql \i util/sql/editfunc.sql \i util/sql/tableattrs.sql +\i util/sql/triggers.sql \set ON_ERROR_STOP 0 \i util/sql/perms.sql diff --git a/util/sql/func.sql b/util/sql/func.sql index b9fa5ade..5423ab9b 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -266,6 +266,20 @@ END; $$ LANGUAGE plpgsql; +-- Create ulist labels for new users. +CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$ + INSERT INTO ulist_labels (uid, id, label, private) + VALUES ($1, 1, 'Playing', false), + ($1, 2, 'Finished', false), + ($1, 3, 'Stalled', false), + ($1, 4, 'Dropped', false), + ($1, 5, 'Wishlist', false), + ($1, 6, 'Blacklist', false), + ($1, 7, 'Voted', false) + ON CONFLICT (uid, id) DO NOTHING; +$$ LANGUAGE SQL; + + ---------------------------------------------------------- @@ -458,257 +472,12 @@ $$ LANGUAGE plpgsql; ----------------------------------------------------------- --- trigger functions -- ----------------------------------------------------------- - - --- keep the c_tags and c_changes columns in the users table up to date -CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$ -BEGIN - IF TG_TABLE_NAME = 'changes' THEN - IF TG_OP = 'INSERT' THEN - UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; - ELSE - UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; - END IF; - ELSIF TG_TABLE_NAME = 'tags_vn' THEN - IF TG_OP = 'INSERT' THEN - UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; - ELSE - UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; - END IF; - END IF; - RETURN NULL; -END; -$$ LANGUAGE 'plpgsql'; - - - --- the stats_cache table -CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ -DECLARE - unhidden boolean; - hidden boolean; -BEGIN - IF TG_OP = 'INSERT' THEN - IF TG_TABLE_NAME = 'users' THEN - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSE - IF TG_TABLE_NAME = 'threads_posts' THEN - IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND threads.hidden = FALSE) THEN - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - END IF; - ELSE - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - END IF; - END IF; - - ELSIF TG_OP = 'UPDATE' THEN - IF TG_TABLE_NAME IN('tags', 'traits') THEN - unhidden := OLD.state <> 2 AND NEW.state = 2; - hidden := OLD.state = 2 AND NEW.state <> 2; - ELSE - unhidden := OLD.hidden AND NOT NEW.hidden; - hidden := NOT unhidden; - END IF; - IF unhidden THEN - IF TG_TABLE_NAME = 'threads' THEN - UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; - END IF; - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSIF hidden THEN - IF TG_TABLE_NAME = 'threads' THEN - UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; - END IF; - UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; - END IF; - - ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN - UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; - END IF; - RETURN NULL; -END; -$$ LANGUAGE 'plpgsql'; - - - --- insert rows into anime for new vn_anime.aid items --- (this is a BEFORE trigger) -CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ -BEGIN - IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN - INSERT INTO anime (id) VALUES (NEW.aid); - END IF; - RETURN NEW; -END; -$$ LANGUAGE plpgsql; - - - --- insert rows into wikidata for new l_wikidata items --- (this is a BEFORE trigger) -CREATE OR REPLACE FUNCTION wikidata_insert() RETURNS trigger AS $$ -BEGIN - INSERT INTO wikidata (id) VALUES (NEW.l_wikidata) ON CONFLICT (id) DO NOTHING; - RETURN NEW; -END; -$$ LANGUAGE plpgsql; - - - --- For each row in rlists, there should be at least one corresponding row in --- ulist_vns for each VN linked to that release. --- 1. When a row is deleted from ulist_vns, also remove all rows from rlists --- with that VN linked. --- 2. When a row is inserted to rlists and there is not yet a corresponding row --- in ulist_vns, add a row to ulist_vns for each vn linked to the release. --- 3. When a release is edited to add another VN, add those VNs to ulist_vns --- for everyone who has the release in rlists. --- This is done in edit_committed(). --- #. When a release is edited to remove a VN, that VN kinda should also be --- removed from ulist_vns, but only if that ulist_vns entry was --- automatically added as part of the rlists entry and the user has not --- changed anything in the ulist_vns row. This isn't currently done. -CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$ -BEGIN - -- 1. - IF TG_TABLE_NAME = 'ulist_vns' THEN - DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT id FROM releases_vn WHERE vid = OLD.vid); - -- 2. - ELSE - INSERT INTO ulist_vns (uid, vid) - SELECT NEW.uid, rv.vid FROM releases_vn rv WHERE rv.id = NEW.rid - ON CONFLICT (uid, vid) DO NOTHING; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - --- Create ulist labels for new users. -CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$ - INSERT INTO ulist_labels (uid, id, label, private) - VALUES ($1, 1, 'Playing', false), - ($1, 2, 'Finished', false), - ($1, 3, 'Stalled', false), - ($1, 4, 'Dropped', false), - ($1, 5, 'Wishlist', false), - ($1, 6, 'Blacklist', false), - ($1, 7, 'Voted', false) - ON CONFLICT (uid, id) DO NOTHING; -$$ LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS 'BEGIN PERFORM ulist_labels_create(NEW.id); 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; -$$ LANGUAGE plpgsql; - - - --- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations --- 2. Set rgraph to NULL when c_languages or c_released has changed -CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$ -BEGIN - IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN - -- 1. - IF NEW.rgraph IS NULL THEN - NOTIFY relgraph; - -- 2. - ELSE - UPDATE vn SET rgraph = NULL WHERE id = NEW.id; - END IF; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - --- Send a notify when producers.rgraph is set to NULL, and there are related entries in producers_relations -CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$ -BEGIN - IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN - NOTIFY relgraph; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - - --- NOTIFY on insert into changes/posts/tags/trait -CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ -BEGIN - IF TG_TABLE_NAME = 'changes' THEN - NOTIFY newrevision; - ELSIF TG_TABLE_NAME = 'threads_posts' THEN - NOTIFY newpost; - ELSIF TG_TABLE_NAME = 'tags' THEN - NOTIFY newtag; - ELSIF TG_TABLE_NAME = 'traits' THEN - NOTIFY newtrait; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - - --- Send a vnsearch notification when the c_search column is set to NULL. -CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$ - BEGIN NOTIFY vnsearch; RETURN NULL; END; -$$ LANGUAGE plpgsql; - - - ---------------------------------------------------------- -- notification functions -- ---------------------------------------------------------- --- called on INSERT INTO threads_posts -CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ -BEGIN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid - FROM threads t - JOIN threads_boards tb ON tb.tid = t.id - WHERE t.id = NEW.tid - AND tb.type = 'u' - AND tb.iid <> NEW.uid -- don't notify when posting in your own board - AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet - SELECT 1 - FROM notifications n - WHERE n.uid = tb.iid - AND n.ntype = 'pm' - AND n.iid = t.id - AND n.read IS NULL - ); - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - - -- called when an entry has been deleted CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) @@ -775,23 +544,6 @@ CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype $$ LANGUAGE sql; --- called on INSERT INTO threads_posts when (NEW.num = 1) -CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ -BEGIN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', u.id, t.id, 1, t.title, NEW.uid - FROM threads t - JOIN threads_boards tb ON tb.tid = t.id - -- get the users who want this announcement - JOIN users u ON u.notify_announce - WHERE t.id = NEW.tid - AND tb.type = 'an' -- announcement board - AND NOT t.hidden; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - ---------------------------------------------------------- diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index c9b598b1..781e6aad 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -128,71 +128,3 @@ CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0)); CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes; - - - --- Triggers - -CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); -CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); - -CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); - -CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); -CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid(); - -CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); - -CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); -CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); - -CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW - WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) - OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages)) - ) EXECUTE PROCEDURE vn_relgraph_notify(); - -CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify(); - -CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); - -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 ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); -CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql new file mode 100644 index 00000000..a8ef3bbc --- /dev/null +++ b/util/sql/triggers.sql @@ -0,0 +1,331 @@ +-- keep the c_tags and c_changes columns in the users table up to date + +CREATE OR REPLACE FUNCTION update_users_cache() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'changes' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; + ELSE + UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; + END IF; + ELSIF TG_TABLE_NAME = 'tags_vn' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + +CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); +CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); + + + + +-- the stats_cache table + +CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ +DECLARE + unhidden boolean; + hidden boolean; +BEGIN + IF TG_OP = 'INSERT' THEN + IF TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSE + IF TG_TABLE_NAME = 'threads_posts' THEN + IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND threads.hidden = FALSE) THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + ELSE + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + END IF; + + ELSIF TG_OP = 'UPDATE' THEN + IF TG_TABLE_NAME IN('tags', 'traits') THEN + unhidden := OLD.state <> 2 AND NEW.state = 2; + hidden := OLD.state = 2 AND NEW.state <> 2; + ELSE + unhidden := OLD.hidden AND NOT NEW.hidden; + hidden := NOT unhidden; + END IF; + IF unhidden THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF hidden THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + + ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + +CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); + + + + +-- insert rows into anime for new vn_anime.aid items + +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + INSERT INTO anime (id) VALUES (NEW.aid) ON CONFLICT (id) DO NOTHING; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); +CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid(); + + + + +-- 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; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); + + + + +-- insert rows into wikidata for new l_wikidata items + +CREATE OR REPLACE FUNCTION wikidata_insert() RETURNS trigger AS $$ +BEGIN + INSERT INTO wikidata (id) VALUES (NEW.l_wikidata) ON CONFLICT (id) DO NOTHING; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); + + + + +-- For each row in rlists, there should be at least one corresponding row in +-- ulist_vns for each VN linked to that release. +-- 1. When a row is deleted from ulist_vns, also remove all rows from rlists +-- with that VN linked. +-- 2. When a row is inserted to rlists and there is not yet a corresponding row +-- in ulist_vns, add a row to ulist_vns for each vn linked to the release. +-- 3. When a release is edited to add another VN, add those VNs to ulist_vns +-- for everyone who has the release in rlists. +-- This is done in edit_committed(). +-- #. When a release is edited to remove a VN, that VN kinda should also be +-- removed from ulist_vns, but only if that ulist_vns entry was +-- automatically added as part of the rlists entry and the user has not +-- changed anything in the ulist_vns row. This isn't currently done. +CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$ +BEGIN + -- 1. + IF TG_TABLE_NAME = 'ulist_vns' THEN + DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT id FROM releases_vn WHERE vid = OLD.vid); + -- 2. + ELSE + INSERT INTO ulist_vns (uid, vid) + SELECT NEW.uid, rv.vid FROM releases_vn rv WHERE rv.id = NEW.rid + ON CONFLICT (uid, vid) DO NOTHING; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE CONSTRAINT TRIGGER update_ulist_vns_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); +CREATE CONSTRAINT TRIGGER update_rlist_ulist_vns AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); + + + + +-- Create ulist_label rows when a new user is added + +CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS 'BEGIN PERFORM ulist_labels_create(NEW.id); RETURN NULL; END' LANGUAGE plpgsql; + +CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create(); + + + + +-- 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; + +CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label(); + + + + +-- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations +-- 2. Set rgraph to NULL when c_languages or c_released has changed + +CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$ +BEGIN + IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN + -- 1. + IF NEW.rgraph IS NULL THEN + NOTIFY relgraph; + -- 2. + ELSE + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW + WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) + OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages)) + ) EXECUTE PROCEDURE vn_relgraph_notify(); + + + + +-- Send a notify when producers.rgraph is set to NULL and there are related entries in producers_relations + +CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$ +BEGIN + IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN + NOTIFY relgraph; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify(); + + + + +-- NOTIFY on insert into changes/posts/tags/trait + +CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'changes' THEN + NOTIFY newrevision; + ELSIF TG_TABLE_NAME = 'threads_posts' THEN + NOTIFY newpost; + ELSIF TG_TABLE_NAME = 'tags' THEN + NOTIFY newtag; + ELSIF TG_TABLE_NAME = 'traits' THEN + NOTIFY newtrait; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); + + + + +-- Send a vnsearch notification when the c_search column is set to NULL. + +CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS 'BEGIN NOTIFY vnsearch; RETURN NULL; END;' LANGUAGE plpgsql; + +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(); + + + + +-- Add a notification when someone posts in someone's board. + +CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ +BEGIN + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + WHERE t.id = NEW.tid + AND tb.type = 'u' + AND tb.iid <> NEW.uid -- don't notify when posting in your own board + AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet + SELECT 1 + FROM notifications n + WHERE n.uid = tb.iid + AND n.ntype = 'pm' + AND n.iid = t.id + AND n.read IS NULL + ); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); + + + + +-- Add a notification when a thread is created in /t/an + +CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ +BEGIN + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'announce', 't', u.id, t.id, 1, t.title, NEW.uid + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + -- get the users who want this announcement + JOIN users u ON u.notify_announce + WHERE t.id = NEW.tid + AND tb.type = 'an' -- announcement board + AND NOT t.hidden; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql index 0ff7a452..7d8c4b82 100644 --- a/util/updates/update_wip_lists.sql +++ b/util/updates/update_wip_lists.sql @@ -118,14 +118,12 @@ DROP FUNCTION update_vnpopularity(); ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0; ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0; -\i util/sql/func.sql -\i util/sql/perms.sql - DROP TRIGGER users_votes_update ON votes; +DROP TRIGGER update_vnlist_rlist ON rlists; -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 CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); +\i util/sql/func.sql +\i util/sql/triggers.sql +\i util/sql/perms.sql \timing SELECT update_users_ulist_stats(NULL); |