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

[Haskelldb-users] About unique

[Guenther S. <gue...@we...>]

Hi guys,

I can honestly say that I don't have a clue on how to use it. Or rather 
where to place it. Or rather to use it to actually get a "select  
distinct".
Whenever I did use it, it always grouped at the innermost level.
How can you determine the level *where* the grouping takes place?

Günther

Re: [Haskelldb-users] Lazy version of query

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

In short, no. That is really dependent on the underlying database
library more than haskellDB. You might want to look at Takusen for
safe, constant-resource queries.

On Fri, May 1, 2009 at 3:37 AM, Guenther Schmidt <gue...@we...> wrote:
> Hi all,
>
> is it possible to create a "lazy" version of query?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> Register Now & Save for Velocity, the Web Performance & Operations
> Conference from O'Reilly Media. Velocity features a full day of
> expert-led, hands-on workshops and two days of sessions from industry
> leaders in dedicated Performance & Operations tracks. Use code vel09scf
> and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] Lazy version of query

[Guenther S. <gue...@we...>]

Hi all,

is it possible to create a "lazy" version of query?

Günther

Re: [Haskelldb-users] finding ids present in one table and not in another

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

How about the 'minus' function?

/Bjorn

On Sat, Apr 25, 2009 at 10:47, Guenther Schmidt <gue...@we...> wrote:
> Hi guys,
>
> I have this problem which I don't know how to solve:
>
> I need to do something with records in one table whose Id is *not*
> present in another table like
>
> testqry = do
>  l1 <- table leistungIndices
>  l2 <- table leistungIndices
>  p  <- table procedureCnts
>  restrict (l1!caseId .<>. p!caseId)
>  restrict (l1!caseId .==. p!caseId)
>  restrict (l1!caseId .==. l2!caseId)
>  project (caseId << l1!caseId
>           # fabId << l1!fabId
>           # opLstgIdx << l1!opLstgIdx
>           # opLstgIdxFab << _sum (l2!opLstgIdx))
>
> which blows up because of the 1st restrict.
>
> How can  I express this properly?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> Crystal Reports &#45; New Free Runtime and 30 Day Trial
> Check out the new simplified licensign option that enables unlimited
> royalty&#45;free distribution of the report engine for externally facing
> server and web deployment.
> http://p.sf.net/sfu/businessobjects
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] finding ids present in one table and not in another

[Guenther S. <gue...@we...>]

Hi guys,

I have this problem which I don't know how to solve:

I need to do something with records in one table whose Id is *not* 
present in another table like

testqry = do
  l1 <- table leistungIndices
  l2 <- table leistungIndices
  p  <- table procedureCnts
  restrict (l1!caseId .<>. p!caseId)
  restrict (l1!caseId .==. p!caseId)
  restrict (l1!caseId .==. l2!caseId)
  project (caseId << l1!caseId
           # fabId << l1!fabId
           # opLstgIdx << l1!opLstgIdx
           # opLstgIdxFab << _sum (l2!opLstgIdx))

which blows up because of the 1st restrict.

How can  I express this properly?

Günther

Re: [Haskelldb-users] Parenthesizing

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

Normal Haskell parantheses should do it.

/Bjorn

On Sun, Apr 12, 2009 at 02:15, Guenther Schmidt <gue...@we...> wrote:
> Hi,
>
> I have a rather complicated arithmetic expression, and I need to make
> sure that parts of the arithmetic expression are parenthesized.
>
> How can I do that?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] Debugging, required extension

[Guenther S. <gue...@we...>]

Hi all,

I'm trying to debug the program, so I copied the haskelldb modules (the 
database directory) to a location within my app in order for my app to 
use the interpreted code.

However ghci complains about some extensions missing and I can't figure 
out which particular ones those are.

Günther

Re: [Haskelldb-users] Exception: No match in record selector Database.HaskellDB.Sql.attrs

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

On Wed, Apr 15, 2009 at 8:58 AM, Guenther Schmidt <gue...@we...> wrote:
> I'm just wondering what I did that would eventually cause it in the first
> place.
>

Ah, I see now. If you look at Sql.hs, you can see attrs is defined as
a selector function on SqlSelect values. If attrs is applied to some
other value of that type (SqlBin, for example), then you would see
this error. I suspect it's a bug and if you can isolate it  (with
dummy table definitions) that would help a lot.

