/lenasys/trunk

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

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

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

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

  `userTypeCode` INT NOT NULL ,

  `userType` VARCHAR(10) NOT NULL ,

  PRIMARY KEY (`userType`))

ENGINE = InnoDB;

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

-- Table `lenasys`.`Users`

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

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

  `userName` VARCHAR(20) NOT NULL ,

  `name` VARCHAR(45) NULL ,

  `passwd` CHAR(40) NULL ,

  `userType` INT NULL , -- 0=teacher or 1=student

  `ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,

  PRIMARY KEY (`userName`) ,

  UNIQUE INDEX `userName_UNIQUE` (`userName` ASC),

  INDEX `fk_Users_UserTypeCodes1_idx` (`userType` ASC) ,

  CONSTRAINT `fk_Users_usertypeCodes1`

    FOREIGN KEY (`userType` )

    REFERENCES `lenasys`.`userTypeCodes` (`userTypeCode` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

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`) ,

  UNIQUE INDEX `courseID_UNIQUE` (`courseID` ASC) )

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`) ,

  INDEX `fk_StudentCourseRegistrations_Courses1_idx` (`courseID` ASC) ,

  CONSTRAINT `fk_StudentCourseRegistrations_Users`

    FOREIGN KEY (`userName` )

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

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_StudentCourseRegistrations_Courses1`

    FOREIGN KEY (`courseID` )

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

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`Quizzes`

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

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

  `quizNr` INT 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 ,

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

  INDEX `fk_Quizzes_Courses1_idx` (`courseID` ASC) ,

  UNIQUE INDEX `quizNr_UNIQUE` (`quizNr` ASC) ,

  CONSTRAINT `fk_Quizzes_Courses1`

    FOREIGN KEY (`courseID` )

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

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

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

  `courseID` VARCHAR(10) NOT NULL ,

  `questionData` VARCHAR(45) NULL , -- the question

  `correctAnswer` VARCHAR(45) NULL , -- the answer

  PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,

  UNIQUE INDEX `questionID_UNIQUE` (`questionID` ASC) ,

  INDEX `fk_QuizQuestions_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,

  CONSTRAINT `fk_QuizQuestions_Quizzes1`

    FOREIGN KEY (`quizNr` , `courseID` )

    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

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 ,

  `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 (`userName`, `courseID`, `quizNr`, `quizCourseID`) ,

  INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,

  CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`

    FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )

    REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_AssignedQuizzes_Quizzes1`

    FOREIGN KEY (`quizNr` , `quizCourseID` )

    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`Examples`

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

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

  `exampleName` VARCHAR(20) NOT NULL ,

  `courseID` VARCHAR(10) NOT NULL ,

  `orderNumber` INT NULL , -- the order of the examples in the same category 

  `description` VARCHAR(200) NULL ,

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

  INDEX `fk_Examples_Courses1_idx` (`courseID` ASC) ,

  UNIQUE INDEX `exampleName_UNIQUE` (`exampleName` ASC) ,

  CONSTRAINT `fk_Examples_Courses1`

    FOREIGN KEY (`courseID` )

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

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`Pages`

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

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

  `pageName` VARCHAR(45) NOT NULL ,

  `exampleName` VARCHAR(20) NOT NULL ,

  `courseID` VARCHAR(10) NOT NULL ,

  `orderNumber` INT NULL , 

  `pageColumn` INT NOT NULL , -- which column in the view the file is presented

  PRIMARY KEY (`pageName`, `exampleName`, `courseID`) ,

  INDEX `fk_Pages_Examples1_idx` (`exampleName` ASC, `courseID` ASC) ,

  CONSTRAINT `fk_Pages_Examples1`

    FOREIGN KEY (`exampleName` , `courseID` )

    REFERENCES `lenasys`.`Examples` (`exampleName` , `courseID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`Files`

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

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

  `fileName` VARCHAR(20) NOT NULL ,

  `fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild

  `codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.

  `dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.

  PRIMARY KEY (`fileName`) ,

  UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )

ENGINE = InnoDB;

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

-- Table `lenasys`.`PageFiles`

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

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

  `fileName` VARCHAR(20) NOT NULL ,

  `pageName` VARCHAR(45) NOT NULL , -- 

  `exampleName` VARCHAR(20) NOT NULL ,

  `courseID` VARCHAR(10) NOT NULL ,

  PRIMARY KEY (`fileName`, `pageName`, `exampleName`, `courseID`) ,

  INDEX `fk_PagesFiles_Files1_idx` (`fileName` ASC) ,

  INDEX `fk_PagesFiles_Pages1_idx` (`pageName` ASC, `exampleName` ASC, `courseID` ASC) ,

  CONSTRAINT `fk_PagesFiles_Files1`

    FOREIGN KEY (`fileName` )

    REFERENCES `lenasys`.`Files` (`fileName` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_PagesFiles_Pages1`

    FOREIGN KEY (`pageName` , `exampleName` , `courseID` )

    REFERENCES `lenasys`.`Pages` (`pageName` , `exampleName` , `courseID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`FileKeywords`

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

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

  `id` INT NOT NULL AUTO_INCREMENT ,

  `fileName` VARCHAR(20) NOT NULL ,

  `keyword` VARCHAR(20) NOT NULL ,

  PRIMARY KEY (`id`, `fileName`) ,

  INDEX `fk_FileKeywords_Files1_idx` (`fileName` ASC) ,

  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,

  CONSTRAINT `fk_FileKeywords_Files1`

    FOREIGN KEY (`fileName` )

    REFERENCES `lenasys`.`Files` (`fileName` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `lenasys`.`FileInterestingLines`

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

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

  `id` INT NOT NULL AUTO_INCREMENT ,

  `fileName` VARCHAR(20) NOT NULL ,

  `beginAt` INT NULL ,

  `endAt` INT NULL ,

  PRIMARY KEY (`id`, `fileName`) ,

  INDEX `fk_FileInterestingLines_Files1_idx` (`fileName` ASC) ,

  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,

  CONSTRAINT `fk_FileInterestingLines_Files1`

    FOREIGN KEY (`fileName` )

    REFERENCES `lenasys`.`Files` (`fileName` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

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`) ,

  UNIQUE INDEX `id_UNIQUE` (`id` ASC))

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`) ,

    UNIQUE INDEX `id_UNIQUE` (`id` ASC))

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`) ,

    UNIQUE INDEX `id_UNIQUE` (`id` ASC))

ENGINE = InnoDB;

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

  insert into `lenasys`.`UserTypeCodes` (`userTypeCode`,`userType`) 

  VALUES (0,'Teacher'),(1,'Student');

insert into `lenasys`.`Users` (`userName`,`name`,`passwd`,`userType`,`ssn`) 

  VALUES ('sha1','Per Student',sha1('Syp9393'),1,'19900385-2345'),

  ('md5','Per Lärare',md5('Syp9393'),0,'19800385-2325');

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

-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. 

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;