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