Documentation Source Text

Check-in [78fe166463]
Login

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

Overview
Comment:Fix documentation typos.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 78fe166463c84c8fb38962e4f5adcb41087717f5
User & Date: shaneh 2010-08-28 05:48:05
Context
2010-08-30
00:13
Updates to the queryplanner.html document. check-in: 33d6d684cb user: drh tags: trunk
2010-08-28
05:48
Fix documentation typos. check-in: 78fe166463 user: shaneh tags: trunk
05:33
Tweaks to the custom dictionary used for spell checking. check-in: b5e80cf302 user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/34to35.in.

179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  a VFS needs to be registered before it can be used.  The first argument
  is a pointer to a customized VFS object that the application has prepared.
  The second argument is true to make the new VFS the default VFS so that
  it will be used by the legacy [sqlite3_open()] and [sqlite3_open16()] APIs.
  If the new VFS is not the default, then you will probably have to use
  the new [sqlite3_open_v2()] API to use it.  Note, however, that if
  a new VFS is the only VFS known to SQLite (if SQLite was compiled without
  its usual default VFS or if the pre-compiled default VFS was removed
  using [sqlite3_vfs_unregister()]) then the new VFS automatic becomes the
  default VFS regardless of the makeDflt argument to [sqlite3_vfs_register()].
}

PARAGRAPH {
  Standard builds include the default "unix" or "win32" VFSes.
  But if you use the -DOS_OTHER=1 compile-time option, then SQLite is







|







179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  a VFS needs to be registered before it can be used.  The first argument
  is a pointer to a customized VFS object that the application has prepared.
  The second argument is true to make the new VFS the default VFS so that
  it will be used by the legacy [sqlite3_open()] and [sqlite3_open16()] APIs.
  If the new VFS is not the default, then you will probably have to use
  the new [sqlite3_open_v2()] API to use it.  Note, however, that if
  a new VFS is the only VFS known to SQLite (if SQLite was compiled without
  its usual default VFS or if the precompiled default VFS was removed
  using [sqlite3_vfs_unregister()]) then the new VFS automatic becomes the
  default VFS regardless of the makeDflt argument to [sqlite3_vfs_register()].
}

PARAGRAPH {
  Standard builds include the default "unix" or "win32" VFSes.
  But if you use the -DOS_OTHER=1 compile-time option, then SQLite is

Changes to pages/asyncvfs.in.

27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
your program crashes or if a power loss occurs after the database
write but before the asynchronous write thread has completed, then the
database change might never make it to disk and the next user of the
database might not see your change.

<p>You lose Durability with asynchronous I/O, but you still retain the
other parts of ACID:  Atomic,  Consistent, and Isolated.  Many
applications get along fine without the Durablity.

<h3>1.1 How it Works</h3>

<p>Asynchronous I/O works by creating an SQLite [sqlite3_vfs | VFS object]
and registering it with [sqlite3_vfs_register()].
When files opened via 
this VFS are written to (using the vfs xWrite() method), the data is not 







|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
your program crashes or if a power loss occurs after the database
write but before the asynchronous write thread has completed, then the
database change might never make it to disk and the next user of the
database might not see your change.

<p>You lose Durability with asynchronous I/O, but you still retain the
other parts of ACID:  Atomic,  Consistent, and Isolated.  Many
applications get along fine without the Durability.

<h3>1.1 How it Works</h3>

<p>Asynchronous I/O works by creating an SQLite [sqlite3_vfs | VFS object]
and registering it with [sqlite3_vfs_register()].
When files opened via 
this VFS are written to (using the vfs xWrite() method), the data is not 

Changes to pages/capi3ref.in.

240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
same content is also available as a
<a href="../capi3ref.html">single large HTML file</a>
for those who prefer that format.</p>

<p>The content on these pages is extracted from comments
in the source code.</p>

<p>The interface is broken down into three catagories:</p>

<ol>
<li><p><a href="objlist.html"><b>List Of Objects.</b></a>
    All abstract objects and datatypes used by the
    SQLite library.  There are a handful of objects, but
    only three which most users need to be aware of:
    A database connection object [sqlite3], 







|







240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
same content is also available as a
<a href="../capi3ref.html">single large HTML file</a>
for those who prefer that format.</p>

<p>The content on these pages is extracted from comments
in the source code.</p>

<p>The interface is broken down into three categories:</p>

<ol>
<li><p><a href="objlist.html"><b>List Of Objects.</b></a>
    All abstract objects and datatypes used by the
    SQLite library.  There are a handful of objects, but
    only three which most users need to be aware of:
    A database connection object [sqlite3], 

Changes to pages/changes.in.

55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
....
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
....
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
     prepared statements of a connection.
<li> Increase the maximum size of a database pages from 32KiB to 64KiB.
<li> Use the [LIKE optimization] even if the right-hand side string contains
     no wildcards.
<li> Added the [SQLITE_FCNTL_CHUNK_SIZE] verb to the [sqlite3_file_control()]
     interface for both unix and windows, to cause database files to grow in
     large chunks in order to reduce disk fragmentation.
<li> Fixed a bug in the query planner that caused performance regresssions
     relative to 3.6.23.1 on some complex joins.
<li> Fixed a typo in the OS/2 backend.
<li> Refactored the pager module.
<li> The SQLITE_MAX_PAGE_SIZE compile-time option is now silently ignored.
     The maximum page size is hard-coded at 65536 bytes.
}

