« back to all changes in this revision
Viewing changes to ZPsycopgDA/db.py
- browse files at revision 2
- compare with another revision
- download diff
- download tarball
- view history from revision 2
- Committer: Federico Di Gregorio
- Date: 2004-10-29 16:08:31 UTC
- Revision ID: fog-3d8587a243fa1d89012217ea99d2486813eb1955
SVN repo up to date (1.1.16pre1).
added
removed
ZPsycopgDA/db.py
1
# ZPsycopgDA/db.py - query execution
2
#
3
# Copyright (C) 2004 Federico Di Gregorio <fog@initd.org>
4
#
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
8
# version.
9
#
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.
13
#
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.
17
#
18
# See the LICENSE file for details.
1
##############################################################################
2
#
3
# Zope Public License (ZPL) Version 1.0
4
# -------------------------------------
5
#
6
# Copyright (c) Digital Creations. All rights reserved.
7
#
8
# This license has been certified as Open Source(tm).
9
#
10
# Redistribution and use in source and binary forms, with or without
11
# modification, are permitted provided that the following conditions are
12
# met:
13
#
14
# 1. Redistributions in source code must retain the above copyright
15
# notice, this list of conditions, and the following disclaimer.
16
#
17
# 2. Redistributions in binary form must reproduce the above copyright
18
# notice, this list of conditions, and the following disclaimer in
19
# the documentation and/or other materials provided with the
20
# distribution.
21
#
22
# 3. Digital Creations requests that attribution be given to Zope
23
# in any manner possible. Zope includes a "Powered by Zope"
24
# button that is installed by default. While it is not a license
25
# violation to remove this button, it is requested that the
26
# attribution remain. A significant investment has been put
27
# into Zope, and this effort will continue if the Zope community
28
# continues to grow. This is one way to assure that growth.
29
#
30
# 4. All advertising materials and documentation mentioning
31
# features derived from or use of this software must display
32
# the following acknowledgement:
33
#
34
# "This product includes software developed by Digital Creations
35
# for use in the Z Object Publishing Environment
36
# (http://www.zope.org/)."
37
#
38
# In the event that the product being advertised includes an
39
# intact Zope distribution (with copyright and license included)
40
# then this clause is waived.
41
#
42
# 5. Names associated with Zope or Digital Creations must not be used to
43
# endorse or promote products derived from this software without
44
# prior written permission from Digital Creations.
45
#
46
# 6. Modified redistributions of any form whatsoever must retain
47
# the following acknowledgment:
48
#
49
# "This product includes software developed by Digital Creations
50
# for use in the Z Object Publishing Environment
51
# (http://www.zope.org/)."
52
#
53
# Intact (re-)distributions of any official Zope release do not
54
# require an external acknowledgement.
55
#
56
# 7. Modifications are encouraged but must be packaged separately as
57
# patches to official Zope releases. Distributions that do not
58
# clearly separate the patches from the original work must be clearly
59
# labeled as unofficial distributions. Modifications which do not
60
# carry the name Zope may be packaged in any form, as long as they
61
# conform to all of the clauses above.
62
#
63
#
64
# Disclaimer
65
#
66
# THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
67
# EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
68
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
69
# PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
70
# CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
71
# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
72
# LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
73
# USE, DATA, OR PROFITS; OR BUSINESS INTERUPTION) HOWEVER CAUSED AND
74
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
75
# OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
76
# OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
77
# SUCH DAMAGE.
78
#
79
#
80
# This software consists of contributions made by Digital Creations and
81
# many individuals on behalf of Digital Creations. Specific
82
# attributions are listed in the accompanying credits file.
83
#
84
##############################################################################
85
86
'''$Id: db.py 532 2004-09-27 18:35:25Z fog $'''
87
__version__='$Revision: 1.31.2.7 $'[11:-2]
88
19
89
20
90
from Shared.DC.ZRDB.TM import TM
21
91
from Shared.DC.ZRDB import dbi_db
22
92
23
from ZODB.POSException import ConflictError
24
25
import time
93
import string, sys
94
from string import strip, split, find
95
from time import time
96
from types import ListType
26
97
import site
27
import pool
28
98
29
99
import psycopg
30
from psycopg.extensions import INTEGER, LONGINTEGER, FLOAT, BOOLEAN
31
from psycopg import NUMBER, STRING, ROWID, DATETIME
32
33
34
35
# the DB object, managing all the real query work
36
37
class DB(TM, dbi_db.DB):
38
100
from psycopg import NUMBER, STRING, INTEGER, FLOAT, DATETIME
101
from psycopg import BOOLEAN, ROWID, LONGINTEGER
102
from ZODB.POSException import ConflictError
103
104
class DB(TM,dbi_db.DB):
105
39
106
_p_oid = _p_changed = _registered = None
40
107
41
def __init__(self, dsn, tilevel, enc='utf-8'):
42
self.dsn = dsn
108
def __init__(self, connection, tilevel, enc='utf-8'):
109
self.connection = connection
43
110
self.tilevel = tilevel
44
self.encoding = enc
111
self.encoding = enc
112
self.db = self.connect(self.connection)
45
113
self.failures = 0
46
114
self.calls = 0
47
115
48
def getconn(self, create=True):
49
conn = pool.getconn(self.dsn)
50
conn.set_isolation_level(int(self.tilevel))
51
return conn
52
53
def putconn(self, close=False):
54
try:
55
conn = pool.getconn(self.dsn, False)
56
except AttributeError:
57
pass
58
pool.putconn(self.dsn, conn, close)
59
60
def getcursor(self):
61
conn = self.getconn()
62
return conn.cursor()
63
116
def connect(self, connection):
117
o = psycopg.connect(connection)
118
o.set_isolation_level(int(self.tilevel))
119
return o
120
64
121
def _finish(self, *ignored):
65
try:
66
conn = self.getconn(False)
67
conn.commit()
68
self.putconn()
69
except AttributeError:
70
pass
122
if hasattr(self, 'db') and self.db:
123
self.db.commit()
71
124
72
125
def _abort(self, *ignored):
73
try:
74
conn = self.getconn(False)
75
conn.rollback()
76
self.putconn()
77
except AttributeError:
78
pass
79
80
def open(self):
81
# this will create a new pool for our DSN if not already existing,
82
# then get and immediately release a connection
83
self.getconn()
84
self.putconn()
85
86
def close(self):
87
# FIXME: if this connection is closed we flush all the pool associated
88
# with the current DSN; does this makes sense?
89
pool.flushpool(self.dsn)
90
91
def sortKey(self):
92
return 1
93
94
## tables and rows ##
95
126
if hasattr(self, 'db') and self.db:
127
self.db.rollback()
128
129
def _cursor(self):
130
"""Obtains a cursor in a safe way."""
131
if not hasattr(self, 'db') or not self.db:
132
self.db = self.connect(self.connection)
133
return self.db.cursor()
134
96
135
def tables(self, rdb=0, _care=('TABLE', 'VIEW')):
97
136
self._register()
98
c = self.getcursor()
99
c.execute(
100
"SELECT t.tablename AS NAME, 'TABLE' AS TYPE "
101
" FROM pg_tables t WHERE tableowner <> 'postgres' "
102
"UNION SELECT v.viewname AS NAME, 'VIEW' AS TYPE "
103
" FROM pg_views v WHERE viewowner <> 'postgres' "
104
"UNION SELECT t.tablename AS NAME, 'SYSTEM_TABLE\' AS TYPE "
105
" FROM pg_tables t WHERE tableowner = 'postgres' "
106
"UNION SELECT v.viewname AS NAME, 'SYSTEM_TABLE' AS TYPE "
107
"FROM pg_views v WHERE viewowner = 'postgres'")
108
res = []
137
c = self._cursor()
138
c.execute('SELECT t.tablename AS NAME, '
139
'\'TABLE\' AS TYPE FROM pg_tables t '
140
'WHERE tableowner <> \'postgres\' '
141
'UNION SELECT v.viewname AS NAME, '
142
'\'VIEW\' AS TYPE FROM pg_views v '
143
'WHERE viewowner <> \'postgres\' '
144
'UNION SELECT t.tablename AS NAME, '
145
'\'SYSTEM_TABLE\' AS TYPE FROM pg_tables t '
146
'WHERE tableowner = \'postgres\' '
147
'UNION SELECT v.viewname AS NAME, '
148
'\'SYSTEM_TABLE\' AS TYPE FROM pg_views v '
149
'WHERE viewowner = \'postgres\' ' )
150
r = []
151
a = r.append
109
152
for name, typ in c.fetchall():
110
153
if typ in _care:
111
res.append({'TABLE_NAME': name, 'TABLE_TYPE': typ})
112
self.putconn()
113
return res
154
a({'TABLE_NAME': name, 'TABLE_TYPE': typ})
155
c.close()
156
return r
114
157
115
158
def columns(self, table_name):
116
159
self._register()
117
c = self.getcursor()
160
c = self._cursor()
118
161
try:
119
r = c.execute('SELECT * FROM "%s" WHERE 1=0' % table_name)
162
r = c.execute('select * from "%s" where 1=0' % table_name)
120
163
except:
121
164
return ()
122
res = []
123
for name, type, width, ds, p, scale, null_ok in c.description:
165
desc = c.description
166
r = []
167
a = r.append
168
for name, type, width, ds, p, scale, null_ok in desc:
124
169
if type == NUMBER:
125
170
if type == INTEGER:
126
171
type = INTEGER
135
180
type = DATETIME
136
181
else:
137
182
type = STRING
138
139
res.append({'Name': name,
140
'Type': type.name,
141
'Precision': 0,
142
'Scale': 0,
143
'Nullable': 0})
144
self.putconn()
145
return res
146
147
## query execution ##
183
a({ 'Name': name,
184
'Type': type.name,
185
'Precision': 0,
186
'Scale': 0,
187
'Nullable': 0})
188
return r
148
189
149
190
def query(self, query_string, max_rows=None, query_data=None):
150
191
self._register()
151
192
self.calls = self.calls+1
152
193
153
194
desc = ()
154
res = []
195
result = []
155
196
nselects = 0
156
157
c = self.getcursor()
158
197
198
c = self._cursor()
199
159
200
try:
160
for qs in [x for x in query_string.split('\0') if x]:
201
for qs in filter(None, map(strip, split(query_string, '\0'))):
161
202
if type(qs) == unicode:
162
203
if self.encoding:
163
204
qs = qs.encode(self.encoding)
164
205
try:
165
206
if (query_data):
166
c.execute(qs, query_data)
207
r = c.execute(qs, query_data)
167
208
else:
168
c.execute(qs)
169
except (psycopg.ProgrammingError, psycopg.IntegrityError), e:
170
if e.args[0].find("concurrent update") > -1:
209
r = c.execute(qs)
210
except (psycopg.ProgrammingError,psycopg.IntegrityError), perr:
211
if perr.args[0].find("concurrent update") > -1:
171
212
raise ConflictError
172
raise e
213
raise perr
173
214
if c.description is not None:
174
nselects += 1
215
nselects = nselects + 1
175
216
if c.description != desc and nselects > 1:
176
raise psycopg.ProgrammingError(
177
'multiple selects in single query not allowed')
217
raise 'Query Error', \
218
'Multiple select schema are not allowed'
178
219
if max_rows:
179
res = c.fetchmany(max_rows)
220
result = c.fetchmany(max_rows)
180
221
else:
181
res = c.fetchall()
222
result = c.fetchall()
182
223
desc = c.description
183
224
self.failures = 0
184
225
185
226
except StandardError, err:
186
227
self._abort()
187
228
raise err
206
247
'null': null_ok,
207
248
})
208
249
209
return items, res
250
return items, result
251
252
def close(self):
253
"""Close the connection."""
254
self.db.close()
255
self.db = None
256
257
def sortKey(self):
258
"""Zope 2.6 added this one."""
259
return 1
Loggerhead is a web-based interface for Breezy
Version: 2.0.1