# -*- coding: iso-8859-1 -*-
#-----------------------------------------------------------------------------
# Modeling Framework: an Object-Relational Bridge for python
#
# Copyright (c) 2001-2004 Sbastien Bigaret <sbigaret@users.sourceforge.net>
# All rights reserved.
#
# This file is part of the Modeling Framework.
#
# This code is distributed under a "3-clause BSD"-style license;
# see the LICENSE file for details.
#-----------------------------------------------------------------------------
"""
SQLExpression
CVS information
$Id: SQLExpression.py 946 2004-10-18 20:35:29Z sbigaret $
"""
__version__='$Revision: 946 $'[11:-2]
from cStringIO import StringIO
import re, string, types
# Framework
import Qualifier
from Qualifier import KeyComparisonQualifier,KeyValueQualifier,\
NotQualifier, AndQualifier, OrQualifier, \
QualifierOperatorEqual, \
QualifierOperatorNotEqual, \
QualifierOperatorLessThan, \
QualifierOperatorGreaterThan, \
QualifierOperatorLessThanOrEqualTo, \
QualifierOperatorGreaterThanOrEqualTo, \
QualifierOperatorLike, \
QualifierOperatorCaseInsensitiveLike, \
QualifierOperatorIn, QualifierOperatorNotIn
from Relationship import \
INNER_JOIN, FULL_OUTER_JOIN, LEFT_OUTER_JOIN, RIGHT_OUTER_JOIN
from logging import warn#, trace
trace=lambdamsg:None
escapeQuote=re.compile("'")
# Constants
BinaryType=0
DateType=1
CharacterType=2
NumericType=3
# for use by SQLExpression.sqlPatternFromShellPatternWithEscapeCharacter
esc_question_tmp_replct='MDL_ESCAPED_QUESTION_MARK_MDL'
esc_star_tmp_replct='MDL_ESCAPED_STAR_MDL'
star=re.compile('\*')
escaped_star=re.compile(r'\\\*')
question_mark=re.compile('\?')
escaped_question_mark=re.compile(r'\\\?')
percent=re.compile('%')
underscore=re.compile('_')
anti_escaped_star=re.compile(esc_star_tmp_replct)
anti_esc_question_mark=re.compile(esc_question_tmp_replct)
class InvalidSQLTypeError(ValueError):
"""
Raised by SQLExpression.formatValueForAttribute() when an unknown SQL type
is encountered at runtime
"""
pass
from Modeling.utils import base_object
class SQLExpression(base_object):
"""
"""
class Internals:
"""
"""
def __init__(self):
"Initializer"
## Aliases <---> RelPath
##
# key: path 'rel1.rel2', value: alias 't<n>'
self.__aliasesByRelPaths={}
# key: alias 't<n>', alias: path 'rel1.rel2',
self.__relPathsByAliases={}
## Aliases <---> entityExternalName
##
# key: alias 't<n>', value: externalName 'TABLE_NAME'
self.__entityExternalNamesByAliases={}
##
##
# key: alias t<n>, value: (alias t<i>, alias t<j>, ...)
self.__comesFromAlias={}
##
##
# key: path 'rel1.rel2', value: (srcKeys, dstKeys)
self.__joinSemanticByRelPath={}
self.__srcAndDstKeysForRelPath={}
## Internals manipulation
def _addAliasForRelPath(self, alias, relPath):
self.__aliasesByRelPaths[relPath]=alias
self.__relPathsByAliases[alias]=relPath
def _addEntityExternalNameForAlias(self, entityExternalName, alias):
self.__entityExternalNamesByAliases[alias]=entityExternalName
def addRelPathForEntity(self, path, entity):
"""
The only entry point for adding an entry
Parameter:
path -- a sequence of (Relationship, ..., Relationship, Attribute)
entity -- the root Entity
"""
if not path:
if not self.__aliasesByRelPaths.get(""):
self._addEntityExternalNameForAlias(entity.externalName(), 't0')
self._addAliasForRelPath('t0', "")
return
relPathString=""
for rel in path[:-1]:
relPathString+=rel.name()+'.'
relPathString=relPathString[:-1]
if self.__aliasesByRelPaths.get(relPathString):
return relPathString
finalAttribute=path[-1]
currentPathSeq=[]
for rel in path[:-1]:
# Add each intermediary path, if necessary
currentPathSeq.append(rel)
self.__addRelPathForEntity(currentPathSeq)
return relPathString
def __addRelPathForEntity(self, relPathSequence):
"""
...
Called from 'addRelPathForEntity()'
Parameters:
relPathSequence -- a sequence of 'Relationship' objects
entity -- the root Entity object
"""
comeFromRelPath=""
for rel in relPathSequence[:-1]:
comeFromRelPath+=rel.name()+'.'
comeFromRelPath=comeFromRelPath[:-1]
if comeFromRelPath:
relPathString=comeFromRelPath+'.'+relPathSequence[-1].name()
else:
relPathString=relPathSequence[-1].name()
if self.__aliasesByRelPaths.get(relPathString):
return
alias=self.nextAlias()
finalElement=relPathSequence[-1]
#print finalElement, repr(finalElement)
extName=finalElement.destinationEntity().externalName()
self._addEntityExternalNameForAlias(extName, alias)
self._addAliasForRelPath(alias, relPathString)
self._addRelPathComesFromAlias(relPathString,
self.aliasForRelPath(comeFromRelPath))
joins=finalElement.joins()
sourceKeys=map(lambda o: o.sourceAttribute().columnName(), joins)
destKeys=map(lambda o: o.destinationAttribute().columnName(), joins)
joinSemantic=finalElement.joinSemantic()
self._addJoinSemanticAndKeysForRelPath(joinSemantic, sourceKeys,
destKeys, relPathString)
trace('orig.: %s alias: %s, relationship %s %s'%\
(self.aliasForRelPath(comeFromRelPath),alias,
map(lambda o: o.sourceAttribute().columnName(),
finalElement.joins()),
map(lambda o: o.destinationAttribute().columnName(),
finalElement.joins())))
def _addJoinSemanticAndKeysForRelPath(self, joinSemantic,
srcKeys, dstKeys, relPath):
"-"
self.__joinSemanticByRelPath[relPath]=joinSemantic
self.__srcAndDstKeysForRelPath[relPath]=(srcKeys, dstKeys)
def _addRelPathComesFromAlias(self, relPathString, alias):
_list=self.__comesFromAlias.get(alias, [])
_list.append(relPathString)
self.__comesFromAlias[alias]=_list
def relPathsComingFromAlias(self, alias): # or?: aliasesBoundToAlias
return self.__comesFromAlias.get(alias, [])
def aliasForRelPath(self, relPath):
return self.__aliasesByRelPaths.get(relPath)
def relPathForAlias(self, alias):
return self.__relPathsByAliases.get(alias)
def entityExternalNameForAlias(self, alias):
return self.__entityExternalNamesByAliases.get(alias)
def aliasForEntityExternalName(self, externalName):
for key in self.__entityExternalNamesByAliases.keys():
if self.__entityExternalNamesByAliases[key]==externalName:
return key
return None
def joinSemanticForRelPath(self, relPath):
return self.__joinSemanticByRelPath.get(relPath)
def sourceKeysForRelPath(self, relPath):
return self.__srcAndDstKeysForRelPath.get(relPath, [(),()])[0]
def destinationKeysForRelPath(self, relPath):
return self.__srcAndDstKeysForRelPath.get(relPath, [(),()])[1]
## Access to dictionary
def entityExternalNamesByAliases(self):
return self.__entityExternalNamesByAliases.copy()
## Utility methods
def nextAlias(self):
aliases=self.__aliasesByRelPaths.values()
if not aliases: return 't0'
aliases.sort()
alias='t'+str( 1+int(aliases[-1][1:]) ) # the next value for t<n>
return alias
def showInternals(self):
s=''
s+=str(self.__entityExternalNamesByAliases)+'\n'
s+=str(self.__aliasesByRelPaths)+'\n'
s+=str(self.__relPathsByAliases)+'\n'
s+=str(self.__comesFromAlias)+'\n'
return s
## END / Internals
SQL92_join=1 # see _addTableJoinsForAlias() for details
def __init__(self, anEntity=None):
"""
Initializer
"""
self._entity=anEntity
self._listString=StringIO()
self._valueList=StringIO()
self._statement=''
self._useAliases=0
self._internals=SQLExpression.Internals()
self._whereClauseString=""
# used when 'JOIN' are not supported, e.g. Oracle8i, to generate
# ANSI equivalent
self._joinClauseString=""
### key: path 'rel1.rel2', value: alias 't<n>'
##self._aliasesByRelPath={}
##
### The following dict. should be updated each time _aliasesByRelPath
### gets updated
### key: alias, value: entityName
##self._entityExternalNameByAlias={}
def addBindVariableDictionary(self, binding):
"""
"""
__unimplemented__()
def addCreateClauseForAttribute(self, attribute):
"""
Adds the create clause for the supplied 'attribute' to the receiver's
listString. The default implementation appends a line of the following
form::
<newline><COLUMN_NAME> <COLUMN_TYPE_STRING> <ALLOWS_NULL_CLAUSE>
where <COLUMN_NAME> is the attribute's columnName(), <COLUMN_TYPE_STRING>
is 'columnTypeStringForAttribute(attribute)' and <ALLOWS_NULL_CLAUSE> is
'allowsNullClauseForConstraint(attribute.allowsNull())'.
See also:
appendItemToListString()
listString()
allowsNullClauseForConstraint()
columnTypeStringForAttribute()
SchemaGeneration.createTableStatementsForEntityGroup()
"""
if attribute.columnName() is None: return
createClause='\n '+attribute.columnName()+' '
createClause+=self.columnTypeStringForAttribute(attribute)+' '
createClause+=self.allowsNullClauseForConstraint(attribute.allowsNull())
self.appendItemToListString(createClause, self._listString)
def addInsertListAttribute(self, attribute, value):
"""
Parameters:
attribute -- an Attribute object
value -- the value to store
"""
sqlAttr=self.sqlStringForAttributeNamed(attribute.name())
sqlValue=self.sqlStringForValue(value, attribute.name())
#print '### sqlAttr: %s / sqlValue: %s'%(sqlAttr, sqlValue)
self.appendItemToListString(sqlAttr, self._listString)
self.appendItemToListString(sqlValue, self._valueList)
def addJoinClause(self, leftName, rightName, semantic):
"""
"""
def addOrderByAttributeOrdering(self, sortOrdering):
"""
"""
def addSelectListAttribute(self, attribute):
"""
Adds the sqlStringForAttributeNamed(attribute.name()) to the
SQLExpression's listString().
See also: sqlStringForAttributeNamed(), appendItemToListString(),
listString()
"""
sqlStr=self.sqlStringForAttributeNamed(attribute.name())
self.appendItemToListString(sqlStr, self._listString)
def addUpdateListAttribute(self, attribute, value):
"""
Uses appendItemToListString() to add to listString() a string
formatted that way::
<attribute's columnName()> = <value>
See also: prepareUpdateExpressionWithRow(), listString()
"""
str="%s = %s"%(attribute.columnName(),
self.sqlStringForValue(value, attribute.name()))
self.appendItemToListString(str, self._listString)
def aliasesByRelationshipPath(self):
"""
Returns the dictionary of table aliases used by the SQLExpression ; the
keys are relationshipPath and the values are the corresponding table
aliases.
If useAliases() is true, the dictionary contains at least one entry with
an empty string as the key and 't0' being the corresponding value.
Otherwise, it may be None.
That dictionary is built upon successive invocation of
sqlStringForAttributePath().
See also: setUseAliases(),
sqlStringForAttributePath(), sqlStringForAttributeNamed()
"""
return self._internals.aliasesByRelPath()
def allowsNullClauseForConstraint(self, flag):
"""
Returns 'NOT NULL' is flag is false, '' (empty string) otherwise.
Subclasses should override this method, without calling it, if the
underlying database uses a different syntax for such a clause.
See: addCreateClauseForAttribute()
"""
if flag: return ''
return 'NOT NULL'
def appendItemToListString(self, itemString, aStringIO):
"""
Appends 'itemString' to 'aStringIO' ; if aStringIO is not empty, a comma
is appended to aStringIO before 'itemString'
Parameters:
- itemString is the string to append
- aStringIO is a StringIO object
See:
addCreateClauseForAttribute()
"""
if len(aStringIO.getvalue())!=0:
aStringIO.write(', ')
aStringIO.write(itemString)
def assembleDeleteStatementWithQualifier(self, aQualifier, tableList, whereClause):
"""
Generates the SQL DELETE statement and assigns it to self's statement()
The generated statement has the following format::
DELETE FROM <tableList> WHERE <whereClause>
Parameters:
aQualifier -- identifies the rows to be deleted. This is usually the
parameter that prepareDeleteExpressionForQualifier() received before
this method is called. It is not used here but might be useful for
subclasses' implementation
tableList -- the name of the table in which the row should be deleted
whereClause -- the SQL where clause to use in the DELETE statement.
See also: statement(), prepareDeleteExpressionForQualifier()
"""
statement='DELETE FROM %s WHERE %s'%(tableList, whereClause)
self._statement=statement
def assembleInsertStatementWithRow(self, row, tableList, columnList, valueList):
"""
Generates the SQL INSERT statement and assigns it to self's statement()
The generated statement has the following format::
INSERT INTO <tableList> [<columnList>] VALUES <valueList>
<columnList> will be omitted
Parameters:
row -- the 'row' parameter that was passed to
prepareInsertExpressionWithRow(). It is not used here but might be
useful for subclasses' implementation
tableList -- the name of the table in which the row should be inserted
columnList -- a comma-separated string containing the name of the
columns (order: see below)
valueList -- a comma-separated string of values to be inserted in the
'tableList'. 'columnList' and 'valueList' should have the same order,
i.e. entries in the former correspond one-to-one to entries in the
latter.
See also: statement(), prepareInsertExpressionWithRow()
"""
statement='INSERT INTO '
statement+=tableList
if columnList:
statement+=' ( %s ) '%columnList
statement+='VALUES ( %s )'%valueList
self._statement=statement
#def assembleJoinClause(self, leftName, rightName, semantic):
# """
# """
# ?? leftName operator rightName
#operator: inner join "=", left-outer join "*=", right-outer join "=*"
## Pb: =* & *= --> identified as buggy?? in postgresql's doc.
## cf. http://www.postgresql.org/idocs/index.php?tutorial-join.html
##
def assembleSelectStatementWithAttributes(self, attributes, lock, qualifier,
fetchOrder, selectString,
columnList, tableList, whereClause,
joinClause, orderByClause,
lockClause):
"""
"""
statement=selectString or 'SELECT '
statement+=columnList
statement+=' FROM '+tableList
if lockClause:
statement+=' '+lockClause
if whereClause or joinClause:
statement+=" WHERE "
statement+=(whereClause and joinClause) \
and ('('+whereClause+') AND ('+joinClause+')') \
or ( whereClause or joinClause )
if orderByClause:
statement+=' ORDER BY '+orderByClause
self._statement=statement
def assembleUpdateStatementWithRow(self, row, aQualifier,
tableList, updateList, whereClause):
"""
Generates the SQL INSERT statement and assigns it to self's statement()
The generated statement has the following format::
UPDATE <tableList> SET <updateList> WHERE <whereClause>
Parameters:
row --
aQualifier -- identifies the rows to be deleted. This is usually the
parameter that prepareDeleteExpressionForQualifier() received before
this method is called. It is not used here but might be useful for
subclasses' implementation
tableList -- the name of the table in which the row should be updated
updateList -- the SQL where clause to use in the UPDATE's SET statement.
whereClause -- the SQL where clause to use in the UPDATE's WHERE
statement.
See also: statement(), prepareUpdateExpressionWithRow()
"""
statement='UPDATE %s SET %s WHERE %s'%(tableList, updateList, whereClause)
self._statement=statement
def bindVariableDictionaries(self):
"""
"""
def bindVariableDictionaryForAttribute(self, attribute, value):
"""
"""
def columnTypeStringForAttribute(self, attribute):
"""
Returns the string corresponding to the attribute's column type definition
when creating a table.
Default implementation returns a different string depending on some of
the attribute's properties:
- if attribute's precision is not zero: 'externalType(precision, scale)'
- if attribute's width is not zero: 'externalType(width)'
- otherwise returns: 'externalType'
Subclasses should override this method, without calling it, if the
underlying database uses a different syntax for column types.
See also:
addCreateClauseForAttribute()
SchemaGeneration.createTableStatementsForEntityGroup()
"""
if attribute.precision():
return attribute.externalType()+'('+\
str(attribute.precision())+','+str(attribute.scale())+')'
if attribute.width():
return attribute.externalType()+'('+\
str(attribute.width())+')'
return attribute.externalType()
def entity(self):
"""
Returns the Entity bound to that SQLExpression
See: __init__()
"""
return self._entity
def entityExternalNamesByAliases(self): ###############################
return self._internals.entityExternalNamesByAliases()
def externalNameQuoteCharacter(self):
"""
Unimplemented
"""
__unimplemented__()
def formatSQLString(self, sqlString, format):
"""
Unimplemented
"""
__unimplemented__()
def formatStringValue(self, string):
"""
Unimplemented
"""
__unimplemented__()
def formatValueForAttribute(self, value, attribute):
"""
Examines the attribute's externalType and forwards the request to the
appropriate method.
The methods to which the message is forwarded are:
- for BinaryType: sqlStringForData()
- for CharacterType: sqlStringForString()
- for DateType: sqlStringForDate()
- for NumericType: sqlStringForNumber()
Raises InvalidSQLTypeError is the attribute's external type is unknown for
the concrete adaptor.
See also: valueTypeForExternalType(), sqlStringForValue()
"""
valueType=self.valueTypeForExternalType(attribute.externalType())
if valueType==BinaryType:
return self.sqlStringForData(value)
elif valueType==CharacterType:
return self.sqlStringForString(value)
elif valueType==DateType:
return self.sqlStringForDate(value)
elif valueType==NumericType:
return self.sqlStringForNumber(value)
raise InvalidSQLTypeError, "SQL type: %s (attribute: %s) is invalid for the current adaptor (valueTypeForExternalType returned: %s)"%(attribute.externalType(), attribute, valueType)
def joinClauseString(self):
"""
Unimplemented and unused: the joinClause is not used in this implementation
of SQLExpression. The statements necessary to support joins (for SELECT,
mainly) are generated in the whereClause when the tables and their aliases
are examined.
"""
return self._joinClauseString
def joinExpression(self):
"""
Unimplemented and not used: cf. joinClauseString()
"""
__unimplemented__()
def listString(self):
"""
Has a different meaning depending on what the SQLExpression was made for:
- for createTableStatements, it holds the list of columns' type
declarations
- for insert statements, it holds the list of column's names (see also:
valueList())
- for update statements, contains the SQL clause suitable for the SET
statement (in: 'UPDATE ... SET ... WHERE ...')
"""
return self._listString.getvalue()
def lockClause(self):
"""
"""
__unimplemented__()
def mustUseBindVariableForAttribute(self, attribute):
"""
"""
__unimplemented__()
def orderByString(self):
"""
"""
__unimplemented__()
def prepareConstraintStatementForRelationship(self, relationship, sourceColumns, destinationColumns):
"""
"""
__unimplemented__()
def prepareDeleteExpressionForQualifier(self, aQualifier):
"""
Prepares the internal state to generate a SQL delete statement, according
to entity() and parameter 'aQualifier'.
The process is the following:
- the use of aliases is disabled (see setUseAliases())
- the whereClauseString() is compuuted ans set by calling
'sqlStringForQualifier(aQualifier)' on itself,
- the database table is computed by calling tableListWithRootEntity()
- Finally, we send to oneself 'assembleDeleteStatementWithQualifier()'
See also: assembleDeleteStatementWithQualifier(), whereClauseString()
"""
self.setUseAliases(0)
self._whereClauseString=self.sqlStringForQualifier(aQualifier)
tableList=self.tableListWithRootEntity(self._entity)
self.assembleDeleteStatementWithQualifier(aQualifier,
tableList,
self.whereClauseString())
def prepareInsertExpressionWithRow(self, row):
"""
Generates an INSERT statement for the supplied 'row' and the
SQLExpression's entity().
The process is the following:
- the use of aliases is disabled (see setUseAliases())
- it calls addInsertListAttribute() on 'self' for each item in 'row'.
Afterwards, listString() contains a comma-separated list of
column's name, and valueList() a comma-separated list of their values,
both following the same order, of course.
- it gets the name of the table by sending the message
tableListWithRootEntity to itself
- finally, it calls assembleInsertStatementWithRow() with the elements
calculated above.
The generated INSERT statement is then available through 'statement()'
Parameter:
row -- a dictionary, with keys as Attribute's names, which are bound to
their corresponding values.
See also: assembleInsertStatementWithRow(), listString(), valueList(),
statement()
"""
self.setUseAliases(0)
for key in row.keys():
self.addInsertListAttribute(self.entity().attributeNamed(key), row[key])
tableList=self.tableListWithRootEntity(self._entity)
self.assembleInsertStatementWithRow(row, tableList, self.listString(),
self.valueList())
def _prepareSelectExpressionWithAttributes(self, attributes, lock,
fetchSpec, count=0):
"""
Prepares the SELECT statement for
prepareSelectCountExpressionWithAttributes() and
prepareSelectExpressionWithAttributes().
Parameters:
attributes, lock, fetchSpec -- see prepareSelectExpressionWithAttributes
count -- integer '0' for prepareSelectExpressionWithAttributes(),
integer '1' for prepareSelectCountExpressionWithAttributes()
"""
self.setUseAliases('Yep')
lockClause=''
if lock:
raise NotImplementedError, 'lock is not implemented yet'
for attribute in attributes:
self.addSelectListAttribute(attribute)
# fetchSpec
if fetchSpec is not None and \
self._entity.restrictingQualifier() is not None:
# TBD
raise NotImplementedError, \
"fetchSpec and/or entity's restrictingQualifier is not None"
# qualifier: to be mixed w/ entity's restrictingQualifier if necessary
fullQualifier=None
fsQualifier=fetchSpec and fetchSpec.qualifier() or None
entityQual=self._entity.restrictingQualifier()
if fsQualifier:
if entityQual:
fullQualifier=AndQualifier([fsQualifier, entityQual])
else:
fullQualifier=fsQualifier
elif entityQual:
fullQualifier=entityQual
# clause: WHERE
#import pdb ; pdb.set_trace()
self._whereClauseString=self.sqlStringForQualifier(fullQualifier)
tableList=self.tableListWithRootEntity(self.entity())
#
joinClause=self.joinClauseString()
# Do we join some tables?
multiple_tables_joined=len(self.entityExternalNamesByAliases())>1
selectString=''
if multiple_tables_joined:
selectString='SELECT DISTINCT '
fetchOrder=None
whereClause=self.whereClauseString()
# orderBy: see fetchSpec...
orderByClause=''
if count and not multiple_tables_joined:
columnList=' COUNT(*) '
else:
columnList=self.listString()
self.assembleSelectStatementWithAttributes(attributes=attributes,
lock=lock,
qualifier=fullQualifier,
fetchOrder=fetchOrder,
selectString=selectString,
columnList=columnList,
tableList=tableList,
whereClause=whereClause,
joinClause=joinClause,
orderByClause=orderByClause,
lockClause=lockClause)
if count and multiple_tables_joined:
self._statement='SELECT COUNT(*) FROM ( '+self._statement+' )'
def prepareSelectCountExpressionWithAttributes(self,
attributes,lock,fetchSpec):
"""
Prepares a 'SELECT' statement from retrieving the number of rows
corresponding to the request. The statement is then available as a string
in self.statement().
See also: AdaptorChannel.rowCountForSelectAttributes()
"""
self._prepareSelectExpressionWithAttributes(attributes, lock, fetchSpec,
count=1)
def prepareSelectExpressionWithAttributes(self,attributes,lock,fetchSpec):
"""
Prepares a 'SELECT' statement. The statement is then available as a string
in self.statement().
See also: AdaptorChannel.selectAttributes()
"""
self._prepareSelectExpressionWithAttributes(attributes, lock, fetchSpec,
count=0)
def prepareUpdateExpressionWithRow(self, row, aQualifier):
"""
Parameters:
row -- a dictionary {key==attributeName: value==value to update}
aQualifier -- identifies the rows to be updated.
"""
self.setUseAliases(0)
for name in row.keys():
self.addUpdateListAttribute(self._entity.attributeNamed(name), row[name])
self._whereClauseString=self.sqlStringForQualifier(aQualifier)
updateList=self.listString()
tableList=self.tableListWithRootEntity(self._entity)
self.assembleUpdateStatementWithRow(row, aQualifier, tableList,
updateList, self.whereClauseString())
def setStatement(self, statement):
"""
Sets the object's SQL statement.
"""
self._statement=statement
def setUseAliases(self, useAliases):
"""
Tells the SQLExpression whether it should use table aliases.
This method has a side-effect: if flag 'useAliases' is true and the
SQLExpression's aliasesByRelationshipPath() is empty, the latter gets
value 't0' for key '""' (empty string) ; if the flag is false,
aliasesByRelationshipPath() is reset.
See also: useAliases()
"""
if useAliases:
self._useAliases=1
#if not self._internals.aliasesByRelationshipPath():
# extName=self._entity.externalName()
#
self._internals.addRelPathForEntity((), self._entity)
else:
self._useAliases=0
self._internals=SQLExpression.Internals()
def setUseBindVariables(self, flag):
"""
Bind variables are not supported yet
"""
__unimplemented__('Bind variables are not supported yet')
def shouldUseBindVariableForAttribute(self, attribute):
"""
Bind variables are not supported yet
"""
__unimplemented__('Bind variables are not supported yet')
def sqlEscapeChar(self):
"""
"""
return '\\'
def sqlPatternFromShellPattern(self, pattern):
"""
Simply calls sqlPatternFromShellPatternWithEscapeCharacter() with
'escapeChar' equal to 'self.sqlEscapeChar()'
"""
return self.sqlPatternFromShellPatternWithEscapeCharacter(pattern, self.sqlEscapeChar())
def sqlPatternFromShellPatternWithEscapeCharacter(self, pattern, escapeChar):
"""
Transforms the shell 'pattern', using '*' and '?' as wildcards (the
former matches 0 to many characters while the second matches one
character) to an valid SQL pattern.
Details: '%' in 'pattern' becomes '\%', '_' becomes '\_',
'\*' becomes '*', '*' becomes '%', '\?' becomes '?' and
'?' becomes '_'.
"""
pattern=percent.sub('\%', pattern)
pattern=underscore.sub('\_', pattern)
pattern=escaped_question_mark.sub(esc_question_tmp_replct, pattern)
pattern=question_mark.sub('_', pattern)
pattern=escaped_star.sub(esc_star_tmp_replct, pattern)
pattern=star.sub('%', pattern)
pattern=anti_escaped_star.sub('*', pattern)
pattern=anti_esc_question_mark.sub('?', pattern)
return pattern
def sqlStringForAttribute(self, attribute):
"""
Returns the attribute's columnName, prepended with the table alias for
the empty string if the SQLExpression uses table aliases.
For example, the returned value for an attribute 'title' whose columnName
is 'TITLE' is 'TITLE', or 't0.TITLE' if useAliases() is true.
See also: useAliases(), aliasesByRelationshipPath(), Attribute.columnName()
"""
if self.useAliases():
return 't0.'+attribute.columnName()
else:
return attribute.columnName()
def sqlStringForAttributeNamed(self, name):
"""
Returns the sql string for the attribute 'name'. If the corresponding
attribute is a regular Attribute (non flattened), it triggers
sqlStringForAttribute() ; otherwise, it builds the path and triggers
sqlStringForAttributePath() (see sqlStringForAttributePath() for a
description of what 'building the path' means).
Raises ValueError in case 'name' is not a valid attribute's name.
See also: sqlStringForAttribute, sqlStringForAttributePath()
Attribute.isFlattened(), Attribute.relationshipPathObjects(),
Attribute.finalAttribute()
"""
## calls sqlStringForAttributePath if flattened
if string.find(name, '.')!=-1:
return self.sqlStringForAttributePath(name)
attribute=self.entity().attributeNamed(name)
if not attribute:
raise ValueError, "Unknown attribute '%s' for entity '%s'"%(name, self.entity().name())
if attribute.isDerived() and not attribute.isFlattened():
raise ValueError, "Error: attribute %s shouldn't be derived"%name
if attribute.isFlattened():
path=list(attribute.relationshipPathObjects())
path.append(attribute.finalAttribute())
return self.sqlStringForAttributePath(path)
else: # Regular attribute
return self.sqlStringForAttribute(attribute)
def sqlStringForAttributePath(self, path):
"""
Given a path (see below), builds the corresponding sql string, completed
with a table alias if the SQLExpression useAliases().
Parameter 'path' is a list composed of Relationship objects that has to
be traversed to access the final Attribute, which is the last element
of the list.
If table aliases is on and aliasesByRelationshipPath() has no key
corresponding to the relationshipPath, that key is set with a new table
alias.
Example: say we have two entities 'Book' and 'Writer', a toOne
relationship 'toBook' from 'Writer' to 'Book', and 'Writer' defined an
attribute 'bookTitle' flattening 'toBook.title' (title being a regular
attribute of 'Book'). When table aliases is on, this methods returns
something like 't1.TITLE' --in that case, aliasesByRelationshipPath()
has a key 'toBook' corresponding to the value 't1'.
**Important note**:
It seems that derived attribute should not be used except to support
inheritance and the vertical-mapping approach. This is **not**
supported for the moment being, thus this method is desactivated and
will unconditionnally raise when invoked. See the project's TODO file
for a more complete explanation.
See also: useAliases(), sqlStringForAttribute(),
aliasesByRelationshipPath(),
Attribute.relationshipPathObjects(), Attribute.finalAttribute()
"""
if not self.useAliases():
raise NotImplementedError, '__TBD'
if type(path)==types.StringType:
path=self._entity.objectsPathForKeyPath(path)
relPath=self._internals.addRelPathForEntity(path, self._entity)
return self._internals.aliasForRelPath(relPath)+'.'+path[-1].columnName()
def sqlStringForCaseInsensitiveLike(self, keyString, valueString):
"""
Returns the SQL string, to be inserted in a WHERE clause, for
case-insensitive comparison between a lvalue and a rvalue.
Default implementation returns the following string::
UPPER(<keyString>) LIKE UPPER(<valueString>)
Parameters:
keyString -- the left value
valueString -- the right value
See also: sqlStringForKeyValueQualifier(),
sqlStringForKeyComparisonQualifier()
"""
return "UPPER(%s) LIKE UPPER(%s)"% ( keyString, valueString )
def sqlStringForConjoinedQualifiers(self, qualifiers):
"""
Returns the SQL string, to be inserted in a WHERE clause, for the supplied
``to-be-conjoined'' qualifiers.
Default implementation returns::
<qualifierString1> AND <qualifierString2> AND ...
... AND <qualifierString_i>
where '<qualifierString_i>' is 'sqlStringForQualifier(qualifiers[i])'
Parameter:
qualifiers -- a sequence of qualifier that should be conjoined. If you
have a 'Qualifier.AndQualifier' instance 'andQualifier', you should
supply 'andQualifier.qualifiers()' as the argument
See also: sqlStringForQualifier()
"""
sqlStr='('
for qualifier in qualifiers:
sqlStr+=self.sqlStringForQualifier(qualifier)+ ' AND '
sqlStr=sqlStr[:-5]+')'
return sqlStr
def sqlStringForDate(self, aDate):
"""
Formats the 'mxDateTime.DateTime' object 'aDate'. SQLExpression's
implementation simply returns 'str(aDate)', or 'NULL' if 'aDate' is None.
See also: formatValueForAttribute()
"""
if aDate is None: return 'NULL'
return "'"+str(aDate)+"'"
def sqlStringForData(self, data):
"""
Not supported yet
"""
__unimplemented__()
def sqlStringForDisjoinedQualifiers(self, qualifiers):
"""
Returns the SQL string, to be inserted in a WHERE clause, for the supplied
``to-be-disjoined'' qualifiers.
Default implementation returns::
<qualifierString1> OR <qualifierString2> OR ... OR <qualifierString_i>
where '<qualifierString_i>' is 'sqlStringForQualifier(qualifiers[i])'
Parameter:
qualifiers -- a sequence of qualifier that should be disjoined. If you
have a 'Qualifier.OrQualifier' instance 'orQualifier', you should
supply 'orQualifier.qualifiers()' as the argument
See also: sqlStringForQualifier()
"""
sqlStr='('
for qualifier in qualifiers:
sqlStr+=self.sqlStringForQualifier(qualifier)+ ' OR '
sqlStr=sqlStr[:-4]+')'
return sqlStr
def sqlStringForJoinSemantic(self, joinSemantic):
"""
"""
if joinSemantic==INNER_JOIN:
return 'INNER JOIN'
elif joinSemantic==FULL_OUTER_JOIN:
return 'FULL OUTER JOIN'
elif joinSemantic==LEFT_OUTER_JOIN:
return 'LEFT OUTER JOIN'
elif joinSemantic==RIGHT_OUTER_JOIN:
return 'RIGHT OUTER JOIN'
else:
raise ValueError, 'Unknown joinSemantic (%s)'%joinSemantic
def sqlStringForKeyComparisonQualifier(self, aQualifier):
"""
Returns the SQL string for the KeyComparisonQualifier supplied.
It is of the form::
<left_columnName> <operator> <right_columnName>
such as in::
t0.LAST_NAME != t2.FIRST_NAME
Both column names are returned by sqlStringForAttributeNamed(), and
the operator is computed by sqlStringForSelector().
When the quelifier's operator is
Qualifier.QualifierOperatorCaseInsensitiveLike, the returned string is
calculated by sqlStringForCaseInsensitiveLike.
Parameter:
aQualifier -- a Qualifier.KeyComparisonQualifier instance
See also: sqlStringForQualifier()
"""
caseInsensitive=0
if aQualifier.operator()==QualifierOperatorCaseInsensitiveLike:
caseInsensitive=1
lkey=aQualifier.leftKey()
rkey=aQualifier.rightKey()
if not caseInsensitive:
operatorStr=self.sqlStringForSelector(aQualifier.operator(), "notNone")
lkeyString=self.sqlStringForAttributeNamed(lkey)
rkeyString=self.sqlStringForAttributeNamed(rkey)
if not caseInsensitive:
return lkeyString+' '+operatorStr+' '+rkeyString
else:
return self.sqlStringForCaseInsensitiveLike(lkeyString, rkeyString)
def sqlStringForKeyValueQualifier(self, aQualifier):
"""
Returns the SQL string for the KeyValueQualifier supplied.
It is of the form::
<key> <operator> <value>
such as in::
t0.LAST_NAME LIKE 'R%'
'key' is returned by sqlStringForAttributeNamed(), 'operator', by
sqlStringForSelector(), and 'value': by sqlStringForValue(), or by
sqlPatternFromShellPattern() is the qualifier's operator is 'LIKE' or
case-insensitive-LIKE (and, when the case-insensitive form of 'LIKE' is
used, the returned string is computed by sqlStringForCaseInsensitiveLike)
Parameter:
aQualifier -- a Qualifier.KeyValueQualifier instance
See also: sqlStringForQualifier()
"""
caseInsensitive=0
if aQualifier.operator()==QualifierOperatorCaseInsensitiveLike:
caseInsensitive=1
key=aQualifier.key()
value=aQualifier.value()
if not caseInsensitive:
operatorStr=self.sqlStringForSelector(aQualifier.operator(), value)
if aQualifier.operator() in (QualifierOperatorCaseInsensitiveLike,
QualifierOperatorLike):
value=self.sqlPatternFromShellPattern(value)
keyString=self.sqlStringForAttributeNamed(key)
if aQualifier.operator() in (QualifierOperatorIn, QualifierOperatorNotIn):
valueString=self.sqlStringForInOperatorValue(value, key)
else:
valueString=self.sqlStringForValue(value, key)
if not caseInsensitive:
return keyString+' '+operatorStr+' '+valueString
else:
return self.sqlStringForCaseInsensitiveLike(keyString, valueString)
def sqlStringForInOperatorValue(self, aList, key):
"""
Returns the SQL string suitable for operator IN and NOT IN.
The returned string is a comma-separated list of the values in aList,
surrounded by brackets.
Example: aList=[1], return '(1)'
aList=(1,2,4), return '(1, 2, 4)'
"""
sqlString=lambda v,k=key,self=self: self.sqlStringForValue(v, k)
if len(aList)==1:
return '(%s)'%sqlString(aList[0])
else:
return "(%s)" % ", ".join(map(sqlString, aList))
def sqlStringForNegatedQualifier(self, aQualifier):
"""
Returns the SQL string for the supplied Qualifier
The string returned is::
NOT (<qualification>)
where 'qualification' is 'sqlStringForQualifier(aQualifier)'
Parameter:
aQualifier -- the qualifier to negate, **NOT** the corresponding
'NotQualifier'. If 'notQualifier' is is Qualifier.NotQualifier, you
should supply 'notQualifier.qualifier()' as the argument.
"""
return 'NOT ('+self.sqlStringForQualifier(aQualifier)+')'
def sqlStringForNumber(self, aNumber, keyPath=''):
"""
Formats 'aNumber' and returns the string suitable for inclusion in a SQL
statement. SQLExpression's implementation simply returns 'str(aNumber)',
or 'NULL' if 'aDate' is None.
See also: formatValueForAttribute()
"""
if aNumber is not None:
return str(aNumber)
else: return 'NULL'
def sqlStringForQualifier(self, aQualifier):
"""
Returns the SQL WHERE clause string built from 'aQualifier' ; NB: the
generated statement does *not* include the 'WHERE' SQL keyword.
Default implementation delegates the actual generation of the SQL string
to the appropriate method [sqlStringFor...Qualifier()], depending on
aQualifier's type/class.
Note that this method does not set the whereClauseString() ; it is the
responsability for the caller to set it when appropriate.
See also: whereClauseString(),
sqlStringForKeyValueQualifier(), sqlStringForNegatedQualifier(),
sqlStringForKeyComparisonQualifier(),
sqlStringForDisjoinedQualifiers(),
sqlStringForConjoinedQualifiers(),
prepareInsertExpressionWithRow(),
prepareSelectExpressionWithAttributes()
"""
if aQualifier is None:
return
qualifierIs=lambda c, qClass=aQualifier.__class__: c==qClass
if qualifierIs(KeyComparisonQualifier):
return self.sqlStringForKeyComparisonQualifier(aQualifier)
elif qualifierIs(KeyValueQualifier):
return self.sqlStringForKeyValueQualifier(aQualifier)
elif qualifierIs(AndQualifier):
return self.sqlStringForConjoinedQualifiers(aQualifier.qualifiers())
elif qualifierIs(OrQualifier):
return self.sqlStringForDisjoinedQualifiers(aQualifier.qualifiers())
elif qualifierIs(NotQualifier):
return self.sqlStringForNegatedQualifier(aQualifier.qualifier())
else:
raise ValueError, 'Unknown qualifier'
def sqlStringForSchemaObjectName(self, name):
"""
Unimplemented
"""
__unimplemented__()
def sqlStringForSelector(self, selector, value):
"""
Returns the appropriate SQL string corresponding to 'selector'
Parameters:
selector -- an operator of module Qualifier (QualifierOperatorEqual,
etc.)
value -- the right-value used with the operator
Depending on 'operator', the returned value is:
- for 'QualifierOperatorEqual': '=' is 'value' is not None, 'IS'
otherwise
- for 'QualifierOperatorNotEqual': '<>' is 'value' is not None,
'IS NOT' otherwise
- for 'QualifierOperatorGreaterThan': '>'
- for 'QualifierOperatorGreaterThanOrEqualTo': '>='
- for 'QualifierOperatorLessThan': '<'
- for 'QualifierOperatorLessThanOrEqualTo': '<='
- for 'QualifierOperatorLike': 'LIKE'
Raise 'ValueError' if 'selector' is not in that list. Note that
'QualifierOperatorCaseInsensitiveLike' is *not* in the list: this is
intended. Specific transformations for this operator are handled by
the specific methods sqlStringForKeyValueQualifier(), etc.
See also: sqlStringForKeyValueQualifier() and others related methods
"""
selectorIs=lambda s, selector=selector: s==selector
if selectorIs(QualifierOperatorEqual):
if value is not None: return '='
else: return 'IS'
elif selectorIs(QualifierOperatorNotEqual):
if value is not None: return "<>"
else: return "IS NOT"
elif selectorIs(QualifierOperatorGreaterThan):
return ">"
elif selectorIs(QualifierOperatorGreaterThanOrEqualTo):
return ">="
elif selectorIs(QualifierOperatorLessThan):
return "<"
elif selectorIs(QualifierOperatorLessThanOrEqualTo):
return "<="
elif selectorIs(QualifierOperatorLike):
return "LIKE"
elif selectorIs(QualifierOperatorIn):
return "IN"
elif selectorIs(QualifierOperatorNotIn):
return "NOT IN"
raise ValueError, 'Unknown selector: %s'%repr(selector)
def sqlStringForString(self, aString):
"""
Formats 'aString' and returns the string suitable for inclusion in a SQL
statement. SQLExpression's implementation surrounds the string with
simple quotes, and back-quotes any simple quotes 'aString' may have.
It returns 'NULL' if 'aString' is None.
See also: formatValueForAttribute()
"""
if aString is not None:
str=escapeQuote.sub("\\'", aString)
return "'"+str+"'"
else:
return 'NULL'
def sqlStringForValue(self, value, keyPath):
"""
Simply calls formatValueForAttribute() and return the formatted string
Implementation note: when SQL bind variable are supported this should
update the bindings dictionary and return the name of the bind variable.
"""
try:
finalAttribute=self._entity.destinationObjectForKeyPath(keyPath)
except AttributeError:
raise ValueError, "Invalid keypath %s"%keyPath
return self.formatValueForAttribute(value, finalAttribute)
def statement(self):
"""
Returns the statement assigned to this object. The statement could have
been set either with 'setStatement()' or by any of the following methods:
- prepareDeleteExpressionForQualifier()
- prepareInsertExpressionWithRow()
- prepareSelectExpressionWithAttributes()
- prepareUpdateExpressionWithRow()
"""
return self._statement
def tableListWithRootEntity(self, anEntity):
"""
"""
trace(self._internals.showInternals())
if self.useAliases():
res=''
aliases=self._internals.entityExternalNamesByAliases().keys()
aliases.sort()
while aliases: # not all aliases are nested in/depending on others
# e.g. an entity can fetch its data from different tables
alias=aliases[0]
res, aliasesToRemove=self._addTableJoinsForAlias(alias, res)
res+=', '
for aliasToRemove in aliasesToRemove:
try: aliases.remove(aliasToRemove)
except ValueError: pass
# end
res=res[:-2] # remove trailing comma
else: # does not use table aliases
res=anEntity.externalName()
return res
def _addTableJoinsForAlias(self, alias, str):
"""
Calculates the SQL statements for JOIN.
The default returns::
table0 alias0 INNER JOIN [ (some other nested join) | table1 alias1 ]
ON (join condition)
except if self.SQL92_join is false: in this case, what is identified as
the "join condition" here above is appended to self._joinClauseString (for
use inside a WHERE clause), and the returned string is simply "table0
alias0, table1 alias1, ..."
Returns: (str, aliases): a string (see above) and the list of aliases that
were used to compute the string.
"""
trace('alias: %s / str: %s'%(alias, str))
relPaths=self._internals.relPathsComingFromAlias(alias)
aliases=map(lambda rp, self=self: self._internals.aliasForRelPath(rp),
relPaths)
str+=self._internals.entityExternalNameForAlias(alias)+' '+alias
processedAliases_total=[]
for boundAlias in aliases:
currentRelPath=self._internals.relPathForAlias(boundAlias)
trace('relPath for bound alias: %s'%(currentRelPath))
if self.SQL92_join:
str+=' '+self.sqlStringForJoinSemantic(self._internals.joinSemanticForRelPath(currentRelPath))+' '
str2, processedAliases=self._addTableJoinsForAlias(boundAlias, '')
if string.find(str2, 'JOIN')!=-1:
str+=' ( '+str2+' ) '
else:
str+=str2
str+=' ON '
srcKeys=self._internals.sourceKeysForRelPath(currentRelPath)
dstKeys=self._internals.destinationKeysForRelPath(currentRelPath)
joinClause=''
for idx in range(len(srcKeys)):
joinClause+='%s.%s=%s.%s AND '%(alias, srcKeys[idx], boundAlias, dstKeys[idx])
joinClause=joinClause[:-5]
if self.SQL92_join:
str+=joinClause
else:
if self._joinClauseString:
self._joinClauseString+=' AND '+joinClause
else:
self._joinClauseString=joinClause
str2, processedAliases=self._addTableJoinsForAlias(boundAlias, '')
str+=', '+str2
processedAliases_total.extend(processedAliases)
aliases.extend(processedAliases_total)
aliases.append(alias)
return (str, aliases)
def toString(self):
"""
Simply returns self.statement()
"""
return self.statement()
def useAliases(self):
"""
Returns whether the SQLExpression uses table aliases.
See also: setUseAliases()
"""
return self._useAliases
def useBindVariables(self):
"""
Bind variables are not supported yet: return '0' (false)
"""
return 0
def valueList(self):
"""
This is set when prepareInsertExpressionWithRow() is called, and it holds
the list of values to insert.
Returned value is a string, such as::
"12, 'Hugo', 'Victor', NULL"
See also: listString(), prepareInsertExpressionWithRow()
"""
return self._valueList.getvalue()
def valueTypeForExternalTypeMapping(self):
"""
Returns a dictionary consisting of the valid SQL types, each of which
being a key bound to either 'BinaryType', 'CharacterType', 'DateType' or
'NumericType' (module's constants)
**Important**: the SQL types should be lower-cased
Default implementation returns::
{ 'char': CharacterType,
'float': NumericType,
'int': NumericType,
'integer': NumericType,
'numeric': NumericType,
'date': DateType,
'time': DateType,
'timestamp': DateType,
'varchar': CharacterType,
}
See also: valueTypeForExternalType()
"""
return { 'char': CharacterType,
'float': NumericType,
'int': NumericType,
'integer': NumericType,
'numeric': NumericType,
'date': DateType,
'time': DateType,
'timestamp': DateType,
'varchar': CharacterType,
}
def valueTypeForExternalType(self, externalTypeName):
"""
Returns the value type corresponding to 'externalTypeName'.
The correspondance table is the following:
- char, varchar: CharacterType
- int, integer: NumericType
- float, numeric: NumericType
- date, time, timestamp: DateType
Returns None when no corresponding value type can be found.
Parameter:
externalTypeName -- a SQL datatype. It is lower-cased before
valueTypeForExternalTypeMapping() is asked for the corresponding value
type.
See also: formatValueForAttribute(), valueTypeForExternalTypeMapping()
"""
type=string.lower(externalTypeName)
return self.valueTypeForExternalTypeMapping().get(type)
def whereClauseString(self):
"""
Returns the WHERE clause string generated for a given SQL statement.
This is set by: prepareInsertExpressionWithRow(),
prepareSelectExpressionWithAttributes()
"""
return self._whereClauseString
def __unimplemented__(msg='Unimplemented yet'):
raise NotImplementedError, msg
|