On Jan 27, 2013, at 4:42 PM, Scott Sadler wrote:
> Hi, I wanted to post here since I had to hack around an issue in order to use
> SQLAlchemy with a dynamic Postgres schema, and I wanted to see if there's a
> way I can get the workarounds out of my code.
>
> In my app I'm separating the data by dynamically managing the schemas. The
> basic way this is achieved is:
>
> new_meta = MetaData(schema='newschema')
> for table in old_meta.tables.items():
> table.tometadata(new_meta)
> new_meta.create_all()
>
> This worked nicely until I tried to use postgresql.ENUM type. The ENUM is a
> first class schema object so it needs to be created separately, but it does
> this by registering an event on the first table it's bound to (doesnt
> transfer to copies) and it doesn't inherit the schema of the table it's bound
> to
the ENUM type accepts an argument "schema" for this purpose, which is part of
the base sqlalchemy.types.Enum contract:
Table('mytable', metadata, Column('data', postgresql.ENUM(name='myenum',
schema='someschema')), schema='someschema')
Whether that schema should automatically be copied from the parent table, I'm
not sure. It would likely be confusing to change it now, though.
You can affect this result yourself without any subclassing, using the
after_parent_attach event:
my_enum = ENUM(...)
@event.listens_for(my_enum, "after_parent_attach")
def associate_schema(target, parent):
target.schema = parent.schema
http://docs.sqlalchemy.org/en/rel_0_8/core/events.html?highlight=after_parent_attach#sqlalchemy.events.DDLEvents.after_parent_attach
Enum also has a create() method of its own, so that you can emit the create any
time:
mytable.c.data.type.create(engine, checkfirst=True)
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html?highlight=enum#sqlalchemy.types.Enum.create
as for tometadata() not creating a copy of the type, I can see that being an
issue. The fact that PG has these types created separately implies the use
case of the same type being used for multiple tables, so it's not clear if
tometadata(schema) should unconditionally suggest copying the enum type out to
that schema as well. Both behaviors could potentially be indicated by adding
a new flag to types.Enum called "inherit_table_schema", indicating the Enum
should attach itself to any table and adopt its schema, and also be copied
during a tometadata() operation with the new schema.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
