/lenasys/trunk

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(32), /*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(32) 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("&lt;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("&lt;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("&lt;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;

//