2 # Run this Tcl script to generate the formatchng.html file.
4 set rcsid
{$Id: formatchng.tcl
,v
1.14 2005/03/23 01:05:14 drh Exp
$ }
6 header
{File Format Changes in SQLite
}
8 <h2
>File Format Changes in SQLite
</h2
>
11 Every effort is made to keep SQLite fully backwards compatible from
12 one release to the next. Rarely
, however
, some
13 enhancements or bug fixes may require a change to
14 the underlying
file format. When this happens and you
15 must convert the contents of your
16 databases into a portable ASCII representation using the old version
17 of the library then reload the data using the new version of the
22 You can
tell if you should reload your databases by comparing the
23 version numbers of the old and new libraries. If the first digit
24 of the version number is different
, then a reload of the database will
25 be required. If the second digit changes
, newer versions of SQLite
26 will be able to
read and write older database files
, but older versions
27 of the library may have difficulty reading or writing newer database
29 For example
, upgrading from
30 version
2.8.14 to
3.0.0 requires a reload. Going from
31 version
3.0.8 to
3.1.0 is backwards compatible but not necessarily
36 The following table summarizes the SQLite
file format changes that have
37 occurred since version
1.0.0:
41 <table border
=2 cellpadding
=5>
43 <th
>Version Change
</th
>
45 <th
>Description Of File Format Change
</th
>
48 <td valign
="top">1.0.32 to
2.0.0</td
>
49 <td valign
="top">2001-Sep-20
</td
>
50 <td
>Version
1.0.X of SQLite used the GDBM library as its backend
51 interface to the disk. Beginning in version
2.0.0, GDBM was replaced
52 by a custom B-Tree library written especially
for SQLite. The new
53 B-Tree backend is twice as fast as GDBM
, supports atomic commits and
54 rollback
, and stores an entire database in a single disk
file instead
55 using a separate
file for each table as GDBM does. The two
56 file formats are not even remotely similar.
</td
>
59 <td valign
="top">2.0.8 to
2.1.0</td
>
60 <td valign
="top">2001-Nov-12
</td
>
61 <td
>The same basic B-Tree
format is used but the details of the
62 index keys were changed in order to provide better query
63 optimization opportunities. Some of the headers were also changed in order
64 to increase the maximum size of a row from
64KB to
24MB.
<p
>
66 This change is an exception to the version number rule described above
67 in that it is neither forwards or backwards compatible. A complete
68 reload of the database is required. This is the only exception.
</td
>
71 <td valign
="top">2.1.7 to
2.2.0</td
>
72 <td valign
="top">2001-Dec-21
</td
>
73 <td
>Beginning with version
2.2.0, SQLite no longer builds an index
for
74 an INTEGER PRIMARY KEY column. Instead
, it uses that column as the actual
75 B-Tree key
for the main table.
<p
>Version
2.2.0 and later of the library
76 will automatically detect when it is reading a
2.1.x database and will
77 disable the new INTEGER PRIMARY KEY feature. In other words
, version
78 2.2.x is backwards compatible to version
2.1.x. But version
2.1.x is not
79 forward compatible with version
2.2.x. If you try to
open
80 a
2.2.x database with an older
2.1.x library and that database contains
81 an INTEGER PRIMARY KEY
, you will likely get a coredump. If the database
82 schema does not contain any INTEGER PRIMARY KEYs
, then the version
2.1.x
83 and version
2.2.x database files will be identical and completely
87 <td valign
="top">2.2.5 to
2.3.0</td
>
88 <td valign
="top">2002-Jan-30
</td
>
89 <td
>Beginning with version
2.3.0, SQLite supports some additional syntax
90 (the
"ON CONFLICT" clause
) in the CREATE TABLE and CREATE INDEX statements
91 that are stored in the SQLITE_MASTER table. If you create a database that
92 contains this new syntax
, then try to
read that database using version
2.2.5
93 or earlier
, the parser will not understand the new syntax and you will get
94 an
error. Otherwise
, databases
for 2.2.x and
2.3.x are interchangeable.
</td
>
97 <td valign
="top">2.3.3 to
2.4.0</td
>
98 <td valign
="top">2002-Mar-10
</td
>
99 <td
>Beginning with version
2.4.0, SQLite added support
for views.
100 Information about views is stored in the SQLITE_MASTER table. If an older
101 version of SQLite attempts to
read a database that contains VIEW information
102 in the SQLITE_MASTER table
, the parser will not understand the new syntax
103 and initialization will fail. Also
, the
104 way SQLite keeps track of unused disk blocks in the database
file
106 If an older version of SQLite attempts to write a database that
107 was previously written by version
2.4.0 or later
, then it may leak disk
111 <td valign
="top">2.4.12 to
2.5.0</td
>
112 <td valign
="top">2002-Jun-17
</td
>
113 <td
>Beginning with version
2.5.0, SQLite added support
for triggers.
114 Information about triggers is stored in the SQLITE_MASTER table. If an older
115 version of SQLite attempts to
read a database that contains a CREATE TRIGGER
116 in the SQLITE_MASTER table
, the parser will not understand the new syntax
117 and initialization will fail.
121 <td valign
="top">2.5.6 to
2.6.0</td
>
122 <td valign
="top">2002-July-17
</td
>
123 <td
>A design flaw in the layout of indices required a
file format change
124 to correct. This change appeared in version
2.6.0.
<p
>
126 If you use version
2.6.0 or later of the library to
open a database
file
127 that was originally created by version
2.5.6 or earlier
, an attempt to
128 rebuild the database into the new
format will occur automatically.
129 This can take some
time for a large database.
(Allow
1 or
2 seconds
130 per megabyte of database under Unix
- longer under Windows.
) This
format
131 conversion is irreversible. It is
<strong
>strongly
</strong
> suggested
132 that you make a backup copy of older database files prior to opening them
133 with version
2.6.0 or later of the library
, in case there are errors in
134 the
format conversion logic.
<p
>
136 Version
2.6.0 or later of the library cannot
open read-only database
137 files from version
2.5.6 or earlier
, since read-only files cannot be
138 upgraded to the new
format.
</p
>
142 <td valign
="top">2.6.3 to
2.7.0</td
>
143 <td valign
="top">2002-Aug-13
</td
>
144 <td
><p
>Beginning with version
2.7.0, SQLite understands two different
145 datatypes
: text and numeric. Text data sorts in memcmp
() order.
146 Numeric data sorts in numerical order
if it looks like a number
,
147 or in memcmp
() order
if it does not.
</p
>
149 <p
>When SQLite version
2.7.0 or later opens a
2.6.3 or earlier database
,
150 it assumes all columns of all tables have type
"numeric". For
2.7.0
151 and later databases
, columns have type
"text" if their datatype
152 string contains the substrings
"char" or
"clob" or
"blob" or
"text".
153 Otherwise they are of type
"numeric".
</p
>
155 <p
>Because
"text" columns have a different sort order from numeric
,
156 indices on
"text" columns occur in a different order
for version
157 2.7.0 and later database. Hence version
2.6.3 and earlier of SQLite
158 will be unable to
read a
2.7.0 or later database. But version
2.7.0
159 and later of SQLite will
read earlier databases.
</p
>
163 <td valign
="top">2.7.6 to
2.8.0</td
>
164 <td valign
="top">2003-Feb-14
</td
>
165 <td
><p
>Version
2.8.0 introduces a change to the
format of the rollback
166 journal
file. The main database
file format is unchanged. Versions
167 2.7.6 and earlier can
read and write
2.8.0 databases and vice versa.
168 Version
2.8.0 can rollback a transaction that was started by version
169 2.7.6 and earlier. But version
2.7.6 and earlier cannot rollback a
170 transaction started by version
2.8.0 or later.
</p
>
172 <p
>The only
time this would ever be an issue is when you have a program
173 using version
2.8.0 or later that crashes with an incomplete
174 transaction
, then you try to examine the database using version
2.7.6 or
175 earlier. The
2.7.6 code will not be able to
read the journal
file
176 and thus will not be able to rollback the incomplete transaction
177 to restore the database.
</p
>
181 <td valign
="top">2.8.14 to
3.0.0</td
>
182 <td valign
="top">2004-Jun-18
</td
>
183 <td
><p
>Version
3.0.0 is a major upgrade
for SQLite that incorporates
184 support
for UTF-16
, BLOBs
, and a more compact
encoding that results
185 in database files that are typically
25% to
50% smaller. The new
file
186 format is very different and is completely incompatible with the
187 version
2 file format.
</p
>
191 <td valign
="top">3.0.8 to
3.1.0</td
>
192 <td valign
="top">2004-Jan-21
</td
>
193 <td
><p
>Version
3.1.0 adds support
for
194 <a href
="pragma.html#pragma_auto_vacuum">autovacuum mode
</a
>.
195 Prior versions of SQLite will be able to
read an autovacuumed
196 database but will not be able to write it. If autovaccum is disabled
197 (which is the
default condition
)
198 then databases are fully forwards and backwards compatible.
</p
>
202 <td valign
="top">3.1.6 to
3.2.0</td
>
203 <td valign
="top">2004-Mar-19
</td
>
204 <td
><p
>Version
3.2.0 adds support
for the
205 <a href
="lang_altertable.html">ALTER TABLE ADD COLUMN
</a
>
206 command. A database that has been modified by this command can
207 not be
read by a version of SQLite prior to
3.1.4. Running
208 <a href
="lang_vacuum.html">VACUUM
</a
>
209 after the ALTER TABLE
210 restores the database to a
format such that it can be
read by earlier
218 To perform a database reload
, have ready versions of the
219 <b
>sqlite
</b
> command-line utility
for both the old and new
220 version of SQLite. Call these two executables
"<b>sqlite-old</b>"
221 and
"<b>sqlite-new</b>". Suppose the name of your old database
222 is
"<b>old.db</b>" and you want to create a new database with
223 the same information named
"<b>new.db</b>". The command to do
228 sqlite-old old.db .dump | sqlite-new new.db
