Debian Codegraph @

www/sqldiff.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>sqldiff.exe: Database Difference Utility</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 sqldiff.exe: Database Difference Utility
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#usage">1. Usage</a></div>
126 <div class="fancy-toc1"><a href="#how_it_works">2. How It Works</a></div>
127 <div class="fancy-toc1"><a href="#limitations">3. Limitations</a></div>
128 </div>
129 </details>
130 </div>
131
132
133
134
135 <h1 id="usage"><span>1. </span>Usage</h1>
136
137 <p>
138 The <tt>sqldiff.exe</tt> binary is a command-line utility program that
139 displays content differences between SQLite databases. Example
140 usage:
141
142 </p><div class="codeblock"><pre>sqldiff &#91;options&#93; database1.sqlite database2.sqlite
143 </pre></div>
144
145 <p>
146 The usual output is an SQL script that will transform
147 database1.sqlite (the "source" database) into database2.sqlite
148 (the "destination" database). This behavior can be
149 altered using command-line switches:
150
151 </p><dl>
152 <dt><b>--changeset FILE</b></dt>
153 <dd><p>Do not write changes to standard output. Instead, write a (binary)
154 changeset file into FILE. The changeset can be interpreted using
155 the <a href="sessionintro.html">session extension</a> to SQLite.</p></dd>
156 <dt><b>--lib LIBRARY</b></dt>
157 <dt><b>-L LIBRARY</b></dt>
158 <dd><p>Load the shared library or DLL file LIBRARY into SQLite prior to
159 computing the differences. This can be used to add application-defined
160 <a href="datatype3.html#collation">collating sequences</a> that are required by the schema.
161 </p></dd><dt><b>--primarykey</b></dt>
162 <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
163 pair rows in the source and destination database. (See additional
164 explanation below.)</p></dd>
165 <dt><b>--schema</b></dt>
166 <dd><p>Show only column name and table differences in the schema,
167 not the table content</p></dd>
168 <dt><b>--summary</b></dt>
169 <dd><p>Show how many rows have changed on each table, but do not show
170 the actual changes</p></dd>
171 <dt><b>--table TABLE</b></dt>
172 <dd><p>Show only the differences in content for TABLE, not for the
173 entire database</p></dd>
174 <dt><b>--transaction</b></dt>
175 <dd><p>Wrap SQL output in a single large transaction</p></dd>
176 <dt><b>--vtab</b></dt>
177 <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.
178 <a href="#sqldiff_vtab">See below</a> for details.
179 </p></dd>
180 </dl>
181
182 <h1 id="how_it_works"><span>2. </span>How It Works</h1>
183
184 <p>The sqldiff.exe utility works by finding rows in the source and
185 destination that are logical "pairs". The default behavior is to
186 treat two rows as pairs if they are in tables with the same name
187 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>
188 table if they have the same <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a>. Any differences in the
189 content of paired rows are output as UPDATEs. Rows in the source
190 database that could not be paired are output as DELETEs. Rows in
191 the destination database that could not be paired are output as
192 INSERTs.
193
194 </p><p>The --primarykey flag changes the pairing algorithm slightly so
195 that the schema-declared <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> is always used for pairing,
196 even on tables that have a <a href="lang_createtable.html#rowid">rowid</a>. This is often a better choice
197 for finding differences, however it can lead to missed differences in
198 the case of rows that have one or more PRIMARY KEY columns set to
199 NULL.</p>
200
201 <h1 id="limitations"><span>3. </span>Limitations</h1>
202
203 <ol>
204 <li>
205 <p>The sqldiff.exe utility does not compute changesets for
206 either: rowid tables for which the rowid is inaccessible;
207 or tables which have no explicit primary key.
208 Given the --changeset option, sqldiff omits them from the comparison.
209 Examples of such tables are:
210
211 </p><div class="codeblock"><pre>CREATE TABLE NilChangeset (
212 -- inaccessible rowid due to hiding its aliases
213 "rowid" TEXT,
214 "oid" TEXT,
215 "_rowid_" TEXT
216 );
217 </pre></div>
218
219 and
220
221 <div class="codeblock"><pre>CREATE TABLE NilChangeset (
222 -- no explicit primary key
223 "authorId" TEXT,
224 "bookId" TEXT
225 );
226 </pre></div>
227
228
229 When sqldiff is made to compare only such tables, no error occurs.
230 However, the result may be unexpected.
231 For example, the effect of this invocation:
232
233 <div class="codeblock"><pre>sqldiff --changeset CHANGESET_OUT --table NilChangeset db1.sdb db2.sdb
234 </pre></div>
235
236 will be to produce an empty file named "CHANGESET_OUT". See <a href="sessionintro.html#limitations">session limitations</a> for details.
237 </li><li><p>
238 The sqldiff.exe utility does not (currently) display differences in
239 <a href="lang_createtrigger.html">TRIGGERs</a> or <a href="lang_createview.html">VIEWs</a>.
240
241 </p></li><li><p>
242 The sqldiff utility is not designed to support schema migrations
243 and is forgiving with respect to differing column definitions.
244 Normally, only the column names and their order are compared
245 for like-named tables before content comparison proceeds.
246
247 </p><p>However, the single-table comparison option, with "sqlite_schema"
248 named, can be used to show or detect detailed schema differences
249 between a pair of databases.
250 When doing this, the output should not be used directly to modify a database.
251
252 <a name="sqldiff_vtab"></a>
253
254 </p></li><li><p id="vtab">
255 By default, differences in the schema or content of virtual tables are
256 not reported on.
257
258 </p><p>However, if a <a href="vtab.html">virtual table</a> implementation creates real tables (sometimes
259 referred to as "shadow" tables) within the database to store its data in, then
260 sqldiff.exe does calculate the difference between these. This can have
261 surprising effects if the resulting SQL script is then run on a database that
262 is not <i>exactly</i> the same as the source database. For several of SQLite's
263 bundled virtual tables (FTS3, FTS5, rtree and others), the surprising effects
264 may include corruption of the virtual table content.
265
266 </p><p> If the --vtab option is passed to sqldiff.exe, then it ignores all
267 underlying shadow tables belonging to an FTS3, FTS5 or rtree virtual table
268 and instead includes the virtual table differences directly.
269
270 </p></li></ol>
271 <p align="center"><small><i>This page was last updated on 2023-01-06 00:45:39Z </small></i></p>
272