[Haskell-cafe] Using Template Haskell to make type-safe database access

[Mads Lindstrøm]

Hi,

Wouter Swierstra wrote:
> Hi Mads,
> 
> > I was wondering if anybody had experimented with using Template
> > Haskell
> > (TH) and ordinary SQL to make type-safe database access?
> > 
> 
> 
> I know HaskellDB, for example, does something quite similar. There's a
> preprocessor that generates a Haskell file with a Haskell
> representation of the types of the database's tables. You could of
> course replace this with a TH function. There are two very nice papers
> about the design of HaskellDB:
> 
> 
> http://research.microsoft.com/users/daan/download/papers/dsec.ps
> 
> 
> http://haskelldb.sourceforge.net/haskelldb.pdf
> 

Thanks.

> 
> I think there may a bit of problem with the approach you suggest: as
> the type returned by the query is computed by the SQL server (if I
> understand you correctly), it's very hard to do anything with the
> result of the query - the Haskell compiler has no idea what type the
> result has, so you can't do anything with it. I think it makes much
> more sense to bookkeep type information on the Haskell side.

But you can ask the SQL server for the type of the result. In the TH
function you could:

1) Call ODBC function SQLPrepare
(http://msdn.microsoft.com/en-us/library/ms710926(VS.85).aspx) . This
just prepares a statement. It do _not_ execute it.

2) Call ODBC function SQLNumParams
(http://msdn.microsoft.com/en-us/library/ms715409(VS.85).aspx) . This
returns the number of parameters.

3) Call ODBC function SQLDescribeParam
(http://msdn.microsoft.com/en-us/library/ms710188(VS.85).aspx) for each
parameter to get type information.

I imagine that this would all be done at compile time by the TH
function. At run time we would call SQLPrepare (again) and SQLExecute.
We obtained type information at compile time, so it should all be quite
type safe.

HDBC almost supports this with describeResult
(http://software.complete.org/static/hdbc/doc/Database-HDBC.html#v%
3AdescribeResult ) and prepare
(http://software.complete.org/static/hdbc/doc/Database-HDBC.html#v%
3Aprepare ), except that we need to execute a prepared SQL statement
before calling describeResult (see link to describeResult). Calling
functions that could potentially change data at compile time seems like
a bad idea. For some people, maybe even a deal breaker.

HSQL has similar functions. But I have not got it to work yet, so that I
could test it.

We would of cause need to map the type information returned by
SQLDescribeParam to Haskell data types. But is seems to me that HSQL and
HDBC can already do that.

Of cause it all requires that the database have identical metadata at
run and compile -time. Either using the same database or a copy. Though
one should note that HaskellDB has the same disadvantage. Actually it do
not seem much of a disadvantage it all, as most code accessing SQL
databases depends on database metadata anyway.



Greetings,

Mads Lindstrøm


> 
> 
> Hope this helps,
> 
> 
>   Wouter