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

[Georgy Okrokvertskhov]

Hi Andrew,

I understand the difficulties with SQLite support, but this is very useful
for development to have SQLite instead of any other DB. I think nodoby uses
SQLite in production, so probably we can just put a release note that there
is a know limitation with SQLite support.

Thanks
Gosha

On Fri, Jan 23, 2015 at 10:04 AM, Andrew Pashkin 
wrote:

>  Hello!
>
> Current situation with SQLite support:
> - Migration tests does not run on SQLIte.
> - At the same time migrations themselves support SQLite (with bugs).
>
> Today I came across this bug:
> Error during execution of database downgrade
> 
>
> We can resolve this bug by hardening SQLite support, in that case:
> - We need to fix migrations and make them support SQLite without bugs, and
> then continuously make some effort to maintain this support (manually
> writing migrations and test cases for them).
> - Also need to introduce migration tests run on SQLite.
>
> We also can drop SQLite support and in this case:
> - We just factor out all that related to SQLite from migrations one time
> and set this bug as "Won't fix".
>
> Let's discuss that.
>
> --
> With kind regards, Andrew Pashkin.
> cell phone - +7 (985) 898 57 59
> Skype - waves_in_fluids
> e-mail - apash...@mirantis.com
>
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
>


-- 
Georgy Okrokvertskhov
Architect,
OpenStack Platform Products,
Mirantis
http://www.mirantis.com
Tel. +1 650 963 9828
Mob. +1 650 996 3284
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Ruslan Kamaldinov]

On Fri, Jan 23, 2015 at 9:04 PM, Andrew Pashkin  wrote:
> Hello!
>
> Current situation with SQLite support:
> - Migration tests does not run on SQLIte.
> - At the same time migrations themselves support SQLite (with bugs).
>
> Today I came across this bug:
> Error during execution of database downgrade
>
> We can resolve this bug by hardening SQLite support, in that case:
> - We need to fix migrations and make them support SQLite without bugs, and
> then continuously make some effort to maintain this support (manually
> writing migrations and test cases for them).
> - Also need to introduce migration tests run on SQLite.
>
> We also can drop SQLite support and in this case:
> - We just factor out all that related to SQLite from migrations one time and
> set this bug as "Won't fix".
>
> Let's discuss that.

I agree that we don't need to support SQLite in migrations. As it was
already said in [1], there is no point in running DB migrations
against SQLite.

Here is what I suggest to do:
1. Use ModelsMigrationsSync from [2] in tests to make sure that
SQLAlchemy models are in sync with migrations. Usage example can be
found at [3]
2. Populate DB schema from SQLAlchemy models in unit-tests which
require access to DB
3. Wipe out everything related to SQLite from DB migrations code
4. Recommend all developers to use MySQL when they run Murano locally
5. For those who still insist on SQLite we can provide a command line
option which would generate database schema from SQLAlchemy metadata.
This should be declared as development only feature, not supported for
any kind of production deployments

[1] http://lists.openstack.org/pipermail/openstack-dev/2015-January/055058.html
[2] 
http://git.openstack.org/cgit/openstack/oslo.db/tree/oslo_db/sqlalchemy/test_migrations.py
[3] 
http://git.openstack.org/cgit/openstack/sahara/tree/sahara/tests/unit/db/migration/test_migrations_base.py#n198

Thanks,
Ruslan

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Andrew Pashkin]

/On 23.01.2015 23:39, Ruslan Kamaldinov wrote://
///
/1. Use ModelsMigrationsSync from [2] in tests to make sure that 
SQLAlchemy models are in sync with migrations. Usage example can be 
found at [3]/


Seems like it is a great helper, as I understand it runs all migrations 
and then compares DB state with models state and throws an error if they 
are out of sync.
What I don't understand - why they still manually write checks for every 
migration [1]? This is redundant, because ModelsMigrationsSync already 
does the job testing that DB is in sync with models.


By the way in Heat project they do the same thing [2]. What am I missing?

[1] 
http://git.openstack.org/cgit/openstack/sahara/tree/sahara/tests/unit/db/migration/test_migrations.py#n402
[2] 
https://github.com/openstack/heat/blob/7d4c4030c591ef5994db4327d66d353ad83c6ea8/heat/tests/db/test_migrations.py#L288



/On 23.01.2015 23:39, Ruslan Kamaldinov wrote://
/
/2. Populate DB schema from SQLAlchemy models in unit-tests which 
require access to DB/

