support preliminary rendering of SQL expressions, at least func.XYZ().

[sqlalchemy-bot]

Migrated issue, originally created by Mike Clarke (@mikeclarke)

Using the declarative base with alembic HEAD and SQLAlchemy 0.9.4, I'm having trouble autogenerating a unique functional index.

from sqlalchemy import Column, Index, Sequence, func
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.types import Integer, String

from st.orm.models.base import Base
from st.orm.models.mixins import RefBankCustomerMixin

class User(Base, RefBankCustomerMixin):
    __tablename__ = 'st_user'

    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    username = Column('username', String(20), unique=True, index=True)
    password = Column('password', String(60))

    @declared_attr
    def __table_args__(cls):
        return (
            Index("ix_user_username_lower", func.lower(cls.username), unique=True),
        )

A pair of index create statements are generated:

def upgrade():
    op.create_index(op.f('ix_st_user_username'), 'st_user', ['username'], unique=True)
    op.create_index('ix_user_username_lower', 'st_user', ['lower'], unique=True)

Ideally, this index definition would create a DDL statement like this:

op.execute('CREATE UNIQUE INDEX ix_user_username_lower ON "ST_USER" (LOWER(username))')

From IRC, it sounds like this behavior is unexpected. Happy to provide additional detail if this is insufficient. Thanks!

Gist: https://gist.github.com/anonymous/10081234

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the first create_index is due to index=True on the username column, so no bug there. The second index, Alembic doesn't have the capability to render all SQL in reverse like that and probably never will, as it would mean the entire SQLAlchemy expression language would know how to render itself in python. hence these are called only "candidate" migrations along with prominent comments stating that this code may need to be adjusted.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: bug
• added labels: feature

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - rendering

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Alembic autogenerates incorrect functional index" to "support preliminary rendering of SQL expressions a"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Duplicate of #197.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

consolidating this with #64.