haskelldb-users — For questions, suggestions, bug reports and patches

[Haskelldb-users] GROUP BY, unique and aggregations

[Henning T. <has...@he...>]

On Thu, 10 Jul 2008, Justin Bailey wrote:

> By the way, another  way to write your query is:
>
> do
>        p <- table Points.points
>        unique
>        project (Points.c << p!Points.c)

>From the description of 'unique' it was not clear to me, which 'project' 
the 'unique' refers to. At first I thought, 'unique' refers to the last 
'project' before 'unique'. But then I thought, that 'project' is just like 
'return' and thus 'unique' cannot "see" it. So, is the location of 
'unique' irrelevant? Isn't it better then to make it a function of type
    unique :: Query (Rel r) -> Query (Rel r)
   which is not applied by monadic binding, but as a transformation of the 
whole query? Currently it's implemented by GROUP BY but it could also be 
implemented by DISTINCT, right?

Now I thought a bit more about grouping and aggregations. When writing 
database queries in the Query monad then I compare that with the list 
monad. Tables or multi-sets could just be seen as lists with irrelevant 
order. However, in the list monad a 'unique' function, that is applied by 
monadic bind, could not be implemented, because the monadic bind always 
feeds single elements to its second operand. But 'unique' must have access 
to the whole table in order to check for duplicates. So, is it sensible to 
have the 'unique' function how it currently is, if it can only be 
implemented in terms of query expressions but not in terms of real data 
(namely lists)?

I think a big deficiency of SQL is, that it tries to handle three types of 
query answers in the same query form. There are

r        -- scalar types as answers produced by aggregations
Rel r    -- the answer of a regular query
Rel (Rel r)
          -- an intermediate type that arises when grouping

  I hoped 'unique' would give us a way to avoid the last type, but it 
seems, that I was wrong.
  Firstly I liked, if HaskellDB would be more precise than SQL with respect 
to the types. If aggregations would have type
   (Rel r -> r)
  then it would not be possible to accidentally apply an aggregation twice 
(AVG(AVG(x2))), mix aggregations with simple column accesses (SELECT 
avg(x1), x2 FROM ...) and there would be no need to check whether the list 
returned by the query indeed consists of a single element.
  Then there are aggregations in conjunction with grouping, where they turn 
an intermediate nested relation of type (Rel (Rel a)) back into a (Rel b). 
A function which groups and aggregates may have type
   groupAndAggregate ::
         (r -> a)   -- select columns or other values to group for
      -> (a -> Rel r -> b)
                    -- aggregation for each group with access to the grouping criterion
      -> Rel r
      -> Rel b

Re: [Haskelldb-users] views

[Henning T. <has...@he...>]

On Thu, 10 Jul 2008, Justin Bailey wrote:

> Can you elaborate? What views do you mean - pattern views (GHC),
> database views, or something else? How is 'let' involved?

I mean database views. In a view you can "store" temporary tables and in 
this respect it is like 'let' in Haskell.

Re: [Haskelldb-users] GROUP BY and unique

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

On Thu, Jul 10, 2008 at 12:04 AM, Henning Thielemann
<has...@he...> wrote:
>
> I found the idea of implementing grouping by the 'unique' statement rather
> elegant, but its translation is not conform to SQL, isn't it?

Thank you - I contributed that code.

>
>
>     do p <- table Points.points
>        r <- project (Points.c << p!Points.c)
>        unique
>        return r
>
> Is translated to
>
> SELECT C2 C
> FROM (SELECT C C2
>       FROM POINTS T1
>       GROUP BY C2) T1
>
>
> But the C2 after GROUP BY does not denote a column of a table. It may be
> replaced by C or the GROUP BY may be moved to the outer SELECT statement.
>

Looks like a bug. I have been able to reproduce and will try to submit
a patch ASAP. Thanks.

Justin

Re: [Haskelldb-users] views

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

Can you elaborate? What views do you mean - pattern views (GHC),
database views, or something else? How is 'let' involved?

On Wed, Jul 9, 2008 at 11:21 PM, Henning Thielemann
<has...@he...> wrote:
>
> Is it planned to support views in HaskellDB? They would serve a nice
> 'let'.
>
> -------------------------------------------------------------------------
> Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
> Studies have shown that voting for your favorite open source project,
> along with a healthy diet, reduces your potential for chronic lameness
> and boredom. Vote Now at http://www.sourceforge.net/community/cca08
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] GROUP BY and unique

[Henning T. <has...@he...>]

