1
DROP DATABASE `lenasys` ;
2
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
5
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` (
6
`courseID` VARCHAR(10) NOT NULL ,
7
`name` VARCHAR(50) NULL ,
8
`courseData` VARCHAR(128) NULL , -- explanation about course, example: G1N 7,5hp + additional text
9
`isHidden` BIT(1) NOT NULL DEFAULT 0 , -- Published or not
10
`isPublic` BIT(1) NOT NULL DEFAULT 1 , -- Public or not
11
PRIMARY KEY (`courseID`))
15
CREATE TABLE IF NOT EXISTS `lenasys`.`Users` (
16
`userName` VARCHAR(20) NOT NULL ,
17
`userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student
18
`name` VARCHAR(45) NULL ,
19
`passwd` CHAR(40) NULL ,
20
`passwdHint` CHAR(100) NULL ,
21
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
22
`firstLogin` BIT(1) NOT NULL DEFAULT 1, -- 1=true 0=false
23
`activeCourse` VARCHAR(10) NULL,
24
`email` VARCHAR(128) NULL,
25
PRIMARY KEY (`userName`) ,
26
FOREIGN KEY (`activeCourse` )
27
REFERENCES `lenasys`.`Courses` (`courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
30
CREATE TABLE IF NOT EXISTS `lenasys`.`Permissions` (
31
`courseID` VARCHAR(10) NOT NULL ,
32
`userName` VARCHAR(20) NOT NULL ,
33
PRIMARY KEY (`courseID`, `userName`) ,
34
FOREIGN KEY (`courseID` )
35
REFERENCES `lenasys`.`Courses` (`courseID` )
36
ON UPDATE CASCADE ON DELETE CASCADE,
37
FOREIGN KEY (`userName` )
38
REFERENCES `lenasys`.`Users` (`userName` )
39
ON UPDATE CASCADE ON DELETE CASCADE)
44
CREATE TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
45
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
46
`userName` VARCHAR(20) NOT NULL ,
47
`courseID` VARCHAR(10) NOT NULL , -- Example G14234
48
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
49
FOREIGN KEY (`userName` )
50
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE ON DELETE CASCADE,
51
FOREIGN KEY (`courseID` )
52
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
56
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` (
57
`categoryName` VARCHAR(64) NOT NULL ,
58
`courseID` VARCHAR(10) NOT NULL ,
59
`orderNr` INT NOT NULL , -- the order of the examples in the same category
60
PRIMARY KEY (`categoryName`, `courseID`) ,
61
FOREIGN KEY (`courseID` )
62
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
65
CREATE TABLE IF NOT EXISTS `lenasys`.`SubCategories` (
66
`subCategoryName` VARCHAR(64) NOT NULL ,
67
`categoryName` VARCHAR(64) NOT NULL ,
68
`courseID` VARCHAR(10) NOT NULL ,
69
`orderNr` INT NOT NULL , -- the order of the examples in the same category
70
PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
71
FOREIGN KEY (`categoryName` , `courseID` )
72
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
75
CREATE TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
76
`quizNr` INT NOT NULL ,
77
`subCategoryName` VARCHAR(64) NOT NULL ,
78
`categoryName` VARCHAR(64) NOT NULL ,
79
`courseID` VARCHAR(10) NOT NULL ,
80
`quizData` VARCHAR(45) NULL ,
81
`allowMultipleReplies` BIT(1) NULL , -- 1=true and 0=false
82
`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
83
`openingDate` DATETIME NULL , -- time for the student to do the quiz
84
`closingDate` DATETIME NULL ,
85
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
86
PRIMARY KEY (`quizNr`, `subCategoryName`, `categoryName` , `courseID`) ,
87
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
88
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
91
CREATE TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
92
`questionID` INT NOT NULL , -- The id of the individual question in the quiz.
93
`quizNr` INT NOT NULL , -- The quiz from wich the question is from
94
`subCategoryName` VARCHAR(64) NOT NULL ,
95
`categoryName` VARCHAR(64) NOT NULL ,
96
`courseID` VARCHAR(10) NOT NULL ,
97
`questionData` VARCHAR(45) NULL , -- the question
98
`correctAnswer` VARCHAR(45) NULL , -- the answer
99
PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` , `courseID`) ,
100
FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`, `courseID` )
101
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
104
CREATE TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
105
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
106
`userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
107
`courseID` VARCHAR(10) NOT NULL ,
108
`quizNr` INT NOT NULL ,
109
`subCategoryName` VARCHAR(64) NOT NULL ,
110
`categoryName` VARCHAR(64) NOT NULL ,
111
`quizCourseID` VARCHAR(10) NOT NULL ,
112
`answers` VARCHAR(45) NULL , -- answers provided by the student
113
`answerHash` VARCHAR(45) NULL , -- a security measure
114
`answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
115
`grade` VARCHAR(8) NULL ,
116
`gradeComment` VARCHAR(200) NULL ,
117
PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
118
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
119
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE,
120
FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
121
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
126
CREATE TABLE IF NOT EXISTS `lenasys`.`Examples` (
127
`exampleName` VARCHAR(20) NOT NULL ,
128
`subCategoryName` VARCHAR(64) NOT NULL ,
129
`categoryName` VARCHAR(64) NOT NULL ,
130
`courseID` VARCHAR(10) NOT NULL ,
131
`orderNr` INT NOT NULL , -- the order of the examples in the same category
132
`description` VARCHAR(200) NULL ,
133
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
134
PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
135
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
136
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
139
CREATE TABLE IF NOT EXISTS `lenasys`.`Files` (
140
`fileName` VARCHAR(228) NOT NULL ,
141
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
142
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
143
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
144
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
145
PRIMARY KEY (`fileName`))
147
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` (
148
`columnNr` INT NOT NULL ,
149
`orderNr` INT NOT NULL , -- the order of the examples in the same category
150
`fileName` VARCHAR(228) NOT NULL , -- primary key??
151
`exampleName` VARCHAR(20) NOT NULL ,
152
`subCategoryName` VARCHAR(64) NOT NULL ,
153
`categoryName` VARCHAR(64) NOT NULL ,
154
`courseID` VARCHAR(10) NOT NULL ,
155
`executable` BIT(1) , -- 1=true and 0=false
156
PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
157
FOREIGN KEY (`fileName` )
158
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE ON DELETE CASCADE,
159
FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
160
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
163
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
164
`id` INT NOT NULL AUTO_INCREMENT ,
165
`fileName` VARCHAR(228) NOT NULL ,
166
`columnNr` INT NOT NULL ,
167
`orderNr` INT NOT NULL ,
168
`exampleName` VARCHAR(20) NOT NULL ,
169
`subCategoryName` VARCHAR(64) NOT NULL ,
170
`categoryName` VARCHAR(64) NOT NULL ,
171
`courseID` VARCHAR(10) NOT NULL ,
174
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
175
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
176
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
179
CREATE TABLE IF NOT EXISTS `lenasys`.`Keywords` (
180
`keyword` VARCHAR(20) NOT NULL ,
181
`exampleName` VARCHAR(20) NOT NULL ,
182
`subCategoryName` VARCHAR(64) NOT NULL ,
183
`categoryName` VARCHAR(64) NOT NULL ,
184
`courseID` VARCHAR(10) NOT NULL ,
185
PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
186
FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
187
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
191
CREATE TABLE IF NOT EXISTS `lenasys`.`logUserLoginAttempts` (
192
`id` INT NOT NULL AUTO_INCREMENT,
193
`userName` VARCHAR(20) NOT NULL ,
194
`userAgent` VARCHAR(200) NOT NULL , -- web browser and version
195
`userIP` VARCHAR(20) NOT NULL , -- ip-number
196
`browserID` VARCHAR(64) NOT NULL , -- autogenerated id for local-storage
197
`loginTimeStamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
198
`success` BIT(1) NOT NULL , -- 1=true and 0=false
201
CREATE TABLE IF NOT EXISTS `lenasys`.`logAssignedQuizzesAnswers` (
203
`userName` VARCHAR(20) NOT NULL ,
204
`userAgent` VARCHAR(200) NOT NULL ,
205
`userIP` VARCHAR(20) NOT NULL ,
206
`courseID` VARCHAR(10) NOT NULL ,
207
`courseOccasion` VARCHAR(16) NOT NULL ,
208
`quizNr` INT NOT NULL ,
209
`answers` VARCHAR(45) NULL ,
210
`answerHash` VARCHAR(45) NULL ,
211
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
212
`grade` VARCHAR(8) NULL ,
213
`gradeComment` VARCHAR(200) NULL ,
217
CREATE TABLE IF NOT EXISTS `lenasys`.`logBenchmark` (
219
`userName` VARCHAR(20) NOT NULL ,
220
`userAgent` VARCHAR(200) NOT NULL ,
221
`userIP` VARCHAR(20) NOT NULL ,
222
`browser` VARCHAR(20) NOT NULL , -- will they be used to show different views? Can we just use userAgent instead?
223
`browserVersion` VARCHAR(20) NOT NULL , -- -||-
224
`renderer` VARCHAR(20) NOT NULL , -- ??
225
`rendererVersion` VARCHAR(20) NOT NULL , -- ??
226
`os` VARCHAR(64) NOT NULL ,
227
`osVersion` VARCHAR(20) NOT NULL ,
228
`fps` VARCHAR(20) NOT NULL ,
229
`maxFps` VARCHAR(20) NOT NULL ,
230
`hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName
231
`app` VARCHAR(20) NOT NULL ,
232
`screenResolution` VARCHAR(20) NOT NULL ,
233
`logTimeStamp` DATETIME NOT NULL ,
234
`runtime` INT NOT NULL ,
238
INSERT INTO `Courses`(`courseID`, `name`, `courseData`, `isPublic`, `isHidden`)
239
VALUES('DA146G' , 'Computer Graphics' , ' 7,5hp (IKI)', 1, 0),
240
('IS317G' , 'Database Construction' , ' 7,5hp (IKI)', 0, 1),
241
('IS114G' , 'Database Systems' , ' 7,5hp (IKI)', 1, 0),
242
('DV313G' , 'Webdevelopment - XML API' , ' 7,5hp (IKI)', 1, 0),
243
('DA330G' , 'Webprogramming' , ' 7,5hp (IKI)', 0, 0),
244
('DA525G' , 'Advanced Computer Graphics and Shader Programming' , ' 7,5hp (IKI)', 0, 1);
246
INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`,`activeCourse`)
247
VALUES ('student','Per Student','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345', 'DA330G'),
248
('student2','Gösta Student','06d31954049ffa053039ae83bab3dcd0ebc67195','SypARN',2,'19800385-2385','DA525G' ),
249
('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325', 'DV313G' );
252
INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`)
253
VALUES ('HT2012 period 2','student','DV313G'),
254
('HT2012 period 2','student','DA525G'),
255
('HT2012 period 2','student2','DV313G'),
256
('HT2012 period 2','student2','DA330G');
258
INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`)
259
VALUES ('Vektorgrafik','DA525G', 1),
260
('Shading','DA525G', 2),
262
('SAX Examples','DV313G', 2),
263
('DOM Examples 1','DV313G', 3),
264
('DOM Examples 2 - Validation','DV313G', 4),
265
('DOM Examples 3 - XPATH','DV313G', 5),
266
('XSLT Examples 1 - Navigation','DV313G', 6),
267
('XSLT 2 - Apply Template and Grouping','DV313G', 7),
268
('PHP Introduction','DV313G', 1),
269
('Categorie i nånting','DA330G', 1),
270
('Categorie i någontingen mer','DA330G', 2);
272
INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`)
273
VALUES ('Punkt' ,'Vektorgrafik','DA525G', 1),
274
('Linjer' ,'Vektorgrafik','DA525G', 2),
275
('Böjningar' ,'Vektorgrafik','DA525G', 3),
276
('Vertexshading','Shading','DA525G', 1),
277
('Globalshading','Shading','DA525G', 2),
278
('Bra 3d' , '3D','DA525G', 1),
279
('Dålig 3d' , '3D','DA525G', 2),
280
('Introduction','PHP Introduction','DV313G', 1),
281
('SAX Parsing','SAX Examples','DV313G', 1),
282
('Validation','DOM Examples 1','DV313G', 1),
283
('XPATH 1','DOM Examples 3 - XPATH','DV313G', 1),
284
('XPATH 2','DOM Examples 3 - XPATH','DV313G', 2),
285
('Subcategorie i nånting' ,'Categorie i nånting','DA330G', 1),
286
('Subcategorie2 i nånting' ,'Categorie i nånting','DA330G', 2),
287
('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DA330G', 1),
288
('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DA330G', 2);
291
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` )
292
VALUES(1,'punkt' ,'Vektorgrafik','DA525G','Svara så gott du kan, lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
293
(2,'punkt' ,'Vektorgrafik','DA525G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
294
(3,'punkt' ,'Vektorgrafik','DA525G','ajaja detta kommer nu gå bra *NAWHT*',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
295
(1,'linjer' ,'Vektorgrafik','DA525G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
296
(5,'Validation' ,'DOM Examples 1','DV313G','Validation with DOM',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
297
(5,'SAX Parsing' ,'SAX Examples','DV313G','parsing with SAX',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00');
299
INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`)
300
VALUES(1,1,'punkt' ,'Vektorgrafik','DA525G','vad är roten av pi plus solens massa?','inte vet jag lol'),
301
(2,1,'punkt' ,'Vektorgrafik','DA525G','hur mår du?','Bra som bara den');
303
INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`)
304
VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om punkter'),
305
('punkt example 2','punkt' ,'Vektorgrafik','DA525G',2,'nån sorts förklaring om punkter i example 2'),
306
('ett till exempel','punkt' ,'Vektorgrafik','DA525G',3,'nån sorts förklaring om punkter igen'),
307
('XPATH 1a','XPATH 2' ,'DOM Examples 3 - XPATH','DV313G',1,'XPATH intro'),
308
('SAX Example 1b','SAX Parsing' ,'SAX Examples','DV313G',2,'Printing of SAX parsing'),
309
('SAX Example 1a','SAX Parsing' ,'SAX Examples','DV313G',1,'Handling of SAX parsing'),
310
('linjer example 1','linjer' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om linjer');
312
INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`)
313
VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit.
314
Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a
315
mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend
316
dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non
317
consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas
318
lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci.
319
Maecenas vulputate tincidunt odio sit amet interdum. CuraBIT(1)ur non ante tristique mi malesuada dictum at quis nunc.
320
Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor,
321
leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros,
322
pharetra vitae tempus vel, pretium at purus.'),
324
('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque haBIT(1)ant morbi tristique
325
senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis.
326
Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim.
327
Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.
328
Aenean venenatis elit vel sem sodales vulputate. Donec adipiscing porta tortor, sed sollicitudin erat dapibus id');
b'\\ No newline at end of file'
added
removed
databas.sql
