1package patchbin
  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 acl (
 21  id INTEGER PRIMARY KEY AUTOINCREMENT,
 22  pubkey string,
 23  ip_address string,
 24  permission string NOT NULL,
 25  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 26);
 27
 28CREATE TABLE IF NOT EXISTS patch_requests (
 29  id INTEGER PRIMARY KEY AUTOINCREMENT,
 30  user_id INTEGER NOT NULL,
 31  repo_name TEXT NOT NULL DEFAULT '',
 32  name TEXT NOT NULL,
 33  text TEXT NOT NULL,
 34  status TEXT NOT NULL,
 35  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 36  updated_at DATETIME NOT NULL,
 37  last_activity DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 38  CONSTRAINT pr_user_id_fk
 39    FOREIGN KEY(user_id) REFERENCES app_users(id)
 40    ON DELETE CASCADE
 41    ON UPDATE CASCADE
 42);
 43
 44CREATE TABLE IF NOT EXISTS patchsets (
 45  id INTEGER PRIMARY KEY AUTOINCREMENT,
 46  user_id INTEGER NOT NULL,
 47  patch_request_id INTEGER NOT NULL,
 48  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 49  CONSTRAINT patchset_user_id_fk
 50    FOREIGN KEY(user_id) REFERENCES app_users(id)
 51    ON DELETE CASCADE
 52    ON UPDATE CASCADE,
 53  CONSTRAINT patchset_patch_request_id_fk
 54    FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
 55    ON DELETE CASCADE
 56    ON UPDATE CASCADE
 57);
 58
 59CREATE TABLE IF NOT EXISTS patches (
 60	id INTEGER PRIMARY KEY AUTOINCREMENT,
 61	user_id INTEGER NOT NULL,
 62	patchset_id INTEGER NOT NULL,
 63	author_name TEXT NOT NULL,
 64	author_email TEXT NOT NULL,
 65	author_date DATETIME NOT NULL,
 66	title TEXT NOT NULL,
 67	body TEXT NOT NULL,
 68	body_appendix TEXT NOT NULL,
 69	commit_sha TEXT NOT NULL,
 70	content_sha TEXT NOT NULL,
 71	raw_text TEXT NOT NULL,
 72	base_commit_sha TEXT,
 73	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 74	CONSTRAINT patches_user_id_fk
 75		FOREIGN KEY(user_id) REFERENCES app_users(id)
 76		ON DELETE CASCADE
 77		ON UPDATE CASCADE,
 78	CONSTRAINT patches_patchset_id_fk
 79		FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
 80		ON DELETE CASCADE
 81		ON UPDATE CASCADE
 82);
 83
 84CREATE TABLE IF NOT EXISTS event_logs (
 85	id INTEGER PRIMARY KEY AUTOINCREMENT,
 86	user_id INTEGER NOT NULL,
 87	patch_request_id INTEGER,
 88	patchset_id INTEGER,
 89	event TEXT NOT NULL,
 90	data TEXT,
 91	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 92	CONSTRAINT event_logs_pr_id_fk
 93		FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
 94		ON DELETE CASCADE
 95		ON UPDATE CASCADE,
 96	CONSTRAINT event_logs_patchset_id_fk
 97		FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
 98		ON DELETE CASCADE
 99		ON UPDATE CASCADE,
100	CONSTRAINT event_logs_user_id_fk
101		FOREIGN KEY(user_id) REFERENCES app_users(id)
102		ON DELETE CASCADE
103		ON UPDATE CASCADE
104);
105
106CREATE INDEX IF NOT EXISTS idx_patch_requests_last_activity ON patch_requests(last_activity);
107`
108
109var sqliteMigrations = []string{
110	"", // migration #0 is reserved for schema initialization
111	"ALTER TABLE patches ADD COLUMN base_commit_sha TEXT",
112	// added this by accident
113	"",
114	// create repos table
115	`CREATE TABLE IF NOT EXISTS repos (
116		id INTEGER PRIMARY KEY AUTOINCREMENT,
117		user_id INTEGER NOT NULL,
118		name TEXT NOT NULL,
119		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
120		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
121		UNIQUE (user_id, name),
122		CONSTRAINT repo_user_id_fk
123			FOREIGN KEY(user_id) REFERENCES app_users(id)
124			ON DELETE CASCADE
125			ON UPDATE CASCADE
126	);`,
127	// migrate existing repo info from patch_requests
128	`INSERT INTO repos (user_id, name) SELECT user_id, repo_id from patch_requests group by repo_id;`,
129	// convert patch_requests.repo_id to integer with FK constraint
130	`CREATE TABLE IF NOT EXISTS tmp_patch_requests (
131		id INTEGER PRIMARY KEY AUTOINCREMENT,
132		user_id INTEGER NOT NULL,
133		repo_id INTEGER NOT NULL,
134		name TEXT NOT NULL,
135		text TEXT NOT NULL,
136		status TEXT NOT NULL,
137		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
138		updated_at DATETIME NOT NULL,
139		CONSTRAINT pr_user_id_fk
140			FOREIGN KEY(user_id) REFERENCES app_users(id)
141			ON DELETE CASCADE
142			ON UPDATE CASCADE,
143		CONSTRAINT pr_repo_id_fk
144			FOREIGN KEY(repo_id) REFERENCES repos(id)
145			ON DELETE CASCADE
146			ON UPDATE CASCADE
147	);
148	INSERT INTO tmp_patch_requests (user_id, repo_id, name, text, status, created_at, updated_at)
149		SELECT pr.user_id, repos.id, pr.name, pr.text, pr.status, pr.created_at, pr.updated_at
150		FROM patch_requests AS pr
151		INNER JOIN repos ON repos.name = pr.repo_id;
152	DROP TABLE patch_requests;
153	ALTER TABLE tmp_patch_requests RENAME TO patch_requests;`,
154	// convert event_logs.repo_id to integer with FK constraint
155	`CREATE TABLE IF NOT EXISTS tmp_event_logs (
156		id INTEGER PRIMARY KEY AUTOINCREMENT,
157		user_id INTEGER NOT NULL,
158		repo_id INTEGER,
159		patch_request_id INTEGER,
160		patchset_id INTEGER,
161		event TEXT NOT NULL,
162		data TEXT,
163		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
164		CONSTRAINT event_logs_pr_id_fk
165			FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
166			ON DELETE CASCADE
167			ON UPDATE CASCADE,
168		CONSTRAINT event_logs_patchset_id_fk
169			FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
170			ON DELETE CASCADE
171			ON UPDATE CASCADE,
172		CONSTRAINT event_logs_user_id_fk
173			FOREIGN KEY(user_id) REFERENCES app_users(id)
174			ON DELETE CASCADE
175			ON UPDATE CASCADE
176		CONSTRAINT event_logs_repo_id_fk
177			FOREIGN KEY(repo_id) REFERENCES repos(id)
178			ON DELETE CASCADE
179			ON UPDATE CASCADE
180	);
181	INSERT INTO tmp_event_logs (user_id, repo_id, patch_request_id, patchset_id, event, data, created_at)
182		SELECT ev.user_id, repos.id, ev.patch_request_id, ev.patchset_id, ev.event, ev.data, ev.created_at
183		FROM event_logs AS ev
184		LEFT JOIN repos ON repos.name = ev.repo_id;
185	DROP TABLE event_logs;
186	ALTER TABLE tmp_event_logs RENAME TO event_logs;`,
187	// Phase 1: Add repo_name column to patch_requests
188	`ALTER TABLE patch_requests ADD COLUMN repo_name TEXT`,
189	// Phase 1: Populate repo_name from existing repos table
190	`UPDATE patch_requests SET repo_name = (SELECT name FROM repos WHERE id = patch_requests.repo_id)`,
191	// Phase 1: Remove patch_requests whose repo no longer exists. These are
192	// orphans from repo deletion, since ON DELETE CASCADE never fired
193	// because PRAGMA foreign_keys was never enabled.
194	`DELETE FROM event_logs WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE repo_name IS NULL);
195	DELETE FROM patches WHERE patchset_id IN (SELECT id FROM patchsets WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE repo_name IS NULL));
196	DELETE FROM patchsets WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE repo_name IS NULL);
197	DELETE FROM patch_requests WHERE repo_name IS NULL;`,
198	// Phase 1: Add last_activity column to patch_requests
199	`ALTER TABLE patch_requests ADD COLUMN last_activity DATETIME`,
200	// Phase 1: Set initial last_activity values from event_logs
201	`UPDATE patch_requests SET last_activity = (SELECT MAX(created_at) FROM event_logs WHERE patch_request_id = patch_requests.id) WHERE last_activity IS NULL`,
202	// Phase 1: Set last_activity to created_at for PRs with no events
203	`UPDATE patch_requests SET last_activity = created_at WHERE last_activity IS NULL`,
204	// Phase 1: Create index on last_activity for fast filtering
205	`CREATE INDEX IF NOT EXISTS idx_patch_requests_last_activity ON patch_requests(last_activity)`,
206	// Phase 2: Drop repos table (no longer needed, repo_name is stored directly)
207	`DROP TABLE IF EXISTS repos`,
208	// Phase 2: Rebuild patch_requests without repo_id (SQLite can't drop a
209	// column that's part of a foreign key constraint via ALTER TABLE).
210	`CREATE TABLE tmp_patch_requests_v2 (
211		id INTEGER PRIMARY KEY AUTOINCREMENT,
212		user_id INTEGER NOT NULL,
213		repo_name TEXT NOT NULL DEFAULT '',
214		name TEXT NOT NULL,
215		text TEXT NOT NULL,
216		status TEXT NOT NULL,
217		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
218		updated_at DATETIME NOT NULL,
219		last_activity DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
220		CONSTRAINT pr_user_id_fk
221			FOREIGN KEY(user_id) REFERENCES app_users(id)
222			ON DELETE CASCADE
223			ON UPDATE CASCADE
224	);
225	INSERT INTO tmp_patch_requests_v2 (id, user_id, repo_name, name, text, status, created_at, updated_at, last_activity)
226		SELECT id, user_id, repo_name, name, text, status, created_at, updated_at, last_activity
227		FROM patch_requests;
228	DROP TABLE patch_requests;
229	ALTER TABLE tmp_patch_requests_v2 RENAME TO patch_requests;
230	CREATE INDEX IF NOT EXISTS idx_patch_requests_last_activity ON patch_requests(last_activity);`,
231	// Phase 2: Rebuild event_logs without repo_id, same reasoning as above.
232	`CREATE TABLE tmp_event_logs_v2 (
233		id INTEGER PRIMARY KEY AUTOINCREMENT,
234		user_id INTEGER NOT NULL,
235		patch_request_id INTEGER,
236		patchset_id INTEGER,
237		event TEXT NOT NULL,
238		data TEXT,
239		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
240		CONSTRAINT event_logs_pr_id_fk
241			FOREIGN KEY(patch_request_id) REFERENCES patch_requests(id)
242			ON DELETE CASCADE
243			ON UPDATE CASCADE,
244		CONSTRAINT event_logs_patchset_id_fk
245			FOREIGN KEY(patchset_id) REFERENCES patchsets(id)
246			ON DELETE CASCADE
247			ON UPDATE CASCADE,
248		CONSTRAINT event_logs_user_id_fk
249			FOREIGN KEY(user_id) REFERENCES app_users(id)
250			ON DELETE CASCADE
251			ON UPDATE CASCADE
252	);
253	INSERT INTO tmp_event_logs_v2 (id, user_id, patch_request_id, patchset_id, event, data, created_at)
254		SELECT id, user_id, patch_request_id, patchset_id, event, data, created_at
255		FROM event_logs;
256	DROP TABLE event_logs;
257	ALTER TABLE tmp_event_logs_v2 RENAME TO event_logs;`,
258	// Phase 2: Collapse legacy statuses (closed, accepted, reviewed) into
259	// open, since the new model only has draft and open.
260	`UPDATE patch_requests SET status = 'open' WHERE status NOT IN ('draft', 'open')`,
261	// Delete patch requests with an empty title. These come from patchsets
262	// whose first patch had no subject line and are unusable in the UI.
263	`DELETE FROM event_logs WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE trim(name) = '');
264	DELETE FROM patches WHERE patchset_id IN (SELECT id FROM patchsets WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE trim(name) = ''));
265	DELETE FROM patchsets WHERE patch_request_id IN (SELECT id FROM patch_requests WHERE trim(name) = '');
266	DELETE FROM patch_requests WHERE trim(name) = '';`,
267}
268
269// Open opens a database connection.
270func SqliteOpen(dsn string, logger *slog.Logger) (*sqlx.DB, error) {
271	logger.Info("opening db file", "dsn", dsn)
272	db, err := sqlx.Connect("sqlite", dsn)
273	if err != nil {
274		return nil, err
275	}
276
277	err = sqliteUpgrade(db)
278	if err != nil {
279		_ = db.Close()
280		return nil, err
281	}
282
283	return db, nil
284}
285
286func sqliteUpgrade(db *sqlx.DB) error {
287	var version int
288	if err := db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
289		return fmt.Errorf("failed to query schema version: %v", err)
290	}
291
292	if version == len(sqliteMigrations) {
293		return nil
294	} else if version > len(sqliteMigrations) {
295		return fmt.Errorf("patchbin (version %d) older than schema (version %d)", len(sqliteMigrations), version)
296	}
297
298	tx, err := db.Beginx()
299	if err != nil {
300		return err
301	}
302	defer func() {
303		_ = tx.Rollback()
304	}()
305
306	if version == 0 {
307		if _, err := tx.Exec(sqliteSchema); err != nil {
308			return fmt.Errorf("failed to initialize schema: %v", err)
309		}
310	} else {
311		for i := version; i < len(sqliteMigrations); i++ {
312			if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
313				return fmt.Errorf("failed to execute migration #%v: %v", i, err)
314			}
315		}
316	}
317
318	// For some reason prepared statements don't work here
319	_, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
320	if err != nil {
321		return fmt.Errorf("failed to bump schema version: %v", err)
322	}
323
324	return tx.Commit()
325}