+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. +
+ ++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. +
+ ++The following table summarizes the SQLite file format changes that have +occurred since version 1.0.0: +
+ +++ ++
++ +Version Change +Approx. Date +Description Of File Format Change ++ +1.0.32 to 2.0.0 +2001-Sep-20 +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. ++ +2.0.8 to 2.1.0 +2001-Nov-12 +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. ++ + 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.
+ +2.1.7 to 2.2.0 +2001-Dec-21 +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. 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.
++ +2.2.5 to 2.3.0 +2002-Jan-30 +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. ++ +2.3.3 to 2.4.0 +2002-Mar-10 +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. ++ +2.4.12 to 2.5.0 +2002-Jun-17 +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. + ++ +2.5.6 to 2.6.0 +2002-July-17 +A design flaw in the layout of indices required a file format change + to correct. This change appeared in version 2.6.0. ++ + 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 strongly 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.
+ + 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.
++ +2.6.3 to 2.7.0 +2002-Aug-13 ++ 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.
+ +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".
+ +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.
++ +2.7.6 to 2.8.0 +2003-Feb-14 ++ 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.
+ +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.
++ +2.8.14 to 3.0.0 +2004-Jun-18 ++ 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.
++ +3.0.8 to 3.1.0 +2005-Jan-21 ++ Version 3.1.0 adds support for + autovacuum mode. + 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.
++ +3.1.6 to 3.2.0 +2005-Mar-19 ++ Version 3.2.0 adds support for the + ALTER TABLE ADD COLUMN + 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 + VACUUM + after the ALTER TABLE + restores the database to a format such that it can be read by earlier + SQLite versions.
++ +3.2.8 to 3.3.0 +2006-Jan-10 ++ 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
+If you need backwards and forwards capatibility, 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
+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.
++ +3.3.6 to 3.3.7 +2006-Aug-12 ++ 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".
+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.
+ +3.4.2 to 3.5.0 +2007-Sep-3 ++ 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 article is avilable which + describing the changes in detail.
+ +The on-disk file format is unchanged.
+
+To perform a database reload, have ready versions of the +sqlite command-line utility for both the old and new +version of SQLite. Call these two executables "sqlite-old" +and "sqlite-new". Suppose the name of your old database +is "old.db" and you want to create a new database with +the same information named "new.db". The command to do +this is as follows: +
+ ++ sqlite-old old.db .dump | sqlite-new new.db ++} +footer $rcsid -- cgit v1.1