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

[Haskelldb-users] [ haskelldb-Bugs-1064382 ] Optimizer loses fields used in restrict

[SourceForge.net <no...@so...>]

Bugs item #1064382, was opened at 2004-11-11 10:19
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=629040&aid=1064382&group_id=101095

Category: optimizer
Group: None
Status: Open
Resolution: None
Priority: 9
Submitted By: Bjorn Bringert (bring)
Assigned to: Nobody/Anonymous (nobody)
Summary: Optimizer loses fields used in restrict

Initial Comment:
Reported by Lemmih:

I'm having trouble with the optimizer when doing a
one-to-many mapping.

My database structure looks like this:
mappingTable = makeTInfo "mapping"
               [makeCInfo "topic" (StringT,False),
                makeCInfo "mid" (IntT,False),
                makeCInfo "outid" (IntT,False)]
optionsTable = makeTInfo "options"
               [makeCInfo "optid" (IntT,False),
                makeCInfo "optname" (StringT,False),
                makeCInfo "optvalue" (StringT,False)]

[Queries]
unoptimized
    = ppSql . toSql . runQuery
getMappings what idcode
    = do t <- table mapping
         restrict (t!topic .==. what .&&. t!mid .==.
idcode)
         project (outid << t!outid)
getOptions what gid
    = do t <- table options
         m <- getMappings what gid
         restrict (t!optid .==. m!outid)
         project (optname << t!optname # optvalue <<
t!optvalue)

'unoptimized (getOptions (constant "") (constant 0))'
generates the somewhat verbose but correct SQL:

SELECT DISTINCT optname4 as optname,
                optvalue4 as optvalue
FROM  (SELECT DISTINCT optname1 as optname4,
                       optvalue1 as optvalue4,
                       outid3,
                       topic2,
                       mid2,
                       outid2,
                       optid1,
                       optname1,
                       optvalue1
       FROM  (SELECT DISTINCT outid2 as outid3,
                              topic2,
                              mid2,
                              outid2,
                              optid1,
                              optname1,
                              optvalue1
              FROM  (SELECT DISTINCT topic as topic2,
                                     mid as mid2,
                                     outid as outid2
                     FROM  mapping as T1) as T1,
                    (SELECT DISTINCT optid as optid1,
                                     optname as optname1,
                                     optvalue as optvalue1
                     FROM  options as T1) as T2
              WHERE ((topic2 = '') AND (mid2 = 0))) as T1
       WHERE (optid1 = outid3)) as T1

but 'getOptions (constant "") (constant 0)' generates
the following incorrect SQL where 'topic2' isn't
selected anywhere:

SELECT DISTINCT optname1 as optname,
                optvalue1 as optvalue
FROM  (SELECT DISTINCT optid as optid1,
                       optname as optname1,
                       optvalue as optvalue1
       FROM  options as T1
       WHERE ((topic2 = '') AND (mid2 = 0))) as T1,
      (SELECT DISTINCT outid as outid2
       FROM  mapping as T1) as T2
WHERE (optid1 = outid2)


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=629040&aid=1064382&group_id=101095

[Haskelldb-users] Bug in the optimizer.

[Lemmih <le...@gm...>]

Hey,

I'm having trouble with the optimizer when doing a one-to-many mapping.

My database structure looks like this:
mappingTable = makeTInfo "mapping"
               [makeCInfo "topic" (StringT,False),
                makeCInfo "mid" (IntT,False),
                makeCInfo "outid" (IntT,False)]
optionsTable = makeTInfo "options"
               [makeCInfo "optid" (IntT,False),
                makeCInfo "optname" (StringT,False),
                makeCInfo "optvalue" (StringT,False)]

[Queries]
unoptimized
    = ppSql . toSql . runQuery
getMappings what idcode
    = do t <- table mapping
         restrict (t!topic .==. what .&&. t!mid .==. idcode)
         project (outid << t!outid)
