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