« Previous | Next »

NBSQLite3 - NativeBoost SQLite for Pharo

01 Mar 2014

I like SQLite. SQLite is small, fast and reliable. As mentioned in this old blog post, I was extending the Squeaksource SQLite FFI wrapper to support parameter binding. Since then, Torsten Bergmann has also picked it up and now tends to it on SmalltalkHub.

Taking the chance to learn NativeBoost, with hints from Masashi Umezawa's PunQLite, I took my SQLite FFI changes and created NBSQLite3. NBSQLite3's small test suite runs successfully on Pharo 2.0 (OSX, Linux) and the current Pharo 3.0 beta on OSX.

Here's a multithreading example:

| db db2 sem blocking blocked |

db := NBSQLite3BaseConnection on: 'file::memory:?cache=shared'.
db2 := NBSQLite3BaseConnection on: 'file::memory:?cache=shared'.
sem := Semaphore new.	

Transcript open; clear.	

blocking := [ :x :sema |
    Transcript show: 'Thread-1: begin exclusive...'; cr; flush.
    [ x open.
      x execute: 'begin exclusive'.
      sema wait.
      Transcript show: 'Thread-1: releasing exclusive lock.'; cr; flush.
      x execute: 'rollback' 
    ] ensure: [ x close ] 
] newProcessWith: (Array with: db with: sem).

blocking resume.
Processor yield.

blocked := [ :x |
    Transcript show: 'Thread-2: create table ...'; cr; flush.
    [[ x open. x execute: 'create table if not exists x (xk integer)' ]
         on: NBSQLite3Locked do: [ :ex | Transcript show: ex messageText; cr; flush ]
    ] ensure: [ x close ]
] newProcessWith: (Array with: db2).

blocked resume.
Processor yield.

sem signal.

To run this example, evaluate "NBSQLite3Example multithreading". The output looks thusly:

Thread-1: begin exclusive...
Thread-2: create table ...
database schema is locked: main
Thread-1: releasing exclusive lock.

The SQLite shared library is compiled with -DSQLITE_USE_URI=1 and -DSQLITE_THREADSAFE=2.

Tags: SQLite