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 36.4.10
- compare with another revision
- download diff
- download tarball
- view history from revision 36.4.10
- Committer: b11johgu
- Date: 2013-04-23 11:54:44 UTC
- mfrom: (46 lenasys)
- mto: This revision was merged to the branch mainline in revision 47.
- Revision ID: b11johgu@student.his.se-20130423115444-fsm8j72dm0mnhh92
removed databas2.sql, put the code in sql so no auto-generated
code remains.
code remains.
- files removed:
- databas2.sql
- files modified:
- codeigniter/application/config/config.php
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
-- -----------------------------------------------------
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`
50
REFERENCES `lenasys`.`Users` (`userName` ),
66
51
FOREIGN KEY (`courseID` )
67
REFERENCES `lenasys`.`Courses` (`courseID` )
68
ON DELETE NO ACTION
69
ON UPDATE NO ACTION)
52
REFERENCES `lenasys`.`Courses` (`courseID` ))
70
53
ENGINE = InnoDB;
71
54
72
55
-- -----------------------------------------------------
76
59
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` (
77
60
`categoryName` VARCHAR(64) NOT NULL ,
78
61
`courseID` VARCHAR(10) NOT NULL ,
79
`orderNr` INT NULL , -- the order of the examples in the same category
62
`orderNr` INT NOT NULL , -- the order of the examples in the same category
80
63
PRIMARY KEY (`categoryName`, `courseID`) ,
81
INDEX `fk_Categories_Courses1_idx` (`courseID` ASC) ,
82
UNIQUE INDEX `categoryName_UNIQUE` (`categoryName` ASC) ,
83
CONSTRAINT `fk_Categories_Courses1`
84
64
FOREIGN KEY (`courseID` )
85
REFERENCES `lenasys`.`Courses` (`courseID` )
86
ON DELETE NO ACTION
87
ON UPDATE NO ACTION)
65
REFERENCES `lenasys`.`Courses` (`courseID` ))
88
66
ENGINE = InnoDB;
89
67
90
68
-- -----------------------------------------------------
100
78
`openingDate` DATETIME NULL , -- time for the student to do the quiz
101
79
`closingDate` DATETIME NULL ,
102
80
PRIMARY KEY (`quizNr`, `courseID`) ,
103
INDEX `fk_Quizzes_Categories1_idx` (`categoryName` ASC , `courseID` ASC) ,
104
UNIQUE INDEX `quizNr_UNIQUE` (`quizNr` ASC) ,
105
CONSTRAINT `fk_Quizzes_Categories1`
106
81
FOREIGN KEY (`categoryName`, `courseID` )
107
REFERENCES `lenasys`.`Categories` (`categoryName`, `courseID` )
108
ON DELETE NO ACTION
109
ON UPDATE NO ACTION)
82
REFERENCES `lenasys`.`Categories` (`categoryName`, `courseID` ))
110
83
ENGINE = InnoDB;
111
84
112
85
-- -----------------------------------------------------
119
92
`questionData` VARCHAR(45) NULL , -- the question
120
93
`correctAnswer` VARCHAR(45) NULL , -- the answer
121
94
PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,
122
UNIQUE INDEX `questionID_UNIQUE` (`questionID` ASC) ,
123
INDEX `fk_QuizQuestions_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
124
CONSTRAINT `fk_QuizQuestions_Quizzes1`
125
95
FOREIGN KEY (`quizNr` , `courseID` )
126
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
127
ON DELETE NO ACTION
128
ON UPDATE NO ACTION)
96
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
129
97
ENGINE = InnoDB;
130
98
131
99
-- -----------------------------------------------------
143
111
`grade` VARCHAR(8) NULL ,
144
112
`gradeComment` VARCHAR(200) NULL ,
145
113
PRIMARY KEY (`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
146
INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
147
CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`
148
114
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
149
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )
150
ON DELETE NO ACTION
151
ON UPDATE NO ACTION,
152
CONSTRAINT `fk_AssignedQuizzes_Quizzes1`
115
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` ),
153
116
FOREIGN KEY (`quizNr` , `quizCourseID` )
154
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
155
ON DELETE NO ACTION
156
ON UPDATE NO ACTION)
117
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` ))
157
118
ENGINE = InnoDB;
158
119
159
120
165
126
`exampleName` VARCHAR(20) NOT NULL ,
166
127
`categoryName` VARCHAR(64) NOT NULL ,
167
128
`courseID` VARCHAR(10) NOT NULL ,
168
`orderNr` 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
169
130
`description` VARCHAR(200) NULL ,
170
131
PRIMARY KEY (`exampleName`, `categoryName`, `courseID`) ,
171
INDEX `fk_Examples_Courses1_idx` (`courseID` ASC) ,
172
UNIQUE INDEX `exampleName_UNIQUE` (`exampleName` ASC) ,
173
CONSTRAINT `fk_Examples_Categories1`
174
132
FOREIGN KEY (`categoryName` )
175
REFERENCES `lenasys`.`Categories` (`categoryName` )
176
ON DELETE NO ACTION
177
ON UPDATE NO ACTION,
178
CONSTRAINT `fk_Examples_Courses1`
133
REFERENCES `lenasys`.`Categories` (`categoryName` ),
179
134
FOREIGN KEY (`courseID` )
180
REFERENCES `lenasys`.`Courses` (`courseID` )
181
ON DELETE NO ACTION
182
ON UPDATE NO ACTION)
135
REFERENCES `lenasys`.`Courses` (`courseID` ))
183
136
ENGINE = InnoDB;
184
137
185
138
-- -----------------------------------------------------
192
145
`categoryName` VARCHAR(64) NOT NULL ,
193
146
`courseID` VARCHAR(10) NOT NULL ,
194
147
PRIMARY KEY (`columnNr`, `orderNr`,`exampleName`,`categoryName`, `courseID`) ,
195
UNIQUE INDEX `columNr_UNIQUE` (`columnNr` ASC) ,
196
INDEX `fk_Containers_Examples1_idx` (`exampleName` ASC, `categoryName` ASC, `courseID` ASC) ,
197
CONSTRAINT `fk_Containers_Examples1`
198
148
FOREIGN KEY (`exampleName` ,`categoryName` , `courseID` )
199
REFERENCES `lenasys`.`Examples` (`exampleName` ,`categoryName` , `courseID` )
200
ON DELETE NO ACTION
201
ON UPDATE NO ACTION)
149
REFERENCES `lenasys`.`Examples` (`exampleName` ,`categoryName` , `courseID` ))
202
150
ENGINE = InnoDB;
203
151
204
152
-- -----------------------------------------------------
209
157
`fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild
210
158
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
211
159
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
212
PRIMARY KEY (`fileName`) ,
213
UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )
160
PRIMARY KEY (`fileName`))
214
161
ENGINE = InnoDB;
215
162
216
217
218
163
-- -----------------------------------------------------
219
164
-- Table `lenasys`.`ContainerFiles`
220
165
-- -----------------------------------------------------
221
CREATE TABLE IF NOT EXISTS `lenasys`.`ContainerFiles` (
222
`fileName` VARCHAR(20) NOT NULL ,
223
`columnNr` INT NOT NULL ,
224
`orderNr` INT NOT NULL , -- the order of the examples in the same category
225
`exampleName` VARCHAR(20) NOT NULL ,
226
`categoryName` VARCHAR(64) NOT NULL ,
227
`courseID` VARCHAR(10) NOT NULL ,
228
PRIMARY KEY (`fileName`, `columnNr`, `exampleName`,`categoryName`, `courseID`) ,
229
INDEX `fk_ContainerFiles_Files1_idx` (`fileName` ASC) ,
230
CONSTRAINT `fk_ContainerFiles_Files1`
231
FOREIGN KEY (`fileName` )
232
REFERENCES `lenasys`.`Files` (`fileName` )
233
ON DELETE NO ACTION
234
ON UPDATE NO ACTION,
235
INDEX `fk_ContainerFiles_Containers1_idx` (`columnNr` ASC,`orderNr` ASC,`exampleName` ASC, `courseID` ASC) ,
236
CONSTRAINT `fk_ContainerFiles_Containers1`
237
FOREIGN KEY (`columnNr` , `orderNr`, `exampleName` , `courseID` )
238
REFERENCES `lenasys`.`Containers` (`columnNr` ,`orderNr`, `exampleName` , `courseID` )
239
ON DELETE NO ACTION
240
ON UPDATE NO ACTION)
241
ENGINE = InnoDB;
242
243
244
245
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
246
180
-- -----------------------------------------------------
247
181
-- Table `lenasys`.`FileInterestingLines`
248
182
-- -----------------------------------------------------
249
183
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
250
`id` INT NOT NULL AUTO_INCREMENT ,
251
`fileName` VARCHAR(20) NOT NULL ,
252
`columnNr` INT NOT NULL ,
253
`orderNr` INT NOT NULL , -- the order of the examples in the same category
254
`exampleName` VARCHAR(20) NOT NULL ,
255
`categoryName` VARCHAR(64) NOT NULL ,
256
`courseID` VARCHAR(10) NOT NULL ,
257
`beginAt` INT NULL ,
258
`endAt` INT NULL ,
259
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`categoryName`, `courseID`) ,
260
INDEX `fk_FileInterestingLines_Files1_idx` (`fileName` ASC) ,
261
UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
262
CONSTRAINT `fk_FileInterestingLines_Files1`
263
FOREIGN KEY (`fileName` )
264
REFERENCES `lenasys`.`Files` (`fileName` )
265
ON DELETE NO ACTION
266
ON UPDATE NO ACTION,
267
CONSTRAINT `fk_FileInterestingLines_ContainerFiles1`
268
FOREIGN KEY (`columnNr` , `orderNr`, `exampleName` , `categoryName`, `courseID` )
269
REFERENCES `lenasys`.`ContainerFiles` (`columnNr` ,`orderNr`, `exampleName` , `categoryName`, `courseID` )
270
ON DELETE NO ACTION
271
ON UPDATE NO ACTION)
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`))
272
198
ENGINE = InnoDB;
273
199
274
200
-- -----------------------------------------------------
281
207
`categoryName` VARCHAR(64) NOT NULL ,
282
208
`courseID` VARCHAR(10) NOT NULL ,
283
209
PRIMARY KEY (`id`, `keyword`, `exampleName`, `categoryName`, `courseID`) ,
284
UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
285
INDEX `fk_Keywords_Examples1_idx` (`exampleName` ASC, `categoryName` ASC, `courseID` ASC) ,
286
CONSTRAINT `fk_Keywords_Examples1`
287
210
FOREIGN KEY (`exampleName`, `categoryName`, `courseID` )
288
REFERENCES `lenasys`.`Examples` (`exampleName`, `categoryName`, `courseID` )
289
ON DELETE NO ACTION
290
ON UPDATE NO ACTION)
211
REFERENCES `lenasys`.`Examples` (`exampleName`, `categoryName`, `courseID` ))
291
212
ENGINE = InnoDB;
292
213
293
214
305
226
`quizNr` INT NOT NULL ,
306
227
`loginTimeStamp` DATETIME NOT NULL ,
307
228
`success` BOOLEAN NOT NULL ,
308
PRIMARY KEY (`id`) ,
309
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
229
PRIMARY KEY (`id`))
310
230
ENGINE = InnoDB;
311
231
-- -------------------------------------------------
312
232
-- Table `lenasys`.`logAssignedQuizzesAnswers`
324
244
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ??
325
245
`grade` VARCHAR(8) NULL ,
326
246
`gradeComment` VARCHAR(200) NULL ,
327
PRIMARY KEY (`id`) ,
328
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
247
PRIMARY KEY (`id`))
329
248
330
249
ENGINE = InnoDB;
331
250
-- -------------------------------------------------
349
268
`screenResolution` VARCHAR(20) NOT NULL ,
350
269
`logTimeStamp` DATETIME NOT NULL ,
351
270
`runtime` INT NOT NULL ,
352
PRIMARY KEY (`id`) ,
353
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
271
PRIMARY KEY (`id`))
354
272
ENGINE = InnoDB;
355
273
-- -------------------------------------------------
356
274
insert into `lenasys`.`UserTypeCodes` (`userTypeCode`,`userType`)
357
275
VALUES (0,'Teacher'),(1,'Student');
358
276
359
277
insert into `lenasys`.`Users` (`userName`,`name`,`passwd`,`userType`,`ssn`)
360
VALUES ('sha1','Per Student',sha1('Syp9393'),1,'19900385-2345'),
361
('md5','Per L�rare',md5('Syp9393'),0,'19800385-2325');
362
278
VALUES ('student','Per Student',sha1('Syp9393'),1,'19900385-2345'),
279
('l�rare','Kalle L�rare',sha1('Syp9393'),0,'19800385-2325');
280
363
281
364
282
365
283
-- -----------------------------------------------------
366
284
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction.
367
SET SQL_MODE=@OLD_SQL_MODE;
368
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
369
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