cc34e69524
* Create base shared instance migration and initial routes * Fix build * Add version uploads * Add permissions field for shared instance users * Actually use permissions field * Add "public" flag to shared instances that allow GETing them without authorization * Add the ability to get and list shared instance versions * Add the ability to delete shared instance versions * Fix build after merge * Secured file hosting (#3784) * Remove Backblaze-specific file-hosting backend * Added S3_USES_PATH_STYLE_BUCKETS * Remove unused file_id parameter from delete_file_version * Add support for separate public and private buckets in labrinth::file_hosting * Rename delete_file_version to delete_file * Add (untested) get_url_for_private_file * Remove url field from shared instance routes * Remove url field from shared instance routes * Use private bucket for shared instance versions * Make S3 environment variables fully separate between public and private buckets * Change file host expiry for shared instances to 180 seconds * Fix lint * Merge shared instance migrations into a single migration * Replace shared instance owners with Ghost instead of deleting the instance
44 lines
1.7 KiB
SQL
44 lines
1.7 KiB
SQL
CREATE TABLE shared_instances (
|
|
id BIGINT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
owner_id BIGINT NOT NULL REFERENCES users,
|
|
current_version_id BIGINT NULL,
|
|
public BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
CREATE INDEX shared_instances_owner_id ON shared_instances(owner_id);
|
|
|
|
CREATE TABLE shared_instance_users (
|
|
user_id BIGINT NOT NULL REFERENCES users ON DELETE CASCADE,
|
|
shared_instance_id BIGINT NOT NULL REFERENCES shared_instances ON DELETE CASCADE,
|
|
permissions BIGINT NOT NULL DEFAULT 0,
|
|
|
|
PRIMARY KEY (user_id, shared_instance_id)
|
|
);
|
|
|
|
CREATE TABLE shared_instance_invited_users (
|
|
id BIGINT PRIMARY KEY,
|
|
shared_instance_id BIGINT NOT NULL REFERENCES shared_instances ON DELETE CASCADE,
|
|
invited_user_id BIGINT NULL REFERENCES users ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX shared_instance_invited_users_shared_instance_id ON shared_instance_invited_users(shared_instance_id);
|
|
CREATE INDEX shared_instance_invited_users_invited_user_id ON shared_instance_invited_users(invited_user_id);
|
|
|
|
CREATE TABLE shared_instance_invite_links (
|
|
id BIGINT PRIMARY KEY,
|
|
shared_instance_id BIGINT NOT NULL REFERENCES shared_instances ON DELETE CASCADE,
|
|
expiration timestamptz NULL,
|
|
remaining_uses BIGINT CHECK ( remaining_uses >= 0 ) NULL
|
|
);
|
|
CREATE INDEX shared_instance_invite_links_shared_instance_id ON shared_instance_invite_links(shared_instance_id);
|
|
|
|
CREATE TABLE shared_instance_versions (
|
|
id BIGINT PRIMARY KEY,
|
|
shared_instance_id BIGINT NOT NULL REFERENCES shared_instances ON DELETE CASCADE,
|
|
size BIGINT NOT NULL,
|
|
sha512 bytea NOT NULL,
|
|
created timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
ALTER TABLE shared_instances
|
|
ADD FOREIGN KEY (current_version_id) REFERENCES shared_instance_versions(id) ON DELETE SET NULL;
|