g1t/services/work/migrations/0025_requested_by.sql
| 1 | -- g1t is the author of the pull requests it makes and of the issues its |
| 2 | -- agent files while at work. The person who asked for the work is kept |
| 3 | -- beside it as requested_by, and answers for it as its author would: |
| 4 | -- every rule that read the author reads COALESCE(requested_by, author) |
| 5 | -- now (Pull::owner in the contracts). |
| 6 | ALTER TABLE pulls ADD COLUMN requested_by_id TEXT; |
| 7 | ALTER TABLE pulls ADD COLUMN requested_by_name TEXT; |
| 8 | ALTER TABLE issues ADD COLUMN requested_by_id TEXT; |
| 9 | ALTER TABLE issues ADD COLUMN requested_by_name TEXT; |
| 10 | |
| 11 | -- A person's own work, and the work g1t made for them, for their lists: |
| 12 | -- what they are working on, and their profile. |
| 13 | CREATE INDEX IF NOT EXISTS pulls_by_owner ON pulls (COALESCE(requested_by_id, author_id), status); |
| 14 | CREATE INDEX IF NOT EXISTS issues_by_owner ON issues (COALESCE(requested_by_id, author_id), created_at); |
| 15 | |
| 16 | -- The pull requests g1t made until now, stored as the person who asked. |
| 17 | -- What marks one is what made_by_g1t reads: g1t's agent, hosted, in a |
| 18 | -- fork of its own. Work g1t started itself is stored as g1t already |
| 19 | -- (its id, or an older one) and nobody asked for it: it stays as it is. |
| 20 | UPDATE pulls |
| 21 | SET requested_by_id = author_id, |
| 22 | requested_by_name = author_name, |
| 23 | author_id = 'usr_g1t_agent', |
| 24 | author_name = 'g1t' |
| 25 | WHERE agent = 'g1t' |
| 26 | AND runtime = 'hosted' |
| 27 | AND fork_namespace IS NOT NULL |
| 28 | AND fork_name IS NOT NULL |
| 29 | AND requested_by_id IS NULL |
| 30 | AND author_id NOT IN ('usr_g1t_agent', 'g1t', 'g1t_policy', 'svc_runner', 'g1t_runner'); |
| 31 | |
| 32 | -- The issues g1t's agent filed are stored as g1t already; who it was |
| 33 | -- working for was not kept. It is the person who asked for the pull |
| 34 | -- request whose run was going in that workspace when the issue was |
| 35 | -- filed, where exactly one person's was. Where none or several were, |
| 36 | -- nobody is named rather than the wrong person. |
| 37 | UPDATE issues |
| 38 | SET requested_by_id = ( |
| 39 | SELECT p.requested_by_id |
| 40 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id |
| 41 | WHERE p.requested_by_id IS NOT NULL |
| 42 | AND (r.repo_id = issues.repo_id |
| 43 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) |
| 44 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at |
| 45 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at |
| 46 | LIMIT 1), |
| 47 | requested_by_name = ( |
| 48 | SELECT p.requested_by_name |
| 49 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id |
| 50 | WHERE p.requested_by_id IS NOT NULL |
| 51 | AND (r.repo_id = issues.repo_id |
| 52 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) |
| 53 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at |
| 54 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at |
| 55 | LIMIT 1) |
| 56 | WHERE author_id = 'usr_g1t_agent' |
| 57 | AND requested_by_id IS NULL |
| 58 | AND ( |
| 59 | SELECT count(DISTINCT p.requested_by_id) |
| 60 | FROM agent_runs r JOIN pulls p ON p.id = r.pull_id |
| 61 | WHERE p.requested_by_id IS NOT NULL |
| 62 | AND (r.repo_id = issues.repo_id |
| 63 | OR r.workspace IN (SELECT workspace FROM agent_runs WHERE repo_id = issues.repo_id)) |
| 64 | AND COALESCE(r.started_at, r.created_at) <= issues.created_at |
| 65 | AND COALESCE(r.finished_at, r.updated_at) >= issues.created_at |
| 66 | ) = 1; |