72 lines
3.1 KiB
SQL
72 lines
3.1 KiB
SQL
-- One-Time File Share — database schema
|
|
-- Run this once against your MySQL database, e.g.:
|
|
-- mysql -u root -p < schema.sql
|
|
-- No auto-migration happens at app startup.
|
|
--
|
|
-- This creates the database and selects it. If you already created the database
|
|
-- yourself (and your DB_NAME differs from `fileshare`), delete the two lines below
|
|
-- and instead pick the database first (in MySQL Workbench: double-click it in the
|
|
-- SCHEMAS sidebar, or run `USE your_db_name;`).
|
|
|
|
CREATE DATABASE IF NOT EXISTS fileshare CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE fileshare;
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email_verified TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_admin TINYINT(1) NOT NULL DEFAULT 0,
|
|
disabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
verify_token CHAR(64) NULL,
|
|
verify_sent_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_users_username (username),
|
|
UNIQUE KEY uq_users_email (email),
|
|
KEY idx_users_verify_token (verify_token)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS files (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
original_name VARCHAR(512) NOT NULL,
|
|
stored_name CHAR(36) NOT NULL,
|
|
ext VARCHAR(32) NULL,
|
|
mime VARCHAR(255) NULL,
|
|
size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
is_bundle TINYINT(1) NOT NULL DEFAULT 0,
|
|
compressed TINYINT(1) NOT NULL DEFAULT 0,
|
|
compressing TINYINT(1) NOT NULL DEFAULT 0,
|
|
av_status ENUM('pending','clean','infected','skipped') NOT NULL DEFAULT 'pending',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_files_stored_name (stored_name),
|
|
KEY idx_files_user (user_id),
|
|
CONSTRAINT fk_files_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS download_links (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
file_id BIGINT UNSIGNED NOT NULL,
|
|
token CHAR(43) NOT NULL,
|
|
used TINYINT(1) NOT NULL DEFAULT 0,
|
|
used_at DATETIME NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_links_token (token),
|
|
KEY idx_links_file (file_id),
|
|
CONSTRAINT fk_links_file FOREIGN KEY (file_id) REFERENCES files (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Session store table required by express-mysql-session.
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
session_id VARCHAR(128) COLLATE utf8mb4_bin NOT NULL,
|
|
expires INT UNSIGNED NOT NULL,
|
|
data MEDIUMTEXT COLLATE utf8mb4_bin,
|
|
PRIMARY KEY (session_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|