diff options
Diffstat (limited to 'lib')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 62 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 4 |
3 files changed, 60 insertions, 8 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 6c4031b01..fdee250f1 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -271,6 +271,47 @@ produces a statement equivalent to:: SELECT CAST('some text' AS TSVECTOR) AS anon_1 +Full Text Searches in Postgresql are influenced by a combination of: the +PostgresSQL setting of ``default_text_search_config``, the ``regconfig`` used to +build the GIN/GiST indexes, and the ``regconfig`` optionally passed in during a +query. + +When performing a Full Text Search against a column that has a GIN or +GiST index that is already pre-computed (which is common on full text searches) +one may need to explicitly pass in a particular PostgresSQL ``regconfig`` value +to ensure the query-planner utilizes the index and does not re-compute the +column on demand. + +In order to provide for this explicit query planning, or to use different search +strategies, the ``match`` method accepts a ``postgresql_regconfig`` keyword +argument. + + select([mytable.c.id]).where( + mytable.c.title.match('somestring', postgresql_regconfig='english') + ) + +Emits the equivalent of:: + + SELECT mytable.id FROM mytable + WHERE mytable.title @@ to_tsquery('english', 'somestring') + +One can also specifically pass in a `'regconfig'` value to the ``to_tsvector()`` +command as the initial argument. + + select([mytable.c.id]).where( + func.to_tsvector('english', mytable.c.title )\ + .match('somestring', postgresql_regconfig='english') + ) + +produces a statement equivalent to:: + + SELECT mytable.id FROM mytable + WHERE to_tsvector('english', mytable.title) @@ + to_tsquery('english', 'somestring') + +It is recommended that you use the ``EXPLAIN ANALYZE...`` tool from PostgresSQL +to ensure that you are generating queries with SQLAlchemy that take full +advantage of any indexes you may have created for full text search. FROM ONLY ... ------------------------ @@ -1134,14 +1175,25 @@ class PGCompiler(compiler.SQLCompiler): def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( - self.process(binary.left, **kw), - self.process(binary.right, **kw) - ) + self.process(binary.left, **kw), + self.process(binary.right, **kw) + ) def visit_match_op_binary(self, binary, operator, **kw): + if "postgresql_regconfig" in binary.modifiers: + regconfig = self.render_literal_value(\ + binary.modifiers['postgresql_regconfig'], + sqltypes.STRINGTYPE) + if regconfig: + return "%s @@ to_tsquery(%s, %s)" % ( + self.process(binary.left, **kw), + regconfig, + self.process(binary.right, **kw) + ) return "%s @@ to_tsquery(%s)" % ( - self.process(binary.left, **kw), - self.process(binary.right, **kw)) + self.process(binary.left, **kw), + self.process(binary.right, **kw) + ) def visit_ilike_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 20f13f70f..4386eb52c 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -213,7 +213,7 @@ class _DefaultColumnComparator(operators.ColumnOperators): """See :meth:`.ColumnOperators.match`.""" return self._boolean_compare(expr, operators.match_op, self._check_literal(expr, operators.match_op, - other)) + other), **kw) def _distinct_impl(self, expr, op, **kw): """See :meth:`.ColumnOperators.distinct`.""" diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 9ad30e49e..55700a382 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -758,8 +758,8 @@ def notcontains_op(a, b, escape=None): return ~a.contains(b, escape=escape) -def match_op(a, b): - return a.match(b) +def match_op(a, b, **kw): + return a.match(b, **kw) def comma_op(a, b): |