1package migrations
2
3import (
4 "database/sql"
5 "path/filepath"
6 "testing"
7
8 _ "github.com/ncruces/go-sqlite3/driver"
9 _ "github.com/ncruces/go-sqlite3/embed"
10)
11
12// TestMigrationsAreIdempotent verifies that running migrations multiple times doesn't cause errors
13func TestMigrationsAreIdempotent(t *testing.T) {
14 // Create temporary database
15 tmpDir := t.TempDir()
16 dbPath := filepath.Join(tmpDir, "test.db")
17
18 // Open database
19 db, err := sql.Open("sqlite3", dbPath)
20 if err != nil {
21 t.Fatalf("Failed to open test database: %v", err)
22 }
23 defer func() { _ = db.Close() }()
24
25 // Run migrations first time
26 if err := RunEmbeddedMigrations(db); err != nil {
27 t.Fatalf("First migration run failed: %v", err)
28 }
29
30 // Get applied migrations after first run
31 appliedFirst, err := GetAppliedMigrations(db)
32 if err != nil {
33 t.Fatalf("Failed to get applied migrations after first run: %v", err)
34 }
35
36 if len(appliedFirst) == 0 {
37 t.Fatal("No migrations were applied on first run")
38 }
39
40 // Run migrations second time - should be idempotent
41 if err := RunEmbeddedMigrations(db); err != nil {
42 t.Fatalf("Second migration run failed (not idempotent): %v", err)
43 }
44
45 // Get applied migrations after second run
46 appliedSecond, err := GetAppliedMigrations(db)
47 if err != nil {
48 t.Fatalf("Failed to get applied migrations after second run: %v", err)
49 }
50
51 // Verify same migrations are applied
52 if len(appliedFirst) != len(appliedSecond) {
53 t.Errorf("Migration count changed: first=%d, second=%d", len(appliedFirst), len(appliedSecond))
54 }
55
56 for i, v := range appliedFirst {
57 if i >= len(appliedSecond) || v != appliedSecond[i] {
58 t.Errorf("Applied migrations differ at index %d: first=%d, second=%d", i, v, appliedSecond[i])
59 }
60 }
61
62 // Run migrations third time to be extra sure
63 if err := RunEmbeddedMigrations(db); err != nil {
64 t.Fatalf("Third migration run failed (not idempotent): %v", err)
65 }
66
67 appliedThird, err := GetAppliedMigrations(db)
68 if err != nil {
69 t.Fatalf("Failed to get applied migrations after third run: %v", err)
70 }
71
72 if len(appliedThird) != len(appliedFirst) {
73 t.Errorf("Migration count changed on third run: expected=%d, got=%d", len(appliedFirst), len(appliedThird))
74 }
75}
76
77// TestMigrationTrackingPreventsReapplication verifies that applied migrations are tracked and not re-run
78func TestMigrationTrackingPreventsReapplication(t *testing.T) {
79 tmpDir := t.TempDir()
80 dbPath := filepath.Join(tmpDir, "test.db")
81
82 db, err := sql.Open("sqlite3", dbPath)
83 if err != nil {
84 t.Fatalf("Failed to open test database: %v", err)
85 }
86 defer func() { _ = db.Close() }()
87
88 // Run migrations
89 if err := RunEmbeddedMigrations(db); err != nil {
90 t.Fatalf("Migration run failed: %v", err)
91 }
92
93 // Check that schema_migrations table exists and has entries
94 var count int
95 err = db.QueryRow("SELECT COUNT(*) FROM schema_migrations").Scan(&count)
96 if err != nil {
97 t.Fatalf("Failed to query schema_migrations table: %v", err)
98 }
99
100 if count == 0 {
101 t.Fatal("No migration tracking entries found")
102 }
103
104 // Verify each migration has exactly one entry
105 rows, err := db.Query("SELECT version, COUNT(*) FROM schema_migrations GROUP BY version HAVING COUNT(*) > 1")
106 if err != nil {
107 t.Fatalf("Failed to check for duplicate migration entries: %v", err)
108 }
109 defer func() { _ = rows.Close() }()
110
111 duplicates := []int{}
112 for rows.Next() {
113 var version, dupeCount int
114 if err := rows.Scan(&version, &dupeCount); err != nil {
115 t.Fatalf("Failed to scan duplicate check: %v", err)
116 }
117 duplicates = append(duplicates, version)
118 }
119
120 if len(duplicates) > 0 {
121 t.Errorf("Found duplicate migration entries for versions: %v", duplicates)
122 }
123}
124
125// TestAllMigrationsApplyOnFreshDatabase verifies all migrations work on a new database
126func TestAllMigrationsApplyOnFreshDatabase(t *testing.T) {
127 tmpDir := t.TempDir()
128 dbPath := filepath.Join(tmpDir, "test.db")
129
130 db, err := sql.Open("sqlite3", dbPath)
131 if err != nil {
132 t.Fatalf("Failed to open test database: %v", err)
133 }
134 defer func() { _ = db.Close() }()
135
136 // Get expected migrations
137 expectedMigrations, err := GetMigrations()
138 if err != nil {
139 t.Fatalf("Failed to get migrations: %v", err)
140 }
141
142 if len(expectedMigrations) == 0 {
143 t.Fatal("No migration files found")
144 }
145
146 // Run migrations
147 if err := RunEmbeddedMigrations(db); err != nil {
148 t.Fatalf("Failed to run migrations: %v", err)
149 }
150
151 // Verify all migrations were applied
152 appliedVersions, err := GetAppliedMigrations(db)
153 if err != nil {
154 t.Fatalf("Failed to get applied migrations: %v", err)
155 }
156
157 if len(appliedVersions) != len(expectedMigrations) {
158 t.Errorf("Expected %d migrations to be applied, got %d", len(expectedMigrations), len(appliedVersions))
159 }
160
161 // Verify each expected migration was applied
162 for i, migration := range expectedMigrations {
163 if i >= len(appliedVersions) {
164 t.Errorf("Migration %d (%s) was not applied", migration.Version, migration.Name)
165 continue
166 }
167 if appliedVersions[i] != migration.Version {
168 t.Errorf("Migration version mismatch at index %d: expected %d, got %d", i, migration.Version, appliedVersions[i])
169 }
170 }
171}
172
173// TestMigrationOrdering verifies migrations are applied in correct order
174func TestMigrationOrdering(t *testing.T) {
175 migrations, err := GetMigrations()
176 if err != nil {
177 t.Fatalf("Failed to get migrations: %v", err)
178 }
179
180 if len(migrations) == 0 {
181 t.Fatal("No migrations found")
182 }
183
184 // Verify migrations are in ascending order
185 for i := 1; i < len(migrations); i++ {
186 if migrations[i].Version <= migrations[i-1].Version {
187 t.Errorf("Migrations not in ascending order: migration[%d].Version=%d, migration[%d].Version=%d",
188 i-1, migrations[i-1].Version, i, migrations[i].Version)
189 }
190 }
191
192 // Verify no gaps in version numbers (should be 1, 2, 3, 4, 5...)
193 for i, migration := range migrations {
194 expectedVersion := i + 1
195 if migration.Version != expectedVersion {
196 t.Errorf("Migration version gap detected: expected version %d, got %d", expectedVersion, migration.Version)
197 }
198 }
199}
200
201// TestMigrationTableCreationIsIdempotent verifies the tracking table can be created multiple times
202func TestMigrationTableCreationIsIdempotent(t *testing.T) {
203 tmpDir := t.TempDir()
204 dbPath := filepath.Join(tmpDir, "test.db")
205
206 db, err := sql.Open("sqlite3", dbPath)
207 if err != nil {
208 t.Fatalf("Failed to open test database: %v", err)
209 }
210 defer func() { _ = db.Close() }()
211
212 // Create tracking table multiple times
213 for i := 0; i < 3; i++ {
214 if err := createMigrationsTable(db); err != nil {
215 t.Fatalf("Failed to create migrations table on attempt %d: %v", i+1, err)
216 }
217 }
218
219 // Verify table exists and has correct schema
220 var tableName string
221 err = db.QueryRow(`
222 SELECT name FROM sqlite_master
223 WHERE type='table' AND name='schema_migrations'
224 `).Scan(&tableName)
225
226 if err != nil {
227 t.Fatalf("Migrations table not found: %v", err)
228 }
229
230 if tableName != "schema_migrations" {
231 t.Errorf("Expected table name 'schema_migrations', got '%s'", tableName)
232 }
233
234 // Verify table has expected columns
235 rows, err := db.Query("PRAGMA table_info(schema_migrations)")
236 if err != nil {
237 t.Fatalf("Failed to get table info: %v", err)
238 }
239 defer func() { _ = rows.Close() }()
240
241 expectedColumns := map[string]bool{
242 "version": false,
243 "name": false,
244 "applied_at": false,
245 }
246
247 for rows.Next() {
248 var cid int
249 var name, ctype string
250 var notnull, pk int
251 var dfltValue sql.NullString
252
253 if err := rows.Scan(&cid, &name, &ctype, ¬null, &dfltValue, &pk); err != nil {
254 t.Fatalf("Failed to scan table info: %v", err)
255 }
256
257 if _, expected := expectedColumns[name]; expected {
258 expectedColumns[name] = true
259 }
260 }
261
262 for col, found := range expectedColumns {
263 if !found {
264 t.Errorf("Expected column '%s' not found in schema_migrations table", col)
265 }
266 }
267}
268
269// TestPartialMigrationState verifies that a database with some migrations can have new ones applied
270func TestPartialMigrationState(t *testing.T) {
271 tmpDir := t.TempDir()
272 dbPath := filepath.Join(tmpDir, "test.db")
273
274 db, err := sql.Open("sqlite3", dbPath)
275 if err != nil {
276 t.Fatalf("Failed to open test database: %v", err)
277 }
278 defer func() { _ = db.Close() }()
279
280 // Create tracking table
281 if err := createMigrationsTable(db); err != nil {
282 t.Fatalf("Failed to create migrations table: %v", err)
283 }
284
285 // Get all migrations
286 allMigrations, err := GetMigrations()
287 if err != nil {
288 t.Fatalf("Failed to get migrations: %v", err)
289 }
290
291 if len(allMigrations) < 2 {
292 t.Skip("Need at least 2 migrations for this test")
293 }
294
295 // Manually apply only the first migration
296 firstMigration := allMigrations[0]
297 tx, err := db.Begin()
298 if err != nil {
299 t.Fatalf("Failed to begin transaction: %v", err)
300 }
301
302 if _, err := tx.Exec(firstMigration.SQL); err != nil {
303 _ = tx.Rollback()
304 t.Fatalf("Failed to execute first migration: %v", err)
305 }
306
307 if _, err := tx.Exec("INSERT INTO schema_migrations (version, name) VALUES (?, ?)", firstMigration.Version, firstMigration.Name); err != nil {
308 _ = tx.Rollback()
309 t.Fatalf("Failed to record first migration: %v", err)
310 }
311
312 if err := tx.Commit(); err != nil {
313 t.Fatalf("Failed to commit first migration: %v", err)
314 }
315
316 // Verify only one migration is applied
317 applied, err := GetAppliedMigrations(db)
318 if err != nil {
319 t.Fatalf("Failed to get applied migrations: %v", err)
320 }
321
322 if len(applied) != 1 {
323 t.Fatalf("Expected 1 applied migration, got %d", len(applied))
324 }
325
326 // Now run all migrations - should apply the remaining ones
327 if err := RunEmbeddedMigrations(db); err != nil {
328 t.Fatalf("Failed to run remaining migrations: %v", err)
329 }
330
331 // Verify all migrations are now applied
332 appliedAfter, err := GetAppliedMigrations(db)
333 if err != nil {
334 t.Fatalf("Failed to get applied migrations after full run: %v", err)
335 }
336
337 if len(appliedAfter) != len(allMigrations) {
338 t.Errorf("Expected %d migrations applied, got %d", len(allMigrations), len(appliedAfter))
339 }
340
341 // Verify the first migration wasn't re-applied
342 if appliedAfter[0] != firstMigration.Version {
343 t.Errorf("First migration version changed: expected %d, got %d", firstMigration.Version, appliedAfter[0])
344 }
345}
346
347// TestGetAppliedMigrationsEmptyDatabase verifies behavior on a database with no migrations
348func TestGetAppliedMigrationsEmptyDatabase(t *testing.T) {
349 tmpDir := t.TempDir()
350 dbPath := filepath.Join(tmpDir, "test.db")
351
352 db, err := sql.Open("sqlite3", dbPath)
353 if err != nil {
354 t.Fatalf("Failed to open test database: %v", err)
355 }
356 defer func() { _ = db.Close() }()
357
358 // Query applied migrations on empty database
359 applied, err := GetAppliedMigrations(db)
360 if err != nil {
361 t.Fatalf("GetAppliedMigrations failed on empty database: %v", err)
362 }
363
364 if len(applied) != 0 {
365 t.Errorf("Expected 0 applied migrations on empty database, got %d", len(applied))
366 }
367}
368
369// TestVerifyAllMigrationsApplied verifies the verification function works correctly
370func TestVerifyAllMigrationsApplied(t *testing.T) {
371 tmpDir := t.TempDir()
372 dbPath := filepath.Join(tmpDir, "test.db")
373
374 db, err := sql.Open("sqlite3", dbPath)
375 if err != nil {
376 t.Fatalf("Failed to open test database: %v", err)
377 }
378 defer func() { _ = db.Close() }()
379
380 // Run migrations
381 if err := RunEmbeddedMigrations(db); err != nil {
382 t.Fatalf("Failed to run migrations: %v", err)
383 }
384
385 // Verify should pass
386 if err := VerifyAllMigrationsApplied(db); err != nil {
387 t.Errorf("VerifyAllMigrationsApplied() failed after running all migrations: %v", err)
388 }
389
390 // Test with missing migration - manually delete one from tracking
391 _, err = db.Exec("DELETE FROM schema_migrations WHERE version = 3")
392 if err != nil {
393 t.Fatalf("Failed to delete migration record: %v", err)
394 }
395
396 // Verify should now fail
397 err = VerifyAllMigrationsApplied(db)
398 if err == nil {
399 t.Error("VerifyAllMigrationsApplied() should have failed with missing migration, but it passed")
400 }
401}
402
403// TestExpectedTablesExistAfterMigrations verifies that all expected tables are created
404func TestExpectedTablesExistAfterMigrations(t *testing.T) {
405 tmpDir := t.TempDir()
406 dbPath := filepath.Join(tmpDir, "test.db")
407
408 db, err := sql.Open("sqlite3", dbPath)
409 if err != nil {
410 t.Fatalf("Failed to open test database: %v", err)
411 }
412 defer func() { _ = db.Close() }()
413
414 // Run migrations
415 if err := RunEmbeddedMigrations(db); err != nil {
416 t.Fatalf("Failed to run migrations: %v", err)
417 }
418
419 // Expected tables based on current migrations
420 expectedTables := []string{
421 "schema_migrations", // Migration tracking
422 "history", // 001_initial.sql
423 "zoom_levels", // 003_add_zoom_levels.sql
424 "certificate_validations", // 005_add_certificate_validations.sql
425 // Note: shortcuts table removed in 007_remove_shortcuts_table.sql
426 }
427
428 // Check each expected table exists
429 for _, tableName := range expectedTables {
430 var name string
431 err := db.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name=?", tableName).Scan(&name)
432 if err == sql.ErrNoRows {
433 t.Errorf("Expected table '%s' does not exist", tableName)
434 } else if err != nil {
435 t.Fatalf("Failed to check for table '%s': %v", tableName, err)
436 }
437 }
438}