From 936d3cf690ace9adfdfe0fb44193c11a0cb26d95 Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Thu, 9 Jan 2025 17:28:46 +0200 Subject: Added goose for migrations, refactored SQL schema, server and client are broken --- .../api/migrations/20250109135557_frequencies.sql | 24 ++++++++++++++++++++++ 1 file changed, 24 insertions(+) create mode 100644 internal/server/api/migrations/20250109135557_frequencies.sql (limited to 'internal/server/api/migrations/20250109135557_frequencies.sql') diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql new file mode 100644 index 0000000..a444af1 --- /dev/null +++ b/internal/server/api/migrations/20250109135557_frequencies.sql @@ -0,0 +1,24 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS frequencies ( + id INTEGER PRIMARY KEY, + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + name TEXT NOT NULL, + 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 +); + +-- +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; +END +-- +goose StatementEnd + +-- +goose Down +DROP TABLE IF EXISTS frequencies; +DROP TRIGGER IF EXISTS on_frequency_delete; -- cgit v1.3.1