From 63a1d24295122728820a61aa93b3da6cd6d401d9 Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Sat, 23 Nov 2024 17:10:29 +0200 Subject: Updated user queries and added trigger on user delete --- migrations/20241026174345_init.sql | 15 +++++++++++++-- 1 file changed, 13 insertions(+), 2 deletions(-) (limited to 'migrations') diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql index c0056b3..49b840e 100644 --- a/migrations/20241026174345_init.sql +++ b/migrations/20241026174345_init.sql @@ -73,12 +73,24 @@ CREATE TABLE network_banned_users ( PRIMARY KEY (network_id, banned_user_id) ); -CREATE INDEX idx_is_deleted_false ON users (is_deleted) WHERE is_deleted = 0; CREATE INDEX idx_network_name ON networks (name) WHERE is_public = 1; CREATE INDEX idx_frequency_network ON frequencies (network_id); CREATE INDEX idx_blocked_by_user ON user_blocked_users (blocker_user_id); CREATE INDEX idx_banned_by_network ON network_banned_users (network_id); +-- +goose StatementBegin +CREATE TRIGGER on_user_delete +AFTER UPDATE OF is_deleted ON users +WHEN NEW.is_deleted = true +BEGIN + DELETE FROM networks WHERE owner_id = NEW.id; + DELETE FROM users_networks WHERE user_id = NEW.id; + DELETE FROM user_blocked_users WHERE blocker_user_id = NEW.id OR blocked_user_id = NEW.id; + DELETE FROM user_trusted_users WHERE truster_user_id = NEW.id OR trusted_user_id = NEW.id; + DELETE FROM network_banned_users WHERE banned_user_id = NEW.id; +END +-- +goose StatementEnd + -- +goose Down DROP TABLE messages; DROP TABLE users; @@ -89,7 +101,6 @@ DROP TABLE user_trusted_users; DROP TABLE user_blocked_users; DROP TABLE network_banned_users; -DROP INDEX idx_is_deleted_false; DROP INDEX idx_network_name; DROP INDEX idx_frequency_network; DROP INDEX idx_blocked_by_user; -- cgit v1.3.1