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

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

[Peter G. <pe...@gm...>]

On 21/12/2007, at 11:52 PM, Bjorn Bringert wrote:

> On Dec 21, 2007, at 17:43 , Justin Bailey wrote:
>> Things I'd like to do still:
>>
>> * Representation of foreign-key relationships (ideally extracted
>> from the DB)
>
> I believe Peter Gammie has some code for foreign key constrains.
> Peter: is that ready to be pulled into HaskellDB?

I can email you (== anyone who's interested) a patch that begins down  
that road. Roughly, I added support for defaults, foreign keys and  
primary keys. IIRC one can create tables with those things but the  
reflection is a bit weak, and the data description types are not  
fantastically robust. (The fieldnames are merely strings, e.g.)

I haven't touched it for a few months now, as I decided it was less  
hassle to just use an SQL script.

You will also need to renovate whatever DB backends you care about.  
(All Haskell DB bindings I looked at act as SQL bridges and treat  
tables merely as tuples.) I added some stuff to HSQL/PostgreSQL, and  
can email you those patches too, if you like. (I could port those to  
another <DB backend>/PostgreSQL if you can convince me there is some  
advantage in doing so - HSQL seemed to be simple, stable, etc. when I  
was looking into these things.)

BTW what state is DBDirect in? I cannot compile hs-plugins (using GHC  
6.6.1) presently, and I don't know what the future holds for that.

Also BTW, I have some not-so-great patches that allow one to do  
character decoding/encoding at the database interface. I use these to  
get PostgreSQL to speak UTF-8. They seem to work well, if not  
particularly cleanly or efficiently. I think some refinement is not  
too much trouble if anyone is interested.

cheers
peter

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

On Dec 21, 2007, at 17:43 , Justin Bailey wrote:

> On Dec 21, 2007 12:26 AM, Bjorn Bringert <bri...@cs...> =20
> wrote:
>
>> Great, thank you! I'm sorry about the complications. I've now pushed
>> your patch.
>>
>>
>> So, what are you working on now?
>>
>
> No problem. I'm interested in using haskelldb to generate SQL for a
> PHP data-access layer, so I've now added named parameters as another
> expression type. PHP's odbc family of functions support "prepared"
> statements with parameters, but they are indicated by position only.
> That means its  very important  that I can get the list of parameters
> in a query in the same order as they will appear in the SQL text.
> Ideally, I could re-use the pretty-printing framework but that is a
> pretty big engineering task. I settled on generating them from the
> intermediate SqlSelect values. Not idea but safe enough.
>
> Things I'd like to do still:
>
>   * Proper joins (starting with inner join)
>   * Representation of foreign-key relationships (ideally extracted =20
> from the DB)
>   * Tests
>
> We'll see how far I get ... Any ideas or suggestions are welcome!

I believe Peter Gammie has some code for foreign key constrains.
Peter: is that ready to be pulled into HaskellDB?

I did some work on the testsuite yesterday. hs-plugins is no longer =20
required to run it, which should make testing much easier.

/Bj=F6rn

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

On Dec 21, 2007 12:26 AM, Bjorn Bringert <bri...@cs...> wrote:

> Great, thank you! I'm sorry about the complications. I've now pushed
> your patch.
>
>
> So, what are you working on now?
>

No problem. I'm interested in using haskelldb to generate SQL for a
PHP data-access layer, so I've now added named parameters as another
expression type. PHP's odbc family of functions support "prepared"
statements with parameters, but they are indicated by position only.
That means its  very important  that I can get the list of parameters
in a query in the same order as they will appear in the SQL text.
Ideally, I could re-use the pretty-printing framework but that is a
pretty big engineering task. I settled on generating them from the
intermediate SqlSelect values. Not idea but safe enough.

Things I'd like to do still:

  * Proper joins (starting with inner join)
  * Representation of foreign-key relationships (ideally extracted from the DB)
  * Tests

We'll see how far I get ... Any ideas or suggestions are welcome!

Justin

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

On Dec 21, 2007, at 2:01 , Justin Bailey wrote:

> Attached you'll find the patch, with whitespace removed. Thanks for =20=

> accepting it.
>
> The darcs contortions I had to go through were involved:
>
> 1) Pull a new repo from my existing, to get my already-recorded patch.
> 2) Unrecord that patch, to make all the changes "live"
> 3) Revert the whitespace
> 4) Record the patch again
> 5) Go to my original repository, unrecord the existing patch
> 6) Pull in the new patch
> 7) Fix up conflicts with new patch and more recent code (since I =20
> had continued coding after recording the last patch sent)
>
> Luckily everything is still working great!

