[openstack-dev] Help with sql upgrade and downgrade

2014-08-20 Thread Murali Balcha
Hi,
I am trying to add two new columns to backups table  in cinder. I created the 
new version file as follows:


from sqlalchemy import Column, MetaData, String, Table, Boolean


def upgrade(migrate_engine):

meta = MetaData()

meta.bind = migrate_engine


backups = Table('backups', meta, autoload=True)


snapshot = Column('snapshot', Boolean(create_constraint=False, name=None))

parent_id = Column('parent_id', String(length=255))


backups.create_column(snapshot)

backups.create_column(parent_id)


backups.update().values(snapshot=False).execute()

backups.update().values(parent_id=None).execute()



def downgrade(migrate_engine):

meta = MetaData()

meta.bind = migrate_engine


backups = Table('backups', meta, autoload=True)


snapshot = backups.columns.snapshot

parent_id = backups.columns.parent_id


backups.drop_column(snapshot)

backups.drop_column(parent_id)

I can successfully add string column parent_id without any problem. However 
adding a boolean column is vexing. Adding a boolean column adds a check 
constraint on the table but when I remove the column in the downgrade, the 
check constraint for snapshot still remains on the table which resulting in the 
following exception. Has anyone run into this problem?


OperationalError: (OperationalError) no such column: snapshot u'\nCREATE TABLE 
backups (\n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at 
DATETIME, \n\tdeleted BOOLEAN, \n\tid VARCHAR(36) NOT NULL, \n\tvolume_id 
VARCHAR(36) NOT NULL, \n\tuser_id VARCHAR(255), \n\tproject_id VARCHAR(255), 
\n\thost VARCHAR(255), \n\tavailability_zone VARCHAR(255), \n\tdisplay_name 
VARCHAR(255), \n\tdisplay_description VARCHAR(255), \n\tcontainer VARCHAR(255), 
\n\tstatus VARCHAR(255), \n\tfail_reason VARCHAR(255), \n\tservice_metadata 
VARCHAR(255), \n\tservice VARCHAR(255), \n\tsize INTEGER, \n\tobject_count 
INTEGER, \n\tparent_id VARCHAR(255), \n\tPRIMARY KEY (id), \n\tCHECK (deleted 
IN (0, 1)), \n\tCHECK (snapshot IN (0, 1))\n)\n\n' ()

Thanks,
Murali Balcha
___
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


Re: [openstack-dev] Help with sql upgrade and downgrade

2014-08-20 Thread Mathieu Gagné

On 2014-08-20 6:42 PM, Murali Balcha wrote:


I can successfully add string column parent_id without any problem.
However adding a boolean column is vexing. Adding a boolean column adds
a check constraint on the table but when I remove the column in the
downgrade, the check constraint for snapshot still remains on the table
which resulting in the following exception. Has anyone run into this
problem?

OperationalError: (OperationalError) no such column: snapshot u'\nCREATE
TABLE backups (\n\tcreated_at DATETIME, \n\tupdated_at DATETIME,
\n\tdeleted_at DATETIME, \n\tdeleted BOOLEAN, \n\tid VARCHAR(36) NOT
NULL, \n\tvolume_id VARCHAR(36) NOT NULL, \n\tuser_id VARCHAR(255),
\n\tproject_id VARCHAR(255), \n\thost VARCHAR(255),
\n\tavailability_zone VARCHAR(255), \n\tdisplay_name VARCHAR(255),
\n\tdisplay_description VARCHAR(255), \n\tcontainer VARCHAR(255),
\n\tstatus VARCHAR(255), \n\tfail_reason VARCHAR(255),
\n\tservice_metadata VARCHAR(255), \n\tservice VARCHAR(255), \n\tsize
INTEGER, \n\tobject_count INTEGER, \n\tparent_id VARCHAR(255),
\n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tCHECK (snapshot
IN (0, 1))\n)\n\n' ()



I had a similar issue when trying to add a boolean column to 
volume_types in Cinder. It looks sqlite does not support DROP CHECK 
required by the downgrade process, therefore resulting in the error you see.


You have to create a sql script specifically for sqlite.

See my change: https://review.openstack.org/#/c/114395/6

In cinder/db/sqlalchemy/migrate_repo/versions/024_sqlite_downgrade.sql

The hack consists of creating a new table without the column and 
copying over the data to it.


--
Mathieu

___
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev