|
|
<?php |
|
|
|
|
|
namespace Schema; |
|
|
|
|
|
require_once __DIR__.'/Migration.php'; |
|
|
|
|
|
use PDO; |
|
|
use Kanboard\Core\Security\Token; |
|
|
use Kanboard\Core\Security\Role; |
|
|
|
|
|
const VERSION = 2; |
|
|
|
|
|
function version_2(PDO $pdo) |
|
|
{ |
|
|
$pdo->exec("ALTER TABLE dbo.users ADD theme nvarchar(50) DEFAULT N'light' NOT NULL"); |
|
|
} |
|
|
|
|
|
function version_1(PDO $pdo) |
|
|
{ |
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.users ( |
|
|
id int identity PRIMARY KEY |
|
|
, username nvarchar(255) NOT NULL |
|
|
, password nvarchar(255) |
|
|
, is_ldap_user bit DEFAULT 0 |
|
|
, name nvarchar(255) |
|
|
, email nvarchar(255) |
|
|
, google_id nvarchar(255) |
|
|
, github_id nvarchar(30) |
|
|
, notifications_enabled bit DEFAULT 0 |
|
|
, timezone nvarchar(50) DEFAULT N'' |
|
|
, language nvarchar(11) DEFAULT N'' |
|
|
, disable_login_form bit DEFAULT 0 |
|
|
, twofactor_activated bit DEFAULT 0 |
|
|
, twofactor_secret char(16) |
|
|
, token nvarchar(255) DEFAULT N'' |
|
|
, notifications_filter int DEFAULT 4 |
|
|
, nb_failed_login int DEFAULT 0 |
|
|
, lock_expiration_date bigint DEFAULT 0 |
|
|
, gitlab_id int |
|
|
, role nvarchar(25) NOT NULL |
|
|
, is_active bit DEFAULT 1 |
|
|
, avatar_path nvarchar(255) |
|
|
, api_access_token nvarchar(255) |
|
|
, filter nvarchar(max) DEFAULT N'' |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.projects ( |
|
|
id int identity PRIMARY KEY |
|
|
, name nvarchar(max) NOT NULL |
|
|
, is_active bit DEFAULT 1 |
|
|
, token nvarchar(255) |
|
|
, last_modified bigint DEFAULT 0 |
|
|
, is_public bit DEFAULT 0 |
|
|
, is_private bit DEFAULT 0 |
|
|
, description nvarchar(max) |
|
|
, identifier nvarchar(50) DEFAULT N'' |
|
|
, start_date nvarchar(10) DEFAULT '' |
|
|
, end_date nvarchar(10) DEFAULT '' |
|
|
, owner_id int DEFAULT 0 |
|
|
, priority_default int DEFAULT 0 |
|
|
, priority_start int DEFAULT 0 |
|
|
, priority_end int DEFAULT 3 |
|
|
, email nvarchar(max) |
|
|
, predefined_email_subjects nvarchar(max) |
|
|
, per_swimlane_task_limits bit DEFAULT 0 NOT NULL |
|
|
, task_limit int DEFAULT 0 |
|
|
, enable_global_tags bit DEFAULT 1 NOT NULL |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.columns ( |
|
|
id int identity PRIMARY KEY |
|
|
, title nvarchar(255) NOT NULL |
|
|
, position int |
|
|
, project_id int NOT NULL |
|
|
, task_limit int DEFAULT 0 |
|
|
, description nvarchar(max) |
|
|
, hide_in_dashboard bit DEFAULT 0 NOT NULL |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, UNIQUE (title, project_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_users ( |
|
|
project_id int NOT NULL |
|
|
, user_id int NOT NULL |
|
|
, role nvarchar(255) NOT NULL |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, UNIQUE(project_id, user_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.actions ( |
|
|
id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, event_name nvarchar(max) NOT NULL |
|
|
, action_name nvarchar(max) NOT NULL |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.action_has_params ( |
|
|
id int identity PRIMARY KEY |
|
|
, action_id int NOT NULL |
|
|
, name nvarchar(max) NOT NULL |
|
|
, value nvarchar(max) NOT NULL |
|
|
, FOREIGN KEY(action_id) REFERENCES dbo.actions(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.remember_me ( |
|
|
id int identity PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, ip nvarchar(45) |
|
|
, user_agent nvarchar(255) |
|
|
, token nvarchar(255) |
|
|
, sequence nvarchar(255) |
|
|
, expiration int |
|
|
, date_creation bigint |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.last_logins ( |
|
|
id int identity PRIMARY KEY |
|
|
, auth_type nvarchar(25) |
|
|
, user_id int NOT NULL |
|
|
, ip nvarchar(45) |
|
|
, user_agent nvarchar(255) |
|
|
, date_creation bigint |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_categories ( |
|
|
id int identity PRIMARY KEY |
|
|
, name nvarchar(255) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, description nvarchar(max) |
|
|
, color_id nvarchar(50) |
|
|
, UNIQUE (project_id, name) |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.swimlanes ( |
|
|
id int identity PRIMARY KEY |
|
|
, name nvarchar(848) NOT NULL /* max size for unique index */ |
|
|
, position int DEFAULT 1 |
|
|
, is_active bit DEFAULT 1 |
|
|
, project_id int NOT NULL |
|
|
, description nvarchar(max) |
|
|
, task_limit int DEFAULT 0 |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, UNIQUE (name, project_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.tasks |
|
|
( |
|
|
id int identity PRIMARY KEY |
|
|
, title nvarchar(max) NOT NULL |
|
|
, description nvarchar(max) |
|
|
, date_creation bigint |
|
|
, color_id nvarchar(255) |
|
|
, project_id int NOT NULL |
|
|
, column_id int NOT NULL |
|
|
, owner_id int DEFAULT 0 |
|
|
, position int |
|
|
, is_active bit DEFAULT 1 |
|
|
, date_completed bigint |
|
|
, score int |
|
|
, date_due bigint |
|
|
, category_id int DEFAULT 0 |
|
|
, creator_id int DEFAULT 0 |
|
|
, date_modification int DEFAULT 0 |
|
|
, reference nvarchar(max) DEFAULT '' |
|
|
, date_started bigint |
|
|
, time_spent float DEFAULT 0 |
|
|
, time_estimated float DEFAULT 0 |
|
|
, swimlane_id int NOT NULL |
|
|
, date_moved bigint DEFAULT 0 |
|
|
, recurrence_status int DEFAULT 0 NOT NULL |
|
|
, recurrence_trigger int DEFAULT 0 NOT NULL |
|
|
, recurrence_factor int DEFAULT 0 NOT NULL |
|
|
, recurrence_timeframe int DEFAULT 0 NOT NULL |
|
|
, recurrence_basedate int DEFAULT 0 NOT NULL |
|
|
, recurrence_parent int |
|
|
, recurrence_child int |
|
|
, priority int DEFAULT 0 |
|
|
, external_provider nvarchar(255) |
|
|
, external_uri nvarchar(255) |
|
|
, FOREIGN KEY (project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY (column_id) REFERENCES dbo.columns(id) ON DELETE NO ACTION /* columns_cascade_delete_trigger */ |
|
|
, FOREIGN KEY (swimlane_id) REFERENCES dbo.swimlanes(id) ON DELETE NO ACTION /* swimlanes_cascade_delete_trigger */ |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.task_has_files ( |
|
|
id int identity PRIMARY KEY |
|
|
, name nvarchar(max) NOT NULL |
|
|
, path nvarchar(max) |
|
|
, is_image bit DEFAULT 0 |
|
|
, task_id int NOT NULL |
|
|
, date bigint NOT NULL DEFAULT 0 |
|
|
, user_id int NOT NULL DEFAULT 0 |
|
|
, size int NOT NULL DEFAULT 0 |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.subtasks ( |
|
|
id int identity PRIMARY KEY |
|
|
, title nvarchar(max) NOT NULL |
|
|
, status smallint DEFAULT 0 |
|
|
, time_estimated float DEFAULT 0 |
|
|
, time_spent float DEFAULT 0 |
|
|
, task_id int NOT NULL |
|
|
, user_id int |
|
|
, position int DEFAULT 1 |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.user_has_notifications ( |
|
|
user_id int NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
, UNIQUE(project_id, user_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.settings ( |
|
|
[option] nvarchar(100) PRIMARY KEY |
|
|
, value nvarchar(max) DEFAULT '' |
|
|
, changed_by int DEFAULT 0 NOT NULL |
|
|
, changed_on int DEFAULT 0 NOT NULL |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_daily_column_stats ( |
|
|
id int identity PRIMARY KEY |
|
|
, day nchar(10) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, column_id int NOT NULL |
|
|
, total int NOT NULL DEFAULT 0 |
|
|
, score int NOT NULL DEFAULT 0 |
|
|
, FOREIGN KEY(column_id) REFERENCES dbo.columns(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.subtask_time_tracking ( |
|
|
id int identity PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, subtask_id int NOT NULL |
|
|
, [start] bigint DEFAULT 0 |
|
|
, [end] bigint DEFAULT 0 |
|
|
, time_spent real DEFAULT 0 |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(subtask_id) REFERENCES dbo.subtasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.links ( |
|
|
id int identity PRIMARY KEY |
|
|
, label nvarchar(255) NOT NULL |
|
|
, opposite_id int DEFAULT 0 |
|
|
, UNIQUE(label) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.task_has_links ( |
|
|
id int identity PRIMARY KEY |
|
|
, link_id int NOT NULL |
|
|
, task_id int NOT NULL |
|
|
, opposite_task_id int NOT NULL |
|
|
, FOREIGN KEY(link_id) REFERENCES dbo.links(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(opposite_task_id) REFERENCES dbo.tasks(id) ON DELETE NO ACTION /* Handled in tasks_cascade_delete_trigger */ |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.transitions ( |
|
|
id int identity PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, task_id int NOT NULL |
|
|
, src_column_id int NOT NULL |
|
|
, dst_column_id int NOT NULL |
|
|
, date bigint NOT NULL |
|
|
, time_spent int DEFAULT 0 |
|
|
, FOREIGN KEY(src_column_id) REFERENCES dbo.columns(id) ON DELETE NO ACTION /* columns_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(dst_column_id) REFERENCES dbo.columns(id) ON DELETE NO ACTION /* columns_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.currencies ( |
|
|
currency nvarchar(3) NOT NULL UNIQUE |
|
|
, rate REAL DEFAULT 0 |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.comments ( |
|
|
id int identity PRIMARY KEY |
|
|
, task_id int NOT NULL |
|
|
, user_id int DEFAULT 0 |
|
|
, date_creation bigint NOT NULL |
|
|
, comment nvarchar(max) NOT NULL |
|
|
, reference nvarchar(max) DEFAULT N'' |
|
|
, date_modification bigint |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_daily_stats ( |
|
|
id int identity PRIMARY KEY |
|
|
, day nchar(10) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, avg_lead_time int NOT NULL DEFAULT 0 |
|
|
, avg_cycle_time int NOT NULL DEFAULT 0 |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.plugin_schema_versions ( |
|
|
plugin nvarchar(80) NOT NULL PRIMARY KEY |
|
|
, version int NOT NULL DEFAULT 0 |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.custom_filters ( |
|
|
id int identity PRIMARY KEY |
|
|
, filter nvarchar(max) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, user_id int NOT NULL |
|
|
, name nvarchar(max) NOT NULL |
|
|
, is_shared bit DEFAULT 0 |
|
|
, append bit DEFAULT 0 |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.user_has_unread_notifications ( |
|
|
id int identity PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, date_creation bigint NOT NULL |
|
|
, event_name nvarchar(max) NOT NULL |
|
|
, event_data nvarchar(max) NOT NULL |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.user_has_notification_types ( |
|
|
id int identity PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, notification_type nvarchar(50) |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_notification_types ( |
|
|
id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, notification_type nvarchar(50) NOT NULL |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
, UNIQUE(project_id, notification_type) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.user_has_metadata ( |
|
|
user_id int NOT NULL |
|
|
, name nvarchar(50) NOT NULL |
|
|
, value nvarchar(255) DEFAULT '' |
|
|
, changed_by int DEFAULT 0 NOT NULL |
|
|
, changed_on int DEFAULT 0 NOT NULL /* TODO: should be bigint?? */ |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, UNIQUE(user_id, name) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_metadata ( |
|
|
project_id int NOT NULL |
|
|
, name nvarchar(50) NOT NULL |
|
|
, value nvarchar(255) DEFAULT '' |
|
|
, changed_by int DEFAULT 0 NOT NULL |
|
|
, changed_on int DEFAULT 0 NOT NULL /* TODO: should be bigint?? */ |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
, UNIQUE(project_id, name) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.task_has_metadata ( |
|
|
task_id int NOT NULL |
|
|
, name nvarchar(50) NOT NULL |
|
|
, value nvarchar(255) DEFAULT '' |
|
|
, changed_by int DEFAULT 0 NOT NULL |
|
|
, changed_on int DEFAULT 0 NOT NULL /* TODO: should be bigint?? */ |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
, UNIQUE(task_id, name) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.groups ( |
|
|
id int identity PRIMARY KEY |
|
|
, external_id nvarchar(255) DEFAULT '' |
|
|
, name nvarchar(850) NOT NULL UNIQUE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.group_has_users ( |
|
|
group_id int NOT NULL |
|
|
, user_id int NOT NULL |
|
|
, FOREIGN KEY(group_id) REFERENCES dbo.groups(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, UNIQUE(group_id, user_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_groups ( |
|
|
group_id int NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, role nvarchar(255) NOT NULL |
|
|
, FOREIGN KEY(group_id) REFERENCES dbo.groups(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
, UNIQUE(group_id, project_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.password_reset ( |
|
|
token nvarchar(80) PRIMARY KEY |
|
|
, user_id int NOT NULL |
|
|
, date_expiration int NOT NULL /* TODO: bigint?? */ |
|
|
, date_creation int NOT NULL /* TODO: bigint?? */ |
|
|
, ip nvarchar(45) NOT NULL |
|
|
, user_agent nvarchar(255) NOT NULL |
|
|
, is_active bit NOT NULL |
|
|
, FOREIGN KEY(user_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.task_has_external_links ( |
|
|
id int identity PRIMARY KEY |
|
|
, link_type nvarchar(100) NOT NULL |
|
|
, dependency nvarchar(100) NOT NULL |
|
|
, title nvarchar(max) NOT NULL |
|
|
, url nvarchar(max) NOT NULL |
|
|
, date_creation int NOT NULL /* TODO: bigint?? */ |
|
|
, date_modification int NOT NULL /* TODO: bigint?? */ |
|
|
, task_id int NOT NULL |
|
|
, creator_id int DEFAULT 0 |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_files ( |
|
|
id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, name nvarchar(max) NOT NULL |
|
|
, path nvarchar(max) NOT NULL |
|
|
, is_image bit DEFAULT 0 |
|
|
, size int DEFAULT 0 NOT NULL |
|
|
, user_id int DEFAULT 0 NOT NULL |
|
|
, date int DEFAULT 0 NOT NULL /* TODO: bigint?? */ |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.tags ( |
|
|
id int identity PRIMARY KEY |
|
|
, name nvarchar(255) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, color_id nvarchar(50) DEFAULT NULL |
|
|
, UNIQUE(project_id, name) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.task_has_tags ( |
|
|
task_id int NOT NULL |
|
|
, tag_id int NOT NULL |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(tag_id) REFERENCES dbo.tags(id) ON DELETE CASCADE |
|
|
, UNIQUE(tag_id, task_id) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_has_roles ( |
|
|
role_id int identity PRIMARY KEY |
|
|
, role nvarchar(255) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, UNIQUE(project_id, role) |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.column_has_move_restrictions ( |
|
|
restriction_id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, role_id int NOT NULL |
|
|
, src_column_id int NOT NULL |
|
|
, dst_column_id int NOT NULL |
|
|
, only_assigned bit DEFAULT 0 |
|
|
, UNIQUE(role_id, src_column_id, dst_column_id) |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(role_id) REFERENCES dbo.project_has_roles(role_id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(src_column_id) REFERENCES dbo.columns(id) ON DELETE NO ACTION /* columns_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(dst_column_id) REFERENCES dbo.columns(id) ON DELETE NO ACTION /* columns_cascade_delete_trigger */ |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_role_has_restrictions ( |
|
|
restriction_id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, role_id int NOT NULL |
|
|
, [rule] nvarchar(255) NOT NULL |
|
|
, UNIQUE(role_id, [rule]) |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(role_id) REFERENCES dbo.project_has_roles(role_id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.column_has_restrictions ( |
|
|
restriction_id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, role_id int NOT NULL |
|
|
, column_id int NOT NULL |
|
|
, [rule] nvarchar(255) NOT NULL |
|
|
, UNIQUE(role_id, column_id, [rule]) |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(role_id) REFERENCES dbo.project_has_roles(role_id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(column_id) REFERENCES dbo.columns(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.invites ( |
|
|
email nvarchar(255) NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, token nvarchar(255) NOT NULL |
|
|
, PRIMARY KEY(email, token) |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.project_activities ( |
|
|
id int identity PRIMARY KEY |
|
|
, date_creation bigint NOT NULL |
|
|
, event_name nvarchar(max) NOT NULL |
|
|
, creator_id int NOT NULL |
|
|
, project_id int NOT NULL |
|
|
, task_id int NOT NULL |
|
|
, data nvarchar(max) |
|
|
, FOREIGN KEY(creator_id) REFERENCES dbo.users(id) ON DELETE CASCADE |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE NO ACTION /* projects_cascade_delete_trigger */ |
|
|
, FOREIGN KEY(task_id) REFERENCES dbo.tasks(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.predefined_task_descriptions ( |
|
|
id int identity PRIMARY KEY |
|
|
, project_id int NOT NULL |
|
|
, title nvarchar(max) NOT NULL |
|
|
, description nvarchar(max) NOT NULL |
|
|
, FOREIGN KEY(project_id) REFERENCES dbo.projects(id) ON DELETE CASCADE |
|
|
); |
|
|
"); |
|
|
$pdo->exec(" |
|
|
CREATE TABLE dbo.sessions ( |
|
|
id nvarchar(450) PRIMARY KEY /* max length for primary key */ |
|
|
, expire_at int NOT NULL |
|
|
, data nvarchar(max) DEFAULT '' |
|
|
); |
|
|
"); |
|
|
|
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE TRIGGER dbo.columns_cascade_delete_trigger |
|
|
ON dbo.columns INSTEAD OF DELETE |
|
|
AS |
|
|
SET NOCOUNT ON; |
|
|
DELETE dbo.column_has_move_restrictions |
|
|
WHERE src_column_id IN (SELECT id FROM deleted) |
|
|
OR dst_column_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.transitions |
|
|
WHERE src_column_id IN (SELECT id FROM deleted) |
|
|
OR dst_column_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.tasks |
|
|
WHERE column_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.columns |
|
|
WHERE id IN (SELECT id FROM deleted); |
|
|
"); |
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE TRIGGER projects_cascade_delete_trigger |
|
|
ON dbo.projects INSTEAD OF DELETE |
|
|
AS |
|
|
SET NOCOUNT ON; |
|
|
DELETE dbo.column_has_move_restrictions |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.column_has_restrictions |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.columns |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.project_activities |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.project_daily_column_stats |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.project_role_has_restrictions |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.swimlanes |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.tasks |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.transitions |
|
|
WHERE project_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.projects |
|
|
WHERE id IN (SELECT id FROM deleted); |
|
|
"); |
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE TRIGGER dbo.swimlanes_cascade_delete_trigger |
|
|
ON dbo.swimlanes INSTEAD OF DELETE |
|
|
AS |
|
|
SET NOCOUNT ON; |
|
|
DELETE dbo.tasks |
|
|
WHERE swimlane_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.swimlanes |
|
|
WHERE id IN (SELECT id FROM deleted); |
|
|
"); |
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE TRIGGER dbo.tasks_cascade_delete_trigger |
|
|
ON dbo.tasks INSTEAD OF DELETE |
|
|
AS |
|
|
SET NOCOUNT ON; |
|
|
DELETE dbo.task_has_links |
|
|
WHERE opposite_task_id IN (SELECT id FROM deleted); |
|
|
DELETE dbo.tasks |
|
|
WHERE id IN (SELECT id FROM deleted); |
|
|
"); |
|
|
|
|
|
|
|
|
$pdo->exec(" |
|
|
ALTER TABLE dbo.project_has_users |
|
|
ADD DEFAULT N'" .Role::PROJECT_VIEWER. "' FOR role; |
|
|
"); |
|
|
$pdo->exec(" |
|
|
ALTER TABLE dbo.users |
|
|
ADD DEFAULT N'" .Role::APP_USER. "' FOR role; |
|
|
"); |
|
|
|
|
|
|
|
|
$aui = $pdo->prepare("INSERT INTO dbo.users (username, password, role) VALUES (?, ?, ?);"); |
|
|
$aui->execute(array('admin', \password_hash('admin', PASSWORD_BCRYPT), Role::APP_ADMIN)); |
|
|
|
|
|
$rq = $pdo->prepare('INSERT INTO dbo.settings ([option],value) VALUES (?, ?);'); |
|
|
$rq->execute(array('api_token', Token::getToken())); |
|
|
$rq->execute(array('application_url', defined('KANBOARD_URL') ? KANBOARD_URL : '')); |
|
|
$rq->execute(array('board_highlight_period', defined('RECENT_TASK_PERIOD') ? RECENT_TASK_PERIOD : 48*60*60)); |
|
|
$rq->execute(array('board_private_refresh_interval', defined('BOARD_CHECK_INTERVAL') ? BOARD_CHECK_INTERVAL : 10)); |
|
|
$rq->execute(array('board_public_refresh_interval', defined('BOARD_PUBLIC_CHECK_INTERVAL') ? BOARD_PUBLIC_CHECK_INTERVAL : 60)); |
|
|
$rq->execute(array('webhook_token', Token::getToken())); |
|
|
|
|
|
$pdo->exec(" |
|
|
INSERT INTO dbo.settings ([option], value) VALUES |
|
|
('application_currency','USD'), |
|
|
('application_date_format','m/d/Y'), |
|
|
('application_language','en_US'), |
|
|
('application_stylesheet',''), |
|
|
('application_time_format','H:i'), |
|
|
('application_timezone','UTC'), |
|
|
('board_columns',''), |
|
|
('calendar_project_tasks','date_started'), |
|
|
('calendar_user_subtasks_time_tracking','0'), |
|
|
('calendar_user_tasks','date_started'), |
|
|
('cfd_include_closed_tasks','1'), |
|
|
('default_color','yellow'), |
|
|
('integration_gravatar','0'), |
|
|
('password_reset','1'), |
|
|
('project_categories',''), |
|
|
('subtask_restriction','0'), |
|
|
('subtask_time_tracking','1'), |
|
|
('webhook_url','') |
|
|
; |
|
|
"); |
|
|
|
|
|
$pdo->exec(" |
|
|
SET IDENTITY_INSERT dbo.links ON; |
|
|
INSERT INTO dbo.links (id, label, opposite_id) VALUES |
|
|
(1,'relates to',0), |
|
|
(2,'blocks',3), |
|
|
(3,'is blocked by',2), |
|
|
(4,'duplicates',5), |
|
|
(5,'is duplicated by',4), |
|
|
(6,'is a child of',7), |
|
|
(7,'is a parent of',6), |
|
|
(8,'targets milestone',9), |
|
|
(9,'is a milestone of',8), |
|
|
(10,'fixes',11), |
|
|
(11,'is fixed by',10) |
|
|
; |
|
|
SET IDENTITY_INSERT dbo.links OFF; |
|
|
"); |
|
|
|
|
|
|
|
|
$pdo->exec(" |
|
|
CREATE UNIQUE INDEX users_username_idx ON dbo.users(username); |
|
|
CREATE UNIQUE INDEX project_daily_column_stats_idx ON dbo.project_daily_column_stats(day, project_id, column_id); |
|
|
CREATE UNIQUE INDEX task_has_links_unique ON dbo.task_has_links(link_id, task_id, opposite_task_id); |
|
|
CREATE UNIQUE INDEX project_daily_stats_idx ON dbo.project_daily_stats(day, project_id); |
|
|
CREATE UNIQUE INDEX user_has_notification_types_user_idx ON dbo.user_has_notification_types(user_id, notification_type); |
|
|
|
|
|
CREATE INDEX columns_project_idx ON dbo.columns(project_id); |
|
|
CREATE INDEX swimlanes_project_idx ON dbo.swimlanes(project_id); |
|
|
CREATE INDEX categories_project_idx ON dbo.project_has_categories(project_id); |
|
|
CREATE INDEX subtasks_task_idx ON dbo.subtasks(task_id); |
|
|
CREATE INDEX files_task_idx ON dbo.task_has_files(task_id); |
|
|
CREATE INDEX task_has_links_task_index ON dbo.task_has_links(task_id); |
|
|
CREATE INDEX transitions_task_index ON dbo.transitions(task_id); |
|
|
CREATE INDEX transitions_project_index ON dbo.transitions(project_id); |
|
|
CREATE INDEX transitions_user_index ON dbo.transitions(user_id); |
|
|
"); |
|
|
} |
|
|
|
