Autogenerate could easily help us out a lot more with current features

[sqlalchemy-bot]

Migrated issue, originally created by Matt Jernigan (juanitogan)

I just went through the model change of a major app update that added a new level of hierarchy to most tables, and learned a lot about Alembic in the process. Alembic could have saved me a few hours if it cached and rendered the detected changes as follows -- with the following hints as well as to where I might need to add my own changes:

#!python

# Drop FKs
op.drop_constraint(..., type_='foreignkey')
op.drop_constraint(..., type_='foreignkey')
op.drop_constraint(..., type_='foreignkey')

### Insert your own drop PKs here:
# op.drop_constraint('pk_table', 'table', type_='primary')

# Drop UQs
op.drop_constraint(..., type_='unique')
op.drop_constraint(..., type_='unique')
op.drop_constraint(..., type_='unique')

### Insert your own other drops here:

# New tables
op.create_table(...)
op.create_table(...)
op.create_table(...)

# New columns (minus the NOT NULL `nullable = False` part)
op.add_column(...)
op.add_column(...)
op.add_column(...)

### Insert your own DML to fill new domain tables and NOT NULL columns here:
# op.execute()

# Now set NOT NULL on the new columns
op.alter_column(..., nullable = False)
op.alter_column(..., nullable = False)
op.alter_column(..., nullable = False)

### Insert your own other creates here:

# Create UQs
op.create_unique_constraint(op.f(...), ...)
op.create_unique_constraint(op.f(...), ...)
op.create_unique_constraint(op.f(...), ...)

### Insert your own create PKs here:
# op.create_primary_key(...)

# Create FKs
op.create_foreign_key(op.f(...), ...)
op.create_foreign_key(op.f(...), ...)
op.create_foreign_key(op.f(...), ...)

Well, more or less. I can't say I got the exact order right (if its even possible given that new tables may need a foreign key to a PK or index that was just dropped -- so maybe FKs on all new tables should be saved for later). Also note that I split out the nullable = False part of new columns until after the DML loading -- very helpful.

Anyhow, regardless of exact order of it all (which I didn't spend too much time on here), I hope the idea is clear -- and I hope this also helps get Alembic closer to auto working with PKs.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

hello -

again this is totally vague. Are you talking about the order of ops as rendered, or the comments added, or what? What is being "cached"? What is the model change that would generate such output ? I have no idea how Alembic would somehow suggest where in code it generates that other arbitrary code might be added? Feel free to propose a pull request if you feel this is easy (as the title mentions).

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

as discussed in https://bitbucket.org/zzzeek/alembic/issues/452/create_unique_constraint-not-working-with#comment-39941968 this needs to be reproposed in terms of specific, well defined behaviors and preferably demonstrations of new rulesets.