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.
38
import sys, string, types
39
from gnue.common.datasources import GDataObjects, GConditions, GConnections
40
from gnue.common.apps import GDebug
45
except ImportError, message:
46
tmsg = u_("Driver not installed: win32all ODBC driver\n\n[%s") % message
47
raise GConnections.AdapterNotInstalled, tmsg
52
class ODBC_RecordSet(GDataObjects.RecordSet):
53
def _postChanges(self, recordNumber=None):
54
if not self.isPending(): return
56
statement = self._buildDeleteStatement()
57
elif self._insertFlag:
58
statement = self._buildInsertStatement()
59
elif self._updateFlag:
60
statement = self._buildUpdateStatement()
62
GDebug.printMesg(9, "_postChanges: statement=%s" % statement)
65
self._parent._update_cursor.execute(statement)
67
# Set _initialData to be the just-now posted values
68
if not self._deleteFlag:
69
self._initialData = {}
70
for key in self._fields.keys():
71
self._initialData[key] = self._fields[key]
73
except self._parent._dataObject._connection._DatabaseError, err:
74
raise GDataObjects.ConnectionError, err
83
# If a vendor can do any of these more efficiently (i.e., use a known
84
# PRIMARY KEY or ROWID, then override these methods. Otherwise, leave
85
# as default. Note that these functions are specific to DB-SIG based
86
# drivers (i.e., these functions are not in the base RecordSet class)
88
def _buildDeleteStatement(self):
89
if self._initialData.has_key(self._parent._primaryIdField):
90
where = [self._parent._primaryIdFormat % \
91
self._initialData[self._parent._primaryIdField] ]
94
for field in self._initialData.keys():
95
if self._parent.isFieldBound(field):
96
if self._initialData[field] == None:
97
where.append ("%s IS NULL" % field)
99
where.append ("%s='%s'" % (field, self._initialData[field]))
101
statement = "DELETE FROM %s WHERE %s" % \
102
(self._parent._dataObject.table, string.join(where,' AND ') )
105
def _buildInsertStatement(self):
109
# TODO: This should actually only insert modified fields.
110
# TODO: Unfortunately, self._modifiedFlags is not being
111
# TODO: set for new records (!@#$)
112
#for field in self._modifiedFlags.keys():
114
for field in self._fields.keys():
115
if self._parent.isFieldBound(field):
116
fields.append (field)
117
if self._fields[field] == None or self._fields[field] == '':
118
vals.append ("NULL") # % (self._fields[field]))
121
if self._parent._fieldTypes[field] == 'number':
122
vals.append ("%s" % (self._fields[field]))
124
vals.append ("'%s'" % (self._fields[field]))
126
vals.append ("%s" % (self._fields[field]))
128
return "INSERT INTO %s (%s) VALUES (%s)" % \
129
(self._parent._dataObject.table, string.join(fields,','), \
130
string.join(vals,',') )
133
def _buildUpdateStatement(self):
135
for field in self._modifiedFlags.keys():
137
if self._parent._fieldTypes[field] == 'number':
138
updates.append ("%s=%s" % (field, self._fields[field]))
140
updates.append ("%s='%s'" % (field, self._fields[field]))
142
updates.append ("%s='%s'" % (field, self._fields[field]))
144
if self._initialData.has_key(self._parent._primaryIdField):
145
where = [self._parent._primaryIdFormat % \
146
self._initialData[self._parent._primaryIdField] ]
149
for field in self._initialData.keys():
150
if self._initialData[field] == None:
151
where.append ("%s IS NULL" % field)
154
if self._parent._fieldTypes[field] == 'number':
155
where.append ("%s=%s" % (field, self._initialData[field]))
157
where.append ("%s='%s'" % (field, self._initialData[field]))
159
where.append ("%s='%s'" % (field, self._initialData[field]))
161
return "UPDATE %s SET %s WHERE %s" % \
162
(self._parent._dataObject.table, string.join(updates,','), \
163
string.join(where,' AND ') )
166
class ODBC_ResultSet(GDataObjects.ResultSet):
167
def __init__(self, dataObject, cursor=None, \
168
defaultValues={}, masterRecordSet=None):
169
GDataObjects.ResultSet.__init__(
170
self,dataObject,cursor,defaultValues,masterRecordSet)
171
self._recordSetClass = ODBC_RecordSet
172
self._fieldNames = None
173
self._fieldTypes = {}
175
# self._recordCount = cursor.rowcount > 0 and cursor.rowcount or 0
176
self._recordCount = 0
178
# If a DB driver supports a unique identifier for rows,
179
# list it here. _primaryIdField is the field name (lower case)
180
# that would appear in the recordset (note that this can be
181
# a system generated format). If a primary id is supported,
182
# _primaryIdFormat is the WHERE clause to be used. It will have
183
# the string % (fieldvalue) format applied to it.
184
self._primaryIdField = None
185
self._primaryIdFormat = "__gnue__ = '%s'"
187
GDebug.printMesg(9, 'ResultSet created')
189
def _loadNextRecord(self):
194
rs = self._cursor.fetchone()
195
except self._dataObject._connection._DatabaseError, err:
197
# TODO: It seems that popy does what the other drivers don't
198
# TODO: and raises this error ALOT need to find out why
199
# raise GDataObjects.ConnectionError, err
202
if not self._fieldNames:
203
self._fieldNames = []
204
for t in (self._cursor.description):
205
self._fieldNames.append (string.lower(t[0]))
206
self._fieldTypes[string.lower(t[0])] = (string.lower(t[1]))
210
dict[self._fieldNames[i]] = f
212
self._cachedRecords.append (self._recordSetClass(parent=self, \
221
class ODBC_DataObject(GDataObjects.DataObject):
223
conditionElements = {
224
'add': (2, 999, '(%s)', '+' ),
225
'sub': (2, 999, '(%s)', '-' ),
226
'mul': (2, 999, '(%s)', '*' ),
227
'div': (2, 999, '(%s)', '/' ),
228
'and': (1, 999, '(%s)', ' AND ' ),
229
'or': (2, 999, '(%s)', ' OR ' ),
230
'not': (1, 1, '(NOT %s)', None ),
231
'negate': (1, 1, '-%s', None ),
232
'eq': (2, 2, '(%s = %s)', None ),
233
'ne': (2, 2, '(%s != %s)', None ),
234
'gt': (2, 2, '(%s > %s)', None ),
235
'ge': (2, 2, '(%s >= %s)', None ),
236
'lt': (2, 2, '(%s < %s)', None ),
237
'le': (2, 2, '(%s <= %s)', None ),
238
'like': (2, 2, '%s LIKE %s', None ),
239
'notlike': (2, 2, '%s NOT LIKE %s', None ),
240
'between': (3, 3, '%s BETWEEN %s AND %s', None ) }
242
def __init__(self, strictQueryCount=1):
243
GDataObjects.DataObject.__init__(self)
245
GDebug.printMesg (9,"DB-SIG database driver backend initializing")
247
self._resultSetClass = ODBC_ResultSet
248
self._DatabaseError = None
249
self._strictQueryCount = strictQueryCount
252
# This should be over-ridden only if driver needs more than user/pass
253
def getLoginFields(self):
254
return [['_username', 'User Name',0],['_password', 'Password',1]]
257
def connect(self, connectData={}):
259
GDebug.printMesg(9,"ODBC database driver initializing")
260
self._DatabaseError = odbc.error
263
service = connectData['service']
268
self._dataConnection = odbc.odbc( "%s/%s/%s" % (
270
connectData['_username'],
271
connectData['_password']))
273
except dbi.opError, value:
274
raise GDataObjects.LoginError, value
276
except self._DatabaseError, value:
277
raise GDataObjects.LoginError, value
283
# Schema (metadata) functions
286
# TODO: See postgresql for an example of what these functions do.
288
# Return a list of the types of Schema objects this driver provides
289
def getSchemaTypes(self):
290
return None # [('table',_('Tables'),1)]
292
# Return a list of Schema objects
293
def getSchemaList(self, type=None):
296
# Find a schema object with specified name
297
def getSchemaByName(self, name, type=None):
300
def _postConnect(self):
301
self.triggerExtensions = TriggerExtensions(self._dataConnection)
303
def _createResultSet(self, conditions={}, readOnly=0, masterRecordSet=None,sql=""):
305
cursor = self._dataConnection.cursor()
306
cursor.execute(self._buildQuery(conditions))
308
except dbi.progError, err:
309
raise GDataObjects.ConnectionError, err
311
except self._DatabaseError, err:
312
raise GDataObjects.ConnectionError, err
313
rs = self._resultSetClass(self, cursor=cursor, masterRecordSet=masterRecordSet)
315
# pull a record count for the upcomming query
316
if self._strictQueryCount:
317
rs._recordCount = self._getQueryCount(conditions)
320
rs._readonly = readOnly
324
def _getQueryCount(self,conditions={}):
325
cursor = self._dataConnection.cursor()
327
cursor.execute(self._buildQueryCount(conditions))
328
rs = cursor.fetchone()
332
def _buildQueryCount(self, conditions={}):
333
q = "SELECT count(*) FROM %s%s" % (self.table, self._conditionToSQL(conditions))
335
GDebug.printMesg(9,q)
340
GDebug.printMesg (9,"DB-SIG database driver: commit()")
343
self._dataConnection.commit()
344
except self._DatabaseError, value:
345
raise GDataObjects.ConnectionError, value
347
self._beginTransaction()
350
GDebug.printMesg (9,"DB-SIG database driver: rollback()")
353
self._dataConnection.rollback()
355
pass # I'm SURE this isn't right (jcater)
356
# But not all db's support transactions
358
self._beginTransaction()
361
def _buildQuery(self, conditions={},forDetail=None,additionalSQL=""):
365
# Used to convert a condition tree to an sql where clause
366
def _conditionToSQL (self, condition):
367
if condition == {} or condition == None:
369
elif type(condition) == types.DictType:
370
cond = GConditions.buildConditionFromDict(condition)
374
if not len(cond._children):
376
elif len(cond._children) > 1:
377
chillun = cond._children
379
_and = GConditions.GCand(cond)
380
_and._children = chillun
383
where = " WHERE (%s)" % (self.__conditionToSQL (cond._children[0]))
384
GDebug.printMesg(9, where)
387
# Used internally by _conditionToSQL
388
def __conditionToSQL (self, element):
389
if type(element) != types.InstanceType:
390
return "%s" % element
392
otype = string.lower(element._type[2:])
393
if otype == 'cfield':
394
return "%s" % element.name
395
elif otype == 'cconst':
396
if element.value == None:
398
elif element.type == 'number':
399
return "%s" % element.value
401
return "'%s'" % element.value
402
elif otype == 'param':
403
v = element.getValue()
404
return (v == None and "NULL") or ("'%s'" % element.getValue())
405
elif self.conditionElements.has_key(otype):
406
for i in range(0, len(element._children)):
407
element._children[i] = self.__conditionToSQL(element._children[i])
408
if len(element._children) < self.conditionElements[otype][0]:
409
tmsg = u_('Condition element "%(element)s" expects at least '
410
'%(expected)s arguments; found %(found)s') \
411
% {'element' : otype,
412
'expected': self.conditionElements[otype][0],
413
'found' : len(element._children)}
414
raise GConditions.ConditionError, tmsg
416
if len(element._children) > self.conditionElements[otype][1]:
417
tmsg = u_('Condition element "%(element)s" expects at most '
418
'%(expected)s arguments; found %(found)s') \
419
% {'element' : otype,
420
'expected': self.conditionElements[otype][1],
421
'found' : len (element._children)}
422
raise GConditions.ConditionError, tmsg
423
if self.conditionElements[otype][3] == None:
424
return self.conditionElements[otype][2] % tuple(element._children)
426
return self.conditionElements[otype][2] % \
427
(string.join(element._children, self.conditionElements[otype][3]))
429
tmsg = u_('Condition clause "%s" is not supported by this db driver.') % otype
430
raise GConditions.ConditionNotSupported, tmsg
432
# Code necessary to force the connection into transaction mode...
433
# this is usually not necessary (MySQL is one of few DBs that must force)
434
def _beginTransaction(self):
438
class ODBC_DataObject_Object(ODBC_DataObject):
439
def _buildQuery(self, conditions={}):
440
GDebug.printMesg(9,'Implicit Fields: %s' % self._fieldReferences)
441
if len(self._fieldReferences):
442
q = "SELECT %s FROM %s%s" % \
443
(string.join(self._fieldReferences.keys(),","), self.table,
444
self._conditionToSQL(conditions))
446
q = "SELECT * FROM %s%s" % (self.table, self._conditionToSQL(conditions))
448
if hasattr(self,'order_by'):
449
q = "%s ORDER BY %s " % (q, self.order_by)
451
GDebug.printMesg(9,q)
458
# Extensions to Trigger Namespaces
460
class TriggerExtensions:
462
def __init__(self, connection):
463
self.__connection = connection
468
######################################
470
# The following hashes describe
471
# this driver's characteristings.
473
######################################
476
# All datasouce "types" and corresponding DataObject class
478
supportedDataObjects = {
479
'object': ODBC_DataObject_Object,
480
# 'sql': ODBC_DataObject_SQL