Proposal: Database Constraints

[schinckel]

Some years ago, I discussed adding database-level check constraints into django: https://groups.google.com/forum/#!topic/django-developers/OJN5kpcseAg

There is also an issue: https://code.djangoproject.com/ticket/11964

I'm thinking about revisiting this, and wanted to get some discussion going about if this is a viable thing to do, and what it might look like.

Django already uses check constraints with PositiveIntegerField and friends, and at first blush I thought we might be able to co-opt some of that (indeed, I've got an internal monkey-patch that does, with some level of success). However, other than the fact there is already a 'sql_create_check' string, the actual python code that creates the check constraint probably isn't that usable in this context. Also, I like the idea of having more complex constraints (postgres has EXCLUDE constraints, but I don't know if there is an equivalent for other backends).

The approach that I am thinking of could see a syntax similar to:

    class MyModel(models.Model):

        start = models.DateField()

        finish = models.DateField(constraints=[('check', '> start')])

        user = models.ForeignKey('auth.User')

This maps directly to creating a check constraint on the table:

     ALTER TABLE "myapp_mymodel" ADD CONSTRAINT CHECK (finish > start)

And, on the same model, a more complex constraint could look like:

        class Meta:

            constraints = [

                ('exclude', [

                    ('overlaps', ['start','finish']),

                    ('equal', 'user')

                ])

           ]

I'm still unsure of the best way to describe this: it's supposed to mean:

       ALTER TABLE "myapp_mymodel" ADD EXCLUDE USING gist (daterange(start, finish) WITH &&, user_id WITH =)

(but the python syntax is obviously immaterial at this stage).

Obviously, we can't just rely on the database to do the validation for us: it will just raise DatabaseErrors when something fails to validate anyway, so we would want to handle this stuff in django's validation framework.

One possibility, at least with the field-based check constraint, would be to automatically add a field validator in the case of a CHECK constraint, however in the case of an EXCLUDE constraint, we can't really validate _without_ hitting the database. Does this mean we should treat EXCLUDE-type constraints as something that is beyond the scope of Django?

With the new migrations framework, it's actually trivial to write a migration to add this type of constraint (or the check constraint), and a pre_save signal handler in conjunction with that would get 90% of the way, but it's still going to be open to a race condition anyway - the only way to actually do that is to try to save the object and see what the database says.

So, I guess I'm asking: is it worth pursuing this further, either in part or full?

Matt.

[Andrew Godwin]

Hmm, I'm not sure about this. On the one hand, during the migrations work I refactored check constraints for the existing stuff (e.g. PostitiveIntegerField) out of the type string and into a separate check constraints string, so some of the work is already there, especially around making sure they're present on field alteration.

On the other hand, check constraints vary wildly from database to database - MySQL doesn't have them, SQLite has a basic version in later versions only and parses but ignores them in earlier versions, PostgreSQL has full support and Oracle has them too but with a different set of functions to PostgreSQL, so it's unlikely anything other than simple comparison would be portable.

For that reason, I wouldn't be the fan of the exploded syntax you've given; I think I'd rather just be able to supply a list of strings for my constraints that are SQL themselves; there's no real benefit to abstraction here, and removing it doubtless removes a lot of code and bugs. Otherwise, I think I'd be +0.

Andrew

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/250ed272-198a-478f-b2ce-920afcf533a2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Blanchard]

Hi

Just FYI: back in 2007 GSOC there was a project to add constraints. The syntax was as follows:

class Manufacturer(models.Model):

mfg_name = models.CharField(maxlength=50)

car_sale_start = models.DateField()

car_sale_end = models.DateField()

quantity_sold = models.IntegerField()

car_price = models.IntegerField()

class Meta:

constraints = (

("check_name”, Check(mfg_name__like = 'Merc*')),

("check_date”, Check(car_sale_start__between = [date(2007,1,1), date(2008,1,1)])),

("check_end_date”, Check(car_sale_end__gte = 'car_sale_start')),

("check_quantity”, Check(quantity_sold__gte = 0)),

("check_price”, Check(car_price__between = [0, 10000])),

     )

So a list of constraint name and a Check() pairs. I think the idea was that Check() could be ANDd or ORd together a bit like the Q() object.

It worked with Postgres AFAIR.

Simon

[schinckel]

On Wednesday, April 2, 2014 3:35:24 AM UTC+10:30, Andrew Godwin wrote:

Hmm, I'm not sure about this. On the one hand, during the migrations work I refactored check constraints for the existing stuff (e.g. PostitiveIntegerField) out of the type string and into a separate check constraints string, so some of the work is already there, especially around making sure they're present on field alteration.

On the other hand, check constraints vary wildly from database to database - MySQL doesn't have them, SQLite has a basic version in later versions only and parses but ignores them in earlier versions, PostgreSQL has full support and Oracle has them too but with a different set of functions to PostgreSQL, so it's unlikely anything other than simple comparison would be portable.

Thanks Andrew. I haven't touched other databases for a long time, so it's good to get that feedback.

 

For that reason, I wouldn't be the fan of the exploded syntax you've given; I think I'd rather just be able to supply a list of strings for my constraints that are SQL themselves; there's no real benefit to abstraction here, and removing it doubtless removes a lot of code and bugs. Otherwise, I think I'd be +0.

Pushing on from there: it's not much work from doing "constraints = ('check start < finish',)" to just writing a RunSQL migration operation. The exploded syntax was more me working things through late at night anyway. And it seems like there's not really much else to do other than raw SQL for exclude constraints.

So, it's actually possible to do _most_ of what I want already - just using a migration. The per-field check constraint could/should also be implemented as a validator. Exclude constraints are another kettle of fish: as I mentioned before, it's not actually possible to validate these constraints without hitting the database, and even then that's not alway enough. You still need to handle IntegrityErrors if an update between when you checked and when you actually try to save causes a conflict.

Matt.

[Anssi Kääriäinen]

On Wednesday, April 2, 2014 6:58:22 AM UTC+3, schinckel wrote:

For that reason, I wouldn't be the fan of the exploded syntax you've given; I think I'd rather just be able to supply a list of strings for my constraints that are SQL themselves; there's no real benefit to abstraction here, and removing it doubtless removes a lot of code and bugs. Otherwise, I think I'd be +0.

Pushing on from there: it's not much work from doing "constraints = ('check start < finish',)" to just writing a RunSQL migration operation. The exploded syntax was more me working things through late at night anyway. And it seems like there's not really much else to do other than raw SQL for exclude constraints.

So, it's actually possible to do _most_ of what I want already - just using a migration. The per-field check constraint could/should also be implemented as a validator. Exclude constraints are another kettle of fish: as I mentioned before, it's not actually possible to validate these constraints without hitting the database, and even then that's not alway enough. You still need to handle IntegrityErrors if an update between when you checked and when you actually try to save causes a conflict.

I'd concentrate effort on allowing fields to define arbitrary extra DDL for migrations. Other use cases for extra migrations for fields are custom index types and ability to do "CREATE EXTENSION some_extension" (for example hstore) when needed. Optimally the latter would be best done with a dependency to another migration so that CREATE EXTENSION will be ran only once, but doing CREATE EXTENSION IF NOT EXISTS is good enough.

The basic idea for this is to allow fields to give extra migration steps. Probably this is best done with get_extra_migrations() method that return a list of extra migration operations (instances of MigrationOperation class). I am not exactly sure how each migration operation should look like, but at the least it must be able to produce the needed SQL (per backend) for the step. Also, I don't know the migrations portion of the ORM that well, so it might be that a list of extra migration operation instances per field isn't sensible.

If get_extra_migrations() was available, then doing CHECK constraints and custom index definitions should be a lot easier. If one wants to write a field with EXCLUDE constraint in their project (or as 3rd party app) that should be possible, too.

 - Anssi

[Andrew Godwin]

As I've said on other threads, fields can't define extra operations - they must do everything inside the SchemaEditor's add_field method (operations are not the only thing which calls the SchemaEditor, in theory). Ticket #22022 (https://code.djangoproject.com/ticket/22022) covers this - there'll be some kind of field method that SchemaEditor looks for for its extra SQL to run.

I agree, though, Anssi - if we have that in there then it's a lot easier.

Andrew

--

You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/2c47e14e-3a46-49c0-8d08-e181f80dd058%40googlegroups.com.

[Thejaswi Puthraya]

On Wednesday, April 2, 2014 4:09:37 AM UTC+5:30, simonb wrote:

Hi

Just FYI: back in 2007 GSOC there was a project to add constraints. The syntax was as follows:

The code is at https://github.com/theju/django-check-constraints/

I doubt it works as is after the SQL compiler changes. It worked well on Postgres and SQLite. MySQL doesn't seem to support check constraints as of date.