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
- browse files at revision 47
- compare with another revision
- download diff
- download tarball
- view history from revision 47
- Committer: Gustav Hatvigsson
- Date: 2013-04-23 11:58:43 UTC
- mfrom: (36.4.10 lenasys)
- Revision ID: gustav.hartvigsson@gmail.com-20130423115843-itpg7gmq2a1q4n5i
Merged some DB changes, removed some auto-generated code.
- files modified:
- databas.sql
added
removed
databas.sql
1
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5
1
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
6
2
USE `lenasys` ;
7
3
-- -----------------------------------------------------
12
8
-- Table `lenasys`.`UserTypeCodes`
13
9
-- -----------------------------------------------------
14
10
CREATE TABLE IF NOT EXISTS `lenasys`.`UserTypeCodes` (
15
`userTypeCode` INT NOT NULL ,
11
`userTypeCode` TINYINT(1) NOT NULL ,
16
12
`userType` VARCHAR(10) NOT NULL ,
17
PRIMARY KEY (`userType`))
13
PRIMARY KEY (`userTypeCode`))
18
14
ENGINE = InnoDB;
19
15
-- -----------------------------------------------------
20
16
-- Table `lenasys`.`Users`
24
20
`userName` VARCHAR(20) NOT NULL ,
25
21
`name` VARCHAR(45) NULL ,
26
22
`passwd` CHAR(40) NULL ,
27
`userType` INT NULL , -- 0=teacher or 1=student
23
`userType` TINYINT(1) NOT NULL , -- 0=teacher or 1=student
28
24
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
29
PRIMARY KEY (`userName`) ,
30
UNIQUE INDEX `userName_UNIQUE` (`userName` ASC),
31
INDEX `fk_Users_UserTypeCodes1_idx` (`userType` ASC) ,
32
CONSTRAINT `fk_Users_usertypeCodes1`
33
FOREIGN KEY (`userType` )
34
REFERENCES `lenasys`.`userTypeCodes` (`userTypeCode` )
35
ON DELETE NO ACTION
36
ON UPDATE NO ACTION)
25
PRIMARY KEY (`userName`),
26
FOREIGN KEY (`userType`)
27
REFERENCES `lenasys`.`UserTypeCodes` (`userTypeCode`) )
37
28
ENGINE = InnoDB;
38
29
39
30
44
35
`courseID` VARCHAR(10) NOT NULL ,
45
36
`name` VARCHAR(45) NULL ,
46
37
`courseData` VARCHAR(10) NULL , -- Short explenation about course, example: G1N 7,5hp
47
PRIMARY KEY (`courseID`) ,
48
UNIQUE INDEX `courseID_UNIQUE` (`courseID` ASC) )
38
PRIMARY KEY (`courseID`))
49
39
ENGINE = InnoDB;
50
40
51
41
-- -----------------------------------------------------
56
46
`userName` VARCHAR(20) NOT NULL ,
57
47
`courseID` VARCHAR(10) NOT NULL , -- Example G14234
58
48
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
59
INDEX `fk_StudentCourseRegistrations_Courses1_idx` (`courseID` ASC) ,
60
CONSTRAINT `fk_StudentCourseRegistrations_Users`
61
49
FOREIGN KEY (`userName` )
62
REFERENCES `lenasys`.`Users` (`userName` )
63
ON DELETE NO ACTION
64
ON UPDATE NO ACTION,
65
CONSTRAINT `fk_StudentCourseRegistrations_Courses1`
66
FOREIGN KEY (`courseID` )
67
REFERENCES `lenasys`.`Courses` (`courseID` )
68
ON DELETE NO ACTION
69
ON UPDATE NO ACTION)
50
REFERENCES `lenasys`.`Users` (`userName` ),
51
FOREIGN KEY (`courseID` )
52
REFERENCES `lenasys`.`Courses` (`courseID` ))
53
ENGINE = InnoDB;
54
55
-- -----------------------------------------------------
56
-- Table `lenasys`.`Categories`
57
-- -----------------------------------------------------
58
59
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` (
60
`categoryName` VARCHAR(64) NOT NULL ,
61
`courseID` VARCHAR(10) NOT NULL ,
62
`orderNr` INT NOT NULL , -- the order of the examples in the same category
63
PRIMARY KEY (`categoryName`, `courseID`) ,
64
FOREIGN KEY (`courseID` )
65
REFERENCES `lenasys`.`Courses` (`courseID` ))
70
66
ENGINE = InnoDB;
71
67
72
68
-- -----------------------------------------------------
74
70
-- -----------------------------------------------------
75
71
CREATE TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
76
72
`quizNr` INT NOT NULL ,
73
`categoryName` VARCHAR(64) NOT NULL ,
77
74
`courseID` VARCHAR(10) NOT NULL ,
78
75
`quizData` VARCHAR(45) NULL ,
79
76
`allowMultipleReplies` BOOLEAN NULL ,
81
78
`openingDate` DATETIME NULL , -- time for the student to do the quiz
82
79
`closingDate` DATETIME NULL ,
83
80
PRIMARY KEY (`quizNr`, `courseID`) ,
84
INDEX `fk_Quizzes_Courses1_idx` (`courseID` ASC) ,
85
UNIQUE INDEX `quizNr_UNIQUE` (`quizNr` ASC) ,
86
CONSTRAINT `fk_Quizzes_Courses1`
87
FOREIGN KEY (`courseID` )
88
REFERENCES `lenasys`.`Courses` (`courseID` )
89
ON DELETE NO ACTION
90
ON UPDATE NO ACTION)
81
FOREIGN KEY (`categoryName`, `courseID` )
82
REFERENCES `lenasys`.`Categories` (`categoryName`, `courseID` ))
91
83
ENGINE = InnoDB;
92
84
93
85
-- -----------------------------------------------------
100
92
`questionData` VARCHAR(45) NULL , -- the question
101
93
`correctAnswer` VARCHAR(45) NULL , -- the answer
102
94
PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,
103
UNIQUE INDEX `questionID_UNIQUE` (`questionID` ASC) ,
104
INDEX `fk_QuizQuestions_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
105
CONSTRAINT `fk_QuizQuestions_Quizzes1`
106
95
FOREIGN KEY (`quizNr` , `courseID` )
107
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
108
ON DELETE NO ACTION
109
ON UPDATE NO ACTION)
96
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
110
97
ENGINE = InnoDB;
111
98
112
99
-- -----------------------------------------------------
124
111
`grade` VARCHAR(8) NULL ,
125
112
`gradeComment` VARCHAR(200) NULL ,
126
113
PRIMARY KEY (`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
127
INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
128
CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`
129
114
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
130
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )
131
ON DELETE NO ACTION
132
ON UPDATE NO ACTION,
133
CONSTRAINT `fk_AssignedQuizzes_Quizzes1`
115
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` ),
134
116
FOREIGN KEY (`quizNr` , `quizCourseID` )
135
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
136
ON DELETE NO ACTION
137
ON UPDATE NO ACTION)
117
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
138
118
ENGINE = InnoDB;
139
119
120
121
140
122
-- -----------------------------------------------------
141
123
-- Table `lenasys`.`Examples`
142
124
-- -----------------------------------------------------
143
125
CREATE TABLE IF NOT EXISTS `lenasys`.`Examples` (
144
126
`exampleName` VARCHAR(20) NOT NULL ,
127
`categoryName` VARCHAR(64) NOT NULL ,
145
128
`courseID` VARCHAR(10) NOT NULL ,
146
`orderNumber` INT NULL , -- the order of the examples in the same category
129
`orderNr` INT NOT NULL , -- the order of the examples in the same category
147
130
`description` VARCHAR(200) NULL ,
148
PRIMARY KEY (`exampleName`, `courseID`) ,
149
INDEX `fk_Examples_Courses1_idx` (`courseID` ASC) ,
150
UNIQUE INDEX `exampleName_UNIQUE` (`exampleName` ASC) ,
151
CONSTRAINT `fk_Examples_Courses1`
131
PRIMARY KEY (`exampleName`, `categoryName`, `courseID`) ,
132
FOREIGN KEY (`categoryName` )
133
REFERENCES `lenasys`.`Categories` (`categoryName` ),
152
134
FOREIGN KEY (`courseID` )
153
REFERENCES `lenasys`.`Courses` (`courseID` )
154
ON DELETE NO ACTION
155
ON UPDATE NO ACTION)
135
REFERENCES `lenasys`.`Courses` (`courseID` ))
156
136
ENGINE = InnoDB;
157
137
158
138
-- -----------------------------------------------------
139
-- Table `lenasys`.`Containers`
159
140
-- -----------------------------------------------------
160
CREATE TABLE IF NOT EXISTS `lenasys`.`Pages` (
161
`pageName` VARCHAR(45) NOT NULL ,
141
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` (
142
`columnNr` INT NOT NULL ,
143
`orderNr` INT NOT NULL , -- the order of the examples in the same category
162
144
`exampleName` VARCHAR(20) NOT NULL ,
145
`categoryName` VARCHAR(64) NOT NULL ,
163
146
`courseID` VARCHAR(10) NOT NULL ,
164
`orderNumber` INT NULL ,
165
`pageColumn` INT NOT NULL , -- which column in the view the file is presented
166
PRIMARY KEY (`pageName`, `exampleName`, `courseID`) ,
167
INDEX `fk_Pages_Examples1_idx` (`exampleName` ASC, `courseID` ASC) ,
168
CONSTRAINT `fk_Pages_Examples1`
169
FOREIGN KEY (`exampleName` , `courseID` )
170
REFERENCES `lenasys`.`Examples` (`exampleName` , `courseID` )
171
ON DELETE NO ACTION
172
ON UPDATE NO ACTION)
147
PRIMARY KEY (`columnNr`, `orderNr`,`exampleName`,`categoryName`, `courseID`) ,
148
FOREIGN KEY (`exampleName` ,`categoryName` , `courseID` )
149
REFERENCES `lenasys`.`Examples` (`exampleName` ,`categoryName` , `courseID` ))
173
150
ENGINE = InnoDB;
174
151
175
152
-- -----------------------------------------------------
181
157
`fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild
182
158
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
183
159
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
184
PRIMARY KEY (`fileName`) ,
185
UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )
186
ENGINE = InnoDB;
187
188
189
190
CREATE TABLE IF NOT EXISTS `lenasys`.`PageFiles` (
191
`fileName` VARCHAR(20) NOT NULL ,
192
`pageName` VARCHAR(45) NOT NULL , --
193
`exampleName` VARCHAR(20) NOT NULL ,
194
`courseID` VARCHAR(10) NOT NULL ,
195
PRIMARY KEY (`fileName`, `pageName`, `exampleName`, `courseID`) ,
196
INDEX `fk_PagesFiles_Files1_idx` (`fileName` ASC) ,
197
INDEX `fk_PagesFiles_Pages1_idx` (`pageName` ASC, `exampleName` ASC, `courseID` ASC) ,
198
CONSTRAINT `fk_PagesFiles_Files1`
199
FOREIGN KEY (`fileName` )
200
REFERENCES `lenasys`.`Files` (`fileName` )
201
ON DELETE NO ACTION
202
ON UPDATE NO ACTION,
203
CONSTRAINT `fk_PagesFiles_Pages1`
204
FOREIGN KEY (`pageName` , `exampleName` , `courseID` )
205
REFERENCES `lenasys`.`Pages` (`pageName` , `exampleName` , `courseID` )
206
ON DELETE NO ACTION
207
ON UPDATE NO ACTION)
208
ENGINE = InnoDB;
209
210
211
CREATE TABLE IF NOT EXISTS `lenasys`.`FileKeywords` (
212
`id` INT NOT NULL AUTO_INCREMENT ,
213
`fileName` VARCHAR(20) NOT NULL ,
214
`keyword` VARCHAR(20) NOT NULL ,
215
PRIMARY KEY (`id`, `fileName`) ,
216
INDEX `fk_FileKeywords_Files1_idx` (`fileName` ASC) ,
217
UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
218
CONSTRAINT `fk_FileKeywords_Files1`
219
FOREIGN KEY (`fileName` )
220
REFERENCES `lenasys`.`Files` (`fileName` )
221
ON DELETE NO ACTION
222
ON UPDATE NO ACTION)
223
ENGINE = InnoDB;
224
225
160
PRIMARY KEY (`fileName`))
161
ENGINE = InnoDB;
162
163
-- -----------------------------------------------------
164
-- Table `lenasys`.`ContainerFiles`
165
-- -----------------------------------------------------
166
CREATE TABLE IF NOT EXISTS `lenasys`.`ContainerFiles` (
167
`fileName` VARCHAR(20) NOT NULL ,
168
`columnNr` INT NOT NULL ,
169
`orderNr` INT NOT NULL , -- the order of the examples in the same category
170
`exampleName` VARCHAR(20) NOT NULL ,
171
`categoryName` VARCHAR(64) NOT NULL ,
172
`courseID` VARCHAR(10) NOT NULL ,
173
PRIMARY KEY ( `fileName`, `columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`),
174
FOREIGN KEY (`fileName`)
175
REFERENCES `lenasys`.`Files` (`fileName`) ,
176
FOREIGN KEY ( `columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`)
177
REFERENCES `lenasys`.`Containers` (`columnNr` , `orderNr` , `exampleName`, `categoryName`, `courseID`))
178
ENGINE = InnoDB;
179
226
180
-- -----------------------------------------------------
227
181
-- Table `lenasys`.`FileInterestingLines`
228
182
-- -----------------------------------------------------
229
183
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
184
`id` INT NOT NULL AUTO_INCREMENT ,
185
`fileName` VARCHAR(20) NOT NULL ,
186
`columnNr` INT NOT NULL ,
187
`orderNr` INT NOT NULL ,
188
`exampleName` VARCHAR(20) NOT NULL ,
189
`categoryName` VARCHAR(64) NOT NULL ,
190
`courseID` VARCHAR(10) NOT NULL ,
191
`beginAt` INT NULL ,
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`))
198
ENGINE = InnoDB;
199
200
-- -----------------------------------------------------
201
-- Table `lenasys`.`Keywords`
202
-- -----------------------------------------------------
203
CREATE TABLE IF NOT EXISTS `lenasys`.`Keywords` (
230
204
`id` INT NOT NULL AUTO_INCREMENT ,
231
`fileName` VARCHAR(20) NOT NULL ,
232
`beginAt` INT NULL ,
233
`endAt` INT NULL ,
234
PRIMARY KEY (`id`, `fileName`) ,
235
INDEX `fk_FileInterestingLines_Files1_idx` (`fileName` ASC) ,
236
UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
237
CONSTRAINT `fk_FileInterestingLines_Files1`
238
FOREIGN KEY (`fileName` )
239
REFERENCES `lenasys`.`Files` (`fileName` )
240
ON DELETE NO ACTION
241
ON UPDATE NO ACTION)
205
`keyword` VARCHAR(20) NOT NULL ,
206
`exampleName` VARCHAR(20) NOT NULL ,
207
`categoryName` VARCHAR(64) NOT NULL ,
208
`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` ))
242
212
ENGINE = InnoDB;
243
213
244
214
262
226
`quizNr` INT NOT NULL ,
263
227
`loginTimeStamp` DATETIME NOT NULL ,
264
228
`success` BOOLEAN NOT NULL ,
265
PRIMARY KEY (`id`) ,
266
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
229
PRIMARY KEY (`id`))
267
230
ENGINE = InnoDB;
268
231
-- -------------------------------------------------
269
232
-- Table `lenasys`.`logAssignedQuizzesAnswers`
278
241
`quizNr` INT NOT NULL ,
279
242
`answers` VARCHAR(45) NULL ,
280
243
`answerHash` VARCHAR(45) NULL ,
281
`answeredTimeStamp` DATETIME NULL -- TIMESTAMP ??
244
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
282
245
`grade` VARCHAR(8) NULL ,
283
246
`gradeComment` VARCHAR(200) NULL ,
284
PRIMARY KEY (`id`) ,
285
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
247
PRIMARY KEY (`id`))
286
248
287
249
ENGINE = InnoDB;
288
250
-- -------------------------------------------------
306
268
`screenResolution` VARCHAR(20) NOT NULL ,
307
269
`logTimeStamp` DATETIME NOT NULL ,
308
270
`runtime` INT NOT NULL ,
309
PRIMARY KEY (`id`) ,
310
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
271
PRIMARY KEY (`id`))
311
272
ENGINE = InnoDB;
312
273
-- -------------------------------------------------
313
274
insert into `lenasys`.`UserTypeCodes` (`userTypeCode`,`userType`)
314
275
VALUES (0,'Teacher'),(1,'Student');
315
276
316
277
insert into `lenasys`.`Users` (`userName`,`name`,`passwd`,`userType`,`ssn`)
317
VALUES ('sha1','Per Student',sha1('Syp9393'),1,'19900385-2345'),
318
('md5','Per L�rare',md5('Syp9393'),0,'19800385-2325');
319
278
VALUES ('student','Per Student',sha1('Syp9393'),1,'19900385-2345'),
279
('l�rare','Kalle L�rare',sha1('Syp9393'),0,'19800385-2325');
280
320
281
321
282
322
283
-- -----------------------------------------------------
323
284
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction.
324
SET SQL_MODE=@OLD_SQL_MODE;
325
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
326
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
b'\\ No newline at end of file'
285
-- ska courseID i ContainerFiles vara foreignkey ifr�n contaiers??
b'\\ No newline at end of file'
Loggerhead is a web-based interface for Breezy
Version: 2.0.2.dev0