File size: 1,780 Bytes
03498c5 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS videos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
channel TEXT NOT NULL,
views INTEGER NOT NULL DEFAULT 0,
description TEXT,
duration_seconds INTEGER NOT NULL DEFAULT 0,
duration_text TEXT NOT NULL,
category TEXT NOT NULL,
youtube_id TEXT NOT NULL,
upload_date TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS likes (
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
value INTEGER NOT NULL CHECK (value IN (-1, 1)),
PRIMARY KEY (user_id, video_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS watch_later (
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
added_at TEXT NOT NULL,
PRIMARY KEY (user_id, video_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS history (
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
watched_at TEXT NOT NULL,
PRIMARY KEY (user_id, video_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
video_id INTEGER NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);
|