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
|
<?php
namespace Schema;
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(\Model\Base::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 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', '".\Model\Base::generateToken()."')
");
}
|
