1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2008 Tiny SPRL (<http://tiny.be>). All Rights Reserved
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation, either version 3 of the License, or
11 # (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
24 from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT, ISOLATION_LEVEL_SERIALIZABLE
25 from psycopg2.pool import ThreadedConnectionPool
26 from psycopg2.psycopg1 import cursor as psycopg1cursor
28 import psycopg2.extensions
29 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
34 from mx import DateTime as mdt
35 re_from = re.compile('.* from "?([a-zA-Z_0-9]+)"? .*$');
36 re_into = re.compile('.* into "?([a-zA-Z_0-9]+)"? .*$');
38 def log(msg, lvl=netsvc.LOG_DEBUG):
39 logger = netsvc.Logger()
40 logger.notifyChannel('sql', lvl, msg)
49 def __init__(self, pool):
51 self._cnx = pool.getconn()
52 self.autocommit(False)
53 self._obj = self._cnx.cursor(cursor_factory=psycopg1cursor)
54 self.dbname = pool.dbname
56 def execute(self, query, params=None):
60 if isinstance(s, unicode):
61 return s.encode('utf-8')
63 p=map(base_string, params)
64 query = base_string(query)
66 if '%d' in query or '%f' in query:
67 log(queyr, netsvc.LOG_WARNING)
68 log("SQL queries mustn't containt %d or %f anymore. Use only %s", netsvc.LOG_WARNING)
69 query = query.replace('%d', '%s').replace('%f', '%s')
73 log("SQL LOG query: %s" % (query,))
74 log("SQL LOG params: %r" % (p,))
76 res = self._obj.execute(query, p)
80 res_from = re_from.match(query.lower())
82 self.sql_from_log.setdefault(res_from.group(1), [0, 0])
83 self.sql_from_log[res_from.group(1)][0] += 1
84 self.sql_from_log[res_from.group(1)][1] += mdt.now() - now
85 res_into = re_into.match(query.lower())
87 self.sql_into_log.setdefault(res_into.group(1), [0, 0])
88 self.sql_into_log[res_into.group(1)][0] += 1
89 self.sql_into_log[res_into.group(1)][1] += mdt.now() - now
92 def print_log(self, type='from'):
93 log("SQL LOG %s:" % (type,))
95 logs = self.sql_from_log.items()
97 logs = self.sql_into_log.items()
98 logs.sort(lambda x, y: cmp(x[1][1], y[1][1]))
101 log("table: %s: %s/%s" %(r[0], str(r[1][1]), r[1][0]))
103 log("SUM:%s/%d" % (sum, self.count))
107 self.print_log('from')
108 self.print_log('into')
111 # This force the cursor to be freed, and thus, available again. It is
112 # important because otherwise we can overload the server very easily
113 # because of a cursor shortage (because cursors are not garbage
114 # collected as fast as they should). The problem is probably due in
115 # part because browse records keep a reference to the cursor.
117 self._pool.putconn(self._cnx)
119 def autocommit(self, on):
120 self._cnx.set_isolation_level([ISOLATION_LEVEL_SERIALIZABLE, ISOLATION_LEVEL_AUTOCOMMIT][bool(on)])
123 return self._cnx.commit()
126 return self._cnx.rollback()
128 def __getattr__(self, name):
129 return getattr(self._obj, name)
131 class ConnectionPool(object):
132 def __init__(self, pool, dbname):
139 def __getattr__(self, name):
140 return getattr(self._pool, name)
142 class PoolManager(object):
145 maxconn = int(tools.config['db_maxconn']) or 64
148 if PoolManager._dsn is None:
149 PoolManager._dsn = ''
150 for p in ('host', 'port', 'user', 'password'):
151 cfg = tools.config['db_' + p]
153 PoolManager._dsn += '%s=%s ' % (p, cfg)
154 return '%s dbname=%s' % (PoolManager._dsn, db_name)
155 dsn = staticmethod(dsn)
158 if db_name not in PoolManager._pools:
159 logger = netsvc.Logger()
161 logger.notifyChannel('dbpool', netsvc.LOG_INFO, 'Connecting to %s' % (db_name.lower()))
162 PoolManager._pools[db_name] = ConnectionPool(ThreadedConnectionPool(0, PoolManager.maxconn, PoolManager.dsn(db_name)), db_name)
164 logger.notifyChannel('dbpool', netsvc.LOG_CRITICAL, 'Unable to connect to %s: %r' % (db_name, e))
166 return PoolManager._pools[db_name]
167 get = staticmethod(get)
169 def db_connect(db_name, serialize=0):
170 return PoolManager.get(db_name)
172 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: