Documentation Source Text

Check-in [afff09ce00]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Bring the formatchng.html document up-to-date.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: afff09ce00a2771f2dcff87fd5fe40ced3ecfdea
User & Date: drh 2015-10-29 18:16:39.579
Context
2015-10-30
01:14
Fix a typo in the formatchng.in document. (check-in: 13dc43c3a3 user: drh tags: trunk)
2015-10-29
18:16
Bring the formatchng.html document up-to-date. (check-in: afff09ce00 user: drh tags: trunk)
2015-10-27
19:18
Update documentation to note that fts5 contentless and external content tables do not support REPLACE. (check-in: 8885fffc21 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/formatchng.in.
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
<title>File Format Changes in SQLite</title>

<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>






<
<
<
|
<
>
|
<
<
>



>
>
|
<
<
|
|
<
|
>
|
<
<
>
>
|







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
<title>File Format Changes in SQLite</title>

<h2>File Format Changes in SQLite</h2>

<p>



The [file format|underlying file format] for SQLite databases does not

change in incompatable ways.  There are literally tens of billions of
SQLite database files in circulation and the SQLite developers are


committing to supporting those files for decades into the future.
</p>

<p>
This document describes incompatibilities that have occurred in
SQLite prior to 2004.  Since 2004, there have been enhancements to
SQLite such that newer database files are unreadable by older versions


of the SQLite library.  But the most recent versions of the SQLite
library should be able to read and write any older SQLite database

file without any problems.
</p>



<p>
In other words, since 2004 all SQLite releases have been backwards
compatible, though not necessarily forwards compatible.
</p>

<p>
The following table summarizes the SQLite file format changes that have
occurred since version 1.0.0:
</p>

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
  <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>







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<


<
<
<
<
<
<
<
<
<
<
<
<
<
<
174
175
176
177
178
179
180



























































































181
182














  <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>



























































































</table>
</blockquote>