getOptions what gid
    = do t <- table options
         m <- getMappings what gid
         restrict (t!optid .==. m!outid)
         project (optname << t!optname # optvalue << t!optvalue)

'unoptimized (getOptions (constant "") (constant 0))' generates the
somewhat verbose but correct SQL:
SELECT DISTINCT optname4 as optname,
                optvalue4 as optvalue
FROM  (SELECT DISTINCT optname1 as optname4,
                       optvalue1 as optvalue4,
                       outid3,
                       topic2,
                       mid2,
                       outid2,
                       optid1,
                       optname1,
                       optvalue1
       FROM  (SELECT DISTINCT outid2 as outid3,
                              topic2,
                              mid2,
                              outid2,
                              optid1,
                              optname1,
                              optvalue1
              FROM  (SELECT DISTINCT topic as topic2,
                                     mid as mid2,
                                     outid as outid2
                     FROM  mapping as T1) as T1,
                    (SELECT DISTINCT optid as optid1,
                                     optname as optname1,
                                     optvalue as optvalue1
                     FROM  options as T1) as T2
              WHERE ((topic2 = '') AND (mid2 = 0))) as T1
       WHERE (optid1 = outid3)) as T1

but 'getOptions (constant "") (constant 0)' generates the following
incorrect SQL where 'topic2' isn't selected anywhere:
SELECT DISTINCT optname1 as optname,
                optvalue1 as optvalue
FROM  (SELECT DISTINCT optid as optid1,
                       optname as optname1,
                       optvalue as optvalue1
       FROM  options as T1
       WHERE ((topic2 = '') AND (mid2 = 0))) as T1,
      (SELECT DISTINCT outid as outid2
       FROM  mapping as T1) as T2
WHERE (optid1 = outid2)

Friendly,
  Lemmih

Re: [Haskelldb-users] HaskellDB efficience

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

Krasimir Angelov wrote:
> --- Bjorn Bringert <d00...@dt...> wrote:
> 
>>Hi Krasimir,
>>
>> the problem is in the code that you use to print the query. You are
>> not actually seeing the query that gets executed since the query is
>> not optimized yet. If you use Database.HaskellDB.showSql, you will
>> see the optimized query.
>>
>>/Bjorn
> 
> 
> Thanks! With showSql the things are much better. Now I
> got:
> 
> SELECT DISTINCT docDate
> FROM  documents as T1
> 
> This is better but why is there DISTINCT keyword. Can
> I get the query without it? The SQL script is even
> worse when I have two tables in query:

No, there is no way to not get DISTINCT in the query. This was a design 
choice made in the original HaskellDB, presumably to be closer to 
relational algebra where relations do not contain duplicate records. If 
you want to return duplicate records, you can include some unique value 
(probably the key) for each record in the results.

> [****** Haskell ******]
> import Database.HaskellDB
> import Database.HaskellDB.Query
> import Database.HaskellDB.Sql
> import Database.HaskellDB.GenericConnect
> import Text.PrettyPrint
> import Testdb.Documents
> 
> main = do
>   let primQuery = do
> 	       docs1 <- table documents
> 	       docs2 <- table documents
> 	       restrict (docs1!xid .==. docs2!xid)
> 	       project (docDate << docs1!docDate #
> 	                docDate << docs2!docDate)
>   putStrLn (render (showSql primQuery))

There is a problem with this query, the results contain the docDate 
field twice. Unfortunately the record type system currently does not 
detect this error. However, by using a technique from HList presented at 
HW2004, such a check could probably be implemented.

> [****** SQL ******]
> SELECT DISTINCT docDate1 as docDate,
>                 docDate1 as docDate
> FROM  (SELECT DISTINCT id as id2,
>                        docDate as docDate2
>        FROM  documents as T1) as T1,
>       (SELECT DISTINCT id as id1,
>                        docDate as docDate1
>        FROM  documents as T1) as T2
> WHERE (id1 = id2)
> 
> There are two derived tables, three distincts and one
> cross join. Can I get the optimal query?
> 
> SELECT T1.docDate as docDate1,
>        T1.docDate as docDate2
> FROM documents as T1
> JOIN documents as T2 on T1.id = T2.id

HaskellDB currently does not produce JOIN clauses. Doing so might be 
possible, but could be difficult in the general case. There is probably 
a lot of room for improvement in the SQL optimization, have a look at 
the code for Database.HaskellDB.Optimize if you want to play with it. 
OTOH, the RDBMS also does optimization, so it might not always be clear 
that client-side optimizations are worthwhile.

/Bjorn

Re: [Haskelldb-users] HaskellDB efficience

[Krasimir A. <ka2...@ya...>]

--- Bjorn Bringert <d00...@dt...> wrote:
> Hi Krasimir,
> 
> the problem is in the code that you use to print the
> query. You are not 
> actually seeing the query that gets executed since
> the query is not 
> optimized yet. If you use
> Database.HaskellDB.showSql, you will see the 
> optimized query.
> 
> /Bjorn

Thanks! With showSql the things are much better. Now I
got:

SELECT DISTINCT docDate
FROM  documents as T1

This is better but why is there DISTINCT keyword. Can
I get the query without it? The SQL script is even
worse when I have two tables in query:

[****** Haskell ******]
import Database.HaskellDB
import Database.HaskellDB.Query
import Database.HaskellDB.Sql
import Database.HaskellDB.GenericConnect
import Text.PrettyPrint
import Testdb.Documents

main = do
  let primQuery = do
	       docs1 <- table documents
	       docs2 <- table documents
	       restrict (docs1!xid .==. docs2!xid)
	       project (docDate << docs1!docDate #
	                docDate << docs2!docDate)
  putStrLn (render (showSql primQuery))

[****** SQL ******]
SELECT DISTINCT docDate1 as docDate,
                docDate1 as docDate
FROM  (SELECT DISTINCT id as id2,
                       docDate as docDate2
       FROM  documents as T1) as T1,
      (SELECT DISTINCT id as id1,
                       docDate as docDate1
       FROM  documents as T1) as T2
WHERE (id1 = id2)

There are two derived tables, three distincts and one
cross join. Can I get the optimal query?

SELECT T1.docDate as docDate1,
       T1.docDate as docDate2
FROM documents as T1
JOIN documents as T2 on T1.id = T2.id

Regards,
  Krasimir



		
__________________________________ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com 
 

Re: [Haskelldb-users] HaskellDB efficience

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

Krasimir Angelov wrote:
> I interested to see the generated SQL code from
> HaskellDB. The first step was to write very simple
> example:
> 
> import Database.HaskellDB
> import Database.HaskellDB.Query
> import Database.HaskellDB.Sql
> import Database.HaskellDB.GenericConnect
> import Text.PrettyPrint
> import Testdb.Documents
> 
> main = do
>   let primQuery = runQuery $ do
> 	       docs <- table documents
> 	       project (docDate << docs!docDate)
>   putStrLn (render (ppSql (toSql primQuery)))
> 
> When the example was executed I got:
> 
> SELECT DISTINCT docDate2 as docDate
> FROM  (SELECT DISTINCT docDate1 as docDate2,
>                        id1,
>                        docDate1,
>                        creationDate1
>        FROM  (SELECT DISTINCT id as id1,
>                               docDate as docDate1,
>                               creationDate as
> creationDate1
>               FROM  documents as T1) as T1) as T1
> 
> I am surprised from this very complicated and
> inefficient code. What is the reason to have these
> nested "DISTICT" statements? I expected to see just
> something like:
> 
> select docDate from documents

Hi Krasimir,

the problem is in the code that you use to print the query. You are not 
actually seeing the query that gets executed since the query is not 
optimized yet. If you use Database.HaskellDB.showSql, you will see the 
optimized query.

/Bjorn

[Haskelldb-users] HaskellDB efficience

[Krasimir A. <ka2...@ya...>]

   Hi, 

I interested to see the generated SQL code from
HaskellDB. The first step was to write very simple
example:

import Database.HaskellDB
import Database.HaskellDB.Query
import Database.HaskellDB.Sql
import Database.HaskellDB.GenericConnect
import Text.PrettyPrint
import Testdb.Documents

main = do
  let primQuery = runQuery $ do
	       docs <- table documents
	       project (docDate << docs!docDate)
  putStrLn (render (ppSql (toSql primQuery)))

When the example was executed I got:

SELECT DISTINCT docDate2 as docDate
FROM  (SELECT DISTINCT docDate1 as docDate2,
                       id1,
                       docDate1,
                       creationDate1
       FROM  (SELECT DISTINCT id as id1,
                              docDate as docDate1,
                              creationDate as
creationDate1
              FROM  documents as T1) as T1) as T1

I am surprised from this very complicated and
inefficient code. What is the reason to have these
nested "DISTICT" statements? I expected to see just
something like:

select docDate from documents

Regards,
  Krasimir



		
__________________________________
Do you Yahoo!?
Y! Messenger - Communicate in real time. Download now. 
http://messenger.yahoo.com

[Haskelldb-users] HaskellDB code now in SourceForge CVS

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

The CVS repository for HaskellDB is now hosted at SourceForge, since the 
old one at Chalmers University of Technology stopped working last week. 
The old repository was imported with the version information intact. I 
took the liberty of reorganizing the source code in the repo before 
importing, so that the source code directories match the module structure.


Anonymous read-only CVS access:

cvs -d:pserver:ano...@cv...:/cvsroot/haskelldb login

(empty password)

cvs -z3 -d:pserver:ano...@cv...:/cvsroot/haskelldb co 
haskelldb


Developer CVS access:

export CVS_RSH=ssh

cvs -z3 -d:ext:dev...@cv...:/cvsroot/haskelldb co 
haskelldb


/Bjorn

Re: [Haskelldb-users] Bug: overlapping instances in HDBRec.hs

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

Volker Wysk wrote:
> I get the following compiler error:
> 
> Overlapping instance declarations:
>   In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.HasField f (RecCons f a r)
>   In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.HasField f (RecCons g a r)
> 
> Overlapping instance declarations:
>   In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.SelectField f (RecCons f a r) a
>   In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.SelectField f (RecCons g b r) a
> 
> The compiler flag -fallow-overlapping-instances is needed. I'm not sure
> what triggers the error, since the compiler doesn't tell.

Are you using "-package haskelldb"? That should turn on overlapping 
instances.

If you are using "-package haskelldb" and still getting the error above, 
  check the extra_ghc_opts section of the output from:

$ ghc-pkg -s haskelldb

It should contain -fallow-overlapping-instances. If not, something has 
gone wrong in the installation, please provide your "ghc-pkg -s 
haskelldb" output.

Concerning all the other problems that you have reported, the HaskellDB 
CVS repo is still not working, I expect to work on moving to SourceForge 
CVS tonight.

/Bjorn

[Haskelldb-users] Bug: overlapping instances in HDBRec.hs

[Volker W. <po...@vo...>]

Hi

I get the following compiler error:

Overlapping instance declarations:
  In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.HasField f (RecCons f a r)
  In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.HasField f (RecCons g a r)

Overlapping instance declarations:
  In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.SelectField f (RecCons f a r) a
  In module Database.HaskellDB.HDBRec: Database.HaskellDB.HDBRec.SelectField f (RecCons g b r) a

The compiler flag -fallow-overlapping-instances is needed. I'm not sure
what triggers the error, since the compiler doesn't tell.

Bye

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] Bug: over-eager query optimizer

[Volker W. <po...@vo...>]

Hi.

I've stumbled into another bug, in version 0.9.

I want to query a table for whether a certain row exists. I don't want to
actually return anything. Since the query needs a project, I return a
constant as a dummy value. This is my query:

      erg <- query db $ do tab <- table T.t
                           restrict (       tab!T.praed .==. constant 1
                                      .&&.  tab!T.arg0 .==. constant 2
                                    )
                           project (T.praed << constant 0)

The query always returns the constant, regardless of whether the row
exists. It should only return a result when the row exists:

mysql> select 0 as praed;
+-------+
| praed |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 0 as praed from t where praed = 1 and arg0 = 2;
Empty set (0.01 sec)

mysql> insert into t (praed, arg0) values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select 0 as praed from t where praed = 1 and arg0 = 2;
+-------+
| praed |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)


I've traced the problem to Database.lazyQuery, and found that the actual
query is optimized away. The unoptimised query is (why so compliated?):

Project [("praed",AttrExpr "praed2")] (Project [("praed2",ConstExpr
(IntegerLit 0)),("praed1",AttrExpr "praed1"),("arg01",AttrExpr
"arg01"),("daten1",AttrExpr "daten1")] (Restrict (BinExpr OpAnd (BinExpr
OpEq (AttrExpr "praed1") (ConstExpr (IntegerLit 1))) (BinExpr OpEq
(AttrExpr "arg01") (ConstExpr (IntegerLit 2)))) (Project
[("praed1",AttrExpr "praed"),("arg01",AttrExpr "arg0"),("daten1",AttrExpr
"daten")] (BaseTable "t" ["praed","arg0","daten"]))))

