Documentation Source Text
Check-in [b8e6ac4ce4]
Not logged in

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

Overview
SHA1 Hash:b8e6ac4ce4cfa9634dc55ab40d3714e946443e61
Date: 2013-11-18 18:48:24
User: drh
Comment:Fix documentation typos pointed out on the mailing list by Philip Newton.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/compile.in

630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from all columns of every index and store that data
  in the [sqlite_stat4] table.  The query planner will then use the
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance is mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histograph data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.







|







630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from all columns of every index and store that data
  in the [sqlite_stat4] table.  The query planner will then use the
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance in mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histograph data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.

Changes to pages/faq.in

523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by millions of users."</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  Details
  can be found in the discussions on the 
  <a href="atomiccommit.html">atomic commit</a> and 
  <a href="lockingv3.html">locking</a> support in SQLite
  as well as in the mailing list archives.</p>
................................................................................
}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
  when building from canonically source files.  They do <u>not</u> work
  when you build from the SQLite [amalgamation] or from the pre-processed
  source files.

  <p>It is possible to build a special [amalgamation] that will work with
  a predetermined set of SQLITE_OMIT_... options.  Instructions for doing
  so can be found with the [omitfeatures | SQLITE_OMIT_... documentation].
}
................................................................................
faq {
  My WHERE clause expression <tt>column1="column1"</tt> does not work.
  It causes every row of the table to be returned, not just the rows
  where column1 has the value "column1".
} {
  Use single-quotes, not double-quotes, around string literals in SQL.
  This is what the SQL standard requires.  Your WHERE clause expression
  should read: <tt>column1='column2'</tt>

  <p>SQL uses double-quotes around identifiers (column or table names) that
  contains special characters or which are keywords.  So double-quotes are
  a way of escaping identifier names.  Hence, when you say
  <tt>column1="column1"</tt> that is equivalent to 
  <tt>column1=column1</tt> which is obviously always true.
}







|







 







|







 







|







523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by millions of users.</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  Details
  can be found in the discussions on the 
  <a href="atomiccommit.html">atomic commit</a> and 
  <a href="lockingv3.html">locking</a> support in SQLite
  as well as in the mailing list archives.</p>
................................................................................
}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
  when building from canonical source files.  They do <u>not</u> work
  when you build from the SQLite [amalgamation] or from the pre-processed
  source files.

  <p>It is possible to build a special [amalgamation] that will work with
  a predetermined set of SQLITE_OMIT_... options.  Instructions for doing
  so can be found with the [omitfeatures | SQLITE_OMIT_... documentation].
}
................................................................................
faq {
  My WHERE clause expression <tt>column1="column1"</tt> does not work.
  It causes every row of the table to be returned, not just the rows
  where column1 has the value "column1".
} {
  Use single-quotes, not double-quotes, around string literals in SQL.
  This is what the SQL standard requires.  Your WHERE clause expression
  should read: <tt>column1='column1'</tt>

  <p>SQL uses double-quotes around identifiers (column or table names) that
  contains special characters or which are keywords.  So double-quotes are
  a way of escaping identifier names.  Hence, when you say
  <tt>column1="column1"</tt> that is equivalent to 
  <tt>column1=column1</tt> which is obviously always true.
}

Changes to pages/fileformat2.in

174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
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]
journalling mode.  If a version of SQLite coded to the current
file format specification encounters a database file where the read
version is 1 or 2 but the write version is greater than 2, then the database
file must be treated as read-only.  If a database file with a read version
greater than 2 is encounter, then that database cannot be read or written.</p>

<h4>1.2.4 Reserved bytes per page</h4>

<p>SQLite has the ability to set aside a small number of extra bytes at
the end of every page for use by extensions.  These extra bytes are
used, for example, by the SQLite Encryption Extension to store a nonce
and/or cryptographic checksum associated with each page.  ^The 
................................................................................
allowed to be less than 480.  In other words, if the page size is 512,
then the reserved space size cannot exceed 32.)^</p>

<h4>1.2.5 Payload fractions</h4>

<p>^The maximum and minimum embedded payload fractions and the leaf
payload fraction values must be 64, 32, and 32.  These values were
originally intended to as tunable parameters that could be used to
modify the storage format of the b-tree algorithm.  However, that
functionality is not supported and there are no current plans to add
support in the future.  Hence, these three bytes are fixed at the
values specified.</p>