You mean - using these tools [3]?

[3] 
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#creating-and-dropping-database-tables



/On 23.01.2015 23:39, Ruslan Kamaldinov wrote://
/

/3. Wipe out everything related to SQLite from DB migrations code //
//4. Recommend all developers to use MySQL when they run Murano locally //
//5. For those who still insist on SQLite we can provide a command 
line option which would generate database schema from SQLAlchemy 
metadata. This should be declared as development only feature, not 
supported for any kind of production deployments /

In what conditions 5) will fail? I see only these cases:
- If data migrations would be introduced and Murano would require some 
data in DB to work correctly.
- If Murano would use some database-specific features (stored procedures 
etc).


There are good chances that these cases will never happen in reality, as 
I understand, so I tend to agree.


--
With kind regards, Andrew Pashkin.
cell phone - +7 (985) 898 57 59
Skype - waves_in_fluids
e-mail - apash...@mirantis.com

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Ruslan Kamaldinov]

On Mon, Jan 26, 2015 at 3:03 PM, Andrew Pashkin  wrote:
> On 23.01.2015 23:39, Ruslan Kamaldinov wrote:
>
> 1. Use ModelsMigrationsSync from [2] in tests to make sure that SQLAlchemy
> models are in sync with migrations. Usage example can be found at [3]
>
> Seems like it is a great helper, as I understand it runs all migrations and
> then compares DB state with models state and throws an error if they are out
> of sync.
> What I don't understand - why they still manually write checks for every
> migration [1]? This is redundant, because ModelsMigrationsSync already does
> the job testing that DB is in sync with models.
>
> By the way in Heat project they do the same thing [2]. What am I missing?

I think it's still important to perform migration specific checks. We
want to make sure that DB is in expected state after each specific
migration.

> [1]
> http://git.openstack.org/cgit/openstack/sahara/tree/sahara/tests/unit/db/migration/test_migrations.py#n402
> [2]
> https://github.com/openstack/heat/blob/7d4c4030c591ef5994db4327d66d353ad83c6ea8/heat/tests/db/test_migrations.py#L288

> 2. Populate DB schema from SQLAlchemy models in unit-tests which require
> access to DB
>
> You mean - using these tools [3]?
>
> [3]
> http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#creating-and-dropping-database-tables

Yes, this one. We still have this code in source tree:
http://git.openstack.org/cgit/stackforge/murano/tree/murano/db/models.py#n289

> In what conditions 5) will fail? I see only these cases:
> - If data migrations would be introduced and Murano would require some data
> in DB to work correctly.

Actually, we already do that. We populate initial set of categories:
http://git.openstack.org/cgit/stackforge/murano/tree/murano/db/migration/alembic_migrations/versions/001_inital_version.py#n40

Would it affect anyone? I don't think so. You always can populate
these categories manually.

> - If Murano would use some database-specific features (stored procedures
> etc).

That should never happen :)

> There are good chances that these cases will never happen in reality, as I
> understand, so I tend to agree.

Agree

--
Ruslan

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Andrew Pashkin]

/On 26.01.2015 18:05, Ruslan Kamaldinov wrote:/
/I think it's still important to perform migration specific checks. We 
want to make sure that DB is in expected state after each specific 
migration./

Why?

On 26.01.2015 18:05, Ruslan Kamaldinov wrote:

On Mon, Jan 26, 2015 at 3:03 PM, Andrew Pashkin  wrote:

On 23.01.2015 23:39, Ruslan Kamaldinov wrote:

1. Use ModelsMigrationsSync from [2] in tests to make sure that SQLAlchemy
models are in sync with migrations. Usage example can be found at [3]

Seems like it is a great helper, as I understand it runs all migrations and
then compares DB state with models state and throws an error if they are out
of sync.
What I don't understand - why they still manually write checks for every
migration [1]? This is redundant, because ModelsMigrationsSync already does
the job testing that DB is in sync with models.

By the way in Heat project they do the same thing [2]. What am I missing?

I think it's still important to perform migration specific checks. We
want to make sure that DB is in expected state after each specific
migration.


[1]
http://git.openstack.org/cgit/openstack/sahara/tree/sahara/tests/unit/db/migration/test_migrations.py#n402
[2]
https://github.com/openstack/heat/blob/7d4c4030c591ef5994db4327d66d353ad83c6ea8/heat/tests/db/test_migrations.py#L288
2. Populate DB schema from SQLAlchemy models in unit-tests which require
access to DB

