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

[Ivan Shmakov]

> > > > > Thomas Christian Chust <address@hidden> writes:

>>>> 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.

>>> I favor a variant of this: introduce a singleton type for SQL NULL
>>> values, and map #t and #f inbound to 1 and 0 respectively (but not
>>> outbound, of course).  Ideally, the singleton type would be the
>>> same in all SQL eggs rather than SQLite-specific:

[...]

>> May we have a library to implement SQL-like ternary logic as well?
>> E. g.: [...]

> Hello,

> adding a few functions and macros to implement ternary logic
> sounds good to me.

       ... And these should be in a separate library, so that they
       could be used along with any SQL engine.

> But the idea to use (void) to represent NULL still appeals to me
> because of their similar meaning

       I feel that the semantics of an ``unspecified value'' is, well,
       underspecified.  Is it guaranteed, e. g., that (eq? (void)
       (void)) will always be true?  (So that (sql-null? o) function
       could be implemented.)

> and because no additional memory and garbage collection time would be
> wasted for another useless singleton class instance ;-)

       Won't it complicate debugging?

> Another idea that just came to my mind is the following: One could
> add a parameter sqlite3:null-value to the SQLite3 egg, which
> defaults (for backwards compatibility reasons) to #f and defines
> the value returned from SQLite3 functions to represent NULL.

       It could be the solution.

       However, my opinion is that the support for representing SQL
       NULL with #f (or any other arbitrary value) is not quite a good
       thing and, eventually, should be dropped.  Since I view it,
       thus, as a temporary solution, I'd recommend a special SQL NULL
       value be used as the default value for the parameter, still
       allowing the old code to invoke ``compatibility mode'' with
       `parameterize'.

       (Of course, one shouldn't try to ``set'' this parameter, since
       it may break one part of code or another.)

> A specialization for the third parameter of sqlite3:bind! on the
> class <top> would be added which would check whether (eq? v
> (sqlite3:null-value))

       Unfortunately, this won't work with the (sql-null) definition
       I've suggested.

       May we use (or (sql-null? v) (eq? v (sqlite3:null-value)))
       instead?

> and execute the two parameter variant of sqlite3:bind! in that case,
> or generate an error in any other case. Additionally one could offer
> a singleton class <sqlite3:null> and some ternary logic support for
> convenience.

> I suppose that this is the most flexible solution suggested so far:
> If you want nearly the same behaviour as now, no action is
> required. If you want a NULL singleton, add the line
> (sqlite3:null-value (void)) or (sqlite3:null-value (make
> <sqlite3:null>)) to the top of your program and you're done.

       ... Provided that there's not a line of code, not in program,
       nor in a library used, which assumes #f is a SQL NULL.

> You can still add whatever specialization you want to sqlite3:bind!
> for booleans. And whatever value you choose to represent NULL, unless
> you add some specializer conflicting with your choice to
> sqlite3:bind!, you are guaranteed that the NULL value can be passed
> both in and out of SQLite3.

> How do you like this approach?

       I view this approach as a temporary way to get around
       compatibility issues, and it sounds like a reasonable one.

       I don't really feel like using an arbitrary Scheme object as a
       SQL NULL value, just the way I won't like using an arbitrary
       Scheme object as an EOF value, or as an empty list.  In
       particular, I feel it could easily be abused, raising an
       incompatibility between different libraries, which could assume
       different objects to correspond to SQL NULLs.

       I still feel like my proposal to have the following interface
       implemented.

   Function: sql-null

       Return an object, corresponding to a SQL NULL value.  The object
       is guaranteed to be of a type disjoint from all of the R5RS'
       standard types.

       It's unspecified whether the values returned by this function
       will be `eq?' to each other:

 (eq? (sql-null) (sql-null))
 => unspecified.

   Function: sql-null? object

       Return #t if OBJECT is a SQL NULL object.  Return #f otherwise.

   Function: sql-not object

       Return OBJECT if OBJECT is a SQL NULL object.  Return the value
       of `(not OBJECT)' otherwise.

 (sql-not (sql-null))  => SQL NULL;
 (sql-not 'a)          => #f;
 (sql-not #f)          => #t;
 (let ((null (sql-null)))
   (eq? null (sql-not null)))
 => #t.

   Syntax:   sql-and test-1 ...

       The TEST expressions are evaluated from left to right, and the
       value of the first expression that evaluates to a false value is
       returned, and any remaining TESTs are not evaluated.

       If there were no expressions to evaluate to a false value, the
       value of any of the expressions to evaluate to a SQL NULL is
       returned.  If there were no such expressions as well, #t is
       returned.

 (sql-and 1)           => 1;
 (sql-and #t (sql-null))
 => SQL NULL;
 (sql-and #f (sql-null))
 => #f.

       In the absence of the expressions that evaluate to SQL NULL
       values, the semantics is the same as for `(and test-1 ...)'.

   Syntax:   sql-or  test-1 ...

       The TEST expressions are evaluated from left to right, and the
       value of the first expression that evaluates to a value, other
       than SQL NULL and a false value (a ``SQL true'' value), is
       returned, and any remaining TESTs are not evaluated.

       If there were no expressions to evaluate to a SQL true value,
       the value of any of the expressions to evaluate to a SQL NULL is
       returned.  If there were no such expressions as well, #f is
       returned.

       In the absence of the expressions that evaluate to SQL NULL
       values, the semantics is the same as for `(or test-1 ...)'.

       Please note that the implementation of `sql-or' and `sql-and'
       I've sent previously was incorrect.  The following
       implementation seems to threat SQL NULL values correctly.

(define-syntax sql-or
 (syntax-rules ()
   ((sql-or a ...) (sql-or:null #f a ...))))

(define-syntax sql-or:null
 (syntax-rules ()
   ((sql-or:null null)   null)
   ((sql-or:null null a b ...)
    (cond ((sql-null? a) (sql-or:null a    b ...))
          ((not a)       (sql-or:null null b ...))
          (else          a)))))

(define-syntax sql-and
 (syntax-rules ()
   ((sql-and a ...) (sql-and:null #t a ...))))

(define-syntax sql-and:null
 (syntax-rules ()
   ((sql-and:null null)   null)
   ((sql-and:null null a b ...)
    (cond ((sql-null? a) (sql-and:null a    b ...))
          (a             (sql-and:null null b ...))
          (else          a)))))