However, web2py maintains the promise of backwards compatibility. One way is to have a 'tinyint_boolean' datatype for those who want to use tinyints as booleans. But that looks kind of messy and inelegant.
An alternative is this: We could add a migration script to /scripts to convert all boolean data types from CHAR(1) to TINYINT(1), and from 'T' to 1 and 'F' to 0. Also, when a table model is called in define_table(), it would check whether its boolean data types are CHAR or INT, and save the result somewhere (so it wouldn't have to keep checking.) If the server is restarted, it would once again perform this check. So, a user would run the migration script and simply restart the server. On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote: > > I have just come across this exact same issue. > > The web2py adapter converts boolean to char(1) but in MySQL the > specification is that boolean is stored as tinyint with 0 and 1. So web2py > adapter is incorrect. Not changing it perpetuates the mistake. > > > On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote: >> >> I'm connecting to a legacy MySQL database (migrate=False) with a lot >> of fields declared BOOLEAN, and noticed that attempts to modify these >> fields with the DAL failed. The DAL issues a query like this: >> >> UPDATE sometable SET someflag='T' WHERE ... >> >> but this gets rejected by MySQL. >> >> Reading through dal.py, I see that the "boolean" type maps to CHAR(1) >> in MySQLAdapter, and represent() converts to "T" and "F" values. >> However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, with >> values 0 or 1, according to: >> >> http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html >> >> I can trivially change this behavior in dal.py for my purposes, but it >> would be interested to try to incorporate this into the main web2py >> distribution. Unfortunately, the trivial change will break backwards >> compatibility for people who are already depending on the current >> behavior. Any thoughts on how this could be done in a backwards- >> compatible way, or is it too much of an edge case to worry about? >> >> Cheers, >> Kevin > > --
