bHTkFGUh

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
4
5-- -----------------------------------------------------
6-- Schema pusp2002hbg
7-- -----------------------------------------------------
8
9-- -----------------------------------------------------
10-- Schema pusp2002hbg
11-- -----------------------------------------------------
12CREATE SCHEMA IF NOT EXISTS `pusp2002hbg` DEFAULT CHARACTER SET utf8 ;
13USE `pusp2002hbg` ;
14
15-- -----------------------------------------------------
16-- Table `pusp2002hbg`.`Users`
17-- -----------------------------------------------------
18CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Users` (
19  `userId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
20  `username` VARCHAR(10) NOT NULL,
21  `password` VARCHAR(10) NOT NULL,
22  `isAdmin` TINYINT NOT NULL DEFAULT 0,
23  PRIMARY KEY (`userId`),
24  UNIQUE INDEX `username_UNIQUE` (`username` ASC))
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `pusp2002hbg`.`Projects`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Projects` (
32  `projectId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
33  `name` VARCHAR(20) NOT NULL,
34  PRIMARY KEY (`projectId`),
35  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `pusp2002hbg`.`Roles`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Roles` (
43  `roleId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
44  `role` VARCHAR(45) NOT NULL,
45  PRIMARY KEY (`roleId`),
46  UNIQUE INDEX `role_UNIQUE` (`role` ASC))
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `pusp2002hbg`.`ProjectUsers`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ProjectUsers` (
54  `projectUserId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
55  `userId` INT UNSIGNED NOT NULL,
56  `projectId` INT UNSIGNED NOT NULL,
57  `roleId` INT UNSIGNED NOT NULL,
58  PRIMARY KEY (`projectUserId`),
59  INDEX `fk_ProjectUsers_Users_idx` (`userId` ASC),
60  INDEX `fk_ProjectUsers_Projects1_idx` (`projectId` ASC),
61  INDEX `fk_ProjectUsers_Roles1_idx` (`roleId` ASC),
62  UNIQUE INDEX `index5` (`userId` ASC, `projectId` ASC),
63  CONSTRAINT `fk_ProjectUsers_Users`
64    FOREIGN KEY (`userId`)
65    REFERENCES `pusp2002hbg`.`Users` (`userId`)
66    ON DELETE CASCADE
67    ON UPDATE NO ACTION,
68  CONSTRAINT `fk_ProjectUsers_Projects1`
69    FOREIGN KEY (`projectId`)
70    REFERENCES `pusp2002hbg`.`Projects` (`projectId`)
71    ON DELETE CASCADE
72    ON UPDATE NO ACTION,
73  CONSTRAINT `fk_ProjectUsers_Roles1`
74    FOREIGN KEY (`roleId`)
75    REFERENCES `pusp2002hbg`.`Roles` (`roleId`)
76    ON DELETE CASCADE
77    ON UPDATE NO ACTION)
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `pusp2002hbg`.`TimeReports`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`TimeReports` (
85  `timeReportId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
86  `projectUserId` INT UNSIGNED NOT NULL,
87  `signedById` INT UNSIGNED NULL,
88  `signedAt` TIMESTAMP NULL,
89  `year` INT(4) UNSIGNED NOT NULL,
90  `week` INT(2) UNSIGNED NOT NULL,
91  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
92  `finished` TINYINT NULL DEFAULT 0,
93  PRIMARY KEY (`timeReportId`),
94  INDEX `fk_TimeReports_ProjectUsers1_idx` (`projectUserId` ASC),
95  INDEX `fk_TimeReports_ProjectUsers2_idx` (`signedById` ASC),
96  CONSTRAINT `fk_TimeReports_ProjectUsers1`
97    FOREIGN KEY (`projectUserId`)
98    REFERENCES `pusp2002hbg`.`ProjectUsers` (`projectUserId`)
99    ON DELETE CASCADE
100    ON UPDATE NO ACTION,
101  CONSTRAINT `fk_TimeReports_ProjectUsers2`
102    FOREIGN KEY (`signedById`)
103    REFERENCES `pusp2002hbg`.`ProjectUsers` (`projectUserId`)
104    ON DELETE CASCADE
105    ON UPDATE NO ACTION)
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `pusp2002hbg`.`ActivityTypes`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivityTypes` (
113  `activityTypeId` INT UNSIGNED NOT NULL,
114  `type` VARCHAR(17) NOT NULL,
115  PRIMARY KEY (`activityTypeId`),
116  UNIQUE INDEX `type_UNIQUE` (`type` ASC))
117ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `pusp2002hbg`.`ActivitySubTypes`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivitySubTypes` (
124  `activitySubTypeId` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
125  `activityTypeId` INT UNSIGNED NOT NULL,
126  `subType` VARCHAR(1) NOT NULL,
127  PRIMARY KEY (`activitySubTypeId`),
128  INDEX `fk_ActivitySubTypes_ActivityTypes1_idx` (`activityTypeId` ASC),
129  CONSTRAINT `fk_ActivitySubTypes_ActivityTypes1`
130    FOREIGN KEY (`activityTypeId`)
131    REFERENCES `pusp2002hbg`.`ActivityTypes` (`activityTypeId`)
132    ON DELETE CASCADE
133    ON UPDATE NO ACTION)
134ENGINE = InnoDB;
135
136
137-- -----------------------------------------------------
138-- Table `pusp2002hbg`.`ActivityReports`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivityReports` (
141  `activityReportId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
142  `timeReportId` INT UNSIGNED NOT NULL,
143  `activityTypeId` INT UNSIGNED NOT NULL,
144  `activitySubTypeId` INT(3) UNSIGNED NULL,
145  `reportDate` DATE NOT NULL,
146  `minutes` INT(4) UNSIGNED NOT NULL DEFAULT 0,
147  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
148  PRIMARY KEY (`activityReportId`),
149  INDEX `fk_ActivityReports_TimeReports1_idx` (`timeReportId` ASC),
150  INDEX `fk_ActivityReports_ActivitySubTypes1_idx` (`activitySubTypeId` ASC),
151  INDEX `fk_ActivityReports_ActivityTypes1_idx` (`activityTypeId` ASC),
152  CONSTRAINT `fk_ActivityReports_TimeReports1`
153    FOREIGN KEY (`timeReportId`)
154    REFERENCES `pusp2002hbg`.`TimeReports` (`timeReportId`)
155    ON DELETE CASCADE
156    ON UPDATE NO ACTION,
157  CONSTRAINT `fk_ActivityReports_ActivitySubTypes1`
158    FOREIGN KEY (`activitySubTypeId`)
159    REFERENCES `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`)
160    ON DELETE CASCADE
161    ON UPDATE NO ACTION,
162  CONSTRAINT `fk_ActivityReports_ActivityTypes1`
163    FOREIGN KEY (`activityTypeId`)
164    REFERENCES `pusp2002hbg`.`ActivityTypes` (`activityTypeId`)
165    ON DELETE CASCADE
166    ON UPDATE NO ACTION)
167ENGINE = InnoDB;
168
169
170SET SQL_MODE=@OLD_SQL_MODE;
171SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
172SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
173
174-- -----------------------------------------------------
175-- Data for table `pusp2002hbg`.`Users`
176-- -----------------------------------------------------
177START TRANSACTION;
178USE `pusp2002hbg`;
179INSERT INTO `pusp2002hbg`.`Users` (`userId`, `username`, `password`, `isAdmin`) VALUES (1, 'Admin', 'Password', 1);
180
181COMMIT;
182
183
184-- -----------------------------------------------------
185-- Data for table `pusp2002hbg`.`Roles`
186-- -----------------------------------------------------
187START TRANSACTION;
188USE `pusp2002hbg`;
189INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (1, 'Project leader');
190INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (2, 'System manager');
191INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (3, 'Developer');
192INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (4, 'Tester');
193
194COMMIT;
195
196
197-- -----------------------------------------------------
198-- Data for table `pusp2002hbg`.`ActivityTypes`
199-- -----------------------------------------------------
200START TRANSACTION;
201USE `pusp2002hbg`;
202INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (11, 'SDP');
203INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (12, 'SRS');
204INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (13, 'SVVS');
205INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (14, 'STLDD');
206INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (15, 'SVVI');
207INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (16, 'SDDD');
208INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (17, 'SVVR');
209INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (18, 'SSD');
210INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (19, 'Final Report');
211INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (21, 'Functional test');
212INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (22, 'System test');
213INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (23, 'Regression test');
214INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (30, 'Meeting');
215INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (41, 'Lecture');
216INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (42, 'Exercise');
217INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (43, 'Computer exercise');
218INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (44, 'Home reading');
219INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (100, 'Other');
220
221COMMIT;
222
223
224-- -----------------------------------------------------
225-- Data for table `pusp2002hbg`.`ActivitySubTypes`
226-- -----------------------------------------------------
227START TRANSACTION;
228USE `pusp2002hbg`;
229INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (1, 11, 'D');
230INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (2, 11, 'I');
231INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (3, 11, 'F');
232INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (4, 11, 'R');
233INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (5, 12, 'D');
234INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (6, 12, 'I');
235INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (7, 12, 'F');
236INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (8, 12, 'R');
237INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (9, 13, 'D');
238INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (10, 13, 'I');
239INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (11, 13, 'F');
240INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (12, 13, 'R');
241INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (13, 14, 'D');
242INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (14, 14, 'I');
243INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (15, 14, 'F');
244INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (16, 14, 'R');
245INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (17, 15, 'D');
246INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (18, 15, 'I');
247INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (19, 15, 'F');
248INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (20, 15, 'R');
249INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (21, 16, 'D');
250INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (22, 16, 'I');
251INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (23, 16, 'F');
252INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (24, 16, 'R');
253INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (25, 17, 'D');
254INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (26, 17, 'I');
255INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (27, 17, 'F');
256INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (28, 17, 'R');
257INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (29, 18, 'D');
258INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (30, 18, 'I');
259INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (31, 18, 'F');
260INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (32, 18, 'R');
261INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (33, 19, 'D');
262INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (34, 19, 'I');
263INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (35, 19, 'F');
264INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (36, 19, 'R');
265
266COMMIT;