Options
Table Refactoring with SQL Source Control?
jlowry
Posts: 4
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.
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.
Comments
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
Product Manager
Redgate Software
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