From a0414925cdf6d57acc8d2f0f0646e495707bd632 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Fri, 26 Dec 2025 21:48:57 -0800 Subject: initial commit --- schema.sql | 43 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) create mode 100644 schema.sql (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..aa5ee69 --- /dev/null +++ b/schema.sql @@ -0,0 +1,43 @@ +-- ========================= +-- Directories table +-- ========================= +CREATE TABLE IF NOT EXISTS directories ( + directory_id BIGSERIAL PRIMARY KEY, + path TEXT NOT NULL, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + CONSTRAINT directories_path_unique UNIQUE (path) +); + +-- ========================= +-- Files table +-- ========================= +CREATE TABLE IF NOT EXISTS files ( + file_id BIGSERIAL PRIMARY KEY, + disc_channel_id VARCHAR(255) NOT NULL, + disc_message_id VARCHAR(255) NOT NULL, + directory_id BIGINT NOT NULL + REFERENCES directories(directory_id) + ON DELETE RESTRICT, + file_name TEXT NOT NULL, + file_description TEXT, + size BIGINT, + mime_type TEXT, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + CONSTRAINT files_unique_name_per_directory + UNIQUE (directory_id, file_name) +); + +-- ========================= +-- Indexes +-- ========================= + +CREATE INDEX IF NOT EXISTS idx_directories_path +ON directories (path); + +CREATE INDEX IF NOT EXISTS idx_files_directory +ON files (directory_id); + + +INSERT INTO directories (path) +VALUES ('') +ON CONFLICT (path) DO NOTHING; \ No newline at end of file -- cgit v1.2.3