Red Gate forums :: View topic - Link/Unkink Static Views?
Return to www.red-gate.com RSS Feed Available

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

Link/Unkink Static Views?

Search in SQL Source Control 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JackAce



Joined: 08 Jul 2011
Posts: 37

PostPosted: Wed Jul 27, 2011 9:14 pm    Post subject: Link/Unkink Static Views? Reply with quote

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



Joined: 28 Jun 2011
Posts: 25

PostPosted: Mon Aug 01, 2011 12:02 pm    Post subject: Reply with quote

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



Joined: 16 Jun 2010
Posts: 1121
Location: My desk.

PostPosted: Mon Aug 01, 2011 6:13 pm    Post subject: Reply with quote

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



Joined: 08 Jul 2011
Posts: 37

PostPosted: Mon Aug 01, 2011 10:05 pm    Post subject: Re: Reply with quote

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



Joined: 16 Jun 2010
Posts: 1121
Location: My desk.

PostPosted: Tue Aug 02, 2011 12:40 pm    Post subject: Reply with quote

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



Joined: 08 Jul 2011
Posts: 37

PostPosted: Tue Aug 02, 2011 8:41 pm    Post subject: Re: Reply with quote

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.
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