internal/store/migrations/0074_id_autoincrement.up.sql
211 lines · 10001 bytes
1-- foreign_keys: off
2-- Ids that are named after their row is gone are never handed out again
3-- (#306). Without AUTOINCREMENT SQLite gives a new row MAX(id)+1, so
4-- deleting the newest account, organization, repository, key or token
5-- let the next one take its id, and with it whatever still named that
6-- id: a deploy key's scope, a repo_access grant, a signed LFS or
7-- reply-by-mail token, a hook's environment. webhook_deliveries and
8-- push_queue rows are named by id by a sender that is mid-request when
9-- a cascade can remove them.
10--
11-- Each table is rebuilt the way 0052 rebuilds labels: foreign keys off
12-- for the step, legacy_alter_table so the children keep naming the
13-- table through the rename and bind to the new one, and the runner's
14-- foreign_key_check before commit. Rows keep their ids; indexes and
15-- triggers are recreated. sqlite_sequence starts at the highest id in
16-- the table or named anywhere else, so an id already freed and still
17-- named (a deploy key for a deleted repository, a grant, an audit row or
18-- a parked profile about text for a deleted owner) is not handed out
19-- either.
20PRAGMA legacy_alter_table = ON;
21
22DROP TRIGGER users_owning_repos;
23DROP TRIGGER orgs_owning_repos;
24
25ALTER TABLE users RENAME TO users_old;
26CREATE TABLE users (
27 id INTEGER PRIMARY KEY AUTOINCREMENT,
28 username TEXT NOT NULL UNIQUE,
29 is_admin INTEGER NOT NULL DEFAULT 0,
30 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
31 pending INTEGER NOT NULL DEFAULT 0,
32 description TEXT NOT NULL DEFAULT '',
33 website TEXT NOT NULL DEFAULT '',
34 disabled INTEGER NOT NULL DEFAULT 0,
35 links TEXT NOT NULL DEFAULT '',
36 repo_limit INTEGER,
37 byte_limit INTEGER,
38 notify_mail INTEGER NOT NULL DEFAULT 1,
39 notify_watch INTEGER NOT NULL DEFAULT 0,
40 theme TEXT NOT NULL DEFAULT 'system',
41 notify_push INTEGER NOT NULL DEFAULT 1,
42 diff_layout TEXT NOT NULL DEFAULT 'unified',
43 notify_reply INTEGER NOT NULL DEFAULT 0
44);
45INSERT INTO users (id, username, is_admin, created_at, pending, description, website, disabled,
46 links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply)
47 SELECT id, username, is_admin, created_at, pending, description, website, disabled,
48 links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply
49 FROM users_old;
50DROP TABLE users_old;
51
52ALTER TABLE orgs RENAME TO orgs_old;
53CREATE TABLE orgs (
54 id INTEGER PRIMARY KEY AUTOINCREMENT,
55 name TEXT NOT NULL UNIQUE,
56 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
57 description TEXT NOT NULL DEFAULT '',
58 website TEXT NOT NULL DEFAULT '',
59 members_role TEXT NOT NULL DEFAULT 'write'
60 CHECK (members_role IN ('write', 'read', 'none')),
61 links TEXT NOT NULL DEFAULT ''
62);
63INSERT INTO orgs (id, name, created_at, description, website, members_role, links)
64 SELECT id, name, created_at, description, website, members_role, links FROM orgs_old;
65DROP TABLE orgs_old;
66
67ALTER TABLE repos RENAME TO repos_old;
68CREATE TABLE repos (
69 id INTEGER PRIMARY KEY AUTOINCREMENT,
70 owner_kind TEXT NOT NULL CHECK (owner_kind IN ('user','org')),
71 owner_id INTEGER NOT NULL,
72 name TEXT NOT NULL,
73 visibility TEXT NOT NULL CHECK (visibility IN ('public','private')),
74 default_branch TEXT NOT NULL DEFAULT 'main',
75 fork_of INTEGER REFERENCES repos(id) ON DELETE SET NULL,
76 issue_counter INTEGER NOT NULL DEFAULT 0,
77 mr_counter INTEGER NOT NULL DEFAULT 0,
78 settings_json TEXT NOT NULL DEFAULT '{}',
79 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
80 build_counter INTEGER NOT NULL DEFAULT 0,
81 UNIQUE (owner_kind, owner_id, name)
82);
83INSERT INTO repos (id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
84 issue_counter, mr_counter, settings_json, created_at, build_counter)
85 SELECT id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
86 issue_counter, mr_counter, settings_json, created_at, build_counter
87 FROM repos_old;
88DROP TABLE repos_old;
89
90ALTER TABLE api_tokens RENAME TO api_tokens_old;
91CREATE TABLE api_tokens (
92 id INTEGER PRIMARY KEY AUTOINCREMENT,
93 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
94 name TEXT NOT NULL,
95 token_hash TEXT NOT NULL UNIQUE,
96 scope TEXT NOT NULL DEFAULT 'full' CHECK (scope IN ('full','read')),
97 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
98 expires_at TEXT,
99 last_used_at TEXT,
100 created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
101 UNIQUE (user_id, name)
102);
103INSERT INTO api_tokens (id, user_id, name, token_hash, scope, created_at, expires_at,
104 last_used_at, created_by_token)
105 SELECT id, user_id, name, token_hash, scope, created_at, expires_at,
106 last_used_at, created_by_token
107 FROM api_tokens_old;
108DROP TABLE api_tokens_old;
109
110ALTER TABLE ssh_keys RENAME TO ssh_keys_old;
111CREATE TABLE ssh_keys (
112 id INTEGER PRIMARY KEY AUTOINCREMENT,
113 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
114 fingerprint TEXT NOT NULL UNIQUE,
115 algo TEXT NOT NULL,
116 blob BLOB NOT NULL,
117 scope TEXT NOT NULL DEFAULT 'full',
118 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
119 last_used_at TEXT,
120 label TEXT NOT NULL DEFAULT '',
121 created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
122 expires_at TEXT
123);
124INSERT INTO ssh_keys (id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
125 label, created_by_token, expires_at)
126 SELECT id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
127 label, created_by_token, expires_at
128 FROM ssh_keys_old;
129DROP TABLE ssh_keys_old;
130CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
131
132ALTER TABLE webhook_deliveries RENAME TO webhook_deliveries_old;
133CREATE TABLE webhook_deliveries (
134 id INTEGER PRIMARY KEY AUTOINCREMENT,
135 webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
136 event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
137 attempts INTEGER NOT NULL DEFAULT 0,
138 next_attempt_at TEXT,
139 delivered_at TEXT,
140 failed_at TEXT,
141 last_status INTEGER,
142 last_error TEXT,
143 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
144);
145INSERT INTO webhook_deliveries (id, webhook_id, event_id, attempts, next_attempt_at,
146 delivered_at, failed_at, last_status, last_error, created_at)
147 SELECT id, webhook_id, event_id, attempts, next_attempt_at,
148 delivered_at, failed_at, last_status, last_error, created_at
149 FROM webhook_deliveries_old;
150DROP TABLE webhook_deliveries_old;
151CREATE INDEX webhook_deliveries_due ON webhook_deliveries(next_attempt_at)
152 WHERE delivered_at IS NULL AND failed_at IS NULL;
153
154ALTER TABLE push_queue RENAME TO push_queue_old;
155CREATE TABLE push_queue (
156 id INTEGER PRIMARY KEY AUTOINCREMENT,
157 device_id INTEGER NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
158 title TEXT NOT NULL,
159 body TEXT NOT NULL,
160 path TEXT NOT NULL,
161 attempts INTEGER NOT NULL DEFAULT 0,
162 next_attempt_at TEXT,
163 sent_at TEXT,
164 failed_at TEXT,
165 last_error TEXT,
166 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
167);
168INSERT INTO push_queue (id, device_id, title, body, path, attempts, next_attempt_at,
169 sent_at, failed_at, last_error, created_at)
170 SELECT id, device_id, title, body, path, attempts, next_attempt_at,
171 sent_at, failed_at, last_error, created_at
172 FROM push_queue_old;
173DROP TABLE push_queue_old;
174CREATE INDEX push_queue_due ON push_queue(next_attempt_at)
175 WHERE sent_at IS NULL AND failed_at IS NULL;
176
177CREATE TRIGGER users_owning_repos BEFORE DELETE ON users
178WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'user' AND owner_id = OLD.id)
179BEGIN
180 SELECT RAISE(ABORT, 'user still owns repositories');
181END;
182CREATE TRIGGER orgs_owning_repos BEFORE DELETE ON orgs
183WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'org' AND owner_id = OLD.id)
184BEGIN
185 SELECT RAISE(ABORT, 'organization still owns repositories');
186END;
187
188PRAGMA legacy_alter_table = OFF;
189
190-- The copies above set each sequence to the table's own highest id; an
191-- empty table has no row yet.
192INSERT INTO sqlite_sequence (name, seq)
193 SELECT t.name, 0 FROM (SELECT 'users' AS name UNION ALL SELECT 'orgs' UNION ALL SELECT 'repos'
194 UNION ALL SELECT 'api_tokens' UNION ALL SELECT 'ssh_keys'
195 UNION ALL SELECT 'webhook_deliveries' UNION ALL SELECT 'push_queue') t
196 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence s WHERE s.name = t.name);
197
198UPDATE sqlite_sequence SET seq = MAX(seq,
199 (SELECT COALESCE(MAX(subject_id), 0) FROM repo_access WHERE subject_kind = 'user'),
200 (SELECT COALESCE(MAX(actor_ref), 0) FROM audit_log),
201 (SELECT COALESCE(MAX(user_id), 0) FROM page_domains),
202 (SELECT COALESCE(MAX(owner_id), 0) FROM profile_about_backfill WHERE owner_kind = 'user'))
203 WHERE name = 'users';
204UPDATE sqlite_sequence SET seq = MAX(seq,
205 (SELECT COALESCE(MAX(subject_id), 0) FROM repo_access WHERE subject_kind = 'org'),
206 (SELECT COALESCE(MAX(owner_id), 0) FROM profile_about_backfill WHERE owner_kind = 'org'))
207 WHERE name = 'orgs';
208UPDATE sqlite_sequence SET seq = MAX(seq,
209 (SELECT COALESCE(MAX(CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)), 0)
210 FROM ssh_keys WHERE scope LIKE 'deploy:%'))
211 WHERE name = 'repos';