Documentation Source Text

<p>As with custom file formats, and unlike pure pile-of-file formats,
a wrapped pile-of-files format is not as easy to edit, since
usually the entire file must be rewritten in order to change any
component part.
</ol>

<p>The purpose of this document is to argue in favor of a fourth
new catagory of application file format: An SQLite database file.

<h2>SQLite As The Application File Format</h2>

<p>
Any application state that can be recorded in a pile-of-files can
also be recorded in an SQLite database with a simple key/value schema
like this:

<p>As with custom file formats, and unlike pure pile-of-file formats,
a wrapped pile-of-files format is not as easy to edit, since
usually the entire file must be rewritten in order to change any
component part.
</ol>

<p>The purpose of this document is to argue in favor of a fourth
new category of application file format: An SQLite database file.

<h2>SQLite As The Application File Format</h2>

<p>
Any application state that can be recorded in a pile-of-files can
also be recorded in an SQLite database with a simple key/value schema
like this:

<div class="rightsidebar">
<form method="GET" action="https://www.hwaci.com/cgi-bin/license-step1">
<input type="submit" value="Buy An SQLite License">
</form>
</div>

<h2>Obtaining An License To Use SQLite</h2>

<p>
Even though SQLite is in the public domain and does not require
a license, some users want to obtain a license anyway.  Some reasons
for obtaining a license include:
</p>

<div class="rightsidebar">
<form method="GET" action="https://www.hwaci.com/cgi-bin/license-step1">
<input type="submit" value="Buy An SQLite License">
</form>
</div>

<h2>Obtaining A License To Use SQLite</h2>

<p>
Even though SQLite is in the public domain and does not require
a license, some users want to obtain a license anyway.  Some reasons
for obtaining a license include:
</p>

<h1>Overview</h1>

<p>
The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns
information about which pages of the database files are used by which
tables and indexes in the schema.
The the DBSTAT virtual table is used to implement [sqlite3_analyzer.exe]
utility program, and to help compute the 
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>

<p>

<h1>Overview</h1>

<p>
The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns
information about which pages of the database files are used by which
tables and indexes in the schema.
The DBSTAT virtual table is used to implement [sqlite3_analyzer.exe]
utility program, and to help compute the 
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>

