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: |
ed98e4b3bf5f8381f8f86a6748680c59 |
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
Changes to pages/changes.in.
︙ | ︙ | |||
42 43 44 45 46 47 48 | 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. | | | | | | | 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 | 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> | | | 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 | 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". | | | 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 | 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; | | | | > > > > > > > > > | 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. |
︙ | ︙ |