provide a "no-bind-param" version of Text() and apply this to DDL

[Former user]

This code:

from sqlalchemy import *
from sqlalchemy.schema import *
from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect

cc = CheckConstraint(r"foo ~ E'a(?:b|c)d")
bar = Table('bar', MetaData(), Column('foo', Text, cc))

dia = PGDialect()
comp = dia.ddl_compiler(dia, CreateTable(bar))
print comp.visit_check_constraint(cc)

prints out:

CHECK (foo ~ E'a(?None|c)d)

Looks like the :b part of the regexp is taken to be a bind parameter, and replaced by the nonexistent value passed for that bind parameter.

Sadly PostgreSQL happily accepts this bogus check constraint at table creation time. It then fails at runtime when you insert into the table, with:

DataError: (DataError) invalid regular expression: quantifier operand invalid

Seems to me that bind parameters shouldn't be expected/supported at all in DDL; indeed the DDL constructor says “SQL bind parameters are not available in DDL statements.” http://docs.sqlalchemy.org/en/latest/core/schema.html#sqlalchemy.schema.DDL.init

[Mike Bayer]

OK well you can escape that with a \ for now, as is part of the contract of Text().

from sqlalchemy import *
from sqlalchemy.schema import *
from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect

cc = CheckConstraint(r"foo ~ E'a(?\:b|c)d")
bar = Table('bar', MetaData(), Column('foo', Text, cc))

dia = PGDialect()
comp = dia.ddl_compiler(dia, CreateTable(bar))
print comp.visit_check_constraint(cc)

[Mike Bayer]

it seems like CheckConstraint is the only place in the schema system that we have any implicit construction of text().

Here's a solution taht adds a new RawText construct:

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 0c25208..cd79db0 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -599,6 +599,9 @@ class SQLCompiler(Compiled):
              self.post_process_text(textclause.text))
         )

+    def visit_raw_text(self, rawtext, **kw):
+        return self.post_process_text(rawtext.text)
+
     def visit_text_as_from(self, taf, iswrapper=False,
                                 compound_index=0, force_result_map=False,
                                 asfrom=False,
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 045056b..11a55d9 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -883,6 +883,17 @@ class TypeClause(ClauseElement):
         self.type = type


+class RawText(ClauseElement):
+    """Represent literal SQL text with no bind or type processing.
+
+    """
+
+    __visit_name__ = 'raw_text'
+
+    def __init__(self, text):
+        self.text = text
+
+
 class TextClause(Executable, ClauseElement):
     """Represent a literal SQL text fragment.

@@ -2716,13 +2727,13 @@ def _clause_element_as_expr(element):
         return element


-def _literal_as_text(element):
+def _literal_as_text(element, textcls=TextClause):
     if isinstance(element, Visitable):
         return element
     elif hasattr(element, '__clause_element__'):
         return element.__clause_element__()
     elif isinstance(element, util.string_types):
-        return TextClause(util.text_type(element))
+        return textcls(util.text_type(element))
     elif isinstance(element, (util.NoneType, bool)):
         return _const_expr(element)
     else:
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 7bf543a..da217e4 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -37,7 +37,8 @@ from . import type_api
 from .base import _bind_or_error, ColumnCollection
 from .elements import ClauseElement, ColumnClause, _truncated_label, \
                         _as_truncated, TextClause, _literal_as_text,\
-                        ColumnElement, _find_columns, quoted_name
+                        ColumnElement, _find_columns, quoted_name, \
+                        RawText
 from .selectable import TableClause
 import collections
 import sqlalchemy
@@ -62,6 +63,10 @@ def _validate_dialect_kwargs(kwargs, name):
             raise TypeError("Additional arguments should be "
                     "named <dialectname>_<argument>, got '%s'" % k)

+def _literal_as_ddl(element):
+    return _literal_as_text(element, textcls=RawText)
+
+
 @inspection._self_inspects
 class SchemaItem(SchemaEventTarget, visitors.Visitable):
     """Base class for items that define a database schema."""
@@ -2276,7 +2281,7 @@ class CheckConstraint(Constraint):

         super(CheckConstraint, self).\
                         __init__(name, deferrable, initially, _create_rule)
-        self.sqltext = _literal_as_text(sqltext)
+        self.sqltext = _literal_as_ddl(sqltext)
         if table is not None:
             self._set_parent_with_dispatch(table)
         elif _autoattach:

this is for 0.9 along with #2882.

[Mike Bayer]

changed my mind on this - would rather not add another backwards incompatible surprise to 0.9, i think we have a lot.

instead, the code above does raise on 0.9 now because Text() does run the bind params through now.

Added a doc example to CheckConstraint and improved the exception raised in :

0fefc6e22641287100eb0648cf1264daeefeb020 0.8, but the exception doesnt raise for CheckConstraint

207aaf2f41cff5970b34999d3cfc845a3b0df29c 0.9, the above code raises

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)