# -*- 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.
#-----------------------------------------------------------------------------
"""
MySQLSQLExpression
CVS information
$Id: MySQLSQLExpression.py 933 2004-08-02 19:58:54Z sbigaret $
"""
__version__='$Revision: 933 $'[11:-2]
from Modeling.DatabaseAdaptors.MySQLAdaptorLayer.mysql_utils import *
from Modeling.SQLExpression import SQLExpression
from Modeling.SQLExpression import DateType,CharacterType,NumericType
from Modeling.logging import trace,db_trace
import string
class MySQLSQLExpression(SQLExpression):
"""
Sub-class of SQLExpression for the MySQLAdaptor. It overrides
valueTypeForExternalTypeMapping() to remove the TIMESTAMP type from the
available SQL data types.
"""
def columnTypeStringForAttribute(self, attribute):
"""
Overrides default implementation to handle TEXT field properly.
It 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 and its externalType is not TEXT:
'externalType(width)'
- otherwise returns: 'externalType'
About 'TEXT': a model can define a width for a TEXT field. It should be
ignored when generating the database schema (for example, TEXT(30) is an
invalid data type), however it might be set to request the validation
mechanism to check the attribute's length at runtime, typically before an
EditingContext saves its changes.
See also:
addCreateClauseForAttribute()
SchemaGeneration.createTableStatementsForEntityGroup()
"""
if attribute.precision():
return attribute.externalType()+'('+\
str(attribute.precision())+','+str(attribute.scale())+')'
if attribute.width() and attribute:
if string.lower(attribute.externalType())!='text':
return attribute.externalType()+'('+\
str(attribute.width())+')'
return attribute.externalType()
def valueTypeForExternalTypeMapping(self):
"""
REMOVES 'timestamp' from the available SQL types returned by
Modeling.SQLExpression, and adds 'datetime' and 'text'.
The reason for the removal of 'timestamp' is that, in MySQL, a DateTime
field is likely to be modified during an update operation, completely
implicitly. This would cause weird bugs, because the framework does not
expect values to change implicitly.
Quoted from
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME:
The TIMESTAMP column type provides a type that you can use to
automatically mark INSERT or UPDATE operations with the current date and
time. If you have multiple TIMESTAMP columns, only the first one is
updated automatically.
Automatic updating of the first TIMESTAMP column occurs under any of the
following conditions:
* The column is not specified explicitly in an INSERT or LOAD DATA
INFILE statement.
* The column is not specified explicitly in an UPDATE statement and
some other column changes value. (Note that an UPDATE that sets a
column to the value it already has will not cause the TIMESTAMP
column to be updated, because if you set a column to its current
value, MySQL ignores the update for efficiency.)
* You explicitly set the TIMESTAMP column to NULL.
(end of quote)
"""
values=SQLExpression.valueTypeForExternalTypeMapping.im_func(self)
del values['timestamp']
values.update({ 'datetime': DateType,
'text': CharacterType,
'double': NumericType })
return values
def prepareSelectCountExpressionWithAttributes(self,
attributes,lock,fetchSpec):
"""
Overrides the default method and prepares a SQL expression suitable for
MySQL, counting the rows qualified by 'fetchSpec'.
The SQL statement depends on whether 'fetchSpec' implies joining two or
more tables:
- if at least a join is needed, the statement is like::
SELECT COUNT(DISTINCT t0.pk1, t0.pk2, ...) FROM ...
INNER JOIN ...
[WHERE <fetchSpec's condition>]
where pk1, pk2 are the primary keys in the fetchSpec's entity,
- otherwise, it is like:
SELECT COUNT(*) FROM <table> [WHERE <fetchSpec's condition>]
Parameters:
attributes -- the attributes to fetch (they do not appear in the sql)
lock -- Unimplemented, should be false
fetchSpec -- the FetchSpecification qualifying the rows to be counted
"""
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 'Unimplemented', \
"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
self._whereClauseString=self.sqlStringForQualifier(fullQualifier)
# Do we join some tables?
multiple_tables_joined=len(self.entityExternalNamesByAliases())>1
selectString=''
columnList=' COUNT(*) '
if multiple_tables_joined:
columnList='COUNT(DISTINCT '
# TBD: cf. test_14: if we use this:
#self._internals.aliasForEntityExternalName(self._entity.externalName())
# then we select from the wrong table (t3 instead of t0)
entity_alias='t0'
for pk in self._entity.primaryKeyAttributes():
columnList+=entity_alias+'.'+pk.columnName()+', '
columnList=columnList[:-2]+') '
else:
columnList=' COUNT(*) '
whereClause=self.whereClauseString()
# orderBy: see fetchSpec...
orderByClause=''
fetchOrder=None
#
tableList=self.tableListWithRootEntity(self.entity())
# The join clause is now built, if necessary
joinClause=self.joinClauseString()
self.assembleSelectStatementWithAttributes(attributes=attributes,
lock=lock,
qualifier=fullQualifier,
fetchOrder=fetchOrder,
selectString=selectString,
columnList=columnList,
tableList=tableList,
whereClause=whereClause,
joinClause=joinClause,
orderByClause=orderByClause,
lockClause=lockClause)
def sqlStringForSelector(self, selector, value):
"""
Overrides the default behaviour to ensure that Qualifier operator
'LIKE' matches case-sensitively.
Note: this is because the SQL LIKE operator is case-insensitive by default
in MySQL.
Returns:
- if selector is Modeling.Qualifier.QualifierOperatorLike,
returns "LIKE BINARY"
- otherwise returns SQLExpression.sqlStringForSelector()
See also: SQLExpression.sqlStringForSelector()
"""
from Modeling.Qualifier import QualifierOperatorLike
selectorIs=lambda s, selector=selector: s==selector
if selectorIs(QualifierOperatorLike):
return "LIKE BINARY"
else:
return SQLExpression.sqlStringForSelector(self, selector, value)
if mysql_server_version()[:2]>=(4,0):
MySQLSQLExpression.SQL92_join=1
else:
MySQLSQLExpression.SQL92_join=0
|