bzlib/dbi updated ==> Re: [plt-scheme] bzlib/dbi error trying to insert a null value

Hi all -

I have incorporate unifying side effects & last inserted id into bzlib/dbi
for spgsql, jsqlite, and jazmysql, and make the changes available via
planet.

You can find the details at
http://weblambda.blogspot.com/2009/11/latest-dbiplt-available-handling-last.html-
enjoy.

Cheers,
yc

On Wed, Nov 4, 2009 at 4:52 PM, YC <yinso.chen at gmail.com> wrote:

> Okay - after some seriously thinking about the issue of last-insert-id &
> side effects, below are my ideas and would love some feedbacks:
>
> *Side Effect Data Structure
> *
> There are 3 options here:
>
>    1. return the underlying side-effect (and re-export the necessary
>    bindings) for each of the drivers
>    2. provide my own unified side-effect object and convert the underling
>    side-effect object onto the first one
>    3. convert the side effect into a record set structure (so you do not
>    need to test whether it's a side effect struct)
>
> The first option's advantage is that if you already have extensive side
> effect testing code you do not have to change much.  The drawback is that
> you'll have more headache/effort to switch between the databases.
>
> The second option's advantage is the reverse of the first one - you'll
> break current code, but you reduce one effort to switch between databases.
>
> The third option unifies the split between record set & side effects.  Its
> advantage is that it removes your need to test the type of the return, but
> the disadvantage is the access of the result is a bit harder.
>
> I can of course provide all 3 approaches, but would be nice to reduce it
> down.  Any thoughts on which one is the best?  My personal thought is #2 or
> #3 (I love the unification aspect of #3 but know this is not generally how
> it is done).
>
> *Last Inserted ID
> *
> There are some general issues with last-inserted-id
>
>    1. it doesn't work for multiple record inserts at the database level
>    1. all databases only return a single value even if you insert multiple
>       records into a table at once
>       2. mysql returns the id for the first record inserted in such case
>       (this feels incorrect)
>       3. sqlite returns the id for the last record inserted in such case
>       2. postgresql is difficult requires you to know the name of the
>    particular sequence object to get the value
>
> Although it does not always work, the fact that single record insert is the
> majority of the usage makes it useful, and in that case, I think the design
> employed by jaz/mysql is the correct one - i.e. *return the value as part
> of the side effect*, regardless which particular options chosen above.
>
> Both jaz/mysql & jaymccarthy/sqlite provides the api to allow this, but not
> schematics/spgsql.  This is for the reason stated above that you'll need to
> know (or derive the sequence) the sequence name in order to determine the
> value, and that this needs to be done with a separate query (which can slow
> things down if you do not need the id).
>
> My question here: should I provide the last-inserted-id for spgsql given
> the constraints?   Or just jazmysql & jsqlite?
>
> I plan on making the changes by next week - so any feedback prior to that
> would be welcome.
>
> Thanks,
> yc
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20091105/d6b7ea1c/attachment.html>