24.7 KB663 lines
Blame
1import Database from "better-sqlite3";
2import { mkdirSync } from "fs";
3import { dirname } from "path";
4
5export function initDatabase(dbPath: string): Database.Database {
6 mkdirSync(dirname(dbPath), { recursive: true });
7 const db = new Database(dbPath);
8
9 // Enable WAL mode for better concurrent access
10 db.pragma("journal_mode = WAL");
11 db.pragma("foreign_keys = ON");
12
13 // Run migrations
14 migrate(db);
15
16 return db;
17}
18
19function migrate(db: Database.Database) {
20 db.exec(`
21 CREATE TABLE IF NOT EXISTS users (
22 id INTEGER PRIMARY KEY,
23 username TEXT UNIQUE NOT NULL,
24 display_name TEXT,
25 created_at TEXT DEFAULT (datetime('now')),
26 updated_at TEXT DEFAULT (datetime('now'))
27 );
28
29 CREATE TABLE IF NOT EXISTS repos (
30 id INTEGER PRIMARY KEY AUTOINCREMENT,
31 owner_id INTEGER NOT NULL REFERENCES users(id),
32 name TEXT NOT NULL,
33 description TEXT,
34 default_branch TEXT DEFAULT 'main',
35 is_private INTEGER DEFAULT 0,
36 mononoke_repo_id INTEGER,
37 created_at TEXT DEFAULT (datetime('now')),
38 updated_at TEXT DEFAULT (datetime('now')),
39 UNIQUE(owner_id, name)
40 );
41
42 CREATE TABLE IF NOT EXISTS diffs (
43 id INTEGER PRIMARY KEY AUTOINCREMENT,
44 repo_id INTEGER NOT NULL REFERENCES repos(id),
45 number INTEGER NOT NULL,
46 title TEXT NOT NULL,
47 description TEXT,
48 author_id INTEGER NOT NULL REFERENCES users(id),
49 head_commit TEXT NOT NULL DEFAULT '',
50 base_commit TEXT,
51 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
52 created_at TEXT DEFAULT (datetime('now')),
53 updated_at TEXT DEFAULT (datetime('now')),
54 landed_at TEXT,
55 landed_by INTEGER REFERENCES users(id),
56 UNIQUE(repo_id, number)
57 );
58
59 CREATE TABLE IF NOT EXISTS reviews (
60 id INTEGER PRIMARY KEY AUTOINCREMENT,
61 diff_id INTEGER NOT NULL REFERENCES diffs(id),
62 reviewer_id INTEGER NOT NULL REFERENCES users(id),
63 status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'changes_requested')),
64 body TEXT,
65 created_at TEXT DEFAULT (datetime('now')),
66 updated_at TEXT DEFAULT (datetime('now'))
67 );
68
69 CREATE TABLE IF NOT EXISTS comments (
70 id INTEGER PRIMARY KEY AUTOINCREMENT,
71 diff_id INTEGER NOT NULL REFERENCES diffs(id),
72 author_id INTEGER NOT NULL REFERENCES users(id),
73 body TEXT NOT NULL,
74 file_path TEXT,
75 line_number INTEGER,
76 side TEXT CHECK(side IN ('left', 'right')),
77 commit_sha TEXT,
78 parent_id INTEGER REFERENCES comments(id),
79 created_at TEXT DEFAULT (datetime('now')),
80 updated_at TEXT DEFAULT (datetime('now'))
81 );
82
83 CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_type, owner_id);
84 CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id);
85 CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id);
86
87 -- Canopy CI/CD
88
89 CREATE TABLE IF NOT EXISTS pipelines (
90 id INTEGER PRIMARY KEY AUTOINCREMENT,
91 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
92 name TEXT NOT NULL,
93 file TEXT NOT NULL,
94 created_at TEXT DEFAULT (datetime('now')),
95 UNIQUE(repo_id, name)
96 );
97
98 CREATE TABLE IF NOT EXISTS pipeline_runs (
99 id INTEGER PRIMARY KEY AUTOINCREMENT,
100 repo_id INTEGER NOT NULL REFERENCES repos(id),
101 pipeline_name TEXT NOT NULL,
102 pipeline_file TEXT NOT NULL,
103 trigger_type TEXT NOT NULL DEFAULT 'push'
104 CHECK(trigger_type IN ('push', 'diff', 'manual', 'schedule')),
105 trigger_ref TEXT,
106 commit_id TEXT,
107 commit_message TEXT,
108 status TEXT NOT NULL DEFAULT 'pending'
109 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'cancelled')),
110 started_at TEXT,
111 finished_at TEXT,
112 duration_ms INTEGER,
113 created_at TEXT DEFAULT (datetime('now'))
114 );
115
116 CREATE TABLE IF NOT EXISTS pipeline_steps (
117 id INTEGER PRIMARY KEY AUTOINCREMENT,
118 run_id INTEGER NOT NULL REFERENCES pipeline_runs(id) ON DELETE CASCADE,
119 step_index INTEGER NOT NULL,
120 name TEXT NOT NULL,
121 image TEXT NOT NULL,
122 status TEXT NOT NULL DEFAULT 'pending'
123 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'skipped')),
124 exit_code INTEGER,
125 started_at TEXT,
126 finished_at TEXT,
127 duration_ms INTEGER
128 );
129
130 CREATE TABLE IF NOT EXISTS step_logs (
131 id INTEGER PRIMARY KEY AUTOINCREMENT,
132 step_id INTEGER NOT NULL REFERENCES pipeline_steps(id) ON DELETE CASCADE,
133 stream TEXT NOT NULL DEFAULT 'stdout' CHECK(stream IN ('stdout', 'stderr')),
134 content TEXT NOT NULL,
135 created_at TEXT DEFAULT (datetime('now'))
136 );
137
138 CREATE TABLE IF NOT EXISTS canopy_secrets (
139 id INTEGER PRIMARY KEY AUTOINCREMENT,
140 repo_id INTEGER NOT NULL REFERENCES repos(id),
141 name TEXT NOT NULL,
142 encrypted_value TEXT NOT NULL,
143 created_at TEXT DEFAULT (datetime('now')),
144 updated_at TEXT DEFAULT (datetime('now')),
145 UNIQUE(repo_id, name)
146 );
147
148 CREATE TABLE IF NOT EXISTS bookmark_state (
149 repo_name TEXT NOT NULL,
150 bookmark_name TEXT NOT NULL,
151 commit_id TEXT NOT NULL,
152 updated_at TEXT DEFAULT (datetime('now')),
153 PRIMARY KEY (repo_name, bookmark_name)
154 );
155
156 CREATE TABLE IF NOT EXISTS orgs (
157 id INTEGER PRIMARY KEY,
158 name TEXT UNIQUE NOT NULL,
159 display_name TEXT,
160 created_at TEXT DEFAULT (datetime('now')),
161 updated_at TEXT DEFAULT (datetime('now'))
162 );
163
164 CREATE INDEX IF NOT EXISTS idx_pipelines_repo ON pipelines(repo_id);
165 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_repo ON pipeline_runs(repo_id);
166 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_status ON pipeline_runs(status);
167 CREATE INDEX IF NOT EXISTS idx_pipeline_steps_run ON pipeline_steps(run_id);
168 CREATE INDEX IF NOT EXISTS idx_step_logs_step ON step_logs(step_id);
169 CREATE INDEX IF NOT EXISTS idx_canopy_secrets_repo ON canopy_secrets(repo_id);
170 `);
171
172 // Add owner_type to repos
173 const repoCols = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
174 if (!repoCols.map((c) => c.name).includes("owner_type")) {
175 db.exec("ALTER TABLE repos ADD COLUMN owner_type TEXT NOT NULL DEFAULT 'user'");
176 }
177
178 // Rebuild legacy repos schema:
179 // - remove stale FK repos.owner_id -> users(id) so org-owned repos can be created
180 // - make uniqueness owner_type-aware
181 const reposTable = db
182 .prepare("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'repos'")
183 .get() as { sql?: string } | undefined;
184 const reposSql = reposTable?.sql ?? "";
185 const needsReposRebuild =
186 /REFERENCES\s+users\s*\(\s*id\s*\)/i.test(reposSql) ||
187 /UNIQUE\s*\(\s*owner_id\s*,\s*name\s*\)/i.test(reposSql);
188
189 if (needsReposRebuild) {
190 db.exec("PRAGMA foreign_keys = OFF");
191 db.exec("BEGIN TRANSACTION");
192 try {
193 db.exec("DROP VIEW IF EXISTS repos_with_owner");
194 db.exec("ALTER TABLE repos RENAME TO repos_legacy");
195 db.exec(`
196 CREATE TABLE repos (
197 id INTEGER PRIMARY KEY AUTOINCREMENT,
198 owner_id INTEGER NOT NULL,
199 owner_type TEXT NOT NULL DEFAULT 'user' CHECK(owner_type IN ('user', 'org')),
200 name TEXT NOT NULL,
201 description TEXT,
202 default_branch TEXT DEFAULT 'main',
203 is_private INTEGER DEFAULT 0,
204 mononoke_repo_id INTEGER,
205 created_at TEXT DEFAULT (datetime('now')),
206 updated_at TEXT DEFAULT (datetime('now')),
207 UNIQUE(owner_type, owner_id, name)
208 )
209 `);
210 db.exec(`
211 INSERT INTO repos (
212 id,
213 owner_id,
214 owner_type,
215 name,
216 description,
217 default_branch,
218 is_private,
219 mononoke_repo_id,
220 created_at,
221 updated_at
222 )
223 SELECT
224 id,
225 owner_id,
226 COALESCE(owner_type, 'user'),
227 name,
228 description,
229 default_branch,
230 is_private,
231 mononoke_repo_id,
232 created_at,
233 updated_at
234 FROM repos_legacy
235 `);
236 db.exec("DROP TABLE repos_legacy");
237 db.exec("CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_type, owner_id)");
238 db.exec("COMMIT");
239 } catch (err) {
240 db.exec("ROLLBACK");
241 throw err;
242 } finally {
243 db.exec("PRAGMA foreign_keys = ON");
244 }
245 }
246
247 // Repair bad FK targets introduced when repos was renamed during rebuild.
248 // Some SQLite versions rewrote dependent FK clauses to repos_legacy.
249 repairLegacyRepoForeignKeys(db);
250
251 // Migrate merge_requests -> diffs, merge_request_id -> diff_id
252 migrateMergeRequestsToDiffs(db);
253
254 // Migrate branch-based diffs to commit-based
255 migrateDiffsToCommitBased(db);
256
257 // Ensure diff-related indexes exist (safe after migration or on fresh db)
258 db.exec("CREATE INDEX IF NOT EXISTS idx_comments_diff ON comments(diff_id)");
259 db.exec("CREATE INDEX IF NOT EXISTS idx_reviews_diff ON reviews(diff_id)");
260 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
261
262 // Add require_diffs setting to repos
263 const repoColsForDiffs = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
264 if (!repoColsForDiffs.map((c) => c.name).includes("require_diffs")) {
265 db.exec("ALTER TABLE repos ADD COLUMN require_diffs INTEGER DEFAULT 0");
266 }
267
268 // Add pages settings to repos
269 const repoColsForPages = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
270 if (!repoColsForPages.map((c) => c.name).includes("pages_enabled")) {
271 db.exec("ALTER TABLE repos ADD COLUMN pages_enabled INTEGER DEFAULT 0");
272 }
273 if (!repoColsForPages.map((c) => c.name).includes("pages_domain")) {
274 db.exec("ALTER TABLE repos ADD COLUMN pages_domain TEXT");
275 }
276 db.exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_repos_pages_domain ON repos(pages_domain) WHERE pages_domain IS NOT NULL");
277
278 // Add commit_message to pipeline_runs
279 const runCols = db.prepare("PRAGMA table_info(pipeline_runs)").all() as { name: string }[];
280 if (!runCols.map((c) => c.name).includes("commit_message")) {
281 db.exec("ALTER TABLE pipeline_runs ADD COLUMN commit_message TEXT");
282 }
283
284 // Add pipeline_id FK to pipeline_runs, backfill from existing data
285 if (!runCols.map((c) => c.name).includes("pipeline_id")) {
286 db.exec(`
287 ALTER TABLE pipeline_runs ADD COLUMN pipeline_id INTEGER REFERENCES pipelines(id) ON DELETE CASCADE;
288
289 INSERT OR IGNORE INTO pipelines (repo_id, name, file)
290 SELECT DISTINCT repo_id, pipeline_name, pipeline_file
291 FROM pipeline_runs
292 WHERE pipeline_name IS NOT NULL;
293
294 UPDATE pipeline_runs
295 SET pipeline_id = (
296 SELECT p.id FROM pipelines p
297 WHERE p.repo_id = pipeline_runs.repo_id
298 AND p.name = pipeline_runs.pipeline_name
299 )
300 WHERE pipeline_id IS NULL;
301
302 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_pipeline ON pipeline_runs(pipeline_id);
303 `);
304 }
305
306 // View for resolving repo owner names across users and orgs
307 // DROP + recreate so the view picks up any new columns added above
308 db.exec("DROP VIEW IF EXISTS repos_with_owner");
309 db.exec(`
310 CREATE VIEW repos_with_owner AS
311 SELECT r.*,
312 CASE r.owner_type
313 WHEN 'user' THEN u.username
314 WHEN 'org' THEN o.name
315 END as owner_name
316 FROM repos r
317 LEFT JOIN users u ON r.owner_type = 'user' AND r.owner_id = u.id
318 LEFT JOIN orgs o ON r.owner_type = 'org' AND r.owner_id = o.id
319 `);
320}
321
322function tableReferencesLegacyRepos(db: Database.Database, tableName: string): boolean {
323 const row = db
324 .prepare("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?")
325 .get(tableName) as { sql?: string } | undefined;
326 const sql = row?.sql ?? "";
327 return /REFERENCES\s+"?repos_legacy"?\s*\(\s*id\s*\)/i.test(sql);
328}
329
330function repairLegacyRepoForeignKeys(db: Database.Database): void {
331 const needsRepair =
332 tableReferencesLegacyRepos(db, "diffs") ||
333 tableReferencesLegacyRepos(db, "pipelines") ||
334 tableReferencesLegacyRepos(db, "pipeline_runs") ||
335 tableReferencesLegacyRepos(db, "canopy_secrets");
336 if (!needsRepair) return;
337
338 const runCols = db.prepare("PRAGMA table_info(pipeline_runs)").all() as { name: string }[];
339 const hasCommitMessage = runCols.some((c) => c.name === "commit_message");
340 const hasPipelineId = runCols.some((c) => c.name === "pipeline_id");
341
342 db.exec("PRAGMA foreign_keys = OFF");
343 db.exec("BEGIN TRANSACTION");
344 try {
345 db.exec("DROP VIEW IF EXISTS repos_with_owner");
346
347 if (tableReferencesLegacyRepos(db, "pipelines")) {
348 db.exec(`
349 CREATE TABLE pipelines_new (
350 id INTEGER PRIMARY KEY AUTOINCREMENT,
351 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
352 name TEXT NOT NULL,
353 file TEXT NOT NULL,
354 created_at TEXT DEFAULT (datetime('now')),
355 UNIQUE(repo_id, name)
356 );
357 INSERT INTO pipelines_new (id, repo_id, name, file, created_at)
358 SELECT id, repo_id, name, file, created_at FROM pipelines;
359 DROP TABLE pipelines;
360 ALTER TABLE pipelines_new RENAME TO pipelines;
361 `);
362 db.exec("CREATE INDEX IF NOT EXISTS idx_pipelines_repo ON pipelines(repo_id)");
363 }
364
365 if (tableReferencesLegacyRepos(db, "pipeline_runs")) {
366 const commitMessageExpr = hasCommitMessage ? "commit_message" : "NULL";
367 const pipelineIdExpr = hasPipelineId ? "pipeline_id" : "NULL";
368 db.exec(`
369 CREATE TABLE pipeline_runs_new (
370 id INTEGER PRIMARY KEY AUTOINCREMENT,
371 repo_id INTEGER NOT NULL REFERENCES repos(id),
372 pipeline_name TEXT NOT NULL,
373 pipeline_file TEXT NOT NULL,
374 trigger_type TEXT NOT NULL DEFAULT 'push'
375 CHECK(trigger_type IN ('push', 'diff', 'manual', 'schedule')),
376 trigger_ref TEXT,
377 commit_id TEXT,
378 commit_message TEXT,
379 status TEXT NOT NULL DEFAULT 'pending'
380 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'cancelled')),
381 started_at TEXT,
382 finished_at TEXT,
383 duration_ms INTEGER,
384 created_at TEXT DEFAULT (datetime('now')),
385 pipeline_id INTEGER REFERENCES pipelines(id) ON DELETE CASCADE
386 );
387 INSERT INTO pipeline_runs_new (
388 id,
389 repo_id,
390 pipeline_name,
391 pipeline_file,
392 trigger_type,
393 trigger_ref,
394 commit_id,
395 commit_message,
396 status,
397 started_at,
398 finished_at,
399 duration_ms,
400 created_at,
401 pipeline_id
402 )
403 SELECT
404 id,
405 repo_id,
406 pipeline_name,
407 pipeline_file,
408 trigger_type,
409 trigger_ref,
410 commit_id,
411 ${commitMessageExpr},
412 status,
413 started_at,
414 finished_at,
415 duration_ms,
416 created_at,
417 ${pipelineIdExpr}
418 FROM pipeline_runs;
419 DROP TABLE pipeline_runs;
420 ALTER TABLE pipeline_runs_new RENAME TO pipeline_runs;
421 `);
422 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_repo ON pipeline_runs(repo_id)");
423 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_status ON pipeline_runs(status)");
424 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_pipeline ON pipeline_runs(pipeline_id)");
425 }
426
427 if (tableReferencesLegacyRepos(db, "diffs")) {
428 // Detect whether we have old branch-based or new commit-based schema
429 const diffCols = db.prepare("PRAGMA table_info(diffs)").all() as { name: string }[];
430 const hasBranches = diffCols.some((c) => c.name === "source_branch");
431 if (hasBranches) {
432 db.exec(`
433 CREATE TABLE diffs_new (
434 id INTEGER PRIMARY KEY AUTOINCREMENT,
435 repo_id INTEGER NOT NULL REFERENCES repos(id),
436 number INTEGER NOT NULL,
437 title TEXT NOT NULL,
438 description TEXT,
439 author_id INTEGER NOT NULL REFERENCES users(id),
440 head_commit TEXT NOT NULL DEFAULT '',
441 base_commit TEXT,
442 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
443 created_at TEXT DEFAULT (datetime('now')),
444 updated_at TEXT DEFAULT (datetime('now')),
445 landed_at TEXT,
446 landed_by INTEGER REFERENCES users(id),
447 UNIQUE(repo_id, number)
448 );
449 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, status, created_at, updated_at, landed_at, landed_by)
450 SELECT id, repo_id, number, title, description, author_id, COALESCE(source_branch, ''),
451 CASE status WHEN 'merged' THEN 'landed' ELSE status END,
452 created_at, updated_at, merged_at, merged_by
453 FROM diffs;
454 DROP TABLE diffs;
455 ALTER TABLE diffs_new RENAME TO diffs;
456 `);
457 } else {
458 db.exec(`
459 CREATE TABLE diffs_new (
460 id INTEGER PRIMARY KEY AUTOINCREMENT,
461 repo_id INTEGER NOT NULL REFERENCES repos(id),
462 number INTEGER NOT NULL,
463 title TEXT NOT NULL,
464 description TEXT,
465 author_id INTEGER NOT NULL REFERENCES users(id),
466 head_commit TEXT NOT NULL DEFAULT '',
467 base_commit TEXT,
468 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
469 created_at TEXT DEFAULT (datetime('now')),
470 updated_at TEXT DEFAULT (datetime('now')),
471 landed_at TEXT,
472 landed_by INTEGER REFERENCES users(id),
473 UNIQUE(repo_id, number)
474 );
475 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, base_commit, status, created_at, updated_at, landed_at, landed_by)
476 SELECT id, repo_id, number, title, description, author_id, head_commit, base_commit, status, created_at, updated_at, landed_at, landed_by
477 FROM diffs;
478 DROP TABLE diffs;
479 ALTER TABLE diffs_new RENAME TO diffs;
480 `);
481 }
482 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
483 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
484 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
485 }
486
487 if (tableReferencesLegacyRepos(db, "canopy_secrets")) {
488 db.exec(`
489 CREATE TABLE canopy_secrets_new (
490 id INTEGER PRIMARY KEY AUTOINCREMENT,
491 repo_id INTEGER NOT NULL REFERENCES repos(id),
492 name TEXT NOT NULL,
493 encrypted_value TEXT NOT NULL,
494 created_at TEXT DEFAULT (datetime('now')),
495 updated_at TEXT DEFAULT (datetime('now')),
496 UNIQUE(repo_id, name)
497 );
498 INSERT INTO canopy_secrets_new (
499 id,
500 repo_id,
501 name,
502 encrypted_value,
503 created_at,
504 updated_at
505 )
506 SELECT
507 id,
508 repo_id,
509 name,
510 encrypted_value,
511 created_at,
512 updated_at
513 FROM canopy_secrets;
514 DROP TABLE canopy_secrets;
515 ALTER TABLE canopy_secrets_new RENAME TO canopy_secrets;
516 `);
517 db.exec("CREATE INDEX IF NOT EXISTS idx_canopy_secrets_repo ON canopy_secrets(repo_id)");
518 }
519
520 db.exec("COMMIT");
521 } catch (err) {
522 db.exec("ROLLBACK");
523 throw err;
524 } finally {
525 db.exec("PRAGMA foreign_keys = ON");
526 }
527}
528
529function migrateMergeRequestsToDiffs(db: Database.Database): void {
530 // Check if old merge_requests table exists, or if reviews/comments still have old column
531 const oldTable = db
532 .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'merge_requests'")
533 .get() as { name: string } | undefined;
534 const reviewCols = db.prepare("PRAGMA table_info(reviews)").all() as { name: string }[];
535 const needsColumnMigration = reviewCols.some((c) => c.name === "merge_request_id");
536 if (!oldTable && !needsColumnMigration) return;
537
538 db.exec("PRAGMA foreign_keys = OFF");
539 db.exec("BEGIN TRANSACTION");
540 try {
541 // Rename merge_requests -> diffs if old table still exists
542 if (oldTable) {
543 // Drop the empty diffs table created by CREATE TABLE IF NOT EXISTS,
544 // then rename the real merge_requests table (which has the actual data)
545 const emptyDiffs = db
546 .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'diffs'")
547 .get();
548 if (emptyDiffs) {
549 db.exec("DROP TABLE diffs");
550 }
551 db.exec("ALTER TABLE merge_requests RENAME TO diffs");
552 }
553
554 // Rebuild reviews with diff_id instead of merge_request_id
555 if (needsColumnMigration) {
556 db.exec(`
557 CREATE TABLE reviews_new (
558 id INTEGER PRIMARY KEY AUTOINCREMENT,
559 diff_id INTEGER NOT NULL REFERENCES diffs(id),
560 reviewer_id INTEGER NOT NULL REFERENCES users(id),
561 status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'changes_requested')),
562 body TEXT,
563 created_at TEXT DEFAULT (datetime('now')),
564 updated_at TEXT DEFAULT (datetime('now'))
565 );
566 INSERT INTO reviews_new (id, diff_id, reviewer_id, status, body, created_at, updated_at)
567 SELECT id, merge_request_id, reviewer_id, status, body, created_at, updated_at
568 FROM reviews;
569 DROP TABLE reviews;
570 ALTER TABLE reviews_new RENAME TO reviews;
571 `);
572 }
573
574 // Rebuild comments with diff_id instead of merge_request_id
575 const commentCols = db.prepare("PRAGMA table_info(comments)").all() as { name: string }[];
576 if (commentCols.some((c) => c.name === "merge_request_id")) {
577 db.exec(`
578 CREATE TABLE comments_new (
579 id INTEGER PRIMARY KEY AUTOINCREMENT,
580 diff_id INTEGER NOT NULL REFERENCES diffs(id),
581 author_id INTEGER NOT NULL REFERENCES users(id),
582 body TEXT NOT NULL,
583 file_path TEXT,
584 line_number INTEGER,
585 side TEXT CHECK(side IN ('left', 'right')),
586 commit_sha TEXT,
587 parent_id INTEGER REFERENCES comments_new(id),
588 created_at TEXT DEFAULT (datetime('now')),
589 updated_at TEXT DEFAULT (datetime('now'))
590 );
591 INSERT INTO comments_new (id, diff_id, author_id, body, file_path, line_number, side, commit_sha, parent_id, created_at, updated_at)
592 SELECT id, merge_request_id, author_id, body, file_path, line_number, side, commit_sha, parent_id, created_at, updated_at
593 FROM comments;
594 DROP TABLE comments;
595 ALTER TABLE comments_new RENAME TO comments;
596 `);
597 }
598
599 // Drop old indexes and create new ones
600 db.exec("DROP INDEX IF EXISTS idx_merge_requests_repo");
601 db.exec("DROP INDEX IF EXISTS idx_merge_requests_author");
602 db.exec("DROP INDEX IF EXISTS idx_comments_mr");
603 db.exec("DROP INDEX IF EXISTS idx_reviews_mr");
604 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
605 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
606 db.exec("CREATE INDEX IF NOT EXISTS idx_comments_diff ON comments(diff_id)");
607 db.exec("CREATE INDEX IF NOT EXISTS idx_reviews_diff ON reviews(diff_id)");
608
609 db.exec("COMMIT");
610 } catch (err) {
611 db.exec("ROLLBACK");
612 throw err;
613 } finally {
614 db.exec("PRAGMA foreign_keys = ON");
615 }
616}
617
618function migrateDiffsToCommitBased(db: Database.Database): void {
619 // Check if diffs table still has branch-based columns
620 const diffCols = db.prepare("PRAGMA table_info(diffs)").all() as { name: string }[];
621 if (!diffCols.some((c) => c.name === "source_branch")) return;
622
623 db.exec("PRAGMA foreign_keys = OFF");
624 db.exec("BEGIN TRANSACTION");
625 try {
626 db.exec(`
627 CREATE TABLE diffs_new (
628 id INTEGER PRIMARY KEY AUTOINCREMENT,
629 repo_id INTEGER NOT NULL REFERENCES repos(id),
630 number INTEGER NOT NULL,
631 title TEXT NOT NULL,
632 description TEXT,
633 author_id INTEGER NOT NULL REFERENCES users(id),
634 head_commit TEXT NOT NULL DEFAULT '',
635 base_commit TEXT,
636 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
637 created_at TEXT DEFAULT (datetime('now')),
638 updated_at TEXT DEFAULT (datetime('now')),
639 landed_at TEXT,
640 landed_by INTEGER REFERENCES users(id),
641 UNIQUE(repo_id, number)
642 );
643 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, status, created_at, updated_at, landed_at, landed_by)
644 SELECT id, repo_id, number, title, description, author_id, COALESCE(source_branch, ''),
645 CASE status WHEN 'merged' THEN 'landed' ELSE status END,
646 created_at, updated_at, merged_at, merged_by
647 FROM diffs;
648 DROP TABLE diffs;
649 ALTER TABLE diffs_new RENAME TO diffs;
650 `);
651 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
652 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
653 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
654
655 db.exec("COMMIT");
656 } catch (err) {
657 db.exec("ROLLBACK");
658 throw err;
659 } finally {
660 db.exec("PRAGMA foreign_keys = ON");
661 }
662}
663