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.

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:

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.