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?
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
