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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
<?php
namespace Schema;
use Core\Security;
const VERSION = 5;
function version_5($pdo)
{
$pdo->exec("ALTER TABLE projects ADD COLUMN is_public BOOLEAN DEFAULT '0'");
}
function version_4($pdo)
{
$pdo->exec("ALTER TABLE users ADD COLUMN notifications_enabled BOOLEAN DEFAULT '0'");
$pdo->exec("
CREATE TABLE user_has_notifications (
user_id INTEGER,
project_id INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
UNIQUE(project_id, user_id)
);
");
}
function version_3($pdo)
{
$pdo->exec("ALTER TABLE config ADD COLUMN webhooks_url_task_modification VARCHAR(255)");
$pdo->exec("ALTER TABLE config ADD COLUMN webhooks_url_task_creation VARCHAR(255)");
}
function version_2($pdo)
{
$pdo->exec("ALTER TABLE tasks ADD COLUMN creator_id INTEGER DEFAULT 0");
$pdo->exec("ALTER TABLE tasks ADD COLUMN date_modification INTEGER DEFAULT 0");
}
function version_1($pdo)
{
$pdo->exec("
CREATE TABLE config (
language CHAR(5) DEFAULT 'en_US',
webhooks_token VARCHAR(255),
timezone VARCHAR(50) DEFAULT 'UTC',
api_token VARCHAR(255)
);
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(255),
is_admin BOOLEAN DEFAULT '0',
default_project_id INTEGER DEFAULT 0,
is_ldap_user BOOLEAN DEFAULT '0',
name VARCHAR(255),
email VARCHAR(255),
google_id VARCHAR(255),
github_id VARCHAR(30)
);
CREATE TABLE remember_me (
id SERIAL PRIMARY KEY,
user_id INTEGER,
ip VARCHAR(40),
user_agent VARCHAR(255),
token VARCHAR(255),
sequence VARCHAR(255),
expiration INTEGER,
date_creation INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE last_logins (
id SERIAL PRIMARY KEY,
auth_type VARCHAR(25),
user_id INTEGER,
ip VARCHAR(40),
user_agent VARCHAR(255),
date_creation INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE,
is_active BOOLEAN DEFAULT '1',
token VARCHAR(255),
last_modified INTEGER DEFAULT 0
);
CREATE TABLE project_has_users (
id SERIAL PRIMARY KEY,
project_id INTEGER,
user_id INTEGER,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE(project_id, user_id)
);
CREATE TABLE project_has_categories (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
project_id INTEGER,
UNIQUE (project_id, name),
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE TABLE columns (
id SERIAL PRIMARY KEY,
title VARCHAR(255),
position INTEGER,
project_id INTEGER,
task_limit INTEGER DEFAULT 0,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
UNIQUE (title, project_id)
);
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
title VARCHAR(255),
description TEXT,
date_creation INTEGER,
color_id VARCHAR(255),
project_id INTEGER,
column_id INTEGER,
owner_id INTEGER DEFAULT 0,
position INTEGER,
is_active BOOLEAN DEFAULT '1',
date_completed INTEGER,
score INTEGER,
date_due INTEGER,
category_id INTEGER DEFAULT 0,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE
);
CREATE TABLE task_has_subtasks (
id SERIAL PRIMARY KEY,
title VARCHAR(255),
status SMALLINT DEFAULT 0,
time_estimated INTEGER DEFAULT 0,
time_spent INTEGER DEFAULT 0,
task_id INTEGER NOT NULL,
user_id INTEGER,
FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
);
CREATE TABLE task_has_files (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
path VARCHAR(255),
is_image BOOLEAN DEFAULT '0',
task_id INTEGER,
FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
task_id INTEGER,
user_id INTEGER,
date INTEGER,
comment TEXT,
FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE actions (
id SERIAL PRIMARY KEY,
project_id INTEGER,
event_name VARCHAR(50),
action_name VARCHAR(50),
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE TABLE action_has_params (
id SERIAL PRIMARY KEY,
action_id INTEGER,
name VARCHAR(50),
value VARCHAR(50),
FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE
);
");
$pdo->exec("
INSERT INTO users
(username, password, is_admin)
VALUES ('admin', '".\password_hash('admin', PASSWORD_BCRYPT)."', '1')
");
$pdo->exec("
INSERT INTO config
(webhooks_token, api_token)
VALUES ('".Security::generateToken()."', '".Security::generateToken()."')
");
}
|
