Skip to main content

max / audiofiles

25.9 KB · 705 lines History Blame Raw
1 //! Migration-log tests: schema shape, replay, idempotence and the M018/M019 rewrites.
2 //!
3 //! Extracted from the former `db.rs` inline test module.
4
5 use crate::db::migrations::{MIGRATION_034, MIGRATION_035, MIGRATIONS};
6 use crate::db::*;
7
8 #[test]
9 fn migration_034_normalises_legacy_key_spellings() {
10 let db = Database::open_in_memory().unwrap();
11 // Rows written before the detector normalised its output. Inserted
12 // post-migration and re-run explicitly, since an in-memory DB starts
13 // empty and the migration would otherwise have nothing to rewrite.
14 db.conn()
15 .execute_batch(
16 "INSERT INTO samples
17 (hash, original_name, file_extension, file_size, import_date, last_modified)
18 VALUES ('a', 'a.wav', 'wav', 1, 0, 0), ('b', 'b.wav', 'wav', 1, 0, 0),
19 ('c', 'c.wav', 'wav', 1, 0, 0), ('d', 'd.wav', 'wav', 1, 0, 0);
20 INSERT INTO audio_analysis
21 (hash, musical_key, duration, sample_rate, channels, analyzed_at)
22 VALUES ('a', 'Am', 1.0, 44100, 2, 0), ('b', 'C#m', 1.0, 44100, 2, 0),
23 ('c', 'F#', 1.0, 44100, 2, 0), ('d', 'A minor', 1.0, 44100, 2, 0);",
24 )
25 .unwrap();
26 db.conn().execute_batch(MIGRATION_034).unwrap();
27
28 let mut stmt = db
29 .conn()
30 .prepare("SELECT hash, musical_key FROM audio_analysis ORDER BY hash")
31 .unwrap();
32 let got: Vec<(String, String)> = stmt
33 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
34 .unwrap()
35 .map(Result::unwrap)
36 .collect();
37
38 assert_eq!(got[0].1, "A minor");
39 assert_eq!(got[1].1, "C# minor");
40 assert_eq!(got[2].1, "F# major");
41 // Already canonical, must not be rewritten to "A minor major".
42 assert_eq!(got[3].1, "A minor");
43
44 // Idempotent: a second application changes nothing.
45 db.conn().execute_batch(MIGRATION_034).unwrap();
46 let after: String = db
47 .conn()
48 .query_row(
49 "SELECT musical_key FROM audio_analysis WHERE hash = 'a'",
50 [],
51 |r| r.get(0),
52 )
53 .unwrap();
54 assert_eq!(after, "A minor");
55 }
56
57 #[test]
58 fn migration_035_rewrites_legacy_key_tags() {
59 let db = Database::open_in_memory().unwrap();
60 db.conn()
61 .execute_batch(
62 "INSERT INTO samples
63 (hash, original_name, file_extension, file_size, import_date, last_modified)
64 VALUES ('a', 'a.wav', 'wav', 1, 0, 0), ('b', 'b.wav', 'wav', 1, 0, 0),
65 ('c', 'c.wav', 'wav', 1, 0, 0);
66 INSERT INTO tags (sample_hash, tag) VALUES
67 ('a', 'key.am'),
68 ('a', 'genre.techno'),
69 ('b', 'key.c-sharpm'),
70 ('b', 'key.f-sharp'),
71 -- already migrated, plus its legacy twin: must not collide
72 ('c', 'key.a-minor'),
73 ('c', 'key.am');",
74 )
75 .unwrap();
76 db.conn().execute_batch(MIGRATION_035).unwrap();
77
78 let tags = |hash: &str| -> Vec<String> {
79 let mut stmt = db
80 .conn()
81 .prepare("SELECT tag FROM tags WHERE sample_hash = ?1 ORDER BY tag")
82 .unwrap();
83 let v: Vec<String> = stmt
84 .query_map([hash], |r| r.get(0))
85 .unwrap()
86 .map(Result::unwrap)
87 .collect();
88 v
89 };
90
91 assert_eq!(tags("a"), vec!["genre.techno", "key.a-minor"]);
92 assert_eq!(tags("b"), vec!["key.c-sharp-minor", "key.f-sharp-major"]);
93 // The collision collapses to the single canonical tag rather than
94 // failing the migration on the primary key.
95 assert_eq!(tags("c"), vec!["key.a-minor"]);
96
97 // Idempotent: canonical tags match no legacy spelling.
98 db.conn().execute_batch(MIGRATION_035).unwrap();
99 assert_eq!(tags("a"), vec!["genre.techno", "key.a-minor"]);
100 }
101
102 #[test]
103 fn key_migrations_do_not_enqueue_sync_changelog() {
104 // Both key migrations normalise a local format that every device fixes
105 // for itself. Replicating them would push canonical values to peers
106 // still running the old detector, which would keep writing the compact
107 // spelling and leave the vault holding both.
108 let db = Database::open_in_memory().unwrap();
109 db.conn()
110 .execute_batch(
111 "INSERT INTO sync_state (key, value) VALUES ('applying_remote', '0')
112 ON CONFLICT(key) DO UPDATE SET value = '0';
113 INSERT INTO samples
114 (hash, original_name, file_extension, file_size, import_date, last_modified)
115 VALUES ('a', 'a.wav', 'wav', 1, 0, 0);
116 INSERT INTO audio_analysis
117 (hash, musical_key, duration, sample_rate, channels, analyzed_at)
118 VALUES ('a', 'Am', 1.0, 44100, 2, 0);",
119 )
120 .unwrap();
121 let before: i64 = db
122 .conn()
123 .query_row("SELECT count(*) FROM sync_changelog", [], |r| r.get(0))
124 .unwrap();
125
126 db.conn().execute_batch(MIGRATION_034).unwrap();
127 db.conn().execute_batch(MIGRATION_035).unwrap();
128
129 let after: i64 = db
130 .conn()
131 .query_row("SELECT count(*) FROM sync_changelog", [], |r| r.get(0))
132 .unwrap();
133 assert_eq!(
134 before, after,
135 "key migrations must not enqueue changelog rows"
136 );
137
138 // Control: the same write outside the migration must enqueue, otherwise
139 // the assertion above would hold even if the triggers never fired here
140 // and would prove nothing.
141 db.conn()
142 .execute_batch("UPDATE audio_analysis SET musical_key = 'B minor' WHERE hash = 'a';")
143 .unwrap();
144 let control: i64 = db
145 .conn()
146 .query_row("SELECT count(*) FROM sync_changelog", [], |r| r.get(0))
147 .unwrap();
148 assert!(
149 control > after,
150 "sync trigger never fired, so the suppression assertion is vacuous"
151 );
152
153 // And the flag is left back where it started, not stuck at '1', which
154 // would silently stop capturing every later user edit.
155 let flag: String = db
156 .conn()
157 .query_row(
158 "SELECT value FROM sync_state WHERE key = 'applying_remote'",
159 [],
160 |r| r.get(0),
161 )
162 .unwrap();
163 assert_eq!(flag, "0");
164 }
165
166 #[test]
167 fn open_in_memory_creates_all_tables() {
168 let db = Database::open_in_memory().unwrap();
169
170 // Exclude the FTS5 shadow tables (vfs_nodes_fts, _data, _idx, _docsize,
171 // _config) created by M027, this asserts the set of logical tables.
172 let tables: Vec<String> = db
173 .conn()
174 .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'vfs_nodes_fts%' ORDER BY name")
175 .unwrap()
176 .query_map([], |row| row.get(0))
177 .unwrap()
178 .collect::<Result<_, _>>()
179 .unwrap();
180
181 let expected = vec![
182 "audio_analysis",
183 "classifier_exemplars",
184 "classifier_layer_rules",
185 "classifier_layers",
186 "cluster_members",
187 "clusters",
188 "collection_members",
189 "collections",
190 "config_key_policy",
191 "edit_history",
192 "fingerprints",
193 "hlc_ledger",
194 "neighbour_graph_dirty",
195 "neighbour_graph_meta",
196 "sample_features",
197 "sample_neighbours",
198 "samples",
199 "sync_changelog",
200 "sync_state",
201 "tag_policy",
202 "tag_provenance",
203 "tag_rules",
204 "tags",
205 "trained_head",
206 "user_config",
207 "vfs",
208 "vfs_nodes",
209 "waveform_data",
210 ];
211 assert_eq!(tables, expected);
212 }
213
214 #[test]
215 fn migration_sets_user_version() {
216 let db = Database::open_in_memory().unwrap();
217 let version: i32 = db
218 .conn()
219 .query_row("PRAGMA user_version", [], |row| row.get(0))
220 .unwrap();
221 assert_eq!(version, SCHEMA_VERSION);
222 }
223
224 #[test]
225 fn migration_is_idempotent() {
226 let db = Database::open_in_memory().unwrap();
227 // Opening again on the same connection shouldn't fail
228 let version: i32 = db
229 .conn()
230 .query_row("PRAGMA user_version", [], |row| row.get(0))
231 .unwrap();
232 assert_eq!(version, SCHEMA_VERSION);
233 }
234
235 #[test]
236 fn audio_analysis_sync_triggers_carry_all_columns() {
237 // Regression guard for the M018 -> M032 fix: M018 recreated these
238 // triggers with the pre-M011 column list, so edits to the columns below
239 // stopped propagating to sync_changelog (and thus across devices).
240 // Assert the live trigger bodies emit every later-added analysis column.
241 // (classification_confidence was in this list until it was retired.)
242 let db = Database::open_in_memory().unwrap();
243 let later_columns = [
244 "spectral_bandwidth",
245 "centroid_variance",
246 "crest_factor",
247 "attack_time",
248 ];
249 for trigger in ["sync_audio_analysis_insert", "sync_audio_analysis_update"] {
250 let sql: String = db
251 .conn()
252 .query_row(
253 "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = ?1",
254 [trigger],
255 |row| row.get(0),
256 )
257 .unwrap();
258 for col in later_columns {
259 assert!(sql.contains(col), "{trigger} is missing column {col}");
260 }
261 }
262 }
263
264 /// The retired sample-class columns must not come back. They were removed from
265 /// the migration bodies that added them (M003, M011) rather than dropped by a
266 /// later migration, which is only safe while nothing is deployed, so this is
267 /// the guard that the edit stays coherent: absent from the table, and absent
268 /// from the changelog payload a peer would receive.
269 #[test]
270 fn retired_class_columns_are_absent() {
271 let db = Database::open_in_memory().unwrap();
272 let columns: Vec<String> = db
273 .conn()
274 .prepare("SELECT name FROM pragma_table_info('audio_analysis')")
275 .unwrap()
276 .query_map([], |row| row.get(0))
277 .unwrap()
278 .collect::<Result<_, _>>()
279 .unwrap();
280 for retired in ["classification", "classification_confidence"] {
281 assert!(
282 !columns.iter().any(|c| c == retired),
283 "audio_analysis still has {retired}"
284 );
285 for trigger in ["sync_audio_analysis_insert", "sync_audio_analysis_update"] {
286 let sql: String = db
287 .conn()
288 .query_row(
289 "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = ?1",
290 [trigger],
291 |row| row.get(0),
292 )
293 .unwrap();
294 assert!(!sql.contains(retired), "{trigger} still emits {retired}");
295 }
296 }
297 }
298
299 /// Open a fresh file-backed DB, close, reopen. The second open re-enters
300 /// `migrate()`; with `user_version=17` no migration body runs, but the
301 /// shape verifies our open/close cycle is clean (no locks, no WAL leak).
302 #[test]
303 fn migration_replay_from_file_no_op() {
304 let dir = tempfile::tempdir().unwrap();
305 let path = dir.path().join("audiofiles.db");
306
307 let db = Database::open(&path).unwrap();
308 drop(db);
309
310 let db = Database::open(&path).unwrap();
311 let version: i32 = db
312 .conn()
313 .query_row("PRAGMA user_version", [], |row| row.get(0))
314 .unwrap();
315 assert_eq!(version, SCHEMA_VERSION);
316 }
317
318 /// Simulates the worst-case recovery path: a prior partial migration left
319 /// every object in place but `user_version` rolled back. Re-running
320 /// `migrate()` against the pre-populated schema must succeed without
321 /// silent failure. This catches the "silent failure → bump user_version"
322 /// bug class for every migration past the inherently-one-shot ones.
323 ///
324 /// The inherently-one-shot migrations are excluded from this replay
325 /// loop:
326 /// * M001, initial schema; bare CREATE TABLEs, runs against an empty DB.
327 /// * M002, `DROP TABLE tags; ALTER tags_v2 RENAME TO tags` rebuild dance.
328 /// * M015, adds `collections.filter_json` and backfills from
329 /// `smart_folders`, then drops `smart_folders`. The backfill SELECT
330 /// references a table that no longer exists after the migration runs,
331 /// so it cannot parse on replay against a post-M015 schema. None of
332 /// these need replay safety: SQLite's atomic-transaction guarantee
333 /// means each migration either fully commits or fully rolls back, so
334 /// the realistic recovery scenario is "re-apply the one migration
335 /// that crashed", not "re-apply every migration from scratch".
336 ///
337 /// Every migration from M003 onward (excluding M015) MUST be
338 /// replay-safe against a populated schema; if you add a new one that
339 /// isn't, this test fails and you should add `IF NOT EXISTS` /
340 /// `DROP IF EXISTS` / `INSERT OR IGNORE` accordingly, or add it to the
341 /// one-shot list above with a clear rationale.
342 #[test]
343 fn migration_replay_from_version_fifteen_against_full_schema() {
344 let dir = tempfile::tempdir().unwrap();
345 let path = dir.path().join("audiofiles.db");
346
347 Database::open(&path).unwrap();
348
349 {
350 let conn = Connection::open(&path).unwrap();
351 conn.execute_batch("PRAGMA user_version = 15").unwrap();
352 }
353
354 let db = Database::open(&path).unwrap();
355 let version: i32 = db
356 .conn()
357 .query_row("PRAGMA user_version", [], |row| row.get(0))
358 .unwrap();
359 assert_eq!(version, SCHEMA_VERSION);
360 }
361
362 /// `SCHEMA_VERSION` is derived from `MIGRATIONS`, and the migration runner
363 /// keys the version it writes off the same list. Pinning the number here
364 /// means adding a migration without meaning to shows up as a failure.
365 #[test]
366 fn schema_version_matches_the_migration_list() {
367 assert_eq!(SCHEMA_VERSION, 39);
368 assert_eq!(SCHEMA_VERSION as usize, MIGRATIONS.len());
369 }
370
371 /// M037 contract: the browse-list sort must not build a temp B-tree.
372 ///
373 /// Asserting the index exists would be the weaker test, because an index
374 /// SQLite declines to use buys nothing. What actually regressed here was the
375 /// PLAN: `SCAN n` plus `USE TEMP B-TREE FOR ORDER BY` sorted the whole
376 /// library to return 500 rows, which cost 63 ms at 40k nodes against 0.81 ms
377 /// once the sort could be walked from the index. So this pins the plan.
378 ///
379 /// It breaks if someone changes the ORDER BY in `search_global` without
380 /// moving the index with it, which is the failure that would silently
381 /// restore the full sort.
382 #[test]
383 fn m037_browse_sort_uses_the_index_and_not_a_temp_btree() {
384 let db = Database::open_in_memory().unwrap();
385 let plan: Vec<String> = db
386 .conn()
387 .prepare(
388 "EXPLAIN QUERY PLAN
389 SELECT n.id, n.name FROM vfs_nodes n
390 LEFT JOIN audio_analysis a ON n.sample_hash = a.hash
391 LEFT JOIN samples s ON n.sample_hash = s.hash
392 WHERE s.deleted_at IS NULL
393 ORDER BY n.node_type ASC, n.name ASC LIMIT 500",
394 )
395 .unwrap()
396 .query_map([], |row| row.get::<_, String>(3))
397 .unwrap()
398 .collect::<std::result::Result<Vec<_>, _>>()
399 .unwrap();
400 let plan = plan.join("\n");
401
402 assert!(
403 !plan.to_uppercase().contains("TEMP B-TREE"),
404 "browse sort fell back to a full sort:\n{plan}"
405 );
406 assert!(
407 plan.contains("idx_vfs_nodes_sort"),
408 "browse sort is not walking the sort index:\n{plan}"
409 );
410 }
411
412 /// M018 contract: the `sync_changelog.row_id` for sensitive tables must
413 /// be a 64-hex SHA-256 (per `hash_row_id`), NOT the cleartext content
414 /// fingerprint or tag string. The cleartext key lives only in `data`.
415 /// This test is the regression gate for the upload audit fix.
416 #[test]
417 fn m018_hashes_sensitive_row_ids() {
418 let db = Database::open_in_memory().unwrap();
419 let conn = db.conn();
420
421 // Seed: insert a sample and a tag. Both should fire triggers that
422 // write to sync_changelog with a hashed row_id.
423 conn.execute(
424 "INSERT INTO samples (hash, original_name, file_extension, file_size, \
425 import_date, last_modified) VALUES \
426 ('abc123', 'kick.wav', 'wav', 100, 0, 0)",
427 [],
428 )
429 .unwrap();
430 conn.execute(
431 "INSERT INTO tags (sample_hash, tag) VALUES ('abc123', 'drums')",
432 [],
433 )
434 .unwrap();
435
436 // samples row_id: 64-hex hash, NOT "abc123".
437 let row_id: String = conn
438 .query_row(
439 "SELECT row_id FROM sync_changelog WHERE table_name = 'samples' AND op = 'INSERT'",
440 [],
441 |row| row.get(0),
442 )
443 .unwrap();
444 assert_eq!(row_id.len(), 64, "row_id should be SHA-256 hex");
445 assert!(row_id.chars().all(|c| c.is_ascii_hexdigit()));
446 assert_ne!(row_id, "abc123", "cleartext sample hash must not leak");
447
448 // tags row_id: 64-hex hash, NOT "abc123:drums".
449 let row_id: String = conn
450 .query_row(
451 "SELECT row_id FROM sync_changelog WHERE table_name = 'tags' AND op = 'INSERT'",
452 [],
453 |row| row.get(0),
454 )
455 .unwrap();
456 assert_eq!(row_id.len(), 64);
457 assert_ne!(row_id, "abc123:drums", "cleartext tag string must not leak");
458
459 // Salted: hash depends on the per-user salt, so two fresh DBs see
460 // different row_ids for the same logical key.
461 let db2 = Database::open_in_memory().unwrap();
462 let conn2 = db2.conn();
463 conn2
464 .execute(
465 "INSERT INTO samples (hash, original_name, file_extension, file_size, \
466 import_date, last_modified) VALUES \
467 ('abc123', 'kick.wav', 'wav', 100, 0, 0)",
468 [],
469 )
470 .unwrap();
471 let row_id2: String = conn2
472 .query_row(
473 "SELECT row_id FROM sync_changelog WHERE table_name = 'samples' AND op = 'INSERT'",
474 [],
475 |row| row.get(0),
476 )
477 .unwrap();
478 assert_ne!(row_id, row_id2, "salt should differ between DBs");
479 }
480
481 /// M018 contract: DELETE rows must carry the canonical PK in `data` so
482 /// the receiving device's `resolve::apply_delete` can reconstruct the
483 /// WHERE clause without parsing the (now-hashed) row_id.
484 #[test]
485 fn m018_delete_triggers_emit_canonical_key_in_data() {
486 let db = Database::open_in_memory().unwrap();
487 let conn = db.conn();
488
489 conn.execute(
490 "INSERT INTO samples (hash, original_name, file_extension, file_size, \
491 import_date, last_modified) VALUES \
492 ('abc', 'k.wav', 'wav', 1, 0, 0)",
493 [],
494 )
495 .unwrap();
496 conn.execute(
497 "INSERT INTO tags (sample_hash, tag) VALUES ('abc', 'kick')",
498 [],
499 )
500 .unwrap();
501 conn.execute(
502 "DELETE FROM tags WHERE sample_hash = 'abc' AND tag = 'kick'",
503 [],
504 )
505 .unwrap();
506
507 let data: String = conn
508 .query_row(
509 "SELECT data FROM sync_changelog WHERE table_name = 'tags' AND op = 'DELETE'",
510 [],
511 |row| row.get(0),
512 )
513 .unwrap();
514 let parsed: serde_json::Value = serde_json::from_str(&data).unwrap();
515 assert_eq!(parsed["sample_hash"], "abc");
516 assert_eq!(parsed["tag"], "kick");
517 }
518
519 /// M019 contract: `samples.deleted_at` column exists, the partial
520 /// index is in place, and the read-path filter actually hides
521 /// tombstoned rows from `sample_extension` (and by extension every
522 /// other query that uses the `query_sample_field` helper).
523 ///
524 /// This test is the regression gate that proves Phase 1 of the
525 /// tombstone design (docs/design-sample-deletion.md) lands the
526 /// promised infrastructure. Phase 2 will wire the UPDATE path that
527 /// sets deleted_at; today nothing in app code sets it, so every
528 /// query continues to return all rows in practice, but tests can
529 /// set it directly and observe the filter working.
530 #[test]
531 fn m019_tombstone_column_and_read_filter() {
532 let db = Database::open_in_memory().unwrap();
533 let conn = db.conn();
534
535 // Column exists with default NULL.
536 conn.execute(
537 "INSERT INTO samples (hash, original_name, file_extension, file_size, \
538 import_date, last_modified) VALUES \
539 ('live', 'k.wav', 'wav', 1, 0, 0)",
540 [],
541 )
542 .unwrap();
543 conn.execute(
544 "INSERT INTO samples (hash, original_name, file_extension, file_size, \
545 import_date, last_modified) VALUES \
546 ('tomb', 't.wav', 'wav', 1, 0, 0)",
547 [],
548 )
549 .unwrap();
550 conn.execute(
551 "UPDATE samples SET deleted_at = 1700000000 WHERE hash = 'tomb'",
552 [],
553 )
554 .unwrap();
555
556 // sample_extension reads via query_sample_field, which now filters
557 // out tombstoned rows.
558 let live_ext =
559 crate::store::sample_extension(&db, &crate::SampleHash::from_trusted("live")).unwrap();
560 assert_eq!(live_ext, "wav");
561
562 let tomb_ext = crate::store::sample_extension(&db, &crate::SampleHash::from_trusted("tomb"));
563 assert!(
564 matches!(tomb_ext, Err(crate::error::CoreError::SampleNotFound(_))),
565 "tombstoned sample should be hidden from sample_extension; got {tomb_ext:?}"
566 );
567
568 // storage_stats also applies the read-path filter: only the live sample
569 // (file_size 1) is counted, not the tombstoned one.
570 let (count, bytes) = db.storage_stats().unwrap();
571 assert_eq!(count, 1, "tombstoned sample should not be counted");
572 assert_eq!(bytes, 1, "tombstoned sample's bytes should be excluded");
573
574 // Default retain-days seed is present.
575 let retain: String = conn
576 .query_row(
577 "SELECT value FROM user_config WHERE key = 'sample_tombstone_retain_days'",
578 [],
579 |r| r.get(0),
580 )
581 .unwrap();
582 assert_eq!(retain, "30");
583
584 // Partial index exists.
585 let idx_count: i64 = conn
586 .query_row(
587 "SELECT COUNT(*) FROM sqlite_master \
588 WHERE type = 'index' AND name = 'idx_samples_deleted_at'",
589 [],
590 |r| r.get(0),
591 )
592 .unwrap();
593 assert_eq!(idx_count, 1);
594 }
595
596 /// Recovery branch contract: when the non-ALTER batch fails for a
597 /// reason OTHER than "already exists", `migrate()` must roll back and
598 /// surface the error, NOT bump `user_version` past the failed
599 /// migration. Prior behavior was a silent `tracing::warn!` followed by
600 /// a `user_version` bump, which left a partially applied schema
601 /// invisible to future open() calls.
602 ///
603 /// Simulates the failure mode by:
604 /// 1. Bringing the DB up to current version.
605 /// 2. Injecting an inline migration (M999) whose non-ALTER body
606 /// references a non-existent table, AND prepending an ALTER on a
607 /// column that already exists, that's the duplicate-column trip
608 /// wire that funnels execution into the recovery branch.
609 /// 3. Setting user_version back to 18 so the runner attempts M019.
610 /// 4. Asserting migrate() returns Err and user_version stays at 18.
611 ///
612 /// We can't easily inject a new migration into the const array, so we
613 /// drive the recovery branch by calling the runner inline.
614 #[test]
615 fn migrate_recovery_branch_fails_fast_on_non_alter_error() {
616 use rusqlite::Connection;
617
618 let dir = tempfile::tempdir().unwrap();
619 let path = dir.path().join("audiofiles.db");
620 let db = Database::open(&path).unwrap();
621 drop(db);
622
623 // Reopen with a raw Connection so we can hand-craft the recovery
624 // scenario without going through migrate().
625 let conn = Connection::open(&path).unwrap();
626 register_hash_row_id(&conn).unwrap();
627 conn.execute_batch("PRAGMA foreign_keys = ON;").unwrap();
628
629 // Simulate the recovery-branch logic directly: try a migration
630 // batch that fails with "duplicate column" (forcing recovery),
631 // and whose non-ALTER body references a missing table (forcing
632 // the failure that previously got swallowed).
633 let bad_sql = "ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0;\n\
634 INSERT INTO no_such_table_exists (k) VALUES ('x');";
635 let initial_version: i32 = conn
636 .query_row("PRAGMA user_version", [], |row| row.get(0))
637 .unwrap();
638 assert_eq!(initial_version, SCHEMA_VERSION);
639
640 let batch = format!("BEGIN;\n{bad_sql}\nPRAGMA user_version = 999;\nCOMMIT;");
641 let first_err = conn.execute_batch(&batch).unwrap_err();
642 assert!(
643 first_err.to_string().contains("duplicate column"),
644 "expected duplicate-column trip wire, got: {first_err}"
645 );
646
647 // Recovery: ALTER tolerated, non-ALTER must fail loudly.
648 let _ = conn.execute_batch("ROLLBACK");
649 conn.execute_batch("BEGIN").unwrap();
650 // ALTER passes (column exists; tolerated).
651 let alter = "ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0";
652 let alter_res = conn.execute_batch(alter);
653 assert!(alter_res.is_err());
654 assert!(
655 alter_res
656 .unwrap_err()
657 .to_string()
658 .contains("duplicate column")
659 );
660
661 // Non-ALTER: fail-fast, return error, do not bump user_version.
662 let non_alter = "INSERT INTO no_such_table_exists (k) VALUES ('x')";
663 let na_res = conn.execute_batch(non_alter);
664 assert!(na_res.is_err());
665 let msg = na_res.unwrap_err().to_string();
666 assert!(
667 !msg.contains("already exists"),
668 "expected a real failure (no such table), got: {msg}"
669 );
670
671 // The fail-fast path rolls back and never reaches the
672 // user_version bump. Confirm.
673 conn.execute_batch("ROLLBACK").unwrap();
674 let after: i32 = conn
675 .query_row("PRAGMA user_version", [], |row| row.get(0))
676 .unwrap();
677 assert_eq!(
678 after, initial_version,
679 "user_version must not bump when recovery non-ALTER fails for a real reason"
680 );
681 }
682
683 /// Companion to `migration_replay_from_version_fifteen_against_full_schema`:
684 /// rolling user_version back and re-opening must heal to version 18 without
685 /// silent partial-state. Identical setup; kept as a contract-specific name
686 /// so a failing test points the reader at the recovery-branch design rather
687 /// than the broader replay-safety claim.
688 #[test]
689 fn migrate_recovery_branch_tolerates_already_exists() {
690 let dir = tempfile::tempdir().unwrap();
691 let path = dir.path().join("audiofiles.db");
692
693 Database::open(&path).unwrap();
694 {
695 let conn = rusqlite::Connection::open(&path).unwrap();
696 conn.execute_batch("PRAGMA user_version = 15").unwrap();
697 }
698 let db = Database::open(&path).unwrap();
699 let version: i32 = db
700 .conn()
701 .query_row("PRAGMA user_version", [], |row| row.get(0))
702 .unwrap();
703 assert_eq!(version, SCHEMA_VERSION);
704 }
705