haskelldb-users

[Haskelldb-users] Resend: patch for review - SQL functions and parameters

[Justin B. <jgb...@gm...>]

All,

 Attached you'll  find my patch for using SQL functions and parameters
 in queries. By functions, I mean the ability to define a function
 prototype that will be used in a query. For example, if you need the
'lower' function in a query, you can define lower as:

 >  lower :: Expr a -> Expr (Maybe String)
 >  lower str = func "lower" str

 The function can then be used in a query:

  qry1 = do
    tbl <- table ...
    project $ col1 << lower (tbl ! col2)

 If the function is used in an inappropriate place, a compile time
error occurs. The arguments to the function do not have to be
expressions:

 >  data DatePart = Day | Century deriving Show

 >  datePart :: DatePart -> Expr (Maybe CalendarTime) -> Expr (Maybe Int)
 >  datePart date col = func "date_part" (constant $ show date) col

 Aggregates are easy to define:

  >  every :: Expr Bool -> ExprAggr Bool
  >  every col = func "every" col

 Because haskelldb implements aggregates to always take one argument
and only one argument, a compile time error occurs if an aggregate
with a different number of arguments is defined.

 One problem with this mechanism is the type signatures can be too
strict. For example, lower above cannot be used where an "Expr String"
 or even "Expr BStrN" (i.e., a bounded string) is expected. I'm not
sure if this library should solve that or if coercion functions should
 be defined by the user. Suggestions welcome.

 Parameters allow generated queries to be used with the "prepared
statement" facility of most databases. Any parameter defined is
 rendered as a "?" in the subsequent SQL, and it is expected the user
will again supply appropriate values when the SQL is executed. Both
named and position parameters can be defined:

 qry1 = do
  ...
  restrict (tbl1 ! col1 .==. namedParam "my_param" constNull)
  restrict (tbl1 ! col2 .==. param constNull)

 When a parameter is defined, a default value must be given for it.
This feature is probably not useful to many, but does allows queries
 to be generated which do not contain any placeholders.'constNull'
just  means the parameter has a NULL default value.

 After SQL has been generated, the "name" of parameters is lost. It is
 very important that parameters can be retrieved in the order they
will  appear in the final query. The function 'queryParameters',
exported  from HaskellDB, does this. It returns a list of [Param]
values. Param  is just (Either Int String), so a named parameter is
represented by  (Right "...") while a positional parameter is (Left
<val>).

 In summary the patch provides:

  * Allows SQL functions to be defined and used of SQL functions in queries
  * Allows positional and named parameters to be used in queries
  * Some minor bug fixes.

 Comments welcome!

 Justin

 p.s. I developed this against PostgreSQL; please let me know if I
have  introduced compatibility problems.

Re: [Haskelldb-users] Resend: patch for review - SQL functions and parameters

[Bjorn B. <bj...@br...>]

On Thu, Apr 3, 2008 at 11:53 PM, Justin Bailey <jgb...@gm...> wrote:
> All,
>
>   Attached you'll  find my patch for using SQL functions and parameters
>   in queries. By functions, I mean the ability to define a function
>   prototype that will be used in a query. For example, if you need the
>  'lower' function in a query, you can define lower as:
>
>   >  lower :: Expr a -> Expr (Maybe String)
>   >  lower str = func "lower" str
>
>   The function can then be used in a query:
>
>   qry1 = do
>     tbl <- table ...
>     project $ col1 << lower (tbl ! col2)
>
>   If the function is used in an inappropriate place, a compile time
>  error occurs. The arguments to the function do not have to be
>  expressions:
>
>   >  data DatePart = Day | Century deriving Show
>
>   >  datePart :: DatePart -> Expr (Maybe CalendarTime) -> Expr (Maybe Int)
>   >  datePart date col = func "date_part" (constant $ show date) col
>
>   Aggregates are easy to define:
>
>   >  every :: Expr Bool -> ExprAggr Bool
>   >  every col = func "every" col
>
>   Because haskelldb implements aggregates to always take one argument
>  and only one argument, a compile time error occurs if an aggregate
>  with a different number of arguments is defined.
>
>   One problem with this mechanism is the type signatures can be too
>  strict. For example, lower above cannot be used where an "Expr String"
>   or even "Expr BStrN" (i.e., a bounded string) is expected. I'm not
>  sure if this library should solve that or if coercion functions should
>   be defined by the user. Suggestions welcome.
>
>   Parameters allow generated queries to be used with the "prepared
>  statement" facility of most databases. Any parameter defined is
>   rendered as a "?" in the subsequent SQL, and it is expected the user
>  will again supply appropriate values when the SQL is executed. Both
>  named and position parameters can be defined:
>
>   qry1 = do
>   ...
>   restrict (tbl1 ! col1 .==. namedParam "my_param" constNull)
>   restrict (tbl1 ! col2 .==. param constNull)
>
>   When a parameter is defined, a default value must be given for it.
>  This feature is probably not useful to many, but does allows queries
>   to be generated which do not contain any placeholders.'constNull'
>  just  means the parameter has a NULL default value.
>
>   After SQL has been generated, the "name" of parameters is lost. It is
>   very important that parameters can be retrieved in the order they
>  will  appear in the final query. The function 'queryParameters',
>  exported  from HaskellDB, does this. It returns a list of [Param]
>  values. Param  is just (Either Int String), so a named parameter is
>  represented by  (Right "...") while a positional parameter is (Left
>  <val>).
>
>   In summary the patch provides:
>
>   * Allows SQL functions to be defined and used of SQL functions in queries
>   * Allows positional and named parameters to be used in queries
>   * Some minor bug fixes.
>
>   Comments welcome!
>
>   Justin
>
>   p.s. I developed this against PostgreSQL; please let me know if I
>  have  introduced compatibility problems.