SELECT DISTINCT praed2 as praed
FROM  (SELECT DISTINCT 0 as praed2,
                       praed1,
                       arg01,
                       daten1
       FROM  (SELECT DISTINCT praed as praed1,
                              arg0 as arg01,
                              daten as daten1
              FROM  t as T1) as T1
       WHERE ((praed1 = 1) AND (arg01 = 2))) as T1


The optimised query is:

Project [("praed",ConstExpr (IntegerLit 0))] Empty

SELECT DISTINCT 0 as praed


Regards,
Volker

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] Binary data bug fixed, patch included

[Volker W. <po...@vo...>]

Hello

I've (kind of) fixed the bug which I've reported before. The included
patch is for haskelldb-0.8. To apply, do in the HaskellDB top directory:

patch -p0 < sql-string-literal-patch

I couldn't find a specification of the syntax of string literals in SQL.
So I've implemented the syntax which MySQL accepts. Hopefully, it also
works with other backends. Most likely, each of the intended backends has
its own syntax, and doesn't care about the SQL standard. So the way
constants are quoted (ShowConstant class) needs to be made dependent on
the database backend (the comment in Query.hs already suggests this).

I was partially mistaken about what goes wrong. The real story is that
Haskell's show was applied by showConstant to a string constant, quoting
it as a Haskell string literal. This was then unsuccessfully undone in
PrimQuery.hs, and unsuccessfully requoted as an SQL string literal.

The way HaskellDB lets you formulate database queries in a type safe way
is great. But from what I've seen, the code appears very hackish, and
likely to contain more bugs. I wouldn't advise anybody to entrust his data
to it. I think considerable portions of it should be rewritten.

Cheers,
Volker

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

Re: [Haskelldb-users] help installing haskelldb

[arabil d. <ara...@ya...>]

Hey thanks. Works ... well partially. The configure
still does not know that I have installed hsql for
ghc, tho the hsql library seems to be located not
under the ghc own directory. The options dont seem to
let me set the path of ghc/hsql; any hint how to
proceed from here? 

Thanks! --A

--- Volker Wysk <po...@vo...> wrote:

> Have you tried "./configure --help"? Probably the
> "--with-ghc" option is
> the right thing for you.
> 
> --
> Volker Wysk <po...@vo...>
> http://www.volker-wysk.de
> On Sat, 4 Sep 2004, arabil drogue wrote:
> 
> > Hello,
> >
> > I am having problems installing haskelldb. When I
> run
> > configure, it fails to see that I have ghc. Now
> > possibly I need to setup some env variable, but I
> dont
> > know what evn var the cofigure of haskelldb
> expect.
> >
> > Help?
> >
> > Thanks. --A
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
> >
> >
>
-------------------------------------------------------
> > This SF.Net email is sponsored by BEA Weblogic
> Workshop
> > FREE Java Enterprise J2EE developer tools!
> > Get your free copy of BEA WebLogic Workshop 8.1
> today.
> >
>
http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click
> > _______________________________________________
> > Haskelldb-users mailing list
> > Has...@li...
> >
>
https://lists.sourceforge.net/lists/listinfo/haskelldb-users
> >
> >
> 
> 



	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

Re: [Haskelldb-users] help installing haskelldb

[Volker W. <po...@vo...>]

Have you tried "./configure --help"? Probably the "--with-ghc" option is
the right thing for you.

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de
On Sat, 4 Sep 2004, arabil drogue wrote:

> Hello,
>
> I am having problems installing haskelldb. When I run
> configure, it fails to see that I have ghc. Now
> possibly I need to setup some env variable, but I dont
> know what evn var the cofigure of haskelldb expect.
>
> Help?
>
> Thanks. --A
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by BEA Weblogic Workshop
> FREE Java Enterprise J2EE developer tools!
> Get your free copy of BEA WebLogic Workshop 8.1 today.
> http://ads.osdn.com/?ad_id=5047&alloc_id=10808&op=click
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>
>

[Haskelldb-users] help installing haskelldb

[arabil d. <ara...@ya...>]

Hello,

I am having problems installing haskelldb. When I run
configure, it fails to see that I have ghc. Now
possibly I need to setup some env variable, but I dont
know what evn var the cofigure of haskelldb expect. 

Help?

