Generating grouped where clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
22 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Generating grouped where clause

Alistair Grant
Hi,

I'm just getting started with Glorp and am porting an existing
application from Django / SQLite3.  I'm trying to construct a query
which is basically:

SELECT ...
FROM ...
WHERE (A or B or C) and (D or E or F)

each of A to F are of the form: field like '%string%'.

I have the query mostly working - it's a left outer join with the 
where clause referencing both tables, however
it seems like SimpleQuery doesn't handle grouping of terms with
brackets, so I always end up with:

A or B or C and D or E or F

What's the best way to achieve the grouping?

I've looked at CompoundQuery, however it produces more complex
sub-queries.

Thanks!
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Esteban A. Maringolo
You'd have to query like

SimpleQuery read: YourClass where: [:each | ((A OR: B) OR: [C])  AND:
[(D OR: [E]) OR: [F]]]

Note the uppercase AND: and OR: selectors, that's so because and: and
or: are usually optimized by the compiler and never sent to the
receiver, and because Glorp uses a message archiver by intercepting
message sends to build the query expressions, you'd have to do that.

Regards!


Esteban A. Maringolo


2016-06-13 17:53 GMT-03:00  <[hidden email]>:

> Hi,
>
> I'm just getting started with Glorp and am porting an existing
> application from Django / SQLite3.  I'm trying to construct a query
> which is basically:
>
> SELECT ...
> FROM ...
> WHERE (A or B or C) and (D or E or F)
>
> each of A to F are of the form: field like '%string%'.
>
> I have the query mostly working - it's a left outer join with the
> where clause referencing both tables, however
> it seems like SimpleQuery doesn't handle grouping of terms with
> brackets, so I always end up with:
>
> A or B or C and D or E or F
>
> What's the best way to achieve the grouping?
>
> I've looked at CompoundQuery, however it produces more complex
> sub-queries.
>
> Thanks!
> Alistair
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Hi Esteban,

Thanks for your reply.

On Monday, 13 June 2016 23:24:31 UTC+2, Esteban A. Maringolo wrote:
You'd have to query like

SimpleQuery read: YourClass where: [:each | ((A OR: B) OR: [C])  AND:
[(D OR: [E]) OR: [F]]]

Note the uppercase AND: and OR: selectors, that's so because and: and
or: are usually optimized by the compiler and never sent to the
receiver, and because Glorp uses a message archiver by intercepting
message sends to build the query expressions, you'd have to do that.

I tried:

(SimpleQuery read: KnowlingMeme 
where: [ :each |
(((each name similarTo: '%one%') OR: (each name similarTo: '%two%')) OR: [ each name similarTo: '%three%'])
AND:
[ ((each label similarTo: '%four%') OR: [each label similarTo: '%five%']) OR: [ each label similarTo: '%six%']]])
executeIn: KnowlingGlorp current session.


The sql generated in the QuerySelectCommand is:

SELECT t1.id, t1.description, t1.creation_date, t1.mod_date, t1.name, t1.label, t1.icon
 FROM knowapp_meme t1
 WHERE t1.name LIKE '%one%' OR t1.name LIKE '%two%' OR t1.name LIKE '%three%' AND t1.label LIKE '%four%' OR t1.label LIKE '%five%' OR t1.label LIKE '%six%'


This is a simpler query than the one I originally described, it doesn't join two tables, and the strings it is searching for don't make sense, but it demonstrates the problem (no brackets where required in the where clause).

Thanks again,
Alistair




 

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Esteban A. Maringolo
You have an error in the syntax, the argument to an OR: message must be a Block.

    | query |
    query := SimpleQuery
                read: Person
                where: [ :each |
                    ((each firstName like: '%one%') OR: [
                        (each firstName like: '%two%' )
                         OR: [each firstName like: '%three%']])
                    AND: [
                        ((each firstName like: '%four%') OR: [
                        (each firstName like: '%five%' )
                         OR: [each firstName like: '%six%']])
                    ]
                ].
    query executeIn: session.
    ].



Produces (line breaks added by me):

SELECT *
 FROM PERSON t1
 WHERE (
        ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
OR (t1.firstName LIKE '%three%')))
        AND
       ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
OR (t1.firstName LIKE '%six%')))
  )

Regards!
Esteban A. Maringolo


2016-06-13 19:04 GMT-03:00  <[hidden email]>:

> Hi Esteban,
>
> Thanks for your reply.
>
> On Monday, 13 June 2016 23:24:31 UTC+2, Esteban A. Maringolo wrote:
>>
>> You'd have to query like
>>
>> SimpleQuery read: YourClass where: [:each | ((A OR: B) OR: [C])  AND:
>> [(D OR: [E]) OR: [F]]]
>>
>> Note the uppercase AND: and OR: selectors, that's so because and: and
>> or: are usually optimized by the compiler and never sent to the
>> receiver, and because Glorp uses a message archiver by intercepting
>> message sends to build the query expressions, you'd have to do that.
>
>
> I tried:
>
> (SimpleQuery read: KnowlingMeme
> where: [ :each |
> (((each name similarTo: '%one%') OR: (each name similarTo: '%two%')) OR: [
> each name similarTo: '%three%'])
> AND:
> [ ((each label similarTo: '%four%') OR: [each label similarTo: '%five%'])
> OR: [ each label similarTo: '%six%']]])
> executeIn: KnowlingGlorp current session.
>
>
> The sql generated in the QuerySelectCommand is:
>
> SELECT t1.id, t1.description, t1.creation_date, t1.mod_date, t1.name,
> t1.label, t1.icon
>  FROM knowapp_meme t1
>  WHERE t1.name LIKE '%one%' OR t1.name LIKE '%two%' OR t1.name LIKE
> '%three%' AND t1.label LIKE '%four%' OR t1.label LIKE '%five%' OR t1.label
> LIKE '%six%'
>
>
> This is a simpler query than the one I originally described, it doesn't join
> two tables, and the strings it is searching for don't make sense, but it
> demonstrates the problem (no brackets where required in the where clause).
>
> Thanks again,
> Alistair
>
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant


On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
You have an error in the syntax, the argument to an OR: message must be a Block.

    | query |
    query := SimpleQuery
                read: Person
                where: [ :each |
                    ((each firstName like: '%one%') OR: [
                        (each firstName like: '%two%' )
                         OR: [each firstName like: '%three%']])
                    AND: [
                        ((each firstName like: '%four%') OR: [
                        (each firstName like: '%five%' )
                         OR: [each firstName like: '%six%']])
                    ]
                ].
    query executeIn: session.
    ].



Produces (line breaks added by me):

SELECT *
 FROM PERSON t1
 WHERE (
        ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
OR (t1.firstName LIKE '%three%')))
        AND
       ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
OR (t1.firstName LIKE '%six%')))
  )

Regards!
Esteban A. Maringolo


Hi,

Interesting, if I copy & paste your code I get:

SELECT t1.id, t1.firstName, t1.lastName, t1.birthDate
 FROM PERSON t1
 WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ? AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I've included my version information below in case I've got an old
version loaded somehow:

Pharo5.0
Latest update: #50759
4.6.2-1-ARCH


Glorp (PierceNg.126)
ConfigurationOfGarageGlorp (EstebanMaringolo.11)
ConfigurationOfGlorp (PierceNg.58)
GarageGlorpDriver (EstebanMaringolo.7)
Glorp-SQLite3 (PierceNg.1)
GlorpTests (PierceNg.21)


This was loaded with:

Gofer it  
smalltalkhubUser: 'TorstenBergmann' project: 'UDBC';
configuration;
load.
(Smalltalk at: #ConfigurationOfUDBC) loadBleedingEdge.

Gofer it
smalltalkhubUser: 'DBXTalk' project: 'Garage';
configurationOf: 'GarageGlorp';
load.
#ConfigurationOfGarageGlorp asClass project stableVersion load.

Gofer it
smalltalkhubUser: 'DBXTalk' project: 'Glorp';
package: 'Glorp-SQLite3';
load.
(Smalltalk at: #GlorpSQLite3CIConfiguration) new configureSqlite3.
(Smalltalk at: #GlorpDemoTablePopulatorResource) invalidateSetup.



Thanks!
Alistair
 

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant


On Tuesday, 14 June 2016 09:06:31 UTC+2, [hidden email] wrote:


On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
You have an error in the syntax, the argument to an OR: message must be a Block.

    | query |
    query := SimpleQuery
                read: Person
                where: [ :each |
                    ((each firstName like: '%one%') OR: [
                        (each firstName like: '%two%' )
                         OR: [each firstName like: '%three%']])
                    AND: [
                        ((each firstName like: '%four%') OR: [
                        (each firstName like: '%five%' )
                         OR: [each firstName like: '%six%']])
                    ]
                ].
    query executeIn: session.
    ].



Produces (line breaks added by me):

SELECT *
 FROM PERSON t1
 WHERE (
        ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
OR (t1.firstName LIKE '%three%')))
        AND
       ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
OR (t1.firstName LIKE '%six%')))
  )

Regards!
Esteban A. Maringolo


Hi,

Interesting, if I copy & paste your code I get:

SELECT <a href="http://t1.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;">t1.id, t1.firstName, t1.lastName, t1.birthDate
 FROM PERSON t1
 WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ? AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I've included my version information below in case I've got an old
version loaded somehow:

Pharo5.0
Latest update: #50759
4.6.2-1-ARCH


Glorp (PierceNg.126)
ConfigurationOfGarageGlorp (EstebanMaringolo.11)
ConfigurationOfGlorp (PierceNg.58)
GarageGlorpDriver (EstebanMaringolo.7)
Glorp-SQLite3 (PierceNg.1)
GlorpTests (PierceNg.21)


This was loaded with:

Gofer it  
smalltalkhubUser: 'TorstenBergmann' project: 'UDBC';
configuration;
load.
(Smalltalk at: #ConfigurationOfUDBC) loadBleedingEdge.

Gofer it
smalltalkhubUser: 'DBXTalk' project: 'Garage';
configurationOf: 'GarageGlorp';
load.
#ConfigurationOfGarageGlorp asClass project stableVersion load.

Gofer it
smalltalkhubUser: 'DBXTalk' project: 'Glorp';
package: 'Glorp-SQLite3';
load.
(Smalltalk at: #GlorpSQLite3CIConfiguration) new configureSqlite3.
(Smalltalk at: #GlorpDemoTablePopulatorResource) invalidateSetup.



Thanks!
Alistair

P.S.  If I open each of the repositories in the Monticello Browser I have the latest package loaded.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Pierce Ng-3
In reply to this post by Alistair Grant
On Tue, Jun 14, 2016 at 12:06:31AM -0700, [hidden email] wrote:

> On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
> > SELECT *
> >  FROM PERSON t1
> >  WHERE (
> >         ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
> > OR (t1.firstName LIKE '%three%')))
> >         AND
> >        ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
> > OR (t1.firstName LIKE '%six%')))
> >   )
>
> SELECT t1.id, t1.firstName, t1.lastName, t1.birthDate
>  FROM PERSON t1
>  WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?
> AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I think Esteban did this using the Postgres driver, not SQLite. The Postgres
driver doesn't support parameter binding, hence Glorp constructs a complete SQL
string with data values embedded - '%one', '%two%', etc.

My SQLite driver _does_ support parameter binding, hence the generated query is
firstName LIKE ? OR firstName like ?, etc.

Try setting UDBCSQLite3Platform>>supportsBinding to false and re-run.  Since
Glorp supports what it calls bound and unbound execution modes, there are two
code paths involved within Glorp.

Personally, I just feel safer using parameter binding and avoid query/command
string construction whenever I can.

Note: I just ran the test suite with UDBCSQLite3Platform>>supportsBinding =
false. I got 888 tests run, 884 passes, 13 skipped, 1 failure, 3 errors.

Pierce


--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Hi Pierce,

On Tuesday, 14 June 2016 14:49:26 UTC+2, Pierce Ng wrote:
On Tue, Jun 14, 2016 at 12:06:31AM -0700, <a href="javascript:" target="_blank" gdf-obfuscated-mailto="XQowSeI8BAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">akgra...@... wrote:

> On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
> > SELECT *
> >  FROM PERSON t1
> >  WHERE (
> >         ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
> > OR (t1.firstName LIKE '%three%')))
> >         AND
> >        ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
> > OR (t1.firstName LIKE '%six%')))
> >   )
>
> SELECT <a href="http://t1.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;">t1.id, t1.firstName, t1.lastName, t1.birthDate
>  FROM PERSON t1
>  WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?
> AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I think Esteban did this using the Postgres driver, not SQLite. The Postgres
driver doesn't support parameter binding, hence Glorp constructs a complete SQL
string with data values embedded - '%one', '%two%', etc.

My SQLite driver _does_ support parameter binding, hence the generated query is
firstName LIKE ? OR firstName like ?, etc.

Try setting UDBCSQLite3Platform>>supportsBinding to false and re-run.  Since
Glorp supports what it calls bound and unbound execution modes, there are two
code paths involved within Glorp.

Personally, I just feel safer using parameter binding and avoid query/command
string construction whenever I can.

Note: I just ran the test suite with UDBCSQLite3Platform>>supportsBinding =
false. I got 888 tests run, 884 passes, 13 skipped, 1 failure, 3 errors.


Thanks for your reply, but the interesting part is not the parameter binding, but that the statement generated by Esteban has the where clause correctly grouped, i.e. brackets around the OR expressions, and the clause generated by me is (incorrectly) missing them.

My guess is that it is a problem with my version of RelationExpression, which is why I included the package versions I have loaded.

For the record, I agree with using the parameter binding.

Thanks!
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alan Knight
Yes, that seems very odd. As I remember it, we always printed parentheses for everything. They were just hard-coded into the way the expression nodes printed. Without that you could get all kinds of problems.

On Tue, Jun 14, 2016 at 6:09 AM <[hidden email]> wrote:
Hi Pierce,

On Tuesday, 14 June 2016 14:49:26 UTC+2, Pierce Ng wrote:
On Tue, Jun 14, 2016 at 12:06:31AM -0700, [hidden email] wrote:

> On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
> > SELECT *
> >  FROM PERSON t1
> >  WHERE (
> >         ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
> > OR (t1.firstName LIKE '%three%')))
> >         AND
> >        ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
> > OR (t1.firstName LIKE '%six%')))
> >   )
>
> SELECT t1.id, t1.firstName, t1.lastName, t1.birthDate
>  FROM PERSON t1
>  WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?
> AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I think Esteban did this using the Postgres driver, not SQLite. The Postgres
driver doesn't support parameter binding, hence Glorp constructs a complete SQL
string with data values embedded - '%one', '%two%', etc.

My SQLite driver _does_ support parameter binding, hence the generated query is
firstName LIKE ? OR firstName like ?, etc.

Try setting UDBCSQLite3Platform>>supportsBinding to false and re-run.  Since
Glorp supports what it calls bound and unbound execution modes, there are two
code paths involved within Glorp.

Personally, I just feel safer using parameter binding and avoid query/command
string construction whenever I can.

Note: I just ran the test suite with UDBCSQLite3Platform>>supportsBinding =
false. I got 888 tests run, 884 passes, 13 skipped, 1 failure, 3 errors.


Thanks for your reply, but the interesting part is not the parameter binding, but that the statement generated by Esteban has the where clause correctly grouped, i.e. brackets around the OR expressions, and the clause generated by me is (incorrectly) missing them.

My guess is that it is a problem with my version of RelationExpression, which is why I included the package versions I have loaded.

For the record, I agree with using the parameter binding.

Thanks!
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant


On Tuesday, 14 June 2016 21:52:32 UTC+2, alan.knight wrote:
Yes, that seems very odd. As I remember it, we always printed parentheses for everything. They were just hard-coded into the way the expression nodes printed. Without that you could get all kinds of problems.

On Tue, Jun 14, 2016 at 6:09 AM <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="sEXlyvhTBAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">akgra...@...> wrote:
Hi Pierce,

On Tuesday, 14 June 2016 14:49:26 UTC+2, Pierce Ng wrote:
On Tue, Jun 14, 2016 at 12:06:31AM -0700, [hidden email] wrote:

> On Tuesday, 14 June 2016 03:56:22 UTC+2, Esteban A. Maringolo wrote:
> > SELECT *
> >  FROM PERSON t1
> >  WHERE (
> >         ((t1.firstName LIKE '%one%') OR ((t1.firstName LIKE '%two%')
> > OR (t1.firstName LIKE '%three%')))
> >         AND
> >        ((t1.firstName LIKE '%four%') OR ((t1.firstName LIKE '%five%')
> > OR (t1.firstName LIKE '%six%')))
> >   )
>
> SELECT <a href="http://t1.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ft1.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFUSAPqycKag5PTtPTdajv-cdD10A&#39;;return true;">t1.id, t1.firstName, t1.lastName, t1.birthDate
>  FROM PERSON t1
>  WHERE t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?
> AND t1.firstName LIKE ? OR t1.firstName LIKE ? OR t1.firstName LIKE ?

I think Esteban did this using the Postgres driver, not SQLite. The Postgres
driver doesn't support parameter binding, hence Glorp constructs a complete SQL
string with data values embedded - '%one', '%two%', etc.

My SQLite driver _does_ support parameter binding, hence the generated query is
firstName LIKE ? OR firstName like ?, etc.

Try setting UDBCSQLite3Platform>>supportsBinding to false and re-run.  Since
Glorp supports what it calls bound and unbound execution modes, there are two
code paths involved within Glorp.

Personally, I just feel safer using parameter binding and avoid query/command
string construction whenever I can.

Note: I just ran the test suite with UDBCSQLite3Platform>>supportsBinding =
false. I got 888 tests run, 884 passes, 13 skipped, 1 failure, 3 errors.


Thanks for your reply, but the interesting part is not the parameter binding, but that the statement generated by Esteban has the where clause correctly grouped, i.e. brackets around the OR expressions, and the clause generated by me is (incorrectly) missing them.

My guess is that it is a problem with my version of RelationExpression, which is why I included the package versions I have loaded.

For the record, I agree with using the parameter binding.

It looks like I can get this working by removing SQLite3Platform>>parenthesizeCommandsInCompoundQuery, which causes my query to be generated correctly, but also causes 22 errors in the automated tests where previously none occurred:

901 run, 879 passes, 12 skipped, 0 expected failures, 0 failures, 22 errors, 0 unexpected passes
Failures:

Errors:
GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclasses
GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclassesNoWhereClause
GlorpHorizontalInheritanceTest>>#testReadRelationshipSingleQuery
GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead
GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead2
GlorpObjectMappedToImaginaryTableTest>>#testReadBackAllCustomer
GlorpObjectMappedToImaginaryTableTest>>#testReadBackEntireExample
GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhere
GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhereWithJoin
GlorpQueryUnionTest>>#testBasicExcept
GlorpQueryUnionTest>>#testBasicExcept2
GlorpQueryUnionTest>>#testBasicExcept3
GlorpQueryUnionTest>>#testBasicIntersect
GlorpQueryUnionTest>>#testBasicIntersect2
GlorpQueryUnionTest>>#testBasicIntersect3
GlorpQueryUnionTest>>#testBasicUnion
GlorpQueryUnionTest>>#testOrderByInUnion
GlorpQueryUnionTest>>#testThreeWayUnion
GlorpQueryUnionTest>>#testUnionWithParameters
GlorpQueryUnionTest>>#testUnionWithRetrieveDisaparateResults
GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults
GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults2

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant


On Wednesday, 15 June 2016 00:01:57 UTC+2, [hidden email] wrote:

It looks like I can get this working by removing SQLite3Platform>>parenthesizeCommandsInCompoundQuery, which causes my query to be generated correctly, but also causes 22 errors in the automated tests where previously none occurred:

901 run, 879 passes, 12 skipped, 0 expected failures, 0 failures, 22 errors, 0 unexpected passes
Failures:

Errors:
GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclasses
GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclassesNoWhereClause
GlorpHorizontalInheritanceTest>>#testReadRelationshipSingleQuery
GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead
GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead2
GlorpObjectMappedToImaginaryTableTest>>#testReadBackAllCustomer
GlorpObjectMappedToImaginaryTableTest>>#testReadBackEntireExample
GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhere
GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhereWithJoin
GlorpQueryUnionTest>>#testBasicExcept
GlorpQueryUnionTest>>#testBasicExcept2
GlorpQueryUnionTest>>#testBasicExcept3
GlorpQueryUnionTest>>#testBasicIntersect
GlorpQueryUnionTest>>#testBasicIntersect2
GlorpQueryUnionTest>>#testBasicIntersect3
GlorpQueryUnionTest>>#testBasicUnion
GlorpQueryUnionTest>>#testOrderByInUnion
GlorpQueryUnionTest>>#testThreeWayUnion
GlorpQueryUnionTest>>#testUnionWithParameters
GlorpQueryUnionTest>>#testUnionWithRetrieveDisaparateResults
GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults
GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults2

My current thoughts are that #parenthesizeCommandsInCompoundQuery is
actually doing double duty.  It is flagging whether the platform needs
parentheses for compound queries, but it is also inadvertantly being
used to flag whether parentheses should be used for grouping AND / OR
relations in WHERE clauses.

I'll add a separate flag for the WHERE clause and that should
(hopefully) resolve all the errors.

As I'm just starting with Glorp I could be way off here, so any feedback
is appreciated.

 Thanks,
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Esteban A. Maringolo
One good thing about Glorp, is that it has a fairly complete test
suite, so you can change "with confidence", because most of the times
if you break something it will be catched by a failing test case.

However, because of the multiplatform support of it, you should test
if your changes don't break the support for other platforms.

There is an outdated CI server for Glorp in
https://ci.inria.fr/dbxtalk/view/Glorp/, but I don't control the
slaves, and because of that I don't know why some builds are reported
as failure when they work perfectly locally or even in other OSs.
I'd like to move the new port to TravisCI or something similar. We'll
see. I don't have much time these days.

Regards!

Esteban A. Maringolo


2016-06-15 2:33 GMT-03:00  <[hidden email]>:

>
>
> On Wednesday, 15 June 2016 00:01:57 UTC+2, [hidden email] wrote:
>>
>>
>> It looks like I can get this working by removing
>> SQLite3Platform>>parenthesizeCommandsInCompoundQuery, which causes my query
>> to be generated correctly, but also causes 22 errors in the automated tests
>> where previously none occurred:
>>
>> 901 run, 879 passes, 12 skipped, 0 expected failures, 0 failures, 22
>> errors, 0 unexpected passes
>> Failures:
>>
>> Errors:
>> GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclasses
>>
>> GlorpHorizontalInheritanceTest>>#testDirectSingleQueryForSubclassesNoWhereClause
>> GlorpHorizontalInheritanceTest>>#testReadRelationshipSingleQuery
>> GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead
>> GlorpObjectMappedToImaginaryTableTest>>#testComplexSubselectRead2
>> GlorpObjectMappedToImaginaryTableTest>>#testReadBackAllCustomer
>> GlorpObjectMappedToImaginaryTableTest>>#testReadBackEntireExample
>> GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhere
>> GlorpObjectMappedToImaginaryTableTest>>#testReadCustomerWhereWithJoin
>> GlorpQueryUnionTest>>#testBasicExcept
>> GlorpQueryUnionTest>>#testBasicExcept2
>> GlorpQueryUnionTest>>#testBasicExcept3
>> GlorpQueryUnionTest>>#testBasicIntersect
>> GlorpQueryUnionTest>>#testBasicIntersect2
>> GlorpQueryUnionTest>>#testBasicIntersect3
>> GlorpQueryUnionTest>>#testBasicUnion
>> GlorpQueryUnionTest>>#testOrderByInUnion
>> GlorpQueryUnionTest>>#testThreeWayUnion
>> GlorpQueryUnionTest>>#testUnionWithParameters
>> GlorpQueryUnionTest>>#testUnionWithRetrieveDisaparateResults
>> GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults
>> GlorpQueryUnionTest>>#testUnionWithRetrieveThoroughlyDisaparateResults2
>
>
> My current thoughts are that #parenthesizeCommandsInCompoundQuery is
> actually doing double duty.  It is flagging whether the platform needs
> parentheses for compound queries, but it is also inadvertantly being
> used to flag whether parentheses should be used for grouping AND / OR
> relations in WHERE clauses.
>
> I'll add a separate flag for the WHERE clause and that should
> (hopefully) resolve all the errors.
>
> As I'm just starting with Glorp I could be way off here, so any feedback
> is appreciated.
>
>  Thanks,
> Alistair
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Hi All,

Just to clarify my description of the bug in previous messages:

The problem is that RelationExpression uses the flag
Descriptor>>parenthesizeCommandsInCompoundQuery to determine whether to
parenthesise the generated sql strings.  This hasn't been a problem on
other platforms because it is always set to true.  SQLite changes the
flag to false, and RelationExpression incorrectly fails to add
parentheses to its sub-expressions, breaking the sql generated for where
clauses.


I have two solutions for this bug:

1. Add an #alwaysParenthesise method to RelationExpression which is used
to ensure that the sql for WHERE clauses are constructed correctly (this
takes Glorp back to the behaviour that Alan described).

2. Add WhereClauseExpression as a subclass of RelationExpression.
RelationExpression>>named:basedOn:withArguments: is modified to answer a
WhereClauseExpression if the relation is one used in a where clause.
WhereClauseExpression simply overrides the sql generation to always use
parentheses.


The advantage of the first is that it is a smaller change.

The advantage of the second is that I think that architecturally it is a
better solution.  RelationExpression seems to have too much
responsibility at the moment, and separating out the responsiblity in
the subclasses will allow for more specific behaviour when required.
The problem is that in the current code base it isn't easy to pick which
code paths create WhereClauseExpressions and which create
RelationExpressions.  As mentioned above, if I implement this I'll be
modifying RelationExpression>>named:basedOn:withArguments: to return a
WhereClauseExpression when it sees an appropriate relation.


On Wed, Jun 15, 2016 at 10:50:46AM -0300, Esteban A. Maringolo wrote:
> One good thing about Glorp, is that it has a fairly complete test
> suite, so you can change "with confidence", because most of the times
> if you break something it will be catched by a failing test case.
> However, because of the multiplatform support of it, you should test
> if your changes don't break the support for other platforms.

I've added a multi-platform automated test that fails in the existing
code base and passes after I've added my fix (and doesn't break any of
the other tests).


I'd like to be able to submit this changes for inclusion in Glorp, so...

Which solution is preferred?

Thanks!
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant


On Sunday, 19 June 2016 08:26:01 UTC+2, [hidden email] wrote:
Hi All,

Just to clarify my description of the bug in previous messages:

The problem is that RelationExpression uses the flag
Descriptor>>parenthesizeCommandsInCompoundQuery to determine whether to
parenthesise the generated sql strings.  This hasn't been a problem on
other platforms because it is always set to true.  SQLite changes the
flag to false, and RelationExpression incorrectly fails to add
parentheses to its sub-expressions, breaking the sql generated for where
clauses.


I have two solutions for this bug:

1. Add an #alwaysParenthesise method to RelationExpression which is used
to ensure that the sql for WHERE clauses are constructed correctly (this
takes Glorp back to the behaviour that Alan described).

2. Add WhereClauseExpression as a subclass of RelationExpression.
RelationExpression>>named:basedOn:withArguments: is modified to answer a
WhereClauseExpression if the relation is one used in a where clause.
WhereClauseExpression simply overrides the sql generation to always use
parentheses.


The advantage of the first is that it is a smaller change.

The advantage of the second is that I think that architecturally it is a
better solution.  RelationExpression seems to have too much
responsibility at the moment, and separating out the responsiblity in
the subclasses will allow for more specific behaviour when required.
The problem is that in the current code base it isn't easy to pick which
code paths create WhereClauseExpressions and which create
RelationExpressions.  As mentioned above, if I implement this I'll be
modifying RelationExpression>>named:basedOn:withArguments: to return a
WhereClauseExpression when it sees an appropriate relation.


On Wed, Jun 15, 2016 at 10:50:46AM -0300, Esteban A. Maringolo wrote:
> One good thing about Glorp, is that it has a fairly complete test
> suite, so you can change "with confidence", because most of the times
> if you break something it will be catched by a failing test case.
> However, because of the multiplatform support of it, you should test
> if your changes don't break the support for other platforms.