> Would you happen to have some brief example of how to do intersect, union or
> minus properly?
>

I don't unfortunately.

Justin

Re: [Haskelldb-users] Exception: No match in record selector Database.HaskellDB.Sql.attrs

[Guenther S. <gue...@we...>]

Hi Justin,

the explanation, I believe, is found here: 
http://www.haskell.org/ghc/docs/latest/html/libraries/base/Control-Exception.html#t%3ARecSelError

I'm just wondering what I did that would eventually cause it in the 
first place.

Quite possible I construct my code, ie the Query (Rel whatever) in a 
way, or in an order, that does compile but throws a runtime error 
nevertheless.

I'm using ghc 6.10.

Would you happen to have some brief example of how to do intersect, 
union or minus properly?

Günther



Justin Bailey schrieb:
> I don't see a place in the source where that exception is thrown
> (grepping for variations on the error string didn't give me anything).
>  Are you sure it's HaskellDB?
>
> On Wed, Apr 15, 2009 at 8:17 AM, Guenther Schmidt <gue...@we...> wrote:
>   
>> Hi all,
>>
>> I get this error
>>
>> Exception: No match in record selector Database.HaskellDB.Sql.attrs
>>
>> in some cases when I do union, intersection or minus.
>>
>> What does that mean?
>>
>> Günther
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by:
>> High Quality Requirements in a Collaborative Environment.
>> Download a free trial of Rational Requirements Composer Now!
>> http://p.sf.net/sfu/www-ibm-com
>> _______________________________________________
>> Haskelldb-users mailing list
>> Has...@li...
>> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>>
>>     

Re: [Haskelldb-users] Exception: No match in record selector Database.HaskellDB.Sql.attrs

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

I don't see a place in the source where that exception is thrown
(grepping for variations on the error string didn't give me anything).
 Are you sure it's HaskellDB?

On Wed, Apr 15, 2009 at 8:17 AM, Guenther Schmidt <gue...@we...> wrote:
> Hi all,
>
> I get this error
>
> Exception: No match in record selector Database.HaskellDB.Sql.attrs
>
> in some cases when I do union, intersection or minus.
>
> What does that mean?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] Exception: No match in record selector Database.HaskellDB.Sql.attrs

[Guenther S. <gue...@we...>]

Hi all,

I get this error

Exception: No match in record selector Database.HaskellDB.Sql.attrs

in some cases when I do union, intersection or minus.

What does that mean?

Günther

[Haskelldb-users] where Not in ...

[Guenther S. <gue...@we...>]

Hi,

I'm trying to select records whose caseId are *not* present in another 
table.

I did a `minus` thingy on the caseIds but then the restrict clause 
causes a run-time
"No match in record selector"
and that spoils the fun.

How *can* I select records that are not present in another table?

Günther

Justin, haskelldb-users, is that currently just you and me?




nonProcs = do
  r <- table casesWithRGs
  p <- table procedureCnts
  n <- (project (caseId << r!caseId)) `minus` (project (caseId << p!caseId))
  l <- table leistungIndices
  restrict (n!caseId  .==. l!caseId)
  project (caseId << l!caseId
           # dummyDouble << _sum (l!opLstgIdx))
                 

[Haskelldb-users] Parenthesizing

[Guenther S. <gue...@we...>]

Hi,

I have a rather complicated arithmetic expression, and I need to make 
sure that parts of the arithmetic expression are parenthesized.

How can I do that?

Günther

Re: [Haskelldb-users] about Select Distinct

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

Yes, use the "unique" function.

On Sat, Apr 11, 2009 at 2:32 PM, Guenther Schmidt <gue...@we...> wrote:
> Hi all,
>
> I'm sorry, I read somewhere that Select Distinct is by default turned
> off now, can't remember where though.
>
> I have one query though, where I certainly would like to turn it on,
> does that possibility still exist?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] Tables and Indexes

[Guenther S. <gue...@we...>]

Hi all,

I keep insisting that the SQL generated is obviously wrong, has to be, 
because the query takes way too long.

Eventually I find out how to index the table properly and as soon as I 
do it right, surprise, surprise it works.

Um.

Just about the 4th time now, I have a few more, don't I?

Günther

[Haskelldb-users] about Select Distinct

[Guenther S. <gue...@we...>]

Hi all,

I'm sorry, I read somewhere that Select Distinct is by default turned 
off now, can't remember where though.

I have one query though, where I certainly would like to turn it on, 
does that possibility still exist?

Günther

[Haskelldb-users] ANN: haskelldb-th 1.1 - Template Haskell utilities for HaskellDB

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

All,

I've just uploaded a set of Template Haskell utilities that make
working with HaskellDB simpler.

Download
=======

http://hackage.haskell.org/cgi-bin/hackage-scripts/package/haskelldb-th


Repository
========

http://patch-tag.com/publicrepos/haskelldb-th


Motivation
=======

Because HaskellDB depends on statically typed tables and fields,
normally you need to generate Haskell source from your database. This
can be painful and makes simple scripts or tests hard to write. This
package allows you to define tables, fields, and records using TH
syntax, and HaskellDB can import those modules just like any other
Haskell source.

Examples
=======
Two examples from the docs are given below, first for mkDBDirectTable
and then for mkRecord.

mkDBDirectTable will create a table definition with appropriate field
definitions. From the haddock docs:

-- | Creates definitions for a table and all its fields in the style
of DBDirect.
-- Takes a table name and a list of fields (as types). Generates
-- a table type and a function to construct the table. The function
-- will be the tablename in lower case. The type alias will
-- be the table name with the first character capitalized.
--
-- For example:
--
-- > mkDBDirectTable "Table1" [("Col1", [t|Int|])
-- >                          , ("Col2", [t|Bool|])]
--
-- Will produce
--
-- > type Table1 = (RecCons Col1 (Expr Int)
-- >                (RecCons Col2 (Expr Bool)))
-- >
-- > table1 :: Table Table1
-- > table1 = baseTable "Table1" $
-- >          hdbMakeEntry Col1 #
-- >          hdbMakeEntry Col2
-- >
-- > data Col1 = Col1
-- > instance FieldTag Col1 where fieldName _ = "col1"
-- > col1 :: Attr Col1 Int
-- > col1 = mkAttr Col1
-- >
-- > data Col2 = Col2
-- > instance FieldTag Col2 where fieldName _ = "col2"
-- > col2 :: Attr Col2 Int
-- > col2 = mkAttr Col2
-- 

mkRecord makes it simpler to construct queries combining columns from
multiple tables:

{- | Create a record from the list of tables and fields given. The first
 element of each tuple is a table. The second is a list of fields from
 that table which will be in the new record. The record will be constructed
 in the order of tables and fields given.

 This is a Template Haskell function which must be spliced in and is
 intended to use the @'@ (quote) facility for capturing names. An example:

> import Status_v (Status_v, status_v)
> import qualified Status_v as Status_v
>
> import Dtls_v (Dtls_v, dtls_v)
> import qualified Dtls_v as Dtls_v
>
>
>  qry = do
>    status <- table status_v;
>    dtls <- table dtls_v;
>    project $(mkRecord [('dtls, ['Dtls_v.desc
>                         , 'Dtls_v.hdr_id
>                         , 'Dtls_v.prt_id
>                         , 'Dtls_v.dtl_id])
>                       ,('status, ['Status_v.stat_nbr])])

The type of qry is then

>          (RecCons Dtls_v.Desc (Expr (Maybe BStr40))
>          (RecCons Dtls_v.Hdr_id (Expr (Maybe Int))
>          (RecCons Dtls_v.Prt_id (Expr (Maybe BStr30))
>          (RecCons Dtls_v.Dtl_id (Expr (Maybe Int))
>          (RecCons Status_v.Stat_nbr (Expr (Maybe Int)) RecNil))))))

If other fields need to be added to the record, they must come before the call
to mkRecord:

>    project $ filtered << someTable ! someField #
>              $(mkRecord [('dtls, ['Dtls_v.prt_desc
>                                    , 'Dtls.hdr_id
>                                    , 'Dtls.prt_id
>                                    , 'Dtls.dtl_id])
>                          ,('status, ['Status_v.stat_nbr])])

-}