I found the idea of implementing grouping by the 'unique' statement rather
elegant, but its translation is not conform to SQL, isn't it?


     do p <- table Points.points
        r <- project (Points.c << p!Points.c)
        unique
        return r

Is translated to

SELECT C2 C
FROM (SELECT C C2
       FROM POINTS T1
       GROUP BY C2) T1


But the C2 after GROUP BY does not denote a column of a table. It may be 
replaced by C or the GROUP BY may be moved to the outer SELECT statement.

[Haskelldb-users] views

[Henning T. <has...@he...>]

Is it planned to support views in HaskellDB? They would serve a nice 
'let'.

Re: [Haskelldb-users] driver options -> driver parameters?

[Henning T. <has...@he...>]

On Mon, 7 Jul 2008, Justin Bailey wrote:

> On Sun, Jul 6, 2008 at 2:09 PM, Henning Thielemann
> <has...@he...> wrote:
>>
>> As I understand the "options" like 'db', 'uid', 'pwd' for the HaskellDB
>> back ends are not optional and thus they should be better called driver
>> arguments?
>
> Sounds pretty reasonable, though a bit pedantic. What makes you ask?

I found myself adding a field 'requiredOptions' to the DriverInterface 
record, but then thought that options that are obligatory are not options.

Re: [Haskelldb-users] capitalized Table and Attribute names

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

On Tue, Jul 8, 2008 at 12:30 AM, Justin Bailey <jgb...@gm...> wrote:
> On Thu, Jul 3, 2008 at 3:18 AM, Henning Thielemann
> <has...@he...> wrote:
>>
>> When running DBDirect, I get table and attribute identifiers like uSERS
>> and tITLE. How about an option for DBDirect that convert names to camel
>> case, like
>>   USER_TABLES -> userTables
>>  ?
>
> I found it was easier to write my "own" DBDirect than use the program
> supplied (I had some special needs, like regenerating all the modules
> in a given directory). A structure describing all the tables is built
> before they are rendered. You can probably do this transformation
> yourself pretty easily.

Yeah, this is useful advice. I did the same for Hope.

/Björn

Re: [Haskelldb-users] capitalized Table and Attribute names

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

On Thu, Jul 3, 2008 at 3:18 AM, Henning Thielemann
<has...@he...> wrote:
>
> When running DBDirect, I get table and attribute identifiers like uSERS
> and tITLE. How about an option for DBDirect that convert names to camel
> case, like
>   USER_TABLES -> userTables
>  ?

I found it was easier to write my "own" DBDirect than use the program
supplied (I had some special needs, like regenerating all the modules
in a given directory). A structure describing all the tables is built
before they are rendered. You can probably do this transformation
yourself pretty easily.

Justin

Re: [Haskelldb-users] DBDirect help

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

On Sun, Jul 6, 2008 at 9:10 PM, Henning Thielemann
<has...@he...> wrote:
>
> On Sat, 5 Jul 2008, Bjorn Bringert wrote:
>
>> On Thu, Jul 3, 2008 at 12:49 PM, Henning Thielemann
>> <has...@he...> wrote:
>>
>> > But since the program has the database back-end baked in, it would be >
>> > straight-forward to show only the options for that particular > back-end. I
>> > could modify DBDirect in this way, if the patches will be > accepted.
>
>> Yes, please do. I won't have much e-mail access during the summer though.
>> I can give you write access to the darcs repo. What's your code.haskell.org
>> username?
>
> thielema

Added.

> However, how can I test whether the modified sources for all drivers can be
> compiled, if I have not installed clients for all these databases?

By installing the client libraries :-)

> I also like to use GetOpt in DBDirect and I like to add an option that
> converts table and column names like TABLE_NAME and COLUMN_NAME to TableName
> and columnName. I find the currently generated cOLUMN_NAME quite
> unsatisfying. However, I'm not sure whether I can assert unique identifiers
> this way.

Hmm, I haven't seen the cOLUMN_NAME behavior, but it seems silly, yes.

/Björn

[Haskelldb-users] driver options -> driver parameters?

[Henning T. <has...@he...>]

As I understand the "options" like 'db', 'uid', 'pwd' for the HaskellDB 
back ends are not optional and thus they should be better called driver 
arguments?

Re: [Haskelldb-users] DBDirect help

[Henning T. <has...@he...>]

On Sat, 5 Jul 2008, Bjorn Bringert wrote:

> On Thu, Jul 3, 2008 at 12:49 PM, Henning Thielemann <has...@he...> wrote:
>
> > But since the program has the database back-end baked in, it would be 
> > straight-forward to show only the options for that particular 
> > back-end. I could modify DBDirect in this way, if the patches will be 
> > accepted.