I've added a multi-platform automated test that fails in the existing
code base and passes after I've added my fix (and doesn't break any of
the other tests).


I'd like to be able to submit this changes for inclusion in Glorp, so...

Which solution is preferred?

Thanks!
Alistair


As usual, posting makes me think of a better option:

Create a subclass of RelationExpression, CompoundExpression that does the
#parenthesizeCommandsInCompoundQuery test.
This could also be done in addition to the WhereClauseExpression.

Cheers,
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Hi All,

After some mistakes and misunderstandings on my part and other bugs I
think I'm finally ready to submit a fix for this.  The modified code is
included below.

My understanding is that CompoundExpression SQL isn't generated through
RelationExpression, but through CompoundSelectCommand, thus the
conditional test with #parenthesizeCommandsInCompoundQuery isn't
required in RelationExpression.

I'd like to submit this so that it is incorporated in to future Glorp
releases, but I'm not sure of the process.  Should I open an issue in
fogbugz, or...?

Thanks,
Alistair



'From Pharo5.0 of 16 April 2015 [Latest update: #50757] on 29 June 2016 at 6:31:35.559615 pm'!

!RelationExpression methodsFor: 'printing SQL' stamp: 'AlistairGrant 6/21/2016 06:54'!
printBasicSQLOn: aCommand withParameters: aDictionary
aCommand nextPut: $(.
leftChild printSQLOn: aCommand withParameters: aDictionary.
self printComparisonTo: rightChild withParameters: aDictionary on: aCommand.
self printOracleOuterJoinOn: aCommand.
aCommand nextPut: $)! !

!RelationExpression methodsFor: 'printing SQL' stamp: 'AlistairGrant 6/21/2016 06:55'!
printBasicUnqualifiedSQLOn: aCommand withParameters: aDictionary
aCommand nextPut: $(.
leftChild printUnqualifiedSQLOn: aCommand withParameters: aDictionary.
self printUnqualifiedComparisonTo: rightChild withParameters: aDictionary on: aCommand.
self printOracleOuterJoinOn: aCommand.
aCommand nextPut: $)! !


!GlorpQueryUnionTest methodsFor: 'support' stamp: 'AlistairGrant 6/21/2016 06:56'!
helpTestCompoundExpressionOnPlatform: platform
| query union |
platform useBinding: false.
query := Query read: GlorpAddress.
union := query unionAll: query.
union session: session.
^(union sqlWith: Dictionary new) sqlString.! !

!GlorpQueryUnionTest methodsFor: 'tests-unit' stamp: 'AlistairGrant 6/21/2016 08:53'!
testCreateCompoundExpression
| platform compoundString expectedString |
expectedString := 'SELECT DISTINCT * FROM (SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
 FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
 FROM GR_ADDRESS t1) t1'.
platform := PostgreSQLPlatform new.
compoundString := self helpTestCompoundExpressionOnPlatform: platform.
self assert: compoundString equals: expectedString.
platform := SQLServerPlatform new.
compoundString := self helpTestCompoundExpressionOnPlatform: platform.
self assert: compoundString equals: expectedString.
platform := OraclePlatform new.
compoundString := self helpTestCompoundExpressionOnPlatform: platform.
self assert: compoundString equals: expectedString.
platform := SQLite3Platform new.
compoundString := self helpTestCompoundExpressionOnPlatform: platform.
self assert: compoundString equals: expectedString.
! !


On Sunday, 19 June 2016 09:43:08 UTC+2, [hidden email] wrote:


On Sunday, 19 June 2016 08:26:01 UTC+2, [hidden email] wrote:
Hi All,

Just to clarify my description of the bug in previous messages:

The problem is that RelationExpression uses the flag
Descriptor>>parenthesizeCommandsInCompoundQuery to determine whether to
parenthesise the generated sql strings.  This hasn't been a problem on
other platforms because it is always set to true.  SQLite changes the
flag to false, and RelationExpression incorrectly fails to add
parentheses to its sub-expressions, breaking the sql generated for where
clauses.


I have two solutions for this bug:

1. Add an #alwaysParenthesise method to RelationExpression which is used
to ensure that the sql for WHERE clauses are constructed correctly (this
takes Glorp back to the behaviour that Alan described).

2. Add WhereClauseExpression as a subclass of RelationExpression.
RelationExpression>>named:basedOn:withArguments: is modified to answer a
WhereClauseExpression if the relation is one used in a where clause.
WhereClauseExpression simply overrides the sql generation to always use
parentheses.


The advantage of the first is that it is a smaller change.

The advantage of the second is that I think that architecturally it is a
better solution.  RelationExpression seems to have too much
responsibility at the moment, and separating out the responsiblity in
the subclasses will allow for more specific behaviour when required.
The problem is that in the current code base it isn't easy to pick which
code paths create WhereClauseExpressions and which create
RelationExpressions.  As mentioned above, if I implement this I'll be
modifying RelationExpression>>named:basedOn:withArguments: to return a
WhereClauseExpression when it sees an appropriate relation.


On Wed, Jun 15, 2016 at 10:50:46AM -0300, Esteban A. Maringolo wrote:
> One good thing about Glorp, is that it has a fairly complete test
> suite, so you can change "with confidence", because most of the times
> if you break something it will be catched by a failing test case.
> However, because of the multiplatform support of it, you should test
> if your changes don't break the support for other platforms.

I've added a multi-platform automated test that fails in the existing
code base and passes after I've added my fix (and doesn't break any of
the other tests).


I'd like to be able to submit this changes for inclusion in Glorp, so...

Which solution is preferred?

Thanks!
Alistair


As usual, posting makes me think of a better option:

Create a subclass of RelationExpression, CompoundExpression that does the
#parenthesizeCommandsInCompoundQuery test.
This could also be done in addition to the WhereClauseExpression.

