summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-11 14:03:50 +0200
committerYorhel <git@yorhel.nl>2015-10-12 19:35:18 +0200
commit1a6dd0160a75bc83390912dfb2635cd5664ce9ee (patch)
treea8ed2161296205b2f9c8cbee27b11554e7c60a48 /util
parent15382265b39f045fc5f20e9e676b24941da6bd63 (diff)
SQL: Split constraints/indices/triggers in new file + use idempotent SQL
Turning the foreign key references into idempotent statements required adding the name for each reference in the query. I used the names of the production database, but since the names are autogenerated at creation time, it's possible that they have other names if the database has been created slightly differently. Using explicit names for everything and having idempotent SQL statements is rather useful when making nontrivial modifications to the database schema. Which is something I consider doing.
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();