sqlite strange behaviour with boolean

[Tim Richardson]

This is web2py 2.7.4, on Windows, python 2.7.5

I have rows in an sqlite table with a boolean field. Currently all rows have this field set to 0 (via an update query in an sqlite console)

When the table is defined in the web2py model as boolean, this query returns no rows:

   query_rows = db(db.order_status.sent_order_rcvd_email == False).select()

but if the table definition changes the field type to integer,

the query works. 

Seems a bit weird. I thought I would have run into this problem earlier unless it is recent behaviour. 

[Tim Richardson]

Ah. A bit of a gotcha perhaps. sqlite defines boolean as an integer value (0 or 1) but web2py uses a 1 char 'T' or 'F'. 

[Derek]

Since you are using Python 2.x you can redefine 'False' to be 'F' if you wish, although I'm sure you'd have some side effects somewhere else in the system. In Python 3.x you can't do that.

[Niphlod]

don't do that, please. If you're annoyed with web2py's defaults, you can easily sublass the adapter and make your own adjustments.

[Tim Richardson]

Not annoyed, it all works. I just didn't know how it worked. Have amended the book in the gotchas section.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/deXDy6kukT8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Tim Richardson

[Derek]

Yea, he should probably override the equality operator to allow F (not just 0 or False). :D

[Tim Richardson]

"he" won't be overriding or redefining booleans ... I can't see that as a step forward for humanity. 

The decision by web2py to use T or F for booleans in SQLite is interesting ...  I wonder why.

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/deXDy6kukT8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Tim Richardson

[Niphlod]

it's because historically boolean support with 1 and 0 was inserted in sqlite3. sqlite2 has no notion of a specific type for it. Even now, SQLite still use dynamic datatyping, so it really doesn't matter what you choose, as long as it's consistent.
The more interesting (i.e. debatable) choice is T or F default for other engines (such as postgresql), but that's more a matter of backward compatibility (once its in, it never goes out)  than anything else.

[Derek]

I'm just giving Tim some options... facetious as they may be...