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 < [email protected]> 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 < >> [email protected]> 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 < >>>> [email protected]> 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 [email protected]. >>>>> 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 [email protected]. >>> 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 [email protected]. > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
