Re: [Chicken-users] DBI

[Ozzi Lee]

Jeremy Sydik wrote:
> I've been working a little in the same vein, but it's still pretty tied 
> to a specific project
> My needs are pretty light, but I'm finding that the most useful 
> functions seem to be
>
> sql:query-list
> and
> sql:query-alist
>
> I started out thinking in terms of Perl/Python type DBI, but I'm 
> questioning that now.  I'd like
>  to see a lightweight layer that means I don't have to remember as many 
> specifics of the
>  specific DB egg -- I agree with John that, most of the time, plugging 
> in multiple DBs isn't
>  that relevant.  I have one project that does it, but it uses such basic 
> SQL-92 that I'm getting
>  away with supporting multiple DBs.  In terms of staying lightweight, 

Agreed, light weight is my goal as well.

> I'd like to look at the
> suggested functions:
>
> dbi:connect => I'm currently doing this as individual 
> (driver:get-connection)'s that produce
>     a function that takes a message to support the rest of the dbi.  
> This seems like a potential
>     win

I'm not sure I understand. Do you mean having functions like the following:

dbi:connect-mysql
dbi:connect-postgresql

and etc?

> dbi:query => like I mentioned above, I'm using query-list and 
> query-alist.  I also see value for
>     query-vector, but haven't done much with it.  The question is, would 
> we be better having
>     individual functions or a single dbi:query that takes a (defaulted) 
> argument that specifies
>     the output?

I'd like to have a single representation for a row, myself, so we can 
just have query return whatever we decide that should be, unless there 
are compelling reasons to have multiple output formats. Perhaps there 
are performance issues of alists vs lists vs vectors that would come 
into play? I have no idea.


> dbi:num-rows => I've found that I almost always end up using (length) 
> instead.  Probably not
>     optimal, but I've not had a major problem here.
> dbi:fetch-row => I've not used this either, but I'm usually pushing my 
> queried list into a for-each
>     or a map.  the question is, what is our specific use case?

num-rows and fetch are actually there to support an implementation of 
representing query results as Streams that I'm considering, mostly.

> dbi:query-fold, dbi:query-map, query-for-each.  I thought about 
> implementing these, but I
>     haven't been able to think of the use case that makes them necessary 
> in the presence
>     Scheme's fold, map, and for-each, other than as shorthand (which 
> could be define'd
>     in place in a heartbeat for anyone who wants it)

These would be here for performance. To use the Scheme map function, we 
have to build up a list of all the resulting rows first. With a 
dbi:query-map function, we wouldn't.

This is the also the same reason I was thinking about a Stream 
implementation. If that ended up being workable we wouldn't need these.

> dbi:insert-id  Should this come from a function, or should it be 
> returned as a response to
>     dbi:query?  I wouldn't mind seeing the query handler be smart enough 
> that if my query
>     involves INSERT and i've inserted a row that becomes ID 42, that the 
> response would
>     be something like '((rows-affected 1) (insert-id 42)) Certainly, 
> we'd want to look at what
>     we'd want to normalize these labels to, but that's not a huge issue.
>
>     The related question is whether singleton response values (like 
> rows-affected with
>     nothing else) should return as a singleton (a)list or as a numeric 
> value --> My vote would
>     be to return the singleton (a)list  to simplify the conditional 
> checking the caller needs
>     to use.


I'll add this to the open questions on the Wiki, I'm not sure what we 
should do about return values in general.

> This leaves
> (dbi:connect driver-proc [[connection params]])
>     In my case, I'm using SRFI-89 style define* for named params and 
> defaults.  What do
>     other people think of this approach?
> (dbi:query conn str [[output type option?]])  With alist, the output 
> type option probably isn't
>     that important depending on how NULL is handled.  It might also be 
> nice to make query
>     smart enough to give sensible responses for non SELECT queries, so 
> we'd need to
>     decide what the alist-names for these should look like
> Is this over-simplified?
>     
> Row representation.  I think my preference would be using alists as the 
> the representation
> Thinking about it, Scheme already gives us everything we need to get 
> value lists, vectors,
> and hash-tables if we start from alists, so it seems like the better 
> choice.

Agreed.

> I've tended to let null be '(), but that partly comes from liking the 
> look of
> (null? (alist-ref 'field result))  I'm not entirely comfortable with 
> leaving the
> value entirely absent simply because the mapping I mention to value
> lists and vectors becomes more problematic.  That said, my usage of
> value lists and vectors is limited enough that I'm not that tied to it 
> either.

That's true, conversion to other representations from the alist will 
have to be considered.