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.
