From 31e3778061a20e0787c076d9353e82b2e223841e Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Sun, 27 Oct 2024 17:04:08 +0200 Subject: feat: add setup for SQL --- migrations/20241026174345_init.sql | 45 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 migrations/20241026174345_init.sql (limited to 'migrations/20241026174345_init.sql') diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql new file mode 100644 index 0000000..ad1f65f --- /dev/null +++ b/migrations/20241026174345_init.sql @@ -0,0 +1,45 @@ +-- +goose Up +CREATE TABLE messages ( + id INTEGER PRIMARY KEY, + sender_id INT NOT NULL REFERENCES users (id), + content TEXT NOT NULL, + + frequency_id INT REFERENCES frequencies (id), + receiver_id INT REFERENCES users (id), + + CHECK ( + (frequency_id IS NOT NULL AND receiver_id IS NULL) OR + (frequency_id IS NULL AND receiver_id IS NOT NULL) + ) +); + +CREATE TABLE users ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + public_key BLOB NOT NULL +); + +CREATE TABLE networks ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + owner_id INT NOT NULL REFERENCES users (id) +); + +CREATE TABLE frequencies ( + id INTEGER PRIMARY KEY, + network_id INT NOT NULL REFERENCES networks (id), + name TEXT NOT NULL +); + +CREATE TABLE users_networks ( + user_id INT NOT NULL REFERENCES users (id), + network_id INT NOT NULL REFERENCES networks (id), + PRIMARY KEY (user_id, network_id) +); + +-- +goose Down +DROP TABLE messages; +DROP TABLE users; +DROP TABLE networks; +DROP TABLE frequencies; +DROP TABLE users_networks; -- cgit v1.3.1