<h4>1.2.6 File change counter</h4>








|







 







|







174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
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]
journalling mode.  If a version of SQLite coded to the current
file format specification encounters a database file where the read
version is 1 or 2 but the write version is greater than 2, then the database
file must be treated as read-only.  If a database file with a read version
greater than 2 is encountered, then that database cannot be read or written.</p>

<h4>1.2.4 Reserved bytes per page</h4>

<p>SQLite has the ability to set aside a small number of extra bytes at
the end of every page for use by extensions.  These extra bytes are
used, for example, by the SQLite Encryption Extension to store a nonce
and/or cryptographic checksum associated with each page.  ^The 
................................................................................
allowed to be less than 480.  In other words, if the page size is 512,
then the reserved space size cannot exceed 32.)^</p>

<h4>1.2.5 Payload fractions</h4>

<p>^The maximum and minimum embedded payload fractions and the leaf
payload fraction values must be 64, 32, and 32.  These values were
originally intended to be tunable parameters that could be used to
modify the storage format of the b-tree algorithm.  However, that
functionality is not supported and there are no current plans to add
support in the future.  Hence, these three bytes are fixed at the
values specified.</p>

<h4>1.2.6 File change counter</h4>

Changes to pages/howtocorrupt.in

145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
...
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
...
486
487
488
489
490
491
492
493
and will be unable to work around the POSIX advisory locking quirks.
A <tt>close()</tt> operation on one connection might unknowingly 
clear the locks on a different database connection, leading to database
corruption.</p>

<p>The scenario above sounds far-fetched.
But the SQLite developers are aware of at 
least one commercial product that was release
with exactly this bug.  The vendor came to the SQLite developers seeking
help in tracking down some infrequent database corruption issues they were
seeing on Linux and Mac.  The problem was eventually traced to the
fact that the application was linking against two separate copies of SQLite.
The solution was to change the application build procedures to link against
just one copy of SQLite instead of two.</p>

................................................................................
be select for use in an application that has to run on an NFS filesystem
that does not support POSIX advisory locking.</p>

<p>It is important that all connections to the same database file use 
the same locking protocol.
If one application is using POSIX advisory locks and another application
is using dot-file locking, then the two applications will not see each
others locks and will not be able to coordinate database access, possibly
leading to database corruption.</p>

<tcl>hd_fragment unlink {unlink corruption} {unlinked database files}</tcl>
<h3>2.4 Unlinking or renaming a database file while in use</h3>

<p>If two processes have open connections to the same database file and
one process closes its connection, unlinks the file, then creates a new
................................................................................

<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and
<tt>FlushFileBuffers()</tt> under windows.  We call this flush of
pending writes a "sync".</p>

<p>Actually, if one is only concerned with atomic and consistent writes and
is willing to forego durable writes, the sync operation does not need
to wait until the content is completely stored on persistent media.  Instead,
the sync operation can be thought of as an I/O barrier.  As long as all
writes that occur before the sync are completed before any write that happens
................................................................................
historical bugs in SQLite (now fixed) that could cause database corruption.
And there may be yet a few more that remain undiscovered.  Because of the
extensive testing and widespread use of SQLite, bugs that result in
database corruption tend to be very obscure.  The likelihood
of an application encountering an SQLite bug is small.  To illustrate this,
an account is given below 
of all database-corruption bugs found in SQLite during the
two-year period from 2009-04-01 to 2013-04-15.
This account should give the reader an intuitive sense of the
kinds of bugs in SQLite that manage to slip through testing procedures
and make it into a release.</p>


<h3>7.1 False corruption reports due to database shrinkage</h3>

<p>If a database is written by SQLite version 3.7.0 or later and then
written again by SQLite version 3.6.23 or earlier in such a way as to
make the size of the database file decrease, then the next time that
SQLite version 3.7.0 access the database file, it might report that the
database file is corrupt.  The database file is not really corrupt, however.
Version 3.7.0 was simply begin overly zealous in its corruption detection.</p>

<p>The problem was fixed on 2011-02-20.  The fix first appears in
SQLite version 3.7.6.</p>

