1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
-- =========================
-- 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)
);
-- =========================
-- File partials table
-- =========================
CREATE TABLE IF NOT EXISTS file_partials (
partial_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,
part_name TEXT NOT NULL,
part_number INTEGER NOT NULL,
part_size BIGINT NOT NULL,
original_filename TEXT NOT NULL,
file_description TEXT,
mime_type TEXT DEFAULT 'application/octet-stream',
uploaded_via_webhook BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT partials_unique_name_per_directory
UNIQUE (directory_id, part_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);
CREATE INDEX IF NOT EXISTS idx_partials_directory
ON file_partials (directory_id);
CREATE INDEX IF NOT EXISTS idx_partials_original_filename
ON file_partials (original_filename);
INSERT INTO directories (path)
VALUES ('')
ON CONFLICT (path) DO NOTHING;
|
