[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-05-11 Thread 'Awe' via web2py-users 
Hello Leonel,
opened this Issue on Mar 14,
still no one assigned?
Regards,
Andreas

Am Freitag, 9. März 2018 16:55:12 UTC+1 schrieb Leonel Câmara:
>
> This is definitely a bug, the solution doesn't seem easy, The problem is 
> that it should not apply the common filters on the alias used on the left 
> join again as they are already applied on the left join ON query itself. 
>   
> Please file an issue here:
> https://github.com/web2py/pydal/issues
>

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-19 Thread 'Awe' via web2py-users 
OK, issue filed on: 2018-03-14
Andreas

Am Montag, 12. März 2018 09:14:22 UTC+1 schrieb Awe:
>
> Hello Leonel,
> thanks for analyzing. Will open issue today.
> Andreas
>
> Am Freitag, 9. März 2018 16:55:12 UTC+1 schrieb Leonel Câmara:
>>
>> This is definitely a bug, the solution doesn't seem easy, The problem is 
>> that it should not apply the common filters on the alias used on the left 
>> join again as they are already applied on the left join ON query itself. 
>>   
>> Please file an issue here:
>> https://github.com/web2py/pydal/issues
>>
>

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-12 Thread 'Awe' via web2py-users 
Hello Leonel,
thanks for analyzing. Will open issue today.
Andreas

Am Freitag, 9. März 2018 16:55:12 UTC+1 schrieb Leonel Câmara:
>
> This is definitely a bug, the solution doesn't seem easy, The problem is 
> that it should not apply the common filters on the alias used on the left 
> join again as they are already applied on the left join ON query itself. 
>   
> Please file an issue here:
> https://github.com/web2py/pydal/issues
>

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-09 Thread Leonel Câmara 
This is definitely a bug, the solution doesn't seem easy, The problem is 
that it should not apply the common filters on the alias used on the left 
join again as they are already applied on the left join ON query itself. 
  
Please file an issue here:
https://github.com/web2py/pydal/issues

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-09 Thread 'Awe' via web2py-users 
Hello Anthony, Richard:

Here is a link to download a demo app: 
https://c.gmx.net/@316857413586131071/y6xMeU53Ql2MtJz-wr0-CQ
Just install it, on the main page click the link.
I just did the input written in the very first post.
Additionally I attached a screenshot showing my result.

Many thanks to You.
Andreas

Am Donnerstag, 8. März 2018 16:55:06 UTC+1 schrieb Anthony:
>
> On Thursday, March 8, 2018 at 10:28:38 AM UTC-5, Leonel Câmara wrote:
>>
>> Record versioning uses a common filter.
>>
>> Common filters do not work if you do not provide a query which is what 
>> happens when you do:
>>
>> db().select(db.test.ALL)
>>
>> You need to do:
>>
>> db(db.test.id > 0).select(db.test.ALL)
>>
>
> I believe the query in question is:
>
> query = (test2.type_marker=='object') | (test2.type_marker==None) & (test1
> .type_marker=='person')
> rows = current.db(query).select(test1.title, test2.title, left=test2.on(
> test1.id==test2.granny))
>
> So, there is a query being passed to db().
>
> Anthony
>

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-08 Thread Anthony 
On Thursday, March 8, 2018 at 10:28:38 AM UTC-5, Leonel Câmara wrote:
>
> Record versioning uses a common filter.
>
> Common filters do not work if you do not provide a query which is what 
> happens when you do:
>
> db().select(db.test.ALL)
>
> You need to do:
>
> db(db.test.id > 0).select(db.test.ALL)
>

I believe the query in question is:

query = (test2.type_marker=='object') | (test2.type_marker==None) & (test1.
type_marker=='person')
rows = current.db(query).select(test1.title, test2.title, left=test2.on(
test1.id==test2.granny))

So, there is a query being passed to db().

Anthony

-- 
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.

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-08 Thread Leonel Câmara 
Record versioning uses a common filter.

Common filters do not work if you do not provide a query which is what 
happens when you do:

db().select(db.test.ALL)

You need to do:

db(db.test.id > 0).select(db.test.ALL)

And then it will work. Note that the calling the DAL without a query 
shortcut is officially deprecated as it causes many problems like this one 
that unnecessarily complicate the DAL's code to fix.

-- 
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.

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-08 Thread 'Awe' via web2py-users 
You wrote:
In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) & 
(test1.type_marker=='person')
   ...: 

In [5]: db().select(db.test.ALL)
Out[5]: 

So in [5] you did not use the query definition, its db() 
AND the result is 3 Rows, 
it should be 2!
Thats why I asked for the query you will use to get exactly this result:
test1.titletest2.title
PeterNone
PaulLaptop

I haven't seen any query in your output with this result.
Andreas



Am Mittwoch, 7. März 2018 20:08:05 UTC+1 schrieb Richard:
>
> That why I think you wrongly initialize you db in the first place while 
> onboarding the record versioning feature...
>
> Please try on your side to create new app drop the model and fixture I 
> include in one of my last email and start web2py shell and launch the 
> various commands of yours...
>
> You should get the same output number of record...
>
> If you don't it might happen that there was a bug in 2.15.4 web2py version 
> / pyDAL...
>
> Try new version...
>
> On Wed, Mar 7, 2018 at 2:05 PM, Richard Vézina  > wrote:
>
>> In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) 
>> & (test1.type_marker=='person')
>>...: 
>>
>> In [5]: db().select(db.test.ALL)
>> Out[5]: 
>>
>> I did there...
>>
>> Same result...
>>
>> That my point...
>>
>> On Wed, Mar 7, 2018 at 1:47 PM, 'Awe' via web2py-users <
>> web...@googlegroups.com > wrote:
>>
>>> Hello Richard,
>>> you are right, the use case is parent child relation.
>>> All I want to point out is if you use this query:
>>> "query = (test2.type_marker=='object') | (test2.type_marker==None) & 
>>> (test1.type_marker=='person')"
>>> you do not get the same result with record versioning enabled or 
>>> disabled.
>>>
>>> Why do I use this query definition because I want to retrieve exactly 
>>> this result:
>>> test1.titletest2.title
>>> PeterNone
>>> PaulLaptop
>>>
>>> If you use this query instead:
>>> (test2.type_marker=='object') & (test1.type_marker=='person')
>>> you do not get the result I need.
>>>
>>> The " | (test2.type_marker==None) " is necessary, due to parent/child 
>>> relationship.
>>>
>>> Please try exactly the query mentioned using versioning enabled/disabled 
>>> and see what happens.
>>>
>>> Many thanks for your patience.
>>>
>>> Am Mittwoch, 7. März 2018 19:34:41 UTC+1 schrieb Richard:

 I don't get it, I don't understand what is the issue...

 I just demonstrate that with and without record versioning DAL returns 
 the same set of result which what it should be doing no??

 So my understanding of the issue is that you don't get the same query 
 output when record versioning is on and when it off which to me should is 
 the issue you point us... I migth not understand the exact problem you 
 have.

 But to me this "query = (test2.type_marker=='object') | 
 (test2.type_marker==None) & (test1.type_marker=='person')"

 or this "query = (test2.type_marker=='object') & 
 (test1.type_marker=='person')" with the set of inputs you have will lead 
 to 
 the same output as there is not type_marker NULL value...

 But I guess I miss something with related to what you try to achieve 
 with this aliasing thing that you do...

 Also the use case is not obvious for me as why are you doing these 
 aliases... I guess to retrieve some sort of genealogy/record parent-child 
 relation...

 Richard


 On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users <
 web...@googlegroups.com> wrote:

> Hello Richard,
> have read your post 3 times, but I can't find the right query result.
> Could you tell me where to find in your test the query which leads to:
> test1.titletest2.title
> PeterNone
> PaulLaptop
>
> Thank you.
>
>
>
>
>
> Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:
>
>> Here some tests : 
>>
>> *WITH record versioning*
>>
>> In [1]: db(db.test.id < 0).select()
>> Out[1]: 
>>
>> In [2]: db(db.test.id > 0).select()
>> Out[2]: 
>>
>> In [3]: test1 = db.test.with_alias('test1')
>>
>> In [4]: test2 = db.test.with_alias('test2')
>>
>> In [5]: query = (test2.type_marker=='object') | 
>> (test2.type_marker==None) & (test1.type_marker=='person')
>>
>> In [6]: db().select(current.db.test.ALL)
>>
>> ---
>> NameError Traceback (most recent call 
>> last)
>> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
>>  
>> in ()
>> > 1 db().select(current.db.test.ALL)
>>
>> NameError: name 'current' is not defined
>>
>> In [7]: db().select(db.test.ALL)
>> Out[7]: 
>>

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread Richard Vézina 
That why I think you wrongly initialize you db in the first place while
onboarding the record versioning feature...

Please try on your side to create new app drop the model and fixture I
include in one of my last email and start web2py shell and launch the
various commands of yours...

You should get the same output number of record...

If you don't it might happen that there was a bug in 2.15.4 web2py version
/ pyDAL...

Try new version...

On Wed, Mar 7, 2018 at 2:05 PM, Richard Vézina 
wrote:

> In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None)
> & (test1.type_marker=='person')
>...:
>
> In [5]: db().select(db.test.ALL)
> Out[5]: 
>
> I did there...
>
> Same result...
>
> That my point...
>
> On Wed, Mar 7, 2018 at 1:47 PM, 'Awe' via web2py-users <
> web2py@googlegroups.com> wrote:
>
>> Hello Richard,
>> you are right, the use case is parent child relation.
>> All I want to point out is if you use this query:
>> "query = (test2.type_marker=='object') | (test2.type_marker==None) &
>> (test1.type_marker=='person')"
>> you do not get the same result with record versioning enabled or disabled.
>>
>> Why do I use this query definition because I want to retrieve exactly
>> this result:
>> test1.titletest2.title
>> PeterNone
>> PaulLaptop
>>
>> If you use this query instead:
>> (test2.type_marker=='object') & (test1.type_marker=='person')
>> you do not get the result I need.
>>
>> The " | (test2.type_marker==None) " is necessary, due to parent/child
>> relationship.
>>
>> Please try exactly the query mentioned using versioning enabled/disabled
>> and see what happens.
>>
>> Many thanks for your patience.
>>
>> Am Mittwoch, 7. März 2018 19:34:41 UTC+1 schrieb Richard:
>>>
>>> I don't get it, I don't understand what is the issue...
>>>
>>> I just demonstrate that with and without record versioning DAL returns
>>> the same set of result which what it should be doing no??
>>>
>>> So my understanding of the issue is that you don't get the same query
>>> output when record versioning is on and when it off which to me should is
>>> the issue you point us... I migth not understand the exact problem you have.
>>>
>>> But to me this "query = (test2.type_marker=='object') |
>>> (test2.type_marker==None) & (test1.type_marker=='person')"
>>>
>>> or this "query = (test2.type_marker=='object') &
>>> (test1.type_marker=='person')" with the set of inputs you have will lead to
>>> the same output as there is not type_marker NULL value...
>>>
>>> But I guess I miss something with related to what you try to achieve
>>> with this aliasing thing that you do...
>>>
>>> Also the use case is not obvious for me as why are you doing these
>>> aliases... I guess to retrieve some sort of genealogy/record parent-child
>>> relation...
>>>
>>> Richard
>>>
>>>
>>> On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users <
>>> web...@googlegroups.com> wrote:
>>>
 Hello Richard,
 have read your post 3 times, but I can't find the right query result.
 Could you tell me where to find in your test the query which leads to:
 test1.titletest2.title
 PeterNone
 PaulLaptop

 Thank you.





 Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:

> Here some tests :
>
> *WITH record versioning*
>
> In [1]: db(db.test.id < 0).select()
> Out[1]: 
>
> In [2]: db(db.test.id > 0).select()
> Out[2]: 
>
> In [3]: test1 = db.test.with_alias('test1')
>
> In [4]: test2 = db.test.with_alias('test2')
>
> In [5]: query = (test2.type_marker=='object') |
> (test2.type_marker==None) & (test1.type_marker=='person')
>
> In [6]: db().select(current.db.test.ALL)
> 
> ---
> NameError Traceback (most recent call
> last)
> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
> in ()
> > 1 db().select(current.db.test.ALL)
>
> NameError: name 'current' is not defined
>
> In [7]: db().select(db.test.ALL)
> Out[7]: 
>
> In [8]: query = (test2.type_marker=='object') &
> (test1.type_marker=='person')
>
> In [9]: db().select(db.test.ALL)
> Out[9]: 
>
> In [10]: db(query).select(test1.title, test2.title, left=test2.on(
> test1.id==test2.granny))
> Out[10]: 
>
> In [11]: db(query)._select(test1.title, test2.title, left=test2.on(
> test1.id==test2.granny))
> Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS
> "test1" LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny")
> AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\')))
> WHERE "test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
> \'person\')) AND (("test1"."is_active"

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread Richard Vézina 
In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) &
(test1.type_marker=='person')
   ...:

In [5]: db().select(db.test.ALL)
Out[5]: 

I did there...

Same result...

That my point...

On Wed, Mar 7, 2018 at 1:47 PM, 'Awe' via web2py-users <
web2py@googlegroups.com> wrote:

> Hello Richard,
> you are right, the use case is parent child relation.
> All I want to point out is if you use this query:
> "query = (test2.type_marker=='object') | (test2.type_marker==None) &
> (test1.type_marker=='person')"
> you do not get the same result with record versioning enabled or disabled.
>
> Why do I use this query definition because I want to retrieve exactly this
> result:
> test1.titletest2.title
> PeterNone
> PaulLaptop
>
> If you use this query instead:
> (test2.type_marker=='object') & (test1.type_marker=='person')
> you do not get the result I need.
>
> The " | (test2.type_marker==None) " is necessary, due to parent/child
> relationship.
>
> Please try exactly the query mentioned using versioning enabled/disabled
> and see what happens.
>
> Many thanks for your patience.
>
> Am Mittwoch, 7. März 2018 19:34:41 UTC+1 schrieb Richard:
>>
>> I don't get it, I don't understand what is the issue...
>>
>> I just demonstrate that with and without record versioning DAL returns
>> the same set of result which what it should be doing no??
>>
>> So my understanding of the issue is that you don't get the same query
>> output when record versioning is on and when it off which to me should is
>> the issue you point us... I migth not understand the exact problem you have.
>>
>> But to me this "query = (test2.type_marker=='object') |
>> (test2.type_marker==None) & (test1.type_marker=='person')"
>>
>> or this "query = (test2.type_marker=='object') &
>> (test1.type_marker=='person')" with the set of inputs you have will lead to
>> the same output as there is not type_marker NULL value...
>>
>> But I guess I miss something with related to what you try to achieve with
>> this aliasing thing that you do...
>>
>> Also the use case is not obvious for me as why are you doing these
>> aliases... I guess to retrieve some sort of genealogy/record parent-child
>> relation...
>>
>> Richard
>>
>>
>> On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users <
>> web...@googlegroups.com> wrote:
>>
>>> Hello Richard,
>>> have read your post 3 times, but I can't find the right query result.
>>> Could you tell me where to find in your test the query which leads to:
>>> test1.titletest2.title
>>> PeterNone
>>> PaulLaptop
>>>
>>> Thank you.
>>>
>>>
>>>
>>>
>>>
>>> Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:
>>>
 Here some tests :

 *WITH record versioning*

 In [1]: db(db.test.id < 0).select()
 Out[1]: 

 In [2]: db(db.test.id > 0).select()
 Out[2]: 

 In [3]: test1 = db.test.with_alias('test1')

 In [4]: test2 = db.test.with_alias('test2')

 In [5]: query = (test2.type_marker=='object') |
 (test2.type_marker==None) & (test1.type_marker=='person')

 In [6]: db().select(current.db.test.ALL)
 
 ---
 NameError Traceback (most recent call
 last)
 /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
 in ()
 > 1 db().select(current.db.test.ALL)

 NameError: name 'current' is not defined

 In [7]: db().select(db.test.ALL)
 Out[7]: 

 In [8]: query = (test2.type_marker=='object') &
 (test1.type_marker=='person')

 In [9]: db().select(db.test.ALL)
 Out[9]: 

 In [10]: db(query).select(test1.title, test2.title, left=test2.on(
 test1.id==test2.granny))
 Out[10]: 

 In [11]: db(query)._select(test1.title, test2.title, left=test2.on(
 test1.id==test2.granny))
 Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS
 "test1" LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny")
 AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\')))
 WHERE "test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
 \'person\')) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
 \'T\'))) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
 \'T\')));'

 In [12]: print db().select(db.test.ALL)
 test.id,test.title,test.granny,test.type_marker,test.is_
 active,test.created_on,test.created_by,test.modified_on,
 test.modified_by
 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07
 11:10:08,
 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07
 11:10:08,
 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07
 11:10:08,


 *WITHOUT record versioning*

 In [1]: db(db.test.id > 0).select()
 Out[1]: 

 In [2]:  test1 =

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread 'Awe' via web2py-users 
Hello Richard,
you are right, the use case is parent child relation.
All I want to point out is if you use this query:
"query = (test2.type_marker=='object') | (test2.type_marker==None) & 
(test1.type_marker=='person')"
you do not get the same result with record versioning enabled or disabled.

