Using Template Haskell to make type-safe database access

[Mads Lindstrøm]

Hi,

I was wondering if anybody had experimented with using Template Haskell
(TH) and ordinary SQL to make type-safe database access?

To clarify what I am thinking about I will sketch how it could be done.

The TH function should take two inputs. SQL (as a string) and a database
source name (DSN). It should return an IO action as output.

The TH-function should:

1. Connect to the database using the DSN
2. Ask the database which types will be returned from the expression
3. Build an IO action which can be used to execute the SQL at run-time.
The action could return the result as a (lazy) list. Due to step two we
can make the returned values type-safe.



Greetings,

Mads Lindstrøm


_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Wouter Swierstra]

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

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.

Hope this helps,

  Wouter

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[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



_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Wouter Swierstra]

Hi Mads,

>> 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:

Thanks for your interesting reply.

I'd forgotten that you can do I/O in TH's quotation monad. I agree  
that you can ask the database server for the type that an SQL  
expression will return. I don't understand metaprogramming enough to  
see how computing types with TH effects the rest of your program.

Here's a concrete example. Suppose you have a query q that, when  
performed, will return a table storing integers. I can see how you can  
ask the SQL server for the type of the query, parse the response, and  
compute the Haskell type "[Int]". I'm not sure how to sum the integers  
returned by the query *in Haskell* (I know SQL can sum numbers too,  
but this is a simple example). What would happen when you apply  
Haskell's sum function to the result of the query? Does TH do enough  
compile time execution to see that the result is well-typed?

Having the SQL server compute types for you does have other drawbacks,  
I think. For example, suppose your query projects out a field that  
does not exist. An error like that will only get caught once you ask  
the server for the type of your SQL expression. If you keep track of  
the types in Haskell, you can catch these errors earlier; Haskell's  
type system can pinpoint which part of the query is accessing the  
wrong field. I feel that if you really care about the type of your  
queries, you should guarantee type correctness by construction, rather  
than check it as an afterthought.

> 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.

Perhaps I should explain my own thoughts on the subject a bit better.  
I got interested in this problem because I think it makes a nice  
example of dependent types "in the real world" - you really want to  
compute the *type* of a table based on the *value* of an SQL DESCRIBE.  
Nicolas Oury and I have written a draft paper describing some of our  
ideas:

http://www.cs.nott.ac.uk/~wss/Publications/ThePowerOfPi.pdf

Any comments are very welcome! Our proposal is not as nice as it could  
be (we would really like to have quotient types), but I hope it hints  
at what is possible.

   Wouter

This message has been checked for viruses but the contents of an attachment
may still contain software viruses, which could damage your computer system:
you are advised to perform your own checks. Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Mads Lindstrøm]

Hi Wouter

Wouter Swierstra wrote:
> Here's a concrete example. Suppose you have a query q that, when  
> performed, will return a table storing integers. I can see how you can  
> ask the SQL server for the type of the query, parse the response, and  
> compute the Haskell type "[Int]". I'm not sure how to sum the integers  
> returned by the query *in Haskell* (I know SQL can sum numbers too,  
> but this is a simple example). What would happen when you apply  
> Haskell's sum function to the result of the query? Does TH do enough  
> compile time execution to see that the result is well-typed?

Not only pictures, but also code can say more than a thousands words.
Therefore, I have been implementing a proof of concept. The code is
attached in two files - SqlExpr.hs and UseSqlExpr.hs. The former
contains two SQL expressions + Haskell code. The latter is the Template
Haskell (TH) code that makes it possible to type-safely access the
database.

