Sunday 11 May 2008

Using OpenDBX with Squeak

A team of students from UTN (National Technological University in Argentina) co-ordinated by Estaban Lorenzano has just been doing some work on SqueakDBX, a package to allow Squeak to access OpenDBX functionality, which gives a lighter-weight alternative to ODBC for connecting to databases including Firebird, Interbase, MS SQL Server, MySQL, Oracle, PostgreSQL, SQLite, SQLite3 and Sybase.

This uses the FFI (Foreign Function Interface) package (available through Package Universe), and requires access to the libopendbx library. Now I had a bit of fun and games working out how to do this on Mac OS X, so here's the magic that worked for me.

Firstly, I was interested in using PostgreSQL to compare with the Squeak postgres support which is already installed on my machine. I downloaded the OpenDBX source -- it's a UNIX tool, so distributed as source needing compilation -- you open a Terminal and change into the newly downloaded directory, and execute configure command as directed by the README: ./configure --with-backends="sqlite3 pgsql", but in my case this barfed because it couldn't find my postgres libraries. This was solved by manually pointing to the postgres library and include directory, as follows:

CPPFLAGS="-I/usr/local/pgsql/include/" LDFLAGS="-L/usr/local/pgsql/lib/" ./configure --with-backends="sqlite3 pgsql"

You'll see that I also set things up for SQLite3 as it's installed by default on OS X, and is increasingly being used to hold configuration files in OS X and Firefox. I know that there's a SQLite3 package for Squeak already, but I thought it would be interesting to compare the two.

Anyway, the configure stage now worked perfectly, and sudo make install installed the libraries for me. All I needed to do now was make Squeak FFI see the libraries. Ha! After a lot of poking about, I found this email from Jon McIntosh, which advised that creating a symbolic link to the library "in the right place is helpful". Thanks for the pointer John, but where's the right place???

Well, after a bit of experimentation, I found that I needed to create the link in the Resources directory of  the SqueakVM package:

cd /Applications/Squeak/Squeak
ln -s /usr/local/lib/libopendbx.dylib opendbx

And that's it. I'm now able to access my PostgreSQL database with code like this:

conn := DBXConnection new
backend: DBXBackend postgresql;
  connect: '' port: 'x';
  open: 'x' name: 'x' password: 'x' method: 0.
  resultSet := conn query: 'select * from airline'.
DBXTranscript show: resultSet.
conn disconnect.
Smalltalk garbageCollect.

It's still very early days for the project, and it is not yet production-ready, so I've not tried doing anything much with it, but I notice that Esteban and colleagues appear to be building in GLORP support from the outset, so it's worth keeping an eye on this project.


Esteban said...

Thanks for the attention to our project!
Just two short observations:
1) This projects is not just mine, but for a little team of students from UTN (National Technological University)... I'm just cordinating and helping a little, the main effort is on the students.
2) Is great you were able to test the connection (in fact, you did almost the same lines I did), but at this time (and for a couple of months more), the code in the repository is very unstable and experimental... it is not ready for use.

Once again, thanks for your words, and stay in contact, we hope to provide some reliable code very soon :)

Esteban Lorenzano

Michael Davies said...

Thanks for the comments Esteban. I'll correct the information in the post. I found out about your project when reading the DB thread on the mailing list. It's great to see work going on to improve DB access for squeak - I hope you guys are able to find sufficient common ground to co-ordinate your efforts. I look forward to hearing how it goes!