-
-
Notifications
You must be signed in to change notification settings - Fork 0
/
000004_create_videos_table.up.sql
32 lines (29 loc) · 1.31 KB
/
000004_create_videos_table.up.sql
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
CREATE TABLE IF NOT EXISTS videos
(
video_id UUID NOT NULL,
course_id UUID NOT NULL,
index INT NOT NULL,
name TEXT NOT NULL,
description TEXT NOT NULL,
free BOOLEAN NOT NULL,
url TEXT NOT NULL,
image_url TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
version INT NOT NULL DEFAULT 1,
PRIMARY KEY (video_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
UNIQUE(course_id, index)
);
CREATE TABLE IF NOT EXISTS videos_progress
(
video_id UUID NOT NULL,
user_id UUID NOT NULL,
progress INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CHECK (progress BETWEEN 0 AND 100),
PRIMARY KEY (video_id, user_id),
FOREIGN KEY (video_id) REFERENCES videos(video_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);