Table Refactoring with SQL Source Control?

jlowry

Probably the biggest problem that we’re running into is when we need to refactor a table.

Say for instance I have a set of address fields in a contacts table that I want to refactor out into their table so that contacts can have multiple addresses. I want to retain the data that's currently entered for each contact by migrating it to the new structure.

What ultimately needs to happen in production:

1. Create the new structure.
2. Migrate the address data from the old fields to the new table.
3. Drop the old fields.

SSC only captures the create and the delete, there's no convenient way to inject the migration script into the works. Since this is not simply static data, but a dynamic migration script, SQL Data Compare can't be used to check in the changes.


Less than ideal workarounds:

* Save the create, migrate, delete statements into one big script. Don't check the changes into source control. Apply this script completely independently of source control to each development and production database. When it's all said and done and everyone has the change, check the change into source control.

* Create the new structure, check it into source control. Create the migration script and apply it to all development and productions databases, but only after everyone has checked out the latest version of the database from source control. At some point in the future, drop the old columns.

slaphead99

My understanding is that refactoring schemas is well beyond the scope of Source control. There are other tools that help you do this (MapForce from Altova.com seems to be suited to this purpose). However, once you do have a new schema designed, you can obviously use source control to ensure you can revert back if ever it is necessary.

jlowry

At my office, we're using emergent design principles, as such it's quite common to need to perform small modifications like this as we go.

David Atkinson

jlowry wrote:

At my office, we're using emergent design principles, as such it's quite common to need to perform small modifications like this as we go.

If you've got any ideas on how you think a tool like SQL Source Control should fix this sort of issue, we'd love to hear from you. Some refactorings are necessarily going to be beyond what the tool can do and manual scripts will need to be fashioned. Whether there's some way of associating this with SQL Source Control so that it is automatically deployed as part of a 'get latest' is an interesting idea, and no doubt fraught with challenges.

David Atkinson
Product Manager
Red Gate Software

d.roberts

some pre and post change sql script might be a way of handling these data migration challenges. it would only work if the addition of new tables/columns was done as one change, the data migration script done as a change after that, and the drop of the old tables/columns done after that.

Just need to be able to insert a change in to SSC that is a manual data migration script, to be run after a particular version is deployed.

simple example:

Changeset4: add new column B to table X
C5: drop column A

associate UPDATE Script with Changeset4, eg:
UPDATE X SET B = A +1