Composite constraints

[dantownsend]

Based off this: https://github.com/piccolo-orm/piccolo/pull/984

That PR is really great, but I've added the following:

• Docs
• Check constraints
• I changed the module to piccolo.constraints from piccolo.constraint, because we support unique and check constraints, so plural made a bit more sense to me.

Remaining tasks:

• [ ] Update / fix the tests

In the original PR, constraints were added like this:

from piccolo.constraints import UniqueConstraint

class Album(Table):
    name = Varchar()
    band = ForeignKey(Band)
    unique_name_band = UniqueConstraint(['name', 'band'])

Which I do quite like.

In this PR I tried this:

class Album(Table):
    name = Varchar()
    band = ForeignKey(Band)

Album.add_unique_constraint(Album.name, Album.band)

My rational is:

• Piccolo tends to use column references instead of strings where possible, because linters can detect errors.
• The user doesn't have to name their constraint if they don't want to. They can do this though: Album.add_unique_constraint(Album.name, Album.band, name='unique_name_band'). If no name is specified we generate a sensible one.

We could support both syntaxes - it wouldn't be that hard, but it can be confusing having multiple ways to do the same thing.

What do people think?

[Skelmis]

Thinking out loud.

I like the use of columns instead of strings, but just thinking it may be worth considering getting it on the table itself somehow. I figure something like Django's Meta model which may look like the following:

class Album(Table):
    name = Varchar()
    band = ForeignKey(Band)
  
    class Meta:
        unique_together = [Album.name, Album.band]

This could also be expanded for other configs such as

class Album(Table):
    name = Varchar()
    band = ForeignKey(Band)
  
    class Meta:
        help_text = "A music album!"
        unique_together = [Album.name, Album.band]

The reasoning behind the thought is just based more so on visual grouping and ensuring table related configs remain on the table itself. Note that I am aware TableMeta handles it all internally regardless, I was more so just thinking about developer UX.

---

Although ignoring the above, I like the proposed syntax and rational. I think your updated approach and method makes more sense then as table attributes. I'd be happy to use this feature in my code bases as proposed

[dantownsend]

@Skelmis Thanks, that's great input.

I agree that having it on the table class somehow is nicer. Even if there's just a classmethod called add_constraints which you override, and it gets called automatically when the table class is ready. I'll experiment with it a bit more.

[dantownsend]

@sinisaos Thanks. I've been thinking about it this morning. I'm leaning towards keeping both approaches - being able to define the constraints inline in the table if you're happy just using strings, and the new API if you want to use column references. Will have a think today.

[sinisaos]

@dantownsend I think you should only use one option (one api) to create and drop unique constraints because having two ways to do the same thing could be confusing and unnecessary. That's just my opinion. You can do what you think is best.

[dantownsend]

I experimented with a bunch of different stuff, but what I ended up with was:

class Manager(Table):
    name = Varchar()
    date_of_birth = Date()

    min_date_of_birth = Check(
        date_of_birth >= datetime.date(year=1920, month=1, day=1)
    )

So basically what's in the original PR, except it can use column references instead of strings.

Pretty much everything else I tried didn't work well with mixins. With the above approach, we can do this:

class DateOfBirthMixin:
    date_of_birth = Date()

    min_date_of_birth = Check(
        date_of_birth >= datetime.date(year=1920, month=1, day=1)
    )


class Manager(DateOfBirthMixin, Table):
    name = Varchar()

I'm pretty much finished with this now - I just need to fix up some broken tests.

[Skelmis]

~My only comment would be possibly to consider changing Check to Constraint so it's inline with SQL + folder and a bit clearer that it happens on the database layer otherwise looks nice and I look forward to using it~

I was wrong, ignore my comment

[Skelmis]

@dantownsend any updates on this? I think if you do the tests and merge conflicts we can merge / mark ready for review? I'm happy with it once checks pass