From f0af745c9bcc21a5a5c3ae925c27dc8a29d319b6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 10 Jun 2019 09:08:43 +0200 Subject: SQL: Add ON DELETE CASCADE to _hist tables referencing changes This makes it possible (but not necessarily easy) to fully delete changes from the database. --- util/sql/tableattrs.sql | 38 +++++++++++++++++++------------------- util/updates/update_20190610.sql | 39 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 58 insertions(+), 19 deletions(-) create mode 100644 util/updates/update_20190610.sql diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index a43edc3c..793caddc 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -1,48 +1,48 @@ 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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) DEFERRABLE; ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE; -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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE; ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE; 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_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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 staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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); @@ -64,29 +64,29 @@ ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) 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_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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) DEFERRABLE; -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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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) DEFERRABLE; 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) DEFERRABLE INITIALLY DEFERRED; 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_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; 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; diff --git a/util/updates/update_20190610.sql b/util/updates/update_20190610.sql new file mode 100644 index 00000000..07b4cc8a --- /dev/null +++ b/util/updates/update_20190610.sql @@ -0,0 +1,39 @@ +ALTER TABLE chars_hist DROP CONSTRAINT chars_hist_chid_fkey ; +ALTER TABLE chars_traits_hist DROP CONSTRAINT chars_traits_hist_chid_fkey ; +ALTER TABLE chars_vns_hist DROP CONSTRAINT chars_vns_hist_chid_fkey ; +ALTER TABLE producers_hist DROP CONSTRAINT producers_chid_id_fkey ; +ALTER TABLE producers_relations_hist DROP CONSTRAINT producers_relations_hist_id_fkey ; +ALTER TABLE releases_hist DROP CONSTRAINT releases_hist_chid_fkey ; +ALTER TABLE releases_lang_hist DROP CONSTRAINT releases_lang_hist_chid_fkey ; +ALTER TABLE releases_media_hist DROP CONSTRAINT releases_media_hist_chid_fkey ; +ALTER TABLE releases_platforms_hist DROP CONSTRAINT releases_platforms_hist_chid_fkey; +ALTER TABLE releases_producers_hist DROP CONSTRAINT releases_producers_hist_chid_fkey; +ALTER TABLE releases_vn_hist DROP CONSTRAINT releases_vn_hist_chid_fkey ; +ALTER TABLE staff_hist DROP CONSTRAINT staff_hist_chid_fkey ; +ALTER TABLE staff_alias_hist DROP CONSTRAINT staff_alias_chid_fkey ; +ALTER TABLE vn_hist DROP CONSTRAINT vn_hist_chid_fkey ; +ALTER TABLE vn_anime_hist DROP CONSTRAINT vn_anime_hist_chid_fkey ; +ALTER TABLE vn_relations_hist DROP CONSTRAINT vn_relations_chid_fkey ; +ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_chid_fkey ; +ALTER TABLE vn_seiyuu_hist DROP CONSTRAINT vn_seiyuu_hist_chid_fkey ; +ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_chid_fkey ; + +ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; -- cgit v1.2.3