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.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+unsubscr...@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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to