[Chicken-users] Request for comments on the SQLite3 egg API

[Thomas Christian Chust]

Hello,

the handling of Scheme booleans and SQL NULL values in the SQLite3 egg
seems to be a constant source of confusion. Thus I would like to ask if
a majority of users would prefer a different approach.

Currently, the SQLite3 egg converts NULL values returned from SQLite3
into #f but doesn't accept Scheme booleans as inputs for SQLite3
routines by default. Thus if you want to bind an SQLite3 statement
parameter to NULL, you have to use the lower level statement API or add
your own specialization on boolean inputs for the sqlite3:bind! method.

The following alternatives would be possible:

  1. Introduce a new separate singleton type for SQL NULL values and
     refrain from using booleans altogether, because there is no SQL
     boolean type in SQLite3.

     Of course a specialization of the tree parameter variant of
     sqlite3:bind! would be added for the null type which is
     equivalent to the two parameter version, in order to make it
     possible to pass a null value to sqlite3:exec instead of only
     being able to set a null value using the lower level statement
     API.

     This would still allow the user to define a sqlite3:bind!
     specialization on booleans if she wanted to define some encoding
     for them.

     Possible conversion from returned data to booleans has to be
     done manually.


  2. Keep the data extraction routines returning #f to represent NULL
     but add an arbitrary default specialization of sqlite3:bind! on
     booleans that represents #f as NULL and #t as 1.

     Just as in alternative 1 it would now be possible to specify
     null values as parameters to sqlite3:exec.

     The user could no longer define her own encoding for booleans on
     the SQL side, but in- and output of booleans to and from SQL
     would work automagically correctly as long as the database didn't
     use an encoding different from

       false = NULL
       true = some number != 0.


  3. Keep everything as it is: Data extraction returns #f for NULL,
     but data input doesn't support booleans by default.

     It is easily possible for the user of the egg to transform this
     into alternative 2 with very few lines of code.


I have discussed alternative 1 with Ivan Shmakov in private
communication. It seems to be conceptually cleanest and it would be a
good choice if more compatibility with databases that have an SQL
boolean type was desired, because otherwise confusion may arise between
returned #f values that really mean false and returned #f values that
really mean NULL. Of course this approach would lose some efficiency and
as the situation is with the present SQLite3 API, a #f return value
always means NULL and the new approach wouldn't be more practical than
the present one.

Alternative 2 is pragmatical, loses some flexibility, but will work in
most cases and will probably save me from further questions on the
mailing list, why #f is an invalid input to sqlite3:exec ;-)
Additionally it would be compatible with most existing SQLite3 code.

Everybody is welcome to make more suggestions how to solve the problem
in a clever way or to vote for one of the alternatives :-)

cu,
Thomas