201 lines
7.7 KiB
SQL
201 lines
7.7 KiB
SQL
CREATE TABLE boards (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
project_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
icon TEXT NOT NULL DEFAULT '',
|
|
show_description INTEGER NOT NULL DEFAULT 0,
|
|
card_properties TEXT NOT NULL DEFAULT '[]',
|
|
is_template INTEGER NOT NULL DEFAULT 0,
|
|
created_by INTEGER NOT NULL,
|
|
updated_by INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (updated_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE blocks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
board_id INTEGER NOT NULL,
|
|
parent_id INTEGER NOT NULL DEFAULT 0,
|
|
created_by INTEGER NOT NULL,
|
|
updated_by INTEGER NOT NULL,
|
|
type TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
fields TEXT NOT NULL DEFAULT '{}',
|
|
completed_at INTEGER NOT NULL DEFAULT 0,
|
|
completed_by INTEGER,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (updated_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE blocks_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
block_id INTEGER NOT NULL,
|
|
public_id TEXT NOT NULL,
|
|
board_id INTEGER NOT NULL,
|
|
parent_id INTEGER NOT NULL DEFAULT 0,
|
|
created_by INTEGER NOT NULL,
|
|
updated_by INTEGER NOT NULL,
|
|
type TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
fields TEXT NOT NULL DEFAULT '{}',
|
|
completed_at INTEGER NOT NULL DEFAULT 0,
|
|
completed_by INTEGER,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
insert_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE board_members (
|
|
board_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'writer',
|
|
created_at INTEGER NOT NULL,
|
|
PRIMARY KEY (board_id, user_id),
|
|
FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE block_comments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
block_id INTEGER NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE block_properties (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
block_id INTEGER NOT NULL UNIQUE,
|
|
status TEXT NOT NULL DEFAULT '',
|
|
card_type TEXT NOT NULL DEFAULT '',
|
|
priority TEXT NOT NULL DEFAULT '',
|
|
due_date TEXT NOT NULL DEFAULT '',
|
|
assignee_id INTEGER,
|
|
sprint TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE board_field_values (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
board_id INTEGER NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
|
|
field TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '',
|
|
display_order INTEGER NOT NULL DEFAULT 0,
|
|
start_date TEXT NOT NULL DEFAULT '',
|
|
end_date TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
UNIQUE(board_id, field, value)
|
|
);
|
|
|
|
CREATE TABLE block_checklist_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
block_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
display_order INTEGER NOT NULL DEFAULT 0,
|
|
created_by INTEGER NOT NULL,
|
|
updated_by INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (updated_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE block_attachments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
block_id INTEGER NOT NULL,
|
|
filename TEXT NOT NULL,
|
|
content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
|
|
size INTEGER NOT NULL DEFAULT 0,
|
|
storage_path TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
delete_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE block_assignees (
|
|
block_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
assigned_at INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (block_id, user_id),
|
|
FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_provider_group_mappings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
auth_provider_id INTEGER NOT NULL,
|
|
group_id INTEGER NOT NULL,
|
|
claim_value TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (auth_provider_id) REFERENCES auth_providers(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
|
|
UNIQUE (auth_provider_id, claim_value, group_id)
|
|
);
|
|
|
|
ALTER TABLE repos ADD COLUMN description TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE users ADD COLUMN avatar_storage_path TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE users ADD COLUMN avatar_content_type TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE users ADD COLUMN avatar_updated_at INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE auth_providers ADD COLUMN oidc_end_session_url TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE auth_providers ADD COLUMN group_sync_mode TEXT NOT NULL DEFAULT 'off';
|
|
ALTER TABLE auth_providers ADD COLUMN oidc_post_logout_redirect INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE sessions ADD COLUMN oidc_id_token TEXT NOT NULL DEFAULT '';
|
|
|
|
INSERT OR IGNORE INTO block_properties (block_id, status, description)
|
|
SELECT bl.id,
|
|
COALESCE(json_extract(bl.fields, '$.status'), ''),
|
|
COALESCE(json_extract(bl.fields, '$.description'), '')
|
|
FROM blocks bl
|
|
WHERE bl.type = 'card' AND bl.delete_at = 0;
|
|
|
|
INSERT OR IGNORE INTO block_assignees (block_id, user_id, assigned_at)
|
|
SELECT block_id, assignee_id, strftime('%s', 'now')
|
|
FROM block_properties
|
|
WHERE assignee_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_boards_project ON boards(project_id, delete_at);
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_board ON blocks(board_id, delete_at);
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_parent ON blocks(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_type ON blocks(board_id, type, delete_at);
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_history_block ON blocks_history(block_id, insert_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_board_members_user ON board_members(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_block_comments_block ON block_comments(block_id, delete_at);
|
|
CREATE INDEX IF NOT EXISTS idx_block_properties_block ON block_properties(block_id);
|
|
CREATE INDEX IF NOT EXISTS idx_board_field_values_board_field ON board_field_values(board_id, field);
|
|
CREATE INDEX IF NOT EXISTS idx_block_checklist_items_block ON block_checklist_items(block_id, delete_at, display_order);
|
|
CREATE INDEX IF NOT EXISTS idx_block_attachments_block ON block_attachments(block_id, delete_at, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_block_assignees_user ON block_assignees(user_id, block_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_provider_group_mappings_provider ON auth_provider_group_mappings(auth_provider_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_provider_group_mappings_claim ON auth_provider_group_mappings(auth_provider_id, claim_value);
|