/lenasys/trunk

CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `lenasys` ;
-- -----------------------------------------------------
CREATE USER 'dbsk'@'localhost' IDENTIFIED BY 'Tomten2009';
GRANT ALL PRIVILEGES ON lenasys.* TO 'dbsk'@'localhost' WITH GRANT OPTION;


-- -----------------------------------------------------
-- Table `lenasys`.`Users`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`Users` (
  `userName` VARCHAR(20) NOT NULL ,
  `userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student
  `name` VARCHAR(45) NULL ,
  `passwd` CHAR(40) NULL ,
  `passwdHint` CHAR(100) NULL ,
  `ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
  PRIMARY KEY (`userName`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `lenasys`.`Courses`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Courses` (
  `courseID` VARCHAR(10) NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `courseData` VARCHAR(10) NULL , -- Short explenation about course, example: G1N 7,5hp
  PRIMARY KEY (`courseID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`StudentCourseRegistrations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
  `courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
  `userName` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL , -- Example G14234
  PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
    FOREIGN KEY (`userName` )
    REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE,
    FOREIGN KEY (`courseID` )
    REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Categories`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`Categories` (
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
  PRIMARY KEY (`categoryName`, `courseID`) ,
    FOREIGN KEY (`courseID` )
    REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`SubCategories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`SubCategories` (
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `orderNr` INT NOT NULL , -- the order of the examples in the same category
  PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
    FOREIGN KEY (`categoryName` , `courseID` )
    REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Quizzes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
  `quizNr` INT NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `quizData` VARCHAR(45) NULL ,
  `allowMultipleReplies` BIT NULL , -- 1=true and 0=false
  `autoCorrected` BIT NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually., 1=true and 0=false
  `openingDate` DATETIME NULL ,  -- time for the student to do the quiz
  `closingDate` DATETIME NULL ,
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
  PRIMARY KEY (`quizNr`,  `subCategoryName`, `categoryName` , `courseID`) ,
    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`QuizQuestions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
  `questionID` INT NOT NULL , -- The id of the individual question in the quiz.
  `quizNr` INT NOT NULL , -- The quiz from wich the question is from
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `questionData` VARCHAR(45) NULL , -- the question
  `correctAnswer` VARCHAR(45) NULL , -- the answer
  PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` ,  `courseID`) ,
    FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`,  `courseID` )
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`AssignedQuizzes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
  `courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2
  `userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
  `courseID` VARCHAR(10) NOT NULL ,
  `quizNr` INT NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `quizCourseID` VARCHAR(10) NOT NULL ,
  `answers` VARCHAR(45) NULL , -- answers provided by the student
  `answerHash` VARCHAR(45) NULL , -- a security measure
  `answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
  `grade` VARCHAR(8) NULL ,
  `gradeComment` VARCHAR(200) NULL ,
  PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
    FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
    REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE,
    FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
    REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `lenasys`.`Examples`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Examples` (
  `exampleName` VARCHAR(20) NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
  `description` VARCHAR(200) NULL ,
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
  PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Files`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (
  `fileName` VARCHAR(20) NOT NULL ,
  `fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
  `codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
  `dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable. 
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
  PRIMARY KEY (`fileName`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `lenasys`.`Containers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Containers` (
  `columnNr` INT NOT NULL ,
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
  `fileName` VARCHAR(20) NOT NULL , -- primary key??
  `exampleName` VARCHAR(20) NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `executable` BIT , -- 1=true and 0=false
  PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
    FOREIGN KEY (`fileName` )
    REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE,
	FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
    REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`FileInterestingLines`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
`id` INT NOT NULL AUTO_INCREMENT ,
`fileName` VARCHAR(20) NOT NULL ,
`columnNr` INT NOT NULL ,
`orderNr` INT NOT NULL ,
`exampleName` VARCHAR(20) NOT NULL ,
`subCategoryName` VARCHAR(64) NOT NULL ,
`categoryName` VARCHAR(64) NOT NULL ,
`courseID` VARCHAR(10) NOT NULL ,
`beginAt` INT NULL ,
`endAt` INT NULL ,
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `lenasys`.`Keywords`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`Keywords` (
  `keyword` VARCHAR(20) NOT NULL ,
  `exampleName` VARCHAR(20) NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
    FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
    REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `lenasys`.`logUsersQuizLogins`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`logUsersQuizLogins` (
  `id` INT NOT NULL ,
  `userName` VARCHAR(20) NOT NULL ,
  `userAgent` VARCHAR(200) NOT NULL , -- web browser and version
  `userIP` VARCHAR(20) NOT NULL , --  ip-number
  `browserID` VARCHAR(64) /*NOT NULL*/ , -- autogenerated id for local-storage
  `courseID` VARCHAR(10) NOT NULL ,
  `courseOccasion` VARCHAR(16) NULL ,
  `quizNr` INT NOT NULL ,
  `loginTimeStamp` DATETIME NOT NULL ,
  `success` BIT NOT NULL , -- 1=true and 0=false
  PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -------------------------------------------------
-- Table `lenasys`.`logAssignedQuizzesAnswers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`logAssignedQuizzesAnswers` (
  `id` INT NOT NULL ,
  `userName` VARCHAR(20)  NOT NULL ,
  `userAgent` VARCHAR(200) NOT NULL ,
  `userIP` VARCHAR(20) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `courseOccasion` VARCHAR(16) NOT NULL ,
  `quizNr` INT NOT NULL ,
  `answers` VARCHAR(45) NULL ,
  `answerHash` VARCHAR(45) NULL ,
  `answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
  `grade` VARCHAR(8) NULL ,
  `gradeComment` VARCHAR(200) NULL ,
  PRIMARY KEY (`id`))

ENGINE = InnoDB;
-- -------------------------------------------------
-- Table `lenasys`.`logBenchmark`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lenasys`.`logBenchmark` (
  `id` INT NOT NULL ,
  `userName` VARCHAR(20) NOT NULL ,
  `userAgent` VARCHAR(200) NOT NULL ,
  `userIP` VARCHAR(20)  NOT NULL ,
  `browser` VARCHAR(20)  NOT NULL , -- will they be used to show different views? Can we just use userAgent instead?
  `browserVersion` VARCHAR(20) NOT NULL , -- -||-
  `renderer` VARCHAR(20) NOT NULL , -- ??
  `rendererVersion` VARCHAR(20) NOT NULL , -- ??
  `os` VARCHAR(64) NOT NULL ,
  `osVersion` VARCHAR(20) NOT NULL ,
  `fps` VARCHAR(20) NOT NULL ,
  `maxFps` VARCHAR(20) NOT NULL ,
  `hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName
  `app` VARCHAR(20) NOT NULL ,
  `screenResolution` VARCHAR(20) NOT NULL ,
  `logTimeStamp` DATETIME NOT NULL ,
  `runtime` INT NOT NULL ,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -------------------------------------------------

INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`) 
VALUES ('student','Per Student',sha1('Syp9393'),'gamla vanliga' ,2,'19900385-2345'),
('student2','Gösta Student',sha1('Syp9393'),'SypARN',2,'19800385-2385'),
('lärare','Kalle Lärare',sha1('Syp9393'),'Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325');

INSERT INTO `Courses`(`courseID`, `name`, `courseData`) 
VALUES('DA133G'  , ' Webbutveckling - datorgrafik G1N' , ' 7,5hp (IKI)'),
('d1popcrn'  , ' Webbutveckling - Läran om Gson' , ' 7,5hp (IKI)'),
('DAG123'  , ' Webbutveckling - Nånting' , ' 7,5hp (IKI)');

INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`) 
VALUES ('HT2012 period 2','student','d1popcrn'),
('HT2012 period 2','student','DA133G'),
('HT2012 period 2','student2','d1popcrn');

INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`) 
VALUES ('Vektorgrafik','DA133G', 1),
('Shading','DA133G', 2),
('3D','DA133G', 3),
('tidigt 90-tal','d1popcrn', 1),
('categorie i nånting','DAG123', 1),
('categorie i någontingen mer','d1popcrn', 2);

INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`) 
VALUES ('punkt' ,'Vektorgrafik','DA133G', 1),
('linjer' ,'Vektorgrafik','DA133G', 2),
('böjningar' ,'Vektorgrafik','DA133G', 3),
('Vertexshading','Shading','DA133G', 1),
('globalshading','Shading','DA133G', 2),
('bra 3d' , '3D','DA133G', 1),
('dålig 3d' , '3D','DA133G', 2),
('Smurfhits','tidigt 90-tal','d1popcrn', 1),
('NES','tidigt 90-tal','d1popcrn', 2),
('Sovmorgon','tidigt 90-tal','d1popcrn', 3),
('subcategorie i nånting' ,'categorie i nånting','DAG123', 1),
('subcategorie2 i nånting' ,'categorie i nånting','DAG123', 2),
('subcategorie i nåntingen mer', 'categorie i någontingen mer','d1popcrn', 1),
('subcategorie2 i nåntingen mer', 'categorie i någontingen mer','d1popcrn', 2);

/*
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`, autoCorrected`, `openingDate`,`closingDate` ) 
VALUES(1,'punkt' ,'Vektorgrafik','DA133G',  );


  `quizNr` INT NOT NULL ,
  `subCategoryName` VARCHAR(64) NOT NULL ,
  `categoryName` VARCHAR(64) NOT NULL ,
  `courseID` VARCHAR(10) NOT NULL ,
  `quizData` VARCHAR(45) NULL ,
  `allowMultipleReplies` BIT NULL , -- 1=true and 0=false
  `autoCorrected` BIT NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually., 1=true and 0=false
  `openingDate` DATETIME NULL ,  -- time for the student to do the quiz
  `closingDate` DATETIME NULL ,
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>');
*/
-- -----------------------------------------------------
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. 
-- ska courseID i ContainerFiles vara foreignkey ifrån contaiers??