rolling back a view to a previous version of the same view

I'm using FM to deploy a database. As a part of that deploy, I'm rolling out a view. The changes go like this:

R1: create view

R2: no change to view

R3: add a column to the view

R4: no change

R5: remov开发者_Python百科e the column from the view

The view is created by a script that is in source control. Let's say that I deploy R3 and then decide that I should roll back to R2 (I'm thinking of a production site that I don't want to leave in an odd state). So the view's script (drop/create) is in a local file. I can't use that script again because the local version is at R3 but I want it to go back to R1 (or R2).

How can I roll the view back to the previous version reliably using FluentMigrator? The only option I can think of is keeping the view create script in a string in my Migration class. But it seems like I'd need to have every version of it in a string in my class -- very unwieldy and a hard sell to the team.

Every realistic solution to this that I can think of sort of goes against the idea of source control -- having a single version of a thing locally and keeping track of its changes over time.

---

I added this feature into FluentMigrator -- ability to execute a script straight from source. You can get it here:

https://github.com/jcollum/fluentmigrator

Currently only supports SVN. Hasn't been integrated into the main branch yet.

---

One very simple way is to do something like:

if (object_id('dbo.myView', 'V')) is not null
   drop view [dbo].[myView]
go
create view [dbo].[myView] as

select foo, bar
from dbo.Orders

Now, if you need to need to alter that view, you make your changes to this script and deploy them. It'll drop the view if it exists and create it fresh. From a source control standpoint, all you're doing is making changes to a text file so you should be able to store it efficiently, look at the differences pretty simply, etc. In other words, you can do all the things that source control does really well because it's just text.