I am a build engineer and responsible for our source control layout. We need to keep a version of all database objects and also group changes together with rollback scripts for pushes to production. We have development, QA, and Production environments and there are different versions of the database objects in each. One area we have trouble is with table creates vs alters. Developers will check in alters but to recreate the database we need the creates. Any ideas would be appreciated.
+1
A:
You should look into how frameworks like rails handle migrations
http://dizzy.co.uk/ruby_on_rails/cheatsheets/rails-migrations
marcgg
2009-06-05 19:46:38
I am not really looking for ways to gen the database just ways to version and track what is happening with it.
Jeremy E
2009-06-05 19:59:19
If you use migrations, everything would be put into subversion and you will be able to use that to track everything. Plus it's a very cool way to handle DB changes. I don't think I understand totally your issues thought
marcgg
2009-06-05 20:48:02
+2
A:
This article should answer most if not all questions
http://odetocode.com/Blogs/scott/archive/2008/01/30/11702.aspx
Jens Schauder
2009-06-05 19:52:31
This article basically re-asks my question section 3 is what I am trying to accomplish. I am looking for a structure and/or process that allows me to version my databases and meet the other constraints of my question. Thanks for the article tho.
Jeremy E
2009-06-05 19:58:01
This site does hold a wealth of information on issues related to versioning it takes a while to peice it together but is worth the look. Thanks
Jeremy E
2009-06-09 12:50:55
+3
A:
I'm a huge fan of http://www.liquibase.org/. It allows you to track versions of your database, including the ability to "update" and "rollback" your schema/data.
They have a nice XML syntax that has baked in support for common refactorings, including ones detailed in http://databaserefactoring.com/
They also have Eclipse plugins to help create the change scripts, and ant task's for integrating into your build.
My only complaint is that it is Java based and I'm fond of installing java on my build server for .NET projects. In that case I've heard good things about http://code.google.com/p/migratordotnet/.
nikmd23
2009-06-05 19:58:06
I voted this up because I like the link to migratordotnet. I will have to look into that especially the MSBuild Task. Thanks
Jeremy E
2009-06-05 20:03:06
A:
Have you checked this previous question? http://stackoverflow.com/questions/778317/need-a-better-way-to-manage-database-schema-changes
Dwight T
2009-06-05 20:34:59
A:
I can recommend you such scenario:
- Dbunit for data population (e.g. dictionaries, masterdata and other critical database information)
- Liquibase of dbdeploy for database refactoring. In my mind liquibase is more refactoring oriented tool and dbdeploy - more process oriented (i.e. you have to do more by hands, but it simplify development, testing and production support process).
If you company are using ORM, you can create database schema "diff" on the fly based on the ORM engine. Besides, if your product works with different databases, you'll have additional level of complexity. Unfortunately, neither dbdeploy, nor liquiase satisfied me for multy-database development/refactroing.
FoxyBOA
2009-06-06 05:35:30