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