summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql90
-rw-r--r--util/sql/func.sql1
-rw-r--r--util/sql/schema.sql92
-rw-r--r--util/sql/tableattrs.sql87
-rw-r--r--util/sql/triggers.sql81
5 files changed, 172 insertions, 179 deletions
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 91f58267..496479a2 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -33,101 +33,19 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
\i util/sql/func.sql
+-- constraints & indices
--- triggers
+\i util/sql/tableattrs.sql
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-
-CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes 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 vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
- WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
- OR OLD.latest IS DISTINCT FROM NEW.latest
- OR 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 DISTINCT FROM NEW.rgraph
- OR OLD.latest IS DISTINCT FROM NEW.latest
- ) 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 release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
- WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
- EXECUTE PROCEDURE release_vncache_update();
-
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-
-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
- OR NEW.latest IS DISTINCT FROM OLD.latest
- ) EXECUTE PROCEDURE vn_vnsearch_notify();
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
- WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
- EXECUTE PROCEDURE vn_vnsearch_notify();
-
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists 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();
+-- triggers
+\i util/sql/triggers.sql
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
CREATE SEQUENCE charimg_seq;
--- Discussion board search index (can't put this in schema.sql because it refers to func.sql)
-CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
-
-
-- Rows that are assumed to be available
INSERT INTO users (id, username, mail, perm) VALUES (0, 'deleted', 'del@vndb.org', 0);
INSERT INTO users (username, mail, perm) VALUES ('multi', 'multi@vndb.org', 0);
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 6f675479..51711f77 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -1,4 +1,3 @@
-
-- A small note on the function naming scheme:
-- edit_* -> revision insertion abstraction functions
-- *_notify -> functions issuing a PgSQL NOTIFY statement
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 3fba4342..59240ea9 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -1,5 +1,3 @@
-
-
-- affiliate_links
CREATE TABLE affiliate_links (
id SERIAL PRIMARY KEY,
@@ -530,93 +528,3 @@ CREATE TABLE wlists (
added timestamptz NOT NULL DEFAULT NOW(),
PRIMARY KEY(uid, vid)
);
-
-
-
-ALTER TABLE affiliate_links ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE chars ADD FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE chars_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE chars_rev ADD FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE chars_rev ADD FOREIGN KEY (main) REFERENCES chars (id);
-ALTER TABLE chars_traits ADD FOREIGN KEY (cid) REFERENCES chars_rev (id);
-ALTER TABLE chars_traits ADD FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE chars_vns ADD FOREIGN KEY (cid) REFERENCES chars_rev (id);
-ALTER TABLE chars_vns ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE chars_vns ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
-ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id);
-ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE staff ADD FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE staff_alias ADD FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE staff_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE staff_rev ADD FOREIGN KEY (sid) REFERENCES staff (id);
-ALTER TABLE staff_rev ADD FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid);
-ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE traits ADD FOREIGN KEY (addedby) REFERENCES users (id);
-ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
-ALTER TABLE traits_parents ADD FOREIGN KEY (trait) REFERENCES traits (id);
-ALTER TABLE traits_parents ADD FOREIGN KEY (parent) REFERENCES traits (id);
-ALTER TABLE traits_chars ADD FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE traits_chars ADD FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE users_prefs ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
-ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id);
-ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE vn_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_staff ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
-
-
-CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;
-CREATE INDEX releases_producers_rid ON releases_producers (rid);
-CREATE INDEX releases_vn_vid ON releases_vn (vid);
-CREATE INDEX tags_vn_date ON tags_vn (date);
-CREATE INDEX tags_vn_vid ON tags_vn (vid);
-CREATE INDEX vn_staff_vid ON vn_staff (vid);
-CREATE INDEX vn_staff_aid ON vn_staff (aid);
-CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0));
-
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
new file mode 100644
index 00000000..1e812f01
--- /dev/null
+++ b/util/sql/tableattrs.sql
@@ -0,0 +1,87 @@
+ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE chars ADD CONSTRAINT chars_latest_fkey FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
+ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id);
+ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE producers ADD CONSTRAINT producers_latest_fkey FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid1_fkey FOREIGN KEY (pid1) REFERENCES producers_rev (id);
+ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid2_fkey FOREIGN KEY (pid2) REFERENCES producers (id);
+ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases ADD CONSTRAINT releases_latest_fkey FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_media ADD CONSTRAINT releases_media_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE staff ADD CONSTRAINT staff_latest_fkey FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_rid_fkey FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_sid_fkey FOREIGN KEY (sid) REFERENCES staff (id);
+ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_aid_fkey FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid);
+ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id);
+ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
+ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id);
+ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
+ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE vn ADD CONSTRAINT vn_latest_fkey FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
+ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid1_fkey FOREIGN KEY (vid1) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid2_fkey FOREIGN KEY (vid2) REFERENCES vn (id);
+ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+
+
+CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;
+CREATE INDEX releases_producers_rid ON releases_producers (rid);
+CREATE INDEX releases_vn_vid ON releases_vn (vid);
+CREATE INDEX tags_vn_date ON tags_vn (date);
+CREATE INDEX tags_vn_vid ON tags_vn (vid);
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
+CREATE INDEX vn_staff_vid ON vn_staff (vid);
+CREATE INDEX vn_staff_aid ON vn_staff (aid);
+CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0));
diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql
new file mode 100644
index 00000000..6988a62a
--- /dev/null
+++ b/util/sql/triggers.sql
@@ -0,0 +1,81 @@
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+
+CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes 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 vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
+ WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
+ OR OLD.latest IS DISTINCT FROM NEW.latest
+ OR 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 DISTINCT FROM NEW.rgraph
+ OR OLD.latest IS DISTINCT FROM NEW.latest
+ ) 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 release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
+ WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
+ EXECUTE PROCEDURE release_vncache_update();
+
+CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+
+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
+ OR NEW.latest IS DISTINCT FROM OLD.latest
+ ) EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
+ WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
+ EXECUTE PROCEDURE vn_vnsearch_notify();
+
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists 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();