Why do I use this query definition because I want to retrieve exactly this 
result:
test1.titletest2.title
PeterNone
PaulLaptop

If you use this query instead:
(test2.type_marker=='object') & (test1.type_marker=='person')
you do not get the result I need.

The " | (test2.type_marker==None) " is necessary, due to parent/child 
relationship.

Please try exactly the query mentioned using versioning enabled/disabled 
and see what happens.

Many thanks for your patience.

Am Mittwoch, 7. März 2018 19:34:41 UTC+1 schrieb Richard:
>
> I don't get it, I don't understand what is the issue...
>
> I just demonstrate that with and without record versioning DAL returns the 
> same set of result which what it should be doing no??
>
> So my understanding of the issue is that you don't get the same query 
> output when record versioning is on and when it off which to me should is 
> the issue you point us... I migth not understand the exact problem you have.
>
> But to me this "query = (test2.type_marker=='object') | 
> (test2.type_marker==None) & (test1.type_marker=='person')"
>
> or this "query = (test2.type_marker=='object') & 
> (test1.type_marker=='person')" with the set of inputs you have will lead to 
> the same output as there is not type_marker NULL value...
>
> But I guess I miss something with related to what you try to achieve with 
> this aliasing thing that you do...
>
> Also the use case is not obvious for me as why are you doing these 
> aliases... I guess to retrieve some sort of genealogy/record parent-child 
> relation...
>
> Richard
>
>
> On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users <
> web...@googlegroups.com > wrote:
>
>> Hello Richard,
>> have read your post 3 times, but I can't find the right query result.
>> Could you tell me where to find in your test the query which leads to:
>> test1.titletest2.title
>> PeterNone
>> PaulLaptop
>>
>> Thank you.
>>
>>
>>
>>
>>
>> Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:
>>
>>> Here some tests : 
>>>
>>> *WITH record versioning*
>>>
>>> In [1]: db(db.test.id < 0).select()
>>> Out[1]: 
>>>
>>> In [2]: db(db.test.id > 0).select()
>>> Out[2]: 
>>>
>>> In [3]: test1 = db.test.with_alias('test1')
>>>
>>> In [4]: test2 = db.test.with_alias('test2')
>>>
>>> In [5]: query = (test2.type_marker=='object') | 
>>> (test2.type_marker==None) & (test1.type_marker=='person')
>>>
>>> In [6]: db().select(current.db.test.ALL)
>>>
>>> ---
>>> NameError Traceback (most recent call 
>>> last)
>>> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
>>>  
>>> in ()
>>> > 1 db().select(current.db.test.ALL)
>>>
>>> NameError: name 'current' is not defined
>>>
>>> In [7]: db().select(db.test.ALL)
>>> Out[7]: 
>>>
>>> In [8]: query = (test2.type_marker=='object') & 
>>> (test1.type_marker=='person')
>>>
>>> In [9]: db().select(db.test.ALL)
>>> Out[9]: 
>>>
>>> In [10]: db(query).select(test1.title, test2.title, left=test2.on(
>>> test1.id==test2.granny))
>>> Out[10]: 
>>>
>>> In [11]: db(query)._select(test1.title, test2.title, left=test2.on(
>>> test1.id==test2.granny))
>>> Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" 
>>> LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND 
>>> (("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\'))) WHERE 
>>> "test2"."type_marker" = \'object\') AND ("test1"."type_marker" = 
>>> \'person\')) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = 
>>> \'T\'))) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = 
>>> \'T\')));'
>>>
>>> In [12]: print db().select(db.test.ALL)
>>> test.id
>>> ,test.title,test.granny,test.type_marker,test.is_active,test.created_on,test.created_by,test.modified_on,test.modified_by
>>> 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07 
>>> 11:10:08,
>>> 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07 
>>> 11:10:08,
>>> 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07 
>>> 11:10:08,
>>>
>>>
>>> *WITHOUT record versioning*
>>>
>>> In [1]: db(db.test.id > 0).select()
>>> Out[1]: 
>>>
>>> In [2]:  test1 = db.test.with_alias('test1')
>>>
>>> In [3]: test2 = db.test.with_alias('test2')
>>>
>>> In [4]: query = (test2.type_marker=='object') | 
>>> (test2.type_marker==None) & (test1.type_marker=='person')
>>>...: 
>>>
>>> In [5]: db().select(db.test.ALL)
>>> Out[5]: 
>>>
>>> In [6]: query = (test2.type_marker=='object') & 
>>> (test1.type_marker=='person')
>>>
>>> In [7]: db().select(db.test.ALL)
>>> Out[7]: 
>>>
>>> In [8]:

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread Richard Vézina 
I don't get it, I don't understand what is the issue...

I just demonstrate that with and without record versioning DAL returns the
same set of result which what it should be doing no??

So my understanding of the issue is that you don't get the same query
output when record versioning is on and when it off which to me should is
the issue you point us... I migth not understand the exact problem you have.

But to me this "query = (test2.type_marker=='object') |
(test2.type_marker==None) & (test1.type_marker=='person')"

or this "query = (test2.type_marker=='object') &
(test1.type_marker=='person')" with the set of inputs you have will lead to
the same output as there is not type_marker NULL value...

But I guess I miss something with related to what you try to achieve with
this aliasing thing that you do...

Also the use case is not obvious for me as why are you doing these
aliases... I guess to retrieve some sort of genealogy/record parent-child
relation...

Richard


On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users <
web2py@googlegroups.com> wrote:

> Hello Richard,
> have read your post 3 times, but I can't find the right query result.
> Could you tell me where to find in your test the query which leads to:
> test1.titletest2.title
> PeterNone
> PaulLaptop
>
> Thank you.
>
>
>
>
>
> Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:
>
>> Here some tests :
>>
>> *WITH record versioning*
>>
>> In [1]: db(db.test.id < 0).select()
>> Out[1]: 
>>
>> In [2]: db(db.test.id > 0).select()
>> Out[2]: 
>>
>> In [3]: test1 = db.test.with_alias('test1')
>>
>> In [4]: test2 = db.test.with_alias('test2')
>>
>> In [5]: query = (test2.type_marker=='object') | (test2.type_marker==None)
>> & (test1.type_marker=='person')
>>
>> In [6]: db().select(current.db.test.ALL)
>> 
>> ---
>> NameError Traceback (most recent call
>> last)
>> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
>> in ()
>> > 1 db().select(current.db.test.ALL)
>>
>> NameError: name 'current' is not defined
>>
>> In [7]: db().select(db.test.ALL)
>> Out[7]: 
>>
>> In [8]: query = (test2.type_marker=='object') &
>> (test1.type_marker=='person')
>>
>> In [9]: db().select(db.test.ALL)
>> Out[9]: 
>>
>> In [10]: db(query).select(test1.title, test2.title, left=test2.on(
>> test1.id==test2.granny))
>> Out[10]: 
>>
>> In [11]: db(query)._select(test1.title, test2.title, left=test2.on(
>> test1.id==test2.granny))
>> Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1"
>> LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND
>> (("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\'))) WHERE
>> "test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
>> \'person\')) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
>> \'T\'))) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
>> \'T\')));'
>>
>> In [12]: print db().select(db.test.ALL)
>> test.id,test.title,test.granny,test.type_marker,test.is_
>> active,test.created_on,test.created_by,test.modified_on,test.modified_by
>> 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>> 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>> 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>>
>>
>> *WITHOUT record versioning*
>>
>> In [1]: db(db.test.id > 0).select()
>> Out[1]: 
>>
>> In [2]:  test1 = db.test.with_alias('test1')
>>
>> In [3]: test2 = db.test.with_alias('test2')
>>
>> In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None)
>> & (test1.type_marker=='person')
>>...:
>>
>> In [5]: db().select(db.test.ALL)
>> Out[5]: 
>>
>> In [6]: query = (test2.type_marker=='object') &
>> (test1.type_marker=='person')
>>
>> In [7]: db().select(db.test.ALL)
>> Out[7]: 
>>
>> In [8]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
>> ==test2.granny))
>> Out[8]: 
>>
>> In [9]: db(query)._select(test1.title, test2.title, left=test2.on(
>> test1.id==test2.granny))
>> Out[9]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1"
>> LEFT JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE
>> (("test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
>> \'person\'));'
>>
>> In [10]: print db().select(db.test.ALL)
>> test.id,test.title,test.granny,test.type_marker,test.is_
>> active,test.created_on,test.created_by,test.modified_on,test.modified_by
>> 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>> 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>> 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07
>> 11:10:08,
>>
>>
>>
>>
>> *I have this in db.py of a newly created app with web2py trunk/master :*
>>
>> ```python
>> db.define_table('test',
>>Field('title', 'string'),
>>Field('granny',

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread 'Awe' via web2py-users 
Hello Richard,
have read your post 3 times, but I can't find the right query result.
Could you tell me where to find in your test the query which leads to:
test1.titletest2.title
PeterNone
PaulLaptop

Thank you.




Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard:
>
> Here some tests : 
>
> *WITH record versioning*
>
> In [1]: db(db.test.id < 0).select()
> Out[1]: 
>
> In [2]: db(db.test.id > 0).select()
> Out[2]: 
>
> In [3]: test1 = db.test.with_alias('test1')
>
> In [4]: test2 = db.test.with_alias('test2')
>
> In [5]: query = (test2.type_marker=='object') | (test2.type_marker==None) 
> & (test1.type_marker=='person')
>
> In [6]: db().select(current.db.test.ALL)
> ---
> NameError Traceback (most recent call last)
> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py 
> in ()
> > 1 db().select(current.db.test.ALL)
>
> NameError: name 'current' is not defined
>
> In [7]: db().select(db.test.ALL)
> Out[7]: 
>
> In [8]: query = (test2.type_marker=='object') & 
> (test1.type_marker=='person')
>
> In [9]: db().select(db.test.ALL)
> Out[9]: 
>
> In [10]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
> ==test2.granny))
> Out[10]: 
>
> In [11]: db(query)._select(test1.title, test2.title, left=test2.on(
> test1.id==test2.granny))
> Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" 
> LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND 
> (("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\'))) WHERE 
> "test2"."type_marker" = \'object\') AND ("test1"."type_marker" = 
> \'person\')) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = 
> \'T\'))) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" = 
> \'T\')));'
>
> In [12]: print db().select(db.test.ALL)
> test.id
> ,test.title,test.granny,test.type_marker,test.is_active,test.created_on,test.created_by,test.modified_on,test.modified_by
> 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
> 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
> 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
>
>
> *WITHOUT record versioning*
>
> In [1]: db(db.test.id > 0).select()
> Out[1]: 
>
> In [2]:  test1 = db.test.with_alias('test1')
>
> In [3]: test2 = db.test.with_alias('test2')
>
> In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) 
> & (test1.type_marker=='person')
>...: 
>
> In [5]: db().select(db.test.ALL)
> Out[5]: 
>
> In [6]: query = (test2.type_marker=='object') & 
> (test1.type_marker=='person')
>
> In [7]: db().select(db.test.ALL)
> Out[7]: 
>
> In [8]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
> ==test2.granny))
> Out[8]: 
>
> In [9]: db(query)._select(test1.title, test2.title, left=test2.on(test1.id
> ==test2.granny))
> Out[9]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" 
> LEFT JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE 
> (("test2"."type_marker" = \'object\') AND ("test1"."type_marker" = 
> \'person\'));'
>
> In [10]: print db().select(db.test.ALL)
> test.id
> ,test.title,test.granny,test.type_marker,test.is_active,test.created_on,test.created_by,test.modified_on,test.modified_by
> 1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
> 2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
> 3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07 
> 11:10:08,
>
>
>
>
> *I have this in db.py of a newly created app with web2py trunk/master :*
>
> ```python
> db.define_table('test',
>Field('title', 'string'),
>Field('granny', 'reference test'),  # convenience - 
> topmost
>Field('type_marker', 'string'),
>auth.signature,
>)
>
> db.test._enable_record_versioning(archive_db=db,
>   archive_name='test_archive',
>   current_record='current_record',
>   is_active='is_active'
>   )
>
> if db(db.test.id > 0).count() == 0:
> db.test.insert(title='Peter', granny=None, type_marker='person')
> db.test.insert(title='Paul', granny=None, type_marker='person')
> db.test.insert(title='Laptop', granny=2, type_marker='object')
> ```
> You can start the web2py shell like this :
>
> python web2py.py -a 'DUMMYPWD' -i 127.0.0.1 -p 8001 -S 
> test_versioning_issue -M
>
>
>
> I don't notice any discrepency between both result, I mean I recover 3 
> records in both case... It makes sens that the raw query change to make 
> sure it select only active records...
>
> It mays happen that you experiment an issue with the web2py version that 
> you use 2.15.4...
>
> You may consider trying the above with a freshly

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread Richard Vézina 
Here some tests :

*WITH record versioning*

In [1]: db(db.test.id < 0).select()
Out[1]: 

In [2]: db(db.test.id > 0).select()
Out[2]: 

In [3]: test1 = db.test.with_alias('test1')

In [4]: test2 = db.test.with_alias('test2')

In [5]: query = (test2.type_marker=='object') | (test2.type_marker==None) &
(test1.type_marker=='person')

In [6]: db().select(current.db.test.ALL)
---
NameError Traceback (most recent call last)
/web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py
in ()
> 1 db().select(current.db.test.ALL)

NameError: name 'current' is not defined

In [7]: db().select(db.test.ALL)
Out[7]: 

In [8]: query = (test2.type_marker=='object') &
(test1.type_marker=='person')

In [9]: db().select(db.test.ALL)
Out[9]: 

In [10]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
==test2.granny))
Out[10]: 

In [11]: db(query)._select(test1.title, test2.title, left=test2.on(test1.id
==test2.granny))
Out[11]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1"
LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND
(("test1"."is_active" = \'T\') AND ("test2"."is_active" = \'T\'))) WHERE
"test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
\'person\')) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
\'T\'))) AND (("test1"."is_active" = \'T\') AND ("test2"."is_active" =
\'T\')));'

In [12]: print db().select(db.test.ALL)
test.id
,test.title,test.granny,test.type_marker,test.is_active,test.created_on,test.created_by,test.modified_on,test.modified_by
1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07
11:10:08,
2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07
11:10:08,
3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07 11:10:08,


*WITHOUT record versioning*

In [1]: db(db.test.id > 0).select()
Out[1]: 

In [2]:  test1 = db.test.with_alias('test1')

In [3]: test2 = db.test.with_alias('test2')

In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) &
(test1.type_marker=='person')
   ...:

In [5]: db().select(db.test.ALL)
Out[5]: 

In [6]: query = (test2.type_marker=='object') &
(test1.type_marker=='person')

In [7]: db().select(db.test.ALL)
Out[7]: 

In [8]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
==test2.granny))
Out[8]: 

In [9]: db(query)._select(test1.title, test2.title, left=test2.on(test1.id
==test2.granny))
Out[9]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS "test1"
LEFT JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE
(("test2"."type_marker" = \'object\') AND ("test1"."type_marker" =
\'person\'));'

In [10]: print db().select(db.test.ALL)
test.id
,test.title,test.granny,test.type_marker,test.is_active,test.created_on,test.created_by,test.modified_on,test.modified_by
1,Peter,,person,True,2018-03-07 11:10:08,,2018-03-07
11:10:08,
2,Paul,,person,True,2018-03-07 11:10:08,,2018-03-07
11:10:08,
3,Laptop,2,object,True,2018-03-07 11:10:08,,2018-03-07 11:10:08,




*I have this in db.py of a newly created app with web2py trunk/master :*

```python
db.define_table('test',
   Field('title', 'string'),
   Field('granny', 'reference test'),  # convenience -
topmost
   Field('type_marker', 'string'),
   auth.signature,
   )

db.test._enable_record_versioning(archive_db=db,
  archive_name='test_archive',
  current_record='current_record',
  is_active='is_active'
  )

if db(db.test.id > 0).count() == 0:
db.test.insert(title='Peter', granny=None, type_marker='person')
db.test.insert(title='Paul', granny=None, type_marker='person')
db.test.insert(title='Laptop', granny=2, type_marker='object')
```
You can start the web2py shell like this :

python web2py.py -a 'DUMMYPWD' -i 127.0.0.1 -p 8001 -S
test_versioning_issue -M



I don't notice any discrepency between both result, I mean I recover 3
records in both case... It makes sens that the raw query change to make
sure it select only active records...

It mays happen that you experiment an issue with the web2py version that
you use 2.15.4...

You may consider trying the above with a freshly create dummy app with the
db.py additions above and see by yourself... If you get the same results it
means that your own app may have been wrongly initialized in some way... If
yous till experiment the issue, try with the stable web2py version from the
web2py.com download page...

Please report here your progress...

If you determine that the issue is related to your old version of web2py,
please consider upgrade to the newer stable or wait for the next release
that Massimo's want to push soon.

Thanks

Richard

On Wed, Mar 7, 2018 at 5:35 AM, 'Awe' via web2py-users <

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-07 Thread 'Awe' via web2py-users 
Great, many thanks for testing it.
Andreas

Am Dienstag, 6. März 2018 22:30:59 UTC+1 schrieb Richard:
>
> Can you make a model definition with a fixture for loading the table, I 
> will make some test with trunk and various version to determine if it a 
> regression... Nevermind I just thought you provide that in the first 
> email... Let me have a look at that...
>
> Richard
>
>
>
> On Mon, Mar 5, 2018 at 2:02 PM, 'Awe' via web2py-users <
> web...@googlegroups.com > wrote:
>
>> Hello Richard,
>> it is not a problem of deleted or changed records. As shown in the 
>> example, there is the table and versioning defined. After that 3 records 
>> are inserted and then the query is executed.
>> The defined query:
>> query = (test2.type_marker=='object') | (test2.type_marker==None) & 
>> (test1.type_marker=='person')
>> rows = current.db(query).select(test1.title, test2.title, 
>> left=test2.on(test1.id==test2.granny))
>> does not give the right result.
>> I appreciate your help, maybe the problem gets easier to understand if 
>> you try to test the given example.
>> Many thanks,
>> Andreas
>>
>>
>> Am Montag, 5. März 2018 16:05:26 UTC+1 schrieb Richard:
>>>
>>> Did you set the actual record that have been deleted to is_active = 
>>> False?? Or all the records that haven't be deleted yet to TRUE?? You have 
>>> to go in your backend and do an update there
>>>
>>> UPDATE stored_item
>>> SET is_active = TRUE
>>>   WHERE is_active IS NULL
>>>
>>> If you didn't delete any record yet...
>>>
>>> You can also do it from web2py I guess with this command :
>>>
>>> db(db.stored_item.is_active == None).update(is_active=True)
>>> db.commit()
>>>
>>> Then you both query should return the same thing... Or at least they 
>>> should...
>>>
>>> It important to initialize archive table and parent table properly when 
>>> you add audit trail table to an already existing table containing record.
>>>
>>> I encourage you to read the book about record versioning : 
>>> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Record-versioning
>>>
>>> Richard
>>>
>>> On Sun, Mar 4, 2018 at 12:53 PM, 'Awe' via web2py-users <
>>> web...@googlegroups.com> wrote:
>>>
 Hello Richard, many thanks for analyzing. Everything you wrote is 
 completely right. But I still do not understand the behaviour explained 
 before.
 If you look at the table posted before, all is_acitve Flags are TRUE.
 to get the result I need, I have defined:
 query = (test2.type_marker=='object') | (test2.type_marker==None) & 
 (test1.type_marker=='person')
 rows = current.db(query).select(test1.title, test2.title, 
 left=test2.on(test1.id==test2.granny))

 the raw sql of this query WITHOUT record versioning is fine.
 SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT 
 JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE 
 (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) 
 AND 
 ("test1"."type_marker" = 'person')));
 And the result is the way it suposed to be:

 test1.titletest2.title
 PeterNone
 PaulLaptop

 The result with record versioning enabled is different:
 test1.titletest2.title
 PaulLaptop

 Even when the is_acitve Field is defined like that:

 Field('is_active', 'boolean', writable=False, readable=False, 
 default=True,required=True, requires=IS_NOT_EMPTY()),

 So maybe I miss something? At the moment I still wonder why the identical 
 query comes to different results.


 Best regards, an many thanks for your help!
 Andreas





 Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard:
>
> Hmmm... I think that if you have record versioning activated there 
> shouldn't be any is_active row(s) with NULL value... I mean is_active 
> flag 
> is used to determine if the record in the "parent" table has been deleted 
> or not, since you can truely deleted in case you use record versioning 
> feature as all the previous record stated records contained in the 
> versioning table reference the parent record in the parent table...
>
> And you see in the second example here :
>
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=record+versioning#Record-versioning
>
> That is_active is set to default=True and there is this note :
>
> Notice the hidden boolean field called is_active and defaulting to 
> True.
>
> So if you have activated record versioning feature over an already 
> existing table you need to set is_active to TRUE for all the existing 
> record once you start using the versioning feature and set the is_active 
> to 
> be mandatory...
>
> I guess it would make sens to change seconde book example in

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-06 Thread Richard Vézina 
Can you make a model definition with a fixture for loading the table, I
will make some test with trunk and various version to determine if it a
regression... Nevermind I just thought you provide that in the first
email... Let me have a look at that...

Richard



On Mon, Mar 5, 2018 at 2:02 PM, 'Awe' via web2py-users <
web2py@googlegroups.com> wrote:

> Hello Richard,
> it is not a problem of deleted or changed records. As shown in the
> example, there is the table and versioning defined. After that 3 records
> are inserted and then the query is executed.
> The defined query:
> query = (test2.type_marker=='object') | (test2.type_marker==None) &
> (test1.type_marker=='person')
> rows = current.db(query).select(test1.title, test2.title,
> left=test2.on(test1.id==test2.granny))
> does not give the right result.
> I appreciate your help, maybe the problem gets easier to understand if you
> try to test the given example.
> Many thanks,
> Andreas
>
>
> Am Montag, 5. März 2018 16:05:26 UTC+1 schrieb Richard:
>>
>> Did you set the actual record that have been deleted to is_active =
>> False?? Or all the records that haven't be deleted yet to TRUE?? You have
>> to go in your backend and do an update there
>>
>> UPDATE stored_item
>> SET is_active = TRUE
>>   WHERE is_active IS NULL
>>
>> If you didn't delete any record yet...
>>
>> You can also do it from web2py I guess with this command :
>>
>> db(db.stored_item.is_active == None).update(is_active=True)
>> db.commit()
>>
>> Then you both query should return the same thing... Or at least they
>> should...
>>
>> It important to initialize archive table and parent table properly when
>> you add audit trail table to an already existing table containing record.
>>
>> I encourage you to read the book about record versioning :
>> http://web2py.com/books/default/chapter/29/06/the-database
>> -abstraction-layer#Record-versioning
>>
>> Richard
>>
>> On Sun, Mar 4, 2018 at 12:53 PM, 'Awe' via web2py-users <
>> web...@googlegroups.com> wrote:
>>
>>> Hello Richard, many thanks for analyzing. Everything you wrote is
>>> completely right. But I still do not understand the behaviour explained
>>> before.
>>> If you look at the table posted before, all is_acitve Flags are TRUE.
>>> to get the result I need, I have defined:
>>> query = (test2.type_marker=='object') | (test2.type_marker==None) &
>>> (test1.type_marker=='person')
>>> rows = current.db(query).select(test1.title, test2.title,
>>> left=test2.on(test1.id==test2.granny))
>>>
>>> the raw sql of this query WITHOUT record versioning is fine.
>>> SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN
>>> "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE
>>> (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND
>>> ("test1"."type_marker" = 'person')));
>>> And the result is the way it suposed to be:
>>>
>>> test1.titletest2.title
>>> PeterNone
>>> PaulLaptop
>>>
>>> The result with record versioning enabled is different:
>>> test1.titletest2.title
>>> PaulLaptop
>>>
>>> Even when the is_acitve Field is defined like that:
>>>
>>> Field('is_active', 'boolean', writable=False, readable=False, 
>>> default=True,required=True, requires=IS_NOT_EMPTY()),
>>>
>>> So maybe I miss something? At the moment I still wonder why the identical 
>>> query comes to different results.
>>>
>>>
>>> Best regards, an many thanks for your help!
>>> Andreas
>>>
>>>
>>>
>>>
>>>
>>> Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard:

 Hmmm... I think that if you have record versioning activated there
 shouldn't be any is_active row(s) with NULL value... I mean is_active flag
 is used to determine if the record in the "parent" table has been deleted
 or not, since you can truely deleted in case you use record versioning
 feature as all the previous record stated records contained in the
 versioning table reference the parent record in the parent table...

 And you see in the second example here :

 http://web2py.com/books/default/chapter/29/06/the-database-
 abstraction-layer?search=record+versioning#Record-versioning

 That is_active is set to default=True and there is this note :

 Notice the hidden boolean field called is_active and defaulting to
 True.

 So if you have activated record versioning feature over an already
 existing table you need to set is_active to TRUE for all the existing
 record once you start using the versioning feature and set the is_active to
 be mandatory...

 I guess it would make sens to change seconde book example in orther
 that is should be more obvious that is_active is mandatory like so :

 db.define_table('stored_item',
 Field('name'),
 Field('quantity', 'integer'),
 Field('is_active', 'boolean',

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-05 Thread 'Awe' via web2py-users 
Hello Richard,
it is not a problem of deleted or changed records. As shown in the example, 
there is the table and versioning defined. After that 3 records are 
inserted and then the query is executed.
The defined query:
query = (test2.type_marker=='object') | (test2.type_marker==None) & 
(test1.type_marker=='person')
rows = current.db(query).select(test1.title, test2.title, left=test2.on(
test1.id==test2.granny))
does not give the right result.
I appreciate your help, maybe the problem gets easier to understand if you 
try to test the given example.
Many thanks,
Andreas


Am Montag, 5. März 2018 16:05:26 UTC+1 schrieb Richard:
>
> Did you set the actual record that have been deleted to is_active = 
> False?? Or all the records that haven't be deleted yet to TRUE?? You have 
> to go in your backend and do an update there
>
> UPDATE stored_item
> SET is_active = TRUE
>   WHERE is_active IS NULL
>
> If you didn't delete any record yet...
>
> You can also do it from web2py I guess with this command :
>
> db(db.stored_item.is_active == None).update(is_active=True)
> db.commit()
>
> Then you both query should return the same thing... Or at least they 
> should...
>
> It important to initialize archive table and parent table properly when 
> you add audit trail table to an already existing table containing record.
>
> I encourage you to read the book about record versioning : 
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Record-versioning
>
> Richard
>
> On Sun, Mar 4, 2018 at 12:53 PM, 'Awe' via web2py-users <
> web...@googlegroups.com > wrote:
>
>> Hello Richard, many thanks for analyzing. Everything you wrote is 
>> completely right. But I still do not understand the behaviour explained 
>> before.
>> If you look at the table posted before, all is_acitve Flags are TRUE.
>> to get the result I need, I have defined:
>> query = (test2.type_marker=='object') | (test2.type_marker==None) & 
>> (test1.type_marker=='person')
>> rows = current.db(query).select(test1.title, test2.title, 
>> left=test2.on(test1.id==test2.granny))
>>
>> the raw sql of this query WITHOUT record versioning is fine.
>> SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN 
>> "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE 
>> (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND 
>> ("test1"."type_marker" = 'person')));
>> And the result is the way it suposed to be:
>>
>> test1.titletest2.title
>> PeterNone
>> PaulLaptop
>>
>> The result with record versioning enabled is different:
>> test1.titletest2.title
>> PaulLaptop
>>
>> Even when the is_acitve Field is defined like that:
>>
>> Field('is_active', 'boolean', writable=False, readable=False, 
>> default=True,required=True, requires=IS_NOT_EMPTY()),
>>
>> So maybe I miss something? At the moment I still wonder why the identical 
>> query comes to different results.
>>
>>
>> Best regards, an many thanks for your help!
>> Andreas
>>
>>
>>
>>
>>
>> Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard:
>>>
>>> Hmmm... I think that if you have record versioning activated there 
>>> shouldn't be any is_active row(s) with NULL value... I mean is_active flag 
>>> is used to determine if the record in the "parent" table has been deleted 
>>> or not, since you can truely deleted in case you use record versioning 
>>> feature as all the previous record stated records contained in the 
>>> versioning table reference the parent record in the parent table...
>>>
>>> And you see in the second example here :
>>>
>>>
>>> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=record+versioning#Record-versioning
>>>
>>> That is_active is set to default=True and there is this note :
>>>
>>> Notice the hidden boolean field called is_active and defaulting to True.
>>>
>>> So if you have activated record versioning feature over an already 
>>> existing table you need to set is_active to TRUE for all the existing 
>>> record once you start using the versioning feature and set the is_active to 
>>> be mandatory...
>>>
>>> I guess it would make sens to change seconde book example in orther that 
>>> is should be more obvious that is_active is mandatory like so :
>>>
>>> db.define_table('stored_item',
>>> Field('name'),
>>> Field('quantity', 'integer'),
>>> Field('is_active', 'boolean',
>>>   writable=False, readable=False, default=True, 
>>> required=True, requires=IS_NOT_EMPTY()))
>>>
>>>
>>>
>>> That way you don't need to care about null value to get all the records 
>>> of the parent table and DAL is not bugged...
>>>
>>> Richard
>>>
>>> On Fri, Mar 2, 2018 at 9:22 AM, 'Awe' via web2py-users <
>>> web...@googlegroups.com> wrote:
>>>
 So, I did check it again:

 It seems that DAL is not able to handle a left join using ISNULL

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-05 Thread Richard Vézina 
Did you set the actual record that have been deleted to is_active = False??
Or all the records that haven't be deleted yet to TRUE?? You have to go in
your backend and do an update there

UPDATE stored_item
SET is_active = TRUE
  WHERE is_active IS NULL

If you didn't delete any record yet...

You can also do it from web2py I guess with this command :

db(db.stored_item.is_active == None).update(is_active=True)
db.commit()

Then you both query should return the same thing... Or at least they
should...

It important to initialize archive table and parent table properly when you
add audit trail table to an already existing table containing record.

I encourage you to read the book about record versioning :
http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Record-versioning

Richard

On Sun, Mar 4, 2018 at 12:53 PM, 'Awe' via web2py-users <
web2py@googlegroups.com> wrote:

> Hello Richard, many thanks for analyzing. Everything you wrote is
> completely right. But I still do not understand the behaviour explained
> before.
> If you look at the table posted before, all is_acitve Flags are TRUE.
> to get the result I need, I have defined:
> query = (test2.type_marker=='object') | (test2.type_marker==None) &
> (test1.type_marker=='person')
> rows = current.db(query).select(test1.title, test2.title,
> left=test2.on(test1.id==test2.granny))
>
> the raw sql of this query WITHOUT record versioning is fine.
> SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN
> "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE
> (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND
> ("test1"."type_marker" = 'person')));
> And the result is the way it suposed to be:
>
> test1.titletest2.title
> PeterNone
> PaulLaptop
>
> The result with record versioning enabled is different:
> test1.titletest2.title
> PaulLaptop
>
> Even when the is_acitve Field is defined like that:
>
> Field('is_active', 'boolean', writable=False, readable=False, 
> default=True,required=True, requires=IS_NOT_EMPTY()),
>
> So maybe I miss something? At the moment I still wonder why the identical 
> query comes to different results.
>
>
> Best regards, an many thanks for your help!
> Andreas
>
>
>
>
>
> Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard:
>>
>> Hmmm... I think that if you have record versioning activated there
>> shouldn't be any is_active row(s) with NULL value... I mean is_active flag
>> is used to determine if the record in the "parent" table has been deleted
>> or not, since you can truely deleted in case you use record versioning
>> feature as all the previous record stated records contained in the
>> versioning table reference the parent record in the parent table...
>>
>> And you see in the second example here :
>>
>> http://web2py.com/books/default/chapter/29/06/the-database-
>> abstraction-layer?search=record+versioning#Record-versioning
>>
>> That is_active is set to default=True and there is this note :
>>
>> Notice the hidden boolean field called is_active and defaulting to True.
>>
>> So if you have activated record versioning feature over an already
>> existing table you need to set is_active to TRUE for all the existing
>> record once you start using the versioning feature and set the is_active to
>> be mandatory...
>>
>> I guess it would make sens to change seconde book example in orther that
>> is should be more obvious that is_active is mandatory like so :
>>
>> db.define_table('stored_item',
>> Field('name'),
>> Field('quantity', 'integer'),
>> Field('is_active', 'boolean',
>>   writable=False, readable=False, default=True, 
>> required=True, requires=IS_NOT_EMPTY()))
>>
>>
>>
>> That way you don't need to care about null value to get all the records
>> of the parent table and DAL is not bugged...
>>
>> Richard
>>
>> On Fri, Mar 2, 2018 at 9:22 AM, 'Awe' via web2py-users <
>> web...@googlegroups.com> wrote:
>>
>>> So, I did check it again:
>>>
>>> It seems that DAL is not able to handle a left join using ISNULL
>>> condition in combination with enabled record versioning.
>>> There are two "is active" checks within the left join: OK
>>> The next two upcoming ANDs to check "is_active" are not necessary and
>>> actually prohibit the query to work properly.
>>> To make this work it should be: AND (("test1"."is_active" = 'T') AND
>>> (("test2"."is_active" = 'T' OR ("test2"."is_active" ISNULL ))
>>> The two AND is active checks at the end of the query are not necessary
>>> at all (my opinion).
>>>
>>> Could somebody please verifiy this!
>>> Andreas
>>>
>>>
>>> Am Donnerstag, 1. März 2018 13:37:59 UTC+1 schrieb Awe:

 Hello,
 I post a simple step by step example, to explain.
 Doing a left join and using a where clause like: table.field==None
 Depending on _enable_record_versioning is enabled or not,

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-04 Thread 'Awe' via web2py-users 
Hello Richard, many thanks for analyzing. Everything you wrote is 
completely right. But I still do not understand the behaviour explained 
before.
If you look at the table posted before, all is_acitve Flags are TRUE.
to get the result I need, I have defined:
query = (test2.type_marker=='object') | (test2.type_marker==None) & 
(test1.type_marker=='person')
rows = current.db(query).select(test1.title, test2.title, left=test2.on(
test1.id==test2.granny))

the raw sql of this query WITHOUT record versioning is fine.
SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN 
"test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE 
(("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND 
("test1"."type_marker" = 'person')));
And the result is the way it suposed to be:

test1.titletest2.title
PeterNone
PaulLaptop

The result with record versioning enabled is different:
test1.titletest2.title
PaulLaptop

Even when the is_acitve Field is defined like that:

Field('is_active', 'boolean', writable=False, readable=False, 
default=True,required=True, requires=IS_NOT_EMPTY()),

So maybe I miss something? At the moment I still wonder why the identical query 
comes to different results.


Best regards, an many thanks for your help!
Andreas





Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard:
>
> Hmmm... I think that if you have record versioning activated there 
> shouldn't be any is_active row(s) with NULL value... I mean is_active flag 
> is used to determine if the record in the "parent" table has been deleted 
> or not, since you can truely deleted in case you use record versioning 
> feature as all the previous record stated records contained in the 
> versioning table reference the parent record in the parent table...
>
> And you see in the second example here :
>
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=record+versioning#Record-versioning
>
> That is_active is set to default=True and there is this note :
>
> Notice the hidden boolean field called is_active and defaulting to True.
>
> So if you have activated record versioning feature over an already 
> existing table you need to set is_active to TRUE for all the existing 
> record once you start using the versioning feature and set the is_active to 
> be mandatory...
>
> I guess it would make sens to change seconde book example in orther that 
> is should be more obvious that is_active is mandatory like so :
>
> db.define_table('stored_item',
> Field('name'),
> Field('quantity', 'integer'),
> Field('is_active', 'boolean',
>   writable=False, readable=False, default=True, 
> required=True, requires=IS_NOT_EMPTY()))
>
>
>
> That way you don't need to care about null value to get all the records of 
> the parent table and DAL is not bugged...
>
> Richard
>
> On Fri, Mar 2, 2018 at 9:22 AM, 'Awe' via web2py-users <
> web...@googlegroups.com > wrote:
>
>> So, I did check it again:
>>
>> It seems that DAL is not able to handle a left join using ISNULL 
>> condition in combination with enabled record versioning.
>> There are two "is active" checks within the left join: OK
>> The next two upcoming ANDs to check "is_active" are not necessary and 
>> actually prohibit the query to work properly.
>> To make this work it should be: AND (("test1"."is_active" = 'T') AND 
>> (("test2"."is_active" = 'T' OR ("test2"."is_active" ISNULL ))
>> The two AND is active checks at the end of the query are not necessary at 
>> all (my opinion).
>>
>> Could somebody please verifiy this!
>> Andreas
>>
>>
>> Am Donnerstag, 1. März 2018 13:37:59 UTC+1 schrieb Awe:
>>>
>>> Hello,
>>> I post a simple step by step example, to explain.
>>> Doing a left join and using a where clause like: table.field==None
>>> Depending on _enable_record_versioning is enabled or not, I get 
>>> different results when executing the identical query.
>>> The result when versioning is DISABLED is the correct one.
>>>
>>> 2.15.4-stable+timestamp.2017.09.02.04.02.22
>>> (läuft auf Rocket 1.2.6, Python 2.7.12)
>>> SQLite DB
>>>
>>> Example:
>>>
>>> #simple table:
>>> current.db.define_table('test',
>>>Field('title', 'string'),
>>>Field('granny', 'reference test'),  # convenience - 
>>> topmost
>>>Field('type_marker', 'string'),
>>>auth.signature,
>>>)
>>> #versioning, enable/disable it
>>> current.db.test._enable_record_versioning(archive_db=current.db,
>>>   archive_name='test_archive',
>>>   current_record='current_record',
>>>   is_active='is_active'
>>>   )
>>>
>>> #put some data in
>>> #current.db.test.insert(**{'title': 'Peter', 'granny': None, 
>>>

Re: [web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-02 Thread Richard Vézina 
Hmmm... I think that if you have record versioning activated there
shouldn't be any is_active row(s) with NULL value... I mean is_active flag
is used to determine if the record in the "parent" table has been deleted
or not, since you can truely deleted in case you use record versioning
feature as all the previous record stated records contained in the
versioning table reference the parent record in the parent table...

And you see in the second example here :

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=record+versioning#Record-versioning

That is_active is set to default=True and there is this note :

Notice the hidden boolean field called is_active and defaulting to True.

So if you have activated record versioning feature over an already existing
table you need to set is_active to TRUE for all the existing record once
you start using the versioning feature and set the is_active to be
mandatory...

I guess it would make sens to change seconde book example in orther that is
should be more obvious that is_active is mandatory like so :

db.define_table('stored_item',
Field('name'),
Field('quantity', 'integer'),
Field('is_active', 'boolean',
  writable=False, readable=False, default=True,
required=True, requires=IS_NOT_EMPTY()))



That way you don't need to care about null value to get all the records of
the parent table and DAL is not bugged...

Richard

On Fri, Mar 2, 2018 at 9:22 AM, 'Awe' via web2py-users <
web2py@googlegroups.com> wrote:

> So, I did check it again:
>
> It seems that DAL is not able to handle a left join using ISNULL condition
> in combination with enabled record versioning.
> There are two "is active" checks within the left join: OK
> The next two upcoming ANDs to check "is_active" are not necessary and
> actually prohibit the query to work properly.
> To make this work it should be: AND (("test1"."is_active" = 'T') AND
> (("test2"."is_active" = 'T' OR ("test2"."is_active" ISNULL ))
> The two AND is active checks at the end of the query are not necessary at
> all (my opinion).
>
> Could somebody please verifiy this!
> Andreas
>
>
> Am Donnerstag, 1. März 2018 13:37:59 UTC+1 schrieb Awe:
>>
>> Hello,
>> I post a simple step by step example, to explain.
>> Doing a left join and using a where clause like: table.field==None
>> Depending on _enable_record_versioning is enabled or not, I get different
>> results when executing the identical query.
>> The result when versioning is DISABLED is the correct one.
>>
>> 2.15.4-stable+timestamp.2017.09.02.04.02.22
>> (läuft auf Rocket 1.2.6, Python 2.7.12)
>> SQLite DB
>>
>> Example:
>>
>> #simple table:
>> current.db.define_table('test',
>>Field('title', 'string'),
>>Field('granny', 'reference test'),  # convenience -
>> topmost
>>Field('type_marker', 'string'),
>>auth.signature,
>>)
>> #versioning, enable/disable it
>> current.db.test._enable_record_versioning(archive_db=current.db,
>>   archive_name='test_archive',
>>   current_record='current_record',
>>   is_active='is_active'
>>   )
>>
>> #put some data in
>> #current.db.test.insert(**{'title': 'Peter', 'granny': None,
>> 'type_marker': 'person'})
>> #current.db.test.insert(**{'title': 'Paul', 'granny': None,
>> 'type_marker': 'person'})
>> #current.db.test.insert(**{'title': 'Laptop', 'granny': 2,
>> 'type_marker': 'object'})
>>
>> #so you get table content (enabled)t:
>> test.idtest.titletest.grannytest.type_marker
>> test.is_activetest.created_ontest.created_by
>> test.modified_ontest.modified_by
>> 1  Peter Noneperson
>> True2018-03-01 08:11:40Me2018-03-01
>> 08:11:40Me
>> 2  Paul  Noneperson
>> True2018-03-01 08:11:40Me2018-03-01
>> 08:11:40Me
>> 3  Laptop   2  object
>> True2018-03-01 08:11:40Me2018-03-01
>> 08:11:40Me
>>
>> #simple controller function
>> def demo():
>> test1=current.db.test.with_alias('test1')
>> test2=current.db.test.with_alias('test2')
>> query = (test2.type_marker=='object') | (test2.type_marker==None) &
>> (test1.type_marker=='person')
>> tbl = current.db().select(current.db.test.ALL)
>> rows = current.db(query).select(test1.title, test2.title,
>> left=test2.on(test1.id==test2.granny))
>> prn =  current.db(query)._select(test1.title, test2.title,
>> left=test2.on(test1.id==test2.granny))
>> return dict(tbl=tbl, rows=rows, prn=prn)
>>
>> Result/Output:
>> test1.titletest2.title
>> PaulLaptop
>>
>> Raw SQL:
>> SELECT "test1"."title",

[web2py] Re: Unexpected behaviour executing a query with DAL and record versioning enabled

2018-03-02 Thread 'Awe' via web2py-users 
So, I did check it again:

It seems that DAL is not able to handle a left join using ISNULL condition 
in combination with enabled record versioning.
There are two "is active" checks within the left join: OK
The next two upcoming ANDs to check "is_active" are not necessary and 
actually prohibit the query to work properly.
To make this work it should be: AND (("test1"."is_active" = 'T') AND 
(("test2"."is_active" = 'T' OR ("test2"."is_active" ISNULL ))
The two AND is active checks at the end of the query are not necessary at 
all (my opinion).

Could somebody please verifiy this!
Andreas

Am Donnerstag, 1. März 2018 13:37:59 UTC+1 schrieb Awe:
>
> Hello,
> I post a simple step by step example, to explain.
> Doing a left join and using a where clause like: table.field==None
> Depending on _enable_record_versioning is enabled or not, I get different 
> results when executing the identical query.
> The result when versioning is DISABLED is the correct one.
>
> 2.15.4-stable+timestamp.2017.09.02.04.02.22
> (läuft auf Rocket 1.2.6, Python 2.7.12)
> SQLite DB
>
> Example:
>
> #simple table:
> current.db.define_table('test',
>Field('title', 'string'),
>Field('granny', 'reference test'),  # convenience - 
> topmost
>Field('type_marker', 'string'),
>auth.signature,
>)
> #versioning, enable/disable it
> current.db.test._enable_record_versioning(archive_db=current.db,
>   archive_name='test_archive',
>   current_record='current_record',
>   is_active='is_active'
>   )
>
> #put some data in
> #current.db.test.insert(**{'title': 'Peter', 'granny': None, 
> 'type_marker': 'person'})
> #current.db.test.insert(**{'title': 'Paul', 'granny': None, 'type_marker': 
> 'person'})
> #current.db.test.insert(**{'title': 'Laptop', 'granny': 2, 'type_marker': 
> 'object'})
>
> #so you get table content (enabled)t:
> test.idtest.titletest.grannytest.type_marker
> test.is_activetest.created_ontest.created_by
> test.modified_ontest.modified_by
> 1  Peter Noneperson
> True2018-03-01 08:11:40Me2018-03-01 
> 08:11:40Me
> 2  Paul  Noneperson
> True2018-03-01 08:11:40Me2018-03-01 
> 08:11:40Me
> 3  Laptop   2  object
> True2018-03-01 08:11:40Me2018-03-01 
> 08:11:40Me
>
> #simple controller function
> def demo():
> test1=current.db.test.with_alias('test1')
> test2=current.db.test.with_alias('test2')
> query = (test2.type_marker=='object') | (test2.type_marker==None) & 
> (test1.type_marker=='person')
> tbl = current.db().select(current.db.test.ALL)
> rows = current.db(query).select(test1.title, test2.title, 
> left=test2.on(test1.id==test2.granny))
> prn =  current.db(query)._select(test1.title, test2.title, 
> left=test2.on(test1.id==test2.granny))
> return dict(tbl=tbl, rows=rows, prn=prn)
>
> Result/Output:
> test1.titletest2.title
> PaulLaptop
>
> Raw SQL:
> SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN 
> "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND 
> (("test1"."is_active" = 'T') AND ("test2"."is_active" = 'T'))) WHERE 
> "test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) 
> AND ("test1"."type_marker" = 'person'))) AND (("test1"."is_active" = 'T') 
> AND ("test2"."is_active" = 'T'))) AND (("test1"."is_active" = 'T') AND 
> ("test2"."is_active" = 'T')));
>
>
> NOW DISABLED Record versioning:
>
> Result/Output:
> test1.titletest2.title
> PeterNone
> PaulLaptop
>
> Raw SQL:
> SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN 
> "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE 
> (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND 
> ("test1"."type_marker" = 'person')));
>
> QUESTION:
> The result with versioning turned off looks right.
> It seems that the automatically inserted "AND... is_active" clauses are 
> not correct.
> Many thanks for your help and assistance.
> Andreas
>
>

-- 
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.