bzr branch
http://gegoxaren.bato24.eu/bzr/lenasys/trunk
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
1 |
DROP DATABASE `lenasys` ; |
2 |
CREATE SCHEMA IF NOT EXISTS `lenasys` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; |
|
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
3 |
USE `lenasys` ; |
|
32.1.1
by b11johgu
Added database and example student. |
4 |
-- ----------------------------------------------------- |
|
50.1.1
by galaxyAbstractor
Started implementing categories and a menu |
5 |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
6 |
-- ----------------------------------------------------- |
7 |
-- Table `lenasys`.`Courses` |
|
8 |
-- ----------------------------------------------------- |
|
9 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Courses` ( |
|
10 |
`courseID` VARCHAR(10) NOT NULL , |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
11 |
`name` VARCHAR(50) NULL , |
|
69.3.1
by b11johgu
added "Hidden" and "published" in course-table |
12 |
`courseData` VARCHAR(128) NULL , -- explanation about course, example: G1N 7,5hp + additional text |
|
109.1.2
by c11emian
Added the functions getPublicCourses(), getPrivateCourses() and getStudentCourses(). |
13 |
`isHidden` BIT(1) NOT NULL DEFAULT 0 , -- Published or not |
14 |
`isPublic` BIT(1) NOT NULL DEFAULT 1 , -- Public or not |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
15 |
PRIMARY KEY (`courseID`)) |
16 |
ENGINE = InnoDB; |
|
|
36.4.11
by b11johgu
fixed bugg #1171788 |
17 |
|
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
18 |
-- ----------------------------------------------------- |
|
29.1.3
by b11johgu
changed the order on the tables. |
19 |
-- Table `lenasys`.`Users` |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
20 |
-- ----------------------------------------------------- |
|
36.2.1
by Johan Gustavsson
fixed bugg #1170270 |
21 |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
22 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Users` ( |
23 |
`userName` VARCHAR(20) NOT NULL , |
|
|
36.4.13
by b11johgu
fixed minor buggs |
24 |
`userType` ENUM('Teacher','Student') DEFAULT 'Student' , -- 1=teacher and 2=student |
|
29.1.3
by b11johgu
changed the order on the tables. |
25 |
`name` VARCHAR(45) NULL , |
|
36.2.1
by Johan Gustavsson
fixed bugg #1170270 |
26 |
`passwd` CHAR(40) NULL , |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
27 |
`passwdHint` CHAR(100) NULL , |
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
28 |
`ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' , |
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
29 |
`firstLogin` BIT(1) NOT NULL DEFAULT 1, -- 1=true 0=false |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
30 |
`activeCourse` VARCHAR(10) NULL, |
31 |
`email` VARCHAR(128) NULL, |
|
32 |
PRIMARY KEY (`userName`) , |
|
33 |
FOREIGN KEY (`activeCourse` ) |
|
34 |
REFERENCES `lenasys`.`Courses` (`courseID`)ON UPDATE CASCADE ON DELETE CASCADE) |
|
35 |
ENGINE = InnoDB; |
|
36 |
||
|
69.3.1
by b11johgu
added "Hidden" and "published" in course-table |
37 |
-- ----------------------------------------------------- |
38 |
-- Table `lenasys`.`Permissions` |
|
39 |
-- ----------------------------------------------------- |
|
40 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Permissions` ( |
|
41 |
`courseID` VARCHAR(10) NOT NULL , |
|
42 |
`userName` VARCHAR(20) NOT NULL , |
|
43 |
PRIMARY KEY (`courseID`, `userName`) , |
|
44 |
FOREIGN KEY (`courseID` ) |
|
45 |
REFERENCES `lenasys`.`Courses` (`courseID` ) |
|
46 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
47 |
FOREIGN KEY (`userName` ) |
|
48 |
REFERENCES `lenasys`.`Users` (`userName` ) |
|
49 |
ON UPDATE CASCADE ON DELETE CASCADE) |
|
50 |
ENGINE = InnoDB; |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
51 |
|
52 |
||
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
53 |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
54 |
-- ----------------------------------------------------- |
55 |
-- Table `lenasys`.`StudentCourseRegistrations` |
|
56 |
-- ----------------------------------------------------- |
|
57 |
CREATE TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` ( |
|
|
29.1.7
by b11johgu
fixed comments and an attribute |
58 |
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2 |
|
29.1.3
by b11johgu
changed the order on the tables. |
59 |
`userName` VARCHAR(20) NOT NULL , |
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
60 |
`courseID` VARCHAR(10) NOT NULL , -- Example G14234 |
61 |
PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) , |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
62 |
FOREIGN KEY (`userName` ) |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
63 |
REFERENCES `lenasys`.`Users` (`userName` )ON UPDATE CASCADE ON DELETE CASCADE, |
|
29.1.3
by b11johgu
changed the order on the tables. |
64 |
FOREIGN KEY (`courseID` ) |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
65 |
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
29.1.3
by b11johgu
changed the order on the tables. |
66 |
ENGINE = InnoDB; |
67 |
||
68 |
-- ----------------------------------------------------- |
|
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
69 |
-- Table `lenasys`.`Categories` |
70 |
-- ----------------------------------------------------- |
|
71 |
||
72 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Categories` ( |
|
73 |
`categoryName` VARCHAR(64) NOT NULL , |
|
74 |
`courseID` VARCHAR(10) NOT NULL , |
|
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
75 |
`orderNr` INT NOT NULL , -- the order of the examples in the same category |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
76 |
PRIMARY KEY (`categoryName`, `courseID`) , |
77 |
FOREIGN KEY (`courseID` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
78 |
REFERENCES `lenasys`.`Courses` (`courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
79 |
ENGINE = InnoDB; |
80 |
||
81 |
-- ----------------------------------------------------- |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
82 |
-- Table `lenasys`.`SubCategories` |
83 |
-- ----------------------------------------------------- |
|
|
36.4.13
by b11johgu
fixed minor buggs |
84 |
CREATE TABLE IF NOT EXISTS `lenasys`.`SubCategories` ( |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
85 |
`subCategoryName` VARCHAR(64) NOT NULL , |
86 |
`categoryName` VARCHAR(64) NOT NULL , |
|
87 |
`courseID` VARCHAR(10) NOT NULL , |
|
88 |
`orderNr` INT NOT NULL , -- the order of the examples in the same category |
|
89 |
PRIMARY KEY (`subCategoryName`, `categoryName`, `courseID`) , |
|
90 |
FOREIGN KEY (`categoryName` , `courseID` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
91 |
REFERENCES `lenasys`.`Categories` (`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
92 |
ENGINE = InnoDB; |
93 |
||
94 |
-- ----------------------------------------------------- |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
95 |
-- Table `lenasys`.`Quizzes` |
96 |
-- ----------------------------------------------------- |
|
97 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Quizzes` ( |
|
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
98 |
`quizNr` INT NOT NULL , |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
99 |
`subCategoryName` VARCHAR(64) NOT NULL , |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
100 |
`categoryName` VARCHAR(64) NOT NULL , |
|
29.1.3
by b11johgu
changed the order on the tables. |
101 |
`courseID` VARCHAR(10) NOT NULL , |
102 |
`quizData` VARCHAR(45) NULL , |
|
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
103 |
`allowMultipleReplies` BIT(1) NULL , -- 1=true and 0=false |
104 |
`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 |
|
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
105 |
`openingDate` DATETIME NULL , -- time for the student to do the quiz |
|
29.1.3
by b11johgu
changed the order on the tables. |
106 |
`closingDate` DATETIME NULL , |
|
62.2.1
by b11johgu
fixed currenttimestamp |
107 |
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , |
|
36.4.13
by b11johgu
fixed minor buggs |
108 |
PRIMARY KEY (`quizNr`, `subCategoryName`, `categoryName` , `courseID`) , |
109 |
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID`) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
110 |
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`, `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
29.1.3
by b11johgu
changed the order on the tables. |
111 |
ENGINE = InnoDB; |
112 |
||
113 |
-- ----------------------------------------------------- |
|
114 |
-- Table `lenasys`.`QuizQuestions` |
|
115 |
-- ----------------------------------------------------- |
|
116 |
CREATE TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` ( |
|
|
29.1.5
by b11johgu
corrected the order of tables/attributes further |
117 |
`questionID` INT NOT NULL , -- The id of the individual question in the quiz. |
118 |
`quizNr` INT NOT NULL , -- The quiz from wich the question is from |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
119 |
`subCategoryName` VARCHAR(64) NOT NULL , |
120 |
`categoryName` VARCHAR(64) NOT NULL , |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
121 |
`courseID` VARCHAR(10) NOT NULL , |
|
29.1.5
by b11johgu
corrected the order of tables/attributes further |
122 |
`questionData` VARCHAR(45) NULL , -- the question |
123 |
`correctAnswer` VARCHAR(45) NULL , -- the answer |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
124 |
PRIMARY KEY (`questionID`, `quizNr`, `subCategoryName`,`categoryName` , `courseID`) , |
125 |
FOREIGN KEY (`quizNr` ,`subCategoryName` ,`categoryName`, `courseID` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
126 |
REFERENCES `lenasys`.`Quizzes` (`quizNr` , `subCategoryName`,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
29.1.3
by b11johgu
changed the order on the tables. |
127 |
ENGINE = InnoDB; |
128 |
||
129 |
-- ----------------------------------------------------- |
|
130 |
-- Table `lenasys`.`AssignedQuizzes` |
|
131 |
-- ----------------------------------------------------- |
|
132 |
CREATE TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` ( |
|
|
29.1.7
by b11johgu
fixed comments and an attribute |
133 |
`courseOccasion` VARCHAR(16) NOT NULL , -- Example HT2012 period 2 |
|
29.1.5
by b11johgu
corrected the order of tables/attributes further |
134 |
`userName` VARCHAR(20) NOT NULL , -- wich student took the quiz |
|
29.1.3
by b11johgu
changed the order on the tables. |
135 |
`courseID` VARCHAR(10) NOT NULL , |
136 |
`quizNr` INT NOT NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
137 |
`subCategoryName` VARCHAR(64) NOT NULL , |
138 |
`categoryName` VARCHAR(64) NOT NULL , |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
139 |
`quizCourseID` VARCHAR(10) NOT NULL , |
|
29.1.5
by b11johgu
corrected the order of tables/attributes further |
140 |
`answers` VARCHAR(45) NULL , -- answers provided by the student |
141 |
`answerHash` VARCHAR(45) NULL , -- a security measure |
|
142 |
`answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz |
|
|
29.1.3
by b11johgu
changed the order on the tables. |
143 |
`grade` VARCHAR(8) NULL , |
144 |
`gradeComment` VARCHAR(200) NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
145 |
PRIMARY KEY (`courseOccasion` ,`userName`, `courseID`, `quizNr`, `quizCourseID`) , |
|
29.1.3
by b11johgu
changed the order on the tables. |
146 |
FOREIGN KEY (`courseOccasion`,`userName` , `courseID` ) |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
147 |
REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE, |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
148 |
FOREIGN KEY (`quizNr` ,`subCategoryName`,`categoryName`, `quizCourseID` ) |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
149 |
REFERENCES `lenasys`.`Quizzes` (`quizNr` ,`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
29.1.3
by b11johgu
changed the order on the tables. |
150 |
ENGINE = InnoDB; |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
151 |
|
|
36.4.6
by b11johgu
changed primary key in UserTypeCodes to UserTypeCode |
152 |
|
153 |
||
154 |
-- ----------------------------------------------------- |
|
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
155 |
-- Table `lenasys`.`Examples` |
156 |
-- ----------------------------------------------------- |
|
157 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Examples` ( |
|
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
158 |
`exampleName` VARCHAR(20) NOT NULL , |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
159 |
`subCategoryName` VARCHAR(64) NOT NULL , |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
160 |
`categoryName` VARCHAR(64) NOT NULL , |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
161 |
`courseID` VARCHAR(10) NOT NULL , |
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
162 |
`orderNr` INT NOT NULL , -- the order of the examples in the same category |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
163 |
`description` VARCHAR(200) NULL , |
|
62.2.1
by b11johgu
fixed currenttimestamp |
164 |
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
165 |
PRIMARY KEY (`exampleName`, `subCategoryName`, `categoryName`, `courseID`) , |
166 |
FOREIGN KEY (`subCategoryName`, `categoryName`, `courseID` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
167 |
REFERENCES `lenasys`.`SubCategories` (`subCategoryName`,`categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
168 |
ENGINE = InnoDB; |
169 |
||
|
29.1.3
by b11johgu
changed the order on the tables. |
170 |
-- ----------------------------------------------------- |
171 |
-- Table `lenasys`.`Files` |
|
172 |
-- ----------------------------------------------------- |
|
173 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Files` ( |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
174 |
`fileName` VARCHAR(228) NOT NULL , |
175 |
`fileType` ENUM('Text', 'Code', 'Video', 'Picture') DEFAULT 'text' , -- text=1, code=2, video=3 picture=4 |
|
176 |
`codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m. |
|
177 |
`dataBlob` BLOB NULL , -- This is where the binary file is stored. Searchable. |
|
|
62.2.1
by b11johgu
fixed currenttimestamp |
178 |
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , |
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
179 |
PRIMARY KEY (`fileName`)) |
|
29.1.3
by b11johgu
changed the order on the tables. |
180 |
ENGINE = InnoDB; |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
181 |
-- ----------------------------------------------------- |
182 |
-- Table `lenasys`.`Containers` |
|
183 |
-- ----------------------------------------------------- |
|
184 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Containers` ( |
|
185 |
`columnNr` INT NOT NULL , |
|
186 |
`orderNr` INT NOT NULL , -- the order of the examples in the same category |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
187 |
`fileName` VARCHAR(228) NOT NULL , -- primary key?? |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
188 |
`exampleName` VARCHAR(20) NOT NULL , |
189 |
`subCategoryName` VARCHAR(64) NOT NULL , |
|
190 |
`categoryName` VARCHAR(64) NOT NULL , |
|
191 |
`courseID` VARCHAR(10) NOT NULL , |
|
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
192 |
`executable` BIT(1) , -- 1=true and 0=false |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
193 |
PRIMARY KEY (`columnNr`, `orderNr`, `fileName`,`exampleName`,`subCategoryName`, `categoryName`, `courseID`) , |
194 |
FOREIGN KEY (`fileName` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
195 |
REFERENCES `lenasys`.`Files` (`fileName` )ON UPDATE CASCADE ON DELETE CASCADE, |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
196 |
FOREIGN KEY (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` ) |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
197 |
REFERENCES `lenasys`.`Examples` (`exampleName`,`subCategoryName` ,`categoryName` , `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
198 |
ENGINE = InnoDB; |
|
29.1.3
by b11johgu
changed the order on the tables. |
199 |
|
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
200 |
-- ----------------------------------------------------- |
201 |
-- Table `lenasys`.`FileInterestingLines` |
|
202 |
-- ----------------------------------------------------- |
|
203 |
CREATE TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` ( |
|
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
204 |
`id` INT NOT NULL AUTO_INCREMENT , |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
205 |
`fileName` VARCHAR(228) NOT NULL , |
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
206 |
`columnNr` INT NOT NULL , |
207 |
`orderNr` INT NOT NULL , |
|
208 |
`exampleName` VARCHAR(20) NOT NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
209 |
`subCategoryName` VARCHAR(64) NOT NULL , |
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
210 |
`categoryName` VARCHAR(64) NOT NULL , |
211 |
`courseID` VARCHAR(10) NOT NULL , |
|
212 |
`beginAt` INT NULL , |
|
213 |
`endAt` INT NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
214 |
PRIMARY KEY (`id`, `fileName` , `columnNr`, `exampleName`,`subCategoryName`,`categoryName`, `courseID`) , |
215 |
FOREIGN KEY (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
216 |
REFERENCES `lenasys`.`Containers` (`columnNr`, `orderNr`, `fileName`, `exampleName`,`subCategoryName`, `categoryName`, `courseID`)ON UPDATE CASCADE ON DELETE CASCADE) |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
217 |
ENGINE = InnoDB; |
218 |
||
219 |
-- ----------------------------------------------------- |
|
220 |
-- Table `lenasys`.`Keywords` |
|
221 |
-- ----------------------------------------------------- |
|
222 |
CREATE TABLE IF NOT EXISTS `lenasys`.`Keywords` ( |
|
223 |
`keyword` VARCHAR(20) NOT NULL , |
|
224 |
`exampleName` VARCHAR(20) NOT NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
225 |
`subCategoryName` VARCHAR(64) NOT NULL , |
|
36.4.7
by b11johgu
Finished database based on relationstabell v.2 |
226 |
`categoryName` VARCHAR(64) NOT NULL , |
227 |
`courseID` VARCHAR(10) NOT NULL , |
|
|
36.4.12
by b11johgu
uppdated database after "relationsdatamodell" in google drive |
228 |
PRIMARY KEY (`keyword`, `exampleName`, `subCategoryName` , `categoryName`, `courseID`) , |
229 |
FOREIGN KEY (`exampleName`,`subCategoryName` , `categoryName`, `courseID` ) |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
230 |
REFERENCES `lenasys`.`Examples` (`exampleName`, `subCategoryName` , `categoryName`, `courseID` )ON UPDATE CASCADE ON DELETE CASCADE) |
|
28.1.1
by b11johgu
added a new .sql to test alongside with the old. |
231 |
ENGINE = InnoDB; |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
232 |
|
233 |
||
234 |
-- ----------------------------------------------------- |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
235 |
-- Table `lenasys`.`logUserLoginAttempts` |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
236 |
-- ----------------------------------------------------- |
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
237 |
CREATE TABLE IF NOT EXISTS `lenasys`.`logUserLoginAttempts` ( |
238 |
`id` INT NOT NULL AUTO_INCREMENT, |
|
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
239 |
`userName` VARCHAR(20) NOT NULL , |
|
36.4.2
by b11johgu
Finished log-tables. |
240 |
`userAgent` VARCHAR(200) NOT NULL , -- web browser and version |
241 |
`userIP` VARCHAR(20) NOT NULL , -- ip-number |
|
|
54.1.1
by b11johgu
Added tables in Users: firstLogin and activeCourse |
242 |
`browserID` VARCHAR(64) NOT NULL , -- autogenerated id for local-storage |
|
62.2.1
by b11johgu
fixed currenttimestamp |
243 |
`loginTimeStamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , |
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
244 |
`success` BIT(1) NOT NULL , -- 1=true and 0=false |
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
245 |
PRIMARY KEY (`id`)) |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
246 |
ENGINE = InnoDB; |
247 |
-- ------------------------------------------------- |
|
248 |
-- Table `lenasys`.`logAssignedQuizzesAnswers` |
|
249 |
-- ----------------------------------------------------- |
|
|
36.4.4
by b11johgu
Changed places on insert inty UserTypeCodes and Users |
250 |
CREATE TABLE IF NOT EXISTS `lenasys`.`logAssignedQuizzesAnswers` ( |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
251 |
`id` INT NOT NULL , |
252 |
`userName` VARCHAR(20) NOT NULL , |
|
|
36.4.2
by b11johgu
Finished log-tables. |
253 |
`userAgent` VARCHAR(200) NOT NULL , |
254 |
`userIP` VARCHAR(20) NOT NULL , |
|
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
255 |
`courseID` VARCHAR(10) NOT NULL , |
256 |
`courseOccasion` VARCHAR(16) NOT NULL , |
|
257 |
`quizNr` INT NOT NULL , |
|
258 |
`answers` VARCHAR(45) NULL , |
|
259 |
`answerHash` VARCHAR(45) NULL , |
|
|
36.4.6
by b11johgu
changed primary key in UserTypeCodes to UserTypeCode |
260 |
`answeredTimeStamp` DATETIME NULL , -- TIMESTAMP ?? |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
261 |
`grade` VARCHAR(8) NULL , |
262 |
`gradeComment` VARCHAR(200) NULL , |
|
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
263 |
PRIMARY KEY (`id`)) |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
264 |
|
265 |
ENGINE = InnoDB; |
|
266 |
-- ------------------------------------------------- |
|
267 |
-- Table `lenasys`.`logBenchmark` |
|
268 |
-- ----------------------------------------------------- |
|
|
36.4.4
by b11johgu
Changed places on insert inty UserTypeCodes and Users |
269 |
CREATE TABLE IF NOT EXISTS `lenasys`.`logBenchmark` ( |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
270 |
`id` INT NOT NULL , |
271 |
`userName` VARCHAR(20) NOT NULL , |
|
|
36.4.2
by b11johgu
Finished log-tables. |
272 |
`userAgent` VARCHAR(200) NOT NULL , |
273 |
`userIP` VARCHAR(20) NOT NULL , |
|
274 |
`browser` VARCHAR(20) NOT NULL , -- will they be used to show different views? Can we just use userAgent instead? |
|
275 |
`browserVersion` VARCHAR(20) NOT NULL , -- -||- |
|
276 |
`renderer` VARCHAR(20) NOT NULL , -- ?? |
|
277 |
`rendererVersion` VARCHAR(20) NOT NULL , -- ?? |
|
278 |
`os` VARCHAR(64) NOT NULL , |
|
279 |
`osVersion` VARCHAR(20) NOT NULL , |
|
280 |
`fps` VARCHAR(20) NOT NULL , |
|
281 |
`maxFps` VARCHAR(20) NOT NULL , |
|
|
36.4.11
by b11johgu
fixed bugg #1171788 |
282 |
`hostName` VARCHAR(20) NOT NULL , -- Ändrad till hostName |
|
36.4.2
by b11johgu
Finished log-tables. |
283 |
`app` VARCHAR(20) NOT NULL , |
284 |
`screenResolution` VARCHAR(20) NOT NULL , |
|
285 |
`logTimeStamp` DATETIME NOT NULL , |
|
286 |
`runtime` INT NOT NULL , |
|
|
36.4.10
by b11johgu
removed databas2.sql, put the code in sql so no auto-generated |
287 |
PRIMARY KEY (`id`)) |
|
36.4.1
by b11johgu
"finished" log-tables, just need further instructions about how |
288 |
ENGINE = InnoDB; |
|
36.2.1
by Johan Gustavsson
fixed bugg #1170270 |
289 |
-- ------------------------------------------------- |
290 |
||
|
119.1.1
by Erik Wikström
Edited courses data, making it more testable with different visble/public settings |
291 |
INSERT INTO `Courses`(`courseID`, `name`, `courseData`, `isPublic`, `isHidden`) |
292 |
VALUES('DA146G' , 'Computer Graphics' , ' 7,5hp (IKI)', 1, 0), |
|
293 |
('IS317G' , 'Database Construction' , ' 7,5hp (IKI)', 0, 1), |
|
294 |
('IS114G' , 'Database Systems' , ' 7,5hp (IKI)', 1, 0), |
|
295 |
('DV313G' , 'Webdevelopment - XML API' , ' 7,5hp (IKI)', 1, 0), |
|
296 |
('DA330G' , 'Webprogramming' , ' 7,5hp (IKI)', 0, 0), |
|
297 |
('DA525G' , 'Advanced Computer Graphics and Shader Programming' , ' 7,5hp (IKI)', 0, 1); |
|
|
36.4.15
by b11johgu
started inserting data into the tables |
298 |
|
|
69.3.3
by b11johgu
deleted files not used |
299 |
INSERT INTO `lenasys`.`Users` (`userName`,`name`,`passwd`, passwdHint,`userType`,`ssn`,`activeCourse`) |
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
300 |
VALUES ('student','Per Student','06d31954049ffa053039ae83bab3dcd0ebc67195','gamla vanliga' ,2,'19900385-2345', 'DA330G'), |
301 |
('student2','Gösta Student','06d31954049ffa053039ae83bab3dcd0ebc67195','SypARN',2,'19800385-2385','DA525G' ), |
|
302 |
('lärare','Kalle Lärare','06d31954049ffa053039ae83bab3dcd0ebc67195','Syp och året då Sverige först tillåter kommersiell radio x2',1,'19800385-2325', 'DV313G' ); |
|
|
69.3.3
by b11johgu
deleted files not used |
303 |
|
304 |
||
|
36.4.16
by b11johgu
added example-data in databas.sql in talbes: |
305 |
INSERT INTO `StudentCourseRegistrations`(`courseOccasion`, `userName`, `courseID`) |
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
306 |
VALUES ('HT2012 period 2','student','DV313G'), |
307 |
('HT2012 period 2','student','DA525G'), |
|
308 |
('HT2012 period 2','student2','DV313G'), |
|
309 |
('HT2012 period 2','student2','DA330G'); |
|
|
36.4.15
by b11johgu
started inserting data into the tables |
310 |
|
|
36.4.16
by b11johgu
added example-data in databas.sql in talbes: |
311 |
INSERT INTO `Categories`(`categoryName`, `courseID`, `orderNr`) |
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
312 |
VALUES ('Vektorgrafik','DA525G', 1), |
313 |
('Shading','DA525G', 2), |
|
314 |
('3D','DA525G', 3), |
|
|
85.1.23
by a11andoh
added information to the XML API course to be able to test with the system |
315 |
('SAX Examples','DV313G', 2), |
316 |
('DOM Examples 1','DV313G', 3), |
|
317 |
('DOM Examples 2 - Validation','DV313G', 4), |
|
318 |
('DOM Examples 3 - XPATH','DV313G', 5), |
|
319 |
('XSLT Examples 1 - Navigation','DV313G', 6), |
|
320 |
('XSLT 2 - Apply Template and Grouping','DV313G', 7), |
|
321 |
('PHP Introduction','DV313G', 1), |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
322 |
('Categorie i nånting','DA330G', 1), |
323 |
('Categorie i någontingen mer','DA330G', 2); |
|
|
36.4.16
by b11johgu
added example-data in databas.sql in talbes: |
324 |
|
325 |
INSERT INTO `SubCategories`(`subCategoryName`, `categoryName`, `courseID`, `orderNr`) |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
326 |
VALUES ('Punkt' ,'Vektorgrafik','DA525G', 1), |
327 |
('Linjer' ,'Vektorgrafik','DA525G', 2), |
|
328 |
('Böjningar' ,'Vektorgrafik','DA525G', 3), |
|
329 |
('Vertexshading','Shading','DA525G', 1), |
|
330 |
('Globalshading','Shading','DA525G', 2), |
|
331 |
('Bra 3d' , '3D','DA525G', 1), |
|
332 |
('Dålig 3d' , '3D','DA525G', 2), |
|
|
85.1.23
by a11andoh
added information to the XML API course to be able to test with the system |
333 |
('Introduction','PHP Introduction','DV313G', 1), |
334 |
('SAX Parsing','SAX Examples','DV313G', 1), |
|
335 |
('Validation','DOM Examples 1','DV313G', 1), |
|
336 |
('XPATH 1','DOM Examples 3 - XPATH','DV313G', 1), |
|
337 |
('XPATH 2','DOM Examples 3 - XPATH','DV313G', 2), |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
338 |
('Subcategorie i nånting' ,'Categorie i nånting','DA330G', 1), |
339 |
('Subcategorie2 i nånting' ,'Categorie i nånting','DA330G', 2), |
|
340 |
('Subcategorie i nåntingen mer', 'Categorie i någontingen mer','DA330G', 1), |
|
341 |
('Subcategorie2 i nåntingen mer', 'Categorie i någontingen mer','DA330G', 2); |
|
|
36.4.17
by b11johgu
added data in tables: |
342 |
|
343 |
||
344 |
INSERT INTO Quizzes(`quizNr`, `subCategoryName`, `categoryName`, `courseID`, `quizData`, `allowMultipleReplies`,`autoCorrected`, `openingDate`,`closingDate` ) |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
345 |
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'), |
346 |
(2,'punkt' ,'Vektorgrafik','DA525G','Lol glhf',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'), |
|
347 |
(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'), |
|
|
85.1.23
by a11andoh
added information to the XML API course to be able to test with the system |
348 |
(1,'linjer' ,'Vektorgrafik','DA525G','happ happ lycka till',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'), |
349 |
(5,'Validation' ,'DOM Examples 1','DV313G','Validation with DOM',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'), |
|
350 |
(5,'SAX Parsing' ,'SAX Examples','DV313G','parsing with SAX',1,1, '2013-05-20 08:00:00','2013-05-25 23:59:00'); |
|
351 |
||
|
36.4.17
by b11johgu
added data in tables: |
352 |
INSERT INTO QuizQuestions(`questionID`, `quizNr`, `subCategoryName`, `categoryName`, `courseID`, `questionData`, `correctAnswer`) |
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
353 |
VALUES(1,1,'punkt' ,'Vektorgrafik','DA525G','vad är roten av pi plus solens massa?','inte vet jag lol'), |
354 |
(2,1,'punkt' ,'Vektorgrafik','DA525G','hur mår du?','Bra som bara den'); |
|
|
36.4.17
by b11johgu
added data in tables: |
355 |
|
356 |
INSERT INTO Examples(`exampleName`, `subCategoryName`, `categoryName`, `courseID`, `orderNr`, `description`) |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
357 |
VALUES('punkt example 1','punkt' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om punkter'), |
358 |
('punkt example 2','punkt' ,'Vektorgrafik','DA525G',2,'nån sorts förklaring om punkter i example 2'), |
|
359 |
('ett till exempel','punkt' ,'Vektorgrafik','DA525G',3,'nån sorts förklaring om punkter igen'), |
|
|
85.1.23
by a11andoh
added information to the XML API course to be able to test with the system |
360 |
('XPATH 1a','XPATH 2' ,'DOM Examples 3 - XPATH','DV313G',1,'XPATH intro'), |
361 |
('SAX Example 1b','SAX Parsing' ,'SAX Examples','DV313G',2,'Printing of SAX parsing'), |
|
362 |
('SAX Example 1a','SAX Parsing' ,'SAX Examples','DV313G',1,'Handling of SAX parsing'), |
|
|
82.2.2
by a11andoh
Added the correct coursenames and courseID. |
363 |
('linjer example 1','linjer' ,'Vektorgrafik','DA525G',1,'nån sorts förklaring om linjer'); |
|
36.4.17
by b11johgu
added data in tables: |
364 |
|
365 |
INSERT INTO Files(`fileName`,`fileType`,`codeLanguage`, `dataBlob`) |
|
366 |
VALUES('exempel fil1',1,'html','Lorem ipsum dolor sit amet, consectetur adipiscing elit. |
|
367 |
Phasellus a tellus lacus, a dapibus velit. Integer ac lorem dui, nec condimentum purus. Nullam convallis erat a
|
|
368 |
mauris pulvinar adipiscing. In consectetur, odio sit amet dictum pulvinar, erat risus condimentum mi, eu eleifend
|
|
369 |
dolor est id elit. Pellentesque eu tellus sed sem molestie fringilla. Integer auctor arcu nec nunc pharetra non
|
|
370 |
consectetur augue viverra. Pellentesque lorem nisl, tristique sed lobortis et, tincidunt sit amet diam. Maecenas
|
|
371 |
lacinia laoreet ligula, eget pretium libero venenatis malesuada. Suspendisse eu velit in arcu consectetur dictum a quis orci.
|
|
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
372 |
Maecenas vulputate tincidunt odio sit amet interdum. CuraBIT(1)ur non ante tristique mi malesuada dictum at quis nunc.
|
|
36.4.17
by b11johgu
added data in tables: |
373 |
Suspendisse ornare leo a elit egestas eu rutrum lorem cursus. Quisque sollicitudin, nisi eget consectetur auctor,
|
374 |
leo risus blandit eros, id aliquet purus tortor a turpis. Donec venenatis blandit est quis imperdiet. Sed mauris eros,
|
|
375 |
pharetra vitae tempus vel, pretium at purus.'), |
|
376 |
||
|
57.1.1
by b11johgu
changed BIT to BIT(1) and CURRENT_TIMESTAMP to NOW() |
377 |
('exempel fil2',1,'html','Donec sed turpis ante, et dapibus augue. Pellentesque haBIT(1)ant morbi tristique |
|
36.4.17
by b11johgu
added data in tables: |
378 |
senectus et netus et malesuada fames ac turpis egestas. Sed at tellus ante. Nunc non dolor ipsum, quis lacinia felis.
|
379 |
Sed tristique, leo eu imperdiet laoreet, lacus lacus porta purus, quis laoreet nisi sem non orci. In non consequat enim.
|
|
380 |
Donec lacinia auctor convallis. Nulla facilisi. Nulla tortor mi, accumsan vel cursus nec, porta nec ante.
|
|
381 |
Aenean venenatis elit vel sem sodales vulputate. Donec adipiscing porta tortor, sed sollicitudin erat dapibus id'); |
|
382 |
||
383 |
||
|
36.2.1
by Johan Gustavsson
fixed bugg #1170270 |
384 |
-- ----------------------------------------------------- |
|
29.1.4
by b11johgu
changed the order of the attributes in the tables. |
385 |
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. |
|
36.4.11
by b11johgu
fixed bugg #1171788 |
386 |
-- ska courseID i ContainerFiles vara foreignkey ifrån contaiers??
|