Hey all, For the better part of the last week, I've been struggling to use SQLAlchemy-Migrate to produce database migration scripts for Glance. In Glance, unlike Nova, we have a unit test that tests that the database for Glance's registry can be downgraded and upgraded smoothly. In Nova, none of the migrate scripts even contain a downgrade method, so this isn't even testable.
We've found a number of problems in using SA-Migrate: 1. When using the SQLite engine, if a table has a column with index=True on it and you drop any column on that table, SA-Migrate will die if you use Python migrate scripts (http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=117) 2. When using the MySQL engine, you cannot use SQL scripts with multiple statements in them as upgrade/downgrade scripts (http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=99) because SA-Migrate pipes all the statements into the Connection.execute() method as a single statement, causing the underlying MySQLdb driver to vomit an error 2014 "Commands out of sync" 3. In order to solve #1 above, you need to do your own SQL scripting to bypass bugs in SA-Migrate. Writing the SQL script means you run into #2 above. This means you can't test both SQLite and MySQL migrations using the same scripts. This sucks. A lot. 4. It seems the contributors to SA-Migrate are not responsive to bug reports. This makes it less than ideal as a dependency for Glance, especially considering the critical nature that database migrations play in installation and upgrading of our software. So, what to do? I see these options, none of which I particularly like: 1) Don't test migration scripts - the Nova approach. This would mean we write all our migrate scripts as Python scripts, which work better with MySQL but break completely for SQLite. No idea about PostgreSQL. 2) Don't use SA-Migrate and write a replacement using python-sqlparse and straight SQL scripts. While I can't stand SA-Migrate at this point, it remains really the only thing that remotely works. 3) Submit patches to SA-Migrate and keep patched versions of SA-Migrate in the Glance PPA. Thoughts? I'm leaning towards #2, then #1, then #3. -jay _______________________________________________ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp