2
# This file is part of GNU Enterprise.
4
# GNU Enterprise is free software; you can redistribute it
5
# and/or modify it under the terms of the GNU General Public
6
# License as published by the Free Software Foundation; either
7
# version 2, or (at your option) any later version.
9
# GNU Enterprise is distributed in the hope that it will be
10
# useful, but WITHOUT ANY WARRANTY; without even the implied
11
# warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
12
# PURPOSE. See the GNU General Public License for more details.
14
# You should have received a copy of the GNU General Public
15
# License along with program; see the file COPYING. If not,
16
# write to the Free Software Foundation, Inc., 59 Temple Place
17
# - Suite 330, Boston, MA 02111-1307, USA.
19
# Copyright 2000-2005 Free Software Foundation
25
# Driver to provide access to data via the public domain win32all ODBC Driver
28
# Only works under Win32... requires the win32all extensions.
29
# (http://aspn.activestate.com/ASPN/Downloads/ActivePython/Extensions/Win32all)
31
# Supported attributes (via connections.conf or <database> tag)
33
# service= This is the ODBC DSN= string to use.
37
__all__ = ['Connection']
40
#### IF YOU MODIFY ANY CONNECTION ####
41
#### ATTRIBUTES, PLEASE UPDATE info.py ####
44
import sys, string, types
45
from gnue.common.datasources import GDataObjects, GConditions, GConnections
46
from gnue.common.apps import GDebug
52
raise GConnections.DependencyError, ('win32all ODBC driver', None)
55
class ODBC_RecordSet(GDataObjects.RecordSet):
56
def _postChanges(self):
57
if not self.isPending(): return
59
statement = self._buildDeleteStatement()
60
elif self._insertFlag:
61
statement = self._buildInsertStatement()
62
elif self._updateFlag:
63
statement = self._buildUpdateStatement()
65
GDebug.printMesg(9, "_postChanges: statement=%s" % statement)
68
self._parent._update_cursor.execute(statement)
70
# Set _initialData to be the just-now posted values
71
if not self._deleteFlag:
72
self._initialData = {}
73
for key in self._fields.keys():
74
self._initialData[key] = self._fields[key]
76
except self._parent._dataObject._connection._DatabaseError, err:
77
raise GDataObjects.ConnectionError, err
86
# If a vendor can do any of these more efficiently (i.e., use a known
87
# PRIMARY KEY or ROWID, then override these methods. Otherwise, leave
88
# as default. Note that these functions are specific to DB-SIG based
89
# drivers (i.e., these functions are not in the base RecordSet class)
91
def _buildDeleteStatement(self):
92
if self._initialData.has_key(self._parent._primaryIdField):
93
where = [self._parent._primaryIdFormat % \
94
self._initialData[self._parent._primaryIdField] ]
97
for field in self._initialData.keys():
98
if self._parent.isFieldBound(field):
99
if self._initialData[field] == None:
100
where.append ("%s IS NULL" % field)
102
where.append ("%s='%s'" % (field, self._initialData[field]))
104
statement = "DELETE FROM %s WHERE %s" % \
105
(self._parent._dataObject.table, string.join(where,' AND ') )
108
def _buildInsertStatement(self):
112
# TODO: This should actually only insert modified fields.
113
# TODO: Unfortunately, self._modifiedFlags is not being
114
# TODO: set for new records (!@#$)
115
#for field in self._modifiedFlags.keys():
117
for field in self._fields.keys():
118
if self._parent.isFieldBound(field):
119
fields.append (field)
120
if self._fields[field] == None or self._fields[field] == '':
121
vals.append ("NULL") # % (self._fields[field]))
124
if self._parent._fieldTypes[field] == 'number':
125
vals.append ("%s" % (self._fields[field]))
127
vals.append ("'%s'" % (self._fields[field]))
129
vals.append ("%s" % (self._fields[field]))
131
return "INSERT INTO %s (%s) VALUES (%s)" % \
132
(self._parent._dataObject.table, string.join(fields,','), \
133
string.join(vals,',') )
136
def _buildUpdateStatement(self):
138
for field in self._modifiedFlags.keys():
140
if self._parent._fieldTypes[field] == 'number':
141
updates.append ("%s=%s" % (field, self._fields[field]))
143
updates.append ("%s='%s'" % (field, self._fields[field]))
145
updates.append ("%s='%s'" % (field, self._fields[field]))
147
if self._initialData.has_key(self._parent._primaryIdField):
148
where = [self._parent._primaryIdFormat % \
149
self._initialData[self._parent._primaryIdField] ]
152
for field in self._initialData.keys():
153
if self._initialData[field] == None:
154
where.append ("%s IS NULL" % field)
157
if self._parent._fieldTypes[field] == 'number':
158
where.append ("%s=%s" % (field, self._initialData[field]))
160
where.append ("%s='%s'" % (field, self._initialData[field]))
162
where.append ("%s='%s'" % (field, self._initialData[field]))
164
return "UPDATE %s SET %s WHERE %s" % \
165
(self._parent._dataObject.table, string.join(updates,','), \
166
string.join(where,' AND ') )
169
class ODBC_ResultSet(GDataObjects.ResultSet):
170
def __init__(self, dataObject, cursor=None, \
171
defaultValues={}, masterRecordSet=None):
172
GDataObjects.ResultSet.__init__(
173
self,dataObject,cursor,defaultValues,masterRecordSet)
174
self._recordSetClass = ODBC_RecordSet
175
self._fieldNames = None
176
self._fieldTypes = {}
178
# self._recordCount = cursor.rowcount > 0 and cursor.rowcount or 0
179
self._recordCount = 0
181
# If a DB driver supports a unique identifier for rows,
182
# list it here. _primaryIdField is the field name (lower case)
183
# that would appear in the recordset (note that this can be
184
# a system generated format). If a primary id is supported,
185
# _primaryIdFormat is the WHERE clause to be used. It will have
186
# the string % (fieldvalue) format applied to it.
187
self._primaryIdField = None
188
self._primaryIdFormat = "__gnue__ = '%s'"
190
GDebug.printMesg(9, 'ResultSet created')
192
def _loadNextRecord(self):
197
rs = self._cursor.fetchone()
198
except self._dataObject._connection._DatabaseError, err:
200
# TODO: It seems that popy does what the other drivers don't
201
# TODO: and raises this error ALOT need to find out why
202
# raise GDataObjects.ConnectionError, err
205
if not self._fieldNames:
206
self._fieldNames = []
207
for t in (self._cursor.description):
208
self._fieldNames.append (string.lower(t[0]))
209
self._fieldTypes[string.lower(t[0])] = (string.lower(t[1]))
213
dict[self._fieldNames[i]] = f
215
self._cachedRecords.append (self._recordSetClass(parent=self, \
224
class ODBC_DataObject(GDataObjects.DataObject):
226
conditionElements = {
227
'add': (2, 999, '(%s)', '+' ),
228
'sub': (2, 999, '(%s)', '-' ),
229
'mul': (2, 999, '(%s)', '*' ),
230
'div': (2, 999, '(%s)', '/' ),
231
'and': (1, 999, '(%s)', ' AND ' ),
232
'or': (2, 999, '(%s)', ' OR ' ),
233
'not': (1, 1, '(NOT %s)', None ),
234
'negate': (1, 1, '-%s', None ),
235
'eq': (2, 2, '(%s = %s)', None ),
236
'ne': (2, 2, '(%s != %s)', None ),
237
'gt': (2, 2, '(%s > %s)', None ),
238
'ge': (2, 2, '(%s >= %s)', None ),
239
'lt': (2, 2, '(%s < %s)', None ),
240
'le': (2, 2, '(%s <= %s)', None ),
241
'like': (2, 2, '%s LIKE %s', None ),
242
'notlike': (2, 2, '%s NOT LIKE %s', None ),
243
'between': (3, 3, '%s BETWEEN %s AND %s', None ) }
245
def __init__(self, strictQueryCount=1):
246
GDataObjects.DataObject.__init__(self)
248
GDebug.printMesg (9,"DB-SIG database driver backend initializing")
250
self._resultSetClass = ODBC_ResultSet
251
self._DatabaseError = None
252
self._strictQueryCount = strictQueryCount
255
# This should be over-ridden only if driver needs more than user/pass
256
def getLoginFields(self):
257
return [['_username', 'User Name',0],['_password', 'Password',1]]
260
def connect(self, connectData={}):
262
GDebug.printMesg(9,"ODBC database driver initializing")
263
self._DatabaseError = odbc.error
266
service = connectData['service']
271
self.native = odbc.odbc( "%s/%s/%s" % (
273
connectData['_username'],
274
connectData['_password']))
276
except dbi.opError, value:
277
raise GDataObjects.LoginError, value
279
except self._DatabaseError, value:
280
raise GDataObjects.LoginError, value
286
# Schema (metadata) functions
289
# TODO: See postgresql for an example of what these functions do.
291
# Return a list of the types of Schema objects this driver provides
292
def getSchemaTypes(self):
293
return None # [('table',_('Tables'),1)]
295
# Return a list of Schema objects
296
def getSchemaList(self, type=None):
299
# Find a schema object with specified name
300
def getSchemaByName(self, name, type=None):
303
def _postConnect(self):
304
self.triggerExtensions = TriggerExtensions(self.native)
306
def _createResultSet(self, conditions={}, readOnly=0, masterRecordSet=None,sql=""):
308
cursor = self.native.cursor()
309
cursor.execute(self._buildQuery(conditions))
311
except dbi.progError, err:
312
raise GDataObjects.ConnectionError, err
314
except self._DatabaseError, err:
315
raise GDataObjects.ConnectionError, err
316
rs = self._resultSetClass(self, cursor=cursor, masterRecordSet=masterRecordSet)
318
# pull a record count for the upcomming query
319
if self._strictQueryCount:
320
rs._recordCount = self._getQueryCount(conditions)
323
rs._readonly = readOnly
327
def _getQueryCount(self,conditions={}):
328
cursor = self.native.cursor()
330
cursor.execute(self._buildQueryCount(conditions))
331
rs = cursor.fetchone()
335
def _buildQueryCount(self, conditions={}):
336
q = "SELECT count(*) FROM %s%s" % (self.table, self._conditionToSQL(conditions))
338
GDebug.printMesg(9,q)
343
GDebug.printMesg (9,"DB-SIG database driver: commit()")
347
except self._DatabaseError, value:
348
raise GDataObjects.ConnectionError, value
350
self._beginTransaction()
353
GDebug.printMesg (9,"DB-SIG database driver: rollback()")
356
self.native.rollback()
358
pass # I'm SURE this isn't right (jcater)
359
# But not all db's support transactions
361
self._beginTransaction()
364
def _buildQuery(self, conditions={},forDetail=None,additionalSQL=""):
368
# Used to convert a condition tree to an sql where clause
369
def _conditionToSQL (self, condition):
370
if condition == {} or condition == None:
372
elif type(condition) == types.DictType:
373
cond = GConditions.buildConditionFromDict(condition)
377
if not len(cond._children):
379
elif len(cond._children) > 1:
380
chillun = cond._children
382
_and = GConditions.GCand(cond)
383
_and._children = chillun
386
where = " WHERE (%s)" % (self.__conditionToSQL (cond._children[0]))
387
GDebug.printMesg(9, where)
390
# Used internally by _conditionToSQL
391
def __conditionToSQL (self, element):
392
if type(element) != types.InstanceType:
393
return "%s" % element
395
otype = string.lower(element._type[2:])
396
if otype == 'cfield':
397
return "%s" % element.name
398
elif otype == 'cconst':
399
if element.value == None:
401
elif element.type == 'number':
402
return "%s" % element.value
404
return "'%s'" % element.value
405
elif otype == 'param':
406
v = element.getValue()
407
return (v == None and "NULL") or ("'%s'" % element.getValue())
408
elif self.conditionElements.has_key(otype):
409
for i in range(0, len(element._children)):
410
element._children[i] = self.__conditionToSQL(element._children[i])
411
if len(element._children) < self.conditionElements[otype][0]:
412
tmsg = u_('Condition element "%(element)s" expects at least '
413
'%(expected)s arguments; found %(found)s') \
414
% {'element' : otype,
415
'expected': self.conditionElements[otype][0],
416
'found' : len (element._children)}
417
raise GConditions.ConditionError, tmsg
418
if len(element._children) > self.conditionElements[otype][1]:
419
tmsg = u_('Condition element "%(element)s" expects at most '
420
'%(expected)s arguments; found %(found)s') \
421
% {'element' : otype,
422
'expected': self.conditionElements[otype][1],
423
'found' : len (element._children)}
424
raise GConditions.ConditionError, tmsg
425
if self.conditionElements[otype][3] == None:
426
return self.conditionElements[otype][2] % tuple(element._children)
428
return self.conditionElements[otype][2] % \
429
(string.join(element._children, self.conditionElements[otype][3]))
431
tmsg = u_('Condition clause "%s" is not supported by this db driver.') % otype
432
raise GConditions.ConditionNotSupported, tmsg
434
# Code necessary to force the connection into transaction mode...
435
# this is usually not necessary (MySQL is one of few DBs that must force)
436
def _beginTransaction(self):
440
class ODBC_DataObject_Object(ODBC_DataObject):
441
def _buildQuery(self, conditions={}):
442
GDebug.printMesg(9,'Implicit Fields: %s' % self._fieldReferences)
443
if len(self._fieldReferences):
444
q = "SELECT %s FROM %s%s" % \
445
(string.join(self._fieldReferences.keys(),","), self.table,
446
self._conditionToSQL(conditions))
448
q = "SELECT * FROM %s%s" % (self.table, self._conditionToSQL(conditions))
450
if hasattr(self,'order_by'):
451
q = "%s ORDER BY %s " % (q, self.order_by)
453
GDebug.printMesg(9,q)
460
# Extensions to Trigger Namespaces
462
class TriggerExtensions:
464
def __init__(self, connection):
465
self.__connection = connection
470
######################################
472
# The following hashes describe
473
# this driver's characteristings.
475
######################################
478
# All datasouce "types" and corresponding DataObject class
480
supportedDataObjects = {
481
'object': ODBC_DataObject_Object,
482
# 'sql': ODBC_DataObject_SQL
added
removed
src/datasources/drivers/odbc/wodbc/Connection.py
