sqlalchemy-tickets — trac tickets and updates forwarded to this list

Re: [Sqlalchemy-tickets] [sqlalchemy] #2651: provide a "no-bind-param" version of Text() and apply this to DDL

[sqlalchemy <mi...@zz...>]

#2651: provide a "no-bind-param" version of Text() and apply this to DDL
-----------------------------------+----------------------------------
      Reporter:  gthb              |      Owner:  zzzeek
          Type:  enhancement       |     Status:  closed
      Priority:  high              |  Milestone:  0.9.0
     Component:  schema            |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 changed my mind on this - would rather not add another backwards
 incompatible surprise to 0.9, i think we have a lot.

 instead, the code above does raise on 0.9 now because `Text()` does run
 the bind params through now.

 Added a doc example to `CheckConstraint` and improved the exception raised
 in :

 r0fefc6e22641287100eb0648cf1264daeefeb020 0.8, but the exception doesnt
 raise for CheckConstraint

 r207aaf2f41cff5970b34999d3cfc845a3b0df29c 0.9, the above code raises

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2651#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2887: subqueryload query invokes ahead of parent loader init, can cause conflicts

[sqlalchemy <mi...@zz...>]

#2887: subqueryload query invokes ahead of parent loader init, can cause conflicts
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  in progress => completed/closed


Comment:

 rdcb7e7759ae85b2cc4d6a93fffd9746365ffe45a 0.8

 r84f1d3417978197c695850b3711ea4b7e2582be8 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2887#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2885: reduce for proxied columns when syncing between mappers

[sqlalchemy <mi...@zz...>]

#2885: reduce for proxied columns when syncing between mappers
-----------------------------------+----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * status_field:  needs tests => completed/closed
 * resolution:   => fixed
 * milestone:  0.9.0 => 0.8.xx


Comment:

 re294767148c7293170f36d56605853eb76f20714 0.8

 r1d9eb4101365e2c7b8d434fae403aa876ccbef59 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2885#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2581: PostgreSQL-9.2 JSON datatype

[sqlalchemy <mi...@zz...>]

#2581: PostgreSQL-9.2 JSON datatype
------------------------------+-------------------------------
      Reporter:  plaes        |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  medium       |  Milestone:  0.9.xx
     Component:  postgres     |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+-------------------------------
Changes (by snotling):

 * cc: damiano.albani@… (added)


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2581#comment:18>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2892: add PG's "create_type" argument to the base Enum type; also look into "create_constraint" for SchemaType in general (was: add PG's "create" argument to the base Enum type; possibly rename it to "create_type"; also look into "create_constraint" for SchemaType in general)

[sqlalchemy <mi...@zz...>]

#2892: add PG's "create_type" argument to the base Enum type; also look into
"create_constraint" for SchemaType in general
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.0
     Component:  sql          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+-------------------------------

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2892#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2892: add PG's "create" argument to the base Enum type; possibly rename it to "create_type"; also look into "create_constraint" for SchemaType in general

[sqlalchemy <mi...@zz...>]

#2892: add PG's "create" argument to the base Enum type; possibly rename it to
"create_type"; also look into "create_constraint" for SchemaType in general
-------------------------+------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  high         |       Milestone:  0.9.0
Component:  sql          |        Severity:  major - 1-3 hours
 Keywords:               |  Progress State:  in queue
-------------------------+------------------------------------


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2892>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2891: Support materialized views in PostgreSQL dialect

[sqlalchemy <mi...@zz...>]

#2891: Support materialized views in PostgreSQL dialect
------------------------------+----------------------------------
      Reporter:  eseifert     |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  postgres     |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+----------------------------------

Comment (by zzzeek):

 additionally, as this seems to be new in pg 9.3, I should add a new
 @requirements rule that checks for PG version 9.3, not to mention need to
 upgrade PG versions locally and on jenkins...

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2891#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2891: Support materialized views in PostgreSQL dialect

[sqlalchemy <mi...@zz...>]

#2891: Support materialized views in PostgreSQL dialect
------------------------------+----------------------------------
      Reporter:  eseifert     |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  postgres     |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => needs tests


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2891#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2891: Support materialized views in PostgreSQL dialect

[sqlalchemy <mi...@zz...>]

#2891: Support materialized views in PostgreSQL dialect
----------------------------------+------------------------------------
      Reporter:  eseifert         |      Owner:  zzzeek
          Type:  enhancement      |     Status:  new
      Priority:  medium           |  Milestone:  0.8.xx
     Component:  postgres         |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by zzzeek):

 OK so we think adding 'm' is all that's needed?    can you either attach
 some short test case (e.g. including a PG MATERIALIZED VIEW create), or a
 pullrequest with such?   this is a one liner code change but we'd need a
 test in test/dialects/postgresql/test_reflection.py for it.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2891#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2891: Support materialized views in PostgreSQL dialect

