Re: [web2py] Re: new feature in trunk: full auditing
Will this auditing also work with changes in data that does not come from form-submission? Say for instance I run a batch update/delete from a script or using 'selectable' in a grid to select a number of records to be deleted, will that not bypass the auditing? I ask because calculated fields are not updated in such a case. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: new feature in trunk: full auditing
Yes but if you do a batch delete or update web2py has to copy all records from one table to another one by one. That may cause a significati slowdown that will lock the db for long time. It may have bad consequences. On Monday, 16 April 2012 04:56:54 UTC-5, Johann Spies wrote: Will this auditing also work with changes in data that does not come from form-submission? Say for instance I run a batch update/delete from a script or using 'selectable' in a grid to select a number of records to be deleted, will that not bypass the auditing? I ask because calculated fields are not updated in such a case. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: new feature in trunk: full auditing
On 16 April 2012 15:18, Massimo Di Pierro massimo.dipie...@gmail.comwrote: Yes but if you do a batch delete or update web2py has to copy all records from one table to another one by one. That may cause a significati slowdown that will lock the db for long time. It may have bad consequences. Thanks for your answer but because I wrote unnecessarily two contrasting questions and you answered one of them I am not sure which one you have answered :) Does that 'yes' mean that batch operations will bypass the auditing process? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: new feature in trunk: full auditing
No. Batch operations do not bypass the auditing process. On Monday, 16 April 2012 08:52:22 UTC-5, Johann Spies wrote: On 16 April 2012 15:18, Massimo Di Pierro massimo.dipie...@gmail.comwrote: Yes but if you do a batch delete or update web2py has to copy all records from one table to another one by one. That may cause a significati slowdown that will lock the db for long time. It may have bad consequences. Thanks for your answer but because I wrote unnecessarily two contrasting questions and you answered one of them I am not sure which one you have answered :) Does that 'yes' mean that batch operations will bypass the auditing process? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: new feature in trunk: full auditing
On 16 April 2012 16:03, Massimo Di Pierro massimo.dipie...@gmail.comwrote: No. Batch operations do not bypass the auditing process. Thanks. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
[web2py] Re: new feature in trunk: full auditing
Fixed in trunk.
On Saturday, 14 April 2012 23:10:18 UTC-5, tomt wrote:
I discovered why the archive table wasn't being created, there is a typo
in gluon/tools.py at line 1293:
for table in tables:
if 'modifed_on' in table.fields():
should be
for table in tables:
if 'modified_on' in table.fields():
Now the the 'mythings_archive' table is created as expected, and changes
are recorded in it as expected.
I didn't mention it before, but I expect that the builtin audit feature
will be very useful for my applications. This feature along with the
addition of SQLFORM.grid have made web2py enormously more productive.
Thanks to all the developers for all these improvements.
- Tom
On Friday, April 13, 2012 10:53:10 PM UTC-6, tomt wrote:
I'm have been trying to test this new audit function in trunk, but so far
the mything_archive table has never been created in any of my tests.
Is this the current state of the implementation, or am I missing
something?
I downloaded the latest trunk and used it web2py admin to create a new
app.
I then modified db.py according to the instructions in this thread:
auth = Auth(db, hmac_key=Auth.get_or_create_key())
auth.define_tables(username=True, signature=True)
...
db.define_table(points,
Field('type','integer'),
Field('point','integer'),
Field('pointname','string'),
auth.signature)
auth.enable_record_versioning(db)
I've tried this with both mysql and sqlite databases, but no *_archive
table...
Any comments or suggestions?
thanks, - 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 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?
[web2py] Re: new feature in trunk: full auditing
I discovered why the archive table wasn't being created, there is a typo in
gluon/tools.py at line 1293:
for table in tables:
if 'modifed_on' in table.fields():
should be
for table in tables:
if 'modified_on' in table.fields():
Now the the 'mythings_archive' table is created as expected, and changes
are recorded in it as expected.
I didn't mention it before, but I expect that the builtin audit feature
will be very useful for my applications. This feature along with the
addition of SQLFORM.grid have made web2py enormously more productive.
Thanks to all the developers for all these improvements.
- Tom
On Friday, April 13, 2012 10:53:10 PM UTC-6, tomt wrote:
I'm have been trying to test this new audit function in trunk, but so far
the mything_archive table has never been created in any of my tests.
Is this the current state of the implementation, or am I missing something?
I downloaded the latest trunk and used it web2py admin to create a new app.
I then modified db.py according to the instructions in this thread:
auth = Auth(db, hmac_key=Auth.get_or_create_key())
auth.define_tables(username=True, signature=True)
...
db.define_table(points,
Field('type','integer'),
Field('point','integer'),
Field('pointname','string'),
auth.signature)
auth.enable_record_versioning(db)
I've tried this with both mysql and sqlite databases, but no *_archive
table...
Any comments or suggestions?
thanks, - 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 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?
[web2py] Re: new feature in trunk: full auditing
I'm have been trying to test this new audit function in trunk, but so far
the mything_archive table has never been created in any of my tests.
Is this the current state of the implementation, or am I missing something?
I downloaded the latest trunk and used it web2py admin to create a new app.
I then modified db.py according to the instructions in this thread:
auth = Auth(db, hmac_key=Auth.get_or_create_key())
auth.define_tables(username=True, signature=True)
...
db.define_table(points,
Field('type','integer'),
Field('point','integer'),
Field('pointname','string'),
auth.signature)
auth.enable_record_versioning(db)
I've tried this with both mysql and sqlite databases, but no *_archive
table...
Any comments or suggestions?
thanks, - 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 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?
Re: [web2py] Re: new feature in trunk: full auditing
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 QueryCOMMIT
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 QueryROLLBACK
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)')
[web2py] Re: new feature in trunk: full auditing
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_field0)).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?
[web2py] Re: new feature in trunk: full auditing
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_field0)).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?
Re: [web2py] Re: new feature in trunk: full auditing
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
Re: [web2py] Re: new feature in trunk: full auditing
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
Re: [web2py] Re: new feature in trunk: full auditing
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 QueryCOMMIT
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 QueryROLLBACK
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,
Re: [web2py] Re: new feature in trunk: full auditing
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 QueryCOMMIT
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 QueryROLLBACK
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
[web2py] Re: new feature in trunk: full auditing
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?
[web2py] Re: new feature in trunk: full auditing
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?
[web2py] Re: new feature in trunk: full auditing
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?
[web2py] Re: new feature in trunk: full auditing
Hi,
I have received the same error as Tom on MySQL (5.5.22) when adding a user
to the auth_user table via the Database Administration screen.
(1452, u'Cannot add or update a child row: a foreign key constraint fails)
Reverting back to signature=False resulted in an error (Trace below) :
InternalError: (1025, uError on rename of './web2py_dev/#sql-415a_13' to
'./web2py_dev/auth_user' (errno: 150))
When using SQLite, you can use the database administrator to add a user
without being logged in to your application.
With the MySQL connection, you must be logged in so that the 'Createed By'
and 'Modified By' fields are correctly populated with a valid entry.
Once you register and login, the database administration tool can be used
as normal.
Thanks
Rakesh
Traceback (most recent call last):
File /data/source/web2py_src/web2py/gluon/restricted.py, line 205, in
restricted
exec ccode in environment
File /data/source/web2py_src/web2py/applications/web2py_test/models/db.py
http://localhost:8000/admin/default/edit/web2py_test/models/db.py, line 23,
in module
auth.define_tables(username=True, signature=False)
File /data/source/web2py_src/web2py/gluon/tools.py, line 1356, in
define_tables
format='%(username)s'))
File /data/source/web2py_src/web2py/gluon/dal.py, line 6621, in define_table
polymodel=polymodel)
File /data/source/web2py_src/web2py/gluon/dal.py, line 784, in create_table
fake_migrate=fake_migrate)
File /data/source/web2py_src/web2py/gluon/dal.py, line 883, in migrate_table
self.execute(sub_query)
File /data/source/web2py_src/web2py/gluon/dal.py, line 1446, in execute
return self.log_execute(*a, **b)
File /data/source/web2py_src/web2py/gluon/dal.py, line 1440, in log_execute
ret = self.cursor.execute(*a, **b)
File /data/source/web2py_src/web2py/gluon/contrib/pymysql/cursors.py, line
108, in execute
self.errorhandler(self, exc, value)
File /data/source/web2py_src/web2py/gluon/contrib/pymysql/connections.py,
line 184, in defaulterrorhandler
raise errorclass, errorvalue
InternalError: (1025, uError on rename of './web2py_dev/#sql-415a_13' to
'./web2py_dev/auth_user' (errno: 150))
On Sunday, 8 April 2012 17:04:14 UTC+2, 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?
[web2py] Re: new feature in trunk: full auditing
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?
[web2py] Re: new feature in trunk: full auditing
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?