Cheers,
Alistair

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Esteban A. Maringolo
In the case of Pharo, I could add you as a contributor to the
SmalltalkHub project and you can commit directly.
Esteban A. Maringolo


2016-06-29 16:02 GMT-03:00  <[hidden email]>:

> Hi All,
>
> After some mistakes and misunderstandings on my part and other bugs I
> think I'm finally ready to submit a fix for this.  The modified code is
> included below.
>
> My understanding is that CompoundExpression SQL isn't generated through
> RelationExpression, but through CompoundSelectCommand, thus the
> conditional test with #parenthesizeCommandsInCompoundQuery isn't
> required in RelationExpression.
>
> I'd like to submit this so that it is incorporated in to future Glorp
> releases, but I'm not sure of the process.  Should I open an issue in
> fogbugz, or...?
>
> Thanks,
> Alistair
>
>
>
> 'From Pharo5.0 of 16 April 2015 [Latest update: #50757] on 29 June 2016 at
> 6:31:35.559615 pm'!
>
> !RelationExpression methodsFor: 'printing SQL' stamp: 'AlistairGrant
> 6/21/2016 06:54'!
> printBasicSQLOn: aCommand withParameters: aDictionary
> aCommand nextPut: $(.
> leftChild printSQLOn: aCommand withParameters: aDictionary.
> self printComparisonTo: rightChild withParameters: aDictionary on: aCommand.
> self printOracleOuterJoinOn: aCommand.
> aCommand nextPut: $)! !
>
> !RelationExpression methodsFor: 'printing SQL' stamp: 'AlistairGrant
> 6/21/2016 06:55'!
> printBasicUnqualifiedSQLOn: aCommand withParameters: aDictionary
> aCommand nextPut: $(.
> leftChild printUnqualifiedSQLOn: aCommand withParameters: aDictionary.
> self printUnqualifiedComparisonTo: rightChild withParameters: aDictionary
> on: aCommand.
> self printOracleOuterJoinOn: aCommand.
> aCommand nextPut: $)! !
>
>
> !GlorpQueryUnionTest methodsFor: 'support' stamp: 'AlistairGrant 6/21/2016
> 06:56'!
> helpTestCompoundExpressionOnPlatform: platform
> | query union |
> platform useBinding: false.
> query := Query read: GlorpAddress.
> union := query unionAll: query.
> union session: session.
> ^(union sqlWith: Dictionary new) sqlString.! !
>
> !GlorpQueryUnionTest methodsFor: 'tests-unit' stamp: 'AlistairGrant
> 6/21/2016 08:53'!
> testCreateCompoundExpression
> | platform compoundString expectedString |
> expectedString := 'SELECT DISTINCT * FROM (SELECT t1.ID, t1.STREET,
> t1.HOUSE_NUM
>  FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
>  FROM GR_ADDRESS t1) t1'.
> platform := PostgreSQLPlatform new.
> compoundString := self helpTestCompoundExpressionOnPlatform: platform.
> self assert: compoundString equals: expectedString.
> platform := SQLServerPlatform new.
> compoundString := self helpTestCompoundExpressionOnPlatform: platform.
> self assert: compoundString equals: expectedString.
> platform := OraclePlatform new.
> compoundString := self helpTestCompoundExpressionOnPlatform: platform.
> self assert: compoundString equals: expectedString.
> platform := SQLite3Platform new.
> compoundString := self helpTestCompoundExpressionOnPlatform: platform.
> self assert: compoundString equals: expectedString.
> ! !
>
>
> On Sunday, 19 June 2016 09:43:08 UTC+2, [hidden email] wrote:
>>
>>
>>
>> On Sunday, 19 June 2016 08:26:01 UTC+2, [hidden email] wrote:
>>>
>>> Hi All,
>>>
>>> Just to clarify my description of the bug in previous messages:
>>>
>>> The problem is that RelationExpression uses the flag
>>> Descriptor>>parenthesizeCommandsInCompoundQuery to determine whether to
>>> parenthesise the generated sql strings.  This hasn't been a problem on
>>> other platforms because it is always set to true.  SQLite changes the
>>> flag to false, and RelationExpression incorrectly fails to add
>>> parentheses to its sub-expressions, breaking the sql generated for where
>>> clauses.
>>>
>>>
>>> I have two solutions for this bug:
>>>
>>> 1. Add an #alwaysParenthesise method to RelationExpression which is used
>>> to ensure that the sql for WHERE clauses are constructed correctly (this
>>> takes Glorp back to the behaviour that Alan described).
>>>
>>> 2. Add WhereClauseExpression as a subclass of RelationExpression.
>>> RelationExpression>>named:basedOn:withArguments: is modified to answer a
>>> WhereClauseExpression if the relation is one used in a where clause.
>>> WhereClauseExpression simply overrides the sql generation to always use
>>> parentheses.
>>>
>>>
>>> The advantage of the first is that it is a smaller change.
>>>
>>> The advantage of the second is that I think that architecturally it is a
>>> better solution.  RelationExpression seems to have too much
>>> responsibility at the moment, and separating out the responsiblity in
>>> the subclasses will allow for more specific behaviour when required.
>>> The problem is that in the current code base it isn't easy to pick which
>>> code paths create WhereClauseExpressions and which create
>>> RelationExpressions.  As mentioned above, if I implement this I'll be
>>> modifying RelationExpression>>named:basedOn:withArguments: to return a
>>> WhereClauseExpression when it sees an appropriate relation.
>>>
>>>
>>> On Wed, Jun 15, 2016 at 10:50:46AM -0300, Esteban A. Maringolo wrote:
>>> > One good thing about Glorp, is that it has a fairly complete test
>>> > suite, so you can change "with confidence", because most of the times
>>> > if you break something it will be catched by a failing test case.
>>> >
>>> > However, because of the multiplatform support of it, you should test
>>> > if your changes don't break the support for other platforms.
>>>
>>> I've added a multi-platform automated test that fails in the existing
>>> code base and passes after I've added my fix (and doesn't break any of
>>> the other tests).
>>>
>>>
>>> I'd like to be able to submit this changes for inclusion in Glorp, so...
>>>
>>> Which solution is preferred?
>>>
>>> Thanks!
>>> Alistair
>>
>>
>>
>> As usual, posting makes me think of a better option:
>>
>> Create a subclass of RelationExpression, CompoundExpression that does the
>> #parenthesizeCommandsInCompoundQuery test.
>> This could also be done in addition to the WhereClauseExpression.
>>
>> Cheers,
>> Alistair
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Hi Esteban,

