Django Composite Key

Allows to use Django with (legacy) databases that have composite multicolumn primary keys and multiple foreignkeys.

NOTE:

MASTER is not "STABLE"

Version 1.6 is in development

For django 1.4.x compatibility checkout the 1.4.x stable branch
For django 1.5.x compatibility checkout the 1.5.x stable branch

Author

Simone Federici s.federici@gmail.com

Running the Test Suite

Clone the django-compositekey repository (in case you havn't done this already):

$ git clone git://github.com/simone/django-compositekey.git

Checkout the Git Submodule containing Django:

$ cd django-compositekey
django-compositekey$ git submodule init
django-compositekey$ git submodule update

Update the Django clone to the version you want to test with:

django-compositekey$ cd django_src
django-compositekey/django_src$ git checkout stable/1.6
django-compositekey$ cd ..

Create a new virtualenv for the project and activate it.

Now install django-compositekey inside the virtualenv:

(django-compositekey) django-compositekey$ pip install --editable .

And install Django too:

(django-compositekey) django-compositekey$ cd django_src
(django-compositekey) django-compositekey/django_src$ pip install --editable .
(django-compositekey) django-compositekey$ cd ..

Use the Makefile to run the composite test suite:

(django-compositekey) django-compositekey$ make test-composite

Composite PK

from django.db import models
from compositekey import db

class Book(models.Model):
    id = db.MultiFieldPK("author", "name")
    name = models.CharField(max_length=100)
    author = models.CharField(max_length=100)

Inheritance

class BookReal(Book):
    text = models.CharField(max_length=100)

Many To Many

class Library(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book)

One To One + Composite PK related

class Biografy(models.Model):
    id = db.MultiFieldPK("book")
    book = models.OneToOneField(Book)
    text = models.CharField(max_length=100)

Abstract ForeignKey + field extensions syntax

class AbstractChapter(models.Model):
    id = db.MultiFieldPK("book", "num")
    book = models.ForeignKey(Book, to_field="id",
        fields_ext={
            "author": {"db_column" :"b_author", "name" : "_author"},
            "name"  : {"db_column" :"b_name"},
        }, related_name="chapter_set")
    num = models.PositiveSmallIntegerField()
    title = models.CharField(max_length=100)

    class Meta:
        abstract = True

ForeignKey Implementation

class Chapter(AbstractChapter):
    text = models.CharField(max_length=100)

SQLALL sqlite example

BEGIN;
CREATE TABLE "sample_book" (
    "name" varchar(100) NOT NULL,
    "author" varchar(100) NOT NULL,
    UNIQUE ("author", "name"),
    PRIMARY KEY ("author", "name")
)
;
CREATE TABLE "sample_bookreal" (
    "book_ptr_name" varchar(100) NOT NULL,
    "book_ptr_author" varchar(100) NOT NULL,
    "text" varchar(100) NOT NULL,
    UNIQUE ("book_ptr_author", "book_ptr_name"),
    FOREIGN KEY ("book_ptr_author", "book_ptr_name") REFERENCES sample_book ("author", "name")
)
;
CREATE TABLE "sample_library_books" (
    "id" serial NOT NULL PRIMARY KEY,
    "book_name" varchar(100) NOT NULL,
    "book_author" varchar(100) NOT NULL,
    "library_id" integer NOT NULL,
    UNIQUE ("library_id", "book_author", "book_name"),
    FOREIGN KEY ("book_author", "book_name") REFERENCES sample_book ("author", "name")
)
;
CREATE TABLE "sample_library" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(100) NOT NULL
)
;
ALTER TABLE "sample_library_books" ADD CONSTRAINT "library_id_refs_id_646b9dae" FOREIGN KEY ("library_id") REFERENCES "sample_library" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "sample_biografy" (
    "book_name" varchar(100) NOT NULL,
    "book_author" varchar(100) NOT NULL,
    "text" varchar(100) NOT NULL,
    UNIQUE ("book_author", "book_name"),
    PRIMARY KEY ("book_author", "book_name"),
    FOREIGN KEY ("book_author", "book_name") REFERENCES sample_book ("author", "name")
)
;
CREATE TABLE "sample_chapter" (
    "b_name" varchar(100) NOT NULL,
    "b_author" varchar(100) NOT NULL,
    "num" smallint CHECK ("num" >= 0) NOT NULL,
    "title" varchar(100) NOT NULL,
    "text" varchar(100) NOT NULL,
    UNIQUE ("b_author", "b_name", "num"),
    PRIMARY KEY ("b_author", "b_name", "num"),
    FOREIGN KEY ("b_author", "b_name") REFERENCES sample_book ("author", "name")
)
;
CREATE TABLE "sample_employee" (
    "code" integer NOT NULL,
    "first_name" varchar(20) NOT NULL,
    "last_name" varchar(20) NOT NULL,
    UNIQUE ("code", "last_name"),
    PRIMARY KEY ("code", "last_name")
)
;
CREATE TABLE "sample_business_employees" (
    "id" serial NOT NULL PRIMARY KEY,
    "employee_code" integer NOT NULL,
    "employee_last_name" varchar(20) NOT NULL,
    "business_id" varchar(20) NOT NULL,
    UNIQUE ("business_id", "employee_code", "employee_last_name"),
    FOREIGN KEY ("employee_code", "employee_last_name") REFERENCES sample_employee ("code", "last_name")
)
;
CREATE TABLE "sample_business" (
    "name" varchar(20) NOT NULL PRIMARY KEY
)
;
ALTER TABLE "sample_business_employees" ADD CONSTRAINT "business_id_refs_name_2205e04d" FOREIGN KEY ("business_id") REFERENCES "sample_business" ("name") DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE "sample_author_friends" (
    "id" serial NOT NULL PRIMARY KEY,
    "from_author_name" varchar(100) NOT NULL,
    "to_author_name" varchar(100) NOT NULL,
    "from_author_age" integer NOT NULL,
    "to_author_age" integer NOT NULL,
    UNIQUE ("from_author_name", "from_author_age", "to_author_name", "to_author_age")
)
;
CREATE TABLE "sample_author" (
    "name" varchar(100) NOT NULL,
    "age" integer NOT NULL,
    UNIQUE ("name", "age"),
    PRIMARY KEY ("name", "age")
)
;
ALTER TABLE "sample_author_friends" ADD CONSTRAINT "from_author_name_from_author_age_refs_name_age_7b9b87e7" FOREIGN KEY ("from_author_name", "from_author_age") REFERENCES "sample_author" ("name", "age") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "sample_author_friends" ADD CONSTRAINT "to_author_name_to_author_age_refs_name_age_7b9b87e7" FOREIGN KEY ("to_author_name", "to_author_age") REFERENCES "sample_author" ("name", "age") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "sample_book_name" ON "sample_book" ("name");
CREATE INDEX "sample_book_name_like" ON "sample_book" ("name" varchar_pattern_ops);
CREATE INDEX "sample_book_author" ON "sample_book" ("author");
CREATE INDEX "sample_book_author_like" ON "sample_book" ("author" varchar_pattern_ops);
CREATE INDEX "sample_bookreal_book_ptr_name" ON "sample_bookreal" ("book_ptr_name");
CREATE INDEX "sample_bookreal_book_ptr_name_like" ON "sample_bookreal" ("book_ptr_name" varchar_pattern_ops);
CREATE INDEX "sample_bookreal_book_ptr_author" ON "sample_bookreal" ("book_ptr_author");
CREATE INDEX "sample_bookreal_book_ptr_author_like" ON "sample_bookreal" ("book_ptr_author" varchar_pattern_ops);
CREATE INDEX "sample_biografy_book_name" ON "sample_biografy" ("book_name");
CREATE INDEX "sample_biografy_book_name_like" ON "sample_biografy" ("book_name" varchar_pattern_ops);
CREATE INDEX "sample_biografy_book_author" ON "sample_biografy" ("book_author");
CREATE INDEX "sample_biografy_book_author_like" ON "sample_biografy" ("book_author" varchar_pattern_ops);
CREATE INDEX "sample_chapter_b_name" ON "sample_chapter" ("b_name");
CREATE INDEX "sample_chapter_b_name_like" ON "sample_chapter" ("b_name" varchar_pattern_ops);
CREATE INDEX "sample_chapter_b_author" ON "sample_chapter" ("b_author");
CREATE INDEX "sample_chapter_b_author_like" ON "sample_chapter" ("b_author" varchar_pattern_ops);
CREATE INDEX "sample_chapter_num" ON "sample_chapter" ("num");
CREATE INDEX "sample_employee_code" ON "sample_employee" ("code");
CREATE INDEX "sample_employee_last_name" ON "sample_employee" ("last_name");
CREATE INDEX "sample_employee_last_name_like" ON "sample_employee" ("last_name" varchar_pattern_ops);
CREATE INDEX "sample_author_name" ON "sample_author" ("name");
CREATE INDEX "sample_author_name_like" ON "sample_author" ("name" varchar_pattern_ops);
CREATE INDEX "sample_author_age" ON "sample_author" ("age");
COMMIT;

