Documentation Source Text

Check-in [9ef2178fee]
Login

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

Overview
Comment:Additional virtual table documentation improvements. Fix the "when-to-use" document to omit discussion of the obsolete bitmap size limits.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9ef2178fee48c1901c48de229c70c5e8c1a8c8ee
User & Date: drh 2009-04-14 11:45:26.000
Context
2009-04-14
18:15
Update to the virtual table documentation - in particular the documentation on the xUpdate method. (check-in: feccb40483 user: drh tags: trunk)
11:45
Additional virtual table documentation improvements. Fix the "when-to-use" document to omit discussion of the obsolete bitmap size limits. (check-in: 9ef2178fee user: drh tags: trunk)
2009-04-13
18:04
Fix a requirement number conflict in fileformat.in. Enhanced and expanded vtab.in. (check-in: 9acad193dd user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/vtab.in.
341
342
343
344
345
346
347




348
349
350
351
352
353
354

<blockquote><pre>
   CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
</pre></blockquote>

<p>Then the virtual table would be created with two hidden columns,
and with datatypes of "VARCHAR(12)" and "INTEGER".





<p>The xCreate must should return [SQLITE_OK] if it is successful in 
creating the new virtual table, or [SQLITE_ERROR] if it is not successful.
If not successful, the [sqlite3_vtab] structure must not be allocated. 
An error message may optionally be returned in *pzErr if unsuccessful.
Space to hold the error message string must be allocated using
an SQLite memory allocation function like 







>
>
>
>







341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358

<blockquote><pre>
   CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
</pre></blockquote>

<p>Then the virtual table would be created with two hidden columns,
and with datatypes of "VARCHAR(12)" and "INTEGER".

<p>The xCreate method need not initialize the pModule, nRef, and zErrMsg
fields of the [sqlite3_vtab] object.  The SQLite core will take care of 
that chore.

<p>The xCreate must should return [SQLITE_OK] if it is successful in 
creating the new virtual table, or [SQLITE_ERROR] if it is not successful.
If not successful, the [sqlite3_vtab] structure must not be allocated. 
An error message may optionally be returned in *pzErr if unsuccessful.
Space to hold the error message string must be allocated using
an SQLite memory allocation function like 
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643

<p>For example, if the aConstraint[3].argvIndex is set to 1, then 
when xFilter is called, the argv[0] passed to xFilter will have 
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 check by 
setting aConstraintUsage[].omit.

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

<blockquote><pre>







|







633
634
635
636
637
638
639
640
641
642
643
644
645
646
647

<p>For example, if the aConstraint[3].argvIndex is set to 1, then 
when xFilter is called, the argv[0] passed to xFilter will have 
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>
691
692
693
694
695
696
697




698
699
700
701
702
703
704
will allocate the memory for the [sqlite3_vtab_cursor] (or a subclass),
initialize the new object, and make *ppCursor point to the new object.
The successful call then returns [SQLITE_OK].

<p>For every successful call to this method, the SQLite core will
later invoke the [sqlite3_module.xClose | xClose] method to destroy 
the allocated cursor.





<p>A virtual table implementation must be able to support an arbitrary
number of simultaneously open cursors.

<p>When initially opened, the cursor is in an undefined state.
The SQLite core will invoke the [xFilter] method
on the cursor prior to any attempt to position or read from the cursor.







>
>
>
>







695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
will allocate the memory for the [sqlite3_vtab_cursor] (or a subclass),
initialize the new object, and make *ppCursor point to the new object.
The successful call then returns [SQLITE_OK].

<p>For every successful call to this method, the SQLite core will
later invoke the [sqlite3_module.xClose | xClose] method to destroy 
the allocated cursor.

<p>The xOpen method need not initialize the pVtab field of the
[sqlite3_vtab_cursor] structure.  The SQLite core will take care
of that chore automatically.

<p>A virtual table implementation must be able to support an arbitrary
number of simultaneously open cursors.

<p>When initially opened, the cursor is in an undefined state.
The SQLite core will invoke the [xFilter] method
on the cursor prior to any attempt to position or read from the cursor.
802
803
804
805
806
807
808
809
810
811
812
813
814
815

816
817
818
819
820
821
822
823
824
825

826



827
828
829
830
831
832
833

<blockquote><pre>
  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
</pre></blockquote>

<p>The SQLite core invokes this method in order to find the value for 
the N-th column of the current row. N is zero-based so the first column 
is numbered 0. The xColumn method must uses one of the 
[sqlite3_result_blob | sqlite3_result_*() APIs] 
to return the result. 
The xColumn method returns its result back to SQLite using one of the
following interface:

<p>

<li> [sqlite3_result_blob()]
<li> [sqlite3_result_double()]
<li> [sqlite3_result_int()]
<li> [sqlite3_result_int64()]
<li> [sqlite3_result_null()]
<li> [sqlite3_result_text()]
<li> [sqlite3_result_text16()]
<li> [sqlite3_result_text16le()]
<li> [sqlite3_result_text16be()]
<li> [sqlite3_result_zeroblob()]

</p>




<p>To raise an error, the xColumn method should use one of the result_text() 
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.








|
<
<
|



>










>

>
>
>







810
811
812
813
814
815
816
817


818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844

<blockquote><pre>
  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
</pre></blockquote>

<p>The SQLite core invokes this method in order to find the value for 
the N-th column of the current row. N is zero-based so the first column 
is numbered 0. 


The xColumn method may return its result back to SQLite using one of the
following interface:

<p>
<ul>
<li> [sqlite3_result_blob()]
<li> [sqlite3_result_double()]
<li> [sqlite3_result_int()]
<li> [sqlite3_result_int64()]
<li> [sqlite3_result_null()]
<li> [sqlite3_result_text()]
<li> [sqlite3_result_text16()]
<li> [sqlite3_result_text16le()]
<li> [sqlite3_result_text16be()]
<li> [sqlite3_result_zeroblob()]
</ul>
</p>

<p>If the xColumn method implementation calls none of the functions above,
then the value of the column defaults to an SQL NULL.

<p>To raise an error, the xColumn method should use one of the result_text() 
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.

Changes to pages/whentouse.in.
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
database component off onto a separate machine, then you should 
definitely consider using an enterprise-class client/server database
engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>When you start a transaction in SQLite (which happens automatically
before any write operation that is not within an explicit BEGIN...COMMIT)
the engine has to allocate a bitmap of dirty pages in the disk file to
help it manage its rollback journal.  SQLite needs 256 bytes of RAM for
every 1MiB of database (assuming a 1024-byte page size: less memory is
used with larger page sizes, of course).  

For smaller databases, the amount of memory
required is not a problem, but when database begins to grow into the
multi-gigabyte range, the size of the bitmap can get quite large.  If
you need to store and modify more than a few dozen GB of data, you should
consider using a different database engine.

</p>
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means







|
|
<
|
|
<
>
|
<
<
<
|
>







221
222
223
224
225
226
227
228
229

230
231

232
233



234
235
236
237
238
239
240
241
242
database component off onto a separate machine, then you should 
definitely consider using an enterprise-class client/server database
engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>With the default page size of 1024 bytes, an SQLite database is
limited in size to 2 tebibytes (2<sup><small>41</small></sup> bytes).

And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum

size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a



client/server database engine that spreads its content across multiple
disk files, and prehaps across multiple volumes.
</p>
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means