[sqlalchemy <mi...@zz...>]

#2891: Support materialized views in PostgreSQL dialect
-------------------------+-----------------------------------------
 Reporter:  eseifert     |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  medium       |       Milestone:  0.8.xx
Component:  postgres     |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 At the moment, with SQLAlchemy 0.8.4, introspecting a materialized view in
 a PostgreSQL 9.3.2 database fails with:

 {{{
   File "/tmp/test/meterializedview.py", line 16, in __init__
     self.MaterializedView = Table('mytable', MetaData(), autoload=True,
 autoload_with=self.db)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 332, in __new__
     table._init(name, metadata, *args, **kw)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 396, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line
 413, in _autoload
     self, include_columns, exclude_columns
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1595, in run_callable
     return conn.run_callable(callable_, *args, **kwargs)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1118, in run_callable
     return callable_(self, *args, **kwargs)
   File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py",
 line 262, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 414, in reflecttable
     for col_d in self.get_columns(table_name, schema, **tblkw):
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 254, in get_columns
     **kw)
   File "<string>", line 1, in <lambda>
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1678, in
 get_columns
     info_cache=kw.get('info_cache'))
   File "<string>", line 1, in <lambda>
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "/usr/lib64/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1593, in
 get_table_oid
     raise exc.NoSuchTableError(table_name)
 }}}

 {{{get_table_oid()}}} executes the following statement:

 {{{#!sql
 SELECT c.oid
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE (%s)
 AND c.relname = :table_name AND c.relkind in ('r','v')
 }}}

 However, a new {{{relkind}}} value {{{m}}} was introduced for materialized
 views, which isn't handled at the moment.

 To (partly) solve the problem for me, i just added {{{m}}} to the where
 clause:

 {{{
 AND c.relname = :table_name AND c.relkind in ('r','v','m')
 }}}

 There are several other cases where {{{relkind}}} is used
 ({{{get_table_names}}}, {{{get_view_names}}}, {{{get_indexes}}}, etc.)
 where it isn't clear how to deal with materialized view because they are a
 hybrid of tables and views and can have their own indexes and table
 spaces.

 As far as I can see, the same issue exists in SQLAlchemy 0.9
 ([browser:lib/sqlalchemy/dialects/postgresql/base.py], rev 164bff0).

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2891>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2890: modernize polymorhpic examples (single table is classical?!) - link from the main docs

[sqlalchemy <mi...@zz...>]

#2890: modernize polymorhpic examples (single table is classical?!) - link from
the main docs
---------------------------+---------------------------------------
 Reporter:  zzzeek         |           Owner:  zzzeek
     Type:  defect         |          Status:  new
 Priority:  medium         |       Milestone:  0.9.0
Component:  documentation  |        Severity:  minor - half an hour
 Keywords:                 |  Progress State:  awaiting triage
---------------------------+---------------------------------------


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2890>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2889: raise ArgumentError when incorrect type of prop handed to a composite

[sqlalchemy <mi...@zz...>]

#2889: raise ArgumentError when incorrect type of prop handed to a composite
-----------------------------------+-----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------

Comment (by zzzeek):

 I've adjusted that message a bit in
 r752359936645a6308beb52e77dbdbfad9929a301 / r9c41c2cdc61bcc1b1f7e3 as we
 support composites to many-to-ones also, so not necessarily Column.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2889#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2889: raise ArgumentError when incorrect type of prop handed to a composite

[sqlalchemy <mi...@zz...>]

#2889: raise ArgumentError when incorrect type of prop handed to a composite
-----------------------------------+-----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  in progress => completed/closed


Comment:

 r730f585dc6878f4ab5575f847bac0d29f02686f2 0.8

 rf244287f46476f3b111a2495ce4207530f4b51d8 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2889#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2889: raise ArgumentError when incorrect type of prop handed to a composite

[sqlalchemy <mi...@zz...>]

#2889: raise ArgumentError when incorrect type of prop handed to a composite
------------------------------+-----------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  medium       |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  trivial - <10 minutes
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-----------------------------------

Comment (by zzzeek):

 see
 https://github.com/zzzeek/sqlalchemy/commit/c3fb278063022575bbf2cb5e5e48025dd006d9b5#commitcomment-4843381

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2889#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2889: raise ArgumentError when incorrect type of prop handed to a composite

[sqlalchemy <mi...@zz...>]

