Red Gate forums :: View topic - Table Refactoring with SQL Source Control?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 1
SQL Source Control 1 forum

Table Refactoring with SQL Source Control?

Search in SQL Source Control 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jlowry



Joined: 18 Aug 2010
Posts: 3

PostPosted: Wed Aug 18, 2010 6:09 pm    Post subject: Table Refactoring with SQL Source Control? Reply with quote

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.
Back to top
View user's profile Send private message
slaphead99



Joined: 10 Mar 2010
Posts: 93

PostPosted: Fri Aug 20, 2010 12:24 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jlowry



Joined: 18 Aug 2010
Posts: 3

PostPosted: Mon Aug 23, 2010 2:41 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1122
Location: Twitter: @dtabase

PostPosted: Tue Aug 24, 2010 11:52 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
d.roberts



Joined: 13 Sep 2010
Posts: 8

PostPosted: Mon Sep 13, 2010 5:53 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group