utils.drop_unique_constraint and associated tests totally broken

[Mike Bayer]

the logic surrounding drop_unique_constraint() and the associated tests is broken in many ways.

the key to the failure is that both the function and the tests assume that autoload=True on a Table reflects UniqueConstraint objects. Unfortunately, this is not the case for SQLAlchemy 0.9.x and earlier, and both the function, and the tests, fail because of this. As this feature has been added in 1.0, the tests again fail on 1.0, but the feature at least seems to work.

The failure on previous versions is that *all* unique constraint objects are dropped against the SQLite backend. Therefore, this method is completely dangerous and should probably be removed entirely, as it is already marked as deprecated.

With SQLAlchemy 0.9.x or earler, there will be *no* UniqueConstraint objects under any circumstances in table.constraints with this code:

    meta = MetaData(bind=self.engine)
    test_table = Table(table_name, meta, autoload=True)

There will however be a PrimaryKeyConstraint object, and that's why this test logic is broken:

    meta = MetaData(bind=self.engine)
    test_table = Table(table_name, meta, autoload=True)
    constraints = [c for c in test_table.constraints if c.name == uc_name]
    self.assertEqual(len(constraints), 0)
    self.assertEqual(len(test_table.constraints), 1)

The "1" there is simply the PrimaryKeyConstraint. The test was written without awareness of this object being present.

With SQLAlchemy master, the length of test_table.constraints is *2* - the PrimaryKeyConstraint, and the UniqueConstraint remaining that was not dropped. So the test fails, because the feature worked in this case and the correct number of constraints is 2.

The drop_unique_constraint() method itself catastrophically drops *all* unique constraints on the SQLite backend for the same reason:

    t = Table(table_name, meta, autoload=True)
    uc = UniqueConstraint(*columns, table=t, name=uc_name)
    uc.drop()

the above Table has *no* unique constraints in it. Migrate's approach of rebuilding the whole table fails because the necessary UniqueConstraint that *isn't* dropped is non-present.

I'm assuming that on any other backend, Migrate is not recreating the entire table so this issue doesn't occur.

The correct way to test for the number of unique constraints regardless of SQLAlchemy version is to use the inspector directly (note that it caches results, so when schema changes are made, a new inspector must be built):

    from sqlalchemy import inspect
    insp = inspect(self.engine)
    constraints = insp.get_unique_constraints(table_name)

[Mike Bayer (zzzeek)]

to fix this for SQLite, we'd have to manually add UniqueConstraint objects to the reflected table after using inspector.get_unique_constraints(), that is, backport SQLAlchemy 1.0's feature. I'd rather whack this method entirely, so putting it out there to estimate the impact of either approach.

[Mike Bayer (zzzeek)]

also note this bug is a blocker for SQLAlchemy's own test suite against openstack, as these tests now fail.

[Viktor Serhieiev (vsergeyev)]

AFAIK, drop_unique_constraint() was decigned as helper for Nova migrations on SQLite. Now these migrations are gone so there is (seems to be) no usage of this function in OpenStack.

[Viktor Serhieiev (vsergeyev)]

Closed by https://review.openstack.org/#/c/126736/