You mean - using these tools [3]?

[3]
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#creating-and-dropping-database-tables

Yes, this one. We still have this code in source tree:
http://git.openstack.org/cgit/stackforge/murano/tree/murano/db/models.py#n289


In what conditions 5) will fail? I see only these cases:
- If data migrations would be introduced and Murano would require some data
in DB to work correctly.

Actually, we already do that. We populate initial set of categories:
http://git.openstack.org/cgit/stackforge/murano/tree/murano/db/migration/alembic_migrations/versions/001_inital_version.py#n40

Would it affect anyone? I don't think so. You always can populate
these categories manually.


- If Murano would use some database-specific features (stored procedures
etc).

That should never happen :)


There are good chances that these cases will never happen in reality, as I
understand, so I tend to agree.

Agree

--
Ruslan

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


--
With kind regards, Andrew Pashkin.
cell phone - +7 (985) 898 57 59
Skype - waves_in_fluids
e-mail - apash...@mirantis.com

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Ruslan Kamaldinov]

On Mon, Jan 26, 2015 at 6:12 PM, Andrew Pashkin  wrote:
> On 26.01.2015 18:05, Ruslan Kamaldinov wrote:
>
> I think it's still important to perform migration specific checks. We want
> to make sure that DB is in expected state after each specific migration.
>
> Why?

1. It's not just the schema we care about. It's the effect of
particular DB migration script on data stored in DB. We need to make
sure that data is not corrupted or lost in any way
2. Some migrations add or remove indexes and constraints, we might
want to test that

Thanks,
Ruslan

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Andrew Pashkin]

On 26.01.2015 18:34, Ruslan Kamaldinov wrote:
> On Mon, Jan 26, 2015 at 6:12 PM, Andrew Pashkin  wrote:
>> On 26.01.2015 18:05, Ruslan Kamaldinov wrote:
>>
>> I think it's still important to perform migration specific checks. We want
>> to make sure that DB is in expected state after each specific migration.
>>
>> Why?
> 
> 1. It's not just the schema we care about. It's the effect of
> particular DB migration script on data stored in DB. We need to make
> sure that data is not corrupted or lost in any way
> 2. Some migrations add or remove indexes and constraints, we might
> want to test that
Ok, I got it =)


-- 
With kind regards, Andrew Pashkin.
cell phone - +7 (985) 898 57 59
Skype - waves_in_fluids
e-mail - apash...@mirantis.com

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Andrew Pashkin]

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.

Another possible solution is to drop all current migrations and
introduce new one with correct names.
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 - apash...@mirantis.com

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Mike Bayer]

Andrew Pashkin  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 - apash...@mirantis.com
> 
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

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

[Andrew Pashkin]

Mike Bayer wrote:
> 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).
With your solution it is still will be necessary for developers
to guess constraints names when writing new migrations. And it will
be even harder, because they will need also to handle case of
"naming conventions".

Mike Bayer wrote:
> However, it’s probably worthwhile to introduce a migration that does
> in fact rename existing constraints on MySQL and Postgresql.
Yes, that's what I want to do in case of the first solution.

Mike Bayer wrote:
>> Another possible solution is to drop all current migrations and
>> introduce new one with correct names.
> you definitely shouldn’t need to do that.
Why?

On 30.01.2015 22:00, Mike Bayer wrote:
> 
> 
> Andrew Pashkin  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/la

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

[Mike Bayer]

Andrew Pashkin  wrote:

> Mike Bayer wrote:
>> 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).
> With your solution it is still will be necessary for developers
> to guess constraints names when writing new migrations. And it will
> be even harder, because they will need also to handle case of
> "naming conventions”.

there’s always a naming convention in place; all databases other than SQLite 
produce them on the fly if you don’t specify one.  The purpose of the 
Alembic/SQLAlchemy naming_convention feature is so that you have *one* naming 
convention, rather than N unpredictable conventions.   I’m not sure if you’re 
arguing the feature should not be used.  IMHO it should definitely be used for 
an application that is deploying cross-database.  Otherwise you have no choice 
but to hardcode the naming conventions of each target database individually in 
all cases that you need to refer to them.




> 
> Mike Bayer wrote:
>> However, it’s probably worthwhile to introduce a migration that does
>> in fact rename existing constraints on MySQL and Postgresql.
> Yes, that's what I want to do in case of the first solution.
> 
> Mike Bayer wrote:
>>> Another possible solution is to drop all current migrations and
>>> introduce new one with correct names.
>> you definitely shouldn’t need to do that.
> Why?
> 
> On 30.01.2015 22:00, Mike Bayer wrote:
>> Andrew Pashkin  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 

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

[Andrew Pashkin]

Mike Bayer wrote:
> there’s always a naming convention in place; all databases other than
> SQLite produce them on the fly if you don’t specify one.  The purpose
> of the Alembic/SQLAlchemy naming_convention feature is so that you
> have *one* naming convention, rather than N unpredictable conventions.
> I’m not sure if you’re arguing the feature should not be used.  IMHO
> it should definitely be used for an application that is deploying
> cross-database.  Otherwise you have no choice but to hardcode the
> naming conventions of each target database individually in all cases
> that you need to refer to them.
You can't just bring SA/Alembic "naming conventions" into the project,
because they will collide with auto-generated constraint names.

So you need to hardcode reverese-engineered constrants names into the
old migrations and then add new migration that renames constraint
according with "naming conventions". OR you need to drop old
migrations, and create new one with "naming conventions" - that will
be backward incompatible, but cleaner.

On 03.02.2015 18:32, Mike Bayer wrote:
> 
> 
> Andrew Pashkin  wrote:
> 
>> Mike Bayer wrote:
>>> 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).
>> With your solution it is still will be necessary for developers
>> to guess constraints names when writing new migrations. And it will
>> be even harder, because they will need also to handle case of
>> "naming conventions”.
> 
> there’s always a naming convention in place; all databases other than SQLite 
> produce them on the fly if you don’t specify one.  The purpose of the 
> Alembic/SQLAlchemy naming_convention feature is so that you have *one* naming 
> convention, rather than N unpredictable conventions.   I’m not sure if you’re 
> arguing the feature should not be used.  IMHO it should definitely be used 
> for an application that is deploying cross-database.  Otherwise you have no 
> choice but to hardcode the naming conventions of each target database 
> individually in all cases that you need to refer to them.
> 
> 
> 
> 
>>
>> Mike Bayer wrote:
>>> However, it’s probably worthwhile to introduce a migration that does
>>> in fact rename existing constraints on MySQL and Postgresql.
>> Yes, that's what I want to do in case of the first solution.
>>
>> Mike Bayer wrote:
 Another possible solution is to drop all current migrations and
 introduce new one with correct names.
>>> you definitely shouldn’t need to do that.
>> Why?
>>
>> On 30.01.2015 22:00, Mike Bayer wrote:
>>> Andrew Pashkin  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 
>>> con

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

[Mike Bayer]

Andrew Pashkin  wrote:

> Mike Bayer wrote:
>> there’s always a naming convention in place; all databases other than
>> SQLite produce them on the fly if you don’t specify one.  The purpose
>> of the Alembic/SQLAlchemy naming_convention feature is so that you
>> have *one* naming convention, rather than N unpredictable conventions.
>> I’m not sure if you’re arguing the feature should not be used.  IMHO
>> it should definitely be used for an application that is deploying
>> cross-database.  Otherwise you have no choice but to hardcode the
>> naming conventions of each target database individually in all cases
>> that you need to refer to them.
> You can't just bring SA/Alembic "naming conventions" into the project,
> because they will collide with auto-generated constraint names.

I was proposing a way to fix this for the murano project which only appears to 
have four migrations so far, but with the assumption that there are existing 
production environments which cannot do a full rebuild.

> 
> So you need to hardcode reverese-engineered constrants names into the
> old migrations and then add new migration that renames constraint
> according with "naming conventions”.
> OR you need to drop old
> migrations, and create new one with "naming conventions" - that will
> be backward incompatible, but cleaner.


My proposal was to essentially do both strategies.   Build out fully clean 
migrations from the start, but also add an additional “conditional” migration 
that will repair a Postgresql / MySQL database that is already at the head, and 
is detected as having the older naming convention.  Because openstack does not 
appear to use offline migrations, this would be doable, though not necessarily 
worth it.

If Murano can afford to just restart with clean migrations and has no 
production deployments yet which would be disrupted by a full rebuild, then 
sure, just do this.





