Desired: Field behavior like AutoField + unique_with

[Matt Cooper]

I'm building a multi-tenant application with a data model that simplifies to:

class Tenant(models.Model):

    name = models.CharField(max_length=50)

class Widget(models.Model):

    owner = models.ForeignKey(Tenant)

    sequence_id = <question here>

I want Widget.sequence_id to work like an AutoField (that is, automatically give me something unique and generally incrementing by 1) but scoped to a single Widget.owner. So for instance, when Tenant A creates 3 widgets, they would get sequence_ids 1, 2, and 3. Then Tenant B comes along and creates 2 widgets; those widgets get sequence_ids 1 and 2. The tenant/user gets no control over the number, but it's something she'll see in the UI. AutoNumber is out because from the perspective of a single tenant, they should have sequential widget IDs. GUIDs/UUIDs are out because it needs to be human-readable.

I looked through docs on AutoField and unique_together. I turned StackOverflow upside down and only found this, which isn't quite what I want. I didn't see anything on djangopackages.com that would solve my problem, though I could have missed it.

Basically, has anyone done something like this before and have a suggested pattern?

Thanks!

~matt

[Jani Tiainen]

I've done something similiar.

Only way to do that is that you need to upkeep that tenant sequence_id manually,

So doing something like:

class Tenant(models.Model):
name = models.CharField(max_length=50)

sequence_value = models.IntegerField()

and in a code:

tenant = Tenant.object.get(id=tenant_id).select_for_update() # Pessimistic locking
tenant.sequence_value = tenant.sequence_value + 1
tenant.save()

widget = Widget(owner=tenant,sequence_value=tenant.sequence_value)
widget.save()

That should do the trick. Another option could be using table triggers to automate
that within a database table trigger(s).

On Mon, 12 Jan 2015 21:26:54 -0800 (PST)
Matt Cooper <vtbas...@gmail.com> wrote:

> I'm building a multi-tenant application with a data model that simplifies
> to:
>
> class Tenant(models.Model):
> name = models.CharField(max_length=50)
>
> class Widget(models.Model):
> owner = models.ForeignKey(Tenant)
> sequence_id = <question here>
>
>
> I want Widget.sequence_id to work like an AutoField (that is, automatically
> give me something unique and generally incrementing by 1) but scoped to a
> single Widget.owner. So for instance, when Tenant A creates 3 widgets, they
> would get sequence_ids 1, 2, and 3. Then Tenant B comes along and creates 2
> widgets; those widgets get sequence_ids 1 and 2. The tenant/user gets no
> control over the number, but it's something she'll see in the UI.
> AutoNumber is out because from the perspective of a single tenant, they
> should have sequential widget IDs. GUIDs/UUIDs are out because it needs to
> be human-readable.
>
> I looked through docs on AutoField and unique_together. I turned
> StackOverflow upside down and only found this

> <http://stackoverflow.com/questions/18072586/django-autofield-increment>,

> which isn't quite what I want. I didn't see anything on djangopackages.com
> that would solve my problem, though I could have missed it.
>
> Basically, has anyone done something like this before and have a suggested
> pattern?
>
> Thanks!
> ~matt
>

> --
> You received this message because you are subscribed to the Google Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/37fd32e7-28dd-4c10-8451-b0d705a3e52e%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Matt Cooper]

Thanks Jani, good to hear someone has gone down this road before. Since you did it in code, is there a reason you kept a sequence value on Tenant rather than using Widget.objects.aggregate(Max(Widget.sequence_value)) ? I ask because for my app, Widget is actually just one of several cases where I need this pattern. If I could keep all the book-keeping on the model with the constraint, that seems cleaner to me. But, of course, I'm not willing to pay a huge performance or corruption penalty just for vague ideological purity :)

Thanks again!

~matt

[Jani Tiainen]

Because sequence number was running number per "tenant" (in my case it
was invoice type) that started from zero (0) at the beginning of the
every year.

You could use max+1 as well but you should be aware that there is a
slim chance that there is race condition and you may end up having two
entities with the same max number - that though would raise error when
trying to save into database.

By having explicitly pessimistic locking (select_for_update) for a row
with tenant + sequence value you can guarantee that no other request
can change the value while you're reading it.

To make that easier you can create custom manager that can retrieve
updated sequence values automagically, so your call would look like
something like:

Tenant.sequence.get_value(tenant, sequence)


On Tue, 13 Jan 2015 06:37:21 -0800 (PST)

> > an email to django-users...@googlegroups.com <javascript:>.

> > > To post to this group, send email to django...@googlegroups.com

> > <javascript:>.

> > > Visit this group at http://groups.google.com/group/django-users.
> > > To view this discussion on the web visit
> > https://groups.google.com/d/msgid/django-users/37fd32e7-28dd-4c10-8451-b0d705a3e52e%40googlegroups.com.
> >
> > > For more options, visit https://groups.google.com/d/optout.
> >
>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group. To unsubscribe from this group and stop
> receiving emails from it, send an email to
> django-users...@googlegroups.com. To post to this group,
> send email to django...@googlegroups.com. Visit this group at
> http://groups.google.com/group/django-users. To view this discussion
> on the web visit

> https://groups.google.com/d/msgid/django-users/1ab4b16d-df97-4412-9622-22cf3b344f80%40googlegroups.com.

[Matt Cooper]

Got it - thank you! This community is great :)