SQLite Version 3.0

At the time of writing, SQLite version 3.0 was close to release, and it may very well be available as a stable release by the time you are reading this book.

The decision was made to cover the latest 2.8 version throughout the book primarily because of the vast user base this version of SQLite already has.

Although version 3.0 adds some exciting functionality, it also has a completely different API, so existing users will be slow to migrate, if they switch to the new version at all. New users should consider carefully whether they want to use the new version or stick with a tried and tested library for their application.

New features will no longer be added to SQLite 2.8, but it will continue to be supported and have maintenance fixes issued for the foreseeable future, so do not be put off from using it simply because a new version has been released.

Let's take a look at some of the changes in SQLite version 3.0.

Naming Changes

Because it is important that SQLite 2.8 can continue to be used while the new version is introduced, version 3.0 uses a new naming convention and allows both versions of the SQLite library to be linked to the same program if required.

The library itself has been renamed to libsqlite3.so on Unix and sqlite3.dll on Windows, and the include file is now called sqlite3.h. The sqlite command-line tool has also been renamed to sqlite3.

Within the API itself, function names have also been changed to be prefixed with sqlite3, but the names themselves remain familiar; for example, sqlite3_open() and sqlite3_changes() will behave as expected.

Although most of the sqlite3 prefixed function names have the same prototype as their corresponding functions in SQLite 2, one significant difference is the sqlite3_open() function, which works as follows:

 int sqlite3_open(const char *filename, sqlite3 **ppDb) 

In SQLite 3, the return value from the database connection function is SQLITE_OK on success or the corresponding error code on failure. The database handle is returned in *ppDb.

The full list of version 3 API calls can be found at http://www.sqlite.org/capi3ref.html.

File Format Changes

The database file format has been overhauled. Version 2.8 databases cannot be read by the 3.0 library, and the reverse is true.

The new file format uses a B+Tree data structure for table storage to replace the B-Trees, which allows better scalability within a filesystem-based database. It is also more highly optimized through omitting unused fields from the disk image and better encoding of floating-point numbers, which has been shown to produce a 2535% reduction in the overall file size.

Migrating from the old database file format to SQLite 3.0 is very simple if you have both the sqlite and sqlite3 command-line tools installed. The following command would effectively upgrade olddb to the new file format, saving the new version as newdb.

 $ sqlite olddb .dump | sqlite3 newdb 

Data Typing

The typeless nature of SQLite columns has been altered in order that BLOBs (Binary Large Objects) can be supported.

Though any value can still be stored in a column of any declared data type, SQLite version 3.0 assigns each value stored a particular data type class. The library receives all values as strings; however, if a string appears to contain only a number, it may be considered an INTEGER or NUMERIC, depending on whether or not the number has a fractional part.

The data type classes in SQLite 3.0 are

• NULL

• TEXT

• REAL

• INTEGER

• BLOB

The type of a value is associated with that value itself and not the column in which it is stored. This is sometimes called manifest typing and is not found in other SQL engines, where the declared column data type determines the type of all the values stored in it.

SQLite supports the concept of type affinity on columns, where the declared data type is the recommended type for values that are inserted into that column. Significantly, the declared types are only recommendations and not requirements, and it is still possible to insert any kind of data into any column. However, the declared column type will sometimes influence the manifest type of a value.

A column with TEXT affinity will store all data using only the NULL, TEXT, and BLOB classes. A numeric value will be converted to TEXT before storage. If the column type definition contains CHAR, CLOB, or TEXT, the column will be given TEXT affinity.

If the column type definition contains BLOB or no data type is given in the schema, the column will have affinity to NONE. This property ensures that no attempt at conversion will be made on inserted values.

An INTEGER or NUMERIC column can use any of the storage classes, but attempts to convert the value to an integer or real number before resorting to a TEXT or BLOB type. A decimal number inserted into a column with INTEGER affinity will cause the fractional part to be discarded and the number to be stored as an integer data type.

If the column type definition contains INT, its affinity will be INTEGER; otherwise, it will be NUMERIC.

Tables created with the CREATE TABLE ... AS SELECT syntax will have no data type definitions. All columns will have affinity NONE.

A few special rules apply when comparing values of different data types. Although INTEGER and NUMERIC types are compared numerically as you would expect, an INTEGER or NUMERIC type is always considered to be less than any TEXT or BLOB regardless of the actual values involved. Any TEXT type is also always less than any BLOB. When a TEXT or BLOB value is compared to a value of the same type, memcpy() is used to evaluate which is the greater value. A NULL is always considered less than a value of any other type.

SQLite 3.0 also features a strict affinity mode, whereby if a data item inserted into a column requires the conversion just described, the database engine will raise an error and perform a rollbackthe behavior that is expected of most other database engines. There is also no affinity mode, which causes SQLite 3 to handle data types in the same manner as SQLite 2.

User-Defined Collating Sequences

A collating sequence is a definition of the way in which text strings are ordered. SQLite 3.0 allows you to implement user-defined collating sequences as functions, which can then be used to determine the sort order within SQLite.

A collating sequence function takes two input arguments and compares them in some custom way. A negative, zero, or positive return value indicates, respectively, that the first value was less than, equal to, or greater than the second.

The COLLATE clause in SQL can appear in the CREATE TABLE statement to define the default collating sequence for a column or in the ORDER BY clause of a SELECT statement.

Improved Concurrency

The new version of SQLite supports better concurrent use through a more complex locking and journaling scheme that allows reader and writer processes to coexist in the same database. The new mechanism also adds the ability to perform atomic transactions across different attached database files.

Rather than being simply locked when a write operation takes place, the database file can now be in any of the following locking states:

• UNLOCKED

• SHARED

• RESERVED

• PENDING

• EXCLUSIVE

The UNLOCKED state means that there are no locks on the database and therefore no read or write operations may be performed.

Many simultaneous reader processes can all hold SHARED locks on the same database; however, no write operation can take place when there is a SHARED lock.

When a process is planning to perform a write, it locks the file as RESERVED. There can be only one RESERVED lock; however, it can coexist with SHARED locks and new SHARED locks can even be acquired while the database is RESERVED. The implication of this is that a writer process can coexist with a readersomething that SQLite 2 was not capable of.

The PENDING lock means that a process needs to write to the database as soon as possible. It will already have requested a RESERVED lock and will be waiting for any SHARED locks to clear before it can perform its write. When a PENDING lock is active, no further SHARED locks can be acquired by other processes. This implements a priority system that ensures new reader processes do not lock the database when a writer is waiting, whereas in SQLite 2 it was possible that a constant stream of reader processes could prevent any writes from taking place.

The only way a write operation can take place is through an EXCLUSIVE lock on the database file. Equivalent to the writer lock in SQLite 2, no other locks can coexist with an EXCLUSIVE. Through the use of RESERVED and PENDING locks, SQLite keeps the amount of time for which an EXCLUSIVE lock is held to a minimum.