Re: [openstack-dev] [DB][Migrations] Switching to using of Alembic
On Tue, Jul 16, 2013 at 11:51 AM, Roman Podolyaka rpodoly...@mirantis.comwrote: Hello, stackers! Most of you who is interested in work around DB in OpenStack must have read this thread [1] started by Boris Pavlovic. Boris made an overview of the work our team is doing to make DB code better. One of our main goals is to switch from sqlalchemy-migrate to Alembic for applying of DB schema migrations. sqlalchemy-migrate was abandoned for a long time, and even now when it's become adopted by OpenStack community, we'd better use a project which is supported by upstream (especially in the case when the author of this project is the same person who also authored SQLAlchemy). The switch isn't going to be simple though. We have a few problems: 1) stable releases must be supported for some time, so we can't switch from migrate to alembic immediately The switch should probably be made when previous migrations scripts are compacted, so all new migrations scripts will use alembic. Switching of such big projects as Nova is hard, so we decided to gain some experience with porting of smaller ones first. Alexei Kornienko is currently working on adding support of Alembic migrations in Ceilometer [3]. I like the idea of switching when we do a major release. I don't think we need to port the old migrations to alembic, though, as I said to Alexei on the review. We should be able to continue to have sqlalchemy-migrate distributed as part of OpenStack for the legacy migrations until those migrations can be dropped entirely. Updating would mean running sqlalchemy-migrate, and then alembic, to apply the full set of migrations. From what I understand the policy to be, since we have sqlalchemy-migrate scripts in Havana we could stop creating new ones in Icehouse and drop the use of sqlalchemy-migrate entirely in the J release when the Havana migrations are removed. Doug Our long term goal is to switch all projects from using of sqlalchemy-migrate to Alembic. 2) we rely on schema migrations to set up an SQLite database for running tests Nova and possibly other projects use schema migrations to set up an SQLite database for running tests. Unfortunately, we can't use models definitions for generation of initial DB schema, because those definitions do not correspond migration scripts. Our team is working on fixing of this issue [2]. As you may now SQLite has limited support of ALTER DDL statements [4]. Nova code contains a few auxiliary functions to make ALTER work in SQLite. Unfortunately, Alembic doesn't support ALTER in SQLite on purpose [5]. In order to run our tests on SQLite right now using Alembic as a schema migration tool, we should add ALTER support to it first. We are going to implement ALTER support in Alembic for SQLite in the next few weeks. As always, your comments in ML and reviews are always welcome. Thanks, Roman [1] http://lists.openstack.org/pipermail/openstack-dev/2013-July/011253.html [2] https://blueprints.launchpad.net/nova/+spec/db-sync-models-with-migrations [3] https://review.openstack.org/#/q/status:open+project:openstack/ceilometer+branch:master+topic:bp/convert-to-alembic,n,z [4] http://www.sqlite.org/lang_altertable.html [5] https://bitbucket.org/zzzeek/alembic ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [DB][Migrations] Switching to using of Alembic
On Tue, Jul 16, 2013 at 11:53 AM, Doug Hellmann doug.hellm...@dreamhost.com wrote: On Tue, Jul 16, 2013 at 11:51 AM, Roman Podolyaka rpodoly...@mirantis.com wrote: Hello, stackers! Most of you who is interested in work around DB in OpenStack must have read this thread [1] started by Boris Pavlovic. Boris made an overview of the work our team is doing to make DB code better. One of our main goals is to switch from sqlalchemy-migrate to Alembic for applying of DB schema migrations. sqlalchemy-migrate was abandoned for a long time, and even now when it's become adopted by OpenStack community, we'd better use a project which is supported by upstream (especially in the case when the author of this project is the same person who also authored SQLAlchemy). The switch isn't going to be simple though. We have a few problems: 1) stable releases must be supported for some time, so we can't switch from migrate to alembic immediately The switch should probably be made when previous migrations scripts are compacted, so all new migrations scripts will use alembic. Switching of such big projects as Nova is hard, so we decided to gain some experience with porting of smaller ones first. Alexei Kornienko is currently working on adding support of Alembic migrations in Ceilometer [3]. I like the idea of switching when we do a major release. I don't think we need to port the old migrations to alembic, though, as I said to Alexei on the review. We should be able to continue to have sqlalchemy-migrate distributed as part of OpenStack for the legacy migrations until those migrations can be dropped entirely. Updating would mean running sqlalchemy-migrate, and then alembic, to apply the full set of migrations. From what I understand the policy to be, since we have sqlalchemy-migrate scripts in Havana we could stop creating new ones in Icehouse and drop the use of sqlalchemy-migrate entirely in the J release when the Havana migrations are removed. This would be my preferred approach as well. Doug Our long term goal is to switch all projects from using of sqlalchemy-migrate to Alembic. 2) we rely on schema migrations to set up an SQLite database for running tests Nova and possibly other projects use schema migrations to set up an SQLite database for running tests. Unfortunately, we can't use models definitions for generation of initial DB schema, because those definitions do not correspond migration scripts. Our team is working on fixing of this issue [2]. As you may now SQLite has limited support of ALTER DDL statements [4]. Nova code contains a few auxiliary functions to make ALTER work in SQLite. Unfortunately, Alembic doesn't support ALTER in SQLite on purpose [5]. In order to run our tests on SQLite right now using Alembic as a schema migration tool, we should add ALTER support to it first. We are going to implement ALTER support in Alembic for SQLite in the next few weeks. As always, your comments in ML and reviews are always welcome. Thanks, Roman [1] http://lists.openstack.org/pipermail/openstack-dev/2013-July/011253.html [2] https://blueprints.launchpad.net/nova/+spec/db-sync-models-with-migrations [3] https://review.openstack.org/#/q/status:open+project:openstack/ceilometer+branch:master+topic:bp/convert-to-alembic,n,z [4] http://www.sqlite.org/lang_altertable.html [5] https://bitbucket.org/zzzeek/alembic ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- -Dolph ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [DB][Migrations] Switching to using of Alembic
To ALL, About Alembic and sqlite Alters, There is easy way to provide Alter in Alembic from sqlite (there are functions they should be just implemented) As I know author is agree if somebody help with this. About switching to Alembic: We are not able to use automatically merging migration tool that produce 1 huge migration form N small until all our migration will be written in alembic. There is no magic and we have only 2 ways to end up with this problems and bugs that could be caused by manually migrations merging and tons of bugs in sqlalchemy-migrate. 1) step by step (openstack method) There are special tests test_migrations that runs migrations on real data against all backends. So we should: a) improve this tests to checks all behaviors // there is a lot of hidden bugs b) replace migration (only one small migration) to alembic c) check that in all backends we made the same changes in schema d) Merge all old migrations in one using alembic (automatically). So it could be done in safe way. 2.a) huge 2 steps 1. Merge all migrations in one huge manually (drop all tests in test migrations) e.g. In Nova was patch https://review.openstack.org/#/c/35748/ I don't believe that there are no mistakes in this migration, and nobody is able to check it. // because of tons of hidden bugs in old migrations and sqla-migrate. 2. Replace this migration in Alembic I don't believe that there will be way to check that there is no bugs 2.b) suicide mode (1 big step) Merge and switch in one step=) We (Mirantis guys and girls) are ready to implement first plan in all projects (step by step, with tons of tests and checks). I think that: Sveta, Anya, Julya, Elena, Yuri, Sergey, Alexei, Roma, Viktor, Alex and me will be able to cover and finish all this work in all projects in Icehouse cycle. If you don't understand why we have only 2 ways to switch to alembic, read at least this: what we have: 1) migration schemas produce different schemas in different backneds 2) db models is not synced with any of these schemas. what it means: 1) So we have to provide sqlite in migrations (until we finish work around unit test against specified backend (https://review.openstack.org/#/c/33236/ ) 2) We are not able to add test that checks that migrations and models are synced (https://review.openstack.org/#/c/34212/) 3) We are not able to use models created DB for unit tests. Best regards, Boris Pavlovic On Tue, Jul 16, 2013 at 9:02 PM, Dolph Mathews dolph.math...@gmail.comwrote: On Tue, Jul 16, 2013 at 11:53 AM, Doug Hellmann doug.hellm...@dreamhost.com wrote: On Tue, Jul 16, 2013 at 11:51 AM, Roman Podolyaka rpodoly...@mirantis.com wrote: Hello, stackers! Most of you who is interested in work around DB in OpenStack must have read this thread [1] started by Boris Pavlovic. Boris made an overview of the work our team is doing to make DB code better. One of our main goals is to switch from sqlalchemy-migrate to Alembic for applying of DB schema migrations. sqlalchemy-migrate was abandoned for a long time, and even now when it's become adopted by OpenStack community, we'd better use a project which is supported by upstream (especially in the case when the author of this project is the same person who also authored SQLAlchemy). The switch isn't going to be simple though. We have a few problems: 1) stable releases must be supported for some time, so we can't switch from migrate to alembic immediately The switch should probably be made when previous migrations scripts are compacted, so all new migrations scripts will use alembic. Switching of such big projects as Nova is hard, so we decided to gain some experience with porting of smaller ones first. Alexei Kornienko is currently working on adding support of Alembic migrations in Ceilometer [3]. I like the idea of switching when we do a major release. I don't think we need to port the old migrations to alembic, though, as I said to Alexei on the review. We should be able to continue to have sqlalchemy-migrate distributed as part of OpenStack for the legacy migrations until those migrations can be dropped entirely. Updating would mean running sqlalchemy-migrate, and then alembic, to apply the full set of migrations. From what I understand the policy to be, since we have sqlalchemy-migrate scripts in Havana we could stop creating new ones in Icehouse and drop the use of sqlalchemy-migrate entirely in the J release when the Havana migrations are removed. This would be my preferred approach as well. Doug Our long term goal is to switch all projects from using of sqlalchemy-migrate to Alembic. 2) we rely on schema migrations to set up an SQLite database for running tests Nova and possibly other projects use schema migrations to set up an SQLite database for running tests. Unfortunately, we can't use models definitions for generation of initial DB schema, because those
Re: [openstack-dev] [DB][Migrations] Switching to using of Alembic
David, 1. Dan Prince thing is equal useful and can help in both cases 2. We are not able to block all openstack for an half year to implement your plan 3. We are able only to convert only grizzly migrations not havana (because our customers should be able to switch from grizzly to havana) 4. We don't need to wait to make SQLAlchemy-migrate deprecated because script that allows in same way to use alembic and migrate is almost ready. 5. I just don't understand, we are spending tons of times to fix and unify work around DB in whole openstack. It is pretty complex and large task. And we should choose the way that is simpler: Our approach: 1) Migrate N migrations to alembic step by step (this work is pretty simple) with tons of tests + Dan Prince method Your approach: 1) Migrate N migrations to one big migrations (it is much more complex then our 1 step) 2) Replace one really huge migration to one Really huge migration in alembic (it is also more complex then our 1 step) So instead on doing 1 long but simple step we are doing 2 complex. Could you explain me why? Best regards, Boris Pavlovic On Tue, Jul 16, 2013 at 10:16 PM, David Ripton drip...@redhat.com wrote: On 07/16/2013 01:58 PM, Boris Pavlovic wrote: There is no magic and we have only 2 ways to end up with this problems and bugs that could be caused by manually migrations merging and tons of bugs in sqlalchemy-migrate. 1) step by step (openstack method) There are special tests test_migrations that runs migrations on real data against all backends. So we should: a) improve this tests to checks all behaviors // there is a lot of hidden bugs b) replace migration (only one small migration) to alembic c) check that in all backends we made the same changes in schema d) Merge all old migrations in one using alembic (automatically). So it could be done in safe way. 2.a) huge 2 steps 1. Merge all migrations in one huge manually (drop all tests in test migrations) e.g. In Nova was patch https://review.openstack.org/#**/c/35748/https://review.openstack.org/#/c/35748/ I don't believe that there are no mistakes in this migration, and nobody is able to check it. // because of tons of hidden bugs in old migrations and sqla-migrate. 2. Replace this migration in Alembic I don't believe that there will be way to check that there is no bugs 2.b) suicide mode (1 big step) Merge and switch in one step=) We have compacted migrations before, and there's a test document for how to verify that the big migration has exactly the same output as the series of small migrations. See https://wiki.openstack.org/** wiki/Database_migration_**testinghttps://wiki.openstack.org/wiki/Database_migration_testing Dan Prince is the expert on this. I think the right process is: 1. Wait until the very beginning of Icehouse cycle. (But not after we have new migrations for Icehouse.) 2. Compact all migrations into 2xx_havana.py (for SQLAlchemy-migrate) 3. Test that it's perfect via above test plan plus whatever enhancements we think of. 4. Manually convert 2xx_havana.py (for SQLAlchemy-migrate) into Alembic, and verify that it's still perfect. 5. Deprecate the SQLAlchemy-migrate version and announce that new migrations should be in Alembic. #4 is hard work but not impossible. I have some old code that does 90% of the work, so we only have to do the other 90%. -- David Ripton Red Hat drip...@redhat.com __**_ OpenStack-dev mailing list OpenStack-dev@lists.openstack.**org OpenStack-dev@lists.openstack.org http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-devhttp://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [DB][Migrations] Switching to using of Alembic
David, Btw it is my code ;), and in the same way we will implement alters in Alembic. Best regards, Boris Pavlovic On Tue, Jul 16, 2013 at 10:09 PM, David Ripton drip...@redhat.com wrote: On 07/16/2013 12:09 PM, Dolph Mathews wrote: On Tue, Jul 16, 2013 at 10:51 AM, Roman Podolyaka rpodoly...@mirantis.com mailto:rpodolyaka@mirantis.**comrpodoly...@mirantis.com wrote: We are going to implement ALTER support in Alembic for SQLite in the next few weeks. I'm a little lost on this ... sqlite doesn't support ALTER, so what exactly is being added to alembic? Is the alembic community receptive or interested? There is some code in Nova (on its way into Oslo) to work around not being able to alter tables in SQLite. It deletes the old table and adds the modified version as a new table. That's the best you can do without modifying SQLite itself. The Alembic README specifically mentions this SQLite issue and says we will support these features provided someone takes the initiative to implement and test. So, yeah, he'll take these patches. Means we'll need to use the future version of Alembic with this feature, though. -- David Ripton Red Hat drip...@redhat.com __**_ OpenStack-dev mailing list OpenStack-dev@lists.openstack.**org OpenStack-dev@lists.openstack.org http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-devhttp://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
