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

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

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.

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

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.

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