Hi Massimo, thank you very much for this detailed explanation.
*About the "boolean query":*
You are right (of course :-)), that a query like
(db.mytable.field>'C') & b
with b as a boolean expression is not mentioned in the manual.
But I think it is not forbidden in SQL and it coulld be useful.
What I want is to switch on or off some parts of a query. A simplified
example:
q = ((db.table.field1>1)&boolean1) | ((db.table.field2>1)&boolean2) ...
This would also work:
if boolean1:
q |= db.table.field1 > 1
if boolean2:
q |= db.table.field2 > 1
....
Maybe this would be a better solution and it does not need a "&
boolean1"...
But I need an inital value for q and now I have the same problem: I need a
q = False
at the beginning. What shall I do? Use a silly query like db.table.id==0
which would be always false?
Or is there another solution without these problems?
Therefore: maybe it would be possible to add two querys, one giving always
False, the other True?
*About groupby:*
sqlite does not like "distinct" (lines 1 and 3), but groupby works now as
expected (line 6). sqlite and postgres are now compatible to each other
(lines 2 and 6).
Testtables # Datebase Command Result 0sqlitedb(db.mytable.id>0).select(
orderby=db.mytable.field2, groupby=db.mytable.field2)mytable.id
mytable.field1mytable.field22Bx5Ey 1sqlitedb(db.mytable.id>0).select(
orderby=db.mytable.field2, distinct=db.mytable.field2)near "ON": syntax
error 2sqlitedb(db.mytable.id>0).select(db.mytable.field2,
orderby=db.mytable.field2, groupby=db.mytable.field2)mytable.field2xy 3
sqlitedb(db.mytable.id>0).select(db.mytable.field2,
orderby=db.mytable.field2, distinct=db.mytable.field2)near "ON": syntax
error 4postgresdb(db.mytable.id>0).select( orderby=db.mytable.field2,
groupby=db.mytable.field2)column "mytable.id" must appear in the GROUP BY
clause or be used in an aggregate function LINE 1: SELECT mytable.id,
mytable.field1, mytable.field2 FROM myta... ^
5postgresdb(db.mytable.id>0).select(
orderby=db.mytable.field2, distinct=db.mytable.field2)mytable.id
mytable.field1mytable.field21Ax3Cy
6postgresdb(db.mytable.id>0).select(db.mytable.field2,
orderby=db.mytable.field2, groupby=db.mytable.field2)mytable.field2xy 7
postgresdb(db.mytable.id>0).select(db.mytable.field2,
orderby=db.mytable.field2, distinct=db.mytable.field2)mytable.field2xy
2015-01-03 14:03 GMT+01:00 Massimo Di Pierro <massimo.dipie...@gmail.com>:
> These are not valid DAL expressions:
>
> db((db.mytable.field1>'C') & 1).select()
> db((db.mytable.field1>'C') & (1==1)).select()
> db((db.mytable.field1>'C') & 'True').select()
> db((db.mytable.field1>'C') & 'TRUE').select()
>
> the argument of db(....) must be a query or a logical expression comprised
> of queries. 1, True, 'True', 'TRUE' are not queries. The fact that
> occasionally you do not get an error is purely accidental. You should not
> use these expressions. Nowhere in the manual we say this is allowed. Future
> versions of web2py may check (and should) and will block them.
>
> In the other case the problem is that the query should be:
>
> db(db.mytable.id>0).select(db.mytable.field2, orderby=db.mytable.field2,
> groupby=db.mytable.field2)
>
> and it should work in both cases.
>
> If you do not specify that you are selecting only field2, they you are
> selecting all fields and the query becomes ambiguous (which id should be
> used for the grouped records?). Sqlite does not care and returns a random
> one. postgresql checks and complains. Apparently this was solved in SQL3
> but most engines do not follow SQL3.
>
>
> http://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function
>
> The standard SQL distinct syntax (in DAL:
> db(...).select(...,distinct=True)) does not solve this problem. So the
> postgresql folks introduced DISTINCT ON (in DAL: db(...).select(...,
> distinct=field)) but this is a postgresql specific API.
>
>
> http://www.postgresqlforbeginners.com/2010/11/sql-distinct-distinct-on-and-all.html
>
> Massimo
>
>
>
> On Saturday, 3 January 2015 04:42:14 UTC-6, mweissen wrote:
>>
>> In the last hour I have written a small application which tests (1) the
>> boolean expressions and (2) distinct/groupby. You can find this app as
>> attach file.
>>
>> (1) sqlite allows True or 1 or '1' as boolean expression, postgres works
>> only with "TRUE" or 'TRUE'
>>
>> (2) sqlite does not understand distinct and postgres need some complex
>> orderby.
>>
>>
>> Testtables Datebase Command Result sqlitedb((db.mytable.field1>'C') &
>> (1==1)).select()mytable.idmytable.field1mytable.field24Dy5Ey
>> sqlitedb((db.mytable.field1>'C')
>> & True).select()mytable.idmytable.field1mytable.field24Dy5Ey
>> sqlitedb((db.mytable.field1>'C')
>> & 1).select()mytable.idmytable.field1mytable.field24Dy5Ey
>> sqlitedb((db.mytable.field1>'C')
>> & '1').select()mytable.idmytable.field1mytable.field24Dy5Ey
>> sqlitedb((db.mytable.field1>'C')
>> & 'TRUE').select()no such column: TRUE sqlitedb((db.mytable.field1>'C')
>> & "TRUE").select()no such column: TRUE postgresdb((db.mytable.field1>'C')
>> & (1==1)).select()argument of AND must be type boolean, not type integer
>> LINE 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^
>> postgresdb((db.mytable.field1>'C') & True).select()argument of AND must
>> be type boolean, not type integer LINE 1: ...le.field2 FROM mytable WHERE
>> ((mytable.field1 > 'C') AND 1); ^ postgresdb((db.mytable.field1>'C') &
>> 1).select()argument of AND must be type boolean, not type integer LINE
>> 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^
>> postgresdb((db.mytable.field1>'C') & '1').select()argument of AND must
>> be type boolean, not type integer LINE 1: ...le.field2 FROM mytable WHERE
>> ((mytable.field1 > 'C') AND 1); ^ postgresdb((db.mytable.field1>'C') &
>> 'TRUE').select()mytable.idmytable.field1mytable.field24Dy5Ey
>> postgresdb((db.mytable.field1>'C')
>> & "TRUE").select()mytable.idmytable.field1mytable.field24Dy5Ey sqlitedb(
>> db.mytable.id>0).select(orderby=db.mytable.field2,
>> groupby=db.mytable.field2)mytable.idmytable.field1mytable.field22Bx5Ey
>> sqlitedb(db.mytable.id>0).select(orderby=db.mytable.field2,
>> distinct=db.mytable.field2)near "ON": syntax error postgresdb(
>> db.mytable.id>0).select(orderby=db.mytable.field2,
>> groupby=db.mytable.field2)column "mytable.id" must appear in the GROUP
>> BY clause or be used in an aggregate function LINE 1: SELECT mytable.id,
>> mytable.field1, mytable.field2 FROM myta... ^ postgresdb(db.mytable.id
>> >0).select(orderby=db.mytable.field2, distinct=db.mytable.field2)
>> mytable.idmytable.field1mytable.field21Ax3Cy
>>
>>
>> 2015-01-03 10:47 GMT+01:00 Marin Pranjić <marin.pran...@gmail.com>:
>>
>>> The latter one works only with postgres:
>>> https://groups.google.com/forum/#!topic/web2py/1_DHUrrg6O8
>>>
>>> What's the error message?
>>>
>>> On Sat, Jan 3, 2015 at 5:30 AM, Massimo Di Pierro <
>>> massimo.dipie...@gmail.com> wrote:
>>>
>>>>
>>>>
>>>> On Friday, 2 January 2015 16:03:16 UTC-6, mweissen wrote:
>>>>>
>>>>> I have tried to convert an application from SQLite to Postgresql and I
>>>>> have found two differences:
>>>>>
>>>>> (1):
>>>>> q = some query like db.table.field<3
>>>>> b = some boolean like a<3
>>>>> db(q & b)
>>>>>
>>>>> SQLite needs to convert b to '0' or '1', Postgresql to 'FALSE' or
>>>>> 'TRUE'
>>>>>
>>>>
>>>> yes but web2py does it for you. you should not need to change your code.
>>>>
>>>>
>>>>>
>>>>> (2): orderby generates some difficult error messages. I have found the
>>>>> following solution
>>>>> SQLite
>>>>> db(db.table.field==x).select(orderby=db.table.field2,
>>>>> groupby=db.table.field2)
>>>>>
>>>>> Postgresql:
>>>>> db(db.table.field==x).select(orderby=db.table.field2,
>>>>> distinct=db.table.field2)
>>>>>
>>>>
>>>> Are you sure? I think they both work on both databases. The problems
>>>> arise when you groupby some fields and records are not sorted by those
>>>> fields. Some engines allow it and some don't.
>>>>
>>>>
>>>>> Does somebody has similar experiences?
>>>>>
>>>>> Regards, Martin
>>>>>
>>>>>
>>>>> 2015-01-02 16:29 GMT+01:00 Michele Comitini <
>>>>> michele.comit...@gmail.com>:
>>>>>
>>>>>> SQLite is by design an excellent embedded database, better suited
>>>>>> for single user implementation and targeted at small datasets.
>>>>>> For larger datasets there is no reason not to use PostgreSQL, it's
>>>>>> free, well supported by web2py,
>>>>>> handles very large datasets and it's easy to setup.
>>>>>>
>>>>>> Migration from SQLite to any other db supported by web2py is very
>>>>>> easy and explained on the web2py book
>>>>>>
>>>>>> 2015-01-02 6:49 GMT+01:00 Eric Taw <tawe...@gmail.com>:
>>>>>>
>>>>>>> From reading a lot of web2py tutorials and guides, they always say
>>>>>>> SQLite is good for small implementations of databases, whereas other SQL
>>>>>>> flavors are better suited for larger uses. Is there any reason why? Can
>>>>>>> I
>>>>>>> still use SQLite if I have a lot of information to store?
>>>>>>> --
>>>>>>> Resources:
>>>>>>> - http://web2py.com
>>>>>>> - http://web2py.com/book (Documentation)
>>>>>>> - http://github.com/web2py/web2py (Source code)
>>>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>>>>>>> ---
>>>>>>> You received this message because you are subscribed to the Google
>>>>>>> Groups "web2py-users" group.
>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>> send an email to web2py+unsubscr...@googlegroups.com.
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> Resources:
>>>>>> - http://web2py.com
>>>>>> - http://web2py.com/book (Documentation)
>>>>>> - http://github.com/web2py/web2py (Source code)
>>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>>>>>> ---
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "web2py-users" group.
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to web2py+unsubscr...@googlegroups.com.
>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>> Resources:
>>>> - http://web2py.com
>>>> - http://web2py.com/book (Documentation)
>>>> - http://github.com/web2py/web2py (Source code)
>>>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>>>> ---
>>>> You received this message because you are subscribed to the Google
>>>> Groups "web2py-users" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to web2py+unsubscr...@googlegroups.com.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
