Re: [Haskelldb-users] Querying an empty table

[Bjorn B. <d00...@dt...>]

Gregory Wright wrote:
> I tried the suggest method and it didn't typecheck. (_case requires Expr,
> not ExprAggr). This seems unnecessarily restrictive; I would expect _case
> to be able to take both an Expr and ExprAggr in the conditional position.

Of course! Silly me. Allowing Expr or ExprAggr in the conditional is not 
enough,
we would have to allow both of them in the result psotions too. This seems
difficult to type correctly.

_case :: ProjectExpr e => [(e Bool, e a)] -> e a -> e a

should work, but then all the expressions have to be of the same type, 
can't mix
aggregate and non-aggregate expressions.

> For my program I have a workaround, but in developing it I ran into an
> interesting question.  (The workaround is just to query the count first,
> and if it is nonzero query for the maximum element.)
> 
> Assume I have a database with a single table, all of whose fields are 
> Strings. In hdb, how can I count the number of records matching a condition? The
> query would look something like
> 
> myquery = do
>         t  <- table mytab
>         restrict (t!myfield .==. constant the_interesting_field_value)
>         u <- project ( <this is the problem>  << count(t!myfield))
>         return u
> 
> The projection is where the trouble is again. (I understand that I could
> return the entire list of match records and take the length, but if the 
> list is long and all I want is the count, that is unreasonably inefficient.)
> 
> Since we don't have a field of type Int, how do we return the count?

This is a known problem, and we don't have a solution for it. It is a 
consequence
of our implementation of record types. See below for a workaround.


> The solution may be straightforward, since we just need a way to easily 
> make a record with known fields. Something like
> 
>         project(returnAs("foo", IntT, True) << count(t!myfield))
> 
> which would result in the query
> 
>         SELECT COUNT(myfield) AS foo ...
> 
> This could also solve the problem of _max and _min returning nothing on
> empty tables, if the column named in returnAs were nullable.

The problem with this approach is that it cannot be type checked.


> Is it possible to declare a fake table (not present in the database) and
> use it as a field in project? Then I could say
> 
>         project (fakecol << count(t!myfield))
> 
> where fakecol was a column of type nullable Int. That might would be
> easy, and it would be easy to explain that DBDirect automatically made
> row declarations for each table in the database, but to return synthetic
> rows you have to declare them yourself.

The best workaround that we know of is to declare fake
fields, as you describe. test/CustomSql.hs has some examples of fake fields.

Here's how to declaare a field foo with type Int:

     data Foo = Foo
     instance FieldTag Foo where fieldName _ = "foo"
     foo = mkAttr Foo :: Attr Foo Int


If you are using ghc you can use Template Haskell to declare fake fields by
using test/THField.hs:

import Dp037.D3proj_time_reports
import THField

$(field "foo" "foo" "Foo" False "Int")

q = do
     t <- table d3proj_time_reports
     project (foo << count(t!userid))

The interesting function is:

-- | Declare a field.
field :: String  -- ^ Haskell identifier for the field (e.g. "xid")
       -> String  -- ^ Actual field name (e.g. "id")
       -> String  -- ^ Name of the field label type (e.g. "Id")
       -> Bool    -- ^ Whether the field is nullable
       -> String  -- ^ Name of the value type of the field (e.g. "Int")
       -> Q [Dec]

/Bjorn