Documentation Source Text

Artifact [d460162ffb]
Login

Artifact d460162ffb997c02a711bf8cbaa06e293d93495a3eac7743981052e99afb904e:


<title>sqldiff.exe: Database Difference Utility</title>
<tcl>hd_keywords sqldiff sqldiff.exe</tcl>

<table_of_contents>
<h1>Usage</h1>

<p>
The <tt>sqldiff.exe</tt> binary is a command-line utility program that
displays the differences between SQLite databases.  Example
usage:

<codeblock>
sqldiff &#91;options&#93; database1.sqlite database2.sqlite
</codeblock>

<p>
The usual output is an SQL script that will transform
database1.sqlite (the "source" database) into database2.sqlite
(the "destination" database).  This behavior can be
altered using command-line switches:

<dl>
<dt><b>--changeset FILE</b></dt>
<dd><p>Do not write changes to standard output.  Instead, write a (binary)
       changeset file into FILE.  The changeset can be interpreted using
       the sessions extension to SQLite.</dd>
<dt><b>--lib LIBRARY</b></dt>
<dt><b>-L LIBRARY</b></dt>
<dd><p>Load the shared library or DLL file LIBRARY into SQLite prior to
       computing the differences.  This can be used to add application-defined
       [collating sequences] that are required by the schema.
<dt><b>--primarykey</b></dt>
<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. 
       <a href=#vtab>See below</a> for details. 
</p></dd>
</dl>

<h1>How It Works</h1>

<p>The sqldiff.exe utility works by finding rows in the source and
destination that are logical "pairs".  The default behavior is to
treat two rows as pairs if they are in tables with the same name
and they have the same [rowid], or in the case of a [WITHOUT ROWID]
table if they have the same [PRIMARY KEY].  Any differences in the
content of paired rows are output as UPDATEs.  Rows in the source
database that could not be paired are output as DELETEs.  Rows in
the destination database that could not be paired are output as
INSERTs.

<p>The --primarykey flag changes the pairing algorithm slightly so
that the schema-declared [PRIMARY KEY] is always used for pairing,
even on tables that have a [rowid].  This is often a better choice
for finding differences, however it can lead to missed differences in
the case of rows that have one or more PRIMARY KEY columns set to
NULL.</p>

<h1>Limitations</h1>

<ol>
<li>
<p>The sqldiff.exe utility is unable to compute differences for
rowid tables for which the rowid is inaccessible.  An example of
a table with an inaccessible rowid is:

<codeblock>
CREATE TABLE inaccessible_rowid(
   "rowid" TEXT,
   "oid" TEXT,
   "_rowid_" TEXT
);
</codeblock>

<li><p>
The sqldiff.exe utility does not (currently) display differences in
[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>