From eab8cba4bb37491d5c3b497b98faa7029e7b7300 Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Tue, 4 Feb 2025 18:52:57 +0200 Subject: Refactored the way notifications work on the server-side, next commit fixes the client to use this new system --- .../20250204150045_last_read_messages.sql | 39 ++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 internal/server/api/migrations/20250204150045_last_read_messages.sql (limited to 'internal/server/api/migrations') diff --git a/internal/server/api/migrations/20250204150045_last_read_messages.sql b/internal/server/api/migrations/20250204150045_last_read_messages.sql new file mode 100644 index 0000000..f44436b --- /dev/null +++ b/internal/server/api/migrations/20250204150045_last_read_messages.sql @@ -0,0 +1,39 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS last_read_messages ( + user_id INT NOT NULL REFERENCES users (id), + source_id INT NOT NULL, -- frequency_id or receiver_id or sender_id + last_read INT NOT NULL, + -- can be 0 for "no messages ever read" (null) + -- can be between msgs in cases like the message getting deleted + PRIMARY KEY (user_id, source_id) +); + +DROP TRIGGER IF EXISTS on_frequency_delete; +-- +goose StatementBegin +CREATE TRIGGER IF NOT EXISTS on_frequency_delete +AFTER DELETE ON frequencies +BEGIN + UPDATE frequencies SET + position = position - 1 + WHERE network_id = OLD.network_id AND position > OLD.position; + + DELETE FROM last_read_messages WHERE source_id = OLD.id; +END +-- +goose StatementEnd + +DROP TRIGGER IF EXISTS on_user_delete; +-- +goose StatementBegin +CREATE TRIGGER IF NOT EXISTS 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 members WHERE user_id = NEW.id; + DELETE FROM trusted_users WHERE trusting_user_id = NEW.id OR trusted_user_id = NEW.id; + DELETE FROM blocked_users WHERE blocking_user_id = NEW.id OR blocked_user_id = NEW.id; + DELETE FROM last_read_messages WHERE user_id = NEW.id; +END +-- +goose StatementEnd + +-- +goose Down +DROP TABLE IF EXISTS last_read_messages; -- cgit v1.3.1