/lenasys/trunk

CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE `lenasys` ;

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Table `lenasys`.`Courses`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`Courses` (

  `courseID` VARCHAR(10) NOT NULL ,

  `name` VARCHAR(45) NULL ,

  `courseData` VARCHAR(128) NULL , -- explenation about course, example: G1N 7,5hp  + additional text

  PRIMARY KEY (`courseID`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- 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' ,

  `firstLogin` BIT(1) NOT NULL DEFAULT 1, -- 1=true 0=false

  `activeCourse` VARCHAR(10) NULL,

  `email` VARCHAR(128) NULL,

  PRIMARY KEY (`userName`) ,

  FOREIGN KEY (`activeCourse` )

  REFERENCES `lenasys`.`Courses` (`courseID`)ON UPDATE CASCADE ON DELETE CASCADE)

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 ON DELETE CASCADE,

    FOREIGN KEY (`courseID` )

    REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE 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 ON DELETE 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 ON DELETE 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(1) NULL , -- 1=true and 0=false

  `autoCorrected` BIT(1) 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 NOW() , 

  PRIMARY KEY (`quizNr`,  `subCategoryName`, `categoryName` , `courseID`) ,

    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)

    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE 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 ON DELETE 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 ON DELETE CASCADE,

    FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )

    REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE 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 NOW() , 

  PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,

    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )

    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `lenasys`.`Files`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (

  `fileName` VARCHAR(228) 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 NOW() , 

  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(228) 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) , -- 1=true and 0=false

  PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,

    FOREIGN KEY (`fileName` )

    REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE ON DELETE CASCADE,

	FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )

    REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `lenasys`.`FileInterestingLines`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (

`id` INT NOT NULL AUTO_INCREMENT ,

`fileName` VARCHAR(228) 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 ON DELETE 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 ON DELETE CASCADE)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `lenasys`.`logUserLoginAttempts`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `lenasys`.`logUserLoginAttempts` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `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

  `loginTimeStamp` TIMESTAMP ON UPDATE NOW()  ,

  `success` BIT(1) 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','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345'),

('student2','Gösta Student','06d31954049ffa053039ae83bab3dcd0ebc67195','SypARN',2,'19800385-2385'),

('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','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'),

('HT2012 period 2','student2','DAG123');

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','DAG123', 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','DAG123', 1),

('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DAG123', 2);

INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` ) 

VALUES(1,'punkt' ,'Vektorgrafik','DA133G','Svara så gott du kan, lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),

(2,'punkt' ,'Vektorgrafik','DA133G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),

(3,'punkt' ,'Vektorgrafik','DA133G','ajaja detta kommer nu gå bra *NAWHT*',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'),

(1,'linjer' ,'Vektorgrafik','DA133G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00');

 INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`) 

 VALUES(1,1,'punkt' ,'Vektorgrafik','DA133G','vad är roten av pi plus solens massa?','inte vet jag lol'),

(2,1,'punkt' ,'Vektorgrafik','DA133G','hur mår du?','Bra som bara den');

INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`) 

VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om punkter'),

('punkt example 2','punkt' ,'Vektorgrafik','DA133G',2,'nån sorts förklaring om punkter i example 2'),

('ett till exempel inom punkt','punkt' ,'Vektorgrafik','DA133G',3,'nån sorts förklaring om punkter igen'),

('linjer example 1','linjer' ,'Vektorgrafik','DA133G',1,'nån sorts förklaring om linjer');

INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`) 

VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit. 

Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a 

mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend 

dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non 

consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas 

lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci. 

Maecenas vulputate tincidunt odio sit amet interdum. CuraBIT(1)ur non ante tristique mi malesuada dictum at quis nunc. 

Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor, 

leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros, 

pharetra vitae tempus vel, pretium at purus.'),

('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque haBIT(1)ant morbi tristique 

senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis. 

Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim. 

Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.

 Aenean venenatis elit vel sem sodales vulputate. Donec adipiscing porta tortor, sed sollicitudin erat dapibus id');

-- -----------------------------------------------------

-- 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??