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 , -- Published or not `isPublic` BIT(1) NOT NULL DEFAULT 1 , -- Public or not 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`, `isPublic`, `isHidden`) VALUES('DA146G' , 'Computer Graphics' , ' 7,5hp (IKI)', 1, 0), ('IS317G' , 'Database Construction' , ' 7,5hp (IKI)', 0, 1), ('IS114G' , 'Database Systems' , ' 7,5hp (IKI)', 1, 0), ('DV313G' , 'Webdevelopment - XML API' , ' 7,5hp (IKI)', 1, 0), ('DA330G' , 'Webprogramming' , ' 7,5hp (IKI)', 0, 0), ('DA525G' , 'Advanced Computer Graphics and Shader Programming' , ' 7,5hp (IKI)', 0, 1); 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), ('SAX Examples','DV313G', 2), ('DOM Examples 1','DV313G', 3), ('DOM Examples 2 - Validation','DV313G', 4), ('DOM Examples 3 - XPATH','DV313G', 5), ('XSLT Examples 1 - Navigation','DV313G', 6), ('XSLT 2 - Apply Template and Grouping','DV313G', 7), ('PHP Introduction','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), ('Introduction','PHP Introduction','DV313G', 1), ('SAX Parsing','SAX Examples','DV313G', 1), ('Validation','DOM Examples 1','DV313G', 1), ('XPATH 1','DOM Examples 3 - XPATH','DV313G', 1), ('XPATH 2','DOM Examples 3 - XPATH','DV313G', 2), ('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'), (5,'Validation' ,'DOM Examples 1','DV313G','Validation with DOM',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'), (5,'SAX Parsing' ,'SAX Examples','DV313G','parsing with SAX',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'), ('punkt example 2','punkt' ,'Vektorgrafik','DA525G',2,'nån sorts förklaring om punkter i example 2'), ('ett till exempel','punkt' ,'Vektorgrafik','DA525G',3,'nån sorts förklaring om punkter igen'), ('XPATH 1a','XPATH 2' ,'DOM Examples 3 - XPATH','DV313G',1,'XPATH intro'), ('SAX Example 1b','SAX Parsing' ,'SAX Examples','DV313G',2,'Printing of SAX parsing'), ('SAX Example 1a','SAX Parsing' ,'SAX Examples','DV313G',1,'Handling of SAX parsing'), ('linjer example 1','linjer' ,'Vektorgrafik','DA525G',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??