SQLite is quite a popular library, particularly on Mac OS X where it’s actually used to implement some system features.

Unfortunately, some of the filesystems on OS X (AFP, for instance) don’t implement advisory locking via the POSIX function fcntl(), which leads SQLite to report that the “database is locked” whenever you try to use it.

Now, SQLite is included with Mac OS X, and Apple have been kind enough to solve this problem for the version that they provide (3.1.3). Unfortunately, that version doesn’t support some of the syntax that gets generated by newer versions of SQLAlchemy, which I’ve been evaluating as part of a new design for my company’s website. So I needed to upgrade to a newer version to make it work, and since my home area is on an AFP share, I needed it to work on that.

The upshot is that I’ve ported Apple’s fix from 3.1.3 to the latest version of SQLite (3.3.6). Here’s a patch. To apply it, download the patch and the sqlite 3.3.6 sources (1.59MB). Then, in Terminal, do something like the following:

alastair$ tar xvzf ~/Desktop/sqlite-3.3.6.tar.gz
sqlite-3.3.6/
sqlite-3.3.6/art/
sqlite-3.3.6/art/tmp/
...
sqlite-3.3.6/publish.sh
alastair$ cd sqlite-3.3.6
alastair$ patch -p1 < ~/Desktop/sqlite-3.3.6-osx.patch

Then build it as normal. Note that if you run make test on an AFP volume, you’ll spot that one or two of the tests will fail. I think this is because of the differences in semantics between POSIX and AFP byte-range locks. If you run them from a local volume instead, they succeed as expected.

If you want the new build to match the version supplied by Apple, remember to configure it with --enable-threadsafe and --enable-tcl to turn on thread-safety and Tcl support.

---

Update 2011-10-14

I’ve removed this patch, as Apple has long since updated SQLite to a newer version.