summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-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);