[Haskelldb-users] Fwd: Patch for review: SQL functions and parameters

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

Please ignore this message - my fumble fingers sent it while it was
still being composed ... Complete message to follow ;)


---------- Forwarded message ----------
From: Justin Bailey <jgb...@gm...>
Date: Thu, Apr 3, 2008 at 2:39 PM
Subject: Patch for review: SQL functions and parameters
To: has...@li...


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
 'trim' function in a query, you can define trim 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.

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