fi1302HG

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