Maintain uniqueness on django-localized-fields

Question

I'm trying to avoid having duplicate localized items stored in a Django-rest-framework app, django-localalized-fields package with a PostgreSQL database I can't find any way to make this work.

(https://pypi.org/project/django-localized-fields/)

I've tried writing custom duplicate detection logic in the Serializer, which works for create, but for update the localized fields become null (they are required fields, so I receive a not null constraint error). It seems to be django-localized-fields utility which is causing this problem.

The serializer runs correctly (create/update) when I'm not overriding create/update in the serializer by defining them separately.

I've also tried adding unique options to the database in the model, which does not work - duplicates are still created. Using the standard unique methods, or the method in the django-localized-fields documentation (uniqueness=['en', 'ro']).

I've also tried the UniqueTogetherValidator in Django, which also doesn't seem to support HStore/localizedfields.

I'd appreciate some help in tracking down either how to fix the update in the serializer or place a unique constraint in the database. Since django-localized-fields uses hstore in PostgreSQL it must be a common enough problem for applications using hstore to maintain uniqueness.

For those who aren't familiar, Hstore stores items as key/value pairs within a database. Here's an example of how django-localized-fields stores language data within the database:

"en"=>"english word!", "es"=>"", "fr"=>"", "frqc"=>"", "fr-ca"=>""

Answer 1

django-localized-fields constraint unique values only per the same language. If you want to achieve that values in a row don't collide with values in another row, you have to validate them on Django and database level.

Validation in Django

In Django you can create custom function validate_hstore_uniqueness, which is called everytime is model validated.

def validate_hstore_uniqueness(obj, field_name):

    value_dict = getattr(obj, field_name)
    cls = obj.__class__
    values = list(value_dict.values())

    # find all duplicite existing objects
    duplicite_objs = cls.objects.filter(**{field_name+'__values__overlap':values})
    if obj.pk:
        duplicite_objs = duplicite_objs.exclude(pk=obj.pk)

    if len(duplicite_objs):
        # extract duplicite values
        existing_values = []
        for obj2 in duplicite_objs:
            existing_values.extend(getattr(obj2, field_name).values())

        duplicate_values = list(set(values) & set(existing_values))

        # raise error for field
        raise ValidationError({
            field_name: ValidationError(
                _('Values %(values)s already exist.'),
                code='unique',
                params={'values': duplicate_values}
            ),
        })


class Test(models.Model):
    slug = LocalizedField(blank=True, null=True, required=False)

    def validate_unique(self, exclude=None):
        super().validate_unique(exclude)

        validate_hstore_uniqueness(self, 'slug')

Constraint in DB

For DB constraint you have to use constraint trigger.

def slug_uniqueness_constraint(apps, schema_editor):
    print('Recreating trigger quotes.slug_uniqueness_constraint')

    # define trigger
    trigger_sql = """
        -- slug_hstore_unique
        CREATE OR REPLACE FUNCTION slug_uniqueness_constraint() RETURNS TRIGGER
        AS $$
            DECLARE
                duplicite_count INT;
            BEGIN
                EXECUTE format('SELECT count(*) FROM %I.%I ' ||
                               'WHERE id != $1 and avals("slug") && avals($2)', TG_TABLE_SCHEMA, TG_TABLE_NAME)
                    INTO duplicite_count
                    USING NEW.id, NEW.slug;

                IF duplicite_count > 0 THEN
                    RAISE EXCEPTION 'Duplicate slug value %', avals(NEW.slug);
                END IF;

                RETURN NEW;
            END;
        $$ LANGUAGE plpgsql;

        DROP TRIGGER IF EXISTS slug_uniqueness_constraint on quotes_author;
        CREATE CONSTRAINT TRIGGER slug_uniqueness_constraint
            AFTER INSERT OR UPDATE OF slug ON quotes_author
            FOR EACH ROW EXECUTE PROCEDURE slug_uniqueness_constraint();

    """

    cursor = connection.cursor()
    cursor.execute(trigger_sql)

And enable it in migrations:

class Migration(migrations.Migration):

    dependencies = [
        ('quotes', '0031_auto_20200109_1432'),
    ]

    operations = [
        migrations.RunPython(slug_uniqueness_constraint)
    ]

Probably is a good idea to also create GIN db index for speeding up lookups:

CREATE INDEX ON test_table using GIN (avals("slug"));