| 1 | -- Issues move out of repositories into their workspaces as work items |
| 2 | -- (src/items.rs). A work item belongs to a workspace, is numbered in the |
| 3 | -- workspace's sequence (which new pull requests share), and links the |
| 4 | -- repositories it concerns: where it was filed (origin, which keeps the |
| 5 | -- issue's old number), where it is meant to land (target), and where work |
| 6 | -- for it landed or is under way (touched). |
| 7 | -- |
| 8 | -- This database does not know which workspace a repository is in, so the |
| 9 | -- job in src/work_migration.rs assigns each work item its workspace and |
| 10 | -- number, and moves labels and milestones to the workspace, once it has |
| 11 | -- asked repos. Everything else moves here: every row that named an issue |
| 12 | -- by repository and number names its work item by id. Until the job is |
| 13 | -- done, an old `#N` resolves through `work_item_repos.legacy_number`. |
| 14 | -- |
| 15 | -- Tables are rebuilt the way D1 allows: inside the migration's |
| 16 | -- transaction, with foreign keys deferred until every rebuilt table is in |
| 17 | -- place again. |
| 18 | PRAGMA defer_foreign_keys = true; |
| 19 | |
| 20 | -- Which workspace each repository is in, and its path: recorded by the |
| 21 | -- job, then kept on repo.created, renamed, transferred and visibility |
| 22 | -- changes. `private` is 1 until the repository is known to be public. |
| 23 | CREATE TABLE repo_workspaces ( |
| 24 | repo_id TEXT PRIMARY KEY, |
| 25 | workspace TEXT NOT NULL, |
| 26 | -- owner/name, as it is now. |
| 27 | path TEXT NOT NULL, |
| 28 | private INTEGER NOT NULL DEFAULT 1 |
| 29 | ); |
| 30 | CREATE INDEX repo_workspaces_by_workspace ON repo_workspaces (workspace); |
| 31 | |
| 32 | -- Each workspace's sequence of numbers, shared by work items and new pull |
| 33 | -- requests. The per-repository `counters` stays, emptied by the job, for |
| 34 | -- the code that still reads it while the job runs. |
| 35 | CREATE TABLE work_counters ( |
| 36 | workspace TEXT PRIMARY KEY, |
| 37 | last INTEGER NOT NULL |
| 38 | ); |
| 39 | |
| 40 | -- One-off jobs, resumable: where each got to, and when it finished. |
| 41 | CREATE TABLE work_jobs ( |
| 42 | name TEXT PRIMARY KEY, |
| 43 | cursor TEXT, |
| 44 | done_at TEXT |
| 45 | ); |
| 46 | INSERT INTO work_jobs (name) VALUES ('work_items'); |
| 47 | |
| 48 | -- The issues, renamed first so that pulls' reference follows them. |
| 49 | ALTER TABLE issues RENAME TO work_items; |
| 50 | |
| 51 | CREATE TABLE work_items_next ( |
| 52 | -- wki_…; one moved from an issue keeps its iss_… id. |
| 53 | id TEXT PRIMARY KEY, |
| 54 | -- The workspace's slug and its number there: NULL until the job |
| 55 | -- assigns them to a moved issue. |
| 56 | workspace TEXT, |
| 57 | number INTEGER, |
| 58 | title TEXT NOT NULL, |
| 59 | body TEXT NOT NULL, |
| 60 | -- JSON array of label names. |
| 61 | labels TEXT NOT NULL DEFAULT '[]', |
| 62 | state TEXT NOT NULL DEFAULT 'open', |
| 63 | -- Why it was closed: completed or not_planned. |
| 64 | reason TEXT, |
| 65 | -- JSON array of { repo_id, pull, commit, pushed_by }: what resolved it |
| 66 | -- in each repository it landed in. |
| 67 | resolutions TEXT NOT NULL DEFAULT '[]', |
| 68 | author_id TEXT NOT NULL, |
| 69 | author_name TEXT NOT NULL, |
| 70 | requested_by_id TEXT, |
| 71 | requested_by_name TEXT, |
| 72 | created_at TEXT NOT NULL, |
| 73 | updated_at TEXT NOT NULL, |
| 74 | closed_at TEXT, |
| 75 | -- JSON array of usernames. |
| 76 | assignees TEXT NOT NULL DEFAULT '[]', |
| 77 | -- JSON array of the ids of the work items that have to land first. |
| 78 | blocked_by TEXT NOT NULL DEFAULT '[]', |
| 79 | -- The number of its workspace milestone. |
| 80 | milestone INTEGER, |
| 81 | UNIQUE (workspace, number) |
| 82 | ); |
| 83 | |
| 84 | INSERT INTO work_items_next |
| 85 | (id, workspace, number, title, body, labels, state, reason, resolutions, author_id, author_name, |
| 86 | requested_by_id, requested_by_name, created_at, updated_at, closed_at, assignees, blocked_by, milestone) |
| 87 | SELECT |
| 88 | i.id, NULL, NULL, i.title, i.body, i.labels, i.state, i.reason, |
| 89 | CASE WHEN i.resolved_by IS NOT NULL OR i.resolved_commit IS NOT NULL |
| 90 | THEN json_array(json_object('repo_id', i.repo_id, 'pull', i.resolved_by, 'commit', i.resolved_commit, |
| 91 | 'pushed_by', i.resolved_pushed_by)) |
| 92 | ELSE '[]' END, |
| 93 | i.author_id, i.author_name, i.requested_by_id, i.requested_by_name, i.created_at, i.updated_at, i.closed_at, |
| 94 | i.assignees, |
| 95 | (SELECT json_group_array(id) FROM |
| 96 | (SELECT b.id AS id FROM json_each(i.blocked_by) j JOIN work_items b ON b.repo_id = i.repo_id AND b.number = j.value |
| 97 | ORDER BY j.key)), |
| 98 | i.milestone |
| 99 | FROM work_items i; |
| 100 | |
| 101 | -- The repositories each work item is linked to. |
| 102 | CREATE TABLE work_item_repos ( |
| 103 | item_id TEXT NOT NULL, |
| 104 | repo_id TEXT NOT NULL, |
| 105 | role TEXT NOT NULL CHECK (role IN ('origin', 'target', 'touched')), |
| 106 | -- For the origin of a moved issue: its number in that repository. |
| 107 | legacy_number INTEGER, |
| 108 | -- owner/name: filled in by the job, kept when the repository goes. |
| 109 | path TEXT, |
| 110 | -- When something that closes it landed there. |
| 111 | landed_at TEXT, |
| 112 | added_at TEXT NOT NULL, |
| 113 | -- Who linked it, by id; NULL when g1t did. |
| 114 | added_by TEXT, |
| 115 | PRIMARY KEY (item_id, repo_id) |
| 116 | ); |
| 117 | CREATE INDEX work_item_repos_by_repo ON work_item_repos (repo_id, role); |
| 118 | CREATE UNIQUE INDEX work_item_repos_by_legacy ON work_item_repos (repo_id, legacy_number) |
| 119 | WHERE legacy_number IS NOT NULL; |
| 120 | |
| 121 | INSERT INTO work_item_repos (item_id, repo_id, role, legacy_number, path, landed_at, added_at, added_by) |
| 122 | SELECT id, repo_id, 'origin', number, NULL, |
| 123 | CASE WHEN state = 'closed' AND (resolved_by IS NOT NULL OR resolved_commit IS NOT NULL) THEN closed_at END, |
| 124 | created_at, author_id |
| 125 | FROM work_items; |
| 126 | |
| 127 | DROP TABLE work_items; |
| 128 | ALTER TABLE work_items_next RENAME TO work_items; |
| 129 | CREATE INDEX work_items_by_state ON work_items (workspace, state, number); |
| 130 | CREATE INDEX work_items_by_milestone ON work_items (workspace, milestone); |
| 131 | CREATE INDEX work_items_by_author ON work_items (author_id, created_at); |
| 132 | CREATE INDEX work_items_by_owner ON work_items (COALESCE(requested_by_id, author_id), created_at); |
| 133 | CREATE INDEX work_items_unnumbered ON work_items (created_at, id) WHERE number IS NULL; |
| 134 | |
| 135 | -- Pull requests name their work item by id, and what they close or are |
| 136 | -- part of as JSON arrays of ids. |
| 137 | DROP INDEX pulls_by_issue; |
| 138 | ALTER TABLE pulls RENAME COLUMN issue_id TO item_id; |
| 139 | ALTER TABLE pulls DROP COLUMN issue_number; |
| 140 | ALTER TABLE pulls ADD COLUMN closes_items TEXT NOT NULL DEFAULT '[]'; |
| 141 | ALTER TABLE pulls ADD COLUMN parts TEXT NOT NULL DEFAULT '[]'; |
| 142 | UPDATE pulls SET closes_items = (SELECT json_group_array(id) FROM |
| 143 | (SELECT r.item_id AS id FROM json_each(pulls.closes) j |
| 144 | JOIN work_item_repos r ON r.repo_id = pulls.repo_id AND r.legacy_number = j.value |
| 145 | ORDER BY j.key)) |
| 146 | WHERE closes IS NOT NULL AND json_valid(closes); |
| 147 | ALTER TABLE pulls DROP COLUMN closes; |
| 148 | CREATE INDEX pulls_by_item ON pulls (item_id); |
| 149 | |
| 150 | -- Comments: a pull request's by repository and number, a work item's by |
| 151 | -- its id. |
| 152 | CREATE TABLE comments_next ( |
| 153 | id TEXT PRIMARY KEY, |
| 154 | repo_id TEXT, |
| 155 | number INTEGER, |
| 156 | item_id TEXT, |
| 157 | author_id TEXT NOT NULL, |
| 158 | author_name TEXT NOT NULL, |
| 159 | body TEXT NOT NULL, |
| 160 | -- For a comment on one line of a pull request's change: the file, and |
| 161 | -- the line as numbered after the change. |
| 162 | path TEXT, |
| 163 | line INTEGER, |
| 164 | -- A reviewer's decision: approve or request_changes. |
| 165 | verdict TEXT, |
| 166 | created_at TEXT NOT NULL, |
| 167 | kind TEXT NOT NULL DEFAULT 'comment', |
| 168 | edited_at TEXT, |
| 169 | agent_id TEXT, |
| 170 | agent_handle TEXT, |
| 171 | agent_name TEXT, |
| 172 | agent_avatar_seed TEXT, |
| 173 | acting_for_id TEXT, |
| 174 | acting_for_name TEXT, |
| 175 | agent_verdict TEXT, |
| 176 | CHECK (item_id IS NOT NULL OR (repo_id IS NOT NULL AND number IS NOT NULL)) |
| 177 | ); |
| 178 | INSERT INTO comments_next |
| 179 | (id, repo_id, number, item_id, author_id, author_name, body, path, line, verdict, created_at, kind, edited_at, |
| 180 | agent_id, agent_handle, agent_name, agent_avatar_seed, acting_for_id, acting_for_name, agent_verdict) |
| 181 | SELECT c.id, |
| 182 | CASE WHEN r.item_id IS NULL THEN c.repo_id END, CASE WHEN r.item_id IS NULL THEN c.number END, r.item_id, |
| 183 | c.author_id, c.author_name, c.body, c.path, c.line, c.verdict, c.created_at, c.kind, c.edited_at, |
| 184 | c.agent_id, c.agent_handle, c.agent_name, c.agent_avatar_seed, c.acting_for_id, c.acting_for_name, c.agent_verdict |
| 185 | FROM comments c LEFT JOIN work_item_repos r ON r.repo_id = c.repo_id AND r.legacy_number = c.number; |
| 186 | DROP TABLE comments; |
| 187 | ALTER TABLE comments_next RENAME TO comments; |
| 188 | CREATE INDEX comments_by_subject ON comments (repo_id, number, id); |
| 189 | CREATE INDEX comments_by_item ON comments (item_id, id); |
| 190 | CREATE INDEX comments_by_author ON comments (author_id, created_at); |
| 191 | CREATE INDEX comments_by_agent ON comments (agent_id, repo_id, number, created_at) |
| 192 | WHERE agent_id IS NOT NULL; |
| 193 | |
| 194 | -- Reactions: the same. |
| 195 | CREATE TABLE reactions_next ( |
| 196 | -- The comment's id, or the pull request's or work item's own id for its |
| 197 | -- description. |
| 198 | subject TEXT NOT NULL, |
| 199 | repo_id TEXT, |
| 200 | number INTEGER, |
| 201 | item_id TEXT, |
| 202 | user_id TEXT NOT NULL, |
| 203 | user_name TEXT NOT NULL, |
| 204 | content TEXT NOT NULL, |
| 205 | created_at TEXT NOT NULL, |
| 206 | PRIMARY KEY (subject, user_id, content) |
| 207 | ); |
| 208 | INSERT INTO reactions_next (subject, repo_id, number, item_id, user_id, user_name, content, created_at) |
| 209 | SELECT x.subject, |
| 210 | CASE WHEN r.item_id IS NULL THEN x.repo_id END, CASE WHEN r.item_id IS NULL THEN x.number END, r.item_id, |
| 211 | x.user_id, x.user_name, x.content, x.created_at |
| 212 | FROM reactions x LEFT JOIN work_item_repos r ON r.repo_id = x.repo_id AND r.legacy_number = x.number; |
| 213 | DROP TABLE reactions; |
| 214 | ALTER TABLE reactions_next RENAME TO reactions; |
| 215 | CREATE INDEX reactions_by_thread ON reactions (repo_id, number); |
| 216 | CREATE INDEX reactions_by_item ON reactions (item_id); |
| 217 | CREATE INDEX reactions_by_user ON reactions (user_id); |
| 218 | |
| 219 | -- Claims are on a work item; the repository is the claimant's checkout. |
| 220 | CREATE TABLE claims_next ( |
| 221 | id TEXT PRIMARY KEY, |
| 222 | item_id TEXT NOT NULL, |
| 223 | repo_id TEXT, |
| 224 | session_id TEXT, |
| 225 | actor_id TEXT NOT NULL, |
| 226 | actor_name TEXT NOT NULL, |
| 227 | harness TEXT, |
| 228 | note TEXT, |
| 229 | -- active, released, expired or handed_off. |
| 230 | state TEXT NOT NULL DEFAULT 'active', |
| 231 | ttl_minutes INTEGER NOT NULL, |
| 232 | created_at TEXT NOT NULL, |
| 233 | expires_at TEXT NOT NULL, |
| 234 | ended_at TEXT, |
| 235 | end_reason TEXT, |
| 236 | -- For a hand-off: the username it is for; NULL for anyone. |
| 237 | handoff_to TEXT |
| 238 | ); |
| 239 | INSERT INTO claims_next |
| 240 | (id, item_id, repo_id, session_id, actor_id, actor_name, harness, note, state, ttl_minutes, created_at, |
| 241 | expires_at, ended_at, end_reason, handoff_to) |
| 242 | SELECT c.id, r.item_id, c.repo_id, c.session_id, c.actor_id, c.actor_name, c.harness, c.note, c.state, c.ttl_minutes, |
| 243 | c.created_at, c.expires_at, c.ended_at, c.end_reason, c.handoff_to |
| 244 | FROM claims c JOIN work_item_repos r ON r.repo_id = c.repo_id AND r.legacy_number = c.issue; |
| 245 | DROP TABLE claims; |
| 246 | ALTER TABLE claims_next RENAME TO claims; |
| 247 | CREATE INDEX claims_by_item ON claims (item_id, state); |
| 248 | CREATE INDEX claims_by_state ON claims (state, expires_at); |
| 249 | CREATE INDEX claims_by_repo ON claims (repo_id, state) WHERE repo_id IS NOT NULL; |
| 250 | CREATE INDEX claims_by_session ON claims (session_id, state) WHERE session_id IS NOT NULL; |
| 251 | |
| 252 | -- A session's work item, by id. |
| 253 | ALTER TABLE sessions ADD COLUMN item_id TEXT; |
| 254 | UPDATE sessions SET item_id = (SELECT r.item_id FROM work_item_repos r |
| 255 | WHERE r.repo_id = sessions.repo_id AND r.legacy_number = sessions.issue) |
| 256 | WHERE issue IS NOT NULL; |
| 257 | DROP INDEX sessions_by_issue; |
| 258 | ALTER TABLE sessions DROP COLUMN issue; |
| 259 | CREATE INDEX sessions_by_item ON sessions (item_id) WHERE item_id IS NOT NULL; |
| 260 | |
| 261 | -- Sessions' ties: a pull request by number, a work item (kind `item`) by |
| 262 | -- id, with number 0. |
| 263 | CREATE TABLE session_links_next ( |
| 264 | session_id TEXT NOT NULL, |
| 265 | repo_id TEXT NOT NULL, |
| 266 | -- item or pull. |
| 267 | kind TEXT NOT NULL, |
| 268 | -- A pull request's number; 0 for a work item. |
| 269 | number INTEGER NOT NULL, |
| 270 | -- A work item's id; '' for a pull request. |
| 271 | item_id TEXT NOT NULL DEFAULT '', |
| 272 | via TEXT NOT NULL, |
| 273 | at TEXT NOT NULL, |
| 274 | PRIMARY KEY (session_id, kind, number, item_id, via) |
| 275 | ); |
| 276 | INSERT INTO session_links_next (session_id, repo_id, kind, number, item_id, via, at) |
| 277 | SELECT session_id, repo_id, kind, number, '', via, at FROM session_links WHERE kind = 'pull'; |
| 278 | INSERT OR IGNORE INTO session_links_next (session_id, repo_id, kind, number, item_id, via, at) |
| 279 | SELECT l.session_id, l.repo_id, 'item', 0, r.item_id, l.via, l.at |
| 280 | FROM session_links l JOIN work_item_repos r ON r.repo_id = l.repo_id AND r.legacy_number = l.number |
| 281 | WHERE l.kind = 'issue'; |
| 282 | DROP TABLE session_links; |
| 283 | ALTER TABLE session_links_next RENAME TO session_links; |
| 284 | CREATE INDEX session_links_by_number ON session_links (repo_id, kind, number); |
| 285 | CREATE INDEX session_links_by_item ON session_links (item_id) WHERE item_id <> ''; |
| 286 | |
| 287 | -- What a session named: a work item (kind `item`) by id. |
| 288 | UPDATE OR IGNORE session_touches SET kind = 'item', |
| 289 | value = (SELECT r.item_id FROM work_item_repos r |
| 290 | WHERE r.repo_id = session_touches.repo_id AND r.legacy_number = CAST(session_touches.value AS INTEGER)) |
| 291 | WHERE kind = 'issue' AND EXISTS (SELECT 1 FROM work_item_repos r |
| 292 | WHERE r.repo_id = session_touches.repo_id AND r.legacy_number = CAST(session_touches.value AS INTEGER)); |
| 293 | DELETE FROM session_touches WHERE kind = 'issue'; |
| 294 | |
| 295 | -- Decisions on a work item, by id; a pull request's stay by number. |
| 296 | ALTER TABLE decisions ADD COLUMN item_id TEXT; |
| 297 | UPDATE decisions SET item_id = (SELECT r.item_id FROM work_item_repos r |
| 298 | WHERE r.repo_id = decisions.repo_id AND r.legacy_number = decisions.issue) |
| 299 | WHERE issue IS NOT NULL; |
| 300 | DROP INDEX decisions_by_issue; |
| 301 | ALTER TABLE decisions DROP COLUMN issue; |
| 302 | CREATE INDEX decisions_by_item ON decisions (item_id, created_at) WHERE item_id IS NOT NULL; |
| 303 | |
| 304 | -- Mentions of the retired agent, kept for their history: on a work item |
| 305 | -- by id too. |
| 306 | ALTER TABLE agent_mentions ADD COLUMN item_id TEXT; |
| 307 | UPDATE agent_mentions SET item_id = (SELECT r.item_id FROM work_item_repos r |
| 308 | WHERE r.repo_id = agent_mentions.repo_id AND r.legacy_number = agent_mentions.number) |
| 309 | WHERE pull_id IS NULL; |
| 310 | |
| 311 | ALTER TABLE queue_entries RENAME COLUMN keep_issue_open TO keep_item_open; |
| 312 | |
| 313 | ALTER TABLE repo_settings RENAME COLUMN has_issues TO has_work_items; |
| 314 | ALTER TABLE repo_settings RENAME COLUMN auto_close_issues TO auto_close_items; |
| 315 | UPDATE repo_settings SET merge_commit_template = replace(merge_commit_template, '{issues}', '{items}') |
| 316 | WHERE merge_commit_template LIKE '%{issues}%'; |
| 317 | UPDATE repo_settings SET squash_commit_template = replace(squash_commit_template, '{issues}', '{items}') |
| 318 | WHERE squash_commit_template LIKE '%{issues}%'; |
| 319 | |
| 320 | -- Labels and milestones belong to the workspace. The repositories' own |
| 321 | -- wait here until the job merges them in: labels by name (the first made |
| 322 | -- keeps its color and description), milestones renumbered in the |
| 323 | -- workspace (`milestone_moves` says where each went). |
| 324 | ALTER TABLE labels RENAME TO repo_labels; |
| 325 | ALTER TABLE milestones RENAME TO repo_milestones; |
| 326 | DROP INDEX milestones_by_state; |
| 327 | |
| 328 | CREATE TABLE labels ( |
| 329 | workspace TEXT NOT NULL, |
| 330 | name TEXT NOT NULL, |
| 331 | -- Six hex digits, without '#'. |
| 332 | color TEXT NOT NULL, |
| 333 | description TEXT NOT NULL DEFAULT '', |
| 334 | created_at TEXT NOT NULL, |
| 335 | PRIMARY KEY (workspace, name) |
| 336 | ); |
| 337 | |
| 338 | CREATE TABLE milestones ( |
| 339 | workspace TEXT NOT NULL, |
| 340 | number INTEGER NOT NULL, |
| 341 | -- For one moved from a repository: that repository's id. |
| 342 | origin_repo_id TEXT, |
| 343 | title TEXT NOT NULL, |
| 344 | description TEXT NOT NULL DEFAULT '', |
| 345 | -- YYYY-MM-DD, or NULL. |
| 346 | due_on TEXT, |
| 347 | -- open or closed. |
| 348 | state TEXT NOT NULL DEFAULT 'open', |
| 349 | created_at TEXT NOT NULL, |
| 350 | updated_at TEXT NOT NULL, |
| 351 | closed_at TEXT, |
| 352 | PRIMARY KEY (workspace, number) |
| 353 | ); |
| 354 | CREATE INDEX milestones_by_state ON milestones (workspace, state, due_on); |
| 355 | |
| 356 | CREATE TABLE milestone_moves ( |
| 357 | repo_id TEXT NOT NULL, |
| 358 | old_number INTEGER NOT NULL, |
| 359 | workspace TEXT NOT NULL, |
| 360 | number INTEGER NOT NULL, |
| 361 | PRIMARY KEY (repo_id, old_number) |
| 362 | ); |
| 363 | |
| 364 | PRAGMA defer_foreign_keys = false; |