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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
<?php
namespace Schema;
const VERSION = 18;
function version_18($pdo)
{
$pdo->exec("
CREATE TABLE task_has_subtasks (
id INTEGER PRIMARY KEY,
title TEXT COLLATE NOCASE,
status INTEGER DEFAULT 0,
time_estimated INTEGER DEFAULT 0,
time_spent INTEGER DEFAULT 0,
task_id INTEGER,
user_id INTEGER,
FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
)"
);
}
function version_17($pdo)
{
$pdo->exec("
CREATE TABLE task_has_files (
id INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE,
path TEXT,
is_image INTEGER DEFAULT 0,
task_id INTEGER,
FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
)"
);
}
function version_16($pdo)
{
$pdo->exec("
CREATE TABLE project_has_categories (
id INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE,
project_id INT,
UNIQUE (project_id, name),
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
)"
);
$pdo->exec("ALTER TABLE tasks ADD COLUMN category_id INTEGER DEFAULT 0");
}
function version_15($pdo)
{
$pdo->exec("ALTER TABLE projects ADD COLUMN last_modified INTEGER DEFAULT 0");
}
function version_14($pdo)
{
$pdo->exec("ALTER TABLE users ADD COLUMN name TEXT");
$pdo->exec("ALTER TABLE users ADD COLUMN email TEXT");
$pdo->exec("ALTER TABLE users ADD COLUMN google_id TEXT");
}
function version_13($pdo)
{
$pdo->exec("ALTER TABLE users ADD COLUMN is_ldap_user INTEGER DEFAULT 0");
}
function version_12($pdo)
{
$pdo->exec(
'CREATE TABLE remember_me (
id INTEGER PRIMARY KEY,
user_id INTEGER,
ip TEXT,
user_agent TEXT,
token TEXT,
sequence TEXT,
expiration INTEGER,
date_creation INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)'
);
$pdo->exec(
'CREATE TABLE last_logins (
id INTEGER PRIMARY KEY,
auth_type TEXT,
user_id INTEGER,
ip TEXT,
user_agent TEXT,
date_creation INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)'
);
$pdo->exec('CREATE INDEX last_logins_user_idx ON last_logins(user_id)');
}
function version_11($pdo)
{
$pdo->exec(
'ALTER TABLE comments RENAME TO comments_bak'
);
$pdo->exec(
'CREATE TABLE comments (
id INTEGER 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
)'
);
$pdo->exec(
'INSERT INTO comments SELECT * FROM comments_bak'
);
$pdo->exec(
'DROP TABLE comments_bak'
);
}
function version_10($pdo)
{
$pdo->exec(
'CREATE TABLE actions (
id INTEGER PRIMARY KEY,
project_id INTEGER,
event_name TEXT,
action_name TEXT,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
)'
);
$pdo->exec(
'CREATE TABLE action_has_params (
id INTEGER PRIMARY KEY,
action_id INTEGER,
name TEXT,
value TEXT,
FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE
)'
);
}
function version_9($pdo)
{
$pdo->exec("ALTER TABLE tasks ADD COLUMN date_due INTEGER");
}
function version_8($pdo)
{
$pdo->exec(
'CREATE TABLE comments (
id INTEGER 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 tasks(id) ON DELETE CASCADE
)'
);
}
function version_7($pdo)
{
$pdo->exec("
CREATE TABLE project_has_users (
id INTEGER 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)
)
");
}
function version_6($pdo)
{
$pdo->exec("ALTER TABLE columns ADD COLUMN task_limit INTEGER DEFAULT '0'");
}
function version_5($pdo)
{
$pdo->exec("ALTER TABLE tasks ADD COLUMN score INTEGER");
}
function version_4($pdo)
{
$pdo->exec("ALTER TABLE config ADD COLUMN timezone TEXT DEFAULT 'UTC'");
}
function version_3($pdo)
{
$pdo->exec('ALTER TABLE projects ADD COLUMN token TEXT');
// For each existing project, assign a different token
$rq = $pdo->prepare("SELECT id FROM projects WHERE token IS NULL");
$rq->execute();
$results = $rq->fetchAll(\PDO::FETCH_ASSOC);
if ($results !== false) {
foreach ($results as &$result) {
$rq = $pdo->prepare('UPDATE projects SET token=? WHERE id=?');
$rq->execute(array(\Core\Security::generateToken(), $result['id']));
}
}
}
function version_2($pdo)
{
$pdo->exec('ALTER TABLE tasks ADD COLUMN date_completed INTEGER');
$pdo->exec('UPDATE tasks SET date_completed=date_creation WHERE is_active=0');
}
function version_1($pdo)
{
$pdo->exec("
CREATE TABLE config (
language TEXT,
webhooks_token TEXT
)
");
$pdo->exec("
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT,
password TEXT,
is_admin INTEGER DEFAULT 0,
default_project_id INTEGER DEFAULT 0
)
");
$pdo->exec("
CREATE TABLE projects (
id INTEGER PRIMARY KEY,
name TEXT NOCASE UNIQUE,
is_active INTEGER DEFAULT 1
)
");
$pdo->exec("
CREATE TABLE columns (
id INTEGER PRIMARY KEY,
title TEXT,
position INTEGER,
project_id INTEGER,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
UNIQUE (title, project_id)
)
");
$pdo->exec("
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
title TEXT,
description TEXT,
date_creation INTEGER,
color_id TEXT,
project_id INTEGER,
column_id INTEGER,
owner_id INTEGER DEFAULT '0',
position INTEGER,
is_active INTEGER DEFAULT 1,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
FOREIGN KEY(column_id) REFERENCES columns(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
(language, webhooks_token)
VALUES ('en_US', '".\Core\Security::generateToken()."')
");
}
|
