Linking the static data with SQL Source Control

BrandonGalderisi · July 23, 2010 1:46PM

I want to link tables which contain only static data to SQL Source Control. How may I do this?

StephanieHerr · July 23, 2010 2:44PM

Hello,

We don't have this feature implemented yet, but there is a workaround using SQL Data Compare Pro, http://www.red-gate.com/supportcenter/C ... .0/toc.htm.

Please let us know how you get on with this and if this is acceptable for now? How often does your static data change? Do you have any ideas on how you would like this integrated into SQL Source Control (e.g., right-click on a table and say source control this table's data, do you need filtering options, e.g., only source control rows 5 - 20)?

Please vote/comment on this feature on our Suggestion Forum at
https://redgate.uservoice.com/forums/39 ... ?ref=title.

BrandonGalderisi · July 26, 2010 8:05AM

For me, a table either does, or does not, contain static data. But I can see the value for others in being able to script only certain data.

As for whether this will work or not, it's quite cumbersome compared to SQL Source Control but I will give it an honest chance before dismissing it.

I would love to see it as a right click option with filtering.

David Atkinson · July 26, 2010 10:12AM

Thanks for giving it a go. Yes, we recognise that it is more cumbersome that it needs to be, but we couldn't justify putting this in v1.0 in place of other 'essential' features that didn't have workarounds.

Thanks for bearing with us. Any other feedback you might have is most welcome.

Regards,

David Atkinson
Product Manager
Red Gate Software

mcnamaragio · August 17, 2010 3:50PM

It would be nice if the static data support is added to the product. Also, it would be nice if it was possible to choose which part of the static data should go under source control.

For example,
Data that comes with default installation of my program should be source controled but the data that developer inserts through the program to check his work should not. Basically, if the data is inserted/updated/deleted from ssms then it should be in source control otherwise it should not. It would be nice if you included similar feature in the program.

David Atkinson · August 17, 2010 4:28PM

We're in the process of designing this feature so your feedback is very useful.

Can I ask what columns exist in your static data table? What distinguishes the default installation data compared to the developer's test data? We are considering allowing a WHERE clause to be specified to designate which part of a table is the actual static data.

If you could email me a sample static data table, this would be useful.

David (David.Atkinson at red-gate.com)

mcnamaragio · August 17, 2010 5:09PM

I guess I didn't explain it well.

There is two kinds of static data in the database:

1. The static part of the data that comes with the installation and cannot be edited/added/deleted by the user. This should be in source control. If the developer makes changes to the data with ssms it should be reflected in source control. It can be solved with sql data compare as it is possible to limit what to synchronize

2. Preconfigured values that can be changed. For example default data for a dropdownbox. The user can add/edit/delete data from the program.

This should be in source control. However when a developer modifies some data to it through the program it shouldn't go in source control. A possible solution can be to have a isdefault column which distinguishes between default and user data. The problem with this approach is that if preconfigured record is modified or deleted from the program (so isdefault is true) it should not be changed in the source control.

Thanks.

David Atkinson · August 17, 2010 5:55PM

1. In terms of limiting what to synchronize, how would you need to do this? Would you need to explicitly select the records within a table that you want to source control, or would you need to use a WHERE clause to specify it?

2. If a developer modifies static data records on his own development database that has previously been committed to source control, they need not commit these new changes to source control. Like any change made on the developer's personal development environment, it doesn't go into source control unless the developer commits it. If the developer has changed something just for a test, they will be able to revert back to this afterwards using the 'undo' function.

David

Simon Quin · August 18, 2010 8:21AM

@David

We would be very interested having static and default data under source control. Our requirements would be along the lines of:

1. Entire tables that contains system defined static data and can never be changed by the end-user.

2. Default data entries that should always be present but can be changed by the end-user: these typically have an internal value that cannot be changed but some other value that can (e.g. a description of a status in a list). Here a WHERE clause wouldn't suffice for an entire table, individual internal values would need to be selected.

3. Default data items that provide the core of values that cannot change but that can be added to by the end user. These would normally have a flag set against system defined values. These could use a WHERE clause to separate the sets.

Hope this is useful.
Simon

jlowry · August 19, 2010 12:51PM

David Atkinson wrote:

1. In terms of limiting what to synchronize, how would you need to do this? Would you need to explicitly select the records within a table that you want to source control, or would you need to use a WHERE clause to specify it?

For us, an example would be adding permissions and menu structure for a particular module that we're working on. If I'm working on 2 modules at once, then I need to be able to individually select the rows that need to be checked in on this commit. We like to make our commits as atomic as possible with regards to the issue that's being solved, so if a row in the static table isn't relevant to the issue, then it should be omitted for this check-in.

David Atkinson wrote:

2. If a developer modifies static data records on his own development database that has previously been committed to source control, they need not commit these new changes to source control. Like any change made on the developer's personal development environment, it doesn't go into source control unless the developer commits it. If the developer has changed something just for a test, they will be able to revert back to this afterwards using the 'undo' function.
David

Perfect!

Linking the static data with SQL Source Control

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk