Eugen Konkov

We often rely on our tools and just deploy new DB versions and move on.

Lets see these simple examples.
Example 1
You have Schema v1 where table's column has the name `X`. At the next Schema v2 instead of it you created column named `Y`.

v1 -> v2
X -> -
- -> Y

So the tool correctly drops the `X` and creates `Y`.
Example 2:
For downgrades it looks the similar:

v2 -> v1
Y -> -
- -> X

Simple! Right??

Example 3
Let's do it in more advanced way. Now instead of create/drop we will rename field:

v1 -> v2
X -> Y{renamed X}

In this scenario SQL:T will detect `renamed` option and will generate `ALTER ...` statements correctly instead of CREATE/DROP one.

Example 4
Let's move to Schema v3 where we create `X` and drop `Y` (like we did in the example 1):

v2 -> v3
Y{renamed X} -> X

So here in the destination Schema v3 column `X` does not have any extra info at its definition (eg. {renamed Y}) thus the tool will just create column X and drop column Y. Nice!

Example 5
But let's double check downgrade direction.
Now the picture will be seen by the tool as next:

v3 -> v2
X -> Y {renamed X}

The tool will detect `{renamed X}` info at the destination and will generate `ALTER ...` statements.

STOP! We did not do any renamings during v2 -> v3 migrations. Hm... this looks suspicious.

Example 6
Ok. Let's check now what happens during v2 -> v1 downgrade.

Here the tool will see it as:

v2 -> v1
Y {renamed X} -> X

Here at the destination Shema v1 column `X` does not any extra info about renamings thus the tool will DROP Y and CREATE X. Right? Technically Yes. Is it correct? NO!!!

We do not expect DATA LOSE at this step, because during upgrade v1 -> v2 we renamed the column X->Y, thus for downgrade we expect it will be renamed back Y -> X.

This is why the notion of direction is important. For downgrades scripts the order of columns are switched and we should analyze the source definition to understand what is going on for this migration.

So for example 6 the tool will understand that Y were renamed from X, thus migration script will generate `ALTER ...` statements.

This is why I had proposed this change: https://github.com/frioux/DBIx-Class-DeploymentHandler/pull/81

And implemented these improvements and bugfixes https://github.com/dbsrgits/sql-translator/pull/188

I want you guys and girls review it, double check it and provide your feedback on this.

Once done I hope then we can merge this fix https://github.com/dbsrgits/sql-translator/pull/184 which will generate the correct downgrade migrations (without unexpeced data loss :) ).