I understand the problem. You can chance the DB type (and you did) but
web2py still tries to put an 'F' or a 'T' in there.
I modified trunk so that this can be achieved:
import copy
db =DAL()
db._adapter.types = copy.copy(db._adapter.types)
db._adapter.types['boolean']='TINYINT(1)'
db._adapter.TRUE = 1
db._adapter.FALSE = 0
db.define_table('test',Field('b', 'boolean'))
db.test.insert(b=True)
db.test.insert(b=False)
rows = db(db.test).select()
print db.executesql(db(db.test)._select())
this should work fine.
On Monday, 1 October 2012 14:34:43 UTC-5, MichaelF wrote:
>
> I spoke too soon about this fixing the problem. It seems that
> adding/updating a record with such a field using the admin interface, and
> using a smartgrid, doesn't do it.
>
> I create this table:
>
> db.define_table('Test_bool',
> Field('test_bool', 'boolean'))
>
> I also have the following at the start of my db.py model file:
>
> db._adapter.types = copy.copy(db._adapter.types)
> db._adapter.types['boolean']='TINYINT(1)'
>
> Once web2py creates the table I confirm that MySQL has ccreated the field
> as TINYINT(1).
>
> I go into the admin interface and insert a record, checking the test_bool
> checkbox. The INSERTed record has a 0 for that field. I try it again, same
> result. I then UPDATE one of those records, checking the test_bool
> checkbox, and the field remains at 0.
>
> I then create a simple smartgrid:
>
> def test_bool():
> grid = SQLFORM.smartgrid(
> db.Test_bool,
> deletable = True, editable = True, create = True
> )
> return locals()
>
> I edit one of the records, checking the box, yet it doesn't 'take'.
>
> Now, if I go in manually ans set the field to 1 (using MySQL Workbench,
> outside the web2py environment), then go to the grid, I see that the box is
> checked. If I uncheck it, that 'takes'.
>
> ??
>
> Thanks.
>
> On Saturday, September 22, 2012 7:21:07 AM UTC-6, MichaelF wrote:
>>
>> Converting to 2.x fixed the problems.
>>
>> On Wednesday, September 19, 2012 12:04:28 PM UTC-6, MichaelF wrote:
>>>
>>> I have come across one bug with this. If I add a record using the admin
>>> interface, I check the 'Is_home_team' checkbox (Is_home_team is defined as
>>> a boolean, of course), yet the record has 0 for that field. Given that, as
>>> you might expect then, all records have a 0 for that field.
>>>
>>> ??
>>>
>>> On Monday, September 17, 2012 9:53:34 PM UTC-6, MichaelF wrote:
>>>>
>>>> Well, that's unfortunate. I've migrated this semi-manually; I had only
>>>> four 'boolean' fields.
>>>>
>>>> Other than that, the suggested fix (
>>>> db._adapter.types['boolean']='TINYINT(1)' ) seems to work.
>>>>
>>>> On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote:
>>>>>
>>>>> I cannot reproduce this error with your code in 2.0.9 and the lines in
>>>>> your traceback do not correspond to the source code I have. I think you
>>>>> may
>>>>> be using an older dal.py
>>>>>
>>>>> On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote:
>>>>>>
>>>>>> Yes; here it is:
>>>>>>
>>>>>> 1.
>>>>>> 2.
>>>>>> 3.
>>>>>> 4.
>>>>>> 5.
>>>>>> 6.
>>>>>> 7.
>>>>>> 8.
>>>>>> 9.
>>>>>>
>>>>>> Traceback (most recent call last):
>>>>>> File "gluon/restricted.py", line 205, in restricted
>>>>>> File "C:/Program Files
>>>>>> (x86)/web2py/applications/NCAA_schedule/models/db.py"
>>>>>> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>,
>>>>>> line 165, in <module>
>>>>>> File "gluon/dal.py", line 6320, in define_table
>>>>>> File "gluon/dal.py", line 742, in create_table
>>>>>> File "gluon/dal.py", line 797, in migrate_table
>>>>>> File "gluon/dal.py", line 6714, in __getitem__
>>>>>> KeyError: 'length_is_yards'
>>>>>>
>>>>>> The table definition follows:
>>>>>>
>>>>>> db.define_table('Pool',
>>>>>> Field('Pool_name', 'string', required=True,
>>>>>> unique=True),
>>>>>> Field('Address1', 'string', length=60),
>>>>>> Field('Address2', 'string', length=60),
>>>>>> Field('City', 'string', length=60),
>>>>>> Field('State', 'string', length=2),
>>>>>> Field('Zip', 'string', length=15),
>>>>>> Field('Nr_lanes', 'integer', required=True),
>>>>>> Field('Length', 'integer', required=True),
>>>>>> Field('Length_is_yards', 'boolean',
>>>>>> required=True,default=True),
>>>>>> Field('Has_moveable_bulkhead', 'boolean',
>>>>>> required=True,
>>>>>> default=False),
>>>>>> format='%(Pool_name)s %(Nr_lanes)s')
>>>>>>
>>>>>> Line 165 is the last line of the statement (format=...).
>>>>>>
>>>>>> On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro
>>>>>> wrote:
>>>>>>>
>>>>>>> Do you have a traceback with more information?
>>>>>>>
>>>>>>> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote:
>>>>>>>>
>>>>>>>> Thanks. However, I refer to that field with upper case in all
>>>>>>>> places. Can you tell me where the lower case 'pending' comes from? The
>>>>>>>> field name has always been defined as upper case, and the app has been
>>>>>>>> working up until I made that latest change. So I went into the db and
>>>>>>>> changed the field name to start with lower case, then changed the
>>>>>>>> model
>>>>>>>> file to make it lower-case 'pending'. That worked, but now the next
>>>>>>>> boolean
>>>>>>>> field in the db.py file has an upper-case/lower-case problem. The
>>>>>>>> field is
>>>>>>>> "Length_is_yards" in both the db.py file and the db, and has been that
>>>>>>>> way
>>>>>>>> for weeks, and we've been through several db migrations for the past
>>>>>>>> several weeks (not sure about on those particular tables, though). Now
>>>>>>>> I
>>>>>>>> get the KeyError as shown above, but this time it's for field
>>>>>>>> 'length_is_yards'. It looks to me that web2py is assuming it's lower
>>>>>>>> case.
>>>>>>>>
>>>>>>>> One of my migrations last week was the "fake_migrate_all=True"
>>>>>>>> type; don't know if that's relevant.
>>>>>>>>
>>>>>>>> Also, in the .database file the field name is Length_is_yards
>>>>>>>> (leading "L" is capital), as is the field name in the MySQL db.
>>>>>>>>
>>>>>>>> I'm confused.
>>>>>>>>
>>>>>>>> Michael
>>>>>>>>
>>>>>>>> On Monday, September 17, 2012 12:51:34 PM UTC-6, Massimo Di Pierro
>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> Field('Pending' <<< upper case
>>>>>>>>> ...
>>>>>>>>> <type 'exceptions.KeyError'> 'pending' <<< lower case
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Monday, 17 September 2012 11:37:13 UTC-5, MichaelF wrote:
>>>>>>>>>>
>>>>>>>>>> I did a simple import of 'copy' and that got me by that first
>>>>>>>>>> problem. But now I have the following problem:
>>>>>>>>>>
>>>>>>>>>> db.define_table('Person_certification',
>>>>>>>>>> Field('Person', db.Person),
>>>>>>>>>> ...
>>>>>>>>>> Field('Pending', 'boolean', default = False),
>>>>>>>>>> ...
>>>>>>>>>>
>>>>>>>>>> I get the following error on the line that defines field
>>>>>>>>>> 'Pending' (and this is the first 'boolean' type in the file):
>>>>>>>>>> <type 'exceptions.KeyError'> 'pending'I have not changed the
>>>>>>>>>> underlying MySQL db yet; all the booleans are still char(1). Do I
>>>>>>>>>> need to
>>>>>>>>>> change them first to Tinyint(1)? I tried that; same error.
>>>>>>>>>>
>>>>>>>>>> Thanks.
>>>>>>>>>>
>>>>>>>>>> On Monday, September 17, 2012 9:21:37 AM UTC-6, MichaelF wrote:
>>>>>>>>>>>
>>>>>>>>>>> 1. What will I need to import to get it to recognize 'copy'? I
>>>>>>>>>>> run the suggested code and get told that 'copy' does not exist.
>>>>>>>>>>> (I'm
>>>>>>>>>>> running 2.5; what do I conditionally import?)
>>>>>>>>>>>
>>>>>>>>>>> 2. Are we doing a copy because all the adapters share the same
>>>>>>>>>>> 'types' object?
>>>>>>>>>>>
>>>>>>>>>>> On Tuesday, August 7, 2012 3:48:35 PM UTC-6, Massimo Di Pierro
>>>>>>>>>>> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>> On can always do:
>>>>>>>>>>>>
>>>>>>>>>>>> db=DAL('mssql://...')
>>>>>>>>>>>> db._adapter.types = copy.copy(db._adapter.types)
>>>>>>>>>>>> db._adapter.types['boolean']='TINYINT(1)'
>>>>>>>>>>>>
>>>>>>>>>>>> It should work. Can you please check it?
>>>>>>>>>>>>
>>>>>>>>>>>> On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
--
