Small. Fast. Reliable.
Choose any three.
Choose any three.
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.
| 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