· 6 years ago · Mar 05, 2019, 09:12 PM
1create table if not exists labels
2(
3 id int auto_increment
4 primary key,
5 label varchar(32) not null,
6 constraint unique_label
7 unique (label)
8);
9
10create table if not exists languages
11(
12 id int(16) auto_increment
13 primary key,
14 language varchar(16) not null,
15 home varchar(128) not null,
16 categories varchar(128) not null,
17 examination varchar(128) not null,
18 progress varchar(128) not null,
19 login varchar(128) not null,
20 logout varchar(128) not null,
21 register varchar(128) not null,
22 recent_video varchar(128) not null,
23 title varchar(128) not null,
24 upload_by varchar(128) not null,
25 labels varchar(128) not null,
26 course varchar(128) not null,
27 length varchar(128) not null,
28 uploaded_on varchar(128) not null,
29 view_course varchar(128) not null,
30 view_video varchar(128) not null,
31 profile varchar(128) not null,
32 dark_mode varchar(128) not null,
33 remember_me varchar(128) not null,
34 episodes varchar(128) not null,
35 holder varchar(128) not null,
36 join_course varchar(128) not null,
37 video_history varchar(128) not null,
38 default_language varchar(128) not null,
39 domains_enabled varchar(128) not null,
40 login_required varchar(128) not null,
41 email_notification varchar(128) not null
42);
43
44create index if not exists index_language
45 on languages (language);
46
47create index if not exists index_language_id
48 on languages (id);
49
50create table if not exists permissions
51(
52 id int(16) auto_increment
53 primary key,
54 permission int(1) not null,
55 name varchar(32) not null,
56 constraint index_permission
57 unique (permission)
58);
59
60create table if not exists domains
61(
62 id int(16) auto_increment
63 primary key,
64 domain varchar(32) not null,
65 default_permission int(1) default 0 not null,
66 constraint unique_domain
67 unique (domain),
68 constraint constraint_permissions_domains
69 foreign key (default_permission) references permissions (permission)
70 on update cascade on delete cascade
71);
72
73create index if not exists index_permissions
74 on domains (default_permission);
75
76create table if not exists users
77(
78 id int(16) auto_increment
79 primary key,
80 e_mail varchar(64) not null,
81 email_verified bit default b'0' not null,
82 passcode varchar(512) not null,
83 firstname varchar(16) not null,
84 lastname varchar(16) not null,
85 permission int(1) default 0 not null,
86 profile_picture varchar(128) default 'default-avatar.png' not null comment 'default-avatar.png',
87 constraint index_email
88 unique (e_mail),
89 constraint constraint_permissions
90 foreign key (permission) references permissions (permission)
91 on update cascade on delete cascade
92);
93
94create table if not exists courses
95(
96 id int(16) auto_increment
97 primary key,
98 title varchar(32) not null,
99 holder_id int(16) not null,
100 labels varchar(64) not null comment 'Labels need to been seperated with a " , " ',
101 episodes int(4) not null,
102 total_length time not null,
103 language_id int(16) not null,
104 description text not null,
105 thumbnail varchar(128) not null,
106 date date null,
107 constraint constraint_course_holder_id
108 foreign key (holder_id) references users (id)
109 on update cascade on delete cascade,
110 constraint constraint_language_id_coursses
111 foreign key (language_id) references languages (id)
112 on update cascade on delete cascade
113);
114
115create index if not exists index_course_holder_id
116 on courses (holder_id);
117
118create index if not exists index_course_id
119 on courses (id);
120
121create index if not exists index_course_title
122 on courses (title);
123
124create index if not exists index_coursse_episodes
125 on courses (id);
126
127create index if not exists index_language_id
128 on courses (language_id);
129
130create table if not exists email_verify
131(
132 id int(16) auto_increment
133 primary key,
134 e_mail varchar(64) not null,
135 code varchar(32) not null,
136 constraint constraint_email_verify
137 foreign key (e_mail) references users (e_mail)
138 on update cascade on delete cascade
139);
140
141create index if not exists index_email
142 on email_verify (e_mail);
143
144create table if not exists password_recovery
145(
146 id int(16) auto_increment
147 primary key,
148 e_mail varchar(64) not null,
149 code varchar(32) not null,
150 constraint constraint_email_recovery
151 foreign key (e_mail) references users (e_mail)
152 on update cascade on delete cascade
153);
154
155create index if not exists index_email
156 on password_recovery (e_mail);
157
158create table if not exists user_settings
159(
160 id int(16) auto_increment
161 primary key,
162 user_id int(16) not null,
163 language_id int(16) default 1 not null,
164 video_history bit default b'1' not null,
165 email_notification bit default b'1' not null,
166 constraint constraint_language_id_user_settings
167 foreign key (language_id) references languages (id)
168 on update cascade on delete cascade,
169 constraint constraint_settings_user_id
170 foreign key (user_id) references users (id)
171 on update cascade on delete cascade
172);
173
174create index if not exists ` index_user_id`
175 on user_settings (user_id);
176
177create index if not exists index_language_id_user_settings
178 on user_settings (language_id);
179
180create index if not exists index_permissions
181 on users (permission);
182
183create index if not exists index_user_id
184 on users (id);
185
186create table if not exists videos
187(
188 id int(16) auto_increment
189 primary key,
190 link varchar(64) not null,
191 title varchar(32) not null,
192 uploader_id int(16) not null,
193 labels varchar(64) not null comment 'Labels need to been seperated with a " , "',
194 course_id int(16) not null,
195 episode int(4) not null,
196 length time not null,
197 language_id int(16) not null,
198 description text not null,
199 thumbnail varchar(128) not null,
200 date timestamp default CURRENT_TIMESTAMP not null,
201 constraint constraint_course_id_videos
202 foreign key (course_id) references courses (id)
203 on update cascade on delete cascade,
204 constraint constraint_language_id_videos
205 foreign key (language_id) references languages (id)
206 on update cascade on delete cascade,
207 constraint constraint_uploader_id
208 foreign key (uploader_id) references users (id)
209 on update cascade on delete cascade
210);
211
212create table if not exists user_courses
213(
214 id int(16) auto_increment
215 primary key,
216 user_id int(16) not null,
217 course_id int(16) not null,
218 video_episode int(4) not null,
219 constraint constraint_course_id_user_courses
220 foreign key (course_id) references courses (id)
221 on update cascade on delete cascade,
222 constraint constraint_user_id_user_courses
223 foreign key (user_id) references users (id)
224 on update cascade on delete cascade,
225 constraint constraint_video_episode_user_courses
226 foreign key (video_episode) references videos (episode)
227 on update cascade on delete cascade
228);
229
230create index if not exists index_course_id
231 on user_courses (course_id);
232
233create index if not exists index_user_id
234 on user_courses (user_id);
235
236create index if not exists index_video_episode
237 on user_courses (video_episode);
238
239create index if not exists index_course_id
240 on videos (course_id);
241
242create index if not exists index_episode
243 on videos (episode);
244
245create index if not exists index_language_id
246 on videos (language_id);
247
248create index if not exists index_link
249 on videos (link);
250
251create index if not exists index_uploader_id
252 on videos (uploader_id);
253
254create index if not exists index_video_id
255 on videos (id);
256
257create index if not exists index_video_title
258 on videos (title);
259
260create table if not exists videos_played
261(
262 id int(16) auto_increment
263 primary key,
264 video_id int(16) not null,
265 user_id int(16) not null,
266 watchtime time not null,
267 date timestamp default CURRENT_TIMESTAMP not null,
268 constraint constraint_user_id
269 foreign key (user_id) references users (id)
270 on update cascade on delete cascade,
271 constraint constraint_video_id
272 foreign key (video_id) references videos (id)
273 on update cascade on delete cascade
274);
275
276create index if not exists index_user_id
277 on videos_played (user_id);
278
279create index if not exists index_video_id
280 on videos_played (video_id);
281
282create table if not exists website_settings
283(
284 id int(16) auto_increment
285 primary key,
286 default_language_id int(16) not null,
287 login_required bit not null,
288 domains_enabled bit not null,
289 constraint constraint_language_id_website_settings
290 foreign key (default_language_id) references languages (id)
291 on update cascade on delete cascade
292);
293
294create index if not exists index_language_id
295 on website_settings (default_language_id);