Thanks. --A

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [Haskelldb-users] HaskellDB is unusable

[Martin A. <ma...@dt...>]

<SNIP>
> 
> No attempt is made by HaskellDB to properly escape strings when composing
> an SQL query. Sql.hs needs to be rectified, and I'm willing to do that.
> I'm about to make the corrections to version 0.8 and send a patch.

You are wery much welcome... :)

> Is HaskellDB being maintained? Judging from the web page, there is
> development, but is any of the developers reading this mailing list ???
> Please send me a note.

Well as HaskellDB was developed as a project for 7 students at Chalmers 
University last year development has gone down in a little bit of slump, 
with summer vacation and other stuff, but as things settle down a little 
now in teh fall I expect that development (or support for that matter) 
will pick up at least a little more steam compared to today...

> >>> And please add a note to the HaskellDB homepage, informing the
> potential user that HaskellDB isn't fit for use right now. <<<

I do believe it's stated to be in alpha or development stage so yes, if 
it's not obvious from the homepage then it's definatly something to be put 
there.

--
Regards
Martin Andersson
 
> Cheers,
> V.W.
> 
> --
> Volker Wysk <po...@vo...>
> http://www.volker-wysk.de
> 

[Haskelldb-users] HaskellDB is unusable

[Volker W. <po...@vo...>]

Hi

I've investigated the bug which I've reported, concerning binary data.

This is the SQL query generated when the string (map chr [0..255]) is
inserted into my table:


INSERT INTO wert (kennung,
          typ,
          daten)
VALUES (1,
  1,
  '\NUL\SOH\STX\ETX\EOT\ENQ\ACK\a\b\t\n\v\...<snip>...\238\239\240\241\242\243\244\245\246\247\248\249\250\251\252\253\254\255')


It is generated by the following function in Sql.hs. The function makes a
component of a Doc, which is later pretty printed to produce the SQL
query.


ppInsert :: SqlInsert -> Doc
ppInsert (SqlInsertQuery table select)
	= text "INSERT INTO" <+> text table
        $$ ppSql select

ppInsert (SqlInsert table exprs)
    = text "INSERT INTO" <+> text table
      <+> parens (vcat $ punctuate comma (map text names))
      $$ text "VALUES"   <+> parens (vcat $ punctuate comma (map text values))
    where
    (names,values)        = unzip exprs


The problem is the (map text values) part. Unusual characters are escaped
by Text.PrettyPrint.HughesPJ, which does it the wrong way. The SQL server
doesn't understand the syntax produced. The problem doesn't affect only
binary data, but also printable eight bit characters.

No attempt is made by HaskellDB to properly escape strings when composing
an SQL query. Sql.hs needs to be rectified, and I'm willing to do that.
I'm about to make the corrections to version 0.8 and send a patch.

Is HaskellDB being maintained? Judging from the web page, there is
development, but is any of the developers reading this mailing list ???
Please send me a note.

>>> And please add a note to the HaskellDB homepage, informing the
potential user that HaskellDB isn't fit for use right now. <<<


Cheers,
V.W.

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] Serious bug when handling binary data

[Volker W. <po...@vo...>]

Hi

I'm using haskelldb-0.8 in conjunction with MySQL. I have a table with a
column of type tinyblob, in which I store binary data. dbdirect translates
this column to an attribute of type String:

data Md5 = Md5

instance FieldTag Md5 where fieldName _ = "md5"

md5 :: Attr Md5 String
md5 = mkAttr Md5


When I use mysqldump in order to dump the database contents to a text
file, I get the following:

...
INSERT INTO dateistatus VALUES ('Y4209ACK196222156177136250f140SOi169148',102);
INSERT INTO dateistatus VALUES ('144135X145/217CACK4Y182rJWm169',102);
INSERT INTO dateistatus VALUES ('226m211Z252219&k206194\"anD:129',102);
INSERT INTO dateistatus VALUES ('DC4165192196N203s219161209132NX205DC2255',102);
INSERT INTO dateistatus VALUES ('Ma224IH^,yiEMv139@|n+',102);
INSERT INTO dateistatus VALUES ('169L222DEL215%NR-253171`^a137P',102);
INSERT INTO dateistatus VALUES ('15219918621420!0H/176/216SOHz244',102);
INSERT INTO dateistatus VALUES ('_219RSuETBF135ENQ(\\215DC1DC2141134129',102);
...

Those funny strings are supposed to be MD5 sums in binary form. It seems
like non printing charactes are converted to sequences of digits.

Also, I don't get the original data back when querying the contents.

Bye

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] Bug: dbdirect doesn't work any longer in haskelldb-0.9

[Volker W. <po...@vo...>]

Hi

I get this:

cd build && dbdirect MySQL localhost hsskripte v ... hsskripte

DB/Direct, Daan Leijen (c) 1999, HWT (c) 2003-2004

checking arguments...
creating database specification...
creating modules from specification...
writing modules...

Fail: ./Hsskripte/Auslstatus.hs: openFile: does not exist (No such file or
directory)


The directory build/hsskripte is created, along with build/hsskripte.hs.
In 0.8 it was build/Hsskripte and build/Hsskripte.hs.

The database name no longer gets capitalized when making the file-,
directory- and MODULE-Name (in hsskripte.hs).

bye

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] small build bug

[Volker W. <po...@vo...>]

Hi.

