Documentation Source Text

Check-in [45f448bf75]
Login

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

Overview
Comment:Updates to the change log.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 45f448bf7510b0a422fd02222a5db3bf0496a8cd3d8a6b9f2ff5b24ba05a1880
User & Date: drh 2018-01-09 15:56:07
Context
2018-01-09
15:57
Remove stray characters from the change log. check-in: 852d1d5d3b user: drh tags: trunk
15:56
Updates to the change log. check-in: 45f448bf75 user: drh tags: trunk
01:25
Add the "docapp" makefile target for building the "docapp" application. check-in: d31880687e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

24
25
26
27
28
29
30












31
32
33
34
35
36
37
..
51
52
53
54
55
56
57


58
59
60
61







62
63
64
65
66
67
68
69
70
71
72
chng {2018-02-00 (3.22.0)} {
<li> The output of [sqlite3_trace_v2()] now shows each individual SQL statements
     run within a trigger.
<li> Add the ability to read from [WAL mode] databases even if the application 
     lacks write permission on the database and its containing directory, as long as
     the -shm and -wal files exist in that directory.
<li> Added the [rtreecheck()] scalar SQL function to the [R-Tree extension].












<li> Query planner enhancements:
<ol type='a'>
  <li> The optimization of using an index to quickly compute an
       aggregate min() or max() is extended to work with
       [indexes on expressions].
  <li> The decision of whether to implement a FROM-clause subquery
       as a co-routine or using query flattening now considers whether
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
str</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.


  <li> Added the ".expert" command
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.







</ol>
<li> Added the [https://sqlite.org/src/file/ext/misc/btreeinfo.c|sqlite_btreeinfo]
     eponymous virtual table for introspecting and estimating the sizes of
     the btrees in a database.
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.
<li> Provide the [sqlite_offset(X)] SQL function that returns
     the byte offset into the database file to the beginning of the record
     holding value X, when compiling with [-DSQLITE_ENABLE_OFFSET_SQL_FUNC].
}








>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
|



>
>
>
>
>
>
>

<
<
<







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
..
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83



84
85
86
87
88
89
90
chng {2018-02-00 (3.22.0)} {
<li> The output of [sqlite3_trace_v2()] now shows each individual SQL statements
     run within a trigger.
<li> Add the ability to read from [WAL mode] databases even if the application 
     lacks write permission on the database and its containing directory, as long as
     the -shm and -wal files exist in that directory.
<li> Added the [rtreecheck()] scalar SQL function to the [R-Tree extension].
<li> New extensions:
<ol type='a'>
  <li> The [Zipfile virtual table] can read and write a 
       [https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP Archive].
  <li> The [https://sqlite.org/src/file/ext/misc/btreeinfo.c|sqlite_btreeinfo]
       eponymous virtual table for introspecting and estimating the sizes of
       the btrees in a database.
  <li> The [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] is a
       [VFS shim] that allows an SQLite database to be appended to some other
       file.  This allows (for example) a database to be appended to an
       executable that then opens and reads the database.
</ol>
<li> Query planner enhancements:
<ol type='a'>
  <li> The optimization of using an index to quickly compute an
       aggregate min() or max() is extended to work with
       [indexes on expressions].
  <li> The decision of whether to implement a FROM-clause subquery
       as a co-routine or using query flattening now considers whether
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
str</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.
  <li> Added support for reading and writing [SQL Archive] files using
       the ".archive" or ".ar" command.
  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].
  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>



<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.
<li> Provide the [sqlite_offset(X)] SQL function that returns
     the byte offset into the database file to the beginning of the record
     holding value X, when compiling with [-DSQLITE_ENABLE_OFFSET_SQL_FUNC].
}

Changes to pages/cli.in.

757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
...
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
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
generates tests to verify that a subset of the tables are unchanged,
simply run ".selftest --init" then [DELETE] the selftest rows that
refer to tables that are not constant.  

<tcl>hd_fragment sqlar</tcl>
<h1>SQLAR Archive Support</h1>

<p>The shell tool ".ar" dot-command provides built-in support for the 
[https://sqlite.org/sqlar|SQLite ARchive format]. The interface is similar to
that of the "tar" command on unix systems. Each invocation of the ".ar"
command must specify a single command option. The following commands
are available:
................................................................................
<h2> SQLAR Update Command </h2>

<p> This command works the same way as the --create command, except that 
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.

<tcl>hd_fragment expert</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
the right SQL indexes. In this context "the right SQL indexes" means those







|







 







|







757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
...
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
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
generates tests to verify that a subset of the tables are unchanged,
simply run ".selftest --init" then [DELETE] the selftest rows that
refer to tables that are not constant.  

<tcl>hd_fragment sqlar {SQL Archive}</tcl>
<h1>SQLAR Archive Support</h1>

<p>The shell tool ".ar" dot-command provides built-in support for the 
[https://sqlite.org/sqlar|SQLite ARchive format]. The interface is similar to
that of the "tar" command on unix systems. Each invocation of the ".ar"
command must specify a single command option. The following commands
are available:
................................................................................
<h2> SQLAR Update Command </h2>

<p> This command works the same way as the --create command, except that 
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.

<tcl>hd_fragment expert {.expert command}</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
the right SQL indexes. In this context "the right SQL indexes" means those

Changes to pages/vfs.in.

172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
200
201
202
203
204
205
206









207
208
209
210
211
212
213

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims}</tcl>
<h2>VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.
................................................................................

<p>
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>









<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uses POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default







|







 







>
>
>
>
>
>
>
>
>







172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims} {VFS shim}</tcl>
<h2>VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.
................................................................................

<p>
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>
<li><p>
[http://www.sqlite.org/src/file/ext/misc/appendvfs.c | appendvfs.c] -
This VFS allows an SQLite database to be appended to the end of some
other file.  This can be used, for example, to append an SQLite database
onto the end of an executable such that the executable where it can
then be easily located by the executable when needed.  The
[command-line shell] will use this VFS if launched with the --append
option.

<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uses POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default

Changes to pages/zipfile.in.

1

2
3
4
5
6
7
8
...
131
132
133
134
135
136
137
138
139
140
141
<title>The SQLite Zipfile Module</title>

<table_of_contents>

<h1>Overview</h1>

<h1>Obtaining and Compiling Zipfile</h1>

<h1>Using Zipfile</h1>
................................................................................
       (which can take any unsigned 16-bit value, not just 0 or 8). The size of
       the uncompressed data (an integer) must be specified for column "sz".
</table>

<p> UPDATE statements are not supported by zipfile virtual tables. Nor is
    specifying an explicit value for the rowid field as part of an INSERT
    statement.





>







 







<
<
<
<
1
2
3
4
5
6
7
8
9
...
132
133
134
135
136
137
138




<title>The SQLite Zipfile Module</title>
<tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl>
<table_of_contents>

<h1>Overview</h1>

<h1>Obtaining and Compiling Zipfile</h1>

<h1>Using Zipfile</h1>
................................................................................
       (which can take any unsigned 16-bit value, not just 0 or 8). The size of
       the uncompressed data (an integer) must be specified for column "sz".
</table>

<p> UPDATE statements are not supported by zipfile virtual tables. Nor is
    specifying an explicit value for the rowid field as part of an INSERT
    statement.