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
>>>>>>>>>>>>>
>>>>>>>>>>>>>
--
