Wednesday, December 18, 2013

Ported DBI and DBD-PostgreSQL from GNU Smalltalk to Pharo

We are building a gsmSCF in Pharo. Normally I would use MongoDB and Voyage to store my objects but as a gsmSCF deals with the balance of users I wanted to use something that supports transactions.

Looking at the SQL support in Pharo. I found SqueakDBX, DBXTalk, native Postgres drivers but all of them lacked prepared statement support and one really doesn't want to format queries by hand.

This is when I decided to port GNU Smalltalks DBI framework and the DBD-PostgreSQL driver. It is not the greatest database framework (QtSql is really close to that) but it does support prepared statements and I have running systems that use this framework.

I have used the gst-convert utility to convert the syntax from GST to FileOut syntax as understood by Pharo and converted the use of DateTime to DateAndTime, Dictionary>>#from: to Dictionary>>#newFrom: and some other incompatibilities. I have fixed some other incompatibilities by hand, e.g. >>#subString: aChar changed to >>#subString: aString and >>#nl to >>#cr. This gave me a port of ROE, DBI and DBD-PostgreSQL relatively quickly. I had some issues with Monticello not properly detecting certain extensions and I had to re-save the methodSource to fix that.

I spent the most time porting the DBD-PostgreSQL FFI code from GNU Smalltalk to NativeBoost of Pharo. The easy part could be converted easily. The literal array in Pharo is interesting and the usage in the >>#nbCallOut: is quite funny. The most difficult part was to create a char** for one call-out and type conversation. I had a de-tour using the NBExternalArrayType and then went to use NativeBoost class>>#allocate: and >>#asNBExternalString. It didn't help that when NativeBoost can't convert a parameter and there will be use-less error message. This is specially annoying when a function takes like 6 arguments or more. Anyway, I had some success yesterday night.

In GNU Smalltalk we are using namespaces so we have DBI.Connection as the entry point. In Pharo this is Connection right now but I should call it DBIConnection in the future. A small example is below and with simple tests it looks like there are no memory leaks in the code. I need to make the code work reliable across image save/restore.

 | connection statement result |
connection := Connection connect: 'dbi:PostgreSQL:dbname=aDb' user: nil password: nil.
statement := connection prepare: 'SELECT * from table WHERE col > $1 AND type = $3'.
result := statement executeWithAll: #(1 'abc').
[result atEnd] whileFalse: [
   | row |
   row := result next.
   row at: 'columnName'.


Pierce Ng said...

Hi. Is your port published, say on Smalltalk Hub or SqueakSource3? What is the license?

zecke said...

It is on Smalltalkhub.!/~GST/DBI.