diff options
| -rw-r--r-- | doc/build/changelog/changelog_08.rst | 8 | ||||
| -rw-r--r-- | doc/build/core/schema.rst | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 62 | ||||
| -rw-r--r-- | lib/sqlalchemy/schema.py | 15 | ||||
| -rw-r--r-- | test/dialect/test_mssql.py | 2 |
5 files changed, 72 insertions, 17 deletions
diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst index 7d6035a6d..c9b66be7e 100644 --- a/doc/build/changelog/changelog_08.rst +++ b/doc/build/changelog/changelog_08.rst @@ -7,6 +7,14 @@ :version: 0.8.0 .. change:: + :tags: mssql, feature + :pullreq: 35 + + Added ``mssql_include`` and ``mssql_clustered`` options to + :class:`.Index`, renders the ``INCLUDE`` and ``CLUSTERED`` keywords, + respectively. Courtesy Derek Harland. + + .. change:: :tags: sql, feature :tickets: 695 diff --git a/doc/build/core/schema.rst b/doc/build/core/schema.rst index 7771e3e4d..b2caf870f 100644 --- a/doc/build/core/schema.rst +++ b/doc/build/core/schema.rst @@ -1170,6 +1170,8 @@ The :class:`~sqlalchemy.schema.Index` object also supports its own ``create()`` {sql}i.create(engine) CREATE INDEX someindex ON mytable (col5){stop} +.. _schema_indexes_functional: + Functional Indexes ~~~~~~~~~~~~~~~~~~~ diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 084d00bef..70ffcb8cd 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -101,6 +101,50 @@ The DATE and TIME types are not available for MSSQL 2005 and previous - if a server version below 2008 is detected, DDL for these types will be issued as DATETIME. +.. _mssql_indexes: + +MSSQL-Specific Index Options +----------------------------- + +The MSSQL dialect supports special options for :class:`.Index`. + +CLUSTERED +^^^^^^^^^^ + +The ``mssql_clustered`` option adds the CLUSTERED keyword to the index:: + + Index("my_index", table.c.x, mssql_clustered=True) + +would render the index as ``CREATE CLUSTERED INDEX my_index ON table (x)`` + +.. versionadded:: 0.8 + +INCLUDE +^^^^^^^ + +The ``mssql_include`` option renders INCLUDE(colname) for the given string names:: + + Index("my_index", table.c.x, mssql_include=['y']) + +would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)`` + +.. versionadded:: 0.8 + +Index ordering +^^^^^^^^^^^^^^ + +Index ordering is available via functional expressions, such as:: + + Index("my_index", table.c.x.desc()) + +would render the index as ``CREATE INDEX my_index ON table (x DESC)`` + +.. versionadded:: 0.8 + +.. seealso:: + + :ref:`schema_indexes_functional` + Compatibility Levels -------------------- MSSQL supports the notion of setting compatibility levels at the @@ -934,6 +978,7 @@ class MSDDLCompiler(compiler.DDLCompiler): def visit_create_index(self, create, include_schema=False): index = create.element + self._verify_index_table(index) preparer = self.preparer text = "CREATE " if index.unique: @@ -943,24 +988,21 @@ class MSDDLCompiler(compiler.DDLCompiler): 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)])) + ', '.join( + self.sql_compiler.process(expr, + include_table=False) for + expr in index.expressions) + ) # handle other included columns if index.kwargs.get("mssql_include"): - inclusions = [index.table.c[col] if isinstance(col, basestring) else col + inclusions = [index.table.c[col] + if isinstance(col, basestring) else col for col in index.kwargs["mssql_include"]] text += " INCLUDE (%s)" \ diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index 5cb592857..3e8f4ea82 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -2340,15 +2340,18 @@ class Index(ColumnCollectionMixin, SchemaItem): column index, adding ``index=True`` to the ``Column`` definition is a shorthand equivalent for an unnamed, single column :class:`.Index`. - See also: + .. seealso:: + + :ref:`schema_indexes` - General information on :class:`.Index`. - :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. + :ref:`mssql_indexes` - MSSQL-specific options available for the + :class:`.Index` construct. """ diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py index d2c6fb488..210f8c748 100644 --- a/test/dialect/test_mssql.py +++ b/test/dialect/test_mssql.py @@ -519,7 +519,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): 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']) + idx = Index("foo", tbl.c.x.desc(), "y") self.assert_compile(schema.CreateIndex(idx), "CREATE INDEX foo ON test (x DESC, y)" ) |