Re: django multi db routing doesnt work wich multiple schemas

[Tom Evans]

On Fri, Oct 12, 2012 at 4:54 PM, mnowotka <mmm...@gmail.com> wrote:
> I have django running on oracle backend. I need to use two schemas - one for
> a legacy DB, second one for all django related tables.
>
> So this is my settings.DATABASES:
>
> 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 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 very simple model:
>
> class Poll(models.Model):
> question = models.CharField(max_length=200)
> user = models.ForeignKey(User)
> pub_date = models.DateTimeField('date published')
>
> I make two syncdbs:
>
> python manage.py syncdb
> python manage.py syndb --database apps
>
> Everything goes fine. Then I create poll object using 'python manage.py
> shell'
>
> superuser = User.objects.all()[0]
> p = Poll(question="foo", user = superuser, pub_date = datetime.now())
> p.save()
>
> And I try to retrieve user from poll:
>
> a = Poll.objects.all()
> b = len(a)
> b = a[0]
> c = b.artist

You can't have relationships between models stored in different databases.

What happens when you define such an invalid relationship and follow
it, Django will use the database specified in b._state.db, and not
what the router determines.

It is an impossibility to have relationships that span different
databases - ie, this is not a Django limitation - so this is just
describing what would happen when you attempt to do something invalid.

Cheers

Tom

[Michał Nowotka]

First of all the error is not from django but from database.
Secondly I'm not using different databases but different schemas so
relations are possible.
The error has nothing to do with foreign key or any relation.
It just looks like django is ignoring router suggestion and directs
sql query to wrong DB.

[Tomas Ehrlich]

Hi Michał,
is "schema" the same as thing as "tablespace"? If so, you should define them using
db_tablespace meta keyword.
https://docs.djangoproject.com/en/1.4/topics/db/tablespaces/

DATABASES creates new database connection and it's really not
possible to make relations between databases.

Cheers,
Tom

Dne Fri, 12 Oct 2012 17:19:42 +0100
Michał Nowotka <mmm...@gmail.com> napsal(a):

S pozdravem
Tomáš Ehrlich

Email: tomas....@gmail.com
Tel: +420 608 219 889
Jabber: elv...@jabber.cz

"Půjdu kamkoliv, pokud je to kupředu." - J. London

[Tomáš Ehrlich]

Sorry, I've just found that schema != tablespace, so my last response is irelevant...

Cheers,

 Tom

Dne pátek, 12. října 2012 19:26:53 UTC+2 Tomáš Ehrlich napsal(a):

[Michał Nowotka]

Unfortunately not exactly.
Schema in Oracle is a synonym of user so you need name and password.
That's why it doesn't fit into tablespaces solution you pointed.
There is open ticket about support for multiple schemas but it's still
unfinished.

This is why I was looking for solution that would work now.
Having said that I really don't understand two things:

- why I was able to create two objects form different db connections
without any error but I can't do the opposite so retrieve related
object from original one?
- what's the purpose of allow_relation method in the router if despite
it suggestions I can bypass this limitation?

[Michał Nowotka]

Anyway, look at the query that needs to be executed. This doesn't
contain any joins. The only problem is that this perfectly correct
query is send to wrong schema although the router suggests a good one.
I don't know why, is there anyone who can explain this to me?