In some work I've done for a client (SQL Server backend, not using .NET), there are three different mechanisms to handle this. They all share a concept -- there is an in-memory representation of the database schema. The details of the representation are different.
There are routines that, given the desired database schema in its in-memory format, figure out what changes need to be made (what tables need to be created, what columns need to be added or modified, what indexes need to be created or removed, etc) and then make them. As the programmer of these tools, I jokingly call my users Captain Picard. (They get to say "make it so" to my tool.) The most general (newest) of these has defined an XML representation of the schema. One reason things can work this way is that application code is not supposed to presume that it knows all the columns in a table; this is so that end-users can add columns to any table, and in some apps can create their own tables. Also, "orphaned" tables and columns are very low-cost, so you need to go out of your way (setting boolean 'keepothertables' or 'keepothercolumns' attributes to the non-default value of False) to have tables and columns not referenced in the schema passed to the "make it so" routine go away. Stored procs, views, and triggers are all built in code, and the code that does so is re-executed when the schema changes. (I highly recommend the latter technique, regardless of what else you do. It makes objections like "we can't make that column wider; too many stored procs would need to be manually edited" go away completely.) The code that does this is not (as yet) available in .NET, and is specific to SQL Server. It seems that a web service could be created that would be passed connection info (ODBC is what we're using now) and the XML version of the desired schema, and it would "make it so." (It could also return a script that would do the work, rather than doing the work.) Good luck... At 09:54 AM 4/21/2004, Gary Davidson wrote >Are there any Continous Integration shops on the list? How do you handle >database changes? > >I am working toward having my team use continous integration as part of our >move to AGILE programming methodologies. > >The one problem I can't seem to solve (i.e make everyone happy) is changes >to the database. Currently eveyone has their own local db and distributes >scripts via email and the developers don't like this and want to all develop >on a "Central Database". > >I had to change from this because I was having problems with scripts being >left out of SS and when it came time to build to production It was a >nightmare of scripts and diffs and late nights and a very pissed off wife. > >I am not afraid, except for the wife :) to make an SOP and enforce it, I am >just looking for the best solution. > >Very soon the source build will be automated so I want to solve the db part. > >Any ideas or help will be appreciated > >Gary Davidson J. Merrill / Analytical Software Corp =================================== This list is hosted by DevelopMentor� http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
