/lenasys/0.1

To get this branch, use:
bzr branch http://gegoxaren.bato24.eu/bzr/lenasys/0.1

« back to all changes in this revision

Viewing changes to trunk/DuggaSys/db.sql

  • Committer: Henrik G.
  • Date: 2013-03-26 23:22:55 UTC
  • Revision ID: henrik.gustavsson@his.se-20130326232255-ik6snyatlbkf3zs1
First seed of Lenasys ... Needs to be Organized Further

Show diffs side-by-side

added added

removed removed

Lines of Context:
trunk/DuggaSys/db.sql
 
1
DROP DATABASE quizsystem;
 
2
CREATE DATABASE quizsystem;
 
3
USE quizsystem;
 
4
/* Students */
 
5
CREATE TABLE Student(
 
6
    ssn CHAR(11), /*YYMMDD-XXXX*/
 
7
    name VARCHAR(100),
 
8
    loginName VARCHAR(50) UNIQUE, /*a02leifo */
 
9
    passw VARCHAR(50), /*MD5-hashat lösenord*/
 
10
    PRIMARY KEY(ssn)    
 
11
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
12
 
 
13
/* Courses */
 
14
CREATE TABLE Course(
 
15
    name VARCHAR(200),
 
16
        description TEXT,
 
17
        courseData TEXT,
 
18
    PRIMARY KEY(name)
 
19
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
20
 
 
21
CREATE TABLE StudentCourseRegistration(
 
22
    studentSsn CHAR(11), /*YYMMDD-XXXX*/
 
23
    courseName VARCHAR(200),
 
24
    courseOccasion VARCHAR(25),
 
25
    PRIMARY KEY(studentSsn, courseName, courseOccasion),
 
26
    FOREIGN KEY(studentSsn) REFERENCES Student(ssn) ON UPDATE CASCADE,
 
27
    FOREIGN KEY(courseName) REFERENCES Course(name) ON UPDATE CASCADE
 
28
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
29
 
 
30
CREATE TABLE Quiz(
 
31
    nr INTEGER,
 
32
    courseName VARCHAR(200),
 
33
        opening TIMESTAMP,
 
34
        closing TIMESTAMP,
 
35
    quizData TEXT,
 
36
        autoCorrected BOOLEAN,
 
37
        allowMultipleReplies BOOLEAN,
 
38
        quizURI VARCHAR(255),
 
39
    PRIMARY KEY(nr, courseName),
 
40
    FOREIGN KEY(courseName) REFERENCES Course(name) ON UPDATE CASCADE
 
41
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
42
 
 
43
CREATE TABLE QuizVariant(
 
44
    qVarNr INTEGER,
 
45
    quizNr INTEGER,
 
46
    quizCourseName VARCHAR(200),
 
47
    correctAnswer VARCHAR(255),
 
48
    quizObjectIDs TEXT,
 
49
    PRIMARY KEY(qVarNr, quizNr, quizCourseName),
 
50
    FOREIGN KEY(quizNr, quizCourseName) REFERENCES Quiz(nr, courseName) ON UPDATE CASCADE
 
51
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
52
 
 
53
CREATE TABLE QuizVariantObject(
 
54
    id VARCHAR(255),
 
55
    quizNr INTEGER,
 
56
    qVarNr INTEGER,
 
57
    quizCourseName VARCHAR(200),
 
58
    objectData TEXT,
 
59
    PRIMARY KEY(id, quizNr, qVarNr, quizCourseName),
 
60
    FOREIGN KEY(qVarNr, quizNr, quizCourseName) REFERENCES QuizVariant(qVarNr, quizNr, quizCourseName) ON UPDATE CASCADE
 
61
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
62
 
 
63
CREATE TABLE AssignedQuizzes(
 
64
    ssn CHAR(11), /*YYMMDD-XXXX*/
 
65
    quizNr INTEGER,
 
66
    qVarNr INTEGER,
 
67
    quizCourseName VARCHAR(200),
 
68
        courseOccasion VARCHAR(25),
 
69
    answerHash VARCHAR(255), /*Hash of Student login name + answer */
0
70
   answer TEXT,
 
71
        grade VARCHAR(10),
 
72
        gradeComment TEXT,
 
73
        answeredDateTime TIMESTAMP,
 
74
    userAgent VARCHAR(1024),
 
75
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
 
76
    PRIMARY KEY(ssn, quizNr, quizCourseName, courseOccasion),
 
77
    FOREIGN KEY(ssn) REFERENCES Student(ssn) ON UPDATE CASCADE,
 
78
    FOREIGN KEY(quizNr, quizCourseName) REFERENCES QuizVariant(quizNr, quizCourseName) ON UPDATE CASCADE
 
79
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
80
 
 
81
CREATE TABLE AssignedQuizzesAnswerLog(
 
82
    ssn CHAR(11), /*YYMMDD-XXXX*/
 
83
        loginName VARCHAR(50),
 
84
    quizNr INTEGER,
 
85
    qVarNr INTEGER,
 
86
    quizCourseName VARCHAR(200),
 
87
        courseOccasion VARCHAR(25),
 
88
    answerHash VARCHAR(255), /*Hash of Student login name + answer */
 
89
    answer TEXT,
 
90
        grade VARCHAR(10),
 
91
        gradeComment TEXT,
 
92
        answeredDateTime TIMESTAMP,
 
93
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
 
94
    userIP VARCHAR(20) /*$_SERVER['REMOTE_ADDR']*/
 
95
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
96
 
 
97
CREATE TABLE QSystemUser(
 
98
    userName     VARCHAR(16) UNIQUE NOT NULL,
 
99
    passw        VARCHAR(41) NOT NULL,
 
100
    userType     VARCHAR(10) NOT NULL,
 
101
    PRIMARY KEY(userName)
 
102
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
103
 
 
104
/* Logging of all student login attempts */
 
105
CREATE TABLE userLoginsLog(
 
106
    id INTEGER AUTO_INCREMENT,
 
107
    loginName VARCHAR(16),
 
108
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
 
109
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
 
110
    DateTime TIMESTAMP,
 
111
        success VARCHAR(25),
 
112
        courseName VARCHAR(100),
 
113
        courseOccasion VARCHAR(25),
 
114
    quizNr INTEGER,
 
115
    PRIMARY KEY(id)
 
116
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
117
 
 
118
/* Logging of failed attempt to login to the backend */
 
119
CREATE TABLE logFailedUserLogins(
 
120
    id INTEGER AUTO_INCREMENT,
 
121
    userName VARCHAR(16),
 
122
    user        Type VARCHAR(10),
 
123
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
 
124
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
 
125
    DateTime TIMESTAMP,
 
126
        success VARCHAR(25),
 
127
    PRIMARY KEY(id)
 
128
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
129
 
 
130
 
 
131
 
 
132
/* Logging of successful logins to the backend */
 
133
CREATE TABLE userLoginsLog(
 
134
    id INTEGER AUTO_INCREMENT,
 
135
    loginName VARCHAR(16),
 
136
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
 
137
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
 
138
    DateTime TIMESTAMP,
 
139
        success VARCHAR(25),
 
140
    PRIMARY KEY(id)
 
141
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;
 
142
 
 
143
INSERT INTO Course(name) VALUES('DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)');
 
144
INSERT INTO QSystemUser(userName,passw,userType) VALUES('tomtefar','$1$nwRo7ye/$WK7wY9GbNMRj3lIeuaPcG/','admin');
 
145
 
 
146
INSERT INTO Student(ssn,name,loginName,passw) VALUES ('780323-1212','Lei Fo','a02leifo','56edf750f3bb2053b3c1c44429b3ce82'); 
 
147
INSERT INTO StudentCourseRegistration(studentSsn, courseName, courseOccasion) VALUES ('780323-1212','DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','HT-12 LP1');
 
148
INSERT INTO StudentCourseRegistration(studentSsn, courseName, courseOccasion) VALUES ('780323-1212','DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','HT-12 LP2');
 
149
 
 
150
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>');
 
151
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');
 
152
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>');
 
153
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>');
 
154
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>');
 
155
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4");</script>');
 
156
 
 
157
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');
 
158
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>');
 
159
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>');
 
160
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>');
 
161
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4 Var2");</script>');
 
162
 
 
163
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>');
 
164
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');
 
165
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>');
 
166
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>');
 
167
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>');
 
168
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4 d2 var1");</script>');
 
169
 
 
170
 
 
171
DELIMITER //
 
172
DROP PROCEDURE IF EXISTS CHECKLOGIN;
 
173
CREATE PROCEDURE CHECKLOGIN(userName VARCHAR(16), userPassword VARCHAR(41))
 
174
BEGIN
 
175
   SELECT userName,passw,userType FROM QSystemUser WHERE userName=userName AND passw=userPassword;
 
176
END;
 
177
//
 
178
DELIMITER ;
 
179
 
 
180
;
 
181
        allowMultipleReplies BOOLEAN,
 
182
        quizURI VARCHAR(255),
 
 
b'\\ No newline at end of file'