As of Oracle's 11gR2 release of BerkeleyDB, the library has included a SQL API which is fully compatible with SQLite3. BerkeleyDB can even be compiled with support for SQLite's full-text search and r-tree extensions. There's a good whitepaper that Oracle published detailing the performance of their implementation versus SQLite. To summarize, since Berkeley supports page-level locking as opposed to database-level locking, it can push quite a few more transactions per second, making it a better fit for write-heavy applications (the one area sqlite suffers, IMO). Additionally, Berkeley makes fewer syscalls due to the way it blocks as opposed to SQLite's use of busy-locks.
This post is partly for me so I remember how I did this, and partly for anyone else interested in trying out Berkeley's SQL support. plug: I enjoy using SQLite for small projects, if you're interested you might check out peewee, a lightweight ORM with some fun SQLite extensions.
I'm testing this on a fresh 64-bit Ubuntu 12.04 EC2 instance. To compile, we'll need a C compiler, mercurial and python libs. If you already have this stuff, feel free to skip to the next section.
sudo apt-get update --fix-missing sudo apt-get install build-essential python-dev mercurial
Grab a copy of the source code (all versions).
Its rather infuriating, but you may have to register for an account to get the
source. Compile it and install it to
wget http://download.oracle.com/berkeley-db/db-5.1.29.tar.gz tar xzf db-5.1.29.tar.gz cd db-5.1.29/build_unix export CFLAGS='-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -fPIC' ../dist/configure --enable-static --disable-shared --enable-sql --enable-sql-compat make sudo make prefix=/usr/local/ install
Next grab a copy of the pysqlite2 driver.
cd ~/bin/ hg clone http://code.google.com/p/pysqlite/ cd pysqlite
library_dirs need to be updated to point to
By default these values are supplied but are commented out. Edit
setup.cfg by hand
or execute the following to uncomment the default values:
sed -i "s|#||g" setup.cfg
Now we're ready to build the extension:
python setup.py build
The extension will be stored in
build/lib.linux-x86_64-2.7/pysqlite2/ -- you can either
symlink this directory into your python-path or install the library globally:
sudo python setup.py install
To make sure we're using the right libraries, use the
dbsql utility to fire up
a SQLite-compatible shell:
Execute a couple queries to create some test data:
create table testing (id integer not null primary key, data text); insert into testing (data) values ('test1'); insert into testing (data) values ('test2'); .quit
Now let's fire up python and see if we can talk to the database we just created:
Here is the python code from an interactive shell session:
>>> from pysqlite2 import dbapi2 as sqlite >>> conn = sqlite.connect('test.db') >>> curs = conn.cursor() >>> res = curs.execute('select * from testing;') >>> res.fetchall() [(1, u'test1'), (2, u'test2')]
You should see the two rows we inserted earlier, indicating everything is working correctly.
Commenting has been closed, but please feel free to contact me