/lenasys/trunk

To get this branch, use:
bzr branch http://gegoxaren.bato24.eu/bzr/lenasys/trunk

« back to all changes in this revision

Viewing changes to databas.sql

  • Committer: Gustav Hatvigsson
  • Date: 2013-04-23 14:03:04 UTC
  • mfrom: (36.4.13 lenasys)
  • Revision ID: gustav.hartvigsson@gmail.com-20130423140304-7nvfh3k2ue069eiu
merged DB changes

Show diffs side-by-side

added added

removed removed

Lines of Context:
databas.sql
1
 
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
 
1
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
2
2
USE `lenasys` ;
3
3
-- -----------------------------------------------------
4
4
CREATE USER 'dbsk'@'localhost' IDENTIFIED BY 'Tomten2009';
5
5
GRANT ALL PRIVILEGES ON lenasys.* TO 'dbsk'@'localhost' WITH GRANT OPTION;
6
6
 
7
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`UserTypeCodes` (
8
 
  `userTypeCode` TINYINT(1) NOT NULL ,
9
 
  `userType` VARCHAR(10) NOT NULL ,
10
 
  PRIMARY KEY (`userTypeCode`))
11
 
ENGINE = InnoDB;
 
7
 
12
8
-- -----------------------------------------------------
13
9
-- Table `lenasys`.`Users`
14
10
-- -----------------------------------------------------
15
11
 
16
12
CREATE  TABLE IF NOT EXISTS `lenasys`.`Users` (
17
13
  `userName` VARCHAR(20) NOT NULL ,
 
14
  `userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student
18
15
  `name` VARCHAR(45) NULL ,
19
16
  `passwd` CHAR(40) NULL ,
20
 
  `userType` TINYINT(1) NOT NULL , -- 0=teacher or 1=student
 
17
  `passwdHint` CHAR(100) NULL ,
21
18
  `ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
22
 
  PRIMARY KEY (`userName`),
23
 
 FOREIGN KEY (`userType`)
24
 
 REFERENCES `lenasys`.`UserTypeCodes` (`userTypeCode`) )
 
19
  PRIMARY KEY (`userName`) )
25
20
ENGINE = InnoDB;
26
21
 
27
22
 
66
58
ENGINE = InnoDB;
67
59
 
68
60
-- -----------------------------------------------------
 
61
-- Table `lenasys`.`SubCategories`
 
62
-- -----------------------------------------------------
 
63
CREATE  TABLE IF NOT EXISTS `lenasys`.`SubCategories` (
 
64
  `subCategoryName` VARCHAR(64) NOT NULL ,
 
65
  `categoryName` VARCHAR(64) NOT NULL ,
 
66
  `courseID` VARCHAR(10) NOT NULL ,
 
67
  `orderNr` INT NOT NULL , -- the order of the examples in the same category
 
68
  PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
 
69
    FOREIGN KEY (`categoryName` , `courseID` )
 
70
    REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` ))
 
71
ENGINE = InnoDB;
 
72
 
 
73
-- -----------------------------------------------------
69
74
-- Table `lenasys`.`Quizzes`
70
75
-- -----------------------------------------------------
71
76
CREATE  TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
72
77
  `quizNr` INT NOT NULL ,
 
78
  `subCategoryName` VARCHAR(64) NOT NULL ,
73
79
  `categoryName` VARCHAR(64) NOT NULL ,
74
80
  `courseID` VARCHAR(10) NOT NULL ,
75
81
  `quizData` VARCHAR(45) NULL ,
77
83
  `autoCorrected` BOOLEAN NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually.
78
84
  `openingDate` DATETIME NULL ,  -- time for the student to do the quiz
79
85
  `closingDate` DATETIME NULL ,
80
 
  PRIMARY KEY (`quizNr`, `courseID`) ,
81
 
    FOREIGN KEY (`categoryName`, `courseID` )
82
 
    REFERENCES `lenasys`.`Categories` (`categoryName`, `courseID` ))
 
86
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
 
87
  PRIMARY KEY (`quizNr`,  `subCategoryName`, `categoryName` , `courseID`) ,
 
88
    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
 
89
    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` ))
83
90
ENGINE = InnoDB;
84
91
 
85
92
-- -----------------------------------------------------
88
95
CREATE  TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
89
96
  `questionID` INT NOT NULL , -- The id of the individual question in the quiz.
90
97
  `quizNr` INT NOT NULL , -- The quiz from wich the question is from
 
98
  `subCategoryName` VARCHAR(64) NOT NULL ,
 
99
  `categoryName` VARCHAR(64) NOT NULL ,
91
100
  `courseID` VARCHAR(10) NOT NULL ,
92
101
  `questionData` VARCHAR(45) NULL , -- the question
