[Haskell-cafe] Has anyone worked on checking SQL-queries at compile time?

[Mads Lindstrøm]

Hi,

Marc Weber wrote:
> Yes, I know about haskelldb.. But some more advanced optimizations can't
> be expressed.. so I'd like to ask if someone has done some work in the
> other direction not ensuring type safety by using the haskell type
> system to derive SQL queries but to use template haskell to derive
> functions from given SQL queries thereby checking them for syntax error ?
> It be a little bit like SQLJ (Java tool for db connectivity)

I am working on this very issue :) However, it is still in the early
stages. What I am trying to do is to use Template Haskell to connect
(compile-time) to the database and let the database figure out the
correct type of a SQL expression. See this thread
http://www.nabble.com/Using-Template-Haskell-to-make-type-safe-database-access-td17027286.html for more details.

> 
> Eg $(q "SELECT '2', 4") should result in
> [(String, Int)]
> and
> 
> $(q "INSERT INTO foo (2, ?1, ?2)" ) should result in
> 
> Int -> <whatever type ?1 is> -> <whatever type ?2 is> -> IO ()

similar to what I had in mind. Also I have access to bound variables
like:

foo :: Int -> IO ()
foo bar = $(q "INSERT INTO foo (2, ?bar)")

Note my current code supports both options - both anonymous (only
question mark) and question mark followed by the name of a bound
variable.

> 
> Ideally this would not only result in a query but in a prepared
> statement.

Do not do that just yet. Have not really thought about it. I think it
would be trivial for databases which supports making prepared statements
doing compile time and evaluating them doing run-time. I do not know how
common this feature is. I know DB2 on z/OS supports it. Other databases
could be investigated.

To support compile-time prepared statements means that the prepared
statement must not be deleted when the database connection is closed.

Maybe, my current API could be changed to support prepared statements in
made doing run-time. I will have to think a bit about it.

> 
> I have no clue how much RDBMS such as Postgresql provide some help
> determining type of parameters or results without acutally doing a query
> (but bcause Postgresql provides transactions this should not be a
> problem)

Using ODBC you do _not_ have to execute a statement to query its results
types. However, querying types of parameter is optional for ODBC driver
implementers (according to the standard). MySQL do not support it. I
_think_ PostgreSQL do not support it either, but I am still
investigating PostgreSQL.

I have also been investigating SQLite and looking around the web for
other databases. A am afraid it is common to _not_ support querying the
result of parameter types :( Currently DB2 (on Linux) seems to be my
best hope. Also I assume that MS SQL server supports querying parameter
types using ODBC, as they invented ODBC.

> 
> I see the following benefits:
> You can use your existing SQL- Knowledge and don't have to dive into
> deep type hackery yet gettitng as much type safety as possible
> 
> Marc Weber
> _______________________________________________

If you are interested in contributing to the project, I would be happy
to put more information on-line. I currently have some working code and
sketchy unit tests. Also I have been taking notes about the various
databases I have used.


Greetings,

Mads