1
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
3
CREATE USER 'dbsk'@'localhost' IDENTIFIED BY 'Tomten2009';
4
GRANT ALL PRIVILEGES ON lenasys.* TO 'dbsk'@'localhost' WITH GRANT OPTION;
8
CREATE TABLE IF NOT EXISTS `lenasys`.`Users` (
9
`userName` VARCHAR(20) NOT NULL ,
10
`userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student
11
`name` VARCHAR(45) NULL ,
12
`passwd` CHAR(40) NULL ,
13
`passwdHint` CHAR(100) NULL ,
14
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
15
PRIMARY KEY (`userName`) )
19
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` (
20
`courseID` VARCHAR(10) NOT NULL ,
21
`name` VARCHAR(45) NULL ,
22
`courseData` VARCHAR(128) NULL , -- explenation about course, example: G1N 7,5hp + additional text
23
PRIMARY KEY (`courseID`))
26
CREATE TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
27
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
28
`userName` VARCHAR(20) NOT NULL ,
29
`courseID` VARCHAR(10) NOT NULL , -- Example G14234
30
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
31
FOREIGN KEY (`userName` )
32
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE,
33
FOREIGN KEY (`courseID` )
34
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
38
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` (
39
`categoryName` VARCHAR(64) NOT NULL ,
40
`courseID` VARCHAR(10) NOT NULL ,
41
`orderNr` INT NOT NULL , -- the order of the examples in the same category
42
PRIMARY KEY (`categoryName`, `courseID`) ,
43
FOREIGN KEY (`courseID` )
44
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
47
CREATE TABLE IF NOT EXISTS `lenasys`.`SubCategories` (
48
`subCategoryName` VARCHAR(64) NOT NULL ,
49
`categoryName` VARCHAR(64) NOT NULL ,
50
`courseID` VARCHAR(10) NOT NULL ,
51
`orderNr` INT NOT NULL , -- the order of the examples in the same category
52
PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
53
FOREIGN KEY (`categoryName` , `courseID` )
54
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE)
57
CREATE TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
58
`quizNr` INT NOT NULL ,
59
`subCategoryName` VARCHAR(64) NOT NULL ,
60
`categoryName` VARCHAR(64) NOT NULL ,
61
`courseID` VARCHAR(10) NOT NULL ,
62
`quizData` VARCHAR(45) NULL ,
63
`allowMultipleReplies` BIT NULL , -- 1=true and 0=false
64
`autoCorrected` BIT NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually., 1=true and 0=false
65
`openingDate` DATETIME NULL , -- time for the student to do the quiz
66
`closingDate` DATETIME NULL ,
67
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
68
PRIMARY KEY (`quizNr`, `subCategoryName`, `categoryName` , `courseID`) ,
69
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
70
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE)
73
CREATE TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
74
`questionID` INT NOT NULL , -- The id of the individual question in the quiz.
75
`quizNr` INT NOT NULL , -- The quiz from wich the question is from
76
`subCategoryName` VARCHAR(64) NOT NULL ,
77
`categoryName` VARCHAR(64) NOT NULL ,
78
`courseID` VARCHAR(10) NOT NULL ,
79
`questionData` VARCHAR(45) NULL , -- the question
80
`correctAnswer` VARCHAR(45) NULL , -- the answer
81
PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` , `courseID`) ,
82
FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`, `courseID` )
83
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE)
86
CREATE TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
87
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
88
`userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
89
`courseID` VARCHAR(10) NOT NULL ,
90
`quizNr` INT NOT NULL ,
91
`subCategoryName` VARCHAR(64) NOT NULL ,
92
`categoryName` VARCHAR(64) NOT NULL ,
93
`quizCourseID` VARCHAR(10) NOT NULL ,
94
`answers` VARCHAR(45) NULL , -- answers provided by the student
95
`answerHash` VARCHAR(45) NULL , -- a security measure
96
`answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
97
`grade` VARCHAR(8) NULL ,
98
`gradeComment` VARCHAR(200) NULL ,
99
PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
100
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
101
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE,
102
FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
103
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
108
CREATE TABLE IF NOT EXISTS `lenasys`.`Examples` (
109
`exampleName` VARCHAR(20) NOT NULL ,
110
`subCategoryName` VARCHAR(64) NOT NULL ,
111
`categoryName` VARCHAR(64) NOT NULL ,
112
`courseID` VARCHAR(10) NOT NULL ,
113
`orderNr` INT NOT NULL , -- the order of the examples in the same category
114
`description` VARCHAR(200) NULL ,
115
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
116
PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
117
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
118
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
121
CREATE TABLE IF NOT EXISTS `lenasys`.`Files` (
122
`fileName` VARCHAR(20) NOT NULL ,
123
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
124
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
125
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
126
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
127
PRIMARY KEY (`fileName`))
129
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` (
130
`columnNr` INT NOT NULL ,
131
`orderNr` INT NOT NULL , -- the order of the examples in the same category
132
`fileName` VARCHAR(20) NOT NULL , -- primary key??
133
`exampleName` VARCHAR(20) NOT NULL ,
134
`subCategoryName` VARCHAR(64) NOT NULL ,
135
`categoryName` VARCHAR(64) NOT NULL ,
136
`courseID` VARCHAR(10) NOT NULL ,
137
`executable` BIT , -- 1=true and 0=false
138
PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
139
FOREIGN KEY (`fileName` )
140
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE,
141
FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
142
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE)
145
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
146
`id` INT NOT NULL AUTO_INCREMENT ,
147
`fileName` VARCHAR(20) NOT NULL ,
148
`columnNr` INT NOT NULL ,
149
`orderNr` INT NOT NULL ,
150
`exampleName` VARCHAR(20) NOT NULL ,
151
`subCategoryName` VARCHAR(64) NOT NULL ,
152
`categoryName` VARCHAR(64) NOT NULL ,
153
`courseID` VARCHAR(10) NOT NULL ,
156
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
157
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
158
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE)
161
CREATE TABLE IF NOT EXISTS `lenasys`.`Keywords` (
162
`keyword` VARCHAR(20) NOT NULL ,
163
`exampleName` VARCHAR(20) NOT NULL ,
164
`subCategoryName` VARCHAR(64) NOT NULL ,
165
`categoryName` VARCHAR(64) NOT NULL ,
166
`courseID` VARCHAR(10) NOT NULL ,
167
PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
168
FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
169
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE)
173
CREATE TABLE IF NOT EXISTS `lenasys`.`logUsersQuizLogins` (
175
`userName` VARCHAR(20) NOT NULL ,
176
`userAgent` VARCHAR(200) NOT NULL , -- web browser and version
177
`userIP` VARCHAR(20) NOT NULL , -- ip-number
178
`browserID` VARCHAR(64) /*NOT NULL*/ , -- autogenerated id for local-storage
179
`courseID` VARCHAR(10) NOT NULL ,
180
`courseOccasion` VARCHAR(16) NULL ,
181
`quizNr` INT NOT NULL ,
182
`loginTimeStamp` DATETIME NOT NULL ,
183
`success` BIT NOT NULL , -- 1=true and 0=false
186
CREATE TABLE IF NOT EXISTS `lenasys`.`logAssignedQuizzesAnswers` (
188
`userName` VARCHAR(20) NOT NULL ,
189
`userAgent` VARCHAR(200) NOT NULL ,
190
`userIP` VARCHAR(20) NOT NULL ,
191
`courseID` VARCHAR(10) NOT NULL ,
192
`courseOccasion` VARCHAR(16) NOT NULL ,
193
`quizNr` INT NOT NULL ,
194
`answers` VARCHAR(45) NULL ,
195
`answerHash` VARCHAR(45) NULL ,
196
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
197
`grade` VARCHAR(8) NULL ,
198
`gradeComment` VARCHAR(200) NULL ,
202
CREATE TABLE IF NOT EXISTS `lenasys`.`logBenchmark` (
204
`userName` VARCHAR(20) NOT NULL ,
205
`userAgent` VARCHAR(200) NOT NULL ,
206
`userIP` VARCHAR(20) NOT NULL ,
207
`browser` VARCHAR(20) NOT NULL , -- will they be used to show different views? Can we just use userAgent instead?
208
`browserVersion` VARCHAR(20) NOT NULL , -- -||-
209
`renderer` VARCHAR(20) NOT NULL , -- ??
210
`rendererVersion` VARCHAR(20) NOT NULL , -- ??
211
`os` VARCHAR(64) NOT NULL ,
212
`osVersion` VARCHAR(20) NOT NULL ,
213
`fps` VARCHAR(20) NOT NULL ,
214
`maxFps` VARCHAR(20) NOT NULL ,
215
`hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName
216
`app` VARCHAR(20) NOT NULL ,
217
`screenResolution` VARCHAR(20) NOT NULL ,
218
`logTimeStamp` DATETIME NOT NULL ,
219
`runtime` INT NOT NULL ,
223
INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`)
224
VALUES ('student','Per Student',sha1('Syp9393'),'gamla vanliga' ,2,'19900385-2345'),
225
('student2','Gösta Student',sha1('Syp9393'),'SypARN',2,'19800385-2385'),
226
('lärare','Kalle Lärare',sha1('Syp9393'),'Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325');
228
INSERT INTO `Courses`(`courseID`, `name`, `courseData`)
229
VALUES('DA133G' , 'Webbutveckling - datorgrafik G1N' , ' 7,5hp (IKI)'),
230
('d1popcrn' , 'Webbutveckling - Läran om Gson' , ' 7,5hp (IKI)'),
231
('DAG123' , 'Webbutveckling - Nånting' , ' 7,5hp (IKI)');
233
INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`)
234
VALUES ('HT2012 period 2','student','d1popcrn'),
235
('HT2012 period 2','student','DA133G'),
236
('HT2012 period 2','student2','d1popcrn'),
237
('HT2012 period 2','student2','DAG123');
239
INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`)
240
VALUES ('Vektorgrafik','DA133G', 1),
241
('Shading','DA133G', 2),
243
('Tidigt 90-tal','d1popcrn', 1),
244
('Categorie i nånting','DAG123', 1),
245
('Categorie i någontingen mer','DAG123', 2);
247
INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`)
248
VALUES ('Punkt' ,'Vektorgrafik','DA133G', 1),
249
('Linjer' ,'Vektorgrafik','DA133G', 2),
250
('Böjningar' ,'Vektorgrafik','DA133G', 3),
251
('Vertexshading','Shading','DA133G', 1),
252
('Globalshading','Shading','DA133G', 2),
253
('Bra 3d' , '3D','DA133G', 1),
254
('Dålig 3d' , '3D','DA133G', 2),
255
('Smurfhits','Tidigt 90-tal','d1popcrn', 1),
256
('NES','Tidigt 90-tal','d1popcrn', 2),
257
('Sovmorgon','Tidigt 90-tal','d1popcrn', 3),
258
('Subcategorie i nånting' ,'Categorie i nånting','DAG123', 1),
259
('Subcategorie2 i nånting' ,'Categorie i nånting','DAG123', 2),
260
('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DAG123', 1),
261
('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DAG123', 2);
265
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` )
266
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'),
267
(2,'punkt' ,'Vektorgrafik','DA133G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),
268
(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'),
269
(1,'linjer' ,'Vektorgrafik','DA133G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00');
271
INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`)
272
VALUES(1,1,'punkt' ,'Vektorgrafik','DA133G','vad är roten av pi plus solens massa?','inte vet jag lol'),
273
(2,1,'punkt' ,'Vektorgrafik','DA133G','hur mår du?','Bra som bara den');
275
INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`)
276
VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om punkter'),
277
('punkt example 2','punkt' ,'Vektorgrafik','DA133G',2,'nån sorts förklaring om punkter i example 2'),
278
('ett till exempel inom punkt','punkt' ,'Vektorgrafik','DA133G',3,'nån sorts förklaring om punkter igen'),
279
('linjer example 1','linjer' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om linjer');
281
INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`)
282
VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit.
283
Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a
284
mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend
285
dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non
286
consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas
287
lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci.
288
Maecenas vulputate tincidunt odio sit amet interdum. Curabitur non ante tristique mi malesuada dictum at quis nunc.
289
Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor,
290
leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros,
291
pharetra vitae tempus vel, pretium at purus.'),
293
('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque habitant morbi tristique
294
senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis.
295
Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim.
296
Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.
297
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
