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','','rakeshsingh...@gmail.com')
> 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 <
>>> 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?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>
> 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 <
>>> 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?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>
> 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 <
>>> 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?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>
