Documentation Source Text

Check-in [d9e242f92a]
Login

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

Overview
Comment:Fix a bunch of documentation typos reported on the mailing list.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d9e242f92a409193b3bc0910bdc28b3d64a074b3
User & Date: drh 2010-08-09 15:45:38.000
Context
2010-08-10
05:58
Fix typos reported on the mailing list. (check-in: a85aaa0b62 user: drh tags: trunk)
2010-08-09
15:45
Fix a bunch of documentation typos reported on the mailing list. (check-in: d9e242f92a user: drh tags: trunk)
14:30
Fix a typo in the datatype3.html document. (check-in: 9fec2f9dda user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
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
appliations 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 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 
Changes to pages/autoinc.in.
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<p>
^The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID. 
^If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly accending order.
</p>


<h2>The AUTOINCREMENT Keyword</h2>

<p>
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly







|







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<p>
^The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID. 
^If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly ascending order.
</p>


<h2>The AUTOINCREMENT Keyword</h2>

<p>
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
Changes to pages/backup.in.
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  the source database mid-backup, the user can be sure that when the backup
  operation is completed the backup database contains a consistent and 
  up-to-date snapshot of the original. However:

  <ul>
    <li> Writes to an in-memory source database, or writes to a file-based 
         source database by an external process or thread using a 
         database connection other than pDb are signficantly more expensive 
         than writes made to a file-based source database using pDb (as the
         entire backup operation must be restarted in the former two cases).

    <li> If the backup process is restarted frequently enough it may never
         run to completion and the backupDb() function may never return.
  </ul>
 







|







346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  the source database mid-backup, the user can be sure that when the backup
  operation is completed the backup database contains a consistent and 
  up-to-date snapshot of the original. However:

  <ul>
    <li> Writes to an in-memory source database, or writes to a file-based 
         source database by an external process or thread using a 
         database connection other than pDb are significantly more expensive 
         than writes made to a file-based source database using pDb (as the
         entire backup operation must be restarted in the former two cases).

    <li> If the backup process is restarted frequently enough it may never
         run to completion and the backupDb() function may never return.
  </ul>
 
Changes to pages/btreemodule.in.
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
      It is also possible for a write-cursor to modify the contents of a b-tree
      structure while other cursors are open on it. The b-tree module does not
      include any type of row-locking mechanism. It is possible for a write-cursor
      to be used to delete an entry from a b-tree structure even if there are
      one or more other cursors currently pointing to the entry being deleted.

    <p class=todo>
      Requirements to do with how the above is handled. Traceibility to 
      sqlite3BtreeCursorHasMoved is required.

    [h3 "Writing to the Database Image"]

    <p>
      The B-Tree module allows the user to write values to a subset of the
      fields from the database image header. The set of writable fields is







|







353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
      It is also possible for a write-cursor to modify the contents of a b-tree
      structure while other cursors are open on it. The b-tree module does not
      include any type of row-locking mechanism. It is possible for a write-cursor
      to be used to delete an entry from a b-tree structure even if there are
      one or more other cursors currently pointing to the entry being deleted.

    <p class=todo>
      Requirements to do with how the above is handled. Traceability to 
      sqlite3BtreeCursorHasMoved is required.

    [h3 "Writing to the Database Image"]

    <p>
      The B-Tree module allows the user to write values to a subset of the
      fields from the database image header. The set of writable fields is
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
      <p>
        The following requirements describe the seventh and eighth paramaters passed
        to the sqlite3BtreeInsert function. Both of these are used to provide extra
        information used by sqlite3BtreeInsert to optimize the insert operation. They
        may be safely ignored by alternative b-tree implementations.

      <p class=todo>
        There should be some rationalization for these, eventually. Some tracebility
        from somewhere to show how the b-tree module offering these slightly esoteric
        interfaces is helpful to SQLite overall.

        [fancyformat_import_requirement L50005]
        [fancyformat_import_requirement L50006]

      <p>
        If a non-zero value is passed as the eighth parameter to sqlite3BtreeInsert 
        and the b-tree cursor has not been positioned as assumed by L50006, the
        results are undefined.

      <p class=todo>
        Malloc and IO error handling. Maybe these should be grouped together
        for a whole bunch of APIs. And hook into the above via a defintion of
        "successful call".

      [h3 sqlite3BtreeIncrVacuum sqlite3BtreeIncrVacuum]
      [btree_api_defn sqlite3BtreeIncrVacuum]

    [h2 "Advisory B-Tree Locks"] 








|













|







889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
      <p>
        The following requirements describe the seventh and eighth paramaters passed
        to the sqlite3BtreeInsert function. Both of these are used to provide extra
        information used by sqlite3BtreeInsert to optimize the insert operation. They
        may be safely ignored by alternative b-tree implementations.

      <p class=todo>
        There should be some rationalization for these, eventually. Some traceability
        from somewhere to show how the b-tree module offering these slightly esoteric
        interfaces is helpful to SQLite overall.

        [fancyformat_import_requirement L50005]
        [fancyformat_import_requirement L50006]

      <p>
        If a non-zero value is passed as the eighth parameter to sqlite3BtreeInsert 
        and the b-tree cursor has not been positioned as assumed by L50006, the
        results are undefined.

      <p class=todo>
        Malloc and IO error handling. Maybe these should be grouped together
        for a whole bunch of APIs. And hook into the above via a definition of
        "successful call".

      [h3 sqlite3BtreeIncrVacuum sqlite3BtreeIncrVacuum]
      [btree_api_defn sqlite3BtreeIncrVacuum]

    [h2 "Advisory B-Tree Locks"] 

1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
        the following sections, a b-tree node page is considered to be a container
        for an ordered list of b-tree cells. Cells may be inserted into or removed
        from any position in the ordered list as required.

      <p>
	A b-tree node page has a finite capacity. If one of the algorithms
	described here is required to insert a cell into a b-tree node page,
	and there is not enough free space within the page to accomadate the
	cell, it is still nominally inserted into the requested position within
        the node, but becomes an overflow cell. Overflow cells never remain so
        for very long. If an insert, replace or delete entry operation creates
        one or more overflow cells, the b-tree structure is rearranged so that
        all cells are stored within the body of a b-tree node page before the
        operation is considered complete. This process of rearranging the b-tree
        structure is termed b-tree balancing, and is described in section 







|







1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
        the following sections, a b-tree node page is considered to be a container
        for an ordered list of b-tree cells. Cells may be inserted into or removed
        from any position in the ordered list as required.

      <p>
	A b-tree node page has a finite capacity. If one of the algorithms
	described here is required to insert a cell into a b-tree node page,
	and there is not enough free space within the page to accommodate the
	cell, it is still nominally inserted into the requested position within
        the node, but becomes an overflow cell. Overflow cells never remain so
        for very long. If an insert, replace or delete entry operation creates
        one or more overflow cells, the b-tree structure is rearranged so that
        all cells are stored within the body of a b-tree node page before the
        operation is considered complete. This process of rearranging the b-tree
        structure is termed b-tree balancing, and is described in section 
Changes to pages/c_interface.in.
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
</pre></blockquote>







|







180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */
</pre></blockquote>
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to an 
perviously undetected logic error in of SQLite. This value is also
returned if a disk I/O error occurs in such a way that SQLite is forced
to leave the database file in a corrupted state.  The latter should only
happen due to a hardware or operating system malfunction.
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is
no space left on the disk, or the database is too big to hold any







|







252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to an 
previously undetected logic error in of SQLite. This value is also
returned if a disk I/O error occurs in such a way that SQLite is forced
to leave the database file in a corrupted state.  The latter should only
happen due to a hardware or operating system malfunction.
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is
no space left on the disk, or the database is too big to hold any
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
In common usage, <b>sqlite_changes</b> returns the number
of rows inserted, deleted, or modified by the most recent <b>sqlite_exec</b>
call or since the most recent <b>sqlite_compile</b>.  But if you have
nested calls to <b>sqlite_exec</b> (that is, if the callback routine
of one <b>sqlite_exec</b> invokes another <b>sqlite_exec</b>) or if
you invoke <b>sqlite_compile</b> to create a new VM while there is
still another VM in existance, then
the meaning of the number returned by <b>sqlite_changes</b> is more
complex.
The number reported includes any changes
that were later undone by a ROLLBACK or ABORT.  But rows that are
deleted because of a DROP TABLE are <em>not</em> counted.</p>

<p>SQLite implements the command "<b>DELETE FROM table</b>" (without







|







653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
<b>sqlite_compile</b> that have not been finalized by <b>sqlite_finalize</b>.
In common usage, <b>sqlite_changes</b> returns the number
of rows inserted, deleted, or modified by the most recent <b>sqlite_exec</b>
call or since the most recent <b>sqlite_compile</b>.  But if you have
nested calls to <b>sqlite_exec</b> (that is, if the callback routine
of one <b>sqlite_exec</b> invokes another <b>sqlite_exec</b>) or if
you invoke <b>sqlite_compile</b> to create a new VM while there is
still another VM in existence, then
the meaning of the number returned by <b>sqlite_changes</b> is more
complex.
The number reported includes any changes
that were later undone by a ROLLBACK or ABORT.  But rows that are
deleted because of a DROP TABLE are <em>not</em> counted.</p>

<p>SQLite implements the command "<b>DELETE FROM table</b>" (without
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
will work just fine.  But suppose the user enters a string like 
"Hi y'all!".  The SQL statement generated reads as follows:

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y'all')
</pre></blockquote>

<p>This is not valid SQL because of the apostrophy in the word "y'all".
But if the %q formatting option is used instead of %s, like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%q')",
  0, 0, 0, zString);
</pre></blockquote>

<p>Then the generated SQL will look like the following:</p>

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y''all')
</pre></blockquote>

<p>Here the apostrophy has been escaped and the SQL statement is well-formed.
When generating SQL on-the-fly from data that might contain a
single-quote character ('), it is always a good idea to use the
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
</p>

<p>If the %Q formatting option is used instead of %q, like this:</p>








|














|







904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
will work just fine.  But suppose the user enters a string like 
"Hi y'all!".  The SQL statement generated reads as follows:

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y'all')
</pre></blockquote>

<p>This is not valid SQL because of the apostrophe in the word "y'all".
But if the %q formatting option is used instead of %s, like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%q')",
  0, 0, 0, zString);
</pre></blockquote>

<p>Then the generated SQL will look like the following:</p>

<blockquote><pre>
INSERT INTO table1 VALUES('Hi y''all')
</pre></blockquote>

<p>Here the apostrophe has been escaped and the SQL statement is well-formed.
When generating SQL on-the-fly from data that might contain a
single-quote character ('), it is always a good idea to use the
SQLite printf routines and the %q formatting option instead of <b>sprintf</b>.
</p>

<p>If the %Q formatting option is used instead of %q, like this:</p>

Changes to pages/capi3ref.in.
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
</ol>

<p>Stable interfaces will be maintained indefinitely in a backwards
compatible way.  An application that uses only stable interfaces
should always be able to relink against a newer version of SQLite
without any changes.</p>

<p>Experiemental interfaces are subject to change.  
Applications that use experimental interfaces
may need to be modified when upgrading to a newer SQLite release, though
this is rare.
When new interfaces are added to SQLite, they generally begin
as experimental interfaces.  After an interface has been in use for
a while and the developers are confident that the design of the interface
is sound and worthy of long-term support, the interface is marked







|







327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
</ol>

<p>Stable interfaces will be maintained indefinitely in a backwards
compatible way.  An application that uses only stable interfaces
should always be able to relink against a newer version of SQLite
without any changes.</p>

<p>Experimental interfaces are subject to change.  
Applications that use experimental interfaces
may need to be modified when upgrading to a newer SQLite release, though
this is rare.
When new interfaces are added to SQLite, they generally begin
as experimental interfaces.  After an interface has been in use for
a while and the developers are confident that the design of the interface
is sound and worthy of long-term support, the interface is marked
Changes to pages/changes.in.
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
<li> Various minor bug fixes and performance enhancements.
}

chng {2010 Jan 06 (3.6.22)} {
<li>Fix bugs that can (rarely) lead to incorrect query results when
    the CAST or OR operators are used in the WHERE clause of a query.
<li>Continuing enhancements and improvements to [FTS3].
<li>Other miscellanous 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







|







89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
<li> Various minor bug fixes and performance enhancements.
}

chng {2010 Jan 06 (3.6.22)} {
<li>Fix bugs that can (rarely) lead to incorrect query results when
    the CAST or OR operators are used in the WHERE clause of a query.
<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
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
<li>Fix an obscure segfault in UTF-8 to UTF-16 conversions</li>
<li>Added driver for OS/2</li>
<li>Correct column meta-information returned for aggregate queries</li>
<li>Enhanced output from EXPLAIN QUERY PLAN</li>
<li>LIMIT 0 now works on subqueries</li>
<li>Bug fixes and performance enhancements in the query optimizer</li>
<li>Correctly handle NULL filenames in ATTACH and DETACH</li>
<li>Inproved syntax error messages in the parser</li>
<li>Fix type coercion rules for the IN operator</li>
}

chng {2006 April 5 (3.3.5)} {
<li>CHECK constraints use conflict resolution algorithms correctly.</li>
<li>The SUM() function throws an error on integer overflow.</li>
<li>Choose the column names in a compound query from the left-most SELECT







|







931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
<li>Fix an obscure segfault in UTF-8 to UTF-16 conversions</li>
<li>Added driver for OS/2</li>
<li>Correct column meta-information returned for aggregate queries</li>
<li>Enhanced output from EXPLAIN QUERY PLAN</li>
<li>LIMIT 0 now works on subqueries</li>
<li>Bug fixes and performance enhancements in the query optimizer</li>
<li>Correctly handle NULL filenames in ATTACH and DETACH</li>
<li>Improved syntax error messages in the parser</li>
<li>Fix type coercion rules for the IN operator</li>
}

chng {2006 April 5 (3.3.5)} {
<li>CHECK constraints use conflict resolution algorithms correctly.</li>
<li>The SUM() function throws an error on integer overflow.</li>
<li>Choose the column names in a compound query from the left-most SELECT
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
isolation level of SERIALIZABLE) and table level locking when
database connections share a common cache.</li>
}

chng {2005 December 19 (3.2.8)} {
<li>Fix an obscure bug that can cause database corruption under the
following unusual circumstances: A large INSERT or UPDATE statement which 
is part of an even larger transaction fails due to a uniqueness contraint
but the containing transaction commits.</li>
}

chng {2005 December 19 (2.8.17)} {
<li>Fix an obscure bug that can cause database corruption under the
following unusual circumstances: A large INSERT or UPDATE statement which 
is part of an even larger transaction fails due to a uniqueness contraint







|







1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
isolation level of SERIALIZABLE) and table level locking when
database connections share a common cache.</li>
}

chng {2005 December 19 (3.2.8)} {
<li>Fix an obscure bug that can cause database corruption under the
following unusual circumstances: A large INSERT or UPDATE statement which 
is part of an even larger transaction fails due to a uniqueness constraint
but the containing transaction commits.</li>
}

chng {2005 December 19 (2.8.17)} {
<li>Fix an obscure bug that can cause database corruption under the
following unusual circumstances: A large INSERT or UPDATE statement which 
is part of an even larger transaction fails due to a uniqueness contraint
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
left table only</li>
}

chng {2005 September 17 (3.2.6)} {
<li>Fix a bug that can cause database corruption if a VACUUM (or
    autovacuum) fails and is rolled back on a database that is
    larger than 1GiB</li>
<li>LIKE optiization now works for columns with COLLATE NOCASE</li>
<li>ORDER BY and GROUP BY now use bounded memory</li>
<li>Added support for COUNT(DISTINCT expr)</li>
<li>Change the way SUM() handles NULL values in order to comply with
    the SQL standard</li>
<li>Use fdatasync() instead of fsync() where possible in order to speed
    up commits slightly</li>
<li>Use of the CROSS keyword in a join turns off the table reordering







|







1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
left table only</li>
}

chng {2005 September 17 (3.2.6)} {
<li>Fix a bug that can cause database corruption if a VACUUM (or
    autovacuum) fails and is rolled back on a database that is
    larger than 1GiB</li>
<li>LIKE optimization now works for columns with COLLATE NOCASE</li>
<li>ORDER BY and GROUP BY now use bounded memory</li>
<li>Added support for COUNT(DISTINCT expr)</li>
<li>Change the way SUM() handles NULL values in order to comply with
    the SQL standard</li>
<li>Use fdatasync() instead of fsync() where possible in order to speed
    up commits slightly</li>
<li>Use of the CROSS keyword in a join turns off the table reordering
Changes to pages/cintro.in.
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
       [sqlite3_column_int | sqlite3_column()] in between
       two calls to [sqlite3_step()]. </li>
  <li> Destroy the [prepared statement] using [sqlite3_finalize()]. </li>
</ol></p>

<p>
  The foregoing is all one really needs to know in order to use SQLite
  effectively.  All the rest is just ornimentation and detail.
</p>

<tcl>HEADING 1 {Convenience Wrappers Around Core Routines}</tcl>

<p>
  The [sqlite3_exec()] interface is a convenience wrapper that carries out
  all four of the above steps with a single function call.  A callback







|







250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
       [sqlite3_column_int | sqlite3_column()] in between
       two calls to [sqlite3_step()]. </li>
  <li> Destroy the [prepared statement] using [sqlite3_finalize()]. </li>
</ol></p>

<p>
  The foregoing is all one really needs to know in order to use SQLite
  effectively.  All the rest is just ornamentation and detail.
</p>

<tcl>HEADING 1 {Convenience Wrappers Around Core Routines}</tcl>

<p>
  The [sqlite3_exec()] interface is a convenience wrapper that carries out
  all four of the above steps with a single function call.  A callback
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
<p><ul>
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
</ul></p>

<p>
  After a [prepared statement] has been evaluated by one or more calls to
  [sqlite3_step()], it can be reset in order to be evaluted again by a
  call to [sqlite3_reset()].
  Using [sqlite3_reset()] on an existing [prepared statement] rather
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  In many SQL statements, the time needed
  to run [sqlite3_prepare()] equals or exceeds the time needed by
  [sqlite3_step()].  So avoiding calls to [sqlite3_prepare()] can result
  in a significant performance improvement.
</p>

<p>
  Usually, though, it is not useful to evaluate exactly the same SQL
  statement more than once.  More often, one wants to evalute similar
  statements.  For example, you might want to evaluate an INSERT statement
  multiple times though with different values to insert.  To accommodate
  this kind of flexibility, SQLite allows SQL statements to contain
  [parameter | parameters]
  which are "bound" to values prior to being evaluated.  These values can
  later be changed and the same [prepared statement] can be evaluated
  a second time using the new values.
</p>

<p>
  In SQLite, whereever it is valid to include a string literal, one can use
  a [parameter] in one of the following forms:
</p>

<p><ul>
  <li> <b>?</b> </li>
  <li> <b>?</b><i>NNN</i> </li>
  <li> <b>:</b><i>AAA</i> </li>







|












|










|







289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
<p><ul>
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>
</ul></p>

<p>
  After a [prepared statement] has been evaluated by one or more calls to
  [sqlite3_step()], it can be reset in order to be evaluated again by a
  call to [sqlite3_reset()].
  Using [sqlite3_reset()] on an existing [prepared statement] rather
  creating a new [prepared statement] avoids unnecessary calls to
  [sqlite3_prepare()].
  In many SQL statements, the time needed
  to run [sqlite3_prepare()] equals or exceeds the time needed by
  [sqlite3_step()].  So avoiding calls to [sqlite3_prepare()] can result
  in a significant performance improvement.
</p>

<p>
  Usually, though, it is not useful to evaluate exactly the same SQL
  statement more than once.  More often, one wants to evaluate similar
  statements.  For example, you might want to evaluate an INSERT statement
  multiple times though with different values to insert.  To accommodate
  this kind of flexibility, SQLite allows SQL statements to contain
  [parameter | parameters]
  which are "bound" to values prior to being evaluated.  These values can
  later be changed and the same [prepared statement] can be evaluated
  a second time using the new values.
</p>

<p>
  In SQLite, wherever it is valid to include a string literal, one can use
  a [parameter] in one of the following forms:
</p>

<p><ul>
  <li> <b>?</b> </li>
  <li> <b>?</b><i>NNN</i> </li>
  <li> <b>:</b><i>AAA</i> </li>
Changes to pages/compile.in.
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
  When this option is defined in the [amalgamation], version 3
  of the full-text search engine is added to the build automatically.
}

COMPILE_OPTION {SQLITE_ENABLE_FTS3_PARENTHESIS} {
  This option modifies the query pattern parser in FTS3 such that it
  supports operators AND and NOT (in addition to the usual OR and NEAR)
  and also allows query expressions to contain nested parenthesesis.
}

COMPILE_OPTION {SQLITE_ENABLE_ICU} {
  This option causes the 
  [http://www.icu-project.org/ | International Components for Unicode]
  or "ICU" extension to SQLite to be added to the build.  
}







|







284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
  When this option is defined in the [amalgamation], version 3
  of the full-text search engine is added to the build automatically.
}

COMPILE_OPTION {SQLITE_ENABLE_FTS3_PARENTHESIS} {
  This option modifies the query pattern parser in FTS3 such that it
  supports operators AND and NOT (in addition to the usual OR and NEAR)
  and also allows query expressions to contain nested parenthesis.
}

COMPILE_OPTION {SQLITE_ENABLE_ICU} {
  This option causes the 
  [http://www.icu-project.org/ | International Components for Unicode]
  or "ICU" extension to SQLite to be added to the build.  
}
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
COMPILE_OPTION {SQLITE_OMIT_BLOB_LITERAL} {
  When this option is defined, it is not possible to specify a blob in
  an SQL statement using the X'ABCD' syntax.
}

COMPILE_OPTION {SQLITE_OMIT_BTREECOUNT} {
  When this option is defined, an optimization that accelerates counting
  all entires in a table (in other words, an optimization that helps
  "SELECT count(*) FROM table" run faster) is omitted.
}

COMPILE_OPTION {SQLITE_OMIT_BUILTIN_TEST} {
  A standard SQLite build includes a small amount of logic controlled
  by the [sqlite3_test_control()] interface that is used to exercise
  parts of the SQLite core that are difficult to control and measure using







|







579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
COMPILE_OPTION {SQLITE_OMIT_BLOB_LITERAL} {
  When this option is defined, it is not possible to specify a blob in
  an SQL statement using the X'ABCD' syntax.
}

COMPILE_OPTION {SQLITE_OMIT_BTREECOUNT} {
  When this option is defined, an optimization that accelerates counting
  all entries in a table (in other words, an optimization that helps
  "SELECT count(*) FROM table" run faster) is omitted.
}

COMPILE_OPTION {SQLITE_OMIT_BUILTIN_TEST} {
  A standard SQLite build includes a small amount of logic controlled
  by the [sqlite3_test_control()] interface that is used to exercise
  parts of the SQLite core that are difficult to control and measure using
Changes to pages/consortium.in.
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
changes to SQLite coming from consortium members take priority 
over requests from all other sources.
Consortium members go to the front of the line.
</p></li>

<li><p>
We will be happy to recognize consortium members on the SQLite website
with a logo and/or a brief acknowledgment of their contribution to the
project.  This is an opportunity for companies to build good will by
demonstrating that they are giving back to the community.  Or, members 
can remain anonymous.
</p></li>

</ul>








|







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
changes to SQLite coming from consortium members take priority 
over requests from all other sources.
Consortium members go to the front of the line.
</p></li>

<li><p>
We will be happy to recognize consortium members on the SQLite website
with a logo and/or a brief acknowledgement of their contribution to the
project.  This is an opportunity for companies to build good will by
demonstrating that they are giving back to the community.  Or, members 
can remain anonymous.
</p></li>

</ul>

Changes to pages/custombuild.in.
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
will also be a little smaller.  Disabling the mutexes at compile-time
is a recommended optimization for applications where it makes sense.</p>

<p>When using SQLite as a shared library, an application can test to see
whether or not mutexes have been disabled using the
[sqlite3_threadsafe()] API.  Applications that link against SQLite at
run-time and use SQLite from multiple threads should probably check this
API to make sure they did not accidently get linked against a version of
the SQLite library that has its mutexes disabled.  Single-threaded
applications will, of course, work correctly regardless of whether or
not SQLite is configured to be threadsafe, though they will be a little
bit faster when using versions of SQLite with mutexes disabled.</p>

<p>SQLite mutexes can also be disabled at run-time using the
the [sqlite3_config()] interface.  To completely disable all mutexing,







|







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
will also be a little smaller.  Disabling the mutexes at compile-time
is a recommended optimization for applications where it makes sense.</p>

<p>When using SQLite as a shared library, an application can test to see
whether or not mutexes have been disabled using the
[sqlite3_threadsafe()] API.  Applications that link against SQLite at
run-time and use SQLite from multiple threads should probably check this
API to make sure they did not accidentally get linked against a version of
the SQLite library that has its mutexes disabled.  Single-threaded
applications will, of course, work correctly regardless of whether or
not SQLite is configured to be threadsafe, though they will be a little
bit faster when using versions of SQLite with mutexes disabled.</p>

<p>SQLite mutexes can also be disabled at run-time using the
the [sqlite3_config()] interface.  To completely disable all mutexing,
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
SQLite requires a recursive mutex.  Most modern pthread implementations
support recursive mutexes, but not all do.  For systems that do not
support recursive mutexes, it is recommended that applications operate
in single-threaded mode only.  If this is not possible, SQLite provides
an alternative recursive mutex implementation built on top of the
standard "fast" mutexes of pthreads.  This alternative
implementation should work correctly as long as pthread_equal() is
atomic and the processor has a coherient data cache.  The alternative
recursive mutex implementation is enabled by the following
compiler command-line switch:</p>

<blockquote><pre>
-DSQLITE_HOMEGROWN_RECURSIVE_MUTEX=1
</pre></blockquote>








|







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
SQLite requires a recursive mutex.  Most modern pthread implementations
support recursive mutexes, but not all do.  For systems that do not
support recursive mutexes, it is recommended that applications operate
in single-threaded mode only.  If this is not possible, SQLite provides
an alternative recursive mutex implementation built on top of the
standard "fast" mutexes of pthreads.  This alternative
implementation should work correctly as long as pthread_equal() is
atomic and the processor has a coherent data cache.  The alternative
recursive mutex implementation is enabled by the following
compiler command-line switch:</p>

<blockquote><pre>
-DSQLITE_HOMEGROWN_RECURSIVE_MUTEX=1
</pre></blockquote>

Changes to pages/datatype3.in.
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a difference on disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangably.</p>

<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 







|







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a difference on disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangeably.</p>

<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 
Changes to pages/docs.in.
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
  copy content from a disk file into an in-memory database or vice
  versa and it can make a hot backup of a live database.  This application
  note gives examples of how.
}
doc {Virtual R-Tree Tables} {rtree.html} {
  A description of the SQLite R-Tree extension. An R-Tree is a specialized
  data structure that supports fast multi-dimensional range queries often
  used in geo-spatial systems.
}
doc {Full Text Search} {fts3.html} {
  A description of the SQLite Full Text Search (FTS3) extension.
}

heading {Upgrading SQLite, Backwards Compatibility}








|







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
  copy content from a disk file into an in-memory database or vice
  versa and it can make a hot backup of a live database.  This application
  note gives examples of how.
}
doc {Virtual R-Tree Tables} {rtree.html} {
  A description of the SQLite R-Tree extension. An R-Tree is a specialized
  data structure that supports fast multi-dimensional range queries often
  used in geospatial systems.
}
doc {Full Text Search} {fts3.html} {
  A description of the SQLite Full Text Search (FTS3) extension.
}

heading {Upgrading SQLite, Backwards Compatibility}

Changes to pages/famous.in.
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<img src="images/foreignlogos/apple.gif" border="0">
</a></td>
<td valign="top">
[http://www.apple.com/ | Apple] uses SQLite for many functions 
within Mac OS-X, including 
[http://www.apple.com/macosx/features/mail.html | Apple Mail], 
[http://www.apple.com/macosx/features/safari.html | Safari], 
and in [http://www.apple.com/aperature/ | Aperture].
Apple uses SQLite in
the [http://www.apple.com/iphone/ | iPhone] and in the
[http://www.apple.com/ipodtouch/ | iPod touch] and in
[http://www.apple.com/itunes/ | iTunes] software.
</td></tr>

<tr><td valign="top">







|







35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<img src="images/foreignlogos/apple.gif" border="0">
</a></td>
<td valign="top">
[http://www.apple.com/ | Apple] uses SQLite for many functions 
within Mac OS-X, including 
[http://www.apple.com/macosx/features/mail.html | Apple Mail], 
[http://www.apple.com/macosx/features/safari.html | Safari], 
and in [http://www.apple.com/aperture/ | Aperture].
Apple uses SQLite in
the [http://www.apple.com/iphone/ | iPhone] and in the
[http://www.apple.com/ipodtouch/ | iPod touch] and in
[http://www.apple.com/itunes/ | iTunes] software.
</td></tr>

<tr><td valign="top">
Changes to pages/features.in.
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
will be transactional.</p></li>

<li><p><b>Database For Gadgets.</b>
SQLite is popular choice for the database engine in cellphones,
PDAs, MP3 players, set-top boxes, and other electronic gadgets.
SQLite has a small code footprint, makes efficient use of memory,
disk space, and disk bandwidth, is highly reliable, and requires
no maintenance from a Database Adminstrator.</p></li>

<li><p><b>Website Database.</b>
Because it requires no configuration and stores information in ordinary
disk files, SQLite is a popular choice as the database to back small
to medium-sized websites.</p></li>

<li><p><b>Stand-in For An Enterprise RDBMS.</b>







|







53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
will be transactional.</p></li>

<li><p><b>Database For Gadgets.</b>
SQLite is popular choice for the database engine in cellphones,
PDAs, MP3 players, set-top boxes, and other electronic gadgets.
SQLite has a small code footprint, makes efficient use of memory,
disk space, and disk bandwidth, is highly reliable, and requires
no maintenance from a Database Administrator.</p></li>

<li><p><b>Website Database.</b>
Because it requires no configuration and stores information in ordinary
disk files, SQLite is a popular choice as the database to back small
to medium-sized websites.</p></li>

<li><p><b>Stand-in For An Enterprise RDBMS.</b>
Changes to pages/fileformat2.in.
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
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.4 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
orginally 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.5 File change counter</h4>








|







186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
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.4 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.5 File change counter</h4>

350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
VFS implementation in implementing the database file locking primitives.
SQLite will not use the lock-byte page; it will never be read or written
by the SQLite core, though operating-system specific VFS implementions may
choose to read or write bytes on the lock-byte page according to the 
needs and proclavities of the underlying system.  The unix and win32
VFS implementations that come built into SQLite do not write to the
lock-byte page, but we are aware of third-party VFS implementations for
other operating systems that do sometimes write to the lock-byte page.</p>

<h3>1.4 The Freelist</h3>

<p>A database file might contain one or more pages that are not in







|

|







350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
VFS implementation in implementing the database file locking primitives.
SQLite will not use the lock-byte page; it will never be read or written
by the SQLite core, though operating-system specific VFS implementations may
choose to read or write bytes on the lock-byte page according to the 
needs and proclivities of the underlying system.  The unix and win32
VFS implementations that come built into SQLite do not write to the
lock-byte page, but we are aware of third-party VFS implementations for
other operating systems that do sometimes write to the lock-byte page.</p>

<h3>1.4 The Freelist</h3>

<p>A database file might contain one or more pages that are not in
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
</p>

<p>A freeblock is a structure used to identify unallocated space within
a b-tree page.  Freeblocks are organized on a chain.  The first 2 bytes of
a freeblock are a big-endian integer which is the offset in the b-tree page
of the next freeblock in the chain, or zero if the freeblock is the last on
the chain.  The third and fourth bytes of each freeblock form
a bit-endian integers which is the size of the freeblock in bytes, including
the 4-byte header.  Freeblocks are always connected in order 
of increasing offset.  The second field of the b-tree page header is the
offset of the first freeblock, or zero if there are no freeblocks on the
page.  In a well-formed b-tree page, there will always be at least one cell
before the first freeblock.</p>

<p>A freeblock requires at least 4 bytes of space.  If there is an isolated







|







535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
</p>

<p>A freeblock is a structure used to identify unallocated space within
a b-tree page.  Freeblocks are organized on a chain.  The first 2 bytes of
a freeblock are a big-endian integer which is the offset in the b-tree page
of the next freeblock in the chain, or zero if the freeblock is the last on
the chain.  The third and fourth bytes of each freeblock form
a big-endian integer which is the size of the freeblock in bytes, including
the 4-byte header.  Freeblocks are always connected in order 
of increasing offset.  The second field of the b-tree page header is the
offset of the first freeblock, or zero if there are no freeblocks on the
page.  In a well-formed b-tree page, there will always be at least one cell
before the first freeblock.</p>

<p>A freeblock requires at least 4 bytes of space.  If there is an isolated
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890

<p>A collating function for each column is necessary in order to compute
the order of text fields.  SQLite defines three built-in collating functions:
</p>

<blockquote><table border=0 cellspacing=10>
<tr><td valign=top>BINARY
    <td>Strings are comparied byte by byte using the memcmp() function
        from the standard C library.
<tr><td valign=top>NOCASE
    <td>Like BINARY except that uppercase ASCII characters ('A' through 'Z')
        are folded into their lowercase equivalents prior to running the
        comparison.  Note that only ASCII characters are case-folded.  NOCASE
        does not implement a general purpose unicode caseless comparison.
<tr><td valign=top>RTRIM







|







876
877
878
879
880
881
882
883
884
885
886
887
888
889
890

<p>A collating function for each column is necessary in order to compute
the order of text fields.  SQLite defines three built-in collating functions:
</p>

<blockquote><table border=0 cellspacing=10>
<tr><td valign=top>BINARY
    <td>Strings are compared byte by byte using the memcmp() function
        from the standard C library.
<tr><td valign=top>NOCASE
    <td>Like BINARY except that uppercase ASCII characters ('A' through 'Z')
        are folded into their lowercase equivalents prior to running the
        comparison.  Note that only ASCII characters are case-folded.  NOCASE
        does not implement a general purpose unicode caseless comparison.
<tr><td valign=top>RTRIM
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
unsigned integer.  Add the value of that integer to the checksum.
<li>Subtrace 200 from X.
<li>If X is greater than or equal to zero, go back to step 3.
</ol>

<p>The checksum value is used to guard against incomplete writes of
a journal page record following a power failure.  A different random nonce
is used each time a trasaction is started in order to minimize the risk
that unwritten sectors might by chance contain data from the same page
that was a part of prior journals.  By changing the nonce for each
transaction, stale data on disk will still generate an incorrect checksum
and be detected with high probability.  The checksum only uses a sparce sample
of 32-bit words from the data record for performance reasons - design studies 
during the planning phases of SQLite 3.0.0 showed
a significant performance hit in checksumming the entire page.</p>







|







1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
unsigned integer.  Add the value of that integer to the checksum.
<li>Subtrace 200 from X.
<li>If X is greater than or equal to zero, go back to step 3.
</ol>

<p>The checksum value is used to guard against incomplete writes of
a journal page record following a power failure.  A different random nonce
is used each time a transaction is started in order to minimize the risk
that unwritten sectors might by chance contain data from the same page
that was a part of prior journals.  By changing the nonce for each
transaction, stale data on disk will still generate an incorrect checksum
and be detected with high probability.  The checksum only uses a sparce sample
of 32-bit words from the data record for performance reasons - design studies 
during the planning phases of SQLite 3.0.0 showed
a significant performance hit in checksumming the entire page.</p>
Changes to pages/fileio.in.
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
        database file, SQLite can reconstruct the original database 
        (before the modifications were attempted) based on the contents of 
        the <i>journal file</i>.

    <li><p>SQLite is required to <b>implement isolated transactions</b> (the 'I'
        from the ACID acronym). 

        <p>This is done by using the file locking facililities provided by the
        VFS adaptor to serialize writers (write transactions) and preventing
        readers (read transactions) from accessing database files while writers
        are midway through updating them.

    <li><p>For performance reasons, it is advantageous to <b>minimize the 
        quantity of data read and written</b> to and from the file-system.








|







104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
        database file, SQLite can reconstruct the original database 
        (before the modifications were attempted) based on the contents of 
        the <i>journal file</i>.

    <li><p>SQLite is required to <b>implement isolated transactions</b> (the 'I'
        from the ACID acronym). 

        <p>This is done by using the file locking facilities provided by the
        VFS adaptor to serialize writers (write transactions) and preventing
        readers (read transactions) from accessing database files while writers
        are midway through updating them.

    <li><p>For performance reasons, it is advantageous to <b>minimize the 
        quantity of data read and written</b> to and from the file-system.

186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
    <p>
      Section <cite>vfs_assumptions</cite> of this document describes the
      various assumptions made about the system to which the VFS adaptor
      component provides access. The basic capabilities and functions 
      required from the VFS implementation are presented along with the
      description of the VFS interface in 
      <cite>capi_sqlitert_requirements</cite>. Section
      <cite>vfs_assumptions</cite> compliments this by describing in more
      detail the assumptions made about VFS implementations on which the
      algorithms presented in this document depend. Some of these assumptions
      relate to performance issues, but most concern the expected state of
      the file-system following a failure that occurs midway through 
      modifying a database file.

    <p>







|







186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
    <p>
      Section <cite>vfs_assumptions</cite> of this document describes the
      various assumptions made about the system to which the VFS adaptor
      component provides access. The basic capabilities and functions 
      required from the VFS implementation are presented along with the
      description of the VFS interface in 
      <cite>capi_sqlitert_requirements</cite>. Section
      <cite>vfs_assumptions</cite> complements this by describing in more
      detail the assumptions made about VFS implementations on which the
      algorithms presented in this document depend. Some of these assumptions
      relate to performance issues, but most concern the expected state of
      the file-system following a failure that occurs midway through 
      modifying a database file.

    <p>
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
      Section <cite>writing_data</cite> describes the steps required to
      open a <i>write transaction </i> and write data to a database file.

    <p>
      Section <cite>rollback</cite> describes the way in which aborted
      <i>write transactions</i> may be rolled back (reverted), either as
      a result of an explicit user directive or because an application,
      operating system or power failure occured while SQLite was midway
      through updating a database file.

    <p>
      Section <cite>page_cache_algorithms</cite> describes some of the
      algorithms used to determine exactly which portions of the database
      file are cached by a <i>page cache</i>, and the effect that they
      have on the quantity and nature of the required VFS operations.







|







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
      Section <cite>writing_data</cite> describes the steps required to
      open a <i>write transaction </i> and write data to a database file.

    <p>
      Section <cite>rollback</cite> describes the way in which aborted
      <i>write transactions</i> may be rolled back (reverted), either as
      a result of an explicit user directive or because an application,
      operating system or power failure occurred while SQLite was midway
      through updating a database file.

    <p>
      Section <cite>page_cache_algorithms</cite> describes some of the
      algorithms used to determine exactly which portions of the database
      file are cached by a <i>page cache</i>, and the effect that they
      have on the quantity and nature of the required VFS operations.
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
      the second sector of the file is in the transient state. If a 
      power failure or operating system crash occurs before or during
      the next call to xSync() on the file handle, then following system
      recovery SQLite assumes that all file data between byte offsets 2048 
      and 4095, inclusive, is invalid. It also assumes that since the first
      sector of the file, containing the data from byte offset 0 to 2047 
      inclusive, is valid, since it was not in a transient state when the 
      crash occured.

    <p>
      Assuming that any and all sectors in the transient state may be 
      corrupted following a power or system failure is a very pessimistic
      approach. Some modern systems provide more sophisticated guarantees
      than this. SQLite allows the VFS implementation to specify at runtime
      that the current platform supports zero or more of the following 







|







319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
      the second sector of the file is in the transient state. If a 
      power failure or operating system crash occurs before or during
      the next call to xSync() on the file handle, then following system
      recovery SQLite assumes that all file data between byte offsets 2048 
      and 4095, inclusive, is invalid. It also assumes that since the first
      sector of the file, containing the data from byte offset 0 to 2047 
      inclusive, is valid, since it was not in a transient state when the 
      crash occurred.

    <p>
      Assuming that any and all sectors in the transient state may be 
      corrupted following a power or system failure is a very pessimistic
      approach. Some modern systems provide more sophisticated guarantees
      than this. SQLite allows the VFS implementation to specify at runtime
      that the current platform supports zero or more of the following 
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
          <p>If a system supports <i>sequential-write</i> it is assumed that 
          <i>syncing</i> any file within the file system flushes all write
          operations on all files (not just the <i>synced</i> file) to
          the persistent media. If a failure does occur, it is not known
          whether or not any of the write operations performed by SQLite 
          since the last time a file was <i>synced</i>. SQLite is able to
          assume that if the write operations of unknown status are arranged
          in the order that they occured:
          <ol> 
            <li> the first <i>n</i> operations will have been executed 
                 successfully,
            <li> the next operation puts all device sectors that it modifies
                 into the transient state, so that following recovery each
                 sector may be partially written, completely written, not
                 written at all or populated with garbage data,







|







370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
          <p>If a system supports <i>sequential-write</i> it is assumed that 
          <i>syncing</i> any file within the file system flushes all write
          operations on all files (not just the <i>synced</i> file) to
          the persistent media. If a failure does occur, it is not known
          whether or not any of the write operations performed by SQLite 
          since the last time a file was <i>synced</i>. SQLite is able to
          assume that if the write operations of unknown status are arranged
          in the order that they occurred:
          <ol> 
            <li> the first <i>n</i> operations will have been executed 
                 successfully,
            <li> the next operation puts all device sectors that it modifies
                 into the transient state, so that following recovery each
                 sector may be partially written, completely written, not
                 written at all or populated with garbage data,
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
      The formalized assumptions in this section refer to <i>system failure</i>
      events.  In this context, this should be interpreted as any failure that
      causes the system to stop operating. For example a power failure or
      operating system crash.

    <p>
      SQLite does not assume that a <b>create file</b> operation has actually
      modified the file-system records within perisistent storage until
      after the file has been successfully <i>synced</i>.

    ASSUMPTION A21001
      If a system failure occurs during or after a "create file"
      operation, but before the created file has been <i>synced</i>, then 
      SQLite assumes that it is possible that the created file may not
      exist following system recovery.







|







424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
      The formalized assumptions in this section refer to <i>system failure</i>
      events.  In this context, this should be interpreted as any failure that
      causes the system to stop operating. For example a power failure or
      operating system crash.

    <p>
      SQLite does not assume that a <b>create file</b> operation has actually
      modified the file-system records within persistent storage until
      after the file has been successfully <i>synced</i>.

    ASSUMPTION A21001
      If a system failure occurs during or after a "create file"
      operation, but before the created file has been <i>synced</i>, then 
      SQLite assumes that it is possible that the created file may not
      exist following system recovery.
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
      recovery. This includes regions of the sectors that were not
      actually modified by the write file operation.

    ASSUMPTION A21011
      If a system failure occurs on a system that supports the 
      <i>atomic-write</i> property for blocks of size <i>N</i> bytes
      following an aligned write of <i>N</i> 
      bytes to a file but before the file has been succesfully <i>synced</i>,
      then is is assumed following recovery that all sectors spanned by the
      write operation were correctly updated, or that none of the sectors were
      modified at all.

    ASSUMPTION A21012
      If a system failure occurs on a system that supports the 
      <i>safe-append</i> following a write operation that appends data
      to the end of the file without modifying any of the existing file 
      content but before the file has been succesfully <i>synced</i>,
      then is is assumed following recovery that either the data was
      correctly appended to the file, or that the file size remains 
      unchanged. It is assumed that it is impossible that the file be
      extended but populated with incorrect data.
      
    ASSUMPTION A21013
      Following a system recovery, if a device sector is deemed to be







|








|







518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
      recovery. This includes regions of the sectors that were not
      actually modified by the write file operation.

    ASSUMPTION A21011
      If a system failure occurs on a system that supports the 
      <i>atomic-write</i> property for blocks of size <i>N</i> bytes
      following an aligned write of <i>N</i> 
      bytes to a file but before the file has been successfully <i>synced</i>,
      then is is assumed following recovery that all sectors spanned by the
      write operation were correctly updated, or that none of the sectors were
      modified at all.

    ASSUMPTION A21012
      If a system failure occurs on a system that supports the 
      <i>safe-append</i> following a write operation that appends data
      to the end of the file without modifying any of the existing file 
      content but before the file has been successfully <i>synced</i>,
      then is is assumed following recovery that either the data was
      correctly appended to the file, or that the file size remains 
      unchanged. It is assumed that it is impossible that the file be
      extended but populated with incorrect data.
      
    ASSUMPTION A21013
      Following a system recovery, if a device sector is deemed to be
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586

    ASSUMPTION A21015
      If a system failure occurs on a system that supports the
      <i>sequential-write</i> property, then it is assumed that the set
      of possible states that the file-system may be in following recovery
      is the same as if each of the write operations performed since the most
      recent time a file was <i>synced</i> was itself followed by a <i>sync
      file</i> operation, and that the system failure may have occured during
      any of the write or <i>sync file</i> operations.


<!--
    <p>
      The return value of the xSectorSize() method, the <i>sector-size</i>, is
      expected by SQLite to be a power of 2 value greater than or equal to 512.







|







572
573
574
575
576
577
578
579
580
581
582
583
584
585
586

    ASSUMPTION A21015
      If a system failure occurs on a system that supports the
      <i>sequential-write</i> property, then it is assumed that the set
      of possible states that the file-system may be in following recovery
      is the same as if each of the write operations performed since the most
      recent time a file was <i>synced</i> was itself followed by a <i>sync
      file</i> operation, and that the system failure may have occurred during
      any of the write or <i>sync file</i> operations.


<!--
    <p>
      The return value of the xSectorSize() method, the <i>sector-size</i>, is
      expected by SQLite to be a power of 2 value greater than or equal to 512.
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
          using the xWrite() method of an open file-handle, the sector
          is said to be in a transient state, where the operating system
          makes no guarantees about the actual content of the sector on the
          persistent media. The sector remains in the transient state until
          the next successful call to xSync() on the same file-handle 
          returns. If a power failure or operating system crash occurs, then
          part or all of all sectors in the transient state when the crash
          occured may contain invalid data following system recovery.
      <li>
          Following a power failure or operating system crash, the content
          of all sectors that were not in a transient state when the crash
          occured may be trusted.
    </ul>

    <p class=todo>
      What do we assume about the other three file-system write 
      operations - xTruncate(), xDelete() and "create file"?









|



|







604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
          using the xWrite() method of an open file-handle, the sector
          is said to be in a transient state, where the operating system
          makes no guarantees about the actual content of the sector on the
          persistent media. The sector remains in the transient state until
          the next successful call to xSync() on the same file-handle 
          returns. If a power failure or operating system crash occurs, then
          part or all of all sectors in the transient state when the crash
          occurred may contain invalid data following system recovery.
      <li>
          Following a power failure or operating system crash, the content
          of all sectors that were not in a transient state when the crash
          occurred may be trusted.
    </ul>

    <p class=todo>
      What do we assume about the other three file-system write 
      operations - xTruncate(), xDelete() and "create file"?


632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
          may contain invalid data. The file-system guarantees
      <li>The <b><i>safe-append</i></b> property.
      <li>The <b><i>atomic write</i></b> property.
    </ul>

    <p class=todo>
      Write an explanation as to how the file-system properties influence
      the model used to predict file damage after a catastrophy.
 -->
 

<h1 id=database_connections>Database Connections</h1>

  <p>
    Within this document, the term <i>database connection</i> has a slightly







|







632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
          may contain invalid data. The file-system guarantees
      <li>The <b><i>safe-append</i></b> property.
      <li>The <b><i>atomic write</i></b> property.
    </ul>

    <p class=todo>
      Write an explanation as to how the file-system properties influence
      the model used to predict file damage after a catastrophe.
 -->
 

<h1 id=database_connections>Database Connections</h1>

  <p>
    Within this document, the term <i>database connection</i> has a slightly
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015

    REQ H35030

    REQ H35120

    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file refered
      to in the above requirements.

    REQ H35040

    <p>
      The <i>cache validiation</i> process is described in detail in section
      <cite>cache_validation</cite>

    REQ H35050

    <p>
      The numbered list above notes that the data for the first page of the
      database file, if it exists and is not already loaded into the <i>page







|





|







995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015

    REQ H35030

    REQ H35120

    <p>
      Section <cite>hot_journal_detection</cite> contains a description of
      and requirements governing the detection of a hot-journal file referred
      to in the above requirements.

    REQ H35040

    <p>
      The <i>cache validation</i> process is described in detail in section
      <cite>cache_validation</cite>

    REQ H35050

    <p>
      The numbered list above notes that the data for the first page of the
      database file, if it exists and is not already loaded into the <i>page
1278
1279
1280
1281
1282
1283
1284

1285
1286
1287
1288
1289
1290
1291
1292
        SQLite solves this problem by using a <i>journal file</i>. In almost
        all cases, before the <i>database file</i> is modified in any way, 
        SQLite stores sufficient information in the <i>journal file</i> to
        allow the original the database file to be reconstructed if a system
        failure occurs while the database file is being updated to reflect
        the modifications made by the <i>write transaction</i>. Each time
        SQLite opens a database file, it checks if such a system failure has

        occured and, if so, reconstructs the database file based on the contents
        of the journal file. The procedure used to detect whether or not this
        process, coined <i>hot journal rollback</i>, is required is described
        in section <cite>hot_journal_detection</cite>. <i>Hot journal rollback
        </i> itself is described in section <cite>hot_journal_rollback</cite>.
        <p>
        The same technique ensures that an SQLite database file cannot be
        corrupted by a system failure that occurs at an inopportune moment.







>
|







1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
        SQLite solves this problem by using a <i>journal file</i>. In almost
        all cases, before the <i>database file</i> is modified in any way, 
        SQLite stores sufficient information in the <i>journal file</i> to
        allow the original the database file to be reconstructed if a system
        failure occurs while the database file is being updated to reflect
        the modifications made by the <i>write transaction</i>. Each time
        SQLite opens a database file, it checks if such a system failure has
        occurred and, if so, 
        reconstructs the database file based on the contents
        of the journal file. The procedure used to detect whether or not this
        process, coined <i>hot journal rollback</i>, is required is described
        in section <cite>hot_journal_detection</cite>. <i>Hot journal rollback
        </i> itself is described in section <cite>hot_journal_rollback</cite>.
        <p>
        The same technique ensures that an SQLite database file cannot be
        corrupted by a system failure that occurs at an inopportune moment.
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
           storage.
    </ol> 

    <p>
      If all three of the steps enumerated above are executed successfully,
      then it is safe to modify the content of the <i>journalled</i> 
      database pages within the database file itself. The combination of
      the three steps above is refered to as <i>syncing the journal file</i>.
 
    REQ H35750

    REQ H35760

    REQ H35770








|







1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
           storage.
    </ol> 

    <p>
      If all three of the steps enumerated above are executed successfully,
      then it is safe to modify the content of the <i>journalled</i> 
      database pages within the database file itself. The combination of
      the three steps above is referred to as <i>syncing the journal file</i>.
 
    REQ H35750

    REQ H35760

    REQ H35770

1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
      summarized as follows:

    <ol>
      <li><p>
        The database file header <i>change counter</i> field is incremented.
        The <i>change counter</i>, described in
        <cite>ff_sqlitert_requirements</cite>, is used by the <i>cache
        validiation</i> procedure described in section
        <cite>cache_validation</cite>.

      <li><p> 
        The <i>journal file</i> is synced. The steps required to <i>sync the
        journal file</i> are described in section
        <cite>syncing_journal_file</cite>.








|







1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
      summarized as follows:

    <ol>
      <li><p>
        The database file header <i>change counter</i> field is incremented.
        The <i>change counter</i>, described in
        <cite>ff_sqlitert_requirements</cite>, is used by the <i>cache
        validation</i> procedure described in section
        <cite>cache_validation</cite>.

      <li><p> 
        The <i>journal file</i> is synced. The steps required to <i>sync the
        journal file</i> are described in section
        <cite>syncing_journal_file</cite>.

Changes to pages/formatchng.in.
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
  <td valign="top">2006-Jan-10</td>
  <td><p>Version 3.3.0 adds support for descending indices and
  uses a new encoding for boolean values that requires
  less disk space.  Version 3.3.0 can read and write database
  files created by prior versions of SQLite.  But prior versions
  of SQLite will not be able to read or write databases created
  by Version 3.3.0</p>
  <p>If you need backwards and forwards capatibility, you can
  compile with -DSQLITE_DEFAULT_FILE_FORMAT=1.  Or at runtime
  you can say "PRAGMA legacy_file_format=ON" prior to creating
  a new database file</p>
  <p>Once a database file is created, its format is fixed.  So
  a database file created by SQLite 3.2.8 and merely modified
  by version 3.3.0 or later will retain the old format.  Except,
  the VACUUM command recreates the database so running VACUUM







|







211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
  <td valign="top">2006-Jan-10</td>
  <td><p>Version 3.3.0 adds support for descending indices and
  uses a new encoding for boolean values that requires
  less disk space.  Version 3.3.0 can read and write database
  files created by prior versions of SQLite.  But prior versions
  of SQLite will not be able to read or write databases created
  by Version 3.3.0</p>
  <p>If you need backwards and forwards compatibility, you can
  compile with -DSQLITE_DEFAULT_FILE_FORMAT=1.  Or at runtime
  you can say "PRAGMA legacy_file_format=ON" prior to creating
  a new database file</p>
  <p>Once a database file is created, its format is fixed.  So
  a database file created by SQLite 3.2.8 and merely modified
  by version 3.3.0 or later will retain the old format.  Except,
  the VACUUM command recreates the database so running VACUUM
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
<tr>
  <td valign="top">3.4.2 to 3.5.0</td>
  <td valign="top">2007-Sep-3</td>
  <td><p>The design of the OS interface layer was changed for
  release 3.5.0.  Applications that implemented a custom OS
  interface will need to be modified in order to upgrade.
  There are also some subtly different semantics a few obscure
  APIs.  An <a href="34to35.html">article</a> is avilable which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
<tr>
  <td valign="top">3.5.9 to 3.6.0</td>
  <td valign="top">2008-July-16</td>
  <td><p>There are minor tweaks to the new OS interface layer that
  was added in version 3.5.0.
  Applications that implemented a custom OS
  interface will need to be adjusted.
  An <a href="35to36.html">article</a> is avilable which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
</table>
</blockquote>







|












|







250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
<tr>
  <td valign="top">3.4.2 to 3.5.0</td>
  <td valign="top">2007-Sep-3</td>
  <td><p>The design of the OS interface layer was changed for
  release 3.5.0.  Applications that implemented a custom OS
  interface will need to be modified in order to upgrade.
  There are also some subtly different semantics a few obscure
  APIs.  An <a href="34to35.html">article</a> is available which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
<tr>
  <td valign="top">3.5.9 to 3.6.0</td>
  <td valign="top">2008-July-16</td>
  <td><p>There are minor tweaks to the new OS interface layer that
  was added in version 3.5.0.
  Applications that implemented a custom OS
  interface will need to be adjusted.
  An <a href="35to36.html">article</a> is available which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
</table>
</blockquote>
Changes to pages/fts3.in.
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
    "oid" and "_oid_" identifiers. Attempting to insert or update a row with a 
    docid value that already exists in the table is an error, just as it would 
    be with an ordinary SQLite table.

  <p>
    There is one other subtle difference between "docid" and the normal SQLite
    aliases for the rowid column. Normally, if an INSERT or UPDATE statement 
    assigns discreet values to two or more aliases of the rowid column, SQLite 
    writes the rightmost of such values specified in the INSERT or UPDATE
    statement to the database. However, assigning a non-NULL value to both
    the "docid" and one or more of the SQLite rowid aliases when inserting or
    updating an FTS3 table is considered an error. See below for an example.

<codeblock>
  <i>-- Create an FTS3 table</i>







|







154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
    "oid" and "_oid_" identifiers. Attempting to insert or update a row with a 
    docid value that already exists in the table is an error, just as it would 
    be with an ordinary SQLite table.

  <p>
    There is one other subtle difference between "docid" and the normal SQLite
    aliases for the rowid column. Normally, if an INSERT or UPDATE statement 
    assigns discrete values to two or more aliases of the rowid column, SQLite 
    writes the rightmost of such values specified in the INSERT or UPDATE
    statement to the database. However, assigning a non-NULL value to both
    the "docid" and one or more of the SQLite rowid aliases when inserting or
    updating an FTS3 table is considered an error. See below for an example.

<codeblock>
  <i>-- Create an FTS3 table</i>
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
  FTS3 tables can be queried efficiently using SELECT statements of two
  different forms:

<ul>
  <li><p>
    <b>Query by rowid</b>. If the WHERE clause of the SELECT statement
    contains a sub-clause of the form "rowid = ?", where ? is an SQL expression,
    FTS3 is able to retreive the requested row directly using the equivalent 
    of an SQLite [INTEGER PRIMARY KEY] index.

  <li><p>
    <b>Full-text query</b>. If the WHERE clause of the SELECT statement contains
    a sub-clause of the form "&lt;column&gt; MATCH ?", FTS3 is able to use 
    the built-in full-text index to restrict the search to those documents 
    that match the full-text query string specified as the right-hand operand







|







231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
  FTS3 tables can be queried efficiently using SELECT statements of two
  different forms:

<ul>
  <li><p>
    <b>Query by rowid</b>. If the WHERE clause of the SELECT statement
    contains a sub-clause of the form "rowid = ?", where ? is an SQL expression,
    FTS3 is able to retrieve the requested row directly using the equivalent 
    of an SQLite [INTEGER PRIMARY KEY] index.

  <li><p>
    <b>Full-text query</b>. If the WHERE clause of the SELECT statement contains
    a sub-clause of the form "&lt;column&gt; MATCH ?", FTS3 is able to use 
    the built-in full-text index to restrict the search to those documents 
    that match the full-text query string specified as the right-hand operand
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
  At first glance, the final two full-text queries in the example above seem
  to be syntacticly incorrect, as there is a table name ("mail") used as
  an SQL expression. The reason this is acceptable is that each FTS3 table
  actually has a [sqlite3_declare_vtab|HIDDEN] column with the same name
  as the table itself (in this case, "mail"). The value stored in this
  column is not meaningful to the application, but can be used as the 
  left-hand operand to a MATCH operator. This special column may also be
  passed as an argument to the [snippet()|FTS3 auxillary functions].

<p>
  The following example illustrates the above. The expressions "docs", 
  "docs.docs" and "main.docs.docs" all refer to column "docs". However, the 
  expression "main.docs" does not refer to any column. It could be used to 
  refer to a table, but a table name is not allowed in the context in which
  it is used below.







|







317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
  At first glance, the final two full-text queries in the example above seem
  to be syntacticly incorrect, as there is a table name ("mail") used as
  an SQL expression. The reason this is acceptable is that each FTS3 table
  actually has a [sqlite3_declare_vtab|HIDDEN] column with the same name
  as the table itself (in this case, "mail"). The value stored in this
  column is not meaningful to the application, but can be used as the 
  left-hand operand to a MATCH operator. This special column may also be
  passed as an argument to the [snippet()|FTS3 auxiliary functions].

<p>
  The following example illustrates the above. The expressions "docs", 
  "docs.docs" and "main.docs.docs" all refer to column "docs". However, the 
  expression "main.docs" does not refer to any column. It could be used to 
  refer to a table, but a table name is not allowed in the context in which
  it is used below.
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
    rowid column supported by all [virtual tables].

  <li><p> 
    The [FTS3 MATCH] operator is supported for queries based on the built-in
    full-text index. 

  <li><p> 
    The FTS3 auxillary functions, [snippet|snippet() and offsets()], are 
    available to support full-text queries.

  <li><p> 
    Each FTS3 table has a [sqlite3_declare_vtab()|HIDDEN column] with the 
    same name as the table itself. The value contained in each row for the
    special column is only useful when used on the left-hand side of a 
    [FTS3 MATCH|MATCH] operator, or when specified as an argument to one 
    of the [snippet|FTS3 auxillary functions].
    

</ol>


<h1 tags="compile fts3">Compiling and Enabling FTS3</h1>








|







|







370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
    rowid column supported by all [virtual tables].

  <li><p> 
    The [FTS3 MATCH] operator is supported for queries based on the built-in
    full-text index. 

  <li><p> 
    The FTS3 auxiliary functions, [snippet|snippet() and offsets()], are 
    available to support full-text queries.

  <li><p> 
    Each FTS3 table has a [sqlite3_declare_vtab()|HIDDEN column] with the 
    same name as the table itself. The value contained in each row for the
    special column is only useful when used on the left-hand side of a 
    [FTS3 MATCH|MATCH] operator, or when specified as an argument to one 
    of the [snippet|FTS3 auxiliary functions].
    

</ol>


<h1 tags="compile fts3">Compiling and Enabling FTS3</h1>

769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
  <i>-- Search for documents that contains at least one of the terms "database"</i>
  <i>-- and "sqlite", and also contains the term "library". Because of the differences</i>
  <i>-- in operator precedences, this query would have a different interpretation using</i>
  <i>-- the enhanced query syntax.</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';
</codeblock>

<h1 tags="snippet, offsets">Auxillary functions - Snippet, Offsets and Matchinfo</h1>

<p>
  The FTS3 module provides three special SQL scalar functions that may be useful
  to the developers of full-text query systems: "snippet", "offsets" and
  "matchinfo". The purpose of the "snippet" and "offsets" functions is to allow
  the user to identify the location of queried terms in the returned documents.
  The "matchinfo" function provides the user with metrics that may be useful







|







769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
  <i>-- Search for documents that contains at least one of the terms "database"</i>
  <i>-- and "sqlite", and also contains the term "library". Because of the differences</i>
  <i>-- in operator precedences, this query would have a different interpretation using</i>
  <i>-- the enhanced query syntax.</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';
</codeblock>

<h1 tags="snippet, offsets">auxiliary functions - Snippet, Offsets and Matchinfo</h1>

<p>
  The FTS3 module provides three special SQL scalar functions that may be useful
  to the developers of full-text query systems: "snippet", "offsets" and
  "matchinfo". The purpose of the "snippet" and "offsets" functions is to allow
  the user to identify the location of queried terms in the returned documents.
  The "matchinfo" function provides the user with metrics that may be useful
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
<codeblock>
  SELECT offsets(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
  SELECT snippet(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
  SELECT matchinfo(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
</codeblock>

<p>
  The three auxillary functions are only useful within a SELECT statement that
  uses the FTS3 table's full-text index. ^If used within a SELECT that uses
  the "query by rowid" or "linear scan" strategies, then the snippet and
  offsets both return an an empty string, and the matchinfo function returns
  a blob value zero bytes in size.

<p id=matchable>
  All three auxillary functions extract a set of "matchable phrases" from
  the FTS3 query expression to work with. The set of matchable phrases for
  a given query consists of all phrases (including unquoted tokens and
  token prefixes) in the expression except those that are prefixed with
  a unary "-" operator (standard syntax) or are part of a sub-expression 
  that is used as the right-hand operand of a NOT operator.

<p>







|






|







792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
<codeblock>
  SELECT offsets(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
  SELECT snippet(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
  SELECT matchinfo(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
</codeblock>

<p>
  The three auxiliary functions are only useful within a SELECT statement that
  uses the FTS3 table's full-text index. ^If used within a SELECT that uses
  the "query by rowid" or "linear scan" strategies, then the snippet and
  offsets both return an an empty string, and the matchinfo function returns
  a blob value zero bytes in size.

<p id=matchable>
  All three auxiliary functions extract a set of "matchable phrases" from
  the FTS3 query expression to work with. The set of matchable phrases for
  a given query consists of all phrases (including unquoted tokens and
  token prefixes) in the expression except those that are prefixed with
  a unary "-" operator (standard syntax) or are part of a sub-expression 
  that is used as the right-hand operand of a NOT operator.

<p>
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
  SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';

  <i>-- The following query returns also matches only the first row in table "mail".</i>
  <i>-- In this case the returned text is "1 0 5 7 1 0 30 7".</i>
  SELECT offsets(mail) FROM mail WHERE mail MATCH 'message';

  <i>-- The following query matches the second row in table "mail". It returns the</i>
  <i>-- text "1 0 28 7 1 1 36 4". Only those occurences of terms "serious" and "mail"</i>
  <i>-- that are part of an instance of the phrase "serious mail" are identified; the</i>
  <i>-- other occurences of "serious" and "mail" are ignored.</i>
  SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"';
</codeblock>

<h2>The Snippet Function</h2>

<p>
  The snippet function is used to create formatted fragments of document text







|

|







871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
  SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';

  <i>-- The following query returns also matches only the first row in table "mail".</i>
  <i>-- In this case the returned text is "1 0 5 7 1 0 30 7".</i>
  SELECT offsets(mail) FROM mail WHERE mail MATCH 'message';

  <i>-- The following query matches the second row in table "mail". It returns the</i>
  <i>-- text "1 0 28 7 1 1 36 4". Only those occurrences of terms "serious" and "mail"</i>
  <i>-- that are part of an instance of the phrase "serious mail" are identified; the</i>
  <i>-- other occurrences of "serious" and "mail" are ignored.</i>
  SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"';
</codeblock>

<h2>The Snippet Function</h2>

<p>
  The snippet function is used to create formatted fragments of document text
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
           text from. Columns are numbered from left to right starting with
           zero. A negative value indicates that the text may be extracted
           from any column.
  <tr><td>5 <td>-15
      <td> The absolute value of this integer argument is used as the 
           (approximate) number of tokens to include in the returned text 
           value. The maximum allowable absolute value is 64. The value of
           this argument is refered to as <i>N</i> in the discussion below.
</table>

<p>
  The snippet function first attempts to find a fragment of text consisting
  of <i>|N|</i> tokens within the current row that contains at least one phrase 
  match for each matchable phrase matched somewhere in the current row, 
  where <i>|N|</i> is the absolute value of the sixth argument passed to the







|







905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
           text from. Columns are numbered from left to right starting with
           zero. A negative value indicates that the text may be extracted
           from any column.
  <tr><td>5 <td>-15
      <td> The absolute value of this integer argument is used as the 
           (approximate) number of tokens to include in the returned text 
           value. The maximum allowable absolute value is 64. The value of
           this argument is referred to as <i>N</i> in the discussion below.
</table>

<p>
  The snippet function first attempts to find a fragment of text consisting
  of <i>|N|</i> tokens within the current row that contains at least one phrase 
  match for each matchable phrase matched somewhere in the current row, 
  where <i>|N|</i> is the absolute value of the sixth argument passed to the
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
<codeblock>
  SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
  SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
</codeblock>

<p>
  The matchinfo function provides much of the information required to calculate
  probabalistic "bag-of-words" relevancy scores such as 
  <a href=http://en.wikipedia.org/wiki/Okapi_BM25>Okapi BM25/BM25F</a> that may
  be used to order results in a full-text search application. Also often
  used in such functions is the length or relative length of each document
  or document field. Unfortunately, this information is not made available
  by the matchinfo function as it would require loading extra data from the
  database, potentially slowing matchinfo() down by an order of magnitude.
  One solution is for the application to store the lengths of each document







|







1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
<codeblock>
  SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
  SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
</codeblock>

<p>
  The matchinfo function provides much of the information required to calculate
  probabilistic "bag-of-words" relevancy scores such as 
  <a href=http://en.wikipedia.org/wiki/Okapi_BM25>Okapi BM25/BM25F</a> that may
  be used to order results in a full-text search application. Also often
  used in such functions is the length or relative length of each document
  or document field. Unfortunately, this information is not made available
  by the matchinfo function as it would require loading extra data from the
  database, potentially slowing matchinfo() down by an order of magnitude.
  One solution is for the application to store the lengths of each document
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
  the <a href="fileformat.html#varint_format">SQLite varint format</a>.

<p>
  An encoded FTS3 varint consumes between one and ten bytes of space. The
  number of bytes required is determined by the sign and magnitude of the
  integer value encoded. More accurately, the number of bytes used to store
  the encoded integer depends on the position of the most significant set bit
  in the 64-bit twos-compliment representation of the integer value. Negative
  values always have the most significant bit set (the sign bit), and so are
  always stored using the full ten bytes. Positive integer values may be
  stored using less space.

<p>
  The final byte of an encoded FTS3 varint has its most significant bit 
  cleared. All preceding bytes have the most significant bit set. Data
  is stored in the remaining seven least signficant bits of each byte.
  The first byte of the encoded representation contains the least significant
  seven bits of the encoded integer value. The second byte of the encoded
  representation, if it is present, contains the seven next least significant
  bits of the integer value, and so on. The following table contains examples
  of encoded integer values:

<table striped=1>







|







|







1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
  the <a href="fileformat.html#varint_format">SQLite varint format</a>.

<p>
  An encoded FTS3 varint consumes between one and ten bytes of space. The
  number of bytes required is determined by the sign and magnitude of the
  integer value encoded. More accurately, the number of bytes used to store
  the encoded integer depends on the position of the most significant set bit
  in the 64-bit twos-complement representation of the integer value. Negative
  values always have the most significant bit set (the sign bit), and so are
  always stored using the full ten bytes. Positive integer values may be
  stored using less space.

<p>
  The final byte of an encoded FTS3 varint has its most significant bit 
  cleared. All preceding bytes have the most significant bit set. Data
  is stored in the remaining seven least significant bits of each byte.
  The first byte of the encoded representation contains the least significant
  seven bits of the encoded integer value. The second byte of the encoded
  representation, if it is present, contains the seven next least significant
  bits of the integer value, and so on. The following table contains examples
  of encoded integer values:

<table striped=1>
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
  b-tree is stored as a blob in the "root" field of the corresponding row
  of the %_segdir table. All other nodes (if any exist) are stored in the 
  "blob" column of the %_segments table. Nodes within the %_segments table are
  identified by the integer value in the blockid field of the corresponding
  row. The following table describes the fields of the %_segdir table:

<table striped=1>
  <tr><th>Column           <th width=100%>Interpretion
  <tr><td>level            <td> 
    Between them, the contents of the "level" and "idx" fields define the
    relative age of the segment b-tree. The smaller the value stored in the
    "level" field, the more recently the segment b-tree was created. If two
    segment b-trees are of the same "level", the segment with the larger
    value stored in the "idx" column is more recent. The PRIMARY KEY constraint
    on the %_segdir table prevents any two segments from having the same value







|







1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
  b-tree is stored as a blob in the "root" field of the corresponding row
  of the %_segdir table. All other nodes (if any exist) are stored in the 
  "blob" column of the %_segments table. Nodes within the %_segments table are
  identified by the integer value in the blockid field of the corresponding
  row. The following table describes the fields of the %_segdir table:

<table striped=1>
  <tr><th>Column           <th width=100%>Interpretation
  <tr><td>level            <td> 
    Between them, the contents of the "level" and "idx" fields define the
    relative age of the segment b-tree. The smaller the value stored in the
    "level" field, the more recently the segment b-tree was created. If two
    segment b-trees are of the same "level", the segment with the larger
    value stored in the "idx" column is more recent. The PRIMARY KEY constraint
    on the %_segdir table prevents any two segments from having the same value
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
</codeblock>

<p>
  The query above could be made to run faster by using the FTS3 [matchinfo]
  function to determine the number of query term instances that appear in each
  result. The matchinfo function is much more efficient than the offsets 
  function. Furthermore, the matchinfo function provides extra information
  regarding the overall number of occurences of each query term in the entire
  document set (not just the current row) and the number of documents in which 
  each query term appears. This may be used (for example) to attach a higher
  weight to less common terms which may increase the overall computed relevancy 
  of those results the user considers more interesting.

<codeblock>
  <i>-- If the application supplies an SQLite user function called "rank" that</i>
  <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i>
  <i>-- relevancy based on it, then the following SQL may be used to return the</i>
  <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i>
  SELECT title FROM documents 
    WHERE documents MATCH &lt;query&gt;
    ORDER BY rank(matchinfo(document)) DESC
    OFFSET 0 LIMIT 10
</codeblock>

<p>
  The SQL query in the example above uses less CPU than the first example
  in this section, but still has a non-obvious performance problem. SQLite
  satisfies this query by retreiving the value of the "title" column and
  matchinfo data from the FTS3 module for every row matched by the users
  query before it sorts and limits the results. Because of the way SQLite's
  virtual table interface works, retrieving the value of the "title" column
  requires loading the entire row from disk (including the "content" field,
  which may be quite large). This means that if the users query matches
  several thousand documents, many megabytes of "title" and "content" data
  may be loaded from disk into memory even though they will never be used
  for any purpose. 

<p>
  The SQL query in the following example block is one solution to this 
  problem. In SQLite, when a <a href="optoverview.html#flattening">sub-query 
  used in a join contains a LIMIT clause</a>, the results of the sub-query are
  calcuated and stored in temporary table before the main query is executed.
  This means that SQLite will load only the docid and matchinfo data for each
  row matching the users query into memory, determine the docid values
  corresponding to the ten most relevant documents, then load only the title
  and content information for those 10 documents only. Because both the matchinfo
  and docid values are gleaned entirely from the full-text index, this results
  in dramatically less data being loaded from the database into memory.








|



















|













|







1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
</codeblock>

<p>
  The query above could be made to run faster by using the FTS3 [matchinfo]
  function to determine the number of query term instances that appear in each
  result. The matchinfo function is much more efficient than the offsets 
  function. Furthermore, the matchinfo function provides extra information
  regarding the overall number of occurrences of each query term in the entire
  document set (not just the current row) and the number of documents in which 
  each query term appears. This may be used (for example) to attach a higher
  weight to less common terms which may increase the overall computed relevancy 
  of those results the user considers more interesting.

<codeblock>
  <i>-- If the application supplies an SQLite user function called "rank" that</i>
  <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i>
  <i>-- relevancy based on it, then the following SQL may be used to return the</i>
  <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i>
  SELECT title FROM documents 
    WHERE documents MATCH &lt;query&gt;
    ORDER BY rank(matchinfo(document)) DESC
    OFFSET 0 LIMIT 10
</codeblock>

<p>
  The SQL query in the example above uses less CPU than the first example
  in this section, but still has a non-obvious performance problem. SQLite
  satisfies this query by retrieving the value of the "title" column and
  matchinfo data from the FTS3 module for every row matched by the users
  query before it sorts and limits the results. Because of the way SQLite's
  virtual table interface works, retrieving the value of the "title" column
  requires loading the entire row from disk (including the "content" field,
  which may be quite large). This means that if the users query matches
  several thousand documents, many megabytes of "title" and "content" data
  may be loaded from disk into memory even though they will never be used
  for any purpose. 

<p>
  The SQL query in the following example block is one solution to this 
  problem. In SQLite, when a <a href="optoverview.html#flattening">sub-query 
  used in a join contains a LIMIT clause</a>, the results of the sub-query are
  calculated and stored in temporary table before the main query is executed.
  This means that SQLite will load only the docid and matchinfo data for each
  row matching the users query into memory, determine the docid values
  corresponding to the ten most relevant documents, then load only the title
  and content information for those 10 documents only. Because both the matchinfo
  and docid values are gleaned entirely from the full-text index, this results
  in dramatically less data being loaded from the database into memory.

Changes to pages/howtocompile.in.
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
[download page] are normally adequate for most users.  However, some
projects may want or need to build their own amalgamations.  A common
reason for building a custom amalgamation is in order to use certain
[compile-time options] to customize the SQLite library.  Recall that
the SQLite amalgamation contains a lot of C-code that is generated by
auxiliary programs and scripts.  Many of the compile-time
options effect this generated code and must be supplied to the code
generators before the amalgamation is assemblied.  The set of 
compile-time options that must be passed into the code generators can
vary from one release of SQLite to the next, but at the time of this
writing (circa SQLite 3.6.20, 2009-11-04) the set of options that must
be known by the code generators includes:</p>

<ul>
<li>[SQLITE_ENABLE_UPDATE_DELETE_LIMIT]







|







162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
[download page] are normally adequate for most users.  However, some
projects may want or need to build their own amalgamations.  A common
reason for building a custom amalgamation is in order to use certain
[compile-time options] to customize the SQLite library.  Recall that
the SQLite amalgamation contains a lot of C-code that is generated by
auxiliary programs and scripts.  Many of the compile-time
options effect this generated code and must be supplied to the code
generators before the amalgamation is assembled.  The set of 
compile-time options that must be passed into the code generators can
vary from one release of SQLite to the next, but at the time of this
writing (circa SQLite 3.6.20, 2009-11-04) the set of options that must
be known by the code generators includes:</p>

<ul>
<li>[SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
Changes to pages/lang.in.
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
that refer to the table being renamed are modified to refer 
to the renamed table by its new name.

<p> ^The ADD COLUMN syntax
is used to add a new column to an existing table.
^The new column is always appended to the end of the list of existing columns.
The [column-def] rule defines the characteristics of the new column.
^(The new column may take any of the forms permissable in a [CREATE TABLE]
statement, with the following restrictions:)^
<ul>
<li>^The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>^The column may not have a default value of CURRENT_TIME, CURRENT_DATE, 
    CURRENT_TIMESTAMP, or an expression in parentheses.</li>
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.







|







150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
that refer to the table being renamed are modified to refer 
to the renamed table by its new name.

<p> ^The ADD COLUMN syntax
is used to add a new column to an existing table.
^The new column is always appended to the end of the list of existing columns.
The [column-def] rule defines the characteristics of the new column.
^(The new column may take any of the forms permissible in a [CREATE TABLE]
statement, with the following restrictions:)^
<ul>
<li>^The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>^The column may not have a default value of CURRENT_TIME, CURRENT_DATE, 
    CURRENT_TIMESTAMP, or an expression in parentheses.</li>
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
same as BEGIN DEFERRED TRANSACTION.)^</p>

<p>^The ROLLBACK TO command reverts the state of the database back to what
it was just after the corresponding SAVEPOINT.  ^Note that unlike that
plain [ROLLBACK] command (without the TO keyword) the ROLLBACK TO command
does not cancel the transaction.  ^Instead of cancelling the transaction,
the ROLLBACK TO command restarts the transaction again at the beginning.
^All intervening SAVEPOINTs are cancelled, however.</p>

<p>^The RELEASE command is like a [COMMIT] for a SAVEPOINT.
^The RELEASE command causes all savepoints back to and including the 
most recent savepoint with a matching name to be removed from the 
transaction stack.  ^The RELEASE of an inner transaction
does not cause any changes to be written to the database file; it merely
removes savepoints from the transaction stack such that it is
no longer possible to ROLLBACK TO those savepoints.
^If a RELEASE command releases the outermost savepoint, so
that the transaction stack becomes empty, then RELEASE is the same
as [COMMIT].
^The [COMMIT] command may be used to release all savepoints and
commit the transaction even if the transaction was originally started
by a SAVEPOINT command instead of a [BEGIN] command.</p>

<p>^If the savepoint-name in a RELEASE command does not match any
savepoint currently in the tranaction stack, then no savepoints are
released, the database is unchanged, and the RELEASE command returns
an error.</p>

<p>^Note that an inner transaction might commit (using the RELEASE command)
but then later have its work undone by a ROLLBACK in an outer transaction.
^A power failure or program crash or OS crash will cause the outer-most
transaction to rollback, undoing all changes that have occurred within







|
















|







425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
same as BEGIN DEFERRED TRANSACTION.)^</p>

<p>^The ROLLBACK TO command reverts the state of the database back to what
it was just after the corresponding SAVEPOINT.  ^Note that unlike that
plain [ROLLBACK] command (without the TO keyword) the ROLLBACK TO command
does not cancel the transaction.  ^Instead of cancelling the transaction,
the ROLLBACK TO command restarts the transaction again at the beginning.
^All intervening SAVEPOINTs are canceled, however.</p>

<p>^The RELEASE command is like a [COMMIT] for a SAVEPOINT.
^The RELEASE command causes all savepoints back to and including the 
most recent savepoint with a matching name to be removed from the 
transaction stack.  ^The RELEASE of an inner transaction
does not cause any changes to be written to the database file; it merely
removes savepoints from the transaction stack such that it is
no longer possible to ROLLBACK TO those savepoints.
^If a RELEASE command releases the outermost savepoint, so
that the transaction stack becomes empty, then RELEASE is the same
as [COMMIT].
^The [COMMIT] command may be used to release all savepoints and
commit the transaction even if the transaction was originally started
by a SAVEPOINT command instead of a [BEGIN] command.</p>

<p>^If the savepoint-name in a RELEASE command does not match any
savepoint currently in the transaction stack, then no savepoints are
released, the database is unchanged, and the RELEASE command returns
an error.</p>

<p>^Note that an inner transaction might commit (using the RELEASE command)
but then later have its work undone by a ROLLBACK in an outer transaction.
^A power failure or program crash or OS crash will cause the outer-most
transaction to rollback, undoing all changes that have occurred within
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>^If the optional IF NOT EXISTS clause is present and another index
with the same name aleady exists, then this command becomes a no-op.</p>

<p>^Indexes are removed with the [DROP INDEX] command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable} {{CREATE TABLE}}







|




|







586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>^If the optional IF NOT EXISTS clause is present and another index
with the same name already exists, then this command becomes a no-op.</p>

<p>^Indexes are removed with the [DROP INDEX] command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable} {{CREATE TABLE}}
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
^The COLLATE clause specifies what text 
[collating function] to use when comparing text entries for the column.  
^The built-in [BINARY] collating function is used by default.
<p>
^The DEFAULT constraint specifies a default value to use when doing an [INSERT].
^The value may be NULL, a string constant, a number, or a constant expression
enclosed in parentheses.
^The default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.  ^If the value is
NULL, a string constant or number, it is inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. ^If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. ^For
CURRENT_TIME, the format is HH:MM:SS. ^For CURRENT_DATE, YYYY-MM-DD.
^The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".







|







631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
^The COLLATE clause specifies what text 
[collating function] to use when comparing text entries for the column.  
^The built-in [BINARY] collating function is used by default.
<p>
^The DEFAULT constraint specifies a default value to use when doing an [INSERT].
^The value may be NULL, a string constant, a number, or a constant expression
enclosed in parentheses.
^The default value may also be one of the special case-independent
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.  ^If the value is
NULL, a string constant or number, it is inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. ^If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. ^For
CURRENT_TIME, the format is HH:MM:SS. ^For CURRENT_DATE, YYYY-MM-DD.
^The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721


<p>^The CREATE TABLE AS form defines the table to be
the result set of a query.  ^The names of the table columns are
the names of the columns in the result.</p>

<p>^If the optional IF NOT EXISTS clause is present and another table
with the same name aleady exists, then this command becomes a no-op.</p>

<p>^Tables are removed using the [DROP TABLE] statement.  </p>

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Every row of every SQLite table has a 64-bit signed integer key 







|







707
708
709
710
711
712
713
714
715
716
717
718
719
720
721


<p>^The CREATE TABLE AS form defines the table to be
the result set of a query.  ^The names of the table columns are
the names of the columns in the result.</p>

<p>^If the optional IF NOT EXISTS clause is present and another table
with the same name already exists, then this command becomes a no-op.</p>

<p>^Tables are removed using the [DROP TABLE] statement.  </p>

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Every row of every SQLite table has a 64-bit signed integer key 
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
<td align="right" valign="top"><b>@</b><i>AAAA</i></td><td width="20"></td>
<td>An "at" sign works exactly like a colon.</td>
</tr>
<tr>
<td align="right" valign="top"><b>$</b><i>AAAA</i></td><td width="20"></td>
<td>A dollar-sign followed by an identifier name also holds a spot for a named
parameter with the name AAAA.  The identifier name in this case can include
one or more occurances of "::" and a suffix enclosed in "(...)" containing
any text at all.  This syntax is the form of a variable name in the
[http://www.tcl.tk/ | Tcl programming language].  The presence
of this syntax results from the fact that SQLite is really a 
[Tcl extension] that has escaped into the wild.</td>
</tr>
</table>
</blockquote>)^







|







1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
<td align="right" valign="top"><b>@</b><i>AAAA</i></td><td width="20"></td>
<td>An "at" sign works exactly like a colon.</td>
</tr>
<tr>
<td align="right" valign="top"><b>$</b><i>AAAA</i></td><td width="20"></td>
<td>A dollar-sign followed by an identifier name also holds a spot for a named
parameter with the name AAAA.  The identifier name in this case can include
one or more occurrences of "::" and a suffix enclosed in "(...)" containing
any text at all.  This syntax is the form of a variable name in the
[http://www.tcl.tk/ | Tcl programming language].  The presence
of this syntax results from the fact that SQLite is really a 
[Tcl extension] that has escaped into the wild.</td>
</tr>
</table>
</blockquote>)^
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  ^WHEN expressions are evaluated from left to
right until one is found that is true, at which point the corresponding
THEN term becomes the result.  ^If no WHEN expression is true then the ELSE 
clause determines the result or the result is NULL if there is no ELSE clase.
</p>

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is the "base" expression.  ^There are two basic forms
of a CASE expression: those with and without a base expression.
^In a CASE without a base expression, each WHEN expression is evaluted
as a boolean and the overall result is determined by first WHEN expression
that is true.
^In a CASE with a base expression, the base expression is evaluted just
once and the result is compared against each WHEN expression until a 
match is found.
^When comparing a base expression against a WHEN expression, the same
collating sequence, affinity, and NULL-handling rules apply as if the
base expression and WHEN expression are respectively the left- and
right-hand operands of an <big><b>=</b></big> operator.</p>








|





|


|







1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  ^WHEN expressions are evaluated from left to
right until one is found that is true, at which point the corresponding
THEN term becomes the result.  ^If no WHEN expression is true then the ELSE 
clause determines the result or the result is NULL if there is no ELSE clause.
</p>

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is the "base" expression.  ^There are two basic forms
of a CASE expression: those with and without a base expression.
^In a CASE without a base expression, each WHEN expression is evaluated
as a boolean and the overall result is determined by first WHEN expression
that is true.
^In a CASE with a base expression, the base expression is evaluated just
once and the result is compared against each WHEN expression until a 
match is found.
^When comparing a base expression against a WHEN expression, the same
collating sequence, affinity, and NULL-handling rules apply as if the
base expression and WHEN expression are respectively the left- and
right-hand operands of an <big><b>=</b></big> operator.</p>

1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
  hex(randomblob(16))<br></br>
  lower(hex(randomblob(16)))
  </blockquote>
}

funcdef {replace(X,Y,Z)} {} {
  ^The replace(X,Y,Z) function returns a string formed by substituting
  string Z for every occurrance of string Y in string X.  ^The [BINARY]
  collating sequence is used for comparisons.  ^If Y is an empty
  string then return X unchanged.  ^If Z is not initially
  a string, it is cast to a UTF-8 string prior to processing.
}

funcdef {round(X) round(X,Y)} {} {
  ^The round(X,Y) function returns a string representation of the floating-point







|







1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
  hex(randomblob(16))<br></br>
  lower(hex(randomblob(16)))
  </blockquote>
}

funcdef {replace(X,Y,Z)} {} {
  ^The replace(X,Y,Z) function returns a string formed by substituting
  string Z for every occurrence of string Y in string X.  ^The [BINARY]
  collating sequence is used for comparisons.  ^If Y is an empty
  string then return X unchanged.  ^If Z is not initially
  a string, it is cast to a UTF-8 string prior to processing.
}

funcdef {round(X) round(X,Y)} {} {
  ^The round(X,Y) function returns a string representation of the floating-point
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
  ^The substr(X,Y,Z) function returns a substring of input string X that begins
  with the Y-th character and which is Z characters long.
  ^If Z is omitted then substr(X,Y) returns all characters through the end
  of the string X beginning with the Y-th.
  ^The left-most character of X is number 1.  ^If Y is negative
  then the first character of the substring is found by counting from the
  right rather than the left.  ^If Z is negative then
  the abs(Z) characters preceeding the Y-th character are returned.
  ^If X is a string then characters indices refer to actual UTF-8 
  characters.  ^If X is a BLOB then the indices refer to bytes.
}

funcdef {total_changes()} {} {
  ^The total_changes() function returns the number of row changes
  caused by INSERT, UPDATE or DELETE







|







1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
  ^The substr(X,Y,Z) function returns a substring of input string X that begins
  with the Y-th character and which is Z characters long.
  ^If Z is omitted then substr(X,Y) returns all characters through the end
  of the string X beginning with the Y-th.
  ^The left-most character of X is number 1.  ^If Y is negative
  then the first character of the substring is found by counting from the
  right rather than the left.  ^If Z is negative then
  the abs(Z) characters preceding the Y-th character are returned.
  ^If X is a string then characters indices refer to actual UTF-8 
  characters.  ^If X is a BLOB then the indices refer to bytes.
}

funcdef {total_changes()} {} {
  ^The total_changes() function returns the number of row changes
  caused by INSERT, UPDATE or DELETE
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
final SELECT statement.
^The limit is applied to the entire query not
to the individual SELECT statement to which it is attached.
^Note that if the OFFSET keyword is used in the LIMIT clause, then the
limit is the first number and the offset is the second number.  ^If a
comma is used instead of the OFFSET keyword, then the offset is the
first number and the limit is the second number.  This seeming
contradition is intentional - it maximizes compatibility with legacy
SQL database systems.
</p>

<p>^A compound SELECT is formed from two or more simple SELECTs connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  ^In
a compound SELECT, all the constituent SELECTs must specify the
same number of result columns.  ^There may be only a single ORDER BY







|







2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
final SELECT statement.
^The limit is applied to the entire query not
to the individual SELECT statement to which it is attached.
^Note that if the OFFSET keyword is used in the LIMIT clause, then the
limit is the first number and the offset is the second number.  ^If a
comma is used instead of the OFFSET keyword, then the offset is the
first number and the limit is the second number.  This seeming
contradiction is intentional - it maximizes compatibility with legacy
SQL database systems.
</p>

<p>^A compound SELECT is formed from two or more simple SELECTs connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  ^In
a compound SELECT, all the constituent SELECTs must specify the
same number of result columns.  ^There may be only a single ORDER BY
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
<p>^(If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option then the syntax of the UPDATE statement is extended
with optional ORDER BY and LIMIT clauses)^ as follows:</p>

<tcl>BubbleDiagram update-stmt-limited</tcl>

<p>^The optional LIMIT clause can be used to limit the number of
rows modifed, and thereby limit the size of the transaction.
^The ORDER BY clause on an UPDATE statement
is used only to determine which rows fall
within the LIMIT.  ^The order in which rows are modified is arbitrary
and is not determined by the ORDER BY clause.</p>

<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, trigger, or view) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>

<p>^The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>







|


















|







2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
<p>^(If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option then the syntax of the UPDATE statement is extended
with optional ORDER BY and LIMIT clauses)^ as follows:</p>

<tcl>BubbleDiagram update-stmt-limited</tcl>

<p>^The optional LIMIT clause can be used to limit the number of
rows modified, and thereby limit the size of the transaction.
^The ORDER BY clause on an UPDATE statement
is used only to determine which rows fall
within the LIMIT.  ^The order in which rows are modified is arbitrary
and is not determined by the ORDER BY clause.</p>

<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, trigger, or view) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scattered out all across the database file rather
than clustered together in one place.</p>

<p>^The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
the token is understood to be an identifier instead of a string literal.
</p></li>

<li><p>^If a keyword in double
quotes (ex: <b>"key"</b> or <b>"glob"</b>) is used in a context where
it cannot be resolved to an identifier but where a string literal
is allowed, then the token is understood to be a string literal instead
of an identifer.</p></li>
</ul>

<p>Programmers are cautioned not to use the two exceptions described in
the previous bullets.  We emphasize that they exist only so that old
and ill-formed SQL statements will run correctly.  Future versions of
SQLite might change to raise errors instead of accepting the malformed
statements covered by the exceptions above.</p>







|







2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
the token is understood to be an identifier instead of a string literal.
</p></li>

<li><p>^If a keyword in double
quotes (ex: <b>"key"</b> or <b>"glob"</b>) is used in a context where
it cannot be resolved to an identifier but where a string literal
is allowed, then the token is understood to be a string literal instead
of an identifier.</p></li>
</ul>

<p>Programmers are cautioned not to use the two exceptions described in
the previous bullets.  We emphasize that they exist only so that old
and ill-formed SQL statements will run correctly.  Future versions of
SQLite might change to raise errors instead of accepting the malformed
statements covered by the exceptions above.</p>
Changes to pages/lockingv3.in.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>

<p>This document was originally created in early 2004 when SQLite version 2
was still in widesprad use and was written to introduce
the new concepts of SQLite version 3 to readers who were already familiar
with SQLite version 2.  But these days, most readers of this document have
probably never seen SQLite version 2 and are only familiar with SQLite
version 3.  Nevertheless, this document continues to serve as an
authoritative reference to how database file locking works in SQLite 
version 3.</p>








|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>

<p>This document was originally created in early 2004 when SQLite version 2
was still in widespread use and was written to introduce
the new concepts of SQLite version 3 to readers who were already familiar
with SQLite version 2.  But these days, most readers of this document have
probably never seen SQLite version 2 and are only familiar with SQLite
version 3.  Nevertheless, this document continues to serve as an
authoritative reference to how database file locking works in SQLite 
version 3.</p>

Changes to pages/news.in.
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
  when compiled for SuSE 10.1 Linux on x86.  The SQLite developers pledge
  to maintain branch test coverage at 100% in all future releases.
  Ongoing work will strive for 100% branch test coverage on the 
  operating-system backends and extensions as well.
}

newsitem {2009-Jun-27} {Version 3.6.16} {
  SQLite [version 3.6.16] is another general maintenance relase containing
  performance and robustness enhancements.  A single notable bug was fixed
  (ticket #3929).  This bug cause cause INSERT or UPDATE statements to fail
  on indexed tables that have AFTER triggers that modify the same table and
  index.
}

newsitem {2009-Jun-15} {Version 3.6.15} {







|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
  when compiled for SuSE 10.1 Linux on x86.  The SQLite developers pledge
  to maintain branch test coverage at 100% in all future releases.
  Ongoing work will strive for 100% branch test coverage on the 
  operating-system backends and extensions as well.
}

newsitem {2009-Jun-27} {Version 3.6.16} {
  SQLite [version 3.6.16] is another general maintenance release containing
  performance and robustness enhancements.  A single notable bug was fixed
  (ticket #3929).  This bug cause cause INSERT or UPDATE statements to fail
  on indexed tables that have AFTER triggers that modify the same table and
  index.
}

newsitem {2009-Jun-15} {Version 3.6.15} {
Changes to pages/nulls.in.
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
all questions in the chart below would be "Yes".  But the
experiments run on other SQL engines showed that none of them
worked this way.  So SQLite was modified to work the same as
Oracle, PostgreSQL, and DB2.  This involved making NULLs
indistinct for the purposes of the SELECT DISTINCT statement and
for the UNION operator in a SELECT.  NULLs are still distinct
in a UNIQUE column.  This seems somewhat arbitrary, but the desire
to be compatible with other engines outweighted that objection.
</p>

<p>
It is possible to make SQLite treat NULLs as distinct for the
purposes of the SELECT DISTINCT and UNION.  To do so, one should
change the value of the NULL_ALWAYS_DISTINCT #define in the
<tt>sqliteInt.h</tt> source file and recompile.







|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
all questions in the chart below would be "Yes".  But the
experiments run on other SQL engines showed that none of them
worked this way.  So SQLite was modified to work the same as
Oracle, PostgreSQL, and DB2.  This involved making NULLs
indistinct for the purposes of the SELECT DISTINCT statement and
for the UNION operator in a SELECT.  NULLs are still distinct
in a UNIQUE column.  This seems somewhat arbitrary, but the desire
to be compatible with other engines outweighed that objection.
</p>

<p>
It is possible to make SQLite treat NULLs as distinct for the
purposes of the SELECT DISTINCT and UNION.  To do so, one should
change the value of the NULL_ALWAYS_DISTINCT #define in the
<tt>sqliteInt.h</tt> source file and recompile.
Changes to pages/oldnews.in.
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
  the new interfaces in the next release without having to declare an
  incompatibility.
}

newsitem {2008-May-12} {Version 3.5.9} {
  Version 3.5.9 adds a new experimental [PRAGMA]: [journal_mode].
  Setting the journal mode to PERSIST can provide performance improvement
  on systems where deleting a file is expective.  The PERSIST journal
  mode is still considered experimental and should be used with caution
  pending further testing.

  Version 3.5.9 is intended to be the last stable release prior to
  version 3.6.0.  Version 3.6.0 will make incompatible changes to the
  [sqlite3_vfs] VFS layer in order to address deficiencies in the original
  design.  These incompatibilities will only effect programmers who







|







108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
  the new interfaces in the next release without having to declare an
  incompatibility.
}

newsitem {2008-May-12} {Version 3.5.9} {
  Version 3.5.9 adds a new experimental [PRAGMA]: [journal_mode].
  Setting the journal mode to PERSIST can provide performance improvement
  on systems where deleting a file is expensive.  The PERSIST journal
  mode is still considered experimental and should be used with caution
  pending further testing.

  Version 3.5.9 is intended to be the last stable release prior to
  version 3.6.0.  Version 3.6.0 will make incompatible changes to the
  [sqlite3_vfs] VFS layer in order to address deficiencies in the original
  design.  These incompatibilities will only effect programmers who
Changes to pages/onefile.in.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<p>
The SQLite database file format is also stable.
All releases of of SQLite version 3 can read and write database
files created by the very first SQLite 3 release (version 3.0.0)
going back to 2004-06-18.  This is "backwards compatibility".
The developers promise to maintain backwards compatibility of
the database file format for all future releases of SQLite 3.
"Forwards compatiblity" means that older releases
of SQLite can also read and write databases created by newer
releases.  SQLite is usually, but not completely forwards
compatible. 
</p>

<p>
The stability of the SQLite database file format and the fact







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<p>
The SQLite database file format is also stable.
All releases of of SQLite version 3 can read and write database
files created by the very first SQLite 3 release (version 3.0.0)
going back to 2004-06-18.  This is "backwards compatibility".
The developers promise to maintain backwards compatibility of
the database file format for all future releases of SQLite 3.
"Forwards compatibility" means that older releases
of SQLite can also read and write databases created by newer
releases.  SQLite is usually, but not completely forwards
compatible. 
</p>

<p>
The stability of the SQLite database file format and the fact
Changes to pages/opcode.in.
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
up to five operands named P1, P2  P3, P4, and P5.  P1, P2, and P3 
are 32-bit signed integers.  These operands often refer to registers.
P2 is always the
jump destination in any operation that might cause a jump.
P4 may be a 32-bit signed integer, a 64-bit signed integer, a
64-bit floating point value, a string literal, a Blob literal,
a pointer to a collating sequence comparison function, or a
pointer to the implemantation of an application-defined SQL
function, or various other things.  P5 is an unsigned character
normally used as a flag.
Some operators use all five operands.  Some use
one or two.  Some operators use none of the operands.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until a Halt instruction is seen, or 







|







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
up to five operands named P1, P2  P3, P4, and P5.  P1, P2, and P3 
are 32-bit signed integers.  These operands often refer to registers.
P2 is always the
jump destination in any operation that might cause a jump.
P4 may be a 32-bit signed integer, a 64-bit signed integer, a
64-bit floating point value, a string literal, a Blob literal,
a pointer to a collating sequence comparison function, or a
pointer to the implementation of an application-defined SQL
function, or various other things.  P5 is an unsigned character
normally used as a flag.
Some operators use all five operands.  Some use
one or two.  Some operators use none of the operands.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until a Halt instruction is seen, or 
Changes to pages/optoverview.in.
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
PARAGRAPH {
  ^(For the index above and WHERE clause like this:
}
CODE {
  ... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
}
PARAGRAPH {
  The index is not usable at all becaues the left-most column of the
  index (column "a") is not constrained.^)  ^Assuming there are no other
  indices, the query above would result in a full table scan.
}
PARAGRAPH {
  ^(For the index above and WHERE clause like this:
}
CODE {







|







170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
PARAGRAPH {
  ^(For the index above and WHERE clause like this:
}
CODE {
  ... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
}
PARAGRAPH {
  The index is not usable at all because the left-most column of the
  index (column "a") is not constrained.^)  ^Assuming there are no other
  indices, the query above would result in a full table scan.
}
PARAGRAPH {
  ^(For the index above and WHERE clause like this:
}
CODE {
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  rowid IN (SELECT rowid FROM /table/ WHERE /expr1/
            UNION SELECT rowid FROM /table/ WHERE /expr2/
            UNION SELECT rowid FROM /table/ WHERE /expr3/)
}
PARAGRAPH {
  The rewritten expression above is conceptual; WHERE clauses containing
  OR are not really rewritten this way.
  The actual implemention of the OR clause uses a mechanism that is
  more efficient than subqueries and which works even 
  for tables where the "rowid" column name has been 
  overloaded for other uses and no longer refers to the real rowid.
  But the essence of the implementation is captured by the statement
  above:  Separate indices are used to find candidate result rows
  from each OR clause term and the final result is the union of
  those rows.







|







268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  rowid IN (SELECT rowid FROM /table/ WHERE /expr1/
            UNION SELECT rowid FROM /table/ WHERE /expr2/
            UNION SELECT rowid FROM /table/ WHERE /expr3/)
}
PARAGRAPH {
  The rewritten expression above is conceptual; WHERE clauses containing
  OR are not really rewritten this way.
  The actual implementation of the OR clause uses a mechanism that is
  more efficient than subqueries and which works even 
  for tables where the "rowid" column name has been 
  overloaded for other uses and no longer refers to the real rowid.
  But the essence of the implementation is captured by the statement
  above:  Separate indices are used to find candidate result rows
  from each OR clause term and the final result is the union of
  those rows.
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
  So which query plan is better? It turns out that the answer depends on
  what kind of data is found in the node and edge tables.
}
PARAGRAPH {
  Let the number of alice nodes be M and the number of bob nodes be N.
  Consider two scenarios. In the first scenario, M and N are both 2 but
  there are thousands of edges on each node. In this case, option 1 is
  perferred. With option 1, the inner loop checks for the existence of
  an edge between a pair of nodes and outputs the result if found. 
  But because there are only 2 alice and bob nodes each, the inner loop
  only has to run 4 times and the query is very quick. Option 2 would
  take much longer here. The outer loop of option 2 only executes twice,
  but because there are a large number of edges leaving each alice node,
  the middle loop has to iterate many thousands of times. It will be
  much slower. So in the first scenario, we prefer to use option 1.







|







564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
  So which query plan is better? It turns out that the answer depends on
  what kind of data is found in the node and edge tables.
}
PARAGRAPH {
  Let the number of alice nodes be M and the number of bob nodes be N.
  Consider two scenarios. In the first scenario, M and N are both 2 but
  there are thousands of edges on each node. In this case, option 1 is
  preferred. With option 1, the inner loop checks for the existence of
  an edge between a pair of nodes and outputs the result if found. 
  But because there are only 2 alice and bob nodes each, the inner loop
  only has to run 4 times and the query is very quick. Option 2 would
  take much longer here. The outer loop of option 2 only executes twice,
  but because there are a large number of edges leaving each alice node,
  the middle loop has to iterate many thousands of times. It will be
  much slower. So in the first scenario, we prefer to use option 1.
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
  result.  ^The result set must contain a single column.
  ^The column in the MIN or MAX function must be an indexed column.
}

HEADING 1 {Automatic Indices} autoindex {automatic indexing} {Automatic indexing}

PARAGRAPH {
  ^(When no indices are available to aid the evaulation of a query, SQLite
  will consider creating a automatic index that lasts only for the duration
  of a single SQL statement and use that index to help boost the query
  performance.)^  Since the cost of constructing the automatic index is
  O(NlogN) (where N is the number of entries in the table) and the cost of
  doing a full table scan is only O(N), an automatic index will
  only be created if SQLite expects that the lookup will be run more than
  logN times during the course of the SQL statement. Consider an example:
}







|
|







943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
  result.  ^The result set must contain a single column.
  ^The column in the MIN or MAX function must be an indexed column.
}

HEADING 1 {Automatic Indices} autoindex {automatic indexing} {Automatic indexing}

PARAGRAPH {
  ^(When no indices are available to aid the evaluation of a query, SQLite
  might create an automatic index that lasts only for the duration
  of a single SQL statement and use that index to help boost the query
  performance.)^  Since the cost of constructing the automatic index is
  O(NlogN) (where N is the number of entries in the table) and the cost of
  doing a full table scan is only O(N), an automatic index will
  only be created if SQLite expects that the lookup will be run more than
  logN times during the course of the SQL statement. Consider an example:
}
Changes to pages/pragma.in.
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatiblity.
<li>^No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>^Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,







|







36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatibility.
<li>^No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>^Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
Pragma cache_size {
    <p>^(<b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the suggested maximum number of database disk pages
    that SQLite will hold in memory at once per open database file.)^  ^Whether
    or not this suggestion is honored is at the discretion of the
    [sqlite3_pcache_methods | Application Defined Page Cache].   ^In the
    default page cache implemention, the suggested cache size is honored
    as long as it is 10 or greater.  ^A suggested cache size of less
    than 10 are treated as if it were 10.
    ^Alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^The default suggested cache size is 2000.</p>








|







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
Pragma cache_size {
    <p>^(<b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the suggested maximum number of database disk pages
    that SQLite will hold in memory at once per open database file.)^  ^Whether
    or not this suggestion is honored is at the discretion of the
    [sqlite3_pcache_methods | Application Defined Page Cache].   ^In the
    default page cache implementation, the suggested cache size is honored
    as long as it is 10 or greater.  ^A suggested cache size of less
    than 10 are treated as if it were 10.
    ^Alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^The default suggested cache size is 2000.</p>

Changes to pages/selfcontained.in.
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
The date and time SQL functions provided by SQLite require
some additional C library support, but those functions can
be also be omitted from the build using compile-time options.
</p>

<p>
Communications between SQLite and the operating system and disk are
mediated through an interchangable 
[sqlite3_vfs | Virtual File System (VFS)] layer.
VFS modules for Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE)
are provided in the source tree.  It is a simple matter to devise an
alternative VFS for embedded devices.
</p>

<p>







|







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
The date and time SQL functions provided by SQLite require
some additional C library support, but those functions can
be also be omitted from the build using compile-time options.
</p>

<p>
Communications between SQLite and the operating system and disk are
mediated through an interchangeable 
[sqlite3_vfs | Virtual File System (VFS)] layer.
VFS modules for Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE)
are provided in the source tree.  It is a simple matter to devise an
alternative VFS for embedded devices.
</p>

<p>
Changes to pages/sharedcache.in.
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
"Schema (sqlite_master) Level Locking").
</p>

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connectino to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>








|







144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
"Schema (sqlite_master) Level Locking").
</p>

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connection to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>

203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
<blockquote><pre>
int sqlite3_enable_shared_cache(int);
</pre></blockquote>

<p>Each call [sqlite3_enable_shared_cache()] effects subsequent database
connections created using [sqlite3_open()], [sqlite3_open16()], or
[sqlite3_open_v2()].  Database connections that already exist are
uneffected.  Each call to [sqlite3_enable_shared_cache()] overrides
all previous calls within the same process.
</p>

<p>Individual database connections created using [sqlite3_open_v2()] can
choose to participate or not participate in shared cache mode by using
the [SQLITE_OPEN_SHAREDCACHE] or [SQLITE_OPEN_PRIVATECACHE] flags the
third parameter.  The use of either of these flags overrides the
global shared cache mode setting established by [sqlite3_enable_shared_cache()].
No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE
and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to
[sqlite3_open_v2()] then the behavior is undefined.</p>







|











203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
<blockquote><pre>
int sqlite3_enable_shared_cache(int);
</pre></blockquote>

<p>Each call [sqlite3_enable_shared_cache()] effects subsequent database
connections created using [sqlite3_open()], [sqlite3_open16()], or
[sqlite3_open_v2()].  Database connections that already exist are
unaffected.  Each call to [sqlite3_enable_shared_cache()] overrides
all previous calls within the same process.
</p>

<p>Individual database connections created using [sqlite3_open_v2()] can
choose to participate or not participate in shared cache mode by using
the [SQLITE_OPEN_SHAREDCACHE] or [SQLITE_OPEN_PRIVATECACHE] flags the
third parameter.  The use of either of these flags overrides the
global shared cache mode setting established by [sqlite3_enable_shared_cache()].
No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE
and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to
[sqlite3_open_v2()] then the behavior is undefined.</p>
Changes to pages/sqlite.in.
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>

<tcl>Code {
$ (((sqlite3 ex1)))
SQLite vresion 3.6.11
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)







|







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>

<tcl>Code {
$ (((sqlite3 ex1)))
SQLite version 3.6.11
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
Changes to pages/support.in.
91
92
93
94
95
96
97
98
99
100
101
unless:
<ul>
<li>You have or intend to acquire a professional support contract
as described above, or</li>
<li>You are working on an open source project.</li>
</ul>
You are welcomed to use SQLite in closed source, proprietary, and/or
commerical projects and to ask questions about such use on the public
mailing list.  But please do not ask to receive free direct technical
support.  The software is free; direct technical support is not.
</p>







|



91
92
93
94
95
96
97
98
99
100
101
unless:
<ul>
<li>You have or intend to acquire a professional support contract
as described above, or</li>
<li>You are working on an open source project.</li>
</ul>
You are welcomed to use SQLite in closed source, proprietary, and/or
commercial projects and to ask questions about such use on the public
mailing list.  But please do not ask to receive free direct technical
support.  The software is free; direct technical support is not.
</p>
Changes to pages/tclsqlite.in.
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396

<p>Column-separator is an optional column separator string.  The default is
the ASCII tab character \t. </p>

<p>Null-indicator is an optional string that indicates a column value is null.
The default is an empty string.  Note that column-separator and
null-indicator are optional positional arguments; if null-indicator
is specified, a column-separator argument must be specifed and
precede the null-indicator argument.</p>

<p>The copy method implements similar functionality to the <b>.import</b>
SQLite shell command. 
The SQLite 2.x <a href="lang.html#copy"><b>COPY</b></a> statement 
(using the PostgreSQL COPY file format)
can be implemented with this method as:</p>







|







382
383
384
385
386
387
388
389
390
391
392
393
394
395
396

<p>Column-separator is an optional column separator string.  The default is
the ASCII tab character \t. </p>

<p>Null-indicator is an optional string that indicates a column value is null.
The default is an empty string.  Note that column-separator and
null-indicator are optional positional arguments; if null-indicator
is specified, a column-separator argument must be specified and
precede the null-indicator argument.</p>

<p>The copy method implements similar functionality to the <b>.import</b>
SQLite shell command. 
The SQLite 2.x <a href="lang.html#copy"><b>COPY</b></a> statement 
(using the PostgreSQL COPY file format)
can be implemented with this method as:</p>
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483

<p>The "exists" method is similar to "onecolumn" and "eval" in that
it executes SQL statements.  The difference is that the "exists" method
always returns a boolean value which is TRUE if a query in the SQL
statement it executes returns one or more rows and FALSE if the SQL
returns an empty set.</p>

<p>The "exists" method is often used to test for the existance of
rows in a table.  For example:</p>

<blockquote><b>
if {&#91;db exists {SELECT 1 FROM table1 WHERE user=$user}&#93;} {<br>
&nbsp;&nbsp;&nbsp;# Processing if $user exists<br>
} else {<br>
&nbsp;&nbsp;&nbsp;# Processing if $user does not exist<br>







|







469
470
471
472
473
474
475
476
477
478
479
480
481
482
483

<p>The "exists" method is similar to "onecolumn" and "eval" in that
it executes SQL statements.  The difference is that the "exists" method
always returns a boolean value which is TRUE if a query in the SQL
statement it executes returns one or more rows and FALSE if the SQL
returns an empty set.</p>

<p>The "exists" method is often used to test for the existence of
rows in a table.  For example:</p>

<blockquote><b>
if {&#91;db exists {SELECT 1 FROM table1 WHERE user=$user}&#93;} {<br>
&nbsp;&nbsp;&nbsp;# Processing if $user exists<br>
} else {<br>
&nbsp;&nbsp;&nbsp;# Processing if $user does not exist<br>
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>backup</b>&nbsp;&nbsp;?<i>source-database</i>?&nbsp;&nbsp;<i>backup-filename</i>
</blockquote>

<p>The optional <i>source-database</i> argument tells which database in
the current connection should be backed up.  The default value is <b>main</b>
(or, in other words, the primary database file).  To back up TEMP tables
use <b>temp</b>.  To backup an auxilary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>backup-filename</i> is the name of a file into which the backup is
written.  <i>Backup-filename</i> does not have to exist ahead of time, but if
it does, it must be a well-formed SQLite database.</p>
}







|







713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>backup</b>&nbsp;&nbsp;?<i>source-database</i>?&nbsp;&nbsp;<i>backup-filename</i>
</blockquote>

<p>The optional <i>source-database</i> argument tells which database in
the current connection should be backed up.  The default value is <b>main</b>
(or, in other words, the primary database file).  To back up TEMP tables
use <b>temp</b>.  To backup an auxiliary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>backup-filename</i> is the name of a file into which the backup is
written.  <i>Backup-filename</i> does not have to exist ahead of time, but if
it does, it must be a well-formed SQLite database.</p>
}
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
<i>dbcmd</i>&nbsp;&nbsp;<b>restore</b>&nbsp;&nbsp;?<i>target-database</i>?&nbsp;&nbsp;<i>source-filename</i>
</blockquote>

<p>The optional <i>target-database</i> argument tells which database in
the current connection should be overwritten with new content.  
The default value is <b>main</b> 
(or, in other words, the primary database file).  To repopulate the TEMP tables
use <b>temp</b>.  To overwrite an auxilary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>source-filename</i> is the name of a existing well-formed SQLite
database file from which the content is extracted.</p>
}








|







737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
<i>dbcmd</i>&nbsp;&nbsp;<b>restore</b>&nbsp;&nbsp;?<i>target-database</i>?&nbsp;&nbsp;<i>source-filename</i>
</blockquote>

<p>The optional <i>target-database</i> argument tells which database in
the current connection should be overwritten with new content.  
The default value is <b>main</b> 
(or, in other words, the primary database file).  To repopulate the TEMP tables
use <b>temp</b>.  To overwrite an auxiliary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>source-filename</i> is the name of a existing well-formed SQLite
database file from which the content is extracted.</p>
}

770
771
772
773
774
775
776
777
778
779
780
781
text of the SQL statement executed and the time elapsed while executing
the statement, in nanoseconds.
</p>
<p>A database handle may only have a single profile script registered at
any time. If there is already a script registered when the profile method
is invoked, the previous profile script is replaced by the new one. If the 
<i>script</i> argument is an empty string, any previously registered 
profile callback is cancelled but no new profile script is registered.
</p>
}

</tcl>







|




770
771
772
773
774
775
776
777
778
779
780
781
text of the SQL statement executed and the time elapsed while executing
the statement, in nanoseconds.
</p>
<p>A database handle may only have a single profile script registered at
any time. If there is already a script registered when the profile method
is invoked, the previous profile script is replaced by the new one. If the 
<i>script</i> argument is an empty string, any previously registered 
profile callback is canceled but no new profile script is registered.
</p>
}

</tcl>
Changes to pages/tempfiles.in.
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
The statement journal is used to undo the first 50 row changes
so that the database is restored to the state it was in at the start
of the statement.
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change muliple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
there are no other active statements on the same database connection then
no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative







|







186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
The statement journal is used to undo the first 50 row changes
so that the database is restored to the state it was in at the start
of the statement.
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change multiple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
there are no other active statements on the same database connection then
no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
Changes to pages/testing.in.
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234

<p>SQLite, like all SQL database engines, makes extensive use of
malloc()  (See the separate report on
[memory allocation | dynamic memory allocation in SQLite] for
additional detail.)
On servers and workstations, malloc() never fails in practice and so correct
handling of out-of-memory (OOM) errors is not particularly important.
But on embedded devices, OOM errors are frightenly common and since
SQLite is frequently used on embedded devices, it is important that
SQLite be able to gracefully handle OOM errors.</p>

<p>OOM testing is accomplished by simulating OOM errors.
SQLite allows an application to substitute an alternative malloc()
implementation using the [sqlite3_config]([SQLITE_CONFIG_MALLOC],...)
interface.  The TCL and TH3 test harnesses are both capable of







|







220
221
222
223
224
225
226
227
228
229
230
231
232
233
234

<p>SQLite, like all SQL database engines, makes extensive use of
malloc()  (See the separate report on
[memory allocation | dynamic memory allocation in SQLite] for
additional detail.)
On servers and workstations, malloc() never fails in practice and so correct
handling of out-of-memory (OOM) errors is not particularly important.
But on embedded devices, OOM errors are frighteningly common and since
SQLite is frequently used on embedded devices, it is important that
SQLite be able to gracefully handle OOM errors.</p>

<p>OOM testing is accomplished by simulating OOM errors.
SQLite allows an application to substitute an alternative malloc()
implementation using the [sqlite3_config]([SQLITE_CONFIG_MALLOC],...)
interface.  The TCL and TH3 test harnesses are both capable of
Changes to pages/threadsafe.in.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<ol>
<li><p><b>Single-thread</b>.
In this mode, all mutexes are disabled and SQLite is unsafe to use in
more than a single thread at once.</p></li>

<li><p><b>Multi-thread</b>.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simulataneously in two or more threads.
</p></li>

<li><p><b>Serialized</b>.
In serialized mode, SQLite can be safely used by multiple threads with no
restriction.</p></li>
</ol>








|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<ol>
<li><p><b>Single-thread</b>.
In this mode, all mutexes are disabled and SQLite is unsafe to use in
more than a single thread at once.</p></li>

<li><p><b>Multi-thread</b>.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.
</p></li>

<li><p><b>Serialized</b>.
In serialized mode, SQLite can be safely used by multiple threads with no
restriction.</p></li>
</ol>

Changes to pages/vdbe.in.
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

<p>This article is a brief introduction to how the VDBE
works and in particular how the various VDBE instructions
(documented <a href="opcode.html">here</a>) work together
to do useful things with the database.  The style is tutorial,
beginning with simple tasks and working toward solving more
complex problems.  Along the way we will visit most
submodules in the SQLite library.  After completeing this tutorial,
you should have a pretty good understanding of how SQLite works
and will be ready to begin studying the actual source code.</p>

<h2>Preliminaries</h2>

<p>The VDBE implements a virtual computer that runs a program in
its virtual machine language.  The goal of each program is to 







|







25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

<p>This article is a brief introduction to how the VDBE
works and in particular how the various VDBE instructions
(documented <a href="opcode.html">here</a>) work together
to do useful things with the database.  The style is tutorial,
beginning with simple tasks and working toward solving more
complex problems.  Along the way we will visit most
submodules in the SQLite library.  After completing this tutorial,
you should have a pretty good understanding of how SQLite works
and will be ready to begin studying the actual source code.</p>

<h2>Preliminaries</h2>

<p>The VDBE implements a virtual computer that runs a program in
its virtual machine language.  The goal of each program is to 
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
as soon as we find it.  We have to locate all records first, then
go back and delete them.  This is because the SQLite database
backend might change the scan order after a delete operation.
And if the scan
order changes in the middle of the scan, some records might be
visited more than once and other records might not be visited at all.</p>

<p>So the implemention of DELETE is really in two loops.  The first loop 
(instructions 5 through 11) locates the records that are to be deleted 
and saves their keys onto a temporary list, and the second loop 
(instructions 16 through 19) uses the key list to delete the records one 
by one.  </p>
}









|







704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
as soon as we find it.  We have to locate all records first, then
go back and delete them.  This is because the SQLite database
backend might change the scan order after a delete operation.
And if the scan
order changes in the middle of the scan, some records might be
visited more than once and other records might not be visited at all.</p>

<p>So the implementation of DELETE is really in two loops.  The first loop 
(instructions 5 through 11) locates the records that are to be deleted 
and saves their keys onto a temporary list, and the second loop 
(instructions 16 through 19) uses the key list to delete the records one 
by one.  </p>
}


890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
<p>The <a href="opcode.html#ListReset">ListReset</a> instruction empties 
the temporary storage list.  This list is emptied automatically when the 
VDBE program terminates, so it isn't necessary in this case.  The Close 
instruction closes the cursor P1.  Again, this is done by the VDBE 
engine when it is finished running this program.  The Commit ends the 
current transaction successfully, and causes all changes that occurred 
in this transaction to be saved to the database.  The final Halt is also 
unneccessary, since it is added to every VDBE program when it is 
prepared to run.</p>


<p>UPDATE statements work very much like DELETE statements except
that instead of deleting the record they replace it with a new one.
Consider this example:
</p>







|







890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
<p>The <a href="opcode.html#ListReset">ListReset</a> instruction empties 
the temporary storage list.  This list is emptied automatically when the 
VDBE program terminates, so it isn't necessary in this case.  The Close 
instruction closes the cursor P1.  Again, this is done by the VDBE 
engine when it is finished running this program.  The Commit ends the 
current transaction successfully, and causes all changes that occurred 
in this transaction to be saved to the database.  The final Halt is also 
unnecessary, since it is added to every VDBE program when it is 
prepared to run.</p>


<p>UPDATE statements work very much like DELETE statements except
that instead of deleting the record they replace it with a new one.
Consider this example:
</p>
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
}

hd_puts {
<p>Remember that every table (except sqlite_master) and every named
index has an entry in the sqlite_master table.  Since we are creating
a new index, we have to add a new entry to sqlite_master.  This is
handled by instructions 3 through 15.  Adding an entry to sqlite_master
works just like any other INSERT statement so we will not say anymore
about it here.  In this example, we want to focus on populating the
new index with valid data, which happens on instructions 16 through 
23.</p>
}

Code {
16    Integer       0      0                                         







|







1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
}

hd_puts {
<p>Remember that every table (except sqlite_master) and every named
index has an entry in the sqlite_master table.  Since we are creating
a new index, we have to add a new entry to sqlite_master.  This is
handled by instructions 3 through 15.  Adding an entry to sqlite_master
works just like any other INSERT statement so we will not say any more
about it here.  In this example, we want to focus on populating the
new index with valid data, which happens on instructions 16 through 
23.</p>
}

Code {
16    Integer       0      0                                         
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
Finally, the callback is invoked at instruction 29.</p>

<p>In summary then, any query with aggregate functions is implemented
by two loops.  The first loop scans the input table and computes
aggregate information into buckets and the second loop scans through
all the buckets to compute the final result.</p>

<p>The realization that an aggregate query is really two consequtive
loops makes it much easier to understand the difference between
a WHERE clause and a HAVING clause in SQL query statement.  The
WHERE clause is a restriction on the first loop and the HAVING
clause is a restriction on the second loop.  You can see this
by adding both a WHERE and a HAVING clause to our example query:</p>









|







1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
Finally, the callback is invoked at instruction 29.</p>

<p>In summary then, any query with aggregate functions is implemented
by two loops.  The first loop scans the input table and computes
aggregate information into buckets and the second loop scans through
all the buckets to compute the final result.</p>

<p>The realization that an aggregate query is really two consecutive
loops makes it much easier to understand the difference between
a WHERE clause and a HAVING clause in SQL query statement.  The
WHERE clause is a restriction on the first loop and the HAVING
clause is a restriction on the second loop.  You can see this
by adding both a WHERE and a HAVING clause to our example query:</p>


1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
The second SELECT statement also constructs rows, but the rows
constructed by the second SELECT are removed from the transient table.
That is why we want the rows to be stored in the key of the SQLite file
instead of in the data -- so they can be easily located and deleted.</p>

<p>Let's look more closely at what is happening here.  The first
SELECT is implemented by the loop at instructions 5 through 10.
Instruction 5 intializes the loop by rewinding its cursor.
Instruction 6 extracts the value of the "two" column from "examp"
and instruction 7 converts this into a row.  Instruction 8 pushes
an empty string onto the stack.  Finally, instruction 9 writes the
row into the temporary table.  But remember, the PutStrKey opcode uses
the top of the stack as the record data and the next on stack as the
key.  For an INSERT statement, the row generated by the
MakeRecord opcode is the record data and the record key is an integer
created by the NewRecno opcode.  But here the roles are reversed and
the row created by MakeRecord is the record key and the record data is
just an empty string.</p>

<p>The second SELECT is implemented by instructions 14 through 19.
Instruction 14 intializes the loop by rewinding its cursor.
A new result row is created from the "four" column of table "examp2"
by instructions 15 and 16.  But instead of using PutStrKey to write this
new row into the temporary table, we instead call Delete to remove
it from the temporary table if it exists.</p>

<p>The result of the compound select is sent to the callback routine
by the loop at instructions 22 through 25.  There is nothing new







|












|







1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
The second SELECT statement also constructs rows, but the rows
constructed by the second SELECT are removed from the transient table.
That is why we want the rows to be stored in the key of the SQLite file
instead of in the data -- so they can be easily located and deleted.</p>

<p>Let's look more closely at what is happening here.  The first
SELECT is implemented by the loop at instructions 5 through 10.
Instruction 5 initializes the loop by rewinding its cursor.
Instruction 6 extracts the value of the "two" column from "examp"
and instruction 7 converts this into a row.  Instruction 8 pushes
an empty string onto the stack.  Finally, instruction 9 writes the
row into the temporary table.  But remember, the PutStrKey opcode uses
the top of the stack as the record data and the next on stack as the
key.  For an INSERT statement, the row generated by the
MakeRecord opcode is the record data and the record key is an integer
created by the NewRecno opcode.  But here the roles are reversed and
the row created by MakeRecord is the record key and the record data is
just an empty string.</p>

<p>The second SELECT is implemented by instructions 14 through 19.
Instruction 14 initializes the loop by rewinding its cursor.
A new result row is created from the "four" column of table "examp2"
by instructions 15 and 16.  But instead of using PutStrKey to write this
new row into the temporary table, we instead call Delete to remove
it from the temporary table if it exists.</p>

<p>The result of the compound select is sent to the callback routine
by the loop at instructions 22 through 25.  There is nothing new
Changes to pages/vtab.in.
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                int argc, sqlite3_value **argv);
</pre></blockquote>

<p>This method begins a search of a virtual table. 
The first argument is a cursor opened by [sqlite3_module.xOpen | xOpen]. 
The next two argument define a particular search index previously 
choosen by [xBestIndex]. The specific meanings of idxNum and idxStr 
are unimportant as long as xFilter and xBestIndex agree on what 
that meaning is.

<p>The xBestIndex function may have requested the values of 
certain expressions using the aConstraintUsage[].argvIndex values 
of the [sqlite3_index_info] structure. 
Those values are passed to xFilter using the argc and argv parameters.







|







756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                int argc, sqlite3_value **argv);
</pre></blockquote>

<p>This method begins a search of a virtual table. 
The first argument is a cursor opened by [sqlite3_module.xOpen | xOpen]. 
The next two argument define a particular search index previously 
chosen by [xBestIndex]. The specific meanings of idxNum and idxStr 
are unimportant as long as xFilter and xBestIndex agree on what 
that meaning is.

<p>The xBestIndex function may have requested the values of 
certain expressions using the aConstraintUsage[].argvIndex values 
of the [sqlite3_index_info] structure. 
Those values are passed to xFilter using the argc and argv parameters.
Changes to req/hlr30000.txt.
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
If the associated database table is a virtual table, the fourth
field of the <i>schema table</i> record shall contain the integer
value 0 (zero).

HLR H30310
In a well-formed database, the fifth field of all <i>schema table</i>
records associated with SQL tables shall contain a "CREATE TABLE"
or "CREATE VIRTUAL TABLE" statment (a text value).  The details
of the statement shall be such that executing the statement
would create a table of precisely the same name and schema as the
existing database table.

HLR H30320
For each PRIMARY KEY or UNIQUE constraint present in the definition
of each SQL table in the database, the schema table of a well-formed







|







130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
If the associated database table is a virtual table, the fourth
field of the <i>schema table</i> record shall contain the integer
value 0 (zero).

HLR H30310
In a well-formed database, the fifth field of all <i>schema table</i>
records associated with SQL tables shall contain a "CREATE TABLE"
or "CREATE VIRTUAL TABLE" statement (a text value).  The details
of the statement shall be such that executing the statement
would create a table of precisely the same name and schema as the
existing database table.

HLR H30320
For each PRIMARY KEY or UNIQUE constraint present in the definition
of each SQL table in the database, the schema table of a well-formed
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
HLR H30530
The most significant bit of all bytes except the last in a serialized
<i>variable length integer</i> is always set. Unless the serialized
form consumes the maximum 9 bytes available, then the most significant
bit of the final byte of the representation is always cleared.

HLR H30540
The eight least significant bytes of the 64-bit twos-compliment
representation of a value stored in a 9 byte <i>variable length
integer</i> are stored in the final byte (byte offset 8) of the
serialized <i>variable length integer</i>. The other 56 bits are
stored in the 7 least significant bits of each of the first 8 bytes
of the serialized <i>variable length integer</i>, in order from
most significant to least significant.








|







265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
HLR H30530
The most significant bit of all bytes except the last in a serialized
<i>variable length integer</i> is always set. Unless the serialized
form consumes the maximum 9 bytes available, then the most significant
bit of the final byte of the representation is always cleared.

HLR H30540
The eight least significant bytes of the 64-bit twos-complement
representation of a value stored in a 9 byte <i>variable length
integer</i> are stored in the final byte (byte offset 8) of the
serialized <i>variable length integer</i>. The other 56 bits are
stored in the 7 least significant bits of each of the first 8 bytes
of the serialized <i>variable length integer</i>, in order from
most significant to least significant.

Changes to req/hlr35000.txt.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
HLR H35030 
While opening a <i>read-only transaction</i>, after successfully
obtaining a <i>shared lock</i> on the database file, SQLite shall
attempt to detect and roll back a <i>hot journal file</i> associated
with the same database file.

HLR H35040 
Assuming no errors have occured, then after attempting to detect and
roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
any entries associated with the current <i>database connection</i>,
then SQLite shall validate the contents of the <i>page cache</i> by
testing the <i>file change counter</i>.  This procedure is known as
<i>cache validiation</i>.

HLR H35050 
If the cache validiate procedure prescribed by H35040 is required and
does not prove that the <i>page cache</i> entries associated with the
current <i>database connection</i> are valid, then SQLite shall discard
all entries associated with the current <i>database connection</i> from
the <i>page cache</i>.

HLR H35060 
When a new <i>database connection</i> is required, SQLite shall attempt







|




|


|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
HLR H35030 
While opening a <i>read-only transaction</i>, after successfully
obtaining a <i>shared lock</i> on the database file, SQLite shall
attempt to detect and roll back a <i>hot journal file</i> associated
with the same database file.

HLR H35040 
Assuming no errors have occurred, then after attempting to detect and
roll back a <i>hot journal file</i>, if the <i>page cache</i> contains
any entries associated with the current <i>database connection</i>,
then SQLite shall validate the contents of the <i>page cache</i> by
testing the <i>file change counter</i>.  This procedure is known as
<i>cache validation</i>.

HLR H35050 
If the cache validate procedure prescribed by H35040 is required and
does not prove that the <i>page cache</i> entries associated with the
current <i>database connection</i> are valid, then SQLite shall discard
all entries associated with the current <i>database connection</i> from
the <i>page cache</i>.

HLR H35060 
When a new <i>database connection</i> is required, SQLite shall attempt
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
When required to truncate a database page from the end of the database
file, SQLite shall discard the associated <i>page cache entry</i>
from the page cache.

HLR H35640 
When required to purge a <i>non-writable dirty page</i> from the
<i>page cache</i>, SQLite shall <i>sync the journal file</i> before
proceding with the write operation required by H35670.

HLR H35660 
After <i>syncing the journal file</i> as required by H35640, SQLite
shall append a new <i>journal header</i> to the <i>journal file</i>
before proceding with the write operation required by H35670.

HLR H35670 
When required to purge a <i>page cache entry</i> that is a
<i>dirty page</i> SQLite shall write the page data into the database
file, using a single call to the xWrite method of the <i>database
connection</i> file handle.








|




|







340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
When required to truncate a database page from the end of the database
file, SQLite shall discard the associated <i>page cache entry</i>
from the page cache.

HLR H35640 
When required to purge a <i>non-writable dirty page</i> from the
<i>page cache</i>, SQLite shall <i>sync the journal file</i> before
proceeding with the write operation required by H35670.

HLR H35660 
After <i>syncing the journal file</i> as required by H35640, SQLite
shall append a new <i>journal header</i> to the <i>journal file</i>
before proceeding with the write operation required by H35670.

HLR H35670 
When required to purge a <i>page cache entry</i> that is a
<i>dirty page</i> SQLite shall write the page data into the database
file, using a single call to the xWrite method of the <i>database
connection</i> file handle.