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