#373: Add support for multiple-column primary keys
------------------------+---------------------------------------------------
Reporter: jacob | Owner: dcramer
Status: assigned | Component: Metasystem
Version: | Resolution:
Keywords: database | Stage: Accepted
Has_patch: 0 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
------------------------+---------------------------------------------------
Comment (by [EMAIL PROTECTED]):
Sorry if this isn't the right place to post this, but I'm a noob... I've
only used Python and Django for a week...
> Currently, you can "fake" it by declaring one of the keys to be primary
in Django and adding a unique constraint to the model. You'd have to do
without the auto-generated SQL that Django gives you, but if you're smart
enough to know how and why to use multiple primary keys, you can probably
write the schema by hand. Still, this is less than ideal.
>
No you really can't "fake" it... You can't even with custom sql do it.
The problem is in the save function, because when you save an object it
overrides all values with the declared primary key.
An example:
{{{
class book():
shelf = IntegerField(primary_key=True)
level = IntegerField()
index = IntegerField()
class Meta:
unique_together = ('shelf', 'level', 'index')
# don't mind the following line right now
primary = ('shelf', 'level', 'index')
}}}
Currently you can only have one book per shelf with the current django
code... (I actually use 0.96, but I don't think it has changed...)
That was the problem, now to the solution! (Note, as I said, I'm a noob,
and I just hacked an afternoon and came up with this)
It needs to have a field called primary specified as shown in the above
model.
{{{
import django.db.models.manipulators
import django.db.models.manager
from django.core import validators
from django.core.exceptions import ObjectDoesNotExist
from django.db.models.fields import AutoField, ImageField,
FieldDoesNotExist
from django.db.models.fields.related import OneToOneRel, ManyToOneRel
from django.db.models.query import delete_objects
from django.db.models.options import Options, AdminOptions
from django.db import connection, backend, transaction, models
from django.db.models import signals
from django.db.models.loading import register_models, get_model
from django.dispatch import dispatcher
from django.utils.datastructures import SortedDict
from django.utils.functional import curry
from django.conf import settings
from itertools import izip
import types
import sys
import os
class Model(models.Model):
def save(self):
dispatcher.send(signal=signals.pre_save, sender=self.__class__,
instance=self)
non_pks = [f for f in self._meta.fields if not (f.primary_key or
(f.name in self.primary))]
otpk = [f for f in self._meta.fields if ((not f.primary_key) and
(f.name in self.primary))]
where_and_clause = ''
for f in otpk:
where_and_clause = where_and_clause.join(' AND %s=%s ' % \
(backend.quote_name(f.attname),
str(getattr(self, f.attname))))
# TODO: the last value in the above % thingy is probbably
unsafe...
cursor = connection.cursor()
# First, try an UPDATE. If that doesn't update anything, do an
INSERT.
pk_val = self._get_pk_val()
pk_set = bool(pk_val)
record_exists = True
if pk_set:
# Determine whether a record with the primary key already
exists.
cursor.execute("SELECT 1 FROM %s WHERE %s=%%s %s LIMIT 1" % \
(backend.quote_name(self._meta.db_table),
backend.quote_name(self._meta.pk.column),
where_and_clause), [pk_val])
# If it does already exist, do an UPDATE.
if cursor.fetchone():
db_values = [f.get_db_prep_save(f.pre_save(self, False))
for f in non_pks]
if db_values:
cursor.execute("UPDATE %s SET %s WHERE %s=%%s %s" % \
(backend.quote_name(self._meta.db_table),
','.join(['%s=%%s' % backend.quote_name(f.column)
for f in non_pks]),
backend.quote_name(self._meta.pk.column),
where_and_clause),
db_values + [pk_val])
else:
record_exists = False
if not pk_set or not record_exists:
field_names = [backend.quote_name(f.column) for f in
self._meta.fields if not isinstance(f, AutoField)]
db_values = [f.get_db_prep_save(f.pre_save(self, True)) for f
in self._meta.fields if not isinstance(f, AutoField)]
# If the PK has been manually set, respect that.
if pk_set:
field_names += [f.column for f in self._meta.fields if
isinstance(f, AutoField)]
db_values += [f.get_db_prep_save(f.pre_save(self, True))
for f in self._meta.fields if isinstance(f, AutoField)]
placeholders = ['%s'] * len(field_names)
if self._meta.order_with_respect_to:
field_names.append(backend.quote_name('_order'))
# TODO: This assumes the database supports subqueries.
placeholders.append('(SELECT COUNT(*) FROM %s WHERE %s =
%%s %s)' % \
(backend.quote_name(self._meta.db_table),
backend.quote_name(self._meta.order_with_respect_to.column),
where_and_clause))
db_values.append(getattr(self,
self._meta.order_with_respect_to.attname))
if db_values:
cursor.execute("INSERT INTO %s (%s) VALUES (%s)" % \
(backend.quote_name(self._meta.db_table),
','.join(field_names),
','.join(placeholders)), db_values)
else:
# Create a new record with defaults for everything.
cursor.execute("INSERT INTO %s (%s) VALUES (%s)" %
(backend.quote_name(self._meta.db_table),
backend.quote_name(self._meta.pk.column),
backend.get_pk_default_value()))
if self._meta.has_auto_field and not pk_set:
setattr(self, self._meta.pk.attname,
backend.get_last_insert_id(cursor, self._meta.db_table,
self._meta.pk.column))
transaction.commit_unless_managed()
# Run any post-save hooks.
dispatcher.send(signal=signals.post_save, sender=self.__class__,
instance=self)
}}}
As some will see, this is a copy and paste of the original save function,
but with additional constraints in the where clause everywhere.
Just import and inherit from this class where you have multiple primary
keys and everything will work.
Also you need some sql like this: (for postgres 8.2)
{{{
ALTER TABLE app_book DROP CONSTRAINT app_book_pkey;
ALTER TABLE app_book ADD CONSTRAINT app_book_pkey PRIMARY KEY ("shelf",
"level", "index")
}}}
This is a fix that works now for the impatient (like me), but I would
really have it work natively, without this hackish code, so keep the good
work up dcramer!
Happy programming wishes Niklas Ulvinge
--
Ticket URL: <http://code.djangoproject.com/ticket/373#comment:21>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
