68 lines
2.2 KiB
MySQL
68 lines
2.2 KiB
MySQL
|
-- files
|
||
|
|
CREATE TABLE files(
|
||
|
|
id serial PRIMARY KEY,
|
||
|
|
hash varchar NOT NULL,
|
||
|
|
mtime timestamp NOT NULL,
|
||
|
|
ctime timestamp NOT NULL,
|
||
|
|
mime varchar,
|
||
|
|
ext varchar,
|
||
|
|
added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (hash)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE file_post_relationships(
|
||
|
|
file_id int NOT NULL REFERENCES files(id),
|
||
|
|
filename varchar NOT NULL,
|
||
|
|
service varchar NOT NULL,
|
||
|
|
"user" varchar NOT NULL,
|
||
|
|
post varchar NOT NULL,
|
||
|
|
contributor_id int REFERENCES account(id),
|
||
|
|
inline boolean NOT NULL DEFAULT FALSE,
|
||
|
|
PRIMARY KEY (file_id, service, "user", post)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE file_discord_message_relationships(
|
||
|
|
file_id int NOT NULL REFERENCES files(id),
|
||
|
|
filename varchar NOT NULL,
|
||
|
|
server varchar NOT NULL,
|
||
|
|
channel varchar NOT NULL,
|
||
|
|
id varchar NOT NULL,
|
||
|
|
contributor_id int REFERENCES account(id),
|
||
|
|
PRIMARY KEY (file_id, SERVER, channel, id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE file_server_relationships(
|
||
|
|
file_id int NOT NULL REFERENCES files(id),
|
||
|
|
remote_path varchar NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE archive_files(
|
||
|
|
file_id int NOT NULL REFERENCES files(id),
|
||
|
|
-- this for sure won't have problems down the line
|
||
|
|
files text[] NOT NULL,
|
||
|
|
password TEXT,
|
||
|
|
CONSTRAINT archive_files_pk PRIMARY KEY (file_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX file_id_idx ON file_post_relationships USING btree("file_id");
|
||
|
|
|
||
|
|
CREATE INDEX file_post_service_idx ON file_post_relationships USING btree("service");
|
||
|
|
|
||
|
|
CREATE INDEX file_post_user_idx ON file_post_relationships USING btree("user");
|
||
|
|
|
||
|
|
CREATE INDEX file_post_id_idx ON file_post_relationships USING btree("post");
|
||
|
|
|
||
|
|
CREATE INDEX file_post_contributor_id_idx ON file_post_relationships USING btree("contributor_id");
|
||
|
|
|
||
|
|
CREATE INDEX file_discord_id_idx ON file_discord_message_relationships USING btree("file_id");
|
||
|
|
|
||
|
|
CREATE INDEX file_discord_message_server_idx ON file_discord_message_relationships USING btree("server");
|
||
|
|
|
||
|
|
CREATE INDEX file_discord_message_channel_idx ON file_discord_message_relationships USING btree("channel");
|
||
|
|
|
||
|
|
CREATE INDEX file_discord_message_id_idx ON file_discord_message_relationships USING btree("id");
|
||
|
|
|
||
|
|
CREATE INDEX file_discord_message_contributor_id_idx ON file_discord_message_relationships USING btree("contributor_id");
|
||
|
|
|
||
|
|
CREATE INDEX file_server_relationships_remote_path_idx ON file_server_relationships USING btree(remote_path);
|