Skip to main content

max / audiofiles

40.0 KB · 1012 lines History Blame Raw
1 //! SQLite database wrapper with versioned migrations for samples, VFS, tags, and analysis tables.
2
3 use std::path::Path;
4
5 use rusqlite::Connection;
6 use thiserror::Error;
7 use tracing::instrument;
8
9 #[derive(Error, Debug)]
10 pub enum DbError {
11 #[error("SQLite error: {0}")]
12 Sqlite(#[from] rusqlite::Error),
13 }
14
15 /// Core database wrapper. All access is synchronous — no async runtime needed,
16 /// safe to use from a CLAP plugin host thread.
17 pub struct Database {
18 conn: Connection,
19 }
20
21 const MIGRATION_001: &str = r#"
22 -- Sample storage and metadata
23 CREATE TABLE samples (
24 hash TEXT PRIMARY KEY,
25 original_name TEXT NOT NULL,
26 file_extension TEXT NOT NULL,
27 file_size INTEGER NOT NULL,
28 import_date INTEGER NOT NULL,
29 last_modified INTEGER NOT NULL
30 );
31
32 -- Audio analysis results
33 CREATE TABLE audio_analysis (
34 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
35 bpm REAL,
36 musical_key TEXT,
37 duration REAL NOT NULL,
38 sample_rate INTEGER NOT NULL,
39 channels INTEGER NOT NULL,
40 peak_db REAL,
41 rms_db REAL,
42 is_loop BOOLEAN,
43 spectral_centroid REAL,
44 onset_strength REAL,
45 analyzed_at INTEGER NOT NULL
46 );
47
48 -- Virtual file systems
49 CREATE TABLE vfs (
50 id INTEGER PRIMARY KEY,
51 name TEXT NOT NULL UNIQUE,
52 created_at INTEGER NOT NULL,
53 modified_at INTEGER NOT NULL
54 );
55
56 -- VFS directory/file nodes
57 CREATE TABLE vfs_nodes (
58 id INTEGER PRIMARY KEY,
59 vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
60 parent_id INTEGER REFERENCES vfs_nodes(id) ON DELETE CASCADE,
61 name TEXT NOT NULL,
62 node_type TEXT NOT NULL CHECK(node_type IN ('directory', 'sample')),
63 sample_hash TEXT REFERENCES samples(hash) ON DELETE CASCADE,
64 created_at INTEGER NOT NULL,
65 UNIQUE(vfs_id, parent_id, name)
66 );
67
68 -- User-defined tags
69 CREATE TABLE tags (
70 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
71 tag_name TEXT NOT NULL,
72 tag_value TEXT NOT NULL,
73 PRIMARY KEY (sample_hash, tag_name, tag_value)
74 );
75
76 -- Collections/playlists
77 CREATE TABLE collections (
78 id INTEGER PRIMARY KEY,
79 name TEXT NOT NULL UNIQUE,
80 description TEXT,
81 created_at INTEGER NOT NULL
82 );
83
84 CREATE TABLE collection_members (
85 collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
86 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
87 added_at INTEGER NOT NULL,
88 PRIMARY KEY (collection_id, sample_hash)
89 );
90
91 -- Smart folders (saved searches)
92 CREATE TABLE smart_folders (
93 id INTEGER PRIMARY KEY,
94 vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
95 name TEXT NOT NULL,
96 query_json TEXT NOT NULL,
97 created_at INTEGER NOT NULL
98 );
99
100 -- Performance indexes
101 CREATE INDEX idx_vfs_nodes_parent ON vfs_nodes(parent_id);
102 CREATE INDEX idx_vfs_nodes_vfs ON vfs_nodes(vfs_id);
103 CREATE INDEX idx_vfs_nodes_hash ON vfs_nodes(sample_hash);
104 CREATE INDEX idx_tags_hash ON tags(sample_hash);
105 CREATE INDEX idx_tags_name_value ON tags(tag_name, tag_value);
106 CREATE INDEX idx_analysis_bpm ON audio_analysis(bpm);
107 CREATE INDEX idx_analysis_key ON audio_analysis(musical_key);
108 "#;
109
110 const MIGRATION_002: &str = r#"
111 CREATE TABLE tags_v2 (
112 sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
113 tag TEXT NOT NULL,
114 PRIMARY KEY (sample_hash, tag)
115 );
116
117 -- Migrate any existing data
118 INSERT OR IGNORE INTO tags_v2 (sample_hash, tag)
119 SELECT sample_hash, LOWER(tag_name || '.' || tag_value) FROM tags;
120
121 DROP TABLE tags;
122 ALTER TABLE tags_v2 RENAME TO tags;
123
124 CREATE INDEX idx_tags_hash ON tags(sample_hash);
125 CREATE INDEX idx_tags_tag ON tags(tag);
126 "#;
127
128 const MIGRATION_003: &str = r#"
129 ALTER TABLE audio_analysis ADD COLUMN lufs REAL;
130 ALTER TABLE audio_analysis ADD COLUMN spectral_flatness REAL;
131 ALTER TABLE audio_analysis ADD COLUMN spectral_rolloff REAL;
132 ALTER TABLE audio_analysis ADD COLUMN zero_crossing_rate REAL;
133 ALTER TABLE audio_analysis ADD COLUMN classification TEXT;
134 "#;
135
136 const MIGRATION_004: &str = r#"
137 CREATE TABLE waveform_data (
138 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
139 num_buckets INTEGER NOT NULL,
140 peak_data BLOB NOT NULL,
141 sample_rate INTEGER NOT NULL,
142 duration REAL NOT NULL,
143 generated_at INTEGER NOT NULL
144 );
145 CREATE INDEX idx_analysis_duration ON audio_analysis(duration);
146 CREATE INDEX idx_analysis_classification ON audio_analysis(classification);
147 CREATE INDEX idx_samples_name ON samples(original_name);
148 "#;
149
150 const MIGRATION_005: &str = r#"
151 CREATE TABLE user_config (key TEXT PRIMARY KEY, value TEXT NOT NULL);
152 "#;
153
154 const MIGRATION_006: &str = r#"
155 CREATE TABLE fingerprints (
156 hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
157 envelope BLOB NOT NULL,
158 sample_rate INTEGER NOT NULL,
159 generated_at INTEGER NOT NULL
160 );
161 "#;
162
163 const MIGRATION_007: &str = r#"
164 -- Per-VFS toggle for syncing audio file blobs to cloud (metadata always syncs)
165 ALTER TABLE vfs ADD COLUMN sync_files INTEGER NOT NULL DEFAULT 0;
166
167 -- Sync metadata key-value store
168 CREATE TABLE sync_state (
169 key TEXT PRIMARY KEY,
170 value TEXT NOT NULL
171 );
172 INSERT INTO sync_state (key, value) VALUES
173 ('device_id', ''),
174 ('pull_cursor', ''),
175 ('auto_sync_enabled', '0'),
176 ('sync_interval_minutes', '15'),
177 ('applying_remote', '0'),
178 ('last_sync_at', ''),
179 ('initial_snapshot_done', '0');
180
181 -- Local change log for push/pull sync
182 CREATE TABLE sync_changelog (
183 id INTEGER PRIMARY KEY AUTOINCREMENT,
184 table_name TEXT NOT NULL,
185 op TEXT NOT NULL,
186 row_id TEXT NOT NULL,
187 timestamp TEXT NOT NULL DEFAULT (datetime('now')),
188 data TEXT,
189 pushed INTEGER NOT NULL DEFAULT 0
190 );
191 CREATE INDEX idx_changelog_pushed ON sync_changelog(pushed);
192
193 -- ── Triggers: record changes unless applying remote data ──
194
195 -- samples
196 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
197 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
198 BEGIN
199 INSERT INTO sync_changelog (table_name, op, row_id, data)
200 VALUES ('samples', 'INSERT', NEW.hash,
201 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
202 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
203 'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
204 END;
205
206 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
207 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
208 BEGIN
209 INSERT INTO sync_changelog (table_name, op, row_id, data)
210 VALUES ('samples', 'UPDATE', NEW.hash,
211 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
212 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
213 'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
214 END;
215
216 CREATE TRIGGER sync_samples_delete AFTER DELETE ON samples
217 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
218 BEGIN
219 INSERT INTO sync_changelog (table_name, op, row_id, data)
220 VALUES ('samples', 'DELETE', OLD.hash, NULL);
221 END;
222
223 -- audio_analysis
224 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
225 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
226 BEGIN
227 INSERT INTO sync_changelog (table_name, op, row_id, data)
228 VALUES ('audio_analysis', 'INSERT', NEW.hash,
229 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
230 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
231 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
232 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
233 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
234 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
235 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
236 END;
237
238 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
239 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
240 BEGIN
241 INSERT INTO sync_changelog (table_name, op, row_id, data)
242 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
243 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
244 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
245 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
246 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
247 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
248 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
249 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
250 END;
251
252 CREATE TRIGGER sync_audio_analysis_delete AFTER DELETE ON audio_analysis
253 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
254 BEGIN
255 INSERT INTO sync_changelog (table_name, op, row_id, data)
256 VALUES ('audio_analysis', 'DELETE', OLD.hash, NULL);
257 END;
258
259 -- vfs
260 CREATE TRIGGER sync_vfs_insert AFTER INSERT ON vfs
261 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
262 BEGIN
263 INSERT INTO sync_changelog (table_name, op, row_id, data)
264 VALUES ('vfs', 'INSERT', CAST(NEW.id AS TEXT),
265 json_object('id', NEW.id, 'name', NEW.name,
266 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
267 'sync_files', NEW.sync_files));
268 END;
269
270 CREATE TRIGGER sync_vfs_update AFTER UPDATE ON vfs
271 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
272 BEGIN
273 INSERT INTO sync_changelog (table_name, op, row_id, data)
274 VALUES ('vfs', 'UPDATE', CAST(NEW.id AS TEXT),
275 json_object('id', NEW.id, 'name', NEW.name,
276 'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
277 'sync_files', NEW.sync_files));
278 END;
279
280 CREATE TRIGGER sync_vfs_delete AFTER DELETE ON vfs
281 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
282 BEGIN
283 INSERT INTO sync_changelog (table_name, op, row_id, data)
284 VALUES ('vfs', 'DELETE', CAST(OLD.id AS TEXT), NULL);
285 END;
286
287 -- vfs_nodes
288 CREATE TRIGGER sync_vfs_nodes_insert AFTER INSERT ON vfs_nodes
289 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
290 BEGIN
291 INSERT INTO sync_changelog (table_name, op, row_id, data)
292 VALUES ('vfs_nodes', 'INSERT', CAST(NEW.id AS TEXT),
293 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
294 'name', NEW.name, 'node_type', NEW.node_type,
295 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
296 END;
297
298 CREATE TRIGGER sync_vfs_nodes_update AFTER UPDATE ON vfs_nodes
299 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
300 BEGIN
301 INSERT INTO sync_changelog (table_name, op, row_id, data)
302 VALUES ('vfs_nodes', 'UPDATE', CAST(NEW.id AS TEXT),
303 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
304 'name', NEW.name, 'node_type', NEW.node_type,
305 'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
306 END;
307
308 CREATE TRIGGER sync_vfs_nodes_delete AFTER DELETE ON vfs_nodes
309 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
310 BEGIN
311 INSERT INTO sync_changelog (table_name, op, row_id, data)
312 VALUES ('vfs_nodes', 'DELETE', CAST(OLD.id AS TEXT), NULL);
313 END;
314
315 -- tags
316 CREATE TRIGGER sync_tags_insert AFTER INSERT ON tags
317 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
318 BEGIN
319 INSERT INTO sync_changelog (table_name, op, row_id, data)
320 VALUES ('tags', 'INSERT', NEW.sample_hash || ':' || NEW.tag,
321 json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag));
322 END;
323
324 CREATE TRIGGER sync_tags_delete AFTER DELETE ON tags
325 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
326 BEGIN
327 INSERT INTO sync_changelog (table_name, op, row_id, data)
328 VALUES ('tags', 'DELETE', OLD.sample_hash || ':' || OLD.tag, NULL);
329 END;
330
331 -- collections
332 CREATE TRIGGER sync_collections_insert AFTER INSERT ON collections
333 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
334 BEGIN
335 INSERT INTO sync_changelog (table_name, op, row_id, data)
336 VALUES ('collections', 'INSERT', CAST(NEW.id AS TEXT),
337 json_object('id', NEW.id, 'name', NEW.name,
338 'description', NEW.description, 'created_at', NEW.created_at));
339 END;
340
341 CREATE TRIGGER sync_collections_update AFTER UPDATE ON collections
342 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
343 BEGIN
344 INSERT INTO sync_changelog (table_name, op, row_id, data)
345 VALUES ('collections', 'UPDATE', CAST(NEW.id AS TEXT),
346 json_object('id', NEW.id, 'name', NEW.name,
347 'description', NEW.description, 'created_at', NEW.created_at));
348 END;
349
350 CREATE TRIGGER sync_collections_delete AFTER DELETE ON collections
351 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
352 BEGIN
353 INSERT INTO sync_changelog (table_name, op, row_id, data)
354 VALUES ('collections', 'DELETE', CAST(OLD.id AS TEXT), NULL);
355 END;
356
357 -- collection_members
358 CREATE TRIGGER sync_collection_members_insert AFTER INSERT ON collection_members
359 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
360 BEGIN
361 INSERT INTO sync_changelog (table_name, op, row_id, data)
362 VALUES ('collection_members', 'INSERT',
363 CAST(NEW.collection_id AS TEXT) || ':' || NEW.sample_hash,
364 json_object('collection_id', NEW.collection_id, 'sample_hash', NEW.sample_hash,
365 'added_at', NEW.added_at));
366 END;
367
368 CREATE TRIGGER sync_collection_members_delete AFTER DELETE ON collection_members
369 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
370 BEGIN
371 INSERT INTO sync_changelog (table_name, op, row_id, data)
372 VALUES ('collection_members', 'DELETE',
373 CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash, NULL);
374 END;
375
376 -- smart_folders
377 CREATE TRIGGER sync_smart_folders_insert AFTER INSERT ON smart_folders
378 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
379 BEGIN
380 INSERT INTO sync_changelog (table_name, op, row_id, data)
381 VALUES ('smart_folders', 'INSERT', CAST(NEW.id AS TEXT),
382 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'name', NEW.name,
383 'query_json', NEW.query_json, 'created_at', NEW.created_at));
384 END;
385
386 CREATE TRIGGER sync_smart_folders_update AFTER UPDATE ON smart_folders
387 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
388 BEGIN
389 INSERT INTO sync_changelog (table_name, op, row_id, data)
390 VALUES ('smart_folders', 'UPDATE', CAST(NEW.id AS TEXT),
391 json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'name', NEW.name,
392 'query_json', NEW.query_json, 'created_at', NEW.created_at));
393 END;
394
395 CREATE TRIGGER sync_smart_folders_delete AFTER DELETE ON smart_folders
396 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
397 BEGIN
398 INSERT INTO sync_changelog (table_name, op, row_id, data)
399 VALUES ('smart_folders', 'DELETE', CAST(OLD.id AS TEXT), NULL);
400 END;
401
402 -- user_config (exclude sync-internal keys)
403 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
404 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
405 AND NEW.key NOT LIKE 'sync_%'
406 BEGIN
407 INSERT INTO sync_changelog (table_name, op, row_id, data)
408 VALUES ('user_config', 'INSERT', NEW.key,
409 json_object('key', NEW.key, 'value', NEW.value));
410 END;
411
412 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
413 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
414 AND NEW.key NOT LIKE 'sync_%'
415 BEGIN
416 INSERT INTO sync_changelog (table_name, op, row_id, data)
417 VALUES ('user_config', 'UPDATE', NEW.key,
418 json_object('key', NEW.key, 'value', NEW.value));
419 END;
420
421 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
422 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
423 AND OLD.key NOT LIKE 'sync_%'
424 BEGIN
425 INSERT INTO sync_changelog (table_name, op, row_id, data)
426 VALUES ('user_config', 'DELETE', OLD.key, NULL);
427 END;
428 "#;
429
430 const MIGRATION_008: &str = r#"
431 -- cloud_only: 1 when the local blob has been deleted but exists in cloud storage
432 ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0;
433
434 -- Recreate samples triggers to include cloud_only in the JSON data
435 DROP TRIGGER IF EXISTS sync_samples_insert;
436 DROP TRIGGER IF EXISTS sync_samples_update;
437
438 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
439 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
440 BEGIN
441 INSERT INTO sync_changelog (table_name, op, row_id, data)
442 VALUES ('samples', 'INSERT', NEW.hash,
443 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
444 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
445 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
446 'cloud_only', NEW.cloud_only));
447 END;
448
449 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
450 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
451 BEGIN
452 INSERT INTO sync_changelog (table_name, op, row_id, data)
453 VALUES ('samples', 'UPDATE', NEW.hash,
454 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
455 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
456 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
457 'cloud_only', NEW.cloud_only));
458 END;
459 "#;
460
461 const MIGRATION_009: &str = r#"
462 -- Duration on samples table so it's available immediately after import (before analysis).
463 ALTER TABLE samples ADD COLUMN duration REAL;
464
465 -- Recreate samples triggers to include duration in the JSON data
466 DROP TRIGGER IF EXISTS sync_samples_insert;
467 DROP TRIGGER IF EXISTS sync_samples_update;
468
469 CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
470 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
471 BEGIN
472 INSERT INTO sync_changelog (table_name, op, row_id, data)
473 VALUES ('samples', 'INSERT', NEW.hash,
474 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
475 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
476 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
477 'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
478 END;
479
480 CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
481 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
482 BEGIN
483 INSERT INTO sync_changelog (table_name, op, row_id, data)
484 VALUES ('samples', 'UPDATE', NEW.hash,
485 json_object('hash', NEW.hash, 'original_name', NEW.original_name,
486 'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
487 'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
488 'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
489 END;
490 "#;
491
492 const MIGRATION_010: &str = r#"
493 -- New spectral and waveform features for improved classification
494 ALTER TABLE audio_analysis ADD COLUMN spectral_bandwidth REAL;
495 ALTER TABLE audio_analysis ADD COLUMN centroid_variance REAL;
496 ALTER TABLE audio_analysis ADD COLUMN crest_factor REAL;
497 ALTER TABLE audio_analysis ADD COLUMN attack_time REAL;
498
499 -- Recreate audio_analysis sync triggers to include new columns
500 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
501 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
502
503 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
504 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
505 BEGIN
506 INSERT INTO sync_changelog (table_name, op, row_id, data)
507 VALUES ('audio_analysis', 'INSERT', NEW.hash,
508 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
509 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
510 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
511 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
512 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
513 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
514 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
515 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
516 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
517 END;
518
519 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
520 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
521 BEGIN
522 INSERT INTO sync_changelog (table_name, op, row_id, data)
523 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
524 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
525 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
526 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
527 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
528 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
529 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
530 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
531 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
532 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
533 END;
534 "#;
535
536 const MIGRATION_011: &str = r#"
537 -- ML classifier confidence score
538 ALTER TABLE audio_analysis ADD COLUMN classification_confidence REAL;
539
540 -- Recreate audio_analysis sync triggers to include new column
541 DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
542 DROP TRIGGER IF EXISTS sync_audio_analysis_update;
543
544 CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
545 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
546 BEGIN
547 INSERT INTO sync_changelog (table_name, op, row_id, data)
548 VALUES ('audio_analysis', 'INSERT', NEW.hash,
549 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
550 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
551 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
552 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
553 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
554 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
555 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
556 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
557 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time,
558 'classification_confidence', NEW.classification_confidence));
559 END;
560
561 CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
562 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
563 BEGIN
564 INSERT INTO sync_changelog (table_name, op, row_id, data)
565 VALUES ('audio_analysis', 'UPDATE', NEW.hash,
566 json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
567 'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
568 'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
569 'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
570 'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
571 'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
572 'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
573 'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
574 'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time,
575 'classification_confidence', NEW.classification_confidence));
576 END;
577 "#;
578
579 const MIGRATION_012: &str = r#"
580 -- Edit history: tracks destructive edits for future undo support
581 CREATE TABLE IF NOT EXISTS edit_history (
582 id INTEGER PRIMARY KEY AUTOINCREMENT,
583 source_hash TEXT NOT NULL,
584 result_hash TEXT NOT NULL,
585 operation TEXT NOT NULL,
586 params_json TEXT,
587 created_at INTEGER NOT NULL DEFAULT (unixepoch())
588 );
589 CREATE INDEX idx_edit_history_source ON edit_history(source_hash);
590 CREATE INDEX idx_edit_history_result ON edit_history(result_hash);
591
592 -- Sync trigger for edit_history
593 CREATE TRIGGER sync_edit_history_insert AFTER INSERT ON edit_history
594 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
595 BEGIN
596 INSERT INTO sync_changelog (table_name, op, row_id, data)
597 VALUES ('edit_history', 'INSERT', CAST(NEW.id AS TEXT),
598 json_object('id', NEW.id, 'source_hash', NEW.source_hash,
599 'result_hash', NEW.result_hash, 'operation', NEW.operation,
600 'params_json', NEW.params_json, 'created_at', NEW.created_at));
601 END;
602 "#;
603
604 const MIGRATION_013: &str = r#"
605 -- Loose-files mode: remember original file path instead of copying into vault.
606 -- NULL = normal (blob in samples/), non-NULL = loose-files (blob at this path).
607 -- Intentionally excluded from sync triggers — source_path is device-local.
608 ALTER TABLE samples ADD COLUMN source_path TEXT;
609 "#;
610
611 const MIGRATION_014: &str = r#"
612 -- Prevent duplicate root-level VFS node names. The existing UNIQUE(vfs_id, parent_id, name)
613 -- constraint treats NULLs as distinct, so root nodes (parent_id IS NULL) could collide.
614 CREATE UNIQUE INDEX IF NOT EXISTS idx_vfs_nodes_root_unique
615 ON vfs_nodes(vfs_id, name) WHERE parent_id IS NULL;
616 "#;
617
618 const MIGRATION_015: &str = r#"
619 -- Merge smart folders into collections: add a filter_json column.
620 -- NULL filter_json = manual collection, non-NULL = dynamic (saved search).
621 ALTER TABLE collections ADD COLUMN filter_json TEXT;
622 -- Migrate existing smart folders into collections with their filters.
623 INSERT OR IGNORE INTO collections (name, description, created_at, filter_json)
624 SELECT name, NULL, created_at, query_json FROM smart_folders;
625 -- Drop the smart_folders table (triggers first, then table).
626 DROP TRIGGER IF EXISTS sync_smart_folders_insert;
627 DROP TRIGGER IF EXISTS sync_smart_folders_update;
628 DROP TRIGGER IF EXISTS sync_smart_folders_delete;
629 DROP TABLE IF EXISTS smart_folders;
630 "#;
631
632 const MIGRATION_016: &str = r#"
633 -- Exclude loose-files mode from sync: a compromised server or second device
634 -- should not be able to silently flip a security-relevant setting.
635 DROP TRIGGER IF EXISTS sync_user_config_insert;
636 DROP TRIGGER IF EXISTS sync_user_config_update;
637 DROP TRIGGER IF EXISTS sync_user_config_delete;
638
639 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
640 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
641 AND NEW.key NOT LIKE 'sync_%'
642 AND NEW.key != 'unsafe_mode'
643 BEGIN
644 INSERT INTO sync_changelog (table_name, op, row_id, data)
645 VALUES ('user_config', 'INSERT', NEW.key,
646 json_object('key', NEW.key, 'value', NEW.value));
647 END;
648
649 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
650 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
651 AND NEW.key NOT LIKE 'sync_%'
652 AND NEW.key != 'unsafe_mode'
653 BEGIN
654 INSERT INTO sync_changelog (table_name, op, row_id, data)
655 VALUES ('user_config', 'UPDATE', NEW.key,
656 json_object('key', NEW.key, 'value', NEW.value));
657 END;
658
659 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
660 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
661 AND OLD.key NOT LIKE 'sync_%'
662 AND OLD.key != 'unsafe_mode'
663 BEGIN
664 INSERT INTO sync_changelog (table_name, op, row_id, data)
665 VALUES ('user_config', 'DELETE', OLD.key, NULL);
666 END;
667 "#;
668
669 const MIGRATION_017: &str = r#"
670 -- Schema-only half of the 'unsafe_mode' -> 'loose_files' rename.
671 -- Recreates the sync-exclusion triggers to reference the new key literal
672 -- in their WHEN clauses (triggers can't parameterize key names, so the
673 -- rewrite has to live in a migration). The runtime row-copy
674 -- (unsafe_mode value -> loose_files row) lives in main.rs at the
675 -- vault-open path; doing it there avoids running it against every
676 -- attached/auxiliary DB that goes through migrate().
677 DROP TRIGGER IF EXISTS sync_user_config_insert;
678 DROP TRIGGER IF EXISTS sync_user_config_update;
679 DROP TRIGGER IF EXISTS sync_user_config_delete;
680
681 CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
682 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
683 AND NEW.key NOT LIKE 'sync_%'
684 AND NEW.key != 'loose_files'
685 BEGIN
686 INSERT INTO sync_changelog (table_name, op, row_id, data)
687 VALUES ('user_config', 'INSERT', NEW.key,
688 json_object('key', NEW.key, 'value', NEW.value));
689 END;
690
691 CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
692 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
693 AND NEW.key NOT LIKE 'sync_%'
694 AND NEW.key != 'loose_files'
695 BEGIN
696 INSERT INTO sync_changelog (table_name, op, row_id, data)
697 VALUES ('user_config', 'UPDATE', NEW.key,
698 json_object('key', NEW.key, 'value', NEW.value));
699 END;
700
701 CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
702 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
703 AND OLD.key NOT LIKE 'sync_%'
704 AND OLD.key != 'loose_files'
705 BEGIN
706 INSERT INTO sync_changelog (table_name, op, row_id, data)
707 VALUES ('user_config', 'DELETE', OLD.key, NULL);
708 END;
709 "#;
710
711 impl Database {
712 /// Open (or create) the database at the given path and run migrations.
713 #[instrument(skip_all)]
714 pub fn open(path: impl AsRef<Path>) -> Result<Self, DbError> {
715 let conn = Connection::open(path)?;
716 conn.execute_batch(
717 "PRAGMA journal_mode=WAL;\
718 PRAGMA foreign_keys=ON;\
719 PRAGMA busy_timeout=5000;\
720 PRAGMA wal_checkpoint(TRUNCATE);",
721 )?;
722 let mut db = Self { conn };
723 db.migrate()?;
724 Ok(db)
725 }
726
727 /// Flush the WAL back into the main database file and remove the -shm file.
728 ///
729 /// Call after large write batches (e.g. import completion) to keep the
730 /// WAL index fresh and avoid stale memory-mapped state on macOS.
731 pub fn wal_checkpoint(&self) -> Result<(), DbError> {
732 self.conn
733 .execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
734 Ok(())
735 }
736
737 /// Open an in-memory database (for tests).
738 #[instrument(skip_all)]
739 pub fn open_in_memory() -> Result<Self, DbError> {
740 let conn = Connection::open_in_memory()?;
741 conn.execute_batch("PRAGMA foreign_keys=ON;")?;
742 let mut db = Self { conn };
743 db.migrate()?;
744 Ok(db)
745 }
746
747 /// Apply pending migrations using PRAGMA user_version as the version tracker.
748 ///
749 /// Each migration step runs inside a transaction so the schema change and
750 /// version bump are atomic — a crash between the two can no longer leave the
751 /// database in an inconsistent state.
752 #[instrument(skip_all)]
753 fn migrate(&mut self) -> Result<(), DbError> {
754 let version: i32 =
755 self.conn
756 .query_row("PRAGMA user_version", [], |row| row.get(0))?;
757
758 const MIGRATIONS: &[&str] = &[
759 MIGRATION_001,
760 MIGRATION_002,
761 MIGRATION_003,
762 MIGRATION_004,
763 MIGRATION_005,
764 MIGRATION_006,
765 MIGRATION_007,
766 MIGRATION_008,
767 MIGRATION_009,
768 MIGRATION_010,
769 MIGRATION_011,
770 MIGRATION_012,
771 MIGRATION_013,
772 MIGRATION_014,
773 MIGRATION_015,
774 MIGRATION_016,
775 MIGRATION_017,
776 ];
777
778 for (i, sql) in MIGRATIONS.iter().enumerate() {
779 let target = (i + 1) as i32;
780 if version < target {
781 let batch = format!("BEGIN;\n{}\nPRAGMA user_version = {};\nCOMMIT;", sql, target);
782 match self.conn.execute_batch(&batch) {
783 Ok(()) => {}
784 Err(e) if e.to_string().contains("duplicate column") => {
785 // Partial prior migration left some columns already added.
786 // Re-run each ALTER TABLE individually, skipping duplicates.
787 let _ = self.conn.execute_batch("ROLLBACK");
788 self.conn.execute_batch("BEGIN")?;
789 for line in sql.lines() {
790 let trimmed = line.trim();
791 if trimmed.to_uppercase().starts_with("ALTER TABLE")
792 && trimmed.to_uppercase().contains("ADD COLUMN")
793 {
794 if let Err(alter_err) = self.conn.execute_batch(trimmed) {
795 if !alter_err.to_string().contains("duplicate column") {
796 let _ = self.conn.execute_batch("ROLLBACK");
797 return Err(DbError::Sqlite(alter_err));
798 }
799 }
800 } else if !trimmed.is_empty() && !trimmed.starts_with("--") {
801 // Non-ALTER statements (CREATE TABLE, triggers, etc.)
802 // Use execute_batch to handle multi-line statements
803 // that may span multiple lines.
804 }
805 }
806 // Re-run the full batch minus ALTER TABLEs for triggers/tables
807 let non_alter: String = sql
808 .lines()
809 .filter(|l| {
810 let t = l.trim().to_uppercase();
811 !(t.starts_with("ALTER TABLE") && t.contains("ADD COLUMN"))
812 })
813 .collect::<Vec<_>>()
814 .join("\n");
815 if !non_alter.trim().is_empty() {
816 // Ignore "already exists" errors from prior partial runs;
817 // log anything else as a warning.
818 if let Err(e) = self.conn.execute_batch(&non_alter) {
819 let msg = e.to_string();
820 if !msg.contains("already exists") {
821 tracing::warn!(
822 migration = target,
823 "Non-ALTER migration statement failed: {msg}"
824 );
825 }
826 }
827 }
828 self.conn.execute_batch(
829 &format!("PRAGMA user_version = {};\nCOMMIT;", target),
830 )?;
831 }
832 Err(e) => return Err(DbError::Sqlite(e)),
833 }
834 }
835 }
836
837 Ok(())
838 }
839
840 /// Run a closure inside a SQLite transaction.
841 ///
842 /// Uses `BEGIN IMMEDIATE` to acquire a write lock upfront, preventing
843 /// deadlocks when the closure issues writes. The closure receives no
844 /// arguments — it accesses the same `Database` through the shared
845 /// `Mutex<Database>`, which is safe because the caller already holds the lock.
846 #[instrument(skip_all)]
847 pub fn transaction<T, F>(&self, f: F) -> Result<T, DbError>
848 where
849 F: FnOnce() -> Result<T, DbError>,
850 {
851 self.conn.execute_batch("BEGIN IMMEDIATE")?;
852 match f() {
853 Ok(val) => {
854 self.conn.execute_batch("COMMIT")?;
855 Ok(val)
856 }
857 Err(e) => {
858 if let Err(rb_err) = self.conn.execute_batch("ROLLBACK") {
859 tracing::warn!("ROLLBACK failed after transaction error: {rb_err}");
860 }
861 Err(e)
862 }
863 }
864 }
865
866 /// Borrow the underlying connection for queries.
867 pub fn conn(&self) -> &Connection {
868 &self.conn
869 }
870
871 /// Aggregate storage stats: (sample_count, total_file_bytes).
872 pub fn storage_stats(&self) -> Result<(u64, u64), DbError> {
873 let (count, total): (u64, u64) = self.conn.query_row(
874 "SELECT COUNT(*), COALESCE(SUM(file_size), 0) FROM samples",
875 [],
876 |row| Ok((row.get(0)?, row.get(1)?)),
877 )?;
878 Ok((count, total))
879 }
880
881 /// Per-VFS storage stats: count and total bytes of *unique* samples
882 /// referenced by `vfs_id`. A sample referenced from multiple nodes in the
883 /// same VFS counts once. Used by the sync panel's per-VFS toggle rows so
884 /// the user can see how much would upload before enabling blob sync.
885 pub fn vfs_storage_stats(&self, vfs_id: i64) -> Result<(u64, u64), DbError> {
886 let (count, total): (u64, u64) = self.conn.query_row(
887 "SELECT COUNT(*), COALESCE(SUM(file_size), 0) FROM samples \
888 WHERE hash IN (\
889 SELECT DISTINCT sample_hash FROM vfs_nodes \
890 WHERE vfs_id = ? AND sample_hash IS NOT NULL\
891 )",
892 [vfs_id],
893 |row| Ok((row.get(0)?, row.get(1)?)),
894 )?;
895 Ok((count, total))
896 }
897 }
898
899 #[cfg(test)]
900 mod tests {
901 use super::*;
902
903 #[test]
904 fn open_in_memory_creates_all_tables() {
905 let db = Database::open_in_memory().unwrap();
906
907 let tables: Vec<String> = db
908 .conn()
909 .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")
910 .unwrap()
911 .query_map([], |row| row.get(0))
912 .unwrap()
913 .collect::<Result<_, _>>()
914 .unwrap();
915
916 let expected = vec![
917 "audio_analysis",
918 "collection_members",
919 "collections",
920 "edit_history",
921 "fingerprints",
922 "samples",
923 "sync_changelog",
924 "sync_state",
925 "tags",
926 "user_config",
927 "vfs",
928 "vfs_nodes",
929 "waveform_data",
930 ];
931 assert_eq!(tables, expected);
932 }
933
934 #[test]
935 fn migration_sets_user_version() {
936 let db = Database::open_in_memory().unwrap();
937 let version: i32 = db
938 .conn()
939 .query_row("PRAGMA user_version", [], |row| row.get(0))
940 .unwrap();
941 assert_eq!(version, 17);
942 }
943
944 #[test]
945 fn migration_is_idempotent() {
946 let db = Database::open_in_memory().unwrap();
947 // Opening again on the same connection shouldn't fail
948 let version: i32 = db
949 .conn()
950 .query_row("PRAGMA user_version", [], |row| row.get(0))
951 .unwrap();
952 assert_eq!(version, 17);
953 }
954
955 #[test]
956 fn foreign_keys_enforced() {
957 let db = Database::open_in_memory().unwrap();
958 // Inserting a vfs_node referencing a non-existent vfs should fail
959 let result = db.conn().execute(
960 "INSERT INTO vfs_nodes (vfs_id, name, node_type, created_at) VALUES (999, 'test', 'directory', 0)",
961 [],
962 );
963 assert!(result.is_err());
964 }
965
966 #[test]
967 fn transaction_commits_on_success() {
968 let db = Database::open_in_memory().unwrap();
969 db.transaction(|| {
970 db.conn().execute(
971 "INSERT INTO user_config (key, value) VALUES ('test_key', 'test_value')",
972 [],
973 )?;
974 Ok(())
975 })
976 .unwrap();
977
978 let val: String = db
979 .conn()
980 .query_row(
981 "SELECT value FROM user_config WHERE key = 'test_key'",
982 [],
983 |row| row.get(0),
984 )
985 .unwrap();
986 assert_eq!(val, "test_value");
987 }
988
989 #[test]
990 fn transaction_rolls_back_on_error() {
991 let db = Database::open_in_memory().unwrap();
992 let result: Result<(), DbError> = db.transaction(|| {
993 db.conn().execute(
994 "INSERT INTO user_config (key, value) VALUES ('rollback_key', 'val')",
995 [],
996 )?;
997 Err(DbError::Sqlite(rusqlite::Error::QueryReturnedNoRows))
998 });
999 assert!(result.is_err());
1000
1001 let count: i64 = db
1002 .conn()
1003 .query_row(
1004 "SELECT COUNT(*) FROM user_config WHERE key = 'rollback_key'",
1005 [],
1006 |row| row.get(0),
1007 )
1008 .unwrap();
1009 assert_eq!(count, 0);
1010 }
1011 }
1012