: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]

5 files changed, 25 insertions, 34 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)
             )