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
|
DROP DATABASE dsystem;
CREATE DATABASE dsystem;
USE dsystem;
CREATE USER 'dbsk'@'localhost' IDENTIFIED BY 'Tomten2009';
GRANT ALL PRIVILEGES ON 'dsystem'.* TO 'dbsk'@'localhost' WITH GRANT OPTION;
/* Students */
CREATE TABLE Student(
ssn CHAR(11), /*YYMMDD-XXXX*/
name VARCHAR(100),
loginName VARCHAR(50) UNIQUE, /*a02leifo */
passw VARCHAR(50), /*MD5 hashed password*/
PRIMARY KEY(ssn)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
/* Courses */
CREATE TABLE Course(
name VARCHAR(200),
description TEXT,
courseData TEXT,
PRIMARY KEY(name)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE StudentCourseRegistration(
studentSsn CHAR(11), /*YYMMDD-XXXX*/
courseName VARCHAR(200),
courseOccasion VARCHAR(25),
PRIMARY KEY(studentSsn, courseName, courseOccasion),
FOREIGN KEY(studentSsn) REFERENCES Student(ssn) ON UPDATE CASCADE,
FOREIGN KEY(courseName) REFERENCES Course(name) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE Quiz(
nr INTEGER,
courseName VARCHAR(200),
opening TIMESTAMP,
closing TIMESTAMP,
quizData TEXT,
autoCorrected BOOLEAN,
allowMultipleReplies BOOLEAN,
quizURI VARCHAR(255),
PRIMARY KEY(nr, courseName),
FOREIGN KEY(courseName) REFERENCES Course(name) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE QuizVariant(
qVarNr INTEGER,
quizNr INTEGER,
quizCourseName VARCHAR(200),
correctAnswer VARCHAR(255),
quizObjectIDs TEXT,
PRIMARY KEY(qVarNr, quizNr, quizCourseName),
FOREIGN KEY(quizNr, quizCourseName) REFERENCES Quiz(nr, courseName) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE QuizVariantObject(
id VARCHAR(255),
quizNr INTEGER,
qVarNr INTEGER,
quizCourseName VARCHAR(200),
objectData TEXT,
PRIMARY KEY(id, quizNr, qVarNr, quizCourseName),
FOREIGN KEY(qVarNr, quizNr, quizCourseName) REFERENCES QuizVariant(qVarNr, quizNr, quizCourseName) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE AssignedQuizzes(
ssn CHAR(11), /*YYMMDD-XXXX*/
quizNr INTEGER,
qVarNr INTEGER,
quizCourseName VARCHAR(200),
courseOccasion VARCHAR(25),
answerHash VARCHAR(255), /*Hash of Student login name + answer */
answer TEXT,
grade VARCHAR(10),
gradeComment TEXT,
answeredDateTime TIMESTAMP,
userAgent VARCHAR(1024),
userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
PRIMARY KEY(ssn, quizNr, quizCourseName, courseOccasion),
FOREIGN KEY(ssn) REFERENCES Student(ssn) ON UPDATE CASCADE,
FOREIGN KEY(quizNr, quizCourseName) REFERENCES QuizVariant(quizNr, quizCourseName) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE AssignedQuizzesAnswerLog(
ssn CHAR(11), /*YYMMDD-XXXX*/
loginName VARCHAR(50),
quizNr INTEGER,
qVarNr INTEGER,
quizCourseName VARCHAR(200),
courseOccasion VARCHAR(25),
answerHash VARCHAR(255), /*Hash of Student login name + answer */
answer TEXT,
grade VARCHAR(10),
gradeComment TEXT,
answeredDateTime TIMESTAMP,
userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
userIP VARCHAR(20) /*$_SERVER['REMOTE_ADDR']*/
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
CREATE TABLE QSystemUser(
userName VARCHAR(16) UNIQUE NOT NULL,
passw VARCHAR(41) NOT NULL,
userType VARCHAR(10) NOT NULL,
PRIMARY KEY(userName)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
/* Logging of all student login attempts */
CREATE TABLE userLoginsLog(
id INTEGER AUTO_INCREMENT,
loginName VARCHAR(16),
userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
DateTime TIMESTAMP,
success VARCHAR(25),
courseName VARCHAR(100),
courseOccasion VARCHAR(25),
quizNr INTEGER,
PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
INSERT INTO Course(name) VALUES('DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)');
INSERT INTO QSystemUser(userName,passw,userType) VALUES('tomtefar','$1$nwRo7ye/$WK7wY9GbNMRj3lIeuaPcG/','admin');
INSERT INTO Student(ssn,name,loginName,passw) VALUES ('780323-1212','Lei Fo','a02leifo','56edf750f3bb2053b3c1c44429b3ce82');
INSERT INTO StudentCourseRegistration(studentSsn, courseName, courseOccasion) VALUES ('780323-1212','DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','HT-12 LP1');
INSERT INTO StudentCourseRegistration(studentSsn, courseName, courseOccasion) VALUES ('780323-1212','DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','HT-12 LP2');
INSERT INTO Quiz(nr, courseName, opening, closing, autoCorrected, allowMultipleReplies, quizURI, quizData) VALUES(1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','2012-08-19 00:00:00','2012-08-28 00:00:00',1,0,'','<div id="dugga"><h1>Dugga ett för en kurs</h1><p>Duggan ditten och duggan datten</p></div>');
INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','1,2,3,4,5,6,7,8','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>Du ska göra bla bla...</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>1 2 3 4 5</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("<TEST4");</script>');
INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','8,7,6,5,4,3,2,1','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>Variant 2 på dugga 1</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>1111 2222 33333</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3 Var2");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("<TEST4 Var2");</script>');
INSERT INTO Quiz(nr, courseName, opening, closing, autoCorrected, quizData) VALUES(2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','2012-08-19 00:00:00','2012-08-28 00:00:00',0,'<div id="dugga"><h1>Dugga 2 för en kurs</h1><p>Duggan ditten och duggan datten</p></div>');
INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','1,2,3,4,5,6,7,8','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>I dugga 2 variant 1 ska bla bla...</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>--!!--!!--!!--</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3 d2 var1");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("<TEST4 d2 var1");</script>');
DELIMITER //
DROP PROCEDURE IF EXISTS CHECKLOGIN;
CREATE PROCEDURE CHECKLOGIN(userName VARCHAR(16), userPassword VARCHAR(41))
BEGIN
SELECT userName,passw,userType FROM QSystemUser WHERE userName=userName AND passw=userPassword;
END;
//
|