|
From: Justin B. <jgb...@gm...> - 2008-04-03 21:53:47
|
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.
|