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
22
# DBSIG2/DataObject.py
26
Generic implementation of dbdriver using Python DB-SIG v2
31
# The classes below are meant to be extended
36
__all__ = ['DataObject','DataObject_SQL','DataObject_Object']
38
from gnue.common.datasources import GConditions, Exceptions
39
from gnue.common.datasources.drivers.Base import DataObject as BaseDataObject
40
from gnue.common.apps import errors
44
from string import join, replace
46
from ResultSet import ResultSet
48
######################################################################
52
class DataObject(BaseDataObject):
54
_escapeSingleQuote = "'"
55
_resultSetClass = ResultSet
58
# The date/time format used in insert/select statements
59
# (based on format used for time.strftime())
60
_dateTimeFormat = "'%c'"
63
# If a DB driver supports a unique identifier for rows,
64
# list it here. _primaryIdField is the field name (lower case)
65
# that would appear in the recordset (note that this can be
66
# a system generated format). If a primary id is supported,
67
# _primaryIdFormat is the WHERE clause to be used. It will have
68
# the string % (fieldvalue) format applied to it.
70
# See Oracle drivers for example
71
_primaryIdField = None # Internal recordset field name (lowercase!!!)
72
_primaryIdSelect = "" # Select clause
73
_primaryIdFormat = "__gnue__ = '%s'" # Where clause format
76
'add': (2, 999, '(%s)', '+' ),
77
'sub': (2, 999, '(%s)', '-' ),
78
'mul': (2, 999, '(%s)', '*' ),
79
'div': (2, 999, '(%s)', '/' ),
80
'and': (1, 999, '(%s)', ' AND ' ),
81
'or': (2, 999, '(%s)', ' OR ' ),
82
'not': (1, 1, '(NOT %s)', None ),
83
'negate': (1, 1, '-%s', None ),
84
'null': (1, 1, '(%s IS NULL)', None ),
85
'notnull': (1, 1, '(%s IS NOT NULL)', None ),
86
'eq': (2, 2, '(%s = %s)', None ),
87
'ne': (2, 2, '(%s != %s)', None ),
88
'gt': (2, 2, '(%s > %s)', None ),
89
'ge': (2, 2, '(%s >= %s)', None ),
90
'lt': (2, 2, '(%s < %s)', None ),
91
'le': (2, 2, '(%s <= %s)', None ),
92
'like': (2, 2, '%s LIKE %s', None ),
93
'notlike': (2, 2, '%s NOT LIKE %s', None ),
94
'between': (3, 3, '%s BETWEEN %s AND %s', None ),
95
'notbetween': (3, 3, '(%s NOT BETWEEN %s AND %s)', None ),
96
# These two are hacks... these are not really valid tags
97
# Used when the 2nd value of EQ or NE is NULL.
98
'__iseq': (2, 2, '(%s IS %s)', None ),
99
'__isne': (2, 2, '(%s IS NOT %s)', None ),
100
'upper': (1, 1, 'UPPER(%s)', None ),
101
'lower': (1, 1, 'LOWER(%s)', None )}
104
def __init__(self, *args, **params):
105
BaseDataObject.__init__(self, *args, **params)
107
gDebug (8, "DB-SIG database driver backend initializing")
111
self._strictQueryCount = True # strictQueryCount
113
self.distinct = False
115
# If possible, this will contain the field names used in the last select
116
# in the same order used in the SELECT statement.
117
self._fieldOrder = []
119
# Internal flag to avoid consistently doing the same check
120
# If this is set to 1 initially, then the
121
self._primaryIdChecked = False # Internal flag
125
def _toSqlString(self, value):
126
if isinstance (value, mx.DateTime.DateTimeType):
127
if (value.year, value.month, value.day) == (1, 1, 1):
128
return value.strftime (self._timeFormat)
130
return value.strftime (self._dateTimeFormat)
135
elif type(value) == types.IntType:
136
return "'%d'" % value
138
elif type(value) == types.LongType:
139
return "'%d'" % value
141
elif type(value) == types.FloatType:
142
if value==int(value):
143
return "'%d'" % value
145
return "'" + str (value) + "'"
147
elif type(value) == types.StringType:
149
if self._unicodeMode:
150
gDebug (1, 'WARNING: non-unicode passed to the dbdriver (%s)' % value)
152
return "'%s'" % replace(value,
154
"%s'" % self._escapeSingleQuote)
156
elif type(value) == types.UnicodeType:
157
return "'%s'" % replace(value,
159
"%s'" % self._escapeSingleQuote)
161
# TODO: make this test faster, possibly move type check to GTypecast
162
elif hasattr(types,'BooleanType') and type(value) == types.BooleanType:
169
err = u_("Object of unknown type (%s) passed to DBSIG2 based dbdriver.") % type(value)
170
# FIXME: raise an error instead of just printing a warning, after some transition time
171
#raise Exceptions.UnknownDataType, err
173
return "'%s'" % replace(str(value),
175
"%s'" % self._escapeSingleQuote)
178
# Used by drivers with a unique id (like rowid) (see Oracle for example)
179
def _checkForPrimaryId(self):
180
self._primaryIdChecked = True
183
def _createResultSet(self, conditions={}, readOnly=False,
184
masterRecordSet=None,sql=""):
186
# Used by drivers with a unique id (like rowid)
187
if not self._primaryIdChecked: self._checkForPrimaryId()
190
query = self._buildQuery(conditions, additionalSQL = sql)
191
cursor = self._connection.makecursor(query)
192
cursor.arraysize = self.cache
194
# pull a record count
195
if self._strictQueryCount:
196
recordCount = cursor.rowcount
197
#disable the count query and see if anyone screams.
198
#recordCount = self._getQueryCount(conditions,sql)
199
# ARGH!!!! Oh, the agony... the agony....
201
except self._DatabaseError:
202
raise Exceptions.ConnectionError, errors.getException () [2]
204
rs = self._resultSetClass(self, cursor=cursor, masterRecordSet=masterRecordSet,
205
fieldOrder=self._fieldOrder)
206
if self._strictQueryCount:
207
rs._recordCount = recordCount
209
rs._readonly = readOnly
213
def _getQueryCount(self,conditions={},sql=""):
214
query = self._buildQueryCount (conditions, additionalSQL = sql)
215
return self._connection.sql1 (query)
218
# Used to convert a condition tree to an sql where clause
219
def _conditionToSQL (self, condition):
220
if condition == {} or condition == None:
222
elif type(condition) == types.DictType:
223
cond = GConditions.buildConditionFromDict(condition)
227
if not len(cond._children):
229
elif len(cond._children) > 1:
230
chillun = cond._children[:]
232
_and = GConditions.GCand(cond)
233
_and._children = chillun
235
where = " WHERE (%s)" % (self.__conditionToSQL (cond._children[0]))
240
# Used internally by _conditionToSQL
242
# This code recursively travels down a condition tree replacing the objects
243
# with a strings representation
244
def __conditionToSQL (self, element):
245
if type(element) != types.InstanceType:
246
return "%s" % element
248
# Note that we strip the GC from the object types and lowercase the rest
249
otype = string.lower(element._type[2:])
250
#print "Otype: ",otype
251
if otype == 'cfield':
252
return "%s" % element.name
253
elif otype == 'cconst':
254
if element.value == None:
256
elif element.type == 'number':
257
return "%s" % element.value
259
return self._toSqlString(element.value)
260
elif otype == 'cparam':
261
v = element.getValue()
262
return (v == None and "NULL") or ("'%s'" % v)
263
elif self.conditionElements.has_key(otype):
265
for i in range(0, len(element._children)):
266
result.append(self.__conditionToSQL(element._children[i]))
267
if len(result) == 2 and \
268
otype in ('eq','ne') and \
270
otype = "__is%s" % otype
272
if len(result) < self.conditionElements [otype][0]:
273
tmsg = u_('Condition element "%(element)s" expects at least '
274
'%(expected)s arguments; found %(found)s') \
275
% {'element' : otype,
276
'expected': self.conditionElements[otype][0],
277
'found' : len (result)}
278
raise GConditions.ConditionError, tmsg
280
if len(result) > self.conditionElements [otype][1]:
281
tmsg = u_('Condition element "%(element)s" expects at most '
282
'%(expected)s arguments; found %(found)s') \
283
% {'element' : otype,
284
'expected': self.conditionElements[otype][1],
285
'found' : len (result)}
286
raise GConditions.ConditionError, tmsg
288
if self.conditionElements[otype][3] == None:
289
return self.conditionElements[otype][2] % tuple(result)
291
return self.conditionElements[otype][2] % \
292
(join(result, self.conditionElements[otype][3]))
294
tmsg = u_('Condition clause "%s" is not supported by this db driver.') % otype
295
raise GConditions.ConditionNotSupported, tmsg
299
######################################################################
303
class DataObject_Object(DataObject):
305
def _buildQuery(self, conditions={}, forDetail=None, additionalSQL=""):
306
gDebug (8, 'DBSIG2-DO::Implicit Fields: %s' % self._fieldReferences)
308
distinct = "distinct "
312
if self._primaryIdSelect:
313
pis = "%s," % self._primaryIdSelect
317
whereClause = self._conditionToSQL(conditions)
320
whereClause += ' and %s' % (additionalSQL)
322
whereClause = ' WHERE %s' % (additionalSQL)
325
q = "(%s) in (SELECT %s FROM %s%s)" % \
326
(join(self._masterfields,","),
327
join(self._detailfields,","),
328
self.table, whereClause)
329
elif self._fieldReferences:
330
self._fieldOrder = fields = self._fieldReferences.keys()
331
q = "SELECT %s%s%s FROM %s%s" % \
332
(distinct, pis, join(fields,","), self.table,
335
self._fieldOrder.insert(0,None)
337
self._fieldOrder = []
338
self._primaryIdSelect = None
339
q = "SELECT %s* FROM %s%s" % (distinct, self.table,
342
if hasattr (self, 'sorting') and not forDetail and self.sorting:
344
for item in self.sorting:
345
field = item ['name']
346
descending = item.get ('descending') or False
347
ignorecase = item.get ('ignorecase') or False
349
fmt = ignorecase and "LOWER(%s)%s" or "%s%s"
350
order.append (fmt % (field, descending and ' desc' or ''))
352
q = "%s ORDER BY %s " % (q, string.join (order, ", "))
358
def _buildQueryCount(self, conditions={}, additionalSQL=""):
359
whereClause = self._conditionToSQL(conditions)
362
whereClause += ' and %s' % (additionalSQL)
364
whereClause = ' WHERE %s' % (additionalSQL)
367
q = "SELECT count(*) FROM %s%s" % (self.table, whereClause)
375
######################################################################
379
class DataObject_SQL(DataObject):
382
# (GDataSource.GSql instance)
385
def _buildQuery(self, conditions={}, forDetail=None, additionalSQL=""):
388
except AttributeError:
389
sql = self.__sql = self._rawSQL.getChildrenAsContent()