Best way to restrict database insert based on another column in Weppy/pyDal?

Question

I'd like to restrict DB inserts for a particular table if the same user already has an item with the same name.

Table
_____________
user     | place         | label     |
--------------------------------------------------------------------------
me       | san francisco | work      |
you      | san francisco | leisure   | # This is ok because different user
me       | san francisco | leisure   | # THIS IS NOT ALLOWED - INSERT FAIL

The labels are unique to the user so I don't want the "name" column to be forced unique -> many users should be able to add the same place but with whatever "label" in the label column they want.

NOTE: Using Weppy - I don't have enough reputation to create a new tag. I will tag this question once I can/there is a weppy tag.

I've found a workaround that seems like too much code. Need to use Place().add_new() instead of built-in pyDal methods:

from weppy.dal import Model, Field, belongs_to


class Place(Model):
    belongs_to('user')

    name = Field() # not using `unique=True` here so others can insert same names.
    label = Field()

    def add_new(self, user_id, name, label):
        user_places = self.db(self.db.Place.user == user_id).select()
        current_names = [x.name for x in user_places]
        if name not in current_names:
            self.create(
                user=user_id,
                name=name,
                label=label
            )

Answer 1

The complicated thing about uniqueness is that you can't be sure it will be respected using just the application code in a concurrent environment like web applications.

For instance, if the same user will produce two concurrent requests – maybe in this case is quite unlikely, but you should be aware of this – then the application code may fail, as a record with the same values can be inserted between the check and the other insert.

This is why you should rely on the database itself first, and since weppy 0.7 you can use database indexes:

class Place(Model):
    belongs_to('user')

    name = Field()
    label = Field()

    indexes = {
        'user_uniq_name': {
            'fields': ['user', 'name'], 'unique': True}
    }

Just remember to generate migrations after you add the index.

Once you have an index with the unique constraint, you can just wrap the creation of the new record within a try-except block:

try:
    rv = Place.create(**some_params)
except:
    # handle the error eg:
    from weppy import abort
    abort(422)

Surely you can still keep some application check before the insert, but since you need to check multiple values and custom validators support just a single value (unless using session to check the user), you'd better use callbacks:

from weppy.dal import before_insert

class Place(Model):
    @before_insert
    def check_name_uniqueness(self, input_fields):
        if self.db(
            (self.user == input_fields['user']) &
            (self.name == input_fields['name'])
        ).count() > 0:
            return True
        return False

As from the docs:

All the callbacks method should return None or False (not returning anything in python is the same of returning None) otherwise returning True will abort the current operation.