1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
.TH sqldiff 1 "2018-05-10"
.SH NAME
sqldiff - sqlite3 database difference utility
.SH SYNOPSIS
.B sqldiff
.RI [ options ]
.I database1.sqlite database2.sqlite
.SH DESCRIPTION
The
.B sqldiff
binary is a command-line utility program that displays the differences
between SQLite databases. The usual output is an SQL script that will
transform
.I database1.sqlite
(the "source" database) into
.I database2.sqlite
(the "destination" database).
The
.B sqldiff
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.
The
.B --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.
.SH OPTIONS
.TP
.BI \-\-changset\ FILE
Do not write changes to standard output. Instead, write a (binary)
changeset file into
.IR FILE .
The changeset can be interpreted using the sessions extension to
SQLite.
.TP
.BI \-\-lib\fR\ LIBRARY\fR,\ \-L\fR\ LIBRARY
Load the shared library or DLL file
.I LIBRARY
into SQLite prior to computing the differences. This can be used to
add application-defined collating sequences that are required by the
schema.
.TP
.B --primarykey
Use the schema-defined PRIMARY KEY instead of the rowid to pair rows
in the source and destination database. (See additional explanation
below.)
.TP
.B --schema
Show only differences in the schema not the table content
.TP
.B --summary
Show how many rows have changed on each table, but do not show the
actual changes
.TP
.BI --table\fR\ TABLE
Show only the differences in content for
.IR TABLE ,
not for the entire database
.TP
.B --transaction
Wrap SQL output in a single large transaction
.TP
.B --vtab
Add support for handling FTS3, FTS5 and rtree virtual tables. See
below for details.
.SH LIMITATIONS
The
.B sqldiff
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:
.nf
CREATE TABLE inaccessible_rowid(
"rowid" TEXT,
"oid" TEXT,
"_rowid_" TEXT
);
.fi
The
.B sqldiff
utility does not (currently) display differences in TRIGGERs or VIEWs.
By default, differences in the schema or content of virtual tables are
not reported on.
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 exactly 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.
If the
.B --vtab
option is passed to
.BR sqldiff ,
then it ignores all underlying shadow tables belonging to an FTS3,
FTS5 or rtree virtual table and instead includes the virtual table
differences directly.
.SH SEE ALSO
.BR sqlite3 (1).
|
