On 4/20/2010 6:25 PM, Michael Bayer wrote:
On Apr 20, 2010, at 4:47 PM, Lance Edgar wrote:
Hi, I'm writing a new custom dialect for a legacy database (Centura
SQLBase 7.5.1) for use in migrating to a new system over time.
Everything's gone pretty well, until I needed a join...
Whereas most dialects would create a statement such as:
SELECT T1.ID, T1.COL1, T2.COL2
FROM T1 JOIN T2
ON T1.ID = T2.ID
WHERE T1.ID = 100
, (at least this flavor of) SQLBase expects it to be like so:
SELECT T1.ID, T1.COL1, T2.COL2
FROM T1, T2
WHERE T1.ID = T2.ID
AND T1.ID = 100
And in fact "JOIN" isn't even one of their reserved words, so sending
it a statement like the first example will of course cause an error.
I've subclassed sqlalchemy.sql.compiler.SQLCompiler in the hopes of
overriding the visit_join method for my dialect, but I'm not sure it's
possible to achieve what I'm after this way? I can of course replace
the " JOIN " text with ", " but if I replace " ON " with " WHERE "
then all of a sudden the final statement has two WHERE clauses and is
thus invalid for a whole new reason.
Is there a way to override the visit_join method to accomplish my goal
or should I be looking somewhere else? (I assume I can add custom
@properties to my data class, for instance. I'd like to solve the
"bigger" problem here but if I can't then I really just need a way
past this particular problem.) TIA, I really appreciate any help.
the Oracle dialect does this, when use_ansi =False. You should copy the code
directly from there ....lib/sqlalchemy/dialects/oracle/base.py.
The only big missing thing here is OUTER JOIN. Oracle 8 uses (+) to indicate an
"outer" join, does SQLBase have something similar ?
Michael, thanks for the tip(s). From what I can tell, SQLBase does also use
the (+) syntax for outer joins. However, my requirements for this dialect are
so specific that I won't bother adding that to my compiler unless/until I or
someone else actually needs it.
In the meantime, I'm making the package available at:
http://sqlbase7-sa.edbob.org/
I'm assuming there's no reason whatsoever to include the dialect within the
sqlalchemy codebase, but I mention the project site in case anyone else would
find it useful. You're welcome to link to it as you have the Progress dialect
(http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#Progress), which BTW I also
appreciated as a starting point, but I suspect the audience will be terribly
small or nonexistent so it's up to you.
Lance
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
