Artifact 9fe7bba9e74caf8865177d7054d5b3432d06c0e4e9377eb03335d20ed491924c:
- File pages/vtablist.in — part of check-in [6f48ca52cf] at 2018-09-28 23:43:25 on branch trunk — Initial documentation for the MEMSTAT virtual table. (user: drh size: 10921)
<title>List Of Virtual Tables</title> <tcl>hd_keywords {virtual table list} {list of virtual tables}</tcl> <table_of_contents> <h1>Introduction</h1> <p>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. <p> The table below lists a few of the virtual tables implmentations 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. <p> 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. <tcl> unset -nocomplain vtab set vtab(sqlite_dbpage) { ifdef SQLITE_ENABLE_DBPAGE_VTAB type eponymous rw 1 src src/dbpage.c doc *sqlite_dbpage supported 1 synopsis { Key/value store for the raw database file content. The key is the page number and the value is binary page content. } } set vtab(dbstat) { ifdef SQLITE_ENABLE_DBSTAT_VTAB type eponymous rw 0 src src/dbstat.c doc *dbstat supported 1 synopsis { Provides information about the purpose and use of each page in a database file. Used in the implementation of the [sqlite3_analyzer] utility program. } } set vtab(sqlite_memstat) { ifdef {} type eponymous rw 0 src ext/misc/memstat.c doc *sqlite_memstat supported 1 synopsis { Provides SQL access to the [sqlite3_status64()] and [sqlite3_db_status()] interfaces. } } set vtab(pragma) { ifdef builtin type table-valued-function src src/pragma.c doc {*PRAGMA functions} supported 1 synopsis { Built-in [table-valued functions] that return the results of [PRAGMA] statements for use within ordinary SQL queries. } } set vtab(templatevtab) { ifdef {} type regular src ext/misc/templatevtab.c doc {} supported 1 synopsis { A template virtual table implementation useful as a starting point for developers who want to write their own virtual tables } } set vtab(RTree) { ifdef SQLITE_ENABLE_RTREE type regular src ext/rtree/ doc *R-Trees supported 1 synopsis { An implementation of the Guttmann R*Tree spatial index idea. } } set vtab(FTS3) { ifdef SQLITE_ENABLE_FTS3 type regular src ext/fts3/ doc {*full-text search} supported 1 synopsis { A high-performance full-text search index. } } set vtab(FTS5) { ifdef SQLITE_ENABLE_FTS5 type regular src ext/fts5/ doc {*FTS5} supported 1 synopsis { A higher-performance full-text search index } } set vtab(approximate_match) { ifdef experimental type regular src ext/misc/amatch.c doc {} supported 0 synopsis { A demonstration of how to use a virtual table to implement approximate string matching. } } set vtab(sqlite_btreeinfo) { ifdef experimental type table-valued-function src ext/misc/btreeinfo.c doc {} supported 0 synopsis { 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. } } set vtab(carray) { ifdef {} type table-valued-function src ext/misc/carray.c doc *carray supported 1 synopsis { A [table-valued function] that allows a C-language array of integers, doubles, or strings to be used as table in a query. } } set vtab(closure) { ifdef {} type regular src ext/misc/closure.c doc {} supported 1 synopsis { Compute the transitive closure of a set. } } set vtab(completion) { ifdef {} type eponymous src ext/misc/completion.c doc *COMPLETION supported 1 synopsis { Suggests completions for partially-entered words during interactive SQL input. Used by the [CLI] to help implement tab-completion. } } set vtab(csv) { ifdef {} type regular src ext/misc/csv.c doc *csv supported 1 synopsis { A virtual table that represents a comma-separated-value or CSV file ([https://www.ietf.org/rfc/rfc4180.txt|RFC 4180]) as a read-only table so that it can be used as part of a larger query. } } set vtab(fsdir) { ifdef {} type table-valued-function src ext/misc/fileio.c doc {} supported 1 synopsis { 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]. } } set vtab(json_each) { ifdef SQLITE_ENABLE_JSON1 type table-valued-function src ext/misc/json1.c doc *json_each supported 1 synopsis { A [table-valued function] for decomposing a JSON string. } } set vtab(json_tree) { ifdef SQLITE_ENABLE_JSON1 type table-valued-function src ext/misc/json1.c doc *json_tree supported 1 synopsis { A [table-valued function] for decomposing a JSON string. } } set vtab(generate_series) { ifdef {} type table-valued-function src ext/misc/series.c doc *series supported 1 synopsis { A [table-valued function] returning a sequence of increasing integers, modeled after the table-valued function by the same name in PostgreSQL. } } set vtab(spellfix1) { ifdef {} type regular src ext/misc/spellfix.c doc *spellfix1 supported 1 synopsis { A virtual table that implements a spelling correction engine. } } set vtab(sqlite_stmt) { ifdef {} type table-valued-function src ext/misc/stmt.c doc *sqlite_stmt supported 1 synopsis { A [table-valued function] containing one row for each [prepared statement] associated with an open [database connection]. } } set vtab(unionvtab) { ifdef {} type regular src ext/misc/unionvtab.c doc *unionvtab supported 1 synopsis { An experimental module providing on-demand read-only access to multiple tables spread across multiple databases, via a single virtual table abstraction. } } set vtab(swarmvtab) { ifdef {} type regular src ext/misc/unionvtab.c doc *swarmvtab supported 1 synopsis { An experimental module providing on-demand read-only access to multiple tables spread across multiple databases, via a single virtual table abstraction. } } set vtab(vfsstat) { ifdef {} type table-valued-function src ext/misc/vfsstat.c doc {} supported 0 synopsis { A [table-valued function] which, in combination with a co-packaged [VFS shim] provides information on the number of system calls performed by SQLite. } } set vtab(vtablog) { ifdef {} type regular src ext/misc/vtablog.c doc {} supported 0 synopsis { 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. } } set vtab(wholenumber) { ifdef {} type eponymous src ext/misc/wholenumber.c doc {} supported 0 synopsis { A virtual table returns all integers between 1 and 4294967295. } } set vtab(zipfile) { ifdef {} type eponymous src ext/misc/zipfile.c doc *zipfile supported 1 synopsis { Represent a [https://en.wikipedia.org/wiki/Zip_(file_format)|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. } } set vtab(OsQuery) { ifdef {} type eponymous src https://github.com/facebook/osquery doc https://osquery.readthedocs.io/en/stable/ supported 0 synopsis { 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 [https://github.com/facebook/osquery|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. } } set vtab(tclvar) { ifdef {} type eponymous src src/test_tclvar.c doc {} supported 1 synopsis { Represents the global variables of a [https://en.wikipedia.org/wiki/Tcl|TCL Interpreter] as an SQL table. Used as part of the SQLite test suite. } } set vtab(files_of_checkin) { ifdef {} type eponymous src https://fossil-scm.org/fossil/file/src/foci.c doc https://fossil-scm.org/fossil/file/src/foci.c supported 1 synopsis { Provides information about all files in a single check-in in the [https://fossil-scm.org/|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. } } </tcl> <h1>Virtual Tables</h1> <table striped=1> <tr><th>Name</th><th>Description</th></tr> <tcl> set ii 1 foreach vtabname [lsort -nocase [array names vtab]] { incr ii set x [dict create {*}$vtab($vtabname)] if {$ii%2} { hd_puts "<tr style=\"text-align:left;\">" } else { hd_puts "<tr style=\"text-align:left;background-color:#DDDDDD;\">" } hd_puts "<td><b>" set doc [dict get $x doc] if {[string index $doc 0]=="*"} { hd_resolve "\[[string range $doc 1 end]|$vtabname\]" } elseif {[string match http* $doc]} { hd_resolve "\[$doc|$vtabname\]" } else { set src [dict get $x src] if {$src!=""} { hd_resolve "\[https://sqlite.org/src/file/$src|$vtabname\]" } else { hd_puts "$vtabname" } } hd_puts "</b></td>\n" hd_puts "<td>" hd_resolve [string trim [dict get $x synopsis]] hd_puts "</td></tr>\n" } </tcl> </table>