| 1 | -- Issues became work items, numbered in their workspace. Links and |
| 2 | -- deliveries name the work item they are about; agent runs, the work item |
| 3 | -- they work on. The new numbers are known only once the work service has |
| 4 | -- numbered every item, so rows kept by a repository's issue number keep |
| 5 | -- it in `legacy_repo_id` and `legacy_number` until `work.migrated` says |
| 6 | -- where it went (src/work_migrated.rs), and are then cleared. |
| 7 | |
| 8 | -- A work item's tie to something outside g1t: the Sentry issue that |
| 9 | -- opened it, the ticket it was imported from. One per outside thing per |
| 10 | -- connection, so an alert that fires 500 times is one work item. |
| 11 | CREATE TABLE links_new ( |
| 12 | id TEXT PRIMARY KEY, |
| 13 | -- The work item's workspace, the connection's. |
| 14 | workspace TEXT NOT NULL, |
| 15 | connection_id TEXT NOT NULL, |
| 16 | provider TEXT NOT NULL, |
| 17 | -- The outside system's own id for it, as its API takes it. |
| 18 | external_id TEXT NOT NULL, |
| 19 | -- What people call it: TECH-1234, or a Sentry short id. |
| 20 | key TEXT NOT NULL, |
| 21 | title TEXT NOT NULL, |
| 22 | url TEXT NOT NULL, |
| 23 | -- The work item, by id and by number in the workspace. Null until |
| 24 | -- `work.migrated` for a link made to an issue. |
| 25 | item_id TEXT, |
| 26 | number INTEGER, |
| 27 | -- The issue it was made to, until `work.migrated`. |
| 28 | legacy_repo_id TEXT, |
| 29 | legacy_number INTEGER, |
| 30 | -- How many times an alert has fired for it, and the count last |
| 31 | -- mentioned on the work item. |
| 32 | count INTEGER NOT NULL DEFAULT 1, |
| 33 | announced INTEGER NOT NULL DEFAULT 1, |
| 34 | -- Whether the outside system has been told work started on it. |
| 35 | told_started INTEGER NOT NULL DEFAULT 0, |
| 36 | first_seen TEXT NOT NULL, |
| 37 | last_seen TEXT NOT NULL, |
| 38 | UNIQUE (connection_id, external_id) |
| 39 | ); |
| 40 | |
| 41 | INSERT INTO links_new |
| 42 | (id, workspace, connection_id, provider, external_id, key, title, url, item_id, number, legacy_repo_id, legacy_number, |
| 43 | count, announced, told_started, first_seen, last_seen) |
| 44 | SELECT id, workspace, connection_id, provider, external_id, key, title, url, NULL, NULL, repo_id, number, |
| 45 | count, announced, told_started, first_seen, last_seen |
| 46 | FROM links; |
| 47 | |
| 48 | -- What was received about an issue: `owner/name#number` becomes the work |
| 49 | -- item's `workspace#number`. The issue is found by its repository's path |
| 50 | -- through the links made to it. |
| 51 | ALTER TABLE deliveries ADD COLUMN item TEXT; |
| 52 | ALTER TABLE deliveries ADD COLUMN legacy_repo_id TEXT; |
| 53 | ALTER TABLE deliveries ADD COLUMN legacy_number INTEGER; |
| 54 | UPDATE deliveries SET |
| 55 | legacy_repo_id = (SELECT l.repo_id FROM links l WHERE l.repo = substr(deliveries.issue, 1, instr(deliveries.issue, '#') - 1) LIMIT 1), |
| 56 | legacy_number = CAST(substr(issue, instr(issue, '#') + 1) AS INTEGER) |
| 57 | WHERE issue IS NOT NULL AND instr(issue, '#') > 0; |
| 58 | ALTER TABLE deliveries DROP COLUMN issue; |
| 59 | CREATE INDEX deliveries_by_legacy ON deliveries (legacy_repo_id, legacy_number) WHERE legacy_repo_id IS NOT NULL; |
| 60 | |
| 61 | DROP TABLE links; |
| 62 | ALTER TABLE links_new RENAME TO links; |
| 63 | CREATE INDEX links_by_item ON links (item_id); |
| 64 | CREATE INDEX links_by_number ON links (workspace, number); |
| 65 | CREATE INDEX links_by_legacy ON links (legacy_repo_id, legacy_number) WHERE legacy_repo_id IS NOT NULL; |
| 66 | |
| 67 | -- The work item a run works on, by number in its workspace (for a pull |
| 68 | -- request with none, the pull request's number). Runs started on an issue |
| 69 | -- keep none: their g1t session names the work item. |
| 70 | ALTER TABLE agent_runs ADD COLUMN item INTEGER; |
| 71 | ALTER TABLE agent_runs DROP COLUMN issue; |