From 4924007317f6d3cb9655b258c01ff888fb3f4a28 Mon Sep 17 00:00:00 2001 From: Ants Aasma Date: Sat, 29 Sep 2007 06:21:34 +0000 Subject: [PATCH] - added partial index support for postgres - fixed create and drop methods on MockConnection --- CHANGES | 3 +++ lib/sqlalchemy/databases/postgres.py | 18 +++++++++++++++++- lib/sqlalchemy/engine/strategies.py | 4 ++-- lib/sqlalchemy/schema.py | 5 ++++- test/dialect/postgres.py | 14 ++++++++++++++ 5 files changed, 40 insertions(+), 4 deletions(-) diff --git a/CHANGES b/CHANGES index 7477f4a9e2..095e582b49 100644 --- a/CHANGES +++ b/CHANGES @@ -5,6 +5,9 @@ CHANGES 0.4.0beta7 ---------- +- Added partial index support for PostgreSQL. Use the postgres_where + keyword on the Index. + - The IdentifierPreprarer's _requires_quotes test is now regex based. Any out-of-tree dialects that provide custom sets of legal_characters or illegal_initial_characters will need to move to regexes or override diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 7518a016e3..76f17bb5bf 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -4,7 +4,7 @@ # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -import re, random, warnings +import re, random, warnings, string from sqlalchemy import sql, schema, exceptions, util from sqlalchemy.engine import base, default @@ -612,6 +612,22 @@ class PGSchemaGenerator(compiler.SchemaGenerator): if not sequence.optional and (not self.checkfirst or not self.dialect.has_sequence(self.connection, sequence.name)): self.append("CREATE SEQUENCE %s" % self.preparer.format_sequence(sequence)) self.execute() + + def visit_index(self, index): + preparer = self.preparer + self.append("CREATE ") + if index.unique: + self.append("UNIQUE ") + self.append("INDEX %s ON %s (%s)" \ + % (preparer.format_index(index), + preparer.format_table(index.table), + string.join([preparer.format_column(c) for c in index.columns], ', '))) + if index.postgres_where is not None: + compiler = self._compile(index.postgres_where, None) + # this might belong to the compiler class + inlined_clause = str(compiler) % dict((key,bind.value) for key,bind in compiler.binds.iteritems()) + self.append(" WHERE " + inlined_clause) + self.execute() class PGSchemaDropper(compiler.SchemaDropper): def visit_sequence(self, sequence): diff --git a/lib/sqlalchemy/engine/strategies.py b/lib/sqlalchemy/engine/strategies.py index 524c4b0d51..175846ff8a 100644 --- a/lib/sqlalchemy/engine/strategies.py +++ b/lib/sqlalchemy/engine/strategies.py @@ -197,11 +197,11 @@ class MockEngineStrategy(EngineStrategy): def create(self, entity, **kwargs): kwargs['checkfirst'] = False - entity.accept_visitor(self.dialect.schemagenerator(self, **kwargs)) + self.dialect.schemagenerator(self.dialect ,self, **kwargs).traverse(entity) def drop(self, entity, **kwargs): kwargs['checkfirst'] = False - entity.accept_visitor(self.dialect.schemadropper(self, **kwargs)) + self.dialect.schemadropper(self.dialect, self, **kwargs).traverse(entity) def execute(self, object, *multiparams, **params): raise NotImplementedError() diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index 713adc5854..fce181cbb8 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -947,13 +947,16 @@ class Index(SchemaItem): unique Defaults to False: create a unique index. - + + postgres_where + Defaults to None: create a partial index when using PostgreSQL """ self.name = name self.columns = [] self.table = None self.unique = kwargs.pop('unique', False) + self.postgres_where = kwargs.pop('postgres_where', None) self._init_items(*columns) def _init_items(self, *args): diff --git a/test/dialect/postgres.py b/test/dialect/postgres.py index bae1f666de..0535da6f86 100644 --- a/test/dialect/postgres.py +++ b/test/dialect/postgres.py @@ -3,6 +3,7 @@ import datetime from sqlalchemy import * from sqlalchemy import exceptions from sqlalchemy.databases import postgres +from sqlalchemy.engine.strategies import MockEngineStrategy from testlib import * class SequenceTest(SQLCompileTest): @@ -517,6 +518,19 @@ class MiscTest(AssertMixin): finally: testbase.db.execute("drop table speedy_users", None) + @testing.supported('postgres') + def test_create_partial_index(self): + tbl = Table('testtbl', MetaData(), Column('data',Integer)) + idx = Index('test_idx1', tbl.c.data, postgres_where=and_(tbl.c.data > 5, tbl.c.data < 10)) + + executed_sql = [] + mock_strategy = MockEngineStrategy() + mock_conn = mock_strategy.create('postgres://', executed_sql.append) + + idx.create(mock_conn) + + assert executed_sql == ['CREATE INDEX test_idx1 ON testtbl (data) WHERE testtbl.data > 5 AND testtbl.data < 10'] + class TimezoneTest(AssertMixin): """test timezone-aware datetimes. psycopg will return a datetime with a tzinfo attached to it, if postgres returns it. python then will not let you compare a datetime with a tzinfo to a datetime -- 2.47.3