[openstack-dev] [Murano] SQLite support - drop or not?

Mike Bayer mbayer at redhat.com
Fri Jan 30 19:00:14 UTC 2015

Andrew Pashkin <apashkin at mirantis.com> wrote:

> Working on this issue I encountered another problem.
> Most indices in the project has no names and because of that,
> developer must reverse-engineer them in every migration.
> Read about that also here [1].
> SQLAlchemy and Alembic provide feature for generation constraint
> names by pattern, specifically to resolve that kind of issues [1].
> I decided to introduce usage of this feature in Murano.
> I've implemented solution that preserves backward-compatibility
> for migration and allows to rename all constraints according
> to patterns safely [2]. With it user, that have already deployed Murano
> will be able to upgrade to new version of Murano without issues.
> There are downsides in this solution:
> - It assumes that all versions of Postgres and MySQL uses the
>  same patterns for constraints names generation.
> - It is hard to implement a test for this solution and it will be slow.
>  Because there is need to reproduce such situation when user has old
>  versions of migrations applied, and then tries to upgrade.

The patch seems to hardcode the conventions for MySQL and Postgresql.   The first thought I had was that in order to remove the dependence on them here, you’d need to instead simply turn off the “naming_convention” in the MetaData if you detect that you’re on one of those two databases.   That would be a safer idea than trying to hardcode these conventions (and would also work for other kinds of backends).

However, I’m not actually sure that you even need special behavior for these two backends.  If an operator runs these migrations on a clean database, then the constraints are generated with the consistent names on all backends.   if a target database already has these schema constructs present, then these migrations are never run; it doesn’t matter that they have the right or wrong names already.

I suppose then that the fear is that some PG/MySQL databases will have constraints that are named in one convention, and others will have constraints using the native conventions.    However, the case now is that all deployments are using native conventions, and being able to DROP these constraints is already not very feasible unless you again were willing to hardcode those naming conventions up forward.    The constraints in these initial migrations, assuming you don’t regenerate them, might just need to be left alone, and the project proceeds in the future with a consistent convention.

However, it’s probably worthwhile to introduce a migration that does in fact rename existing constraints on MySQL and Postgresql.  This would be a migration script that emits DROP CONSTRAINT and CREATE CONSTRAINT for all the above constraints that have an old name and a new name.  The script would need to check the backend, as you’re doing now, in order to run, and yes it would hardcode the names of those conventions, but at least it would just be a one-time run against only currently deployed databases.   Since your migrations are run “live”, the script can make itself a “conditional” run by checking for the “old” names and skipping those that don’t exist.      

> Another possible solution is to drop all current migrations and
> introduce new one with correct names.

you definitely shouldn’t need to do that.

> This brings us to new problem - migrations and models are out of sync
> right now in multiple places - there are different field types in
> migrations and models, migrations introduces indices that is absent
> in models, etc.
> And this solution has great downside - it is not backward-compatible,
> so all old users will lost their data.
> We (Murano team) should decide, what solution we want to use.
> [1]
> http://alembic.readthedocs.org/en/latest/naming.html#tutorial-constraint-names
> [2] https://review.openstack.org/150818
> -- 
> With kind regards, Andrew Pashkin.
> cell phone - +7 (985) 898 57 59
> Skype - waves_in_fluids
> e-mail - apashkin at mirantis.com
> __________________________________________________________________________
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

More information about the OpenStack-dev mailing list