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.title test2.title
Peter None
Paul Laptop
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]: <Rows (0)>
>
> In [2]: db(db.test.id > 0).select()
> Out[2]: <Rows (3)>
>
> 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 <module>()
> ----> 1 db().select(current.db.test.ALL)
>
> NameError: name 'current' is not defined
>
> In [7]: db().select(db.test.ALL)
> Out[7]: <Rows (3)>
>
> In [8]: query = (test2.type_marker=='object') &
> (test1.type_marker=='person')
>
> In [9]: db().select(db.test.ALL)
> Out[9]: <Rows (3)>
>
> In [10]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
> ==test2.granny))
> Out[10]: <Rows (1)>
>
> 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,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
> 2,Paul,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
> 3,Laptop,2,object,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
>
>
> *WITHOUT record versioning*
>
> In [1]: db(db.test.id > 0).select()
> Out[1]: <Rows (3)>
>
> 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]: <Rows (3)>
>
> In [6]: query = (test2.type_marker=='object') &
> (test1.type_marker=='person')
>
> In [7]: db().select(db.test.ALL)
> Out[7]: <Rows (3)>
>
> In [8]: db(query).select(test1.title, test2.title, left=test2.on(test1.id
> ==test2.granny))
> Out[8]: <Rows (1)>
>
> 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,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
> 2,Paul,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
> 3,Laptop,2,object,True,2018-03-07 11:10:08,<NULL>,2018-03-07
> 11:10:08,<NULL>
>
>
>
>
> *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 <
> web...@googlegroups.com <javascript:>> wrote:
>
>> 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.title test2.title
>>>>>> Peter None
>>>>>> Paul Laptop
>>>>>>
>>>>>> The result with record versioning enabled is different:
>>>>>> test1.title test2.title
>>>>>> Paul Laptop
>>>>>>
>>>>>> 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,
>>>>>>>>> '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.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 None person
>>>>>>>>> True 2018-03-01 08:11:40 Me
>>>>>>>>> 2018-03-01
>>>>>>>>> 08:11:40 Me
>>>>>>>>> 2 Paul None person
>>>>>>>>> True 2018-03-01 08:11:40 Me
>>>>>>>>> 2018-03-01
>>>>>>>>> 08:11:40 Me
>>>>>>>>> 3 Laptop 2
>>>>>>>>> object True 2018-03-01 08:11:40
>>>>>>>>> Me 2018-03-01 08:11:40 Me
>>>>>>>>>
>>>>>>>>> #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.title test2.title
>>>>>>>>> Paul Laptop
>>>>>>>>>
>>>>>>>>> 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.title test2.title
>>>>>>>>> Peter None
>>>>>>>>> Paul Laptop
>>>>>>>>>
>>>>>>>>> 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+un...@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+un...@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+un...@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+un...@googlegroups.com <javascript:>.
>> 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.