EUROPYTHON 2012 Support for Future API of CompositeField

Composite PK

from django.db import models
from compositekey import __future__

class Book(models.Model):
    name = models.CharField(max_length=100)
    author = models.CharField(max_length=100)
    id = models.CompositeField(author, name, primary_key=True)

Inheritance

class BookReal(Book):
    text = models.CharField(max_length=100)

Many To Many

class Library(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book)

One To One + Composite PK related

class Biografy(models.Model):
    book = models.OneToOneField(Book)
    text = models.CharField(max_length=100)
    id = models.ComositeField(book, primary_key=True)

Abstract ForeignKey + field extensions syntax

class AbstractChapter(models.Model):
    author = models.CharField(max_length=100, db_column="b1_author", name="_author")
    name = models.CharField(max_length=100, db_column="b1_name", name="_name")
    book = models.ForeignKey(Book, fields=(author, name), related_name="chapter_set")
    num = models.PositiveSmallIntegerField()
    title = models.CharField(max_length=100)
    id = models.CompositeField(book, num, primary_key=True)

    class Meta:
        abstract = True

Customize the ManyToMany Intermediate Model/Table

class Library(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book, through="BookLibraryM2M")

    def __unicode__(self):
        return u"Library: %s" % unicode(self.name)

class BookLibraryM2M(models.Model):
    book = models.ForeignKey(Book)
    library = models.ForeignKey(Library)
    id = models.CompositeField(book, library, primary_key=True)

    class Meta:
        auto_created = Library

pzg250/django-compositekey

Django Composite Key

NOTE:

Version 1.6 is in development

Author

Links:

Running the Test Suite

Composite PK

Inheritance

Many To Many

One To One + Composite PK related

Abstract ForeignKey + field extensions syntax

ForeignKey Implementation

SQLALL sqlite example

EUROPYTHON 2012 Support for Future API of CompositeField

Composite PK

Inheritance

Many To Many

One To One + Composite PK related

Abstract ForeignKey + field extensions syntax

Customize the ManyToMany Intermediate Model/Table