Documentation Source Text

Check-in [3d96456389]
Login

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

Overview
Comment:Improvements to documentation associated with new features in version 3.26.0.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3d964563891a25a4c536a2201e5d31ac2f94e38616ef340b2864c49114498b4e
User & Date: drh 2018-11-13 19:55:59
Context
2018-11-15
16:53
Remove all hyperlinks to CVSTrac in preparation for shutting down all CVSTrac servers, which are now unmaintained for 10 years and are showing their age. check-in: 3c24a2c7f6 user: drh tags: trunk
2018-11-13
19:55
Improvements to documentation associated with new features in version 3.26.0. check-in: 3d96456389 user: drh tags: trunk
2018-11-12
15:24
Enhancements to the documentation regarding security and shadow tables. check-in: 7b6b7a791b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-12-00 (3.26.0)} {
<li>Optimization: when doing an UPDATE on a table with [indexes on expressions],
    do not update the expression indexes if they do not refer to any of the columns
    of the table being updated.
<li>Added the [SQLITE_DBCONFIG_DEFENSIVE] option which disables the ability to 
    create corrupt database files using ordinary SQL.
<li>Added support for read-only [shadow tables] when the [SQLITE_DBCONFIG_DEFENSIVE]
    option is enabled.
<li>Added the [PRAGMA legacy_alter_table] command, which if enabled causes the
    [ALTER TABLE] command to behave like older version of SQLite (prior to
    version 3.25.0) for compatibility.
<li>Added [PRAGMA table_xinfo] that works just like [PRAGMA table_info]
    except thta is also shows hidden columns in virtual tables.
<li>Added the [https://sqlite.org/src/file/ext/misc/explain.c|explain virtual table]
    as a run-time loadable extension.
<li>Add a limit counter to the query planner to prevent excessive
    [sqlite3_prepare()] times for certain pathological SQL inputs.
<li>Added support for the [sqlite3_normalized_sql()] interface, when compiling
    with SQLITE_ENABLE_NORMALIZE.
<li>Enhanced triggers so that they can use [table-valued functions] that







|










|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-12-00 (3.26.0)} {
<li>Optimization: When doing an [UPDATE] on a table with [indexes on expressions],
    do not update the expression indexes if they do not refer to any of the columns
    of the table being updated.
<li>Added the [SQLITE_DBCONFIG_DEFENSIVE] option which disables the ability to 
    create corrupt database files using ordinary SQL.
<li>Added support for read-only [shadow tables] when the [SQLITE_DBCONFIG_DEFENSIVE]
    option is enabled.
<li>Added the [PRAGMA legacy_alter_table] command, which if enabled causes the
    [ALTER TABLE] command to behave like older version of SQLite (prior to
    version 3.25.0) for compatibility.
<li>Added [PRAGMA table_xinfo] that works just like [PRAGMA table_info]
    except that it also shows [hidden columns] in virtual tables.
<li>Added the [https://sqlite.org/src/file/ext/misc/explain.c|explain virtual table]
    as a run-time loadable extension.
<li>Add a limit counter to the query planner to prevent excessive
    [sqlite3_prepare()] times for certain pathological SQL inputs.
<li>Added support for the [sqlite3_normalized_sql()] interface, when compiling
    with SQLITE_ENABLE_NORMALIZE.
<li>Enhanced triggers so that they can use [table-valued functions] that

Changes to pages/cli.in.

134
135
136
137
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
164
165
166

167
168
169
170

171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
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
225
226
227
228
229
230
231
232
233
234
235
236
237
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.
</p>

<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:

</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
.exit                  Exit this program

.expert                EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message

.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
.iotrace FILE          Enable I/O diagnostic logging to FILE
.limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT
.lint OPTIONS          Report potential schema issues. Options:
                         fkey-indexes     Find missing foreign key indexes
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         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
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?      Show the CREATE statements matching PATTERN
                          Add --indent for pretty-printing
.selftest ?--init?     Run tests defined in the SELFTEST table
.separator COL ?ROW?   Change the column separator and optionally the row
                         separator for both the output mode and .import

.session CMD ...       Create or control sessions
.sha3sum ?OPTIONS...?  Compute a SHA3 hash of database content
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats ?on|off?        Show stats or turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.testcase NAME         Begin redirecting output to 'testcase-out.txt'
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tclscript>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be







|
|
>




|
|
|
<
|
|
|
|
|
|
|
|
|
|
<
<
|
|
|
<
>
|
|
|
<
>
|
|
|
<
<
|
|
|
<
|
|
|
<
<
<
<
<
<
<
<
<
<
|
|
<
<
|
<
<
|
|
|
|
<
>
|
|
|
|
<
|
<
<
>
|
|
|
|
|
|
|
<
<
|
|
|
|
|
|
|
|
<







134
135
136
137
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

164
165
166
167

168
169
170
171


172
173
174

175
176
177










178
179


180


181
182
183
184

185
186
187
188
189

190


191
192
193
194
195
196
197
198


199
200
201
202
203
204
205
206

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

<tclscript>DisplayCode {
sqlite> (((.help)))
.archive ...             Manage SQL archives
.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
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?TABLE? ...        Render all database content as SQL


.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

.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off

.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes


.iotrace FILE            Enable I/O diagnostic logging to FILE
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.

.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode










.nullvalue STRING        Use STRING in place of NULL values
.once (-e|-x|FILE)       Output for the next SQL command only to FILE


.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE


.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.print STRING...         Print literal STRING
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program

.read FILE               Read input from FILE
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN

.selftest ?OPTIONS?      Run tests defined in the SELFTEST table


.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?on|off?          Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE


.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace FILE|off          Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set column widths for "column" mode

sqlite> 
}</tclscript>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be

Changes to pages/geopoly.in.

329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
   geopoly_svg(geopoly_regular(x,y,40,n),
        printf('style="fill:none;stroke:%s;stroke-width:2"',color))
   || printf(' &lt;text x="%d" y="%d" alignment-baseline="central" text-anchor="middle">%d&lt;/text>',x,y+6,n)
  FROM t1;
SELECT '&lt;/svg>';
</codeblock>

<tcl>hd_fragment regpoly geopoly_ccw</tcl>
<h2>The geopoly_ccw(J) Function</h2>

<p>The geopoly_ccw(J) function returns the polygon J with counter-clockwise (CCW) rotation.

<p>
[https://tools.ietf.org/html/rfc7946 | RFC-7946] requires that polygons use CCW rotation.
But the spec also observes that many legacy GeoJSON files do not following the spec and







|







329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
   geopoly_svg(geopoly_regular(x,y,40,n),
        printf('style="fill:none;stroke:%s;stroke-width:2"',color))
   || printf(' &lt;text x="%d" y="%d" alignment-baseline="central" text-anchor="middle">%d&lt;/text>',x,y+6,n)
  FROM t1;
SELECT '&lt;/svg>';
</codeblock>

<tcl>hd_fragment ccw geopoly_ccw</tcl>
<h2>The geopoly_ccw(J) Function</h2>

<p>The geopoly_ccw(J) function returns the polygon J with counter-clockwise (CCW) rotation.

<p>
[https://tools.ietf.org/html/rfc7946 | RFC-7946] requires that polygons use CCW rotation.
But the spec also observes that many legacy GeoJSON files do not following the spec and