1 # ZPsycopgDA/db.py - query execution
3 # Copyright (C) 2004 Federico Di Gregorio <fog@initd.org>
5 # This program is free software; you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by the
7 # Free Software Foundation; either version 2, or (at your option) any later
10 # Or, at your option this program (ZPsycopgDA) can be distributed under the
11 # Zope Public License (ZPL) Version 1.0, as published on the Zope web site,
12 # http://www.zope.org/Resources/ZPL.
14 # This program is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY
16 # or FITNESS FOR A PARTICULAR PURPOSE.
18 # See the LICENSE file for details.
20 from Shared.DC.ZRDB.TM import TM
21 from Shared.DC.ZRDB import dbi_db
23 from ZODB.POSException import ConflictError
29 from psycopg2.extensions import INTEGER, LONGINTEGER, FLOAT, BOOLEAN, DATE, TIME
30 from psycopg2 import NUMBER, STRING, ROWID, DATETIME
33 # the DB object, managing all the real query work
35 class DB(TM, dbi_db.DB):
37 _p_oid = _p_changed = _registered = None
39 def __init__(self, dsn, tilevel, enc='utf-8'):
41 self.tilevel = tilevel
47 def getconn(self, create=True):
48 conn = pool.getconn(self.dsn)
49 conn.set_isolation_level(int(self.tilevel))
52 def putconn(self, close=False):
54 conn = pool.getconn(self.dsn, False)
55 except AttributeError:
57 pool.putconn(self.dsn, conn, close)
63 def _finish(self, *ignored):
65 conn = self.getconn(False)
68 except AttributeError:
71 def _abort(self, *ignored):
73 conn = self.getconn(False)
76 except AttributeError:
80 # this will create a new pool for our DSN if not already existing,
81 # then get and immediately release a connection
86 # FIXME: if this connection is closed we flush all the pool associated
87 # with the current DSN; does this makes sense?
88 pool.flushpool(self.dsn)
93 def make_mappings(self):
94 """Generate the mappings used later by self.convert_description()."""
95 self.type_mappings = {}
96 for t, s in [(INTEGER,'i'), (LONGINTEGER, 'i'), (NUMBER, 'n'),
97 (BOOLEAN,'n'), (ROWID, 'i'),
98 (DATETIME, 'd'), (DATE, 'd'), (TIME, 'd')]:
100 self.type_mappings[v] = (t, s)
102 def convert_description(self, desc, use_psycopg_types=False):
103 """Convert DBAPI-2.0 description field to Zope format."""
105 for name, typ, width, ds, p, scale, null_ok in desc:
106 m = self.type_mappings.get(typ, (STRING, 's'))
109 'type': use_psycopg_types and m[0] or m[1],
117 ## tables and rows ##
119 def tables(self, rdb=0, _care=('TABLE', 'VIEW')):
123 "SELECT t.tablename AS NAME, 'TABLE' AS TYPE "
124 " FROM pg_tables t WHERE tableowner <> 'postgres' "
125 "UNION SELECT v.viewname AS NAME, 'VIEW' AS TYPE "
126 " FROM pg_views v WHERE viewowner <> 'postgres' "
127 "UNION SELECT t.tablename AS NAME, 'SYSTEM_TABLE\' AS TYPE "
128 " FROM pg_tables t WHERE tableowner = 'postgres' "
129 "UNION SELECT v.viewname AS NAME, 'SYSTEM_TABLE' AS TYPE "
130 "FROM pg_views v WHERE viewowner = 'postgres'")
132 for name, typ in c.fetchall():
134 res.append({'TABLE_NAME': name, 'TABLE_TYPE': typ})
138 def columns(self, table_name):
142 r = c.execute('SELECT * FROM "%s" WHERE 1=0' % table_name)
146 return self.convert_description(c.description, True)
148 ## query execution ##
150 def query(self, query_string, max_rows=None, query_data=None):
152 self.calls = self.calls+1
161 for qs in [x for x in query_string.split('\0') if x]:
162 if type(qs) == unicode:
164 qs = qs.encode(self.encoding)
167 c.execute(qs, query_data)
170 except psycopg2.OperationalError, e:
178 c.execute(qs, query_data)
181 except (psycopg2.ProgrammingError,
182 psycopg2.IntegrityError), e:
183 if e.args[0].find("concurrent update") > -1:
186 except (psycopg2.ProgrammingError, psycopg2.IntegrityError), e:
187 if e.args[0].find("concurrent update") > -1:
190 if c.description is not None:
192 if c.description != desc and nselects > 1:
193 raise psycopg2.ProgrammingError(
194 'multiple selects in single query not allowed')
196 res = c.fetchmany(max_rows)
202 except StandardError, err:
206 return self.convert_description(desc), res