48 lines
1.6 KiB
SQL
48 lines
1.6 KiB
SQL
-- Photobooth database setup
|
|
|
|
CREATE DATABASE IF NOT EXISTS `photobooth`
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_0900_ai_ci;
|
|
|
|
CREATE USER IF NOT EXISTS 'photobooth_app'@'%' IDENTIFIED BY 'photobooth_app_password_placeholder';
|
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON `photobooth`.* TO 'photobooth_app'@'%';
|
|
FLUSH PRIVILEGES;
|
|
|
|
USE `photobooth`;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id CHAR(36) PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(120) NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_users_email (email)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS refresh_tokens (
|
|
id CHAR(36) PRIMARY KEY,
|
|
user_id CHAR(36) NOT NULL,
|
|
token_hash VARCHAR(255) NOT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
revoked_at DATETIME NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_refresh_token_hash (token_hash),
|
|
KEY idx_refresh_user (user_id),
|
|
CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS media (
|
|
id CHAR(36) PRIMARY KEY,
|
|
user_id CHAR(36) NOT NULL,
|
|
type ENUM('shot','strip') NOT NULL,
|
|
url VARCHAR(2048) NOT NULL,
|
|
thumb_url VARCHAR(2048) NULL,
|
|
theme_id VARCHAR(64) NULL,
|
|
meta JSON NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY idx_media_user_created (user_id, created_at),
|
|
KEY idx_media_type (type),
|
|
CONSTRAINT fk_media_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|