I'm not sure what sort of answer this question will lead to, but here goes. I know foreignkeys on separate databases isn't supposed to work, according to the django docs, but I got it to work. First off, I'm using an old release (1, 2, 0, 'alpha', 1), one of the earlier multidb releases. Because I hacked on it for so long and so much to make it work for my needs, I've literally ended up with a branch of my own to maintain. As such, I port features in as I need them from various patches to the trunk. It sucks, but anyway, I came across a problem where I wanted to use django-pyodbc to connect to a mssql server on another machine. This machine was not my default router, in fact, it was the third database I moved into my dbrouter. Further complicating my design, (lack thereof) I needed to use a field on a table in this mssql database as a foreign key to my primary database (mysql).
I don't want to be lectured on my use of foreign keys like this, I know. At first it seemed ok, and then I found a nugget on the django doc pages that said foreign keys to external databases are not allowed/ won't work. (http://docs.djangoproject.com/en/dev/topics/db/multi-db/ #cross-database-relations) Since I ported/added things I needed in the past, I didn't let this deter me. I pressed on. After a lot of experimenting, I discovered a very strange behavior. Take this for example: class mssqlTable(Model): # this is one of three non-default databases in my db router # this would get selected explictly using db_for_read/write display = models.CharField(max_length=128) def __unicode__(self): return "mssqlTable:%s"%self.id class mysqlTable(Model): # this is in my default database in my db router assignee = models.ForeignKey(mssqlTable) display = models.CharField(max_length=128) def __unicode__(self): return "mysqlTable:%s"%self.id Now the weirdness, and I think Model.objects.defer has it's hands in this somewhere, but didn't look too hard there, other than watch it try to defer in pdb. launch the shell: python manage.py shell import the models... x=mysqlTable.objects.all() As long as I don't try to do: x.assignee It works great. As soon as i try to hit that deferred field though, it pukes with a horrible error that comes up from django-pyodbc. I can see x.assignee_id though, that is populated with an int value of the FK from the external DB. However, if I do: x=mysqlTable.objects.all()[0] x.assignee it works great! I never did find out exactly what that slice does, but I later surmised that the slice is causing the connection.alias to hold the right handle to the right database. However, when defer "undefers" or whatever the process is that loads those fields is, fires later, it doesn't hold the right connection.alias all the way through. In fact, it does hold the right alias for most of the process, it just doesn't pass it in one method. The rest of the failure occurs in django-pyodbc. I found the following in the latest svn, and for all practical purposes, it's exactly the same as mine. The call to connection.ops.field_cast_sql doesn't pass connection through. It only passes db_type, which doesn't hold the connection instance that is passed in to this method. def sql_for_columns(self, data, qn, connection): """ Returns the SQL fragment used for the left-hand side of a column constraint (for example, the "T1.foo" portion in the clause "WHERE ... T1.foo = 6"). """ table_alias, name, db_type = data if table_alias: lhs = '%s.%s' % (qn(table_alias), qn(name)) else: lhs = qn(name) return connection.ops.field_cast_sql(db_type) % lhs sql_for_columns actually calls this: PYTHON/site-packages/sql_server/pyodbc/operations.py:63 def field_cast_sql(self, db_type, connection=None): """ Given a column type (e.g. 'BLOB', 'VARCHAR'), returns the SQL necessary to cast it before using it in a WHERE statement. Note that the resulting string should contain a '%s' placeholder for the column being searched against. """ if self.sql_server_ver < 2005 and db_type and db_type.lower() == 'ntext': return 'CAST(%s as nvarchar)' return '%s' You can see that field_cast_sql can accept connection, but it never gets it passed. It in turns, calls a getter (self.sql_server_ver) on self and that looks like this: (line 14) def _get_sql_server_ver(self, connection=None): """ Returns the version of the SQL Server in use: """ if self._ss_ver is not None: return self._ss_ver else: if connection: cur = connection.cursor() else: from django.db import connection cur = connection.cursor() cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)") ver_code = int(cur.fetchone()[0].split('.')[0]) if ver_code >= 10: self._ss_ver = 2008 elif ver_code == 9: self._ss_ver = 2005 else: self._ss_ver = 2000 return self._ss_ver sql_server_ver = property(_get_sql_server_ver) Unfortunately, the only way i know to work around this sort of call, is to add a setter in to this property. Even though, the actual method it calls, _get_sql_server_ver can accept connection yet again, it doesn't get that passed in. However, if you do pass it in, the cur instance is on the right database. The error i found was that it would enter the else block and create a new cursor to the db, and select the default database instead of the external/non-default one i needed to use. So, the failure to pass connection starts first in Django's where.py file and then there are two other errors in the django-pyodbc source. If I modify all of the above functions to pass connection, the issues I experienced go away and I can use the mssql database as i wanted to, as a foreign-key to another database on another machine. My where.py ends up like this: def sql_for_columns(self, data, qn, connection): """ Returns the SQL fragment used for the left-hand side of a column constraint (for example, the "T1.foo" portion in the clause "WHERE ... T1.foo = 6"). """ table_alias, name, db_type = data if table_alias: lhs = '%s.%s' % (qn(table_alias), qn(name)) else: lhs = qn(name) try: return connection.ops.field_cast_sql(db_type,connection) % lhs except TypeError,e: return connection.ops.field_cast_sql(db_type) % lhs The try/except is a hack for me. anything that isn't in this mssql database will throw a type error because it doesn't use the django- odbc source. I don't think the real cause is in django though, but this is the only way i found to work around it. thoughts? If it's anything at all, I found it interesting that I could get this working without a lot of effort. I've loaded over 1200 records in the new tables i created that depend on it and haven't had a problem. Admin works fine too, for the most part. some things like the list_filter are giving me issues that i'll have to fix. Nevertheless, it's cool and wanted to throw this out there for people to tear into...or ignore. Thanks for all the hard work! regards, -j -- You received this message because you are subscribed to the Google Groups "Django developers" 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-developers?hl=en.