UseSqlExpr.hs is a lot easier to understand than SqlExpr.hs. So if you
only have time to look at one of them, look at UseSqlExpr.hs. The reason
SqlExpr.hs is harder to understand is not just because it is longer, but
also because TH is difficult. At least TH was difficult for me. It might
just be because I have never worked with anything like TH before (have
not learned Lisp yet :( ). It remained me of going from OO to FP. You
have to change how you think.

Your example of fetching a [Int] and take there sum is shown in
UseSqlExpr.hs.

The output from running UseSqlExpr.hs (on my computer) is:

[1,2,3,4]
[(1,"WikiSysop",""),(2,"Mads","Mads Lindstr\195\184m"),(3,"Madstest","Bob"),(4,"Test2","Test 2")]
Sum is: 10

>
> Having the SQL server compute types for you does have other drawbacks,  
> I think. For example, suppose your query projects out a field that  
> does not exist. An error like that will only get caught once you ask  
> the server for the type of your SQL expression. If you keep track of  
> the types in Haskell, you can catch these errors earlier; Haskell's  
> type system can pinpoint which part of the query is accessing the  
> wrong field. I feel that if you really care about the type of your  
> queries, you should guarantee type correctness by construction, rather  
> than check it as an afterthought.

But the SQL database will output a meaningful error message. And TH is
asking the server at compile time. Thus, the user can also get the error
message at compile time. TH is used as part of the compilation process.
I _think_ it would be fair to say it occurs concurrently with type
checking (or maybe intermittently). Thus the user do not get the error
message later than with a type based approach.

If you, with the currently implemented proof of concept, name a
non-existing field in your SQL you get:

<compile time output>
UseSqlExpr.hs:22:6:
    Exception when trying to run compile-time code:
      Exception when trying "executing prepared statement" : execute execute: ["1054: [MySQL][ODBC 3.51 Driver][mysqld-5.0.32-Debian_7etch5-log]Unknown column 'duser_id' in 'field list'"]
      Code: compileSql
              "DSN=MySQL_DSN;USER=StocksDaemon;" "SELECT duser_id FROM user;"
    In the expression:
        $[splice](compileSql
                    "DSN=MySQL_DSN;USER=StocksDaemon;" "SELECT duser_id FROM user;")
          c
    In the definition of `selectIntegerList':
        selectIntegerList c = $[splice](compileSql
                                          "DSN=MySQL_DSN;USER=StocksDaemon;"
                                          "SELECT duser_id FROM user;")
                                c
make: *** [all] Fejl 1
</compile time output>

ok, there is some noise. But at the end of line three it says "Unknown
column 'duser_id'". Also with a little more work I could properly
improve the output.

> Perhaps I should explain my own thoughts on the subject a bit better.  
> I got interested in this problem because I think it makes a nice  
> example of dependent types "in the real world" - you really want to  

But won't you end up implementing all the functionality of an SQL
parser? While possible, it does seem like a huge job. With a TH solution
you will safe a lot of work.

Also, almost every software developer already knows SQL. And the few
that do not, will likely have to learn SQL if they are to do substantial
work with databases. Whereas if you implement a type based solution a
developer will have to learn how to use your library. A library that
will be a lot more complex to learn than what I am proposing (assuming
the developer already knows SQL).

> compute the *type* of a table based on the *value* of an SQL DESCRIBE.  
> Nicolas Oury and I have written a draft paper describing some of our  
> ideas:
>
> http://www.cs.nott.ac.uk/~wss/Publications/ThePowerOfPi.pdf
>

I have not read the paper yet, as I have been busy coding. Plus I have a
day job. But I did read the first couple of pages and so far the paper
seems very interesting. When time permits I will read the rest.
Hopefully this weekend.

> Any comments are very welcome! Our proposal is not as nice as it could  
> be (we would really like to have quotient types), but I hope it hints  
> at what is possible.


Greetings,

Mads Lindstørm


[SqlExpr.hs]

---

{-# LANGUAGE TemplateHaskell #-}

module SqlExpr where

import Language.Haskell.TH

import Database.HDBC
import Database.HDBC.ODBC

compileSql :: String -> String -> ExpQ
compileSql dsn sqlExpr =
    do desc <- runIO (connectDB dsn sqlExpr)
       [| \dsn -> do rows <- fetchRows dsn sqlExpr
                     return $ map ( $(fromRow desc) ) rows |]

-- Evaluated at run-time
fetchRows :: Connection -> String -> IO [[SqlValue]]
fetchRows c sqlExpr =
    do preStmt <- prepare c sqlExpr          `myCatch` "calling prepare"
       execute preStmt []                    `myCatch` "executing prepared statement"
       fetchAllRows preStmt                  `myCatch` "fetch all rows"

-- Evaluated both compile & runtime
myCatch :: IO a -> String -> IO a      
myCatch command doing =
    command `catchSql` (\e -> error ("Exception when trying \"" ++ doing ++ "\" : " ++ seErrorMsg e))

-- The rest is evaluated only at compile-time
connectDB :: String -> String -> IO [SqlTypeId]
connectDB dsn sqlExpr =
    do c <- connectODBC dsn                  `myCatch` "connecting to DB"
       preStmt <- prepare c sqlExpr          `myCatch` "calling prepare"
       execute preStmt []                    `myCatch` "executing prepared statement"
       y <- fetchRow preStmt                 `myCatch` "fecthing a row"
       description <- describeResult preStmt `myCatch` "describing result"
       let desc = map (colType . snd) description
       disconnect c                          `myCatch` "disconnecting"
       return desc

fromRow :: [SqlTypeId] -> ExpQ
fromRow xs = do es <- mapM fromColumn xs
                names <- mapM (\i -> newName ("x" ++ show i)) [0..(length es - 1)]
                return $ LamE [ListP (map VarP names)] (TupE $ map (\(e, n) -> AppE e (VarE n)) $ zip es names)

fromColumn :: SqlTypeId -> ExpQ
fromColumn SqlIntegerT = [| readInteger |]
fromColumn SqlVarCharT = [| readString |]
-- missing a lot of types here - but this is just a proff of concept

readInteger :: SqlValue -> Integer
readInteger (SqlString s) = read s

readString :: SqlValue -> String
readString (SqlString s) = s

---

[UseSqlExpr.hs]

---

{-# LANGUAGE TemplateHaskell #-}

module Main where

import SqlExpr

import Database.HDBC
import Database.HDBC.ODBC

main :: IO ()
main = do connection <- connectODBC "DSN=MySQL_DSN;USER=StocksDaemon;"
          xs <- selectIntegerList connection
          ys <- selectTupleList connection
          print xs
          print ys
          let mySum :: Integer
              mySum = sum xs
          putStrLn ("Sum is: " ++ (show mySum))

selectIntegerList :: Connection -> IO [Integer]
selectIntegerList c =
    $(compileSql "DSN=MySQL_DSN;USER=StocksDaemon;"
      "SELECT user_id FROM user;") c

selectTupleList :: Connection -> IO [(Integer, String, String)]
selectTupleList c =
    $(compileSql "DSN=MySQL_DSN;USER=StocksDaemon;"
      "SELECT user_id, user_name, user_real_name FROM user;") c

---

[makefile]

---

all:
        - rm *.o *.hi UseSqlExpr *~
        ghc -fglasgow-exts --make -Wall UseSqlExpr.hs

#-ddump-splices

---

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Bulat Ziganshin-2]

Hello Mads,

Thursday, May 8, 2008, 1:24:05 AM, you wrote:

> also because TH is difficult. At least TH was difficult for me. It might
> just be because I have never worked with anything like TH before (have

no, TH is dificult by itself. if you have spare time - read about
metalua, which implements the same idea in Lua environment. it's
simple and straightforward, and even allows to easily change syntax.
one possible reason of TH difficulty may be that Haskell is
strict-typed language

--
Best regards,
 Bulat                            mailto:Bulat.Ziganshin@...

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Wouter Swierstra]

Hi Mads,

> Not only pictures, but also code can say more than a thousands words.
> Therefore, I have been implementing a proof of concept. The code is
> attached in two files.

Nice! I have to admit, it's much nicer than I expected it to be. Just  
out of curiousity, what happens when you write:

selectTupleList :: Connection -> IO [Integer]

instead of

selectTupleList :: Connection -> IO [(Integer, String, String)]

What kind of error message do you get? More specifically, is this  
error caught statically or dynamically.

The only other limitation I can think of, would be in the situation  
where you don't have compile-time access to the database, e.g.  
developing software for a client with a database that can only be  
accessed from their intranet. I have no idea how much of a limitation  
that is.

> ok, there is some noise. But at the end of line three it says "Unknown
> column 'duser_id'". Also with a little more work I could properly
> improve the output.

The message is a bit verbose, but if you ignore the noise, it gives  
pretty good feedback about what's wrong. Good.

>> Perhaps I should explain my own thoughts on the subject a bit better.
>> I got interested in this problem because I think it makes a nice
>> example of dependent types "in the real world" - you really want to
>
> But won't you end up implementing all the functionality of an SQL
> parser? While possible, it does seem like a huge job. With a TH  
> solution
> you will safe a lot of work.

Yes - but parsing the result of an SQL describe statement is pretty  
easy.

> A library that
> will be a lot more complex to learn than what I am proposing (assuming
> the developer already knows SQL).

Hmm. This is a rather sticky point. One might also argue that Haskell  
developers have to learn SQL to use the solution you propose. I'm not  
particularly convinced. Both approaches have their merits I think.

Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's  
a really clever and approachable guy - and he knows a lot more about  
interfacing with databases than I do.

Kind regards,

   Wouter


This message has been checked for viruses but the contents of an attachment
may still contain software viruses, which could damage your computer system:
you are advised to perform your own checks. Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Mads Lindstrøm]

Hi Wouter,

Wouter Swierstra wrote:

> Nice! I have to admit, it's much nicer than I expected it to be. Just  
> out of curiousity, what happens when you write:
>
> selectTupleList :: Connection -> IO [Integer]
>
> instead of
>
> selectTupleList :: Connection -> IO [(Integer, String, String)]
>
> What kind of error message do you get? More specifically, is this  
> error caught statically or dynamically.

The type annotation in UseSqlExpr.hs was just for the reader. The
compiler can infer the types completely. Thus when I make the suggested
change I get a compile time error. It looks like this:

UseSqlExpr.hs:27:6:
    Couldn't match expected type `Integer'
           against inferred type `(Integer, String, String)'
      Expected type: IO [Integer]
      Inferred type: IO [(Integer, String, String)]
    In the expression:
        (return
       $ (map
            (\ [x0[a2ZY], x1[a2ZZ], x2[a300]]
                 -> (readInteger x0[a2ZY],
                     readString x1[a2ZZ],
                     readString x2[a300]))
            rows[a2ZX]))
    In the expression:
        do rows[a2ZX] <- fetchRows
                           dsn[a2ZW]
                           ['S', 'E', 'L', 'E', 'C', 'T', ' ', 'u', 's', 'e', 'r', '_', 'i',
                            'd', ',', ' ', 'u', 's', 'e', 'r', '_', 'n', 'a', 'm', 'e', ',',
                            ' ', 'u', 's', 'e', 'r', '_', 'r', 'e', 'a', 'l', '_', 'n', 'a',
                            'm', 'e', ' ', 'F', 'R', 'O', 'M', ' ', 'u', 's', 'e', 'r', ';']
           (return
          $ (map
               (\ [x0[a2ZY], x1[a2ZZ], x2[a300]]
                    -> (readInteger x0[a2ZY],
                        readString x1[a2ZZ],
                        readString x2[a300]))
               rows[a2ZX]))
make: *** [all] Fejl 1

>
> The only other limitation I can think of, would be in the situation  
> where you don't have compile-time access to the database, e.g.  
> developing software for a client with a database that can only be  
> accessed from their intranet. I have no idea how much of a limitation  
> that is.

True, but this limitation is only relevant when you do not have access
to the production database or a database with identical metadata. How
often do people develop like that? How are they testing? I have a hard
time picturing a setup without a test database with identical metadata
to the production database.

> >> Perhaps I should explain my own thoughts on the subject a bit better.
> >> I got interested in this problem because I think it makes a nice
> >> example of dependent types "in the real world" - you really want to
> >
> > But won't you end up implementing all the functionality of an SQL
> > parser? While possible, it does seem like a huge job. With a TH  
> > solution
> > you will safe a lot of work.
>
> Yes - but parsing the result of an SQL describe statement is pretty  
> easy.

ok.

>
> > A library that
> > will be a lot more complex to learn than what I am proposing (assuming
> > the developer already knows SQL).
>
> Hmm. This is a rather sticky point. One might also argue that Haskell  
> developers have to learn SQL to use the solution you propose. I'm not  
> particularly convinced. Both approaches have their merits I think.

Yes. I was _not_ making what you could call a strong argument. I was
assuming that most (Haskell) developers knew SQL anyway. I have no data
to back it up. Just my gut feeling.

To be fair I should mention a couple of drawbacks with the TH-based
approach. While SQL got static typing, it is not really as powerful as
it could be. For example if you do "select sum(...) from ..." the type
system will tell you that a set of values are returned. In reality this
set will never have more than one member. Your proposal would be able to
return a Float in stead of a [Float].

Another advantage your proposal (and disadvantage of the TH based one)
would be that it can abstract over variances in different database
implementation. That is, you could translate to SQL depending on SQL
backend. This would be really nice. But I guess it would also be a big
task.

>
> Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's  
> a really clever and approachable guy - and he knows a lot more about  
> interfacing with databases than I do.
>
> Kind regards,
>
>    Wouter

/Mads Lindstrøm



_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Bjorn Bringert]

On Thu, May 8, 2008 at 5:32 PM, Mads Lindstrøm <mads_lindstroem@...> wrote:

> Hi Wouter,
>
>  Wouter Swierstra wrote:
>
>
> > Nice! I have to admit, it's much nicer than I expected it to be. Just
>  > out of curiousity, what happens when you write:
>  >
>  > selectTupleList :: Connection -> IO [Integer]
>  >
>  > instead of
>  >
>  > selectTupleList :: Connection -> IO [(Integer, String, String)]
>  >
>  > What kind of error message do you get? More specifically, is this
>  > error caught statically or dynamically.
>
>  The type annotation in UseSqlExpr.hs was just for the reader. The
>  compiler can infer the types completely. Thus when I make the suggested
>  change I get a compile time error. It looks like this:
>
>  UseSqlExpr.hs:27:6:
>     Couldn't match expected type `Integer'
>            against inferred type `(Integer, String, String)'
>       Expected type: IO [Integer]
>       Inferred type: IO [(Integer, String, String)]
>     In the expression:
>         (return
>        $ (map
>             (\ [x0[a2ZY], x1[a2ZZ], x2[a300]]
>                  -> (readInteger x0[a2ZY],
>                      readString x1[a2ZZ],
>                      readString x2[a300]))
>             rows[a2ZX]))
>     In the expression:
>         do rows[a2ZX] <- fetchRows
>                            dsn[a2ZW]
>                            ['S', 'E', 'L', 'E', 'C', 'T', ' ', 'u', 's', 'e', 'r', '_', 'i',
>                             'd', ',', ' ', 'u', 's', 'e', 'r', '_', 'n', 'a', 'm', 'e', ',',
>                             ' ', 'u', 's', 'e', 'r', '_', 'r', 'e', 'a', 'l', '_', 'n', 'a',
>                             'm', 'e', ' ', 'F', 'R', 'O', 'M', ' ', 'u', 's', 'e', 'r', ';']
>            (return
>           $ (map
>                (\ [x0[a2ZY], x1[a2ZZ], x2[a300]]
>                     -> (readInteger x0[a2ZY],
>                         readString x1[a2ZZ],
>                         readString x2[a300]))
>                rows[a2ZX]))
>  make: *** [all] Fejl 1
>
>
>  >
>  > The only other limitation I can think of, would be in the situation
>  > where you don't have compile-time access to the database, e.g.
>  > developing software for a client with a database that can only be
>  > accessed from their intranet. I have no idea how much of a limitation
>  > that is.
>
>  True, but this limitation is only relevant when you do not have access
>  to the production database or a database with identical metadata. How
>  often do people develop like that? How are they testing? I have a hard
>  time picturing a setup without a test database with identical metadata
>  to the production database.
>
>
>  > >> Perhaps I should explain my own thoughts on the subject a bit better.
>  > >> I got interested in this problem because I think it makes a nice
>  > >> example of dependent types "in the real world" - you really want to
>  > >
>  > > But won't you end up implementing all the functionality of an SQL
>  > > parser? While possible, it does seem like a huge job. With a TH
>  > > solution
>  > > you will safe a lot of work.
>  >
>  > Yes - but parsing the result of an SQL describe statement is pretty
>  > easy.
>  ok.
>
>
>  >
>  > > A library that
>  > > will be a lot more complex to learn than what I am proposing (assuming
>  > > the developer already knows SQL).
>  >
>  > Hmm. This is a rather sticky point. One might also argue that Haskell
>  > developers have to learn SQL to use the solution you propose. I'm not
>  > particularly convinced. Both approaches have their merits I think.
>
>  Yes. I was _not_ making what you could call a strong argument. I was
>  assuming that most (Haskell) developers knew SQL anyway. I have no data
>  to back it up. Just my gut feeling.
>
>  To be fair I should mention a couple of drawbacks with the TH-based
>  approach. While SQL got static typing, it is not really as powerful as
>  it could be. For example if you do "select sum(...) from ..." the type
>  system will tell you that a set of values are returned. In reality this
>  set will never have more than one member. Your proposal would be able to
>  return a Float in stead of a [Float].
>
>  Another advantage your proposal (and disadvantage of the TH based one)
>  would be that it can abstract over variances in different database
>  implementation. That is, you could translate to SQL depending on SQL
>  backend. This would be really nice. But I guess it would also be a big
>  task.
>
>
>  >
>  > Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's
>  > a really clever and approachable guy - and he knows a lot more about
>  > interfacing with databases than I do.

I guess I'll just have to reply then :-)

Mads: Preparing the statement and asking the DB about the type at
compile is a great idea! I've never thought of that. Please consider
completing this and packaging it as a library.

I can't really see any major problems with this approach, other than
the obivious "changing schema" problem that it shares with HaskellDB.
Of course there are some things that it won't catch, like the
singleton results from aggregates, but it does go a long way towards a
statically safe DB interface.

I think that the main disadvantages compared to HaskellDB are that:
- It won't let you write your own query combinators.
- The program becomes dependent on the exact SQL dialect you chose to use.

On the other hand it addresses some of the problems with HaskellDB:
- Poorly optimized queries.
- Missing SQL features (e.g. outer joins).
- Poor support for DB-specific features.
- Difficult to understand type errors.

/Björn

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Mads Lindstrøm]

Hi

Bjorn Bringert wrote:

> Mads: Preparing the statement and asking the DB about the type at
> compile is a great idea! I've never thought of that. Please consider
> completing this and packaging it as a library.

Thanks for the nice remark. And I will begin completing the idea, as
soon I have packaged up and wrote a little tutorial about my other
project (SybWidget). I already started that about three weeks ago, so it
should be finished soon.


Greetings,

Mads Lindstrøm



_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe@...
http://www.haskell.org/mailman/listinfo/haskell-cafe