| 1 | -- Issues, and the number sequence they draw from. |
| 2 | -- |
| 3 | -- `repo_sequences` is a table of its own rather than a column on `repositories` because |
| 4 | -- the sequence is not a property of the repository: **pull requests will take numbers |
| 5 | -- from it too**, so `#4` means one thing on a repository whatever kind of thing it is. |
| 6 | -- Keeping it separate also means allocating a number never writes the repository row, |
| 7 | -- which a push is already contending for. |
| 8 | -- |
| 9 | -- `next_number` holds the number to hand out *next*, so a repository that has never |
| 10 | -- allocated one has no row at all and the first allocation inserts `2` — see |
| 11 | -- `SqliteIssueRepo::next_number`, where the single upsert-returning statement makes the |
| 12 | -- read-modify-write atomic. SQLite serialises writers, so two concurrent creations |
| 13 | -- cannot receive the same number. |
| 14 | ( |
| 15 | repo_id text primary key references repositories (id) on delete cascade, |
| 16 | next_number integer not null |
| 17 | ); |
| 18 | |
| 19 | -- `unique (repo_id, number)` is the constraint that makes the sequence load-bearing: if |
| 20 | -- allocation ever did hand out a duplicate, this fails the insert rather than letting |
| 21 | -- two issues answer to the same URL. |
| 22 | -- |
| 23 | -- `state` is text rather than an integer so a row is readable in a shell, matching |
| 24 | -- `repositories.visibility`. `closed_at` is null while open. |
| 25 | ( |
| 26 | id text primary key, |
| 27 | repo_id text not null references repositories (id) on delete cascade, |
| 28 | number integer not null, |
| 29 | title text not null, |
| 30 | body text not null, |
| 31 | state text not null, |
| 32 | author_id text not null references users (id), |
| 33 | created_at integer not null, |
| 34 | updated_at integer not null, |
| 35 | closed_at integer, |
| 36 | unique (repo_id, number) |
| 37 | ); |
| 38 | |
| 39 | -- The list page filters by state and orders by number descending, which this covers |
| 40 | -- end to end. |
| 41 | (repo_id, state, number desc); |