1
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
3
-- -----------------------------------------------------
5
-- -----------------------------------------------------
6
-- Table `lenasys`.`Courses`
7
-- -----------------------------------------------------
8
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` (
9
`courseID` VARCHAR(10) NOT NULL ,
10
`name` VARCHAR(45) NULL ,
11
`courseData` VARCHAR(128) NULL , -- explenation about course, example: G1N 7,5hp + additional text
12
PRIMARY KEY (`courseID`))
15
-- -----------------------------------------------------
16
-- Table `lenasys`.`Users`
17
-- -----------------------------------------------------
19
CREATE TABLE IF NOT EXISTS `lenasys`.`Users` (
20
`userName` VARCHAR(20) NOT NULL ,
21
`userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student
22
`name` VARCHAR(45) NULL ,
23
`passwd` CHAR(40) NULL ,
24
`passwdHint` CHAR(100) NULL ,
25
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
26
`firstLogin` BIT(1) NOT NULL DEFAULT 1, -- 1=true 0=false
27
`activeCourse` VARCHAR(10) NULL,
28
`email` VARCHAR(128) NULL,
29
PRIMARY KEY (`userName`) ,
30
FOREIGN KEY (`activeCourse` )
31
REFERENCES `lenasys`.`Courses` (`courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
37
-- -----------------------------------------------------
38
-- Table `lenasys`.`StudentCourseRegistrations`
39
-- -----------------------------------------------------
40
CREATE TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
41
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
42
`userName` VARCHAR(20) NOT NULL ,
43
`courseID` VARCHAR(10) NOT NULL , -- Example G14234
44
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
45
FOREIGN KEY (`userName` )
46
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE ON DELETE CASCADE,
47
FOREIGN KEY (`courseID` )
48
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
51
-- -----------------------------------------------------
52
-- Table `lenasys`.`Categories`
53
-- -----------------------------------------------------
55
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` (
56
`categoryName` VARCHAR(64) NOT NULL ,
57
`courseID` VARCHAR(10) NOT NULL ,
58
`orderNr` INT NOT NULL , -- the order of the examples in the same category
59
PRIMARY KEY (`categoryName`, `courseID`) ,
60
FOREIGN KEY (`courseID` )
61
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
64
-- -----------------------------------------------------
65
-- Table `lenasys`.`SubCategories`
66
-- -----------------------------------------------------
67
CREATE TABLE IF NOT EXISTS `lenasys`.`SubCategories` (
68
`subCategoryName` VARCHAR(64) NOT NULL ,
69
`categoryName` VARCHAR(64) NOT NULL ,
70
`courseID` VARCHAR(10) NOT NULL ,
71
`orderNr` INT NOT NULL , -- the order of the examples in the same category
72
PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
73
FOREIGN KEY (`categoryName` , `courseID` )
74
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
77
-- -----------------------------------------------------
78
-- Table `lenasys`.`Quizzes`
79
-- -----------------------------------------------------
80
CREATE TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
81
`quizNr` INT NOT NULL ,
82
`subCategoryName` VARCHAR(64) NOT NULL ,
83
`categoryName` VARCHAR(64) NOT NULL ,
84
`courseID` VARCHAR(10) NOT NULL ,
85
`quizData` VARCHAR(45) NULL ,
86
`allowMultipleReplies` BIT(1) NULL , -- 1=true and 0=false
87
`autoCorrected` BIT(1) NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually., 1=true and 0=false
88
`openingDate` DATETIME NULL , -- time for the student to do the quiz
89
`closingDate` DATETIME NULL ,
90
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
91
PRIMARY KEY (`quizNr`, `subCategoryName`, `categoryName` , `courseID`) ,
92
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
93
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
96
-- -----------------------------------------------------
97
-- Table `lenasys`.`QuizQuestions`
98
-- -----------------------------------------------------
99
CREATE TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
100
`questionID` INT NOT NULL , -- The id of the individual question in the quiz.
101
`quizNr` INT NOT NULL , -- The quiz from wich the question is from
102
`subCategoryName` VARCHAR(64) NOT NULL ,
103
`categoryName` VARCHAR(64) NOT NULL ,
104
`courseID` VARCHAR(10) NOT NULL ,
105
`questionData` VARCHAR(45) NULL , -- the question
106
`correctAnswer` VARCHAR(45) NULL , -- the answer
107
PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` , `courseID`) ,
108
FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`, `courseID` )
109
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
112
-- -----------------------------------------------------
113
-- Table `lenasys`.`AssignedQuizzes`
114
-- -----------------------------------------------------
115
CREATE TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
116
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
117
`userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
118
`courseID` VARCHAR(10) NOT NULL ,
119
`quizNr` INT NOT NULL ,
120
`subCategoryName` VARCHAR(64) NOT NULL ,
121
`categoryName` VARCHAR(64) NOT NULL ,
122
`quizCourseID` VARCHAR(10) NOT NULL ,
123
`answers` VARCHAR(45) NULL , -- answers provided by the student
124
`answerHash` VARCHAR(45) NULL , -- a security measure
125
`answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
126
`grade` VARCHAR(8) NULL ,
127
`gradeComment` VARCHAR(200) NULL ,
128
PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
129
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
130
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE,
131
FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
132
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
137
-- -----------------------------------------------------
138
-- Table `lenasys`.`Examples`
139
-- -----------------------------------------------------
140
CREATE TABLE IF NOT EXISTS `lenasys`.`Examples` (
141
`exampleName` VARCHAR(20) NOT NULL ,
142
`subCategoryName` VARCHAR(64) NOT NULL ,
143
`categoryName` VARCHAR(64) NOT NULL ,
144
`courseID` VARCHAR(10) NOT NULL ,
145
`orderNr` INT NOT NULL , -- the order of the examples in the same category
146
`description` VARCHAR(200) NULL ,
147
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
148
PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
149
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
150
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
153
-- -----------------------------------------------------
154
-- Table `lenasys`.`Files`
155
-- -----------------------------------------------------
156
CREATE TABLE IF NOT EXISTS `lenasys`.`Files` (
157
`fileName` VARCHAR(228) NOT NULL ,
158
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
159
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
160
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
161
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
162
PRIMARY KEY (`fileName`))
164
-- -----------------------------------------------------
165
-- Table `lenasys`.`Containers`
166
-- -----------------------------------------------------
167
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` (
168
`columnNr` INT NOT NULL ,
169
`orderNr` INT NOT NULL , -- the order of the examples in the same category
170
`fileName` VARCHAR(228) NOT NULL , -- primary key??
171
`exampleName` VARCHAR(20) NOT NULL ,
172
`subCategoryName` VARCHAR(64) NOT NULL ,
173
`categoryName` VARCHAR(64) NOT NULL ,
174
`courseID` VARCHAR(10) NOT NULL ,
175
`executable` BIT(1) , -- 1=true and 0=false
176
PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
177
FOREIGN KEY (`fileName` )
178
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE ON DELETE CASCADE,
179
FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
180
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
183
-- -----------------------------------------------------
184
-- Table `lenasys`.`FileInterestingLines`
185
-- -----------------------------------------------------
186
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
187
`id` INT NOT NULL AUTO_INCREMENT ,
188
`fileName` VARCHAR(228) NOT NULL ,
189
`columnNr` INT NOT NULL ,
190
`orderNr` INT NOT NULL ,
191
`exampleName` VARCHAR(20) NOT NULL ,
192
`subCategoryName` VARCHAR(64) NOT NULL ,
193
`categoryName` VARCHAR(64) NOT NULL ,
194
`courseID` VARCHAR(10) NOT NULL ,
197
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
198
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
199
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
202
-- -----------------------------------------------------
203
-- Table `lenasys`.`Keywords`
204
-- -----------------------------------------------------
205
CREATE TABLE IF NOT EXISTS `lenasys`.`Keywords` (
206
`keyword` VARCHAR(20) NOT NULL ,
207
`exampleName` VARCHAR(20) NOT NULL ,
208
`subCategoryName` VARCHAR(64) NOT NULL ,
209
`categoryName` VARCHAR(64) NOT NULL ,
210
`courseID` VARCHAR(10) NOT NULL ,
211
PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
212
FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
213
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
217
-- -----------------------------------------------------
218
-- Table `lenasys`.`logUserLoginAttempts`
219
-- -----------------------------------------------------
220
CREATE TABLE IF NOT EXISTS `lenasys`.`logUserLoginAttempts` (
221
`id` INT NOT NULL AUTO_INCREMENT,
222
`userName` VARCHAR(20) NOT NULL ,
223
`userAgent` VARCHAR(200) NOT NULL , -- web browser and version
224
`userIP` VARCHAR(20) NOT NULL , -- ip-number
225
`browserID` VARCHAR(64) NOT NULL , -- autogenerated id for local-storage
226
`loginTimeStamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
227
`success` BIT(1) NOT NULL , -- 1=true and 0=false
230
-- -------------------------------------------------
231
-- Table `lenasys`.`logAssignedQuizzesAnswers`
232
-- -----------------------------------------------------
233
CREATE TABLE IF NOT EXISTS `lenasys`.`logAssignedQuizzesAnswers` (
235
`userName` VARCHAR(20) NOT NULL ,
236
`userAgent` VARCHAR(200) NOT NULL ,
237
`userIP` VARCHAR(20) NOT NULL ,
238
`courseID` VARCHAR(10) NOT NULL ,
239
`courseOccasion` VARCHAR(16) NOT NULL ,
240
`quizNr` INT NOT NULL ,
241
`answers` VARCHAR(45) NULL ,
242
`answerHash` VARCHAR(45) NULL ,
243
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
244
`grade` VARCHAR(8) NULL ,
245
`gradeComment` VARCHAR(200) NULL ,
249
-- -------------------------------------------------
250
-- Table `lenasys`.`logBenchmark`
251
-- -----------------------------------------------------
252
CREATE TABLE IF NOT EXISTS `lenasys`.`logBenchmark` (
254
`userName` VARCHAR(20) NOT NULL ,
255
`userAgent` VARCHAR(200) NOT NULL ,
256
`userIP` VARCHAR(20) NOT NULL ,
257
`browser` VARCHAR(20) NOT NULL , -- will they be used to show different views? Can we just use userAgent instead?
258
`browserVersion` VARCHAR(20) NOT NULL , -- -||-
259
`renderer` VARCHAR(20) NOT NULL , -- ??
260
`rendererVersion` VARCHAR(20) NOT NULL , -- ??
261
`os` VARCHAR(64) NOT NULL ,
262
`osVersion` VARCHAR(20) NOT NULL ,
263
`fps` VARCHAR(20) NOT NULL ,
264
`maxFps` VARCHAR(20) NOT NULL ,
265
`hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName
266
`app` VARCHAR(20) NOT NULL ,
267
`screenResolution` VARCHAR(20) NOT NULL ,
268
`logTimeStamp` DATETIME NOT NULL ,
269
`runtime` INT NOT NULL ,
272
-- -------------------------------------------------
274
INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`)
275
VALUES ('student','Per Student','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345'),
276
('student2','Gösta Student','06d31954049ffa053039ae83bab3dcd0ebc67195','SypARN',2,'19800385-2385'),
277
('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325');
279
INSERT INTO `Courses`(`courseID`, `name`, `courseData`)
280
VALUES('DA133G' , 'Webbutveckling - datorgrafik G1N' , ' 7,5hp (IKI)'),
281
('d1popcrn' , 'Webbutveckling - Läran om Gson' , ' 7,5hp (IKI)'),
282
('DAG123' , 'Webbutveckling - Nånting' , ' 7,5hp (IKI)');
284
INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`)
285
VALUES ('HT2012 period 2','student','d1popcrn'),
286
('HT2012 period 2','student','DA133G'),
287
('HT2012 period 2','student2','d1popcrn'),
288
('HT2012 period 2','student2','DAG123');
290
INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`)
291
VALUES ('Vektorgrafik','DA133G', 1),
292
('Shading','DA133G', 2),
294
('Tidigt 90-tal','d1popcrn', 1),
295
('Categorie i nånting','DAG123', 1),
296
('Categorie i någontingen mer','DAG123', 2);
298
INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`)
299
VALUES ('Punkt' ,'Vektorgrafik','DA133G', 1),
300
('Linjer' ,'Vektorgrafik','DA133G', 2),
301
('Böjningar' ,'Vektorgrafik','DA133G', 3),
302
('Vertexshading','Shading','DA133G', 1),
303
('Globalshading','Shading','DA133G', 2),
304
('Bra 3d' , '3D','DA133G', 1),
305
('Dålig 3d' , '3D','DA133G', 2),
306
('Smurfhits','Tidigt 90-tal','d1popcrn', 1),
307
('NES','Tidigt 90-tal','d1popcrn', 2),
308
('Sovmorgon','Tidigt 90-tal','d1popcrn', 3),
309
('Subcategorie i nånting' ,'Categorie i nånting','DAG123', 1),
310
('Subcategorie2 i nånting' ,'Categorie i nånting','DAG123', 2),
311
('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DAG123', 1),
312
('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DAG123', 2);
316
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` )
317
VALUES(1,'punkt' ,'Vektorgrafik','DA133G','Svara så gott du kan, lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
318
(2,'punkt' ,'Vektorgrafik','DA133G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
319
(3,'punkt' ,'Vektorgrafik','DA133G','ajaja detta kommer nu gå bra *NAWHT*',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
320
(1,'linjer' ,'Vektorgrafik','DA133G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00');
322
INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`)
323
VALUES(1,1,'punkt' ,'Vektorgrafik','DA133G','vad är roten av pi plus solens massa?','inte vet jag lol'),
324
(2,1,'punkt' ,'Vektorgrafik','DA133G','hur mår du?','Bra som bara den');
326
INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`)
327
VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om punkter'),
328
('punkt example 2','punkt' ,'Vektorgrafik','DA133G',2,'nån sorts förklaring om punkter i example 2'),
329
('ett till exempel','punkt' ,'Vektorgrafik','DA133G',3,'nån sorts förklaring om punkter igen'),
330
('linjer example 1','linjer' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om linjer');
332
INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`)
333
VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit.
334
Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a
335
mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend
336
dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non
337
consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas
338
lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci.
339
Maecenas vulputate tincidunt odio sit amet interdum. CuraBIT(1)ur non ante tristique mi malesuada dictum at quis nunc.
340
Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor,
341
leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros,
342
pharetra vitae tempus vel, pretium at purus.'),
344
('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque haBIT(1)ant morbi tristique
345
senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis.
346
Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim.
347
Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.
348
Aenean venenatis elit vel sem sodales vulputate. Donec adipiscing porta tortor, sed sollicitudin erat dapibus id');
351
-- -----------------------------------------------------
352
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction.
353
-- ska courseID i ContainerFiles vara foreignkey ifrån contaiers??
b'\\ No newline at end of file'
added
removed
databas.sql
