declared_attr __table_args__ are not taken into account

[sqlalchemy-bot]

Migrated issue, originally created by Adrian (@thiefmaster)

I have a model containing this method:

@declared_attr
def __table_args__(cls):
    return (db.Index('ix_reservations_start_date_date', cast(cls.start_date, Date)),
            db.Index('ix_reservations_end_date_date', cast(cls.end_date, Date)),
            db.Index('ix_reservations_start_date_time', cast(cls.start_date, Time)),
            db.Index('ix_reservations_end_date_time', cast(cls.end_date, Time)))

(using declared_attr is necessary since I have to reference the column and using a string doesn't work there)

Unfortunately alembic does not detect those index definitions. Also, apparently the cast is lost. Is there any clean way to create such indexes in a migration?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

for the first issue, __table_args__, can't reproduce, run this test:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    start_date = Column(DateTime)

    @declared_attr
    def __table_args__(cls):
        return (
            Index(
                'ix_reservations_start_date_date',
                cast(cls.start_date, Date)),
        )


e = create_engine("postgresql://scott:tiger@localhost/test")
conn = e.connect()


from alembic.migration import MigrationContext
from alembic import autogenerate
context = MigrationContext.configure(
    connection=conn,
    opts={
        'compare_type': True,
        'compare_server_default': True,
        'target_metadata': Base.metadata,
        'upgrade_token': "upgrades",
        'downgrade_token': "downgrades",
        'alembic_module_prefix': 'op.',
        'sqlalchemy_module_prefix': 'sa.',
    }
)

autogen_context = {
    'imports': set(),
    'connection': conn,
    'dialect': conn.dialect,
    'context': context
    }
diffs = []
autogenerate._produce_net_changes(conn, Base.metadata, diffs,
                                  autogen_context,
                            )
print diffs

output:

#!

[('add_table', Table('foo', MetaData(bind=None), Column('id', Integer(), table=<foo>, primary_key=True, nullable=False), Column('start_date', DateTime(), table=<foo>), schema=None)), 
('add_index', Index('ix_reservations_start_date_date', <sqlalchemy.sql.elements.Cast object at 0x101f5a190>))]

you can see your index is right there.

For the second part, dupe of #197.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: invalid

[sqlalchemy-bot]

Adrian (@thiefmaster) wrote:

Hrm, in my case the table and also those indexes already existed - there shouldn't have been any changes.

[sqlalchemy-bot]

Adrian (@thiefmaster) wrote:

Using your script I can reproduce the issue if I create the table and index manually and then run it:

/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:53: SAWarning: Skipped unsupported reflection of expression-based index ix_foo_start_date_date
  ret = fn(self, con, *args, **kw)
[('add_index',
  Index('ix_foo_start_date_date', Column('start_date', DateTime(), table=<foo>)))]

Anyway, I guess that means it's SQLAlchemy's fault...

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

yeah, functional indexes on PG don't reflect right now. in this case it would cause the opposite effect, you get the index diff when it shouldn't be there.