Fix uniqueness constraint on image_members table.

Fixes bug #1098700.

Previously, deleting then re-creating an image member failed with
500 ServerError due to the registry service failing to commit
the update to the image_members table.

The problem arose because the declared uniqueness constraint on that
table only spanned the image_id and member columns, but did not take
into account that deleted rows are left in situ with the deleted_at
and deleted columns set as appropriate. Hence the unique constraint
was violated by the addition of the new row.

We modify the uniqueness constraint via a migration script to take
account of the deleted_at column also (NULL for live memberships).

Note that the new ability to recreate an image membership cannot be
be asserted in a functional test, as these tests are based on sqlite
which does not support 'ALTER TABLE DROP CONSTRAINT'.

Change-Id: I175801ba95ecd9295791bb12e9096f59efa19c02

glance/db/sqlalchemy/migrate_repo/versions/022_image_member_index.py

@@ -0,0 +1,84 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2013 Red Hat, Inc.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from migrate.changeset import UniqueConstraint
+from sqlalchemy import and_, func, orm
+from sqlalchemy import MetaData, Table
+
+
+NEW_KEYNAME = 'image_members_image_id_member_deleted_at_key'
+
+
+def upgrade(migrate_engine):
+    image_members = _get_image_members_table(migrate_engine)
+
+    if (migrate_engine.name == 'mysql' or
+        migrate_engine.name == 'postgresql'):
+        UniqueConstraint('image_id',
+                         name=_get_original_keyname(migrate_engine.name),
+                         table=image_members).drop()
+        UniqueConstraint('image_id',
+                         'member',
+                         'deleted_at',
+                         name=NEW_KEYNAME,
+                         table=image_members).create()
+
+
+def downgrade(migrate_engine):
+    image_members = _get_image_members_table(migrate_engine)
+
+    if (migrate_engine.name == 'mysql' or
+        migrate_engine.name == 'postgresql'):
+        _sanitize(migrate_engine, image_members)
+        UniqueConstraint('image_id',
+                         name=NEW_KEYNAME,
+                         table=image_members).drop()
+        UniqueConstraint('image_id',
+                         'member',
+                         name=_get_original_keyname(migrate_engine.name),
+                         table=image_members).create()
+
+
+def _get_image_members_table(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+    return Table('image_members', meta, autoload=True)
+
+
+def _get_original_keyname(db):
+    return {'mysql': 'image_id',
+            'postgresql': 'image_members_image_id_member_key'}[db]
+
+
+def _sanitize(migrate_engine, table):
+    """
+    Avoid possible integrity error by removing deleted rows
+    to accommdate less restrictive uniqueness constraint
+    """
+    session = orm.sessionmaker(bind=migrate_engine)()
+    # find the image_member rows containing duplicate combinations
+    # of image_id and member
+    qry = (session.query(table.c.image_id, table.c.member)
+                  .group_by(table.c.image_id, table.c.member)
+                  .having(func.count() > 1))
+    for image_id, member in qry:
+        # only remove duplicate rows already marked deleted
+        d = table.delete().where(and_(table.c.deleted == True,
+                                      table.c.image_id == image_id,
+                                      table.c.member == member))
+        d.execute()
+    session.close()

glance/db/sqlalchemy/models.py

@@ -152,10 +152,14 @@ class ImageLocation(BASE, ModelBase):
 class ImageMember(BASE, ModelBase):
     """Represents an image members in the datastore"""
     __tablename__ = 'image_members'
+    unique_constraint_key_name = 'image_members_image_id_member_deleted_at_key'
     __table_args__ = (Index('ix_image_members_image_id_member',
                             'image_id',
                             'member'),
-                      UniqueConstraint('image_id', 'member'), {})
+                      UniqueConstraint('image_id',
+                                       'member',
+                                       'deleted_at',
+                                       name=unique_constraint_key_name), {})
 
     id = Column(Integer, primary_key=True)
     image_id = Column(String(36), ForeignKey('images.id'),