[PATCH] MySQL driver uses type BOOLEAN, unsupported by MySQL 4.0 and below

sqlalchemy.databases.mysql.MSBoolean.get_col_spec returns "BOOLEAN", which was added to MySQL in version 4.1.0 and is not supported by earlier versions. I've attached a patch that makes it return "BOOL", which ''is'' supported.

From http://www.mysql.org/doc/refman/4.1/en/numeric-type-overview.html:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). The synonym BOOLEAN was added in MySQL 4.1.0. A value of zero is considered false. Non-zero values are considered true: ...

The attached SQLAlchemy test case, when run with SQLAlchemy 0.3.7, outputs the following:

2007-05-31 14:16:22,691 INFO sqlalchemy.engine.base.Engine.0x..ec 
CREATE TABLE temp (
    a BOOLEAN
)


2007-05-31 14:16:22,692 INFO sqlalchemy.engine.base.Engine.0x..ec None
2007-05-31 14:16:22,692 INFO sqlalchemy.engine.base.Engine.0x..ec ROLLBACK
Traceback (most recent call last):
  File "./sqlalchemy-mysql-40-bool.py", line 17, in ?
    temp_table.create()
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/schema.py", line 364, in create
    self.metadata.create_all(connectable=connectable, checkfirst=checkfirst, tables=[self](self))
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/schema.py", line 1150, in create_all
    connectable.create(self, checkfirst=checkfirst, tables=tables)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 691, in create
    self._run_visitor(self.dialect.schemagenerator, entity, connection=connection, **kwargs)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 721, in _run_visitor
    element.accept_visitor(visitorcallable(conn, **kwargs))
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/schema.py", line 1172, in accept_visitor
    visitor.visit_metadata(self)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/ansisql.py", line 806, in visit_metadata
    table.accept_visitor(self)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/sql.py", line 2541, in accept_visitor
    visitor.visit_table(self)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/ansisql.py", line 841, in visit_table
    self.execute()
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 1203, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 509, in execute
    return Connection.executors[c](c)(self, object, *multiparams, **params)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 524, in execute_text
    self._execute_raw(context)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 573, in _execute_raw
    self._execute(context)
  File "/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py", line 591, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) (1064, "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'BOOLEAN\n)' at line 2") '\nCREATE TABLE temp (\n\ta BOOLEAN\n)\n\n' ()

Comments (4)