This page describes the optional virtual table interface that may be used by applications to query and inspect the structure of a ZipVFS database.

Functionality

This module provides three eponymous virtual tables - the "zipvfs", "zipvfs_freelist" and "zipvfs_stat" tables. The schemas of these three tables are equivalent to:

  CREATE TABLE zipvfs(
    pgno INTEGER PRIMARY KEY,     -- User db page number
    offset INTEGER,               -- Byte offset of record in zipvfs file
    size INTEGER                  -- Size in bytes of record in zipvfs file
  );

  CREATE TABLE zipvfs_freelist(
    offset INTEGER,               -- Byte offset of free-slot
    size INTEGER,                 -- Size in bytes of free-slot
    parent INTEGER,               -- Offset of b-tree node
    parent_idx INTEGER            -- Index of entry in b-tree node
  );

  CREATE TABLE zipvfs_stat(
    field TEXT PRIMARY KEY,       -- Field name
    value INTEGER                 -- Field value
  );

The "zipvfs" table contains one row for each page in the database. Column "pgno" contains, as one would expect, the page number. Column "offset" contains the byte offset of the compressed page within the zipvfs file on disk. Column "size" contains the size in bytes of the compressed record.

The "zipvfs_freelist" table contains one row for each free-slot in the database file. The columns "offset" and "size" contain the values one would expect. Internally, free-slots are stored in a b-tree structure, the nodes of which are themselves free-slots. The "parent" field of each row contains the offset to the free-slot on which the current entry was found, or 0 if the current entry is the root node of the b-tree. The "parent_idx" field contains the offset of the entry on the b-tree node.

At time of writing the "zipvfs_stat" table always contains exactly six rows (more may be added in the future). The "field" column contains the name of an integer statistic and the "value" column contains its current value. The six available statistics and their interpretations are shown in the following table.

Name	Interpretation
'free-slots'	The number of free-slots currently in the ZipVFS database file.
'file-bytes'	The size of the ZipVFS database on disk, in bytes. The actual database file may be slightly larger than this, as ZipVFS normally extends files by either 1024 or 4096 bytes at a time.
'content-bytes'	The total size of all compressed user database pages currently stored in the zipvfs database file, in bytes.
'free-bytes'	The total size of all free space on free-slots in the zipvfs database file, in bytes.
'frag-bytes'	The total size of all unused space at the end of used slots in the zipvfs database file (i.e. space wasted because a slots payload size is larger than the size of the compressed page currently stored in it).
'gap-bytes'	Total size of the unused block of space created by an incremental compact operation. See the ZIPVFS_CTRL_COMPACT file-control operation for more details.

The "zipvfs_stat" statistics are the same as those accessible using the ZIPVFS_CTRL_STAT file control.

By default, the "zipvfs" and "zipvfs_stat" virtual tables report on the "main" database. To use these interfaces to inspect an attached database, the name of the attached database must be specified as a parameter to the virtual table name. For example:

  ATTACH 'xyz.db' AS aux;
  SELECT * FROM zipvfs('aux');         -- Inspect the database just attached
  SELECT * FROM zipvfs_stat('aux');    -- ("aux"), instead of the main database.

Compilation and Deployment

All code for this module is contained in source file zipvfs_vtab.c. There are two ways to use this code in an application:

1. As a loadable extension. In this case, the module is compiled in the same way as any other loadable extension. Once compiled, the extension can be loaded at runtime by the SQLite command line shell or any other application.

2. By statically linking the module into the application. In this case the zipvfs_vtab.c should be compiled and linked into the application along with the other source files. zipvfs_vtab.c should be compiled with the following compiler option:

   -DSQLITE_CORE

   Before the virtual tables may be used, they must be registered with the database connection by calling the following function:

     int sqlite3_zipvfsvtab_register(sqlite3 *db);
   

   There is no header file for this function, so users may need to copy the declaration above into the application code to prevent compiler warnings.

   Function sqlite3_zipvfsvtab_register() should be passed the database handle with which the virtual table modules should be registered. If successful, SQLITE_OK is returned. If not, an SQLite error code. If more than one connection needs to access the virtual tables, sqlite3_zipvfsvtab_register() must be called once for each (sqlite3*) handle.