> Yes, please do. I won't have much e-mail access during the summer 
> though. I can give you write access to the darcs repo. What's your 
> code.haskell.org username?

thielema


However, how can I test whether the modified sources for all drivers can 
be compiled, if I have not installed clients for all these databases?


I also like to use GetOpt in DBDirect and I like to add an option that 
converts table and column names like TABLE_NAME and COLUMN_NAME to 
TableName and columnName. I find the currently generated cOLUMN_NAME quite 
unsatisfying. However, I'm not sure whether I can assert unique 
identifiers this way.

Re: [Haskelldb-users] DBDirect help

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

On Thu, Jul 3, 2008 at 12:49 PM, Henning Thielemann
<has...@he...> wrote:
>
> Currently I get
>
> $ DBDirect-hsql-oracle
> DB/Direct: Daan Leijen (c) 1999, HWT (c) 2003-2004,
>            Bjorn Bringert (c) 2005-2007
>
> Usage: DBDirect-hsql-oracle [-b] <module> <options>
>
> -b         Use bounded string types
> <options>  Driver dependent,e.g.
>            WX:              dsn=<dsn>,uid=<uid>,pwd=<pwd>
>            HSQL.MySQL:      server=<server>,db=<db>,uid=<uid>,pwd=<pwd>
>            HDBC.PostgreSQL: host=<server>,dbname=<db>,user=<uid>,password=<pwd>
>
>
> But since the program has the database back-end baked in, it would be
> straight-forward to show only the options for that particular back-end. I
> could modify DBDirect in this way, if the patches will be accepted.

Yes, please do. I won't have much e-mail access during the summer
though. I can give you write access to the darcs repo. What's your
code.haskell.org username?

/Björn

[Haskelldb-users] DBDirect help

[Henning T. <has...@he...>]

Currently I get

$ DBDirect-hsql-oracle
DB/Direct: Daan Leijen (c) 1999, HWT (c) 2003-2004,
            Bjorn Bringert (c) 2005-2007

Usage: DBDirect-hsql-oracle [-b] <module> <options>

-b         Use bounded string types
<options>  Driver dependent,e.g.
            WX:              dsn=<dsn>,uid=<uid>,pwd=<pwd>
            HSQL.MySQL:      server=<server>,db=<db>,uid=<uid>,pwd=<pwd>
            HDBC.PostgreSQL: host=<server>,dbname=<db>,user=<uid>,password=<pwd>


But since the program has the database back-end baked in, it would be 
straight-forward to show only the options for that particular back-end. I 
could modify DBDirect in this way, if the patches will be accepted.

[Haskelldb-users] capitalized Table and Attribute names

[Henning T. <has...@he...>]

When running DBDirect, I get table and attribute identifiers like uSERS 
and tITLE. How about an option for DBDirect that convert names to camel 
case, like
   USER_TABLES -> userTables
  ?

[Haskelldb-users] Oracle via HaskellDB: AS keyword

[Henning T. <has...@he...>]

When using HaskellDB to call HSQL/Oracle, it generates an SQL query like

SELECT TITLE
FROM COURSES as T1

Our Oracle DB doesn't like the 'as', it says

*** Exception: user error (ORA-00933: SQL command not properly ended
)


Without the 'as' the query is processed.
  I looked into Database.HaskellDB.Sql.Print, especially ppAs, but the 
pretty printing does not depend on the Database back-end. How can I 
achieve that the 'as' is not generated for Oracle or is there an SQL 
syntax that is understood by all databases?

Re: [Haskelldb-users] Oracle via HaskellDB

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

On Tue, Jul 1, 2008 at 1:48 PM, Henning Thielemann
<has...@he...> wrote:
>Which one, HSQL or HDBC, is the
> better choice for this task?
>

I have been using HDBC on to access postgres and have had good luck
with it. I tried HSQL first but could not get it to compile under
Windows (that was about 6 months ago). I see from your next message
that you have had more luck with HSQL, so it's really up to you which
is best.

Justin

Re: [Haskelldb-users] Oracle via HaskellDB

[Henning T. <has...@he...>]

On Tue, 1 Jul 2008, Henning Thielemann wrote:

