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
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>File Format Changes in SQLite</title>
<style type="text/css">
body {
margin: auto;
font-family: Verdana, sans-serif;
padding: 8px 1%;
}
a { color: #044a64 }
a:visited { color: #734559 }
.logo { position:absolute; margin:3px; }
.tagline {
float:right;
text-align:right;
font-style:italic;
width:300px;
margin:12px;
margin-top:58px;
}
.toolbar {
text-align: center;
line-height: 1.6em;
margin: 0;
padding: 0px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #044a64; background: white; }
.content { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }
/* rounded corners */
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
/* Things for "fancyformat" documents start here. */
.fancy img+p {font-style:italic}
.fancy .codeblock i { color: darkblue; }
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
.fancy h2 { margin-left: 10px }
.fancy h3 { margin-left: 20px }
.fancy h4 { margin-left: 30px }
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
.fancy #toc a { color: darkblue ; text-decoration: none }
.fancy .todo { color: #AA3333 ; font-style : italic }
.fancy .todo:before { content: 'TODO:' }
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
.fancy img { display:block; }
.fancy :link:hover, .fancy :visited:hover { background: wheat }
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
.fancy li p { margin: 1em 0 }
/* End of "fancyformat" specific rules. */
</style>
</head>
<body>
<div><!-- container div to satisfy validator -->
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
border="0"></a>
<div><!-- IE hack to prevent disappearing logo--></div>
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
<table width=100% style="clear:both"><tr><td>
<div class="se"><div class="sw"><div class="ne"><div class="nw">
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
<td width=100%>
<div class="toolbar">
<a href="about.html">About</a>
<a href="sitemap.html">Sitemap</a>
<a href="docs.html">Documentation</a>
<a href="download.html">Download</a>
<a href="copyright.html">License</a>
<a href="news.html">News</a>
<a href="support.html">Support</a>
</div>
<script>
gMsg = "Search SQLite Docs..."
function entersearch() {
var q = document.getElementById("q");
if( q.value == gMsg ) { q.value = "" }
q.style.color = "black"
q.style.fontStyle = "normal"
}
function leavesearch() {
var q = document.getElementById("q");
if( q.value == "" ) {
q.value = gMsg
q.style.color = "#044a64"
q.style.fontStyle = "italic"
}
}
</script>
<td>
<div style="padding:0 1em 0px 0;white-space:nowrap">
<form name=f method="GET" action="http://www.sqlite.org/search">
<input id=q name=q type=text
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
</form>
</div>
</table>
</div></div></div></div>
</td></tr></table>
<div class=startsearch></div>
<h2>File Format Changes in SQLite</h2>
<p>
Every effort is made to keep SQLite fully backwards compatible from
one release to the next. Rarely, however, some
enhancements or bug fixes may require a change to
the underlying file format. When this happens and you
must convert the contents of your
databases into a portable ASCII representation using the old version
of the library then reload the data using the new version of the
library.
</p>
<p>
You can tell if you should reload your databases by comparing the
version numbers of the old and new libraries. If the first digit
of the version number is different, then a reload of the database will
be required. If the second digit changes, newer versions of SQLite
will be able to read and write older database files, but older versions
of the library may have difficulty reading or writing newer database
files.
For example, upgrading from
version 2.8.14 to 3.0.0 requires a reload. Going from
version 3.0.8 to 3.1.0 is backwards compatible but not necessarily
forwards compatible.
</p>
<p>
The following table summarizes the SQLite file format changes that have
occurred since version 1.0.0:
</p>
<blockquote>
<table border=2 cellpadding=5>
<tr>
<th>Version Change</th>
<th>Approx. Date</th>
<th>Description Of File Format Change</th>
</tr>
<tr>
<td valign="top">1.0.32 to 2.0.0</td>
<td valign="top">2001-Sep-20</td>
<td>Version 1.0.X of SQLite used the GDBM library as its backend
interface to the disk. Beginning in version 2.0.0, GDBM was replaced
by a custom B-Tree library written especially for SQLite. The new
B-Tree backend is twice as fast as GDBM, supports atomic commits and
rollback, and stores an entire database in a single disk file instead
using a separate file for each table as GDBM does. The two
file formats are not even remotely similar.</td>
</tr>
<tr>
<td valign="top">2.0.8 to 2.1.0</td>
<td valign="top">2001-Nov-12</td>
<td>The same basic B-Tree format is used but the details of the
index keys were changed in order to provide better query
optimization opportunities. Some of the headers were also changed in order
to increase the maximum size of a row from 64KB to 24MB.<p>
This change is an exception to the version number rule described above
in that it is neither forwards or backwards compatible. A complete
reload of the database is required. This is the only exception.</td>
</tr>
<tr>
<td valign="top">2.1.7 to 2.2.0</td>
<td valign="top">2001-Dec-21</td>
<td>Beginning with version 2.2.0, SQLite no longer builds an index for
an INTEGER PRIMARY KEY column. Instead, it uses that column as the actual
B-Tree key for the main table.<p>Version 2.2.0 and later of the library
will automatically detect when it is reading a 2.1.x database and will
disable the new INTEGER PRIMARY KEY feature. In other words, version
2.2.x is backwards compatible to version 2.1.x. But version 2.1.x is not
forward compatible with version 2.2.x. If you try to open
a 2.2.x database with an older 2.1.x library and that database contains
an INTEGER PRIMARY KEY, you will likely get a coredump. If the database
schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
and version 2.2.x database files will be identical and completely
interchangeable.</p>
</tr>
<tr>
<td valign="top">2.2.5 to 2.3.0</td>
<td valign="top">2002-Jan-30</td>
<td>Beginning with version 2.3.0, SQLite supports some additional syntax
(the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
that are stored in the SQLITE_MASTER table. If you create a database that
contains this new syntax, then try to read that database using version 2.2.5
or earlier, the parser will not understand the new syntax and you will get
an error. Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td>
</tr>
<tr>
<td valign="top">2.3.3 to 2.4.0</td>
<td valign="top">2002-Mar-10</td>
<td>Beginning with version 2.4.0, SQLite added support for views.
Information about views is stored in the SQLITE_MASTER table. If an older
version of SQLite attempts to read a database that contains VIEW information
in the SQLITE_MASTER table, the parser will not understand the new syntax
and initialization will fail. Also, the
way SQLite keeps track of unused disk blocks in the database file
changed slightly.
If an older version of SQLite attempts to write a database that
was previously written by version 2.4.0 or later, then it may leak disk
blocks.</td>
</tr>
<tr>
<td valign="top">2.4.12 to 2.5.0</td>
<td valign="top">2002-Jun-17</td>
<td>Beginning with version 2.5.0, SQLite added support for triggers.
Information about triggers is stored in the SQLITE_MASTER table. If an older
version of SQLite attempts to read a database that contains a CREATE TRIGGER
in the SQLITE_MASTER table, the parser will not understand the new syntax
and initialization will fail.
</td>
</tr>
<tr>
<td valign="top">2.5.6 to 2.6.0</td>
<td valign="top">2002-July-17</td>
<td>A design flaw in the layout of indices required a file format change
to correct. This change appeared in version 2.6.0.<p>
If you use version 2.6.0 or later of the library to open a database file
that was originally created by version 2.5.6 or earlier, an attempt to
rebuild the database into the new format will occur automatically.
This can take some time for a large database. (Allow 1 or 2 seconds
per megabyte of database under Unix - longer under Windows.) This format
conversion is irreversible. It is <strong>strongly</strong> suggested
that you make a backup copy of older database files prior to opening them
with version 2.6.0 or later of the library, in case there are errors in
the format conversion logic.<p>
Version 2.6.0 or later of the library cannot open read-only database
files from version 2.5.6 or earlier, since read-only files cannot be
upgraded to the new format.</p>
</td>
</tr>
<tr>
<td valign="top">2.6.3 to 2.7.0</td>
<td valign="top">2002-Aug-13</td>
<td><p>Beginning with version 2.7.0, SQLite understands two different
datatypes: text and numeric. Text data sorts in memcmp() order.
Numeric data sorts in numerical order if it looks like a number,
or in memcmp() order if it does not.</p>
<p>When SQLite version 2.7.0 or later opens a 2.6.3 or earlier database,
it assumes all columns of all tables have type "numeric". For 2.7.0
and later databases, columns have type "text" if their datatype
string contains the substrings "char" or "clob" or "blob" or "text".
Otherwise they are of type "numeric".</p>
<p>Because "text" columns have a different sort order from numeric,
indices on "text" columns occur in a different order for version
2.7.0 and later database. Hence version 2.6.3 and earlier of SQLite
will be unable to read a 2.7.0 or later database. But version 2.7.0
and later of SQLite will read earlier databases.</p>
</td>
</tr>
<tr>
<td valign="top">2.7.6 to 2.8.0</td>
<td valign="top">2003-Feb-14</td>
<td><p>Version 2.8.0 introduces a change to the format of the rollback
journal file. The main database file format is unchanged. Versions
2.7.6 and earlier can read and write 2.8.0 databases and vice versa.
Version 2.8.0 can rollback a transaction that was started by version
2.7.6 and earlier. But version 2.7.6 and earlier cannot rollback a
transaction started by version 2.8.0 or later.</p>
<p>The only time this would ever be an issue is when you have a program
using version 2.8.0 or later that crashes with an incomplete
transaction, then you try to examine the database using version 2.7.6 or
earlier. The 2.7.6 code will not be able to read the journal file
and thus will not be able to rollback the incomplete transaction
to restore the database.</p>
</td>
</tr>
<tr>
<td valign="top">2.8.14 to 3.0.0</td>
<td valign="top">2004-Jun-18</td>
<td><p>Version 3.0.0 is a major upgrade for SQLite that incorporates
support for UTF-16, BLOBs, and a more compact encoding that results
in database files that are typically 25% to 50% smaller. The new file
format is very different and is completely incompatible with the
version 2 file format.</p>
</td>
</tr>
<tr>
<td valign="top">3.0.8 to 3.1.0</td>
<td valign="top">2005-Jan-21</td>
<td><p>Version 3.1.0 adds support for
<a href="pragma.html#pragma_auto_vacuum">autovacuum mode</a>.
Prior versions of SQLite will be able to read an autovacuumed
database but will not be able to write it. If autovaccum is disabled
(which is the default condition)
then databases are fully forwards and backwards compatible.</p>
</td>
</tr>
<tr>
<td valign="top">3.1.6 to 3.2.0</td>
<td valign="top">2005-Mar-19</td>
<td><p>Version 3.2.0 adds support for the
<a href="lang_altertable.html">ALTER TABLE ADD COLUMN</a>
command. A database that has been modified by this command can
not be read by a version of SQLite prior to 3.1.4. Running
<a href="lang_vacuum.html">VACUUM</a>
after the ALTER TABLE
restores the database to a format such that it can be read by earlier
SQLite versions.</p>
</td>
</tr>
<tr>
<td valign="top">3.2.8 to 3.3.0</td>
<td valign="top">2006-Jan-10</td>
<td><p>Version 3.3.0 adds support for descending indices and
uses a new encoding for boolean values that requires
less disk space. Version 3.3.0 can read and write database
files created by prior versions of SQLite. But prior versions
of SQLite will not be able to read or write databases created
by Version 3.3.0</p>
<p>If you need backwards and forwards compatibility, you can
compile with -DSQLITE_DEFAULT_FILE_FORMAT=1. Or at runtime
you can say "PRAGMA legacy_file_format=ON" prior to creating
a new database file</p>
<p>Once a database file is created, its format is fixed. So
a database file created by SQLite 3.2.8 and merely modified
by version 3.3.0 or later will retain the old format. Except,
the VACUUM command recreates the database so running VACUUM
on 3.3.0 or later will change the file format to the latest
edition.</p>
</td>
</tr>
<tr>
<td valign="top">3.3.6 to 3.3.7</td>
<td valign="top">2006-Aug-12</td>
<td><p>The previous file format change has caused so much
grief that the default behavior has been changed back to
the original file format. This means that DESC option on
indices is ignored by default that the more efficient encoding
of boolean values is not used. In that way, older versions
of SQLite can read and write databases created by newer
versions. If the new features are desired, they can be
enabled using pragma: "PRAGMA legacy_file_format=OFF".</p>
<p>To be clear: both old and new file formats continue to
be understood and continue to work. But the old file format
is used by default instead of the new. This might change
again in some future release - we may go back to generating
the new file format by default - but probably not until
all users have upgraded to a version of SQLite that will
understand the new file format. That might take several
years.</p></td>
</tr>
<tr>
<td valign="top">3.4.2 to 3.5.0</td>
<td valign="top">2007-Sep-3</td>
<td><p>The design of the OS interface layer was changed for
release 3.5.0. Applications that implemented a custom OS
interface will need to be modified in order to upgrade.
There are also some subtly different semantics a few obscure
APIs. An <a href="34to35.html">article</a> is available which
describing the changes in detail.</p>
<p>The on-disk file format is unchanged.</p>
</td>
</tr>
<tr>
<td valign="top">3.5.9 to 3.6.0</td>
<td valign="top">2008-July-16</td>
<td><p>There are minor tweaks to the new OS interface layer that
was added in version 3.5.0.
Applications that implemented a custom OS
interface will need to be adjusted.
An <a href="35to36.html">article</a> is available which
describing the changes in detail.</p>
<p>The on-disk file format is unchanged.</p>
</td>
</tr>
</table>
</blockquote>
<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite. Call these two executables "<b>sqlite-old</b>"
and "<b>sqlite-new</b>". Suppose the name of your old database
is "<b>old.db</b>" and you want to create a new database with
the same information named "<b>new.db</b>". The command to do
this is as follows:
</p>
<blockquote>
sqlite-old old.db .dump | sqlite-new new.db
</blockquote>
|