Enjoy!

Justin

[Haskelldb-users] Substring - Special Operators

[Guenther S. <gue...@we...>]

Hi all,

how can I "define" a special operator like substring?

Günther

Re: [Haskelldb-users] Wrapping / Escaping Identifiers

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

Unfortunately I don't think you can do it w/o writing your own pretty
printer (in Database.HaskellDB.Sql.Print) and your own version of
query (from Database.HaskellDB) to use your pretty-printer.

On Tue, Apr 7, 2009 at 11:04 AM, Guenther Schmidt <gue...@we...> wrote:
> Hi all,
>
> in my SQLite backend there are some unfortunately chosen Table and Field
> names like: some-table, some-column.
>
> This requires the generated SQL to wrap these table names in quotes.
>
> I cannot simple wrap the table and field names in the haskell table
> definition because when the result comes back the result column names
> can't be matched (because of the quotes). How can I make the
> SQLGenerator wrap the table and field names?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] Wrapping / Escaping Identifiers

[Guenther S. <gue...@we...>]

Hi all,

in my SQLite backend there are some unfortunately chosen Table and Field 
names like: some-table, some-column.

This requires the generated SQL to wrap these table names in quotes.

I cannot simple wrap the table and field names in the haskell table 
definition because when the result comes back the result column names 
can't be matched (because of the quotes). How can I make the 
SQLGenerator wrap the table and field names?

