From f5ceecf5098f75bf8732db04c97818d57d17c634 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 14 Nov 2010 13:42:29 +0100 Subject: SQL: Added ON DELETE clause to all foreign keys referencing users (id) This makes deleting user accounts less error prone. It also seems I forgot to git add update_2.14.sql in an earlier commit, sorry about that. --- util/sql/schema.sql | 24 ++++---- util/updates/update_2.14.sql | 135 +++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 147 insertions(+), 12 deletions(-) create mode 100644 util/updates/update_2.14.sql (limited to 'util') diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 72ecd9cc..1eb823e3 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -36,7 +36,7 @@ CREATE TABLE notifications ( iid integer NOT NULL, subid integer, c_title text NOT NULL, - c_byuser integer + c_byuser integer NOT NULL DEFAULT 0 ); -- producers @@ -194,7 +194,7 @@ CREATE TABLE tags ( added timestamptz NOT NULL DEFAULT NOW(), state smallint NOT NULL DEFAULT 0, c_vns integer NOT NULL DEFAULT 0, - addedby integer NOT NULL DEFAULT 1 + addedby integer NOT NULL DEFAULT 0 ); -- tags_aliases @@ -358,9 +358,9 @@ CREATE TABLE wlists ( -ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); -ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; +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); @@ -378,19 +378,19 @@ ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes 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 (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); -ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +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); +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); +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 vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); @@ -403,8 +403,8 @@ ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn 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 (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); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.14.sql b/util/updates/update_2.14.sql new file mode 100644 index 00000000..5c8cfe76 --- /dev/null +++ b/util/updates/update_2.14.sql @@ -0,0 +1,135 @@ + +\i util/sql/func.sql + +-- redefine the triggers to use the new conditional triggers in PostgreSQL 9.0 + +DROP TRIGGER hidlock_update ON vn; +DROP TRIGGER hidlock_update ON producers; +DROP TRIGGER hidlock_update ON releases; +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(); + + +DROP TRIGGER vn_stats_update ON vn; +DROP TRIGGER producers_stats_update ON producers; +DROP TRIGGER releases_stats_update ON releases; +DROP TRIGGER threads_stats_update ON threads; +DROP TRIGGER threads_posts_stats_update ON threads_posts; +DROP TRIGGER users_stats_update ON users; +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 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(); + +DROP TRIGGER vn_anime_aid ON vn_anime; +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(); + +DROP TRIGGER anime_fetch_notify ON anime; +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); + +DROP TRIGGER vn_rev_image_notify ON vn_rev; +CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify(); + +DROP TRIGGER screenshot_process_notify ON screenshots; +CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify(); + +DROP TRIGGER vn_relgraph_notify ON vn; +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(); + +DROP TRIGGER producer_relgraph_notify ON producers; +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(); + +DROP TRIGGER release_vncache_update ON releases; +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(); + +DROP TRIGGER notify_dbdel ON vn; +DROP TRIGGER notify_dbdel ON producers; +DROP TRIGGER notify_dbdel ON releases; +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(); + +DROP TRIGGER notify_listdel ON vn; +DROP TRIGGER notify_listdel ON releases; +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(); + +DROP TRIGGER notify_dbedit ON vn; +DROP TRIGGER notify_dbedit ON producers; +DROP TRIGGER notify_dbedit ON releases; +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(); + +DROP TRIGGER notify_announce ON threads_posts; +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); + +DROP TRIGGER vn_vnsearch_notify ON vn; +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 AND NOT NEW.hidden + OR NEW.hidden IS DISTINCT FROM OLD.hidden + OR NEW.latest IS DISTINCT FROM OLD.latest + ) EXECUTE PROCEDURE vn_vnsearch_notify(); + +DROP TRIGGER vn_vnsearch_notify ON releases; +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(); + + + +-- add ON DELETE clause to all foreign keys referencing users (id) +-- and change some defaults/constraints to make sure it'll actually work + +ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey; +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; + +UPDATE notifications SET c_byuser = 0 WHERE c_byuser IS NULL; +ALTER TABLE notifications ALTER COLUMN c_byuser SET DEFAULT 0; +ALTER TABLE notifications ALTER COLUMN c_byuser SET NOT NULL; +ALTER TABLE notifications DROP CONSTRAINT notifications_uid_fkey; +ALTER TABLE notifications DROP CONSTRAINT notifications_c_byuser_fkey; +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 rlists DROP CONSTRAINT rlists_uid_fkey; +ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE sessions DROP CONSTRAINT sessions_uid_fkey; +ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE tags ALTER COLUMN addedby SET DEFAULT 0; +ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey; +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; + +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey; +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey; +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; + +ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey; +ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey; +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + -- cgit v1.2.3