Changes between Version 16 and Version 17 of SqliteVersions

Aug 15, 2012, 7:15:03 PM (8 years ago)



  • SqliteVersions

    v16 v17  
    11= SQLite Versions =
    3 '''Our code must be compatible with SQLite 3.3.4''' which was released in
    4 February 2006. That means we can't use any of the features added since then,
    5 such as --
     3== Summary ==
     4'''Our code must be compatible with SQLite 3.5.9''' which was released in
     5May 2008.
    7  * virtual tables (3.3.7)
    8  * full text search (3.3.8)
    9  * `IF EXISTS` on `CREATE/DROP VIEW` (3.3.8)
    10  * `randomblob()` and `hex()` (3.3.13)
    11  * `SUBSTR()` 3rd param becomes optional (3.5.2)
    12  * `group_concat() ` (3.5.4)
    13  * foreign key constraints (3.6.19)
     7== What Determines the Minimum ==
     9SQLite is often "baked into" Python, meaning that it is an integral part of
     10Python and can't be upgraded. Therefore, the oldest SQLite version we
     11must support is whatever is in
     12the oldest Python we support, and that's Python 2.6.0. The SQLite version
     13in that Python varies, but the oldest appears to be in the Windows
     14version of Python 2.6.0. That Python reports that it uses SQLite 3.5.9.
    17 The fact that we can't use `randomblob()` and `hex()` is especially
    18 significant for us since they can be used to get SQLite to generate UUIDs. In
    19 fact,
    20 [ it's stated in the SQLite docs]
    21 that UUID generation is the main reason those functions were added.
     16For reference, Python 2.6.0 on OS X 10.6 reports SQLite 3.6.1, but SQLite
     17is an integral part of OS X and I don't think Python includes it statically
     18on this platform. It uses the version that OS X supplies, and I imagine
     19that could be upgraded over time.
    23 == The Background ==
     21I didn't test any Linux versions of Python 2.6.0. I don't think
     22any package managers provide that version. They provide a more advanced
     23version of the 2.6 series, like 2.6.4.
    25 Many C-based libraries are dynamically loaded at runtime, and SQLite can
    26 be too. However, it's quite small and so can be statically linked into
    27 applications. In fact, that's a very typical usage scenario for SQLite.
    28 Python is a good (and pertinent) example of this, as SQLite is commonly
    29 built into Python.
     25== Do We Care? ==
    31 === Huh? I Thought SQLite Was Shipped with Python... ===
     27Not so much, anymore.
    33 The ''source'' distribution of Python >= 2.5 ships with SQLite ''bindings''.
    34 That means that Python knows how to talk to SQLite if it is present,
    35 but SQLite itself is not "baked in".
     29Vespa versions up to and including 0.5.1 supported
     30Python 2.5. In Python 2.5.0, the oldest SQLite was 3.3.4 (released in
     31Feb 2006).
    37 When Python is built from source, it will statically link SQLite if it finds
    38 a statically-linkable SQLite library. If a statically-linkable library isn't present, it
    39 will attempt to load it dynamically at runtime. If
    40 neither is present, Python won't be able to read/write SQLite databases.
    41 (On the Mac, this isn't a practical concern since a dynamically loadable
    42 SQLite library ships as part of the operating system.)
     33Early in Vespa's development, there were some features
     34in SQLite that I wanted to use that had been added after 3.3.4, so
     35the SQLite version mattered a lot. For instance, SQLite added
     36features for generating UUIDs, but not until 3.3.13. That meant
     37that generating UUIDs at the database level was out of the question.
    44 Most users don't build Python from source.
    45 Linux users usually get a precompiled Python from their package manager.
    46 OS X and Windows users usually download a precompiled binary from ``
    47 or use
    48 a precompiled distribution from Enthought or !ActiveState. In all of the
    49 Pythons I've checked from these sources, SQLite is "baked in". That's
    50 very convenient for the user, but for us it also means that
    51 '''the SQLite version is tied to the Python version'''.
     39I learned to live without those features (which was fine, really).
    53 Therfore, the oldest SQLite version we must support is whatever is in
    54 the oldest Python we support, and that's Python 2.5.0. The SQLite in that
    55 Python is 3.3.4.
     41As of this writing, the database is mature. Any new tables are
     42likely to follow the pattern we've established which deliberately
     43avoids using SQLite's "newer" features.
    57 == SQLite Verions in Various Distributions ==
    59 ||'''Operating System'''||Python Version'''||'''SQLite Version'''||
    60 ||Windows XP||'s 2.5.0|| 3.3.4||
    61 ||Windows XP||'s 2.5.4|| 3.3.4||
    62 ||Windows XP||Enthought 2.5.4|| 3.3.4||
    63 ||Ubuntu 8.04||ActivePython|| 3.3.4||
    64 ||Ubuntu 8.04||2.5.2|| 3.4.2||
    65 ||Ubuntu 8.10||2.5.2|| 3.5.9||
    66 ||RHEL 5.4||2.4.3||3.3.6||
    67 ||RHEL 6.0||2.6.5||3.6.20||
    68 ||CentOS 5.4||2.4.3||3.3.6||
    69 ||Mac OS 10.5.8||2.5.1|| 3.4.0||
    70 ||Mac OS 10.6.1||2.6.1|| 3.6.12||
    71 ||Mac OS 10.6.4||2.6.1|| 3.6.12||
    73 On the Mac, SQLite is not statically linked into the system Python but instead lives in `/usr/lib`.
    75 Our server scion runs RHEL 5.4.
    77 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()`
    79 Note to self: to get the CentOS version number, execute `cat /etc/redhat-release`.
    81 == Getting SQLite 3.3.4 ==
    83 The source tarball is gone from ``, but I found copies on six different Web sites,
    84 downloaded them all and verified that the MD5 sums matched. One
    85 of those copies is attached to this page.
    87 I attached the documentation, too. You can build it yourself if you want
    88 with the command `make doc`.
     45To see the limitations that were present when we used Python 2.5,
     46read [wiki:SqliteVersions?version=16 version 16 of this page].