Re: [Chicken-users] DBI

[Jeremy Sydik]

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,  
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
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?
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?
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)
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.

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.

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.

--Jeremy

On Feb 27, 2008, at 2:11 PM, John Cowan wrote:

> Ozzi Lee scripsit:
>
> > Let me know what you think. If someone's already got something  
> > going I'd
> > like to pitch in as well.
>
> I suggest that a row be an a-list, and that null columns be  
> represented
> by being non-existent in the a-list.  If you end up preferring a plain
> list or a vector, then use (void) instead -- I am trying to get this
> standardized as the Chicken representation of SQL's NULL.
>
> In reality, though, I think portability between databases is more
> hypothetical than real.  Projects typically start with one database  
> and
> stick to it, for moving between databases *even if a portability layer
> is in use* turns out to be hard -- all sorts of stuff outside the main
> code base ends up changing (path names, load scripts, whatever).
>
> --
> You let them out again, Old Man Willow!                 John Cowan
> What you be a-thinking of?  You should not be waking!   address@hidden
> Eat earth!  Dig deep!  Drink water!  Go to sleep!
> Bombadil is talking.                                    http://ccil.org/~cowan
>
>
> _______________________________________________
> Chicken-users mailing list
> address@hidden
> http://lists.nongnu.org/mailman/listinfo/chicken-users