................................................................................
<li>Continuing enhancements and improvements to [FTS3].
<li>Other miscellaneous bug fixes.
}

chng {2009 Dec 07 (3.6.21)} {
<li>The SQL output resulting from [sqlite3_trace()] is now modified to include
the values of [bound parameters].
<li>Performance optimizations targetting a specific use case from
a single high-profile user of SQLite.  A 12% reduction in the number of
CPU operations is achieved (as measured by valgrind).  Actual performance
improvements in practice may vary depending on workload.  Changes
include:
<ul>
<li>The [ifnull()] and [coalesce()] SQL functions are now implemented
using in-line VDBE code rather than calling external functions, so that
unused arguments need never be evaluated.
<li>The [substr()] SQL function does not bother to measure the length
................................................................................
<li>Other minor bug fixes.</li>
}

chng {2005 February 1 (3.1.1 BETA)} {
<li>Automatic caching of prepared statements in the TCL interface</li>
<li>ATTACH and DETACH as well as some other operations cause existing
    prepared statements to expire.</li>
<li>Numerious minor bug fixes</li>
}

chng {2005 January 21 (3.1.0 ALPHA)} {
<li>Autovacuum support added</li>
<li>CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP added</li>
<li>Support for the EXISTS clause added.</li>
<li>Support for correlated subqueries added.</li>
................................................................................
<li>Many nuisance bugs fixed.</li>
}

chng {2004 October 11 (3.0.8)} {
<li>Add support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions.</li>
<li>Allow new user-defined functions to be created when there are
already one or more precompiled SQL statements.<li>
<li>Fix portability problems for Mingw/MSYS.</li>
<li>Fix a byte alignment problem on 64-bit Sparc machines.</li>
<li>Fix the ".import" command of the shell so that it ignores \r
characters at the end of lines.</li>
<li>The "csv" mode option in the shell puts strings inside double-quotes.</li>
<li>Fix typos in documentation.</li>
<li>Convert array constants in the code to have type "const".</li>
<li>Numerous code optimizations, specially optimizations designed to







|







 







|

|







 







|







 







|







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
....
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
....
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
     prepared statements of a connection.
<li> Increase the maximum size of a database pages from 32KiB to 64KiB.
<li> Use the [LIKE optimization] even if the right-hand side string contains
     no wildcards.
<li> Added the [SQLITE_FCNTL_CHUNK_SIZE] verb to the [sqlite3_file_control()]
     interface for both unix and windows, to cause database files to grow in
     large chunks in order to reduce disk fragmentation.
<li> Fixed a bug in the query planner that caused performance regressions
     relative to 3.6.23.1 on some complex joins.
<li> Fixed a typo in the OS/2 backend.
<li> Refactored the pager module.
<li> The SQLITE_MAX_PAGE_SIZE compile-time option is now silently ignored.
     The maximum page size is hard-coded at 65536 bytes.
}

................................................................................
<li>Continuing enhancements and improvements to [FTS3].
<li>Other miscellaneous bug fixes.
}

chng {2009 Dec 07 (3.6.21)} {
<li>The SQL output resulting from [sqlite3_trace()] is now modified to include
the values of [bound parameters].
<li>Performance optimizations targeting a specific use case from
a single high-profile user of SQLite.  A 12% reduction in the number of
CPU operations is achieved (as measured by Valgrind).  Actual performance
improvements in practice may vary depending on workload.  Changes
include:
<ul>
<li>The [ifnull()] and [coalesce()] SQL functions are now implemented
using in-line VDBE code rather than calling external functions, so that
unused arguments need never be evaluated.
<li>The [substr()] SQL function does not bother to measure the length
................................................................................
<li>Other minor bug fixes.</li>
}

chng {2005 February 1 (3.1.1 BETA)} {
<li>Automatic caching of prepared statements in the TCL interface</li>
<li>ATTACH and DETACH as well as some other operations cause existing
    prepared statements to expire.</li>
<li>Numerous minor bug fixes</li>
}

chng {2005 January 21 (3.1.0 ALPHA)} {
<li>Autovacuum support added</li>
<li>CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP added</li>
<li>Support for the EXISTS clause added.</li>
<li>Support for correlated subqueries added.</li>
................................................................................
<li>Many nuisance bugs fixed.</li>
}

