1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>File Format Changes in SQLite</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
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...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
122
<h2>File Format Changes in SQLite</h2>
125
Every effort is made to keep SQLite fully backwards compatible from
126
one release to the next. Rarely, however, some
127
enhancements or bug fixes may require a change to
128
the underlying file format. When this happens and you
129
must convert the contents of your
130
databases into a portable ASCII representation using the old version
131
of the library then reload the data using the new version of the
136
You can tell if you should reload your databases by comparing the
137
version numbers of the old and new libraries. If the first digit
138
of the version number is different, then a reload of the database will
139
be required. If the second digit changes, newer versions of SQLite
140
will be able to read and write older database files, but older versions
141
of the library may have difficulty reading or writing newer database
143
For example, upgrading from
144
version 2.8.14 to 3.0.0 requires a reload. Going from
145
version 3.0.8 to 3.1.0 is backwards compatible but not necessarily
150
The following table summarizes the SQLite file format changes that have
151
occurred since version 1.0.0:
155
<table border=2 cellpadding=5>
157
<th>Version Change</th>
158
<th>Approx. Date</th>
159
<th>Description Of File Format Change</th>
162
<td valign="top">1.0.32 to 2.0.0</td>
163
<td valign="top">2001-Sep-20</td>
164
<td>Version 1.0.X of SQLite used the GDBM library as its backend
165
interface to the disk. Beginning in version 2.0.0, GDBM was replaced
166
by a custom B-Tree library written especially for SQLite. The new
167
B-Tree backend is twice as fast as GDBM, supports atomic commits and
168
rollback, and stores an entire database in a single disk file instead
169
using a separate file for each table as GDBM does. The two
170
file formats are not even remotely similar.</td>
173
<td valign="top">2.0.8 to 2.1.0</td>
174
<td valign="top">2001-Nov-12</td>
175
<td>The same basic B-Tree format is used but the details of the
176
index keys were changed in order to provide better query
177
optimization opportunities. Some of the headers were also changed in order
178
to increase the maximum size of a row from 64KB to 24MB.<p>
180
This change is an exception to the version number rule described above
181
in that it is neither forwards or backwards compatible. A complete
182
reload of the database is required. This is the only exception.</td>
185
<td valign="top">2.1.7 to 2.2.0</td>
186
<td valign="top">2001-Dec-21</td>
187
<td>Beginning with version 2.2.0, SQLite no longer builds an index for
188
an INTEGER PRIMARY KEY column. Instead, it uses that column as the actual
189
B-Tree key for the main table.<p>Version 2.2.0 and later of the library
190
will automatically detect when it is reading a 2.1.x database and will
191
disable the new INTEGER PRIMARY KEY feature. In other words, version
192
2.2.x is backwards compatible to version 2.1.x. But version 2.1.x is not
193
forward compatible with version 2.2.x. If you try to open
194
a 2.2.x database with an older 2.1.x library and that database contains
195
an INTEGER PRIMARY KEY, you will likely get a coredump. If the database
196
schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
197
and version 2.2.x database files will be identical and completely
201
<td valign="top">2.2.5 to 2.3.0</td>
202
<td valign="top">2002-Jan-30</td>
203
<td>Beginning with version 2.3.0, SQLite supports some additional syntax
204
(the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
205
that are stored in the SQLITE_MASTER table. If you create a database that
206
contains this new syntax, then try to read that database using version 2.2.5
207
or earlier, the parser will not understand the new syntax and you will get
208
an error. Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td>
211
<td valign="top">2.3.3 to 2.4.0</td>
212
<td valign="top">2002-Mar-10</td>
213
<td>Beginning with version 2.4.0, SQLite added support for views.
214
Information about views is stored in the SQLITE_MASTER table. If an older
215
version of SQLite attempts to read a database that contains VIEW information
216
in the SQLITE_MASTER table, the parser will not understand the new syntax
217
and initialization will fail. Also, the
218
way SQLite keeps track of unused disk blocks in the database file
220
If an older version of SQLite attempts to write a database that
221
was previously written by version 2.4.0 or later, then it may leak disk
225
<td valign="top">2.4.12 to 2.5.0</td>
226
<td valign="top">2002-Jun-17</td>
227
<td>Beginning with version 2.5.0, SQLite added support for triggers.
228
Information about triggers is stored in the SQLITE_MASTER table. If an older
229
version of SQLite attempts to read a database that contains a CREATE TRIGGER
230
in the SQLITE_MASTER table, the parser will not understand the new syntax
231
and initialization will fail.
235
<td valign="top">2.5.6 to 2.6.0</td>
236
<td valign="top">2002-July-17</td>
237
<td>A design flaw in the layout of indices required a file format change
238
to correct. This change appeared in version 2.6.0.<p>
240
If you use version 2.6.0 or later of the library to open a database file
241
that was originally created by version 2.5.6 or earlier, an attempt to
242
rebuild the database into the new format will occur automatically.
243
This can take some time for a large database. (Allow 1 or 2 seconds
244
per megabyte of database under Unix - longer under Windows.) This format
245
conversion is irreversible. It is <strong>strongly</strong> suggested
246
that you make a backup copy of older database files prior to opening them
247
with version 2.6.0 or later of the library, in case there are errors in
248
the format conversion logic.<p>
250
Version 2.6.0 or later of the library cannot open read-only database
251
files from version 2.5.6 or earlier, since read-only files cannot be
252
upgraded to the new format.</p>
256
<td valign="top">2.6.3 to 2.7.0</td>
257
<td valign="top">2002-Aug-13</td>
258
<td><p>Beginning with version 2.7.0, SQLite understands two different
259
datatypes: text and numeric. Text data sorts in memcmp() order.
260
Numeric data sorts in numerical order if it looks like a number,
261
or in memcmp() order if it does not.</p>
263
<p>When SQLite version 2.7.0 or later opens a 2.6.3 or earlier database,
264
it assumes all columns of all tables have type "numeric". For 2.7.0
265
and later databases, columns have type "text" if their datatype
266
string contains the substrings "char" or "clob" or "blob" or "text".
267
Otherwise they are of type "numeric".</p>
269
<p>Because "text" columns have a different sort order from numeric,
270
indices on "text" columns occur in a different order for version
271
2.7.0 and later database. Hence version 2.6.3 and earlier of SQLite
272
will be unable to read a 2.7.0 or later database. But version 2.7.0
273
and later of SQLite will read earlier databases.</p>
277
<td valign="top">2.7.6 to 2.8.0</td>
278
<td valign="top">2003-Feb-14</td>
279
<td><p>Version 2.8.0 introduces a change to the format of the rollback
280
journal file. The main database file format is unchanged. Versions
281
2.7.6 and earlier can read and write 2.8.0 databases and vice versa.
282
Version 2.8.0 can rollback a transaction that was started by version
283
2.7.6 and earlier. But version 2.7.6 and earlier cannot rollback a
284
transaction started by version 2.8.0 or later.</p>
286
<p>The only time this would ever be an issue is when you have a program
287
using version 2.8.0 or later that crashes with an incomplete
288
transaction, then you try to examine the database using version 2.7.6 or
289
earlier. The 2.7.6 code will not be able to read the journal file
290
and thus will not be able to rollback the incomplete transaction
291
to restore the database.</p>
295
<td valign="top">2.8.14 to 3.0.0</td>
296
<td valign="top">2004-Jun-18</td>
297
<td><p>Version 3.0.0 is a major upgrade for SQLite that incorporates
298
support for UTF-16, BLOBs, and a more compact encoding that results
299
in database files that are typically 25% to 50% smaller. The new file
300
format is very different and is completely incompatible with the
301
version 2 file format.</p>
305
<td valign="top">3.0.8 to 3.1.0</td>
306
<td valign="top">2005-Jan-21</td>
307
<td><p>Version 3.1.0 adds support for
308
<a href="pragma.html#pragma_auto_vacuum">autovacuum mode</a>.
309
Prior versions of SQLite will be able to read an autovacuumed
310
database but will not be able to write it. If autovaccum is disabled
311
(which is the default condition)
312
then databases are fully forwards and backwards compatible.</p>
316
<td valign="top">3.1.6 to 3.2.0</td>
317
<td valign="top">2005-Mar-19</td>
318
<td><p>Version 3.2.0 adds support for the
319
<a href="lang_altertable.html">ALTER TABLE ADD COLUMN</a>
320
command. A database that has been modified by this command can
321
not be read by a version of SQLite prior to 3.1.4. Running
322
<a href="lang_vacuum.html">VACUUM</a>
323
after the ALTER TABLE
324
restores the database to a format such that it can be read by earlier
329
<td valign="top">3.2.8 to 3.3.0</td>
330
<td valign="top">2006-Jan-10</td>
331
<td><p>Version 3.3.0 adds support for descending indices and
332
uses a new encoding for boolean values that requires
333
less disk space. Version 3.3.0 can read and write database
334
files created by prior versions of SQLite. But prior versions
335
of SQLite will not be able to read or write databases created
337
<p>If you need backwards and forwards compatibility, you can
338
compile with -DSQLITE_DEFAULT_FILE_FORMAT=1. Or at runtime
339
you can say "PRAGMA legacy_file_format=ON" prior to creating
340
a new database file</p>
341
<p>Once a database file is created, its format is fixed. So
342
a database file created by SQLite 3.2.8 and merely modified
343
by version 3.3.0 or later will retain the old format. Except,
344
the VACUUM command recreates the database so running VACUUM
345
on 3.3.0 or later will change the file format to the latest
350
<td valign="top">3.3.6 to 3.3.7</td>
351
<td valign="top">2006-Aug-12</td>
352
<td><p>The previous file format change has caused so much
353
grief that the default behavior has been changed back to
354
the original file format. This means that DESC option on
355
indices is ignored by default that the more efficient encoding
356
of boolean values is not used. In that way, older versions
357
of SQLite can read and write databases created by newer
358
versions. If the new features are desired, they can be
359
enabled using pragma: "PRAGMA legacy_file_format=OFF".</p>
360
<p>To be clear: both old and new file formats continue to
361
be understood and continue to work. But the old file format
362
is used by default instead of the new. This might change
363
again in some future release - we may go back to generating
364
the new file format by default - but probably not until
365
all users have upgraded to a version of SQLite that will
366
understand the new file format. That might take several
370
<td valign="top">3.4.2 to 3.5.0</td>
371
<td valign="top">2007-Sep-3</td>
372
<td><p>The design of the OS interface layer was changed for
373
release 3.5.0. Applications that implemented a custom OS
374
interface will need to be modified in order to upgrade.
375
There are also some subtly different semantics a few obscure
376
APIs. An <a href="34to35.html">article</a> is available which
377
describing the changes in detail.</p>
379
<p>The on-disk file format is unchanged.</p>
383
<td valign="top">3.5.9 to 3.6.0</td>
384
<td valign="top">2008-July-16</td>
385
<td><p>There are minor tweaks to the new OS interface layer that
386
was added in version 3.5.0.
387
Applications that implemented a custom OS
388
interface will need to be adjusted.
389
An <a href="35to36.html">article</a> is available which
390
describing the changes in detail.</p>
392
<p>The on-disk file format is unchanged.</p>
399
To perform a database reload, have ready versions of the
400
<b>sqlite</b> command-line utility for both the old and new
401
version of SQLite. Call these two executables "<b>sqlite-old</b>"
402
and "<b>sqlite-new</b>". Suppose the name of your old database
403
is "<b>old.db</b>" and you want to create a new database with
404
the same information named "<b>new.db</b>". The command to do
409
sqlite-old old.db .dump | sqlite-new new.db
added
removed
formatchng.html
