/lenasys/trunk

DROP DATABASE `lenasys` ;

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(50) NULL ,

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

  `isHidden` BIT(1) NOT NULL DEFAULT 0 ,

  `isPublic` BIT(1) NOT NULL DEFAULT 1 ,

  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`.`Permissions`

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

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

  `courseID` VARCHAR(10) NOT NULL ,

  `userName` VARCHAR(20) NOT NULL ,

  PRIMARY KEY (`courseID`, `userName`) ,

    FOREIGN KEY (`courseID` )

    REFERENCES `lenasys`.`Courses` (`courseID` )

	ON UPDATE CASCADE ON DELETE CASCADE,

    FOREIGN KEY (`userName` )

    REFERENCES `lenasys`.`Users` (`userName` )

	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 DEFAULT CURRENT_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 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 DEFAULT CURRENT_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 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 DEFAULT CURRENT_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(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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  ,

  `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 `Courses`(`courseID`, `name`, `courseData`) 

VALUES('DA146G'  , 'Computer Graphics' , ' 7,5hp (IKI)'),

('IS317G'  , 'Database Construction' , ' 7,5hp (IKI)'),

('IS114G'  , 'Database Systems' , ' 7,5hp (IKI)'),

('DV313G'  , 'Webdevelopment - XML API' , ' 7,5hp (IKI)'),

('DA330G'  , 'Webprogramming' , ' 7,5hp (IKI)'),

('DA525G'  ,  'Advanced Computer Graphics and Shader Programming'  ,  ' 7,5hp (IKI)');

INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`,`activeCourse`) 

VALUES ('student','Per Student','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345', 'DA330G'),

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

('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325', 'DV313G' );

INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`) 

VALUES ('HT2012 period 2','student','DV313G'),

('HT2012 period 2','student','DA525G'),

('HT2012 period 2','student2','DV313G'),

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

INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`) 

VALUES ('Vektorgrafik','DA525G', 1),

('Shading','DA525G', 2),

('3D','DA525G', 3),

('Tidigt 90-tal','DV313G', 1),

('Categorie i nånting','DA330G', 1),

('Categorie i någontingen mer','DA330G', 2);

INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`) 

VALUES ('Punkt' ,'Vektorgrafik','DA525G', 1),

('Linjer' ,'Vektorgrafik','DA525G', 2),

('Böjningar' ,'Vektorgrafik','DA525G', 3),

('Vertexshading','Shading','DA525G', 1),

('Globalshading','Shading','DA525G', 2),

('Bra 3d' , '3D','DA525G', 1),

('Dålig 3d' , '3D','DA525G', 2),

('Smurfhits','Tidigt 90-tal','DV313G', 1),

('NES','Tidigt 90-tal','DV313G', 2),

('Sovmorgon','Tidigt 90-tal','DV313G', 3),

('Subcategorie i nånting' ,'Categorie i nånting','DA330G', 1),

('Subcategorie2 i nånting' ,'Categorie i nånting','DA330G', 2),

('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DA330G', 1),

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

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

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

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

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

(1,'linjer' ,'Vektorgrafik','DA525G','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','DA525G','vad är roten av pi plus solens massa?','inte vet jag lol'),

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

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

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