Günther

[Haskelldb-users] HaskellDB and Unicode?

[Guenther S. <gue...@we...>]

Hi Artyom,

thanks.

Could you also please tell me how I have to handle Unicode with 
Haskelldb. I have a Unicode problem when using Haskelldb to import from 
Sqlite to an MS-Access database.

Günther

Artyom Shalkhakov schrieb:
> Hi,
>
> 2009/4/7 Guenther Schmidt <gue...@we...>:
>   
>> so I'v managed to fire of an apparently correct query, HURRAY, but, I
>> know this is ridiculus, how do I get at the values?
>>
>> I mean I get back a IO [Record .....] but I wonna get at the individual
>> values too.
>>     
>
> It's very easy, just use the record selection operator:
>
> record ! field -- yields field value
>
> (see http://hackage.haskell.org/packages/archive/haskelldb/0.12/doc/html/Database-HaskellDB.html#v%3A%21)
>
> Cheers,
> Artyom Shalkhakov.
>   

[Haskelldb-users] Get results out of a row

[Guenther S. <gue...@we...>]

Hi,

so I'v managed to fire of an apparently correct query, HURRAY, but, I 
know this is ridiculus, how do I get at the values?

I mean I get back a IO [Record .....] but I wonna get at the individual 
values too.

I know how to print them though :)

Günther

Re: [Haskelldb-users] haskelldb-hdbc-odbc

[Guenther S. <gue...@we...>]

Hi Justin,

I think I will try to use haskellDB as a template to create an AST and 
then just create SQL from that. Right now I don't really have the time 
to check out all of haskellDBs capabilities and then maybe find myself 
in need of something that can't be done within haskellDB.

Primarily I need to get in control of the wallpaper-sized SQL statements 
my app uses, it isn't really all that necessary to perfectly type safe, 
especially not the incoming part, ie. the results of the queries.

Thanks for your help and patience.

Günther

Re: [Haskelldb-users] haskelldb-hdbc-odbc

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

For specifics on using HDBC-ODBC, you'll want to look at their
documentation. Each driver is a little different. Here is what i used
to connect to postgres:

import Database.HaskellDB.HDBC.PostgreSQL
...

login :: MonadIO m => String -> Int -> String -> String -> String ->
(Database -> m a) -> m a
login server port user password dbname = postgresqlConnect [("host", server),
  ("port", show port),
  ("user", user),
  ("password", password),
  ("dbname", dbname)]


On Sun, Apr 5, 2009 at 9:05 AM, Guenther Schmidt <gue...@we...> wrote:
> Hi all,
>
> how can I "connect" to an ODBC data base, ie. what do I need for "withDB" ?
>
> Günther
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

[Haskelldb-users] haskelldb-hdbc-odbc

[Guenther S. <gue...@we...>]

Hi all,

how can I "connect" to an ODBC data base, ie. what do I need for "withDB" ?

Günther