<h3>7.2 Corruption follow switches between rollback and WAL modes</h3>

<p>Repeatedly switching an SQLite database in and out of [WAL | WAL mode]
and running the [VACUUM] command in between switches, in one process or
thread, can cause another process or thread that has the database file
open to miss the fact that the database has changed.  That second process
or thread might then try to modify the database using a stale cache and
cause database corruption.</p>
................................................................................
has already completed, allowing that process to continue using the
database file without running recovery first.  If that process writes
to the file, then the file might go corrupt.  This race condition
had apparently existing in all prior versions of SQLite for Windows going
back to 2004.  But the race was very tight.  Practically speaking, you
need a fast multi-core machine in which you launch two processes to run
recovery at the same moment on two separate cores.  This defect was
on windows systems only and did not effect the posix OS interface.</p>







|







 







|







 







|







 







|












|




|







 







|
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
...
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
...
486
487
488
489
490
491
492
493
and will be unable to work around the POSIX advisory locking quirks.
A <tt>close()</tt> operation on one connection might unknowingly 
clear the locks on a different database connection, leading to database
corruption.</p>

<p>The scenario above sounds far-fetched.
But the SQLite developers are aware of at 
least one commercial product that was released
with exactly this bug.  The vendor came to the SQLite developers seeking
help in tracking down some infrequent database corruption issues they were
seeing on Linux and Mac.  The problem was eventually traced to the
fact that the application was linking against two separate copies of SQLite.
The solution was to change the application build procedures to link against
just one copy of SQLite instead of two.</p>

................................................................................
be select for use in an application that has to run on an NFS filesystem
that does not support POSIX advisory locking.</p>

<p>It is important that all connections to the same database file use 
the same locking protocol.
If one application is using POSIX advisory locks and another application
is using dot-file locking, then the two applications will not see each
other's locks and will not be able to coordinate database access, possibly
leading to database corruption.</p>

<tcl>hd_fragment unlink {unlink corruption} {unlinked database files}</tcl>
<h3>2.4 Unlinking or renaming a database file while in use</h3>

<p>If two processes have open connections to the same database file and
one process closes its connection, unlinks the file, then creates a new
................................................................................

<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and
<tt>FlushFileBuffers()</tt> under Windows.  We call this flush of
pending writes a "sync".</p>

<p>Actually, if one is only concerned with atomic and consistent writes and
is willing to forego durable writes, the sync operation does not need
to wait until the content is completely stored on persistent media.  Instead,
the sync operation can be thought of as an I/O barrier.  As long as all
writes that occur before the sync are completed before any write that happens
................................................................................
historical bugs in SQLite (now fixed) that could cause database corruption.
And there may be yet a few more that remain undiscovered.  Because of the
extensive testing and widespread use of SQLite, bugs that result in
database corruption tend to be very obscure.  The likelihood
of an application encountering an SQLite bug is small.  To illustrate this,
an account is given below 
of all database-corruption bugs found in SQLite during the
four-year period from 2009-04-01 to 2013-04-15.
This account should give the reader an intuitive sense of the
kinds of bugs in SQLite that manage to slip through testing procedures
and make it into a release.</p>


<h3>7.1 False corruption reports due to database shrinkage</h3>

<p>If a database is written by SQLite version 3.7.0 or later and then
written again by SQLite version 3.6.23 or earlier in such a way as to
make the size of the database file decrease, then the next time that
SQLite version 3.7.0 access the database file, it might report that the
database file is corrupt.  The database file is not really corrupt, however.
Version 3.7.0 was simply being overly zealous in its corruption detection.</p>

<p>The problem was fixed on 2011-02-20.  The fix first appears in
SQLite version 3.7.6.</p>

<h3>7.2 Corruption following switches between rollback and WAL modes</h3>

<p>Repeatedly switching an SQLite database in and out of [WAL | WAL mode]
and running the [VACUUM] command in between switches, in one process or
thread, can cause another process or thread that has the database file
open to miss the fact that the database has changed.  That second process
or thread might then try to modify the database using a stale cache and
cause database corruption.</p>
................................................................................
has already completed, allowing that process to continue using the
database file without running recovery first.  If that process writes
to the file, then the file might go corrupt.  This race condition
had apparently existing in all prior versions of SQLite for Windows going
back to 2004.  But the race was very tight.  Practically speaking, you
need a fast multi-core machine in which you launch two processes to run
recovery at the same moment on two separate cores.  This defect was
on Windows systems only and did not affect the posix OS interface.</p>

