Documentation Source Text

Check-in [ab7c4f5247]
Login

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

Overview
Comment:Merge lang.html and vtab.html corrections that got marooned on a branch into the main line.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ab7c4f52478c44268d34af5fef7c07f7802304e4
User & Date: drh 2009-07-29 23:07:55
Context
2009-07-30
10:51
Update documentation to talk about 100% test coverage in SQLite. check-in: f7aa8bfebf user: drh tags: trunk
2009-07-29
23:07
Merge lang.html and vtab.html corrections that got marooned on a branch into the main line. check-in: ab7c4f5247 user: drh tags: trunk
06:32
Fix a typo in c3ref/intro.html. check-in: a3ee478f75 user: dan tags: trunk
2009-04-13
14:43
Further enhancements to the virtual table documentation. check-in: ebd923dab6 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

1017
1018
1019
1020
1021
1022
1023

1024
1025
1026
1027
1028
1029
1030
1031
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as column definitions in a traditional
[CREATE TABLE] statement.  
SQLite passes the module arguments directly

to the module without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

<p>A virtual table is destroyed using the ordinary
[DROP TABLE] statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>







>
|







1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as column definitions in a traditional
[CREATE TABLE] statement.  
SQLite passes the module arguments directly
to the [xCreate] and [xConnect] methods of the module implementation
without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

<p>A virtual table is destroyed using the ordinary
[DROP TABLE] statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>

Changes to pages/vtab.in.

262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
...
364
365
366
367
368
369
370

371
372
373
374
375
376
377
...
419
420
421
422
423
424
425

426
427
428
429
430
431
432
...
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
...
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655

656
657
658
659
660
661
662
...
712
713
714
715
716
717
718

719
720
721
722
723
724
725
...
732
733
734
735
736
737
738

739
740
741
742
743
744
745
...
782
783
784
785
786
787
788

789
790
791
792
793
794
795
...
841
842
843
844
845
846
847

848
849
850
851
852
853
854
...
955
956
957
958
959
960
961

962
963
964
965
966
967
968
...
991
992
993
994
995
996
997

998
999
1000
1001
1002
1003
1004
....
1011
1012
1013
1014
1015
1016
1017

1018
1019
1020
1021
1022
1023
1024
....
1031
1032
1033
1034
1035
1036
1037

1038
1039
1040
1041
1042
1043
1044
for "sqlite3_create_module".

<p>You might also want to implement your new virtual table as a 
[sqlite3_load_extension | loadable extension].

<h2>2.0 Virtual Table Methods</h2>

<tcl>############################################################### xCreate
hd_fragment xcreate {sqlite3_module.xCreate} {xCreate}</tcl>
<h3>2.1 The xCreate Method</h3>

