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

[Haskelldb-users] data structures over tables

[Frederik E. <fre...@a5...>]

Hi,

This question is only partly about haskelldb.

I'm thinking about implementing special indexing data structures over
SQL tables in databases that might not support those structures
natively, for instance an R-tree for multidimensional range queries (I
think mysql supports only up to dimension 2) or a simple balanced tree
for fast calculation of a running total of a given column. (data
structure links would be to primary key columns of tables)

I thought that haskelldb might be a good candidate for such an
implementation because of the ability to achieve modularity by
composing query functions, and also the potential to minimize context
switches by sending large chunks of SQL code to the server at once...
But presumably the datatypes would require atomicity in the form of
locking or transactions, which haskelldb would have to support, as
well as 'if' statements or 'if' functions for logic. An alternative
would be to write stored procedures directly in SQL. (can haskelldb
generate these?) A separate issue is enforcing type constraints, such
as saying that an 'id' value in a certain column can only be used to
look up elements of certain other columns, it seems like it could be a
capability of haskelldb, is it?

Anyway, it seems like a promising use for SQL to me - implement
complex large data structures in a portable way - but I've found very
few relatively unhelpful references to it on the web, partly because I
don't know a search term which doesn't select lots of pages with
information on data structures used internally by sql databases. So if
anybody has any suggestions or pointers I'd be interested. Thanks,

Frederik

-- 
http://ofb.net/~frederik/

[Haskelldb-users] opinions about some bugs

[Santoemma E. <Enr...@be...>]

