Code

Opened 5 years ago

Closed 4 years ago

Last modified 8 months ago

#12540 closed Uncategorized (fixed)

Add support for cross-database foreign keys and m2m

Reported by: russellm Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2-alpha
Severity: Normal Keywords:
Cc: zbraniecki Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Django's multidb support currently prevents foreign keys from referencing foreign databases. This means the obvious use case of "put User objects in the auth database" et al are not possible.

The full solution requires at least the following:

  • Modifying ForeignKey to relax foreign key constraints if it is known that the foreign key will be on another database
  • Providing hooks to allow FK and M2M descriptors to look up the right database to use

There has been some initial discussion on django users about this subject.

Attachments (2)

t12540-r12262.1.diff (46.2 KB) - added by russellm 4 years ago.
First pass at improved cross-database support.
t12540-r12262.2.diff (53.4 KB) - added by russellm 4 years ago.
Version 2 of a fix for database allocation problems. This time with a public DB Router API.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 5 years ago by zbraniecki

  • Cc zbraniecki added

comment:3 Changed 5 years ago by zbraniecki

I suggest also adding config parameters that would bind apps in a project to a given database. Something like:

settings.py
DATABASES = {

'default': {

'NAME': 'db1',
'ENGINE': 'django.db.backends.mysql',
'USER': 'root',
'PASSWORD': ,

},
'db2': {

'NAME': 'db2',
'ENGINE': 'django.db.backends.mysql',
'USER': 'root',
'PASSWORD': ,

}

}

INSTALLED_APPS = (

{'NAME':'app1', 'DEFAULT_DB': 'db1'},
{'NAME':'app2', 'DEFAULT_DB': 'db2'},

)

this would allow models to be able to discover that the ForeignKey referenced in app1.models is using db2. It would also allow for models in app1 to on save() add required records to models from app2 without having to explicitly use using() on them (which would constrain the app relations)

Does it sounds reasonable? Is it a material for a separate ticket?

comment:4 Changed 4 years ago by russellm

@zbraniecki: This is not a new idea - it has been made by several people over the history of multi-db development. Read up on the archives to see why it hasn't been added.

Changed 4 years ago by russellm

First pass at improved cross-database support.

comment:5 Changed 4 years ago by russellm

The patch I've just uploaded is a first pass at implementing a fix for cross-database support. Feedback is welcome.

Changed 4 years ago by russellm

Version 2 of a fix for database allocation problems. This time with a public DB Router API.

comment:6 Changed 4 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

(In [12272]) Fixed #12540, #12541 -- Added database routers, allowing for configurable database use behavior in a multi-db setup, and improved error checking for cross-database joins.

comment:7 Changed 8 months ago by DrMeers

  • Easy pickings unset
  • Severity set to Normal
  • Type set to Uncategorized
  • UI/UX unset

The title/description of this ticket haven't actually been "fixed", have they? -- "Django doesn’t currently provide any support for foreign key or many-to-many relationships spanning multiple databases" -- https://docs.djangoproject.com/en/dev/topics/db/multi-db/#limitations-of-multiple-databases

Is the addition of such support still being considered, and tracked elsewhere?

comment:8 Changed 8 months ago by russellm

I'm not aware of any plans to change the status quo here. Cross database referential integrity is a Hard Problem™. "Fixed" is perhaps misleading in this case, but it's as fixed as it's likely to get. The "fix" in this case was adding configurable routers, which lets you have a bit more control over where models are synchronised. That doesn't make cross database joins possible, but it does let you actively prevent them.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.