Documentation Source Text

Check-in [4c3f0e6adb]
Login

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

Overview
Comment:Updates to the change log and other documentation pages.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4c3f0e6adb974b4f65af7c9e96c68574dcf6611dfe9b867bd3bd6bee4db787e0
User & Date: drh 2018-05-17 20:40:24.027
Context
2018-05-18
18:02
Update docs for the r-tree auxiliary columns. (check-in: daa211f9cd user: drh tags: trunk)
2018-05-17
20:40
Updates to the change log and other documentation pages. (check-in: 4c3f0e6adb user: drh tags: trunk)
2018-05-09
10:11
Merge fixes from the 3.23 branch. (check-in: cc22fb914a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
22
23
24
25
26
27
28


29
30
31
32
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
}

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]







>
>







>
>
>
|

<
>
>
>
|
<
<










>
>
>
>
>
>
>







22
23
24
25
26
27
28
29
30
31
32
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
}

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]
Changes to pages/cli.in.
143
144
145
146
147
148
149

150
151
152
153
154
155
156

157
158
159
160
161
162
163
</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







>







>







143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
</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
190
191
192
193
194
195
196

197
198
199
200
201
202
203
                         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







>







192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
                         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
Changes to pages/printf.in.
8
9
10
11
12
13
14

15
16

17
18
19
20
21
22
23
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?







>


>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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?
Changes to pages/sqlar.in.
192
193
194
195
196
197
198







199
200
201
202
203
204
205
206




207
208
209
210
211
212
213
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







>
>
>
>
>
>
>








>
>
>
>







192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
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