I mean, you have to access a preexisting database with boolean stored as
tinyints, datetime stored as unixtime and so on. You cannot change that.
Focusing on booleans, how to make a simple form work?
At Mysql Test Instance:
CREATE TABLE table1 (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`chk1` tinyint(1) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
);
INSERT INTO table1 SET chk1=1;
Model:
db = DAL('mysql:....', migrate_enabled=False)
db.define_table('table1',
Field('chk1', 'boolean'),
)
Controller:
def checkbox():
form = SQLFORM(db.table1, record=db.table1[1])
form.process().accepted
return dict(form=form)
This gives no error but it doesn't work neither with reads nor with writes.
Form always generate "chk1=0" when you try to update the record, even if
you mark the checkbox. DAL expects a varchar field at database with 'T' or
'F' value, so you'll have this unexpected behaviour.
One solution would be creating a Mysql view (if you have rights to do so)
that converts tinyint to the expected varchar, but this would work only for
SELECTs but no with UPDATEs (you cannot update a derived column at mysql):
CREATE VIEW view1
AS SELECT id, IF(chk1=1,'T','F') as chk1 FROM table1;
Then you rewrite model and controller with 'view1' instead of 'table1', and
you'll see right results with "reads" but error with "writes".
So if you need the form to update the record you can do the conversion at
web2py level, using 'filter_in' and 'filter_out'. Rewrite model:
db.define_table('table1',
Field('chk1',
'string',
widget = SQLFORM.widgets.boolean.widget,
filter_in = lambda x: 1 if x == 'on' else 0,
filter_out = lambda x: 'on' if x == 1 else None,
)
)
Now it works also for writes.
BUT, it's not trivial at all and probably unstable. I've made lots of
combinations until i get this working code. Note, for example, that 'T' is
internally converted to/from 'on' and 'F' to/from None. Besides, you have
to define field as 'string'. I'm pretty sure this has "colateral effects".
At least unexpected behaviour with some funcionalities of the framework.
SQLCustomType should work fine with this, but it's marked as experimental
and it has a related bug with write forms (
https://code.google.com/p/web2py/issues/detail?id=1879).
SO, anyone has a better approach?.
Regards.
--
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 the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
