| | <?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); |
| | "); |
| | } |
| |
|