#2889: raise ArgumentError when incorrect type of prop handed to a composite
--------------------+----------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:  0.8.xx
Component:  orm     |        Severity:  trivial - <10 minutes
 Keywords:          |  Progress State:  in progress
--------------------+----------------------------------------
 {{{
 #!diff
 --- a/test/orm/test_composites.py
 +++ b/test/orm/test_composites.py
 @@ -6,7 +6,7 @@ from sqlalchemy import Integer, String, ForeignKey, \
  from sqlalchemy.testing.schema import Table, Column
  from sqlalchemy.orm import mapper, relationship, \
      CompositeProperty, aliased
 -from sqlalchemy.orm import composite, Session
 +from sqlalchemy.orm import composite, Session, configure_mappers
  from sqlalchemy.testing import eq_
  from sqlalchemy.testing import fixtures

 @@ -739,6 +739,24 @@ class ConfigurationTest(fixtures.MappedTest):
          })
          self._test_roundtrip()

 +    def test_check_prop_type(self):
 +        edge, Edge, Point = (self.tables.edge,
 +                                self.classes.Edge,
 +                                self.classes.Point)
 +        mapper(Edge, edge, properties={
 +            'start': sa.orm.composite(Point, (edge.c.x1,), edge.c.y1),
 +        })
 +        assert_raises_message(
 +            sa.exc.ArgumentError,
 +            # note that we also are checking that the tuple
 +            # renders here, so the "%" operator in the string needs to
 +            # apply the tuple also
 +            r"Composite expects Column or Column-bound "
 +            "attributes/attribute names as "
 +            "arguments, got: \(Column",
 +            configure_mappers
 +        )
 +
  class ComparatorTest(fixtures.MappedTest, testing.AssertsCompiledSQL):
      __dialect__ = 'default'

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2889>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2888: Misleading error message

[sqlalchemy <mi...@zz...>]

#2888: Misleading error message
-----------------------------------+----------------------------------
      Reporter:  smurf             |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * severity:  no triage selected yet => minor - half an hour
 * type:  enhancement => defect
 * component:  cextensions => orm
 * priority:  high => medium
 * milestone:   => 0.8.xx
 * resolution:   => fixed
 * status_field:  awaiting triage => completed/closed


Comment:

 I'm very sorry you had a problem!  I hope the solution here works for you.

 r69154698d20d1c15cee83990ec719d1deaf193ae 0.8

 race7bdbc78571619cb103d57a188fbe6aa92b627 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2888#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2888: Misleading error message

[sqlalchemy <mi...@zz...>]

#2888: Misleading error message
-------------------------+-----------------------------------------
 Reporter:  smurf        |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  high         |       Milestone:
Component:  cextensions  |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 Consider the following tables:


 {{{
 class Unit(Base):
     __tablename__ = 'unit'

     id = Column(Integer, primary_key=True)
     name = Column(String(20))

     def __repr__(self):
         return "<%s:%s:%s>" % (self.__class__.__name__,self.id,self.name)


 class HourData(Base):
     __tablename__ = 'hourdata'
     id = Column(Integer, primary_key=True)

     datum = Column(Date, nullable=False)
     hour = Column(Integer, nullable=False)

     unit_id = Column(Integer, ForeignKey('unit.id', name="fk_hd_unit",
 use_alter=True), nullable=False)
     unit = relationship("unit", backref='hours')

 }}}

 Executing this statement

 {{{
 session.query(Unit)
 }}}


 Then results in this error message:
 {{{
 sqlalchemy.exc.ArgumentError: Class object expected, got 'Table('unit',
 MetaData(bind=None), Column('id', Integer(), table=<unit>,
 primary_key=True, nullable=False), Column('name', String(length=20),
 table=<unit>), Column('mieter_id', Integer(), ForeignKey('mieter.id'),
 table=<unit>, nullable=False), schema=None)'.
 }}}

 This is totally misleading, because the actual error is in table HourData
 – whose relationship statement is supposed to refer to "Unit" instead of
 "unit". An error like this is impossible to figure out unless you happen
 to find the explanation in stackoverflow.

 [http://stackoverflow.com/questions/8170333/sqlalchemy-expects-an-object-
 but-finds-a-table#answer-9558936]

 Thus, please fix this error message. It should at least tell me which
 relationship statement in which table is actually broken.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2888>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2882: literal binds for text()

[sqlalchemy <mi...@zz...>]

#2882: literal binds for text()
-----------------------------------+----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.0
     Component:  sql               |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 rd5384d601107b76bca1634c55fb72372b2d68d42

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2882#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2883: foreign key to a non-table doesn't raise (correctly, consistently)

[sqlalchemy <mi...@zz...>]

#2883: foreign key to a non-table doesn't raise (correctly, consistently)
-----------------------------------+----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.0
     Component:  schema            |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 r84af7e6c22100ef26c5a27185b1d270f5

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2883#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2887: subqueryload query invokes ahead of parent loader init, can cause conflicts

[sqlalchemy <mi...@zz...>]

#2887: subqueryload query invokes ahead of parent loader init, can cause conflicts
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------

Comment (by zzzeek):

 whew, no problem, that was just because we were testing _dict being empty,
 which it might be anyway, here's an even more crude "with a flag" thing,
 obviously need to clean this up:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/strategies.py
 b/lib/sqlalchemy/orm/strategies.py
 index 8226a0e..2f20040 100644
 --- a/lib/sqlalchemy/orm/strategies.py
 +++ b/lib/sqlalchemy/orm/strategies.py
 @@ -937,12 +937,22 @@ class SubqueryLoader(AbstractRelationshipLoader):
          # call upon create_row_processor again
          collections = path.get(context.attributes, "collections")
          if collections is None:
 -            collections = dict(
 -                    (k, [vv[0] for vv in v])
 -                    for k, v in itertools.groupby(
 -                        subq,
 -                        lambda x: x[1:]
 -                    ))
 +            flag = [0]
 +            _data = dict()
 +            #subq = list(subq)
 +            class X(object):
 +                def get(self, key, default):
 +                    if not flag[0]:
 +                        flag[0] = 1
 +                        _data.update(
 +                            (k, [vv[0] for vv in v])
 +                            for k, v in itertools.groupby(
 +                                subq,
 +                                lambda x: x[1:]
 +                            )
 +                        )
 +                    return _data.get(key, default)
 +            collections = X()
              path.set(context.attributes, 'collections', collections)

          if adapter:
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2887#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2887: subqueryload query invokes ahead of parent loader init, can cause conflicts

[sqlalchemy <mi...@zz...>]

#2887: subqueryload query invokes ahead of parent loader init, can cause conflicts
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------

Comment (by zzzeek):

 we can address that issue by adding a "make sure we run the subq loader"
 function like this:

 {{{
 #!diff
 @@ -962,7 +969,13 @@ class SubqueryLoader(AbstractRelationshipLoader):
              state.get_impl(self.key).\
                      set_committed_value(state, dict_, collection)

 -        return load_collection_from_subq, None, None
 +        def _make_sure_its_loaded(state, dict_, row):
 +                collections.get(
 +                tuple([row[col] for col in local_cols]),
 +                ()
 +            )
 +
 +        return load_collection_from_subq, None, None,
 _make_sure_its_loaded

      def _create_scalar_loader(self, collections, local_cols):
          def load_scalar_from_subq(state, dict_, row):
 @@ -980,7 +993,13 @@ class SubqueryLoader(AbstractRelationshipLoader):
              state.get_impl(self.key).\
                      set_committed_value(state, dict_, scalar)

 -        return load_scalar_from_subq, None, None
 +        def _make_sure_its_loaded(state, dict_, row):
 +            collections.get(
 +                tuple([row[col] for col in local_cols]),
 +                ()
 +            )
 +
 +        return load_scalar_from_subq, None, None, _make_sure_its_loaded


 }}}

 additional issues remain with some self-referential loading

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2887#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2887: subqueryload query invokes ahead of parent loader init, can cause conflicts

[sqlalchemy <mi...@zz...>]

#2887: subqueryload query invokes ahead of parent loader init, can cause conflicts
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------

Comment (by zzzeek):

 at least some of the tests that fail are because they rely upon
 subqueryload to emit its query and load further related objects, even when
 the lead object otherwise is not subject to any row processing at all:

 {{{
         u1 =
 sess.query(User).filter_by(id=7).options(subqueryload("orders")).one()

         # id=7 is already loaded, no row processing for this User will
 take effect.
         # but we still want orders.items to get populated on the existing
 orders.
 sess.query(User).filter_by(id=7).options(subqueryload_all("orders.items")).first()
         assert 'items' in u1.orders[0].__dict__
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2887#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2887: subqueryload query invokes ahead of parent loader init, can cause conflicts

[sqlalchemy <mi...@zz...>]

#2887: subqueryload query invokes ahead of parent loader init, can cause conflicts
--------------------+------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.8.xx
Component:  orm     |        Severity:  major - 1-3 hours
 Keywords:          |  Progress State:  in progress
--------------------+------------------------------------
 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class Parent(Base):
     __tablename__ = 'parent'

     id = Column(Integer, primary_key=True)
     name = Column(String(20))

     children = relationship('Child1',
                         back_populates='parent',
                         lazy='noload'
                     )

 class Child1(Base):
     __tablename__ = 'child1'

     id = Column(Integer, primary_key=True)
     name = Column(String(20))
     parent_id = Column(Integer, ForeignKey('parent.id'))

     parent = relationship('Parent', back_populates='children',
 lazy='joined')

 engine = create_engine('sqlite:///:memory:', echo="debug")
 Base.metadata.create_all(engine)
 Session = sessionmaker(bind=engine)

 s = Session()
 s.add(Parent(name='parent', children=[Child1(name='c1')]))
 s.commit()

 parent = s.query(Parent).options([subqueryload('children')]).first()
 print parent.children

 }}}

 in the above case, the load is first Parent->subquery(children).  When the
 row hits, loading looks at "children" in order to invoke its loader - when
 it's a subqueryloader, it produces the query and invokes it, which is
 Child1->joinedload->Parent->noload(children).  The query joinloads onto
 Parent, populates Parent.children, Parent now goes into the identity map
 and is an "Existing" load - it's basically done being loaded.  Then we go
 back out to the original load of Parent which sees that Parent is already
 in the identity map, and is an "existingload" - so the subqueryloader for
 Parent.children, even though it created a row processor, never gets to use
 it.

 at some point we added caching to the subqueryload row_processor, so that
 inheriting mappers don't keep re-invoking the subquery - this is #2480,
 but looking at that, this is not actually the issue - the subq loader is
 still being invoked ahead of time before that.

 the solution would appear, crudely that the row processor needs to not
 invoke the subq query at all until it's time to populate:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/strategies.py
 b/lib/sqlalchemy/orm/strategies.py
 index 8226a0e..f053b0c 100644
 --- a/lib/sqlalchemy/orm/strategies.py
 +++ b/lib/sqlalchemy/orm/strategies.py
 @@ -937,12 +937,19 @@ class SubqueryLoader(AbstractRelationshipLoader):
          # call upon create_row_processor again
          collections = path.get(context.attributes, "collections")
          if collections is None:
 -            collections = dict(
 -                    (k, [vv[0] for vv in v])
 -                    for k, v in itertools.groupby(
 -                        subq,
 -                        lambda x: x[1:]
 -                    ))
 +            _data = dict()
 +            class X(object):
 +                def get(self, key, default):
 +                    if not _data:
 +                        _data.update(
 +                            (k, [vv[0] for vv in v])
 +                            for k, v in itertools.groupby(
 +                                subq,
 +                                lambda x: x[1:]
 +                            )
 +                        )
 +                    return _data.get(key, default)
 +            collections = X()
              path.set(context.attributes, 'collections', collections)

          if adapter:
 }}}

 however, this breaks a bunch of tests.   adding `subq = list(subq)` to the
 above then fixes those tests, so this proves that current behavior is
 somehow relying upon the subq query being invoked early, at the moment I
 can't imagine how that is needed.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2887>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2886: Add an officially supported query compilation function

