summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-30 16:02:16 +0100
committerYorhel <git@yorhel.nl>2019-12-30 16:19:12 +0100
commit214646f73973a84ae4a91e8b7a3be440546edccf (patch)
tree7726bf2f8b9ee292d98455be50fba71168bd75e5 /util
parent8143e41a59efb8d7220da458e0a94b5b839179b2 (diff)
Minor refactor: Move all trigger functions and declarations to separate file
This keeps the function body and the trigger declaration closer to each other, so it's easier to tell when the function will be called and which cases it needs to handle. If you ignore errors from existing trigger declarations, the triggers.sql script is idempotent and can be used in from scripts.
Diffstat (limited to 'util')
-rwxr-xr-xutil/devdump.pl1
-rw-r--r--util/sql/all.sql1
-rw-r--r--util/sql/func.sql276
-rw-r--r--util/sql/tableattrs.sql68
-rw-r--r--util/sql/triggers.sql331
-rw-r--r--util/updates/update_wip_lists.sql10
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);