-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.sql
145 lines (122 loc) · 3.72 KB
/
db.sql
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
CREATE TABLE USERS
(
USER_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
USERNAME VARCHAR(256) UNIQUE NOT NULL,
EMAIL VARCHAR(256) UNIQUE NOT NULL,
PASSWORD VARCHAR NOT NULL,
PROFILE_PICTURE BYTEA
);
CREATE TABLE PROJECTS
(
PROJECT_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
OWNER_ID UUID REFERENCES USERS(USER_ID) NOT NULL,
PROJECT_NAME VARCHAR(256) NOT NULL,
DESCRIPTION TEXT,
CREATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
UPDATED_AT TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE USERS_PROJECTS
(
PROJECT_ID UUID REFERENCES PROJECTS(PROJECT_ID),
USER_ID UUID REFERENCES USERs(USER_ID),
PRIMARY KEY (PROJECT_ID, USER_ID)
);
CREATE TABLE BOARDS
(
BOARD_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
PROJECT_ID UUID REFERENCES PROJECTS(PROJECT_ID) NOT NULL,
BOARD_NAME VARCHAR(256) NOT NULL
);
CREATE TYPE PRIORITY AS ENUM
(
'LOW',
'NORMAL',
'HIGH',
'URGENT'
);
CREATE TABLE DEFAULT_TASK_TYPES
(
TASK_TYPE_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
TASK_TYPE_NAME VARCHAR(256) NOT NULL,
TASK_TYPE_DESCRIPTION TEXT
);
INSERT INTO DEFAULT_TASK_TYPES(TASK_TYPE_NAME, TASK_TYPE_DESCRIPTION)
VALUES ('Task (default)', ''),
('Bug', ' Identify and track software bugs for resolution '),
('Milestone', ''),
('Request', ' Handle incoming requests or tasks from clients or team members ');
CREATE TABLE TASK_TYPES
(
TASK_TYPE_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
PROJECT_ID UUID REFERENCES PROJECTS(PROJECT_ID) NOT NULL,
TASK_TYPE_NAME VARCHAR(256) NOT NULL,
TASK_TYPE_DESCRIPTION TEXT
);
CREATE TABLE TASKS
(
TASK_ID UUID DEFAULT uuid_generate_v4() UNIQUE,
BOARD_ID UUID REFERENCES BOARDS(BOARD_ID),
TASK_NAME VARCHAR(512) NOT NULL,
DESCRIPTION TEXT,
CREATED_AT TIMESTAMP DEFAULT NOW() NOT NULL,
UPDATED_AT TIMESTAMP DEFAULT NOW() NOT NULL,
-- FEATURES
PRIORITY PRIORITY DEFAULT 'NORMAL',
DUE_DATE DATE,
TASK_TYPE_ID UUID REFERENCES TASK_TYPES(TASK_TYPE_ID) NOT NULL,
PRIMARY KEY (TASK_ID, BOARD_ID)
);
CREATE TABLE RELATED_TASKS
(
TASK_ID UUID REFERENCES TASKS(TASK_ID) UNIQUE,
LINKED_TASK_ID UUID REFERENCES TASKS(TASK_ID) UNIQUE,
PRIMARY KEY (TASK_ID, LINKED_TASK_ID)
);
CREATE TABLE TASKS_USERS
(
TASK_ID UUID REFERENCES TASKS(TASK_ID) NOT NULL,
USER_ID UUID REFERENCES USERS(USER_ID) NOT NULL,
PRIMARY KEY (TASK_ID, USER_ID)
);
COMMENT ON TABLE TASKS_USERS IS 'THIS IS THE RELATIONSHIP BETWEEN A TASK AND ITS MEMBERS';
CREATE TABLE COMMENTS
(
COMMENT_ID UUID DEFAULT uuid_generate_v4(),
TASK_ID UUID REFERENCES TASKS(TASK_ID) NOT NULL,
USER_ID UUID REFERENCES USERS(USER_ID) NOT NULL,
COMMENT TEXT NOT NULL,
CREATED_AT TIMESTAMP DEFAULT NOW() NOT NULL,
PRIMARY KEY (TASK_ID, USER_ID, COMMENT_ID)
);
CREATE TABLE TAGS
(
TAG_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
PROJECT_ID UUID REFERENCES PROJECTS(PROJECT_ID) NOT NULL,
TAG_NAME VARCHAR(256) NOT NULL,
COLOR VARCHAR(6) DEFAULT '007CBA' NOT NULL
);
CREATE TABLE TAGS_TASKS
(
TAG_ID UUID REFERENCES TAGS(TAG_ID),
TASK_ID UUID REFERENCES TASKS(TASK_ID)
);
CREATE TABLE ATTACHMENTS
(
ATTACHMENT_ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
TASK_ID UUID REFERENCES TASKS(TASK_ID) NOT NULL,
PROJECT_ID UUID REFERENCES PROJECTS(PROJECT_ID) NOT NULL,
ATTACHMENT BYTEA NOT NULL,
ATTACHMENT_NAME VARCHAR(256) NOT NULL,
CREATED_AT TIMESTAMP DEFAULT NOW() NOT NULL
);
CREATE
OR REPLACE FUNCTION update_changetimestamp_column () RETURNS TRIGGER AS $$
BEGIN
NEW."UPDATED_AT" = NOW();
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE
OR REPLACE TRIGGER UPDATE_PROJECT BEFORE
UPDATE ON PROJECTS FOR EACH ROW
EXECUTE FUNCTION UPDATE_CHANGETIMESTAMP_COLUMN ();