Django multi db routing does not work with multiple schemes

Question

I have django running on an oracle server. I need to use two schemes: one for the outdated database, the second for all django-related tables.

So these are my settings. DATA:

APPS_DB = 'apps' DATABASES = { 'default' : { 'ENGINE': 'django.db.backends.oracle' 'NAME': 'django', 'USER': 'django-tables', 'PASSWORD': '****', 'HOST': 'localhost', 'PORT': '1531', }, APPS_DB : { 'ENGINE': 'django.db.backends.oracle', 'NAME': 'django', 'USER': 'legacy-stuff', 'PASSWORD': '****', 'HOST': 'localhost', 'PORT': '1531', }, } 

I also defined a router:

 class MyRouter(object): """A router to control all database operations on models""" def __init__(self): aux = [] for app in settings.INSTALLED_APPS: if not app.endswith('myapp'): aux.append(app) self.djangoStuff = tuple(map(lambda x: x[x.rfind('.')+1:], aux)) def is_django_stuff(self, model): return model._meta.app_label in self.djangoStuff def db_for_read(self, model, **hints): "Point all django apps models to separate DB" logger.info("READ from " + model._meta.app_label) if self.is_django_stuff(model): logger.info("Will be directed to default DB") return None logger.info("Will be directed to legacy DB") return settings.APPS_DB def db_for_write(self, model, **hints): "Point all django apps models to separate DB" logger.info("WRITE") if self.is_django_stuff(model): return None return settings.APPS_DB def allow_relation(self, obj1, obj2, **hints): "Allow any relation" logger.info("ALLOW REL") return True def allow_syncdb(self, db, model): "Allow syncdb for all managed objects" logger.info("ALLOW SYNC") if db == 'default' and self.is_django_stuff(model): return True if db != 'default' and not self.is_django_stuff(model): return True return False 

Now I have a very simple model:

 class Poll(models.Model): question = models.CharField(max_length=200) user = models.ForeignKey(User) pub_date = models.DateTimeField('date published') 

I do two syncdbs:

 python manage.py syncdb python manage.py syndb --database apps 

Everything is going well. Then I create a polling object using the "python manage.py shell"

 superuser = User.objects.all()[0] p = Poll(question="foo", user = superuser, pub_date = datetime.now()) p.save() 

And I'm trying to extract the user from the survey:

  a = Poll.objects.all() b = len(a) b = a[0] c = b.artist 

I have enabled registration in the router, so I see that the last request will be sent to fix the database:

 READ from myapp Will be directed to apps DB READ from myapp Will be directed to apps DB READ from auth Will be directed to default DB 

I even see the actual SQL statement:

 (0.005) SELECT "AUTH_USER"."ID", "AUTH_USER"."USERNAME", "AUTH_USER"."FIRST_NAME", "AUTH_USER"."LAST_NAME", "AUTH_USER"."EMAIL", "AUTH_USER"."PASSWORD", "AUTH_USER"."IS_STAFF", "AUTH_USER"."IS_ACTIVE", "AUTH_USER"."IS_SUPERUSER", "AUTH_USER"."LAST_LOGIN", "AUTH_USER"."DATE_JOINED" FROM "AUTH_USER" WHERE "AUTH_USER"."ID" = :arg0 ; args=(1,) 

But I get an error message:

  File "<console>", line 1, in <module> File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/fields/related.py", line 350, in __get__ rel_obj = qs.get(**params) File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/query.py", line 361, in get num = len(clone) File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/query.py", line 85, in __len__ self._result_cache = list(self.iterator()) File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/query.py", line 291, in iterator for row in compiler.results_iter(): File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/sql/compiler.py", line 763, in results_iter for rows in self.execute_sql(MULTI): File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/models/sql/compiler.py", line 818, in execute_sql cursor.execute(sql, params) File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/backends/util.py", line 40, in execute return self.cursor.execute(sql, params) File "/usr/local/lib/python2.7/dist-packages/Django-1.4.1-py2.7.egg/django/db/backends/oracle/base.py", line 675, in execute return self.cursor.execute(query, self._param_generator(params)) DatabaseError: ORA-00942: table or view does not exist 

So my question is: what am I doing wrong?

Answer 1

Cross-database foreign keys are essentially invalid because Django models the “correct” relational database with referential integrity and cannot be applied at the database level if the models are stored in completely different physical stores.

In any case, for this reason, Django must assume that any objects exist in the same database as the one you originally retrieved. In your case, he got the Poll object from your old database, so he should look for your user (or artist or whatever) there.

For simple queries like this, it is very easy to work with, for example:

 poll = Poll.objects.all()[0] user_id = poll.user_id # _id after the name of your "Foreign Key" field - which cannot really be an FK user = User.objects.get(user_id) # This will be a new query and can use a different database, it will check the router 

For more complex queries (joins, etc.) you often find that you need to create lists or sets of identifiers and execute queries using a filter (id__in = your_list_of_ids).

Depending on the number of records to accomplish this, there may be penalties for performance or memory usage. (But in some cases, your requests will actually be much faster than the original connections, your application depends on everything.) You may need to split your identifier lists into batches or your requests may become too long, etc. Etc. But none of these problems are insurmountable.

When you are dealing with an identifier from another database, you need to provide referential integrity. Sometimes you need to configure batch processes to process data.

All this makes it sound wrong, but such a separation of problems, especially if you can limit dependencies in only one direction, may be the right rule.