Documentation Source Text

     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.
<li> Added the "--newlines" option to the ".dump" command to cause U+000a and
     U+000d characters to be output literally rather than escaped using the
     [replace()] function.
</ul>
<li> Query planner enhancements:
<ul>
<li> When generating individual loops for each ORed term of an OR scan,
     move any constant WHERE expressions outside of the loop, as is 
................................................................................
     the selection toward the one that does not use the sorter.
<li> Evaluate WHERE clause constraints involving correlated subqueries
     last, in the hope that they never have be evaluated at all.
</ul>
<li> Add [SQLITE_STMTSTATUS_REPREPARE], [SQLITE_STMTSTATUS_RUN], 
     and [SQLITE_STMTSTATUS_MEMUSED] options for the
     [sqlite3_stmt_status()] interface.
<li> Provide eponymous virtual tables for
     [PRAGMA integrity_check], [PRAGMA quick_check], and
     [PRAGMA foreign_key_check].
<li> Add the -withoutnulls option to the [TCL interface eval method].
<li> Enhance the [sqlite3_analyzer.exe] utility program so that it shows
     the number of bytes of metadata on btree pages.
<li> The [SQLITE_DBCONFIG_ENABLE_QPSG] run-time option and the
     [SQLITE_ENABLE_QPSG] compile-time option enable the

     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.
<li> Added the "--newlines" option to the "[.dump]" command to cause U+000a and
     U+000d characters to be output literally rather than escaped using the
     [replace()] function.
</ul>
<li> Query planner enhancements:
<ul>
<li> When generating individual loops for each ORed term of an OR scan,
     move any constant WHERE expressions outside of the loop, as is 
................................................................................
     the selection toward the one that does not use the sorter.
<li> Evaluate WHERE clause constraints involving correlated subqueries
     last, in the hope that they never have be evaluated at all.
</ul>
<li> Add [SQLITE_STMTSTATUS_REPREPARE], [SQLITE_STMTSTATUS_RUN], 
     and [SQLITE_STMTSTATUS_MEMUSED] options for the
     [sqlite3_stmt_status()] interface.
<li> Provide [PRAGMA functions] for
     [PRAGMA integrity_check], [PRAGMA quick_check], and
     [PRAGMA foreign_key_check].
<li> Add the -withoutnulls option to the [TCL interface eval method].
<li> Enhance the [sqlite3_analyzer.exe] utility program so that it shows
     the number of bytes of metadata on btree pages.
<li> The [SQLITE_DBCONFIG_ENABLE_QPSG] run-time option and the
     [SQLITE_ENABLE_QPSG] compile-time option enable the

<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>


<p>For example, to see a list of the tables in the database, you
can enter ".tables".</p>


<tclscript>DisplayCode {
sqlite> (((.tables)))
tbl1
................................................................................
for all [attached] databases, not just the primary database.  And it arranges
its output into neat columns.

<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.


<p>The ".schema" command shows the complete schema for the database,
or for a single table if an optional tablename argument is provided:

<tclscript>DisplayCode {
sqlite> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
................................................................................
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>
<h1>CSV Import</h1>

<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
SQLite table into which the CSV data is to be inserted.

................................................................................
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.  That command only works as shown on Windows.  The
equivalent line on a Mac would be ".system open /work/dataout.csv".
On Linux and other unix systems you will need to enter something like
".system libreoffice /work/dataout.csv", substituting your preferred
CSV viewing program for "libreoffice".

<tcl>hd_fragment dump</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>

<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<tcl>hd_fragment dtables {.tables}</tcl>
<p>For example, to see a list of the tables in the database, you
can enter ".tables".</p>


<tclscript>DisplayCode {
sqlite> (((.tables)))
tbl1
................................................................................
for all [attached] databases, not just the primary database.  And it arranges
its output into neat columns.

<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.

<tcl>hd_fragment dschema {.schema}</tcl>
<p>The ".schema" command shows the complete schema for the database,
or for a single table if an optional tablename argument is provided:

<tclscript>DisplayCode {
sqlite> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
................................................................................
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} {.import}</tcl>
<h1>CSV Import</h1>

<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
SQLite table into which the CSV data is to be inserted.

................................................................................
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.  That command only works as shown on Windows.  The
equivalent line on a Mac would be ".system open /work/dataout.csv".
On Linux and other unix systems you will need to enter something like
".system libreoffice /work/dataout.csv", substituting your preferred
CSV viewing program for "libreoffice".

<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>

    identified and fixed early, reducing problems
    that might be caused by linking against a different version of SQLite.
    </p>
}

Pragma secure_delete {
    <p>^(<b>PRAGMA DB.secure_delete;
     <br>PRAGMA DB.secure_delete = </b><i>boolean</i></p>
    <p>Query or change the secure-delete setting.)^ ^When secure_delete is
    on, SQLite overwrites deleted content with zeros.  ^The default
    setting for secure_delete is determined by the [SQLITE_SECURE_DELETE]
    compile-time option and is normally off.  The off setting for
    secure_delete improves performance by reducing the
    amount of disk I/O.  Applications that wish to avoid leaving
    forensic traces after content is deleted or updated should enable the
    secure_delete pragma prior to performing the delete or update, or else
    run [VACUUM] after the delete or update.










    <p>
    ^When there are [ATTACH | attached databases] and no database
    is specified in the pragma, all databases have their secure-delete
    setting altered.
    ^The secure-delete setting for newly attached databases is the setting
    of the main database at the time the ATTACH command is evaluated.

    identified and fixed early, reducing problems
    that might be caused by linking against a different version of SQLite.
    </p>
}

Pragma secure_delete {
    <p>^(<b>PRAGMA DB.secure_delete;
     <br>PRAGMA DB.secure_delete = </b><i>boolean</i>|<b>FAST</b></p>
    <p>Query or change the secure-delete setting.)^ ^When secure_delete is
    on, SQLite overwrites deleted content with zeros.  ^The default
    setting for secure_delete is determined by the [SQLITE_SECURE_DELETE]
    compile-time option and is normally off.  The off setting for
    secure_delete improves performance by reducing the number of CPU cycles
    and the amount of disk I/O.  Applications that wish to avoid leaving
    forensic traces after content is deleted or updated should enable the
    secure_delete pragma prior to performing the delete or update, or else
    run [VACUUM] after the delete or update.

    <p>The "fast" setting for secure_delete (added circa [dateof:3.20.0])
    is an intermediate setting in between "on" and "off".
    When secure_delete is set to "fast",
    SQLite will overwrite deleted content with zeros only if doing so
    does not increase the amount of I/O.  In other words, the "fast"
    setting uses more CPU cycles but does not use more I/O.
    This has the effect of purging all old content from [B-tree|b-tree pages],
    but leaving forensic traces on [freelist|freelist pages].

    <p>
    ^When there are [ATTACH | attached databases] and no database
    is specified in the pragma, all databases have their secure-delete
    setting altered.
    ^The secure-delete setting for newly attached databases is the setting
    of the main database at the time the ATTACH command is evaluated.