oProject/oProject_mysql_create.sql at master · seddik/oProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
CREATE TABLE `User` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`username` varchar(200) UNIQUE,
	`firstname` varchar(200),
	`lastname` varchar(200),
	`hour_rate` FLOAT DEFAULT '0',
	`email` varchar(200) NOT NULL UNIQUE,
	PRIMARY KEY (`id`)
);

CREATE TABLE `User_role` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`user_id` INT NOT NULL AUTO_INCREMENT,
	`role_id` INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Role` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` varchar(200) NOT NULL UNIQUE,
	`fullname` varchar(500) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Project` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`Title` varchar(200) NOT NULL,
	`start_date` DATE,
	`end_date` DATE,
	`budget` DECIMAL(15,3),
	`is_closed` INT(1) NOT NULL DEFAULT '0',
	`flow_id` INT,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Project_User` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`rate` DECIMAL NOT NULL DEFAULT '0',
	`user_id` INT NOT NULL,
	`project_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Task` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` varchar(200) NOT NULL,
	`weight` INT DEFAULT '1',
	`estimated_minutes` INT,
	`start_date` DATETIME,
	`end_date` DATETIME,
	`progress` INT NOT NULL DEFAULT '0',
	`user_id` INT,
	`project_id` INT NOT NULL AUTO_INCREMENT,
	`phase_id` INT,
	`milestone_id` INT,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Statu` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` varchar(200) NOT NULL UNIQUE,
	`is_closed` INT(1) NOT NULL DEFAULT '0',
	`is_progress` INT(1) NOT NULL DEFAULT '0',
	`task_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Work` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`rate` DECIMAL(15,3) NOT NULL,
	`minutes` INT NOT NULL,
	`user_id` INT NOT NULL,
	`task_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Flow` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` varchar(200) NOT NULL UNIQUE,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Phase` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` varchar(200) NOT NULL,
	`flow_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Milestone` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` varchar(200) NOT NULL,
	`start_date` DATETIME NOT NULL,
	`project_id` INT NOT NULL,
	`end_date` DATETIME NOT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `User_role` ADD CONSTRAINT `User_role_fk0` FOREIGN KEY (`user_id`) REFERENCES `User`(`id`);

ALTER TABLE `User_role` ADD CONSTRAINT `User_role_fk1` FOREIGN KEY (`role_id`) REFERENCES `Role`(`id`);

ALTER TABLE `Project` ADD CONSTRAINT `Project_fk0` FOREIGN KEY (`flow_id`) REFERENCES `Flow`(`id`);

ALTER TABLE `Project_User` ADD CONSTRAINT `Project_User_fk0` FOREIGN KEY (`user_id`) REFERENCES `User`(`id`);

ALTER TABLE `Project_User` ADD CONSTRAINT `Project_User_fk1` FOREIGN KEY (`project_id`) REFERENCES `Project`(`id`);

ALTER TABLE `Task` ADD CONSTRAINT `Task_fk0` FOREIGN KEY (`user_id`) REFERENCES `User`(`id`);

ALTER TABLE `Task` ADD CONSTRAINT `Task_fk1` FOREIGN KEY (`project_id`) REFERENCES `Project`(`id`);

ALTER TABLE `Task` ADD CONSTRAINT `Task_fk2` FOREIGN KEY (`phase_id`) REFERENCES `Phase`(`id`);

ALTER TABLE `Task` ADD CONSTRAINT `Task_fk3` FOREIGN KEY (`milestone_id`) REFERENCES `Milestone`(`id`);

ALTER TABLE `Statu` ADD CONSTRAINT `Statu_fk0` FOREIGN KEY (`task_id`) REFERENCES `Task`(`id`);

ALTER TABLE `Work` ADD CONSTRAINT `Work_fk0` FOREIGN KEY (`user_id`) REFERENCES `User`(`id`);

ALTER TABLE `Work` ADD CONSTRAINT `Work_fk1` FOREIGN KEY (`task_id`) REFERENCES `Task`(`id`);

ALTER TABLE `Phase` ADD CONSTRAINT `Phase_fk0` FOREIGN KEY (`flow_id`) REFERENCES `Flow`(`id`);

ALTER TABLE `Milestone` ADD CONSTRAINT `Milestone_fk0` FOREIGN KEY (`project_id`) REFERENCES `Project`(`id`);