Bug in deflate of json fields

[Sebastian Ortiz Vasquez]

Hi, i discovered a bug, but don't have enoght time to solve this.

The problem is this, i'm using psycopg2==2.5.2 to get this working, because pg8000 still in web2py distribution does not work.

I defined a field of type 'json', (postgresql supports it), when web2py inflates the json field as a dict, thats good, but if i do change the json field, and i do a row.update_record() it fails as follows, becuse DAL is not able to deflate the field as json, a work arround i used is to set the field using row[field] = gluon.contrib.simplejson.dumps(dict) and it works, but is really annoying if i do change any other field in this way row[any_field] = 'some data', and the call the row.update_record() it fails becuase the same problem with the json field.


Thank you,

File "applications/pyforms/modules/pyform_forms.py", line 339, in _edit_grid_data
    if row.update_record():
  File "/home/sebas/dev/orfeo_pyforms/web2py/gluon/dal.py", line 10640, in __call__
    table._db(table._id==id,ignore_common_filters=True).update(**newfields)
  File "/home/sebas/dev/orfeo_pyforms/web2py/gluon/dal.py", line 10549, in update
    ret = db._adapter.update("%s" % table._tablename,self.query,fields)
  File "/home/sebas/dev/orfeo_pyforms/web2py/gluon/dal.py", line 1620, in update
    raise e
DataError: invalid input syntax for type json
LINE 1: ...topografica=NULL,reviso=NULL,estructura_ecologica='{u''1'': ...

[Sebastian Ortiz Vasquez]

I have been debugging a little bit this problem and found the following, in the model a json field is marked as  "readable=True, writable=False", i build my own JqGridWidget for allowing insert data via ajax request from the jqGrid widget, and the  SQLForm, in conjunction with the inflated that from the DAL gives this erratic behaviuor.

When a field is not writable the FORM send the update with the data from the record, if exists, and the data on the record was inflated to a dict, but when the RecordUpdates tries to write to the database it does not deflates the dict to a proper json string, and fails. One workarround is the following, other more general could be if the db adapter could do the proper deflate.

in sqlhtml.py

if dbio:
            if 'delete_this_record' in fields:
                # this should never happen but seems to happen to some
                del fields['delete_this_record']
            for field in self.table:
                if not field.name in fields and field.writable is False \
                        and field.update is None and field.compute is None:
                    if record_id and self.record:
                        #: Added valitation for deflate the data correctly.
                        if field.type == 'json':
                            fields[field.name] = serializers.json(self.record[field.name])
                        else:
                            fields[field.name] = self.record[field.name]
                    elif not self.table[field.name].default is None:
                        fields[field.name] = self.table[field.name].default