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 /usr/local/:
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
Inside setup.cfg the include_dirs and library_dirs need to be updated to point to /usr/local.
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:
dbsql test.db
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:
python
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