Documentation Source Text

}

chng {2018-07-00 (3.24.0)} {
<li> Add support for PostgreSQL-style [UPSERT].
<li> Added new C-language APIs for discovering SQL keywords used by
     SQLite: [sqlite3_keyword_count()], [sqlite3_keyword_name()], and
     [sqlite3_keyword_check()].


<li> Enhance [ALTER TABLE] so that it recognizes "true" and "false" as
     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
<li> Improve the format of the [EXPLAIN QUERY PLAN] raw output, so that
     it gives better information about the query plan and about the
     relationships between the various components of the plan.
<li> The [CLI] automatically intercepts the raw [EXPLAIN QUERY PLAN] 
     output and reformats it into an ASCII-art graph.
<li> Enhance the query planner to allow the [OR optimization] to proceed
     even if the OR expression has also been converted into an IN
     expression.  Uses of the OR optimization are now also 
     [eqp-or-opt|more clearly shown] in the [EXPLAIN QUERY PLAN] output.



<p><b>Performance:</b>
<li> [UPDATE] avoids writing database pages that do not actually change.
     For example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-op if the
     value in column x is already 25.  Similarly, 
     when doing [UPDATE] on records that span multiple pages, only write
     the subset of pages that contain the changed value(s).
<li> Queries that use ORDER BY and LIMIT now try to avoid computing
     rows that cannot possibly come in under the LIMIT. This can greatly
     improve performance of ORDER BY LIMIT queries, especially when the
     LIMIT is small relative to the number of unrestricted output rows.







<li> Performance improvements in the LEMON-generated parser.
<p><b>Bug fixes:</b>
<li> For the right-hand table of a LEFT JOIN, compute the values
     of expressions directly rather than loading precomputed values
     out of an [expression index] as the expression index might
     not contain the correct value.  Ticket
     [https://sqlite.org/src/info/7fa8049685b50b5aeb0c2|7fa8049685b50b5aeb0c2]

}

chng {2018-07-00 (3.24.0)} {
<li> Add support for PostgreSQL-style [UPSERT].
<li> Added new C-language APIs for discovering SQL keywords used by
     SQLite: [sqlite3_keyword_count()], [sqlite3_keyword_name()], and
     [sqlite3_keyword_check()].
<li> Added new C-language APIs for dynamic strings based on the
     [sqlite3_str] object.
<li> Enhance [ALTER TABLE] so that it recognizes "true" and "false" as
     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
<li> Improve the format of the [EXPLAIN QUERY PLAN] raw output, so that
     it gives better information about the query plan and about the
     relationships between the various components of the plan.
<li> Added the [SQLITE_DBCONFIG_RESET_DATABASE] option to the
     [sqlite3_db_config()] API.
<p><b>[CLI] Enhancements:</b>
<li> Automatically intercepts the raw [EXPLAIN QUERY PLAN] 
     output and reformats it into an ASCII-art graph.
<li> Lines that begin with "#" and that are not in the middle of an
     SQL statement are interpreted as comments.
<li> Added the --append option to the ".backup" command.
<li> Added the ".dbconfig" command.
<p><b>Performance:</b>
<li> [UPDATE] avoids writing database pages that do not actually change.
     For example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-op if the
     value in column x is already 25.  Similarly, 
     when doing [UPDATE] on records that span multiple pages, only write
     the subset of pages that contain the changed value(s).
<li> Queries that use ORDER BY and LIMIT now try to avoid computing
     rows that cannot possibly come in under the LIMIT. This can greatly
     improve performance of ORDER BY LIMIT queries, especially when the
     LIMIT is small relative to the number of unrestricted output rows.
<li> The [OR optimization] is allowed to proceed
     even if the OR expression has also been converted into an IN
     expression.  Uses of the OR optimization are now also 
     [eqp-or-opt|more clearly shown] in the [EXPLAIN QUERY PLAN] output.
<li> The query planner is more aggressive about using
     [automatic indexes] for views and subqueries for which it is
     not possible to create a persistent index.
<li> Performance improvements in the LEMON-generated parser.
<p><b>Bug fixes:</b>
<li> For the right-hand table of a LEFT JOIN, compute the values
     of expressions directly rather than loading precomputed values
     out of an [expression index] as the expression index might
     not contain the correct value.  Ticket
     [https://sqlite.org/src/info/7fa8049685b50b5aeb0c2|7fa8049685b50b5aeb0c2]

</p>

<tclscript>DisplayCode {
sqlite> (((.help)))
.archive ...           Manage SQL archives: ".archive --help" for details
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE

.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases

.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.excel                 Display the output of next command in a spreadsheet
................................................................................
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once (-e|-x|FILE)     Output for the next SQL command only to FILE
                         or invoke system text editor (-e) or spreadsheet (-x)
                         on the output.
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file

.output ?FILE?         Send output to FILE or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE

</p>

<tclscript>DisplayCode {
sqlite> (((.help)))
.archive ...           Manage SQL archives: ".archive --help" for details
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
                         Add "--append" to open using appendvfs.
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dbconfig ?op? ?val?   List or change sqlite3_db_config() options
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.excel                 Display the output of next command in a spreadsheet
................................................................................
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once (-e|-x|FILE)     Output for the next SQL command only to FILE
                         or invoke system text editor (-e) or spreadsheet (-x)
                         on the output.
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file
                         Other options: --readonly --append --zip
.output ?FILE?         Send output to FILE or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE

accessible via the following interfaces:

<ul>
<li> [printf()] &rarr; an SQL function returning the formatted string
<li> [sqlite3_mprintf()] &rarr; Store the formatted string in memory obtained
     [sqlite3_malloc64()].
<li> [sqlite3_snprintf()] &rarr; Store the formatted string in a static buffer

<li> [sqlite3_vmprintf()] &rarr; Varargs version of sqlite3_mprintf()
<li> [sqlite3_vsnprintf()] &rarr; Varargs version of sqlite3_snprintf()

</ul>

<p>The same core string formatter is also used internally by SQLite.

<h2>Advantages</h2>

<p>Why does SQLite have its own private built-in printf() implementation?

accessible via the following interfaces:

<ul>
<li> [printf()] &rarr; an SQL function returning the formatted string
<li> [sqlite3_mprintf()] &rarr; Store the formatted string in memory obtained
     [sqlite3_malloc64()].
<li> [sqlite3_snprintf()] &rarr; Store the formatted string in a static buffer
<li> [sqlite3_str_appendf()] &rarr; Append formatted text to a dynamic string
<li> [sqlite3_vmprintf()] &rarr; Varargs version of sqlite3_mprintf()
<li> [sqlite3_vsnprintf()] &rarr; Varargs version of sqlite3_snprintf()
<li> [sqlite3_str_vappendf()] &rarr; Varargs version of sqlite3_str_appendf()
</ul>

<p>The same core string formatter is also used internally by SQLite.

<h2>Advantages</h2>

<p>Why does SQLite have its own private built-in printf() implementation?

To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:

<codeblock>
sqlite3 alltxt.sqlar -Ac *.txt
</codeblock>








<p>
For usage hints and a summary of all options, simply give the [CLI]
the -A option with no additional arguments:

<codeblock>
sqlite3 -A
</codeblock>





<h2>Other command-line tools</h2>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the 
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the

To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:

<codeblock>
sqlite3 alltxt.sqlar -Ac *.txt
</codeblock>

<p>
To add or update files in an existing SQLite Archive:

<codeblock>
sqlite3 example.sqlar -Au *.md
</codeblock>

<p>
For usage hints and a summary of all options, simply give the [CLI]
the -A option with no additional arguments:

<codeblock>
sqlite3 -A
</codeblock>

<p>
All of these commands work the same way if the filename argument is
is a ZIP Archive instead of an SQLite database.

<h2>Other command-line tools</h2>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the 
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the