/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` BOOLEAN NULL , 
  `autoCorrected` BOOLEAN NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually.
  `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` BOOLEAN ,
  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` BOOLEAN NOT NULL ,
  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`,`userType`,`ssn`) 
  VALUES ('student','Per Student',sha1('Syp9393'),1,'19900385-2345'),
  ('lärare','Kalle Lärare',sha1('Syp9393'),0,'19800385-2325');



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