SQL Server BIT is native boolean

[Mike Bayer]

we should not be creating a constraint for Boolean on SQL Server and we should support native boolean rules. The BIT type only stores 1 or 0 and converts any other integer into a 1:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(Boolean(create_constraint=False))

#e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)
e = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=FreeTDS", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

conn = e.connect()
conn.execute("INSERT INTO a (data) VALUES (1)")
conn.execute("INSERT INTO a (data) VALUES (15)")

if e.dialect.driver == 'pymssql':
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": True})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 25})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 1})
else:
    conn.execute("INSERT INTO a (data) VALUES (?)", [True])
    conn.execute("INSERT INTO a (data) VALUES (?)", [25])
    conn.execute("INSERT INTO a (data) VALUES (?)", [1])


print e.execute(select([A.data])).fetchall()

[Mike Bayer]

Enable native boolean for SQL Server

SQL Server supports what SQLAlchemy calls "native boolean" with its BIT type, as this type only accepts 0 or 1 and the DBAPIs return its value as True/False. So the SQL Server dialects now enable "native boolean" support, in that a CHECK constraint is not generated for a :class:.Boolean datatype. The only difference vs. other native boolean is that there are no "true" / "false" constants so "1" and "0" are still rendered here.

Tests are implicit in the existing suites.

Change-Id: I75bbcd549884099fb1a177e68667bf880c40fa7c Fixes: #4061

→ <<cset 888f112b78e1>>