-
Notifications
You must be signed in to change notification settings - Fork 0
/
base.sql
269 lines (247 loc) · 11.1 KB
/
base.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
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
/*DROP DATABASE event_booking;*/
CREATE DATABASE event_booking;
USE event_booking;
/*--------------------------------------------------------------------*\
TABLE MODULES
\*--------------------------------------------------------------------*/
CREATE TABLE modules
(
id INT PRIMARY KEY AUTO_INCREMENT,
module VARCHAR(100),
description TEXT
);
INSERT INTO modules (module, description) VALUES ('Keynote', 'Presentation des nouveautées d\'Apple');
INSERT INTO modules (module, description) VALUES ('Xcode', 'Presentation du logiciel Xcode');
INSERT INTO modules (module, description) VALUES ('iOS', 'Presentation du nouvel os pour les iDevices');
INSERT INTO modules (module, description)
VALUES ('macOS', 'Presentation du nouvel os pour les Macintosh');
INSERT INTO modules (module, description)
VALUES ('watchOS', 'Presentation du nouvel os pour les Apple Watch');
INSERT INTO modules (module, description) VALUES ('tvOS', 'Presentation du nouvel os pour les Apple TV');
/*--------------------------------------------------------------------*\
TABLE EVENTS
\*--------------------------------------------------------------------*/
CREATE TABLE events
(
id INT PRIMARY KEY AUTO_INCREMENT,
event VARCHAR(100),
address VARCHAR(255),
description TEXT,
eventDate DATE,
duration INT,
nbSlotsPerDay INT,
nbSpeakerMaxByModule INT,
bookingOpen BOOLEAN DEFAULT FALSE
);
INSERT INTO events (event, address, eventDate, duration, nbSlotsPerDay, nbSpeakerMaxByModule) VALUE (
'WWDC16',
'Cupertino',
'2016/09/09',
3,
3,
2
);
INSERT INTO events (event, address, description, eventDate, duration, nbSlotsPerDay, nbSpeakerMaxByModule, bookingOpen)
VALUE (
'WWDC17',
'Cupertino',
'Conférence Mondiale 2017 pour les développeurs iOS et MacOs ',
'2017/09/09',
2,
3,
2,
TRUE
);
INSERT INTO events (event, address, description, eventDate, duration, nbSlotsPerDay, nbSpeakerMaxByModule)
VALUE (
'WWDC18',
'Cupertino',
'Conférence Mondiale 2018 pour les développeurs iOS et MacOs ',
'2018/09/09',
2,
3,
2
);
/*--------------------------------------------------------------------*\
TABLE HALLS
\*--------------------------------------------------------------------*/
CREATE TABLE halls
(
id INT PRIMARY KEY AUTO_INCREMENT,
hall VARCHAR(30),
building VARCHAR(60),
picture TEXT,
capacity INT
);
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Main', 500, 'A1-001', 'img/halls/main.jpg');
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Hall1', 100, 'A2-001', 'img/halls/hall1.jpg');
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Hall2', 50, 'A2-001', 'img/halls/hall2.jpg');
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Hall3', 50, 'A2-002', 'img/halls/hall3.jpg');
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Hall4', 50, 'A3-001', 'img/halls/hall4.jpg');
INSERT INTO halls (hall, capacity, building, picture)
VALUES ('Hall5', 2, 'A3-002', 'img/halls/hall5.jpg');
/*--------------------------------------------------------------------*\
TABLE SLOTS
\*--------------------------------------------------------------------*/
CREATE TABLE slots
(
id INT PRIMARY KEY AUTO_INCREMENT,
idEvent INT,
slot DATETIME,
CONSTRAINT session_event_id_fk FOREIGN KEY (idEvent) REFERENCES events (id)
);
INSERT INTO slots (idEvent, slot)
VALUES (2, '2017-09-09 10:00:00');
INSERT INTO slots (idEvent, slot)
VALUES (2, '2017-09-09 14:00:00');
INSERT INTO slots (idEvent, slot)
VALUES (2, '2017-09-09 16:00:00');
INSERT INTO slots (idEvent, slot)
VALUES (2, '2017-09-10 10:00:00');
/*--------------------------------------------------------------------*\
TABLE ROLES
\*--------------------------------------------------------------------*/
CREATE TABLE roles
(
id INT PRIMARY KEY AUTO_INCREMENT,
role VARCHAR(30)
);
CREATE UNIQUE INDEX roles_role_uindex
ON roles (role);
INSERT INTO roles (role) VALUES ('admin');
INSERT INTO roles (role) VALUES ('contributeur');
INSERT INTO roles (role) VALUES ('membre');
/*--------------------------------------------------------------------*\
TABLE USERS
\*--------------------------------------------------------------------*/
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
email VARCHAR(255),
password VARCHAR(255),
name VARCHAR(30),
surname VARCHAR(30),
idRole INT,
themeChoice VARCHAR(30) DEFAULT 'main1.css',
lastActiveConnection TIMESTAMP NULL,
CONSTRAINT membres_role_id_fk FOREIGN KEY (idRole) REFERENCES roles (id)
);
INSERT INTO users (name, surname, email, username, idRole, password) VALUES
(' ', 'Administrateur', '[email protected]', 'adm', 1,
'$2y$10$AHhvaGCuVR0evH6kQb7Fku.kznlFP2PKo2s4xuEKpY4T.Owh/9sHK');
INSERT INTO users (name, surname, email, username, idRole, password)
VALUES (' ', 'Speaker', '[email protected]', 'spk', 2, '$2y$10$P.LM.eSrrZLJoEJXwbDhROQf3wmWa26PEDgOb9yxE75TIFywnJ4TO');
INSERT INTO users (name, surname, email, username, idRole, password)
VALUES (' ', 'User', '[email protected]', 'usr', 3, '$2y$10$NnxHSgWMSLdLyhGIvsI30uqcxnqcBgRNTs8UrJmaV5uvQRqpaB7mu');
INSERT INTO users (name, surname, email, username, idRole, password) VALUES
('BOURNONVILLE', 'Julien', '[email protected]', 'jbo', 3,
'$2y$10$U3swoaSgKPAgFR5xmW/3xOqGXFdQC3RolhwkFffx1c/lm9CVlGCZO');
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jharvey0', '[email protected]', 'Harvey', 'Judith', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('tmiller1', '[email protected]', 'Miller', 'Teresa', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jmccoy2', '[email protected]', 'Mccoy', 'Joyce', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jrichards3', '[email protected]', 'Richards', 'Joyce', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('mperry4', '[email protected]', 'Perry', 'Mark', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jholmes5', '[email protected]', 'Holmes', 'Jason', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jcole6', '[email protected]', 'Cole', 'Jimmy', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('kcarr7', '[email protected]', 'Carr', 'Kathleen', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('ahughes8', '[email protected]', 'Hughes', 'Aaron', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('tpierce9', '[email protected]', 'Pierce', 'Tina', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jfranklina', '[email protected]', 'Franklin', 'Juan', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('rsimsb', '[email protected]', 'Sims', 'Roger', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('kramosc', '[email protected]', 'Ramos', 'Keith', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('rramosd', '[email protected]', 'Ramos', 'Roy', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jellise', '[email protected]', 'Ellis', 'Joan', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('lreynoldsf', '[email protected]', 'Reynolds', 'Lawrence', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('rwashingtong', '[email protected]', 'Washington', 'Richard', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('brussellh', '[email protected]', 'Russell', 'Bonnie', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('hwheeleri', '[email protected]', 'Wheeler', 'Howard', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('pblackj', '[email protected]', 'Black', 'Phyllis', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('lcolek', '[email protected]', 'Cole', 'Lillian', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('blongl', '[email protected]', 'Long', 'Benjamin', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('jporterm', '[email protected]', 'Porter', 'Jeffrey', 3);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('lgrayn', '[email protected]', 'Gray', 'Linda', 2);
INSERT INTO users (username, email, name, surname, idRole)
VALUES ('pbakero', '[email protected]', 'Baker', 'Philip', 2);
/*--------------------------------------------------------------------*\
TABLE SESSIONS
\*--------------------------------------------------------------------*/
CREATE TABLE sessions (
id INT PRIMARY KEY AUTO_INCREMENT,
idModule INT,
idHall INT,
idSlot INT,
mainSession BOOLEAN DEFAULT FALSE,
CONSTRAINT session_slot_id_fk FOREIGN KEY (idSlot) REFERENCES slots (id),
CONSTRAINT session_module_id_fk FOREIGN KEY (idModule) REFERENCES modules (id),
CONSTRAINT session_hall_id_fk FOREIGN KEY (idHall) REFERENCES halls (id)
);
INSERT INTO sessions (idSlot, idModule, idHall, mainSession) VALUES (1, 1, 1, TRUE);
INSERT INTO sessions (idSlot, idModule, idHall) VALUES (2, 2, 1);
INSERT INTO sessions (idSlot, idModule, idHall) VALUES (2, 4, 2);
INSERT INTO sessions (idSlot, idModule, idHall) VALUES (2, 3, 3);
INSERT INTO sessions (idSlot, idModule, idHall) VALUES (3, 4, 4);
INSERT INTO sessions (idSlot, idModule, idHall) VALUES (4, 2, 1);
/*--------------------------------------------------------------------*\
TABLE SESSIONS_SPEAKERS
\*--------------------------------------------------------------------*/
CREATE TABLE sessions_speakers
(
idSession INT,
idUser INT,
CONSTRAINT sessions_speaker_idSession_idUser_pk PRIMARY KEY (idSession, idUser),
CONSTRAINT sessions_speaker_sessions_id_fk FOREIGN KEY (idSession) REFERENCES sessions (id)
ON DELETE CASCADE,
CONSTRAINT sessions_speaker_users_id_fk FOREIGN KEY (idUser) REFERENCES users (id)
ON DELETE CASCADE
);
INSERT INTO sessions_speakers (idSession, idUser) VALUES (1, 2);
INSERT INTO sessions_speakers (idSession, idUser) VALUES (1, 6);
INSERT INTO sessions_speakers (idSession, idUser) VALUES (2, 2);
INSERT INTO sessions_speakers (idSession, idUser) VALUES (2, 14);
INSERT INTO sessions_speakers (idSession, idUser) VALUES (3, 11);
/*--------------------------------------------------------------------*\
TABLE SESSIONS_AUDITORS
\*--------------------------------------------------------------------*/
CREATE TABLE sessions_auditors
(
idSession INT,
idUser INT,
CONSTRAINT sessions_auditors_idSession_idUser_pk PRIMARY KEY (idSession, idUser),
CONSTRAINT sessions_auditors_sessions_id_fk FOREIGN KEY (idSession) REFERENCES sessions (id),
CONSTRAINT sessions_auditors_users_id_fk FOREIGN KEY (idUser) REFERENCES users (id)
);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (1,1);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (4,1);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (5,1);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (6,1);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (3,2);
INSERT INTO sessions_auditors (idSession, idUser) VALUES (3,3);