-
Notifications
You must be signed in to change notification settings - Fork 4
/
createdatabase.sql
234 lines (213 loc) · 8.6 KB
/
createdatabase.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
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
create table roaddanger.countries
(
id char(2) not null
primary key,
name varchar(50) null,
domain varchar(50) null,
options text null,
defaultlanguageid char(2) null,
constraint countries_id_uindex
unique (id)
);
create table roaddanger.languages
(
id char(2) not null
primary key,
name varchar(50) null,
translations mediumtext null,
constraint languages_id_uindex
unique (id)
);
create table roaddanger.logins
(
id int auto_increment
primary key,
userid int null,
tokenhash varchar(60) null,
lastlogin timestamp null
);
create table roaddanger.logs
(
id int auto_increment
primary key,
userid int null,
timestamp timestamp default current_timestamp() null,
level tinyint null,
ip varchar(45) null,
info varchar(255) not null,
constraint logs_id_uindex
unique (id)
);
create table roaddanger.longtexts
(
id char(100) not null,
language_id char(2) default 'en' not null,
content text null,
primary key (id, language_id)
);
create table roaddanger.questionnaires
(
id int auto_increment
primary key,
active smallint default 0 null,
type smallint default 0 null comment '0: standard
1: Bechdel test',
country_id char(2) null,
title varchar(100) null,
public tinyint(1) default 0 null
);
create table roaddanger.questions
(
id int auto_increment
primary key,
text varchar(200) null,
active tinyint(1) default 0 null,
question_order smallint null,
explanation varchar(200) null
);
create table roaddanger.questionnaire_questions
(
questionnaire_id int not null,
question_id int not null,
question_order smallint null,
primary key (questionnaire_id, question_id),
constraint quest_questions_questionnaires_id_fk
foreign key (questionnaire_id) references roaddanger.questionnaires (id)
on update cascade on delete cascade,
constraint quest_questions_questions_id_fk
foreign key (question_id) references roaddanger.questions (id)
on update cascade
);
create table roaddanger.users
(
id int auto_increment
primary key,
email varchar(254) not null,
firstname varchar(100) null,
lastname varchar(100) null,
language char(2) null,
countryid char(2) null,
registrationtime timestamp default current_timestamp() not null,
passwordhash varchar(60) null,
passwordrecoveryid varchar(16) null,
passwordrecoverytime timestamp null,
permission tinyint default 0 null,
lastactive timestamp default current_timestamp() not null,
constraint users_email_uindex
unique (email),
constraint users_id_uindex
unique (id),
constraint users_FK
foreign key (language) references roaddanger.languages (id)
on update cascade on delete set null,
constraint users_FK_country
foreign key (countryid) references roaddanger.countries (id)
on update cascade on delete set null
)
comment 'permission: 0=helper; 1=admin; 2=moderator';
create table roaddanger.crashes
(
id int auto_increment
primary key,
userid int null,
awaitingmoderation tinyint(1) default 1 null,
createtime timestamp default current_timestamp() not null,
updatetime timestamp default current_timestamp() not null,
date date null,
streamtopuserid int null,
streamtoptype smallint null,
title varchar(500) not null,
text varchar(500) null,
countryid char(2) null,
location point null,
latitude decimal(9, 6) null,
longitude decimal(9, 6) null,
tree tinyint(1) default 0 null,
trafficjam tinyint(1) default 0 null,
unilateral tinyint(1) null,
hitrun tinyint(1) default 0 null,
website varchar(1000) null,
pet tinyint(1) default 0 null,
streamdatetime timestamp default current_timestamp() not null,
constraint posts_id_uindex
unique (id),
constraint crashes_countries_id_fk
foreign key (countryid) references roaddanger.countries (id)
on update cascade on delete set null,
constraint posts___fk_user
foreign key (userid) references roaddanger.users (id)
on update cascade on delete cascade
)
comment 'streamtoptype: 1: edited, 2: article added, 3: placed on top';
create table roaddanger.articles
(
id int auto_increment
primary key,
crashid int null,
userid int null,
awaitingmoderation tinyint(1) default 1 null,
createtime timestamp default current_timestamp() null,
streamdatetime timestamp default current_timestamp() not null,
publishedtime timestamp default '0000-00-00 00:00:00' not null,
title varchar(500) not null,
text varchar(500) not null,
alltext varchar(10000) default '' null,
url varchar(1000) not null,
urlimage varchar(1000) not null,
sitename varchar(200) not null,
constraint articles_id_uindex
unique (id),
constraint articles___fk_crashes
foreign key (crashid) references roaddanger.crashes (id)
on update cascade on delete cascade,
constraint articles___fk_user
foreign key (userid) references roaddanger.users (id)
on update cascade on delete cascade
);
create table roaddanger.answers
(
questionid int not null,
articleid int not null,
answer tinyint(1) null,
explanation varchar(200) null,
constraint answers_pk
unique (questionid, articleid),
constraint answers_articles_id_fk
foreign key (articleid) references roaddanger.articles (id)
on update cascade on delete cascade,
constraint answers_questions_id_fk
foreign key (questionid) references roaddanger.questions (id)
on update cascade on delete cascade
);
create index articles__index_crashid
on roaddanger.articles (crashid);
create fulltext index title
on roaddanger.articles (title, text);
create index crashes__date_streamdate_index
on roaddanger.crashes (date, streamdatetime);
create index crashes__index_date
on roaddanger.crashes (date);
create index crashes__index_streamdatetime
on roaddanger.crashes (streamdatetime);
create fulltext index title
on roaddanger.crashes (title, text);
create table roaddanger.crashpersons
(
id int auto_increment
primary key,
crashid int not null,
transportationmode smallint default 0 null,
health smallint null,
child smallint null,
underinfluence tinyint(1) null,
hitrun tinyint(1) null,
groupid int null,
constraint crashpersons___fkcrashes
foreign key (crashid) references roaddanger.crashes (id)
on update cascade on delete cascade
)
comment 'health: unknown: 0, unharmed: 1, injured: 2, dead: 3 | transportationmode: unknown: 0, pedestrian: 1, bicycle: 2, scooter: 3, motorcycle: 4, car: 5, taxi: 6, emergencyVehicle: 7, deliveryVan: 8, tractor: 9, bus: 10, tram: 11, truck: 12, train: 13, wheelchair: 14, mopedCar: 15';
create index crashpersons___fkcrash
on roaddanger.crashpersons (crashid);
create index crashpersons___fkgroup
on roaddanger.crashpersons (groupid);