wiki:SqliteVersions

Version 13 (modified by flip, 9 years ago) (diff)

--

SQLite Versions

Our code must be compatible with SQLite 3.3.4 which was released in February 2006. That means we can't use any of the features added since then, such as --

  • virtual tables (3.3.7)
  • full text search (3.3.8)
  • IF EXISTS on CREATE/DROP VIEW (3.3.8)
  • randomblob() and hex() (3.3.13)
  • SUBSTR() 3rd param becomes optional (3.5.2)
  • group_concat() (3.5.4)
  • foreign key constraints (3.6.19)

http://www.sqlite.org/changes.html#version_3_3_4

The Background

Many C-based libraries are dynamically loaded at runtime, and SQLite can be too. However, it's quite small and so can be statically linked into applications. In fact, that's a very typical usage scenario for SQLite. Python is a good (and pertinent) example of this, as SQLite is commonly built into Python.

Huh? I Thought SQLite Was Shipped with Python…

The source distribution of Python >= 2.5 ships with SQLite bindings. That means that Python knows how to talk to SQLite if it is present, but SQLite itself is not "baked in".

When Python is built from source (as I've done on my Mac), if it finds a statically-linkable SQLite library, it will statically link it into the Python exectuable. If a statically-linkable library isn't present, it will attempt to load it dynamically at runtime. If neither is present, Python won't be able to read/write SQLite databases. (On the Mac, this isn't a practical concern since a dynamically loadable SQLite library ships as part of the operating system.)

Most Linux and especially Windows users don't build Python from source. Linux users usually get a precompiled Python from their package manager. Windows users usually download the .msi from python.org or use a precompiled distribution from Enthought or ActiveState. In all of the Pythons I've checked from these sources, SQLite is "baked in". That's very convenient for the user, but for us it also means that the SQLite version is tied to the Python version.

Therfore, the oldest SQLite version we must support is whatever is in the oldest Python we support, and that's Python 2.5.0. The SQLite in that Python is 3.3.4.

SQLite Verions in Various Distributions

Operating SystemPython VersionSQLite Version
Windows XPPython.org's 2.5.0 3.3.4
Windows XPPython.org's 2.5.4 3.3.4
Windows XPEnthought 2.5.4 3.3.4
Ubuntu 8.04ActivePython 2.5.5.7 3.3.4
Ubuntu 8.042.5.2 3.4.2
Ubuntu 8.102.5.2 3.5.9
RHEL 5.42.4.33.3.6
CentOS 5.42.4.33.3.6
Mac OS 10.5.82.5.1 3.4.0
Mac OS 10.6.12.6.1 3.6.12
Mac OS 10.6.42.6.1 3.6.12

On the Mac, SQLite is not statically linked into the system Python but instead lives in /usr/lib.

Our server scion runs RHEL 5.4.

Python < 2.5 usually uses use the 3rd party sqlite module by Gerhard Häring (which became the standard library version in Python 2.5). To get the SQLite version in those Pythons, run this query: SELECT sqlite_version()

Note to self: to get the CentOS version number, execute cat /etc/redhat-release.

Getting SQLite 3.3.4

The source tarball is gone from sqlite.org, but I found copies on six different Web sites, downloaded them all and verified that the MD5 sums matched. One of those copies is attached to this page.

I attached the documentation, too. You can build it yourself if you want with the command make doc.

Attachments (2)

Download all attachments as: .zip