On Feb 19, 2:32 pm, "Troy" <[EMAIL PROTECTED]> wrote:
> Even though MySQL allows the setting of COLLATE, it does not support
> functional indexes, so if your code explicitly calls lower you
> technically now have code that will work for both MySQL and Postgres,
> but MySQL is going to take a big performance hit and perform table
> scans regardless of any created indexes. Realistically, I can't see
> anyone setting MySQL collate rules to case-sensitive without the
> support of functional indexes, which MySQL does not have.
right...so some explicitness is required, somewhere (since SA cant
just put lower() across the board).
>
> In simple terms, if the Postgres dialect supported
> compare_insensitive=True|False|[upper|lower]?
on a Column-by-Column or even per-operation context is probably more
useful.
> sqlalchemy easily support Postgres and MySQL with the same code in out-
> of-the-box configurations. But even if it didn't, if there was a way
> to override the default postgres dialect I'd be a happy camper.
> Infact, that is exactly what I have done. I added a dialect called
> "lowergres", but I'm stuck because I can not seem to find the
> appropriate hook to alter the column to a func.lower when the column
> is not part of the selected column list.
format_column looks to me
> like the right place to do it without converting a column to a
> function, but the column object in that function has no context as to
> where it lies in the sql statement.
for this approach, id advise setting "state" on the compiler when you
are processing the column clause of a SELECT (or dealing with insert/
update column lists too), vs. when you are processing WHERE, ORDER BY,
etc. that flag can be used to determine where youre traversing. the
compiler knows the context since its the one generating the statement.
a more accurate way to do is to wrap the Column itself, as it goes
into a comparison operation or ORDER BY, in a new construct such as
"CaseInsensitive"...heres some pseudo-ish code based on the __eq__()
method you see in sqlalchemy/sql.py _CompareMixin (assume "self" is a
Column):
def __eq__(self, other):
return _BooleanExpression(CaseInsensitive(self), other, '==')
Better yet its configurable on Column:
def __eq__(self, other):
if self.case_insensitive_compare:
return _BooleanExpression(CaseInsensitive(self), other, '==')
else:
return _BooleanExpression(self, other, '==')
CaseInsensitive looks a lot like _Function and is just:
class CaseInsensitive(_CalculatedClause):
def __init__(self, target):
self.target = target
def accept_visitor(self, visitor):
self.target.accept_visitor(visitor)
visitor.visit_case_insensitive(self)
ansicompiler provides the string representation of the underlying
target with no modification:
def visit_case_insensitive(self, object):
self.strings[object] = self.strings[object.target]
postgres compiler with case_insensitive provides it as:
def visit_case_insensitive(self, object):
self.strings[object] = "lower(%s)" % self.strings[object.target]
other dialects can have whatever flags to turn on/off the "lower()"
wrapping as well.
what we're really talking about here is a func.lower() that has
special meaning to the compiler, i.e. that it should be conditionally
applied based on dialect flags. i think the flag on Column to have
the wrapper applied might be pretty slick.
>
> I'm curious as to others experiences with writing an app that supports
> both Postgres and MySQL with sqlalchemy, because if someone else is
> doing this then I must be missing something, or maybe not. At first,
> our unit tests all passed, then when we added real world data with
> mixed case, tests started to fail on everything doing sorts and
> where's on character data.
yeah i dont think anyone has gotten too far with this issue, also ive
no idea what the giant universe of Hibernate users do either (i think
there just arent db-platform-neutral J2EE apps).
>
> How about a Pepsi (at PyCon)?
sure thing !
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