> There was already a question about Oracle access via HaskellDB:
>  http://www.haskell.org/pipermail/haskell-cafe/2008-February/039068.html
>   which unfortunately remained unanswered. On the one hand Oracle support is
> listed in the package description, but I cannot find a module for it. Since I
> failed installing Oracle's ODBC driver, but I can run Oracle's sqlplus (access
> via OCI), I'm interested in Oracle/OCI support by HaskellDB. If this is not
> already implemented somewhere - how difficult would it be to use the related
> stuff from Takusen? In the end it would be certainly perfect to have one OCI
> package which is used by both Takusen and HaskellDB. If I understand the
> architecture of HaskellDB right then we need HSQL or HDBC support for Oracle
> before going to implement support by HaskellDB. Which one, HSQL or HDBC, is the
> better choice for this task?

I do now see that HSQL has Oracle support, which is in the darcs 
repository but not on Hackage. However the HaskellDB interface is still 
missing (and I can compile HSQL Oracle support, I can also log in to the 
database, but cannot get data out of it ...)

[Haskelldb-users] Oracle via HaskellDB

[Henning T. <has...@he...>]

There was already a question about Oracle access via HaskellDB:
  http://www.haskell.org/pipermail/haskell-cafe/2008-February/039068.html
   which unfortunately remained unanswered. On the one hand Oracle support is 
listed in the package description, but I cannot find a module for it. Since I 
failed installing Oracle's ODBC driver, but I can run Oracle's sqlplus (access 
via OCI), I'm interested in Oracle/OCI support by HaskellDB. If this is not 
already implemented somewhere - how difficult would it be to use the related 
stuff from Takusen? In the end it would be certainly perfect to have one OCI 
package which is used by both Takusen and HaskellDB. If I understand the 
architecture of HaskellDB right then we need HSQL or HDBC support for Oracle 
before going to implement support by HaskellDB. Which one, HSQL or HDBC, is the 
better choice for this task?

Re: [Haskelldb-users] errors evaluating aggregates

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

I think your problem may be the database driver, not haskelldb. What
database are you talking to? Can you get any more useful output (try
running with "+RTS -xc" option). From the GHC documentation:

"-xc

(Only available when the program is compiled for profiling.) When an
exception is raised in the program, this option causes the current
cost-centre-stack to be dumped to stderr.

This can be particularly useful for debugging: if your program is
complaining about a head [] error and you haven't got a clue which bit
of code is causing it, compiling with -prof -auto-all and running with
+RTS -xc -RTS will tell you exactly the call stack at the point the
error was raised."

Justin