Great, thank you! I'm sorry about the complications. I've now pushed =20
your patch.

So, what are you working on now?

/Bj=F6rn

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

On Dec 19, 2007, at 18:28 , Justin Bailey wrote:

> On Dec 18, 2007 3:33 AM, Bjorn Bringert <bri...@cs...> =20
> wrote:
>> this looks good. I haven't run the testsuite yet, since it still
>> requires hs-plugins. I really should get around to fixing that. Hope
>> seems to run fine with it.
>
> I ran some of the tests against postgres and they worked.
> Unfortunately any test involving "calendartime" failed (seemed to be
> unable to parse a timezone) and I didn't have time to track down the
> root cause.

There are various know problems like that with different combinations =20=

of back-ends and drivers. Help with fixing them would be very =20
welcome. These things are mostly problems in HDBC or HSQL, or in how =20
HaskellDB uses them. The root cause is generally inconsistencies =20
between the different database implementations.

>> I'll be happy to push this to the main repo. Would it be possible for
>> you to send me an updated patch with only the necessary changes? Some
>> of the changes in the patch seem to only affect whitespace. I prefer
>> to keep patches minimal.
>
> I'd be glad to do that, but how can I update my recorded darcs patch?
> I've also continued to code on my working copy so it's evolved since I
> recorded the patch submitted. My darcs-fu is not very strong.

There are several possibilities. If you haven't touched the lines =20
with white-space changes, you could change them back to their =20
original state, and then use 'darcs amend-record' to update your =20
patch. Asking in #darcs might be best. If this becomes too difficult, =20=

I could accept the patch as it is.

/Bj=F6rn

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

On Dec 18, 2007 3:33 AM, Bjorn Bringert <bri...@cs...> wrote:
> this looks good. I haven't run the testsuite yet, since it still
> requires hs-plugins. I really should get around to fixing that. Hope
> seems to run fine with it.

I ran some of the tests against postgres and they worked.
Unfortunately any test involving "calendartime" failed (seemed to be
unable to parse a timezone) and I didn't have time to track down the
root cause.

>
> I'll be happy to push this to the main repo. Would it be possible for
> you to send me an updated patch with only the necessary changes? Some
> of the changes in the patch seem to only affect whitespace. I prefer
> to keep patches minimal.

I'd be glad to do that, but how can I update my recorded darcs patch?
I've also continued to code on my working copy so it's evolved since I
recorded the patch submitted. My darcs-fu is not very strong.

Justin

