autogenerate doesn't render DEFERRABLE, INITIALLY, ONUPDATE, ONDELETE of ForeignKeyConstraint()

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "DEFERRABLE and INITIALLY arguments not supported b" to "DEFERRABLE INITIALLY ONUPDATE ONDELETE arguments n"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

0ed2080

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Saif Hakim (@saifelse) wrote:

I don't see support for initially in create_foreign_key. Am I missing something?

https://bitbucket.org/zzzeek/alembic/src/623c7e76ef04c5656d7a116287a39e318f6744b1/alembic/operations.py?at=master#cl-517

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "DEFERRABLE INITIALLY ONUPDATE ONDELETE arguments n" to "autogenerate doesn't render DEFERRABLE INITIALLY O"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

see #190

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - rendering

[sqlalchemy-bot]

Tim Mitchell wrote:

I had this issue crop up again in alembic-0.7.5.post2.
The ondelete clause was missed when I changed the foreign key on a column from one table to another.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

@tim_mitchell - can you please provide more detail? Was this issue resolved for you and then stopped working? Can you provide specifics about the move of an FK here? the create for the FK is using the FK with the arguments you've given it and the above changeset has tests for this (see https://bitbucket.org/zzzeek/alembic/commits/0ed20805f3f32de768b9b4eb9eebc9c3e0ac7453#Ltests/test_autogenerate.pyT957)

[sqlalchemy-bot]

Tim Mitchell wrote:

I have only been using alembic for about a month. All added constraints show this defect. None of your tests appear to test multiple keyword arguments as is always the case in my situation.
Have upgraded to alembic 0.7.6/sa 1.04 and problem persists.

Here is excerpts from my code which is hopefully enough to reproduce.

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base, declared_attr

def column_names(constraint, table):
    columns = getattr(constraint, 'columns', None)
    if columns is None:
        return ''
    columns_str = '_'.join(columns.keys())
    return columns_str

naming_convention = {  # for indices, constraints, primary and foreign keys
                       b"uq": b"%(table_name)s_%(column_names)s_key",
                       b"fk": b"%(table_name)s_%(column_0_name)s_fkey",
                       b"pk": b"%(table_name)s_pkey",
                       b"ix": b'%(table_name)s_%(column_names)s_idx',
                       b"ck": b"%(table_name)s_%(constraint_name)s_chk",
                       b"column_names": column_names,
}


class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__

    id = Column(GUID, default=uuid.uuid4, primary_key=True)


metadata = sqlalchemy.MetaData(schema='my_schema',
                    naming_convention=naming_convention)
Base = declarative_base(cls=Base, metadata=metadata)


def foreign_key_def(*args, **kwargs):
    """ A ForeignKey contraint that defaults to defer constraint checking until the commit.
    This is easier and faster than adding commits to ensure that sqlalchemy executes
    inserts with relationships in the correct order.
    """
    if b'deferrable' not in kwargs:
        kwargs[b'deferrable'] = True
    if b'initially' not in kwargs:
        kwargs[b'initially'] = b'DEFERRED'
    if b'ondelete' not in kwargs:
        kwargs[b'ondelete'] = b'CASCADE'
    return ForeignKey(*args, **kwargs)

def required_column(*args, **kwargs):
    kwargs[b'nullable'] = False
    return Column(*args, **kwargs)


class BlockModel(Base):
    # more columns and relationships
    pass

class BlockModelAndValues(Base):
    block_model_id = required_column(GUID, foreign_key_def(BlockModel.id))
    # more columns and relationships

class BlockModelCategoryValues(Base):
    # more columns
    pass

class AssocBlockModelCategoryValues(Base):
    owner_id = required_column(GUID, foreign_key_def(BlockModel.id))
    # upgrade to
    # owner_id = required_column(GUID, foreign_key_def(BlockModelAndValues.id))
    values_id = required_column(GUID, foreign_key_def(BlockModelCategoryValues.id))


# ALEMBIC GENERATED CODE
#    op.create_foreign_key(op.f('AssocBlockModelCategoryValues_owner_id_fkey'), 'AssocBlockModelCategoryValues',
#                          'BlockModelAndValues', ['owner_id'], ['id'], source_schema='central_data',
#                          referent_schema='central_data', deferrable='True', initially='DEFERRED')
# ondelete= is missing

[sqlalchemy-bot]

Tim Mitchell wrote:

Have just noticed that compare_metadata() does not notice the differing ondelete clauses either.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

tests are added: 87629c5

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Ok thats the ForeignKeyConstraint. for the create_fk_constraint directive, they are missing. that's now #298.

For the comparison part of this, autogenerate doesn't compare those values right now as they are not well-supported by SQLAlchemy reflection right now.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

#298 is fixed, thanks for reporting!