· 8 years ago · Jun 06, 2018, 04:10 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema attendance_new
9-- -----------------------------------------------------
10DROP SCHEMA IF EXISTS `attendance_new` ;
11
12-- -----------------------------------------------------
13-- Schema attendance_new
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `attendance_new` DEFAULT CHARACTER SET utf8 ;
16USE `attendance_new` ;
17
18-- -----------------------------------------------------
19-- Table `attendance_new`.`notification`
20-- -----------------------------------------------------
21DROP TABLE IF EXISTS `attendance_new`.`notification` ;
22
23CREATE TABLE IF NOT EXISTS `attendance_new`.`notification` (
24 `id` INT(11) NOT NULL AUTO_INCREMENT,
25 `title` VARCHAR(128) NOT NULL,
26 `description` VARCHAR(256) NOT NULL,
27 `link` VARCHAR(256) NULL DEFAULT NULL,
28 `shown` TINYINT(1) NOT NULL DEFAULT '0',
29 PRIMARY KEY (`id`),
30 UNIQUE INDEX `notification_id_uindex` (`id` ASC))
31ENGINE = InnoDB
32DEFAULT CHARACTER SET = utf8;
33
34
35-- -----------------------------------------------------
36-- Table `attendance_new`.`user`
37-- -----------------------------------------------------
38DROP TABLE IF EXISTS `attendance_new`.`user` ;
39
40CREATE TABLE IF NOT EXISTS `attendance_new`.`user` (
41 `id` INT(11) NOT NULL AUTO_INCREMENT,
42 `orion_login` VARCHAR(128) NOT NULL,
43 `name` VARCHAR(128) NULL DEFAULT NULL,
44 `last_name` VARCHAR(128) NULL DEFAULT NULL,
45 `honorific_prefix` VARCHAR(45) NULL DEFAULT NULL,
46 `honorific_suffix` VARCHAR(45) NULL DEFAULT NULL,
47 `authority` ENUM('USER', 'SECRETARY') NULL DEFAULT NULL,
48 `is_active` TINYINT NULL DEFAULT 1,
49 `main_work_station` ENUM('KIV', 'NTIS') NULL DEFAULT NULL,
50 PRIMARY KEY (`id`))
51ENGINE = InnoDB
52DEFAULT CHARACTER SET = utf8;
53
54
55-- -----------------------------------------------------
56-- Table `attendance_new`.`user_holiday`
57-- -----------------------------------------------------
58DROP TABLE IF EXISTS `attendance_new`.`user_holiday` ;
59
60CREATE TABLE IF NOT EXISTS `attendance_new`.`user_holiday` (
61 `id` INT(11) NOT NULL AUTO_INCREMENT,
62 `user_id` INT(11) NOT NULL,
63 `day` DATE NOT NULL,
64 `type` VARCHAR(45) NOT NULL,
65 PRIMARY KEY (`id`, `user_id`),
66 INDEX `fk_user_vacation_user1_idx` (`user_id` ASC),
67 CONSTRAINT `fk_user_vacation_user1`
68 FOREIGN KEY (`user_id`)
69 REFERENCES `attendance_new`.`user` (`id`)
70 ON DELETE CASCADE
71 ON UPDATE NO ACTION)
72ENGINE = InnoDB
73AUTO_INCREMENT = 2
74DEFAULT CHARACTER SET = utf8;
75
76
77-- -----------------------------------------------------
78-- Table `attendance_new`.`day_time_sheet`
79-- -----------------------------------------------------
80DROP TABLE IF EXISTS `attendance_new`.`day_time_sheet` ;
81
82CREATE TABLE IF NOT EXISTS `attendance_new`.`day_time_sheet` (
83 `id` INT NOT NULL AUTO_INCREMENT,
84 `user_id` INT(11) NOT NULL,
85 `date` DATE NOT NULL,
86 `first_part_from` TIME NULL,
87 `first_part_to` TIME NULL,
88 `second_part_from` TIME NULL,
89 `second_part_to` TIME NULL,
90 `day_type` VARCHAR(128) NULL DEFAULT NULL,
91 PRIMARY KEY (`id`, `user_id`, `date`),
92 INDEX `fk_day_time_sheet_user1_idx` (`user_id` ASC),
93 CONSTRAINT `fk_day_time_sheet_user1`
94 FOREIGN KEY (`user_id`)
95 REFERENCES `attendance_new`.`user` (`id`)
96 ON DELETE CASCADE
97 ON UPDATE NO ACTION)
98ENGINE = InnoDB;
99
100
101-- -----------------------------------------------------
102-- Table `attendance_new`.`project`
103-- -----------------------------------------------------
104DROP TABLE IF EXISTS `attendance_new`.`project` ;
105
106CREATE TABLE IF NOT EXISTS `attendance_new`.`project` (
107 `id` INT(11) NOT NULL AUTO_INCREMENT,
108 `project_name` VARCHAR(200) NOT NULL,
109 `project_name_short` VARCHAR(15) NOT NULL,
110 `description` TEXT NULL DEFAULT '',
111 PRIMARY KEY (`id`),
112 UNIQUE INDEX `project_id_uindex` (`id` ASC))
113ENGINE = InnoDB
114DEFAULT CHARACTER SET = utf8;
115
116
117-- -----------------------------------------------------
118-- Table `attendance_new`.`user_contract`
119-- -----------------------------------------------------
120DROP TABLE IF EXISTS `attendance_new`.`user_contract` ;
121
122CREATE TABLE IF NOT EXISTS `attendance_new`.`user_contract` (
123 `id` INT(11) NOT NULL AUTO_INCREMENT,
124 `user_id` INT(11) NOT NULL,
125 `work_station` ENUM('KIV', 'NTIS') NOT NULL,
126 `obligation` DOUBLE NOT NULL DEFAULT '0',
127 `active_from` DATE NOT NULL DEFAULT current_date,
128 `active_to` DATE NULL DEFAULT NULL,
129 PRIMARY KEY (`id`, `user_id`),
130 UNIQUE INDEX `user_project_id_uindex` (`id` ASC),
131 INDEX `fk_user_contract_user1_idx` (`user_id` ASC),
132 CONSTRAINT `fk_user_contract_user1`
133 FOREIGN KEY (`user_id`)
134 REFERENCES `attendance_new`.`user` (`id`)
135 ON DELETE CASCADE
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB
138DEFAULT CHARACTER SET = utf8;
139
140
141-- -----------------------------------------------------
142-- Table `attendance_new`.`user_sharing_timesheet`
143-- -----------------------------------------------------
144DROP TABLE IF EXISTS `attendance_new`.`user_sharing_timesheet` ;
145
146CREATE TABLE IF NOT EXISTS `attendance_new`.`user_sharing_timesheet` (
147 `sharing_user_id` INT(11) NOT NULL,
148 `share_to_id` INT(11) NOT NULL,
149 PRIMARY KEY (`sharing_user_id`, `share_to_id`),
150 INDEX `fk_user_has_user_user4_idx` (`share_to_id` ASC),
151 INDEX `fk_user_has_user_user3_idx` (`sharing_user_id` ASC),
152 CONSTRAINT `fk_user_has_user_user3`
153 FOREIGN KEY (`sharing_user_id`)
154 REFERENCES `attendance_new`.`user` (`id`)
155 ON DELETE CASCADE
156 ON UPDATE NO ACTION,
157 CONSTRAINT `fk_user_has_user_user4`
158 FOREIGN KEY (`share_to_id`)
159 REFERENCES `attendance_new`.`user` (`id`)
160 ON DELETE CASCADE
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB
163DEFAULT CHARACTER SET = utf8;
164
165
166-- -----------------------------------------------------
167-- Table `attendance_new`.`user_assigned_to_project`
168-- -----------------------------------------------------
169DROP TABLE IF EXISTS `attendance_new`.`user_assigned_to_project` ;
170
171CREATE TABLE IF NOT EXISTS `attendance_new`.`user_assigned_to_project` (
172 `project_id` INT(11) NOT NULL,
173 `user_id` INT(11) NOT NULL,
174 `user_contract_id` INT(11) NOT NULL,
175 PRIMARY KEY (`project_id`, `user_id`, `user_contract_id`),
176 INDEX `fk_user_assigned_to_project_user_contract1_idx` (`user_contract_id` ASC, `user_id` ASC),
177 CONSTRAINT `fk_user_assigned_to_project_project1`
178 FOREIGN KEY (`project_id`)
179 REFERENCES `attendance_new`.`project` (`id`)
180 ON DELETE CASCADE
181 ON UPDATE NO ACTION,
182 CONSTRAINT `fk_user_assigned_to_project_user_contract1`
183 FOREIGN KEY (`user_contract_id` , `user_id`)
184 REFERENCES `attendance_new`.`user_contract` (`id` , `user_id`)
185 ON DELETE CASCADE
186 ON UPDATE NO ACTION)
187ENGINE = InnoDB;
188
189
190-- -----------------------------------------------------
191-- Table `attendance_new`.`attendance`
192-- -----------------------------------------------------
193DROP TABLE IF EXISTS `attendance_new`.`attendance` ;
194
195CREATE TABLE IF NOT EXISTS `attendance_new`.`attendance` (
196 `id` INT NOT NULL AUTO_INCREMENT,
197 `user_id` INT(11) NOT NULL,
198 `active_from` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
199 `first_part_from` TIME NOT NULL,
200 `first_part_to` TIME NOT NULL,
201 `second_part_from` TIME NOT NULL,
202 `second_part_to` TIME NOT NULL,
203 PRIMARY KEY (`id`, `user_id`, `active_from`),
204 INDEX `fk_day_time_sheet_user1_idx` (`user_id` ASC),
205 CONSTRAINT `fk_day_time_sheet_user10`
206 FOREIGN KEY (`user_id`)
207 REFERENCES `attendance_new`.`user` (`id`)
208 ON DELETE CASCADE
209 ON UPDATE NO ACTION)
210ENGINE = InnoDB;
211
212
213SET SQL_MODE=@OLD_SQL_MODE;
214SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
215SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;