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