now you're on the big stage where your column is properly recognzied, so you
have to write the compiles method correctly:
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if not getattr(element, 'proxies', None):
if kw.has_key("within_columns_clause") and kw["within_columns_clause"]
== True:
return "AsBinary(%s)" % element
return compiler.visit_column(element)
I cannot say for sure if checking for "proxies" is always the correct way to
tell if you're derived or not. Another way is to check the parent "table" of
the column and seeing if it is an instance of Table or Select.
On May 11, 2010, at 10:49 AM, Tobias wrote:
> Ok, I made a basic example. Thanks for taking a look at it!
>
>
> from sqlalchemy import MetaData, Table, Column, create_engine
> from sqlalchemy.types import Integer, Unicode
> from sqlalchemy.orm import mapper
> from sqlalchemy.orm.query import Query
> from sqlalchemy.sql import func, and_
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql import select
>
> engine = create_engine('oracle://dummy:du...@dummy:1521/dummy',
> echo=True)
> Base = declarative_base(metadata=MetaData())
>
> class GeometryExtensionColumn(Column):
> pass
>
> @compiles(GeometryExtensionColumn)
> def compile_column(element, compiler, **kw):
> if kw.has_key("within_columns_clause") and
> kw["within_columns_clause"] == True:
> return "AsBinary(%s)" % element
>
> return element.__str__()
>
> class MappedClass(Base):
> __tablename__ = "table"
> id = Column(Integer, primary_key=True)
> text = Column(Unicode)
> geom = GeometryExtensionColumn(Unicode)
>
> print select([MappedClass.__table__]).where(MappedClass.id ==
> 1).limit(1).compile(engine)
>
> # Output for release 0.6.0
> #
> #SELECT id, text, geom
> #FROM (SELECT "table".id AS id, "table".text AS text,
> AsBinary(table.geom) AS geom
> #FROM "table"
> #WHERE "table".id = :id_1)
> #WHERE ROWNUM <= :ROWNUM_1
>
>
> # Output for current tip (b03613c840a4)
> #
> #SELECT id, text, AsBinary(geom)
> #FROM (SELECT "table".id AS id, "table".text AS text,
> AsBinary(table.geom) AS geom
> #FROM "table"
> #WHERE "table".id = :id_1)
> #WHERE ROWNUM <= :ROWNUM_1
>
>
> On May 11, 2:41 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> I would need fully working code to test. just one python file with the
>> minimal reproducing situation.
>>
>> On May 11, 2010, at 2:41 AM, Tobias wrote:
>>
>>
>>
>>> Hi Michael,
>>
>>> thanks for your reply. The current tip seems to fix that problem
>>> without having to override '_make_proxy()'. But there is another
>>> problem I have with the current tip even without aliases.
>>
>>> The following query worked fine in release 0.6.0, but in the current
>>> tip the outer queries are not compiled correctly:
>>
>>> s1 = session.query(Spot).filter(Spot.spot_id == 1).first()
>>
>>> Query sent in release 0.6.0:
>>
>>> 2010-05-11 08:25:01,938 INFO sqlalchemy.engine.base.Engine.0x...112c
>>> SELECT spots_spot_location, spots_spot_id, spots_spot_height
>>> FROM (SELECT spots_spot_location, spots_spot_id, spots_spot_height,
>>> ROWNUM AS ora_rn
>>> FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
>>> spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
>>> AS spots_spot_height
>>> FROM spots
>>> WHERE spots.spot_id = :spot_id_1)
>>> WHERE ROWNUM <= :ROWNUM_1)
>>> WHERE ora_rn > :ora_rn_1
>>> 2010-05-11 08:25:01,939 INFO sqlalchemy.engine.base.Engine.0x...112c
>>> {'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
>>
>>> Query sent in current tip:
>>
>>> 2010-05-11 08:28:33,856 INFO sqlalchemy.engine.base.Engine.0x...838c
>>> SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location), spots_spot_id,
>>> spots_spot_height
>>> FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location),
>>> spots_spot_id, spots_spot_height, ROWNUM AS ora_rn
>>> FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
>>> spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
>>> AS spots_spot_height
>>> FROM spots
>>> WHERE spots.spot_id = :spot_id_1)
>>> WHERE ROWNUM <= :ROWNUM_1)
>>> WHERE ora_rn > :ora_rn_1
>>> 2010-05-11 08:28:33,857 INFO sqlalchemy.engine.base.Engine.0x...838c
>>> {'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
>>
>>> Only the innermost query should contain "SELECT
>>> SDO_UTIL.TO_WKBGEOMETRY(..)".
>>
>>> Tobias
>>
>>> On May 10, 5:40 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>>> creating an alias() or otherwise using the .c. collection of any
>>>> selectable that's derived from another selectable (as when you say
>>>> select([sometable]).c.somecolumn) means that the Column objects are
>>>> actually copies of the original column objects. This copying procedure is
>>>> performed by column._make_proxy(). in rb03613c840a4 I have modified
>>>> this so that it uses the effective class of the object, typically
>>>> self.__class__, when it constructs the new Column object. However in 0.6
>>>> it is harcdoded to "ColumnClause" and/or "Column". So for the current
>>>> release, you'd have to ensure your GeometryExtensionColumn is overriding
>>>> _make_proxy() as well. I would use the _make_proxy() of Column to get its
>>>> result, then change the class of the returned Column to
>>>> GeometryExtensionColumn on the way out. you might want to first check
>>>> that the problem goes away when using the current tip.
>>
>>>> On May 10, 2010, at 6:07 AM, Tobias wrote:
>>
>>>>> Hi,
>>
>>>>> I am currently working on adding support for Oracle to GeoAlchemy and
>>>>> Oracle has some methods [1] that (somehow) are only recognized when a
>>>>> table alias is used. The function "aliased" [2] seemed to work
>>>>> perfectly, but then I realized that the compiler extension for my
>>>>> custom column is not executed anymore.
>>
>>>>> The compiler extension looks like this [3]:
>>
>>>>> [..]
>>>>> class GeometryExtensionColumn(Column):
>>>>> pass
>>
>>>>> @compiles(GeometryExtensionColumn)
>>>>> def compile_column(element, compiler, **kw):
>>>>> if kw.has_key("within_columns_clause") and
>>>>> kw["within_columns_clause"] == True:
>>>>> return compiler.process(functions.wkb(element))
>>
>>>>> return element.__str__()
>>>>> [..]
>>
>>>>> And if I make a query using the original mapped class, it works as
>>>>> expected:
>>
>>>>> s = session.query(Spot).get(1)
>>
>>>>> 2010-05-10 11:49:19,957 INFO sqlalchemy.engine.base.Engine.0x...408c
>>>>> SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
>>>>> spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
>>>>> AS spots_spot_height
>>>>> FROM spots
>>>>> WHERE spots.spot_id = :param_1
>>>>> 2010-05-10 11:49:19,958 INFO sqlalchemy.engine.base.Engine.0x...408c
>>>>> {'param_1': 1}
>>
>>>>> But when I create an alias and use this alias in a query,
>>>>> "compile_column" is not called anymore and in this case
>>>>> "SDO_UTIL.TO_WKBGEOMETRY" is not added to the query:
>>
>>>>> spot_alias = aliased(Spot)
>>>>> s_alias = session.query(spot_alias).filter(spot_alias.spot_id ==
>>>>> 1).first()
>>
>>>>> 2010-05-10 11:49:36,481 INFO sqlalchemy.engine.base.Engine.0x...408c
>>>>> SELECT spots_1_spot_location, spots_1_spot_id, spots_1_spot_height
>>>>> FROM (SELECT spots_1_spot_location, spots_1_spot_id,
>>>>> spots_1_spot_height, ROWNUM AS ora_rn
>>>>> FROM (SELECT spots_1.spot_location AS spots_1_spot_location,
>>>>> spots_1.spot_id AS spots_1_spot_id, spots_1.spot_height AS
>>>>> spots_1_spot_height
>>>>> FROM spots spots_1
>>>>> WHERE spots_1.spot_id = :spot_id_1)
>>>>> WHERE ROWNUM <= :ROWNUM_1)
>>>>> WHERE ora_rn > :ora_rn_1
>>
>>>>> What is going wrong?
>>
>>>>> Thanks,
>>>>> Tobias
>>
>>>>> [1]:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_ob...
>>>>> [2]:http://www.sqlalchemy.org/docs/ormtutorial.html#using-aliases
>>>>> [3]:http://bitbucket.org/geoalchemy/geoalchemy/src/c0bfcd46cb3a/geoalchem...
>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "sqlalchemy" group.
>>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>>> To unsubscribe from this group, send email to
>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>> For more options, visit this group
>>>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>> To unsubscribe from this group, send email to
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> For more options, visit this group
>>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group
>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group
>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
