[Haskell-cafe] ANNOUNCE: hdbi-1.0.0 and hdbi-postgresql-1.0.0

[Bardur Arantsson]

On 2013-07-31 09:22, Alexey Uimanov wrote:
>>
>> Regard parameterized SQL: It might be worth using named parameters (e.g.
>> ":foo" and ":bar" or something like that) rather than "?" as
>> placeholders in SQL/prepared SQL. This will make it slightly more
>> flexible if you need to provide different SQL strings for different
>> databases, but want to reuse the code which does the actual running of
>> the SQL. It's also more flexible if you need to repeat parameters -- the
>> latter is typical with PostgreSQL if you want to emulate
>> "update-or-insert" in a single SQL statement
>>
> 
> Named parameters might be more flexible, but it is need to think hard about
> how to implement this.
> If you are using named parameters you need to pass not just list [SqlValue]
> as parameters,
> but Map Text SqlValue or something. So named parameters will not be
> compatible with unnamed and will need
> separate query parser.
> 

The use case I'm thinking of it something like this:

  reportSQL :: DatabaseType -> SQL
  reportSQL MySQL = "
     SELECT ... custName = :custName ...
     INTERSECTION
     SELECT ... custName = :custName
     "
  reportSQL PostgreSQL = "
     SELECT ... AS cust
       WHERE cust.custName = :custName
         FROM SELECT ... AS foo
           WHERE foo.custName = cust.custName
     "

For this fictitious example we imagine that PostgreSQL can handle a
nested query of some particular shape where we need an INTERSECTION
query in MySQL. Obviously this is a made up example, but you get the
idea. The point is that the MySQL query may need to refer to the
":custName" parameter multiple times whereas the PostgreSQL one doesn't.
Similarly the positions in the SQL may need to be different.

You perhaps still want to have a way to run both variants using the
exact same code:

   runReport :: DatabaseType -> Text -> IO whatever
   runReport databaseType customerName = do
     result <- runSQLWithParameters (reportSQL databaseType)
                   [("custName", customerName)]
     ... do stuff with result ...
     return whatever

Of course this being Haskell you can always use higher-order functions
(e.g. a function DatabaseType -> Text -> IO QueryResult which
encompasses the runSQLWithParameters *and* reportSQL function, but then
you're mixing up the running of the query with the query itself) for
similar purposes, but I tend to find named parameters of this type to be
quite useful for readability.

As Kirill mentioned, you can also use numbered parameters, but I tend to
like named parameters for readability.

Implementation should be reasonably simple: Replace all ":xyz" (or
whatever syntax you choose) parameters in input with "$n" and maintain a
map which tells you which parameter "$1", "$2", etc. correspond to.

Anyway, this is an issue I've sometimes run across with JDBC (which uses
?) in particular and it can be very annoying. Perhaps the best thing in
Haskell would be to just avoid raw SQL entirely in favor of combinators,
but then you often end up with suboptimal SQL which can't really exploit
all the features of your chosen database. Even so it would be nice to
have a DB interface/library that can hit that sweet spot where you can
write your own SQL but your program won't be too tied to a single DB
backend (modulo the concrete SQL).

> 
>> Regarding migrations: If you haven't already, please have a look at
>> Liquibase (http://www.liquibase.org/documentation/index.html) before
>> attempting to implement migrations. The most important attributes of
>> Liquibase are:
>>
> 
> What I am trying to implement is not a new migration system, but just the
> common interface for
> simple schema actions, here is my in-mind draft:
> 
> newtype TableName = TableName Text
> 
[--snip--]
> This typeclasses must provide database-independent schema introspection and
> changing.
> Migration system can be anything you want.
> 

Ah, OK, I see I just misinterpreted the bit in the package description
about migrations then :).

You might end up having a little trouble reconciling metadata from the
different database backends, but certainly there must be *some* useful
common subset of table/index/etc. metadata :).

> I also have the idea do not throw the exceptions in IO but return  (Either
> SqlError a) from
> all the Connection and Statement methods for safe data processing. What do
> you think about ?
> 

I don't think I'm qualified to have an opinion either way, but perhaps


http://www.randomhacks.net/articles/2007/03/10/haskell-8-ways-to-report-errors

and particularly

   http://hackage.haskell.org/package/errors

can serve as insipration :).

Regards,