smriti/db/
migrations.rs

1//! Database migrations for schema versioning
2
3use rusqlite::{Connection, Result as SqliteResult};
4
5/// The highest schema version this binary knows how to produce. Bump
6/// this in lockstep with each new `migrate_vN_to_vM` (and add the
7/// matching `if current_version < N` line in `run_migrations`).
8///
9/// `run_migrations` refuses to open a DB whose `schema_version` is
10/// higher than this — that would mean a newer build wrote it, and
11/// blindly reading would expose missing tables / columns to old code.
12pub const MAX_KNOWN_SCHEMA_VERSION: i32 = 28;
13
14/// Get the current schema version
15pub fn get_schema_version(conn: &Connection) -> SqliteResult<i32> {
16    let result = conn.query_row("SELECT MAX(version) FROM schema_version", [], |row| {
17        row.get(0)
18    });
19
20    match result {
21        Ok(version) => Ok(version),
22        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(0),
23        Err(e) => Err(e),
24    }
25}
26
27/// Distinct error returned by `run_migrations` when the DB is newer
28/// than this binary supports. Surfaced to the user with a friendlier
29/// message than a generic SQLite error.
30#[derive(Debug)]
31pub struct SchemaTooNewError {
32    pub db_version: i32,
33    pub max_supported: i32,
34}
35
36impl std::fmt::Display for SchemaTooNewError {
37    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
38        write!(
39            f,
40            "Library was created by a newer version of Smriti \
41             (schema v{}). This build only supports up to v{}. \
42             Please update Smriti to open this library.",
43            self.db_version, self.max_supported
44        )
45    }
46}
47
48impl std::error::Error for SchemaTooNewError {}
49
50/// Run any pending migrations
51pub fn run_migrations(conn: &Connection) -> Result<(), Box<dyn std::error::Error>> {
52    let current_version = get_schema_version(conn).unwrap_or(0);
53
54    // Forward-compat guard: don't read schemas this binary doesn't
55    // know about. Better to refuse opening than to silently
56    // mis-interpret unfamiliar columns or miss new tables.
57    if current_version > MAX_KNOWN_SCHEMA_VERSION {
58        return Err(Box::new(SchemaTooNewError {
59            db_version: current_version,
60            max_supported: MAX_KNOWN_SCHEMA_VERSION,
61        }));
62    }
63
64    if current_version < 2 {
65        migrate_v1_to_v2(conn)?;
66    }
67    if current_version < 3 {
68        migrate_v2_to_v3(conn)?;
69    }
70    if current_version < 4 {
71        migrate_v3_to_v4(conn)?;
72    }
73    if current_version < 5 {
74        migrate_v4_to_v5(conn)?;
75    }
76    if current_version < 6 {
77        migrate_v5_to_v6(conn)?;
78    }
79    if current_version < 7 {
80        migrate_v6_to_v7(conn)?;
81    }
82    if current_version < 8 {
83        migrate_v7_to_v8(conn)?;
84    }
85    if current_version < 9 {
86        migrate_v8_to_v9(conn)?;
87    }
88    if current_version < 10 {
89        migrate_v9_to_v10(conn)?;
90    }
91    if current_version < 11 {
92        migrate_v10_to_v11(conn)?;
93    }
94    if current_version < 12 {
95        migrate_v11_to_v12(conn)?;
96    }
97    if current_version < 13 {
98        migrate_v12_to_v13(conn)?;
99    }
100    if current_version < 14 {
101        migrate_v13_to_v14(conn)?;
102    }
103    if current_version < 15 {
104        migrate_v14_to_v15(conn)?;
105    }
106    if current_version < 16 {
107        migrate_v15_to_v16(conn)?;
108    }
109    if current_version < 17 {
110        migrate_v16_to_v17(conn)?;
111    }
112    if current_version < 18 {
113        migrate_v17_to_v18(conn)?;
114    }
115    if current_version < 19 {
116        migrate_v18_to_v19(conn)?;
117    }
118    if current_version < 20 {
119        migrate_v19_to_v20(conn)?;
120    }
121    if current_version < 21 {
122        migrate_v20_to_v21(conn)?;
123    }
124    if current_version < 22 {
125        migrate_v21_to_v22(conn)?;
126    }
127    if current_version < 23 {
128        migrate_v22_to_v23(conn)?;
129    }
130    if current_version < 24 {
131        migrate_v23_to_v24(conn)?;
132    }
133    if current_version < 25 {
134        migrate_v24_to_v25(conn)?;
135    }
136    if current_version < 26 {
137        migrate_v25_to_v26(conn)?;
138    }
139    if current_version < 27 {
140        migrate_v26_to_v27(conn)?;
141    }
142    if current_version < 28 {
143        migrate_v27_to_v28(conn)?;
144    }
145    ensure_performance_indexes(conn)?;
146    let updated_version = get_schema_version(conn).unwrap_or(current_version);
147    tracing::info!("Database at schema version {}", updated_version);
148    Ok(())
149}
150
151fn migrate_v27_to_v28(conn: &Connection) -> SqliteResult<()> {
152    let tx = conn.unchecked_transaction()?;
153    tx.execute_batch(
154        r#"
155        CREATE TABLE IF NOT EXISTS google_takeout_items (
156            content_hash TEXT PRIMARY KEY,
157            file_path TEXT NOT NULL UNIQUE,
158            metadata_json TEXT,
159            imported_at DATETIME DEFAULT CURRENT_TIMESTAMP,
160            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
161        );
162
163        CREATE TABLE IF NOT EXISTS google_takeout_albums (
164            content_hash TEXT NOT NULL,
165            album_name TEXT NOT NULL,
166            PRIMARY KEY (content_hash, album_name),
167            FOREIGN KEY (content_hash) REFERENCES google_takeout_items(content_hash)
168                ON DELETE CASCADE
169        );
170
171        CREATE INDEX IF NOT EXISTS idx_google_takeout_items_path
172            ON google_takeout_items(file_path);
173
174        INSERT INTO schema_version (version) VALUES (28);
175        "#,
176    )?;
177    tx.commit()?;
178    tracing::info!("Migrated database to schema version 28 (Google Takeout imports)");
179    Ok(())
180}
181
182fn ensure_performance_indexes(conn: &Connection) -> SqliteResult<()> {
183    conn.execute_batch(
184        r#"
185        CREATE INDEX IF NOT EXISTS idx_photos_timeline_order
186            ON photos(is_trashed, (date_taken IS NULL), date_taken DESC, id DESC);
187        CREATE INDEX IF NOT EXISTS idx_photos_favorite_order
188            ON photos(is_favorite, is_trashed, (date_taken IS NULL), date_taken DESC, id DESC)
189            WHERE is_favorite = TRUE;
190        CREATE INDEX IF NOT EXISTS idx_photos_gps_bounds
191            ON photos(is_trashed, gps_latitude, gps_longitude)
192            WHERE gps_latitude IS NOT NULL AND gps_longitude IS NOT NULL;
193        CREATE INDEX IF NOT EXISTS idx_faces_review_pending
194            ON faces(user_confirmed, cluster_id, id)
195            WHERE user_confirmed = 0 AND cluster_id IS NOT NULL;
196        "#,
197    )
198}
199
200fn migrate_v26_to_v27(conn: &Connection) -> SqliteResult<()> {
201    let tx = conn.unchecked_transaction()?;
202    tx.execute_batch(
203        r#"
204        CREATE TABLE IF NOT EXISTS albums (
205            id INTEGER PRIMARY KEY,
206            name TEXT NOT NULL,
207            cover_photo_id INTEGER,
208            cover_auto_picked BOOLEAN DEFAULT TRUE,
209            photo_count INTEGER DEFAULT 0,
210            created_by TEXT NOT NULL DEFAULT 'user' CHECK(created_by IN ('user', 'agent')),
211            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
212            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
213            FOREIGN KEY (cover_photo_id) REFERENCES photos(id) ON DELETE SET NULL
214        );
215
216        CREATE TABLE IF NOT EXISTS album_photos (
217            id INTEGER PRIMARY KEY,
218            album_id INTEGER NOT NULL,
219            photo_id INTEGER NOT NULL,
220            added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
221            FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
222            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
223            UNIQUE(album_id, photo_id)
224        );
225
226        CREATE INDEX IF NOT EXISTS idx_album_photos_album ON album_photos(album_id);
227        CREATE INDEX IF NOT EXISTS idx_album_photos_photo ON album_photos(photo_id);
228        "#,
229    )?;
230    match tx.execute(
231        "ALTER TABLE albums ADD COLUMN created_by TEXT NOT NULL DEFAULT 'user' CHECK(created_by IN ('user', 'agent'))",
232        [],
233    ) {
234        Ok(_) => {}
235        Err(e) => {
236            let msg = e.to_string();
237            if !msg.contains("duplicate column") {
238                return Err(e);
239            }
240        }
241    }
242    tx.execute("INSERT INTO schema_version (version) VALUES (27)", [])?;
243    tx.commit()?;
244    tracing::info!("Migrated database to schema version 27 (album provenance)");
245    Ok(())
246}
247
248fn migrate_v25_to_v26(conn: &Connection) -> SqliteResult<()> {
249    let tx = conn.unchecked_transaction()?;
250    tx.execute_batch(
251        r#"
252        CREATE TABLE IF NOT EXISTS semantic_index_state (
253            photo_id INTEGER NOT NULL,
254            model_key TEXT NOT NULL,
255            status TEXT NOT NULL DEFAULT 'pending'
256                CHECK(status IN ('pending', 'indexed', 'failed', 'unsupported')),
257            vector_offset INTEGER,
258            vector_dim INTEGER,
259            attempts INTEGER NOT NULL DEFAULT 0,
260            last_error TEXT,
261            indexed_at DATETIME,
262            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
263            PRIMARY KEY (photo_id, model_key),
264            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
265        );
266
267        CREATE INDEX IF NOT EXISTS idx_semantic_state_status
268            ON semantic_index_state(model_key, status);
269        CREATE INDEX IF NOT EXISTS idx_semantic_state_photo
270            ON semantic_index_state(photo_id);
271
272        INSERT INTO schema_version (version) VALUES (26);
273        "#,
274    )?;
275    tx.commit()?;
276    tracing::info!("Migrated database to schema version 26 (semantic search)");
277    Ok(())
278}
279
280fn migrate_v24_to_v25(conn: &Connection) -> SqliteResult<()> {
281    let tx = conn.unchecked_transaction()?;
282    tx.execute_batch(
283        r#"
284        CREATE TABLE IF NOT EXISTS excluded_folders (
285            relative_path TEXT PRIMARY KEY,
286            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
287        );
288
289        INSERT INTO schema_version (version) VALUES (25);
290        "#,
291    )?;
292    tx.commit()?;
293    tracing::info!("Migrated database to schema version 25 (excluded folders)");
294    Ok(())
295}
296
297fn migrate_v23_to_v24(conn: &Connection) -> SqliteResult<()> {
298    let tx = conn.unchecked_transaction()?;
299    match tx.execute(
300        "ALTER TABLE photos ADD COLUMN is_favorite BOOLEAN DEFAULT FALSE",
301        [],
302    ) {
303        Ok(_) => {}
304        Err(e) => {
305            let msg = e.to_string();
306            if !msg.contains("duplicate column") {
307                return Err(e);
308            }
309        }
310    }
311    tx.execute(
312        "CREATE INDEX IF NOT EXISTS idx_photos_favorite ON photos(is_favorite, date_taken DESC) WHERE is_favorite = TRUE",
313        [],
314    )?;
315    tx.execute("INSERT INTO schema_version (version) VALUES (24)", [])?;
316    tx.commit()?;
317    tracing::info!("Migrated database to schema version 24 (favorites smart album)");
318    Ok(())
319}
320
321fn migrate_v22_to_v23(conn: &Connection) -> SqliteResult<()> {
322    let tx = conn.unchecked_transaction()?;
323    for (col, def) in &[
324        ("media_type", "TEXT NOT NULL DEFAULT 'photo'"),
325        ("duration_ms", "INTEGER"),
326        ("video_codec", "TEXT"),
327        ("audio_codec", "TEXT"),
328        ("frame_rate", "REAL"),
329        ("bitrate", "INTEGER"),
330        ("has_audio", "BOOLEAN DEFAULT FALSE"),
331    ] {
332        let sql = format!("ALTER TABLE photos ADD COLUMN {} {}", col, def);
333        match tx.execute(&sql, []) {
334            Ok(_) => {}
335            Err(e) => {
336                let msg = e.to_string();
337                if !msg.contains("duplicate column") {
338                    return Err(e);
339                }
340            }
341        }
342    }
343    tx.execute(
344        "CREATE INDEX IF NOT EXISTS idx_photos_media_type ON photos(media_type)",
345        [],
346    )?;
347    tx.execute("INSERT INTO schema_version (version) VALUES (23)", [])?;
348    tx.commit()?;
349    tracing::info!("Migrated database to schema version 23 (video media metadata)");
350    Ok(())
351}
352
353fn migrate_v21_to_v22(conn: &Connection) -> SqliteResult<()> {
354    let tx = conn.unchecked_transaction()?;
355    tx.execute_batch(
356        r#"
357        CREATE TABLE IF NOT EXISTS photo_stacks (
358            id INTEGER PRIMARY KEY,
359            kind TEXT NOT NULL,
360            source_group_id INTEGER NOT NULL,
361            source_group_hash TEXT,
362            cover_photo_id INTEGER NOT NULL,
363            confidence REAL NOT NULL DEFAULT 1.0,
364            dismissed BOOLEAN DEFAULT FALSE,
365            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
366            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
367            UNIQUE(kind, source_group_id)
368        );
369
370        CREATE TABLE IF NOT EXISTS photo_stack_members (
371            id INTEGER PRIMARY KEY,
372            stack_id INTEGER NOT NULL,
373            photo_id INTEGER NOT NULL,
374            quality_score REAL NOT NULL DEFAULT 0,
375            score_reasons TEXT,
376            is_cover BOOLEAN DEFAULT FALSE,
377            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
378            FOREIGN KEY (stack_id) REFERENCES photo_stacks(id) ON DELETE CASCADE,
379            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
380            UNIQUE(stack_id, photo_id)
381        );
382
383        CREATE INDEX IF NOT EXISTS idx_photo_stacks_cover ON photo_stacks(cover_photo_id);
384        CREATE INDEX IF NOT EXISTS idx_photo_stacks_active ON photo_stacks(dismissed, kind);
385        CREATE INDEX IF NOT EXISTS idx_photo_stack_members_stack ON photo_stack_members(stack_id);
386        CREATE INDEX IF NOT EXISTS idx_photo_stack_members_photo ON photo_stack_members(photo_id);
387        CREATE INDEX IF NOT EXISTS idx_photo_stack_members_cover ON photo_stack_members(stack_id, is_cover);
388        "#,
389    )?;
390    tx.execute("INSERT INTO schema_version (version) VALUES (22)", [])?;
391    tx.commit()?;
392    tracing::info!("Migrated database to schema version 22 (timeline photo stacks)");
393    Ok(())
394}
395
396fn migrate_v18_to_v19(conn: &Connection) -> SqliteResult<()> {
397    // Streaming scanner pipeline stage flags.
398    // Existing rows: anything inserted by the legacy scanner already
399    // has EXIF and (if successful) a thumbnail. Mark them done so the
400    // new workers don't re-process them.
401    let tx = conn.unchecked_transaction()?;
402    for (col, def) in &[
403        ("metadata_extracted", "BOOLEAN DEFAULT FALSE"),
404        ("thumbnailed", "BOOLEAN DEFAULT FALSE"),
405    ] {
406        let sql = format!("ALTER TABLE photos ADD COLUMN {} {}", col, def);
407        match tx.execute(&sql, []) {
408            Ok(_) => {}
409            Err(e) => {
410                let msg = e.to_string();
411                if !msg.contains("duplicate column") {
412                    return Err(e);
413                }
414            }
415        }
416    }
417    tx.execute(
418        "UPDATE photos SET metadata_extracted = TRUE WHERE date_taken IS NOT NULL OR camera_make IS NOT NULL",
419        [],
420    )?;
421    tx.execute(
422        "UPDATE photos SET thumbnailed = TRUE WHERE thumbnail_path IS NOT NULL",
423        [],
424    )?;
425    tx.execute(
426        "CREATE INDEX IF NOT EXISTS idx_photos_metadata_extracted ON photos(metadata_extracted) WHERE metadata_extracted = FALSE",
427        [],
428    )?;
429    tx.execute(
430        "CREATE INDEX IF NOT EXISTS idx_photos_thumbnailed ON photos(thumbnailed) WHERE thumbnailed = FALSE",
431        [],
432    )?;
433    tx.execute("INSERT INTO schema_version (version) VALUES (19)", [])?;
434    tx.commit()?;
435    tracing::info!("Migrated database to schema version 19 (scanner pipeline stages)");
436    Ok(())
437}
438
439fn migrate_v19_to_v20(conn: &Connection) -> SqliteResult<()> {
440    let tx = conn.unchecked_transaction()?;
441    tx.execute_batch(
442        r#"
443        CREATE TABLE IF NOT EXISTS face_negatives (
444            face_id        INTEGER NOT NULL,
445            not_cluster_id INTEGER NOT NULL,
446            created_at     INTEGER NOT NULL DEFAULT (strftime('%s','now')),
447            PRIMARY KEY (face_id, not_cluster_id),
448            FOREIGN KEY (face_id)        REFERENCES faces(id)         ON DELETE CASCADE,
449            FOREIGN KEY (not_cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE
450        );
451        CREATE INDEX IF NOT EXISTS idx_face_negatives_cluster ON face_negatives(not_cluster_id);
452        INSERT INTO schema_version (version) VALUES (20);
453        "#,
454    )?;
455    tx.commit()?;
456    tracing::info!("Migrated database to schema version 20 (face_negatives)");
457    Ok(())
458}
459
460fn migrate_v20_to_v21(conn: &Connection) -> SqliteResult<()> {
461    let tx = conn.unchecked_transaction()?;
462    tx.execute_batch(
463        r#"
464        CREATE TABLE IF NOT EXISTS face_processing_stats (
465            id                INTEGER PRIMARY KEY CHECK (id = 1),
466            rejected_small    INTEGER NOT NULL DEFAULT 0,
467            rejected_lowconf  INTEGER NOT NULL DEFAULT 0,
468            rejected_blurry   INTEGER NOT NULL DEFAULT 0,
469            rejected_yaw      INTEGER NOT NULL DEFAULT 0,
470            completed_at      INTEGER NOT NULL DEFAULT (strftime('%s','now'))
471        );
472        INSERT INTO schema_version (version) VALUES (21);
473        "#,
474    )?;
475    tx.commit()?;
476    tracing::info!("Migrated database to schema version 21 (face_processing_stats)");
477    Ok(())
478}
479
480fn migrate_v17_to_v18(conn: &Connection) -> SqliteResult<()> {
481    // Perceptual hash for near-duplicate detection. 64-bit DCT phash;
482    // populated lazily by the thumbnail pipeline (see services/thumbnail.rs)
483    // and read by DuplicateDetector::find_perceptual_duplicates.
484    let tx = conn.unchecked_transaction()?;
485    match tx.execute("ALTER TABLE photos ADD COLUMN phash INTEGER", []) {
486        Ok(_) => {}
487        Err(e) => {
488            let msg = e.to_string();
489            if !msg.contains("duplicate column") {
490                return Err(e);
491            }
492        }
493    }
494    tx.execute(
495        "CREATE INDEX IF NOT EXISTS idx_photos_phash ON photos(phash) WHERE phash IS NOT NULL",
496        [],
497    )?;
498    tx.execute("INSERT INTO schema_version (version) VALUES (18)", [])?;
499    tx.commit()?;
500    tracing::info!("Migrated database to schema version 18 (photos.phash)");
501    Ok(())
502}
503
504fn migrate_v16_to_v17(conn: &Connection) -> SqliteResult<()> {
505    // Persist average brightness per photo. Previously face_processor
506    // recomputed it every run; now it's stored, queried via SQL during
507    // contextual identity propagation, and reused across runs.
508    let tx = conn.unchecked_transaction()?;
509    match tx.execute("ALTER TABLE photos ADD COLUMN brightness REAL", []) {
510        Ok(_) => {}
511        Err(e) => {
512            let msg = e.to_string();
513            if !msg.contains("duplicate column") {
514                return Err(e);
515            }
516        }
517    }
518    tx.execute("INSERT INTO schema_version (version) VALUES (17)", [])?;
519    tx.commit()?;
520    tracing::info!("Migrated database to schema version 17 (photos.brightness)");
521    Ok(())
522}
523
524fn migrate_v15_to_v16(conn: &Connection) -> SqliteResult<()> {
525    // Path-string normalization: rewrite stored relative paths to use
526    // forward slashes only. Backslashes from prior Windows writes are
527    // remapped so the same drive opens identically on every OS.
528    let tx = conn.unchecked_transaction()?;
529    tx.execute_batch(
530        r#"
531        UPDATE photos SET file_path = REPLACE(file_path, '\', '/');
532        UPDATE photos SET thumbnail_path = REPLACE(thumbnail_path, '\', '/')
533            WHERE thumbnail_path IS NOT NULL;
534        UPDATE trash SET original_path = REPLACE(original_path, '\', '/');
535
536        INSERT INTO schema_version (version) VALUES (16);
537        "#,
538    )?;
539    tx.commit()?;
540    tracing::info!("Migrated database to schema version 16 (forward-slash paths)");
541    Ok(())
542}
543
544fn migrate_v14_to_v15(conn: &Connection) -> SqliteResult<()> {
545    // Phase 2 Track A4/B2: composite indexes for hot query paths.
546    // Single-column indexes exist for these columns already, but the
547    // combined access pattern (filter-then-sort) hit the table before.
548    conn.execute_batch(
549        r#"
550        CREATE INDEX IF NOT EXISTS idx_photos_trashed_date
551            ON photos(is_trashed, date_taken DESC);
552
553        CREATE INDEX IF NOT EXISTS idx_photos_faces_processed_trashed
554            ON photos(faces_processed, is_trashed, date_taken DESC);
555
556        CREATE INDEX IF NOT EXISTS idx_faces_cluster_confidence
557            ON faces(cluster_id, confidence DESC, id);
558
559        CREATE INDEX IF NOT EXISTS idx_faces_photo_cluster
560            ON faces(photo_id, cluster_id);
561
562        INSERT INTO schema_version (version) VALUES (15);
563        "#,
564    )?;
565    tracing::info!("Migrated database to schema version 15 (composite indexes)");
566    Ok(())
567}
568
569fn migrate_v13_to_v14(conn: &Connection) -> SqliteResult<()> {
570    conn.execute_batch(
571        r#"
572        CREATE TABLE IF NOT EXISTS recent_searches (
573            id INTEGER PRIMARY KEY,
574            query TEXT NOT NULL,
575            last_used DATETIME DEFAULT CURRENT_TIMESTAMP,
576            use_count INTEGER DEFAULT 1,
577            UNIQUE(query)
578        );
579
580        CREATE INDEX IF NOT EXISTS idx_recent_searches_used
581            ON recent_searches(last_used DESC);
582
583        INSERT INTO schema_version (version) VALUES (14);
584        "#,
585    )?;
586    tracing::info!("Migrated database to schema version 14 (recent searches)");
587    Ok(())
588}
589
590fn migrate_v12_to_v13(conn: &Connection) -> SqliteResult<()> {
591    conn.execute_batch(
592        r#"
593        CREATE TABLE IF NOT EXISTS album_suggestions (
594            id INTEGER PRIMARY KEY,
595            kind TEXT NOT NULL,
596            title TEXT NOT NULL,
597            photo_ids_json TEXT NOT NULL,
598            cover_photo_id INTEGER,
599            fingerprint TEXT NOT NULL,
600            status TEXT NOT NULL DEFAULT 'pending',
601            seen_count INTEGER DEFAULT 0,
602            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
603            FOREIGN KEY (cover_photo_id) REFERENCES photos(id) ON DELETE SET NULL
604        );
605
606        CREATE INDEX IF NOT EXISTS idx_album_suggestions_status ON album_suggestions(status);
607        CREATE INDEX IF NOT EXISTS idx_album_suggestions_fingerprint ON album_suggestions(fingerprint);
608
609        INSERT INTO schema_version (version) VALUES (13);
610        "#,
611    )?;
612
613    tracing::info!("Migrated database to schema version 13 (album suggestions)");
614    Ok(())
615}
616
617fn migrate_v11_to_v12(conn: &Connection) -> SqliteResult<()> {
618    conn.execute_batch(
619        r#"
620        CREATE TABLE IF NOT EXISTS albums (
621            id INTEGER PRIMARY KEY,
622            name TEXT NOT NULL,
623            cover_photo_id INTEGER,
624            cover_auto_picked BOOLEAN DEFAULT TRUE,
625            photo_count INTEGER DEFAULT 0,
626            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
627            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
628            FOREIGN KEY (cover_photo_id) REFERENCES photos(id) ON DELETE SET NULL
629        );
630
631        CREATE TABLE IF NOT EXISTS album_photos (
632            id INTEGER PRIMARY KEY,
633            album_id INTEGER NOT NULL,
634            photo_id INTEGER NOT NULL,
635            added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
636            FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
637            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
638            UNIQUE(album_id, photo_id)
639        );
640
641        CREATE INDEX IF NOT EXISTS idx_album_photos_album ON album_photos(album_id);
642        CREATE INDEX IF NOT EXISTS idx_album_photos_photo ON album_photos(photo_id);
643
644        INSERT INTO schema_version (version) VALUES (12);
645        "#,
646    )?;
647
648    tracing::info!("Migrated database to schema version 12 (albums)");
649    Ok(())
650}
651
652fn migrate_v10_to_v11(conn: &Connection) -> SqliteResult<()> {
653    conn.execute_batch(
654        r#"
655        CREATE TABLE IF NOT EXISTS memory_blocks (
656            id INTEGER PRIMARY KEY,
657            kind TEXT NOT NULL,
658            target_key TEXT NOT NULL,
659            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
660            UNIQUE(kind, target_key)
661        );
662
663        CREATE INDEX IF NOT EXISTS idx_memory_blocks_kind ON memory_blocks(kind);
664
665        INSERT INTO schema_version (version) VALUES (11);
666        "#,
667    )?;
668
669    tracing::info!("Migrated database to schema version 11 (memory blocks)");
670    Ok(())
671}
672
673fn migrate_v3_to_v4(conn: &Connection) -> SqliteResult<()> {
674    // Add lens_model, flash, gps_altitude (these were missed in v3).
675    // Wrap in an explicit transaction so a crash mid-loop doesn't leave
676    // some columns added without bumping schema_version.
677    let tx = conn.unchecked_transaction()?;
678    let columns = [
679        ("lens_model", "TEXT"),
680        ("flash", "TEXT"),
681        ("gps_altitude", "REAL"),
682    ];
683
684    for (col, col_type) in &columns {
685        let sql = format!("ALTER TABLE photos ADD COLUMN {} {}", col, col_type);
686        match tx.execute(&sql, []) {
687            Ok(_) => {}
688            Err(e) => {
689                let msg = e.to_string();
690                if !msg.contains("duplicate column") {
691                    return Err(e);
692                }
693            }
694        }
695    }
696
697    tx.execute("INSERT INTO schema_version (version) VALUES (4)", [])?;
698    tx.commit()?;
699    tracing::info!("Migrated database to schema version 4 (lens, flash, altitude)");
700    Ok(())
701}
702
703fn migrate_v5_to_v6(conn: &Connection) -> SqliteResult<()> {
704    // Atomic: ALTER loop + FTS table + triggers + version bump all in
705    // one transaction. Without this, a kill mid-ALTER could land us
706    // with new columns but no FTS index — confusing on next launch.
707    let tx = conn.unchecked_transaction()?;
708
709    let columns = [
710        ("content_category", "TEXT DEFAULT 'photo'"),
711        ("ocr_text", "TEXT"),
712        ("ocr_processed", "BOOLEAN DEFAULT FALSE"),
713        ("ocr_confidence", "REAL"),
714    ];
715
716    for (col, col_type) in &columns {
717        let sql = format!("ALTER TABLE photos ADD COLUMN {} {}", col, col_type);
718        match tx.execute(&sql, []) {
719            Ok(_) => {}
720            Err(e) => {
721                let msg = e.to_string();
722                if !msg.contains("duplicate column") {
723                    return Err(e);
724                }
725            }
726        }
727    }
728
729    tx.execute_batch(
730        r#"
731        CREATE VIRTUAL TABLE IF NOT EXISTS photos_fts USING fts5(
732            ocr_text,
733            content='photos',
734            content_rowid='id'
735        );
736
737        CREATE TRIGGER IF NOT EXISTS photos_fts_insert AFTER INSERT ON photos BEGIN
738            INSERT INTO photos_fts(rowid, ocr_text) VALUES (new.id, COALESCE(new.ocr_text, ''));
739        END;
740
741        CREATE TRIGGER IF NOT EXISTS photos_fts_update AFTER UPDATE OF ocr_text ON photos BEGIN
742            UPDATE photos_fts SET ocr_text = COALESCE(new.ocr_text, '') WHERE rowid = new.id;
743        END;
744
745        CREATE TRIGGER IF NOT EXISTS photos_fts_delete AFTER DELETE ON photos BEGIN
746            DELETE FROM photos_fts WHERE rowid = old.id;
747        END;
748
749        CREATE INDEX IF NOT EXISTS idx_photos_content_category ON photos(content_category);
750        CREATE INDEX IF NOT EXISTS idx_photos_ocr_processed ON photos(ocr_processed);
751
752        INSERT INTO schema_version (version) VALUES (6);
753        "#,
754    )?;
755    tx.commit()?;
756
757    tracing::info!("Migrated database to schema version 6 (documents + OCR fields)");
758    Ok(())
759}
760
761fn migrate_v6_to_v7(conn: &Connection) -> SqliteResult<()> {
762    match conn.execute(
763        "ALTER TABLE face_clusters ADD COLUMN photo_count INTEGER DEFAULT 0",
764        [],
765    ) {
766        Ok(_) => {}
767        Err(e) => {
768            let msg = e.to_string();
769            if !msg.contains("duplicate column") {
770                return Err(e);
771            }
772        }
773    }
774
775    conn.execute_batch(
776        r#"
777        UPDATE face_clusters
778        SET
779            face_count = (SELECT COUNT(*) FROM faces WHERE cluster_id = face_clusters.id),
780            photo_count = (
781                SELECT COUNT(DISTINCT photo_id)
782                FROM (
783                    SELECT photo_id FROM faces WHERE cluster_id = face_clusters.id
784                    UNION
785                    SELECT photo_id FROM photo_inferred_identities WHERE cluster_id = face_clusters.id
786                )
787            ),
788            representative_face_id = (
789                SELECT id
790                FROM faces
791                WHERE cluster_id = face_clusters.id
792                ORDER BY confidence DESC
793                LIMIT 1
794            ),
795            updated_at = CURRENT_TIMESTAMP;
796
797        INSERT INTO schema_version (version) VALUES (7);
798        "#,
799    )?;
800
801    tracing::info!("Migrated database to schema version 7 (face cluster photo counts)");
802    Ok(())
803}
804
805fn migrate_v7_to_v8(conn: &Connection) -> SqliteResult<()> {
806    match conn.execute(
807        "ALTER TABLE photo_inferred_identities ADD COLUMN is_inferred BOOLEAN DEFAULT TRUE",
808        [],
809    ) {
810        Ok(_) => {}
811        Err(e) => {
812            let msg = e.to_string();
813            if !msg.contains("duplicate column") {
814                return Err(e);
815            }
816        }
817    }
818
819    conn.execute_batch(
820        r#"
821        UPDATE photo_inferred_identities
822        SET is_inferred = TRUE
823        WHERE is_inferred IS NULL;
824
825        CREATE INDEX IF NOT EXISTS idx_inferred_is_inferred ON photo_inferred_identities(is_inferred);
826
827        INSERT INTO schema_version (version) VALUES (8);
828        "#,
829    )?;
830
831    tracing::info!("Migrated database to schema version 8 (inferred identity flag)");
832    Ok(())
833}
834
835fn migrate_v9_to_v10(conn: &Connection) -> SqliteResult<()> {
836    let add_col = |sql: &str| -> SqliteResult<()> {
837        match conn.execute(sql, []) {
838            Ok(_) => Ok(()),
839            Err(e) => {
840                let msg = e.to_string();
841                if msg.contains("duplicate column") {
842                    Ok(())
843                } else {
844                    Err(e)
845                }
846            }
847        }
848    };
849
850    add_col("ALTER TABLE faces ADD COLUMN user_confirmed INTEGER DEFAULT 0")?;
851    add_col("ALTER TABLE face_clusters ADD COLUMN is_user_named INTEGER DEFAULT 0")?;
852    add_col("ALTER TABLE person_gallery_embeddings ADD COLUMN source TEXT DEFAULT 'auto'")?;
853
854    conn.execute_batch(
855        r#"
856        CREATE TABLE IF NOT EXISTS cluster_cannot_merge (
857            id INTEGER PRIMARY KEY,
858            cluster_a_id INTEGER NOT NULL,
859            cluster_b_id INTEGER NOT NULL,
860            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
861            FOREIGN KEY (cluster_a_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
862            FOREIGN KEY (cluster_b_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
863            UNIQUE(cluster_a_id, cluster_b_id)
864        );
865
866        CREATE INDEX IF NOT EXISTS idx_cannot_merge_a ON cluster_cannot_merge(cluster_a_id);
867        CREATE INDEX IF NOT EXISTS idx_cannot_merge_b ON cluster_cannot_merge(cluster_b_id);
868
869        CREATE TABLE IF NOT EXISTS face_review_queue (
870            id INTEGER PRIMARY KEY,
871            face_id INTEGER NOT NULL,
872            candidate_cluster_id INTEGER NOT NULL,
873            score REAL NOT NULL,
874            ambiguity REAL,
875            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
876            resolved_at DATETIME,
877            resolved_as TEXT,
878            FOREIGN KEY (face_id) REFERENCES faces(id) ON DELETE CASCADE,
879            FOREIGN KEY (candidate_cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
880            UNIQUE(face_id, candidate_cluster_id)
881        );
882
883        CREATE INDEX IF NOT EXISTS idx_review_queue_face ON face_review_queue(face_id);
884        CREATE INDEX IF NOT EXISTS idx_review_queue_cluster ON face_review_queue(candidate_cluster_id);
885        CREATE INDEX IF NOT EXISTS idx_review_queue_unresolved
886            ON face_review_queue(resolved_at) WHERE resolved_at IS NULL;
887
888        CREATE INDEX IF NOT EXISTS idx_gallery_source ON person_gallery_embeddings(source);
889
890        INSERT INTO schema_version (version) VALUES (10);
891        "#,
892    )?;
893
894    tracing::info!("Migrated database to schema version 10 (face feedback tables)");
895    Ok(())
896}
897
898fn migrate_v8_to_v9(conn: &Connection) -> SqliteResult<()> {
899    conn.execute_batch(
900        r#"
901        CREATE TABLE IF NOT EXISTS person_gallery_embeddings (
902            id INTEGER PRIMARY KEY,
903            cluster_id INTEGER NOT NULL,
904            face_id INTEGER NOT NULL,
905            embedding BLOB NOT NULL,
906            pose_label TEXT,
907            quality_score REAL,
908            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
909
910            FOREIGN KEY (cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
911            FOREIGN KEY (face_id) REFERENCES faces(id) ON DELETE CASCADE,
912            UNIQUE(cluster_id, face_id)
913        );
914
915        CREATE INDEX IF NOT EXISTS idx_gallery_cluster ON person_gallery_embeddings(cluster_id);
916        CREATE INDEX IF NOT EXISTS idx_gallery_face ON person_gallery_embeddings(face_id);
917
918        INSERT INTO schema_version (version) VALUES (9);
919        "#,
920    )?;
921
922    tracing::info!("Migrated database to schema version 9 (person gallery embeddings)");
923    Ok(())
924}
925
926fn migrate_v4_to_v5(conn: &Connection) -> SqliteResult<()> {
927    conn.execute_batch(
928        r#"
929        CREATE TABLE IF NOT EXISTS photo_inferred_identities (
930            id INTEGER PRIMARY KEY,
931            photo_id INTEGER NOT NULL,
932            cluster_id INTEGER NOT NULL,
933            source_photo_id INTEGER NOT NULL,
934            confidence REAL NOT NULL,
935            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
936
937            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
938            FOREIGN KEY (cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
939            FOREIGN KEY (source_photo_id) REFERENCES photos(id) ON DELETE CASCADE,
940            UNIQUE(photo_id, cluster_id)
941        );
942
943        CREATE INDEX IF NOT EXISTS idx_inferred_photo ON photo_inferred_identities(photo_id);
944        CREATE INDEX IF NOT EXISTS idx_inferred_cluster ON photo_inferred_identities(cluster_id);
945
946        INSERT INTO schema_version (version) VALUES (5);
947        "#,
948    )?;
949
950    tracing::info!("Migrated database to schema version 5 (inferred identities)");
951    Ok(())
952}
953
954fn migrate_v2_to_v3(conn: &Connection) -> SqliteResult<()> {
955    // Add EXIF shooting parameters. Wrap the ALTER loop + version
956    // bump in one transaction so partial application can't leave us
957    // with some columns added but schema_version still at 2.
958    let tx = conn.unchecked_transaction()?;
959    let columns = [
960        ("iso", "INTEGER"),
961        ("aperture", "TEXT"),
962        ("shutter_speed", "TEXT"),
963        ("focal_length", "TEXT"),
964        ("lens_model", "TEXT"),
965        ("flash", "TEXT"),
966        ("gps_altitude", "REAL"),
967    ];
968
969    for (col, col_type) in &columns {
970        let sql = format!("ALTER TABLE photos ADD COLUMN {} {}", col, col_type);
971        match tx.execute(&sql, []) {
972            Ok(_) => {}
973            Err(e) => {
974                // Column may already exist if migration was partially applied
975                let msg = e.to_string();
976                if !msg.contains("duplicate column") {
977                    return Err(e);
978                }
979            }
980        }
981    }
982
983    tx.execute("INSERT INTO schema_version (version) VALUES (3)", [])?;
984    tx.commit()?;
985    tracing::info!("Migrated database to schema version 3 (EXIF shooting params)");
986    Ok(())
987}
988
989fn migrate_v1_to_v2(conn: &Connection) -> SqliteResult<()> {
990    conn.execute_batch(
991        r#"
992        CREATE TABLE IF NOT EXISTS trash (
993            id INTEGER PRIMARY KEY,
994            photo_id INTEGER NOT NULL UNIQUE,
995            original_path TEXT NOT NULL,
996            trashed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
997            FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
998        );
999
1000        CREATE INDEX IF NOT EXISTS idx_trash_trashed_at ON trash(trashed_at);
1001        CREATE INDEX IF NOT EXISTS idx_face_clusters_name ON face_clusters(name);
1002
1003        INSERT INTO schema_version (version) VALUES (2);
1004        "#,
1005    )?;
1006
1007    Ok(())
1008}
1009
1010#[cfg(test)]
1011mod tests {
1012    use super::*;
1013    use rusqlite::Connection;
1014
1015    #[test]
1016    fn run_migrations_reports_newer_schema_version() {
1017        let conn = Connection::open_in_memory().unwrap();
1018        conn.execute_batch(
1019            r#"
1020            CREATE TABLE schema_version (
1021                version INTEGER PRIMARY KEY,
1022                applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
1023            );
1024            INSERT INTO schema_version (version) VALUES (999);
1025            "#,
1026        )
1027        .unwrap();
1028
1029        let err = run_migrations(&conn).unwrap_err();
1030        let schema = err
1031            .downcast_ref::<SchemaTooNewError>()
1032            .expect("newer schema should produce SchemaTooNewError");
1033        assert_eq!(schema.db_version, 999);
1034        assert_eq!(schema.max_supported, MAX_KNOWN_SCHEMA_VERSION);
1035    }
1036
1037    #[test]
1038    fn migrate_v27_to_v28_adds_takeout_ledger() {
1039        let conn = Connection::open_in_memory().unwrap();
1040        conn.execute_batch(
1041            r#"
1042            CREATE TABLE schema_version (
1043                version INTEGER PRIMARY KEY,
1044                applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
1045            );
1046            INSERT INTO schema_version (version) VALUES (27);
1047            "#,
1048        )
1049        .unwrap();
1050
1051        migrate_v27_to_v28(&conn).unwrap();
1052        assert_eq!(get_schema_version(&conn).unwrap(), 28);
1053        let table_count: i64 = conn
1054            .query_row(
1055                "SELECT COUNT(*) FROM sqlite_master
1056                 WHERE type = 'table'
1057                   AND name IN ('google_takeout_items', 'google_takeout_albums')",
1058                [],
1059                |row| row.get(0),
1060            )
1061            .unwrap();
1062        assert_eq!(table_count, 2);
1063    }
1064
1065    #[test]
1066    fn run_migrations_adds_timeline_order_index_without_version_bump() {
1067        let conn = Connection::open_in_memory().unwrap();
1068        conn.execute_batch(
1069            r#"
1070            CREATE TABLE schema_version (
1071                version INTEGER PRIMARY KEY,
1072                applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
1073            );
1074            INSERT INTO schema_version (version) VALUES (28);
1075            CREATE TABLE photos (
1076                id INTEGER PRIMARY KEY,
1077                date_taken DATETIME,
1078                gps_latitude REAL,
1079                gps_longitude REAL,
1080                is_favorite BOOLEAN DEFAULT FALSE,
1081                is_trashed BOOLEAN DEFAULT FALSE
1082            );
1083            CREATE TABLE faces (
1084                id INTEGER PRIMARY KEY,
1085                photo_id INTEGER NOT NULL,
1086                cluster_id INTEGER,
1087                user_confirmed INTEGER DEFAULT 0
1088            );
1089            "#,
1090        )
1091        .unwrap();
1092
1093        run_migrations(&conn).unwrap();
1094
1095        let version = get_schema_version(&conn).unwrap();
1096        assert_eq!(version, 28);
1097        let plan = conn
1098            .prepare(
1099                "EXPLAIN QUERY PLAN
1100                 SELECT id FROM photos
1101                 WHERE is_trashed = 0
1102                 ORDER BY date_taken IS NULL ASC, date_taken DESC, id DESC
1103                 LIMIT 50",
1104            )
1105            .unwrap()
1106            .query_map([], |row| row.get::<_, String>(3))
1107            .unwrap()
1108            .collect::<rusqlite::Result<Vec<_>>>()
1109            .unwrap()
1110            .join("\n");
1111        assert!(plan.contains("idx_photos_timeline_order"), "{plan}");
1112        assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
1113        let plan = conn
1114            .prepare(
1115                "EXPLAIN QUERY PLAN
1116                 SELECT id FROM faces
1117                 WHERE cluster_id = 10 AND user_confirmed = 0
1118                 ORDER BY id ASC
1119                 LIMIT 50",
1120            )
1121            .unwrap()
1122            .query_map([], |row| row.get::<_, String>(3))
1123            .unwrap()
1124            .collect::<rusqlite::Result<Vec<_>>>()
1125            .unwrap()
1126            .join("\n");
1127        assert!(plan.contains("idx_faces_review_pending"), "{plan}");
1128        assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
1129        let plan = conn
1130            .prepare(
1131                "EXPLAIN QUERY PLAN
1132                 SELECT id FROM photos
1133                 WHERE is_favorite = TRUE AND is_trashed = 0
1134                 ORDER BY date_taken IS NULL ASC, date_taken DESC, id DESC
1135                 LIMIT 50",
1136            )
1137            .unwrap()
1138            .query_map([], |row| row.get::<_, String>(3))
1139            .unwrap()
1140            .collect::<rusqlite::Result<Vec<_>>>()
1141            .unwrap()
1142            .join("\n");
1143        assert!(plan.contains("idx_photos_favorite_order"), "{plan}");
1144        assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
1145        let plan = conn
1146            .prepare(
1147                "EXPLAIN QUERY PLAN
1148                 SELECT id FROM photos
1149                 WHERE is_trashed = 0
1150                   AND gps_latitude IS NOT NULL
1151                   AND gps_longitude IS NOT NULL
1152                   AND gps_latitude >= 10.0 AND gps_latitude <= 20.0
1153                   AND gps_longitude >= 70.0 AND gps_longitude <= 80.0
1154                 LIMIT 50",
1155            )
1156            .unwrap()
1157            .query_map([], |row| row.get::<_, String>(3))
1158            .unwrap()
1159            .collect::<rusqlite::Result<Vec<_>>>()
1160            .unwrap()
1161            .join("\n");
1162        assert!(plan.contains("idx_photos_gps_bounds"), "{plan}");
1163    }
1164
1165    #[test]
1166    fn test_migrate_v18_to_v19() -> rusqlite::Result<()> {
1167        let conn = Connection::open_in_memory().unwrap();
1168        conn.execute_batch(
1169            r#"
1170            CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied_at DATETIME DEFAULT CURRENT_TIMESTAMP);
1171            INSERT INTO schema_version (version) VALUES (1);
1172
1173            CREATE TABLE IF NOT EXISTS photos (
1174                id INTEGER PRIMARY KEY,
1175                file_path TEXT NOT NULL,
1176                file_name TEXT NOT NULL,
1177                file_hash TEXT NOT NULL,
1178                file_size INTEGER NOT NULL,
1179                file_mtime INTEGER,
1180                date_taken DATETIME,
1181                date_taken_source TEXT,
1182                gps_latitude REAL,
1183                gps_longitude REAL,
1184                location_city TEXT,
1185                location_country TEXT,
1186                camera_make TEXT,
1187                camera_model TEXT,
1188                iso INTEGER,
1189                aperture TEXT,
1190                shutter_speed TEXT,
1191                focal_length TEXT,
1192                lens_model TEXT,
1193                flash TEXT,
1194                gps_altitude REAL,
1195                width INTEGER,
1196                height INTEGER,
1197                orientation INTEGER DEFAULT 1,
1198                thumbnail_path TEXT,
1199                faces_processed BOOLEAN DEFAULT FALSE,
1200                content_category TEXT DEFAULT 'photo',
1201                ocr_text TEXT,
1202                ocr_processed BOOLEAN DEFAULT FALSE,
1203                ocr_confidence REAL,
1204                brightness REAL,
1205                phash INTEGER,
1206                is_trashed BOOLEAN DEFAULT FALSE,
1207                trashed_at DATETIME,
1208                indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
1209                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
1210                UNIQUE(file_path)
1211            );
1212            "#,
1213        ).unwrap();
1214
1215        // Insert a row that already has EXIF data (simulates legacy scanner)
1216        conn.execute(
1217            "INSERT INTO photos (file_path, file_name, file_hash, file_size, date_taken, camera_make, thumbnail_path, faces_processed) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 0)",
1218            rusqlite::params!["/photos/img1.jpg", "img1.jpg", "abc123", 1000000, "2024-01-01T00:00:00Z", "Canon", "thumb/img1.jpg"],
1219        ).unwrap();
1220
1221        // Insert a row with no EXIF and no thumbnail
1222        conn.execute(
1223            "INSERT INTO photos (file_path, file_name, file_hash, file_size, faces_processed) VALUES (?1, ?2, ?3, ?4, 0)",
1224            rusqlite::params!["/photos/img2.png", "img2.png", "def456", 500000],
1225        ).unwrap();
1226
1227        // Advance schema version to 18 (simulate all prior migrations done)
1228        conn.execute("INSERT INTO schema_version (version) VALUES (18)", [])
1229            .unwrap();
1230
1231        // Run v18->v19 migration
1232        migrate_v18_to_v19(&conn).unwrap();
1233
1234        // Verify columns exist
1235        let columns: Vec<String> = conn
1236            .prepare("PRAGMA table_info(photos)")?
1237            .query_map([], |row| row.get::<_, String>(1))?
1238            .collect::<rusqlite::Result<Vec<_>>>()?;
1239        assert!(columns.iter().any(|c| c == "metadata_extracted"));
1240        assert!(columns.iter().any(|c| c == "thumbnailed"));
1241
1242        // Row with EXIF data should have metadata_extracted = TRUE
1243        let meta_flag: bool = conn
1244            .query_row(
1245                "SELECT metadata_extracted FROM photos WHERE file_path = '/photos/img1.jpg'",
1246                [],
1247                |row| row.get(0),
1248            )
1249            .unwrap();
1250        assert!(meta_flag);
1251
1252        // Row without EXIF should have metadata_extracted = FALSE
1253        let meta_flag2: bool = conn
1254            .query_row(
1255                "SELECT metadata_extracted FROM photos WHERE file_path = '/photos/img2.png'",
1256                [],
1257                |row| row.get(0),
1258            )
1259            .unwrap();
1260        assert!(!meta_flag2);
1261
1262        // Row with thumbnail should have thumbnailed = TRUE
1263        let thumb_flag: bool = conn
1264            .query_row(
1265                "SELECT thumbnailed FROM photos WHERE file_path = '/photos/img1.jpg'",
1266                [],
1267                |row| row.get(0),
1268            )
1269            .unwrap();
1270        assert!(thumb_flag);
1271
1272        // Row without thumbnail should have thumbnailed = FALSE
1273        let thumb_flag2: bool = conn
1274            .query_row(
1275                "SELECT thumbnailed FROM photos WHERE file_path = '/photos/img2.png'",
1276                [],
1277                |row| row.get(0),
1278            )
1279            .unwrap();
1280        assert!(!thumb_flag2);
1281
1282        // Verify partial indexes exist
1283        let idx_count: i64 = conn.query_row(
1284            "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name IN ('idx_photos_metadata_extracted', 'idx_photos_thumbnailed')",
1285            [],
1286            |row| row.get(0),
1287        ).unwrap();
1288        assert_eq!(idx_count, 2);
1289        Ok(())
1290    }
1291
1292    #[test]
1293    fn test_migrate_v24_to_v25() {
1294        let conn = Connection::open_in_memory().unwrap();
1295        conn.execute_batch(
1296            r#"
1297            CREATE TABLE schema_version (
1298                version INTEGER PRIMARY KEY,
1299                applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
1300            );
1301            INSERT INTO schema_version (version) VALUES (24);
1302            "#,
1303        )
1304        .unwrap();
1305
1306        migrate_v24_to_v25(&conn).unwrap();
1307
1308        let table_count: i64 = conn
1309            .query_row(
1310                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='excluded_folders'",
1311                [],
1312                |row| row.get(0),
1313            )
1314            .unwrap();
1315        assert_eq!(table_count, 1);
1316
1317        let version = get_schema_version(&conn).unwrap();
1318        assert_eq!(version, 25);
1319    }
1320}
smriti/db/migrations.rs

smriti/db/
migrations.rs