<p>

  incr ::cnt
}

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field.
} {
  <p>Short answer: A column declared [INTEGER PRIMARY KEY] will
  autoincrement.</p>

  <p>Longer answer:
  If you declare a column of a table to be [INTEGER PRIMARY KEY], then
  whenever you insert a NULL

  incr ::cnt
}

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field?
} {
  <p>Short answer: A column declared [INTEGER PRIMARY KEY] will
  autoincrement.</p>

  <p>Longer answer:
  If you declare a column of a table to be [INTEGER PRIMARY KEY], then
  whenever you insert a NULL

    Errors reported when content is changed are "DML errors" and errors
    reported when the schema is changed are "DDL errors".
    So, in other words, misconfigured foreign key constraints that require
    looking at both the child and parent are DML errors.
    ^The English language error message for foreign key DML errors is usually
    "foreign key mismatch" but can also be "no such table" if the parent
    table does not exist.
    ^(Foreign key DML errors are may be reported if:

  <ul>
    <li> The parent table does not exist, or
    <li> The parent key columns named in the foreign key constraint do
         not exist, or
    <li> The parent key columns named in the foreign key constraint are not
         the primary key of the parent table and are not subject to a unique

    Errors reported when content is changed are "DML errors" and errors
    reported when the schema is changed are "DDL errors".
    So, in other words, misconfigured foreign key constraints that require
    looking at both the child and parent are DML errors.
    ^The English language error message for foreign key DML errors is usually
    "foreign key mismatch" but can also be "no such table" if the parent
    table does not exist.
    ^(Foreign key DML errors are reported if:

  <ul>
    <li> The parent table does not exist, or
    <li> The parent key columns named in the foreign key constraint do
         not exist, or
    <li> The parent key columns named in the foreign key constraint are not
         the primary key of the parent table and are not subject to a unique

simply by cloning the official repository:</p>

<blockquote><pre>
fossil clone http://www.sqlite.org/src private-project.fossil
</pre></blockquote>

<p>This command both creates the new repository and populates it with
all the latest SQLite could.  You can the create a private branch as
described in section 3.4.</p>

<p>When the private repository is created by cloning, incorporating new
public SQLite releases becomes much easier too.  To pull in all of the
latest changes from the public SQLite repository, simply move into
the open check-out and do:</p>

<blockquote><pre>
fossil update
</pre></blockquote>

<p>Then continue to merge the changes in "trunk" with your "private"
changes as described in section 3.7.</p>

simply by cloning the official repository:</p>

<blockquote><pre>
fossil clone http://www.sqlite.org/src private-project.fossil
</pre></blockquote>

<p>This command both creates the new repository and populates it with
all the latest SQLite could.  You can then create a private branch as
described in section 3.4.</p>

<p>When the private repository is created by cloning, incorporating new
public SQLite releases becomes much easier too.  To pull in all of the
latest changes from the public SQLite repository, simply move into
the open check-out and do:</p>

<blockquote><pre>
fossil update
</pre></blockquote>

<p>Then continue to merge the changes in "trunk" with your "private"
changes as described in section 3.7.</p>

The total amount of data written is around three times the size of the
final database file. By contrast, an RBU Vacuum requires roughly the size
of the final database file in temporary disk space and writes a total of
twice that to disk.

<p>On the other hand, an RBU Vacuum uses more CPU than a regular SQLite
VACUUM - in one test as much as five times as much. For this reason, an RBU
Vaccum is often significantly slower than an SQLite VACUUM under the same
conditions.

<li><b>RBU runs in the background</b>

<p>An ongoing RBU operation (either an update or a vacuum) does not
interfere with read access to the database file.

................................................................................
<li><p>The target database may not contain [indexes on expressions].
<li><p>No other writes may occur on the target database while the
       RBU update is being applied.  A read-lock is held on the target
       database to prevent this.</p></li>
</ul>


<h2>Preparing An RBU Update File</h2>

<p>All changes to be applied by RBU are stored in a separate SQLite database
called the "RBU database".  The database that is to be modified is called
the "target database".

<p>For each table in the target database that will be modified by the update,
a corresponding table is created within the RBU database. The RBU database
................................................................................
<p>is represented by the data_t1 row created by:

<codeblock>
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x');
</codeblock>

<p>If RBU is used to update a large BLOB value within a target database, it
may be be more efficient to store a patch or delta that can be used to modify
the existing BLOB instead of an entirely new value within the RBU database. 
RBU allows deltas to be specified in two ways:

<ul>
  <li> In the "fossil delta" format - the format used for blob deltas by the 
  <a href=http://fossil-scm.org>Fossil source-code management system</a>, or

The total amount of data written is around three times the size of the
final database file. By contrast, an RBU Vacuum requires roughly the size
of the final database file in temporary disk space and writes a total of
twice that to disk.

<p>On the other hand, an RBU Vacuum uses more CPU than a regular SQLite
VACUUM - in one test as much as five times as much. For this reason, an RBU
Vacuum is often significantly slower than an SQLite VACUUM under the same
conditions.

<li><b>RBU runs in the background</b>

<p>An ongoing RBU operation (either an update or a vacuum) does not
interfere with read access to the database file.

................................................................................
<li><p>The target database may not contain [indexes on expressions].
<li><p>No other writes may occur on the target database while the
       RBU update is being applied.  A read-lock is held on the target
       database to prevent this.</p></li>
</ul>


<h2>Preparing an RBU Update File</h2>

<p>All changes to be applied by RBU are stored in a separate SQLite database
called the "RBU database".  The database that is to be modified is called
the "target database".

<p>For each table in the target database that will be modified by the update,
a corresponding table is created within the RBU database. The RBU database
................................................................................
<p>is represented by the data_t1 row created by:

<codeblock>
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x');
</codeblock>

<p>If RBU is used to update a large BLOB value within a target database, it
may be more efficient to store a patch or delta that can be used to modify
the existing BLOB instead of an entirely new value within the RBU database. 
RBU allows deltas to be specified in two ways:

<ul>
  <li> In the "fossil delta" format - the format used for blob deltas by the 
  <a href=http://fossil-scm.org>Fossil source-code management system</a>, or

<dd><p>Use the schema-defined [PRIMARY KEY] instead of the [rowid] to
       pair rows in the source and destination database.  (See additional
       explanation below.)</dd>
<dt><b>--schema</b></dt>
<dd><p>Show only differences in the schema not the table content</p></dd>
<dt><b>--summary</b></dt>
<dd><p>Show how many rows have changed on each table, but do not show
       the actual chagnes</dd>
<dt><b>--table TABLE</b></dt>
<dd><p>Show only the differences in content for TABLE, not for the
       entire database</p></dd>
<dt><b>--transaction</b></dt>
<dd><p>Wrap SQL output in a single large transaction</p></dd>
<dt><b>--vtab</b></dt>
<dd><p>Add support for handling [FTS3], [FTS5] and [rtree] virtual tables. 
................................................................................
[CREATE TRIGGER|TRIGGERs] or [CREATE VIEW|VIEWs].

<li><p id=vtab>
By default, differences in the schema or content of virtual tables are
not reported on. 

<p>However, if a [virtual table] implementation creates real tables (sometimes
refered to as "shadow" tables) within the database to store its data in, then
sqldiff.exe does calculate the difference between these. This can have
surprising effects if the resulting SQL script is then run on a database that
is not <i>exactly</i> the same as the source database. For several of SQLite's
bundled virtual tables (FTS3, FTS5, rtree and others), the surprising effects
may include corruption of the virtual table content.

<p> If the --vtab option is passed to sqldiff.exe, then it ignores all 
underlying shadow tables belonging to an FTS3, FTS5 or rtree virtual table
and instead includes the virtual table differences directly.

</ol>

<dd><p>Use the schema-defined [PRIMARY KEY] instead of the [rowid] to
       pair rows in the source and destination database.  (See additional
       explanation below.)</dd>
<dt><b>--schema</b></dt>
<dd><p>Show only differences in the schema not the table content</p></dd>
<dt><b>--summary</b></dt>
<dd><p>Show how many rows have changed on each table, but do not show
       the actual changes</dd>
<dt><b>--table TABLE</b></dt>
<dd><p>Show only the differences in content for TABLE, not for the
       entire database</p></dd>
<dt><b>--transaction</b></dt>
<dd><p>Wrap SQL output in a single large transaction</p></dd>
<dt><b>--vtab</b></dt>
<dd><p>Add support for handling [FTS3], [FTS5] and [rtree] virtual tables. 
................................................................................
[CREATE TRIGGER|TRIGGERs] or [CREATE VIEW|VIEWs].

<li><p id=vtab>
By default, differences in the schema or content of virtual tables are
not reported on. 

<p>However, if a [virtual table] implementation creates real tables (sometimes
referred to as "shadow" tables) within the database to store its data in, then
sqldiff.exe does calculate the difference between these. This can have
surprising effects if the resulting SQL script is then run on a database that
is not <i>exactly</i> the same as the source database. For several of SQLite's
bundled virtual tables (FTS3, FTS5, rtree and others), the surprising effects
may include corruption of the virtual table content.

<p> If the --vtab option is passed to sqldiff.exe, then it ignores all 
underlying shadow tables belonging to an FTS3, FTS5 or rtree virtual table
and instead includes the virtual table differences directly.

</ol>