分享一个SQL trace 功能

fisher

SAP中有个SQL跟踪的功能对开发很有帮助，在Odoo中虽然可以启用SQL调试参数或者在数据库配置文件中启用SQL语句日志功能，但这些功能要么不能按用户帐号，模块或数据表（model)过滤，不是很方便。
参考OCA中的connector项目，做了一个简单的SQL trace 模块，贴出来分享一下。
[b]init.py[/b]

import sqltrace
import sqltrace_hook
import res_users

[b]openerp.py[/b]

{
'name': 'SQL trace',
'version': '1.0',
'author': 'xxx',
'website': '',
'category': 'xxxx',
'sequence': 100,
'summary': 'SQL trace',
'images': [
],
'depends': [
'mail',
],
'description': """
SQL Trace
""",
'data': ['res_users_view.xml'
],
'test': [
],
'installable': True,
'auto_install': False,
'application': True,
}

[b]sqltrace_hook.py[/b]

from openerp.osv import orm
import datetime

_search_original = orm.BaseModel._search

def _search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False, access_rights_uid=None):
start = datetime.datetime.now()
result = _search_original(self, cr, user, args, offset, limit, order, context, count, access_rights_uid)
duration = datetime.datetime.now() - start
if context: # make sure context is ready
sql_trace = context.get("sql_trace", False) # default no trace
sql_module = context.get("sql_module", 'Empty') # in case global context not ready
sql_model = context.get("sql_model", 'Empty')
if sql_module == 'Empty':
check_module = False
elif not sql_module: # leave module emtpy to trace all modules
check_module = True
else:
check_module = self._module in sql_module
if sql_model == 'Empty':
check_model = False
elif not sql_model: # leave model empty to trace all models
check_model = True
else:
check_model = self._name in sql_model
if sql_trace and check_module and check_model:
query = self._where_calc(cr, user, args, context=context)
self._apply_ir_rules(cr, user, query, 'read', context=context)
order_by = self._generate_order_by(order, query)
from_clause, where_clause, where_clause_params = query.get_sql()
where_str = where_clause and (" WHERE %s" % where_clause) or ''
limit_str = limit and ' limit %d' % limit or ''
offset_str = offset and ' offset %d' % offset or ''
query_str = 'SELECT "%s".id FROM ' % self._table + from_clause + where_str + order_by + limit_str + offset_str
cr.execute("""INSERT INTO sqltrace (module,model,statement,user_id,sql,parameters, result, duration, date_update)
VALUES (%s,%s, %s, %s,%s, %s, %s ,%s, (now() at time zone 'UTC'))""",
(self._module, self._name, 'select', user, query_str, reduce(lambda x, y: str(x)+' ,'+str(y),
where_clause_params or ['']), str(result or ['']), duration.seconds))
return result
orm.BaseModel._search = _search

[b]sqltrace.py[/b]
from openerp import models, fields, api, _

class SQLTrace(models.Model):
""" workstation """
_name = 'sqltrace'
_description = 'SQL Trace'

module = fields.Char()
model = fields.Char()
statement = fields.Char()
user_id = fields.Many2one('res.users')
sql = fields.Text()
parameters = fields.Text()
result = fields.Text()
date_update = fields.Datetime()
duration = fields.Float()

[b]res_users.py[/b]

from openerp import models, fields, api, _

class res_users(models.Model):
_inherit = 'res.users'

context_sql_trace = fields.Boolean('Activate SQL Trace?')
context_sql_module = fields.Char('Modules for SQL Trace')
context_sql_model = fields.Char('Models for SQL Trace')

res_users_view.xml
<openerp>
<data>
<record model="ir.ui.view" id="sqltrace_user_view">
<field name="name">sqltrace.user.view.inherit</field>
<field name="model">res.users</field>
<field name="inherit_id" ref="base.view_users_form"/>
<field name="priority" eval="20"/>
<field name="arch" type="xml">
<field name="tz" position="after">
<group string="SQL Trace" name="SQLTrace">
<field name="context_sql_trace"/>
<field name="context_sql_module"/>
<field name="context_sql_model"/>
</group>
</field>
</field>
</record>
</data>
</openerp>

重点分享
1. 在用户表中增加的栏位以context_开头的话，就会被自动加到全局的context中，有缓存的作用
2. 因为_search会在context可用前被调用，所以需要判断context是否可用
3. 解析SQL语句内容的部分从原models.py中的_search抄过来的

fisher

因为是在users表里增加了栏位，新模块加载未安装前可能会报用户表里的新栏位不存在的错误，这时可以直接在数据库里先手工增加这些栏位就可以了。

卓忆

谢谢分享，Mark之，
另外祝新年进步！身体健康！财源广进！

mrshelly

赞...................!!!!