diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/20241026174345_init.sql | 18 |
1 files changed, 16 insertions, 2 deletions
diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql index 6fa0b93..e4f0f78 100644 --- a/migrations/20241026174345_init.sql +++ b/migrations/20241026174345_init.sql @@ -37,7 +37,7 @@ CREATE TABLE frequencies ( id INTEGER PRIMARY KEY, network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, name TEXT NOT NULL, - hex_color TEXT, + hex_color TEXT NOT NULL, perms INT NOT NULL CHECK (perms IN (0, 1, 2)), -- 0 no access | 1 read | 2 read & write position INT NOT NULL @@ -45,7 +45,7 @@ CREATE TABLE frequencies ( CREATE TABLE users_networks ( user_id INT NOT NULL REFERENCES users (id), - network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + network_id INT NOT NULL REFERENCES networks (id), joined_at TEXT NOT NULL DEFAULT current_timestamp, is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true, is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false, @@ -99,6 +99,20 @@ BEGIN END -- +goose StatementEnd +-- +goose StatementBegin +CREATE TRIGGER on_network_delete +AFTER DELETE ON networks +BEGIN + UPDATE users_networks SET + position = position - 1 + WHERE user_id IN ( + SELECT user_id FROM users_networks WHERE network_id = OLD.network_id + ) AND position > OLD.position; + + DELETE FROM users_networks WHERE network_id = OLD.network_id; +END +-- +goose StatementEnd + -- +goose Down DROP TABLE messages; DROP TABLE users; |