Hi Michael,
checking for "proxies" did not work for aliases when using a "Query"
object:
q = Query(mappedclass_alias).filter(mappedclass_alias.id ==
2).limit(1)
print q.statement
SELECT table_1.id, table_1.text, table_1.geom
FROM "table" AS table_1
WHERE table_1.id = :id_1
LIMIT 1
But your 2nd suggestion works fine, my compiles method now looks like
this:
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if isinstance(element.table, (Table, Alias)):
if kw.has_key("within_columns_clause") and
kw["within_columns_clause"] == True:
return compiler.process(functions.wkb(element))
return compiler.visit_column(element)
Thanks a lot, you are doing an awesome job!
Tobias
On May 11, 5:21 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> ...
>
> read more »
--
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.