SGVsbG8sDQogDQpJJ2QgbGlrZSB0byBzaGFyZS9hc2sgb3BpbmlvbnMgYWJvdXQgdGhlc2UgYnVn
czoNCi0gUmVwZWF0ZWQgZmllbGQgbmFtZXMgZG9uJ3Qgd29yaw0KLSBNUyBBY2Nlc3MgVElNRVNU
QU1QOiAiUHJvZ3JhbSB0eXBlIG91dCBvZiByYW5nZSIgKGFjdHVhbGx5IE9yYWNsZSB0b28pDQot
IFBvc3RncmVTUUwgT0RCQyB0cmVhdHMgVElNRVNUQU1QIGFzIHN0cmluZyAoT3JhY2xlIHRvbykN
Ci0gQ2FuJ3QgaW5zZXJ0IFRJTUUgdmFsdWVzIGluIFBvc3RncmVTUUwgKE9yYWNsZSB0b28pDQot
IERJU1RJTkNUIG1hbmRhdG9yeQ0KIA0KLSBBYm91dCB0aGUgZmlyc3QsIHRoaXMgZnJhZ21lbnQg
c2hvd3MgY2xlYXJseSB0aGF0IHRoZXJlIGlzIGEgcHJvYmxlbSBpbiB0aGUgY29kZSB3aGljaCBn
ZW5lcmF0ZXMgZnJlc2ggYWxpYXNlczoNClNFTEVDVCBESVNUSU5DVCBPSUQyICBPSUQsDQogIE9J
RDIgIE9JRA0KRlJPTSAgKFNFTEVDVCBESVNUSU5DVCBPSUQgIE9JRDINCiAgICAgICBGUk9NICBz
aXRpICBUMSkgIFQxLA0KICAgICAgKFNFTEVDVCBESVNUSU5DVCBPSUQgIE9JRDENCiAgICAgICBG
Uk9NICBzaXN0ZW1hX3BhcmFtZXRyaSAgVDEpICBUMg0KLi4uDQogDQpUaGUgc2Vjb25kIGxpbmUg
c2hvdWxkIGhhdmUgYmVlbjogT0lEMSBPSUQNCiANCkkgcHV0IHNvbWUgdHJhY2VzIGFyb3VuZCwg
YW5kIGZvdW5kIHRoYXQgdGhlIHF1ZXJ5IG1vbmFkIGlzIGJ1Z2d5LiBTdGlsbCBmaW5kaW5nIG91
dCB3aGVyZSwgYnV0IHBlcmhhcHMgc29tZWJvZHkgd2hvIGtub3dzIHRoZSBjb2RlIGJldHRlciBj
b3VsZCBjaGVjayBpdC4gSXQncyBhIHNlcmlvdXMgYnVnLg0KIA0KQnkgdGhlIHdheSwgZG8gZXZl
cnlib2R5IGtub3cgdGhhdCBoc3FsIHJlYWRzIGZpZWxkIGJ5IG5hbWUgbG9va3VwIGFuZCB3eCBi
eSBudW1iZXIgdG9vPyBUaGlzIGZpeGVzIGhhbGYgb2YgdGhlIGJ1ZyAodGhlIHR3byBPSUQgbWF5
IGJlIHJlYWQgcHJvcGVybHkpLCBhbmQgSSBiZWxpZXZlIGl0J3MgYmV0dGVyIG5vdCB0byB0cnVz
dCBmaWVsZCBuYW1lcyB3aGVuIHJldHJpZXZpbmcgZGF0YS4gSSB0cmllZCwgYW5kIGl0J3MgYSBv
bmUtbGluZSBmaXggb24gd3guaHMgYW5kIGVhc3kgdG9vIG9uIGhzcWwsIGJ1dCB0aGVyZSB0aGUg
cGF0Y2ggbXVzdCBiZSBvbiB0aGUgaHNxbCBwYWNrYWdlLg0KVGhpcyBjb3VsZCBhbHNvIGZpeCBP
cmFjbGUncyB1cHBlciBjYXNlIGJlaGF2aW91ci4NCiANClRoaXMgaXMgdGhlIGNvZGU6DQogDQp3
eFByaW1RdWVyeSBjb25uZWN0aW9uIHNxbCBzY2hlbWUgcmVsID0gZG8NCiAgICBoYW5kbGVEYkVy
cm9yICQgV1guZGJRdWVyeSBjb25uZWN0aW9uIHNxbCBnZXRSZXN1bHRzDQogd2hlcmUgZ2V0UmVz
dWx0cyA9IGdldFJlYyB3eEdldEluc3RhbmNlcyByZWwgc2NoZW1lJw0KICAgICAgIHNjaGVtZScg
PSBtYXAgc2hvdyBbMS4ubGVuZ3RoIHNjaGVtZV0NCg0KIA0KLSBBYm91dCB0aGUgZGF0ZXMsIHRo
ZXkgYXJlIHN1Y2ggYSBtZXNzIHRoYXQgSSBtb2RpZmllZCB3eCB0byB3b3JrIHdpdGggc3RyaW5n
czoNCiANCnd4R2V0Q2FsZW5kYXJUaW1lIDo6IERiUm93IGEgLT4gU3RyaW5nIC0+IElPIChNYXli
ZSBDYWxlbmRhclRpbWUpDQp3eEdldENhbGVuZGFyVGltZSByIGYgPSBkbw0KICBjYWwgPC0gV1gu
ZGJSb3dHZXRTdHJpbmcgciBmDQogIHJldHVybiAkIHN0cmluZ1RvQ2FsZW5kYXJUaW1lIGNhbA0K
ICANCnN0cmluZ1RvQ2FsZW5kYXJUaW1lIDo6IFN0cmluZyAtPiBNYXliZSBDYWxlbmRhclRpbWUN
CnN0cmluZ1RvQ2FsZW5kYXJUaW1lICIiID0gTm90aGluZw0Kc3RyaW5nVG9DYWxlbmRhclRpbWUg
cyAgPQ0KICBsZXQgW3llYXIsXyxtb250aCxfLGRheSxfLGhvdXIsXyxtaW51dGUsXyxzZWNvbmRd
ID0gc3BlenphIFs0LDEsMiwxLDIsMSwyLDEsMiwxLDJdIHMNCiAgIGluICBKdXN0ICQgQ2FsZW5k
YXJUaW1lIHllYXIgKHRvRW51bSAobW9udGgtMSkpIGRheSBob3VyIG1pbnV0ZSBzZWNvbmQgMCAo
dG9FbnVtIDApIDAgIiIgMCBGYWxzZQ0KIA0Kc3BlenphIDo6IFtJbnRdIC0+IFN0cmluZyAtPiBb
SW50XQ0Kc3BlenphIFtdIF8gICAgID0gW10NCnNwZXp6YSAoeDp4cykgcyA9IFtyZWFkICQgdGFr
ZSB4IHMgXSArKyBzcGV6emEgeHMgKGRyb3AgeCBzKQ0KIA0KSXQncyBhIGJlZ2lubmluZy4gSSBi
ZWxpZXZlIHRoaXMgcm9hZCBpcyBiZXR0ZXIsIGFzIGFueSBkYiBoYXMgdG9vIGRpZmZlcmVudCBi
ZWhhdmlvdXIgd2l0aCBkYXRlcy4NCiANCi0gRElTVElOQ1QuIEkgY2FuJ3Qgc2VlIGFueSByZWFz
b24gdG8gbW9kaWZ5IHRoaXMgYmVoYXZpb3VyLiBBIHBvbGl0ZSB0YWJsZSBzaG91bGQgaGF2ZSBn
b3QgYSB1bmlxdWUgZmllbGQuIEEgc2V0IG9mIGlkZW50aWNhbCByZWNvcmRzIGlzIHVzZWxlc3Ms
IGltaG8uDQogDQpUaGF0J3MgYWxsLiBTb3JyeSBmb3IgdGhpcyBsb25nIHBvc3QuDQpBbmQsIGJ5
IHRoZSB3YXksIG15IHZpdmlkIGNvbXBsaW1lbnRzIHRvIHRoZSBvcmlnaW5hbCBhdXRob3IgYW5k
IHRvIHRoZSBTd2VkaXNoIGNyZXcuDQogDQpFbnJpY28NCiANCg==

[Haskelldb-users] [ haskelldb-Bugs-1123625 ] Multiple errors generating SELECT

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

Bugs item #1123625, was opened at 2005-02-15 21:30
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=1123625&group_id=101095

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: John Goerzen (jgoerzen)
Assigned to: Nobody/Anonymous (nobody)
Summary: Multiple errors generating SELECT

Initial Comment:
I am using HSQL ODBC backend.

First, the space before FROM is sometimes omitted:

eu_mod_userid,eu_mod_dateFROM  eu_mstr as T1

This query generated with:

do t <- table eu_mstr
   return t

Second, space before WHERE is also omitted at times:

teFROM  eu_mstr as T1WHERE (eu_cm_nbr = 'xxxxxx')

This query generated with:

do t <- table eu_mstr
   restrict (t!eu_cm_nbr .==. constJust dealer)
   return t

Third, sorting causes invalid column names to be selected:

SELECT DISTINCT eu_addr1 as eu_addr,eu_rsb1 as eu_rsb,
....

There is no eu_addr1 column; eu_addr only.

This query generated by:

do t <- table eu_mstr
   restrict (t!eu_cm_nbr .==. constJust dealer)
   order [asc t eu_sort]
   return t

All SQL dumps were obtained using the database's own
logging facility, which captures exactly the text sent
to it.

Finally, if I add a project statement to the end of all
of this, to reduce server load and gather only the
columns I want, it makes no difference to the generated
query; it still does the eu_addr1 thing and selects all
of the several dozen columns in the table.  I only care
about 2.

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

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