> 
> On 03.02.2015 18:32, Mike Bayer wrote:
>> Andrew Pashkin  wrote:
>> 
>>> Mike Bayer wrote:
 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).
>>> With your solution it is still will be necessary for developers
>>> to guess constraints names when writing new migrations. And it will
>>> be even harder, because they will need also to handle case of
>>> "naming conventions”.
>> 
>> there’s always a naming convention in place; all databases other than SQLite 
>> produce them on the fly if you don’t specify one.  The purpose of the 
>> Alembic/SQLAlchemy naming_convention feature is so that you have *one* 
>> naming convention, rather than N unpredictable conventions.   I’m not sure 
>> if you’re arguing the feature should not be used.  IMHO it should definitely 
>> be used for an application that is deploying cross-database.  Otherwise you 
>> have no choice but to hardcode the naming conventions of each target 
>> database individually in all cases that you need to refer to them.
>> 
>> 
>> 
>> 
>>> Mike Bayer wrote:
 However, it’s probably worthwhile to introduce a migration that does
 in fact rename existing constraints on MySQL and Postgresql.
>>> Yes, that's what I want to do in case of the first solution.
>>> 
>>> Mike Bayer wrote:
> Another possible solution is to drop all current migrations and
> introduce new one with correct names.
 you definitely shouldn’t need to do that.
>>> Why?
>>> 
>>> On 30.01.2015 22:00, Mike Bayer wrote:
 Andrew Pashkin  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 c

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

[Georgy Okrokvertskhov]

I think we should switch to clean migration path. We do have production
installations but we can handle initial db uprgade case by case for
customers. It is better to fix this issue now when we have few customers
rather then doing later at larger scale.

Thanks
Georgy

On Tue, Feb 3, 2015 at 9:05 AM, Mike Bayer  wrote:

>
>
> Andrew Pashkin  wrote:
>
> > Mike Bayer wrote:
> >> there’s always a naming convention in place; all databases other than
> >> SQLite produce them on the fly if you don’t specify one.  The purpose
> >> of the Alembic/SQLAlchemy naming_convention feature is so that you
> >> have *one* naming convention, rather than N unpredictable conventions.
> >> I’m not sure if you’re arguing the feature should not be used.  IMHO
> >> it should definitely be used for an application that is deploying
> >> cross-database.  Otherwise you have no choice but to hardcode the
> >> naming conventions of each target database individually in all cases
> >> that you need to refer to them.
> > You can't just bring SA/Alembic "naming conventions" into the project,
> > because they will collide with auto-generated constraint names.
>
> I was proposing a way to fix this for the murano project which only
> appears to have four migrations so far, but with the assumption that there
> are existing production environments which cannot do a full rebuild.
>
> >
> > So you need to hardcode reverese-engineered constrants names into the
> > old migrations and then add new migration that renames constraint
> > according with "naming conventions”.
> > OR you need to drop old
> > migrations, and create new one with "naming conventions" - that will
> > be backward incompatible, but cleaner.
>
>
> My proposal was to essentially do both strategies.   Build out fully clean
> migrations from the start, but also add an additional “conditional”
> migration that will repair a Postgresql / MySQL database that is already at
> the head, and is detected as having the older naming convention.  Because
> openstack does not appear to use offline migrations, this would be doable,
> though not necessarily worth it.
>
> If Murano can afford to just restart with clean migrations and has no
> production deployments yet which would be disrupted by a full rebuild, then
> sure, just do this.
>
>
>
>
>
> >
> > On 03.02.2015 18:32, Mike Bayer wrote:
> >> Andrew Pashkin  wrote:
> >>
> >>> Mike Bayer wrote:
>  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).
> >>> With your solution it is still will be necessary for developers
> >>> to guess constraints names when writing new migrations. And it will
> >>> be even harder, because they will need also to handle case of
> >>> "naming conventions”.
> >>
> >> there’s always a naming convention in place; all databases other than
> SQLite produce them on the fly if you don’t specify one.  The purpose of
> the Alembic/SQLAlchemy naming_convention feature is so that you have *one*
> naming convention, rather than N unpredictable conventions.   I’m not sure
> if you’re arguing the feature should not be used.  IMHO it should
> definitely be used for an application that is deploying cross-database.
> Otherwise you have no choice but to hardcode the naming conventions of each
> target database individually in all cases that you need to refer to them.
> >>
> >>
> >>
> >>
> >>> Mike Bayer wrote:
>  However, it’s probably worthwhile to introduce a migration that does
>  in fact rename existing constraints on MySQL and Postgresql.
> >>> Yes, that's what I want to do in case of the first solution.
> >>>
> >>> Mike Bayer wrote:
> > Another possible solution is to drop all current migrations and
> > introduce new one with correct names.
>  you definitely shouldn’t need to do that.
> >>> Why?
> >>>
> >>> On 30.01.2015 22:00, Mike Bayer wrote:
>  Andrew Pashkin  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 vers

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

