blob: f44436bd89653e89efda6121118845a45926691f (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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;
|