[Haskelldb-users] [ haskelldb-Bugs-1122758 ] DISTINCT mandatory

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

Bugs item #1122758, was opened at 2005-02-14 17:57
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=1122758&group_id=101095

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: John Goerzen (jgoerzen)
Assigned to: Nobody/Anonymous (nobody)
Summary: DISTINCT mandatory

Initial Comment:
There appears to be no way to turn off DISTINCT in the
SELECT query.  I don't know why this is the default (it
seriously hinders performace on many backends), and
being unable to disable it renders haskelldb unusable
for various purposes (esp. for tables that hold event
logs and the like)

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

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

[Haskelldb-users] [ haskelldb-Bugs-1122530 ] DBDirect generates uncompilable code

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

Bugs item #1122530, was opened at 2005-02-14 12:22
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=1122530&group_id=101095

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: John Goerzen (jgoerzen)
Assigned to: Nobody/Anonymous (nobody)
Summary: DBDirect generates uncompilable code

Initial Comment:
Hi,

Most of the code DBDirect generates works fine. 
However, the attached file doesn't build; the error is:

./DBMFGPro/Exr_rate.hs:4:
    Multiple declarations of `DBMFGPro.Exr_rate.exr_rate'
        ./DBMFGPro/Exr_rate.hs:28
        ./DBMFGPro/Exr_rate.hs:102

And indeed, it looks like ghc has a valid complaint here.

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

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

Re: [Haskelldb-users] Problem configuring

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

Robert van Herk wrote:
> Hi all,
>=20
> I have a problem with configuring haskelldb:
>=20
> I am trying to build haskelldb, with support for ghc-hsql.
>=20
> I do not have root permissions on this box. Therefore, I made a local=20
> file containing my ghc package configuration. The local file is in=20
> /Users/rherk/soft/package.conf. It does have an entry for hsql, and the=
=20
> entry is correct. When I do:
>=20
> ./configure --with-ghc-pkg=3D"ghc-pkg -f /Users/rherk/soft/package.conf=
"=20
> --prefix=3D/Users/rherk/soft/haskelldb
>=20
> I end up with:
>=20
> checking for ghc-pkg -f /Users/rherk/soft/package.conf... no
> configure: error: ghc-pkg is required for GHC support
>=20
> So, I though I'd make a script in ~/soft/ghc-pkg, namely:
>=20
> ghc-pkg -f /Users/rherk/soft/package.conf $@
>=20
> Then, I try:
>=20
> ./configure --with-ghc-pkg=3D"/Users/rherk/soft/ghc-pkg"=20
> --prefix=3D/Users/rherk/soft/haskelldb --enable-ghc-hsql
>=20
> Now, I get:
> configure: WARNING: HSQL for ghc not found, not
>                 building ghc HSQL support
>=20
> and I end up with:
> HaskellDB will support:
> -----------------------
>=20
> GHC:               yes
>  HSQL:            no
>    ODBC:          no
>    MySql:         no
>    SQLite:        no
>    PostgreSQL:    no
>  WxHaskell:       no
> Hugs:              no
>  HSQL:            no
>    ODBC:          no
>    MySql:         no
>    SQLite:        no
>    PostgreSQL:    no
>  WxHaskell:       no
>=20
> My own ghc-pkg script does however seem to work:
> ./ghc-pkg -l
> /Users/rherk/soft/package.conf:
>    hsql
> /usr/local/lib/ghc-6.2.1/package.conf:
>    rts, base, haskell98, haskell-src, network, parsec, QuickCheck,
>    readline, OpenGL, GLUT, unix, lang, concurrent, posix, util, data,
>    text, net, hssource, uust
>=20
> Does anyone have any idea on what might be wrong?

I think that:

./configure --with-ghc-flags=3D-package-conf=3D/Users/rherk/soft/package.=
conf

should work, but I haven't tested it.

I think that the problem is that when the configure script checks for=20
the presence of some module, it uses ghc, not ghc-pkg, since ghc must be=20
able to find the module later during the compilation. I guess you could=20
make another script which calls ghc and uses your local package conf.=20
You could then use --with-ghc to use it for compilation. Using=20
--with-ghc-flags as above should also work.

/Bj=F6rn

[Haskelldb-users] Problem configuring

[Robert v. H. <rh...@cs...>]

Hi all,

I have a problem with configuring haskelldb:

I am trying to build haskelldb, with support for ghc-hsql.

I do not have root permissions on this box. Therefore, I made a local 
file containing my ghc package configuration. The local file is in 
/Users/rherk/soft/package.conf. It does have an entry for hsql, and the 
entry is correct. When I do:

./configure --with-ghc-pkg="ghc-pkg -f /Users/rherk/soft/package.conf" 
--prefix=/Users/rherk/soft/haskelldb

I end up with:

checking for ghc-pkg -f /Users/rherk/soft/package.conf... no
configure: error: ghc-pkg is required for GHC support

So, I though I'd make a script in ~/soft/ghc-pkg, namely:

ghc-pkg -f /Users/rherk/soft/package.conf $@

Then, I try:
 
./configure --with-ghc-pkg="/Users/rherk/soft/ghc-pkg" 
--prefix=/Users/rherk/soft/haskelldb --enable-ghc-hsql

Now, I get:
configure: WARNING: HSQL for ghc not found, not
                 building ghc HSQL support

and I end up with:
HaskellDB will support:
-----------------------

GHC:               yes
  HSQL:            no
    ODBC:          no
    MySql:         no
    SQLite:        no
    PostgreSQL:    no
  WxHaskell:       no
Hugs:              no
  HSQL:            no
    ODBC:          no
    MySql:         no
    SQLite:        no
    PostgreSQL:    no
  WxHaskell:       no

My own ghc-pkg script does however seem to work:
./ghc-pkg -l
/Users/rherk/soft/package.conf:
    hsql
/usr/local/lib/ghc-6.2.1/package.conf:
    rts, base, haskell98, haskell-src, network, parsec, QuickCheck,
    readline, OpenGL, GLUT, unix, lang, concurrent, posix, util, data,
    text, net, hssource, uust

Does anyone have any idea on what might be wrong?

Robert

Re: [Haskelldb-users] alias problem with Oracle

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

Santoemma Enrico wrote:
> is there a way to avoid the "as" when building a "from" clause: "from
> mytable as T1"? Oracle, through ODBC, seems not to like it, giving
> something like "sql command unproperly terminated"

Well, there is no way to avoid it in the current version. You could of=20
course try to hack it yourself. However, you might get into trouble if=20
the resulting query uses the same table twice.

There is a known bug with table aliases when using union:
http://sourceforge.net/tracker/index.php?func=3Ddetail&aid=3D961104&group=
_id=3D101095&atid=3D629040

But it doesn't seem like AS would cause an error like that, could it be=20
something else in the query? Did you try using showSql to see the SQL=20
query? Does Oracle not allow table aliases?

> More generally, I believe that an sql wrapper should consider the
> different db syntaxes. This is a common topic to other wrappers too.=20
> Opinions?

Yes, this has been discussed before, and it seems most people agree that=20
it would be useful to tailor the SQL generation to the particular RDBMS=20
being used. This can be tricky with ODBC, since HaskellDB will have=20
trouble figuring out which RDBMS it is talking too. But maybe this could=20
just be another parameter to the connect function.

Another related topic is implementing non-SQL backends, which would be=20
interesting if not just to see how general HaskellDB really is.

/Bj=F6rn

[Haskelldb-users] alias problem with Oracle

[Santoemma E. <enr...@be...>]

Hello,

is there a way to avoid the "as" when building a "from" clause: "from =
mytable as T1"?
Oracle, through ODBC, seems not to like it, giving something like "sql =
command unproperly terminated"

I found it in Sql.hs. I guess that I could make a little change and =
recompile, just to keep on testing HaskellDB on my project

More generally, I believe that an sql wrapper should consider the =
different db syntaxes. This is a common topic to other wrappers too.
Opinions?

Thanks,
Enrico

Re: [Haskelldb-users] HaskellDB in an industrial environment

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

Shae Matijs Erisson wrote:
> Bjorn Bringert <d00...@dt...> writes:
>=20
>>Good idea! If you move the stuff to the wiki, I'll update the
>>HaskellDB homepage. Assuming there are no objections from anyone?
>=20
> I've moved the HaskellDBTutorial from AvianWiki to HaWiki.
> The rest of the docs would also be welcome on HaWiki.

Ok, I've changed the tutorial link. Anyone wanna move the other docs?

>>Eh, right. The Chalmers computer support group seems to have changed
>>the installation. I have removed the link from the homepage. Maybe we
>>can put a demo app somewhere else, or link to an existing one. Shae?
>=20
> Sure, is the latest demo app source available somewhere?
> I have HaskellDB installed on ScannedInAvian, so it's not a problem.

I think the DEMO code is available from the homepage, but I don't really=20
know anything about it. Mary, Martin, Torbj=F6rn; any comments?

I was thinking that there might be other demo apps to put up too. There=20
could be a wiki page with links to apps using HaskellDB. Hackage,=20
Curryspondence and BloB off the top of my head, I'm sure there are others=
.

> I hear rumors that HaskellDB will be a Cabal/debian package soon too.

Sweet! Who's working on that?

/Bj=F6rn

Re: [Haskelldb-users] HaskellDB in an industrial environment

[Shae M. E. <sh...@Sc...>]

Bjorn Bringert <d00...@dt...> writes:

> Good idea! If you move the stuff to the wiki, I'll update the
> HaskellDB homepage. Assuming there are no objections from anyone?

I've moved the HaskellDBTutorial from AvianWiki to HaWiki.
The rest of the docs would also be welcome on HaWiki.

> Eh, right. The Chalmers computer support group seems to have changed
> the installation. I have removed the link from the homepage. Maybe we
> can put a demo app somewhere else, or link to an existing one. Shae?

Sure, is the latest demo app source available somewhere?
I have HaskellDB installed on ScannedInAvian, so it's not a problem.

I hear rumors that HaskellDB will be a Cabal/debian package soon too.
-- 
Shae Matijs Erisson - http://www.ScannedInAvian.com/ - Sockmonster once said:
You could switch out the unicycles for badgers, and the game would be the same.

Re: [Haskelldb-users] HaskellDB in an industrial environment

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

Frederik Eaton wrote:
>>Santoemma Enrico wrote:
>>
>>>1) Is the library too young for a real world project? Does it work
>>>with Oracle (throuhg ODBC)?
>>
>>There are still significant bugs in HaskellDB, see the open bugs in
>>http://sourceforge.net/tracker/?group_id=3D101095&atid=3D629040
>=20
>=20
> What's the biggest bug? "No explicit grouping"?

I'm not sure. AFAIK, there are no bugs which make HaskellDB generally=20
unusable, but different people will have different needs. Actually now=20
tha you mention it "No explicit grouping" feels more like a request for=20
enhancement, which are tracked by the RFE tracker at:

http://sourceforge.net/tracker/?group_id=3D101095&atid=3D629043

The bugs I personally think are the worst are the high priority ones in=20
the bug tracker.


> Also, it would be nice to have somewhere -- maybe in the wiki? -- some
> examples of SQL queries which can be done and which can't be done in
> HaskellDB. I have a program which I've started writing using regular
> SQL statements, and I'm wondering if I should translate it to
> HaskellDB. Something like this would help me decide. For example here
> are some queries, variables with $ represent parameters... (it's a
> simple flash card program)

I don't have time to write the HaskellDB code for you right now, it=20
being Saturday night and all, but see the comments below.

> select id from cards where cards.id not in
>     (select alg_data.id from alg_data) order by rand() limit 1

HaskellDB does not currently support queries in expressions, as needed=20
by the IN operator, see the RFE:
http://sourceforge.net/tracker/index.php?func=3Ddetail&aid=3D961869&group=
_id=3D101095&atid=3D629043

There is also no rand() function, but that can quite easily be added by=20
the used.

> select id from alg_data where uid =3D $uid and
>     when_eligible < $vt and
>     id like $pat order by curr_delay desc limit 1

Should be ok.

> select max(show_num) from hist where (uid,id)=3D($uid,$id)

Seems easy enough.

> select min(a.s), avg(a.s), max(a.s), sum(a.s) from=20
>     (select id,max(show_num) s from hist where=20
>             uid =3D $uid and
>             id in (select id from alg_data where
>                           uid=3D$uid and when_eligible < $vt)
>                    group by id) as a

IN expressions are not supported, see above. This query could be done as=20
a join instead, right? (It's saturday night, you don't expect me to=20
actually think, do you? :-P )

> P.S. Is there just the tutorial in wiki-form? Why not put the rest of
> the documentation on a wiki, so people can add to it?

Good idea! If you move the stuff to the wiki, I'll update the HaskellDB=20
homepage. Assuming there are no objections from anyone?

> P.P.S. The test application says "Server error!"...
>=20
> http://jackass.tekno.chalmers.se/dp03-7/cgi/run

Eh, right. The Chalmers computer support group seems to have changed the=20
installation. I have removed the link from the homepage. Maybe we can=20
put a demo app somewhere else, or link to an existing one. Shae?


/Bj=F6rn

Re: R: [Haskelldb-users] HaskellDB in an industrial environment

[Bjorn B. <bri...@cs...>]

Jeremy Shaw wrote:
> Bjorn Bringert wrote:
>>Santoemma Enrico wrote:
>>>Btw, is it possible that the bug 'Repeated field names don't work' is
>>>due to the fact that one cannot read the same field two times when
>>>using ODBC? This is an old odbc error that I often (still) meet. In
>>>haskell I strictly retrieve the record into memory before starting to
>>>retrieve a field, and this fixes the error.
>>
>>The problem is that the extensible record type that we created for=20
>>HaskellDB does not disallow creating a record which contains two fields=
=20
>>with the same label. HList, presented at Haskell Workshp 2004 has a=20
>>solution to this problem, see http://homepages.cwi.nl/~ralf/HList/paper=
.pdf
>=20
> Speaking of which, do you think that we should try to convert
> haskelldb to something like the scheme presented in the Hlist paper? I
> am not familiar enough with the internals of haskelldb yet to compare
> the existing scheme to the one proposed in hlist...

I know the HaskellDB version well, but I haven't studied HList well=20
enough to to be sure. I guess it would be ideal if we could use HList as=20
a library, but I don't know if this is feasible.

/Bj=F6rn

Re: [Haskelldb-users] HaskellDB in an industrial environment

[Frederik E. <fre...@a5...>]

> Santoemma Enrico wrote:
> >1) Is the library too young for a real world project? Does it work
> >with Oracle (throuhg ODBC)?
> 
> There are still significant bugs in HaskellDB, see the open bugs in
> http://sourceforge.net/tracker/?group_id=101095&atid=629040

What's the biggest bug? "No explicit grouping"?

Also, it would be nice to have somewhere -- maybe in the wiki? -- some
examples of SQL queries which can be done and which can't be done in
HaskellDB. I have a program which I've started writing using regular
SQL statements, and I'm wondering if I should translate it to
HaskellDB. Something like this would help me decide. For example here
are some queries, variables with $ represent parameters... (it's a
simple flash card program)

select id from cards where cards.id not in
    (select alg_data.id from alg_data) order by rand() limit 1

select id from alg_data where uid = $uid and
    when_eligible < $vt and
    id like $pat order by curr_delay desc limit 1

select max(show_num) from hist where (uid,id)=($uid,$id)

select min(a.s), avg(a.s), max(a.s), sum(a.s) from 
    (select id,max(show_num) s from hist where 
            uid = $uid and
            id in (select id from alg_data where
                          uid=$uid and when_eligible < $vt)
                   group by id) as a

I guess there must be a lot of benefits to using an embedded language,
I read the paper and it seems like a great idea - but if I couldn't
translate all of these, or get back the same functionality, then it
would be hard to try to port the project, so I'm reluctant to start
without more examples of what can and can't be done (and I imagine
that others are in similar positions). I'm a bit new to SQL, so please
bear with me...

Frederik

P.S. Is there just the tutorial in wiki-form? Why not put the rest of
the documentation on a wiki, so people can add to it?

P.P.S. The test application says "Server error!"...

http://jackass.tekno.chalmers.se/dp03-7/cgi/run

--
Frederik Eaton                         http://ofb.net/~frederik/

Re: R: [Haskelldb-users] HaskellDB in an industrial environment

[Jeremy S. <jer...@li...>]

At Fri, 04 Feb 2005 15:08:24 +0100,
Bjorn Bringert wrote:
> 
> Santoemma Enrico wrote:
> > Btw, is it possible that the bug 'Repeated field names don't work' is
> > due to the fact that one cannot read the same field two times when
> > using ODBC? This is an old odbc error that I often (still) meet. In
> > haskell I strictly retrieve the record into memory before starting to
> > retrieve a field, and this fixes the error.
> 
> The problem is that the extensible record type that we created for 
> HaskellDB does not disallow creating a record which contains two fields 
> with the same label. HList, presented at Haskell Workshp 2004 has a 
> solution to this problem, see http://homepages.cwi.nl/~ralf/HList/paper.pdf

Speaking of which, do you think that we should try to convert
haskelldb to something like the scheme presented in the Hlist paper? I
am not familiar enough with the internals of haskelldb yet to compare
the existing scheme to the one proposed in hlist...

Jeremy Shaw.
--

This message contains information which may be confidential and privileged. Unless you are the 
addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone 
the message or any information contained in the message. If you have received the message in error, 
please advise the sender and delete the message.  Thank you.

Re: R: [Haskelldb-users] HaskellDB in an industrial environment

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

Santoemma Enrico wrote:
> Btw, is it possible that the bug 'Repeated field names don't work' is
> due to the fact that one cannot read the same field two times when
> using ODBC? This is an old odbc error that I often (still) meet. In
> haskell I strictly retrieve the record into memory before starting to
> retrieve a field, and this fixes the error.

The problem is that the extensible record type that we created for=20
HaskellDB does not disallow creating a record which contains two fields=20
with the same label. HList, presented at Haskell Workshp 2004 has a=20
solution to this problem, see http://homepages.cwi.nl/~ralf/HList/paper.p=
df

/Bj=F6rn

R: [Haskelldb-users] HaskellDB in an industrial environment

[Santoemma E. <enr...@be...>]

Thank you, Bjorn. I'll test it. The bug list isn't discouraging :)

Btw, is it possible that the bug 'Repeated field names don't work' is =
due to the fact that one cannot read the same field two times when using =
ODBC?
This is an old odbc error that I often (still) meet.
In haskell I strictly retrieve the record into memory before starting to =
retrieve a field, and this fixes the error.

It seems that the second read gets an emtpy string.

Enrico

-----Messaggio originale-----
Da: Bjorn Bringert [mailto:d00...@dt...]
Inviato: venerd=EC 4 febbraio 2005 13.31
A: Santoemma Enrico
Cc: HaskellDB (E-mail)
Oggetto: Re: [Haskelldb-users] HaskellDB in an industrial environment


Santoemma Enrico wrote:
> 1) Is the library too young for a real world project? Does it work
> with Oracle (throuhg ODBC)?

There are still significant bugs in HaskellDB, see the open bugs in
http://sourceforge.net/tracker/?group_id=3D101095&atid=3D629040

Whether it is ready for a real world project depends on the nature of=20
the project I guess. The only way to find out is to test it. I should=20
mention that there is currently no support for things like prepared=20
statements and persistant connections.

HaskellDB works with the HSQL and wxHaskell ODBC interfaces, but as far
as I know, noone has tested HaskellDB with Oracle.

> 2) I'm happy to test the library, but in case of big troubles/doubts
> is it realistic to get feedback from the people behind the software
> in, say, 2-3 days?

At the moment, I seem to be the only person answering questions, and I
can't promise to always be available because of travel and such. I am
also quite bust right now, so I don't have much time for large scale
hacking. I should have time for small fixes, applying patches and
answering questions though.

> 3) Where exactly the correction to the DBDirect bug is? The home page
> says that, on Oct 3, there is a bug fixed in cvs. I downloaded
> DBDirect v. 1.12 but I found only minor changes on the error strings.

As far as I recall, the fix was not in the DBDirect module itself, but
rather in one of the modules it uses.

In general, using the CVS version is probably the best way to go, since
there hasn't been a release for a while.

/Bj=F6rn Bringert

Re: [Haskelldb-users] HaskellDB in an industrial environment

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

Santoemma Enrico wrote:
> 1) Is the library too young for a real world project? Does it work
> with Oracle (throuhg ODBC)?

There are still significant bugs in HaskellDB, see the open bugs in
http://sourceforge.net/tracker/?group_id=3D101095&atid=3D629040

Whether it is ready for a real world project depends on the nature of=20
the project I guess. The only way to find out is to test it. I should=20
mention that there is currently no support for things like prepared=20
statements and persistant connections.

HaskellDB works with the HSQL and wxHaskell ODBC interfaces, but as far
as I know, noone has tested HaskellDB with Oracle.

> 2) I'm happy to test the library, but in case of big troubles/doubts
> is it realistic to get feedback from the people behind the software
> in, say, 2-3 days?

At the moment, I seem to be the only person answering questions, and I
can't promise to always be available because of travel and such. I am
also quite bust right now, so I don't have much time for large scale
hacking. I should have time for small fixes, applying patches and
answering questions though.

> 3) Where exactly the correction to the DBDirect bug is? The home page
> says that, on Oct 3, there is a bug fixed in cvs. I downloaded
> DBDirect v. 1.12 but I found only minor changes on the error strings.

As far as I recall, the fix was not in the DBDirect module itself, but
rather in one of the modules it uses.

In general, using the CVS version is probably the best way to go, since
there hasn't been a release for a while.

/Bj=F6rn Bringert

[Haskelldb-users] HaskellDB in an industrial environment

[Santoemma E. <enr...@be...>]

Hello,

since some month I'm considering to use the library in a part of a =
project for the market.
I gave up to put it in a previous part of the project, a framework, =
because my users wanted complete control over the sql statements, and =
also because (I believe) HaskellDB doesn't support left/right joins.

But now I'm working on pure combinators of the project's data set, and =
perhaps HaskellDB can show its power.

I ask these three questions:
1) Is the library too young for a real world project? Does it work with =
Oracle (throuhg ODBC)?
2) I'm happy to test the library, but in case of big troubles/doubts is =
it realistic to get feedback from the people behind the software in, =
say, 2-3 days?
3) Where exactly the correction to the DBDirect bug is? The home page =
says that, on Oct 3, there is a bug fixed in cvs. I downloaded DBDirect =
v. 1.12 but I found only minor changes on the error strings.

Almost forgotting: warm congratulation for the great work done!

Enrico

[Haskelldb-users] Removing subqueries from haskelldb + future of haskelldb

[Jeremy S. <jer...@li...>]

Hello,

I hacked around with the optimizer and sql generator so that many
sub-queries get rewritten as joins. I think that we should be able to
rewrite most, if not all, queries with sub-queries. (In general, not
all sql statements can be rewritten with out sub-queries, but I think
that the subset of relational algebra currently implement by haskelldb
does not have any cases that require sub-queries. )

That is good because, (1) mysql 4.0 does not support sub-queries (2)
mysql 4.1 does, but they are painfully slow. For example, the
following query takes around 10 seconds when generated as a sub-query
and 0.2 seconds with the subqueries removed. (There are around ~2000
bugs that block bug 7178).

taskQuery =
    do d <- table D.dependencies
       b <- table B.bugs
       restrict (d!D.blocked .==. constant 7178)
       restrict (b!B.bug_id .==. d!D.dependson)
       project ( B.bug_id << b!B.bug_id #
		 B.assigned_to << b!B.assigned_to #
		 B.bug_status << b!B.bug_status #
		 B.short_desc << b!B.short_desc #
		 B.priority << b!B.priority
	       )


I have not gotten around to finishing and verifying the correctness of
this code, so it should be considered experimental. But I wanted to
get some early feedback in case I am totally screwing up ;)

If you have access to the book (hint: college library):

Database System Implementations 
  by Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom

http://www.amazon.com/exec/obidos/tg/detail/-/0130402648/102-9632978-2282521?v=glance

I highly recommend you read the beginnings of Chapters 6 and 7.

In Chapter 6, they defined a variation of relational algebra as on
algebra on bags instead of sets. They also extend traditional
relational algebra to include operators such as group-by, order-by,
and select distinct. 

In chapter 7 they introduce the two-operator select, and a bunch of
algebraic laws for transforming queries. Note that the laws are
defined for bags, not sets.

I wonder if adopting the relational algebra defined in this book could
prove to be an important step in making haskelldb more practical for
the real world. It seems like a fairly straightward project if someone
is looking for a class project, or just has some time to kill.

The biggest 'problem' with the book is it is intended for people who
want to write a database. So, they are assuming SQL->Relational
Algebra->Optimized Physical Query Plan. We are interested in the
oppsition direction, Relation Algebra->SQL. This just means that some
of the goals of optimizing are counter-productive. For example, in
SQL->RA, it is beneficial to push the projections to the bottom
layer. But the patch I submit, I actually attempt to float the
projections to the top because it allows me to write a simlper query
that MySQL can execute faster.

Quick Overview of what the patch does:

In the SQL generator I add a new special case:

	  binary Times (SqlTable t1) (SqlTable t2) = newSelect { tables = [("",SqlTable t1),("",SqlTable t2)] } 

This causes the generator to generate a join in the case where you
have the project of two tables.

For example,

do t1 <- table table1
   t2 <- table table2
   project ( <everything> ... )

would be written as:

SELECT * FROM table1, table2;

Then in the optimizer, I attempt to float projections upward, so that
all of my products are the product of two simple tables. When it
floats the projections, it will also automatically merge adjacent
projections. It think this may also have the side effect of removing
the need for removeEmpty and removeDead, but now I can't remember :) I
suspect I also disabled removeRestrict, because it will try to push
things below the product, which is the exact opposite of what I want
it most cases. I think we only want to push restrictions down, if the
query has an unremovable two operator select (and, as far as I can
tell, haskelldb does not have a two operator select).

Jeremy Shaw.

ps. This patch is against 0.8, because that is the last version that
built for me :)

* modified files

--- orig/src/Optimize.hs
+++ mod/src/Optimize.hs
@@ -15,7 +15,7 @@
 -- $Revision: 1.16 $
 -----------------------------------------------------------
 
-module Database.HaskellDB.Optimize (optimize) where
+module Database.HaskellDB.Optimize (optimize, floatProject, mergeProject) where
 
 import Control.Exception (assert)
 import Data.List (intersect)
@@ -24,9 +24,10 @@
 -- | Optimize a PrimQuery
 optimize :: PrimQuery -> PrimQuery
 optimize = mergeProject
-	   . removeEmpty
-           . removeDead
-           . pushRestrict
+	   . floatProject
+--	   . removeEmpty
+--           . removeDead
+--           . pushRestrict
 
 -- | Remove unused attributes from projections.
 removeDead :: PrimQuery -> PrimQuery
@@ -160,6 +161,40 @@
           safe assoc
           	= not (any (nestedAggregate.snd) assoc)
 
+floatProject :: PrimQuery -> PrimQuery
+
+-- | Does not check for conflicting names
+floatProject (Binary Times query1 query2) =
+    let query1' = mergeProject $ floatProject query1
+	query2' = mergeProject $ floatProject query2
+	in
+	case (query1',query2') of
+	     ((Project assoc1 q1),(Project assoc2 q2)) -> Project (assoc1 ++ assoc2) (Binary Times q1 q2)
+	     ((Project assoc1 q1),q2) -> Project assoc1 (Binary Times q1 q2)
+	     (q1,(Project assoc2 q2)) -> Project assoc2 (Binary Times q1 q2)
+	     (q1,q2) -> Binary Times q1 q2
+
+-- | Have not figured out what other binary ops I can float past
+floatProject (Binary op query1 query2) = Binary op (floatProject query1) (floatProject query2)
+
+floatProject (Project assoc query) = (Project assoc (floatProject query))
+
+-- | float the project past a restrict
+floatProject (Restrict x query) = 
+    case floatProject query of
+	 Project assoc q -> 
+	     let x' = substAttr assoc x
+		  in
+		  (Project assoc (floatProject (Restrict x' q)))
+	 q -> (Restrict x q)
+
+-- | Have not figured out if/when I can float past a special op
+floatProject (Special op query) = (Special op (floatProject query))
+
+-- | Termination case
+floatProject q@(BaseTable _ _) = q
+
+
 -- | Push restrictions down through projections and binary ops.
 pushRestrict :: PrimQuery -> PrimQuery
 


--- orig/src/Sql.hs
+++ mod/src/Sql.hs
@@ -54,6 +54,7 @@
                 | SqlBin   String SqlSelect SqlSelect
                 | SqlTable TableName -- ^ Select a whole table named TableName.
                 | SqlEmpty -- ^ Empty select.
+		  deriving Show
 
 
 -- | Data type representing the SQL UPDATE statement.
@@ -131,12 +132,16 @@
                   sql   = toSelect q
                   
           -- binary assumes that q1 and q2 are not empty
-          binary Times q1 q2  
-          	| null (attrs q1) = addTable q1 q2
-          	| null (attrs q2) = addTable q2 q1
-          	| otherwise       = newSelect { tables = [("",q1),("",q2)] }
-          	where
-          	  addTable sql q  = sql{ tables = tables sql ++ [("",q)] }
+	  binary Times (SqlTable t1) (SqlTable t2) = newSelect { tables = [("",SqlTable t1),("",SqlTable t2)] }
+          binary Times q1 q2
+	      | null (criteria q1) && null (criteria q2) = q1  { tables = tables q1 ++ tables q2
+							       , attrs =  attrs q1 ++ attrs q2
+							       }
+              | null (attrs q1) = addTable q1 q2
+              | null (attrs q2) = addTable q2 q1
+              | otherwise       = newSelect { tables = [("",q1),("",q2)] }
+              where
+              addTable sql q  = sql{ tables = tables sql ++ [("",q)] }
 		 
           binary op q1 q2         
           	= SqlBin (toSqlOp op) q1 q2


--

This message contains information which may be confidential and privileged. Unless you are the 
addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone 
the message or any information contained in the message. If you have received the message in error, 
please advise the sender and delete the message.  Thank you.

[Haskelldb-users] [ haskelldb-Bugs-1077061 ] DBDirect does not allow a hierarchical base module

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

Bugs item #1077061, was opened at 2004-12-01 21:42
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=1077061&group_id=101095

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Bjorn Bringert (bring)
Assigned to: Nobody/Anonymous (nobody)
Summary: DBDirect does not allow a hierarchical base module

Initial Comment:
Database.HaskellDB.DBSpec.DBSpecToDBDirect.dbInfoToModuleFiles
(and thereby also DBDirect) behaves incorrectly when
given a hierarchical name as the base module name.

A name containing a dot is be treated as if the dot was
part of the module name, and the directory created by
DBDirect will have the full hierarchical name.

The correct behavior should be to create the directory
hierarchy correspondning to the hierarchical name.

Bug reported by Niklas Broberg

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

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

Re: [Haskelldb-users] problem: Overlapping instance declarations

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

> Programs using HaskellDB must be compiled with support for overlapping
> instances. Compiling against the HaskellDB package should do this
> automatically, so perhaps the problem is that you are not using the
> "-package haskelldb" flag. If this is not the problem, please provide a
> minimal program that produces this error, and the ghc command line used
> to compile it.


the problem was the absence of the "-package haskelldb" flag. now it works
fine. thank you.

carlos.

Re: [Haskelldb-users] problem: Overlapping instance declarations

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

carlos andreazza wrote:
> Hi there,
> 
> I'm getting the following error message when compiling a program that uses
> HaskellDB:
> 
> # Overlapping instance declarations:
> #  In module Database.HaskellDB.HDBRec: HasField f (RecCons f a r)
> #  In module 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
> 
> someone can help me? I have a compiler ghc, version 6.2.2.

Programs using HaskellDB must be compiled with support for overlapping 
instances. Compiling against the HaskellDB package should do this 
automatically, so perhaps the problem is that you are not using the 
"-package haskelldb" flag. If this is not the problem, please provide a 
minimal program that produces this error, and the ghc command line used 
to compile it.

Cheers,

Bjorn

[Haskelldb-users] problem: Overlapping instance declarations

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

Hi there,

I'm getting the following error message when compiling a program that uses
HaskellDB:

# Overlapping instance declarations:
#  In module Database.HaskellDB.HDBRec: HasField f (RecCons f a r)
#  In module 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

someone can help me? I have a compiler ghc, version 6.2.2.

thanks in advance.

carlos.

Re: [Haskelldb-users] Bug in the optimizer.

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

Lemmih wrote:
> Hey,
>=20
> I'm having trouble with the optimizer when doing a one-to-many mapping.

<snip>

Lemmih found the bug, it was a silly typo:

| noneIn2   =3D Binary op (pushRestrict (Restrict x query2)) query1

should be:

| noneIn2   =3D Binary op (pushRestrict (Restrict x query1)) query2

Fix is in CVS.

/Bj=F6rn