· 7 years ago · Feb 24, 2019, 05:22 PM
1create table if not exists domains
2(
3 id int(16) auto_increment
4 primary key,
5 domain varchar(32) not null,
6 default_permission int(1) default 0 not null,
7 constraint unique_domain
8 unique (domain)
9);
10
11create index if not exists index_permissions
12 on domains (default_permission);
13
14create table if not exists labels
15(
16 id int auto_increment
17 primary key,
18 label varchar(32) not null,
19 constraint unique_label
20 unique (label)
21);
22
23create table if not exists languages
24(
25 id int(16) auto_increment
26 primary key,
27 language varchar(16) not null,
28 home varchar(128) not null,
29 categories varchar(128) not null,
30 examination varchar(128) not null,
31 progress varchar(128) not null,
32 login varchar(128) not null,
33 register varchar(128) not null
34);
35
36create index if not exists index_language
37 on languages (language);
38
39create table if not exists users
40(
41 id int(16) auto_increment
42 primary key,
43 email varchar(32) not null,
44 email_verified bit default b'0' not null,
45 password varchar(512) not null,
46 firstname varchar(16) not null,
47 lastname varchar(16) not null,
48 full_name varchar(64) not null,
49 permission int(1) default 0 not null,
50 prolile_picture varchar(128) not null,
51 constraint index_email
52 unique (email),
53 constraint index_permissions
54 unique (permission)
55);
56
57create table if not exists courses
58(
59 id int(16) auto_increment
60 primary key,
61 title varchar(32) not null,
62 holder_id int(16) not null,
63 holder varchar(32) not null,
64 labels varchar(64) not null comment 'Labels need to been seperated with a " , "',
65 episodes int(4) not null,
66 total_lenght time not null,
67 language varchar(16) not null,
68 description text not null,
69 thumbnail varchar(128) not null,
70 constraint constraint_course_holder_full_name
71 foreign key (holder) references users (full_name)
72 on update cascade on delete cascade,
73 constraint constraint_course_holder_id
74 foreign key (holder_id) references users (id)
75 on update cascade on delete cascade,
76 constraint constraint_language
77 foreign key (language) references languages (language)
78 on update cascade on delete cascade
79);
80
81create index if not exists index_course_holder
82 on courses (holder);
83
84create index if not exists index_course_holder_id
85 on courses (holder_id);
86
87create index if not exists index_course_id
88 on courses (id);
89
90create index if not exists index_course_title
91 on courses (title);
92
93create index if not exists index_language
94 on courses (language);
95
96create table if not exists permissions
97(
98 id int(16) auto_increment
99 primary key,
100 permission int(1) not null,
101 name varchar(32) not null,
102 constraint constraint_permission_domains
103 foreign key (permission) references domains (default_permission)
104 on update cascade on delete cascade,
105 constraint constraint_permission_user
106 foreign key (permission) references users (permission)
107 on update cascade on delete cascade
108);
109
110create index if not exists index_permission
111 on permissions (permission);
112
113create table if not exists user_settings
114(
115 id int(16) auto_increment
116 primary key,
117 user_id int(16) not null,
118 user_name varchar(64) not null,
119 language varchar(16) not null,
120 video_history bit default b'1' not null,
121 email_notification bit default b'1' not null,
122 constraint constraint_settings_user_id
123 foreign key (user_id) references users (id)
124 on update cascade on delete cascade,
125 constraint constraint_settings_user_name
126 foreign key (user_name) references users (full_name)
127 on update cascade on delete cascade,
128 constraint constraint_user_language
129 foreign key (language) references languages (language)
130 on update cascade on delete cascade
131);
132
133create index if not exists ` index_user_id`
134 on user_settings (user_id);
135
136create index if not exists index_language
137 on user_settings (language);
138
139create index if not exists index_user_name
140 on user_settings (user_name);
141
142create index if not exists index_full_name
143 on users (full_name);
144
145create index if not exists index_user_id
146 on users (id);
147
148create table if not exists videos
149(
150 id int(16) auto_increment
151 primary key,
152 link varchar(64) not null,
153 title varchar(32) not null,
154 uploader_id int(16) not null,
155 uploader varchar(32) not null,
156 labels varchar(64) not null comment 'Labels need to been seperated with a " , "',
157 course_id int(16) not null,
158 course varchar(16) not null,
159 lenght time not null,
160 language varchar(16) not null,
161 description text not null,
162 thumbnail varchar(128) not null,
163 date datetime default CURRENT_TIMESTAMP not null,
164 constraint index_course_id
165 unique (course_id),
166 constraint constraint_video_language
167 foreign key (language) references languages (language)
168 on update cascade on delete cascade,
169 constraint fk_course_id
170 foreign key (course_id) references courses (id)
171 on update cascade on delete cascade,
172 constraint fk_course_title
173 foreign key (course) references courses (title)
174 on update cascade on delete cascade,
175 constraint fk_uploader_full_name
176 foreign key (uploader) references users (full_name)
177 on update cascade on delete cascade,
178 constraint fk_uploader_id
179 foreign key (uploader_id) references users (id)
180 on update cascade on delete cascade
181);
182
183create index if not exists index_course_title
184 on videos (course);
185
186create index if not exists index_language
187 on videos (language);
188
189create index if not exists index_link
190 on videos (link);
191
192create index if not exists index_uploader
193 on videos (uploader);
194
195create index if not exists index_uploader_id
196 on videos (uploader_id);
197
198create index if not exists index_video_id
199 on videos (id);
200
201create index if not exists index_video_title
202 on videos (title);
203
204create table if not exists videos_played
205(
206 id int(16) auto_increment
207 primary key,
208 video_id int(16) not null,
209 video_link varchar(64) not null,
210 video_title varchar(32) not null,
211 user_id int(16) not null,
212 user_name varchar(64) not null,
213 watchtime time not null,
214 date date not null,
215 constraint constraint_user_id
216 foreign key (user_id) references users (id)
217 on update cascade on delete cascade,
218 constraint constraint_user_name
219 foreign key (user_name) references users (full_name)
220 on update cascade on delete cascade,
221 constraint constraint_video_id
222 foreign key (video_id) references videos (id)
223 on update cascade on delete cascade,
224 constraint constraint_video_link
225 foreign key (video_link) references videos (link)
226 on update cascade on delete cascade,
227 constraint constraint_video_title
228 foreign key (video_title) references videos (title)
229 on update cascade on delete cascade
230);
231
232create index if not exists index_user_id
233 on videos_played (user_id);
234
235create index if not exists index_user_name
236 on videos_played (user_name);
237
238create index if not exists index_video_id
239 on videos_played (video_id);
240
241create index if not exists index_video_link
242 on videos_played (video_link);
243
244create index if not exists index_video_title
245 on videos_played (video_title);
246
247create table if not exists website_settings
248(
249 id int(16) auto_increment
250 primary key,
251 default_language varchar(16) default 'en_US' not null,
252 login_required bit not null,
253 domains_enabled bit not null,
254 constraint constraint_website_language
255 foreign key (default_language) references languages (language)
256 on update cascade on delete cascade
257);
258
259create index if not exists index_language
260 on website_settings (default_language);