Documentation Source Text

Check-in [51202caa59]
Login

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

Overview
Comment:Add documentation for the ".recover" command to the shell tool page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 51202caa595a26a886239a821a3f21a2ccdf89d95ddc9fdeff917a026dd56727
User & Date: dan 2019-04-29 18:45:44
Context
2019-05-02
19:03
Use freopen() rather than assigning to "stdin" in althttpd.c. check-in: 229fda6dfb user: drh tags: trunk
2019-04-29
18:45
Add documentation for the ".recover" command to the shell tool page. check-in: 51202caa59 user: dan tags: trunk
16:21
Merge changes from the 3.28 branch. check-in: d220b2f30c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/cli.in.

   760    760   into other popular SQL database engines.  Like this:</p>
   761    761   
   762    762   
   763    763   <tclscript>DisplayCode {
   764    764   $ (((createdb ex2)))
   765    765   $ (((sqlite3 ex1 .dump | psql ex2)))
   766    766   }</tclscript>
          767  +
          768  +<tcl>hd_fragment recover {.recover dot-command}</tcl>
          769  +<h1>Recover Data From a Corrupted Database</h1>
          770  +
          771  +<p>Like the ".dump" command, ".recover" attempts to convert the entire 
          772  +contents of a database file to text. The difference is that instead of
          773  +reading data using the normal SQL database interface, ".recover"
          774  +attempts to reassemble the database based on data extracted directly from 
          775  +as many database pages as possible. If the database is corrupt, ".recover"
          776  +is usually able to recover data from all uncorrupted parts of the database,
          777  +whereas ".dump" stops when the first sign of corruption is encountered.
          778  +
          779  +<p>If the ".recover" command recovers one or more rows that it cannot 
          780  +attribute to any database table, the output script creates a "lost_and_found"
          781  +table to store the orphaned rows. The schema of the lost_and_found
          782  +table is as follows:
          783  +
          784  +<tclscript>DisplayCode {
          785  +  CREATE TABLE lost_and_found(
          786  +    rootpgno INTEGER,             -- root page of tree pgno is a part of
          787  +    pgno INTEGER,                 -- page number row was found on
          788  +    nfield INTEGER,               -- number of fields in row
          789  +    id INTEGER,                   -- value of rowid field, or NULL
          790  +    c0, c1, c2, c3...             -- columns for fields of row
          791  +);
          792  +}</tclscript>
          793  +
          794  +<p>The "lost_and_found" table contains one row for each orphaned row recovered
          795  +from the database. Additionally, there is one row for each recovered index
          796  +entry that cannot be attributed to any SQL index. This is because, in an
          797  +SQLite database, the same format is used to store SQL index entries and
          798  +WITHOUT ROWID table entries.
          799  +
          800  +<table striped=1>
          801  +  <tr><th style="width:15ex">Column<th>Contents
          802  +  <tr><td>rootpgno<td> Even though it may not be possible to attribute the 
          803  +      row to a specific database table, it may be part of a tree structure
          804  +      within the database file. In this case, the root page number of that
          805  +      tree structure is stored in this column. Or, if the page the row was
          806  +      found on is not part of a tree structure, this column stores a copy of
          807  +      the value in column "pgno" - the page number of the page the row was
          808  +      found on. In many, although not all, cases, all rows in the
          809  +      lost_and_found table with the same value in this column belong to the
          810  +      same table.
          811  +
          812  +  <tr><td>pgno<td> The page number of the page on which this row was found.
          813  +
          814  +  <tr><td>nfield<td> The number of fields in this row.
          815  +
          816  +  <tr><td>id<td> If the row comes from a WITHOUT ROWID table, this column 
          817  +      contains NULL. Otherwise, it contains the 64-bit integer rowid value for
          818  +      the row.
          819  +
          820  +  <tr><td>c0,&nbsp;c1,&nbspc2...<td> The values for each column of the row
          821  +      are stored in these columns. The ".recover" command creates the
          822  +      lost_and_found table with as many columns as required by the longest
          823  +      orphaned row.
          824  +</table>
          825  +
          826  +<p>If the recovered database schema already contains a table named
          827  +"lost_and_found", the ".recover" command uses the name "lost_and_found0". If
          828  +the name "lost_and_found0" is also already taken, "lost_and_found1", and so
          829  +on. The default name "lost_and_found" may be overridden by invoking ".recover"
          830  +with the --lost-and-found switch. For example, to have the output script call
          831  +the table "orphaned_rows":
          832  +
          833  +<tclscript>DisplayCode {
          834  +  sqlite> .recover --lost-and-found orphaned_rows
          835  +}</tclscript>
   767    836   
   768    837   <tcl>hd_fragment dotload</tcl>
   769    838   <h1>Loading Extensions</h1>
   770    839   
   771    840   <p>You can add new custom [application-defined SQL functions],
   772    841   [collating sequences], [virtual tables], and [VFSes] to the command-line
   773    842   shell at run-time using the ".load" command.  First, convert the
................................................................................
   788    857   as a second argument to the ".load" command.
   789    858   
   790    859   <p>Source code for several useful extensions can be found in the
   791    860   <a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
   792    861   subdirectory of the SQLite source tree.  You can use these extensions
   793    862   as-is, or as a basis for creating your own custom extensions to address
   794    863   your own particular needs.
          864  +
   795    865   
   796    866   <tcl>hd_fragment sha3sum {.sha3sum dot-command}</tcl>
   797    867   <h1>Cryptographic Hashes Of Database Content</h1>
   798    868   
   799    869   <p>The ".sha3sum" dot-command computes a
   800    870   [https://en.wikipedia.org/wiki/SHA-3|SHA3] hash of the <em>content</em>
   801    871   of the database.  To be clear, the hash is computed over the database content,