[sqlalchemy <mi...@zz...>]

#2886: Add an officially supported query compilation function
------------------------------+----------------------------------
      Reporter:  agronholm    |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  medium       |  Milestone:  0.9.xx
     Component:  orm          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+----------------------------------
Changes (by zzzeek):

 * milestone:   => 0.9.xx
 * component:  utils => orm
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => in queue


Comment:

 well there's several aspects to this, one is whether `str()` takes into
 account the current engine so that the statement renders as it would on
 the backend, the other is the thing with the bounds which is sort of
 something else.   as far as binds as strings, that will have a lot of
 unsupported edge cases as we don't implement stringifiers for all types -
 and in fact it's only as of 0.9 that we even have the ability to support
 per-type stringifiers.

 the recipe for all of those things together is as follows:

 {{{
 #!python
 def query_as_str_w_binds(query):
    context = query._compile_context()
    context.statement.use_labels = True
    conn = query.session.connection(mapper=query._mapper_zero_or_none(),
 clause=context.statement)
    return unicode(context.statement.compile(conn,
 compiler_kwargs={"literal_binds": True}))
 }}}

 literal binds definitely needs to be an option.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2886#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2886: Add an officially supported query compilation function

[sqlalchemy <mi...@zz...>]

#2886: Add an officially supported query compilation function
----------------------------------+------------------------------------
      Reporter:  agronholm        |      Owner:  zzzeek
          Type:  enhancement      |     Status:  new
      Priority:  medium           |  Milestone:
     Component:  utils            |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by agronholm):

 I've fixed the acute issue with sqlacodegen, but the general need for this
 remains.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2886#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python