:class:`.Index` now supports arbitrary SQL expressions and/or

functions, in addition to straight columns.   Common modifiers
include using ``somecolumn.desc()`` for a descending index and
``func.lower(somecolumn)`` for a case-insensitive index, depending on the
capabilities of the target backend.
[ticket:695]

7 files changed, 87 insertions, 55 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index d7c29654a..58ed65bc9 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -935,9 +935,8 @@ class MSDDLCompiler(compiler.DDLCompiler):
     def visit_drop_index(self, drop):
         return "\nDROP INDEX %s.%s" % (
             self.preparer.quote_identifier(drop.element.table.name),
-            self.preparer.quote(
-                        self._index_identifier(drop.element.name),
-                        drop.element.quote)
+            self._prepared_index_name(drop.element,
+                                        include_schema=True)
             )
 
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 6e09a359e..6bc9bd4a4 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1513,11 +1513,14 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
 
     def visit_create_index(self, create):
         index = create.element
+        self._verify_index_table(index)
         preparer = self.preparer
         table = preparer.format_table(index.table)
-        columns = [preparer.quote(c.name, c.quote) for c in index.columns]
+        columns = [self.sql_compiler.process(expr, include_table=False)
+                for expr in index.expressions]
+
         name = preparer.quote(
-                    self._index_identifier(index.name),
+                    self._prepared_index_name(index),
                     index.quote)
 
         text = "CREATE "
@@ -1550,10 +1553,9 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
     def visit_drop_index(self, drop):
         index = drop.element
 
-        return "\nDROP INDEX %s ON %s" % \
-                    (self.preparer.quote(
-                        self._index_identifier(index.name), index.quote
-                    ),
+        return "\nDROP INDEX %s ON %s" % (
+                    self._prepared_index_name(index,
+                                        include_schema=False),
                      self.preparer.format_table(index.table))
 
     def visit_drop_constraint(self, drop):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 81d2079c0..afbb23e15 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -962,13 +962,13 @@ class PGDDLCompiler(compiler.DDLCompiler):
     def visit_create_index(self, create):
         preparer = self.preparer
         index = create.element
+        self._verify_index_table(index)
         text = "CREATE "
         if index.unique:
             text += "UNIQUE "
-        ops = index.kwargs.get('postgresql_ops', {})
         text += "INDEX %s ON %s " % (
-                    preparer.quote(
-                        self._index_identifier(index.name), index.quote),
+                        self._prepared_index_name(index,
+                                include_schema=True),
                     preparer.format_table(index.table)
                 )
 
@@ -976,20 +976,20 @@ class PGDDLCompiler(compiler.DDLCompiler):
             using = index.kwargs['postgresql_using']
             text += "USING %s " % preparer.quote(using, index.quote)
 
+        ops = index.kwargs.get('postgresql_ops', {})
         text += "(%s)" \
                 % (
                     ', '.join([
-                        preparer.format_column(c) +
+                        self.sql_compiler.process(expr, include_table=False) +
+
+
                         (c.key in ops and (' ' + ops[c.key]) or '')
-                        for c in index.columns])
+
+
+                        for expr, c in zip(index.expressions, index.columns)])
                     )
 
-        if "postgres_where" in index.kwargs:
-            whereclause = index.kwargs['postgres_where']
-            util.warn_deprecated(
-                    "The 'postgres_where' argument has been renamed "
-                    "to 'postgresql_where'.")
-        elif 'postgresql_where' in index.kwargs:
+        if 'postgresql_where' in index.kwargs:
             whereclause = index.kwargs['postgresql_where']
         else:
             whereclause = None
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 241d0bcdf..7558acccc 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -522,18 +522,8 @@ class SQLiteDDLCompiler(compiler.DDLCompiler):
         return preparer.format_table(table, use_schema=False)
 
     def visit_create_index(self, create):
-        index = create.element
-        preparer = self.preparer
-        text = "CREATE "
-        if index.unique:
-            text += "UNIQUE "
-        text += "INDEX %s ON %s (%s)" \
-                    % (preparer.format_index(index,
-                       name=self._index_identifier(index.name)),
-                       preparer.format_table(index.table, use_schema=False),
-                       ', '.join(preparer.quote(c.name, c.quote)
-                                 for c in index.columns))
-        return text
+        return super(SQLiteDDLCompiler, self).\
+                    visit_create_index(create, include_table_schema=False)
 
 
 class SQLiteTypeCompiler(compiler.GenericTypeCompiler):
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index 33a0c445b..a9e5c5fda 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -412,8 +412,8 @@ class SybaseDDLCompiler(compiler.DDLCompiler):
         index = drop.element
         return "\nDROP INDEX %s.%s" % (
             self.preparer.quote_identifier(index.table.name),
-            self.preparer.quote(
-                    self._index_identifier(index.name), index.quote)
+            self._prepared_index_name(drop.element,
+                                        include_schema=False)
             )
 
 
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index b5e97802d..5cb592857 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -2344,22 +2344,30 @@ class Index(ColumnCollectionMixin, SchemaItem):
 
     :ref:`schema_indexes` - General information on :class:`.Index`.
 
