Documentation Source Text

Check-in [3d073f02d7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the vtablist.html page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3d073f02d77c13d75b621c258b1b696358bb2d6432cf9cd8effd5b6d472e0c72
User & Date: drh 2018-05-25 17:50:41
Context
2018-05-25
18:17
Improved wording on the OsQuery description. check-in: 1a2d15781d user: drh tags: trunk
17:50
Add the vtablist.html page. check-in: 3d073f02d7 user: drh tags: trunk
12:54
Clarification of xUpdate behavior in the virtual table documentation. check-in: 36334d1830 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/vtab.in.

49
50
51
52
53
54
55



56
57
58
59
60
61
62
<li> Introspect the disk content of an SQLite database file
     (the [dbstat virtual table])
<li> Read and/or write the content of a comma-separated value (CSV)
     file
<li> Access the filesystem of the host computer as if it were a database table
<li> Enabling SQL manipulation of data in statistics packages like R
</ul>





<h2>Usage</h2>

<p>A virtual table is created using a [CREATE VIRTUAL TABLE] statement.

<tcl>







>
>
>







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<li> Introspect the disk content of an SQLite database file
     (the [dbstat virtual table])
<li> Read and/or write the content of a comma-separated value (CSV)
     file
<li> Access the filesystem of the host computer as if it were a database table
<li> Enabling SQL manipulation of data in statistics packages like R
</ul>

<p>See the [list of virtual tables] page for a longer list of actual
virtual table implementations.


<h2>Usage</h2>

<p>A virtual table is created using a [CREATE VIRTUAL TABLE] statement.

<tcl>

Added pages/vtablist.in.















































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
<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        {}
  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(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 SQLite virtual table can be
    used to solve 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>