Re: [Haskelldb-users] Possible patch: Including SQL functions in queries

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

Hi Justin,

On Thu, Mar 20, 2008 at 12:06 AM, Justin Bailey <jgb...@gm...> wrote:
> All,
>
>  A feature haskelldb is lacking is a way to use SQL functions in
>  queries which are not defined by the library. For example, the "trim"
>  function is used frequently but can only be included in a haskelldb
>  query through the 'literal' combinator or by writing your own function
>  which creates the appropriate PrimExpr value.

Yes, that would be nice.


>  What I'd like to be able to do is use SQL functions in a type-safe
>  way, just like those included in the library. For example, a trim
>  function would look like:
>
>   trim :: Expr (Maybe a) -> Expr (Maybe String) -- Nulls are still null

Hmm, what if you want to apply this to a NOT NULL value? Should trim
be overloaded? Or maybe there should be a some kind of lifting
function to handle Maybes?


>  To use the function in a project is as simple as:
>
>   project $ rawNameField << customers ! Customers.name #
>                name << trim (customers ! Customers.name)
>
>
>  Some functions take multiple arguments. For example. 'rpad', which
>  takes a column, a padding length, and an optional padding character.
>  Notice not all arguments are expressions:
>
>   rpad :: Expr a -> Expr Int -> Maybe String -> Expr (Maybe String)
>
>  Sometimes the aggregate expressions provided by haskelldb aren't
>  enough. The 'every' function is an aggregate found on postgresql:
>
>   every :: Expr Bool -> ExprAggr Bool
>
>  The code below allows these functions to be implemented in terms of
>  two combinators - func and arg. It is intended to be included in the
>  Query module, and only func and arg would exported. Questions I'd like
>  answered:
>
>   * Is the approach over-engineered? Is there a simpler way?
>   * Is the feature useful?
>   * Comments on the implementation?
>
>  After the code the bodies of the functions above are given.
>
>   -- Used to construct type-safe function definitions with
>   -- arbitraryly typed exprssions. See func and arg to use these.
>   -- The data and instances below are modeled on RecNil/RecCons from
>   -- HDBRec.
>   data ExprNil = ExprNil
>   data ExprCons a b = ExprCons a b
>
>   instance ToPrimExprs ExprNil where
>     toPrimExprs ~ExprNil = []
>
>   instance (ExprC e, ToPrimExprs r) => ToPrimExprs (ExprCons (e a) r) where
>     toPrimExprs ~(ExprCons e r) = primExpr e : toPrimExprs r
>
>   class (ExprC e) => MakeFunc e r where
>   {- | Combinator which can be used to define SQL functions which will
>   appear in queries. Each argument for the function is specified by the arg
>   combinator. All arguments must be chained together via function composition.
>   Examples include:
>
>     lower :: Expr a -> Expr (Maybe String)
>     lower str = func "lower" $ arg str
>
>   The arguments to the function do not have to be Expr if they can
>   be converted to Expr:
>
>     data DatePart = Day | Century deriving Show
>
>     datePart :: DatePart -> Expr (Maybe CalendarTime) -> Expr (Maybe Int)
>     datePart date col = func "date_part" $ arg (constant $ show date) . arg col
>
>   Aggregate functions can also be defined:
>
>     every :: Expr Bool -> ExprAggr Bool
>     every col = func "every" $ arg col
>
>   Note that type signatures are required on each function defined, as func is
>   defined in a typeclass. Also, because of the implementation of aggregates,
>   only one argument can be provided.-}
>     func :: (ExprC e, ToPrimExprs r) => String -- | The name of the function
>       -> (ExprNil -> ExprCons (Expr d) r) -- | The arguments,
>  specified via arg combinators joined by composition.
>       -> e o -- | The resulting expression.
>
>   -- | This instance ensures only one argument can be provided to
>   -- an aggregate function.
>   instance MakeFunc ExprAggr ExprNil where
>     func = funcA
>
>   funcA :: String -> (ExprNil -> ExprCons (Expr a) ExprNil) -> ExprAggr o
>   funcA name args = ExprAggr (AggrExpr (AggrOther name) (primExpr .
>  unExprs $ (args ExprNil)))
>     where
>       unExprs :: ExprCons (Expr a) ExprNil -> Expr a
>       unExprs ~(ExprCons e _) = e
>
>   -- | This instance allows any number of expressions to be used as
>   -- arguments to a non-aggregate function.
>   instance MakeFunc Expr a where
>     func = funcE
>
>   funcE :: (ToPrimExprs r) => String -> (ExprNil -> ExprCons (Expr e)
>  r) -> Expr o
>   funcE name args = Expr (FunExpr name (toPrimExprs (args ExprNil)))
>
>   -- | Used to specify an individual argument to a SQL function definition. This
>   -- combinator must be strung together with function composition. That chain
>   -- must be provided to the func combinator.
>   arg :: (Expr a) -> (c -> ExprCons (Expr a) c)
>   arg expr = ExprCons expr
>
>  I modeled the code above after the (#) function and the RecCons/RecNil
>  types from the HDBRec module. In this case, a "list" of expressions is
>  built through composition with the arg combinator and handed to the
>  func combinator. Depending on the result type (Expr or ExprAggr), one
>  of the two instances is selected. Since ExprAggr can only take one
>  argument, the instance is only defined for (ExprCons x ExprNil). A
>  really ugly error will result if more than one argument is defined for
>  an aggregrate. Non-aggregates, however, can take any number of
>  arguments.

Hmm, I get the feeling that this could be implemented in a simpler way.

If we ignore the one-argument restriction on aggregates, couldn't you
use the same trick as Text.Printf or the Remote class in HaXR (see
http://darcs.haskell.org/haxr/Network/XmlRpc/Client.hs). The aggregate
restriction could be handled by having a separate 'func' for
aggregates, with a simpler type.

Also, why are ExprNil and ExprCons needed? Couldn't func produce a
PrimExpr internally?


>  The implementation of the motivating functions is then:
>
>   trim str = func "trim" $ arg str
>
>   rpad str len (Just char) = func "rpad" $ arg str . arg (constant
>  len) . arg (constant char)
>   rpad str len Nothing = func "rpad" $ arg str . arg (constant len)
>
>   every col = func "every" $ arg col
>
>  Thanks in advance to any responders!
>
>  Justin

With the solution I refer to above, you should be able to write them
as (not tested of course):

trim str = func "trim" str

rpad str len (Just char) = func "rpad" str (constant len) (constant char)
rpad str len Nothing = func "rpad"  str (constant len)

every col = funcAggr "every" col

/Bjorn