On Wed, Jun 4, 2008 at 6:10 PM, "Rafael J. Fernández-Moctezuma"
<rfe...@cs...> wrote:
> Hello everybody!
>
> I am getting a runtime error when I evaluate aggregates. For example,
> consider:
>
> query1 = do s <- table sailors
>            project (sid <<  _sum (s!sid))
>
> "sid" is an attribute in table "sailors", of type Int (and NOT NULL).
> When evaluating, I get a runtime error:
>
> user error (The type of sid field can't be converted to SqlUnknown 246 type)
>
> My intuition is that the type of a sum expression is not necessarily
> Int, since you can always sum Doubles. Well, the next obvious step for
> me was to rewrite the query and make the "receiving" column type a double:
>
> data Doublefield = Doublefield
>
> instance FieldTag Doublefield where fieldName _ = "doublefield"
>
> doublefield ::  Attr Doublefield Double
> doublefield = mkAttr Doublefield
>
> query2 = do s <- table sailors
>            project (doublefield <<  _sum (s!sid))
>
>
> Oops, that won't compile, the inferred type of sum is indeed Int (or so
> I'm told by GHC):
>
>    Couldn't match expected type `Double' against inferred type `Int'
>      Expected type: Expr Double
>      Inferred type: Expr Int
>
> Strangely, count, max, and min do work. I wonder if (1) I'm missing
> something, or (2) this is indeed an unexpected behaviour (given query1
> passes the typechecker) that may be specific to MySQL...
>
> Thanks,
>
> RJFM
>
>
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://sourceforge.net/services/buy/index.php
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] errors evaluating aggregates

[Rafael J. Fernández-M. <rfe...@cs...>]

Hello everybody!

I am getting a runtime error when I evaluate aggregates. For example, 
consider:

query1 = do s <- table sailors
            project (sid <<  _sum (s!sid))

"sid" is an attribute in table "sailors", of type Int (and NOT NULL). 
When evaluating, I get a runtime error:

user error (The type of sid field can't be converted to SqlUnknown 246 type)

My intuition is that the type of a sum expression is not necessarily 
Int, since you can always sum Doubles. Well, the next obvious step for 
me was to rewrite the query and make the "receiving" column type a double:

data Doublefield = Doublefield

instance FieldTag Doublefield where fieldName _ = "doublefield"

doublefield ::  Attr Doublefield Double
doublefield = mkAttr Doublefield

query2 = do s <- table sailors
            project (doublefield <<  _sum (s!sid))


Oops, that won't compile, the inferred type of sum is indeed Int (or so 
I'm told by GHC):

    Couldn't match expected type `Double' against inferred type `Int'
      Expected type: Expr Double
      Inferred type: Expr Int

Strangely, count, max, and min do work. I wonder if (1) I'm missing 
something, or (2) this is indeed an unexpected behaviour (given query1 
passes the typechecker) that may be specific to MySQL...

Thanks,

RJFM

Re: [Haskelldb-users] General questions (insertion)

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

On Mon, May 26, 2008 at 7:58 PM, "Rafael J. Fernández-Moctezuma"
<rfe...@cs...> wrote:
> Hi!
>
> On May 26, 2008, at 3:32 AM, Marc Weber wrote:
>
>>>
>> Haskelldb provides type safety when creating SQL-queries.. but how
>> does
>> it know about your database scheme?
>
> tiny, minor detail :) (for some reason I thought this was encapsulated
> in the connection...)
>
>>
>>        createDBModules = dbInfoToModuleFiles "." "TimeTrackerDB"
>> dbInfo
>>
>
> I used a similar construct and now have support modules, so variables
> are in scope. Hooray!
>
> So I tested my insertion,
>
>   testInsert database = insert database boats (bid << constant 1 #
> bname << constant "Boo" # color << constant "Purple")
>
> only to find an unexpected error, namely, "No instance for (Num (Maybe
> Int)) arising from the literal `1' [..]
>
> I'm sure I'm missing yet another obvious thing... any ideas?

Use

constant (Just 1)

instead of

constant 1

/Björn

Re: [Haskelldb-users] General questions (insertion)

[R. J. Fernández-M. <rfe...@cs...>]

Hi!

On May 26, 2008, at 3:32 AM, Marc Weber wrote:

>>
> Haskelldb provides type safety when creating SQL-queries.. but how  
> does
> it know about your database scheme?

tiny, minor detail :) (for some reason I thought this was encapsulated  
in the connection...)

>
>        createDBModules = dbInfoToModuleFiles "." "TimeTrackerDB"  
> dbInfo
>

I used a similar construct and now have support modules, so variables  
are in scope. Hooray!

So I tested my insertion,

   testInsert database = insert database boats (bid << constant 1 #  
bname << constant "Boo" # color << constant "Purple")

only to find an unexpected error, namely, "No instance for (Num (Maybe  
Int)) arising from the literal `1' [..]

I'm sure I'm missing yet another obvious thing... any ideas?

Thanks much!

RJFM

Re: [Haskelldb-users] General questions (insertion)

[Marc W. <mar...@gm...>]

> Unfortunatelly, the ghc compiler tells me that neither of boats, bid,  
> bname, and color are visible.

I'm guessing you are missing a simple thing:
Haskelldb provides type safety when creating SQL-queries.. but how does
it know about your database scheme?
Somewhere there is a tool (src/Database/HaskellDB.hs) which can create those missing modules
"telling" haskelldb about the db scheme.

I've used this in my own project:

        dbName = "timeTracker"
        dbInfo = DBInfo dbName (DBOptions False) [ trackedData, summarize, projects ]
                  where trackedData = TInfo "trackedData" [ CInfo "tag" (StringT, False)
                                                         , CInfo "start" (CalendarTimeT, False)
                                                         , CInfo "end" (CalendarTimeT, False)
                                                         ]
                [..]

        createDBModules = dbInfoToModuleFiles "." "TimeTrackerDB" dbInfo

but there is also
        dbSpecToDatabase db dbInfo
to create the db from spec and I recall there is another method crating the
dbInfo from a real database..

Marc Weber

[Haskelldb-users] General questions (insertion)

[R. J. Fernández-M. <rfe...@cs...>]

Hello!

I have a table with the following schema:

boats(bid int not null, bname string, color string)

and I would like to perform the basic insert, update, delete functions  
on it. However, I've had a hard time figuring out how to correctly  
compose HaskellDB's functions. Following some code I've seen in this  
list (and a few blogs), I wrote these little functions:

testInsert db = insert db boats ((bid << constant 1) # (bname <<  
constant "Boat") # (color << constant "Purple"))

main = do
        let db = mysqlConnect MySQLOptions { ... }
        testInsert db


Unfortunatelly, the ghc compiler tells me that neither of boats, bid,  
bname, and color are visible.

Am I missing something obvious?

Thanks for your help!

RJFM