Issues

Issue #47 wontfix

migrations are including dialect-specific datatypes for columns it should already know the datatype for

dieselmachine
created an issue

I have a simple test project i built, and generated the initial commit, and upgraded to it. The relevant section from the migration file is this:

{{{

!python

op.create_table('user_profiles', ... sa.Column('po_zip', sa.String(length=12), nullable=True), ... }}}

I then edited the po_zip column on the model to be String(14) instead, and generated a new revision using autogenerate, and the detected change looks like this:

{{{

!python

op.alter_column('user_profiles', u'po_zip', 
           existing_type=mysql.VARCHAR(length=12), 
           type_=sa.String(length=14), 
           existing_nullable=True)

}}}

Now, the inclusion of mysql.* implies the migration read from the db to determine the diffs, rather than running the present migrations from base to head and using that as the base for comparison.

I'd like the generated diffs to be comparisons between the current orm.metadata and the generated metadata by running previous revisions up to the current head, rather than reading from the database. I'd like the generated diff to look like this:

{{{

!python

op.alter_column('user_profiles', u'po_zip', 
           existing_type=sa.String(length=12), 
           type_=sa.String(length=14), 
           existing_nullable=True)

}}}

Is there a way to accomplish this? Is autogenerate just comparing the current orm to the raw db everytime?

Comments (5)

  1. Mike Bayer repo owner

    yes, autogenerate compares the state of the database as is to the given metadata. I don't understand how exactly the system could "run present migrations from base to head" - if this means, "run them into the database", this is not possible as the database is expected to already be at the head migration. If this means, "run them into a sample MetaData object and compare", this is also not possible, as no initial state of the database is given, cannot operate for complex operations such as op.execute("any sql"), and in general would be a vastly complex and largely ineffective operation for little benefit.

    The comparison of types and the reflection can be overridden to provide custom behavior, however I am travelling at the moment and do not have the resources to work up a complete example until late next week.

  2. Mike Bayer repo owner

    actually now that I've read this more carefully, what do you need exactly ? It's out of scope for Alembic to do the comparison by running the all the migrations "virtually" into a metadata - this would be a very involved and complex operation. What's the issue with existing_type=mysql.VARCHAR exactly, trying to run on multiple dbs ?

  3. dieselmachine reporter

    (Reply via dies...@gmail.com):

    Yeah, that was the intention. To be able to generate migration files that would run anywhere, without containing dialect-specific information.

    I'm able to generate an 'initial' dump by calling _compare_tables with an empty set for conn_table_names, and the resulting migration file, being based only on the orm, contains no dialect-specific info. The -exact- situation I was trying to solve was changing a String(12) to a String(14), rather than showing 'existing_type=String(12)', it shows mysql.VARCHAR(12). I was hoping the migration would somehow be aware that the previous 'state' of the column was a String, but it seems I didn't really understand how alembic worked.

    Because the migration file operates on the db, it wouldn't cause any havoc to 'guess' the existing type based on the current type (even if the change was from Unicode(12) to String(14), the actual interpretation would still be based on the model), so when the migration encounters a String(14), and see the previous was varchar(12), because varchar "equates" to the same raw type as String, it could return String(12) as the existing_type, with a little coercion.

    I haven't tried your suggestion yet from the previous reply, but I plan on playing around with it this weekend.

  4. Mike Bayer repo owner

    the "previous type" argument is best expressed as exactly what was found in the target database, since this is used to accommodate ALTER syntaxes that require the full column specification to be repeated, including type information, in order to do something else like change the NOT NULL setting. If the migration is actually changing the type to something else then the "previous type" field is not used.

  5. Mike Bayer repo owner

    at the moment I don't have plans to make a change here yet, so when you've had a chance to try things out feel free to reopen here if you want to keep discussing things.

  6. Log in to comment