SQLite database¶

This page describes the image catalog database used by the Wsl-Manager modules. It is a small SQLite file that tracks available image sources (remote and builtin) and the local images downloaded on disk.

Location and lifetime¶

• The database file lives at $env:LOCALAPPDATA/Wsl/RootFS/images.db (or $HOME/.local/share/Wsl/RootFS/images.db on Linux, for testing purposes only) and is created on first use.
• The class WslImageDatabase wraps a System.Data.SQLite.SQLiteConnection through SQLiteHelper and provides typed methods to manipulate the tables.
• The helper function Get-WslImageDatabase opens a singleton instance of WslImageDatabase, runs pending migrations, and starts a 3-minute timer that closes the connection when idle. Close-WslImageDatabase ends the session early when needed.

Schema overview¶

The initial structure is defined in Wsl-Image/db.sqlite. Migrations in Wsl-Image/Wsl-Image.Database.ps1 add columns and indexes over time.

erDiagram
    direction LR
    LocalImage }o--o|  ImageSource : "source"
    ImageSource o|--o{ ImageSourceCache : "cached in"

Table: ImageSource¶

Tracks every known image source.

Primary key: (Type, Distribution, Tags, Configured); Id is also unique.

Table: LocalImage¶

Represents images present (or expected) on disk. Most columns are copied from the corresponding ImageSource row when created. This allows tracking local state even if the source is later removed or updated.

There is a unique index on (ImageSourceId, Name) to prevent duplicates for the same source.

Table: ImageSourceCache¶

Stores the last-seen cache headers for builtin/remote catalogs (etag and timestamp) to avoid redundant downloads.

Versioning and migrations¶

WslImageDatabase tracks the current schema version in PRAGMA user_version. When opened, it runs UpdateIfNeeded to apply any missing migrations in order. Each migration is a SQL snippet that modifies the schema or copies data as needed. The current version is 7 as of this writing. The following is a summary of the migrations:

1. Create base tables from db.sqlite.
2. Import cached builtin and Incus images into ImageSource.
3. Add GroupTag to ImageSource (used to tag refresh batches) and populate from cache etags.
4. Import existing local tarballs/JSON metadata into LocalImage (TransferLocalImages).
5. Add Size to ImageSource and LocalImage.
6. Add unique index on LocalImage (ImageSourceId, Name).
7. Change ImageSource primary key to use Tags instead of Release (with data copy/rename).

Each step runs only once per database and bumps user_version.

How the module uses the database¶

Opening and caching¶

• There is a static singleton instance stored in [WslImageDatabase]::Instance.
• Get-WslImageDatabase opens the SQLite file, sets UpdateTimestampColumn = 'UpdateDate', and schedules automatic close after inactivity (3 minutes).
• Callers are expected to reuse the singleton instead of opening new connections.

Inserting and updating image sources and local images¶

For insertion and updates, the module uses INSERT statements with UPSERT clauses to ensure atomicity and avoid duplicates.

For image sources, the unique primary key on (Type, Distribution, Tags, Configured) ensures that only one row exists per combination. For Incus images, the Tags field contains the Release value. In consequence, we will have only one Incus/Ubuntu/22.04/FALSE row for Ubuntu 22.04 whatsoever. For builtins, Tags contains latest, ensuring that only one builtin per distribution exists.

For local images, the unique index on (ImageSourceId, Name) ensures that only one local entry exists per name.

Importing builtin catalogs¶

• The cmdlet Update-WslBuiltinImageCache downloads Builtin or Incus catalog from the github rootfs branch. It updates ImageSourceCache, and inserts rows into ImageSource with generated GUIDs through SaveImageBuiltins.
• SaveImageBuiltins writes new builtin rows with a GroupTag corresponding to the HTTP Response ETag Header, then deletes older rows for the same type and a different GroupTag. This ensures that only the latest builtins are kept.
• SaveImageBuiltins also marks matching LocalImage rows as Outdated when digests change.

Managing individual image sources¶

• SaveImageSource upserts a single source (including digest and size data) and marks related local entries Outdated if the digest changes. This is used when refreshing local images or adding custom URIs.
• GetImageSources and GetImageBuiltins return typed PSCustomObjects for consumers.

Importing local images¶

• Move-LocalWslImage scans the image base directory for *.rootfs.tar.gz and matching *.json metadata. Missing JSON files are synthesized via New-WslImage-MissingMetadata using file names and tarball contents.
• Each JSON entry is matched against existing ImageSource rows by type/distribution/release, URL, or digest. When none is found, a new ImageSource is created with a GroupTag matching the LocalImage ID.
• Local files are renamed to <digest>.rootfs.tar.gz (or <algorithm>_<digest>.rootfs.tar.gz) to ensure deterministic filenames, unless -DoNotChangeFiles is passed.
• The resulting LocalImage row is inserted with State = 'Synced' when the file exists or NotDownloaded otherwise. JSON metadata files are removed after import unless file changes are suppressed.

Creating and updating local entries programmatically¶

• [WslImageDatabase]::CreateLocalImageFromImageSource clones a source row into LocalImage with State = 'NotDownloaded' and respects the unique index to avoid duplicates.
• [WslImageDatabase]::SaveLocalImage upserts LocalImage rows from cmdlet output, normalizing hash data and size when provided.
• [WslImageDatabase]::RemoveLocalImage and [WslImageDatabase]::RemoveImageSource delete rows safely; the foreign key uses ON DELETE SET NULL to keep local entries if their source is removed.

Cache handling¶

• [WslImageDatabase]::GetImageSourceCache and [WslImageDatabase]::UpdateImageSourceCache read/write the cache table so callers can perform conditional requests (e.g., ETag comparison) when refreshing catalogs.

Practical tips¶

• Always call Get-WslImageDatabase to obtain the shared connection and ensure migrations run.
• When introducing schema changes, add a migration block in UpdateIfNeeded, bump CurrentVersion, and add the SQL snippet alongside the others (similar to AddImageSourceGroupTagSql).
• Keep Tags populated; they are part of the primary key and are filled with Release when absent during migrations.
• Use the provided helpers (SaveImageBuiltins, SaveImageSource, SaveLocalImage) rather than issuing raw SQL so digests, states, and timestamps stay consistent.