How to rewrite this postgres constraint to be more in line with django 2.2?

Question

THis is the raw query that I write for postgres for the check constraint

   ALTER TABLE rea_asplinkage ADD CONSTRAINT asp_sub_project_positive_integer
        CHECK (
            jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number'
        and (linkage->'root'->>'in_sub_project')::numeric % 1 = 0
        and (linkage->'root'->>'in_sub_project')::numeric > 0
        );

And the way I create the migration is this way

# Generated by Django 2.2.10 on 2020-05-16 12:59

from django.db import connection, migrations



class Migration(migrations.Migration):

    dependencies = [("rea", "0029_asplinkage")]

    operations = [
        migrations.RunSQL(
            sql="""
            ALTER TABLE rea_asplinkage ADD CONSTRAINT asp_sub_project_positive_integer
            CHECK (
                jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number'
            and (linkage->'root'->>'in_sub_project')::numeric % 1 = 0
            and (linkage->'root'->>'in_sub_project')::numeric > 0
            );
            """,
            reverse_sql="""
                ALTER TABLE rea_asplinkage DROP CONSTRAINT "asp_sub_project_positive_integer";
            """,
        )
    ]

And this works.

But this means that my original model does not show the constraint in the class Meta of the ASPLinkage model

class ASPLinkage(TimeStampedModel, SoftDeletableModel, PersonStampedModel, OrganizationOwnedModel):
    linkage = JSONField(default=default_linkage_for_asp)

    objects = OrganizationOwnedSoftDeletableManager()

I have tried ExpressionWrapper and RawSQL in creating the constraints inside the class Meta, but it still doesn't work.

For reference, I have looked at the examples found in https://github.com/django/django/blob/master/tests/constraints/models.py#L12

I have also looked at Separate Database and State migration via https://realpython.com/create-django-index-without-downtime/#when-django-generates-a-new-migration

But I still cannot get it to work

So is this even possible?

Update

Let me write a summary of my question for better readability.

I want to write constraints on a JSONField.
I can do that directly on the Postgres
Therefore I can do it using raw sql in the migration file
But I cannot do the equivalent using Django model meta / CheckConstraint which usually is how anyone does it. See https://docs.djangoproject.com/en/3.0/ref/models/constraints/
So how do I rewrite this raw sql to produce constraint on a jsonfield in postgres but in the Django way?

Are you just using this field to store an integer? If so can you use a models.PositiveIntegerField instead? — voodoo-burger
– voodoo-burger, Commented May 16, 2020 at 15:30
because i need to cater for semi-structured data, i chose jsonb. therefore, jsonb is a hard limit. But at the same time, i want to cater for as much data integrity as possible at the database level. so my answer to your question is NO. it's not just to store 1 field — Kim Stacks
– Kim Stacks, Commented May 17, 2020 at 16:03

Simon Charette · Accepted Answer · 2020-05-28 13:07:22Z

2

+300

In order to achieve that on Django 2.2 you'll need to register two new JSONField transforms/lookups since support for conditional expressions was only added in the upcoming 3.1 release.

You'll first want to register lookups for JSONField key accesses

from django.db import models
from django.db.models.lookups import Lookup
from django.contrib.postgres.fields.jsonb import (
    KeyTransform, KeyTransformTextLookupMixin
)

@KeyTransform.register_lookup
class KeyTransformIsInteger(KeyTransformTextLookupMixin, Lookup):
    lookup_name = 'is_int'
    prepare_rhs = False

    def as_sql(self, compiler, connection):
        key_expr = KeyTransform(
            self.lhs.key_name, *self.lhs.source_expressions, **self.lhs.extra
        )
        key_sql, key_params = self.process_lhs(
            compiler, connection, lhs=key_expr
        )
        lhs_sql, lhs_params = self.process_lhs(compiler, connection)
        rhs_sql, rhs_params = self.process_rhs(compiler, connection)
        sql = "(jsonb_typeof(%s) = %%s AND mod(%s::numeric, %%s) = %%s) IS %s" % (
            key_sql, lhs_sql, rhs_sql
        )
        params = [
            *key_params, 'number',
            *lhs_params, 1, 0,
            *rhs_params,
        ]
        return sql, params

@KeyTransform.register_lookup
class KeyTransformIntegerGt(KeyTransformTextLookupMixin, Lookup):
    lookup_name = 'int_gt'
    prepare_rhs = False

    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = self.process_lhs(compiler, connection)
        rhs_sql, rhs_params = self.process_rhs(compiler, connection)
        sql = "%s::int > %s" % (lhs_sql, rhs_sql)
        params = [*lhs_params, *rhs_params]
        return sql, params

Once this is done you should be able to define your constraint like

CheckConstraint(
    check=Q(
        linkage__root__in_sub_project__is_int=True,
        linkage__root__in_sub_project__int_gt=0,
    ),
    name='asp_sub_project_positive_integer',
)

Once you're on Django 3.1 you should be able to pass a RawSQL directly to CheckConstraint.check as long as it has an output_field = models.BooleanField().

RawSQL("""
   jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number'
   and (linkage->'root'->>'in_sub_project')::numeric % 1 = 0
   and (linkage->'root'->>'in_sub_project')::numeric > 0
""",
    output_field=models.BooleanField()
)

edited May 28, 2020 at 13:07

answered May 26, 2020 at 18:27

Simon Charette

5,1361 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Kim Stacks Over a year ago

> return sql % (lhs, lhs, rhs), params the lhs and rhs are undefined here. Can help?

Kim Stacks Over a year ago

You can take a look at my 2ndtry in gist.github.com/simkimsia/60149d5ab4bad1817fc33315dc11b300 after I try to narrow down the errors

Simon Charette Over a year ago

I adjusted the answer to address the missing variable name references and include the lhs params twice.

Kim Stacks Over a year ago

Wow! It works. I see that you're at 2950 so I will give you another 50 pts to help you get to a nice round 3k.

Simon Charette Over a year ago

Thanks for your kind offer @KimStacks but I'm not taking freelance work at the moment and I usually prefer to spend my time contributing to Django itself when possible. I suggest you have a look at youtube.com/watch?v=CGF-0csOjPw and go through the documentation docs.djangoproject.com/en/3.0/howto/custom-lookups to learn more about it. In the end you want as_sql to return something you'd be able to pass to cursor.execute, I had to use mod over % because the latter conflicted with string formatting.

|

Collectives™ on Stack Overflow

How to rewrite this postgres constraint to be more in line with django 2.2?

Update

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related