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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>sqldiff.exe: Database Difference Utility</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
sqldiff.exe: Database Difference Utility
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#usage">1. Usage</a></div>
<div class="fancy-toc1"><a href="#how_it_works">2. How It Works</a></div>
<div class="fancy-toc1"><a href="#limitations">3. Limitations</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="usage"><span>1. </span>Usage</h1>
<p>
The <tt>sqldiff.exe</tt> binary is a command-line utility program that
displays content differences between SQLite databases. Example
usage:
</p><div class="codeblock"><pre>sqldiff [options] database1.sqlite database2.sqlite
</pre></div>
<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:
</p><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 <a href="sessionintro.html">session extension</a> to SQLite.</p></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
<a href="datatype3.html#collation">collating sequences</a> that are required by the schema.
</p></dd><dt><b>--primarykey</b></dt>
<dd><p>Use the schema-defined <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> instead of the <a href="lang_createtable.html#rowid">rowid</a> to
pair rows in the source and destination database. (See additional
explanation below.)</p></dd>
<dt><b>--schema</b></dt>
<dd><p>Show only column name and table 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</p></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 <a href="fts3.html">FTS3</a>, <a href="fts5.html">FTS5</a> and <a href="rtree.html">rtree</a> virtual tables.
<a href="#sqldiff_vtab">See below</a> for details.
</p></dd>
</dl>
<h1 id="how_it_works"><span>2. </span>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 <a href="lang_createtable.html#rowid">rowid</a>, or in the case of a <a href="withoutrowid.html">WITHOUT ROWID</a>
table if they have the same <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a>. 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><p>The --primarykey flag changes the pairing algorithm slightly so
that the schema-declared <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> is always used for pairing,
even on tables that have a <a href="lang_createtable.html#rowid">rowid</a>. 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 id="limitations"><span>3. </span>Limitations</h1>
<ol>
<li>
<p>The sqldiff.exe utility does not compute changesets for
either: rowid tables for which the rowid is inaccessible;
or tables which have no explicit primary key.
Given the --changeset option, sqldiff omits them from the comparison.
Examples of such tables are:
</p><div class="codeblock"><pre>CREATE TABLE NilChangeset (
-- inaccessible rowid due to hiding its aliases
"rowid" TEXT,
"oid" TEXT,
"_rowid_" TEXT
);
</pre></div>
and
<div class="codeblock"><pre>CREATE TABLE NilChangeset (
-- no explicit primary key
"authorId" TEXT,
"bookId" TEXT
);
</pre></div>
When sqldiff is made to compare only such tables, no error occurs.
However, the result may be unexpected.
For example, the effect of this invocation:
<div class="codeblock"><pre>sqldiff --changeset CHANGESET_OUT --table NilChangeset db1.sdb db2.sdb
</pre></div>
will be to produce an empty file named "CHANGESET_OUT". See <a href="sessionintro.html#limitations">session limitations</a> for details.
</li><li><p>
The sqldiff.exe utility does not (currently) display differences in
<a href="lang_createtrigger.html">TRIGGERs</a> or <a href="lang_createview.html">VIEWs</a>.
</p></li><li><p>
The sqldiff utility is not designed to support schema migrations
and is forgiving with respect to differing column definitions.
Normally, only the column names and their order are compared
for like-named tables before content comparison proceeds.
</p><p>However, the single-table comparison option, with "sqlite_schema"
named, can be used to show or detect detailed schema differences
between a pair of databases.
When doing this, the output should not be used directly to modify a database.
<a name="sqldiff_vtab"></a>
</p></li><li><p id="vtab">
By default, differences in the schema or content of virtual tables are
not reported on.
</p><p>However, if a <a href="vtab.html">virtual table</a> 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><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.
</p></li></ol>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/sqldiff.in?m=a0553bcc5b">2023-01-06 00:45:39</a> UTC </small></i></p>
|
