Thanks Massimo. Will do.
I'll test it against Oracle today as well.
On Wednesday, 11 April 2012 06:40:56 UTC+2, Massimo Di Pierro wrote:
>
> This helps a lot. WIll check this asap. To make sure I do not foget and it
> tracked, plase open an issue in google code.
>
>
> On Tuesday, 10 April 2012 14:12:38 UTC-5, Rakesh Singh wrote:
>>
>> Hi Massimo,
>>
>> Regarding the MySQL error, re-created the database and started a new app
>> with auth.signature=True
>>
>> Here is the auth_user creation log on MySQL followed by the insert that
>> generates the error :
>>
>> CREATE TABLE auth_user(
>> id INT AUTO_INCREMENT NOT NULL,
>> first_name VARCHAR(128),
>> last_name VARCHAR(128),
>> email VARCHAR(255),
>> username VARCHAR(128),
>> password VARCHAR(255),
>> registration_key VARCHAR(255),
>> reset_password_key VARCHAR(255),
>> registration_id VARCHAR(255),
>> is_active CHAR(1),
>> created_on DATETIME,
>> created_by INT, INDEX created_by__idx (created_by), FOREIGN KEY
>> (created_by) REFERENCES auth_user(id) ON DELETE CASCADE,
>> modified_on DATETIME,
>> modified_by INT, INDEX modified_by__idx (modified_by), FOREIGN KEY
>> (modified_by) REFERENCES auth_user(id) ON DELETE CASCADE,
>> PRIMARY KEY(id)
>> ) ENGINE=InnoDB CHARACTER SET utf8
>> 3 Query COMMIT
>>
>>
>> INSERT INTO
>> auth_user(username,first_name,last_name,modified_by,is_active,registration_id,created_by,
>> reset_password_key,created_on,modified_on,password,registration_key,email)
>>
>> VALUES ('rakesh','Rakesh','Singh',0,'T','',0,'','2012-04-10
>> 20:47:09','2012-04-10 20:47:09',
>> '0835d7189a6927648202bd9d8a8562a8','','[email protected]')
>> 6 Query ROLLBACK
>>
>> Manually executing the insert returns:
>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
>> constraint fails (`web2py_dev`.`auth_user`, CONSTRAINT `auth_user_ibfk_1`
>> FOREIGN KEY (`created_by`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE)
>>
>>
>> Which makes sense, since the value for created_by (and modified_by)
>> defaults to 0 and that ID does not exist in auth_user.id
>> Modifying the created_by and modified_by values to NULL allows this
>> record to be inserted.
>>
>> Thank you for your help once again.
>>
>> Regards,
>>
>> Rakesh
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>>> On Tue, Apr 10, 2012 at 9:33 AM, Massimo Di Pierro <
>>>> [email protected]> 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?
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>
>> On Tuesday, 10 April 2012 18:53:05 UTC+2, Massimo Di Pierro wrote:
>>>
>>> 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 <
>>>> [email protected]> 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?
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>
>> On Tuesday, 10 April 2012 18:53:05 UTC+2, Massimo Di Pierro wrote:
>>>
>>> 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 <
>>>> [email protected]> 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?
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>
