49 lines
1.5 KiB
SQL
49 lines
1.5 KiB
SQL
-- Movie Select schema
|
|
-- Run once: mysql -u serve -p serve < setup-db.sql
|
|
|
|
CREATE TABLE IF NOT EXISTS rounds (
|
|
uuid CHAR(36) PRIMARY KEY,
|
|
phase TINYINT NOT NULL DEFAULT 1,
|
|
setup_done TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT NOW(),
|
|
updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS round_users (
|
|
round_uuid CHAR(36) NOT NULL,
|
|
name VARCHAR(30) NOT NULL,
|
|
done_phase1 TINYINT(1) NOT NULL DEFAULT 0,
|
|
done_phase2 TINYINT(1) NOT NULL DEFAULT 0,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (round_uuid, name),
|
|
FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS movies (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
round_uuid CHAR(36) NOT NULL,
|
|
title VARCHAR(60) NOT NULL,
|
|
added_by VARCHAR(30) NOT NULL,
|
|
added_at DATETIME NOT NULL DEFAULT NOW(),
|
|
UNIQUE KEY uq_round_title (round_uuid, title),
|
|
FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
round_uuid CHAR(36) NOT NULL,
|
|
user_name VARCHAR(30) NOT NULL,
|
|
movie_title VARCHAR(60) NOT NULL,
|
|
rating TINYINT NOT NULL,
|
|
PRIMARY KEY (round_uuid, user_name, movie_title),
|
|
FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS round_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
round_uuid CHAR(36) NOT NULL,
|
|
winner VARCHAR(60),
|
|
movies_json TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT NOW(),
|
|
FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
|
|
);
|