[Adam Young]

Drop.  It is wasting cycles, and not something we should use in 
production.  Migrations specific to SQLPlus are the most time consuming 
work-arounds we have.  SQLPlus does not suit our development approach.




On 02/03/2015 01:32 PM, Georgy Okrokvertskhov wrote:
I think we should switch to clean migration path. We do have 
production installations but we can handle initial db uprgade case by 
case for customers. It is better to fix this issue now when we have 
few customers rather then doing later at larger scale.


Thanks
Georgy

On Tue, Feb 3, 2015 at 9:05 AM, Mike Bayer > wrote:




Andrew Pashkin mailto:apash...@mirantis.com>> wrote:

> Mike Bayer wrote:
>> there’s always a naming convention in place; all databases other than
>> SQLite produce them on the fly if you don’t specify one.  The
purpose
>> of the Alembic/SQLAlchemy naming_convention feature is so that you
>> have *one* naming convention, rather than N unpredictable
conventions.
>> I’m not sure if you’re arguing the feature should not be used. 
IMHO

>> it should definitely be used for an application that is deploying
>> cross-database.  Otherwise you have no choice but to hardcode the
>> naming conventions of each target database individually in all
cases
>> that you need to refer to them.
> You can't just bring SA/Alembic "naming conventions" into the
project,
> because they will collide with auto-generated constraint names.

I was proposing a way to fix this for the murano project which
only appears to have four migrations so far, but with the
assumption that there are existing production environments which
cannot do a full rebuild.

>
> So you need to hardcode reverese-engineered constrants names
into the
> old migrations and then add new migration that renames constraint
> according with "naming conventions”.
> OR you need to drop old
> migrations, and create new one with "naming conventions" - that will
> be backward incompatible, but cleaner.


My proposal was to essentially do both strategies.  Build out
fully clean migrations from the start, but also add an additional
“conditional” migration that will repair a Postgresql / MySQL
database that is already at the head, and is detected as having
the older naming convention.  Because openstack does not appear to
use offline migrations, this would be doable, though not
necessarily worth it.

If Murano can afford to just restart with clean migrations and has
no production deployments yet which would be disrupted by a full
rebuild, then sure, just do this.





>
> On 03.02.2015 18:32, Mike Bayer wrote:
>> Andrew Pashkin mailto:apash...@mirantis.com>> wrote:
>>
>>> Mike Bayer wrote:
 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).
>>> With your solution it is still will be necessary for developers
>>> to guess constraints names when writing new migrations. And it
will
>>> be even harder, because they will need also to handle case of
>>> "naming conventions”.
>>
>> there’s always a naming convention in place; all databases
other than SQLite produce them on the fly if you don’t specify
one.  The purpose of the Alembic/SQLAlchemy naming_convention
feature is so that you have *one* naming convention, rather than N
unpredictable conventions.   I’m not sure if you’re arguing the
feature should not be used.  IMHO it should definitely be used for
an application that is deploying cross-database.  Otherwise you
have no choice but to hardcode the naming conventions of each
target database individually in all cases that you need to refer
to them.
>>
>>
>>
>>
>>> Mike Bayer wrote:
 However, it’s probably worthwhile to introduce a migration
that does
 in fact rename existing constraints on MySQL and Postgresql.
>>> Yes, that's what I want to do in case of the first solution.
>>>
>>> Mike Bayer wrote:
> Another possible solution is to drop all current migrations and
> introduce new one with correct names.
 you definitely shouldn’t need to do that.
>>> Why?
>>>
>>> On 30.01.2015 22:00, Mike Bayer wrote:
 Andrew Pashkin mailto:apash...@mirantis.com>> wrote:

> Working on this issue I encountered another problem.
>
> Most indices in the project has no names and because of that,
> deve