diff options
author | Yorhel <git@yorhel.nl> | 2009-07-19 16:07:49 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-07-19 16:07:49 +0200 |
commit | 68c2d44c4480ef0a0b2637a5a8e1e0deace5387b (patch) | |
tree | 2a36122859a157e1079f9c477a6fbddd3b809bbc /util | |
parent | 09869c785d57b20b2ff4346a53476a8f8efac6da (diff) |
Removed DEFERRED property on most foreign key constraints
Only allowing the deferred state on a select few foreign keys would
detect problems in an earlier stage (rather than waiting for a commit to
happen), and guarantees that some things are inserted before others,
which in turn eases the writing of trigger functions.
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 84 | ||||
-rw-r--r-- | util/updates/update_2.6.sql | 89 |
2 files changed, 130 insertions, 43 deletions
diff --git a/util/dump.sql b/util/dump.sql index c3f6be0d..eb502602 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -347,53 +347,51 @@ CREATE TABLE wlists ( ----------------------------------------------- -ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id); ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; +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) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (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); +ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +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); 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) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id) 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); +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (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_categories 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 votes ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql index e8f4e9b4..428c6369 100644 --- a/util/updates/update_2.6.sql +++ b/util/updates/update_2.6.sql @@ -49,6 +49,95 @@ UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 7; +-- remove the DEFERRED attribute on all foreign key checks on which it isn't necessary +-- (note: these queries all assume the foreign keys have their default names, as given +-- by PostgreSQL. This shouldn't be a problem, provided if you haven't touched them.) +ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey; +ALTER TABLE changes DROP CONSTRAINT changes_causedby_fkey; +ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_id_fkey; +ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_pid_fkey; +ALTER TABLE quotes DROP CONSTRAINT quotes_vid_fkey; +ALTER TABLE releases_lang DROP CONSTRAINT releases_lang_rid_fkey; +ALTER TABLE releases_media DROP CONSTRAINT releases_media_rid_fkey; +ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_rid_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_rid_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey; +ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_id_fkey; +ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_rid_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_rid_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_rid_fkey; +ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey; +ALTER TABLE tags_aliases DROP CONSTRAINT tags_aliases_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_parent_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_tag_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_vid_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_tid_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey; +ALTER TABLE threads_boards DROP CONSTRAINT threads_tags_tid_fkey; -- threads_boards used to be called threads_tags +ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey; +ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_aid_fkey; +ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_vid_fkey; +ALTER TABLE vn_categories DROP CONSTRAINT vn_categories_vid_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey; +ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey; +ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_vid_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_vid_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_rid_fkey; +ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey; +ALTER TABLE votes DROP CONSTRAINT votes_vid_fkey; +ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey; +ALTER TABLE wlists DROP CONSTRAINT wlists_vid_fkey; + +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (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_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); +ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +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); +ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (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_categories 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 votes ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); + + + -- automatically insert rows into the anime table for unknown aids -- when inserted into vn_anime CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ |