internal/store/migrations/0052_org_scope.up.sql

 1-- foreign_keys: off
 2-- Labels and milestones scoped to a repository or to an org (#203).
 3-- Exactly one of repo_id and org_id is set. Uniqueness is per scope, as
 4-- two partial indexes; the app refuses a repo name the org already holds.
 5--
 6-- Both tables have children (issue_labels, issues.milestone_id,
 7-- merge_requests.milestone_id). Foreign keys are off for this migration:
 8-- rebuilding a parent table that children reference loses the children's
 9-- rows with foreign keys on. legacy_alter_table keeps the children naming
10-- labels and milestones through the rename, so they bind to the new
11-- tables rather than to labels_old/milestones_old. foreign_key_check
12-- afterwards proves the ids line up.
13PRAGMA legacy_alter_table = ON;
14
15ALTER TABLE labels RENAME TO labels_old;
16CREATE TABLE labels (
17    id      INTEGER PRIMARY KEY,
18    repo_id INTEGER REFERENCES repos(id) ON DELETE CASCADE,
19    org_id  INTEGER REFERENCES orgs(id) ON DELETE CASCADE,
20    name    TEXT NOT NULL,
21    color   TEXT NOT NULL DEFAULT '',
22    CHECK ((repo_id IS NULL) <> (org_id IS NULL))
23);
24INSERT INTO labels (id, repo_id, name, color)
25    SELECT id, repo_id, name, color FROM labels_old;
26DROP TABLE labels_old;
27CREATE UNIQUE INDEX labels_repo_name ON labels(repo_id, name) WHERE repo_id IS NOT NULL;
28CREATE UNIQUE INDEX labels_org_name  ON labels(org_id, name)  WHERE org_id  IS NOT NULL;
29
30ALTER TABLE milestones RENAME TO milestones_old;
31CREATE TABLE milestones (
32    id          INTEGER PRIMARY KEY,
33    repo_id     INTEGER REFERENCES repos(id) ON DELETE CASCADE,
34    org_id      INTEGER REFERENCES orgs(id) ON DELETE CASCADE,
35    title       TEXT NOT NULL,
36    description TEXT NOT NULL DEFAULT '',
37    due_date    TEXT NOT NULL DEFAULT '',
38    state       TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open','closed')),
39    created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
40    CHECK ((repo_id IS NULL) <> (org_id IS NULL))
41);
42INSERT INTO milestones (id, repo_id, title, description, due_date, state, created_at)
43    SELECT id, repo_id, title, description, due_date, state, created_at FROM milestones_old;
44DROP TABLE milestones_old;
45CREATE UNIQUE INDEX milestones_repo_title ON milestones(repo_id, title) WHERE repo_id IS NOT NULL;
46CREATE UNIQUE INDEX milestones_org_title  ON milestones(org_id, title)  WHERE org_id  IS NOT NULL;
47
48PRAGMA legacy_alter_table = OFF;