Changes to pages/lang.in

1135
1136
1137
1138
1139
1140
1141

1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
....
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
<h3>The RAISE() function</h3>

<p>^(A special SQL function RAISE() may be used within a trigger-program,)^
with the following syntax</p> 

<tcl>BubbleDiagram raise-function</tcl>


<p>^When one of the first three forms is called during trigger-program
execution, the specified [ON CONFLICT] processing is performed
(either ABORT, FAIL or ROLLBACK) and the current query terminates.
An error code of [SQLITE_CONSTRAINT] is returned to the application,
along with the specified error message.</p>

<p>^When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would of been executed are abandoned. ^No database
changes are rolled back.  ^If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
</p>

<tcl>hd_fragment temptrig {TEMP triggers on non-TEMP tables}</tcl>
<h3>TEMP Triggers on Non-TEMP Tables</h3>
................................................................................
[glob(<i>Y</i>,<i>X</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>^The REGEXP operator is a special syntax for the regexp()
user function.  ^No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  ^If a [application-defined SQL function] named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>^The MATCH operator is a special syntax for the match()
application-defined function.  ^The default match() function implementation
raises an exception and is not really useful for anything.
^But extensions can override the match() function with more
helpful logic.</p>







>
|

|





|







 







|
|
|







1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
....
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
<h3>The RAISE() function</h3>

<p>^(A special SQL function RAISE() may be used within a trigger-program,)^
with the following syntax</p> 

<tcl>BubbleDiagram raise-function</tcl>

<p>^(When one of RAISE(ROLLBACK,...), RAISE(ABORT,...) or RAISE(FAIL,...)
is called during trigger-program
execution, the specified [ON CONFLICT] processing is performed
the current query terminates.)^
An error code of [SQLITE_CONSTRAINT] is returned to the application,
along with the specified error message.</p>

<p>^When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would have been executed are abandoned. ^No database
changes are rolled back.  ^If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
</p>

<tcl>hd_fragment temptrig {TEMP triggers on non-TEMP tables}</tcl>
<h3>TEMP Triggers on Non-TEMP Tables</h3>
................................................................................
[glob(<i>Y</i>,<i>X</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>^The REGEXP operator is a special syntax for the regexp()
user function.  ^No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  ^If an [application-defined SQL function] named "regexp"
is added at run-time, then the "<i>X</i> REGEXP <i>Y</i>" operator will
be implemented as a call to "regexp(<i>Y</i>,<i>X</i>)".</p>

<tcl>hd_fragment match MATCH</tcl>
<p>^The MATCH operator is a special syntax for the match()
application-defined function.  ^The default match() function implementation
raises an exception and is not really useful for anything.
^But extensions can override the match() function with more
helpful logic.</p>

Changes to pages/lockingv3.in

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
    </li>
<li>Drop the EXCLUSIVE and PENDING locks from the database file.
    </li>
</ol>

<p>As soon as the PENDING lock is released from the database file, other
processes can begin reading the database again.  In the current implementation,
the RESERVED lock is also released, but that is not essential.  Future
versions of SQLite might provide a "CHECKPOINT" SQL command that will
commit all changes made so far within a transaction but retain the
RESERVED lock so that additional changes can be made without given
any other process an opportunity to write.</p>

<p>If a transaction involves multiple databases, then a more complex
commit sequence is used, as follows:</p>

<ol>
<li value="4">
   Make sure all individual database files have an EXCLUSIVE lock and a







|
|
<
<
<







376
377
378
379
380
381
382
383
384



385
386
387
388
389
390
391
    </li>
<li>Drop the EXCLUSIVE and PENDING locks from the database file.
    </li>
</ol>

<p>As soon as the PENDING lock is released from the database file, other
processes can begin reading the database again.  In the current implementation,
the RESERVED lock is also released, but that is not essential for
correct operation.</p>




<p>If a transaction involves multiple databases, then a more complex
commit sequence is used, as follows:</p>

<ol>
<li value="4">
   Make sure all individual database files have an EXCLUSIVE lock and a

Changes to pages/malloc.in

50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
errors.
</p></li>

<li><p>
<b>Memory usage limits.</b>
The [sqlite3_soft_heap_limit64()] mechanism allows the application to
set a memory usage limit that SQLite strives to stay below.  SQLite
will attempt to reuse memory from its caches rather than allocation new
memory as it approaches the soft limit.
</p></li>

<li><p>
<b>Zero-malloc option</b>
The application can provide SQLite with several buffers of bulk memory
at startup and SQLite will then use those provided buffers for all of
................................................................................

<p>Memsys6 was added in SQLite [version 3.6.1].
It is very experimental.  Its future is uncertain and it may be removed
in a subsequent release.  Update:  Memsys6 was removed as of 
[version 3.6.5].</p>

<p>Other experimental memory allocators might be added in future releases
of SQLite.  One many anticipate that these will be called memsys7, memsys8,
and so forth.</p>

<a name="appalloc"></a>
<h4>3.1.5 Application-defined memory allocators</h4>

<p>New memory allocators do not have to be part of the SQLite source tree
nor included in the sqlite3.c [amalgamation].  Individual applications can







|







 







|







50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
errors.
</p></li>

<li><p>
<b>Memory usage limits.</b>
The [sqlite3_soft_heap_limit64()] mechanism allows the application to
set a memory usage limit that SQLite strives to stay below.  SQLite
will attempt to reuse memory from its caches rather than allocating new
memory as it approaches the soft limit.
</p></li>

<li><p>
<b>Zero-malloc option</b>
The application can provide SQLite with several buffers of bulk memory
at startup and SQLite will then use those provided buffers for all of
................................................................................

<p>Memsys6 was added in SQLite [version 3.6.1].
It is very experimental.  Its future is uncertain and it may be removed
in a subsequent release.  Update:  Memsys6 was removed as of 
[version 3.6.5].</p>

<p>Other experimental memory allocators might be added in future releases
of SQLite.  One may anticipate that these will be called memsys7, memsys8,
and so forth.</p>

<a name="appalloc"></a>
<h4>3.1.5 Application-defined memory allocators</h4>

<p>New memory allocators do not have to be part of the SQLite source tree
nor included in the sqlite3.c [amalgamation].  Individual applications can

Changes to pages/optoverview.in

315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
  <ol>
  <li>^The left-hand side of the LIKE or GLOB operator must be the name
      of an indexed column with [affinity | TEXT affinity].</li>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>^The build-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>







|







315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
  <ol>
  <li>^The left-hand side of the LIKE or GLOB operator must be the name
      of an indexed column with [affinity | TEXT affinity].</li>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>^The built-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>

Changes to pages/pragma.in

127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
  
<p>    ^The application_id PRAGMA is used to query or set the 32-bit
       unsigned big-endian "Application ID" integer located at offset
       68 into the [database header].  Applications that use SQLite as their
       [application file-format] should set the Application ID integer to
       a unique integer so that utilities such as 
       [http://www.darwinsys.com/file/ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list
       assigned application IDs can be seen by consulting the
       [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt
        |magic.txt] file in the SQLite source repository.
}

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;







|







127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
  
<p>    ^The application_id PRAGMA is used to query or set the 32-bit
       unsigned big-endian "Application ID" integer located at offset
       68 into the [database header].  Applications that use SQLite as their
       [application file-format] should set the Application ID integer to
       a unique integer so that utilities such as 
       [http://www.darwinsys.com/file/ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list of
       assigned application IDs can be seen by consulting the
       [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt
        |magic.txt] file in the SQLite source repository.
}

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;

Changes to pages/queryplanner-ng.in

358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
There are no API changes nor modifications
to compilation procedures.</p>

<p>But as with any query planner change, upgrading to the NGQP does carry
a small risk of introducing performance regressions.  The problem here is
not that the NGQP is incorrect or buggy or inferior to the legacy query
planner.  Given reliable information about the selectivity of indices, 
the NGQP should always pick a plan than is as good or better than before.
The problem is that some applications may be using low-quality and
low-selectivity indices without having run [ANALYZE].  The older query
planners look at many fewer possible implementations for each query and 
so they may have stumbled over a good plan by stupid luck.  The NGQP, on 
the other hand, looks at many more query plan possibilities, and it may 
chose a different query plan that
works better in theory, assuming good indices, but which gives a performance







|







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
There are no API changes nor modifications
to compilation procedures.</p>

<p>But as with any query planner change, upgrading to the NGQP does carry
a small risk of introducing performance regressions.  The problem here is
not that the NGQP is incorrect or buggy or inferior to the legacy query
planner.  Given reliable information about the selectivity of indices, 
the NGQP should always pick a plan that is as good or better than before.
The problem is that some applications may be using low-quality and
low-selectivity indices without having run [ANALYZE].  The older query
planners look at many fewer possible implementations for each query and 
so they may have stumbled over a good plan by stupid luck.  The NGQP, on 
the other hand, looks at many more query plan possibilities, and it may 
chose a different query plan that
works better in theory, assuming good indices, but which gives a performance

Changes to pages/tempfiles.in

128
129
130
131
132
133
134
135


136
137
138
139
140
141
142
...
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
...
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other
words, journal mode PERSIST exhibits the same behavior as is seen
in EXCLUSIVE locking mode. The
OFF journal mode causes SQLite to the rollback journal.


The OFF journal mode disables the atomic
commit and rollback capabilities of SQLite.  The ROLLBACK command
is not available when OFF journal mode is set.  And if a crash or power
loss occurs in the middle of a transaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.
The MEMORY journal mode causes the rollback journal to be stored in
................................................................................

<p>
More complex queries may or may not be able to employ query
flattening to avoid the temporary table.  Whether or not
the query can be flattened depends on such factors as whether
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.8 Transient Indices</h3>

<p>
................................................................................
compared to the previous to see if it starts a new "group".
The ordering by GROUP BY terms is done in exactly the same way
as the ordering by ORDER BY terms.  A preexisting index is used
if possible, but if no suitable index is available, a transient
index is created.
</p>

<p>
The previous two paragraphs describe the implementation of SQLite
as of version 3.5.8.  There are known problems with this approach
for very large results sets - result sets that are larger than the
available disk cache.  Future versions of SQLite will likely address
this deficiency by completely reworking the sort algorithm for 
cases when no suitable preexisting sort index is available.  The
new sort algorithm will also use temporary files, but not in the
same way as the current implementation, the temporary files
for the new implementation will probably not be index files.
</p>

<p>
The DISTINCT keyword on an aggregate query is implemented by
creating a transient index in a temporary file and storing
each result row in that index.  As new result rows are computed
a check is made to see if they already exist in the transient
index and if they do the new result row is discarded.
</p>







|
>
>







 







|







 







<
<
<
<
<
<
<
<
<
<
<
<







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
...
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
...
464
465
466
467
468
469
470












471
472
473
474
475
476
477
as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other
words, journal mode PERSIST exhibits the same behavior as is seen
in EXCLUSIVE locking mode. The
OFF journal mode causes SQLite to omit the rollback journal, completely.
In other words, no rollback journal is every written if journal mode is
set to OFF.
The OFF journal mode disables the atomic
commit and rollback capabilities of SQLite.  The ROLLBACK command
is not available when OFF journal mode is set.  And if a crash or power
loss occurs in the middle of a transaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.
The MEMORY journal mode causes the rollback journal to be stored in
................................................................................

<p>
More complex queries may or may not be able to employ query
flattening to avoid the temporary table.  Whether or not
the query can be flattened depends on such factors as whether
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query can and cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.8 Transient Indices</h3>

<p>
................................................................................
compared to the previous to see if it starts a new "group".
The ordering by GROUP BY terms is done in exactly the same way
as the ordering by ORDER BY terms.  A preexisting index is used
if possible, but if no suitable index is available, a transient
index is created.
</p>













<p>
The DISTINCT keyword on an aggregate query is implemented by
creating a transient index in a temporary file and storing
each result row in that index.  As new result rows are computed
a check is made to see if they already exist in the transient
index and if they do the new result row is discarded.
</p>

Changes to pages/wal.in

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<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 well for very large transactions.  For transactions larger than







|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<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 well for very large transactions.  For transactions larger than