That would be great, thanks.  I'm Alistair on smalltalkhub.com.

I'd also still like to submit it upstream if possible.

Thanks,
Alistair
P.S.  I haven't forgotten about the suggestions for PharoInProgress /
Glorp.  I wanted to get my application at least partially working so I
have a better understanding of how the whole system works.


On Wednesday, 29 June 2016 23:18:52 UTC+2, Esteban A. Maringolo wrote:
In the case of Pharo, I could add you as a contributor to the
SmalltalkHub project and you can commit directly.
Esteban A. Maringolo


2016-06-29 16:02 GMT-03:00  <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="k7VdXmPzCAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">akgra...@...>:
> Hi All,
>
> After some mistakes and misunderstandings on my part and other bugs I
> think I'm finally ready to submit a fix for this.  The modified code is
> included below.
> ...

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alan Knight
Niall Ross at Cincom could probably help with getting it upstreamed.

On Thu, Jun 30, 2016, 2:59 AM <[hidden email]> wrote:
Hi Esteban,

That would be great, thanks.  I'm Alistair on smalltalkhub.com.

I'd also still like to submit it upstream if possible.

Thanks,
Alistair
P.S.  I haven't forgotten about the suggestions for PharoInProgress /
Glorp.  I wanted to get my application at least partially working so I
have a better understanding of how the whole system works.


On Wednesday, 29 June 2016 23:18:52 UTC+2, Esteban A. Maringolo wrote:
In the case of Pharo, I could add you as a contributor to the
SmalltalkHub project and you can commit directly.
Esteban A. Maringolo


2016-06-29 16:02 GMT-03:00  <[hidden email]>:
> Hi All,
>
> After some mistakes and misunderstandings on my part and other bugs I
> think I'm finally ready to submit a fix for this.  The modified code is
> included below.
> ...

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Esteban A. Maringolo
In reply to this post by Alistair Grant
I added you as a team member of the DBXTalk team, you should be able
to commit to SmalltalkHub using your credentials.

Regards!
Esteban A. Maringolo


2016-06-30 3:59 GMT-03:00  <[hidden email]>:

> Hi Esteban,
>
> That would be great, thanks.  I'm Alistair on smalltalkhub.com.
>
> I'd also still like to submit it upstream if possible.
>
> Thanks,
> Alistair
> P.S.  I haven't forgotten about the suggestions for PharoInProgress /
> Glorp.  I wanted to get my application at least partially working so I
> have a better understanding of how the whole system works.
>
>
> On Wednesday, 29 June 2016 23:18:52 UTC+2, Esteban A. Maringolo wrote:
>>
>> In the case of Pharo, I could add you as a contributor to the
>> SmalltalkHub project and you can commit directly.
>> Esteban A. Maringolo
>>
>>
>> 2016-06-29 16:02 GMT-03:00  <[hidden email]>:
>> > Hi All,
>> >
>> > After some mistakes and misunderstandings on my part and other bugs I
>> > think I'm finally ready to submit a fix for this.  The modified code is
>> > included below.
>> > ...
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Generating grouped where clause

Alistair Grant
Thanks, Estaban!

It will take me a while to package it up, test the package and submit it.
I'll let you know when it's done.

Thanks again,
Alistair.


On Thursday, 30 June 2016 16:01:02 UTC+2, Esteban A. Maringolo wrote:
I added you as a team member of the DBXTalk team, you should be able
to commit to SmalltalkHub using your credentials.

Regards!
Esteban A. Maringolo


2016-06-30 3:59 GMT-03:00  <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="PCkeaBMqCQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">akgra...@...>:

> Hi Esteban,
>
> That would be great, thanks.  I'm Alistair on <a href="http://smalltalkhub.com" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fsmalltalkhub.com\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEvRRCzmW4F66fF7xw12y1ooQ3tFw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fsmalltalkhub.com\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEvRRCzmW4F66fF7xw12y1ooQ3tFw&#39;;return true;">smalltalkhub.com.
>
> I'd also still like to submit it upstream if possible.
>
> Thanks,
> Alistair
> P.S.  I haven't forgotten about the suggestions for PharoInProgress /
> Glorp.  I wanted to get my application at least partially working so I
> have a better understanding of how the whole system works.
>
>
> On Wednesday, 29 June 2016 23:18:52 UTC+2, Esteban A. Maringolo wrote:
>>
>> In the case of Pharo, I could add you as a contributor to the
>> SmalltalkHub project and you can commit directly.
>> Esteban A. Maringolo
>>
>>
>> 2016-06-29 16:02 GMT-03:00  <[hidden email]>:
>> > Hi All,
>> >
>> > After some mistakes and misunderstandings on my part and other bugs I
>> > think I'm finally ready to submit a fix for this.  The modified code is
>> > included below.
>> > ...
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="PCkeaBMqCQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">glorp-group...@googlegroups.com.
> To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="PCkeaBMqCQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">glorp...@....
> Visit this group at <a href="https://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;">https://groups.google.com/group/glorp-group.
> For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
12