| 1 | -- Issues became work items of a workspace (the work service's |
| 2 | -- 0049_work_items). Items are kept by key: a work item by its id, a pull |
| 3 | -- request by its repository and number. A work item keeps its workspace, |
| 4 | -- its number there, its origin (where it was filed, if anywhere) and every |
| 5 | -- repository it is linked to. |
| 6 | -- |
| 7 | -- Pull requests are copied as they are. Issues are dropped: they are |
| 8 | -- indexed again as work items, by id, when the work service's move |
| 9 | -- announces them (`work.migrated`), with their workspace and new number. |
| 10 | |
| 11 | DROP TRIGGER items_ai; |
| 12 | DROP TRIGGER items_ad; |
| 13 | DROP TRIGGER items_au; |
| 14 | DROP TABLE items_fts; |
| 15 | |
| 16 | CREATE TABLE items_next ( |
| 17 | iid INTEGER PRIMARY KEY, |
| 18 | -- item or pull. |
| 19 | kind TEXT NOT NULL, |
| 20 | -- A work item's id; a pull request's repository id and number, `rep_…/12`. |
| 21 | key TEXT NOT NULL UNIQUE, |
| 22 | -- A pull request's repository; a work item's origin, if it has one. |
| 23 | repo_id TEXT, |
| 24 | -- A work item's workspace, lowercase. |
| 25 | workspace TEXT, |
| 26 | number INTEGER NOT NULL, |
| 27 | title TEXT NOT NULL, |
| 28 | body TEXT NOT NULL DEFAULT '', |
| 29 | -- open or closed. |
| 30 | state TEXT NOT NULL, |
| 31 | -- A work item: open, completed or not_planned. A pull request: draft, |
| 32 | -- open, merged or closed. |
| 33 | status TEXT NOT NULL, |
| 34 | -- Lowercase username. |
| 35 | author TEXT NOT NULL, |
| 36 | requested_by TEXT, |
| 37 | -- Lowercase, each between bars: |bug|good first issue|. |
| 38 | labels TEXT NOT NULL DEFAULT '|', |
| 39 | -- A work item's linked repositories by id, each between bars: |
| 40 | -- |rep_1|rep_2|. |
| 41 | repos TEXT NOT NULL DEFAULT '|', |
| 42 | created_at TEXT NOT NULL, |
| 43 | updated_at TEXT NOT NULL |
| 44 | ); |
| 45 | |
| 46 | INSERT INTO items_next |
| 47 | (iid, kind, key, repo_id, workspace, number, title, body, state, status, author, requested_by, labels, repos, |
| 48 | created_at, updated_at) |
| 49 | SELECT iid, kind, repo_id || '/' || number, repo_id, NULL, number, title, body, state, status, author, requested_by, |
| 50 | labels, '|', created_at, updated_at |
| 51 | FROM items WHERE kind = 'pull'; |
| 52 | |
| 53 | DROP TABLE items; |
| 54 | ALTER TABLE items_next RENAME TO items; |
| 55 | |
| 56 | CREATE INDEX items_recent ON items (kind, updated_at); |
| 57 | CREATE INDEX items_by_author ON items (author); |
| 58 | CREATE INDEX items_by_repo ON items (repo_id); |
| 59 | CREATE INDEX items_by_workspace ON items (workspace, number); |
| 60 | |
| 61 | CREATE VIRTUAL TABLE items_fts USING fts5( |
| 62 | title, body, |
| 63 | content = 'items', content_rowid = 'iid', |
| 64 | tokenize = 'unicode61 remove_diacritics 2', prefix = '2 3' |
| 65 | ); |
| 66 | INSERT INTO items_fts (items_fts) VALUES ('rebuild'); |
| 67 | CREATE TRIGGER items_ai AFTER INSERT ON items BEGIN |
| 68 | INSERT INTO items_fts (rowid, title, body) VALUES (new.iid, new.title, new.body); |
| 69 | END; |
| 70 | CREATE TRIGGER items_ad AFTER DELETE ON items BEGIN |
| 71 | INSERT INTO items_fts (items_fts, rowid, title, body) VALUES ('delete', old.iid, old.title, old.body); |
| 72 | END; |
| 73 | CREATE TRIGGER items_au AFTER UPDATE OF title, body ON items BEGIN |
| 74 | INSERT INTO items_fts (items_fts, rowid, title, body) VALUES ('delete', old.iid, old.title, old.body); |
| 75 | INSERT INTO items_fts (rowid, title, body) VALUES (new.iid, new.title, new.body); |
| 76 | END; |