1use rusqlite::{Connection, Result as SqliteResult};
9
10pub fn create_schema(conn: &Connection) -> SqliteResult<()> {
14 conn.execute_batch(SCHEMA_SQL)?;
15 tracing::info!("Database schema created successfully");
16 Ok(())
17}
18
19const SCHEMA_SQL: &str = r#"
20-- ============================================================
21-- SCHEMA VERSION
22-- ============================================================
23
24CREATE TABLE IF NOT EXISTS schema_version (
25 version INTEGER PRIMARY KEY,
26 applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
27);
28
29INSERT INTO schema_version (version) VALUES (28);
30
31-- ============================================================
32-- PHOTOS TABLE
33-- Core photo metadata
34-- ============================================================
35
36CREATE TABLE IF NOT EXISTS photos (
37 id INTEGER PRIMARY KEY,
38
39 -- File information
40 file_path TEXT NOT NULL, -- Relative path from drive root
41 file_name TEXT NOT NULL,
42 file_hash TEXT NOT NULL, -- SHA256 for duplicate detection
43 file_size INTEGER NOT NULL,
44 file_mtime INTEGER, -- File modification time (unix timestamp)
45
46 -- EXIF metadata
47 date_taken DATETIME, -- From EXIF, fallback to file mtime
48 date_taken_source TEXT, -- 'exif' | 'filename' | 'mtime'
49 gps_latitude REAL,
50 gps_longitude REAL,
51 location_city TEXT, -- Reverse geocoded
52 location_country TEXT, -- Reverse geocoded
53 camera_make TEXT,
54 camera_model TEXT,
55 iso INTEGER, -- ISO sensitivity
56 aperture TEXT, -- e.g. "f/2.8"
57 shutter_speed TEXT, -- e.g. "1/125"
58 focal_length TEXT, -- e.g. "50mm"
59 lens_model TEXT, -- e.g. "iPhone 15 Pro back camera"
60 flash TEXT, -- "Fired" or "Off"
61 gps_altitude REAL, -- meters above/below sea level
62 width INTEGER,
63 height INTEGER,
64 orientation INTEGER DEFAULT 1,
65
66 -- Media metadata
67 media_type TEXT NOT NULL DEFAULT 'photo', -- 'photo' | 'video'
68 duration_ms INTEGER,
69 video_codec TEXT,
70 audio_codec TEXT,
71 frame_rate REAL,
72 bitrate INTEGER,
73 has_audio BOOLEAN DEFAULT FALSE,
74
75 -- Processing state
76 thumbnail_path TEXT, -- Path to cached thumbnail (relative)
77 faces_processed BOOLEAN DEFAULT FALSE,
78 metadata_extracted BOOLEAN DEFAULT FALSE,
79 thumbnailed BOOLEAN DEFAULT FALSE,
80 brightness REAL, -- Average luma in [0,1], cached from face pipeline
81 phash INTEGER, -- 64-bit DCT perceptual hash for near-duplicate detection
82 content_category TEXT DEFAULT 'photo', -- 'photo' | 'document' | 'screenshot' | 'presentation' | 'whiteboard' | 'receipt'
83 ocr_text TEXT,
84 ocr_processed BOOLEAN DEFAULT FALSE,
85 ocr_confidence REAL,
86
87 -- Soft delete
88 is_favorite BOOLEAN DEFAULT FALSE,
89 is_trashed BOOLEAN DEFAULT FALSE,
90 trashed_at DATETIME,
91
92 -- Timestamps
93 indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
94 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
95
96 UNIQUE(file_path)
97);
98
99-- ============================================================
100-- FACES TABLE
101-- Detected faces in photos
102-- ============================================================
103
104CREATE TABLE IF NOT EXISTS faces (
105 id INTEGER PRIMARY KEY,
106 photo_id INTEGER NOT NULL,
107
108 -- Bounding box (normalized 0-1 coordinates)
109 bbox_x REAL NOT NULL,
110 bbox_y REAL NOT NULL,
111 bbox_width REAL NOT NULL,
112 bbox_height REAL NOT NULL,
113
114 -- Face embedding (512-dimensional vector, stored as blob)
115 embedding BLOB NOT NULL,
116
117 -- Clustering
118 cluster_id INTEGER, -- NULL = unassigned
119 confidence REAL, -- Detection confidence
120
121 -- Interactive feedback: 0=untouched, 1=user confirmed, -1=user rejected from a candidate
122 user_confirmed INTEGER DEFAULT 0,
123
124 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
125
126 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
127 FOREIGN KEY (cluster_id) REFERENCES face_clusters(id) ON DELETE SET NULL
128);
129
130-- ============================================================
131-- FACE CLUSTERS TABLE
132-- A cluster represents a person
133-- ============================================================
134
135CREATE TABLE IF NOT EXISTS face_clusters (
136 id INTEGER PRIMARY KEY,
137 name TEXT, -- NULL = unnamed, user sets this
138 representative_face_id INTEGER, -- Best face for this cluster (for display)
139 face_count INTEGER DEFAULT 0,
140 photo_count INTEGER DEFAULT 0,
141
142 -- 1 if the user has explicitly named/confirmed this cluster
143 is_user_named INTEGER DEFAULT 0,
144
145 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
146 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
147
148 FOREIGN KEY (representative_face_id) REFERENCES faces(id) ON DELETE SET NULL
149);
150
151-- ============================================================
152-- INFERRED IDENTITIES
153-- Contextual person links for photos without visible faces
154-- ============================================================
155
156CREATE TABLE IF NOT EXISTS photo_inferred_identities (
157 id INTEGER PRIMARY KEY,
158 photo_id INTEGER NOT NULL,
159 cluster_id INTEGER NOT NULL,
160 source_photo_id INTEGER NOT NULL,
161 confidence REAL NOT NULL,
162 is_inferred BOOLEAN DEFAULT TRUE,
163 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
164
165 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
166 FOREIGN KEY (cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
167 FOREIGN KEY (source_photo_id) REFERENCES photos(id) ON DELETE CASCADE,
168 UNIQUE(photo_id, cluster_id)
169);
170
171CREATE TABLE IF NOT EXISTS person_gallery_embeddings (
172 id INTEGER PRIMARY KEY,
173 cluster_id INTEGER NOT NULL,
174 face_id INTEGER NOT NULL,
175 embedding BLOB NOT NULL,
176 pose_label TEXT,
177 quality_score REAL,
178 -- 'auto' = chosen by diversity sampling, 'user_confirmed' = sticky user decision
179 source TEXT DEFAULT 'auto',
180 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
181
182 FOREIGN KEY (cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
183 FOREIGN KEY (face_id) REFERENCES faces(id) ON DELETE CASCADE,
184 UNIQUE(cluster_id, face_id)
185);
186
187-- ============================================================
188-- INTERACTIVE FEEDBACK
189-- User-driven constraints and review queue for face recognition
190-- ============================================================
191
192CREATE TABLE IF NOT EXISTS cluster_cannot_merge (
193 id INTEGER PRIMARY KEY,
194 cluster_a_id INTEGER NOT NULL,
195 cluster_b_id INTEGER NOT NULL,
196 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
197
198 FOREIGN KEY (cluster_a_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
199 FOREIGN KEY (cluster_b_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
200 UNIQUE(cluster_a_id, cluster_b_id)
201);
202
203CREATE TABLE IF NOT EXISTS face_review_queue (
204 id INTEGER PRIMARY KEY,
205 face_id INTEGER NOT NULL,
206 candidate_cluster_id INTEGER NOT NULL,
207 score REAL NOT NULL, -- top match score (higher = better)
208 ambiguity REAL, -- score margin to 2nd candidate (lower = more ambiguous)
209 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
210 resolved_at DATETIME,
211 resolved_as TEXT, -- 'same' | 'different' | 'skipped'
212
213 FOREIGN KEY (face_id) REFERENCES faces(id) ON DELETE CASCADE,
214 FOREIGN KEY (candidate_cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE,
215 UNIQUE(face_id, candidate_cluster_id)
216);
217
218CREATE TABLE IF NOT EXISTS face_negatives (
219 face_id INTEGER NOT NULL,
220 not_cluster_id INTEGER NOT NULL,
221 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
222 PRIMARY KEY (face_id, not_cluster_id),
223 FOREIGN KEY (face_id) REFERENCES faces(id) ON DELETE CASCADE,
224 FOREIGN KEY (not_cluster_id) REFERENCES face_clusters(id) ON DELETE CASCADE
225);
226
227-- Single-row table: rejection counts from the most recent face-processing
228-- run. Lets people_clustering_diagnostics report "we dropped 12k blurry
229-- faces" after a restart instead of only on the live `complete` event.
230CREATE TABLE IF NOT EXISTS face_processing_stats (
231 id INTEGER PRIMARY KEY CHECK (id = 1),
232 rejected_small INTEGER NOT NULL DEFAULT 0,
233 rejected_lowconf INTEGER NOT NULL DEFAULT 0,
234 rejected_blurry INTEGER NOT NULL DEFAULT 0,
235 rejected_yaw INTEGER NOT NULL DEFAULT 0,
236 completed_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
237);
238
239-- ============================================================
240-- MEMORY BLOCKS
241-- User preferences for the Memories feature: hide memories involving
242-- specific people, or (future) specific date ranges.
243-- ============================================================
244
245CREATE TABLE IF NOT EXISTS memory_blocks (
246 id INTEGER PRIMARY KEY,
247 kind TEXT NOT NULL, -- 'person' (v1); 'date_range' future
248 target_key TEXT NOT NULL, -- cluster_id as string, or "MM-DD..MM-DD"
249 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
250 UNIQUE(kind, target_key)
251);
252
253-- ============================================================
254-- ALBUMS
255-- User-created photo collections
256-- ============================================================
257
258CREATE TABLE IF NOT EXISTS albums (
259 id INTEGER PRIMARY KEY,
260 name TEXT NOT NULL,
261 cover_photo_id INTEGER,
262 cover_auto_picked BOOLEAN DEFAULT TRUE,
263 photo_count INTEGER DEFAULT 0,
264 created_by TEXT NOT NULL DEFAULT 'user' CHECK(created_by IN ('user', 'agent')),
265 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
266 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
267 FOREIGN KEY (cover_photo_id) REFERENCES photos(id) ON DELETE SET NULL
268);
269
270CREATE TABLE IF NOT EXISTS album_photos (
271 id INTEGER PRIMARY KEY,
272 album_id INTEGER NOT NULL,
273 photo_id INTEGER NOT NULL,
274 added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
275 FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
276 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
277 UNIQUE(album_id, photo_id)
278);
279
280-- ============================================================
281-- GOOGLE PHOTOS TAKEOUT
282-- Durable source metadata and album membership for resumable imports.
283-- Originals remain ordinary files under the library root.
284-- ============================================================
285
286CREATE TABLE IF NOT EXISTS google_takeout_items (
287 content_hash TEXT PRIMARY KEY,
288 file_path TEXT NOT NULL UNIQUE,
289 metadata_json TEXT,
290 imported_at DATETIME DEFAULT CURRENT_TIMESTAMP,
291 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
292);
293
294CREATE TABLE IF NOT EXISTS google_takeout_albums (
295 content_hash TEXT NOT NULL,
296 album_name TEXT NOT NULL,
297 PRIMARY KEY (content_hash, album_name),
298 FOREIGN KEY (content_hash) REFERENCES google_takeout_items(content_hash)
299 ON DELETE CASCADE
300);
301
302-- ============================================================
303-- ALBUM SUGGESTIONS
304-- Auto-detected trip/event album proposals
305-- ============================================================
306
307CREATE TABLE IF NOT EXISTS album_suggestions (
308 id INTEGER PRIMARY KEY,
309 kind TEXT NOT NULL, -- 'trip' | 'event'
310 title TEXT NOT NULL,
311 photo_ids_json TEXT NOT NULL, -- JSON array of photo IDs
312 cover_photo_id INTEGER,
313 fingerprint TEXT NOT NULL, -- Hash of sorted photo IDs
314 status TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'accepted' | 'dismissed'
315 seen_count INTEGER DEFAULT 0,
316 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
317 FOREIGN KEY (cover_photo_id) REFERENCES photos(id) ON DELETE SET NULL
318);
319
320-- ============================================================
321-- DUPLICATE GROUPS
322-- Groups of identical or near-identical photos
323-- ============================================================
324
325CREATE TABLE IF NOT EXISTS duplicate_groups (
326 id INTEGER PRIMARY KEY,
327 group_hash TEXT, -- Shared hash
328 duplicate_type TEXT NOT NULL, -- 'exact' | 'perceptual'
329 resolved BOOLEAN DEFAULT FALSE, -- User has dealt with this group
330 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
331);
332
333CREATE TABLE IF NOT EXISTS duplicate_group_members (
334 id INTEGER PRIMARY KEY,
335 group_id INTEGER NOT NULL,
336 photo_id INTEGER NOT NULL,
337 is_suggested_keep BOOLEAN DEFAULT FALSE, -- Our recommendation
338
339 FOREIGN KEY (group_id) REFERENCES duplicate_groups(id) ON DELETE CASCADE,
340 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
341 UNIQUE(group_id, photo_id)
342);
343
344-- ============================================================
345-- BURST GROUPS
346-- Photos taken within seconds of each other
347-- ============================================================
348
349CREATE TABLE IF NOT EXISTS burst_groups (
350 id INTEGER PRIMARY KEY,
351 start_time DATETIME NOT NULL,
352 end_time DATETIME NOT NULL,
353 photo_count INTEGER DEFAULT 0,
354 resolved BOOLEAN DEFAULT FALSE, -- User has reviewed this burst
355 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
356);
357
358CREATE TABLE IF NOT EXISTS burst_group_members (
359 id INTEGER PRIMARY KEY,
360 group_id INTEGER NOT NULL,
361 photo_id INTEGER NOT NULL,
362 sharpness_score REAL, -- Higher = sharper
363 blur_score REAL, -- Lower = less blur
364 face_count INTEGER DEFAULT 0,
365 is_suggested_best BOOLEAN DEFAULT FALSE,
366
367 FOREIGN KEY (group_id) REFERENCES burst_groups(id) ON DELETE CASCADE,
368 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
369 UNIQUE(group_id, photo_id)
370);
371
372-- ============================================================
373-- PHOTO STACKS
374-- Timeline presentation groups for high-confidence related photos
375-- ============================================================
376
377CREATE TABLE IF NOT EXISTS photo_stacks (
378 id INTEGER PRIMARY KEY,
379 kind TEXT NOT NULL, -- 'exact_duplicate' | 'perceptual_duplicate' | 'burst'
380 source_group_id INTEGER NOT NULL, -- duplicate_groups.id or burst_groups.id
381 source_group_hash TEXT,
382 cover_photo_id INTEGER NOT NULL,
383 confidence REAL NOT NULL DEFAULT 1.0,
384 dismissed BOOLEAN DEFAULT FALSE,
385 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
386 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
387 UNIQUE(kind, source_group_id)
388);
389
390CREATE TABLE IF NOT EXISTS photo_stack_members (
391 id INTEGER PRIMARY KEY,
392 stack_id INTEGER NOT NULL,
393 photo_id INTEGER NOT NULL,
394 quality_score REAL NOT NULL DEFAULT 0,
395 score_reasons TEXT,
396 is_cover BOOLEAN DEFAULT FALSE,
397 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
398 FOREIGN KEY (stack_id) REFERENCES photo_stacks(id) ON DELETE CASCADE,
399 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
400 UNIQUE(stack_id, photo_id)
401);
402
403-- ============================================================
404-- TRASH TABLE
405-- Tracks soft-deleted photos for restore/permanent delete
406-- ============================================================
407
408CREATE TABLE IF NOT EXISTS trash (
409 id INTEGER PRIMARY KEY,
410 photo_id INTEGER NOT NULL UNIQUE,
411 original_path TEXT NOT NULL,
412 trashed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
413
414 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
415);
416
417-- ============================================================
418-- OCR SEARCH INDEX
419-- FTS5 index over extracted OCR text
420-- ============================================================
421
422CREATE VIRTUAL TABLE IF NOT EXISTS photos_fts USING fts5(
423 ocr_text,
424 content='photos',
425 content_rowid='id'
426);
427
428CREATE TRIGGER IF NOT EXISTS photos_fts_insert AFTER INSERT ON photos BEGIN
429 INSERT INTO photos_fts(rowid, ocr_text) VALUES (new.id, COALESCE(new.ocr_text, ''));
430END;
431
432CREATE TRIGGER IF NOT EXISTS photos_fts_update AFTER UPDATE OF ocr_text ON photos BEGIN
433 UPDATE photos_fts SET ocr_text = COALESCE(new.ocr_text, '') WHERE rowid = new.id;
434END;
435
436CREATE TRIGGER IF NOT EXISTS photos_fts_delete AFTER DELETE ON photos BEGIN
437 DELETE FROM photos_fts WHERE rowid = old.id;
438END;
439
440-- ============================================================
441-- RECENT SEARCHES
442-- Per-library search history (last N queries)
443-- ============================================================
444
445CREATE TABLE IF NOT EXISTS recent_searches (
446 id INTEGER PRIMARY KEY,
447 query TEXT NOT NULL,
448 last_used DATETIME DEFAULT CURRENT_TIMESTAMP,
449 use_count INTEGER DEFAULT 1,
450 UNIQUE(query)
451);
452
453-- ============================================================
454-- EXCLUDED FOLDERS
455-- Per-library folders skipped by scanner and reindexer
456-- ============================================================
457
458CREATE TABLE IF NOT EXISTS excluded_folders (
459 relative_path TEXT PRIMARY KEY,
460 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
461);
462
463-- ============================================================
464-- SEMANTIC SEARCH
465-- ============================================================
466
467CREATE TABLE IF NOT EXISTS semantic_index_state (
468 photo_id INTEGER NOT NULL,
469 model_key TEXT NOT NULL,
470 status TEXT NOT NULL DEFAULT 'pending'
471 CHECK(status IN ('pending', 'indexed', 'failed', 'unsupported')),
472 vector_offset INTEGER,
473 vector_dim INTEGER,
474 attempts INTEGER NOT NULL DEFAULT 0,
475 last_error TEXT,
476 indexed_at DATETIME,
477 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
478 PRIMARY KEY (photo_id, model_key),
479 FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
480);
481
482-- ============================================================
483-- INDEXES FOR PERFORMANCE
484-- ============================================================
485
486-- Photos
487CREATE INDEX IF NOT EXISTS idx_photos_date ON photos(date_taken);
488CREATE INDEX IF NOT EXISTS idx_photos_hash ON photos(file_hash);
489CREATE INDEX IF NOT EXISTS idx_photos_location ON photos(location_country, location_city);
490CREATE INDEX IF NOT EXISTS idx_photos_gps_bounds
491 ON photos(is_trashed, gps_latitude, gps_longitude)
492 WHERE gps_latitude IS NOT NULL AND gps_longitude IS NOT NULL;
493CREATE INDEX IF NOT EXISTS idx_photos_trashed ON photos(is_trashed);
494CREATE INDEX IF NOT EXISTS idx_photos_path ON photos(file_path);
495CREATE INDEX IF NOT EXISTS idx_photos_content_category ON photos(content_category);
496CREATE INDEX IF NOT EXISTS idx_photos_media_type ON photos(media_type);
497CREATE INDEX IF NOT EXISTS idx_photos_favorite
498 ON photos(is_favorite, date_taken DESC) WHERE is_favorite = TRUE;
499CREATE INDEX IF NOT EXISTS idx_photos_favorite_order
500 ON photos(is_favorite, is_trashed, (date_taken IS NULL), date_taken DESC, id DESC)
501 WHERE is_favorite = TRUE;
502CREATE INDEX IF NOT EXISTS idx_photos_ocr_processed ON photos(ocr_processed);
503CREATE INDEX IF NOT EXISTS idx_photos_metadata_extracted
504 ON photos(metadata_extracted) WHERE metadata_extracted = FALSE;
505CREATE INDEX IF NOT EXISTS idx_photos_thumbnailed
506 ON photos(thumbnailed) WHERE thumbnailed = FALSE;
507CREATE INDEX IF NOT EXISTS idx_photos_trashed_date
508 ON photos(is_trashed, date_taken DESC);
509CREATE INDEX IF NOT EXISTS idx_photos_timeline_order
510 ON photos(is_trashed, (date_taken IS NULL), date_taken DESC, id DESC);
511CREATE INDEX IF NOT EXISTS idx_photos_faces_processed_trashed
512 ON photos(faces_processed, is_trashed, date_taken DESC);
513CREATE INDEX IF NOT EXISTS idx_photos_phash ON photos(phash) WHERE phash IS NOT NULL;
514
515-- Faces
516CREATE INDEX IF NOT EXISTS idx_faces_cluster ON faces(cluster_id);
517CREATE INDEX IF NOT EXISTS idx_faces_photo ON faces(photo_id);
518CREATE INDEX IF NOT EXISTS idx_faces_cluster_confidence
519 ON faces(cluster_id, confidence DESC, id);
520CREATE INDEX IF NOT EXISTS idx_faces_photo_cluster
521 ON faces(photo_id, cluster_id);
522CREATE INDEX IF NOT EXISTS idx_faces_review_pending
523 ON faces(user_confirmed, cluster_id, id)
524 WHERE user_confirmed = 0 AND cluster_id IS NOT NULL;
525CREATE INDEX IF NOT EXISTS idx_face_clusters_name ON face_clusters(name);
526CREATE INDEX IF NOT EXISTS idx_inferred_photo ON photo_inferred_identities(photo_id);
527CREATE INDEX IF NOT EXISTS idx_inferred_cluster ON photo_inferred_identities(cluster_id);
528CREATE INDEX IF NOT EXISTS idx_inferred_is_inferred ON photo_inferred_identities(is_inferred);
529CREATE INDEX IF NOT EXISTS idx_gallery_cluster ON person_gallery_embeddings(cluster_id);
530CREATE INDEX IF NOT EXISTS idx_gallery_face ON person_gallery_embeddings(face_id);
531CREATE INDEX IF NOT EXISTS idx_gallery_source ON person_gallery_embeddings(source);
532CREATE INDEX IF NOT EXISTS idx_cannot_merge_a ON cluster_cannot_merge(cluster_a_id);
533CREATE INDEX IF NOT EXISTS idx_cannot_merge_b ON cluster_cannot_merge(cluster_b_id);
534CREATE INDEX IF NOT EXISTS idx_review_queue_face ON face_review_queue(face_id);
535CREATE INDEX IF NOT EXISTS idx_review_queue_cluster ON face_review_queue(candidate_cluster_id);
536CREATE INDEX IF NOT EXISTS idx_review_queue_unresolved ON face_review_queue(resolved_at)
537 WHERE resolved_at IS NULL;
538CREATE INDEX IF NOT EXISTS idx_face_negatives_cluster ON face_negatives(not_cluster_id);
539CREATE INDEX IF NOT EXISTS idx_memory_blocks_kind ON memory_blocks(kind);
540-- Duplicate and burst group members
541CREATE INDEX IF NOT EXISTS idx_dup_members_group ON duplicate_group_members(group_id);
542CREATE INDEX IF NOT EXISTS idx_dup_members_photo ON duplicate_group_members(photo_id);
543CREATE INDEX IF NOT EXISTS idx_burst_members_group ON burst_group_members(group_id);
544CREATE INDEX IF NOT EXISTS idx_burst_members_photo ON burst_group_members(photo_id);
545CREATE INDEX IF NOT EXISTS idx_photo_stacks_cover ON photo_stacks(cover_photo_id);
546CREATE INDEX IF NOT EXISTS idx_photo_stacks_active ON photo_stacks(dismissed, kind);
547CREATE INDEX IF NOT EXISTS idx_photo_stack_members_stack ON photo_stack_members(stack_id);
548CREATE INDEX IF NOT EXISTS idx_photo_stack_members_photo ON photo_stack_members(photo_id);
549CREATE INDEX IF NOT EXISTS idx_photo_stack_members_cover ON photo_stack_members(stack_id, is_cover);
550CREATE INDEX IF NOT EXISTS idx_trash_trashed_at ON trash(trashed_at);
551
552-- Albums
553CREATE INDEX IF NOT EXISTS idx_album_photos_album ON album_photos(album_id);
554CREATE INDEX IF NOT EXISTS idx_album_photos_photo ON album_photos(photo_id);
555CREATE INDEX IF NOT EXISTS idx_google_takeout_items_path ON google_takeout_items(file_path);
556
557-- Album suggestions
558CREATE INDEX IF NOT EXISTS idx_album_suggestions_status ON album_suggestions(status);
559CREATE INDEX IF NOT EXISTS idx_album_suggestions_fingerprint ON album_suggestions(fingerprint);
560
561-- Recent searches
562CREATE INDEX IF NOT EXISTS idx_recent_searches_used ON recent_searches(last_used DESC);
563
564-- Semantic search
565CREATE INDEX IF NOT EXISTS idx_semantic_state_status
566 ON semantic_index_state(model_key, status);
567CREATE INDEX IF NOT EXISTS idx_semantic_state_photo
568 ON semantic_index_state(photo_id);
569"#;
570
571#[cfg(test)]
572mod tests {
573 use super::*;
574 use rusqlite::Connection;
575
576 #[test]
577 fn test_create_schema() {
578 let conn = Connection::open_in_memory().unwrap();
579 create_schema(&conn).unwrap();
580
581 let count: i32 = conn
583 .query_row(
584 "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='photos'",
585 [],
586 |row| row.get(0),
587 )
588 .unwrap();
589
590 assert_eq!(count, 1);
591
592 let count: i32 = conn
593 .query_row(
594 "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='excluded_folders'",
595 [],
596 |row| row.get(0),
597 )
598 .unwrap();
599
600 assert_eq!(count, 1);
601
602 let plan = conn
603 .prepare(
604 "EXPLAIN QUERY PLAN
605 SELECT id FROM photos
606 WHERE is_trashed = 0
607 ORDER BY date_taken IS NULL ASC, date_taken DESC, id DESC
608 LIMIT 50",
609 )
610 .unwrap()
611 .query_map([], |row| row.get::<_, String>(3))
612 .unwrap()
613 .collect::<rusqlite::Result<Vec<_>>>()
614 .unwrap()
615 .join("\n");
616 assert!(plan.contains("idx_photos_timeline_order"), "{plan}");
617 assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
618
619 let plan = conn
620 .prepare(
621 "EXPLAIN QUERY PLAN
622 SELECT id FROM faces
623 WHERE cluster_id = 10 AND user_confirmed = 0
624 ORDER BY id ASC
625 LIMIT 50",
626 )
627 .unwrap()
628 .query_map([], |row| row.get::<_, String>(3))
629 .unwrap()
630 .collect::<rusqlite::Result<Vec<_>>>()
631 .unwrap()
632 .join("\n");
633 assert!(plan.contains("idx_faces_review_pending"), "{plan}");
634 assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
635
636 let plan = conn
637 .prepare(
638 "EXPLAIN QUERY PLAN
639 SELECT id FROM photos
640 WHERE is_favorite = TRUE AND is_trashed = 0
641 ORDER BY date_taken IS NULL ASC, date_taken DESC, id DESC
642 LIMIT 50",
643 )
644 .unwrap()
645 .query_map([], |row| row.get::<_, String>(3))
646 .unwrap()
647 .collect::<rusqlite::Result<Vec<_>>>()
648 .unwrap()
649 .join("\n");
650 assert!(plan.contains("idx_photos_favorite_order"), "{plan}");
651 assert!(!plan.contains("USE TEMP B-TREE"), "{plan}");
652
653 let plan = conn
654 .prepare(
655 "EXPLAIN QUERY PLAN
656 SELECT id FROM photos
657 WHERE is_trashed = 0
658 AND gps_latitude IS NOT NULL
659 AND gps_longitude IS NOT NULL
660 AND gps_latitude >= 10.0 AND gps_latitude <= 20.0
661 AND gps_longitude >= 70.0 AND gps_longitude <= 80.0
662 LIMIT 50",
663 )
664 .unwrap()
665 .query_map([], |row| row.get::<_, String>(3))
666 .unwrap()
667 .collect::<rusqlite::Result<Vec<_>>>()
668 .unwrap()
669 .join("\n");
670 assert!(plan.contains("idx_photos_gps_bounds"), "{plan}");
671 }
672}