Haddock fails when building the documentation of haskelldb-0.9. Prefix the
'<' characters in src/Query.hs, line 160 with backslashes.

bye

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de

[Haskelldb-users] Re: haskelldb help

[carlos a. <ca...@ci...>]

ok! thank you.

> carlos andreazza wrote:
> > I'm trying to use haskelldb, and I'm getting some difficult to understa=
nd
> > the examples showed in 'The HaskellDB Guide to Hacking'. Can you send m=
e
> > the source code of the module Farming.Farmers (Forum.Users will be of
> > great help too)?
>
> Hi Carlos,
>
> those modules are generated by the DbDirect program. If you create the
> tables using the SQL code in the guide and then run DbDirect against the
> database, those modules will be generated.
>
> It seems that the SQL code for creating the tables was missing from the
> guide. I have moved the guide to http://haskelldb.sourceforge.net/guide/
> and added the SQL code.
>
> Please let us know if there are any problems with this approach.
>
> /Bj=F6rn Bringert
>
> PS: it might be better to address any future questions to the
> has...@li... mailing list.
>

[Haskelldb-users] Re: haskelldb help

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

carlos andreazza wrote:
> I'm trying to use haskelldb, and I'm getting some difficult to understa=
nd
> the examples showed in 'The HaskellDB Guide to Hacking'. Can you send m=
e
> the source code of the module Farming.Farmers (Forum.Users will be of
> great help too)?

Hi Carlos,

those modules are generated by the DbDirect program. If you create the=20
tables using the SQL code in the guide and then run DbDirect against the=20
database, those modules will be generated.

It seems that the SQL code for creating the tables was missing from the=20
guide. I have moved the guide to http://haskelldb.sourceforge.net/guide/=20
and added the SQL code.

Please let us know if there are any problems with this approach.

/Bj=F6rn Bringert

PS: it might be better to address any future questions to the=20
has...@li... mailing list.

[Haskelldb-users] Re: Opinions/Guidance wanted for starting an undergraduate project

[<adr...@ya...>]

--- Anders_Höckersten <ch...@dt...> wrote:
> Hi. It's always nice to hear from someone is
> interested in HaskellDB. 
> I'll try to answer your questions as well as I can.
> As you may or may 
> not know, I am one of the HaskellDB developers. I am
> also an 
> undergraduate student starting my 4th year this
> autumn.
Yes, so I see.  An impressive piece of project work,
both for the practical significance and as a piece of
research :).
...
> It [HaskellDB] handles 
> dynamism through a Haskell concept called monads.
> db :: (Database -> IO a) -> IO a
> db = genericConnect "sqlite"
> ["./database","ReadWriteMode"]
> addCustomer :: Customer -> IO ()
> addCustomer c = db ( \db -> insert db C.customer
> (C.iD << constant (cid c) #
> C.name << constant (cname c) #
> C.address << constant (caddr c) #
> C.price << constant (cpric c)))
> getCustomers :: IO [Customer]
> getCustomers = db ( \db ->
> do t <- query db (table C.customer)
> return (map toCustomer t))

As I understand monads, you basically have a function
which evaluates to a tuple (s,a) where s represents
some state of a system at some point in 'time' and a
is the normal 'result value.'  And then using a small
number of operators you compose several such functions
and the output state of the first operation is fed in
as the input state to the second one, and so forth.
Then the final result is simply the final (s,a) and
the messiness of the implicit intermediate states is
hidden by some nice syntacic sugaring on Haskell's
part.  The whole process being roughly akin to piping
several schemas under the Z formal specification
method.  But, it's the assignment to variables over
multiple statements that is causing me some confusion.
 For example, assuming some previously declared
customer variables, c1 & c2:

a: IO a
a=addcustomer c1

So now in addition to the value 'unit' which you just
threw away, a also contains the new system state.

b: IO a
b=addcustomer c2
Performs similarly.  Now my question was going to be,
"What happens if I direct HaskellDB to perform a query
based upon the state information held in a?  Because
databases deal with real imperative states which
become overwritten, the DBMS has no way of accessing
the database's state which it was in between the two
operations after the second addcustomer transaction
has been committed."  However my latest bit of
guesswork leads my to conclude that you are working
slightly differently from my 'bottom-up' monads
thinking and are also throwing away the 'updated'
state information and then magicing the current state
of the database into each query statement using the db
function.

This idea works, but doesn't solve the (to some extent
philosophical) problem that functional languages are
static in nature, yet our database [variable] needs to
be updated.  From what I gather HaskellDB has dodged
this problem by only ever working with a "connection
function" which (after several intermediate levels)
boils down to being a pointer to some real dynamic
database in the imperative depths of the
implementation of HSQL somewhere.  The database
doesn't exist in it's own right in Haskell at all! 
Altough another thought is that you'd be right to
object and say that databases and tables don't exist
in their own right in imperative languages either and
we'd be dealing with references via connection
functions again - except: We can wrap up such
semantics inside an object, yielding a clean (in OOP
terms) method based interface.  But variables by
reference/pointers are not a standard piece of the
functional programming paradigm, (even though SML
explicitly supports such things).  I'm not entirely
sure what my supervisor was proposing, but something
along the lines of using monads as in my examples
above, thus yielding a sort of "history of states,"
somehow prohibiting asking for anything but the
current state (the tricky part) and rely on lazy
evaluation so that the system never attempts to
compute the uncomputable past states.

