- The Boolean type, when used on a backend that doesn't

have native boolean support, will generate a CHECK
constraint "col IN (0, 1)" along with the int/smallint-
based column type.  This can be switched off if
desired with create_constraint=False.
Note that MySQL has no native boolean *or* CHECK constraint
support so this feature isn't available on that platform.
[ticket:1589]

8 files changed, 141 insertions, 181 deletions

diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index 21fec6b51..86d6876a6 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -123,29 +123,7 @@ RESERVED_WORDS = set([
     ])
 
 
-class _FBBoolean(sqltypes.Boolean):
-    def result_processor(self, dialect, coltype):
-        def process(value):
-            if value is None:
-                return None
-            return value and True or False
-        return process
-
-    def bind_processor(self, dialect):
-        def process(value):
-            if value is True:
-                return 1
-            elif value is False:
-                return 0
-            elif value is None:
-                return None
-            else:
-                return value and True or False
-        return process
-
-
 colspecs = {
-    sqltypes.Boolean: _FBBoolean,
 }
 
 ischema_names = {
@@ -321,10 +299,13 @@ class FBDialect(default.DefaultDialect):
     sequences_optional = False
     supports_default_values = True
     postfetch_lastrowid = False
-
+    
+    supports_native_boolean = False
+    
     requires_name_normalize = True
     supports_empty_insert = False
 
+    
     statement_compiler = FBCompiler
     ddl_compiler = FBDDLCompiler
     preparer = FBIdentifierPreparer
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index bd275d758..1060446b2 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -604,25 +604,6 @@ class IMAGE(sqltypes.Binary):
 class BIT(sqltypes.TypeEngine):
     __visit_name__ = 'BIT'
     
-class _MSBoolean(sqltypes.Boolean):
-    def result_processor(self, dialect, coltype):
-        def process(value):
-            if value is None:
-                return None
-            return value and True or False
-        return process
-
-    def bind_processor(self, dialect):
-        def process(value):
-            if value is True:
-                return 1
-            elif value is False:
-                return 0
-            elif value is None:
-                return None
-            else:
-                return value and True or False
-        return process
 
 class MONEY(sqltypes.TypeEngine):
     __visit_name__ = 'MONEY'
@@ -640,7 +621,6 @@ class SQL_VARIANT(sqltypes.TypeEngine):
 MSNumeric = _MSNumeric
 MSDateTime = _MSDateTime
 MSDate = _MSDate
-MSBoolean = _MSBoolean
 MSReal = REAL
 MSTinyInteger = TINYINT
 MSTime = TIME
@@ -667,7 +647,6 @@ colspecs = {
     sqltypes.DateTime : _MSDateTime,
     sqltypes.Date : _MSDate,
     sqltypes.Time : TIME,
-    sqltypes.Boolean : _MSBoolean,
 }
 
 ischema_names = {
@@ -1137,6 +1116,7 @@ class MSDialect(default.DefaultDialect):
     colspecs = colspecs
     ischema_names = ischema_names
     
+    supports_native_boolean = False
     supports_unicode_binds = True
     postfetch_lastrowid = True
     
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 3a2773892..01f8b13a7 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -167,6 +167,13 @@ available.
 
       update(..., mysql_limit=10)
 
+Boolean Types
+-------------
+
+MySQL's BOOL type is a synonym for SMALLINT, so is actually a numeric value,
+and additionally MySQL doesn't support CHECK constraints. Therefore SQLA's
+Boolean type cannot fully constrain values to just "True" and "False" the way it does for most other backends.
+
 Troubleshooting
 ---------------
 
@@ -1080,32 +1087,7 @@ class SET(_StringType):
                 return value
         return process
 
-class _MSBoolean(sqltypes.Boolean):
-    """MySQL BOOLEAN type."""
-
-    __visit_name__ = 'BOOLEAN'
-
-    def result_processor(self, dialect, coltype):
-        def process(value):
-            if value is None:
-                return None
-            return value and True or False
-        return process
-
-    def bind_processor(self, dialect):
-        def process(value):
-            if value is True:
-                return 1
-            elif value is False:
-                return 0
-            elif value is None:
-                return None
-            else:
-                return value and True or False
-        return process
-
 # old names
-MSBoolean = _MSBoolean
 MSTime = _MSTime
 MSSet = SET
 MSEnum = ENUM
@@ -1141,7 +1123,6 @@ colspecs = {
     sqltypes.Numeric: NUMERIC,
     sqltypes.Float: FLOAT,
     sqltypes.Binary: _BinaryType,
-    sqltypes.Boolean: _MSBoolean,
     sqltypes.Time: _MSTime,
     sqltypes.Enum: ENUM,
 }
@@ -1656,7 +1637,6 @@ class MySQLDialect(default.DefaultDialect):
     max_identifier_length = 255
     
     supports_native_enum = True
-    supports_native_boolean = True
     
     supports_sane_rowcount = True
     supports_sane_multi_rowcount = False
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index d13e37d60..ddcc9f460 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -158,25 +158,6 @@ class LONG(sqltypes.Text):
 class _OracleBoolean(sqltypes.Boolean):
     def get_dbapi_type(self, dbapi):
         return dbapi.NUMBER
-    
-    def result_processor(self, dialect, coltype):
-        def process(value):
-            if value is None:
-                return None
-            return value and True or False
-        return process
-
-    def bind_processor(self, dialect):
-        def process(value):
-            if value is True:
-                return 1
-            elif value is False:
-                return 0
-            elif value is None:
-                return None
-            else:
-                return value and True or False
-        return process
 
 colspecs = {
     sqltypes.Boolean : _OracleBoolean,
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index d0a87d282..7a9e2e710 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -525,6 +525,7 @@ class PGDialect(default.DefaultDialect):
     supports_sane_rowcount = True
     
     supports_native_enum = True
+    supports_native_boolean = True
     
     supports_sequences = True
     sequences_optional = True
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 2b929331a..d83eb4b86 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -146,23 +146,7 @@ class TIME(_DateTimeMixin, sqltypes.Time):
     def result_processor(self, dialect, coltype):
         return self._result_processor(datetime.time)
 
-class _SLBoolean(sqltypes.Boolean):
-    def bind_processor(self, dialect):
-        def process(value):
-            if value is None:
-                return None
-            return value and 1 or 0
-        return process
-
-    def result_processor(self, dialect, coltype):
-        def process(value):
-            if value is None:
-                return None
-            return value == 1
-        return process
-
 colspecs = {
-    sqltypes.Boolean: _SLBoolean,
     sqltypes.Date: DATE,
     sqltypes.DateTime: DATETIME,
     sqltypes.Float: _SLFloat,
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index 70087ee73..d3a15dc8b 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -519,89 +519,97 @@ class Column(SchemaItem, expression.ColumnClause):
             to generate primary key identifiers (i.e. Firebird, Postgresql, 
             Oracle).
 
-        :param default: A scalar, Python callable, or :class:`~sqlalchemy.sql.expression.ClauseElement`
-          representing the *default value* for this column, which will be
-          invoked upon insert if this column is otherwise not specified
-          in the VALUES clause of the insert.  This is a shortcut
-          to using :class:`ColumnDefault` as a positional argument.
+        :param default: A scalar, Python callable, or
+            :class:`~sqlalchemy.sql.expression.ClauseElement` representing the
+            *default value* for this column, which will be invoked upon insert
+            if this column is otherwise not specified in the VALUES clause of
+            the insert. This is a shortcut to using :class:`ColumnDefault` as
+            a positional argument.
           
-          Contrast this argument to ``server_default`` which creates a 
-          default generator on the database side.
+            Contrast this argument to ``server_default`` which creates a 
+            default generator on the database side.
         
-        :param key: An optional string identifier which will identify this ``Column`` 
-            object on the :class:`Table`.  When a key is provided, this is the
-            only identifier referencing the ``Column`` within the application,
-            including ORM attribute mapping; the ``name`` field is used only
-            when rendering SQL.
+        :param key: An optional string identifier which will identify this
+            ``Column`` object on the :class:`Table`. When a key is provided,
+            this is the only identifier referencing the ``Column`` within the
+            application, including ORM attribute mapping; the ``name`` field
+            is used only when rendering SQL.
 
         :param index: When ``True``, indicates that the column is indexed.
-          This is a shortcut for using a :class:`Index` construct on the table.
-          To specify indexes with explicit names or indexes that contain multiple 
-          columns, use the :class:`Index` construct instead.
-
-        :param info: A dictionary which defaults to ``{}``.  A space to store application 
-          specific data. This must be a dictionary.
-
-        :param nullable: If set to the default of ``True``, indicates the column
-            will be rendered as allowing NULL, else it's rendered as NOT NULL.
-            This parameter is only used when issuing CREATE TABLE statements.
-
-        :param onupdate: A scalar, Python callable, or :class:`~sqlalchemy.sql.expression.ClauseElement`
-            representing a default value to be applied to the column within UPDATE
-            statements, which wil be invoked upon update if this column is not present
-            in the SET clause of the update.  This is a shortcut to using 
-            :class:`ColumnDefault` as a positional argument with ``for_update=True``.
+            This is a shortcut for using a :class:`Index` construct on the
+            table. To specify indexes with explicit names or indexes that
+            contain multiple columns, use the :class:`Index` construct
+            instead.
+
+        :param info: A dictionary which defaults to ``{}``. A space to store
+            application specific data. This must be a dictionary.
+
+        :param nullable: If set to the default of ``True``, indicates the 
+            column will be rendered as allowing NULL, else it's rendered as
+            NOT NULL. This parameter is only used when issuing CREATE TABLE
+            statements.
+
+        :param onupdate: A scalar, Python callable, or
+            :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
+            default value to be applied to the column within UPDATE
+            statements, which wil be invoked upon update if this column is not
+            present in the SET clause of the update. This is a shortcut to
+            using :class:`ColumnDefault` as a positional argument with
+            ``for_update=True``.
             
         :param primary_key: If ``True``, marks this column as a primary key
-            column.  Multiple columns can have this flag set to specify composite
-            primary keys.  As an alternative, the primary key of a :class:`Table` can
-            be specified via an explicit :class:`PrimaryKeyConstraint` object.
+            column. Multiple columns can have this flag set to specify
+            composite primary keys. As an alternative, the primary key of a
+            :class:`Table` can be specified via an explicit
+            :class:`PrimaryKeyConstraint` object.
 
-        :param server_default: A :class:`FetchedValue` instance, str, Unicode or
-          :func:`~sqlalchemy.sql.expression.text` construct representing 
-          the DDL DEFAULT value for the column.
+        :param server_default: A :class:`FetchedValue` instance, str, Unicode
+            or :func:`~sqlalchemy.sql.expression.text` construct representing
+            the DDL DEFAULT value for the column.
 
-          String types will be emitted as-is, surrounded by single quotes::
+            String types will be emitted as-is, surrounded by single quotes::
 
-              Column('x', Text, server_default="val")
+                Column('x', Text, server_default="val")
 
-              x TEXT DEFAULT 'val'
+                x TEXT DEFAULT 'val'
 
-          A :func:`~sqlalchemy.sql.expression.text` expression will be 
-          rendered as-is, without quotes::
+            A :func:`~sqlalchemy.sql.expression.text` expression will be
+            rendered as-is, without quotes::
 
-              Column('y', DateTime, server_default=text('NOW()'))0
+                Column('y', DateTime, server_default=text('NOW()'))0
 
-              y DATETIME DEFAULT NOW()
+                y DATETIME DEFAULT NOW()
 
-          Strings and text() will be converted into a :class:`DefaultClause`
-          object upon initialization.
+            Strings and text() will be converted into a :class:`DefaultClause`
+            object upon initialization.
           
-          Use :class:`FetchedValue` to indicate that an already-existing column will generate
-          a default value on the database side which will be available to SQLAlchemy 
-          for post-fetch after inserts.  
-          This construct does not specify any DDL and the implementation is 
-          left to the database, such as via a trigger.
-
-        :param server_onupdate:   A :class:`FetchedValue` instance representing 
-            a database-side default generation function.  This indicates to 
-            SQLAlchemy that a newly generated value will be available after updates.
-            This construct does not specify any DDL and the implementation is 
-            left to the database, such as via a trigger.
-
-        :param quote: Force quoting of this column's name on or off, corresponding
-           to ``True`` or ``False``.  When left at its default of ``None``,
-           the column identifier will be quoted according to whether the name is
-           case sensitive (identifiers with at least one upper case character are 
-           treated as case sensitive), or if it's a reserved word.  This flag 
-           is only needed to force quoting of a reserved word which is not known
-           by the SQLAlchemy dialect.
-
-        :param unique: When ``True``, indicates that this column contains a unique
-            constraint, or if ``index`` is ``True`` as well, indicates that the
-            :class:`Index` should be created with the unique flag.  To specify multiple
-            columns in the constraint/index or to specify an explicit name,
-            use the :class:`UniqueConstraint` or :class:`Index` constructs explicitly.
+            Use :class:`FetchedValue` to indicate that an already-existing
+            column will generate a default value on the database side which
+            will be available to SQLAlchemy for post-fetch after inserts. This
+            construct does not specify any DDL and the implementation is left
+            to the database, such as via a trigger.
+
+        :param server_onupdate:   A :class:`FetchedValue` instance
+             representing a database-side default generation function. This
+             indicates to SQLAlchemy that a newly generated value will be
+             available after updates. This construct does not specify any DDL
+             and the implementation is left to the database, such as via a
+             trigger.
+
+        :param quote: Force quoting of this column's name on or off,
+             corresponding to ``True`` or ``False``. When left at its default
+             of ``None``, the column identifier will be quoted according to
+             whether the name is case sensitive (identifiers with at least one
+             upper case character are treated as case sensitive), or if it's a
+             reserved word. This flag is only needed to force quoting of a
+             reserved word which is not known by the SQLAlchemy dialect.
+
+        :param unique: When ``True``, indicates that this column contains a
+             unique constraint, or if ``index`` is ``True`` as well, indicates
+             that the :class:`Index` should be created with the unique flag.
+             To specify multiple columns in the constraint/index or to specify
+             an explicit name, use the :class:`UniqueConstraint` or
+             :class:`Index` constructs explicitly.
 
         """
 
@@ -640,8 +648,12 @@ class Column(SchemaItem, expression.ColumnClause):
         self.constraints = set()
         self.foreign_keys = util.OrderedSet()
         self._table_events = set()
-        
-        if isinstance(self.type, types.SchemaType):
+
+        # check if this Column is proxying another column
+        if '_proxies' in kwargs:
+            self.proxies = kwargs.pop('_proxies')
+        # otherwise, add DDL-related events
+        elif isinstance(self.type, types.SchemaType):
             self.type._set_parent(self)
             
         if self.default is not None:
@@ -649,6 +661,7 @@ class Column(SchemaItem, expression.ColumnClause):
                 args.append(self.default)
             else:
                 args.append(ColumnDefault(self.default))
+
         if self.server_default is not None:
             if isinstance(self.server_default, FetchedValue):
                 args.append(self.server_default)
@@ -812,9 +825,8 @@ class Column(SchemaItem, expression.ColumnClause):
             key = name or self.key, 
             primary_key = self.primary_key, 
             nullable = self.nullable, 
-            quote=self.quote, *fk)
+            quote=self.quote, _proxies=[self], *fk)
         c.table = selectable
-        c.proxies = [self]
         selectable.columns.add(c)
         if self.primary_key:
             selectable.primary_key.add(c)
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index d7dda85e2..66b90ce04 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -87,9 +87,11 @@ class AbstractType(Visitable):
         return False
 
     def get_dbapi_type(self, dbapi):
-        """Return the corresponding type object from the underlying DB-API, if any.
+        """Return the corresponding type object from the underlying DB-API, if
+        any.
+        
+         This can be useful for calling ``setinputsizes()``, for example.
 
-        This can be useful for calling ``setinputsizes()``, for example.
         """
         return None
 
@@ -98,6 +100,7 @@ class AbstractType(Visitable):
         translate it to a new operator based on the semantics of this type.
 
         By default, returns the operator unchanged.
+
         """
         return op
 
@@ -1101,7 +1104,7 @@ class PickleType(MutableType, TypeDecorator):
         return self.mutable
 
 
-class Boolean(TypeEngine):
+class Boolean(TypeEngine, SchemaType):
     """A bool datatype.
 
     Boolean typically uses BOOLEAN or SMALLINT on the DDL side, and on
@@ -1111,6 +1114,44 @@ class Boolean(TypeEngine):
 
     __visit_name__ = 'boolean'
 
+    def __init__(self, create_constraint=True, name=None):
+        """Construct a Boolean.
+        
+        :param create_constraint: defaults to True.  If the boolean 
+        is generated as an int/smallint, also create a CHECK constraint
+        on the table that ensures 1 or 0 as a value.
+        
+        :param name: if a CHECK constraint is generated, specify
+        the name of the constraint.
+        
+        """
+        self.create_constraint = create_constraint
+        self.name = name
+        
+    def _set_table(self, table, column):
+        if not self.create_constraint:
+            return
+            
+        def should_create_constraint(compiler):
+            return not compiler.dialect.supports_native_boolean
+
+        e = schema.CheckConstraint(
+                        column.in_([0, 1]),
+                        name=self.name,
+                        _create_rule=should_create_constraint
+                    )
+        table.append_constraint(e)
+    
+    def result_processor(self, dialect, coltype):
+        if dialect.supports_native_boolean:
+            return None
+        else:
+            def process(value):
+                if value is None:
+                    return None
+                return value and True or False
+            return process
+
 class Interval(TypeDecorator):
     """A type for ``datetime.timedelta()`` objects.