[Sqlalchemy-commits] sqlalchemy: merge default
Brought to you by:
zzzeek
From: <co...@sq...> - 2013-01-20 17:44:24
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/634f41d5384c changeset: 9085:634f41d5384c user: Mike Bayer <mi...@zz...> date: Sun Jan 20 12:44:07 2013 -0500 description: merge default diffstat: doc/build/changelog/changelog_08.rst | 8 +++ doc/build/core/schema.rst | 2 + lib/sqlalchemy/dialects/mssql/base.py | 79 +++++++++++++++++++++++++++++++++++ lib/sqlalchemy/schema.py | 21 +++++--- test/dialect/test_mssql.py | 36 +++++++++++++++ 5 files changed, 137 insertions(+), 9 deletions(-) diffs (203 lines): diff -r f4f185ebb491 -r 634f41d5384c doc/build/changelog/changelog_08.rst --- a/doc/build/changelog/changelog_08.rst Fri Jan 18 19:58:44 2013 -0500 +++ b/doc/build/changelog/changelog_08.rst Sun Jan 20 12:44:07 2013 -0500 @@ -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 -r f4f185ebb491 -r 634f41d5384c doc/build/core/schema.rst --- a/doc/build/core/schema.rst Fri Jan 18 19:58:44 2013 -0500 +++ b/doc/build/core/schema.rst Sun Jan 20 12:44:07 2013 -0500 @@ -1170,6 +1170,8 @@ {sql}i.create(engine) CREATE INDEX someindex ON mytable (col5){stop} +.. _schema_indexes_functional: + Functional Indexes ~~~~~~~~~~~~~~~~~~~ diff -r f4f185ebb491 -r 634f41d5384c lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Fri Jan 18 19:58:44 2013 -0500 +++ b/lib/sqlalchemy/dialects/mssql/base.py Sun Jan 20 12:44:07 2013 -0500 @@ -101,6 +101,50 @@ 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 @@ -932,6 +976,41 @@ return colspec + 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: + text += "UNIQUE " + + # handle clustering option + if index.kwargs.get("mssql_clustered"): + text += "CLUSTERED " + + text += "INDEX %s ON %s (%s)" \ + % ( + self._prepared_index_name(index, + include_schema=include_schema), + preparer.format_table(index.table), + ', '.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 + 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 -r f4f185ebb491 -r 634f41d5384c lib/sqlalchemy/schema.py --- a/lib/sqlalchemy/schema.py Fri Jan 18 19:58:44 2013 -0500 +++ b/lib/sqlalchemy/schema.py Sun Jan 20 12:44:07 2013 -0500 @@ -2340,15 +2340,18 @@ column index, adding ``index=True`` to the ``Column`` definition is a shorthand equivalent for an unnamed, single column :class:`.Index`. - See also: - - :ref:`schema_indexes` - General information on :class:`.Index`. - - :ref:`postgresql_indexes` - PostgreSQL-specific options available for the - :class:`.Index` construct. - - :ref:`mysql_indexes` - MySQL-specific options available for the - :class:`.Index` construct. + .. seealso:: + + :ref:`schema_indexes` - General information on :class:`.Index`. + + :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:`mssql_indexes` - MSSQL-specific options available for the + :class:`.Index` construct. """ diff -r f4f185ebb491 -r 634f41d5384c test/dialect/test_mssql.py --- a/test/dialect/test_mssql.py Fri Jan 18 19:58:44 2013 -0500 +++ b/test/dialect/test_mssql.py Sun Jan 20 12:44:07 2013 -0500 @@ -506,6 +506,42 @@ "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.desc(), "y") + 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. |