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: |
d9e242f92a409193b3bc0910bdc28b3d |
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
Changes to pages/asyncvfs.in.
︙ | ︙ | |||
27 28 29 30 31 32 33 | 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 | | | 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 | <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 | | | 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 | 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 | | | 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 | 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> | | | 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 | <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> | | | | 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 | 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, | | | 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 | #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 */ | | | 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 | 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 | | | 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 | <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 | | | 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 | 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> | | | | 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 | </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> | | | 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 | <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]. | | | 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 | <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> | | | 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 | 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 | | | 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 | 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> | | | 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 | [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 | | | 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 | <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 | | | | | 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 | 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) | | | 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 | 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 | | | 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 | 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 | | | 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 | 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 | | | 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 | 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 | | | 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 | <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 | | | 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 | 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 | | | 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 | <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], | | | 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 | 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 | | | 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 | 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 | | | 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 | 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 | | | | 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 | </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 | | | 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 | <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 | | | 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 | 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 | | | 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 | 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). | | | 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 | <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 | | | 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 | 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, | | | 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 | 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 | | | 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 | <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 | | | 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 | 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 | | | 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 | 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> | | | | 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 | 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 | | | 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 | 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 | | | | 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 | 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 | | | 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 | REQ H35030 REQ H35120 <p> Section <cite>hot_journal_detection</cite> contains a description of | | | | 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 | 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 | > | | 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 | 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 | | | 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 | 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 | | | 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 | <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> | | | 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 | <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 | | | | 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 | "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 | | | 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 | 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, | | | 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 "<column> 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 | 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 | | | 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 | 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> | | | | 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 | <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> | | | 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 | <codeblock> SELECT offsets(mail) FROM mail WHERE mail MATCH <full-text query expression>; SELECT snippet(mail) FROM mail WHERE mail MATCH <full-text query expression>; SELECT matchinfo(mail) FROM mail WHERE mail MATCH <full-text query expression>; </codeblock> <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 <full-text query expression>; SELECT snippet(mail) FROM mail WHERE mail MATCH <full-text query expression>; SELECT matchinfo(mail) FROM mail WHERE mail MATCH <full-text query expression>; </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 | 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> | | | | 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 | 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 | | | 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 | <codeblock> SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH <query expression>; SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH <query expression>; </codeblock> <p> The matchinfo function provides much of the information required to calculate | | | 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 <query expression>; SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH <query expression>; </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 | 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 | | | | 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 | 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> | | | 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 | </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 | | | | | 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 <query> 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 | [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 | | | 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 | 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. | | | 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 | 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. | | | | 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 | [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 | | | | 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 | ^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. | | | 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 | <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 | | | 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 | <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 | | | 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 | <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 | | | | | 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 | 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 | | | 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 | ^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 | | | 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 | 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 | | | 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 | <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 | | | | 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 | 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 | | | 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 | 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 | | | 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 | 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} { | | | 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 | 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 | | | 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 | 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 | | | 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 | <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. | | | 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 | 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 | | | 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 | 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 { | | | 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 | 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. | | | 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 | 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 | | | 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 | 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 { | | | | 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 | 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 | | | 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 | 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 | | | 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 | 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 | | | 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 | "Schema (sqlite_master) Level Locking"). </p> <blockquote><pre> /* Set the value of the read-uncommitted flag: ** ** True -> Set the connection to read-uncommitted mode. | | | 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 = <boolean>; /* Retrieve the current value of the read-uncommitted flag */ PRAGMA read_uncommitted; </pre></blockquote> |
︙ | ︙ | |||
203 204 205 206 207 208 209 | <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 | | | 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 | 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))) | | | 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 | 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 | | | 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 | <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 | | | 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 | <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> | | | 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 {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {<br> # Processing if $user exists<br> } else {<br> # Processing if $user does not exist<br> |
︙ | ︙ | |||
713 714 715 716 717 718 719 | <blockquote> <i>dbcmd</i> <b>backup</b> ?<i>source-database</i>? <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 | | | 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 | <blockquote> <i>dbcmd</i> <b>backup</b> ?<i>source-database</i>? <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 | <i>dbcmd</i> <b>restore</b> ?<i>target-database</i>? <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 | | | 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 | <i>dbcmd</i> <b>restore</b> ?<i>target-database</i>? <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 | 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 | | | 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 | 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 | | | 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 | <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. | | | 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 | <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 | | | 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 | <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 | | | 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 | 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> | | | 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 | <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 | | | 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 | } 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 | | | 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 | 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> | | | 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 | 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. | | | | 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 | 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 | | | 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 | 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" | | | 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 | 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 | | | 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 | 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 | | | | | 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 | 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 | | | | 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. |
︙ | ︙ |