How to Enable logging at DAL level ?

[Mandar Vaze]

Hi,

I'm interested in capturing all the inserts/updates and deletes - Especially I want the auth_user performing these operations and the timestamp(s) for the operations.

This will be useful in production setup to figure out who modified/deleted specific record in case things go wrong.

(When I was using pylons+sqlalchemy in one of my earlier projects, I found this functionality useful, and all I had to do was modify logging.conf)

I played around with logging - copied logging.example.conf to logging.conf, then added following lines :

[loggers]                                                                      
keys=root,rocket,markdown,web2py,dal,rewrite,cron,app,welcome

[logger_dal]                                                                    
level=DEBUG                                                                    
handlers=consoleHandler,rotatingFileHandler                                    
qualname=web2py.dal                                                            
propagate=0          

Then I restarted web2py. But I still don't see any DB transactions in the log.

Further looking into gluon/dal.py - I realized that insert/update/delete() do NOT have logger.debug statements - It is likely that is why I don't see anything in the log/web2py.log

Any sugestions how I get the functionality ?  

Thanks,

-Mandar

[Niphlod]

There is no facility to log all the queries ATM, but the functionality to have all things "traced" for all tables (or only for the tables you want to) is embedded in DAL: there are no logs, just "a way" to version records on the tables.

http://web2py.com/books/default/chapter/29/06#Record-versioning

[Mandar Vaze]

I was able to get the functionality I wanted :)

I "traced" the DAL (import pdb - pdb.set_trace()) and realized that "debug" was set to False - Default value.

I updated models/db.py as follows :

db = DAL('mysql://user:pass@host/db', debug=True)  

Now all the queries get logged nicely.

I understand this might be "overkill" since there are a LOTS of "select"s (as expected) but at least I have the data now. Now to optimize the solution.

BTW, I'm using 2.0.9, if it matters

-Mandar

[Niphlod]

I learnt something new today. if you need only the statements for the auth_user table it's a little overkill (and a lot les understandable, and you don't know, e.g. who modified the records). I'd stick with table versioning.

[Mandar Vaze / मंदार वझे]

 

if you need only the statements for the auth_user table it's a little overkill (and a lot les understandable, and you don't know, e.g. who modified the records). I'd stick with table versioning.

Not sure I completely understand what you mean, but my requirement is NOT restricted to "who changed auth_user" entries.

I need "who updated/deleted" the other records in the system (There are some master tables, there are some tasks etc.)

So I need an ability to trace the changes that caused "unwanted" results (Crucial data deleted for example - who and when ?)

-Mandar

[Niphlod]

and, still, you don't know who issued the "delete from myimportantdata where 1=1" or "update myimportantdata set dontdeletethiscolumn='' " just logging the queries. Have you by any chance looked into web2py's versioning ?