htoolkit-users

[htoolkit-users] HSQL, HList

[Frederik E. <fre...@a5...>]

I wrote some functions which can be used with HSQL to make it a lot
more concise, using the HList concept:

http://ofb.net/~frederik/SqlRow/

If anybody wants to look at it and give feedback. Maybe it could be
incorporated into the library.

Is there anyone on this list?

Frederik

Re: [htoolkit-users] HSQL, HList

[Shae M. E. <sh...@Sc...>]

Frederik Eaton <fre...@a5...> writes:

> Is there anyone on this list?

Me me! I'm here! I haven't gotten around to reading the HList paper though,
so I won't be able to give you intelligent feedback on your code till I do.
-- 
Shae Matijs Erisson - http://www.ScannedInAvian.com/ - Sockmonster once said:
You could switch out the unicycles for badgers, and the game would be the same.

Re: [htoolkit-users] HSQL, HList

[Frederik E. <fre...@a5...>]

On Tue, Feb 22, 2005 at 09:51:35PM +0100, Shae Matijs Erisson wrote:
> Frederik Eaton <fre...@a5...> writes:
> 
> > Is there anyone on this list?
> 
> Me me! I'm here! I haven't gotten around to reading the HList paper though,
> so I won't be able to give you intelligent feedback on your code till I do.

Oh, don't read the whole paper, in fact you probably don't need to
read any of it, most of it goes far beyond what I use, which is pretty
self-explanatory.

 data a :* l = a :. l
 data HNil = Nil

e.g.

 -- x :: Bool :* String :* Int :* HNil
 x = True :. "Hi" :. 4 :. Nil

It's just a way of giving you something like a tuple type, but with
access to the internals so you can make instances based on the
structure. The important part of my module is:

 instance SqlRow HNil where
     getFieldValues s [] = return Nil
     getFieldValues s _ = fail "Wrong number of fields"

 instance (SqlBind a, SqlRow l) => SqlRow (a :* l) where
     getFieldValues s (f:fs) = do
         a <- getFieldValue s f 
         l <- getFieldValues s fs
         return (a :. l)

The paper does other neat things like defining classes for appending
two HList types and for accessing list elements by index (which has to
be encoded as a type-level natural).

-- 
Frederik Eaton                         http://ofb.net/~frederik/

Re: [htoolkit-users] HSQL, HList

[Bjorn B. <d00...@dt...>]

Frederik Eaton wrote:
> I wrote some functions which can be used with HSQL to make it a lot
> more concise, using the HList concept:
>=20
> http://ofb.net/~frederik/SqlRow/
>=20
> If anybody wants to look at it and give feedback. Maybe it could be
> incorporated into the library.

One difference to the current HSQL is that you access record fields by=20
position rather than by name. Not sure whether this is good or bad. One=20
problem is that there is as little type safety now as before, and it's=20
as easy to access a non-existing field.

The nice thing is that you can get back a whole row at a time, and I=20
like the pattern trick in:

getRows stmt >>=3D
         mapM_ (\ (m :. n :. p :. Nil) ->

All in all I think it is a very good demonstration of what you can do=20
with heterogenous lists, and it's not really worse than HSQL when it=20
comes to type-safety.

You might want to have a look at HaskellDB,=20
http://haskelldb.sourceforge.net/ . It uses a similar scheme for record=20
types. It adds a layer of type safety, and you construct the queries=20
using combinators.

You might want to be careful with collectRowsAsync. We have the same=20
thing in HaskellDB, and it turns out to be a problem with some databases=20
if you run a second query without getting all that data from the first=20
one. I think MySQL with the native drivers does it for example, but not=20
with ODBC.

> Is there anyone on this list?

Yes.

/Bj=F6rn

Re: [htoolkit-users] HSQL, HList

[Frederik E. <fre...@a5...>]

On Tue, Feb 22, 2005 at 10:18:03PM +0100, Bjorn Bringert wrote:
> Frederik Eaton wrote:
> >I wrote some functions which can be used with HSQL to make it a lot
> >more concise, using the HList concept:
> >
> >http://ofb.net/~frederik/SqlRow/
> >
> >If anybody wants to look at it and give feedback. Maybe it could be
> >incorporated into the library.
> 
> One difference to the current HSQL is that you access record fields by 
> position rather than by name. Not sure whether this is good or bad. One 
> problem is that there is as little type safety now as before, and it's 
> as easy to access a non-existing field.
> 
> The nice thing is that you can get back a whole row at a time, and I 
> like the pattern trick in:
> 
> getRows stmt >>=
>         mapM_ (\ (m :. n :. p :. Nil) ->
> 
> All in all I think it is a very good demonstration of what you can do 
> with heterogenous lists, and it's not really worse than HSQL when it 
> comes to type-safety.

Thanks.

> You might want to have a look at HaskellDB, 
> http://haskelldb.sourceforge.net/ . It uses a similar scheme for record 
> types. It adds a layer of type safety, and you construct the queries 
> using combinators.
> 
> You might want to be careful with collectRowsAsync. We have the same 
> thing in HaskellDB, and it turns out to be a problem with some databases 
> if you run a second query without getting all that data from the first 
> one. I think MySQL with the native drivers does it for example, but not 
> with ODBC.

About accessing record fields by name vs. position, that was one
reason I wanted to write this. I wasn't sure how to name fields with
expressions such as:

select min(a.s), avg(a.s), max(a.s), sum(a.s) from ...

and felt that it shouldn't be necessary either. Temporary names (X as
a, Y as b) are possible but cumbersome. Positional bindings seem more
lightweight and nimble and in tune with the rest of Haskell - they are
how function parameters are done, after all.

One thing that doesn't come out in the example is that it should also
be a lot easier to do assignments from singleton select statements as
above, with another utility function or two, which can be quite common
in database applications.

(min :. ave :. max :. sum :. Nil) <- doOneRow "select ..."

I've looked at HaskellDB, I was shying away from it because I thought
I needed a more direct mapping to SQL internals.

Thanks for the heads up on collectRowsAsync.

-- 
Frederik Eaton                         http://ofb.net/~frederik/

Re: [htoolkit-users] HSQL, HList

[Frederik E. <fre...@a5...>]

> You might want to be careful with collectRowsAsync. We have the same 
> thing in HaskellDB, and it turns out to be a problem with some databases 
> if you run a second query without getting all that data from the first 
> one. I think MySQL with the native drivers does it for example, but not 
> with ODBC.

What are the symptoms?

What I'm seeing is that if I run closeStatement before reading all the
rows from a query, it goes ahead and reads the rest of the rows
silently, which is bad if there are millions of rows. For instance, if
there is an error, like if I request a field with the wrong type, then
since these are caught by a 'finally' clause in getFieldValue and
trigger a closeStatement, the error message isn't printed (by
'handleSql print' in main) until the whole table is read in, several
minutes later.

