support functional indexes with op.create_index()

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

op.create_index() only accepts string column names. Particularly with autogenerate and issue #197, we need to be able to put expressions in here without needing a Column object lying around. So let's allow text():

op.create_index("foo", "table", text("lower('x')"), text("lower('y')"))

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Created https://bitbucket.org/zzzeek/sqlalchemy/issue/3174/support-text-sent-to-indexes on the SQLAlchemy side to fix this but for now we will work around it here.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Added support for functional indexes when using the
  :meth:.Operations.create_index directive. Within the list of columns,
  the SQLAlchemy text() construct can be sent, embedding a literal
  SQL expression; the :meth:.Operations.create_index will perform some hackery
  behind the scenes to get the :class:.Index construct to cooperate.
  This works around some current limitations in :class:.Index
  which should be resolved on the SQLAlchemy side at some point.
  fixes #222

→ 707f2d5

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Added support for functional indexes when using the
  :meth:.Operations.create_index directive. Within the list of columns,
  the SQLAlchemy text() construct can be sent, embedding a literal
  SQL expression; the :meth:.Operations.create_index will perform some hackery
  behind the scenes to get the :class:.Index construct to cooperate.
  This works around some current limitations in :class:.Index
  which should be resolved on the SQLAlchemy side at some point.
  fixes #222

→ c5be980

[sqlalchemy-bot]

Antti Haapala wrote:

This was the first Google result for Alembic functional indexes, just noting that the columns must be in a list (sequence):

op.create_index("foo", "table", [text("lower('x')"), text("lower('y')")])

otherwise one gets TypeError: 'TextClause' object is not iterable. (Took me some minutes to realize why, and well, in the
process I did upgrade my Alembic and SQLAlchemy...)