I agree that is people would normally use the feature. And that's a matter
of deleting older archived records. What I am not sure is if it should be
built-in into web2py.
If would be easy to do db(db.table_archive).delete() which event should
trigger it? perhaps this is one of those actions should be
called explicitly and not done automatically.
On Tuesday, 10 April 2012 09:22:44 UTC-5, Richard wrote:
>
> 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?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>
