Documentation Source Text

Check-in [ed98e4b3bf]
Login

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

Overview
Comment:Documentation for PRAGMA secure_delete=FAST. Better hyperlinks on the change log.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ed98e4b3bf5f8381f8f86a6748680c59c4b3b44f9d18eb5a599b16af6f8adeca
User & Date: drh 2017-07-13 22:24:35.654
Context
2017-07-13
22:39
Fix the release date for 3.20.0. (check-in: ef397e0f00 user: drh tags: trunk)
22:24
Documentation for PRAGMA secure_delete=FAST. Better hyperlinks on the change log. (check-in: ed98e4b3bf user: drh tags: trunk)
20:59
Add basic documentation for the COMPLETION extension. (check-in: 875a26d2d9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
     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 
     done for top-level loops.
<li> The query planner examines the values of bound parameters to help
     determine if a partial index is usable.
<li> When deciding between two plans with the same estimated cost, bias 
     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







|

|

|
|


















|







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
     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 
     done for top-level loops.
<li> The query planner examines the values of bound parameters to help
     determine if a partial index is usable.
<li> When deciding between two plans with the same estimated cost, bias 
     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
Changes to pages/cli.in.
468
469
470
471
472
473
474

475
476
477
478
479
480
481
<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







>







468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
<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
497
498
499
500
501
502
503

504
505
506
507
508
509
510
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 (







>







498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
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 (
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
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.








|







567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
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.

634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
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>







|







636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
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>
Changes to pages/pragma.in.
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077









1078
1079
1080
1081
1082
1083
1084
    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.







|




|
|



>
>
>
>
>
>
>
>
>







1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
    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.