internal/store/migrations/0075_orphan_grants_deploy_keys.up.sql

 1-- Grants and parked profile about texts of deleted accounts and
 2-- organizations, and deploy keys of deleted repositories, name their
 3-- subject by id with no foreign key; deletes left them behind until #306. The counts go in a note the
 4-- daemon logs with the migration and then drops.
 5INSERT INTO settings (key, value)
 6    SELECT 'migration_note', 'removed grants of deleted accounts or organizations: ' || g.n
 7        || '; deploy keys of deleted repositories: ' || k.n
 8        || '; profile about texts of deleted accounts or organizations: ' || b.n
 9    FROM (SELECT COUNT(*) AS n FROM repo_access a
10          WHERE (a.subject_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = a.subject_id))
11             OR (a.subject_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = a.subject_id))) g,
12         (SELECT COUNT(*) AS n FROM ssh_keys
13          WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
14              NOT IN (SELECT id FROM repos)) k,
15         (SELECT COUNT(*) AS n FROM profile_about_backfill p
16          WHERE (p.owner_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = p.owner_id))
17             OR (p.owner_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = p.owner_id))) b
18    WHERE g.n + k.n + b.n > 0
19    ON CONFLICT (key) DO UPDATE SET value = excluded.value;
20
21DELETE FROM repo_access
22    WHERE (subject_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = repo_access.subject_id))
23       OR (subject_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = repo_access.subject_id));
24
25UPDATE settings SET value = value + 1
26    WHERE key = 'key_epoch' AND EXISTS (SELECT 1 FROM ssh_keys
27        WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
28            NOT IN (SELECT id FROM repos));
29
30DELETE FROM ssh_keys
31    WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
32        NOT IN (SELECT id FROM repos);
33
34DELETE FROM profile_about_backfill
35    WHERE (owner_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = profile_about_backfill.owner_id))
36       OR (owner_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = profile_about_backfill.owner_id));