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 55
- compare with another revision
- compare with revision 55
- download diff
- download tarball
- view history from revision 55
- Committer: Gustav Hatvigsson
- Date: 2013-05-07 11:53:54 UTC
- mfrom: (54.1.1 lenasys)
- Revision ID: gustav.hartvigsson@gmail.com-20130507115354-e0cl6rbsno8oanzh
merge
- files modified:
- databas.sql
added
removed
databas.sql
2
2
USE `lenasys` ;
3
3
-- -----------------------------------------------------
4
4
5
5
-- -----------------------------------------------------
6
-- Table `lenasys`.`Courses`
7
-- -----------------------------------------------------
8
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` (
9
`courseID` VARCHAR(10) NOT NULL ,
10
`name` VARCHAR(45) NULL ,
11
`courseData` VARCHAR(128) NULL , -- explenation about course, example: G1N 7,5hp + additional text
12
PRIMARY KEY (`courseID`))
13
ENGINE = InnoDB;
6
14
7
15
-- -----------------------------------------------------
8
16
-- Table `lenasys`.`Users`
15
23
`passwd` CHAR(40) NULL ,
16
24
`passwdHint` CHAR(100) NULL ,
17
25
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
18
PRIMARY KEY (`userName`) )
19
ENGINE = InnoDB;
20
21
22
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` (
23
`courseID` VARCHAR(10) NOT NULL ,
24
`name` VARCHAR(45) NULL ,
25
`courseData` VARCHAR(128) NULL , -- explenation about course, example: G1N 7,5hp + additional text
26
PRIMARY KEY (`courseID`))
27
ENGINE = InnoDB;
26
`firstLogin` BIT NOT NULL DEFAULT 1, -- 1=true 0=false
27
`activeCourse` VARCHAR(10) NULL,
28
`email` VARCHAR(128) NULL,
29
PRIMARY KEY (`userName`) ,
30
FOREIGN KEY (`activeCourse` )
31
REFERENCES `lenasys`.`Courses` (`courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
32
ENGINE = InnoDB;
33
34
35
28
36
29
37
-- -----------------------------------------------------
30
38
-- Table `lenasys`.`StudentCourseRegistrations`
38
43
`courseID` VARCHAR(10) NOT NULL , -- Example G14234
39
44
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
40
45
FOREIGN KEY (`userName` )
41
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE,
46
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE ON DELETE CASCADE,
42
47
FOREIGN KEY (`courseID` )
43
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
48
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
44
49
ENGINE = InnoDB;
45
50
46
51
-- -----------------------------------------------------
53
58
`orderNr` INT NOT NULL , -- the order of the examples in the same category
54
59
PRIMARY KEY (`categoryName`, `courseID`) ,
55
60
FOREIGN KEY (`courseID` )
56
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE)
61
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
57
62
ENGINE = InnoDB;
58
63
59
64
-- -----------------------------------------------------
66
71
`orderNr` INT NOT NULL , -- the order of the examples in the same category
67
72
PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) ,
68
73
FOREIGN KEY (`categoryName` , `courseID` )
69
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE)
74
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
70
75
ENGINE = InnoDB;
71
76
72
77
-- -----------------------------------------------------
85
90
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
86
91
PRIMARY KEY (`quizNr`, `subCategoryName`, `categoryName` , `courseID`) ,
87
92
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`)
88
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE)
93
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
89
94
ENGINE = InnoDB;
90
95
91
96
-- -----------------------------------------------------
101
106
`correctAnswer` VARCHAR(45) NULL , -- the answer
102
107
PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` , `courseID`) ,
103
108
FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`, `courseID` )
104
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE)
109
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
105
110
ENGINE = InnoDB;
106
111
107
112
-- -----------------------------------------------------
122
127
`gradeComment` VARCHAR(200) NULL ,
123
128
PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) ,
124
129
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
125
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE,
130
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE,
126
131
FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` )
127
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
132
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
128
133
ENGINE = InnoDB;
129
134
130
135
142
147
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
143
148
PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) ,
144
149
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` )
145
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE)
150
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
146
151
ENGINE = InnoDB;
147
152
148
153
-- -----------------------------------------------------
149
154
-- Table `lenasys`.`Files`
150
155
-- -----------------------------------------------------
151
156
CREATE TABLE IF NOT EXISTS `lenasys`.`Files` (
152
`fileName` VARCHAR(256) NOT NULL ,
153
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
154
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
155
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
157
`fileName` VARCHAR(228) NOT NULL ,
158
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4
159
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
160
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable.
156
161
`updatedAt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
157
162
PRIMARY KEY (`fileName`))
158
163
ENGINE = InnoDB;
162
167
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` (
163
168
`columnNr` INT NOT NULL ,
164
169
`orderNr` INT NOT NULL , -- the order of the examples in the same category
165
`fileName` VARCHAR(20) NOT NULL , -- primary key??
170
`fileName` VARCHAR(228) NOT NULL , -- primary key??
166
171
`exampleName` VARCHAR(20) NOT NULL ,
167
172
`subCategoryName` VARCHAR(64) NOT NULL ,
168
173
`categoryName` VARCHAR(64) NOT NULL ,
170
175
`executable` BIT , -- 1=true and 0=false
171
176
PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) ,
172
177
FOREIGN KEY (`fileName` )
173
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE,
178
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE ON DELETE CASCADE,
174
179
FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )
175
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE)
180
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
176
181
ENGINE = InnoDB;
177
182
178
183
-- -----------------------------------------------------
180
185
-- -----------------------------------------------------
181
186
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
182
187
`id` INT NOT NULL AUTO_INCREMENT ,
183
`fileName` VARCHAR(256) NOT NULL ,
188
`fileName` VARCHAR(228) NOT NULL ,
184
189
`columnNr` INT NOT NULL ,
185
190
`orderNr` INT NOT NULL ,
186
191
`exampleName` VARCHAR(20) NOT NULL ,
191
196
`endAt` INT NULL ,
192
197
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) ,
193
198
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)
194
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE)
199
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE ON DELETE CASCADE)
195
200
ENGINE = InnoDB;
196
201
197
202
-- -----------------------------------------------------
205
210
`courseID` VARCHAR(10) NOT NULL ,
206
211
PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) ,
207
212
FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` )
208
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE)
213
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE)
209
214
ENGINE = InnoDB;
210
215
211
216
212
217
-- -----------------------------------------------------
218
-- Table `lenasys`.`logUserLoginAttempts`
213
219
-- -----------------------------------------------------
214
CREATE TABLE IF NOT EXISTS `lenasys`.`logUsersQuizLogins` (
215
`id` INT NOT NULL ,
220
CREATE TABLE IF NOT EXISTS `lenasys`.`logUserLoginAttempts` (
221
`id` INT NOT NULL AUTO_INCREMENT,
216
222
`userName` VARCHAR(20) NOT NULL ,
217
223
`userAgent` VARCHAR(200) NOT NULL , -- web browser and version
218
224
`userIP` VARCHAR(20) NOT NULL , -- ip-number
219
`browserID` VARCHAR(64) /*NOT NULL*/ , -- autogenerated id for local-storage
220
`courseID` VARCHAR(10) NOT NULL ,
221
`courseOccasion` VARCHAR(16) NULL ,
222
`quizNr` INT NOT NULL ,
223
`loginTimeStamp` DATETIME NOT NULL ,
225
`browserID` VARCHAR(64) NOT NULL , -- autogenerated id for local-storage
226
`loginTimeStamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
224
227
`success` BIT NOT NULL , -- 1=true and 0=false
225
228
PRIMARY KEY (`id`))
226
229
ENGINE = InnoDB;
270
272
-- -------------------------------------------------
271
273
272
274
INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`)
273
VALUES ('student','Per Student',sha1('Syp9393'),'gamla vanliga' ,2,'19900385-2345'),
274
('student2','Gösta Student',sha1('Syp9393'),'SypARN',2,'19800385-2385'),
275
('lärare','Kalle Lärare',sha1('Syp9393'),'Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325');
275
VALUES ('student','Per Student','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345'),
276
('student2','Gösta Student','06d31954049ffa053039ae83bab3dcd0ebc67195','SypARN',2,'19800385-2385'),
277
('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325');
276
278
277
279
INSERT INTO `Courses`(`courseID`, `name`, `courseData`)
278
280
VALUES('DA133G' , 'Webbutveckling - datorgrafik G1N' , ' 7,5hp (IKI)'),
Loggerhead is a web-based interface for Breezy
Version: 2.0.2.dev0