Documentation Source Text

<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new interfaces [sqlite3_bind_pointer()], [sqlite3_result_pointer()], and
     [sqlite3_value_pointer()].  Update the [carray(PTR,N)] and 
     [https://www.sqlite.org/src/file/ext/misc/remember.c | remember(V,PTR)]
     extensions to require the use of [sqlite3_bind_pointer()] to set their
     pointer values.
<li> Added the [STMT virtual table] extension.
<li> Added the [COMPLETION extension] - designed to suggest
     tab-completions for interactive user interfaces.
<li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces
     with the extra "prepFlags" parameters.
<li> Provide the [SQLITE_PREPARE_PERSISTENT] flag [sqlite3_prepare_v3()] and
     use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the
     [R-Tree extension].
<li> Added the [PRAGMA secure_delete=FAST] command.  When secure_delete is
     set to FAST, old content is overwritten with zeros as long as that does
     not increase the amount of I/O.  Deleted content might still persist on
     the [free-page list] but will be purged from all b-tree pages.
<li> Enhancements to the [command-line shell]:
<ul>
<li> Add support for tab-completion using the [COMPLETION extension], for
     both readline and linenoise.
<li> Add the ".cd" command.
<li> Enhance the ".schema" command to show the schema of all attached
     databases.
<li> Enhance ".tables" so that it shows the schema names for all attached
     if the name is anything other than "main".
<li> The ".import" command ignores an initial UTF-8 BOM.

<title>The COMPLETION() Table-Valued Function</title>
<tcl>hd_keywords COMPLETION {COMPLETION extension} \
 {COMPLETION table-valued function}</tcl>
<fancy_format>

<h1>Overview</h1>

<p>The COMPLETION extension implements a [table-valued function] named
"completion" that can be used to suggest completions of partially entered
words on an interactive SQL input device.  The completion table can be
used to help implement tab-completion, for example.

<h1>Details</h1>

<p>The designed query interface is:

<codeblock>
SELECT DISTINCT candidate COLLATE nocase
  FROM completion($prefix, $wholeline)
 ORDER BY 1;
</codeblock>

<p>The query above will return suggestions for the whole input word that
begins with $prefix.  The $wholeline parameter is the complete line of
input text used for context.

<p>The completion table might return the same candidate more than once, and
it will return candidates in an arbitrary order.  The DISTINCT keyword and
the ORDER BY in the sample query above are added to make the answers unique
and in lexicographical order.

<h2>Example Usage</h2>

<p>The completion table is used to implement tab-completion in the
[command-line shell] in conjunction with either the readline or linenoise
input line editing packages for unix.  See the
[https://sqlite.org/src/file/src/shell.c.in] source file for example
code.  Search for "FROM completion" to find the relevant code sections.

<p>Because the completion table is built into the command-line shell in order
to provide for tab-completions, you can run test queries against the
completion table directly in the command-line shell.  Simply type a
query such as the example shown above, filling in appropriate values
for $prefix and $wholeline, and observe the output.

<h1>Limitations</h1>

<p>The completion table is designed for interactive use.  It will return
answers at a speed appropriate for human typing.  No effort is made to
be unusually efficient, so long as the response time is nearly instantaneous
in a user interface.

<p>As of this writing (2017-07-13), the completion virtual table only
looks for SQL keywords, and schema, table, and column names.  The
context contained in $wholeline is completely ignored.  Future enhancements
will try to return new completions taken from function and pragma names
and other sources, as well as consider more context.