- July 23, 2010
- mysql sequence uuid auto decrement auto increment primary key
- 2 (after 2 ratings)
Auto-incremented primary keys are the default in Django and they are supported natively in most databases but for anything more complex things are less trivial. DB sequences are not standard, may not be available and even if they are, they are typically limited to simple integer sequence generators. This snippet bypasses the problem by allowing arbitrary auto-generated keys in Python.
The implementation needs to determine whether an IntegrityError was raised because of a duplicate primary key. Unfortunately this information is not readily available, it can be found only by sniffing the DB-specific error code and string. The current version works for MySQL (5.1 at least); comments about how to determine this in other databases will be incorporated in the snippet.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
from django.db import IntegrityError class AutoPrimaryKeyMixin(object): '''A mixin class for ad-hoc auto-generated primary keys. This mixin allows a model to auto-generate primary key, regardless of whether the underlying database supports sequences natively. Moreover, the generators are not limited to simple integer sequences; arbitrary primary keys can be generated at the application level. Instead of locking the table, the primary key integrity constraint is maintained by attempting the save, catching the potential IntegrityError, re-generating the primary key and keep trying until it succeeds. The key generation callable is passed a ``num_attempts`` parameter so it may raise an exception if desired after a maximum number of failed attempts to break from the loop. .. note:: The current version works only in MySQL. ''' def save(self, *args, **kwargs): if self.pk != self._meta.pk.get_default(): return super(AutoPrimaryKeyMixin, self).save(*args, **kwargs) # here we just want to force_insert=True but since parameters can be # passed positionally and/or by name, we have to do the following acrobatics args = (True, False) + args[2:] kwargs.pop('force_insert', None) kwargs.pop('force_update', None) num_attempts = 0 while True: try: self.pk = self._compute_new_pk(num_attempts) return super(AutoPrimaryKeyMixin, self).save(*args, **kwargs) except IntegrityError, ex: # XXX: ugly MySQL-specific hack to determine if the # IntegrityError is due to a duplicate primary key. # Supporting more databases will require more similar hacks if not (ex == 1062 and ex.endswith("'PRIMARY'")): raise num_attempts += 1 def _compute_new_pk(self, num_attempts): raise NotImplementedError('abstract method') #======= examples =================================================== from django.db import models class AutoIncrementPrimaryKeyMixin(AutoPrimaryKeyMixin): '''Mixin for models with positive auto-increment primary key IDs. Each generated ID is 1 higher than the *current* maximum ID. Thus IDs generated by this class are not necessarily ever increasing, unlike native auto-increment IDs. ''' def _compute_new_pk(self, num_attempts): max_pk = models.Max(self._meta.pk.attname) aggregate = self.__class__._base_manager.aggregate return max(1, (aggregate(max_pk=max_pk)['max_pk'] or 0) + 1) class AutoDecrementPrimaryKeyMixin(AutoPrimaryKeyMixin): '''Mixin for models with negative auto-decrement primary key IDs. Each generated ID is 1 lower than the *current* minimum ID. Thus IDs generated by this class are not necessarily ever decreasing. ''' def _compute_new_pk(self, num_attempts): min_pk = models.Min(self._meta.pk.attname) aggregate = self.__class__._base_manager.aggregate return min(-1, (aggregate(min_pk=min_pk)['min_pk'] or 0) - 1) class UUIDModel(AutoPrimaryKeyMixin, models.Model): '''Abstract base class for models with UUID primary keys.''' uuid = models.CharField(max_length=36, primary_key=True) class Meta: abstract = True def _compute_new_pk(self, num_attempts): import uuid return str(uuid.uuid1()) class NegIDPerson(AutoDecrementPrimaryKeyMixin, models.Model): name = models.CharField(max_length=100) age = models.PositiveIntegerField() class UUIDPerson(UUIDModel): name = models.CharField(max_length=100) age = models.PositiveIntegerField()