SQLite Database Management

SQLite Database Management#

Screenbox's DatabaseService (implements IDatabaseService) manages a single SQLite file — screenbox.db — stored in ApplicationData.LocalFolder. The service is registered as a singleton and acts as a quick cache layer; data loss is handled gracefully rather than being treated as a fatal error .

Key Source Files#

File	Role
`DatabaseService.cs`	Core class: constants, `InitializeAsync`, `CreateConnection`
`DatabaseService.Schema.cs`	Initialization, schema enforcement, corruption recovery, SQL DDL
`IDatabaseService.cs`	Public interface contract
`App.xaml.cs`	Startup call: `dbService.InitializeAsync()` on app launch

Initialization Flow#

InitializeAsync() is called during app startup in StartupInitAsync. The method delegates to EnsureInitializedAsync, which uses a double-checked lock to guarantee InitializeCoreAsync runs exactly once .

InitializeCoreAsync builds a connection string pointing to screenbox.db in LocalFolder with ReadWriteCreate mode, then calls EnsureSchemaAsync. If that throws a SqliteException or IOException, the service treats it as corruption and falls through to RecreateDatabaseFileAsync .

InitializeAsync()
  └─ EnsureInitializedAsync() [lock-guarded, once]
       └─ InitializeCoreAsync()
            ├─ EnsureSchemaAsync() [normal path]
            └─ RecreateDatabaseFileAsync() [on SqliteException / IOException]

Schema Enforcement#

EnsureSchemaAsync runs inside a transaction and enforces these tables:

Table	Primary Key	Notes
`library_folders`	`id` (autoincrement)	Maps folder paths to media type
`media_records`	`path`	Rich metadata: title, duration, tags, dimensions, bitrate
`playback_progress`	`location`	Stores `position_ticks` per media location
`playlists`	`id` (text)	Display name + last-updated timestamp
`playlist_items`	`id` (autoincrement)	FK → `playlists(id)` ON DELETE CASCADE

WAL journal mode and foreign-key enforcement are configured on every connection .

Schema drift is detected via EnsureReplaceableTable: if the actual column set doesn't exactly match the expected columns, the table is dropped and recreated . The playlists and playlist_items tables use dedicated helpers (EnsurePlaylistsTable, EnsurePlaylistItemsTable) with the same drop-and-recreate strategy.

Corruption Recovery#

When EnsureSchemaAsync fails with SqliteException or IOException, RecreateDatabaseFileAsync:

Clears _connectionString to prevent any further connection attempts against the broken file.
Calls TryDeleteDatabaseAsync, which deletes the .db, .db-shm, and .db-wal files via the WinRT storage API.
Restores _connectionString and creates a fresh database with all five tables in a single transaction.

Legacy Data Migration#

On first run after an upgrade from a pre-SQLite version, TryImportLegacyPlaylistsAsync reads JSON playlist files from the Playlists/ folder in LocalFolder and inserts them into the new playlists/playlist_items tables. After a successful import, TryDeleteLegacyMigrationArtifactsAsync removes songs.bin, videos.bin, last_positions.bin, and the Playlists/ folder .

Public API (`IDatabaseService`)#

Beyond initialization, the service exposes :

LoadLibraryCacheAsync(mediaType) / SaveMusicCacheAsync / SaveVideoCacheAsync — library folder + media record cache
ReplacePlaybackProgressAsync / LoadPlaybackProgressAsync — per-media resume position
SavePlaylistAsync / LoadPlaylistAsync / ListPlaylistsAsync / DeletePlaylistAsync — playlist CRUD

All public methods call EnsureInitializedAsync() at entry to guarantee the schema is ready before any query executes.