Merged in dharland/sqlalchemy (pull request #35)

Add extra mssql dialect options to Index

2 files changed, 73 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 58ed65bc9..084d00bef 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -932,6 +932,43 @@ class MSDDLCompiler(compiler.DDLCompiler):
 
         return colspec
 
+    def visit_create_index(self, create, include_schema=False):
+        index = create.element
+        preparer = self.preparer
+        text = "CREATE "
+        if index.unique:
+            text += "UNIQUE "
+
+        # handle clustering option
+        if index.kwargs.get("mssql_clustered"):
+            text += "CLUSTERED "
+
+        # extend the custom ordering to the right length
+        ordering = index.kwargs.get("mssql_ordering", [])
+        if len(ordering) > len(index.columns):
+            raise ValueError("Column ordering length is incompatible with index columns")
+        elif len(ordering) < len(index.columns):
+            ordering.extend([""]*(len(index.columns) - len(ordering)))
+
+        text += "INDEX %s ON %s (%s)" \
+                    % (
+                        self._prepared_index_name(index,
+                                include_schema=include_schema),
+                        preparer.format_table(index.table),
+                        ', '.join([preparer.quote(c.name, c.quote) + (" " + o if o else "")
+                                   for c, o in zip(index.columns, ordering)]))
+
+        # handle other included columns
+        if index.kwargs.get("mssql_include"):
+            inclusions = [index.table.c[col] if isinstance(col, basestring) else col
+                          for col in index.kwargs["mssql_include"]]
+
+            text += " INCLUDE (%s)" \
+                % ', '.join([preparer.quote(c.name, c.quote)
+                             for c in inclusions])
+
+        return text
+
     def visit_drop_index(self, drop):
         return "\nDROP INDEX %s.%s" % (
             self.preparer.quote_identifier(drop.element.table.name),
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index 8c1c0873a..d2c6fb488 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -506,6 +506,42 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1))"
                             )
 
+    def test_index_clustering(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('id', Integer))
+        idx = Index("foo", tbl.c.id, mssql_clustered=True)
+        self.assert_compile(schema.CreateIndex(idx),
+                            "CREATE CLUSTERED INDEX foo ON test (id)"
+                            )
+
+    def test_index_ordering(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('x', Integer), Column('y', Integer), Column('z', Integer))
+        idx = Index("foo", tbl.c.x, "y", mssql_ordering=['DESC'])
+        self.assert_compile(schema.CreateIndex(idx),
+                            "CREATE INDEX foo ON test (x DESC, y)"
+                            )
+
+    def test_index_extra_include_1(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('x', Integer), Column('y', Integer), Column('z', Integer))
+        idx = Index("foo", tbl.c.x, mssql_include=['y'])
+        self.assert_compile(schema.CreateIndex(idx),
+                            "CREATE INDEX foo ON test (x) INCLUDE (y)"
+                            )
+
+    def test_index_extra_include_2(self):
+        metadata = MetaData()
+        tbl = Table('test', metadata,
+                    Column('x', Integer), Column('y', Integer), Column('z', Integer))
+        idx = Index("foo", tbl.c.x, mssql_include=[tbl.c.y])
+        self.assert_compile(schema.CreateIndex(idx),
+                            "CREATE INDEX foo ON test (x) INCLUDE (y)"
+                            )
+
 class SchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL):
     """SQL server cannot reference schema-qualified tables in a SELECT statement, they
     must be aliased.