Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Coming back to SQL Server after several years away, I find the Visual Studio Database projects (and now SSDT) very exciting, as they are aiming to offer a way to develop databases declaratively, rather than having to mess around with change scripts.

However, the elephant in the room is the 'data motions' (I'm not too sure whether that's an official term): it's not too hard to automatically script a simple change in one table, but if you're normalising/denormalising, things get hairy very quickly.

Gert Drapers said as much in this TechEd video, and that they're trying to work out a way of working with these complex data migrations too.

The question: This got me thinking. How could you do this? Is there a way - even in theory - to define these data migrations declaratively? Or even a non-declarative way which could be combined elegantly with the declarative bits? Does anything like this already exist in other frameworks (I've vaguely heard something about Ruby/Rails?)

(I wondered whether to post this on dba, but I'm not sure whether they like 'good subjective questions (if this is one), feel free to migrate if necessary)

share|improve this question
    
I am pretty sure there must exist some sort of research about this topic (since it is an old one), though I don't know any references. But having developed some real-world databases with the need of structural changes from release to release, I guess there will be no general solution. –  Doc Brown Aug 11 '12 at 10:05
    
Typical changes may be simple, so the data migration could be defined declaratively, I am sure, but the requirements can also become quite complex, so the "data motion" will need a special in the processing, manual refinement or performance optimization. –  Doc Brown Aug 11 '12 at 10:08
    
You may want to take a look at: bartling.blogspot.com/2011/02/agile-database-modeling.html –  Emmad Kareem Aug 11 '12 at 11:04
    
@EmmadKareem: nice link. Having a short look into features of Liquibase (the first tool mentioned in your link), it shows that the feature for non-trivial "data motions" contained in that tool is "apply a custom SQL". Is that a "declarative solution"? I am not sure. –  Doc Brown Aug 11 '12 at 11:36
    
@DocBrown, you may be right. I was not sure of what input could represent a valid declarative specification. Most of the terms in this post are a bit vague to me to be honest. –  Emmad Kareem Aug 11 '12 at 11:44

2 Answers 2

After searching a little bit, I found this patent reference from 2002 for a "Declarative data transformation engine":

http://www.google.com/patents/US20070220022

(Disclaimer: that kind of patents are the reason why I think the american software patent system is totally flawed and needs to be changed).

However, I don't know if someone made a product based on this patent for the usage you described above. In theory, that should be possible, by performing those steps:

  1. create a new database schema as a copy of your existing one (version 1)
  2. apply schema transformations to transform it into "version 2"
  3. apply data migration from database version 1 into database version 2
  4. replace database version 1 by version 2

If this is a good idea to solve your "data movement" problem this way, depends a lot on your circumstances and your database system, since it involves making a whole copy instead of applying the migration in-place. Where it works, you can solve step 3 by any "Extract-Transform-Load" tool available on the market. AFAIK, tools like Microsoft SSIS or Oracle ODI seem to offer declarative definitions of transformation rules as well as non-declarative.

share|improve this answer
    
+1, very interesting. –  Emmad Kareem Aug 11 '12 at 11:27

If you're using Entity Framework Code first to manage your database schema, there is a migrations api (similar to Ruby on Rails' migrations). Although you are using C#, you essentially "declare" the migrations in code. And the framework handles the mechanics for you. In many cases, just the act of creating a new migration snapshot is enough for the system to automatically discover and make the migration work for you. Here are some references that discuss how it works

Visual Studio Magazine Article

ADO.NET Team Blog

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.