Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Documentation typos. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
b503d1f516715b0bea4637cb34e04a89 |
User & Date: | drh 2011-06-20 21:48:16.611 |
Context
2011-06-20
| ||
23:51 | More typo fixes. (check-in: 8f9d1225b6 user: drh tags: trunk) | |
21:48 | Documentation typos. (check-in: b503d1f516 user: drh tags: trunk) | |
20:27 | Update the compile-time options. (check-in: 46855636fa user: drh tags: trunk) | |
Changes
Changes to pages/34to35.in.
︙ | ︙ | |||
385 386 387 388 389 390 391 | PARAGRAPH { The differences between an [SQLITE_OPEN_TEMP_DB] database and an [SQLITE_OPEN_TRANSIENT_DB] database is this: The [SQLITE_OPEN_TEMP_DB] is used for explicitly declared and named TEMP tables (using the CREATE TEMP TABLE syntax) or for named tables in a temporary database that is created by opening a database with a filename that is an empty | | | 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 | PARAGRAPH { The differences between an [SQLITE_OPEN_TEMP_DB] database and an [SQLITE_OPEN_TRANSIENT_DB] database is this: The [SQLITE_OPEN_TEMP_DB] is used for explicitly declared and named TEMP tables (using the CREATE TEMP TABLE syntax) or for named tables in a temporary database that is created by opening a database with a filename that is an empty string. An [SQLITE_OPEN_TRANSIENT_DB] holds a database table that SQLite creates automatically in order to evaluate a subquery or ORDER BY or GROUP BY clause. Both TEMP_DB and TRANSIENT_DB databases are private and are deleted automatically. TEMP_DB databases last for the duration of the database connection. TRANSIENT_DB databases last only for the duration of a single SQL statement. } |
︙ | ︙ |
Changes to pages/35to36.in.
︙ | ︙ | |||
142 143 144 145 146 147 148 | } INDENTED { <strong>Key Point:</strong> The incompatible changes in the SQLite operating-system interface for version 3.6.0 only affect the rare applications that make use of the [sqlite3_vfs | virtual file system] interface or that | | | 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | } INDENTED { <strong>Key Point:</strong> The incompatible changes in the SQLite operating-system interface for version 3.6.0 only affect the rare applications that make use of the [sqlite3_vfs | virtual file system] interface or that supply an application-defined [sqlite3_mutex_enter | mutex implementation] or that make use of other obscure compile-time options. The changes introduced by SQLite version 3.6.0 will have zero impact on the vast majority of SQLite applications that use the built-in interfaces to Unix, Windows, and OS/2 and that use the standard build configuration. } HEADING 2 {Changes In The Way The IN Operator Handles NULLs} |
︙ | ︙ |
Changes to pages/c_interface.in.
︙ | ︙ | |||
251 252 253 254 255 256 257 | <dd><p>This value is returned if the operating system informs SQLite 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 | | | 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 | <dd><p>This value is returned if the operating system informs SQLite 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 a 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 |
︙ | ︙ |
Changes to pages/changes.in.
︙ | ︙ | |||
1511 1512 1513 1514 1515 1516 1517 | smaller. The code is now also a little bit faster.</li> <li>sqlite_exec() is now implemented as a wrapper around sqlite_compile() and sqlite_step().</li> <li>The built-in min() and max() functions now honor the difference between NUMERIC and TEXT datatypes. Formerly, min() and max() always assumed their arguments were of type NUMERIC.</li> <li>New HH:MM:SS modifier to the built-in date/time functions.</li> | | | 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 | smaller. The code is now also a little bit faster.</li> <li>sqlite_exec() is now implemented as a wrapper around sqlite_compile() and sqlite_step().</li> <li>The built-in min() and max() functions now honor the difference between NUMERIC and TEXT datatypes. Formerly, min() and max() always assumed their arguments were of type NUMERIC.</li> <li>New HH:MM:SS modifier to the built-in date/time functions.</li> <li>Experimental sqlite_last_statement_changes() API added. Fixed the last_insert_rowid() function so that it works correctly with triggers.</li> <li>Add functions prototypes for the database encryption API.</li> <li>Fix several nuisance bugs.</li> } chng {2004 February 8 (2.8.12)} { |
︙ | ︙ |
Changes to pages/custombuild.in.
︙ | ︙ | |||
109 110 111 112 113 114 115 | 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 | | | 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | 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 [sqlite3_config()] interface. To completely disable all mutexing, the application can invoke:</p> <blockquote><pre> sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); </pre></blockquote> <p>Disabling mutexes at run-time is not as effective as disabling them |
︙ | ︙ |
Changes to pages/datatypes.in.
︙ | ︙ | |||
74 75 76 77 78 79 80 | INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data will result in an error. </p> <p> INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY | | | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data will result in an error. </p> <p> INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY KEY column, the column will actually be filled with an integer that is one greater than the largest key already in the table. Or if the largest key is 2147483647, then the column will be filled with a random integer. Either way, the INTEGER PRIMARY KEY column will be assigned a unique integer. You can retrieve this integer using the <b>sqlite_last_insert_rowid()</b> API function or using the <b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement. </p> |
︙ | ︙ |
Changes to pages/fileformat2.in.
︙ | ︙ | |||
744 745 746 747 748 749 750 | byte are used to hold overflow content.</p> <h3>1.7 Pointer Map or Ptrmap Pages</h3> <p>Pointer map or ptrmap pages are extra pages inserted into the database to make the operation of [auto_vacuum] and [incremental_vacuum] modes more efficient. Other page types in the database typically have pointers | | | | 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 | byte are used to hold overflow content.</p> <h3>1.7 Pointer Map or Ptrmap Pages</h3> <p>Pointer map or ptrmap pages are extra pages inserted into the database to make the operation of [auto_vacuum] and [incremental_vacuum] modes more efficient. Other page types in the database typically have pointers from parent to child. For example, an interior b-tree page contains pointers to its child b-tree pages and an overflow chain has a pointer from earlier to later links in the chain. A ptrmap page contains linkage information going in the opposite direction, from child to parent.</p> <p>^Ptrmap pages must exist in any database file which has a non-zero largest root b-tree page value at offset 52 in the database header. ^If the largest root b-tree page value is zero, then the database must not contain ptrmap pages.</p> <p>^In a database with ptrmap pages, the first ptrmap page is page 2. A ptrmap page consists of an array of 5-byte entries. Let J be the number of 5-byte entries that will fit in the usable space of a page. (In other words, J=U/5.) ^The first ptrmap page will contain back pointer information for pages 3 through J+2, inclusive. ^The second pointer map page will be on page J+3 and that ptrmap page will provide back pointer information for pages J+4 through 2*J+3 inclusive. And so forth for the entire database file.</p> <p>^(In a database that uses ptrmap pages, all pages at locations identified by the computation in the previous paragraph must be ptrmap page and no other page may be a ptrmap page. Except, if the byte-lock page happens to fall on the same page number as a ptrmap page, then the ptrmap is moved to the following page for that one case.)^</p> |
︙ | ︙ |
Changes to pages/fileio.in.
︙ | ︙ | |||
463 464 465 466 467 468 469 | ASSUMPTION A21004 If a system failure occurs during a "delete file" operation, it is assumed that following system recovery the file-system will either contain the file being deleted in the state it was in before the operation was attempted, or not contain the file at all. It is assumed that it is not possible for the file to have become corrupted | | | 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 | ASSUMPTION A21004 If a system failure occurs during a "delete file" operation, it is assumed that following system recovery the file-system will either contain the file being deleted in the state it was in before the operation was attempted, or not contain the file at all. It is assumed that it is not possible for the file to have become corrupted purely as a result of a failure occurfing during a "delete file" operation. <p> The effects of a <b>truncate file</b> operation are not assumed to be made persistent until after the corresponding file has been <i>synced</i>. |
︙ | ︙ |
Changes to pages/fts3.in.
︙ | ︙ | |||
317 318 319 320 321 322 323 | operator is a string consisting of a single term. In this case, the MATCH expression evaluates to true for all documents that contain one or more instances of the specified word ("sqlite", "search" or "database", depending on which example you look at). Specifying a single term as the right-hand operand of the MATCH operator results in the simplest and most common type of full-text query possible. However more complicated queries are possible, including phrase searches, term-prefix searches and searches for documents | | | | 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 | operator is a string consisting of a single term. In this case, the MATCH expression evaluates to true for all documents that contain one or more instances of the specified word ("sqlite", "search" or "database", depending on which example you look at). Specifying a single term as the right-hand operand of the MATCH operator results in the simplest and most common type of full-text query possible. However more complicated queries are possible, including phrase searches, term-prefix searches and searches for documents containing combinations of terms occurring within a defined proximity of each other. The various ways in which the full-text index may be queried are [FTS MATCH|described below]. <p> Normally, full-text queries are case-insensitive. However, this is dependent on the specific [tokenizer] used by the FTS table being queried. Refer to the section on [tokenizer|tokenizers] for details. <p> The paragraph above notes that a MATCH operator with a simple term as the right-hand operand evaluates to true for all documents that contain the specified term. In this context, the "document" may refer to either the |
︙ | ︙ | |||
449 450 451 452 453 454 455 | <codeblock> -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS </codeblock> <p> Note that enabling FTS3 also makes FTS4 available. There is not a separate | | | 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 | <codeblock> -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS </codeblock> <p> Note that enabling FTS3 also makes FTS4 available. There is not a separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite either supports both FTS3 and FTS4 or it supports neither. <p> If using the amalgamation autoconf based build system, setting the CPPFLAGS environment variable while running the 'configure' script is an easy way to set these macros. For example, the following command: |
︙ | ︙ | |||
771 772 773 774 775 776 777 | <p> FTS query set operations using the standard query syntax are similar, but not identical, to set operations with the enhanced query syntax. There are four differences, as follows: <ol> <li value=1><p> Only the implicit version of the AND operator is supported. | | | 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 | <p> FTS query set operations using the standard query syntax are similar, but not identical, to set operations with the enhanced query syntax. There are four differences, as follows: <ol> <li value=1><p> Only the implicit version of the AND operator is supported. Specifying the string "AND" as part of a standard query syntax query is interpreted as a term query for the set of documents containing the term "and". </ol> <ol> <li value=2><p> Parenthesis are not supported. </ol> |
︙ | ︙ | |||
998 999 1000 1001 1002 1003 1004 | a larger number of "extra" phrase matches are favoured. The start of the selected text fragment may be moved a few tokens forward or backward to attempt to concentrate the phrase matches toward the center of the fragment. <p> Assuming <i>N</i> is a positive value, if no fragments can be found that | | | 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 | a larger number of "extra" phrase matches are favoured. The start of the selected text fragment may be moved a few tokens forward or backward to attempt to concentrate the phrase matches toward the center of the fragment. <p> Assuming <i>N</i> is a positive value, if no fragments can be found that contain a phrase match corresponding to each matchable phrase, the snippet function attempts to find two fragments of approximately <i>N</i>/2 tokens that between them contain at least one phrase match for each matchable phrase matched by the current row. If this fails, attempts are made to find three fragments of <i>N</i>/3 tokens each and finally four <i>N</i>/4 token fragments. If a set of four fragments cannot be found that encompasses the required phrase matches, the four fragments of <i>N</i>/4 tokens that provide the best coverage are selected. |
︙ | ︙ | |||
1068 1069 1070 1071 1072 1073 1074 | <p> The matchinfo function is called with either one or two arguments. As for all auxiliary functions, the first argument must be the special [FTS hidden column]. The second argument, if it is specified, must be a text value comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's' and 'x'. If no second argument is explicitly supplied, it defaults to "pcx". The | | | 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 | <p> The matchinfo function is called with either one or two arguments. As for all auxiliary functions, the first argument must be the special [FTS hidden column]. The second argument, if it is specified, must be a text value comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's' and 'x'. If no second argument is explicitly supplied, it defaults to "pcx". The second argument is referred to as the "format string" below. <p> Characters in the matchinfo format string are processed from left to right. Each character in the format string causes one or more 32-bit unsigned integer values to be added to the returned array. The "values" column in the following table contains the number of integer values appended to the output buffer for each supported format string character. In the formula |
︙ | ︙ | |||
1103 1104 1105 1106 1107 1108 1109 | </ul> The first set of three values corresponds to the left-most column of the table (column 0) and the left-most matchable phrase in the query (phrase 0). If the table has more than one column, the second set of three values in the output array correspond to phrase 0 and column 1. Followed by phrase 0, column 2 and so on for all columns of the table. And so on for phrase 1, column 0, then phrase 1, column 1 | | | 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 | </ul> The first set of three values corresponds to the left-most column of the table (column 0) and the left-most matchable phrase in the query (phrase 0). If the table has more than one column, the second set of three values in the output array correspond to phrase 0 and column 1. Followed by phrase 0, column 2 and so on for all columns of the table. And so on for phrase 1, column 0, then phrase 1, column 1 etc. In other words, the data for occurrences of phrase <i>p</i> in column <i>c</i> may be found using the following formula: <pre> hits_this_row = array[3 * (c + p*cols) + 0] hits_all_rows = array[3 * (c + p*cols) + 1] docs_with_hits = array[3 * (c + p*cols) + 2] </pre> |
︙ | ︙ | |||
1825 1826 1827 1828 1829 1830 1831 | b-tree and merge the results), but it has been found that in practice this overhead is often negligible. <h2>Variable Length Integer (varint) Format</h2> <p> Integer values stored as part of segment b-tree nodes are encoded using the | | | 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 | b-tree and merge the results), but it has been found that in practice this overhead is often negligible. <h2>Variable Length Integer (varint) Format</h2> <p> Integer values stored as part of segment b-tree nodes are encoded using the FTS varint format. This encoding is similar, but <b>not identical</b>, to the <a href="fileformat.html#varint_format">SQLite varint format</a>. <p> An encoded FTS 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 |
︙ | ︙ |
Changes to pages/howtocorrupt.in.
1 2 3 4 5 6 | <title>How To Corrupt An SQLite Database File</title> <tcl>hd_keywords {how to corrupt}</tcl> <h1 align=center>How To Corrupt An SQLite Database File</h1> <p>An SQLite database is highly resistant to corruption. | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <title>How To Corrupt An SQLite Database File</title> <tcl>hd_keywords {how to corrupt}</tcl> <h1 align=center>How To Corrupt An SQLite Database File</h1> <p>An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction, the partially written transaction should be automatically rolled back the next time the database file is accessed. The recovery process is fully automatic and does not require any action on the part of the user or the application. </p> |
︙ | ︙ | |||
232 233 234 235 236 237 238 | due to a disk drive failure. It is very rare, but disks will occasionally flip a bit in the middle of a sector.</p> <p>We are told that in some flash memory controllers the wear-leveling logic can cause random filesystem damage if power is interrupted during a write. This can manifest, for example, as random changes in the middle of a file that was not even open at the time of the power loss. So, for example, | | | 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | due to a disk drive failure. It is very rare, but disks will occasionally flip a bit in the middle of a sector.</p> <p>We are told that in some flash memory controllers the wear-leveling logic can cause random filesystem damage if power is interrupted during a write. This can manifest, for example, as random changes in the middle of a file that was not even open at the time of the power loss. So, for example, a device would be writing content into an MP3 file in flash memory when a power loss occurs, and that could result in an SQLite database being corrupted even though the database as not even in use at the time of the power loss.</p> <h2>5.0 Memory corruption</h2> <p>SQLite is a C-library that runs in the same address space as the |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
356 357 358 359 360 361 362 | finishes when it is [sqlite3_blob_close() | closed].)^ </p> <p> ^The explicit COMMIT command runs immediately, even if there are pending [SELECT] statements. ^However, if there are pending write operations, the COMMIT command | | | 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 | finishes when it is [sqlite3_blob_close() | closed].)^ </p> <p> ^The explicit COMMIT command runs immediately, even if there are pending [SELECT] statements. ^However, if there are pending write operations, the COMMIT command will fail with an error code [SQLITE_BUSY]. </p> <p> ^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code if an another thread or process has a [shared lock] on the database that prevented the database from being updated. ^When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later |
︙ | ︙ | |||
782 783 784 785 786 787 788 | is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error. <p>If a table has a single column primary key, and the declared type of that column is "INTEGER", then the column is known as an [INTEGER PRIMARY KEY]. | | | 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 | is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error. <p>If a table has a single column primary key, and the declared type of that column is "INTEGER", then the column is known as an [INTEGER PRIMARY KEY]. See below for a description of the special properties and behaviors associated with an [INTEGER PRIMARY KEY]. <p>^Each row in a table with a primary key must feature a unique combination of values in its primary key columns. ^For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so that two or more rows |
︙ | ︙ | |||
884 885 886 887 888 889 890 | the rowid. <p> The exception mentioned above is that ^if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key. This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in very serious backwards incompatibilities. | | | 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 | the rowid. <p> The exception mentioned above is that ^if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key. This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in very serious backwards incompatibilities. The SQLite developers feel that goofy behavior in a corner case is far better than a compatibility break, so the original behavior is retained. This means that ^(the following three table declarations all cause the column "x" to be an alias for the rowid (an integer primary key): <ul> <li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt> <li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt> |
︙ | ︙ | |||
1251 1252 1253 1254 1255 1256 1257 | remaining rows are deleted. <p>^If the DELETE statement has no ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are assembled in an arbitrary order before applying the LIMIT and OFFSET clauses to determine the subset that are actually deleted. | | | 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 | remaining rows are deleted. <p>^If the DELETE statement has no ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are assembled in an arbitrary order before applying the LIMIT and OFFSET clauses to determine the subset that are actually deleted. <p>^(The ORDER BY clause on a DELETE statement is used only to determine which rows fall within the LIMIT. The order in which rows are deleted is arbitrary and is not influenced by the ORDER BY clause.)^ <tcl>hd_fragment truncateopt {truncate optimization}</tcl> <h3>The Truncate Optimization</h3> <p>^When the WHERE is omitted from a DELETE statement and the table |
︙ | ︙ | |||
2105 2106 2107 2108 2109 2110 2111 | ^The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. ^The result of load_extension() is always a NULL. ^If Y is omitted then the default entry point of <b>sqlite3_extension_init</b> is used. ^The load_extension() function raises an exception if the extension fails to load or initialize correctly. <p>^The load_extension() function will fail if the extension attempts to | | | 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 | ^The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. ^The result of load_extension() is always a NULL. ^If Y is omitted then the default entry point of <b>sqlite3_extension_init</b> is used. ^The load_extension() function raises an exception if the extension fails to load or initialize correctly. <p>^The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence. ^The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the [sqlite3_load_extension()] C-language API.</p> } |
︙ | ︙ | |||
2683 2684 2685 2686 2687 2688 2689 | </tcl> <p>The INSERT statement comes in three basic forms. <ul> <li><p>^The first form (with the "VALUES" keyword) creates a single new row in an existing table. ^If no column-list is specified then the number of values must be the same as the number of columns in the table. ^In this case | | | 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 | </tcl> <p>The INSERT statement comes in three basic forms. <ul> <li><p>^The first form (with the "VALUES" keyword) creates a single new row in an existing table. ^If no column-list is specified then the number of values must be the same as the number of columns in the table. ^In this case the result of evaluating the left-most expression in the VALUES list is inserted into the left-most column of the new row, and so on. ^If a column-list is specified, then the number of values must match the number of specified columns. ^Each of the named columns of the new row is populated with the results of evaluating the corresponding VALUES expression. ^Table columns that do not appear in the column list are populated with the default column value (specified as part of the CREATE TABLE statement), or with NULL if no default value is specified. |
︙ | ︙ | |||
2820 2821 2822 2823 2824 2825 2826 | <p>^The [sqlite3_update_hook | update hook] is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. ^Nor does REPLACE increment the [sqlite3_changes | change counter]. The exceptional behaviors defined in this paragraph might change in a future release.</p> </dl> | | | 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 | <p>^The [sqlite3_update_hook | update hook] is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. ^Nor does REPLACE increment the [sqlite3_changes | change counter]. The exceptional behaviors defined in this paragraph might change in a future release.</p> </dl> <p>^The algorithm specified in the OR clause of an INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. ^If no algorithm is specified anywhere, the ABORT algorithm is used.</p> <tcl> ############################################################################## Section REINDEX reindex REINDEX |
︙ | ︙ | |||
3184 3185 3186 3187 3188 3189 3190 | (columns are numbered from left to right starting with 1). <li><p>^If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column. <li><p>^Otherwise, if the ORDER BY expression is any other expression, it | | | 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 | (columns are numbered from left to right starting with 1). <li><p>^If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column. <li><p>^Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. ^If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions. ^However, if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column. </ol> <p>^For the purposes of sorting rows, values are compared in the same way |
︙ | ︙ | |||
3276 3277 3278 3279 3280 3281 3282 | <p>The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a column name to the left of the equals sign and a scalar expression to the right. ^For each affected row, the named columns are set to the values found by evaluating the corresponding scalar expressions. ^If a single column-name appears more than once in the list of | | | 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 | <p>The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a column name to the left of the equals sign and a scalar expression to the right. ^For each affected row, the named columns are set to the values found by evaluating the corresponding scalar expressions. ^If a single column-name appears more than once in the list of assignment expressions, all but the rightmost occurrence is ignored. ^Columns that do not appear in the list of assignments are left unmodified. ^The scalar expressions may refer to columns of the row being updated. ^In this case all scalar expressions are evaluated before any assignments are made. <p>^The optional conflict-clause allows the user to nominate a specific constraint conflict resolution algorithm to use during this one UPDATE command. Refer to the section entitled [ON CONFLICT] for additional information. |
︙ | ︙ | |||
3408 3409 3410 3411 3412 3413 3414 | <tcl> ############################################################################## Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}} </tcl> | | | 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 | <tcl> ############################################################################## Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}} </tcl> <p>^The INDEXED BY phrase is an SQL extension found only in SQLite which can be used to verify that the correct indices are being used on a [DELETE], [SELECT], or [UPDATE] statement. ^The INDEXED BY phrase always follows the name of a table that SQLite will be reading. The INDEXED BY phrase can be seen in the following syntax diagrams:</p> <tcl> |
︙ | ︙ |
Changes to pages/lockingv3.in.
︙ | ︙ | |||
224 225 226 227 228 229 230 | </ul> </li> </ul> <tcl>HEADING 2 {Dealing with hot journals} hot_journals</tcl> <p> | | | 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | </ul> </li> </ul> <tcl>HEADING 2 {Dealing with hot journals} hot_journals</tcl> <p> Before reading from a database file, SQLite always checks to see if that database file has a hot journal. If the file does have a hot journal, then the journal is rolled back before the file is read. In this way, we ensure that the database file is in a consistent state before it is read. </p> <p>When a process wants to read from a database file, it followed the following sequence of steps: |
︙ | ︙ |
Changes to pages/oldnews.in.
︙ | ︙ | |||
233 234 235 236 237 238 239 | An analysis of the problem suggests that the bug might be able to cause database corruption, however focused efforts to find a real-world test cases that actually causes database corruption have so far been unsuccessful. Hence, the likelihood of this bug causing problems is low. Nevertheless, we have decided to do an emergency branch release out of an abundance of caution. | | | 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 | An analysis of the problem suggests that the bug might be able to cause database corruption, however focused efforts to find a real-world test cases that actually causes database corruption have so far been unsuccessful. Hence, the likelihood of this bug causing problems is low. Nevertheless, we have decided to do an emergency branch release out of an abundance of caution. The [version 3.6.6.2] release also fixes an obscure memory leak that can occur following a disk I/O error. } newsitem {2008-Nov-22} {Version 3.6.6.1} { This release fixes a bug that was introduced into SQLite [version 3.6.4] and that can cause database corruption in obscure cases. This bug has never been seen in the wild; it was first detected by internal stress |
︙ | ︙ | |||
374 375 376 377 378 379 380 | and will avoid an entire class of stack overflow bugs that have caused problems in the past. Even though this change is large, extensive testing has found zero errors in the new virtual machine and so we believe this to be a very stable release. } newsitem {2007-Dec-14} {Version 3.5.4} { | | | 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 | and will avoid an entire class of stack overflow bugs that have caused problems in the past. Even though this change is large, extensive testing has found zero errors in the new virtual machine and so we believe this to be a very stable release. } newsitem {2007-Dec-14} {Version 3.5.4} { Version 3.5.4 fixes a long-standing but obscure bug in UPDATE and DELETE which might cause database corruption. (See ticket #2832.) Upgrading is recommended for all users. This release also brings the processing of ORDER BY statements into compliance with standard SQL. This could, in theory, cause problems for existing applications that depend on the older, buggy behavior. See ticket #2822 for additional information. |
︙ | ︙ | |||
602 603 604 605 606 607 608 | This release fixes many minor bugs and documentation typos and provides some minor new features and performance enhancements. Upgrade only if you are having problems or need one of the new features. } newsitem {2006-Feb-11} {Version 3.3.4} { This release fixes several bugs, including a | | | 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 | This release fixes many minor bugs and documentation typos and provides some minor new features and performance enhancements. Upgrade only if you are having problems or need one of the new features. } newsitem {2006-Feb-11} {Version 3.3.4} { This release fixes several bugs, including a blunder that might cause a deadlock on multithreaded systems. Anyone using SQLite in a multithreaded environment should probably upgrade. } newsitem {2006-Jan-31} {Version 3.3.3 stable} { There have been no major problems discovered in version 3.3.2, so we hereby declare the new APIs and language features to be stable and supported. |
︙ | ︙ |
Changes to pages/optoverview.in.
︙ | ︙ | |||
345 346 347 348 349 350 351 | PRAGMA case_sensitive_like=ON; } PARAGRAPH { Then the LIKE operator pays attention to case and the example above would evaluate to false.)^ ^Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. ^International character sets | | | 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 | PRAGMA case_sensitive_like=ON; } PARAGRAPH { Then the LIKE operator pays attention to case and the example above would evaluate to false.)^ ^Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. ^International character sets are case sensitive in SQLite unless an application-defined [collating sequence] and [like | like() SQL function] are provided that take non-ASCII characters into account. ^But if an application-defined collating sequence and/or like() SQL function are provided, the LIKE optimization described here will never be taken. } PARAGRAPH { |
︙ | ︙ | |||
437 438 439 440 441 442 443 | optimization described above. } HEADING 1 {Joins} joins PARAGRAPH { | | | 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 | optimization described above. } HEADING 1 {Joins} joins PARAGRAPH { ^The ON and USING clauses of an inner join are converted into additional terms of the WHERE clause prior to WHERE clause analysis described above in paragraph 1.0. ^(Thus with SQLite, there is no computational advantage to use the newer SQL92 join syntax over the older SQL89 comma-join syntax. They both end up accomplishing exactly the same thing on inner joins.)^ } PARAGRAPH { |
︙ | ︙ | |||
608 609 610 611 612 613 614 | SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the [ANALYZE] results in the <b>sqlite_stat1</b> and <b>sqlite_stat2</b> tables. That approach is not recommended except for the one scenario described in the following paragraph. } PARAGRAPH { | | | 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 | SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the [ANALYZE] results in the <b>sqlite_stat1</b> and <b>sqlite_stat2</b> tables. That approach is not recommended except for the one scenario described in the following paragraph. } PARAGRAPH { For a program that uses an SQLite database as its application file format, when a new database instances is first created the [ANALYZE] command is ineffective because the database contain no data from which to gather statistics. In that case, one could construct a large prototype database containing typical data during development and run the [ANALYZE] command on this prototype database to gather statistics, then save the prototype statistics as part of the application. After deployment, when the application goes to create a new database file, |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
52 53 54 55 56 57 58 | Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content] global PragmaDebug foreach x $namelist {set PragmaDebug($x) 1} } </tcl> | | | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content] global PragmaDebug foreach x $namelist {set PragmaDebug($x) 1} } </tcl> <p>The PRAGMA statement is an SQL extension specific to SQLite and used to 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 |
︙ | ︙ | |||
680 681 682 683 684 685 686 | the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</p> } Pragma reverse_unordered_selects { <p>^(<b>PRAGMA reverse_unordered_selects; <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b>)^</p> | | | 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 | the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</p> } Pragma reverse_unordered_selects { <p>^(<b>PRAGMA reverse_unordered_selects; <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b>)^</p> <p>^When enabled, this PRAGMA causes [SELECT] statements without an ORDER BY clause to emit their results in the reverse order of what they normally would. This can help debug applications that are making invalid assumptions about the result order.<p>SQLite makes no guarantees about the order of results if a SELECT omits the ORDER BY clause. Even so, the order of results does not change from one run to the next, and so many applications mistakenly come to depend on the arbitrary output order whatever that order happens to be. However, |
︙ | ︙ |
Changes to pages/requirements.in.
︙ | ︙ | |||
19 20 21 22 23 24 25 | <li>Derived high-level requirements</li> <li>Low-level requirements</li> <li>Derived low-level requirements</li> </ul> <p>The usual distinction between high-level and low-level requirements is that high-level requirements describe "what" the system does and the | | | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | <li>Derived high-level requirements</li> <li>Low-level requirements</li> <li>Derived low-level requirements</li> </ul> <p>The usual distinction between high-level and low-level requirements is that high-level requirements describe "what" the system does and the low-level requirements describe "how" the system does it. Since the requirements denoted here describe the behavior of SQLite and not its implementation, they are best thought of as high-level requirements. Consistent with that view, most of the requirements numbers begin with the letter "<b>H</b>" (for "high-level"). A few of the requirements presented here specify broad objectives that SQLite strives to achieve. These broad requirements can be thought of as system requirements and are number with an initial letter "<b>S</b>".</p> |
︙ | ︙ |
Changes to pages/tclsqlite.in.
︙ | ︙ | |||
757 758 759 760 761 762 763 | 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> | | | 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 | 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 an existing well-formed SQLite database file from which the content is extracted.</p> } ############################################################################## METHOD version { Return the current library version. For example, "3.6.17". } |
︙ | ︙ |
Changes to pages/tempfiles.in.
︙ | ︙ | |||
355 356 357 358 359 360 361 | <p> More complex queries may or may not be able to employ query flattening to avoid the temporary table. Whether or not the query can be flattened depends on such factors as whether or not the subquery or outer query contain aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. | | | 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | <p> More complex queries may or may not be able to employ query flattening to avoid the temporary table. Whether or not the query can be flattened depends on such factors as whether or not the subquery or outer query contain aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. The rules for when a query and cannot be flattened are very complex and are beyond the scope of this document. </p> <tcl>hd_fragment transidx</tcl> <h3>2.6 Transient Indices</h3> <p> |
︙ | ︙ | |||
416 417 418 419 420 421 422 | new sort algorithm will also use temporary files, but not in the same way as the current implementation, the temporary files for the new implementation will probably not be index files. </p> <p> The DISTINCT keyword on an aggregate query is implemented by | | | 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 | new sort algorithm will also use temporary files, but not in the same way as the current implementation, the temporary files for the new implementation will probably not be index files. </p> <p> The DISTINCT keyword on an aggregate query is implemented by creating a transient index in a temporary file and storing each result row in that index. As new result rows are computed a check is made to see if they already exist in the transient index and if they do the new result row is discarded. </p> <p> The UNION operator for compound queries is implemented by creating |
︙ | ︙ | |||
550 551 552 553 554 555 556 | <p> The default setting for the [temp_store pragma] is 0, which means to following the recommendation of [SQLITE_TEMP_STORE] compile-time parameter. </p> <p> | | | 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 | <p> The default setting for the [temp_store pragma] is 0, which means to following the recommendation of [SQLITE_TEMP_STORE] compile-time parameter. </p> <p> To reiterate, the [SQLITE_TEMP_STORE] compile-time parameter and the [temp_store pragma] only influence the temporary files other than the rollback journal and the master journal. The rollback journal and the master journal are always written to disk regardless of the settings of the [SQLITE_TEMP_STORE] compile-time parameter and the [temp_store pragma]. </p> |
︙ | ︙ |
Changes to pages/uri.in.
︙ | ︙ | |||
108 109 110 111 112 113 114 | deleted when the database connection closes.)^ ^If the authority section is present, then the path is always an absolute pathname. ^If the authority section is omitted, then the path is an absolute pathname if it begins with the "/" character (ASCII code 0x2f) and is a relative pathname otherwise. ^(On windows, if the absolute path begins with "<b>/<i>X</i>:/</b>" where <b><i>X</i></b> is any single ASCII alphabetic character ("a" through "z" or "A" through "Z") then the "<b><i>X:</i></b>" | | | 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | deleted when the database connection closes.)^ ^If the authority section is present, then the path is always an absolute pathname. ^If the authority section is omitted, then the path is an absolute pathname if it begins with the "/" character (ASCII code 0x2f) and is a relative pathname otherwise. ^(On windows, if the absolute path begins with "<b>/<i>X</i>:/</b>" where <b><i>X</i></b> is any single ASCII alphabetic character ("a" through "z" or "A" through "Z") then the "<b><i>X:</i></b>" is understood to be the drive letter of the volume containing the file, not the toplevel directory.)^ <p>An ordinary filename can usually be converted into an equivalent URI by the steps shown below. The one exception is that a relative windows pathname with a drive letter cannot be converted directly into a URI; it must be changed into an absolute pathname first.</p> |
︙ | ︙ | |||
131 132 133 134 135 136 137 | <li>Prepend the "<tt>file:</tt>" scheme. </ol> <h2>3.2 Query String</h2> <p>^A URI filename can optionally be followed by a query string. ^The query string consists of text following the first "<tt>?</tt>" | | | 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | <li>Prepend the "<tt>file:</tt>" scheme. </ol> <h2>3.2 Query String</h2> <p>^A URI filename can optionally be followed by a query string. ^The query string consists of text following the first "<tt>?</tt>" character but excluding the optional fragment that begins with with "<tt>#</tt>". ^The query string is divided into key/value pairs. We usually refer to these key/value pairs as "query parameters". ^Key/value pairs are separated by a single "<tt>&</tt>" character. ^The key comes first and is separated from the value by a single "<tt>=</tt>" character. ^Both key and value may contain <b>%HH</b> escape sequences.</p> |
︙ | ︙ |
Changes to pages/vfs.in.
︙ | ︙ | |||
239 240 241 242 243 244 245 | This file implements a shim that can be used to simulate filesystem faults. This shim is used during testing to verify that SQLite responses sanely to hardware malfunctions or to other error conditions such as running out of filesystem space that are difficult to test on a real system. </ul> <p> | | | | 239 240 241 242 243 244 245 246 247 248 249 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 | This file implements a shim that can be used to simulate filesystem faults. This shim is used during testing to verify that SQLite responses sanely to hardware malfunctions or to other error conditions such as running out of filesystem space that are difficult to test on a real system. </ul> <p> There are other VFS implementations both in the core SQLite source code library and in available extensions. The list above is not meant to be exhaustive but merely representative of the kinds of features that can be realized using the VFS interface. </p> <h2>3.0 VFS Implementations</h2> <p> A new VFS is implemented by subclassing three objects: </p> <ul> <li>[sqlite3_vfs] <li>[sqlite3_io_methods] <li>[sqlite3_file] </ul> <p> An [sqlite3_vfs] object defines the name of the VFS and the core methods that implement the interface to the operating system, such as checking for existence of files, deleting files, creating files and opening and for reading and/or writing, converting filenames into their canonical form. The [sqlite3_vfs] object also contains methods for obtaining randomness from the operating system, for suspending a process (sleeping) and for finding the current date and time. </p> |
︙ | ︙ |
Changes to pages/vtab.in.
︙ | ︙ | |||
583 584 585 586 587 588 589 | [sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method. Hidden columns are counted when determining the column index. <p>The aConstraint[] array contains information about all constraints that apply to the virtual table. But some of the constraints might not be usable because of the way tables are ordered in a join. The xBestIndex method must therefore only consider constraints | | | 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 | [sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method. Hidden columns are counted when determining the column index. <p>The aConstraint[] array contains information about all constraints that apply to the virtual table. But some of the constraints might not be usable because of the way tables are ordered in a join. The xBestIndex method must therefore only consider constraints that have an aConstraint[].usable flag which is true. <p>In addition to WHERE clause constraints, the SQLite core also tells the xBestIndex method about the ORDER BY clause. (In an aggregate query, the SQLite core might put in GROUP BY clause information in place of the ORDER BY clause information, but this fact should not make any difference to the xBestIndex method.) If all terms of the ORDER BY clause are columns in the virtual table, |
︙ | ︙ |