web2py DAL Boolean type does not play well with others

[Markus Gritsch]

Hi,

web2py's Boolean implementation (as CHAR(1) with 'T' and 'F' values)
periodically leads to some confusion [1][2].

Further it leads to problems when trying to connect to an existing
schema created by some other ORM. Both SQLObject and SQLAlchemy use
BOOLEAN as the SQL data type with values 0 and 1 (I have only checked
the SQLite and MySQL backends). Such existing data can only be used
with the Integer datatype workaround which does not make me totally
happy.

It's even worse the other way around when web2py created the schema.
I really do not like to use 'T' and 'F' for boolean columns in
SQLObject queries.

I cannot understand the recent 'portability reasons' argumentation in
[2]. If a backend does not support BOOLEAN, the DAL may use the CHAR
(1) workaround. But on backends like SQLite and MySQL which do have a
BOOLEAN data type it should be used, IMO.

I know, for backwards compatibility reasons web2py cannot change its
behavior. However, the current behavior is bad for interoperability
with other widely used ORMs.

[1] http://groups.google.com/group/web2py/browse_thread/thread/c3b5e32c7a5824c0
[2] http://groups.google.com/group/web2py/browse_thread/thread/5a0039a8fba97e39

[mdipierro]

This is a good point. It is as it is because originally my concern was
to have the same type mapped into the same type as much as possible.
Another problem is that some systems do not allow storage of NULL in
boolean and that requires treating the type as an exception.

This cannot be changed for backward compatibility but it is trivial to
create new adaptors for example

db=DAL('sqlite+b://...')

where sqlite+b would mean used boolean instead of char. This is
already done for mssql

db=DAL('mssql://...') uses BIT
db=DAL('mssql2://...') uses CHAR(1)

I will think about this some more but definitively should be solved.

Massimo

> [1]http://groups.google.com/group/web2py/browse_thread/thread/c3b5e32c7a...
> [2]http://groups.google.com/group/web2py/browse_thread/thread/5a0039a8fb...

[Thadeus Burgess]

You have another problem of boolean fields that get exported to csv on
say sqlite, and need to be imported to the production server... there
will need to be a type conversion from T to 1.

-Thadeus

> --
>
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>
>
>

[mdipierro]

This problem does not exist not and should not exist later (unless
there is a bug I am not aware of). Independently on how boolean is
stored in DB, it is bool in web2py (True/False, not 0/1 or 'T'/'F').
Therefore they are always exported and imported as bool.

massimo

On Dec 12, 10:53 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
> You have another problem of boolean fields that get exported to csv on
> say sqlite, and need to be imported to the production server... there
> will need to be a type conversion from T to 1.
>
> -Thadeus
>