chng {2004 October 11 (3.0.8)} {
<li>Add support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions.</li>
<li>Allow new user-defined functions to be created when there are
already one or more precompiled SQL statements.<li>
<li>Fix portability problems for MinGW/MSYS.</li>
<li>Fix a byte alignment problem on 64-bit Sparc machines.</li>
<li>Fix the ".import" command of the shell so that it ignores \r
characters at the end of lines.</li>
<li>The "csv" mode option in the shell puts strings inside double-quotes.</li>
<li>Fix typos in documentation.</li>
<li>Convert array constants in the code to have type "const".</li>
<li>Numerous code optimizations, specially optimizations designed to

Changes to pages/datatype3.in.

49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
of any storage class.</p>

<p>All values in SQL statements, whether they are literals embedded in SQL
statement text or [parameters] bound to 
[prepared statements | pre-compiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>







|







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
of any storage class.</p>

<p>All values in SQL statements, whether they are literals embedded in SQL
statement text or [parameters] bound to 
[prepared statements | precompiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>

Changes to pages/faq.in.

421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
  VFS layer is used to simulate operating system crashes and power
  failures in order to ensure that transactions are atomic across
  these events.  A mechanism for deliberately injecting I/O errors
  shows that SQLite is resilient to such malfunctions.  (As an
  experiment, try inducing these kinds of errors on other SQL database
  engines and see what happens!)</p>

  <p>We also run SQLite using [http://valgrind.org | valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the
  compilers used for SQLite development (various versions of GCC).







|







421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
  VFS layer is used to simulate operating system crashes and power
  failures in order to ensure that transactions are atomic across
  these events.  A mechanism for deliberately injecting I/O errors
  shows that SQLite is resilient to such malfunctions.  (As an
  experiment, try inducing these kinds of errors on other SQL database
  engines and see what happens!)</p>

  <p>We also run SQLite using [http://valgrind.org | Valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the
  compilers used for SQLite development (various versions of GCC).

Changes to pages/fileformat2.in.

165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
....
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
size of 65536 bytes is supported.  The value 65536 will not fit in a
two-byte integer, so to specify a 65536-byte page size, the value is
at offset 16 is 0x00 0x01.
This value can be interpreted as a big-endian
1 and thought of is as a magic number to represent the 65536 page size.
Or one can view the two-byte field as a little endian number and say
that it represents the page size divided by 256.  These two 
interpretations of the page-size field are equivelent.</p>

<h4>1.2.3 File format version numbers</h4>

<p>The file format write version and file format read version at offsets
18 and 19 are intended to allow for enhancements of the file format
in future versions of SQLite.  In current versions of SQLite, both of
these values are 1 for rollback journalling modes and 2 for [WAL]
................................................................................
for i from 0 to n-1 step 2:
   s0 += x(i) + s1;
   s1 += x(i+1) + s0;
endfor
# result in s0 and s1
</pre></blockquote>)^

<p>The outputs s0 and s1 are both weighted checksums using fibonacci weights
in reverse order (the largest fibonacci weight occurs on the first element
of the sequence being summed.)  The s1 value spans all 32-bit integer
terms of the sequence whereas s0 omits the final term.</p>

<h3>4.3 Checkpoint Algorithm</h3>

<p>On a [checkpoint], the WAL is first flushed to persistent storage using
the xSync method of the [sqlite3_io_methods | VFS]. 







|







 







|
|







165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
....
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
size of 65536 bytes is supported.  The value 65536 will not fit in a
two-byte integer, so to specify a 65536-byte page size, the value is
at offset 16 is 0x00 0x01.
This value can be interpreted as a big-endian
1 and thought of is as a magic number to represent the 65536 page size.
Or one can view the two-byte field as a little endian number and say
that it represents the page size divided by 256.  These two 
interpretations of the page-size field are equivalent.</p>

<h4>1.2.3 File format version numbers</h4>

<p>The file format write version and file format read version at offsets
18 and 19 are intended to allow for enhancements of the file format
in future versions of SQLite.  In current versions of SQLite, both of
these values are 1 for rollback journalling modes and 2 for [WAL]
................................................................................
for i from 0 to n-1 step 2:
   s0 += x(i) + s1;
   s1 += x(i+1) + s0;
endfor
# result in s0 and s1
</pre></blockquote>)^

<p>The outputs s0 and s1 are both weighted checksums using Fibonacci weights
in reverse order (the largest Fibonacci weight occurs on the first element
of the sequence being summed.)  The s1 value spans all 32-bit integer
terms of the sequence whereas s0 omits the final term.</p>

<h3>4.3 Checkpoint Algorithm</h3>

<p>On a [checkpoint], the WAL is first flushed to persistent storage using
the xSync method of the [sqlite3_io_methods | VFS]. 

Changes to pages/howtocompile.in.

126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
[http://wiki.tcl.tk/2541 | tclsh] or 
[http://wiki.tcl.tk/2364 | wish] using the 
[http://wiki.tcl.tk/9830 | TCL load command], or to generate a
standalone tclsh that comes with SQLite built in.
A copy of the tcl amalgamation
is included on the [download page] as a file in the [TEA tarball].</p>

<p>To generate a TCL-loadable library for SQLite on linux, the following
command will suffice:</p>

<blockquote><pre>
gcc -o libtclsqlite3.so -shared tclsqlite3.c -lpthread -ldl -ltcl
</pre></blockquote>

<p>Building shared libraries for Mac OSX and Windows is not nearly so simple,
................................................................................
<li>[SQLITE_OMIT_TRIGGER]
<li>[SQLITE_OMIT_VACUUM]
<li>[SQLITE_OMIT_VIEW]
<li>[SQLITE_OMIT_VIRTUALTABLE]
</ul>

<p>To build a custom amalgamation, first download the original individual
source files onto a unix or unix-like development platform.  (Any linux or
mac osx machine will suffice.  Windows will work if loaded with
[http://www.cygwin.com/ | cygwin].)  Be sure to get the original source
files not the "preprocessed source files".  One can obtain the complete
set of original source files either from the [download page] or directly
from the [http://www.sqlite.org/src | configuration management system].</p>

<p>Suppose the SQLite source tree is stored in a directory named "sqlite".
Plan to construct the amalgamation in a parallel directory named (for
example) "bld".  First construct an appropriate Makefile by either







|







 







|

|







126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
[http://wiki.tcl.tk/2541 | tclsh] or 
[http://wiki.tcl.tk/2364 | wish] using the 
[http://wiki.tcl.tk/9830 | TCL load command], or to generate a
standalone tclsh that comes with SQLite built in.
A copy of the tcl amalgamation
is included on the [download page] as a file in the [TEA tarball].</p>

<p>To generate a TCL-loadable library for SQLite on Linux, the following
command will suffice:</p>

<blockquote><pre>
gcc -o libtclsqlite3.so -shared tclsqlite3.c -lpthread -ldl -ltcl
</pre></blockquote>

<p>Building shared libraries for Mac OSX and Windows is not nearly so simple,
................................................................................
<li>[SQLITE_OMIT_TRIGGER]
<li>[SQLITE_OMIT_VACUUM]
<li>[SQLITE_OMIT_VIEW]
<li>[SQLITE_OMIT_VIRTUALTABLE]
</ul>

<p>To build a custom amalgamation, first download the original individual
source files onto a unix or unix-like development platform.  (Any Linux or
mac osx machine will suffice.  Windows will work if loaded with
[http://www.cygwin.com/ | Cygwin].)  Be sure to get the original source
files not the "preprocessed source files".  One can obtain the complete
set of original source files either from the [download page] or directly
from the [http://www.sqlite.org/src | configuration management system].</p>

<p>Suppose the SQLite source tree is stored in a directory named "sqlite".
Plan to construct the amalgamation in a parallel directory named (for
example) "bld".  First construct an appropriate Makefile by either

Changes to pages/lang.in.

2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
....
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
....
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
<tr><td> %H <td><td> hour: 00-24 
<tr><td> %j <td><td> day of year: 001-366
<tr><td> %J <td><td> Julian day number
<tr><td> %m <td><td> month: 01-12
<tr><td> %M <td><td> minute: 00-59
<tr><td> %s <td><td> seconds since 1970-01-01
<tr><td> %S <td><td> seconds: 00-59
<tr><td> %w <td><td> day of week 0-6 with sunday==0
<tr><td> %W <td><td> week of year: 00-53
<tr><td> %Y <td><td> year: 0000-9999
<tr><td> %% <td><td> %
</table>
</blockquote>)^

<p>
................................................................................
algorithm always works like ABORT.</p>

<p>^When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] fire if and only if
[recursive_triggers pragma | recursive triggers] are enabled.</p>

<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that
are deleted by the REPLACE conflict resolution stretegy.  ^Nor does
REPLACE increment the [sqlite3_changes | change counter].
The exceptional behaviors defined in this paragraph might change 
in a future release.</p>
</dl>

<p>^The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
................................................................................
<em>not</em> a hinting mechanism and it should not be used as such.
^The INDEXED BY clause does not give the optimizer hints about which index
to use; it gives the optimizer a requirement of which index to use.
^If the query optimizer is unable to use the index specified by the
INDEX BY clause, then the query will fail with an error.</p>

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the preformance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.
Developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very







|







 







|







 







|







2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
....
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
....
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
<tr><td> %H <td><td> hour: 00-24 
<tr><td> %j <td><td> day of year: 001-366
<tr><td> %J <td><td> Julian day number
<tr><td> %m <td><td> month: 01-12
<tr><td> %M <td><td> minute: 00-59
<tr><td> %s <td><td> seconds since 1970-01-01
<tr><td> %S <td><td> seconds: 00-59
<tr><td> %w <td><td> day of week 0-6 with Sunday==0
<tr><td> %W <td><td> week of year: 00-53
<tr><td> %Y <td><td> year: 0000-9999
<tr><td> %% <td><td> %
</table>
</blockquote>)^

<p>
................................................................................
algorithm always works like ABORT.</p>

<p>^When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] fire if and only if
[recursive_triggers pragma | recursive triggers] are enabled.</p>

<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that
are deleted by the REPLACE conflict resolution strategy.  ^Nor does
REPLACE increment the [sqlite3_changes | change counter].
The exceptional behaviors defined in this paragraph might change 
in a future release.</p>
</dl>

<p>^The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
................................................................................
<em>not</em> a hinting mechanism and it should not be used as such.
^The INDEXED BY clause does not give the optimizer hints about which index
to use; it gives the optimizer a requirement of which index to use.
^If the query optimizer is unable to use the index specified by the
INDEX BY clause, then the query will fail with an error.</p>

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the performance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.
Developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very

Changes to pages/limits.in.

350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
}

limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a machines with
a 32-bit integer.</p>

<p>







|







350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
}

limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a machines with
a 32-bit integer.</p>

<p>

Changes to pages/oldnews.in.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
...
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
  hd_puts "<hr width=\"50%\">"
}


newsitem {2009-Dec-07} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}

newsitem {2009-Nov-04} {Version 3.6.20} {
  SQLite [version 3.6.20] is a general maintenance release.  The
................................................................................
  Version 3.5.7 fixes several minor and obscure bugs, especially 
  in the autoconf-generated makefile.  Upgrading is optional.
  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Feb-6} {Version 3.5.6} {
  Version 3.5.6 fixes a minor regression in 3.5.5 - a regression that
  had nothing to do with the massive change ove the virtual machine
  to a register-based design.
  No problems have been reported with the new virtual machine.  This
  release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Jan-31} {Version 3.5.5} {
  Version 3.5.5 changes over 8% of the core source code of SQLite in order
................................................................................
newsitem {2005-Sep-24} {Version 3.2.7} {
  This version fixes several minor and obscure bugs.
  Upgrade only if you are having problems.
}

newsitem {2005-Sep-16} {Version 3.2.6 - Critical Bug Fix} {
  This version fixes a bug that can result in database
  corruption if a VACUUM of a 1 gibibyte or larger database fails
  (perhaps do to running out of disk space or an unexpected power loss)
  and is later rolled back.
  <p>
  Also in this release:
  The ORDER BY and GROUP BY processing was rewritten to use less memory.
  Support for COUNT(DISTINCT) was added.  The LIKE operator can now be
  used by the optimizer on columns with COLLATE NOCASE.
................................................................................
  <a href="http://www.sqlite.org/releasenotes310.html">release notes
  for version 3.1.0</a> for a detailed description of the
  changes available in the 3.1 series.
}

newsitem {2005-Feb-01} {Version 3.1.1 (beta) Released} {
  Version 3.1.1 (beta) is now available on the
  website.  Verison 3.1.1 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  The
  <a href="http://www.sqlite.org/releasenotes310.html">release notes</a>
  From version 3.1.0 apply equally to this release beta.  A stable release
  is expected within a couple of weeks.
}

newsitem {2005-Jan-21} {Version 3.1.0 (alpha) Released} {
  Version 3.1.0 (alpha) is now available on the
  website.  Verison 3.1.0 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  See the
  <a href="http://www.sqlite.org/releasenotes310.html">release notes</a>
  for details.

  This is an alpha release.  A beta release is expected in about a week
  with the first stable release to follow after two more weeks.







|







 







|







 







|







 







|









|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
...
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
  hd_puts "<hr width=\"50%\">"
}


newsitem {2009-Dec-07} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by Valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}

newsitem {2009-Nov-04} {Version 3.6.20} {
  SQLite [version 3.6.20] is a general maintenance release.  The
................................................................................
  Version 3.5.7 fixes several minor and obscure bugs, especially 
  in the autoconf-generated makefile.  Upgrading is optional.
  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Feb-6} {Version 3.5.6} {
  Version 3.5.6 fixes a minor regression in 3.5.5 - a regression that
  had nothing to do with the massive change of the virtual machine
  to a register-based design.
  No problems have been reported with the new virtual machine.  This
  release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Jan-31} {Version 3.5.5} {
  Version 3.5.5 changes over 8% of the core source code of SQLite in order
................................................................................
newsitem {2005-Sep-24} {Version 3.2.7} {
  This version fixes several minor and obscure bugs.
  Upgrade only if you are having problems.
}

newsitem {2005-Sep-16} {Version 3.2.6 - Critical Bug Fix} {
  This version fixes a bug that can result in database
  corruption if a VACUUM of a 1 gigabyte or larger database fails
  (perhaps do to running out of disk space or an unexpected power loss)
  and is later rolled back.
  <p>
  Also in this release:
  The ORDER BY and GROUP BY processing was rewritten to use less memory.
  Support for COUNT(DISTINCT) was added.  The LIKE operator can now be
  used by the optimizer on columns with COLLATE NOCASE.
................................................................................
  <a href="http://www.sqlite.org/releasenotes310.html">release notes
  for version 3.1.0</a> for a detailed description of the
  changes available in the 3.1 series.
}

newsitem {2005-Feb-01} {Version 3.1.1 (beta) Released} {
  Version 3.1.1 (beta) is now available on the
  website.  Version 3.1.1 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  The
  <a href="http://www.sqlite.org/releasenotes310.html">release notes</a>
  From version 3.1.0 apply equally to this release beta.  A stable release
  is expected within a couple of weeks.
}

newsitem {2005-Jan-21} {Version 3.1.0 (alpha) Released} {
  Version 3.1.0 (alpha) is now available on the
  website.  Version 3.1.0 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  See the
  <a href="http://www.sqlite.org/releasenotes310.html">release notes</a>
  for details.

  This is an alpha release.  A beta release is expected in about a week
  with the first stable release to follow after two more weeks.

Changes to pages/pragma.in.

801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
    the name the database was attached with.)^  ^The first row will be for 
    the main database.  ^The second row will be for the database used to 
    store temporary tables.</p>
}

LegacyPragma foreign_key_list {
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one rwo for each foreign key that references
    a column in the argument table.)^
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^ ^Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 







|







801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
    the name the database was attached with.)^  ^The first row will be for 
    the main database.  ^The second row will be for the database used to 
    store temporary tables.</p>
}

LegacyPragma foreign_key_list {
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each foreign key that references
    a column in the argument table.)^
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^ ^Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 

Changes to pages/queryplanner.in.

134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
<p>
The problem with looking up information by rowid is that you probably
do not care what the price of "item 4" is - you want to know the price
of peaches.  And so a rowid lookup is is not helpful.
</p>

<p>
To make the original query more effcient, we can add an index on the
"fruit" column of the "fruitsforsale" table like this:
</p>

<tcl>code {
CREATE INDEX idx1 ON fruitsforsale(fruit);
}</tcl>

................................................................................
binary search and output the price from the original table.  But instead
of quiting, the database engine then advances to the next row of index
to repeat the process for next fruit='Orange' entry.  Advancing to the
next row of an index (or table) is much less costly than doing a binary
search since the next row is often located on the same database page as
the current row.  In fact, the cost of advancing to the next row is so
cheap in comparison to a binary search that we usually ignore it.  So
our estimate for the total cost of this query is 3 binary searchs.
If the number of rows of output is K and the number of rows in the table
is N, then in general the cost of doing the query proportional
to (K+1)*logN.
</p>

<h3>1.5 Multiple AND-Connected WHERE-Clause Terms</h3>

................................................................................
code {SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA';}
figure 14 #fig14 idx4lu1.gif {Query Using A Covering Index}
</tcl>

<p>
Hence, by adding extra "output" columns onto the end of an index, one
can avoid having to reference the original table and thereby
cut the number of binary searchs for a query in half.  This is a
constant-factor improvement in performance (roughly a doubling of
the speed).  But on the other hand, it is also just a refinement;
A two-fold performance increase is not nearly as dramatic as the
one-millon-fold increase seen when the table was first indexed.
And for most queries, the difference between 1 microsecond and
2 microseconds is unlikely to be noticed.
</p>

<h3>1.8 OR-Connected Terms In The WHERE Clause</h3>

<p>







|







 







|







 







|



|







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
<p>
The problem with looking up information by rowid is that you probably
do not care what the price of "item 4" is - you want to know the price
of peaches.  And so a rowid lookup is is not helpful.
</p>

<p>
To make the original query more efficient, we can add an index on the
"fruit" column of the "fruitsforsale" table like this:
</p>

<tcl>code {
CREATE INDEX idx1 ON fruitsforsale(fruit);
}</tcl>

................................................................................
binary search and output the price from the original table.  But instead
of quiting, the database engine then advances to the next row of index
to repeat the process for next fruit='Orange' entry.  Advancing to the
next row of an index (or table) is much less costly than doing a binary
search since the next row is often located on the same database page as
the current row.  In fact, the cost of advancing to the next row is so
cheap in comparison to a binary search that we usually ignore it.  So
our estimate for the total cost of this query is 3 binary searches.
If the number of rows of output is K and the number of rows in the table
is N, then in general the cost of doing the query proportional
to (K+1)*logN.
</p>

<h3>1.5 Multiple AND-Connected WHERE-Clause Terms</h3>

................................................................................
code {SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA';}
figure 14 #fig14 idx4lu1.gif {Query Using A Covering Index}
</tcl>

<p>
Hence, by adding extra "output" columns onto the end of an index, one
can avoid having to reference the original table and thereby
cut the number of binary searches for a query in half.  This is a
constant-factor improvement in performance (roughly a doubling of
the speed).  But on the other hand, it is also just a refinement;
A two-fold performance increase is not nearly as dramatic as the
one-million-fold increase seen when the table was first indexed.
And for most queries, the difference between 1 microsecond and
2 microseconds is unlikely to be noticed.
</p>

<h3>1.8 OR-Connected Terms In The WHERE Clause</h3>

<p>

Changes to pages/tclsqlite.in.

364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
...
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>copy</b>&nbsp;&nbsp;<i>conflict-algorithm</i>
&nbsp;&nbsp;<i>table-name&nbsp;</i>&nbsp;&nbsp;<i>file-name&nbsp;</i>
&nbsp;&nbsp;&nbsp;&nbsp;?<i>column-separator&nbsp;</i>?
&nbsp;&nbsp;?<i>null-indicator</i>?
</blockquote>

<p>Conflict-alogrithm must be one of the SQLite conflict algorithms for
the INSERT statement: <i>rollback</i>, <i>abort</i>,
<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language
section for <a href="lang.html#conflict">ON CONFLICT</a> for more information.
The conflict-algorithm must be specified in lower case.
</p>

<p>Table-name must already exists as a table.  File-name must exist, and
................................................................................
<i>dbcmd</i>&nbsp;&nbsp;<b>incrblob</b>&nbsp;&nbsp;<b>?-readonly??</b>
&nbsp;&nbsp;<i>?DB?&nbsp;&nbsp;TABLE&nbsp;&nbsp;COLUMN&nbsp;&nbsp;ROWID</i>
</blockquote>

<p>
The command returns a new TCL channel for reading or writing to the BLOB.
The channel is opened using the underlying 
<a href="c3ref/blob_open.html">sqlite3_blob_open()</a> C-langauge
interface.  Close the channel using the <b>close</b> command of TCL.
</p>
}

##############################################################################
METHOD errorcode {








|







 







|







364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
...
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>copy</b>&nbsp;&nbsp;<i>conflict-algorithm</i>
&nbsp;&nbsp;<i>table-name&nbsp;</i>&nbsp;&nbsp;<i>file-name&nbsp;</i>
&nbsp;&nbsp;&nbsp;&nbsp;?<i>column-separator&nbsp;</i>?
&nbsp;&nbsp;?<i>null-indicator</i>?
</blockquote>

<p>Conflict-algorithm must be one of the SQLite conflict algorithms for
the INSERT statement: <i>rollback</i>, <i>abort</i>,
<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language
section for <a href="lang.html#conflict">ON CONFLICT</a> for more information.
The conflict-algorithm must be specified in lower case.
</p>

<p>Table-name must already exists as a table.  File-name must exist, and
................................................................................
<i>dbcmd</i>&nbsp;&nbsp;<b>incrblob</b>&nbsp;&nbsp;<b>?-readonly??</b>
&nbsp;&nbsp;<i>?DB?&nbsp;&nbsp;TABLE&nbsp;&nbsp;COLUMN&nbsp;&nbsp;ROWID</i>
</blockquote>

<p>
The command returns a new TCL channel for reading or writing to the BLOB.
The channel is opened using the underlying 
<a href="c3ref/blob_open.html">sqlite3_blob_open()</a> C-language
interface.  Close the channel using the <b>close</b> command of TCL.
</p>
}

##############################################################################
METHOD errorcode {

Changes to pages/testing.in.

651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
SQLITE_DEBUG preprocessor macro defined.</p>

<tcl>hd_fragment valgrind</tcl>
<h3>8.2 Valgrind</h3>

<p>[http://valgrind.org/ | Valgrind] is perhaps the most amazing
and useful developer tool in the world.  Valgrind is a simulator - it simulates
an x86 running a linux binary.  (Ports of valgrind for platforms other
than linux are in development, but as of this writing, valgrind only
works reliably on linux, which in the opinion of the SQLite developers 
means that linux should be the preferred platform for all software development.)
As valgrind runs a linux binary, it looks for all kinds of interesting
errors such as array overruns, reading from uninitialized memory,
stack overflows, memory leaks, and so forth.  Valgrind finds problems
that can easily slip through all of the other tests run against SQLite.
And, when valgrind does find an error, it can dump the developer directly
into a symbolic debugger at the exact point where the error occur, to
facilitate a quick fix.</p>

<p>Because it is a simulator, running a binary in valgrind is slower than 
running it on native hardware.  So it is impractical to run the full
SQLite test suite through valgrind.  However, the veryquick tests and
a subset of the TH3 tests are run through valgrind prior to every release.</p>

<tcl>hd_fragment memtesting</tcl>
<h3>8.3 Memsys2</h3>

<p>SQLite contains a pluggable
[memory allocation | memory allocation subsystem].
The default implementation uses system malloc() and free(). 
However, if SQLite is compiled with [SQLITE_MEMDEBUG], an alternative
memory allocation wrapper ([memsys2])
is inserted that looks for memory allocation
errors at run-time.  The memsys2 wrapper checks for memory leaks, of
course, but also looks for buffer overruns, uses of uninitialized memory,
and attempts to use memory after it has been freed.  These same checks
are also done by valgrind (and, indeed, valgrind does them better)
but memsys2 has the advantage of being much faster than valgrind, which
means the checks can be done more often and for longer tests.</p>

<tcl>hd_fragment mutextesting</tcl>
<h3>8.4 Mutex Asserts</h3>

<p>SQLite contains a pluggable mutex subsystem.  Depending on 
compile-time options, the default mutex system contains interfaces







|
|
|
|
|



|



|

|
|













|
|







651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
SQLITE_DEBUG preprocessor macro defined.</p>

<tcl>hd_fragment valgrind</tcl>
<h3>8.2 Valgrind</h3>

<p>[http://valgrind.org/ | Valgrind] is perhaps the most amazing
and useful developer tool in the world.  Valgrind is a simulator - it simulates
an x86 running a Linux binary.  (Ports of Valgrind for platforms other
than Linux are in development, but as of this writing, Valgrind only
works reliably on Linux, which in the opinion of the SQLite developers 
means that Linux should be the preferred platform for all software development.)
As Valgrind runs a Linux binary, it looks for all kinds of interesting
errors such as array overruns, reading from uninitialized memory,
stack overflows, memory leaks, and so forth.  Valgrind finds problems
that can easily slip through all of the other tests run against SQLite.
And, when Valgrind does find an error, it can dump the developer directly
into a symbolic debugger at the exact point where the error occur, to
facilitate a quick fix.</p>

<p>Because it is a simulator, running a binary in Valgrind is slower than 
running it on native hardware.  So it is impractical to run the full
SQLite test suite through Valgrind.  However, the veryquick tests and
a subset of the TH3 tests are run through Valgrind prior to every release.</p>

<tcl>hd_fragment memtesting</tcl>
<h3>8.3 Memsys2</h3>

<p>SQLite contains a pluggable
[memory allocation | memory allocation subsystem].
The default implementation uses system malloc() and free(). 
However, if SQLite is compiled with [SQLITE_MEMDEBUG], an alternative
memory allocation wrapper ([memsys2])
is inserted that looks for memory allocation
errors at run-time.  The memsys2 wrapper checks for memory leaks, of
course, but also looks for buffer overruns, uses of uninitialized memory,
and attempts to use memory after it has been freed.  These same checks
are also done by valgrind (and, indeed, Valgrind does them better)
but memsys2 has the advantage of being much faster than Valgrind, which
means the checks can be done more often and for longer tests.</p>

<tcl>hd_fragment mutextesting</tcl>
<h3>8.4 Mutex Asserts</h3>

<p>SQLite contains a pluggable mutex subsystem.  Depending on 
compile-time options, the default mutex system contains interfaces

Changes to pages/vdbe.in.

859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
case, Next falls through, and ListRead jumps. Later on, we will see 
other looping instructions (NextIdx and SortNext) that operate using the 
same principle.</p>

<p>The <a href="opcode.html#NotExists">NotExists</a> instruction pops 
the top stack element and uses it as an integer key.  If a record with 
that key does not exist in table P1, then jump to P2.  If a record does 
exist, then fall thru to the next instruction.  In this case P2 takes 
us to the Goto at the end of the loop, which jumps back to the ListRead 
at the beginning.  This could have been coded to have P2 be 16, the 
ListRead at the start of the loop, but the SQLite parser which generated 
this code didn't make that optimization.</p>
<p>The <a href="opcode.html#Delete">Delete</a> does the work of this 
loop; it pops an integer key off the stack (placed there by the 
preceding ListRead) and deletes the record of cursor P1 that has that key.  







|







859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
case, Next falls through, and ListRead jumps. Later on, we will see 
other looping instructions (NextIdx and SortNext) that operate using the 
same principle.</p>

<p>The <a href="opcode.html#NotExists">NotExists</a> instruction pops 
the top stack element and uses it as an integer key.  If a record with 
that key does not exist in table P1, then jump to P2.  If a record does 
exist, then fall through to the next instruction.  In this case P2 takes 
us to the Goto at the end of the loop, which jumps back to the ListRead 
at the beginning.  This could have been coded to have P2 be 16, the 
ListRead at the start of the loop, but the SQLite parser which generated 
this code didn't make that optimization.</p>
<p>The <a href="opcode.html#Delete">Delete</a> does the work of this 
loop; it pops an integer key off the stack (placed there by the 
preceding ListRead) and deletes the record of cursor P1 that has that key.  

Changes to pages/vtab.in.

290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
[sqlite3_create_module()] and as the argument to the USING clause of the
[CREATE VIRTUAL TABLE] statement that is running.
The second, argv[1], is the name of the database in which the new virtual table is being created. The database name is "main" for the primary database, or
"temp" for TEMP database, or the name given at the end of the [ATTACH]
statement for attached databases.  The third element of the array, argv[2], 
is the name of the new virtual table, as specified following the TABLE
keyword in the [CREATE VIRTUAL TABLE] statement.
If present, the fourth and subsquent strings in the argv[] array report 
the arguments to the module name in the [CREATE VIRTUAL TABLE] statement.

<p>The job of this method is to construct the new virtual table object
(an [sqlite3_vtab] object) and return a pointer to it in *ppVTab.

<p>As part of the task of creating a new [sqlite3_vtab] structure, this 
method <u>must</u> invoke [sqlite3_declare_vtab()] to tell the SQLite 







|







290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
[sqlite3_create_module()] and as the argument to the USING clause of the
[CREATE VIRTUAL TABLE] statement that is running.
The second, argv[1], is the name of the database in which the new virtual table is being created. The database name is "main" for the primary database, or
"temp" for TEMP database, or the name given at the end of the [ATTACH]
statement for attached databases.  The third element of the array, argv[2], 
is the name of the new virtual table, as specified following the TABLE
keyword in the [CREATE VIRTUAL TABLE] statement.
If present, the fourth and subsequent strings in the argv[] array report 
the arguments to the module name in the [CREATE VIRTUAL TABLE] statement.

<p>The job of this method is to construct the new virtual table object
(an [sqlite3_vtab] object) and return a pointer to it in *ppVTab.

<p>As part of the task of creating a new [sqlite3_vtab] structure, this 
method <u>must</u> invoke [sqlite3_declare_vtab()] to tell the SQLite 

Changes to pages/wal.in.

49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
    "<tt>-shm</tt> shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic
    by default, is still something that application developers need to
    be mindful of.
<li>WAL works best with smaller transactions.  WAL does
    not work as well as tranditional rollback journal modes when used on
    exceedingly large transactions (transactions where the size of the
    change to the database file reaches into the gigabyte range).
    
</ol>

<h2>How WAL Works</h2>








|







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
    "<tt>-shm</tt> shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic
    by default, is still something that application developers need to
    be mindful of.
<li>WAL works best with smaller transactions.  WAL does
    not work as well as traditional rollback journal modes when used on
    exceedingly large transactions (transactions where the size of the
    change to the database file reaches into the gigabyte range).
    
</ol>

<h2>How WAL Works</h2>

Changes to pages/whentouse.in.

221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
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 perhaps across multiple volumes.
</p>







|







221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
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 terabytes (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 perhaps across multiple volumes.
</p>