validate_and_insert() question

[tiadobatima]

Hi guys,

I'm trying to understand what validate_and_insert() and validate_and_update() are doing to see if I can use it reliably.

I'm writing an API, and I have no need for form validation. So after spending sometime reading the DAL code, I still have a few questions:

- When I insert a record that already exists (unique field), validate_and_insert() is smart enough to tell me that the record already exist, but I can't figure out how? I can't find out which "select" statement it's doing. 

- If the validation is per-field, and the table has 5 "unique" fields, will DAL make 5 selects before inserting?

- Is it possible to get more than one error in return dict from validate_and_insert() ret.errors?  

- What people are using for DAL/DB layer validations on rest APIs? From my google searches, it looks like not a lot of people are using validate_and_insert().

Thanks! :)

[Alec Taylor]

What kind of features are you looking to get from web2py?

To me it sounds like Twisted might be a better solution for you.

--
 
 
 

[Niphlod]

If you can't understand the code, just try it:

web2py.py -M -S mytestapp

db.define_table(
    'tests',
    Field('uniquefield', unique=True),
    Field('withvalidator', requires=IS_NOT_IN_DB(db, 'tests.withvalidator'))
)
>>> ret = db.tests.validate_and_insert(uniquefield='a', withvalidator='b')
>>> ret
<Row {'errors': <Row {}>, 'id': 1}>
>>> ret = db.tests.validate_and_insert(uniquefield='a', withvalidator='b')
>>> ret
<Row {'errors': <Row {'uniquefield': 'value already in database or empty', 'withvalidator': 'value already in database or empty'}>, 'id': None}>
>>> ret = db(db.tests.uniquefield=='a').validate_and_update(withvalidator='b')
>>> ret
<Row {'updated': None, 'errors': <Row {'withvalidator': 'value already in database or empty'}>}>
>>> ret = db(db.tests.uniquefield=='a').validate_and_update(withvalidator='c')
>>> ret
<Row {'errors': <Row {}>, 'update': 1}>

So, just as stated into the book, you have:
ret.errors that holds all errors if some costraint isn't fullfilled (and there is an error for every field that has an error, so multiple errors can be returned)
ret.id with the id of the newly inserted record
ret.update with the number of lines actually updated.

PS: if you have 5 unique fields, the only way to validate them (before inserting or updating) all is firing 5 different queries ( DAL does "under the hood" this by default with unique (it gets by default a "IS_NOT_IN_DB validator"), IS_IN_DB and IS_NOT_IN_DB validators).

[Niphlod]

With that statement ("""if you don't understand the code, try it""") I meant that you can always check a functionality testing it before loosing your mind on the code.

All the "magic" is done by:

gluon/dal.py (validate_and_insert())

...
for key,value in fields.items():
            value,error = self[key].validate(value)
            if error:
                response.errors[key] = error
...

the "validate()" method on the field calls the validators, defined into gluon/validators.py and "associated" by table definitions (db.define_table) or field requirements (Field('a', requires=....)).

>>> print db.tests.uniquefield.requires
[<gluon.validators.IS_NOT_IN_DB object at 0x32404d0>, <gluon.validators.IS_LENGTH object at 0x3240490>]

A unique field gets a IS_IN_DB validator in order to check before inserting the record (otherwise the commit() would raise an exception at the database level. Unfortunately, there is no universal way to tell WHICH field failed (and why) when you blindly insert a record, and you should wait for the commit() to raise that exception, so you'll lose "multiple" inserts with their properly related errors).

Some definitions (like the "unique" one) defaults to a "hidden" requires to your ones (as documented in the book http://web2py.com/books/default/chapter/29/6#Record-representation). Datetime fields, for example, are checked with a IS_DATETIME() validator,  reference with a IS_IN_DB(), etc. etc. etc.

This is done into the sqlhtml_validators() function in gluon/dal.py. You can find there that a unique Field gets a IS_IN_DB() validator by default

...        
        if field.unique:
                requires._and = validators.IS_NOT_IN_DB(field.db,field)
...

PS: all code excerpts are from the trunk version. May be a little different from your web2py version, but this is sort of an "introduction" on where to find answers to your questions.

[tiadobatima]

Thank you very much for the very detailed answer Niphlod! It's very clear now.

And big thanks to Alec too. I did look into Twisted and Tornado, and they indeed look like a god fit for me. I'm still investigating.

Cheers,

g.