cli + tui to publish to leaflet (wip) & manage tasks, notes & watch/read lists 馃崈
charm
leaflet
readability
golang
1package store
2
3import (
4 "database/sql"
5 "embed"
6 "fmt"
7 "io/fs"
8 "testing"
9
10 _ "github.com/mattn/go-sqlite3"
11)
12
13//go:embed sql/migrations
14var testMigrationFiles embed.FS
15
16type fakeMigrationFS struct {
17 shouldFailRead bool
18 invalidSQL bool
19 hasNewMigrations bool
20}
21
22type fakeDirEntry struct {
23 name string
24}
25
26func (f fakeDirEntry) Name() string { return f.name }
27func (f fakeDirEntry) IsDir() bool { return false }
28func (f fakeDirEntry) Type() fs.FileMode { return 0 }
29func (f fakeDirEntry) Info() (fs.FileInfo, error) { return nil, fmt.Errorf("info not available") }
30
31func (f *fakeMigrationFS) ReadDir(name string) ([]fs.DirEntry, error) {
32 if name == "sql/migrations" {
33 entries := []fs.DirEntry{
34 fakeDirEntry{name: "0000_create_migrations_table_up.sql"},
35 }
36 if f.hasNewMigrations {
37 entries = append(entries,
38 fakeDirEntry{name: "0001_test_migration_up.sql"},
39 fakeDirEntry{name: "0001_test_migration_down.sql"},
40 )
41 }
42 return entries, nil
43 }
44 return nil, fmt.Errorf("directory not found: %s", name)
45}
46
47func (f *fakeMigrationFS) ReadFile(name string) ([]byte, error) {
48 if f.shouldFailRead {
49 return nil, fmt.Errorf("simulated read failure")
50 }
51 if f.invalidSQL {
52 return []byte("INVALID SQL SYNTAX GOES HERE AND MAKES DATABASE SAD"), nil
53 }
54 if name == "sql/migrations/0000_create_migrations_table_up.sql" {
55 return []byte("CREATE TABLE migrations (version TEXT PRIMARY KEY, applied_at DATETIME DEFAULT CURRENT_TIMESTAMP);"), nil
56 }
57 if name == "sql/migrations/0001_test_migration_up.sql" {
58 return []byte("CREATE TABLE test_table (id INTEGER PRIMARY KEY);"), nil
59 }
60 if name == "sql/migrations/0001_test_migration_down.sql" {
61 return []byte("DROP TABLE IF EXISTS test_table;"), nil
62 }
63 return nil, fmt.Errorf("file not found: %s", name)
64}
65
66func createTestDB(t *testing.T) *sql.DB {
67 db, err := sql.Open("sqlite3", ":memory:")
68 if err != nil {
69 t.Fatalf("Failed to create in-memory database: %v", err)
70 }
71
72 if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
73 t.Fatalf("Failed to enable foreign keys: %v", err)
74 }
75
76 t.Cleanup(func() {
77 db.Close()
78 })
79
80 return db
81}
82
83func TestNewMigrationRunner(t *testing.T) {
84 db := createTestDB(t)
85
86 runner := CreateMigrationRunner(db, testMigrationFiles)
87 if runner == nil {
88 t.Fatal("NewMigrationRunner should not return nil")
89 }
90
91 if runner.db != db {
92 t.Error("Migration runner should store the database reference")
93 }
94}
95
96func TestMigrationRunner_RunMigrations(t *testing.T) {
97 t.Run("runs migrations successfully", func(t *testing.T) {
98 db := createTestDB(t)
99 runner := CreateMigrationRunner(db, testMigrationFiles)
100
101 err := runner.RunMigrations()
102 if err != nil {
103 t.Fatalf("RunMigrations failed: %v", err)
104 }
105
106 var count int
107 err = db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='migrations'").Scan(&count)
108 if err != nil {
109 t.Fatalf("Failed to check migrations table: %v", err)
110 }
111
112 if count != 1 {
113 t.Error("Migrations table should exist after running migrations")
114 }
115
116 var migrationCount int
117 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&migrationCount)
118 if err != nil {
119 t.Fatalf("Failed to count applied migrations: %v", err)
120 }
121
122 if migrationCount == 0 {
123 t.Error("At least one migration should be applied")
124 }
125 })
126
127 t.Run("handles migration directory read failure", func(t *testing.T) {
128 db := createTestDB(t)
129
130 emptyFS := embed.FS{}
131 runner := CreateMigrationRunner(db, emptyFS)
132
133 err := runner.RunMigrations()
134 if err == nil {
135 t.Error("RunMigrations should fail when migration directory cannot be read")
136 }
137 })
138
139 t.Run("handles migration table check failure", func(t *testing.T) {
140 db := createTestDB(t)
141 db.Close()
142
143 runner := CreateMigrationRunner(db, testMigrationFiles)
144 err := runner.RunMigrations()
145 if err == nil {
146 t.Error("RunMigrations should fail when database connection is closed")
147 }
148 })
149
150 t.Run("handles migration file read failure", func(t *testing.T) {
151 db := createTestDB(t)
152
153 fakeFS := &fakeMigrationFS{shouldFailRead: true, hasNewMigrations: true}
154 runner := CreateMigrationRunner(db, fakeFS)
155
156 err := runner.RunMigrations()
157 if err == nil {
158 t.Error("RunMigrations should fail when migration file cannot be read")
159 }
160 })
161
162 t.Run("handles invalid SQL in migration file", func(t *testing.T) {
163 db := createTestDB(t)
164
165 fakeFS := &fakeMigrationFS{invalidSQL: true, hasNewMigrations: true}
166 runner := CreateMigrationRunner(db, fakeFS)
167
168 err := runner.RunMigrations()
169 if err == nil {
170 t.Error("RunMigrations should fail when migration contains invalid SQL")
171 }
172 })
173
174 t.Run("handles migration record insertion failure", func(t *testing.T) {
175 db := createTestDB(t)
176 runner := CreateMigrationRunner(db, testMigrationFiles)
177
178 err := runner.RunMigrations()
179 if err != nil {
180 t.Fatalf("First RunMigrations failed: %v", err)
181 }
182
183 _, err = db.Exec("DROP TABLE migrations")
184 if err != nil {
185 t.Fatalf("Failed to drop migrations table: %v", err)
186 }
187
188 _, err = db.Exec("CREATE TABLE migrations (version TEXT PRIMARY KEY CHECK(length(version) < 0))")
189 if err != nil {
190 t.Fatalf("Failed to create migrations table with constraint: %v", err)
191 }
192
193 err = runner.RunMigrations()
194 if err == nil {
195 t.Error("RunMigrations should fail when migration record cannot be inserted")
196 }
197 })
198
199 t.Run("skips already applied migrations", func(t *testing.T) {
200 db := createTestDB(t)
201 runner := CreateMigrationRunner(db, testMigrationFiles)
202
203 err := runner.RunMigrations()
204 if err != nil {
205 t.Fatalf("First RunMigrations failed: %v", err)
206 }
207
208 var initialCount int
209 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&initialCount)
210 if err != nil {
211 t.Fatalf("Failed to count migrations: %v", err)
212 }
213
214 err = runner.RunMigrations()
215 if err != nil {
216 t.Fatalf("Second RunMigrations failed: %v", err)
217 }
218
219 var finalCount int
220 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&finalCount)
221 if err != nil {
222 t.Fatalf("Failed to count migrations after second run: %v", err)
223 }
224
225 if finalCount != initialCount {
226 t.Errorf("Expected %d migrations, got %d (migrations should not be re-applied)", initialCount, finalCount)
227 }
228 })
229
230 t.Run("creates expected tables", func(t *testing.T) {
231 db := createTestDB(t)
232 runner := CreateMigrationRunner(db, testMigrationFiles)
233
234 err := runner.RunMigrations()
235 if err != nil {
236 t.Fatalf("RunMigrations failed: %v", err)
237 }
238
239 expectedTables := []string{"migrations", "tasks", "movies", "tv_shows", "books", "notes"}
240
241 for _, tableName := range expectedTables {
242 var count int
243 err = db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", tableName).Scan(&count)
244 if err != nil {
245 t.Fatalf("Failed to check table %s: %v", tableName, err)
246 }
247
248 if count != 1 {
249 t.Errorf("Table %s should exist after migrations", tableName)
250 }
251 }
252 })
253}
254
255func TestMigrationRunner_GetAppliedMigrations(t *testing.T) {
256 t.Run("returns empty list when no migrations table", func(t *testing.T) {
257 db := createTestDB(t)
258 runner := CreateMigrationRunner(db, testMigrationFiles)
259
260 migrations, err := runner.GetAppliedMigrations()
261 if err != nil {
262 t.Fatalf("GetAppliedMigrations failed: %v", err)
263 }
264
265 if len(migrations) != 0 {
266 t.Errorf("Expected 0 migrations, got %d", len(migrations))
267 }
268 })
269
270 t.Run("handles database connection failure", func(t *testing.T) {
271 db := createTestDB(t)
272 db.Close()
273 runner := CreateMigrationRunner(db, testMigrationFiles)
274
275 _, err := runner.GetAppliedMigrations()
276 if err == nil {
277 t.Error("GetAppliedMigrations should fail when database connection is closed")
278 }
279 })
280
281 t.Run("handles query execution failure", func(t *testing.T) {
282 db := createTestDB(t)
283 runner := CreateMigrationRunner(db, testMigrationFiles)
284
285 err := runner.RunMigrations()
286 if err != nil {
287 t.Fatalf("RunMigrations failed: %v", err)
288 }
289
290 // Close the database to trigger a query failure
291 db.Close()
292
293 _, err = runner.GetAppliedMigrations()
294 if err == nil {
295 t.Error("GetAppliedMigrations should fail when database is closed")
296 }
297 })
298
299 t.Run("handles row scan failure", func(t *testing.T) {
300 db := createTestDB(t)
301 runner := CreateMigrationRunner(db, testMigrationFiles)
302
303 err := runner.RunMigrations()
304 if err != nil {
305 t.Fatalf("RunMigrations failed: %v", err)
306 }
307
308 // Insert a record with NULL applied_at which should cause scan issues
309 _, err = db.Exec("INSERT INTO migrations (version, applied_at) VALUES ('test', NULL)")
310 if err != nil {
311 t.Fatalf("Failed to insert NULL migration record: %v", err)
312 }
313
314 _, err = runner.GetAppliedMigrations()
315 if err == nil {
316 t.Error("GetAppliedMigrations should fail when scanning NULL applied_at field")
317 }
318 })
319
320 t.Run("returns applied migrations", func(t *testing.T) {
321 db := createTestDB(t)
322 runner := CreateMigrationRunner(db, testMigrationFiles)
323
324 // Run migrations first
325 err := runner.RunMigrations()
326 if err != nil {
327 t.Fatalf("RunMigrations failed: %v", err)
328 }
329
330 migrations, err := runner.GetAppliedMigrations()
331 if err != nil {
332 t.Fatalf("GetAppliedMigrations failed: %v", err)
333 }
334
335 if len(migrations) == 0 {
336 t.Error("Should have applied migrations")
337 }
338
339 for _, migration := range migrations {
340 if migration.Version == "" {
341 t.Error("Migration version should not be empty")
342 }
343 if !migration.Applied {
344 t.Error("Migration should be marked as applied")
345 }
346 if migration.AppliedAt == "" {
347 t.Error("Migration should have applied timestamp")
348 }
349 }
350
351 for i := 1; i < len(migrations); i++ {
352 if migrations[i-1].Version > migrations[i].Version {
353 t.Error("Migrations should be sorted by version")
354 }
355 }
356 })
357}
358
359func TestMigrationRunner_GetAvailableMigrations(t *testing.T) {
360 t.Run("returns available migrations from embedded files", func(t *testing.T) {
361 db := createTestDB(t)
362 runner := CreateMigrationRunner(db, testMigrationFiles)
363
364 migrations, err := runner.GetAvailableMigrations()
365 if err != nil {
366 t.Fatalf("GetAvailableMigrations failed: %v", err)
367 }
368
369 if len(migrations) == 0 {
370 t.Error("Should have available migrations")
371 }
372
373 for _, migration := range migrations {
374 if migration.Version == "" {
375 t.Error("Migration version should not be empty")
376 }
377 if migration.UpSQL == "" {
378 t.Error("Migration should have up SQL")
379 }
380 // Note: Down SQL might be empty for some migrations, so we don't check it
381 }
382
383 for i := 1; i < len(migrations); i++ {
384 if migrations[i-1].Version > migrations[i].Version {
385 t.Error("Migrations should be sorted by version")
386 }
387 }
388 })
389
390 t.Run("handles migration directory read failure", func(t *testing.T) {
391 db := createTestDB(t)
392
393 emptyFS := embed.FS{}
394 runner := CreateMigrationRunner(db, emptyFS)
395
396 _, err := runner.GetAvailableMigrations()
397 if err == nil {
398 t.Error("GetAvailableMigrations should fail when migration directory cannot be read")
399 }
400 })
401
402 t.Run("handles migration file read failure", func(t *testing.T) {
403 db := createTestDB(t)
404
405 fakeFS := &fakeMigrationFS{shouldFailRead: true}
406 runner := CreateMigrationRunner(db, fakeFS)
407
408 _, err := runner.GetAvailableMigrations()
409 if err == nil {
410 t.Error("GetAvailableMigrations should fail when migration file cannot be read")
411 }
412 })
413
414 t.Run("includes both up and down SQL when available", func(t *testing.T) {
415 db := createTestDB(t)
416 runner := CreateMigrationRunner(db, testMigrationFiles)
417
418 migrations, err := runner.GetAvailableMigrations()
419 if err != nil {
420 t.Fatalf("GetAvailableMigrations failed: %v", err)
421 }
422
423 var foundMigrationWithDown bool
424 for _, migration := range migrations {
425 if migration.UpSQL != "" && migration.DownSQL != "" {
426 foundMigrationWithDown = true
427 break
428 }
429 }
430
431 if !foundMigrationWithDown {
432 t.Log("Note: No migrations found with both up and down SQL - this may be expected")
433 }
434 })
435}
436
437func TestMigrationRunner_Rollback(t *testing.T) {
438 t.Run("fails when no migrations to rollback", func(t *testing.T) {
439 db := createTestDB(t)
440 runner := CreateMigrationRunner(db, testMigrationFiles)
441
442 err := runner.Rollback()
443 if err == nil {
444 t.Error("Rollback should fail when no migrations are applied")
445 }
446 })
447
448 t.Run("handles database connection failure", func(t *testing.T) {
449 db := createTestDB(t)
450 runner := CreateMigrationRunner(db, testMigrationFiles)
451
452 err := runner.RunMigrations()
453 if err != nil {
454 t.Fatalf("RunMigrations failed: %v", err)
455 }
456
457 db.Close()
458
459 err = runner.Rollback()
460 if err == nil {
461 t.Error("Rollback should fail when database connection is closed")
462 }
463 })
464
465 t.Run("handles migration directory read failure during rollback", func(t *testing.T) {
466 db := createTestDB(t)
467 runner := CreateMigrationRunner(db, testMigrationFiles)
468
469 err := runner.RunMigrations()
470 if err != nil {
471 t.Fatalf("RunMigrations failed: %v", err)
472 }
473
474 emptyFS := embed.FS{}
475 runner.migrationFiles = emptyFS
476
477 err = runner.Rollback()
478 if err == nil {
479 t.Error("Rollback should fail when migration directory cannot be read")
480 }
481 })
482
483 t.Run("handles missing down migration file", func(t *testing.T) {
484 db := createTestDB(t)
485 runner := CreateMigrationRunner(db, testMigrationFiles)
486
487 err := runner.RunMigrations()
488 if err != nil {
489 t.Fatalf("RunMigrations failed: %v", err)
490 }
491
492 fakeFS := &fakeMigrationFS{}
493 runner.migrationFiles = fakeFS
494
495 err = runner.Rollback()
496 if err == nil {
497 t.Error("Rollback should fail when down migration file is not found")
498 }
499 })
500
501 t.Run("handles down migration file read failure", func(t *testing.T) {
502 db := createTestDB(t)
503
504 fakeFS := &fakeMigrationFS{}
505 runner := CreateMigrationRunner(db, fakeFS)
506
507 err := runner.RunMigrations()
508 if err != nil {
509 t.Fatalf("RunMigrations failed: %v", err)
510 }
511
512 fakeFS.shouldFailRead = true
513
514 err = runner.Rollback()
515 if err == nil {
516 t.Error("Rollback should fail when down migration file cannot be read")
517 }
518 })
519
520 t.Run("handles invalid down migration SQL", func(t *testing.T) {
521 db := createTestDB(t)
522
523 fakeFS := &fakeMigrationFS{}
524 runner := CreateMigrationRunner(db, fakeFS)
525
526 err := runner.RunMigrations()
527 if err != nil {
528 t.Fatalf("RunMigrations failed: %v", err)
529 }
530
531 fakeFS.invalidSQL = true
532
533 err = runner.Rollback()
534 if err == nil {
535 t.Error("Rollback should fail when down migration contains invalid SQL")
536 }
537 })
538
539 t.Run("handles migration record deletion failure", func(t *testing.T) {
540 db := createTestDB(t)
541 runner := CreateMigrationRunner(db, testMigrationFiles)
542
543 err := runner.RunMigrations()
544 if err != nil {
545 t.Fatalf("RunMigrations failed: %v", err)
546 }
547
548 _, err = db.Exec("DROP TABLE migrations")
549 if err != nil {
550 t.Fatalf("Failed to drop migrations table: %v", err)
551 }
552
553 err = runner.Rollback()
554 if err == nil {
555 t.Error("Rollback should fail when migration record cannot be deleted")
556 }
557 })
558
559 t.Run("rolls back last migration", func(t *testing.T) {
560 db := createTestDB(t)
561 runner := CreateMigrationRunner(db, testMigrationFiles)
562
563 err := runner.RunMigrations()
564 if err != nil {
565 t.Fatalf("RunMigrations failed: %v", err)
566 }
567
568 var initialCount int
569 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&initialCount)
570 if err != nil {
571 t.Fatalf("Failed to count migrations: %v", err)
572 }
573
574 if initialCount == 0 {
575 t.Skip("No migrations to rollback")
576 }
577
578 err = runner.Rollback()
579 if err != nil {
580 t.Fatalf("Rollback failed: %v", err)
581 }
582
583 var finalCount int
584 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&finalCount)
585 if err != nil {
586 t.Fatalf("Failed to count migrations after rollback: %v", err)
587 }
588
589 if finalCount != initialCount-1 {
590 t.Errorf("Expected %d migrations after rollback, got %d", initialCount-1, finalCount)
591 }
592 })
593}
594
595func TestMigrationHelperFunctions(t *testing.T) {
596 t.Run("extractVersionFromFilename", func(t *testing.T) {
597 testCases := []struct {
598 filename string
599 expected string
600 }{
601 {"0000_create_migrations_table_up.sql", "0000"},
602 {"0001_create_all_tables_up.sql", "0001"},
603 {"0002_add_indexes_down.sql", "0002"},
604 {"invalid_filename.sql", "invalid"},
605 {"", ""},
606 }
607
608 for _, tc := range testCases {
609 result := extractVersionFromFilename(tc.filename)
610 if result != tc.expected {
611 t.Errorf("extractVersionFromFilename(%s): expected %s, got %s", tc.filename, tc.expected, result)
612 }
613 }
614 })
615
616 t.Run("extractNameFromFilename", func(t *testing.T) {
617 testCases := []struct {
618 filename string
619 expected string
620 }{
621 {"0000_create_migrations_table_up.sql", "create_migrations_table"},
622 {"0001_create_all_tables_up.sql", "create_all_tables"},
623 {"0002_add_indexes_down.sql", "add_indexes"},
624 {"invalid_filename.sql", ""},
625 {"0003_up.sql", ""},
626 {"", ""},
627 }
628
629 for _, tc := range testCases {
630 result := extractNameFromFilename(tc.filename)
631 if result != tc.expected {
632 t.Errorf("extractNameFromFilename(%s): expected %s, got %s", tc.filename, tc.expected, result)
633 }
634 }
635 })
636}
637
638func TestMigrationIntegration(t *testing.T) {
639 t.Run("full migration lifecycle", func(t *testing.T) {
640 db := createTestDB(t)
641 runner := CreateMigrationRunner(db, testMigrationFiles)
642
643 available, err := runner.GetAvailableMigrations()
644 if err != nil {
645 t.Fatalf("GetAvailableMigrations failed: %v", err)
646 }
647
648 if len(available) == 0 {
649 t.Skip("No migrations available for testing")
650 }
651
652 err = runner.RunMigrations()
653 if err != nil {
654 t.Fatalf("RunMigrations failed: %v", err)
655 }
656
657 applied, err := runner.GetAppliedMigrations()
658 if err != nil {
659 t.Fatalf("GetAppliedMigrations failed: %v", err)
660 }
661
662 if len(applied) == 0 {
663 t.Error("No migrations were applied")
664 }
665
666 tables := []string{"tasks", "movies", "tv_shows", "books", "notes"}
667 for _, table := range tables {
668 var count int
669 err = db.QueryRow("SELECT COUNT(*) FROM " + table).Scan(&count)
670 if err != nil {
671 t.Errorf("Failed to query table %s: %v", table, err)
672 }
673 }
674
675 if len(applied) > 1 { // Only test rollback if we have more than one migration
676 err = runner.Rollback()
677 if err != nil {
678 t.Logf("Rollback failed (may be expected): %v", err)
679 }
680 }
681 })
682
683 t.Run("migration runner works with real database", func(t *testing.T) {
684 db := createTestDB(t)
685 runner := CreateMigrationRunner(db, migrationFiles)
686
687 err := runner.RunMigrations()
688 if err != nil {
689 t.Fatalf("RunMigrations with real files failed: %v", err)
690 }
691
692 var count int
693 err = db.QueryRow("SELECT COUNT(*) FROM migrations").Scan(&count)
694 if err != nil {
695 t.Fatalf("Failed to count real migrations: %v", err)
696 }
697
698 if count == 0 {
699 t.Error("Real migrations should be applied")
700 }
701 })
702}