Hi Justin,

this looks great. How do you substitute in values for parameters in queries?

There could be an alternative approach to parameters that lets you
make them type safe by using lambda expression. I'm not sure that this
is implementable, but it ought to be. Here's how I imagine it working:

selectById :: Int -> Query (Rel ...)
selectById i =
  do t <- table foo
        restrict (t ! id .==. constant i)
        ...

test :: Database -> IO [...]
test db =
  do q <- prepare db selectById
        queryBind db q 42


Here, prepare and queryBind are something along these lines (but more
general to support multiple arguments, and with some type class
constraints):

prepare :: Database -> (a -> Query (Rel b)) -> PreparedQuery (a -> Rel b)

queryBind :: Database -> PreparedQuery (a -> Rel b) -> a -> IO [b]


/Björn

Re: [Haskelldb-users] Resend: patch for review - SQL functions and parameters

[Justin B. <jgb...@gm...>]

On Fri, Apr 4, 2008 at 8:28 AM, Bjorn Bringert <bj...@br...> wrote:
>  this looks great. How do you substitute in values for parameters in queries?

That's something I didn't look at, but is needed. Since I'm using
haskelldb just to generate SQL I haven't concentrated on the runtime
side.  However, see below ..

>  Here, prepare and queryBind are something along these lines (but more
>  general to support multiple arguments, and with some type class
>  constraints):
>
>  prepare :: Database -> (a -> Query (Rel b)) -> PreparedQuery (a -> Rel b)
>
>  queryBind :: Database -> PreparedQuery (a -> Rel b) -> a -> IO [b]

I like this, and I wonder if Peter's code (mentioned in another
thread) addresses it. I'd be glad to take a shot at unifying the two
approaches, once his patch is in.

In any case, do you think the parameters should show up in the type of
Query (Rel b)? Any ideas how that could look, and how to make sure
queries can still be rendered to SQL w/o runtime values (so I can
still get my SQL w/o having to execute it)?

Justin

Re: [Haskelldb-users] Resend: patch for review - SQL functions and parameters

[Bjorn B. <bj...@br...>]

On Fri, Apr 4, 2008 at 5:51 PM, Justin Bailey <jgb...@gm...> wrote:
> On Fri, Apr 4, 2008 at 8:28 AM, Bjorn Bringert <bj...@br...> wrote:
>  >  this looks great. How do you substitute in values for parameters in queries?
>
>  That's something I didn't look at, but is needed. Since I'm using
>  haskelldb just to generate SQL I haven't concentrated on the runtime
>  side.  However, see below ..
>
>
>  >  Here, prepare and queryBind are something along these lines (but more
>  >  general to support multiple arguments, and with some type class
>  >  constraints):
>  >
>  >  prepare :: Database -> (a -> Query (Rel b)) -> PreparedQuery (a -> Rel b)
>  >
>  >  queryBind :: Database -> PreparedQuery (a -> Rel b) -> a -> IO [b]
>
>  I like this, and I wonder if Peter's code (mentioned in another
>  thread) addresses it. I'd be glad to take a shot at unifying the two
>  approaches, once his patch is in.
>
>  In any case, do you think the parameters should show up in the type of
>  Query (Rel b)? Any ideas how that could look, and how to make sure
>  queries can still be rendered to SQL w/o runtime values (so I can
>  still get my SQL w/o having to execute it)?

Hmm, I'm not sure that the Query should have the parameters in it,
since a Query is different from a PreparedQuery.

To render a parametrized query as SQL, you could have a function
(needs a better name):

showParamSql :: (a -> Query (Rel b)) -> String

This would have to invent names for the parameters, so it needs some
trickery inside. Or maybe, forcing you to supply a name for each
parameter:

showParamSql :: (a -> Query (Rel b)) -> String -> String

(All of the above needs to be extended with type-trickery to handle
multiple parameters)

/Björn

Re: [Haskelldb-users] Resend: patch for review - SQL functions and parameters

[Peter G. <pe...@gm...>]

On 04/04/2008, at 10:51 PM, Justin Bailey wrote:

> I like this, and I wonder if Peter's code (mentioned in another
> thread) addresses it. I'd be glad to take a shot at unifying the two
> approaches, once his patch is in.

Err, patches to what? I've only hacked HSQL, not HaskellDB. As I said  
before, I'm in the process of removing my dependency on HaskellDB...

I asked Bjorn about it as he was/is the de facto HSQL maintainer.

cheers
peter