1. Introduction

A virtual table is an object that presents an SQL table interface but which is not stored in the database file, at least not directly. The virtual table mechanism is a feature of SQLite that allows SQLite to access and manipulate resources other than bits in the database file using the powerful SQL query language.

The table below lists a few of the virtual tables implementations available for SQLite. Developers can deploy these virtual tables in their own applications, or use the implementations shown below as templates for writing their own virtual tables.

The list below is not exhaustive. Other virtual table implementation exist in the SQLite source tree and elsewhere. The list below tries to capture the more interesting virtual table implementations.

2. Virtual Tables

Name	Description
approximate_match	A demonstration of how to use a virtual table to implement approximate string matching.
bytecode	A table-valued function that shows the bytecodes of a prepared statement.
carray	A table-valued function that allows a C-language array of integers, doubles, or strings to be used as table in a query.
closure	Compute the transitive closure of a set.
completion	Suggests completions for partially-entered words during interactive SQL input. Used by the CLI to help implement tab-completion.
csv	A virtual table that represents a comma-separated-value or CSV file (RFC 4180) as a read-only table so that it can be used as part of a larger query.
dbstat	Provides information about the purpose and use of each page in a database file. Used in the implementation of the sqlite3_analyzer utility program.
files_of_checkin	Provides information about all files in a single check-in in the Fossil version control system. This virtual table is not part of the SQLite project but is included because it provides an example of how to use virtual tables and because it is used to help version control the SQLite sources.
fsdir	A table-valued function returning one row for each file in a selected file hierarchy of the host computer. Used by the CLI to help implement the .archive command.
FTS3	A high-performance full-text search index.
FTS5	A higher-performance full-text search index
generate_series	A table-valued function returning a sequence of integers, modeled after the table-valued function by the same name in PostgreSQL.
json_each	A table-valued function for decomposing a JSON string.
json_tree	A table-valued function for decomposing a JSON string.
OsQuery	Hundreds of virtual tables that publish various aspects of the host computer, such as the process table, user lists, active network connections, and so forth. OsQuery is a separate project, started by Facebook, hosted on GitHub, and intended for security analysis and intrusion detection OsQuery is not a part of the SQLite project, but is included in this list because it demonstrates how the SQL language and the SQLite virtual table mechanism can be leveraged to provide elegant solutions to important real-world problems.
pragma	Built-in table-valued functions that return the results of PRAGMA statements for use within ordinary SQL queries.
RTree	An implementation of the Guttmann R*Tree spatial index idea.
spellfix1	A virtual table that implements a spelling correction engine.
sqlite_btreeinfo	This experimental table-valued function provides information about a single B-tree in a database file, such as the depth, and estimated number of pages and number of entries, and so forth.
sqlite_dbpage	Key/value store for the raw database file content. The key is the page number and the value is binary page content.
sqlite_memstat	Provides SQL access to the sqlite3_status64() and sqlite3_db_status() interfaces.
sqlite_stmt	A table-valued function containing one row for each prepared statement associated with an open database connection.
swarmvtab	An experimental module providing on-demand read-only access to multiple tables spread across multiple databases, via a single virtual table abstraction.
tables_used	A table-valued function that shows the tables and indexes that are accessed by a prepared statement.
tclvar	Represents the global variables of a TCL Interpreter as an SQL table. Used as part of the SQLite test suite.
templatevtab	A template virtual table implementation useful as a starting point for developers who want to write their own virtual tables
unionvtab	An experimental module providing on-demand read-only access to multiple tables spread across multiple databases, via a single virtual table abstraction.
vfsstat	A table-valued function which, in combination with a co-packaged VFS shim provides information on the number of system calls performed by SQLite.
vtablog	A virtual table that prints diagnostic information on stdout when its key methods are invoked. Intended for interactive analysis and debugging of virtual table interfaces.
wholenumber	A virtual table returns all integers between 1 and 4294967295.
zipfile	Represent a ZIP Archive as an SQL table. Works for both reading and writing. Used by the CLI to implement the ability to read and write ZIP Archives.

This page last modified on 2023-05-01 21:49:55 UTC