-    :ref:`postgresql_indexes` - PostgreSQL-specific options available for the :class:`.Index` construct.
+    :ref:`postgresql_indexes` - PostgreSQL-specific options available for the
+    :class:`.Index` construct.
+
+    :ref:`mysql_indexes` - MySQL-specific options available for the
+    :class:`.Index` construct.
 
-    :ref:`mysql_indexes` - MySQL-specific options available for the :class:`.Index` construct.
     """
 
     __visit_name__ = 'index'
 
-    def __init__(self, name, *columns, **kw):
+    def __init__(self, name, *expressions, **kw):
         """Construct an index object.
 
         :param name:
           The name of the index
 
-        :param \*columns:
-          Columns to include in the index. All columns must belong to the same
-          table.
+        :param \*expressions:
+          Column expressions to include in the index.   The expressions
+          are normally instances of :class:`.Column`, but may also
+          be arbitrary SQL expressions which ultmately refer to a
+          :class:`.Column`.
+
+          .. versionadded:: 0.8 :class:`.Index` supports SQL expressions as
+             well as plain columns.
 
         :param unique:
             Defaults to False: create a unique index.
@@ -2369,9 +2377,25 @@ class Index(ColumnCollectionMixin, SchemaItem):
 
         """
         self.table = None
+
+        columns = []
+        for expr in expressions:
+            if not isinstance(expr, expression.ClauseElement):
+                columns.append(expr)
+            else:
+                cols = []
+                visitors.traverse(expr, {}, {'column': cols.append})
+                if cols:
+                    columns.append(cols[0])
+                else:
+                    columns.append(expr)
+
+        self.expressions = expressions
+
         # will call _set_parent() if table-bound column
         # objects are present
         ColumnCollectionMixin.__init__(self, *columns)
+
         self.name = name
         self.unique = kw.pop('unique', False)
         self.kwargs = kw
@@ -2397,6 +2421,12 @@ class Index(ColumnCollectionMixin, SchemaItem):
                 )
         table.indexes.add(self)
 
+        self.expressions = [
+            expr if isinstance(expr, expression.ClauseElement)
+            else colexpr
+            for expr, colexpr in zip(self.expressions, self.columns)
+        ]
+
     @property
     def bind(self):
         """Return the connectable associated with this Index."""
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 127de1dfa..152e68e34 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1933,20 +1933,17 @@ class DDLCompiler(engine.Compiled):
     def visit_drop_view(self, drop):
         return "\nDROP VIEW " + self.preparer.format_table(drop.element)
 
-    def _index_identifier(self, ident):
-        if isinstance(ident, sql._truncated_label):
-            max = self.dialect.max_index_name_length or \
-                        self.dialect.max_identifier_length
-            if len(ident) > max:
-                ident = ident[0:max - 8] + \
-                                "_" + util.md5_hex(ident)[-4:]
-        else:
-            self.dialect.validate_identifier(ident)
 
-        return ident
+    def _verify_index_table(self, index):
+        if index.table is None:
+            raise exc.CompileError("Index '%s' is not associated "
+                            "with any table." % index.name)
+
 
-    def visit_create_index(self, create, include_schema=False):
+    def visit_create_index(self, create, include_schema=False,
+                                include_table_schema=True):
         index = create.element
+        self._verify_index_table(index)
         preparer = self.preparer
         text = "CREATE "
         if index.unique:
@@ -1955,9 +1952,13 @@ class DDLCompiler(engine.Compiled):
                     % (
                         self._prepared_index_name(index,
                                 include_schema=include_schema),
-                       preparer.format_table(index.table),
-                       ', '.join(preparer.quote(c.name, c.quote)
-                                 for c in index.columns))
+                       preparer.format_table(index.table,
+                                    use_schema=include_table_schema),
+                       ', '.join(
+                            self.sql_compiler.process(expr,
+                                include_table=False) for
+                                expr in index.expressions)
+                        )
         return text
 
     def visit_drop_index(self, drop):
@@ -1973,8 +1974,18 @@ class DDLCompiler(engine.Compiled):
         else:
             schema_name = None
 
+        ident = index.name
+        if isinstance(ident, sql._truncated_label):
+            max_ = self.dialect.max_index_name_length or \
+                        self.dialect.max_identifier_length
+            if len(ident) > max_:
+                ident = ident[0:max_ - 8] + \
+                                "_" + util.md5_hex(ident)[-4:]
+        else:
+            self.dialect.validate_identifier(ident)
+
         index_name = self.preparer.quote(
-                            self._index_identifier(index.name),
+                                    ident,
                                     index.quote)
 
         if schema_name: