On Feb 25, 2014, at 11:40 AM, Florian Rüchel <florian.ruec...@gmail.com> wrote:
> 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 the MetaData collection of strings is not available, as is the case here,
refer to the Column object directly:
language_id = Column(ForeignKey(Language.id))
The trick here is of course that your class organization works out such that
the “global” stuff is available at the import level by the time your “customer”
stuff is being declared.
> 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.
I think having separate MetaData objects does (which in this case is a side
effect of having separate Bases). There’s other ways to do it but in
particular splitting out MetaData instances along the “create” paths is often
useful.
As I implied, separate MetaData is not the same as separate Bases. You can
share the Base and all the class names for making relationships, while not the
MetaData object, like this:
Base = declarative_base()
class CustomerBase(Base):
__abstract__ = True
metadata = MetaData()
class GlobalBase(Base):
__abstract__ = True
metadata = MetaData()
this is from
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#abstract.
Another way to go is that when you do a create_all(), you can filter the tables
there, if you had one big MetaData collection:
def create_for_schema(metadata, schema):
metadata.create_all(tables=[t for t in metadata.tables.values() if t.schema
== schema])
I think my current work app is even mixing both techniques in different cases.
signature.asc
Description: Message signed with OpenPGP using GPGMail
