Documentation Source Text

Check-in [0d81c00b9a]
Login

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

Overview
Comment:Add a change log for version 3.8.6. Update the home page for version 3.8.6.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0d81c00b9a9461ec512d6423d3a927657992bdfb
User & Date: drh 2014-07-24 15:36:08.395
Context
2014-07-24
15:44
Add a change-log not about schema indexes being preferred over transient indexes. (check-in: 3659d1f8cd user: drh tags: trunk)
15:36
Add a change log for version 3.8.6. Update the home page for version 3.8.6. (check-in: 0d81c00b9a user: drh tags: trunk)
14:39
Add documentation for sqlite3_index_info.estimatedRows, part of the virtual table interface. (check-in: 77f80a4186 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
13
14
15
16
17
18
19






























20
21
22
23
24
25
26
<tcl>
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}































chng {2014-06-04 (3.8.5)} {
<li>Added support for [partial sorting by index].
<li>Enhance the query planner so that it always prefers an index that uses a superset of
    WHERE clause terms relative to some other index.
<li>Improvements to the [automerge command] of [FTS4] to better control the index size
    for a full-text index that is subject to a large number of updates.







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







13
14
15
16
17
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
44
45
46
47
48
49
50
51
52
53
54
55
56
<tcl>
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2014-09-?? (3.8.6)} {
<li>Added the [http://www.sqlite.org/src/finfo?name=ext/misc/fileio.c|fileio.c]
    [loadable extension] source code file to the source tree.
<li>Increase the timeout in [WAL mode] before issuing an [SQLITE_PROTOCOL]
    error from 1 second to 10 seconds.
<li>Added the [likely(X)] SQL function.
<li>The query planner now uses [sqlite_stat4] information (created by [ANALYZE])
    to help determine if the [skip-scan optimization] is appropriate.
<li>The [unicode61] tokenizer is now included in [FTS4] by default.
<li>Added the [.fullschema] dot-command to the [command-line shell].
<li>Added the [file I/O functions|readfile(X) and writefile(X,Y)]
    extension functions to the [command-line shell]
<li>Added support for hexadecimal integer literals in the SQL parser.
    (Ex: 0x123abc)
<li>Increase the maximum value of [SQLITE_MAX_ATTACHED] from 62 to 125.
<li>Trigger automatic reprepares on all prepared statements when [ANALYZE] is
    run.
<p><b>Bug Fixes:</b>
<li>Fix the [sqlite3_stmt_busy()] interface so that it gives the correct answer
    for [ROLLBACK] statements that have been stepped but never reset.
<li>CSV output from the [command-line shell] now always uses CRNL for the
    row separator and avoids inserting CR in front of NLs contained in
    data.
<li>Fix a [column affinity] problem with the [IN operator].
    Ticket [http://www.sqlite.org/src/info/9a8b09f8e6|9a8b09f8e6].
<li>Fix the [ANALYZE] command so that it adds correct samples for
    [WITHOUT ROWID] tables in the [sqlite_stat4] table.
    Ticket [http://www.sqlite.org/src/info/b2fa5424e6fcb15|b2fa5424e6fcb15].
}

chng {2014-06-04 (3.8.5)} {
<li>Added support for [partial sorting by index].
<li>Enhance the query planner so that it always prefers an index that uses a superset of
    WHERE clause terms relative to some other index.
<li>Improvements to the [automerge command] of [FTS4] to better control the index size
    for a full-text index that is subject to a large number of updates.
Changes to pages/cli.in.
588
589
590
591
592
593
594













595
596
597
598
599
600
601
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tcl>DisplayCode {
sqlite> (((.databases)))
}</tcl>















<tcl>hd_fragment csv {CSV import}</tcl>
<h3>CSV Import</h3>

<p>Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.  The ".import" command takes two arguments which are the
name of the disk file from which CSV data is to be read and the name of the







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







588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tcl>DisplayCode {
sqlite> (((.databases)))
}</tcl>

<tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl>
<p>The ".fullschema" dot-command works like the ".schema" command in
that it displays the entire database schema.  But ".fullschema" also
includes dumps of the statistics tables "sqlite_stat1", "sqlite_stat3",
and "sqlite_stat4", if they exist.  The ".fullschema" command normally
provides all of the information needed to exactly recreate a query
plan for a specific query.  When reporting suspected problems with
the SQLite query planner to the SQLite development team, developers
are requested to provide the complete ".fullschema" output as part
of the trouble report.  Note that the sqlite_stat3 and sqlite_stat4
tables contain samples of index entries and so might contain sensitive
data, so do not send the ".fullschema" output of a proprietary database
over a public channel.</p>

<tcl>hd_fragment csv {CSV import}</tcl>
<h3>CSV Import</h3>

<p>Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.  The ".import" command takes two arguments which are the
name of the disk file from which CSV data is to be read and the name of the
Changes to pages/fileformat2.in.
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for
each index for which statistics are available, however size limits are
not hard bounds.
The meanings of the columns in the sqlite_stat4 table are as follows:

<center>
<table border="0" width="100%" cellpadding="10">
<tr><td valign="top" align="right">tbl:</td>
    <td>The sqlite_stat4.tbl column holds name of the table that owns







|







1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for
each index for which statistics are available, however these limits are
not hard bounds.
The meanings of the columns in the sqlite_stat4 table are as follows:

<center>
<table border="0" width="100%" cellpadding="10">
<tr><td valign="top" align="right">tbl:</td>
    <td>The sqlite_stat4.tbl column holds name of the table that owns
Changes to pages/index.in.
91
92
93
94
95
96
97
98
99

100
101
102
103
104
105
106

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_5.html">Version 3.8.5</a>
of SQLite is recommended for all new development.

Upgrading from all other versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>







|

>







91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_6.html">Version 3.8.6</a>
of SQLite is recommended for all new development.
Upgrading from version 3.8.5 is optional.
Upgrading from all other versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
Changes to pages/lang.in.
2359
2360
2361
2362
2363
2364
2365












2366
2367
2368
2369
2370
2371
2372
  ^The likelihood(X) function is a no-op that the code generator
  optimizes away so that it consumes no CPU cycles during run-time
  (that is, during calls to [sqlite3_step()]).
  ^The purpose of the likelihood(X,Y) function is to provide a hint
  to the query planner that the argument X is a boolean that is
  true with a probability of approximately Y.
  ^(The [unlikely(X)] function is short-hand for likelihood(X,0.0625).)^












}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point name is used.
  ^The load_extension() function raises an exception if the extension fails to







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







2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
  ^The likelihood(X) function is a no-op that the code generator
  optimizes away so that it consumes no CPU cycles during run-time
  (that is, during calls to [sqlite3_step()]).
  ^The purpose of the likelihood(X,Y) function is to provide a hint
  to the query planner that the argument X is a boolean that is
  true with a probability of approximately Y.
  ^(The [unlikely(X)] function is short-hand for likelihood(X,0.0625).)^
  ^(The [likely(X)] function is short-hand for likelihood(X,0.9375).)^
}

funcdef {likely(X)} {} {
  ^The likely(X) function returns the argument X unchanged.
  ^The likely(X) function is a no-op that the code generator
  optimizes away so that it consumes no CPU cycles at
  run-time (that is, during calls to [sqlite3_step()]).
  ^The purpose of the likely(X) function is to provide a hint
  to the query planner that the argument X is a boolean value
  that is usually true. ^(The likely(X) function is equivalent
  to [likelihood](X,0.9375).)^ See also: [unlikely(X)].
}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point name is used.
  ^The load_extension() function raises an exception if the extension fails to
Changes to pages/limits.in.
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 62.</p>

<p>
The maximum number of attached databases can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_ATTACHED],size) interface.
</p>
}








<
<
|







352
353
354
355
356
357
358


359
360
361
362
363
364
365
366
limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.


The maximum number of attached databases cannot be increased above 125.</p>

<p>
The maximum number of attached databases can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_ATTACHED],size) interface.
</p>
}