PySQLite extends the
class with its own
class, with which errors
Two subclasses of Error are implemented InterfaceError and DatabaseError . These allow you to detect whether the error originates with the SQLite engine or the PySQLite interface.
class is further subdivided as
Appendix I. The Future of SQLite
This appendix looks at how SQLite might be improved, enhanced, or extended in the future.
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
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
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.
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
Within the API itself, function
Although most of the
prefixed function names have the same prototype as their corresponding functions in SQLite 2, one significant difference is the
function, which works as
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
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
The typeless nature of SQLite
Though any value can still be stored in a column of any declared data type, SQLite version 3.0
The data type classes in SQLite 3.0 are
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.
column can use any of the storage classes, but attempts to convert the value to an integer or real number before
If the column type definition contains
, its affinity will be
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
types are compared
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
A collating sequence function takes two input arguments and
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.
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:
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
. There can be only one
lock; however, it can coexist with
locks and new
locks can even be
lock means that a process needs to write to the database as soon as possible. It will already have
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.