Specifying a DB schema name when auto generating migrations

[Ruslan Spivak]

Hi,

I'm trying to use Alembic 0.3.2 with SQLAlchemy 0.7.7 and most
importantly IBM DB SA.
Because Alembic doesn't come with entrypoint for DB2 I whipped out one
for myself which is pretty basic right now but does its job.

ddl/ibmdb.py:

from alembic.ddl.impl import DefaultImpl

class IBMDBImpl(DefaultImpl):
__dialect__ = 'ibm_db_sa'
transactional_ddl = True

So far so good. I can run alembic with --autogenerate and it works
fine except for the fact that it analyzes the default schema in the
DB2 database and not the one that is set through custom declarative
Base in __table_args__.

Is there a way to tell Alembic to use a particular DB schema?

Thanks,
Ruslan

[Michael Bayer]

Here's the points at which this may not (or definitely does not) work:

1. the IBM DB2 dialect would need to support reflection of tables that are not in the default schema. Hopefully it does this already.

2. The autogenerate system in Alembic would need to include some way to configure it to also look in non-default schemas. The most straightforward way to do this might perhaps be that it runs through the given model metadata to collect all the schemas present so that it knows where to look. If you're interested in helping with this let me know.

3. the migration directives in op.* are in great need of more "schema" arguments, in most cases it's not supported. Help is also needed here, this is https://bitbucket.org/zzzeek/alembic/issue/33/support-of-schemas-is-absent-for-most-of .

So the space for support is there, but not something I've needed myself yet, so patches/tests to get started would help this along...else I'll get to it eventually.

[Ruslan Spivak]

Thanks for your suggestions Michael.

I'll dig deeper into the code and see what I can do.

Cheers,
Ruslan

On May 16, 2:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> Here's the points at which this may not (or definitely does not) work:
>
> 1. the IBM DB2 dialect would need to support reflection of tables that are not in the default schema.   Hopefully it does this already.
>
> 2. The autogenerate system in Alembic would need to include some way to configure it to also look in non-default schemas.   The most straightforward way to do this might perhaps be that it runs through the given model metadata to collect all the schemas present so that it knows where to look.   If you're interested in helping with this let me know.
>

> 3. the migration directives in op.* are in great need of more "schema" arguments, in most cases it's not supported.   Help is also needed here, this ishttps://bitbucket.org/zzzeek/alembic/issue/33/support-of-schemas-is-a....

[Sok Ann Yap]

Hi Ruslan,

If your application happens to use just one schema, then I think specifying it in __table_args__ is overkill. You can either add "CurrentSchema = XXX" to db2cli.ini (if using the ibm_db driver), or append "CurrentSchema=XXX" to SQLAlchemy URL (if using the pyodbc driver).

Regards,

Yap