Hi,
It looks like a great new feature... I would suggest something. Instead of
copying every update, I would keep the records only once the it gets in a
particular state. In my case a records could have no review, can be
reviewed and approved, so I just want to keep the changes that occured once
the records have been reviewed. Before that it is only noise to me. I mean
if the records don't need a review process I don't need the audit trail and
if it needs a review or an approval I only need to audit the change after
it has been review once what happen before I don't care. I think it is a
really good trade off between size of the database and an audit trail
feature. But it means that you have to build a reviewing process feature...
It could be option maybe, I mean audit all vs audit after get the state
review.
Thanks for this Massimo any way our lives can't be easier without you :)
Richard
On Tue, Apr 10, 2012 at 9:33 AM, Massimo Di Pierro <
massimo.dipie...@gmail.com> wrote:
> Actually I was wrong. this is not the problem. Web2py does the right
> thing. Is there any way you can look into the mysql logs what is the sql
> string that causes the problem?
>
>
> On Tuesday, 10 April 2012 08:23:03 UTC-5, Massimo Di Pierro wrote:
>>
>> I figured this out. The table has a self reference and web2py inserts a
>> zero in it instead of NULL. Works for sqlite but not MySQL.
>> Changing zero with NULL may be treated as a bug fix it will constitute a
>> minor change of backward compatibility in case you incorrectly do
>>
>> db(db.table.reference_field==**0).select()
>>
>> while the correct thing to do would be
>>
>> db(~(db.table.reference_field>**0)).select()
>>
>> I will try fix it and then will ask for comments.
>>
>> On Monday, 9 April 2012 18:29:10 UTC-5, tomt wrote:
>>>
>>> I have declared the table in db.py with auth.signature, and uncommented
>>> auth.enable_record_versioning(**db), but the _archive table isn't
>>> created.
>>> - Tom
>>>
>>> On Monday, April 9, 2012 8:33:18 AM UTC-6, Massimo Di Pierro wrote:
>>>>
>>>> the signature=True only adds a signature to the auth_* tables so that
>>>> if a user creates an account for another user or creates a group, you can
>>>> keep track of who did it.
>>>>
>>>> The mything_archive table should be created by:
>>>>
>>>> auth.enable_record_versioning(**db)
>>>>
>>>> This should be called after the mything table is defined. Does it work?
>>>>
>>>> On Sunday, 8 April 2012 22:08:47 UTC-5, tomt wrote:
>>>>>
>>>>> Hi,
>>>>> Thanks for your response. I deleted the database as you suggested and
>>>>> changed signature=False. The problem did go away and I was able to add
>>>>> users without the error.
>>>>> I then reverted to signature=True. While subsequent modifications did
>>>>> show the signature, the 'mything_archive' was never created.
>>>>>
>>>>> - Tom
>>>>>
>>>>> On Sunday, April 8, 2012 9:04:14 AM UTC-6, Massimo Di Pierro wrote:
>>>>>>
>>>>>> Can you try again with mysql, delete the database and replace:
>>>>>>
>>>>>> auth.define_tables(signature=**True)
>>>>>> with
>>>>>> auth.define_tables(signature=**False)
>>>>>>
>>>>>> Does the problem does away? It looks like it does not like the self
>>>>>> reference in auth_user.
>>>>>>
>>>>>> On Saturday, 7 April 2012 22:09:31 UTC-5, tomt wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I tried using your new versioning feature in trunk.
>>>>>>> I created an app using a mysql database:
>>>>>>> db = DAL('mysql://version:version@**localhost/version')
>>>>>>> When I used the admin function to define a new user
>>>>>>> I received the following error:
>>>>>>> ..............................**..........
>>>>>>> <class 'gluon.contrib.pymysql.err.**IntegrityError'>
>>>>>>> (1452, u'Cannot add or update a child row: a foreign key constraint
>>>>>>> fails
>>>>>>> (`version/auth_user`, CONSTRAINT `auth_user_ibfk_1`
>>>>>>> FOREIGN KEY (`created_by`) REFERENCES `auth_user` (`id`) ON DELETE
>>>>>>> CASCADE)')
>>>>>>> ..............................**..........
>>>>>>>
>>>>>>> I rebuilt the app to use sqlite instead of mysql:
>>>>>>> db = DAL('sqlite://storage.sqlite')
>>>>>>>
>>>>>>> I was then able to add a user without the error
>>>>>>>
>>>>>>> I was using MySQL client version: 5.0.84
>>>>>>>
>>>>>>> - any suggestions? - Tom
>>>>>>>
>>>>>>> On Thursday, April 5, 2012 4:16:04 PM UTC-6, Massimo Di Pierro wrote:
>>>>>>>>
>>>>>>>> This is how it works:
>>>>>>>>
>>>>>>>> # define auth
>>>>>>>> auth = Auth(db, hmac_key=Auth.get_or_create_**key())
>>>>>>>> auth.define_tables(username=**True,signature=True)
>>>>>>>>
>>>>>>>> # define your own tables like
>>>>>>>> db.define_table('mything',**Field('name'),auth.signature)
>>>>>>>>
>>>>>>>> # than do:
>>>>>>>> auth.enable_record_versioning(**db)
>>>>>>>>
>>>>>>>> how does it work? every table, including auth_user will have an
>>>>>>>> auth.signature including created_by, created_on, modified_by,
>>>>>>>> modified_on,
>>>>>>>> is_active fields. When a record of table mything (or any other table)
>>>>>>>> is
>>>>>>>> modified, a copy of the previous record is copied into mything_archive
>>>>>>>> which references the current record. When a record is deleted, it is
>>>>>>>> not
>>>>>>>> actually deleted but is_active is set to False, all records with
>>>>>>>> is_active==False are filtered out in searches except in appadmin.
>>>>>>>>
>>>>>>>> Pros:
>>>>>>>> - your app will get full record archival for auditing purposes
>>>>>>>> - could not be simpler. nothing else to do. Try with
>>>>>>>> SQLFORM.grid(db.mything) for example.
>>>>>>>> - does not break references and there is no need for uuids
>>>>>>>> - does not slow down searches because archive is done in separate
>>>>>>>> archive tables
>>>>>>>>
>>>>>>>> Cons:
>>>>>>>> - uses lots of extra memory because every version of a record is
>>>>>>>> stored (it would be more efficient to store changes only but that would
>>>>>>>> make more difficult to do auditing).
>>>>>>>> - slows down db(...).update(...) for multi record because it needs
>>>>>>>> to copy all records needing update from the original table to the
>>>>>>>> archive
>>>>>>>> table. This requires selecting all the records.
>>>>>>>>
>>>>>>>> Comments? Suggestions?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
