From: Justin B. <jgb...@gm...> - 2007-12-11 20:53:47
Attachments:
JGBAILEY.12112007.patch.txt
|
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 |
From: Bjorn B. <bri...@cs...> - 2007-12-18 11:33:26
|
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> |
From: Justin B. <jgb...@gm...> - 2007-12-19 17:28:30
|
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 |
From: Bjorn B. <bri...@cs...> - 2007-12-20 21:39:24
|
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 |
From: Bjorn B. <bri...@cs...> - 2007-12-21 08:26:42
|
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 |
From: Justin B. <jgb...@gm...> - 2007-12-21 16:43:34
|
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 |
From: Bjorn B. <bri...@cs...> - 2007-12-21 16:52:32
|
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 |
From: Peter G. <pe...@gm...> - 2007-12-22 02:21:28
|
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 |
From: Justin B. <jgb...@gm...> - 2007-12-26 18:19:52
|
On Dec 21, 2007 9:32 AM, Peter Gammie <pe...@gm...> wrote: > 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.) Sounds interesting, though I'm probably more interested in the reflection/representation side. Were those relationships able to be used in queries? In any case I wouldn't mind seeing the code. Justin |
From: Peter G. <pe...@gm...> - 2007-12-27 01:42:50
Attachments:
constraints.darcs
parens.darcs
|
On 27/12/2007, at 1:19 AM, Justin Bailey wrote: > On Dec 21, 2007 9:32 AM, Peter Gammie <pe...@gm...> wrote: >> 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.) > > Sounds interesting, though I'm probably more interested in the > reflection/representation side. Were those relationships able to be > used in queries? In any case I wouldn't mind seeing the code. I've attached two patches. One is for this constraint stuff. It's for describing tables only, not for use in queries. (What do you have in mind? The possibility of doing natural joins?) It probably depends on some changes to the HSQL/ PostgreSQL driver that should be fairly obvious. I can send that patch along too if you want it. Note that the other backends do not conform to my changes to the types. There is probably a lot of GHC warning elimination in that patch too. The other fixes the expression generator - it does not generate enough parentheses for boolean expressions (at least). Bjorn, please adjust to taste and commit. cheers peter |
From: Bjorn B. <bri...@cs...> - 2007-12-27 17:43:21
|
On Dec 21, 2007, at 18:32 , Peter Gammie wrote: > 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 (=3D=3D 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. > ... I've just pushed a bunch of patches that add one DBDirect executable =20 for each driver. /Bj=F6rn |
From: Peter G. <pe...@gm...> - 2007-12-28 10:13:25
|
On 28/12/2007, at 12:42 AM, Bjorn Bringert wrote: > I've just pushed a bunch of patches that add one DBDirect executable > for each driver. OK, I'll try out haskelldb-hsql-postgresql. BTW the latest Cabal-think is that you should not put optimisation options in Ghc-options. There are Cabal-level flags for that. cheers peter |