Link/Unkink Static Views?

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

Link/Unkink Static Views?

Postby JackAce » Wed Jul 27, 2011 9:14 pm

Is there a way to version control the content in views using SSC?

I found a way to create synchronization scripts between views using the SQL Data Compare GUI, but I am not sure whether it is possible using SQL Source Control.
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm

Postby aolcot » Mon Aug 01, 2011 12:02 pm

Unless I have misunderstood your question, but wouldn't you want to version control the data within the base tables instead seeing as a view is just something referencing those base tables.
aolcot
 
Posts: 25
Joined: Tue Jun 28, 2011 3:13 pm

Postby james.billings » Mon Aug 01, 2011 6:13 pm

As aolcot said, I'd expect you to simply source control the table data that the view references. Is there a specific reason you'd like to source control the view interpretation of this?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Re:

Postby JackAce » Mon Aug 01, 2011 10:05 pm

james.billings wrote:As aolcot said, I'd expect you to simply source control the table data that the view references. Is there a specific reason you'd like to source control the view interpretation of this?


Well, perhaps I should have explained a little more.

We have a table that has system-defined lookup data as well as user-defined data. Not the greatest database design, I agree, but it is a design that I have inherited.

So the underlying table looks like this
Code: Select all
MyID    BIGINT    IDENTITY(1, 1),    -- Primary Key
MyCode   VARCHAR(50),    -- Unique values when UserID is NULL
UserID    BIGINT    NULL,
MyDescription    VARCHAR(200)    -- Data that we are concerned with
-- other columns go here



The view does not contain the Primary Key and is schema bound. It also filters out records where UserID is not NULL. In the view, there is a unique constraint/index defined for the MyCode field.

We rely on the MyCode field to identify records that we need. The underlying Primary Key values may be different in Development, QA, Staging and Production.

So if we want to version control the content that we care about (values in the table where the UserID is NULL), then I figured that we could do it by versioning the content in the views.

If I pull up the SQL Data Compare GUI and allow views to be shown, it gives me the option of synchronizing the views. This works fine. If I create a record in the table that has MyID of 10 and MyCode of 'happy birthday', then a record will be created in the other table with MyID of 101 and MyCode 'happy birthday', assuming that 101 is the next IDENTITY record in the table. If you update MyDescription in the first table to 'You live in a zoo' for the 'happy birthday' record, then the corresponding record in the second table will also update, even if the underlying MyID value is different.

The problem is that there is no way to detect/commit changes to source control when changes are made to the data.

I wouldn't be surprised to hear that I am going about this in a very backwards way. If there is a more elegant solution (besides putting the data in a separate table), I'd love to hear it.
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm

Postby james.billings » Tue Aug 02, 2011 12:40 pm

Thanks for the clarification.

I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control lives in its own table, and isn't "mixed" in with other records- so the short answer is you'd need to split it out.

We can certainly look at implementing source-controlling views, but I'm not sure as to the complexity it would involve. I'd suggest you add it as a request over on our Uservoice so that we can gauge the interest from other users to see how in-demand this would be.

Thanks!
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Re:

Postby JackAce » Tue Aug 02, 2011 8:41 pm

james.billings wrote:Thanks for the clarification.

I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control lives in its own table, and isn't "mixed" in with other records- so the short answer is you'd need to split it out.

We can certainly look at implementing source-controlling views, but I'm not sure as to the complexity it would involve. I'd suggest you add it as a request over on our Uservoice so that we can gauge the interest from other users to see how in-demand this would be.

Thanks!


Yes, I understand that this is probably an unusual scenario that is spurned by a questionable design pattern, so I'll probably be a lone voice. In any case, I'll submit the request shortly.

Moving forward, we'll probably refactor the single table into two tables.
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm


Return to SQL Source Control 2

Who is online

Users browsing this forum: No registered users and 1 guest