93
102
  `correctAnswer` VARCHAR(45) NULL , -- the answer
94
 
  PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,
95
 
    FOREIGN KEY (`quizNr` , `courseID` )
96
 
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
 
103
  PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` ,  `courseID`) ,
 
104
    FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`,  `courseID` )
 
105
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` ))
97
106
ENGINE = InnoDB;
98
107
 
99
108
-- -----------------------------------------------------
104
113
  `userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
105
114
  `courseID` VARCHAR(10) NOT NULL ,
106
115
  `quizNr` INT NOT NULL ,
 
116
  `subCategoryName` VARCHAR(64) NOT NULL ,
 
117
  `categoryName` VARCHAR(64) NOT NULL ,
107
118
  `quizCourseID` VARCHAR(10) NOT NULL ,
108
119
  `answers` VARCHAR(45) NULL , -- answers provided by the student
109
120
  `answerHash` VARCHAR(45) NULL , -- a security measure
110
121
  `answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
111
122
  `grade` VARCHAR(8) NULL ,
112
123
  `gradeComment` VARCHAR(200) NULL ,
113
 
  PRIMARY KEY (`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
 
124
  PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
114
125
    FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
115
126
    REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` ),
116
 
    FOREIGN KEY (`quizNr` , `quizCourseID` )
117
 
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
 
127
    FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
 
128
    REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` ))
118
129
ENGINE = InnoDB;
119
130
 
120
131
 
124
135
-- -----------------------------------------------------
125
136
CREATE  TABLE IF NOT EXISTS `lenasys`.`Examples` (
126
137
  `exampleName` VARCHAR(20) NOT NULL ,
 
138
  `subCategoryName` VARCHAR(64) NOT NULL ,
127
139
  `categoryName` VARCHAR(64) NOT NULL ,
128
140
  `courseID` VARCHAR(10) NOT NULL ,
129
141
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
130
142
  `description` VARCHAR(200) NULL ,
131
 
  PRIMARY KEY (`exampleName`, `categoryName`, `courseID`) ,
132
 
    FOREIGN KEY (`categoryName` )
133
 
    REFERENCES `lenasys`.`Categories` (`categoryName` ),
134
 
    FOREIGN KEY (`courseID` )
135
 
    REFERENCES `lenasys`.`Courses` (`courseID` ))
136
 
ENGINE = InnoDB;
137
 
 
138
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`Containers` (
139
 
  `columnNr` INT NOT NULL ,
140
 
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
141
 
  `exampleName` VARCHAR(20) NOT NULL ,
142
 
  `categoryName` VARCHAR(64) NOT NULL ,
143
 
  `courseID` VARCHAR(10) NOT NULL ,
144
 
  PRIMARY KEY (`columnNr`,  `orderNr`,`exampleName`,`categoryName`, `courseID`) ,
145
 
    FOREIGN KEY (`exampleName` ,`categoryName` , `courseID` )
146
 
    REFERENCES `lenasys`.`Examples` (`exampleName` ,`categoryName` , `courseID` ))
 
143
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
 
144
  PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
 
145
    FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
 
146
    REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` ))
147
147
ENGINE = InnoDB;
148
148
 
149
149
-- -----------------------------------------------------
154
151
-- -----------------------------------------------------
155
152
CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (
156
153
  `fileName` VARCHAR(20) NOT NULL ,
157
 
  `fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild
 
154
  `fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
158
155
  `codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
159
 
  `dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
 
156
  `dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable. 
 
157
  `updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , 
160
158
  PRIMARY KEY (`fileName`))
161
159
ENGINE = InnoDB;
 
160
-- -----------------------------------------------------
 
161
-- Table `lenasys`.`Containers`
 
162
-- -----------------------------------------------------
 
163
CREATE  TABLE IF NOT EXISTS `lenasys`.`Containers` (
 
164
  `columnNr` INT NOT NULL ,
 
165
  `orderNr` INT NOT NULL , -- the order of the examples in the same category 
 
166
  `fileName` VARCHAR(20) NOT NULL , -- primary key??
 
167
  `exampleName` VARCHAR(20) NOT NULL ,
 
168
  `subCategoryName` VARCHAR(64) NOT NULL ,
 
169
  `categoryName` VARCHAR(64) NOT NULL ,
 
170
  `courseID` VARCHAR(10) NOT NULL ,
 
171
  `executable` BOOLEAN ,
 
172
  PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
 
173
    FOREIGN KEY (`fileName` )
 
174
    REFERENCES `lenasys`.`Files` (`fileName` ),
 
175
        FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
 
176
    REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` ))
 
177
ENGINE = InnoDB;
162
178
 
163
179
-- -----------------------------------------------------
164
 
