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.