Re: [Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

Hi Justin,

this looks good. I haven't run the testsuite yet, since it still =20
requires hs-plugins. I really should get around to fixing that. Hope =20
seems to run fine with it.

I'll be happy to push this to the main repo. Would it be possible for =20=

you to send me an updated patch with only the necessary changes? Some =20=

of the changes in the patch seem to only affect whitespace. I prefer =20
to keep patches minimal.

/Bj=F6rn


On Dec 11, 2007, at 21:53 , Justin Bailey wrote:

> All,
>
> Attached you'll find a patch that removes the mandatory "DISTINCT"
> clause from select statements. Instead, if a query has the "unqiue"
> function applied, a 'group by' statement will appear in the select,
> grouping by all non-aggregate columns. This works properly with
> optimization so unused columns do not appear in the group-by. Note
> this patch replaces my previous one - I think this approach is a lot
> better and works in more situations.
>
> One change I'm unsure of is that "unique" is NOT default behavior
> anymore. I think a select statement with a "distinct" on all columns
> is very surprising so I like the change. Others may disagree.
>
> A summary of significant changes:
>
>     M ./src/Database/HaskellDB/PrimQuery.hs -3 +7
> * Added "Group" value to PrimQuery. This value holds an association
> list and a query. The association list is the columns/expressions that
> will be grouped; the query is the query to which Group was applied. In
> SQL, grouping can happen on expressions, which is why an Assoc list is
> used instead of a simple list of column names.
>
>
>     M ./src/Database/HaskellDB/Query.hs -9 +30
> * Added the 'unique' function, which causes the Group value to be
> added to the query. All currently projected columns are examined and
> non-aggregates are added to the Group value. This allows those columns
> to be later generated in a "group by" statement.
>
>     M ./src/Database/HaskellDB/Sql/Generate.hs +3
> * Added 'sqlGroup' field to SqlGenerator value. Used to process =20
> Group values.
>
>     M ./src/Database/HaskellDB/Sql.hs -12 +16
> * Changed the 'groupby' field from a list of SqlExpr to a list of
> (SqlColumn, SqlExpr) pairs. Not really needed right now, but will
> allow 'GROUP BY' to be as expressive as the 'column list' available to
> SELECT.
>
>     M ./src/Database/HaskellDB/Sql/Default.hs -8 +12
> * Added 'defaultSqlGroup' converts the Assoc list from the Group value
> to a list of (SqlColumn, SqlExpr) pairs and stores them in the groupby
> field.
> * Note that defaultSqlSelect did NOT have to change - it already
> handled grouping for non-aggregate expressions properly.
>
>     M ./src/Database/HaskellDB/Sql/Print.hs -3 +9
>     M ./src/Database/HaskellDB.hs -1 +1
>     M ./src/Database/HaskellDB/Optimize.hs -7 +16
> * Various small changes to support new values and changed structures.
>
> I welcome feedback on this patch. I'm happy to clarify anything or
> provide examples.
>
> Justin<JGBAILEY.12112007.patch.txt>

[Haskelldb-users] Request for code review: Patch removing "distinct" option, adds "unique" function

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

All,

Attached you'll find a patch that removes the mandatory "DISTINCT"
clause from select statements. Instead, if a query has the "unqiue"
function applied, a 'group by' statement will appear in the select,
grouping by all non-aggregate columns. This works properly with
optimization so unused columns do not appear in the group-by. Note
this patch replaces my previous one - I think this approach is a lot
better and works in more situations.

One change I'm unsure of is that "unique" is NOT default behavior
anymore. I think a select statement with a "distinct" on all columns
is very surprising so I like the change. Others may disagree.

A summary of significant changes:

    M ./src/Database/HaskellDB/PrimQuery.hs -3 +7
* Added "Group" value to PrimQuery. This value holds an association
list and a query. The association list is the columns/expressions that
will be grouped; the query is the query to which Group was applied. In
SQL, grouping can happen on expressions, which is why an Assoc list is
used instead of a simple list of column names.


    M ./src/Database/HaskellDB/Query.hs -9 +30
* Added the 'unique' function, which causes the Group value to be
added to the query. All currently projected columns are examined and
non-aggregates are added to the Group value. This allows those columns
to be later generated in a "group by" statement.

    M ./src/Database/HaskellDB/Sql/Generate.hs +3
* Added 'sqlGroup' field to SqlGenerator value. Used to process Group values.

    M ./src/Database/HaskellDB/Sql.hs -12 +16
* Changed the 'groupby' field from a list of SqlExpr to a list of
(SqlColumn, SqlExpr) pairs. Not really needed right now, but will
allow 'GROUP BY' to be as expressive as the 'column list' available to
SELECT.

    M ./src/Database/HaskellDB/Sql/Default.hs -8 +12
* Added 'defaultSqlGroup' converts the Assoc list from the Group value
to a list of (SqlColumn, SqlExpr) pairs and stores them in the groupby
field.
* Note that defaultSqlSelect did NOT have to change - it already
handled grouping for non-aggregate expressions properly.

    M ./src/Database/HaskellDB/Sql/Print.hs -3 +9
    M ./src/Database/HaskellDB.hs -1 +1
    M ./src/Database/HaskellDB/Optimize.hs -7 +16
* Various small changes to support new values and changed structures.

I welcome feedback on this patch. I'm happy to clarify anything or
provide examples.

Justin

[Haskelldb-users] Request for review - removing "DISTINCT"

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

All,

I've created an initial implementation which removes the 'distinct'
option attached to all queries, and implements an alternate method of
specifying 'distinct' rows. I added a 'unique' function, exported by
HaskellDB.Query, which ensures that all projected attributes
ultimately end up in a 'group by' clause, unless the attribute is
itself an aggregate already.

Technically, I added a 'Group' constructor to the 'PrimQuery' data
type. I did not modify the existing aggregate expression
implementation. I believe my change does not affect that at all. The
'Group' could really just be named 'Unique' for now, since it's only
used to indicate all rows should be unique, but I suspect it will
expand into other "aggregate" operators from the relational algebra.

I then added another function the the generator which handles groups.
When converting from PrimQuery to SqlSelect, defaultSqlGroup (in
Default.hs) is used to ensure all non-aggregate columns are in the
'group by' clause. Because I do not get the "PrimExpr" values that
define the attributes (by this point I only get [(SqlColumn,
SqlExpression)] pairs if I'm lucky), I rely on a hack to determine if
a column is aggregate or not[1]. If the column is defined as a
FunSqlExpression, I assume it is aggregate. This hack is implemented
in "isAggregateSqlExpr", which is exported by Sql.hs.

I'd like comments on the code and the approach. Please don't commit
the patch as I haven't tested it much and I'm sure my implementation
will continue to evolve.

Justin

[1] I think I now see what Jeremy meant when he said the "conversion
to internal representation" happened too early. Not having this
information made life painful.

[2] A quick example of how this is used (this uses my local table
definitions so it won't compile directly). The two queries are:

  allROs = table DB.ro_table

  unique_ROs = do
    ro_hdr <- allROs
    unique ro_hdr

And the SQL produced (eliding many columns):

*Main> putStrLn $ showSql allROs
SELECT ...
             mod_dt
FROM ro_table as T1

*Main> putStrLn $ showSql unique_ROs
SELECT ...
       mod_dt1 as mod_dt
FROM (SELECT ...
             mod_dt as mod_dt1
      FROM ro_table as T1
      GROUP BY ...
               mod_dt) as T1

[Haskelldb-users] Changing "distinct" to group by

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

All,

This email asks for some design advice. I've been trying to remove
the "distinct" clause and instead doing it the right way - grouping by
each column in the select. I have tried several approaches, none of
which has worked quite right:


1  - Make all columns aggregate and non-aggregate:  In Sql/Default.hs,
defaultSqlProject determines if any column has an aggregate expresion
and, if so, it adds all non-aggregate columns to the groupby list. My
first attempt updated an existing Project by adding a "AggrExpr" for
each expression, using the "AggrSpecial" operation. This resulted in
the columns correcltly moving to the group by, but Aggregates are
always rendered as "functions" (e.g. COUNT( ..), AVG( ...)) so I got a
lot of columns like "(bus_unit_id) as bus_unit_id1". I also got
multiple nested selects, but I think that is because I don't quite
understand the PrimQuery/Alias mapping when creating a new Project.

2 - Adding a new aggregate operation - I tried adding an AggrUnique
constructor to the AggrOp type, but this quickly ran into the same
problems as above. Ideally, I wanted to treat AggrOp as a
ColumnSqlExpression when printing (in Sql/Print.hs), but AggrExpr does
not have the right information with it - namely, a way to convert the
column expression to a string.

3 - Adding a new special operation - Two special operators, "TOP" and
"ORDER" are already defined. I tried adding a "Unique" operator but,
unfortunately, the special operator handler doesn't have access to the
associatoin list making up the select, and thus I could not updated
the "groupby" field like I was able to do in #1.

Any suggestions or thoughts about other approaches are welcome. Thanks
in advance!

Justin

Re: [Haskelldb-users] Another try

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

On Nov 20, 2007, at 11:55 , Immanuel Normann wrote:

> On Tue, 2007-11-20 at 11:40 +0100, Bjorn Bringert wrote:
>
>> EXISTS is unfortunately not implemented yet. There are only inner
>> joins. Fixing these deficiencies would be very welcome.
>
> I will have a look at it - as soon as I am relieved from other =20
> dominant
> dueties.
>
> Still I couldn't find even inner joins in the haskellDB 0.10 API.
>
> Immanuel

Inner joins are implicit. Using two 'table' calls makes a cartesian =20
product between those tables. You can then use 'restrict' with =20
equality on fields from the two tables to get an equi-join.

/Bj=F6rn

Re: [Haskelldb-users] Another try

[Immanuel N. <i.n...@ja...>]

On Tue, 2007-11-20 at 11:40 +0100, Bjorn Bringert wrote:

> EXISTS is unfortunately not implemented yet. There are only inner  
> joins. Fixing these deficiencies would be very welcome.

I will have a look at it - as soon as I am relieved from other dominant
dueties.

Still I couldn't find even inner joins in the haskellDB 0.10 API.

Immanuel

Re: [Haskelldb-users] Another try

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

On Nov 20, 2007, at 11:05 , Immanuel Normann wrote:

> Hello,
>
> this is another try to get help about a question which I have send to
> this mailing list about 2 weeks ago under the subject "Missing SQL
> features". I haven't received any feedback may be because the =20
> traffic is
> rather low in this mailing list - so I try again:
>
> I can't find the SQL operators exists (and join) in HaskellDB. Are =20
> they
> not supported yet or have they different names here?
>
> Basically I need to express in HaskellDB something like:
>
> SELECT DISTINCT store_type FROM stores
>   WHERE NOT EXISTS (SELECT * FROM cities_stores
>                     WHERE cities_stores.store_type =3D =20
> stores.store_type);
>
> (i.e. asking the question "What kind of store is present in no =20
> cities?")
>
> Any hints how to accomplish this in HaskellDB are highly appreciated.
>
> Thanks,
> Immanuel

Hi Immanuel,

EXISTS is unfortunately not implemented yet. There are only inner =20
joins. Fixing these deficiencies would be very welcome.

/Bj=F6rn

[Haskelldb-users] Another try

[Immanuel N. <i.n...@ja...>]

Hello,

this is another try to get help about a question which I have send to
this mailing list about 2 weeks ago under the subject "Missing SQL
features". I haven't received any feedback may be because the traffic is
rather low in this mailing list - so I try again:

I can't find the SQL operators exists (and join) in HaskellDB. Are they
not supported yet or have they different names here?

Basically I need to express in HaskellDB something like:

SELECT DISTINCT store_type FROM stores
  WHERE NOT EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type);

(i.e. asking the question "What kind of store is present in no cities?")

Any hints how to accomplish this in HaskellDB are highly appreciated.

Thanks,
Immanuel

Re: [Haskelldb-users] Interested in updating HaskellDB

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

At Sat, 17 Nov 2007 09:13:13 -0800,
Justin Bailey wrote:
> 
> On Nov 16, 2007 6:20 PM, Jeremy Shaw <jer...@li...> wrote:
>
> You're proposal below might be beyond my current abilities though -
> I haven't quite grokked type-level programming like that.

No problem. Removing the monadic stuff might be feasible. I believe
the monad is only used to create unique names for internal column and
table names. But, you might be able to use a different system for
ensuring that the names are unique.

> Thanks also for the book recommendations. I've got them on the way to
> my local library!

Good thinking :)

j.

Re: [Haskelldb-users] RFI: DBSpec.hs

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

On Nov 19, 2007, at 17:28 , Jim Stuttard wrote:

> Hi,
>
> What should be the exact contents of  the (..) parameters?
>
> module Database.HaskellDB.DBSpec
>     (DBInfo(..),TInfo(..),CInfo(..),DBOptions(..),
>      FieldDesc, FieldType(..),
>
> TIA
> Jim

It should be (..) :-)

(..) is Haskell syntax for "export all the constructors of this type".

/Bj=F6rn

[Haskelldb-users] RFI: DBSpec.hs

[Jim S. <jim...@ta...>]

Hi,

What should be the exact contents of  the (..) parameters?

module Database.HaskellDB.DBSpec
    (DBInfo(..),TInfo(..),CInfo(..),DBOptions(..),
     FieldDesc, FieldType(..),

TIA
Jim

Re: [Haskelldb-users] Interested in updating HaskellDB

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

On Nov 16, 2007 6:20 PM, Jeremy Shaw <jer...@li...> wrote:
> Hello,
>
> You should also look into the fixing the bug that occurs if the same
> column name appears in two tables, and you do a query that uses both
> columns.
>
> There are also two issues I have with the way HaskellDB does things:
>
>  1. I don't think the queries really need constructed using monads.
>
>  2. I think HaskellDB converts from Query Algebra to the internal data
>  structure too early.
>

I'll keep that in mind, because one of the primary reasons I'd like to
do this is to be able to compose queries. You're proposal below might
be beyond my current abilities though - I haven't quite grokked
type-level programming like that.

Thanks also for the book recommendations. I've got them on the way to
my local library!

Justin

Re: [Haskelldb-users] Interested in updating HaskellDB

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

Hello,

You should also look into the fixing the bug that occurs if the same
column name appears in two tables, and you do a query that uses both
columns.

There are also two issues I have with the way HaskellDB does things:

 1. I don't think the queries really need constructed using monads.

 2. I think HaskellDB converts from Query Algebra to the internal data
 structure too early.

I imagine an algebra somewhat like this:

data Algebra :: (* -> *) where
    Project   :: (IsSubset schema schema', FieldName field, Show a) => Projection schema field a -> Algebra schema' -> Algebra ((field :=: a) :*: Empty)
    Selection :: (Expr schemaE Bool) -> Algebra schema -> Algebra schema
    Product   :: Algebra schemaa -> Algebra schemab -> Algebra schemac
    Table     :: TableName -> Algebra schema

and then you write a query a bit like this:

testb = 
    projectAs Table1.field1 (undefined :: Table1.OtherField) $
    selection (Table1.field1 .==. (Lit 1)) $
        product Table1.table Table2.table

*Examples> testb
project <some projections>
  (select Table1.FieldName1 == 1
     (product
        (table Table1)
        (table Table2)))
*Examples> toSql testb
SELECT Table1.FieldName1 AS Table1.OtherField FROM Table1, Table2 WHERE Table1.FieldName1 == 1
*Examples> 

One advantage of this scheme is that it makes it very natural to
compose query fragments into larger queries. It also allows you to
preform a certain level of query optimization at compile time, instead
of runtime.

The disadvantage is that it is difficult to implement. The projections
gave me the most trouble. My hope is that the associated types stuff
will make it easier to write the code.

I developed an untype version of this method for querying debian
package indexes, which you can find here:

http://www.n-heptane.com/nhlab/repos/haskell-dql/

I doubt it still builds however, due to the libraries is uses changing
significantly.

In other news, if you want more information on query algebra, and
doing things such as optimizing out sub-selects, I recommend you get
this book:

http://www.amazon.com/Database-System-Implementation-Hector-Garcia-Molina/dp/0130402648/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1195265481&sr=8-1
http://infolab.stanford.edu/~ullman/dbsi.html

It has a chapter on query algebra and another on query algebra
optimization. Unlike most other writings on the subject, it treats
databases as multisets/bags, instead of sets. Since you want to get
rid of 'DISTINCT', you will need use the correct laws for multiset
union, intersection, etc.

If you are very interested in databases, you could instead buy the
successor to that book:

http://www.amazon.com/Database-Systems-Complete-Hector-Garcia-Molina/dp/0130319953
http://infolab.stanford.edu/~ullman/dscb.html

But it is significantly more expensive. 

j.



At Fri, 16 Nov 2007 12:16:36 -0800,
Justin Bailey wrote:
> 
> Greetings,
> 
> I've spent most of this week investigating HaskellDB. I am interested
> in using it as the basis for specifying and generating an ORM layer
> for an application I work on at my day job. I've gotten to the point
> of using it to query our existing database and generate table
> specifications from that. I am very impressed, especially considering
> the low level of activity on the project over the last several years.
> It's solid work.
> 
> I'd like to extend the API to do joins properly and to not force a
> "DISTINCT" clause into each query. Is that a feasible idea (assuming
> my Haskell abilities are up to it)? Would this list be a good resource
> for asking questions about the code, if I start hacking on it?
> 
> Thanks for the great work so far and in advance for any help or advice.
> 
> Justin
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users

Re: [Haskelldb-users] Interested in updating HaskellDB

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

On Nov 16, 2007, at 21:16 , Justin Bailey wrote:

> Greetings,
>
> I've spent most of this week investigating HaskellDB. I am interested
> in using it as the basis for specifying and generating an ORM layer
> for an application I work on at my day job. I've gotten to the point
> of using it to query our existing database and generate table
> specifications from that. I am very impressed, especially considering
> the low level of activity on the project over the last several years.
> It's solid work.
>
> I'd like to extend the API to do joins properly and to not force a
> "DISTINCT" clause into each query. Is that a feasible idea (assuming
> my Haskell abilities are up to it)? Would this list be a good resource
> for asking questions about the code, if I start hacking on it?
>
> Thanks for the great work so far and in advance for any help or =20
> advice.
>
> Justin

Hi Justin,

that sounds like a good plan. The DISTINCT thing ought to be easy =20
enough. It's not obvious to me how to get the types right for joins, =20
but you are welcome to look at it. CoddFish [1] might provide some =20
inspiration.

This list doesn't get a lot of traffic, but I think it's the best =20
place for discussing HaskellDB hacking.

Peter Gammie is also hacking on HaskellDB at the moment, so you may =20
want to talk to him. Peter: It might makes sense for you to be on =20
this list.

/Bj=F6rn

[1] http://wiki.di.uminho.pt/twiki/bin/view/Research/PURe/CoddFish

[Haskelldb-users] Interested in updating HaskellDB

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

Greetings,

I've spent most of this week investigating HaskellDB. I am interested
in using it as the basis for specifying and generating an ORM layer
for an application I work on at my day job. I've gotten to the point
of using it to query our existing database and generate table
specifications from that. I am very impressed, especially considering
the low level of activity on the project over the last several years.
It's solid work.

I'd like to extend the API to do joins properly and to not force a
"DISTINCT" clause into each query. Is that a feasible idea (assuming
my Haskell abilities are up to it)? Would this list be a good resource
for asking questions about the code, if I start hacking on it?

Thanks for the great work so far and in advance for any help or advice.

Justin

[Haskelldb-users] Missing SQL features?!

[Immanuel N. <i.n...@ja...>]

Hello,

I can't find the SQL operators exists (and join) in HaskellDB. Are they
not supported yet or have they different names here?

Are nested selects possible?

Basically I need to express in HaskellDB something like:

SELECT DISTINCT store_type FROM stores
  WHERE NOT EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type);

(i.e. asking the question "What kind of store is present in no cities?")

Any hints how to accomplish this in HaskellDB are highly appreciated.

Thanks,
Immanuel

Re: [Haskelldb-users] Problems with insert

[Immanuel N. <i.n...@iu...>]

Am Dienstag, den 23.10.2007, 13:54 +0200 schrieb Björn Bringert:

> 
> Hi Immanuel,
> 
> your withDB type looks strange. You say that it is:
> 
> withDB :: GetRec er vr => Query (Rel er) -> IO [Record vr]
> 
> This looks like it takes a query, runs it, and returns the results. That 
> is, I expect that your withDB implementation is something like this:
> 
> withDB :: GetRec er vr => Query (Rel er) -> IO [Record vr]
> withDB q = connect (... db connection arguments ...) (\db -> query db q)
Actually I copied this withDB from a HaskellDB Wiki
http://www.haskell.org/hawiki/HaskellDbTutorial
Which appearently does not exist anymore. 
> 
> But you are then trying to use the same withDB function to do an insert, 
> even though the withDB in itself does a query. What I would do instead 
> is to have withDB be something like (not tested):
> 
> withDB :: (Database -> IO a) -> IO a
> withDB f = connect (... db connection arguments ...) f
> 
> Then your query example would be:
> 
> select = do withDB $ \db -> query db $ do t <- table t1
>                                         project (c1 << t!c1)
> 
> And the insert would be as you wrote it:
> 
> testInsert = withDB $ \db -> insert db t1 ((c1 << constant 3) #
>                                  &#-1;&#-1;          (c2 << constant "foo"))
Thank you! I will try this. 
> 
> 
> Note that every use of withDB opens and closes a database connection,so 
> in real programs, you want to make sure not to do this over and over for 
> performance reasons.
In fact I haven't considered this. Since I am not that familliar with
HaskellDB. Could you sketch how to insert efficiently without redundant
connection open/close? say given the list of type [(Int,String)] that
should be inserted into table with two columns of appropriate types.

Thanks,
Immanuel

Re: [Haskelldb-users] Problems with insert

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

Immanuel Normann wrote:
> Hello,
> 
> I want to use the insert function and try to follow the guide from
> http://haskelldb.sourceforge.net/guide/insert.html
> 
> My database has one table t1 with the following layout:
> 
> +-------+----------+------+-----+---------+-------+
> | Field | Type     | Null | Key | Default | Extra |
> +-------+----------+------+-----+---------+-------+
> | c1    | int(11)  | YES  |     | NULL    |       | 
> | c2    | char(32) | YES  |     | NULL    |       | 
> +-------+----------+------+-----+---------+-------+
> 
> Right now I am able to set up all such that I can send successfully a
> select query with e.g.:
> 
> select = do withDB $ do t <- table t1
>                         project (c1 << t!c1)
> 
> But I fail with insert though I follow the instruction (of course
> adapted to my table layout) as presented on that website:
> 
> 
> testInsert =
>    do  withDB $ (\db -> insert db t1 ((c1 << constant 3) #
> 				      (c2 << constant "foo")))
> 
> This yields the error:
>     The lambda expression `\ db -> ...' has one argument,
>     but its type `Query (Rel er)' has none
>     In the second argument of `($)', namely
> 	`(\ db
> 	      -> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))'
>     In the expression:
> 	  withDB
> 	$ (\ db
> 	       -> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))
>     In the expression:
> 	do withDB
> 	 $ (\ db
> 		-> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))
> 
> In fact the types are:
> (\db -> insert db t1 ((c1 << constant 3) # (c2 << constant "foo"))) :: Database -> IO ()
> withDB :: (Database.HaskellDB.Database.GetRec er vr) => Query (Rel er) -> IO [Record vr]
> 
> So, how does insert work correctly?
> 
> Thanks,
> Immanuel

Hi Immanuel,

your withDB type looks strange. You say that it is:

withDB :: GetRec er vr => Query (Rel er) -> IO [Record vr]

This looks like it takes a query, runs it, and returns the results. That 
is, I expect that your withDB implementation is something like this:

withDB :: GetRec er vr => Query (Rel er) -> IO [Record vr]
withDB q = connect (... db connection arguments ...) (\db -> query db q)

But you are then trying to use the same withDB function to do an insert, 
even though the withDB in itself does a query. What I would do instead 
is to have withDB be something like (not tested):

withDB :: (Database -> IO a) -> IO a
withDB f = connect (... db connection arguments ...) f

Then your query example would be:

select = do withDB $ \db -> query db $ do t <- table t1
                                        project (c1 << t!c1)

And the insert would be as you wrote it:

testInsert = withDB $ \db -> insert db t1 ((c1 << constant 3) #
                                            (c2 << constant "foo"))


Note that every use of withDB opens and closes a database connection,so 
in real programs, you want to make sure not to do this over and over for 
performance reasons.

/Björn

[Haskelldb-users] Problems with insert

[Immanuel N. <i.n...@iu...>]

Hello,

I want to use the insert function and try to follow the guide from
http://haskelldb.sourceforge.net/guide/insert.html

My database has one table t1 with the following layout:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | YES  |     | NULL    |       | 
| c2    | char(32) | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+

Right now I am able to set up all such that I can send successfully a
select query with e.g.:

select = do withDB $ do t <- table t1
                        project (c1 << t!c1)

But I fail with insert though I follow the instruction (of course
adapted to my table layout) as presented on that website:


testInsert =
   do  withDB $ (\db -> insert db t1 ((c1 << constant 3) #
				      (c2 << constant "foo")))

This yields the error:
    The lambda expression `\ db -> ...' has one argument,
    but its type `Query (Rel er)' has none
    In the second argument of `($)', namely
	`(\ db
	      -> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))'
    In the expression:
	  withDB
	$ (\ db
	       -> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))
    In the expression:
	do withDB
	 $ (\ db
		-> insert db t1 ((c1 << (constant 3)) # (c2 << (constant "foo"))))

In fact the types are:
(\db -> insert db t1 ((c1 << constant 3) # (c2 << constant "foo"))) :: Database -> IO ()
withDB :: (Database.HaskellDB.Database.GetRec er vr) => Query (Rel er) -> IO [Record vr]

So, how does insert work correctly?

Thanks,
Immanuel