<blockquote><pre>
  int (*xCreate)(sqlite3 *db, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
................................................................................
<p>The xCreate method is required for every virtual table implementation, 
though the xCreate and [xConnect] pointers of the [sqlite3_module] object
may point to the same function the virtual table does not need to initialize
backing store.

<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>

<h3>2.2 The xConnect Method</h3>

<blockquote><pre>
  int (*xConnect)(sqlite3*, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
................................................................................
<p>The xConnect method is required for every virtual table implementation, 
though the [xCreate] and xConnect pointers of the [sqlite3_module] object
may point to the same function the virtual table does not need to initialize
backing store.

<tcl>############################################################ xBestIndex
hd_fragment xbestindex {sqlite3_module.xBestIndex} {xBestIndex}</tcl>

<h3>2.3 The xBestIndex Method</h3>

<p>SQLite uses the xBestIndex method of a virtual table module to determine
the best way to access the virtual table. 
The xBestIndex method has a prototype like this:

<blockquote><pre>
................................................................................
<p>Given all of the information above, the job of the xBestIndex 
method it to figure out the best way to search the virtual table.

<p>The xBestIndex method fills the idxNum and idxStr fields with 
information that communicates an indexing strategy to the [xFilter] 
method. The information in idxNum and idxStr is arbitrary as far 
as the SQLite core is concerned. The SQLite core just copies the 
information through to the xFilter method. Any desired meaning can 
be assigned to idxNum and idxStr as long as xBestIndex and xFilter 
agree on what that meaning is.

<p>The idxStr value may be a string obtained from an SQLite
memory allocation function such as [sqlite3_mprintf()]. 
If this is the case, then the needToFreeIdxStr flag must be set to 
true so that the SQLite core will know to call [sqlite3_free()] on 
................................................................................
the EXPR value of the aConstraint[3] constraint.

<p>By default, the SQLite core double checks all constraints on 
each row of the virtual table that it receives. If such a check 
is redundant, the xBestFilter method can suppress that double-check by 
setting aConstraintUsage[].omit.

<tcl>########################################################## xDisconnect
hd_fragment xdisconnect {sqlite3_module.xDisconnect} {xDisconnect}</tcl>
<h3>2.4 The xDisconnect Method</h3>

<blockquote><pre>
  int (*xDisconnect)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method releases a connection to a virtual table. 
Only the [sqlite3_vtab] object is destroyed.
The virtual table is not destroyed and any backing store 
associated with the virtual table persists. 

This method undoes the work of [xConnect].

<p>This method is a destructor for a connection to the virtual table.
Constrast this method with [xDestroy].  The xDestroy is a destructor
for the entire virtual table.

<p>The xDestroy method is required for every virtual table implementation,
................................................................................
The SQLite core will invoke the [xFilter] method
on the cursor prior to any attempt to position or read from the cursor.

<p>The xOpen method is required for every virtual table implementation.

<tcl>############################################################### xClose
hd_fragment xclose {sqlite3_module.xClose}</tcl>

<h3>2.7 The xClose Method</h3>

<blockquote><pre>
  int (*xClose)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xClose method closes a cursor previously opened by 
................................................................................
returns an error.  The SQLite core will not use the
[sqlite3_vtab_cursor] again after it has been closed.

<p>The xClose method is required for every virtual table implementation.

<tcl>############################################################## xEof
hd_fragment xeof {sqlite3_module.xEof} {xEof}</tcl>

<h3>2.8 The xEof Method</h3>

<blockquote><pre>
  int (*xEof)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xEof method must return false (zero) if the specified cursor 
................................................................................
<p>This method must return [SQLITE_OK] if successful, or an sqlite 
[error code] if an error occurs.

<p>The xFilter method is required for every virtual table implementation.

<tcl>############################################################### xNext
hd_fragment xnext {sqlite3_module.xNext} {xNext}</tcl>

<h3>2.10 The xNext Method</h3>

<blockquote><pre>
  int (*xNext)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xNext method advances a [sqlite3_vtab_cursor | virtual table cursor]
................................................................................
methods to set the error message text, then return an appropriate
[error code].  The xColumn method must return [SQLITE_OK] on success.

<p>The xColumn method is required for every virtual table implementation.

<tcl>############################################################# xRowid
hd_fragment xrowid {sqlite3_module.xRowid} {xRowid}</tcl>

<h3>2.12 The xRowid Method</h3>

<blockquote><pre>
  int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
</pre></blockquote>

<p>A successful invocation of this method will cause *pRowid to be
................................................................................
<p>The xUpdate method is optional.
If the xUpdate pointer in the [sqlite3_module] for a virtual table
is a NULL pointer, then the virtual table is read-only.


<tcl>########################################################## xFindFunction
hd_fragment xfindfunction {sqlite3_module.xFindFunction} {xFindFunction}</tcl>

<h3>2.14 The xFindFunction Method</h3>

<blockquote><pre>
  int (*xFindFunction)(
    sqlite3_vtab *pVtab,
    int nArg,
    const char *zName,
................................................................................
first argument.

<p>The function pointer returned by this routine must be valid for
the lifetime of the [sqlite3_vtab] object given in the first parameter.

<tcl>############################################################ xBegin
hd_fragment xBegin {sqlite3_module.xBegin} {xBegin}</tcl>

<h3>2.15 The xBegin Method</h3>

<blockquote><pre>
  int (*xBegin)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method begins a transaction on a virtual table.
................................................................................
on a single virtual table
without an intervening call to either [xCommit] or [xRollback].
Multiple calls to other methods can and likely will occur in between
the xBegin and the corresponding [xCommit] or [xRollback].

<tcl>############################################################ xSync
hd_fragment xsync {sqlite3_module.xSync}</tcl>

<h3>2.16 The xSync Method</h3>

<blockquote><pre>
  int (*xSync)(sqlite3_vtab *pVTab);
</pre></blockquote>


................................................................................
prior to an [xCommit] or [xRollback].  In order to implement two-phase
commit, the xSync method on all virtual tables is invoked prior to
invoking the [xCommit] method on any virtual table.  If any of the 
xSync methods fail, the entire transaction is rolled back.

<tcl>########################################################### xCommit
hd_fragment xcommit {sqlite3_module.xCommit} {xCommit}</tcl>

<h3>2.17 The xCommit Method</h3>

<blockquote><pre>
  int (*xCommit)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method causes a virtual table transaction to commit.







<
|







 







>







 







>







 







|







 







<
|










>







 







>







 







>







 







>







 







>







 







>







 







>







 







>







 







>







262
263
264
265
266
267
268

269
270
271
272
273
274
275
276
...
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
...
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
...
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
...
638
639
640
641
642
643
644

645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
...
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
...
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
...
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
...
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
...
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
....
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
....
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
for "sqlite3_create_module".

<p>You might also want to implement your new virtual table as a 
[sqlite3_load_extension | loadable extension].

<h2>2.0 Virtual Table Methods</h2>


<tcl>hd_fragment xcreate {sqlite3_module.xCreate} {xCreate}</tcl>
<h3>2.1 The xCreate Method</h3>

<blockquote><pre>
  int (*xCreate)(sqlite3 *db, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
................................................................................
<p>The xCreate method is required for every virtual table implementation, 
though the xCreate and [xConnect] pointers of the [sqlite3_module] object
may point to the same function the virtual table does not need to initialize
backing store.

<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<tcl>hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h3>2.2 The xConnect Method</h3>

<blockquote><pre>
  int (*xConnect)(sqlite3*, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab,
               char **pzErr);
................................................................................
<p>The xConnect method is required for every virtual table implementation, 
though the [xCreate] and xConnect pointers of the [sqlite3_module] object
may point to the same function the virtual table does not need to initialize
backing store.

<tcl>############################################################ xBestIndex
hd_fragment xbestindex {sqlite3_module.xBestIndex} {xBestIndex}</tcl>
<tcl>hd_fragment xbestindex {sqlite3_module.xBestIndex} {xBestIndex}</tcl>
<h3>2.3 The xBestIndex Method</h3>

<p>SQLite uses the xBestIndex method of a virtual table module to determine
the best way to access the virtual table. 
The xBestIndex method has a prototype like this:

<blockquote><pre>
................................................................................
<p>Given all of the information above, the job of the xBestIndex 
method it to figure out the best way to search the virtual table.

<p>The xBestIndex method fills the idxNum and idxStr fields with 
information that communicates an indexing strategy to the [xFilter] 
method. The information in idxNum and idxStr is arbitrary as far 
as the SQLite core is concerned. The SQLite core just copies the 
information through to the [xFilter] method. Any desired meaning can 
be assigned to idxNum and idxStr as long as xBestIndex and xFilter 
agree on what that meaning is.

<p>The idxStr value may be a string obtained from an SQLite
memory allocation function such as [sqlite3_mprintf()]. 
If this is the case, then the needToFreeIdxStr flag must be set to 
true so that the SQLite core will know to call [sqlite3_free()] on 
................................................................................
the EXPR value of the aConstraint[3] constraint.

<p>By default, the SQLite core double checks all constraints on 
each row of the virtual table that it receives. If such a check 
is redundant, the xBestFilter method can suppress that double-check by 
setting aConstraintUsage[].omit.


<tcl>hd_fragment xdisconnect {sqlite3_module.xDisconnect} {xDisconnect}</tcl>
<h3>2.4 The xDisconnect Method</h3>

<blockquote><pre>
  int (*xDisconnect)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method releases a connection to a virtual table. 
Only the [sqlite3_vtab] object is destroyed.
The virtual table is not destroyed and any backing store 
associated with the virtual table persists. 

This method undoes the work of [xConnect].

<p>This method is a destructor for a connection to the virtual table.
Constrast this method with [xDestroy].  The xDestroy is a destructor
for the entire virtual table.

<p>The xDestroy method is required for every virtual table implementation,
................................................................................
The SQLite core will invoke the [xFilter] method
on the cursor prior to any attempt to position or read from the cursor.

<p>The xOpen method is required for every virtual table implementation.

<tcl>############################################################### xClose
hd_fragment xclose {sqlite3_module.xClose}</tcl>
<tcl>hd_fragment xclose {sqlite3_module.xClose}</tcl>
<h3>2.7 The xClose Method</h3>

<blockquote><pre>
  int (*xClose)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xClose method closes a cursor previously opened by 
................................................................................
returns an error.  The SQLite core will not use the
[sqlite3_vtab_cursor] again after it has been closed.

<p>The xClose method is required for every virtual table implementation.

<tcl>############################################################## xEof
hd_fragment xeof {sqlite3_module.xEof} {xEof}</tcl>
<tcl>hd_fragment xeof {sqlite3_module.xEof} {xEof}</tcl>
<h3>2.8 The xEof Method</h3>

<blockquote><pre>
  int (*xEof)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xEof method must return false (zero) if the specified cursor 
................................................................................
<p>This method must return [SQLITE_OK] if successful, or an sqlite 
[error code] if an error occurs.

<p>The xFilter method is required for every virtual table implementation.

<tcl>############################################################### xNext
hd_fragment xnext {sqlite3_module.xNext} {xNext}</tcl>
<tcl>hd_fragment xnext {sqlite3_module.xNext} {xNext}</tcl>
<h3>2.10 The xNext Method</h3>

<blockquote><pre>
  int (*xNext)(sqlite3_vtab_cursor*);
</pre></blockquote>

<p>The xNext method advances a [sqlite3_vtab_cursor | virtual table cursor]
................................................................................
methods to set the error message text, then return an appropriate
[error code].  The xColumn method must return [SQLITE_OK] on success.

<p>The xColumn method is required for every virtual table implementation.

<tcl>############################################################# xRowid
hd_fragment xrowid {sqlite3_module.xRowid} {xRowid}</tcl>
<tcl>hd_fragment xrowid {sqlite3_module.xRowid} {xRowid}</tcl>
<h3>2.12 The xRowid Method</h3>

<blockquote><pre>
  int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
</pre></blockquote>

<p>A successful invocation of this method will cause *pRowid to be
................................................................................
<p>The xUpdate method is optional.
If the xUpdate pointer in the [sqlite3_module] for a virtual table
is a NULL pointer, then the virtual table is read-only.


<tcl>########################################################## xFindFunction
hd_fragment xfindfunction {sqlite3_module.xFindFunction} {xFindFunction}</tcl>
<tcl>hd_fragment xfindfunction {sqlite3_module.xFindFunction} {xFindFunction}</tcl>
<h3>2.14 The xFindFunction Method</h3>

<blockquote><pre>
  int (*xFindFunction)(
    sqlite3_vtab *pVtab,
    int nArg,
    const char *zName,
................................................................................
first argument.

<p>The function pointer returned by this routine must be valid for
the lifetime of the [sqlite3_vtab] object given in the first parameter.

<tcl>############################################################ xBegin
hd_fragment xBegin {sqlite3_module.xBegin} {xBegin}</tcl>
<tcl>hd_fragment xBegin {sqlite3_module.xBegin} {xBegin}</tcl>
<h3>2.15 The xBegin Method</h3>

<blockquote><pre>
  int (*xBegin)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method begins a transaction on a virtual table.
................................................................................
on a single virtual table
without an intervening call to either [xCommit] or [xRollback].
Multiple calls to other methods can and likely will occur in between
the xBegin and the corresponding [xCommit] or [xRollback].

<tcl>############################################################ xSync
hd_fragment xsync {sqlite3_module.xSync}</tcl>
<tcl>hd_fragment xsync {sqlite3_module.xSync}</tcl>
<h3>2.16 The xSync Method</h3>

<blockquote><pre>
  int (*xSync)(sqlite3_vtab *pVTab);
</pre></blockquote>


................................................................................
prior to an [xCommit] or [xRollback].  In order to implement two-phase
commit, the xSync method on all virtual tables is invoked prior to
invoking the [xCommit] method on any virtual table.  If any of the 
xSync methods fail, the entire transaction is rolled back.

<tcl>########################################################### xCommit
hd_fragment xcommit {sqlite3_module.xCommit} {xCommit}</tcl>
<tcl>hd_fragment xcommit {sqlite3_module.xCommit} {xCommit}</tcl>
<h3>2.17 The xCommit Method</h3>

<blockquote><pre>
  int (*xCommit)(sqlite3_vtab *pVTab);
</pre></blockquote>

<p>This method causes a virtual table transaction to commit.