Latest DBI.plt Available - Handling Last Inserted ID and Side Effects

The newest version of DBI (and the 3 RDBMS drivers) has now been made available on planet - it addresses the issue of side effects and last inserted id.

As usual - they are released under LGPL.

To download & install - use require:

(require (planet bzlib/dbi:1:3) 
         (planet bzlib/dbd-jazmysql:1:2) 
         (planet bzlib/dbd-jsqlite:1:3) 
         (planet bzlib/dbd-spgsql:1:2))

Unifying the side effects and the last inserted id turns out to be a non-trivial task - I have already voiced the options on the plt-scheme list, repeating here for the sake of completeness:

• the underlying drivers returns different values for side effects
• there might be legacy code that are utilizing the underlying side effect results - which can increase effort if porting to another database
• not all drivers provide ways to retrieve all side effect values
• last inserted id does not work consistently when dealing with the multi-records-insert-at-once scenario - luckily this is generally not how insertion is used
• for postgresql - we'll need to derive the underlying table to sequence name mapping in order to determine the last inserted id, and this requires a separate query that you might not want to "pay for" unless you have needs for last inserted id

Based on the above design constraints, I have chosen the following:

• make available multiple drivers for each database - one for different types of the side effect results
• default the side effect results to an unified effect structure, which is inspired by jazmysql
• provide last-inserted-id for the 3 RDBMS drivers (SPGSQL has a special requirement, described below)

The Different Side Effects

There are 3 separate side effect types:

• past-thru-effect - this is the side effect available as a backward compatibility. Basically whatever the side effect objects are returned by the underlying driver are direct returned; and you can use the underlying driver's code to access the values
• effect - this is the unified side effect object and the default going forward
• effect-set - this converts the effect structure into a result set

For example, if you want to make a connection with the first side effect type with bzlib/dbd-spgsql, you can do the following:

(connect 'spgsql/past-thru-effect <rest-of-args> ...) 

And for the other two types:

;; use the effect structure
(connect 'spgsql/effect <rest-of-args> ...)
;; use result set as the effect structure
(connect 'spgsql/effect-set <rest-of-args> ...)

The default is 'spgsql/effect. That means when you pass in 'spgsql as the driver name, you are passing in the equivalent of 'spgsql/effect.

The same goes for the other two drivers:

(connect 'jsqlite/past-thru-effect ...)
(connect 'jsqlite/effect ...) ;; the default; same as 'jsqlite
(connect 'jsqlite/effect-set ...) 

(connect 'jazmysql/past-thru-effect ...)
(connect 'jazmysql/effect ...) ;; the default; same as 'jazmysql
(connect 'jazmysql/effect-set ...) 

If you chooes */past-thru-effect you'll have to use the side effect structures returned by the underlying driver - I won't discuss this since this is meant for backward compatibility - if you are writing new code I would encourage to either use */effect or */effect-set.

The effect Structure

Inspired by jaz/mysql, the effect structure has the following definition:

(define-struct effect 
  (rows ;; the # of rows affected or #f
   insert-id ;; the last inserted id or #f
   status ;; the status of the underlying connection or #f
   warning-count ;; the warning count or #f 
   message ;; the message returned with the query or #f 
   error ;; the error message (or exception object) or #f 
  ))

You can use the appropriate struct accessor functions to access the values if you use the */effect drivers.

In */effect-set drivers, the returned effect object are converted to results, with the first row being the column names of:

• affected rows
• insert id
• status
• warning count
• message
• error

And the second row would contain the value converted from the effect structure, but with #f mapped to '(), based on the convention of the result set's handling of NULL.

Last Inserted ID

For both dbd-jsqlite and dbd-jazmysql, the */effect & */effect-set correctly captures the last inserted ID in the effect structure's insert-id field. They are also correctly returned in the */past-thru-effect version, since the underlying driver directly supports the concept of last inserted id (the jsqlite/past-thru-effect will return the last-inserted-id as a number, and jazmysql/past-thru-effect will return the last-inserted-id contained as part of the side-effect structure).

The dbd-spgsql driver is more complicated, however. The spgsql/past-thru-effect does not return the last-inserted-id, because in postgresql you need to know the underlying sequence object name that the table uses to manages the auto increment, and you also need to make a secondary query, which adds additional overhead.

dbd-spgsql handles this issue by taking in an additional parameter, identified by keyword #:t2s (table to sequence), when you make the connection:

(connect 'spgsql/effect <arg> ... '#:t2s 
         <procedure-to-translate-table-name-to-sequence-name>) 

The #:t2s parameter takes a procedure that takes in a string (the table name) and returns a string (the sequence name). If you supply the parameter, and the query is an insert query, then the driver will help you to automatically make the subsequent query to retrieve the last inserted id. If you do not supply the parameter, then no overhead for accessing the last inserted id will be incurred.

That's it for now. Enjoy.