> >(P.S: I haven’t actually managed to get a working
> copy
> >of HaskellDB installed yet.  GHC compiles under
> >Mandrake but fails to work.  HSQL - both Windows
> >binary and compiling from sources under Cygwin
> both fail to work. :( )
> Too bad. If you provide me with some more
> information I might be able to 
> help you with this.

GHC 6.2.1
<interactive>:1:
    Could not find interface file for `GHC.Exception'
    (use -v to see a list of the files searched for)

<interactive>:1:
    Could not find interface file for `GHC.Handle'
    (use -v to see a list of the files searched for)

<interactive>:1:
    Could not find interface file for `GHC.IO'
    (use -v to see a list of the files searched for)

<interactive>:1:
    Could not find interface file for
`System.IO.Error'
    (use -v to see a list of the files searched for)

<interactive>:1:
    Failed to load interface for `GHC.Base':
        Could not find interface file for `GHC.Base'
        (use -v to see a list of the files searched
for)
ghc-6.2.1: panic! (the `impossible' happened, GHC
version 6.2.1):
        interactiveUI:setBuffering

Please report it as a compiler bug to
gla...@ha...,
or http://sourceforge.net/projects/ghc/.


It was maked/compiled from sources under Mandrake 10,
2.4 kernel, following the install instructions given
and compiled without any errors.  (There's no
pre-compiled RPM given for Mandrake.)  Anyway having
thought that assembling the three components would be
easier under Linux, I reverted to Windows and hunted
down binaries of GHC 6.2.1, HSQL 1.4 and HaskellDB
0.8. The strange thing is that the HSQL binary only
installs on GHC 6.2.1, yet when I do install it and
run a HaskellDB example program I receive a message
saying that "package HSQL found version 6.2.0, was
expecting version 6.2.1."

Regards,

Adrian Hudnott




	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com

Re: [Haskelldb-users] Opinions/Guidance wanted for starting an undergraduate project

[<ch...@dt...>]

Hi. It's always nice to hear from someone is interested in HaskellDB.=20
I'll try to answer your questions as well as I can. As you may or may=20
not know, I am one of the HaskellDB developers. I am also an=20
undergraduate student starting my 4th year this autumn.

Adrian Hudnott wrote:

>Hi.  I'm new to Haskell but am exploring the
>possibilities for extending the functionality of
>functional programming languages in for my
>undergraduate 3rd year project.  Along with graphical
>user interfaces databases seems one area where
>functional languages are typically less developed than
>imperative languages.  While reading C J Date's
>classic book I was inspired by the example of Datalog
>applied to logic programming to think about similar
>correspondences between relational databases and
>functional programming, and realize that such
>correlations are strong.  I=92m looking to build a
>pre-compiler and database access extension for a
>mainstream functional language, based upon relational
>algebra and the essence of types, tuples, etc. as
>opposed to an SQL wrapper.  Then I came across
>HaskellDB and realized that this has already been
>done!  I also dug out some old papers which
>integrate the functional paradigm and databases even
>closer, such as FDM/DAPLEX (Shipman, 1981) and FQL
>(Buneman, 1979).  In a nutshell: Buneman reduces all
>database queries into terms of functional composition,
>mapping, filtering and tuple building without loosing
>much clarity!  There appear to be three possibilities
>for where I could go from here:
>
>1)Do you (the developers, including Dr Leijen) have
>any particular applications which you=92d like to see
>HaskellDB put to use for?  Perhaps one of these will
>inspire me to head off in a different direction.
> =20
>
I'm not sure Dr Leijen reads this mailing list, so you might want to=20
contact him directly if you wish for his opinion. There are of course=20
lots of applications that could be created using HaskellDB. Most of them=20
involve combining HaskellDB and some other Haskell library. I am=20
currently developing a combined HaskellDB/wxHaskell program (a rather=20
dull GUI program that needs to access a database). Some of my colleagues=20
made a basic web forum by combining HaskellDB and WASH/CGI.

>=20
>2)Investigate the possibilities for taking the
>HaskellDB project further in some way.
> =20
>
There is some interesting work to be done here, that we have been=20
thinking about, specifically we want to add a non-SQL backend to=20
HaskellDB, perhaps something like Hardbms (nice since it is defined=20
completely in Haskell) The problem with this is that it requires lots of=20
internal restructuring of HaskellDB, which might prove too difficult (or=20
rather, time-consuming) for someone not familiar with the code. This is=20
something that's on the top of my list for future developments I want to =
do.

>3)The most ambitious and my favourite option: To
>start from scratch at implementing something based
>upon
>similar principles to HaskellDB in NJ
>SML/MLj/Java/JDBC or SML.NET/J# and javacc or SML
>(which is much more the domain of my existing skills
>set).  My supervisor has some reservations, and asks
>how much HaskellDB relies on laziness, in determining
>whether this is practical?  The documentation on
>HaskellDB seems a little sparse (with me being new to
>Haskell syntax as well!). How are you modelling the
>=93dynamism=94 of a database?  Meaning: when [say] an
>insert operation is performed do you return =93a new
>database=94?  If so, what happens if you try to
>perform an operation using a variable which still
>points to =93the old database=94?
> =20
>
This sounds quite interesting, although it does seem like a rather large=20
project. I agree that the HaskellDB documentation is lacking, especially=20
for beginners. I recommend the following resources, in order:
- Daan's and Erik Meijer's original paper, see=20
http://www.cs.uu.nl/people/daan/download/papers/dsec.ps
- Daan's PhD thesis, chapter 5 talks about HaskellDB, see2
http://www.cs.uu.nl/people/daan/download/papers/phd-thesis.pdf
- Our as of yet unpublished paper, found at=20
http://haskelldb.sourceforge.net/haskelldb.ps

HaskellDB actually doesn't rely on laziness very much. It handles=20
dynamism through a Haskell concept called monads. I won't discuss them=20
further here. If you're not familiar with them, for now you may think of=20
them as a way to allow us to perform I/O in a purely functional way.

I'm going to try and explain with some examples from my own program.

A database is normally modeled with a function of type:
db :: (Database -> IO a) -> IO a
db =3D genericConnect "sqlite" ["./database","ReadWriteMode"]

So here "db" is my database function. It takes another function as an=20
argument. It then uses this function to perform some action in the real=20
world, returning that result. It is used in the following way:

addCustomer :: Customer -> IO ()
addCustomer c =3D db ( \db -> insert db C.customer
(C.iD << constant (cid c) #
C.name << constant (cname c) #
C.address << constant (caddr c) #
C.price << constant (cpric c)))

Here our database is applied to the insert function. What this actually=20
does is that db connects to the database and performs the actions=20
specified, then disconnects and returns the result (which here is=20
irrelevant).

getCustomers :: IO [Customer]
getCustomers =3D db ( \db ->
do t <- query db (table C.customer)
return (map toCustomer t))

Here is another example. This queries the database for all our=20
customers, applies a Haskell function to them via map, and returns the=20
result of that operation.

I hope this cleared things up a bit.

>(P.S: I haven=92t actually managed to get a working copy
>of HaskellDB installed yet.  GHC compiles under
>Mandrake but fails to work.  HSQL - both Windows
>binary and compiling from sources under Cygwin both
>fail to work. :( )
> =20
>
Too bad. If you provide me with some more information I might be able to=20
help you with this.

Regards,
Anders

[Haskelldb-users] Opinions/Guidance wanted for starting an undergraduate project

[<adr...@ya...>]

Hi.  I'm new to Haskell but am exploring the
possibilities for extending the functionality of
functional programming languages in for my
undergraduate 3rd year project.  Along with graphical
user interfaces databases seems one area where
functional languages are typically less developed than
imperative languages.  While reading C J Date's
classic book I was inspired by the example of Datalog
applied to logic programming to think about similar
correspondences between relational databases and
functional programming, and realize that such
correlations are strong.  I’m looking to build a
pre-compiler and database access extension for a
mainstream functional language, based upon relational
algebra and the essence of types, tuples, etc. as
opposed to an SQL wrapper.  Then I came across
HaskellDB and realized that this has already been
done!  I also dug out some old papers which
integrate the functional paradigm and databases even
closer, such as FDM/DAPLEX (Shipman, 1981) and FQL
(Buneman, 1979).  In a nutshell: Buneman reduces all
database queries into terms of functional composition,
mapping, filtering and tuple building without loosing
much clarity!  There appear to be three possibilities
for where I could go from here:

1)Do you (the developers, including Dr Leijen) have
any particular applications which you’d like to see
HaskellDB put to use for?  Perhaps one of these will
inspire me to head off in a different direction.
 
2)Investigate the possibilities for taking the
HaskellDB project further in some way.
 
3)The most ambitious and my favourite option: To
start from scratch at implementing something based
upon
similar principles to HaskellDB in NJ
SML/MLj/Java/JDBC or SML.NET/J# and javacc or SML
(which is much more the domain of my existing skills
set).  My supervisor has some reservations, and asks
how much HaskellDB relies on laziness, in determining
whether this is practical?  The documentation on
HaskellDB seems a little sparse (with me being new to
Haskell syntax as well!). How are you modelling the
“dynamism” of a database?  Meaning: when [say] an
insert operation is performed do you return “a new
database”?  If so, what happens if you try to
perform an operation using a variable which still
points to “the old database”?

(P.S: I haven’t actually managed to get a working copy
of HaskellDB installed yet.  GHC compiles under
Mandrake but fails to work.  HSQL - both Windows
binary and compiling from sources under Cygwin both
fail to work. :( )

Regards,

Adrian Hudnott



	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com

Re: [Haskelldb-users] Demo

[Martin A. <ma...@dt...>]

Oki, have added login info to the homepage...

/mardy

> > Hello,
> > 
> > Is there a username, password for testing the Demo at
> > http://jackass.tekno.chalmers.se/dp03-7/cgi/run ?
> > 
> > Sorry if I'm being dense but I couldn't see anything about how to log in
> > to see the forum in action.
> > 
> > -Rob
> > 
> Thank you for testing our demo application.
> 
> Sorry for not having this information on the website.
> 
> Login:		guest
> Password:	guest 
> 
> Hopefully the homepage will be updated soon.
> 
> -- 
> //
>   Conny Andersson
>   for...@dt...
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: Oracle 10g
> Get certified on the hottest thing ever to hit the market... Oracle 10g.
> Take an Oracle 10g class now, and we'll give you the exam FREE.
> http://ads.osdn.com/?ad_id149&alloc_id?66&op=click
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users

-- 
/mardy