Documentation Source Text

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-04-00 (3.28.0)} {
<li> New query optimizations:
<ol type="a">
<li> Enable the [LIKE optimization] for cases when the ESCAPE keyword
     is present and [PRAGMA case_sensitive_like] is on.
<li> In queries that are driven by a [partial index], avoid unnecessary
     tests of the constraint named in the WHERE clause of the partial
     index, since we know that constraint must always be true.
</ol>
<li> Enhancements to the [TCL Interface]:
<ol type="a">
<li> Added the -returntype option to the [function method].
<li> Added the new [bind_fallback method].
</ol>
<li> Enhancements to the [CLI]:
<ol type="a">
<li> Added support for [bound parameters] and the [.parameter command].
<li> Fix the [file I/O functions|readfile()] function so that it returns
     an empty BLOB rather than throwing an out-of-memory error when
     reading an empty file.
</ol>
<li> Added the [https://sqlite.org/src/file/ext/misc/fossildelta.c|fossildelta.c]
     extension that can create, apply, and deconstruct the 
 [https://fossil-scm.org/fossil/doc/trunk/www/delta_format.wiki|Fossil DVCS file delta format]
     that is used by the [RBU extension].
<li> The [fts3_tokenizer()] function is restrict to return only NULL
     values unless application-defined FTS3 tokenizers are enabled using
     the [sqlite3_db_config]([SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER])
     setting.
<li> Improvemented robustnessness to corrupt database files.
<li> Miscellaneous performance enhancements
}

chng {2019-02-25 (3.27.2)} {
<li> Fix a bug in the IN operator that was introduced by an 
     attempted optimization in version 3.27.0. Ticket
     [https://www.sqlite.org/src/info/df46dfb631f75694|df46dfb631f75694]
<li> Fix a bug causing a crash when a [window function] is misused.  Ticket
     [https://www.sqlite.org/src/info/4feb3159c6bc3f7e33959|4feb3159c6bc3f7e33959].

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending|Version 3.28.0
bd49a8271d|2019-02-25|Version 3.27.2
0eca3dd3d3|2019-02-08|Version 3.27.1
97744701c3|2019-02-07|Version 3.27.0
bf8c1b2b7a|2018-12-01|Version 3.26.0
89e099fbe5|2018-11-05|Version 3.25.3
fb90e7189a|2018-09-25|Version 3.25.2
2ac9003de4|2018-09-18|Version 3.25.1

  #hd_puts $body
  #hd_puts {</pre></blockquote>}
  return "<codeblock>$body</codeblock>"
}

DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20

because double-clicking starts the sqlite3.exe without command-line arguments,
no database file will have been specified, so SQLite will use a temporary
database that is deleted when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:

<tclscript>DisplayCode {
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tclscript>

<p>The example above causes the database file named "ex1.db" to be opened
and used.  The "ex1.db" file is created if it does not previously exist.
You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default temporary
storage, then save that database into a disk file using the ".save" command:

<tclscript>DisplayCode {
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite> 
}</tclscript>

<p>
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
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.
There were originally just a few dot commands, but over the years
many new features have accumulated so that today there over 60.
</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>

.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
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.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 ?OPTIONS?         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>

<li><p>
[https://sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs.c] &mdash;
This extension implements a new [VFS] that allows an SQLite database
to be appended to some other file, such as an executable.  This
extension is only needed if the --append option to the .archive
command is used.
</ol>

<tcl>hd_fragment param {.parameter command}</tcl>
<h1>SQL Parameters</h1>

<p>SQLite allows [bound parameters] to appear in an SQL statement anywhere
that a literal value is allowed.  The values for these parameters are set
using the [sqlite3_bind_int|sqlite3_bind_...()] family of APIs.

<p>Parameters can be either named or unnamed.  An unnamed parameter is a single
question mark ("?").  Named parameters are a "?" followed immediately by a number
(ex: "?15" or "?123") or one of the characters "$", ":", or "@" followed by an
alphanumeric name (ex: "$var1", ":xyz", "@bingo").

<p>This command-line shell leaves unnamed parameters unbound, meaning that they
will have a value of an SQL NULL, but named parameters might be assigned values.
If there exists a TEMP table named "$Parameters" with a schema like this:

<codeblock>
CREATE TEMP TABLE "$Parameters"(
  key TEXT PRIMARY KEY,
  value ANY
) WITHOUT ROWID;
</codeblock>

<p>And if there is a entry in that table where the key column exactly matches
the name of parameter (including the initial "?", "$", ":", or "@" character)
then the parameter is assigned the value of the value column.  If no entry exists,
the parameter defaults to NULL.

<p>The ".parameter" command exists to simplify managing this table.  The
".parameter init" command (often abbreviated as just ".param init") creates
the temp."$Parameters" table if it does not already exist.  The ".param list"
command shows all entries in the temp."$Parameters" table.  The ".param clear"
command drops the temp."$Parameters" table.  The ".param set KEY VALUE" and
".param unset KEY" commands create or delete entries from the 
temp."$Parameters" table.


<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>
SQLite [https://sqlite.org/src|source code]
is in the [public-domain] and is free to 
everyone to use for any purpose.

<h3>Latest Release</h3>
<a href="releaselog/3_28_0.html">Version 3.28.0</a> ([dateof:3.28.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<h3>Common Links</h3>
<tcl>common_links</tcl>
</div>