internal/store/migrations/0039_mr_head_history.up.sql

 1-- Every push to a merge request's source stales its reviews, and nothing
 2-- said what had changed between the two heads. Answering that needs the
 3-- head it used to be, which nothing kept: merge_requests.head_sha is
 4-- overwritten in place (#111).
 5--
 6-- One row per head a merge request has had, oldest first by id. The base
 7-- recorded alongside is the merge base at that moment, so a range-diff
 8-- compares like with like even when the target moved underneath.
 9CREATE TABLE mr_heads (
10    id         INTEGER PRIMARY KEY,
11    mr_id      INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
12    sha        TEXT NOT NULL,
13    base_sha   TEXT NOT NULL DEFAULT '',
14    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
15);
16CREATE INDEX mr_heads_mr ON mr_heads(mr_id, id);
17
18-- The current head of every existing merge request, so one that has never
19-- been force-pushed since this migration still has a first entry to
20-- measure from.
21INSERT INTO mr_heads (mr_id, sha, base_sha)
22SELECT id, head_sha, COALESCE(merged_base, '') FROM merge_requests WHERE head_sha <> '';