internal/store/migrations/0074_id_autoincrement.down.sql
175 lines · 7742 bytes
1-- foreign_keys: off
2-- Back to ids without AUTOINCREMENT: the same rebuild with the keyword
3-- dropped, and the tables' sqlite_sequence rows removed.
4PRAGMA legacy_alter_table = ON;
5
6DROP TRIGGER users_owning_repos;
7DROP TRIGGER orgs_owning_repos;
8
9ALTER TABLE users RENAME TO users_old;
10CREATE TABLE users (
11 id INTEGER PRIMARY KEY,
12 username TEXT NOT NULL UNIQUE,
13 is_admin INTEGER NOT NULL DEFAULT 0,
14 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
15 pending INTEGER NOT NULL DEFAULT 0,
16 description TEXT NOT NULL DEFAULT '',
17 website TEXT NOT NULL DEFAULT '',
18 disabled INTEGER NOT NULL DEFAULT 0,
19 links TEXT NOT NULL DEFAULT '',
20 repo_limit INTEGER,
21 byte_limit INTEGER,
22 notify_mail INTEGER NOT NULL DEFAULT 1,
23 notify_watch INTEGER NOT NULL DEFAULT 0,
24 theme TEXT NOT NULL DEFAULT 'system',
25 notify_push INTEGER NOT NULL DEFAULT 1,
26 diff_layout TEXT NOT NULL DEFAULT 'unified',
27 notify_reply INTEGER NOT NULL DEFAULT 0
28);
29INSERT INTO users (id, username, is_admin, created_at, pending, description, website, disabled,
30 links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply)
31 SELECT id, username, is_admin, created_at, pending, description, website, disabled,
32 links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply
33 FROM users_old;
34DROP TABLE users_old;
35
36ALTER TABLE orgs RENAME TO orgs_old;
37CREATE TABLE orgs (
38 id INTEGER PRIMARY KEY,
39 name TEXT NOT NULL UNIQUE,
40 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
41 description TEXT NOT NULL DEFAULT '',
42 website TEXT NOT NULL DEFAULT '',
43 members_role TEXT NOT NULL DEFAULT 'write'
44 CHECK (members_role IN ('write', 'read', 'none')),
45 links TEXT NOT NULL DEFAULT ''
46);
47INSERT INTO orgs (id, name, created_at, description, website, members_role, links)
48 SELECT id, name, created_at, description, website, members_role, links FROM orgs_old;
49DROP TABLE orgs_old;
50
51ALTER TABLE repos RENAME TO repos_old;
52CREATE TABLE repos (
53 id INTEGER PRIMARY KEY,
54 owner_kind TEXT NOT NULL CHECK (owner_kind IN ('user','org')),
55 owner_id INTEGER NOT NULL,
56 name TEXT NOT NULL,
57 visibility TEXT NOT NULL CHECK (visibility IN ('public','private')),
58 default_branch TEXT NOT NULL DEFAULT 'main',
59 fork_of INTEGER REFERENCES repos(id) ON DELETE SET NULL,
60 issue_counter INTEGER NOT NULL DEFAULT 0,
61 mr_counter INTEGER NOT NULL DEFAULT 0,
62 settings_json TEXT NOT NULL DEFAULT '{}',
63 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
64 build_counter INTEGER NOT NULL DEFAULT 0,
65 UNIQUE (owner_kind, owner_id, name)
66);
67INSERT INTO repos (id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
68 issue_counter, mr_counter, settings_json, created_at, build_counter)
69 SELECT id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
70 issue_counter, mr_counter, settings_json, created_at, build_counter
71 FROM repos_old;
72DROP TABLE repos_old;
73
74ALTER TABLE api_tokens RENAME TO api_tokens_old;
75CREATE TABLE api_tokens (
76 id INTEGER PRIMARY KEY,
77 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
78 name TEXT NOT NULL,
79 token_hash TEXT NOT NULL UNIQUE,
80 scope TEXT NOT NULL DEFAULT 'full' CHECK (scope IN ('full','read')),
81 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
82 expires_at TEXT,
83 last_used_at TEXT,
84 created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
85 UNIQUE (user_id, name)
86);
87INSERT INTO api_tokens (id, user_id, name, token_hash, scope, created_at, expires_at,
88 last_used_at, created_by_token)
89 SELECT id, user_id, name, token_hash, scope, created_at, expires_at,
90 last_used_at, created_by_token
91 FROM api_tokens_old;
92DROP TABLE api_tokens_old;
93
94ALTER TABLE ssh_keys RENAME TO ssh_keys_old;
95CREATE TABLE ssh_keys (
96 id INTEGER PRIMARY KEY,
97 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
98 fingerprint TEXT NOT NULL UNIQUE,
99 algo TEXT NOT NULL,
100 blob BLOB NOT NULL,
101 scope TEXT NOT NULL DEFAULT 'full',
102 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
103 last_used_at TEXT,
104 label TEXT NOT NULL DEFAULT '',
105 created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
106 expires_at TEXT
107);
108INSERT INTO ssh_keys (id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
109 label, created_by_token, expires_at)
110 SELECT id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
111 label, created_by_token, expires_at
112 FROM ssh_keys_old;
113DROP TABLE ssh_keys_old;
114CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
115
116ALTER TABLE webhook_deliveries RENAME TO webhook_deliveries_old;
117CREATE TABLE webhook_deliveries (
118 id INTEGER PRIMARY KEY,
119 webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
120 event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
121 attempts INTEGER NOT NULL DEFAULT 0,
122 next_attempt_at TEXT,
123 delivered_at TEXT,
124 failed_at TEXT,
125 last_status INTEGER,
126 last_error TEXT,
127 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
128);
129INSERT INTO webhook_deliveries (id, webhook_id, event_id, attempts, next_attempt_at,
130 delivered_at, failed_at, last_status, last_error, created_at)
131 SELECT id, webhook_id, event_id, attempts, next_attempt_at,
132 delivered_at, failed_at, last_status, last_error, created_at
133 FROM webhook_deliveries_old;
134DROP TABLE webhook_deliveries_old;
135CREATE INDEX webhook_deliveries_due ON webhook_deliveries(next_attempt_at)
136 WHERE delivered_at IS NULL AND failed_at IS NULL;
137
138ALTER TABLE push_queue RENAME TO push_queue_old;
139CREATE TABLE push_queue (
140 id INTEGER PRIMARY KEY,
141 device_id INTEGER NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
142 title TEXT NOT NULL,
143 body TEXT NOT NULL,
144 path TEXT NOT NULL,
145 attempts INTEGER NOT NULL DEFAULT 0,
146 next_attempt_at TEXT,
147 sent_at TEXT,
148 failed_at TEXT,
149 last_error TEXT,
150 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
151);
152INSERT INTO push_queue (id, device_id, title, body, path, attempts, next_attempt_at,
153 sent_at, failed_at, last_error, created_at)
154 SELECT id, device_id, title, body, path, attempts, next_attempt_at,
155 sent_at, failed_at, last_error, created_at
156 FROM push_queue_old;
157DROP TABLE push_queue_old;
158CREATE INDEX push_queue_due ON push_queue(next_attempt_at)
159 WHERE sent_at IS NULL AND failed_at IS NULL;
160
161CREATE TRIGGER users_owning_repos BEFORE DELETE ON users
162WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'user' AND owner_id = OLD.id)
163BEGIN
164 SELECT RAISE(ABORT, 'user still owns repositories');
165END;
166CREATE TRIGGER orgs_owning_repos BEFORE DELETE ON orgs
167WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'org' AND owner_id = OLD.id)
168BEGIN
169 SELECT RAISE(ABORT, 'organization still owns repositories');
170END;
171
172PRAGMA legacy_alter_table = OFF;
173
174DELETE FROM sqlite_sequence WHERE name IN
175 ('users', 'orgs', 'repos', 'api_tokens', 'ssh_keys', 'webhook_deliveries', 'push_queue');