I have an application that uses Postgres Schemas to separate customers from
each other. As a result each customer has their own schema and there is one
additional schema that holds globally valid tables (such as the list of
customers). So while each customer schema is identical, the global schema
is completely different. Because of this, I have created two distinct
declarative_bases and so each have their own metadata. However, I still
want to be able to reference between those tables. In particular, I want to
be able for my customer table to reference foreign keys in the global
table, for example, I have a global list of languages and each user can
select theirs from it. Since the language table is in the metadata for the
global base but the user table is in the metadata for the customer base, I
cannot mix them as it stands.
When I customer logs in to the application, I set the database search path
to the customer schema and the global schema, so on the Postgres side
referencing works fine.
The first question is now: Does it even make sense to have separate bases?
In my opinion it does because then I can issue separate create_all
statements based on the schema I want to create them in. Otherwise I would
have to split them up manually. I think it is a good idea to keep a
separation here and distinct metadatas seems to be a good way for this.
So if I accept to have separate bases, is there even a way to reference
tables between them? Could I create some kind of a merge that looks in both
metadatas for the foreign keys?
Here is a small example to illustrate what I am doing:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Integer
from sqlalchemy.schema import ForeignKey, Column
from sqlalchemy import create_engine
CustomerBase = declarative_base()
GlobalBase = declarative_base()
class Customer(GlobalBase):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
class Language(GlobalBase):
__tablename__ = 'language'
id = Column(Integer, primary_key=True)
class User(CustomerBase):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
language_id = Column(ForeignKey('language.id'))
engine = create_engine('sqlite://')
CustomerBase.metadata.create_all(engine)
GlobalBase.metadata.create_all(engine)
The statement "language_id = Column(ForeignKey('language.id'))" fails,
because it uses a different base class than the language table. This is
basically what I am stuck with.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
