Repository navigation
Expand file tree
/
Copy pathmodel_db.cpp
More file actions
1876 lines (1571 loc) · 65.4 KB
/
Copy pathmodel_db.cpp
File metadata and controls
1876 lines (1571 loc) · 65.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
// This file is part of the Diffractor photo and video organizer
// Copyright 2026 Zac Walker
//
// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at https://mozilla.org/MPL/2.0/.
// Purpose: SQLite database layer. Manages persistent storage of indexed file metadata,
// thumbnails, and application state using SQLite database.
#include "pch.h"
#include <sqlite3.h>
#include "files.h"
#include "model_db.h"
#include "av_tracker.h"
#include "model.h"
#include "model_db_pack.h"
#include "model_index.h"
static std::atomic<int> db_fails = 0;
inline void db_trace_error(sqlite3* db, const std::string_view sql)
{
const auto code = sqlite3_errcode(db);
core::log(__FUNCTION__, std::format("Database error: {} [{}]", core::utf8_cast(sqlite3_errmsg(db)), sql));
// Only count faults that say the database itself is unhealthy. Contention and cancellation
// are normal under load, and counting them left has_errors() permanently true, which
// recommends deleting a database that is perfectly good.
switch (code)
{
case SQLITE_BUSY:
case SQLITE_LOCKED:
case SQLITE_INTERRUPT:
case SQLITE_CONSTRAINT: break;
default: db_fails.fetch_add(1); break;
}
}
inline int db_exec(sqlite3* db, const std::string& sql)
{
const auto result = sqlite3_exec(db, std::bit_cast<const char*>(sql.c_str()), nullptr, nullptr, nullptr);
if (SQLITE_OK != result)
{
db_trace_error(db, sql);
}
return result;
}
class db_statement
{
sqlite3* _db;
sqlite3_stmt* _handle;
public:
db_statement(sqlite3* db, const std::string& sql) : _db(db), _handle(nullptr) { compile(sql); }
~db_statement()
{
if (_handle != nullptr)
{
const int ret = sqlite3_finalize(_handle);
_handle = nullptr;
if (ret != SQLITE_OK) db_trace_error(_db, "sqlite3_finalize");
}
}
void compile(const std::string_view sql)
{
const int ret = sqlite3_prepare_v2(_db, std::bit_cast<const char*>(sql.data()), static_cast<int>(sql.size()),
&_handle, nullptr);
if (ret != SQLITE_OK) db_trace_error(_db, "sqlite3_prepare_v2");
}
// False when the statement failed to compile. Every accessor below is then a silent no-op,
// so a caller that must not mistake "no rows" for "could not ask" has to check this.
bool is_valid() const { return _handle != nullptr; }
void bind(const int i, const std::string_view v) const
{
if (_handle != nullptr)
{
const int ret = sqlite3_bind_text(_handle, i, std::bit_cast<const char*>(v.data()),
static_cast<int>(v.size()), SQLITE_STATIC);
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_text {} {}", i, v));
}
}
void bind(const int i, const int32_t n) const
{
if (_handle != nullptr)
{
const int ret = sqlite3_bind_int(_handle, i, n);
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_int {} {}", i, n));
}
}
void bind(const int i, const uint32_t n) const
{
if (_handle != nullptr)
{
// Check for overflow before casting
if (n > static_cast<uint32_t>(INT_MAX))
{
db_trace_error(_db, std::format("sqlite3_bind_int overflow: {}", n));
return;
}
const int ret = sqlite3_bind_int(_handle, i, static_cast<int>(n));
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_int {} {}", i, n));
}
}
void bind(const int i, const double n) const
{
if (_handle != nullptr)
{
const int ret = sqlite3_bind_double(_handle, i, n);
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_double {} {}", i, n));
}
}
void bind(const int i, const int64_t n) const
{
if (_handle != nullptr)
{
const int ret = sqlite3_bind_int64(_handle, i, n);
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_int64 {} {}", i, n));
}
}
void bind(const int i, const uint64_t n) const
{
if (_handle != nullptr)
{
// Check for overflow before casting
if (n > static_cast<uint64_t>(LLONG_MAX))
{
db_trace_error(_db, std::format("sqlite3_bind_int64 overflow: {}", n));
return;
}
const int ret = sqlite3_bind_int64(_handle, i, static_cast<sqlite3_int64>(n));
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_int64 {} {}", i, n));
}
}
void bind(const int i, const core::cspan cs) const
{
if (_handle != nullptr && !cs.empty())
{
const int ret = sqlite3_bind_blob(_handle, i, cs.data, static_cast<int>(cs.size), SQLITE_STATIC);
if (ret != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_blob {}", i));
}
}
void bind_null(const int i) const
{
if (_handle != nullptr)
{
if (sqlite3_bind_null(_handle, i) != SQLITE_OK) db_trace_error(_db, std::format("sqlite3_bind_null {}", i));
}
}
void reset() const
{
if (_handle != nullptr)
{
if (sqlite3_reset(_handle) != SQLITE_OK) db_trace_error(_db, "sqlite3_reset");
if (sqlite3_clear_bindings(_handle) != SQLITE_OK) db_trace_error(_db, "sqlite3_clear_bindings");
}
}
bool read(const bool checked = false) const
{
if (_handle != nullptr)
{
const int result = sqlite3_step(_handle);
switch (result)
{
case SQLITE_ROW: return true;
case SQLITE_DONE: return false;
default:
db_trace_error(_db, "sqlite3_step");
if (checked)
throw app_exception(
std::format("Failed to read cached properties: {}", core::utf8_cast(sqlite3_errmsg(_db))));
}
}
else if (checked)
{
throw app_exception("Cached-property query is not prepared"s);
}
return false;
}
bool read_checked() const { return read(true); }
void exec() const
{
while (read())
{
}
}
int int32(const int i) const
{
if (_handle != nullptr)
{
return sqlite3_column_int(_handle, i);
}
return 0;
}
int64_t int64(const int i) const
{
if (_handle != nullptr)
{
return sqlite3_column_int64(_handle, i);
}
return 0;
}
std::string text(const int i) const
{
if (_handle != nullptr)
{
const auto* text_ptr = sqlite3_column_text(_handle, i);
if (text_ptr != nullptr)
{
return std::string(std::bit_cast<const char*>(text_ptr));
}
}
return {};
}
core::blob blob(const int i) const
{
const auto bytes = data(i);
return {bytes.data, bytes.data + bytes.size};
}
core::cspan data(const int i) const
{
if (_handle == nullptr) return {nullptr, 0};
// sqlite3_column_blob answers null for a null column, for a zero-length blob, and for an
// allocation failure - and only the last of those still reports a length. Taking that length
// on trust would build a range over a pointer that does not exist.
const auto* const data_ptr = static_cast<const uint8_t*>(sqlite3_column_blob(_handle, i));
if (data_ptr == nullptr) return {nullptr, 0};
// The length is read after the value, as sqlite documents: asking first can convert the
// column and change what the value call then returns.
return {data_ptr, static_cast<size_t>(sqlite3_column_bytes(_handle, i))};
}
core::cspan data_checked(const int i) const
{
if (!_handle) throw app_exception("Cached-property query is not prepared"s);
const auto value = data(i);
if (!value.data && sqlite3_errcode(_db) == SQLITE_NOMEM)
{
db_trace_error(_db, "sqlite3_column_blob");
throw app_exception("Failed to read cached-property bytes"s);
}
return value;
}
// Distinguishes a column an upgrade added but never filled from one holding zero, which the
// typed readers cannot: both answer 0.
bool is_null(const int i) const { return _handle == nullptr || sqlite3_column_type(_handle, i) == SQLITE_NULL; }
};
class transaction
{
sqlite3* _db;
bool _acquired;
public:
transaction(sqlite3* db, const bool start = true) : _db(db), _acquired(false)
{
if (start)
{
const int ret = db_exec(_db, "BEGIN TRANSACTION"s);
_acquired = ret == SQLITE_OK;
}
}
~transaction() noexcept
{
if (_acquired)
{
try
{
if (db_exec(_db, "COMMIT"s) != SQLITE_OK)
{
// A COMMIT that failed leaves the transaction open on a connection that outlives
// this object, and every later BEGIN then fails with "cannot start a transaction
// within a transaction" - so nothing the session writes from here on is ever
// committed. Rolling back closes it: this batch is lost, the rest are not.
db_exec(_db, "ROLLBACK"s);
}
}
catch (...)
{
// Log error but don't throw from destructor
try
{
db_trace_error(_db, "COMMIT failed in destructor");
}
catch (...)
{
// formatting the diagnostic must not terminate the process
}
}
_acquired = false;
}
}
bool acquired() const { return _acquired; }
bool commit()
{
if (!_acquired) return false;
const auto result = db_exec(_db, "COMMIT"s);
if (result == SQLITE_OK)
{
_acquired = false;
return true;
}
rollback();
return false;
}
void rollback() noexcept
{
if (!_acquired) return;
try
{
db_exec(_db, "ROLLBACK"s);
}
catch (...)
{
}
_acquired = false;
}
};
df_assert_pod(item_import);
df_assert_move_only(db_item_t);
df_assert_move_only(item_db_write);
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
database::database(index_state& s) : _state(s)
{
}
database::~database()
{
close();
}
void database::open(const core::folder_path folder, const std::string_view file_name)
{
sqlite3_initialize();
core::scope_locked_inc l(_state.searching);
_db_thread_id = core::current_thread_id();
_db_path = core::file_path(folder, file_name, ".db");
open();
}
static std::string load_create_sql()
{
auto sql = load_app_resource(app_resource::sql);
return std::string(std::bit_cast<const char*>(sql.data()), sql.size());
}
// Bump when a release changes what a scan records, so cached metadata written by an older build
// can no longer be trusted. 1: scanning stopped storing reverse-geocoded place text as though a
// file had carried it, and older rows cannot be told apart from genuine stored text.
// 2: dates became the date pack, which records which tag each date came from. A pre-pack row can
// still answer, at low authority, so this invalidation only asks for a re-scan.
// 3: the panorama declaration a file carries in GPano is recorded. Version 2 was stamped before
// that landed, so a branch build already carries rows a re-scan has never filled - and @panorama
// under-reports silently until each file happens to be touched. The row still answers meanwhile,
// so this is a re-scan request too.
// 4: audio channel count joined the serialized cache record and sample rate widened past 16 bits.
// Existing rows still answer with unrelated metadata, so only their scan stamp is cleared.
// 5: audio and video are read by libfiles in Diffractor's own terms - short codec names, a pixel format
// as chroma and depth, the coded sample type - and FFmpeg only where libfiles cannot. Rows written
// before still answer meanwhile, so audio and video items are only asked to be re-scanned, as for 4.
// 6: values read from a video's name - show, title, season, episode, year - are derived as each row loads
// and never stored, and a track, disc or episode number with no total is stored. Older rows hold the old
// name reading as though the file carried it, and lack those numbers, so audio and video items are asked
// to be re-scanned, as for 5; they still answer meanwhile.
// 7: a tracker module's comment is its song message alone. Where a module has none, libopenmpt stands its
// sample and instrument names in for one, and those were stored as though the file carried them - the panel
// that lists the names showed them twice. Tracker modules are asked to be re-scanned; they answer meanwhile.
constexpr int db_metadata_version = 7;
// Only the metadata is dropped. Thumbnails, hashes, playback positions, and import history
// stay, so the re-index re-reads files but never re-encodes a thumbnail.
// Stamping the version over an invalidation that failed would mark metadata this build cannot
// trust as upgraded, and nothing would ever re-read it. The update is one statement, so a
// failure leaves the rows untouched and the older version stamp in place to retry.
bool database::invalidate_cached_metadata() const
{
return db_exec(_db, "UPDATE item_properties SET properties = NULL, last_scanned = NULL;"s) == SQLITE_OK;
}
// The date pack needs the file re-read, but the row it replaces is still usable in the meantime, so
// `properties` is kept and only `last_scanned` is cleared. Search, grouping and the timeline answer
// from the legacy dates until the incremental scan reaches each file.
// One statement, for the same reason as the invalidation above: a row-by-row pass that skipped the
// types carrying no metadata would have to materialise the whole table to test each name, could
// half-succeed without saying so, and would save less than re-reading those types costs.
bool database::request_date_pack_rescan() const
{
return db_exec(_db, "UPDATE item_properties SET last_scanned = NULL;"s) == SQLITE_OK;
}
bool database::request_rescan(const std::function<bool(std::string_view name)>& admits) const
{
std::vector<std::pair<std::string, core::cached>> rows;
{
const db_statement items(_db, "select folder, name from item_properties where properties IS NOT NULL"s);
if (!items.is_valid()) return false;
while (items.read())
{
const auto name = core::cache(items.text(1));
if (admits(name)) rows.emplace_back(items.text(0), name);
}
}
transaction t(_db);
const db_statement update(_db, "UPDATE item_properties SET last_scanned = NULL where folder=? and name=?"s);
for (const auto& [folder, name] : rows)
{
update.bind(1, folder);
update.bind(2, name);
update.exec();
update.reset();
}
return t.commit();
}
bool database::request_audio_metadata_rescan() const
{
return request_rescan([](const std::string_view name)
{
const auto* const ft = files::file_type_from_name(name);
return ft->has_trait(file_traits::music_metadata) || ft->has_trait(file_traits::video_metadata) ||
ft->has_trait(file_traits::av);
});
}
bool database::request_tracker_metadata_rescan() const
{
return request_rescan([](const std::string_view name)
{ return is_tracker_extension(name.substr(core::find_ext(name))); });
}
void database::upgrade_cached_metadata()
{
core::assert_true(is_db_thread());
int stored_version = 0;
{
const db_statement user_version(_db, "PRAGMA user_version"s);
if (user_version.read()) stored_version = user_version.int32(0);
}
// A cache written by a build newer than this one, which is what rolling a release back leaves
// behind. The rows stay readable - the record framing and the date pack are both built to be -
// so nothing is dropped. What has to change is the stamp: leaving it above our own would mean
// that going forward again finds a version it has already satisfied and skips the upgrade it
// owed, silently trusting rows this build has since rewritten in an older shape. Stamping it
// down costs one background re-scan per direction change and keeps the number meaningful.
if (stored_version > db_metadata_version)
{
db_exec(_db, std::format("PRAGMA user_version = {};", db_metadata_version));
core::log(__FUNCTION__, std::format("Cached metadata was written by a newer build: version {} -> {}",
stored_version, db_metadata_version));
return;
}
if (stored_version == db_metadata_version)
{
return;
}
// Version 1 rows can answer while they wait for the re-scan: they predate the pack and carry the two
// plain date records this build still reads. Version 2 was stamped mid-branch against an unversioned
// pack, which this build refuses and which has no legacy copy beside it, so keeping those rows would
// leave every one of them dateless until a scan happened to reach it. Later versions each changed what
// one kind of file records, so only those files are asked to be read again - audio and video from 3 to
// 5, which covers the tracker modules 6 asks for as well.
const auto upgraded = stored_version == 1 ? request_date_pack_rescan()
: stored_version >= 3 && stored_version <= 5 ? request_audio_metadata_rescan()
: stored_version == 6 ? request_tracker_metadata_rescan()
: invalidate_cached_metadata();
if (!upgraded)
{
core::log(__FUNCTION__,
std::format("Failed to invalidate cached metadata written by version {}", stored_version));
return;
}
db_exec(_db, std::format("PRAGMA user_version = {};", db_metadata_version));
core::log(__FUNCTION__,
std::format("Cached metadata invalidated: version {} -> {}", stored_version, db_metadata_version));
}
// Memory-mapped reads replace a syscall and a page copy with a page fault. Measured 1.7-1.8x on
// random thumbnail reads, which is where nearly all of this database's bytes are. 32-bit builds get
// none: their 2 GB address space is already the scarce resource next to decoded images.
constexpr int64_t db_mmap_size = sizeof(void*) >= 8 ? 1LL << 30 : 0;
// True only for codes that mean the file itself cannot be read as a database. Busy, locked, I/O,
// permission, and disk-full faults are environmental: the database is probably intact and
// replacing it would destroy a cache that is perfectly good.
static bool is_unusable_db_file(const int result)
{
switch (result & 0xff)
{
case SQLITE_CORRUPT:
case SQLITE_NOTADB:
case SQLITE_FORMAT: return true;
default: return false;
}
}
bool database_test_seams::schema_failure_allows_replacement(const int sqlite_result)
{
return sqlite_result == SQLITE_ERROR || is_unusable_db_file(sqlite_result);
}
static std::atomic<int> fail_next_journal_probe_step_result = SQLITE_OK;
int database_test_seams::set_journal_mode(sqlite3* db, const std::string_view requested)
{
const auto sql = std::format("PRAGMA journal_mode = {}", requested);
sqlite3_stmt* handle = nullptr;
const auto prepare_result = sqlite3_prepare_v2(db, sql.data(), static_cast<int>(sql.size()), &handle, nullptr);
if (prepare_result != SQLITE_OK)
{
if (handle) sqlite3_finalize(handle);
core::log(__FUNCTION__, std::format("Failed to prepare journal mode: {}", core::utf8_cast(sqlite3_errmsg(db))));
return sqlite3_extended_errcode(db);
}
const std::unique_ptr<sqlite3_stmt, decltype(&sqlite3_finalize)> journal_mode(handle, sqlite3_finalize);
const auto step_result = sqlite3_step(journal_mode.get());
if (step_result != SQLITE_ROW)
{
core::log(__FUNCTION__, std::format("Failed to set journal mode: {}", core::utf8_cast(sqlite3_errmsg(db))));
return sqlite3_extended_errcode(db);
}
const auto* const text = sqlite3_column_text(journal_mode.get(), 0);
const auto actual = text == nullptr ? std::string{} : std::string(reinterpret_cast<const char*>(text));
if (core::icmp(actual, requested) != 0)
{
core::log(__FUNCTION__, std::format("Requested journal mode '{}' but SQLite selected '{}'", requested, actual));
return SQLITE_CANTOPEN;
}
return SQLITE_OK;
}
int database_test_seams::probe_journal_mode(sqlite3* db)
{
sqlite3_stmt* handle = nullptr;
constexpr std::string_view sql = "SELECT count(*) FROM sqlite_schema"sv;
const auto prepare_result = sqlite3_prepare_v2(db, sql.data(), static_cast<int>(sql.size()), &handle, nullptr);
if (prepare_result != SQLITE_OK)
{
if (handle) sqlite3_finalize(handle);
core::log(__FUNCTION__,
std::format("Failed to prepare journal probe: {}", core::utf8_cast(sqlite3_errmsg(db))));
return sqlite3_extended_errcode(db);
}
const std::unique_ptr<sqlite3_stmt, decltype(&sqlite3_finalize)> probe(handle, sqlite3_finalize);
auto step_result = fail_next_journal_probe_step_result.exchange(SQLITE_OK);
if (step_result == SQLITE_OK) step_result = sqlite3_step(probe.get());
if (step_result == SQLITE_ROW) return SQLITE_OK;
core::log(__FUNCTION__, std::format("Journal probe failed: {}", core::utf8_cast(sqlite3_errmsg(db))));
return step_result == SQLITE_DONE ? SQLITE_ERROR : step_result;
}
void database_test_seams::fail_next_journal_probe(const int sqlite_result)
{
fail_next_journal_probe_step_result.store(sqlite_result);
}
enum class schema_check_result
{
usable,
incompatible,
environmental_failure,
};
// WAL keeps its index in a memory-mapped -shm file shared between processes. Network shares, and
// some virtualised or redirected profile filesystems, cannot provide one, and the failure arrives
// as an I/O or open error on the first statement that needs a write transaction.
static bool is_shared_memory_failure_result(const int result)
{
switch (result)
{
case SQLITE_CANTOPEN:
case SQLITE_IOERR_SHMOPEN:
case SQLITE_IOERR_SHMSIZE:
case SQLITE_IOERR_SHMLOCK:
case SQLITE_IOERR_SHMMAP: return true;
default: return false;
}
}
// Every column this build names, one statement per table. These are compiled and discarded, never
// run. A file whose schema cannot answer them is not one this build can use: the index select
// would fail to prepare and load an empty index without saying so.
static schema_check_result schema_is_usable(sqlite3* db)
{
static constexpr std::string_view statements[] = {
"select folder, name, properties, hash, media_position, flag, crc, last_scanned, last_indexed from item_properties",
// Named separately so a half-applied perceptual-hash upgrade is caught here, where replacing
// the database is still an option, rather than by the index load throwing.
"select phash, phash90, phash180, phash270 from item_properties",
"select folder, name, bitmap, cover_art, last_scanned from item_thumbnails",
"select key, created_date, value from web_service_cache",
"select name, modified, size, imported from item_imports",
};
for (const auto sql : statements)
{
sqlite3_stmt* handle = nullptr;
const auto result = sqlite3_prepare_v2(db, sql.data(), static_cast<int>(sql.size()), &handle, nullptr);
sqlite3_finalize(handle);
if (result != SQLITE_OK)
{
core::log(__FUNCTION__,
std::format("Schema check failed: {} [{}]", core::utf8_cast(sqlite3_errmsg(db)), sql));
return database_test_seams::schema_failure_allows_replacement(result)
? schema_check_result::incompatible
: schema_check_result::environmental_failure;
}
}
return schema_check_result::usable;
}
// Opens the connection only. False when the file cannot be opened at all, which is a path or
// permission fault that replacing the file would not fix.
bool database::connect()
{
const auto rc = sqlite3_open(std::bit_cast<const char*>(_db_path.str().c_str()), &_db);
if (rc != SQLITE_OK)
{
// Get error message before closing
std::string error_msg;
if (_db != nullptr)
{
error_msg = core::utf8_cast(sqlite3_errmsg(_db));
sqlite3_close(_db);
}
else
{
error_msg = "Failed to allocate database connection"s;
}
_db = nullptr;
core::log(__FUNCTION__, std::format("Failed to open database: {}", error_msg));
return false;
}
sqlite3_busy_timeout(_db, 1000);
return true;
}
// The database and its write-ahead siblings. The main file goes first: while it survives, the
// -wal still holds committed frames that belong to it, and removing that alone would lose them.
platform::file_op_result database::delete_database_files() const
{
// A database that was never created is already in the state the caller is asking for, and
// reporting a failed delete would block the reset that doubles as a retry of the open.
if (!platform::exists(_db_path))
{
return {platform::file_op_result_code::OK};
}
const auto result = platform::delete_file(_db_path);
if (result.success())
{
// A leftover write-ahead log is replayed into whatever database file appears next, which
// would restore the very content that could not be read.
for (const auto suffix : {"-wal"sv, "-shm"sv})
{
const std::string sibling_name = std::string(_db_path.name()) + std::string(suffix);
const auto sibling = core::file_path(_db_path.folder(), std::string_view(sibling_name));
if (platform::exists(sibling))
{
const auto sibling_result = platform::delete_file(sibling);
if (sibling_result.failed())
{
core::log(__FUNCTION__, sibling_result.format_error(std::format("Failed to delete {}", sibling)));
}
}
}
}
return result;
}
// Applies the schema and loads the index into memory. Returns false only when the file on disk is
// one this build cannot use and `can_replace` allows a fresh one to be created in its place.
// Everything else throws, so a transient fault never costs the user their cache.
bool database::prepare_database(const bool can_replace)
{
auto journal_result = database_test_seams::set_journal_mode(_db, "WAL"sv);
if (SQLITE_OK == journal_result) journal_result = database_test_seams::probe_journal_mode(_db);
auto using_rollback_journal = false;
if (is_shared_memory_failure_result(journal_result))
{
core::log(__FUNCTION__, "Shared memory unavailable - using a rollback journal"sv);
if (sqlite3_close_v2(_db) != SQLITE_OK)
{
db_trace_error(_db, "sqlite3_close_v2"s);
}
_db = nullptr;
if (!connect())
{
throw app_exception(std::format("Failed to reopen database for rollback journal\n\nPath: {}", _db_path));
}
db_exec(_db, "PRAGMA locking_mode=EXCLUSIVE;"s);
journal_result = database_test_seams::set_journal_mode(_db, "DELETE"sv);
db_exec(_db, "PRAGMA locking_mode=NORMAL;"s);
if (SQLITE_OK == journal_result) db_fails.store(0);
using_rollback_journal = SQLITE_OK == journal_result;
}
if (SQLITE_OK != journal_result)
{
const auto message = std::format("Failed to select database journal mode: {}\n\nPath: {}",
core::utf8_cast(sqlite3_errmsg(_db)), _db_path);
core::log(__FUNCTION__, message);
if (can_replace && is_unusable_db_file(journal_result))
{
return false;
}
throw app_exception(message);
}
// The first statement to touch the file is where an unreadable header surfaces, so this one
// takes part in the replace decision too.
const auto configure_result = db_exec(_db, "PRAGMA cache_size=-32768; PRAGMA trusted_schema=OFF;"s);
if (SQLITE_OK != configure_result)
{
if (can_replace && is_unusable_db_file(configure_result))
{
return false;
}
throw app_exception("Failed to configure database connection"s);
}
if constexpr (db_mmap_size > 0)
{
db_exec(_db, std::format("PRAGMA mmap_size={};", db_mmap_size));
}
const auto sql = load_create_sql();
const auto create_result = db_exec(_db, sql);
if (SQLITE_OK != create_result)
{
const auto message = std::format("Failed to create database: {}\n\nPath: {}",
core::utf8_cast(sqlite3_errmsg(_db)), _db_path);
core::log(__FUNCTION__, message);
if (can_replace && is_unusable_db_file(create_result))
{
return false;
}
throw app_exception(message);
}
if (using_rollback_journal)
{
journal_result = database_test_seams::set_journal_mode(_db, "DELETE"sv);
if (SQLITE_OK != journal_result)
{
throw app_exception(std::format("Failed to retain rollback journal mode\n\nPath: {}", _db_path));
}
}
{
const db_statement journal_mode(_db, "PRAGMA journal_mode"s);
const auto mode = journal_mode.read() ? journal_mode.text(0) : std::string{};
if (mode != "wal")
{
// Recorded rather than refused. The index is a rebuildable cache, and a corrupt file is
// now replaced on open, so the weaker crash guarantee costs a re-index at worst.
core::log(__FUNCTION__, std::format("Journal mode is '{}' rather than wal", mode));
}
}
db_exec(_db, "PRAGMA optimize=0x10002;"s);
// The periodic run below is what keeps statistics current on a connection that lives for the
// whole session; the open above has just done the work for today.
_optimized_day = core::utc_now().to_days();
// Schema upgrades must run before the index load, which selects the columns they add.
// Otherwise that select fails to prepare and the whole index loads empty and silent.
sqlite3_exec(_db, "ALTER TABLE item_properties ADD COLUMN crc INTEGER;", nullptr, nullptr, nullptr);
sqlite3_exec(_db, "ALTER TABLE item_properties ADD COLUMN phash INTEGER;", nullptr, nullptr, nullptr);
// The stored hash keeps its meaning, so existing rows stay valid for an unturned comparison. A row
// with phash set but phash90 null has simply not been hashed in the other orientations yet.
sqlite3_exec(_db, "ALTER TABLE item_properties ADD COLUMN phash90 INTEGER;", nullptr, nullptr, nullptr);
sqlite3_exec(_db, "ALTER TABLE item_properties ADD COLUMN phash180 INTEGER;", nullptr, nullptr, nullptr);
sqlite3_exec(_db, "ALTER TABLE item_properties ADD COLUMN phash270 INTEGER;", nullptr, nullptr, nullptr);
sqlite3_exec(_db, "ALTER TABLE item_thumbnails ADD COLUMN cover_art BLOB NULL;", nullptr, nullptr, nullptr);
// Those upgrades report nothing when they fail, so the schema they were meant to reach is
// checked rather than assumed.
switch (schema_is_usable(_db))
{
case schema_check_result::usable: break;
case schema_check_result::incompatible:
if (can_replace)
{
return false;
}
throw app_exception(std::format("Database schema cannot be used by this build\n\nPath: {}", _db_path));
case schema_check_result::environmental_failure:
throw app_exception(std::format("Database schema could not be checked\n\nPath: {}", _db_path));
}
upgrade_cached_metadata();
load_index_values();
core::log(__FUNCTION__, std::format("Loaded index in {} ms", _state.stats.index_load_ms));
find_web_request = std::make_unique<db_statement>(_db, "select value from web_service_cache where key=?"s);
find_folder_thumbnail = std::make_unique<db_statement>(
_db, "select bitmap, cover_art, last_scanned from item_thumbnails where folder=?"s);
find_thumbnail = std::make_unique<db_statement>(
_db, "select bitmap, cover_art, last_scanned from item_thumbnails where folder=? AND name=?"s);
_state.stats.database_size = platform::file_attributes(_db_path).size;
_state.stats.database_path = _db_path;
core::log(__FUNCTION__, std::format("Index open {}", _state.stats.database_size));
return true;
}
size_t database_test_seams::folder_thumbnail_visit_budget()
{
return 100;
}
void database_test_seams::visit_folder_thumbnail_candidates(
const core::folder_path& root, const std::function<bool(const core::folder_path&)>& visit,
const std::function<std::vector<core::folder_path>(const core::folder_path&)>& enumerate)
{
std::vector<core::folder_path> folders;
folders.emplace_back(root);
for (auto idx = 0u; idx < folders.size(); ++idx)
{
const auto folder = folders[idx];
if (!visit(folder)) break;
if (folders.size() < folder_thumbnail_visit_budget())
{
for (const auto& child : enumerate(folder))
{
if (folders.size() >= folder_thumbnail_visit_budget()) break;
folders.emplace_back(child);
}
}
}
}
void database::open()
{
core::assert_true(is_db_thread());
core::scope_locked_inc slc(df::jobs_running);
core::log(__FUNCTION__, std::format("Open database: {}", _db_path));
if (!connect())
{
// No cached values can arrive, so the load is finished in the only sense readers care about.
// Leaving it unreported strands everything gated on is_init_complete - the item totals and the
// sidebar counts would both wait for a cache that is never coming.
_state.cache_load_complete();
return;
}
if (!prepare_database(true))
{
// The cache holds nothing that re-indexing cannot rebuild, so a file this build cannot
// read is replaced. Refusing to start would leave the user no way back in, and the
// only repair - resetting the index - lives behind the window that failed to open.
core::log(__FUNCTION__, std::format("Replacing unusable database: {}", _db_path));
close();
const auto delete_result = delete_database_files();
if (delete_result.failed())
{
throw app_exception(delete_result.format_error(
std::format("Failed to replace an unusable database\n\nPath: {}", _db_path)));
}
if (!connect())
{
throw app_exception(std::format("Failed to create database\n\nPath: {}", _db_path));
}
prepare_database(false);
// The faults counted while reading the old file are resolved by the replacement, and
// leaving them would keep recommending a reset that has effectively already happened.
db_fails.store(0);
}
}
bool database::is_open() const
{
return _db != nullptr;
};
void database::close()
{
find_web_request.reset();
find_folder_thumbnail.reset();
find_thumbnail.reset();