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 ,
10
`isPublic` BIT(1) NOT NULL DEFAULT 1 ,
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`)
239
VALUES('DA146G' , 'Computer Graphics' , ' 7,5hp (IKI)'),
240
('IS317G' , 'Database Construction' , ' 7,5hp (IKI)'),
241
('IS114G' , 'Database Systems' , ' 7,5hp (IKI)'),
242
('DV313G' , 'Webdevelopment - XML API' , ' 7,5hp (IKI)'),
243
('DA330G' , 'Webprogramming' , ' 7,5hp (IKI)'),
244
('DA525G' , 'Advanced Computer Graphics and Shader Programming' , ' 7,5hp (IKI)');
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
('Tidigt 90-tal','DV313G', 1),
263
('Categorie i nånting','DA330G', 1),
264
('Categorie i någontingen mer','DA330G', 2);
266
INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`)
267
VALUES ('Punkt' ,'Vektorgrafik','DA525G', 1),
268
('Linjer' ,'Vektorgrafik','DA525G', 2),
269
('Böjningar' ,'Vektorgrafik','DA525G', 3),
270
('Vertexshading','Shading','DA525G', 1),
271
('Globalshading','Shading','DA525G', 2),
272
('Bra 3d' , '3D','DA525G', 1),
273
('Dålig 3d' , '3D','DA525G', 2),
274
('Smurfhits','Tidigt 90-tal','DV313G', 1),
275
('NES','Tidigt 90-tal','DV313G', 2),
276
('Sovmorgon','Tidigt 90-tal','DV313G', 3),
277
('Subcategorie i nånting' ,'Categorie i nånting','DA330G', 1),
278
('Subcategorie2 i nånting' ,'Categorie i nånting','DA330G', 2),
279
('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DA330G', 1),
280
('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DA330G', 2);
284
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` )
285
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'),
286
(2,'punkt' ,'Vektorgrafik','DA525G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
287
(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'),
288
(1,'linjer' ,'Vektorgrafik','DA525G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00');
290
INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`)
291
VALUES(1,1,'punkt' ,'Vektorgrafik','DA525G','vad är roten av pi plus solens massa?','inte vet jag lol'),
292
(2,1,'punkt' ,'Vektorgrafik','DA525G','hur mår du?','Bra som bara den');
294
INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`)
295
VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om punkter'),
296
('punkt example 2','punkt' ,'Vektorgrafik','DA525G',2,'nån sorts förklaring om punkter i example 2'),
297
('ett till exempel','punkt' ,'Vektorgrafik','DA525G',3,'nån sorts förklaring om punkter igen'),
298
('linjer example 1','linjer' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om linjer');
300
INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`)
301
VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit.
302
Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a
303
mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend
304
dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non
305
consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas
306
lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci.
307
Maecenas vulputate tincidunt odio sit amet interdum. CuraBIT(1)ur non ante tristique mi malesuada dictum at quis nunc.
308
Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor,
309
leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros,
310
pharetra vitae tempus vel, pretium at purus.'),
312
('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque haBIT(1)ant morbi tristique
313
senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis.
314
Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim.
315
Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.
316
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
