Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Comment: | Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2 |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
96d1781d44a800a11e31c304ef68c697 |
User & Date: | larrybr 2022-01-20 21:38:08 |
2022-01-21
| ||
19:26 | Add requirements marks to newer parts of the date/time documentation. (check-in: deb6d14033 user: drh tags: trunk) | |
2022-01-20
| ||
23:31 | Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2 (check-in: 921013da18 user: drh tags: branch-3.37) | |
21:38 | Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2 (check-in: 96d1781d44 user: larrybr tags: trunk) | |
2022-01-18
| ||
19:52 | Take branch-3.37 CTE typo fix (check-in: 5ca5cda1eb user: larrybr tags: trunk) | |
Changes to pages/arch.in.
︙ | ︙ | |||
188 189 190 191 192 193 194 | a virtual machine. <p>The virtual machine itself is entirely contained in a single source file <file>vdbe.c</file>. The <file>vdbe.h</file> header file defines an interface between the virtual machine and the rest of the SQLite library and <file>vdbeInt.h</file> which defines structures and interfaces that | | | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 | a virtual machine. <p>The virtual machine itself is entirely contained in a single source file <file>vdbe.c</file>. The <file>vdbe.h</file> header file defines an interface between the virtual machine and the rest of the SQLite library and <file>vdbeInt.h</file> which defines structures and interfaces that are private to the virtual machine itself. Various other <b>vdbe*.c</b> files are helpers to the virtual machine. The <file>vdbeaux.c</file> file contains utilities used by the virtual machine and interface modules used by the rest of the library to construct VM programs. The <file>vdbeapi.c</file> file contains external interfaces to the virtual machine such as the [sqlite3_bind_int()] and [sqlite3_step()]. Individual values (strings, integer, floating point numbers, and BLOBs) are stored |
︙ | ︙ | |||
247 248 249 250 251 252 253 | The interface between page cache subsystem and the rest of SQLite is defined by the header file <file>pager.h</file>. </p> <h1>OS Interface</h1> <p> | | | | | | 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 | The interface between page cache subsystem and the rest of SQLite is defined by the header file <file>pager.h</file>. </p> <h1>OS Interface</h1> <p> In order to provide portability across operating systems, SQLite uses an abstract object called the [VFS]. Each VFS provides methods for opening, reading, writing, and closing files on disk, and for other OS-specific tasks such as finding the current time, or obtaining randomness to initialize the built-in pseudo-random number generator. SQLite currently provides VFSes for unix (in the <file>os_unix.c</file> file) and Windows (in the <file>os_win.c</file> file). </p> <h1>Utilities</h1> |
︙ | ︙ |
Changes to pages/cli.in.
︙ | ︙ | |||
136 137 138 139 140 141 142 | Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (".") are intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements. There were originally just a few dot commands, but over the years | | | 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (".") are intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements. There were originally just a few dot commands, but over the years many new features have accumulated so that today there are over 60. </p> <p> For a listing of the available dot commands, you can enter ".help" with no arguments. Or enter ".help TOPIC" for detailed information about TOPIC. The list of available dot-commands follows: </p> |
︙ | ︙ | |||
299 300 301 302 303 304 305 | sqlite> (((select * from tbl1;))) hello!, 10 goodbye, 20 sqlite> }</tclscript> <p>The next ".mode" command will reset the ".separator" back to its default. | | | 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 | sqlite> (((select * from tbl1;))) hello!, 10 goodbye, 20 sqlite> }</tclscript> <p>The next ".mode" command will reset the ".separator" back to its default. So you will need to repeat the ".separator" command whenever you change modes if you want to continue using a non-standard separator. <tcl> hd_fragment dotmodequote {.mode quote} </tcl> <p>In "quote" mode, the output is formatted as SQL literals. Strings are enclosed in single-quotes and internal single-quotes are escaped by doubling. |
︙ | ︙ | |||
368 369 370 371 372 373 374 | ------------ ------ hello! 10 goodbye 20 sqlite> }</tclscript> <p>A width of 0 means the column width is chosen automatically. | | | | | 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | ------------ ------ hello! 10 goodbye 20 sqlite> }</tclscript> <p>A width of 0 means the column width is chosen automatically. Unspecified column widths become zero. Hence, the command ".width" with no arguments resets all column widths to zero and hence causes all column widths to be determined automatically. <p>The "column" mode is a tabular output format. Other tabular output formats are "box", "markdown", and "table": <tclscript>DisplayCode { sqlite> (((.width))) sqlite> (((.mode markdown))) |
︙ | ︙ | |||
512 513 514 515 516 517 518 | the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second result column is the filename of the external file. There may be a third result column which will be either "'r/o'" or | | | | 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 | the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second result column is the filename of the external file. There may be a third result column which will be either "'r/o'" or "'r/w'" depending on whether the database file is read-only or read-write. And there might be a fourth result column showing the result of [sqlite3_txn_state()] for that database file. <tclscript>DisplayCode { sqlite> (((.databases))) }</tclscript> <tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl> |
︙ | ︙ | |||
565 566 567 568 569 570 571 | file rather than working as a stand-alone file. See the [https://www.sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs extension] for more information. <p>The --zip option causes the specified input file to be interpreted as a ZIP archive instead of as an SQLite database file. | | | 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 | file rather than working as a stand-alone file. See the [https://www.sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs extension] for more information. <p>The --zip option causes the specified input file to be interpreted as a ZIP archive instead of as an SQLite database file. <p>The --hexdb option causes the database content to be to be read from subsequent lines of input in a hex format, rather than from a separate file on disk. The "dbtotxt" command-line tool can be used to generate the appropriate text for a database. The --hexdb option is intended for use by the SQLite developers for testing purposes. We do not know of any use cases for this option outside of internal SQLite testing and development. <h1>Redirecting I/O</h1> |
︙ | ︙ | |||
698 699 700 701 702 703 704 | are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment editfunc {edit() SQL function}</tcl> <h2>The edit() SQL function</h2> | | | | > | | 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 | are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment editfunc {edit() SQL function}</tcl> <h2>The edit() SQL function</h2> <p>The CLI has another built-in SQL function named edit(). Edit() takes one or two arguments. The first argument is a value - often a large multi-line string to be edited. The second argument is the invocation for a text editor. (It may include options to affect the editor's behavior.) If the second argument is omitted, the VISUAL environment variable is used. The edit() function writes its first argument into a temporary file, invokes the editor on the temporary file, rereads the file back into memory after the editor is done, then returns the edited text. <p>The edit() function can be used to make changes to large text values. For example: |
︙ | ︙ | |||
1032 1033 1034 1035 1036 1037 1038 | }</tclscript> <tcl>hd_fragment dotload {.load command}</tcl> <h1>Loading Extensions</h1> <p>You can add new custom [application-defined SQL functions], [collating sequences], [virtual tables], and [VFSes] to the command-line | | | | 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 | }</tclscript> <tcl>hd_fragment dotload {.load command}</tcl> <h1>Loading Extensions</h1> <p>You can add new custom [application-defined SQL functions], [collating sequences], [virtual tables], and [VFSes] to the command-line shell at run-time using the ".load" command. First, build the extension as a DLL or shared library (as described in the [Run-Time Loadable Extensions] document) then type: <tclscript>DisplayCode { sqlite> .load /path/to/my_extension }</tclscript> <p>Note that SQLite automatically adds the appropriate extension suffix |
︙ | ︙ | |||
1110 1111 1112 1113 1114 1115 1116 | <p>If there is no selftest table, the ".selftest" command runs [PRAGMA integrity_check]. <p>The ".selftest --init" command creates the selftest table if it does not already exists, then appends entries that check the SHA3 hash of the content of all tables. Subsequent runs of ".selftest" will verify that the database has not been changed in any way. To | | | 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 | <p>If there is no selftest table, the ".selftest" command runs [PRAGMA integrity_check]. <p>The ".selftest --init" command creates the selftest table if it does not already exists, then appends entries that check the SHA3 hash of the content of all tables. Subsequent runs of ".selftest" will verify that the database has not been changed in any way. To generate tests to verify that a subset of the tables is unchanged, simply run ".selftest --init" then [DELETE] the selftest rows that refer to tables that are not constant. <tcl>hd_fragment sqlar {.archive command}</tcl> <h1>SQLite Archive Support</h1> <p>The ".archive" dot-command and the "-A" command-line option |
︙ | ︙ | |||
1301 1302 1303 1304 1305 1306 1307 | numeric st_mode integers from the stat() system call into human-readable strings after the fashion of the "ls -l" command. <li><p> [https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] — This extension adds the sqlar_compress() and sqlar_uncompress() functions that are needed to compress and uncompress file content | | | 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 | numeric st_mode integers from the stat() system call into human-readable strings after the fashion of the "ls -l" command. <li><p> [https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] — This extension adds the sqlar_compress() and sqlar_uncompress() functions that are needed to compress and uncompress file content as it is inserted and extracted from an SQLite Archive. <li><p> [zipfile|zipfile.c] — This extension implements the "zipfile(FILE)" table-valued function which is used to read ZIP archives. This extension is only needed when reading ZIP archives instead of SQLite archives. |
︙ | ︙ | |||
1451 1452 1453 1454 1455 1456 1457 | </table> <p>The functionality described in this section may be integrated into other applications or tools using the <a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert"> SQLite expert extension</a> code. | | | 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 | </table> <p>The functionality described in this section may be integrated into other applications or tools using the <a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert"> SQLite expert extension</a> code. <p>A database schema which incorporate SQL custom functions made available via the extension load mechanism may need special provision to work with the .expert feature. Because the feature uses additional connections to implement its functionality, those custom functions must be made available to those additional connections. This can be done by means of the extension load/usage options described at <a href="c3ref/auto_extension.html"> Automatically Load Statically Linked Extensions</a> |
︙ | ︙ | |||
1482 1483 1484 1485 1486 1487 1488 | connection, creating it if it does not already exist, by typing the ".conn" command followed by its number. Close a database connection by typing ".conn close N" where N is the connection number. <p> Though the underlying SQLite database connections are completely independent of one another, many of the CLI settings, such as the output format, are | | | 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 | connection, creating it if it does not already exist, by typing the ".conn" command followed by its number. Close a database connection by typing ".conn close N" where N is the connection number. <p> Though the underlying SQLite database connections are completely independent of one another, many of the CLI settings, such as the output format, are shared across all database connections. Thus, changing the [output mode] in one connection will change it in them all. On the other hand, some [dot-commands] such as [.open] only affect the current connection. <tcl>hd_fragment dotother</tcl> <h1>Other Dot Commands</h1> <p>There are many other dot-commands available in the command-line |
︙ | ︙ | |||
1525 1526 1527 1528 1529 1530 1531 | <tcl>hd_fragment endsh</tcl> <h1>Marking The End Of An SQL Statement</h1> <p> SQLite commands are normally terminated by a semicolon. In the CLI you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server | | | 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 | <tcl>hd_fragment endsh</tcl> <h1>Marking The End Of An SQL Statement</h1> <p> SQLite commands are normally terminated by a semicolon. In the CLI you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively, and are supported by the SQLite CLI for compatibility. These won't work in <b>sqlite3_exec()</b>, because the CLI translates these inputs into a semicolon before passing them down into the SQLite core.</p> <tcl>hd_fragment clopts {command-line options}</tcl> <h1>Command-line Options</h1> |
︙ | ︙ | |||
1627 1628 1629 1630 1631 1632 1633 | </ul> <p>Basically, any feature of the CLI that reads or writes from a file on disk other than the main database file is disabled. <h3>Bypassing --safe restrictions for specific commands</h3> | | | | | | | | | | > | | | | | > | 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 | </ul> <p>Basically, any feature of the CLI that reads or writes from a file on disk other than the main database file is disabled. <h3>Bypassing --safe restrictions for specific commands</h3> <p>If the "--nonce NONCE" option is also included on the command-line, for some large and arbitrary NONCE string, then the ".nonce NONCE" command (with the same large nonce string) will permit the next SQL statement or dot-command to bypass the --safe restrictions. <p>Suppose you want to run a suspicious script and the script requires one or two of the features that --safe normally disables. For example, suppose it needs to ATTACH one additional database. Or suppose the script needs to load a specific extension. This can be accomplished by preceding the (carefully audited) ATTACH statement or the ".load" command with an appropriate ".nonce" command and supplying the same nonce value using the "--nonce" command-line option. Those specific commands will then be allowed to execute normally, but all other unsafe commands will still be restricted. <p>The use of ".nonce" is dangerous in the sense that a mistake can allow a hostile script to damage your system. Therefore, use ".nonce" carefully, sparingly, and as a last resort when there are no other ways to get a script to run under --safe mode. <tcl>hd_fragment compiling</tcl> <h1>Compiling the sqlite3 program from sources</h1> <p> To compile the command-line shell on unix systems and on Windows with MinGW, the usual configure-make command works: |
︙ | ︙ |
Changes to pages/datatype3.in.
︙ | ︙ | |||
91 92 93 94 95 96 97 | <li><b>REAL</b> as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. <li><b>INTEGER</b> as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. </ul>)^ | | | 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | <li><b>REAL</b> as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. <li><b>INTEGER</b> as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. </ul>)^ <p>Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in [date and time functions].</p> <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> <h1>Type Affinity</h1> |
︙ | ︙ | |||
465 466 467 468 469 470 471 | <p>To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion does not lose essential information. Numeric values can always be converted into TEXT. TEXT values can be converted into numeric values if the text content is a well-formed integer or real literal, but not a hexadecimal integer literal. BLOB values are converted into TEXT values by simply interpreting | | | 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 | <p>To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion does not lose essential information. Numeric values can always be converted into TEXT. TEXT values can be converted into numeric values if the text content is a well-formed integer or real literal, but not a hexadecimal integer literal. BLOB values are converted into TEXT values by simply interpreting the binary BLOB content as a text string in the current database encoding. <p>^(Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:)^</p> <ul> <li><p>^If one operand has INTEGER, REAL or NUMERIC affinity |
︙ | ︙ | |||
487 488 489 490 491 492 493 | as is.</p> </ul> <p>^(The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons.)^ ^(Datatype conversions in comparisons of the | | | 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 | as is.</p> </ul> <p>^(The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons.)^ ^(Datatype conversions in comparisons of the form "x IN (SELECT y ...)" are handled as if the comparison were really "x=y".)^ ^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...".)^ ^In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. </p> |
︙ | ︙ | |||
558 559 560 561 562 563 564 | -- No affinity conversions occur. INTEGER values on the left are -- always less than TEXT values on the right. SELECT d < '40', d < '60', d < '600' FROM t1; 1|1|1 </pre> </blockquote>)^ | | | 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 | -- No affinity conversions occur. INTEGER values on the left are -- always less than TEXT values on the right. SELECT d < '40', d < '60', d < '600' FROM t1; 1|1|1 </pre> </blockquote>)^ <p>^All of the results in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a". <h1>Operators</h1> <p>^(Mathematical operators (+, -, *, /, %, <<, >>, &, and |) interpret both operands as if they were numbers. |
︙ | ︙ | |||
617 618 619 620 621 622 623 | {collating function} *collation *BINARY *NOCASE *RTRIM \ {BINARY collating function} \ {NOCASE collating function} \ {RTRIM collating function}</tcl> <h1>Collating Sequences</h1> <p>^When SQLite compares two strings, it uses a collating sequence or | | | 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 | {collating function} *collation *BINARY *NOCASE *RTRIM \ {BINARY collating function} \ {NOCASE collating function} \ {RTRIM collating function}</tcl> <h1>Collating Sequences</h1> <p>^When SQLite compares two strings, it uses a collating sequence or collating function (two terms for the same thing) to determine which string is greater or if the two strings are equal. ^SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.</p> <ul> <li>^(<b>BINARY</b> - Compares string data using memcmp(), regardless of text encoding.</li>)^ |
︙ | ︙ | |||
684 685 686 687 688 689 690 | </ol> <p> ^An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfix [COLLATE operator]. ^Thus, if a [COLLATE operator] is used | | | 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 | </ol> <p> ^An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfix [COLLATE operator]. ^Thus, if a [COLLATE operator] is used anywhere in a comparison expression, the collating function defined by that operator is used for string comparison regardless of what table columns might be a part of that expression. ^If two or more [COLLATE operator] subexpressions appear anywhere in a comparison, the left most explicit collating function is used regardless of how deeply the COLLATE operators are nested in the expression and regardless of how the expression is parenthesized. </p> |
︙ | ︙ |
Changes to pages/different.in.
︙ | ︙ | |||
74 75 76 77 78 79 80 | memory stick or emailed for sharing. <p> Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem | | | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | memory stick or emailed for sharing. <p> Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem altogether. This provides added performance, but at the cost of considerable setup and maintenance complexity. } feature onefile {Stable Cross-Platform Database File} { The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. Big-endian or little-endian, 32-bit or |
︙ | ︙ | |||
230 231 232 233 234 235 236 | above. SQLite also provides statements such as <a href="lang_replace.html">REPLACE</a> and the <a href="lang_conflict.html">ON CONFLICT</a> clause that allow for added control over the resolution of constraint conflicts. SQLite supports <a href="lang_attach.html">ATTACH</a> and <a href="lang_detach.html">DETACH</a> commands that allow multiple independent databases to be used together in the same query. | | | 230 231 232 233 234 235 236 237 238 239 240 241 242 | above. SQLite also provides statements such as <a href="lang_replace.html">REPLACE</a> and the <a href="lang_conflict.html">ON CONFLICT</a> clause that allow for added control over the resolution of constraint conflicts. SQLite supports <a href="lang_attach.html">ATTACH</a> and <a href="lang_detach.html">DETACH</a> commands that allow multiple independent databases to be used together in the same query. And SQLite defines APIs which allow the user to add new <a href="c3ref/create_function.html">SQL functions</a> and <a href="c3ref/create_collation.html">collating sequences</a>. } </tcl> |
Changes to pages/errlog.in.
︙ | ︙ | |||
94 95 96 97 98 99 100 | statement (using [sqlite3_step()]) that error is logged. </p> <li><p> When a schema change occurs that requires a prepared statement to be reparsed and reprepared, that event is logged with the error code SQLITE_SCHEMA. The reparse and reprepare is normally automatic (assuming that | | | 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | statement (using [sqlite3_step()]) that error is logged. </p> <li><p> When a schema change occurs that requires a prepared statement to be reparsed and reprepared, that event is logged with the error code SQLITE_SCHEMA. The reparse and reprepare is normally automatic (assuming that [sqlite3_prepare_v2()] has been used to prepare the statements originally, which is recommended) and so these logging events are normally the only way to know that reprepares are taking place.</p> <li><p> SQLITE_NOTICE messages are logged whenever a database has to be recovered because the previous writer crashed without completing its transaction. The error code is SQLITE_NOTICE_RECOVER_ROLLBACK when recovering a |
︙ | ︙ | |||
138 139 140 141 142 143 144 | This is useful in detecting application design issues when return codes are not consistently checked in the application code. </ul> <p>SQLite strives to keep error logger traffic low and only send messages to the error logger when there really is something wrong. Applications might further cull the error message traffic | | | | | 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 | This is useful in detecting application design issues when return codes are not consistently checked in the application code. </ul> <p>SQLite strives to keep error logger traffic low and only send messages to the error logger when there really is something wrong. Applications might further cull the error message traffic by deliberately ignoring certain classes of error messages that they do not care about. For example, an application that makes frequent database schema changes might want to ignore all SQLITE_SCHEMA errors.</p> <h1>Summary</h1> <p>The use of the error logger callback is highly recommended. The debugging information that the error logger provides has proven very useful in tracking down obscure problems that occur with applications after they get into the field. The error logger callback has also proven useful in catching occasional errors that the application misses because of inconsistent checking of API return codes. Developers are encouraged to implement an error logger callback early in the development cycle in order to spot unexpected behavior quickly, and to leave the error logger callback turned on through deployment. If the error logger never finds a problem, then no harm is done. But failure to set up an appropriate error logger might compromise diagnostic capabilities later on.</p> |
Changes to pages/expridx.in.
︙ | ︙ | |||
49 50 51 52 53 54 55 | SELECT * FROM account_change WHERE acct_no=$xyz ORDER BY abs(amt) DESC; </codeblock> <p> Both of the above example queries would work fine without the acctchng_magnitude index. | | | 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | SELECT * FROM account_change WHERE acct_no=$xyz ORDER BY abs(amt) DESC; </codeblock> <p> Both of the above example queries would work fine without the acctchng_magnitude index. The acctchng_magnitude index merely helps the queries to run faster, especially on databases where there are many entries in the table for each account. <h1>How To Use Indexes On Expressions</h1> <p> Use a [CREATE INDEX] statement to create a new index on one or more |
︙ | ︙ |
Changes to pages/famous.in.
︙ | ︙ | |||
88 89 90 91 92 93 94 | least four separate times requesting the US Export Control Number for SQLite. So presumably GE is using SQLite in something that they are exporting. But nobody (outside of GE) seems to know what that might be. } famous_user google http://www.google.com/ google.gif { uses SQLite in their | | | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | least four separate times requesting the US Export Control Number for SQLite. So presumably GE is using SQLite in something that they are exporting. But nobody (outside of GE) seems to know what that might be. } famous_user google http://www.google.com/ google.gif { uses SQLite in their [http://code.google.com/android/ | Android] cell-phone operating system, and in the [http://www.google.com/chrome | Chrome Web Browser]. } famous_user intuit http://www.intuit.com/ intuit.gif { [http://www.intuit.com/ | Intuit] apparently uses SQLite in [http://www.quickbooks.com/ | QuickBooks] and in [http://turbotax.intuit.com/ | TurboTax] to judge from some error |
︙ | ︙ |
Changes to pages/foreignkeys.in.
︙ | ︙ | |||
213 214 215 216 217 218 219 | </ul> <h1 id=fk_enable tags="foreign key constraints are enabled"> Enabling Foreign Key Support </h1> <p> ^In order to use foreign key constraints in SQLite, the library must | | | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | </ul> <h1 id=fk_enable tags="foreign key constraints are enabled"> Enabling Foreign Key Support </h1> <p> ^In order to use foreign key constraints in SQLite, the library must be compiled with neither [SQLITE_OMIT_FOREIGN_KEY] nor [SQLITE_OMIT_TRIGGER] defined. ^(If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to [version 3.6.19] ([dateof:3.6.19]) - foreign key definitions are parsed and may be queried using [PRAGMA foreign_key_list], but foreign key constraints are not enforced.)^ ^The [PRAGMA foreign_keys] command is a no-op in this configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key |
︙ | ︙ | |||
796 797 798 799 800 801 802 | <p> ^A [CREATE TABLE] command operates the same whether or not [foreign key constraints are enabled]. ^The parent key definitions of foreign key constraints are not checked when a table is created. ^There is nothing stopping the user from creating a foreign key definition that refers to a parent table that does not exist, or to parent key columns that | | > | 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 | <p> ^A [CREATE TABLE] command operates the same whether or not [foreign key constraints are enabled]. ^The parent key definitions of foreign key constraints are not checked when a table is created. ^There is nothing stopping the user from creating a foreign key definition that refers to a parent table that does not exist, or to parent key columns that do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint. <p> The [ALTER TABLE] command works differently in two respects when foreign key constraints are enabled: <ul> <li><p> |
︙ | ︙ |
Changes to pages/gencol.in.
1 2 3 4 5 6 7 8 9 10 11 12 13 | <title>Generated Columns</title> <tcl> hd_keywords {generated columns} {computed columns} {generated column} \ {Generated columns} </tcl> <table_of_contents> <h1>Introduction</h1> <p>Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <title>Generated Columns</title> <tcl> hd_keywords {generated columns} {computed columns} {generated column} \ {Generated columns} </tcl> <table_of_contents> <h1>Introduction</h1> <p>Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly written. The only way to change the value of a generated column is to modify the values of the other columns used to calculate the generated column. <h1>Syntax</h1> <p>Syntactically, generated columns are designated using a "GENERATED ALWAYS" [column-constraint]. For example: |
︙ | ︙ | |||
85 86 87 88 89 90 91 | ^The expression of a generated column can refer to any of the other declared columns in the table, including other generated columns, as long as the expression does not directly or indirectly refer back to itself. <li><p> ^Generated columns can occur anywhere in the table definition. ^Generated | | | 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | ^The expression of a generated column can refer to any of the other declared columns in the table, including other generated columns, as long as the expression does not directly or indirectly refer back to itself. <li><p> ^Generated columns can occur anywhere in the table definition. ^Generated columns can be interspersed among ordinary columns. ^It is not necessary to put generated columns at the end of the list of columns in the table definition, as is shown in the examples above. </ol> <h2>Limitations</h2> |
︙ | ︙ |
Changes to pages/inmemorydb.in.
︙ | ︙ | |||
53 54 55 56 57 58 59 | <tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl> <h2>In-memory Databases And Shared Cache</h2> <p>In-memory databases are allowed to use [shared cache] if they are opened using a [URI filename]. If the unadorned ":memory:" name is used to specify the in-memory database, then that database always has a private | | | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | <tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl> <h2>In-memory Databases And Shared Cache</h2> <p>In-memory databases are allowed to use [shared cache] if they are opened using a [URI filename]. If the unadorned ":memory:" name is used to specify the in-memory database, then that database always has a private cache and is only visible to the database connection that originally opened it. However, the same in-memory database can be opened by two or more database connections as follows: <blockquote><pre> rc = sqlite3_open("file::memory:?cache=shared", &db); </pre></blockquote> |
︙ | ︙ | |||
107 108 109 110 111 112 113 | rc = sqlite3_open("", &db); </pre></blockquote> <blockquote><pre> ATTACH DATABASE '' AS aux2; </pre></blockquote> | | | | | | | 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | rc = sqlite3_open("", &db); </pre></blockquote> <blockquote><pre> ATTACH DATABASE '' AS aux2; </pre></blockquote> <p>A different temporary file is created each time so that, just as with the special ":memory:" string, two database connections to temporary databases each have their own private database. Temporary databases are automatically deleted when the connection that created them closes.</p> <p>Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence there is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if the database becomes large or if SQLite comes under memory pressure.</p> <p>The previous paragraphs describe the behavior of temporary databases under the default SQLite configuration. An application can use the [temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to force temporary databases to behave as pure in-memory databases, if desired. </p> |
Changes to pages/intern-v-extern-blob.in.
︙ | ︙ | |||
12 13 14 15 16 17 18 | in a separate file and store just the corresponding filename in the database? </p> <p> To try to answer this, we ran 49 test cases with various BLOB sizes and SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the Ext4 filesystem on a fast SATA disk). | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | in a separate file and store just the corresponding filename in the database? </p> <p> To try to answer this, we ran 49 test cases with various BLOB sizes and SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the Ext4 filesystem on a fast SATA disk). For each test case, a database was created containing 100MB of BLOB content. The sizes of the BLOBs ranged from 10KB to 1MB. The number of BLOBs varied in order to keep the total BLOB content at about 100MB. (Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and so forth.) SQLite [version 3.7.8] ([dateof:3.7.8]) was used. </p> <blockquote><i> |
︙ | ︙ |
Changes to pages/isolation.in.
︙ | ︙ | |||
33 34 35 36 37 38 39 | it will remain off. Hence, unless the [read_uncommitted pragma] is used to change the default behavior, changes made by one database connection are invisible to readers on a different database connection sharing the same cache until the writer commits its transaction. </p> <p> | | | | < | | 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | it will remain off. Hence, unless the [read_uncommitted pragma] is used to change the default behavior, changes made by one database connection are invisible to readers on a different database connection sharing the same cache until the writer commits its transaction. </p> <p> If two database connections share the same cache and the reader has enabled the [read_uncommitted pragma], then the reader will be able to see changes made by the writer before the writer transaction commits. The combined use of [shared cache mode] and the [read_uncommitted pragma] is the only way that one database connection can see uncommitted changes on a different database connection. In all other circumstances, separate database connections are completely isolated from one another. </p> <p>Except in the case of [shared cache] database connections with [PRAGMA read_uncommitted] turned on, all transactions in SQLite show "serializable" isolation. SQLite implements serializable transactions by actually serializing the writes. There can only be a single writer at a time to an SQLite database. There can be multiple database connections open at the same time, and all of those database connections can write to the database file, but they have to take turns. SQLite uses locks to serialize the writes automatically; this is not something that the applications using SQLite need to worry about.</p> <h2>Isolation And Concurrency</h2> <p> SQLite implements isolation and concurrency control (and atomicity) using transient journal files that appear in the same directory as the database file. There are two major "journal modes". The older "rollback mode" corresponds to using the "DELETE", "PERSIST", or "TRUNCATE" options to the [journal_mode pragma]. In rollback mode, changes are written directly into the database file, while simultaneously a separate rollback journal file is constructed that is able to restore the database to its original state if the transaction rolls back. Rollback mode (specifically DELETE mode, meaning that the rollback journal |
︙ | ︙ | |||
84 85 86 87 88 89 90 | enabled by running "[PRAGMA journal_mode|PRAGMA journal_mode=WAL]". </p> <p> In rollback mode, SQLite implements isolation by locking the database file and preventing any reads by other database connections while each write transaction is underway. | | | | | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | enabled by running "[PRAGMA journal_mode|PRAGMA journal_mode=WAL]". </p> <p> In rollback mode, SQLite implements isolation by locking the database file and preventing any reads by other database connections while each write transaction is underway. Readers can be active at the beginning of a write, before any content is flushed to disk and while all changes are still held in the writer's private memory space. But before any changes are made to the database file on disk, all readers must be (temporarily) expelled in order to give the writer exclusive access to the database file. Hence, readers are prohibited from seeing incomplete transactions by virtue of being locked out of the database while the transaction is being written to disk. Only after the transaction is completely written and synced to disk and committed are the readers allowed back into the database. Hence readers never get a chance to see partially written changes. </p> <p> WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go |
︙ | ︙ | |||
118 119 120 121 122 123 124 | <p> An example: Suppose there are two database connections X and Y. X starts a read transaction using [BEGIN] followed by one or more [SELECT] statements. Then Y comes along and runs an [UPDATE] statement to modify the database. X can subsequently do a [SELECT] against the records that Y modified but X will see the older unmodified entries because Y's changes are all invisible to X while X is holding a read transaction. If X wants to see | | | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | <p> An example: Suppose there are two database connections X and Y. X starts a read transaction using [BEGIN] followed by one or more [SELECT] statements. Then Y comes along and runs an [UPDATE] statement to modify the database. X can subsequently do a [SELECT] against the records that Y modified but X will see the older unmodified entries because Y's changes are all invisible to X while X is holding a read transaction. If X wants to see the changes that Y made, then X must end its read transaction and start a new one (by running [COMMIT] followed by another [BEGIN].) </p> <p> Another example: X starts a read transaction using [BEGIN] and [SELECT], then Y makes a changes to the database using [UPDATE]. Then X tries to make a change to the database using [UPDATE]. The attempt by X to escalate its |
︙ | ︙ |
Changes to pages/json1.in.
︙ | ︙ | |||
235 236 237 238 239 240 241 | </tcl> <h2>VALUE arguments</h2> <p> For functions that accept "<i>value</i>" arguments (also shown as "<i>value1</i>" and "<i>value2</i>"), | | | | | 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | </tcl> <h2>VALUE arguments</h2> <p> For functions that accept "<i>value</i>" arguments (also shown as "<i>value1</i>" and "<i>value2</i>"), those arguments are usually understood to be literal strings that are quoted and become JSON string values in the result. Even if the input <i>value</i> strings look like well-formed JSON, they are still interpreted as literal strings in the result. <p> However, if a <i>value</i> argument comes directly from the result of another JSON function or from [the -> operator] (but not [the ->> operator]), then the argument is understood to be actual JSON and the complete JSON is inserted rather than a quoted string. <p> For example, in the following call to json_object(), the <i>value</i> argument looks like a well-formed JSON array. However, because it is just |
︙ | ︙ | |||
402 403 404 405 406 407 408 | {json_extract('{"a":"xyz"}', '$.a')} {'xyz'} \ {json_extract('{"a":NULL}', '$.a')} NULL </tcl> <p>There is a subtle incompatibility between the json_extract() function in SQLite and the json_extract() function in MySQL. The MySQL version of json_extract() always returns JSON. The SQLite version of | | | 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 | {json_extract('{"a":"xyz"}', '$.a')} {'xyz'} \ {json_extract('{"a":NULL}', '$.a')} NULL </tcl> <p>There is a subtle incompatibility between the json_extract() function in SQLite and the json_extract() function in MySQL. The MySQL version of json_extract() always returns JSON. The SQLite version of json_extract() only returns JSON if there are two or more PATH arguments (because the result is then a JSON array) or if the single PATH argument references an array or object. In SQLite, if json_extract() has only a single PATH argument and that PATH references a JSON null or a string or a numeric value, then json_extract() returns the corresponding SQL NULL, TEXT, INTEGER, or REAL value. <p>The difference between MySQL json_extract() and SQLite json_extract() |
︙ | ︙ | |||
655 656 657 658 659 660 661 | of X. The json_type(X,P) function returns the "type" of the element in X that is selected by path P. The "type" returned by json_type() is one of the following SQL text values: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'. If the path P in json_type(X,P) selects an element that does not exist in X, then this function returns NULL. | | | 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | of X. The json_type(X,P) function returns the "type" of the element in X that is selected by path P. The "type" returned by json_type() is one of the following SQL text values: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'. If the path P in json_type(X,P) selects an element that does not exist in X, then this function returns NULL. <p>The json_type() function throws an error if any of its arguments is not well-formed or is a BLOB. <p>Examples: <tcl> jexample \ {json_type('{"a":[2,3.5,true,false,null,"x"]}')} 'object' \ |
︙ | ︙ | |||
728 729 730 731 732 733 734 | <tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl> <tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl> <h2>The json_each() and json_tree() table-valued functions</h2> <p>The json_each(X) and json_tree(X) [table-valued functions] walk the JSON value provided as their first argument and return one row for each element. The json_each(X) function only walks the immediate children | | | 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 | <tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl> <tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl> <h2>The json_each() and json_tree() table-valued functions</h2> <p>The json_each(X) and json_tree(X) [table-valued functions] walk the JSON value provided as their first argument and return one row for each element. The json_each(X) function only walks the immediate children of the top-level array or object, or just the top-level element itself if the top-level element is a primitive value. The json_tree(X) function recursively walks through the JSON substructure starting with the top-level element. <p>The json_each(X,P) and json_tree(X,P) functions work just like their one-argument counterparts except that they treat the element |
︙ | ︙ | |||
793 794 795 796 797 798 799 | <p> The "fullkey" column is a text path that uniquely identifies the current row element within the original JSON string. The complete key to the true top-level element is returned even if an alternative starting point is provided by the "root" argument. <p> | | | 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 | <p> The "fullkey" column is a text path that uniquely identifies the current row element within the original JSON string. The complete key to the true top-level element is returned even if an alternative starting point is provided by the "root" argument. <p> The "path" column is the path to the array or object container that holds the current row, or the path to the current row in the case where the iteration starts on a primitive type and thus only provides a single row of output. <h3>Examples using json_each() and json_tree()</h3> <p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or |
︙ | ︙ |
Changes to pages/lang_aggfunc.in.
︙ | ︙ | |||
10 11 12 13 14 15 16 | } funcdef {avg(X)} {*avg {avg() aggregate function}} { ^The avg() function returns the average value of all non-NULL <i>X</i> within a group. ^String and BLOB values that do not look like numbers are interpreted as 0. | | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | } funcdef {avg(X)} {*avg {avg() aggregate function}} { ^The avg() function returns the average value of all non-NULL <i>X</i> within a group. ^String and BLOB values that do not look like numbers are interpreted as 0. ^The result of avg() is always a floating point value whenever there is at least one non-NULL input even if all inputs are integers. ^The result of avg() is NULL if and only if there are no non-NULL inputs. } funcdef {count(X) count(*)} {*count {count() aggregate function}} { ^The count(X) function returns a count of the number of times |
︙ | ︙ | |||
58 59 60 61 62 63 64 | funcdef {sum(X) total(X)} { *sumFunc *sum *total {sum() aggregate function} {total() aggregate function} } { ^The sum() and total() aggregate functions | | | | | 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | funcdef {sum(X) total(X)} { *sumFunc *sum *total {sum() aggregate function} {total() aggregate function} } { ^The sum() and total() aggregate functions return the sum of all non-NULL values in the group. ^If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.</p> <p>^The result of total() is always a floating point value. ^The result of sum() is an integer value if all non-NULL inputs are integers. ^If any input to sum() is neither an integer nor a NULL, then sum() returns a floating point value which is an approximation of the mathematical sum.</p> <p>^Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. ^Total() never throws an integer overflow. } </tcl> |
︙ | ︙ |
Changes to pages/lang_corefunc.in.
︙ | ︙ | |||
167 168 169 170 171 172 173 | 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> | | | 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | 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> <p>For security reasons, extension loading is disabled by default and must be enabled by a prior call to [sqlite3_enable_load_extension()].</p> } funcdef {lower(X)} {} { ^The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. ^The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII |
︙ | ︙ | |||
216 217 218 219 220 221 222 | funcdef {nullif(X,Y)} {} { ^The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. ^The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. ^If neither argument to nullif() defines a collating function | | | 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 | funcdef {nullif(X,Y)} {} { ^The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. ^The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. ^If neither argument to nullif() defines a collating function then the BINARY collating function is used. } funcdef {printf(FORMAT,...)} {} { ^The printf() SQL function is an alias for the [format() SQL function]. The format() SQL function was original named printf(). But the name was later changed to format() for compatibility with other database engines. The original printf() name is retained as an alias so as not to break any legacy code. |
︙ | ︙ |
Changes to pages/limits.in.
︙ | ︙ | |||
398 399 400 401 402 403 404 | approximately 2e+13 rows, and then only if there are no indices and if each row contains very little data. } limititem {Maximum Database Size} {} { <p> Every database consists of one or more "pages". Within a single database, | | | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 | approximately 2e+13 rows, and then only if there are no indices and if each row contains very little data. } limititem {Maximum Database Size} {} { <p> Every database consists of one or more "pages". Within a single database, every page is the same size, but different databases can have page sizes that are powers of two between 512 and 65536, inclusive. The maximum size of a database file is 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,000 gibibytes). <p> This particular upper bound is untested since the developers do not |
︙ | ︙ |
Changes to pages/loadext.in.
︙ | ︙ | |||
28 29 30 31 32 33 34 | need to supply SQLite with the name of the file containing the shared library or DLL and an entry point to initialize the extension. In C code, this information is supplied using the [sqlite3_load_extension()] API. See the documentation on that routine for additional information.</p> <p>Note that different operating systems use different filename | | | 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | need to supply SQLite with the name of the file containing the shared library or DLL and an entry point to initialize the extension. In C code, this information is supplied using the [sqlite3_load_extension()] API. See the documentation on that routine for additional information.</p> <p>Note that different operating systems use different filename suffixes for their shared libraries. Windows uses ".dll", Mac uses ".dylib", and most unixes other than mac use ".so". If you want to make your code portable, you can omit the suffix from the shared library filename and the appropriate suffix will be added automatically by the [sqlite3_load_extension()] interface.</p> <p>There is also an SQL function that can be used to load extensions: [load_extension(X,Y)]. It works just like the [sqlite3_load_extension()] |
︙ | ︙ | |||
328 329 330 331 332 333 334 | <h1>Implementation Details</h1> <p>SQLite implements run-time extension loading using the xDlOpen(), xDlError(), xDlSym(), and xDlClose() methods of the [sqlite3_vfs] object. These methods are implemented using the dlopen() library on unix (which explains why SQLite commonly | | | 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 | <h1>Implementation Details</h1> <p>SQLite implements run-time extension loading using the xDlOpen(), xDlError(), xDlSym(), and xDlClose() methods of the [sqlite3_vfs] object. These methods are implemented using the dlopen() library on unix (which explains why SQLite commonly needs to be linked against the "-ldl" library on unix systems) and using LoadLibrary() API on Windows. In a custom [VFS] for unusual systems, these methods can all be omitted, in which case the run-time extension loading mechanism will not work (though you will still be able to statically link the extension code, assuming the entry pointers are uniquely named). SQLite can be compiled with [SQLITE_OMIT_LOAD_EXTENSION] to omit the extension loading code from the build. |
Changes to pages/mmap.in.
︙ | ︙ | |||
14 15 16 17 18 19 20 | xFetch() and xUnfetch() methods on [sqlite3_io_methods].</p> <p>There are advantages and disadvantages to using memory-mapped I/O. Advantages include:</p> <ol> <li><p>Many operations, especially I/O intensive operations, can be | | | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | xFetch() and xUnfetch() methods on [sqlite3_io_methods].</p> <p>There are advantages and disadvantages to using memory-mapped I/O. Advantages include:</p> <ol> <li><p>Many operations, especially I/O intensive operations, can be faster since content need not be copied between kernel space and user space.</p> <li><p>The SQLite library may need less RAM since it shares pages with the operating-system page cache and does not always need its own copy of working pages.</p> </ol> |
︙ | ︙ |
Changes to pages/nulls.in.
︙ | ︙ | |||
220 221 222 223 224 225 226 | </tr> </table> <table border=0 align="right" cellpadding=0 cellspacing=0> <tr> <td valign="top" rowspan=5>Notes: </td> <td>1. </td> | | | 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 | </tr> </table> <table border=0 align="right" cellpadding=0 cellspacing=0> <tr> <td valign="top" rowspan=5>Notes: </td> <td>1. </td> <td>Older versions of firebird omit all NULLs from SELECT DISTINCT and from UNION.</td> </tr> <tr><td>2. </td> <td>Test data unavailable.</td> </tr> <tr><td>3. </td> <td>MySQL version 3.23.41 does not support UNION.</td> |
︙ | ︙ | |||
249 250 251 252 253 254 255 | above. </p> <pre> -- I have about decided that SQL's treatment of NULLs is capricious and cannot be -- deduced by logic. It must be discovered by experiment. To that end, I have -- prepared the following script to test how various SQL databases deal with NULL. | | | | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 | above. </p> <pre> -- I have about decided that SQL's treatment of NULLs is capricious and cannot be -- deduced by logic. It must be discovered by experiment. To that end, I have -- prepared the following script to test how various SQL databases deal with NULL. -- My aim is to use the information gathered from this script to make SQLite as -- much like other databases as possible. -- -- If you could please run this script in your database engine and mail the results -- to me at drh@hwaci.com, that will be a big help. Please be sure to identify the -- database engine you use for this test. Thanks. -- -- If you have to change anything to get this script to run with your database -- engine, please send your revised script together with your results. |
︙ | ︙ | |||
280 281 282 283 284 285 286 | select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1; select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1; select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1; select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1; select a+60, case b when c then 1 else 0 end from t1; select a+70, case c when b then 1 else 0 end from t1; | | | 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 | select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1; select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1; select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1; select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1; select a+60, case b when c then 1 else 0 end from t1; select a+70, case c when b then 1 else 0 end from t1; -- What happens when you multiply a NULL by zero? select a+80, b*0 from t1; select a+90, b*c from t1; -- What happens to NULL for other operators? select a+100, b+c from t1; -- Test the treatment of aggregate operators |
︙ | ︙ |
Changes to pages/partialindex.in.
︙ | ︙ | |||
95 96 97 98 99 100 101 | person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, -- other fields elided ); </codeblock>)^ | | | 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, -- other fields elided ); </codeblock>)^ <p>The team_id field cannot be unique because there are usually multiple people on the same team. One cannot make the combination of team_id and is_team_leader unique since there are usually multiple non-leaders on each team. ^(The solution to enforcing one leader per team is to create a unique index on team_id but restricted to those entries for which is_team_leader is true:</p> <codeblock> |
︙ | ︙ |
Changes to pages/quirks.in.
︙ | ︙ | |||
177 178 179 180 181 182 183 | <p> Usually (the exceptions are [INTEGER PRIMARY KEY] tables and [WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really the same as a UNIQUE constraint. Due to an historical oversight, the column values of such a PRIMARY KEY are allowed to be NULL. This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that | | | 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | <p> Usually (the exceptions are [INTEGER PRIMARY KEY] tables and [WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really the same as a UNIQUE constraint. Due to an historical oversight, the column values of such a PRIMARY KEY are allowed to be NULL. This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that the decision was made to support the buggy behavior moving forward. <p> The value of an [INTEGER PRIMARY KEY] column must always be a non-NULL integer. The PRIMARY KEY columns of a [WITHOUT ROWID] table are also required to be non-NULL. <h1>Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause</h1> |
︙ | ︙ | |||
304 305 306 307 308 309 310 | third parameter changed from 0 to 1. <h1>Keywords Can Often Be Used As Identifiers</h1> <p> The SQL language is rich in keywords. Most SQL implementations do not allow keywords to be used as identifiers | | | 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 | third parameter changed from 0 to 1. <h1>Keywords Can Often Be Used As Identifiers</h1> <p> The SQL language is rich in keywords. Most SQL implementations do not allow keywords to be used as identifiers (names of tables or columns) unless they are enclosed in double-quotes. But SQLite is more flexible. Many keywords can be used as identifiers without needing to be quoted, as long as those keywords are used in a context where it is clear that they are intended to be an identifier. <p> For example, the following statement is valid in SQLite: <codeblock> CREATE TABLE union(true INT, with BOOLEAN); |
︙ | ︙ |
Changes to pages/series.in.
︙ | ︙ | |||
12 13 14 15 16 17 18 | and a number of rows determined by the parameters START, END, and STEP. The first row of the table has a value of START. Subsequent rows increase by STEP up to END. <p>Omitted parameters take on default values. STEP defaults to 1. END defaults to 9223372036854775807. The START parameter is required as of version 3.37.0 ([dateof:3.37.0]) and later and an error will | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | and a number of rows determined by the parameters START, END, and STEP. The first row of the table has a value of START. Subsequent rows increase by STEP up to END. <p>Omitted parameters take on default values. STEP defaults to 1. END defaults to 9223372036854775807. The START parameter is required as of version 3.37.0 ([dateof:3.37.0]) and later and an error will be raised if START is omitted or has a self-referential or otherwise uncomputable value. Older versions used a default of 0 for START. The legacy behavior can be obtained from recent code by compiling with -DZERO_ARGUMENT_GENERATE_SERIES. <h2>Equivalent Recursive Common Table Expression</h2> <p>The generate_series table can be simulated using a |
︙ | ︙ |
Changes to pages/stricttables.in.
︙ | ︙ | |||
156 157 158 159 160 161 162 | <p>Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "[PRAGMA writable_schema=ON]" immediately after opening the database file, prior to doing anything else that requires knowing the schema. ^One of the features of PRAGMA writable_schema=ON is that it disables errors in the schema parser. This is intentional, because a big reason for | | | 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | <p>Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "[PRAGMA writable_schema=ON]" immediately after opening the database file, prior to doing anything else that requires knowing the schema. ^One of the features of PRAGMA writable_schema=ON is that it disables errors in the schema parser. This is intentional, because a big reason for having PRAGMA writable_schema=ON is to facilitate recovery of database files with corrupt schemas. ^(So with writable_schema=ON, when the schema parser reaches the STRICT keyword, it says to itself "I don't know what to do with this, but everything up to this point seems like a valid table definition so I'll just use what I have.")^ ^Hence, the STRICT keyword is effectively ignored. Because nothing else about the file format changes for STRICT tables, everything else will work normally. Of course, rigid type enforcement will not occur because the earlier |
︙ | ︙ |
Changes to pages/testing.in.
︙ | ︙ | |||
918 919 920 921 922 923 924 | extremely effective method for locating and preventing bugs. Because every single branch instruction in SQLite core code is covered by test cases, the developers can be confident that changes made in one part of the code do not have unintended consequences in other parts of the code. The many new features and performance improvements that have been added to SQLite in recent years would not have been possible without | | | 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 | extremely effective method for locating and preventing bugs. Because every single branch instruction in SQLite core code is covered by test cases, the developers can be confident that changes made in one part of the code do not have unintended consequences in other parts of the code. The many new features and performance improvements that have been added to SQLite in recent years would not have been possible without the availability of full-coverage testing.</p> <p>Maintaining 100% MC/DC is laborious and time-consuming. The level of effort needed to maintain full-coverage testing is probably not cost effective for a typical application. However, we think that full-coverage testing is justified for a [most widely deployed|very widely deployed] infrastructure library like SQLite, and especially for a database library which by its very |
︙ | ︙ |
Changes to pages/threadsafe.in.
︙ | ︙ | |||
35 36 37 38 39 40 41 | <p> The default mode is serialized. </p> <h1>Compile-time selection of threading mode</h1> <p> | | | 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | <p> The default mode is serialized. </p> <h1>Compile-time selection of threading mode</h1> <p> Use the [SQLITE_THREADSAFE] compile-time parameter to select the threading mode. If no [SQLITE_THREADSAFE] compile-time parameter is present, then serialized mode is used. This can be made explicit with [SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=1]. With [SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=0] the threading mode is single-thread. With |
︙ | ︙ |
Changes to pages/uri.in.
︙ | ︙ | |||
187 188 189 190 191 192 193 | <tcl>hd_fragment uriimmutable {"immutable" query parameter}</tcl> <dt><b>immutable=1</b></dt> <dd><p>^The immutable query parameter is a boolean that signals to SQLite that the underlying database file is held on read-only media and cannot be modified, even by another process with elevated privileges. ^SQLite always opens immutable database files read-only and it skips all file locking and change detection | | | 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | <tcl>hd_fragment uriimmutable {"immutable" query parameter}</tcl> <dt><b>immutable=1</b></dt> <dd><p>^The immutable query parameter is a boolean that signals to SQLite that the underlying database file is held on read-only media and cannot be modified, even by another process with elevated privileges. ^SQLite always opens immutable database files read-only and it skips all file locking and change detection on immutable database files. If this query parameter (or the [SQLITE_IOCAP_IMMUTABLE] bit in xDeviceCharacteristics) asserts that a database file is immutable and that file changes anyhow, then SQLite might return incorrect query results and/or [SQLITE_CORRUPT] errors. </dd> <tcl>hd_fragment urimode {"mode" query parameter}</tcl> |
︙ | ︙ |
Changes to pages/whentouse.in.
︙ | ︙ | |||
379 380 381 382 383 384 385 | database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine. <p>SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle | | | 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 | database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine. <p>SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency than many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will. </li> <li><p><b>Big data? → choose client/server</b></p> |
︙ | ︙ |
Changes to pages/withoutrowid.in.
︙ | ︙ | |||
233 234 235 236 237 238 239 | page size. WITHOUT ROWID tables will work (in the sense that they get the correct answer) for arbitrarily large rows - up to 2GB in size - but traditional rowid tables tend to work faster for large row sizes. This is because rowid tables are implemented as [B*-Trees] where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes. Storing content in | | | 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 | page size. WITHOUT ROWID tables will work (in the sense that they get the correct answer) for arbitrarily large rows - up to 2GB in size - but traditional rowid tables tend to work faster for large row sizes. This is because rowid tables are implemented as [B*-Trees] where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes. Storing content in intermediate nodes causes each intermediate node entry to take up more space on the page and thus reduces the fan-out, increasing the search cost. <p>The "sqlite3_analyzer.exe" utility program, available as source code in the SQLite source tree or as a precompiled binary on the [http://www.sqlite.org/download.html | SQLite Download page], can be used to measure the average sizes of table rows in an existing SQLite database.</p> |
︙ | ︙ |