diff options
| -rw-r--r-- | migrations/20241026174345_init.sql | 25 |
1 files changed, 20 insertions, 5 deletions
diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql index 1494e50..4d55e0d 100644 --- a/migrations/20241026174345_init.sql +++ b/migrations/20241026174345_init.sql @@ -3,7 +3,7 @@ CREATE TABLE messages ( id INTEGER PRIMARY KEY, sender_id INT NOT NULL REFERENCES users (id), content TEXT NOT NULL, - edited BOOLEAN NOT NULL CHECK (edited IN (0, 1)), + edited BOOLEAN NOT NULL CHECK (edited IN (0, 1)) DEFAULT 0, frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE, receiver_id INT REFERENCES users (id), @@ -19,7 +19,8 @@ CREATE TABLE users ( name TEXT NOT NULL, public_key BLOB NOT NULL UNIQUE, description TEXT, - is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (0, 1)) + is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (0, 1)) DEFAULT 1, + is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (0, 1)) DEFAULT 0 ); CREATE TABLE networks ( @@ -39,21 +40,23 @@ CREATE TABLE frequencies ( hex_color TEXT, perms INT NOT NULL CHECK (perms IN (0, 1, 2)), -- 0 rw admin | 1 r all w admin | 2 rw all - position INT NOT NULL + position INT NOT NULL -- TODO: make this auto inc ); CREATE TABLE users_networks ( user_id INT NOT NULL REFERENCES users (id), network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, joined_at TEXT NOT NULL DEFAULT current_timestamp, - is_admin BOOLEAN NOT NULL CHECK (is_admin IN (0, 1)), + is_admin BOOLEAN NOT NULL CHECK (is_admin IN (0, 1)) DEFAULT 0, + is_muted BOOLEAN NOT NULL CHECK (is_muted IN (0, 1)) DEFAULT 0, PRIMARY KEY (user_id, network_id) + -- TODO: In discord, if u get "kicked" then join does your "joined_at" show the oldest or latest? ); CREATE TABLE user_trusted_users ( truster_user_id INT NOT NULL REFERENCES users (id), trusted_user_id INT NOT NULL REFERENCES users (id), - trusted_public_key BLOB NOT NULL UNIQUE, + trusted_public_key BLOB NOT NULL, PRIMARY KEY (truster_user_id, trusted_user_id) ); @@ -71,6 +74,12 @@ CREATE TABLE network_banned_users ( PRIMARY KEY (network_id, banned_user_id) ); +CREATE INDEX idx_is_deleted_false ON users (is_deleted) WHERE is_deleted = 0; +CREATE INDEX idx_network_name ON networks (name) WHERE is_public = 1; +CREATE INDEX idx_frequency_network ON frequencies (network_id); +CREATE INDEX idx_blocked_by_user ON user_blocked_users (blocker_user_id); +CREATE INDEX idx_banned_by_network ON network_banned_users (network_id); + -- +goose Down DROP TABLE messages; DROP TABLE users; @@ -80,3 +89,9 @@ DROP TABLE users_networks; DROP TABLE user_trusted_users; DROP TABLE user_blocked_users; DROP TABLE network_banned_users; + +DROP INDEX idx_is_deleted_false; +DROP INDEX idx_network_name; +DROP INDEX idx_frequency_network; +DROP INDEX idx_blocked_by_user; +DROP INDEX idx_banned_by_network; |