From 2cb2b624f97a88df857777104cc0db6d733306ef Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 13 Jul 2006 01:12:53 +0000
Subject: [PATCH] fixed up boolean datatype for sqlite, mysql, ms-sql

---
 CHANGES                            |  1 +
 lib/sqlalchemy/databases/mssql.py  | 13 +++++++++++++
 lib/sqlalchemy/databases/mysql.py  | 17 +++++++++++++++--
 lib/sqlalchemy/databases/sqlite.py |  4 ++++
 test/sql/testtypes.py              | 26 ++++++++++++++++++++++++++
 5 files changed, 59 insertions(+), 2 deletions(-)

diff --git a/CHANGES b/CHANGES
index 9db4d3efe3..acf61f8702 100644
--- a/CHANGES
+++ b/CHANGES
@@ -8,6 +8,7 @@ activated when activemapper is imported
 - small fix to URL regexp to allow filenames with '@' in them
 - fixes to Session expunge/update/etc.
 - select_table mappers *still* werent always compiling
+- fixed up Boolean datatype
 
 0.2.5
 - fixed endless loop bug in select_by(), if the traversal hit
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 89cc883989..c297195caf 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -143,6 +143,19 @@ class MSBinary(sqltypes.Binary):
 class MSBoolean(sqltypes.Boolean):
     def get_col_spec(self):
         return "BIT"
+    def convert_result_value(self, value, dialect):
+        if value is None:
+            return None
+        return value and True or False
+    def convert_bind_param(self, value, dialect):
+        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
         
 colspecs = {
     sqltypes.Integer : MSInteger,
diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py
index e152a48338..997010f1c2 100644
--- a/lib/sqlalchemy/databases/mysql.py
+++ b/lib/sqlalchemy/databases/mysql.py
@@ -157,7 +157,7 @@ class MSBinary(sqltypes.Binary):
             return "BINARY(%d)" % self.length
         else:
             return "BLOB"
-    def convert_result_value(self, value, engine):
+    def convert_result_value(self, value, dialect):
         if value is None:
             return None
         else:
@@ -182,7 +182,20 @@ class MSEnum(sqltypes.String):
 class MSBoolean(sqltypes.Boolean):
     def get_col_spec(self):
         return "BOOLEAN"
-        
+    def convert_result_value(self, value, dialect):
+        if value is None:
+            return None
+        return value and True or False
+    def convert_bind_param(self, value, dialect):
+        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
+            
 colspecs = {
 #    sqltypes.BIGinteger : MSInteger,
     sqltypes.Integer : MSInteger,
diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py
index 2b10a8ad93..c07952ff21 100644
--- a/lib/sqlalchemy/databases/sqlite.py
+++ b/lib/sqlalchemy/databases/sqlite.py
@@ -79,6 +79,10 @@ class SLBinary(sqltypes.Binary):
 class SLBoolean(sqltypes.Boolean):
     def get_col_spec(self):
         return "BOOLEAN"
+    def convert_result_value(self, value, dialect):
+        if value is None:
+            return None
+        return value and True or False
         
 colspecs = {
     sqltypes.Integer : SLInteger,
diff --git a/test/sql/testtypes.py b/test/sql/testtypes.py
index 71c98f105b..9a25cbee50 100644
--- a/test/sql/testtypes.py
+++ b/test/sql/testtypes.py
@@ -272,6 +272,32 @@ class DateTest(AssertMixin):
         
         #x = db.text("select * from query_users_with_date where user_datetime=:date", bindparams=[bindparam('date', )]).execute(date=datetime.datetime(2005, 11, 10, 11, 52, 35)).fetchall()
         #print repr(x)
+
+class BooleanTest(AssertMixin):
+    def setUpAll(self):
+        global bool_table
+        metadata = BoundMetaData(testbase.db)
+        bool_table = Table('booltest', metadata, 
+            Column('id', Integer, primary_key=True),
+            Column('value', Boolean))
+        bool_table.create()
+    def tearDownAll(self):
+        bool_table.drop()
+    def testbasic(self):
+        bool_table.insert().execute(id=1, value=True)
+        bool_table.insert().execute(id=2, value=False)
+        bool_table.insert().execute(id=3, value=True)
+        bool_table.insert().execute(id=4, value=True)
+        bool_table.insert().execute(id=5, value=True)
+        
+        res = bool_table.select(bool_table.c.value==True).execute().fetchall()
+        print res
+        assert(res==[(1, True),(3, True),(4, True),(5, True)])
+        
+        res2 = bool_table.select(bool_table.c.value==False).execute().fetchall()
+        print res2
+        assert(res2==[(2, False)])
+        
         
 if __name__ == "__main__":
     testbase.main()
-- 
2.47.2