Documentation Source Text

Check-in [86e3ea9245]
Login

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

Overview
Comment:Add documentation on the sqlite_dbpage virtual table.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:86e3ea924535c7096013a4490d53074a245458d6f4880e8297ca955182a9de9a
User & Date: drh 2018-09-28 20:51:13
Context
2018-09-28
20:59
Fix typos on th enew dbpage.html page. check-in: 966ccc894f user: drh tags: trunk
20:51
Add documentation on the sqlite_dbpage virtual table. check-in: 86e3ea9245 user: drh tags: trunk
17:08
Add documentation for the geopoly_regular() function. check-in: 7804034c38 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/compile.in.

   835    835     <li> [sqlite3_column_table_name16()] </li>
   836    836     <li> [sqlite3_column_origin_name()] </li>
   837    837     <li> [sqlite3_column_origin_name16()] </li>
   838    838     </ul>
   839    839   }
   840    840   
   841    841   COMPILE_OPTION {SQLITE_ENABLE_DBPAGE_VTAB} {
   842         -  This option enables the 
   843         -  [https://sqlite.org/src/file/src/dbpage.c|sqlite_dbpage virtual table].
          842  +  This option enables the [SQLITE_DBPAGE virtual table].
   844    843   }
   845    844   
   846    845   COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} {
   847    846     This option enables the [dbstat virtual table].
   848    847   }
   849    848   
   850    849   COMPILE_OPTION {SQLITE_ENABLE_DESERIALIZE} {

Added pages/dbpage.in.

            1  +<title>The SQLITE_DBPAGE Virtual Table</title>
            2  +<tcl>hd_keywords sqlite_dbpage {SQLITE_DBPAGE virtual table} \
            3  +        {the SQLITE_DBPAGE extension}</tcl>
            4  +<fancy_format>
            5  +
            6  +<h1>Overview</h1>
            7  +
            8  +<p>
            9  +The SQLITE_DBPAGE extension implements an [eponymous-only virtual table] that
           10  +provides direct access to the underlying database file by interacting directly
           11  +with the page.  SQLITE_DBPAGE is capable of both reading and writing any
           12  +page of the database.  Because interaction is through the pager layer, all
           13  +changes are transactional.
           14  +</p>
           15  +
           16  +<p>
           17  +<b>Warning:</b> writing to the SQLITE_DBPAGE virtual table can very easily
           18  +cause unrecoverably database corruption.  Do not allow untrusted components
           19  +to access the SQLITE_DBPAGE table.  Use appropriate care while using the
           20  +SQLITE_DBPAGE table.  Back up important data prior to experimenting with the
           21  +SQLITE_DBPAGE table.
           22  +
           23  +<p>
           24  +The SQLITE_DBPAGE extension is included in the [amalgamation] though 
           25  +it is disabled
           26  +by default.  Use the [SQLITE_ENABLE_DBPAGE_VTAB] compile-time option to enable
           27  +the SQLITE_DBPAGE extension.  The SQLITE_DBPAGE extension makes use of
           28  +unpublished internal interfaces and is not run-time loadable.  The only way
           29  +to add SQLITE_DBPAGE to an application is to compile it in using the
           30  +[SQLITE_ENABLE_DBPAGE_VTAB] compile-time option.
           31  +</p>
           32  +
           33  +<p>
           34  +The SQLITE_DBPAGE extension is enabled in default builds
           35  +of the [command-line shell].
           36  +
           37  +<h1>Usage</h1>
           38  +
           39  +<p>
           40  +The SQLITE_DBPAGE virtual table read/write table that provides direct
           41  +access to the underlying disk file on a page-by-page basis.  The
           42  +virtual table appears to have a schema like this:
           43  +
           44  +<codeblock>
           45  +CREATE TABLE sqlite_dbpage(
           46  +  pgno INTEGER PRIMARY KEY,
           47  +  data BLOB
           48  +);
           49  +</codeblock>
           50  +
           51  +<p>
           52  +An SQLite database file is divided into pages.
           53  +The first page is 1, the second page is 2, and so forth.
           54  +There is no page 0.
           55  +Every page is the same size.
           56  +The size of every page is a power of 2 between 512 and 65536.
           57  +See the [file format] documentation for further details.
           58  +
           59  +<p>
           60  +The SQLITE_DBPAGE table allows an application to view or replace the
           61  +raw binary content of each page of the database file.
           62  +No attempt is made to interpret the content of the page.
           63  +Content is returned byte-for-byte as it appears on disk.
           64  +
           65  +<p>
           66  +The SQLITE_DBPAGE table has one row for each page in the database file.
           67  +SQLITE_DBPAGE allows pages to be read or to be overwritten.
           68  +However the size of the database file cannot be changed.  It is not
           69  +possible to change the number of rows in the SQLITE_DBPAGE table by
           70  +running DELETE or INSERT operations against that table.
           71  +
           72  +<h2>Using SQLITE_DBPAGE On ATTACH-ed Databases</h2>
           73  +
           74  +<p>
           75  +The SQLITE_DBPAGE table schema shown above is incomplete.  There is
           76  +a third [hidden column] named "schema" that determines which
           77  +[ATTACH|ATTACH-ed database] should be read or written.  Because
           78  +the "schema" column is hidden, it can be used as a parameter when
           79  +SQLITE_DBPAGE is invoked as a [table-valued function].
           80  +
           81  +<p>
           82  +For example, suppose an additional database is attached to the 
           83  +database connection using a statement like this:
           84  +
           85  +<codeblock>
           86  +ATTACH 'auxdata1.db' AS aux1;
           87  +</codeblock>
           88  +
           89  +<p>
           90  +Then to read the first page of that database file, one merely runs:
           91  +
           92  +<codeblock>
           93  +SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=1;
           94  +</codeblock>
           95  +
           96  +<p>
           97  +If the "schema" is omitted, it defaults to the primary database
           98  +(usually called 'main', unless renamed using [SQLITE_DBCONFIG_MAINDBNAME]).
           99  +Hence, the following two queries are normally equivalent:
          100  +
          101  +<codeblock>
          102  +SELECT data FROM sqlite_dbpage('main') WHERE pgno=1;
          103  +SELECT data FROM sqlite_dbpage WHERE pgno=1;
          104  +</codeblock>
          105  +
          106  +<p>
          107  +The SQLITE_DBPAGE table can participate in a join just like any other
          108  +table.  Hence, to see the content of the first page to all connected
          109  +database files, one might run a statement like this:
          110  +
          111  +<codeblock>
          112  +SELECT dbpage.data, dblist.name
          113  +  FROM pragma_database_list AS dblist
          114  +  JOIN sqlite_dbpage(dblist.name) AS dbpage
          115  + WHERE dbpage.pgno=1;
          116  +</codeblock>

Changes to pages/vtablist.in.

    29     29   <tcl>
    30     30   unset -nocomplain vtab
    31     31   set vtab(sqlite_dbpage) {
    32     32     ifdef      SQLITE_ENABLE_DBPAGE_VTAB
    33     33     type       eponymous
    34     34     rw         1
    35     35     src        src/dbpage.c
    36         -  doc        {}
           36  +  doc        *sqlite_dbpage
    37     37     supported  1
    38     38     synopsis   {
    39     39       Key/value store for the raw database file content.  The key is the
    40     40       page number and the value is binary page content.
    41     41     }
    42     42   }
    43     43   set vtab(dbstat) {