Documentation Source Text

into other popular SQL database engines.  Like this:</p>


<tclscript>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tclscript>

<tcl>hd_fragment recover {.recover dot-command}</tcl>
<h1>Recover Data From a Corrupted Database</h1>

<p>Like the ".dump" command, ".recover" attempts to convert the entire 
contents of a database file to text. The difference is that instead of
reading data using the normal SQL database interface, ".recover"
attempts to reassemble the database based on data extracted directly from 
as many database pages as possible. If the database is corrupt, ".recover"
is usually able to recover data from all uncorrupted parts of the database,
whereas ".dump" stops when the first sign of corruption is encountered.

<p>If the ".recover" command recovers one or more rows that it cannot 
attribute to any database table, the output script creates a "lost_and_found"
table to store the orphaned rows. The schema of the lost_and_found
table is as follows:

<tclscript>DisplayCode {
  CREATE TABLE lost_and_found(
    rootpgno INTEGER,             -- root page of tree pgno is a part of
    pgno INTEGER,                 -- page number row was found on
    nfield INTEGER,               -- number of fields in row
    id INTEGER,                   -- value of rowid field, or NULL
    c0, c1, c2, c3...             -- columns for fields of row
);
}</tclscript>

<p>The "lost_and_found" table contains one row for each orphaned row recovered
from the database. Additionally, there is one row for each recovered index
entry that cannot be attributed to any SQL index. This is because, in an
SQLite database, the same format is used to store SQL index entries and
WITHOUT ROWID table entries.

<table striped=1>
  <tr><th style="width:15ex">Column<th>Contents
  <tr><td>rootpgno<td> Even though it may not be possible to attribute the 
      row to a specific database table, it may be part of a tree structure
      within the database file. In this case, the root page number of that
      tree structure is stored in this column. Or, if the page the row was
      found on is not part of a tree structure, this column stores a copy of
      the value in column "pgno" - the page number of the page the row was
      found on. In many, although not all, cases, all rows in the
      lost_and_found table with the same value in this column belong to the
      same table.

  <tr><td>pgno<td> The page number of the page on which this row was found.

  <tr><td>nfield<td> The number of fields in this row.

  <tr><td>id<td> If the row comes from a WITHOUT ROWID table, this column 
      contains NULL. Otherwise, it contains the 64-bit integer rowid value for
      the row.

  <tr><td>c0,&nbsp;c1,&nbspc2...<td> The values for each column of the row
      are stored in these columns. The ".recover" command creates the
      lost_and_found table with as many columns as required by the longest
      orphaned row.
</table>

<p>If the recovered database schema already contains a table named
"lost_and_found", the ".recover" command uses the name "lost_and_found0". If
the name "lost_and_found0" is also already taken, "lost_and_found1", and so
on. The default name "lost_and_found" may be overridden by invoking ".recover"
with the --lost-and-found switch. For example, to have the output script call
the table "orphaned_rows":

<tclscript>DisplayCode {
  sqlite> .recover --lost-and-found orphaned_rows
}</tclscript>

<tcl>hd_fragment dotload</tcl>
<h1>Loading Extensions</h1>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the

as a second argument to the ".load" command.

<p>Source code for several useful extensions can be found in the
<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
subdirectory of the SQLite source tree.  You can use these extensions
as-is, or as a basis for creating your own custom extensions to address
your own particular needs.


<tcl>hd_fragment sha3sum {.sha3sum dot-command}</tcl>
<h1>Cryptographic Hashes Of Database Content</h1>

<p>The ".sha3sum" dot-command computes a
[https://en.wikipedia.org/wiki/SHA-3|SHA3] hash of the <em>content</em>
of the database.  To be clear, the hash is computed over the database content,