I'm trying to get MyODBC installed - this is necessary using MySQL
through ODBC, right? - to see if the behavior is any different, since
you seemed to imply it would be less problematic.

Frederik

-- 
http://ofb.net/~frederik/

Re: [htoolkit-users] HSQL, HList

[Bjorn B. <d00...@dt...>]

Frederik Eaton wrote:
>>You might want to be careful with collectRowsAsync. We have the same=20
>>thing in HaskellDB, and it turns out to be a problem with some database=
s=20
>>if you run a second query without getting all that data from the first=20
>>one. I think MySQL with the native drivers does it for example, but not=
=20
>>with ODBC.
>=20
> What are the symptoms?

If you already have an open result and try to run another query, you=20
will get an expcetion with the MySQL drivers.

> What I'm seeing is that if I run closeStatement before reading all the
> rows from a query, it goes ahead and reads the rest of the rows
> silently, which is bad if there are millions of rows. For instance, if
> there is an error, like if I request a field with the wrong type, then
> since these are caught by a 'finally' clause in getFieldValue and
> trigger a closeStatement, the error message isn't printed (by
> 'handleSql print' in main) until the whole table is read in, several
> minutes later.

That doesn't seem like what you'd expect. Bug in the driver, or HSQL mayb=
e?

> I'm trying to get MyODBC installed - this is necessary using MySQL
> through ODBC, right? - to see if the behavior is any different, since
> you seemed to imply it would be less problematic.

Well, yes and no. You don't get the exception like with the MySQL=20
driver, but the reason for that is that the MyODBC driver seems to fetch=20
the entire result set from the server straight away and keep it locally.=20
So you can have two open queries, but the only thing you gain from=20
laziness is that the data you never use will be in C code, not Haskell,=20
and thus take up less space. But that of course won't help if the=20
results are larger that you local RAM for example.

/Bj=F6rn

Re: [htoolkit-users] HSQL, HList

[Frederik E. <fre...@a5...>]

> If you already have an open result and try to run another query, you 
> will get an expcetion with the MySQL drivers.

OK, this was the subject of my other recent message.

> >What I'm seeing is that if I run closeStatement before reading all the
> >rows from a query, it goes ahead and reads the rest of the rows
> >silently, which is bad if there are millions of rows. For instance, if
> >there is an error, like if I request a field with the wrong type, then
> >since these are caught by a 'finally' clause in getFieldValue and
> >trigger a closeStatement, the error message isn't printed (by
> >'handleSql print' in main) until the whole table is read in, several
> >minutes later.
> 
> That doesn't seem like what you'd expect. Bug in the driver, or HSQL maybe?

I don't know, but the HSQL code looks like it's doing the right thing.
OTOH, MySQL should do better than that...

> >I'm trying to get MyODBC installed - this is necessary using MySQL
> >through ODBC, right? - to see if the behavior is any different, since
> >you seemed to imply it would be less problematic.
> 
> Well, yes and no. You don't get the exception like with the MySQL 
> driver, but the reason for that is that the MyODBC driver seems to fetch 
> the entire result set from the server straight away and keep it locally. 
> So you can have two open queries, but the only thing you gain from 
> laziness is that the data you never use will be in C code, not Haskell, 
> and thus take up less space. But that of course won't help if the 
> results are larger that you local RAM for example.

Ah, that's terrible. So I have no reason to use ODBC.

Frederik

-- 
http://ofb.net/~frederik/