Using validate_and_insert with a computed field

[Chris]

Hello,

I've got a users table with a computed field:

db.define_table(
    auth.settings.table_user_name,
    [...]
    Field('calculated_field', requires=IS_NOT_EMPTY(),
          compute=lambda(r): calculate_field(r)),

And a bit of code that uses validate_and_insert() to insert a row:

        dct_new_user = {...}

        new_user = db.auth_user.validate_and_insert(**dct_new_user)

This code always fails, returning

<Row {'errors': {'calculated_field': 'Enter a value'}, 'id': None}>

Is this a bug? If not, what should I do to get around it?

Thanks!

[Alfonso Serra]

Hi Chris, why do you use requires IS_NOT_EMPTY in a computed field?, if you do, you have to supply a value before is computed, isnt it?

[Christopher L]

Hi Alfonso,

I think the original reason was that sometimes I would end up with a computed field without a value. Inserts should automatically compute rows, but for some reason that wasn't happening here.

On Apr 18, 2017 8:04 AM, "Alfonso Serra" <aleon...@gmail.com> wrote:

Hi Chris, why do you use requires IS_NOT_EMPTY in a computed field?, if you do, you have to supply a value before is computed, isnt it?

--
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/Kqfg-MG5K44/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alfonso Serra]

As per the docs, they say they are stored in db on insert but not computed on retrieval. i guess this applies to updates.
Does this calculate_field function does not return a value in some case? for example total = unit * price may be None if the operation fails because either unit or price is None or the function does not return any value.

[Anthony]

You should not set "requires" on a computed field -- when you use validate_and_insert, the validation will be run before the computed value is generated. However, you can instead set required=True. In that case, any time you do an insert or update, if the fields needed by the computed field are missing, the computed field will trigger an exception (you'll have to catch the exception). Alternatively, you could add IS_NOT_EMPTY validators to the fields that are needed by the computed field -- as long as those fields are not empty, neither will the computed field be.

Anthony

[Christopher L]

Thanks for the info Anthony! I'll try one of those approaches.

Why is validation run before compute?

[Anthony]

On Tuesday, April 18, 2017 at 5:41:38 PM UTC-4, Chris wrote:

Thanks for the info Anthony! I'll try one of those approaches.

Why is validation run before compute?

In terms of implementation, compute is handled in the .insert() method, which must necessarily run after validation. I suppose we could add code to calculate computed values before validation in the .validate_and_ methods, but I don't think that really makes sense. Computed fields should not need to be validated, as they are in control of generating their own values. You should instead validate the inputs to the computed field.

Anthony

[Chris]

I looked it up:

Notice that requires=... is enforced at the level of forms, required=True is enforced at the level of the DAL (insert), while notnull, unique and ondelete are enforced at the level of the database. While they sometimes may seem redundant, it is important to maintain the distinction when programming with the DAL.

 The mix of form, DAL and database level validation threw me off, but I think I get it now. requires= would be checked before any DAL logic. I'll remove the logic from the computed field. Hopefully it should be populated for all future values. Thanks!

[Chris]

Uh oh...I just removed requires from the computed field and reran my unit tests, and now validate_and_insert inserts but the computed field isn't calculated at all! I tried insert and it does the computation.

Is there some reason validate_and_insert would not compute where insert does?

[Anthony]

On Wednesday, April 19, 2017 at 7:50:46 PM UTC-4, Chris wrote:

Uh oh...I just removed requires from the computed field and reran my unit tests, and now validate_and_insert inserts but the computed field isn't calculated at all! I tried insert and it does the computation.

Is there some reason validate_and_insert would not compute where insert does?

The computed field will not get a value if any of the fields needed by the compute function are missing. Also, I notice that when .validate_and_insert() is called, if any of the fields are set to None, they end up getting dropped before being passed to .insert() (this should probably be considered a bug). So, if you are setting a field needed by the compute function to None, then that would cause the compute to fail. If that's not your situation, then you'll have to show more code to diagnose the problem.

Anthony

[Anthony]

I'm not sure if this is the cause of your problem, but I just posted an issue: https://github.com/web2py/pydal/issues/462

Anthony

[Chris]

Thanks for the update!

I'm passing all of the fields needed by the compute function. It looks something like this:

        dct_new_user = { fields here }
        # new_user = db.auth_user.insert(**dct_new_user)
        new_user = db.auth_user.validate_and_insert(**dct_new_user)

The former calculates the computed field, but the latter does not.

[Anthony]

Need to see the fields.

[Chris]

Can do, here's an example:

# DB

db.define_table('story',
    Field('title',
          length=512,
          widget=lambda field, value: SQLFORM.widgets.string.widget(field,
                                        value,
                                        _size=40),
          requires=[IS_NOT_EMPTY(), IS_LENGTH(minsize=1, maxsize=512)]),
    Field('titleAsSlug',
          compute=lambda(r): urls.convert_to_slug(r['title'])),
    Field('readURL', unique=True, label=T('URL'), required=False,
          widget=lambda field, value:
          SQLFORM.widgets.string.widget(field,
                                        value,
                                        _size=60,
                                        _placeholder='http://www.example.com')),
)

# Test

class TestModels(unittest.TestCase):
    def testStoryNewCreate(self):
        dct_new_story = {
                         "title": "Unit Test"}
        new_story = db.story.validate_and_insert(**dct_new_story)
        self.assertFalse(new_story["errors"], "Error inserting a new story: " + str(new_story))

The readURL field generates an error that the entry is found in the database. Unique=True is a DB constraint according to the web2py manual and NULLs in PostgreSQL and SQL in general don't violate the unique constraint. Web2py attaches an IS_NOT_IN_DB validator that converts None to "None" instead of null, which only works until there is a row with a readURL of 'None'.
The titleAsSlug field is set to an empty string by the validator, which is perhaps why validate_and_insert doesn't compute it where insert does.

Hope that's helpful, thanks for anything you can do here!


On Thursday, April 20, 2017 at 11:05:02 PM UTC-4, Anthony wrote:

Need to see the fields.

[Anthony]

Thanks. I updated the Github issue, as there are multiple problems with the current implementation of the .validate_and_ methods.

For now, you should be able to get around the two issues you observe by removing the validators:

db.story.titleAsSlug.requires = db.story.readURL.requires = None

You can probably permanently set the titleAsSlug requires to None, as it will not appear in forms anyway. The default readURL validator, on the other hand, may be needed if you intend to allow inserts/updates directly via SQLFORM.process().

The problem is that the default validator assigned to titleAsSlug transforms None to '', and the default validator for readURL transforms None to 'None'. These values then get inserted in the record.

Anthony

[Chris]

Many thanks Anthony!

That looks like it will work for now...I may specifically handle call that code just before validate_and_insert calls in the web app, so I don't get rid of readURL validation in a place where I need it.

[Chris]

Just as a quick follow up: If this is still an issue it's going to probably be in validators.py:726:

def __call__(self, value):
    value = to_native(str(value))

Not 100% sure how this code works, but it does seem that these calls are either backwards or need to be changed, as to_native will always be a null operation.