migration 6 drops deleted image property index in sqlite

[Mark Washenberger]

At version 5 of the glance db in sqlite, I see the following schema/indices:

sqlite> select * from migrate_version;
Glance Migrations|/home/markwash/devel/glance/glance/db/sqlalchemy/migrate_repo|5
sqlite> .schema image_properties
CREATE TABLE image_properties (
 id INTEGER NOT NULL,
 image_id INTEGER NOT NULL,
 "key" VARCHAR(255) NOT NULL,
 value TEXT,
 created_at DATETIME NOT NULL,
 updated_at DATETIME,
 deleted_at DATETIME,
 deleted BOOLEAN NOT NULL,
 PRIMARY KEY (id),
 UNIQUE (image_id, "key"),
 FOREIGN KEY(image_id) REFERENCES images (id),
 CHECK (deleted IN (0, 1))
);
CREATE INDEX ix_image_properties_deleted ON image_properties (deleted);
CREATE INDEX ix_image_properties_image_id ON image_properties (image_id);
CREATE INDEX ix_image_properties_image_id_key ON image_properties (image_id, "key");
sqlite> .indices
ix_image_properties_deleted
ix_image_properties_image_id
ix_image_properties_image_id_key
ix_images_deleted
ix_images_is_public

After upgrading to version 6, however, I see

sqlite> .schema image_properties
CREATE TABLE image_properties (
 id INTEGER NOT NULL,
 image_id INTEGER NOT NULL,
 name VARCHAR(255) NOT NULL,
 value TEXT,
 created_at DATETIME NOT NULL,
 updated_at DATETIME,
 deleted_at DATETIME,
 deleted BOOLEAN NOT NULL,
 PRIMARY KEY (id),
 CHECK (deleted IN (0, 1)),
 UNIQUE (image_id, name),
 FOREIGN KEY(image_id) REFERENCES images (id)
);
CREATE INDEX ix_image_properties_name ON image_properties (name);
sqlite> .indices
ix_image_properties_name
ix_images_deleted
ix_images_is_public

From the text of the change, this does not seem intentional.

        Column('deleted', Boolean(), nullable=False, default=False,
               index=True),

This doesn't really seem like a big deal, as the index is preserved in mysql at least. Just wanted to document it.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/23873

[John Bresnahan (jbresnah)]

The index is again lost at migration point 12. I will create a separate bug for that.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/23873
Committed: http://github.com/openstack/glance/commit/03c5a98b22e33334264abbe5d0f394b33ac9f581
Submitter: Jenkins
Branch: master

commit 03c5a98b22e33334264abbe5d0f394b33ac9f581
Author: John Bresnahan <email address hidden>
Date: Thu Mar 7 13:48:22 2013 -1000

    Insure that migration 6 retains deleted image property index.

    Migration 6 for sqlite deletes the entire table and then recreates it,
    however it fails to create the image_properties deleted index.

    Fixes bug: 1051123

    Change-Id: I4740ff1d5b9ec44067e6af7dd822329105222690