Eric Bower
·
2024-11-12
sqlite.go
1package git
2
3import (
4 "fmt"
5 "log/slog"
6
7 "github.com/jmoiron/sqlx"
8 _ "modernc.org/sqlite"
9)
10
11var sqliteSchema = `
12CREATE TABLE IF NOT EXISTS app_users (
13 id INTEGER PRIMARY KEY AUTOINCREMENT,
14 pubkey TEXT NOT NULL UNIQUE,
15 name TEXT NOT NULL UNIQUE,
16 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
17 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
18);
19
20CREATE TABLE IF NOT EXISTS repos (
21 id INTEGER PRIMARY KEY AUTOINCREMENT,
22 user_id INTEGER NOT NULL,
23 name TEXT NOT NULL,
24 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
25 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
26 UNIQUE (user_id, name),
27 CONSTRAINT repo_user_id_fk
28 FOREIGN KEY(user_id) REFERENCES app_users(id)
29 ON DELETE CASCADE
30 ON UPDATE CASCADE
31);
32
33CREATE TABLE IF NOT EXISTS acl (
34 id INTEGER PRIMARY KEY AUTOINCREMENT,
35 pubkey string,
36 ip_address string,
37 permission string NOT NULL,
38 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
39);
40
41CREATE TABLE IF NOT EXISTS patch_requests (
42 id INTEGER PRIMARY KEY AUTOINCREMENT,
43 user_id INTEGER NOT NULL,
44 repo_id TEXT NOT NULL,
45 name TEXT NOT NULL,
46 text TEXT NOT NULL,
47 status TEXT NOT NULL,
48 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
49 updated_at DATETIME NOT NULL,
50 CONSTRAINT pr_user_id_fk
51 FOREIGN KEY(user_id) REFERENCES app_users(id)
52 ON DELETE CASCADE
53 ON UPDATE CASCADE
54);
55
56CREATE TABLE IF NOT EXISTS patchsets (
57 id INTEGER PRIMARY KEY AUTOINCREMENT,
58 user_id INTEGER NOT NULL,
59 patch_request_id INTEGER NOT NULL,
60 review BOOLEAN NOT NULL DEFAULT false,
61 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
62 CONSTRAINT patchset_user_id_fk
63 FOREIGN KEY(user_id) REFERENCES app_users(id)
64 ON DELETE CASCADE
65 ON UPDATE CASCADE,
66 CONSTRAINT patchset_patch_request_id_fk
67 FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
68 ON DELETE CASCADE
69 ON UPDATE CASCADE
70);
71
72CREATE TABLE IF NOT EXISTS patches (
73 id INTEGER PRIMARY KEY AUTOINCREMENT,
74 user_id INTEGER NOT NULL,
75 patchset_id INTEGER NOT NULL,
76 author_name TEXT NOT NULL,
77 author_email TEXT NOT NULL,
78 author_date DATETIME NOT NULL,
79 title TEXT NOT NULL,
80 body TEXT NOT NULL,
81 body_appendix TEXT NOT NULL,
82 commit_sha TEXT NOT NULL,
83 content_sha TEXT NOT NULL,
84 raw_text TEXT NOT NULL,
85 base_commit_sha TEXT,
86 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
87 CONSTRAINT patches_user_id_fk
88 FOREIGN KEY(user_id) REFERENCES app_users(id)
89 ON DELETE CASCADE
90 ON UPDATE CASCADE,
91 CONSTRAINT patches_patchset_id_fk
92 FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
93 ON DELETE CASCADE
94 ON UPDATE CASCADE
95);
96
97CREATE TABLE IF NOT EXISTS event_logs (
98 id INTEGER PRIMARY KEY AUTOINCREMENT,
99 user_id INTEGER NOT NULL,
100 repo_id TEXT,
101 patch_request_id INTEGER,
102 patchset_id INTEGER,
103 event TEXT NOT NULL,
104 data TEXT,
105 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
106 CONSTRAINT event_logs_pr_id_fk
107 FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
108 ON DELETE CASCADE
109 ON UPDATE CASCADE,
110 CONSTRAINT event_logs_patchset_id_fk
111 FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
112 ON DELETE CASCADE
113 ON UPDATE CASCADE,
114 CONSTRAINT event_logs_user_id_fk
115 FOREIGN KEY(user_id) REFERENCES app_users(id)
116 ON DELETE CASCADE
117 ON UPDATE CASCADE
118);
119`
120
121var sqliteMigrations = []string{
122 "", // migration #0 is reserved for schema initialization
123 "ALTER TABLE patches ADD COLUMN base_commit_sha TEXT",
124 // added this by accident
125 "",
126 // create repos table
127 `CREATE TABLE IF NOT EXISTS repos (
128 id INTEGER PRIMARY KEY AUTOINCREMENT,
129 user_id INTEGER NOT NULL,
130 name TEXT NOT NULL,
131 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
132 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
133 UNIQUE (user_id, name),
134 CONSTRAINT repo_user_id_fk
135 FOREIGN KEY(user_id) REFERENCES app_users(id)
136 ON DELETE CASCADE
137 ON UPDATE CASCADE
138 );`,
139 // migrate existing repo info from patch_requests
140 `INSERT INTO repos (user_id, name) SELECT user_id, repo_id from patch_requests group by repo_id;`,
141 // convert patch_requests.repo_id to integer with FK constraint
142 `CREATE TABLE IF NOT EXISTS tmp_patch_requests (
143 id INTEGER PRIMARY KEY AUTOINCREMENT,
144 user_id INTEGER NOT NULL,
145 repo_id INTEGER NOT NULL,
146 name TEXT NOT NULL,
147 text TEXT NOT NULL,
148 status TEXT NOT NULL,
149 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
150 updated_at DATETIME NOT NULL,
151 CONSTRAINT pr_user_id_fk
152 FOREIGN KEY(user_id) REFERENCES app_users(id)
153 ON DELETE CASCADE
154 ON UPDATE CASCADE,
155 CONSTRAINT pr_repo_id_fk
156 FOREIGN KEY(repo_id) REFERENCES repos(id)
157 ON DELETE CASCADE
158 ON UPDATE CASCADE
159 );
160 INSERT INTO tmp_patch_requests (user_id, repo_id, name, text, status, created_at, updated_at)
161 SELECT pr.user_id, repos.id, pr.name, pr.text, pr.status, pr.created_at, pr.updated_at
162 FROM patch_requests AS pr
163 INNER JOIN repos ON repos.name = pr.repo_id;
164 DROP TABLE patch_requests;
165 ALTER TABLE tmp_patch_requests RENAME TO patch_requests;`,
166 // convert event_logs.repo_id to integer with FK constraint
167 `CREATE TABLE IF NOT EXISTS tmp_event_logs (
168 id INTEGER PRIMARY KEY AUTOINCREMENT,
169 user_id INTEGER NOT NULL,
170 repo_id INTEGER,
171 patch_request_id INTEGER,
172 patchset_id INTEGER,
173 event TEXT NOT NULL,
174 data TEXT,
175 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
176 CONSTRAINT event_logs_pr_id_fk
177 FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
178 ON DELETE CASCADE
179 ON UPDATE CASCADE,
180 CONSTRAINT event_logs_patchset_id_fk
181 FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
182 ON DELETE CASCADE
183 ON UPDATE CASCADE,
184 CONSTRAINT event_logs_user_id_fk
185 FOREIGN KEY(user_id) REFERENCES app_users(id)
186 ON DELETE CASCADE
187 ON UPDATE CASCADE
188 CONSTRAINT event_logs_repo_id_fk
189 FOREIGN KEY(repo_id) REFERENCES repos(id)
190 ON DELETE CASCADE
191 ON UPDATE CASCADE
192 );
193 INSERT INTO tmp_event_logs (user_id, repo_id, patch_request_id, patchset_id, event, data, created_at)
194 SELECT ev.user_id, repos.id, ev.patch_request_id, ev.patchset_id, ev.event, ev.data, ev.created_at
195 FROM event_logs AS ev
196 LEFT JOIN repos ON repos.name = ev.repo_id;
197 DROP TABLE event_logs;
198 ALTER TABLE tmp_event_logs RENAME TO event_logs;`,
199}
200
201// Open opens a database connection.
202func SqliteOpen(dsn string, logger *slog.Logger) (*sqlx.DB, error) {
203 logger.Info("opening db file", "dsn", dsn)
204 db, err := sqlx.Connect("sqlite", dsn)
205 if err != nil {
206 return nil, err
207 }
208
209 err = sqliteUpgrade(db)
210 if err != nil {
211 db.Close()
212 return nil, err
213 }
214
215 return db, nil
216}
217
218func sqliteUpgrade(db *sqlx.DB) error {
219 var version int
220 if err := db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
221 return fmt.Errorf("failed to query schema version: %v", err)
222 }
223
224 if version == len(sqliteMigrations) {
225 return nil
226 } else if version > len(sqliteMigrations) {
227 return fmt.Errorf("git-pr (version %d) older than schema (version %d)", len(sqliteMigrations), version)
228 }
229
230 tx, err := db.Beginx()
231 if err != nil {
232 return err
233 }
234 defer func() {
235 _ = tx.Rollback()
236 }()
237
238 if version == 0 {
239 if _, err := tx.Exec(sqliteSchema); err != nil {
240 return fmt.Errorf("failed to initialize schema: %v", err)
241 }
242 } else {
243 for i := version; i < len(sqliteMigrations); i++ {
244 if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
245 return fmt.Errorf("failed to execute migration #%v: %v", i, err)
246 }
247 }
248 }
249
250 // For some reason prepared statements don't work here
251 _, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
252 if err != nil {
253 return fmt.Errorf("failed to bump schema version: %v", err)
254 }
255
256 return tx.Commit()
257}