CREATE TABLE IF NOT EXISTS `lenasys`.`ContainerFiles` (
165
 
        `fileName` VARCHAR(20) NOT NULL ,
166
 
        `columnNr` INT NOT NULL ,
167
 
        `orderNr` INT NOT NULL , -- the order of the examples in the same category 
168
 
        `exampleName` VARCHAR(20) NOT NULL ,
169
 
        `categoryName` VARCHAR(64) NOT NULL ,
170
 
        `courseID` VARCHAR(10) NOT NULL ,
171
 
        PRIMARY KEY ( `fileName`, `columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`),
172
 
        FOREIGN KEY (`fileName`)
173
 
        REFERENCES  `lenasys`.`Files` (`fileName`) ,
174
 
        FOREIGN KEY ( `columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`)
175
 
        REFERENCES `lenasys`.`Containers` (`columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`))
176
 
        ENGINE = InnoDB;
177
 
        
178
180
-- Table `lenasys`.`FileInterestingLines`
179
181
-- -----------------------------------------------------
180
182
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
186
185
`columnNr` INT NOT NULL ,
187
186
`orderNr` INT NOT NULL ,
188
187
`exampleName` VARCHAR(20) NOT NULL ,
 
188
`subCategoryName` VARCHAR(64) NOT NULL ,
189
189
`categoryName` VARCHAR(64) NOT NULL ,
190
190
`courseID` VARCHAR(10) NOT NULL ,
191
191
`beginAt` INT NULL ,
192
192
`endAt` INT NULL ,
193
 
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`categoryName`, `courseID`) ,
194
 
FOREIGN KEY (`fileName`)
195
 
REFERENCES `lenasys`.`Files` (`fileName`),
196
 
FOREIGN KEY (`columnNr`, `orderNr`, `exampleName`, `categoryName`, `courseID`)
197
 
REFERENCES `lenasys`.`ContainerFiles` (`columnNr`, `orderNr`, `exampleName`, `categoryName`, `courseID`))
 
193
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
 
194
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
 
195
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`))
198
196
ENGINE = InnoDB;
199
197
 
200
198
-- -----------------------------------------------------
201
199
-- Table `lenasys`.`Keywords`
202
200
-- -----------------------------------------------------
203
201
CREATE  TABLE IF NOT EXISTS `lenasys`.`Keywords` (
204
 
  `id` INT NOT NULL AUTO_INCREMENT ,
205
202
  `keyword` VARCHAR(20) NOT NULL ,
206
203
  `exampleName` VARCHAR(20) NOT NULL ,
 
204
  `subCategoryName` VARCHAR(64) NOT NULL ,
207
205
  `categoryName` VARCHAR(64) NOT NULL ,
208
206
  `courseID` VARCHAR(10) NOT NULL ,
209
 
  PRIMARY KEY (`id`, `keyword`, `exampleName`, `categoryName`, `courseID`) ,
210
 
    FOREIGN KEY (`exampleName`, `categoryName`, `courseID` )
211
 
    REFERENCES `lenasys`.`Examples` (`exampleName`, `categoryName`, `courseID` ))
 
207
  PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
 
208
    FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
 
209
    REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` ))
212
210
ENGINE = InnoDB;
213
211
 
214
212
 
263
261
  `osVersion` VARCHAR(20) NOT NULL ,
264
262
  `fps` VARCHAR(20) NOT NULL ,
265
263
  `maxFps` VARCHAR(20) NOT NULL ,
266
 
  `hostName` VARCHAR(20) NOT NULL , -- �ndrad till hostName
 
264
  `hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName
267
265
  `app` VARCHAR(20) NOT NULL ,
268
266
  `screenResolution` VARCHAR(20) NOT NULL ,
269
267
  `logTimeStamp` DATETIME NOT NULL ,
271
269
  PRIMARY KEY (`id`))
272
270
ENGINE = InnoDB;
273
271
-- -------------------------------------------------
274
 
  insert into `lenasys`.`UserTypeCodes` (`userTypeCode`,`userType`) 
275
 
  VALUES (0,'Teacher'),(1,'Student');
276
272
 
277
273
insert into `lenasys`.`Users` (`userName`,`name`,`passwd`,`userType`,`ssn`) 
278
274
  VALUES ('student','Per Student',sha1('Syp9393'),1,'19900385-2345'),
279
 
  ('l�rare','Kalle L�rare',sha1('Syp9393'),0,'19800385-2325');
 
275
  ('lärare','Kalle Lärare',sha1('Syp9393'),0,'19800385-2325');
280
276
 
281
277
 
282
278
 
283
279
-- -----------------------------------------------------
284
280
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. 
 
 
b'\\ No newline at end of file'
 
281
-- ska courseID i ContainerFiles vara foreignkey ifrån contaiers??
 
 
b'\\ No newline at end of file'