/lenasys/trunk

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

« back to all changes in this revision

Viewing changes to db.sql

  • Committer: Gustav Hartvigsson
  • Date: 2013-04-12 19:13:58 UTC
  • Revision ID: gustav.hartvigsson@gmail.com-20130412191358-lvnmll48cw7idkzk
added:
* COPYING - licensing information
* COPYRIGHT_HEADER - the header that should be in every file related to
  the project.
* README - Information about the project.
* lgpl-3.0 - The license that is used by this project.

Show diffs side-by-side

added added

removed removed

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