diff options
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/schema.sql | 511 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 177 |
2 files changed, 453 insertions, 235 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 59240ea9..b43079dc 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -1,3 +1,38 @@ +-- Convention for database items with version control: +-- +-- CREATE TABLE items ( +-- id SERIAL PRIMARY KEY, +-- locked boolean NOT NULL DEFAULT FALSE, +-- hidden boolean NOT NULL DEFAULT FALSE, +-- -- item-specific columns here +-- ); +-- CREATE TABLE items_hist ( -- History of the 'items' table +-- chid integer NOT NULL, -- references changes.id +-- -- item-specific columns here +-- ); +-- +-- The history of the 'locked' and 'hidden' flags is recorded in the changes +-- table. It's possible for 'items' to have more item-specific columns than +-- 'items_hist'. Some columns are caches or otherwise autogenerated, and do not +-- need to be versioned. +-- +-- item-related tables work roughly the same: +-- +-- CREATE TABLE items_field ( +-- id integer, -- references items.id +-- -- field-specific columns here +-- ); +-- CREATE TABLE items_field_hist ( -- History of the 'items_field' table +-- chid integer, -- references changes.id +-- -- field-specific columns here +-- ); +-- +-- The changes and *_hist tables contain all the data. In a sense, the other +-- tables related to the item are just a cache/view into the latest versions. +-- All modifications to the item tables has to go through the edit_* functions +-- in func.sql, these are also responsible for keeping things synchronized. + + -- affiliate_links CREATE TABLE affiliate_links ( id SERIAL PRIMARY KEY, @@ -26,29 +61,44 @@ CREATE TABLE anime ( -- changes CREATE TABLE changes ( - id SERIAL NOT NULL PRIMARY KEY, - type dbentry_type NOT NULL, - rev integer NOT NULL DEFAULT 1, - added timestamptz NOT NULL DEFAULT NOW(), - requester integer NOT NULL DEFAULT 0, - ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '', - ihid boolean NOT NULL DEFAULT FALSE, - ilock boolean NOT NULL DEFAULT FALSE + id SERIAL PRIMARY KEY, + type dbentry_type NOT NULL, + itemid integer NOT NULL, + rev integer NOT NULL DEFAULT 1, + added timestamptz NOT NULL DEFAULT NOW(), + requester integer NOT NULL DEFAULT 0, + ip inet NOT NULL DEFAULT '0.0.0.0', + comments text NOT NULL DEFAULT '', + ihid boolean NOT NULL DEFAULT FALSE, + ilock boolean NOT NULL DEFAULT FALSE ); -- chars CREATE TABLE chars ( - id SERIAL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + name varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + gender gender NOT NULL DEFAULT 'unknown', + s_bust smallint NOT NULL DEFAULT 0, + s_waist smallint NOT NULL DEFAULT 0, + s_hip smallint NOT NULL DEFAULT 0, + b_month smallint NOT NULL DEFAULT 0, + b_day smallint NOT NULL DEFAULT 0, + height smallint NOT NULL DEFAULT 0, + weight smallint NOT NULL DEFAULT 0, + bloodt blood_type NOT NULL DEFAULT 'unknown', + main integer, -- chars.id + main_spoil smallint NOT NULL DEFAULT 0 ); --- chars_rev -CREATE TABLE chars_rev ( - id integer NOT NULL PRIMARY KEY, - cid integer NOT NULL, +-- chars_hist +CREATE TABLE chars_hist ( + chid integer NOT NULL PRIMARY KEY, name varchar(250) NOT NULL DEFAULT '', original varchar(250) NOT NULL DEFAULT '', alias varchar(500) NOT NULL DEFAULT '', @@ -63,25 +113,42 @@ CREATE TABLE chars_rev ( height smallint NOT NULL DEFAULT 0, weight smallint NOT NULL DEFAULT 0, bloodt blood_type NOT NULL DEFAULT 'unknown', - main integer, + main integer, -- chars.id main_spoil smallint NOT NULL DEFAULT 0 ); -- chars_traits CREATE TABLE chars_traits ( - cid integer NOT NULL, - tid integer NOT NULL, - spoil smallint NOT NULL DEFAULT 0, - PRIMARY KEY(cid, tid) + id integer NOT NULL, + tid integer NOT NULL, -- traits.id + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(id, tid) +); + +-- chars_traits_hist +CREATE TABLE chars_traits_hist ( + chid integer NOT NULL, + tid integer NOT NULL, -- traits.id + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(chid, tid) ); -- chars_vns CREATE TABLE chars_vns ( - cid integer NOT NULL, - vid integer NOT NULL, - rid integer NULL, - spoil smallint NOT NULL DEFAULT 0, - role char_role NOT NULL DEFAULT 'main' + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + rid integer NULL, -- releases.id + spoil smallint NOT NULL DEFAULT 0, + role char_role NOT NULL DEFAULT 'main' +); + +-- chars_vns_hist +CREATE TABLE chars_vns_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + rid integer NULL, -- releases.id + spoil smallint NOT NULL DEFAULT 0, + role char_role NOT NULL DEFAULT 'main' ); -- login_throttle @@ -106,33 +173,47 @@ CREATE TABLE notifications ( -- producers CREATE TABLE producers ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE, - rgraph integer + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + type producer_type NOT NULL DEFAULT 'co', + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + website varchar(250) NOT NULL DEFAULT '', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150), + rgraph integer -- relgraphs.id +); + +-- producers_hist +CREATE TABLE producers_hist ( + chid integer NOT NULL PRIMARY KEY, + type producer_type NOT NULL DEFAULT 'co', + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + website varchar(250) NOT NULL DEFAULT '', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150) ); -- producers_relations CREATE TABLE producers_relations ( - pid1 integer NOT NULL, - pid2 integer NOT NULL, - relation producer_relation NOT NULL, - PRIMARY KEY(pid1, pid2) + id integer NOT NULL, + pid integer NOT NULL, -- producers.id + relation producer_relation NOT NULL, + PRIMARY KEY(id, pid) ); --- producers_rev -CREATE TABLE producers_rev ( - id integer NOT NULL PRIMARY KEY, - pid integer NOT NULL DEFAULT 0, - type producer_type NOT NULL DEFAULT 'co', - name varchar(200) NOT NULL DEFAULT '', - original varchar(200) NOT NULL DEFAULT '', - website varchar(250) NOT NULL DEFAULT '', - lang language NOT NULL DEFAULT 'ja', - "desc" text NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - l_wp varchar(150) +-- producers_relations_hist +CREATE TABLE producers_relations_hist ( + chid integer NOT NULL, + pid integer NOT NULL, -- producers.id + relation producer_relation NOT NULL, + PRIMARY KEY(chid, pid) ); -- quotes @@ -144,71 +225,124 @@ CREATE TABLE quotes ( -- releases CREATE TABLE releases ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + type release_type NOT NULL DEFAULT 'complete', + website varchar(250) NOT NULL DEFAULT '', + catalog varchar(50) NOT NULL DEFAULT '', + gtin bigint NOT NULL DEFAULT 0, + released integer NOT NULL DEFAULT 0, + notes text NOT NULL DEFAULT '', + minage smallint, + patch boolean NOT NULL DEFAULT FALSE, + freeware boolean NOT NULL DEFAULT FALSE, + doujin boolean NOT NULL DEFAULT FALSE, + resolution smallint NOT NULL DEFAULT 0, + voiced smallint NOT NULL DEFAULT 0, + ani_story smallint NOT NULL DEFAULT 0, + ani_ero smallint NOT NULL DEFAULT 0 +); + +-- releases_hist +CREATE TABLE releases_hist ( + chid integer NOT NULL PRIMARY KEY, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + type release_type NOT NULL DEFAULT 'complete', + website varchar(250) NOT NULL DEFAULT '', + catalog varchar(50) NOT NULL DEFAULT '', + gtin bigint NOT NULL DEFAULT 0, + released integer NOT NULL DEFAULT 0, + notes text NOT NULL DEFAULT '', + minage smallint, + patch boolean NOT NULL DEFAULT FALSE, + freeware boolean NOT NULL DEFAULT FALSE, + doujin boolean NOT NULL DEFAULT FALSE, + resolution smallint NOT NULL DEFAULT 0, + voiced smallint NOT NULL DEFAULT 0, + ani_story smallint NOT NULL DEFAULT 0, + ani_ero smallint NOT NULL DEFAULT 0 ); -- releases_lang CREATE TABLE releases_lang ( - rid integer NOT NULL, - lang language NOT NULL, - PRIMARY KEY(rid, lang) + id integer NOT NULL, + lang language NOT NULL, + PRIMARY KEY(id, lang) +); + +-- releases_lang_hist +CREATE TABLE releases_lang_hist ( + chid integer NOT NULL, + lang language NOT NULL, + PRIMARY KEY(chid, lang) ); -- releases_media CREATE TABLE releases_media ( - rid integer NOT NULL DEFAULT 0, - medium medium NOT NULL, - qty smallint NOT NULL DEFAULT 1, - PRIMARY KEY(rid, medium, qty) + id integer NOT NULL, + medium medium NOT NULL, + qty smallint NOT NULL DEFAULT 1, + PRIMARY KEY(id, medium, qty) +); + +-- releases_media_hist +CREATE TABLE releases_media_hist ( + chid integer NOT NULL, + medium medium NOT NULL, + qty smallint NOT NULL DEFAULT 1, + PRIMARY KEY(chid, medium, qty) ); -- releases_platforms CREATE TABLE releases_platforms ( - rid integer NOT NULL DEFAULT 0, - platform platform NOT NULL, - PRIMARY KEY(rid, platform) + id integer NOT NULL, + platform platform NOT NULL, + PRIMARY KEY(id, platform) +); + +-- releases_platforms_hist +CREATE TABLE releases_platforms_hist ( + chid integer NOT NULL, + platform platform NOT NULL, + PRIMARY KEY(chid, platform) ); -- releases_producers CREATE TABLE releases_producers ( - pid integer NOT NULL, - rid integer NOT NULL, - developer boolean NOT NULL DEFAULT FALSE, - publisher boolean NOT NULL DEFAULT TRUE, + id integer NOT NULL, + pid integer NOT NULL, -- producers.id + developer boolean NOT NULL DEFAULT FALSE, + publisher boolean NOT NULL DEFAULT TRUE, CHECK(developer OR publisher), - PRIMARY KEY(pid, rid) + PRIMARY KEY(id, pid) ); --- releases_rev -CREATE TABLE releases_rev ( - id integer NOT NULL PRIMARY KEY, - rid integer NOT NULL DEFAULT 0, - title varchar(250) NOT NULL DEFAULT '', - original varchar(250) NOT NULL DEFAULT '', - type release_type NOT NULL DEFAULT 'complete', - website varchar(250) NOT NULL DEFAULT '', - released integer NOT NULL DEFAULT 0, - notes text NOT NULL DEFAULT '', - minage smallint, - gtin bigint NOT NULL DEFAULT 0, - patch boolean NOT NULL DEFAULT FALSE, - catalog varchar(50) NOT NULL DEFAULT '', - resolution smallint NOT NULL DEFAULT 0, - voiced smallint NOT NULL DEFAULT 0, - freeware boolean NOT NULL DEFAULT FALSE, - doujin boolean NOT NULL DEFAULT FALSE, - ani_story smallint NOT NULL DEFAULT 0, - ani_ero smallint NOT NULL DEFAULT 0 +-- releases_producers_hist +CREATE TABLE releases_producers_hist ( + chid integer NOT NULL, + pid integer NOT NULL, -- producers.id + developer boolean NOT NULL DEFAULT FALSE, + publisher boolean NOT NULL DEFAULT TRUE, + CHECK(developer OR publisher), + PRIMARY KEY(chid, pid) ); -- releases_vn CREATE TABLE releases_vn ( - rid integer NOT NULL DEFAULT 0, - vid integer NOT NULL DEFAULT 0, - PRIMARY KEY(rid, vid) + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(id, vid) +); + +-- releases_vn_hist +CREATE TABLE releases_vn_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(chid, vid) ); -- relgraphs @@ -244,33 +378,47 @@ CREATE TABLE sessions ( -- staff CREATE TABLE staff ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + aid integer NOT NULL, -- staff_alias.aid + gender gender NOT NULL DEFAULT 'unknown', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_site varchar(250) NOT NULL DEFAULT '', + l_twitter varchar(16) NOT NULL DEFAULT '', + l_anidb integer +); + +-- staff_hist +CREATE TABLE staff_hist ( + chid integer NOT NULL PRIMARY KEY, + aid integer NOT NULL, -- Can't refer to staff_alias.id, because the alias might have been deleted + gender gender NOT NULL DEFAULT 'unknown', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_site varchar(250) NOT NULL DEFAULT '', + l_twitter varchar(16) NOT NULL DEFAULT '', + l_anidb integer ); -- staff_alias CREATE TABLE staff_alias ( - id SERIAL NOT NULL, - rid integer, - name varchar(200) NOT NULL DEFAULT '', - original varchar(200) NOT NULL DEFAULT '', - PRIMARY KEY (id, rid) + id integer NOT NULL, + aid SERIAL PRIMARY KEY, -- Globally unique ID of this alias + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '' ); --- staff_rev -CREATE TABLE staff_rev ( - id integer NOT NULL PRIMARY KEY, - sid integer NOT NULL, - aid integer NOT NULL, - gender gender NOT NULL DEFAULT 'unknown', - lang language NOT NULL DEFAULT 'ja', - "desc" text NOT NULL DEFAULT '', - l_wp varchar(150) NOT NULL DEFAULT '', - l_site varchar(250) NOT NULL DEFAULT '', - l_twitter varchar(16) NOT NULL DEFAULT '', - l_anidb integer +-- staff_alias_hist +CREATE TABLE staff_alias_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + PRIMARY KEY(chid, aid) ); -- stats_cache @@ -427,78 +575,129 @@ CREATE TABLE users_prefs ( -- vn CREATE TABLE vn ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE, - rgraph integer, + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + length smallint NOT NULL DEFAULT 0, + img_nsfw boolean NOT NULL DEFAULT FALSE, + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_encubed varchar(100) NOT NULL DEFAULT '', + l_renai varchar(100) NOT NULL DEFAULT '', + rgraph integer, -- relgraphs.id c_released integer NOT NULL DEFAULT 0, c_languages language[] NOT NULL DEFAULT '{}', + c_olang language[] NOT NULL DEFAULT '{}', c_platforms platform[] NOT NULL DEFAULT '{}', c_popularity real, - c_rating real, + c_rating real, c_votecount integer NOT NULL DEFAULT 0, - c_search text, - c_olang language[] NOT NULL DEFAULT '{}' + c_search text +); + +-- vn_hist +CREATE TABLE vn_hist ( + chid integer NOT NULL PRIMARY KEY, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + length smallint NOT NULL DEFAULT 0, + img_nsfw boolean NOT NULL DEFAULT FALSE, + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_encubed varchar(100) NOT NULL DEFAULT '', + l_renai varchar(100) NOT NULL DEFAULT '' ); -- vn_anime CREATE TABLE vn_anime ( - vid integer NOT NULL, - aid integer NOT NULL, - PRIMARY KEY(vid, aid) + id integer NOT NULL, + aid integer NOT NULL, -- anime.id + PRIMARY KEY(id, aid) +); + +-- vn_anime_hist +CREATE TABLE vn_anime_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- anime.id + PRIMARY KEY(chid, aid) ); -- vn_relations CREATE TABLE vn_relations ( - vid1 integer NOT NULL DEFAULT 0, - vid2 integer NOT NULL DEFAULT 0, - relation vn_relation NOT NULL, - official boolean NOT NULL DEFAULT TRUE, - PRIMARY KEY(vid1, vid2) + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + relation vn_relation NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + PRIMARY KEY(id, vid) ); --- vn_rev -CREATE TABLE vn_rev ( - id integer NOT NULL PRIMARY KEY, - vid integer NOT NULL DEFAULT 0, - title varchar(250) NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - img_nsfw boolean NOT NULL DEFAULT FALSE, - length smallint NOT NULL DEFAULT 0, - "desc" text NOT NULL DEFAULT '', - l_wp varchar(150) NOT NULL DEFAULT '', - image integer NOT NULL DEFAULT 0, - l_encubed varchar(100) NOT NULL DEFAULT '', - l_renai varchar(100) NOT NULL DEFAULT '', - original varchar(250) NOT NULL DEFAULT '' +-- vn_relations_hist +CREATE TABLE vn_relations_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + relation vn_relation NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + PRIMARY KEY(chid, vid) ); -- vn_screenshots CREATE TABLE vn_screenshots ( - vid integer NOT NULL DEFAULT 0, - scr integer NOT NULL DEFAULT 0, - nsfw boolean NOT NULL DEFAULT FALSE, - rid integer, - PRIMARY KEY(vid, scr) + id integer NOT NULL, + scr integer NOT NULL, -- screenshots.id + rid integer, -- releases.id (only NULL for old revisions, nowadays not allowed anymore) + nsfw boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(id, scr) +); + +-- vn_screenshots_hist +CREATE TABLE vn_screenshots_hist ( + chid integer NOT NULL, + scr integer NOT NULL, + rid integer, + nsfw boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(chid, scr) ); -- vn_seiyuu CREATE TABLE vn_seiyuu ( - vid integer NOT NULL, - aid integer NOT NULL, - cid integer NOT NULL, - note varchar(250) NOT NULL DEFAULT '', - PRIMARY KEY (vid, aid, cid) + id integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid + cid integer NOT NULL, -- chars.id + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (id, aid, cid) +); + +-- vn_seiyuu_hist +CREATE TABLE vn_seiyuu_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted + cid integer NOT NULL, -- chars.id + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (chid, aid, cid) ); -- vn_staff CREATE TABLE vn_staff ( - vid integer NOT NULL, - aid integer NOT NULL, - role credit_type NOT NULL DEFAULT 'staff', - note varchar(250) NOT NULL DEFAULT '', - PRIMARY KEY (vid, aid, role) + id integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid + role credit_type NOT NULL DEFAULT 'staff', + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (id, aid, role) +); + +-- vn_staff_hist +CREATE TABLE vn_staff_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- See note at vn_seiyuu_hist.aid + role credit_type NOT NULL DEFAULT 'staff', + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (chid, aid, role) ); -- vnlists diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 4028e98e..05770f83 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -1,88 +1,107 @@ -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); +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_main_fkey FOREIGN KEY (main) REFERENCES chars (id); +ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_main_fkey FOREIGN KEY (main) REFERENCES chars (id); +ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_id_fkey FOREIGN KEY (id) REFERENCES chars (id); +ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (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 chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_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_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_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_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_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_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id); +ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +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_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (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_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id); +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid); +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid); +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (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 notifications_uid ON notifications (uid); -CREATE INDEX releases_producers_rid ON releases_producers (rid); +CREATE INDEX releases_producers_pid ON releases_producers (pid); CREATE INDEX releases_vn_vid ON releases_vn (vid); +CREATE INDEX staff_alias_id ON staff_alias (id); 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)); +CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev); +CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0)); +CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0)); |