I have been reading about this topic lately. I was not exactly looking for that particular expression when I found myself reading about it. I was actually researching a way of "Database Versioning". We all ( I hope) know how important source code versioning is, but not many of us actually consider database versioning. Maybe it is due to the fact that many developers do "up front design", and find that changing the database schema is not natural and should not be done. Maybe they also feel that it is their fault :) who knows. I never considered such a thing until I decided I wanted to evolve. The need is obvious because database schemas (as well as configuration data) do change during the life of a project and it is important to know what happened when and be able to apply those changes to a "base line". We need to do that in order to synchronize our new "Source Code" with our new "Database Schema". This problem might not be obvious to those using a shared database among the team, so the database is always up to date for all, but when you come across concepts like Branching and Merging things are not so simple anymore. Let us take an example: I have the X-Project (cool name no?) and I am developing along side my colleague. Now I make changes to the project that require me to also change a certain Table (add a column for example). So I do exactly that, now at the same time I recall that I have a release of this X-Project called X-Project 1.0 and it has its database working fine. I also notice that my latest change (adding the column) needs to be done for the release as a patch or bug fix, so what do I do. Using the Development Database for the 1.0 release will break the application because since the release of X-Project 1.0 (a couple of months ago) I have done a lot of changes to the schema. But I still need that latest change, so I could just do the same work again (since I still remember what I did a 1 minute ago). But now let us change the variables, say the 1 change I did is more (X-Changes), and the 1 minute is now X-Weeks, will this still work? This change will be forgotten because it is not tracked (at least not easily, since someone can be writing down every move they make on the database, if you know someone like that tell me).
So what is the solution? Actually this turns out to be a not so simple problem. A lot of talk is out there about it http://www.codinghorror.com/blog/archives/001050.html, http://www.oratransplant.nl/2005/08/03/version-control-of-database-objects/, etc...
I am still reading about this, and as a first idea (from the reading I have done), I could use change scripts that describe the changes I have done at every step, commit them to source control, and when I need to peek back I can. Also, using Toad for Oracle, or (not at all free) tools for SQL Server, I could do changes directly to the schema (using GUI, without manual scripting) then compare the new Database to the old one (which means I have to backup the old one first) and generate the changes using the tool. But this last method is resource consuming (since I have to do a full backup of the DB every step of the way), and now that I have thought about it, it does not seem that practical.
These are just my not so organized thoughts on the matter, I will keep on researching and update this as I go (organize it as well).
So what is the solution? Actually this turns out to be a not so simple problem. A lot of talk is out there about it http://www.codinghorror.com/blog/archives/001050.html, http://www.oratransplant.nl/2005/08/03/version-control-of-database-objects/, etc...
I am still reading about this, and as a first idea (from the reading I have done), I could use change scripts that describe the changes I have done at every step, commit them to source control, and when I need to peek back I can. Also, using Toad for Oracle, or (not at all free) tools for SQL Server, I could do changes directly to the schema (using GUI, without manual scripting) then compare the new Database to the old one (which means I have to backup the old one first) and generate the changes using the tool. But this last method is resource consuming (since I have to do a full backup of the DB every step of the way), and now that I have thought about it, it does not seem that practical.
These are just my not so organized thoughts on the matter, I will keep on researching and update this as I go (organize it as well).
3 comments:
You know it isn't very hard to keep a copy of the initial script for tables and put all the alter column statements in there that you make. I do an if exists on the column before the alter too so it can always be used on any version of the DB. Procs,views,functions are even easier just edit away and keep that copy in source control as well.
My approach is to keep all database changes in a script in version control -- the deployment process (first to the test server, then to the production server) is automated, and runs the update script. Live changes to the database structure aren't allowed (test or prod), so if you make some local DB changes as part of implementing a new feature, it'll break on the test server.
The db_changes.sql script in SVN gets cleared and committed after deployment to production.
Putting your changes into the script isn't a hard habit to adopt, particularly once you've forgotten a few times and crashed your tests.
I find it helps to use some tools. ER Studio can emit ddl for building an entire schema (which goes in source control), and more importantly, it can build alter scripts via compare dev to prod at the end of an iteration.
Doesn't work with mysql welll though.
James
Post a Comment