On Tuesday, August 27, 2013 at 9:31:29 AM UTC-5, Michael Bayer wrote: > > > On Aug 26, 2013, at 11:49 PM, Samer Atiani <sat...@gmail.com <javascript:>> > wrote: > > Hello, > > All my tables have create_date and update_date columns in them, and I like > to keep these columns as the last two columns in any table for convention > reasons. However, when you use alembic autogenerate to generate schema > migrations to add columns, the columns are always added to the end of the > table. With time, the create_date and update_date columns in my tables will > end up being in the middle of the column list in my MySQL database. > > So I was trying to alter this behavior by making alembic use MySQL's > "ALTER TABLE x ADD COLUMN y AFTER z" feature to always try to add columns > before create_date or update_date. The only way I could find out how is to > override the visit_add_column method after EnvironmentContext > configuration. I achieved this by changing the "run_migrations_online()" > method in alembic/env.py so that it looks like: > > > However, this feels quite brittle and is probably touching internal stuff > that it shouldn't touch. My question to you is: is there a better way to > achieve this? I looked at SQLAlchemy Core Events, but I couldn't find an > event that could correspond to adding columns, nor is the alembic code > firing any events I could see when it add columns (unlike, for example, > when it emits a CREATE TABLE statements). > > > > AddColumn is a SQL expression element so the standard way we want to > override those is to use @compiles ( > http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#changing-the-default-compilation-of-existing-constructs). > > As you might have noticed, Alembic is already using that system > internally in order to provide compile rules for constructs. So we want > to override that, however the roadblock is that the @compiles system > currently doesn't have a nice way of letting us override the @compiles of > an existing @compiles (should be added as a feature). So there's a slight > bit of internals we need to get at Alembic's already present @compiles rule > for now, but the rest is straightforward: > > from sqlalchemy.ext.compiler import compiles > from alembic.ddl.base import AddColumn > > # ideally, the @compiles system would have some way of getting > # us the "existing" @compiles decorator, so this part is the > # hack > specs = AddColumn.__dict__.get('_compiler_dispatcher').specs > existing_dispatch = specs.get('mysql', specs['default']) > > @compiles(AddColumn, "mysql") > def add_column(element, compiler, **kw): > text = existing_dispatch(element, compiler, **kw) > if "after" in element.column.info: > text += " AFTER %s" % element.column.info['after'] > return text > > from sqlalchemy import Column, Integer > from alembic.migration import MigrationContext > from alembic.operations import Operations > > ctx = MigrationContext.configure(dialect_name="mysql", opts={"as_sql": > True}) > op = Operations(ctx) > > op.add_column("t", Column('y', Integer)) > > op.add_column("t", Column('x', Integer, info={"after": "y"})) >
I'm interested in applying a similar solution to handle alter columns. The problem I'm seeing is that MySQLImpl.alter_column() does not pass *kw to MySQLChangeColumn or MySQLModifyColumn. So there doesn't seem to be a simple way that I can write a @compiles(MySQLChangeColumn, 'mysql') decorated function, and the interface would have to be something like element.info instead of element.column.info too. Also, can you explain why I have to use MySQLChangeColumn/MySQLModifyColumn instead of AlterColumn in the @compiles decorator? Would it be possible to get support for this into alembic proper? I can get it to work, but I have to monkey patch MySQLImpl. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.