On Jul 13, 2007, at 05:11, Pieter wrote:
> My apologizes for this off-topic post, but I jsut think I will find
> here the
> people that know the answer to my questions.
>
> I want to start using version control for my databases too, but I
> don't
> really know how I could do his the best. I see somehow 2 possbilities:
>
> 1. Scripting everytime the whole DataBase. But:
> - Using which Tool to do this? Preferably a free (!) opensource tool?
> - What if a database change forces the data to be migrated? For
> instances
> one table is splitted in 2, and the data has to be copied and
> transformed:
> How to do this?
>
> 2. Adding a migration script for each version in which every
> developer adds
> the script for the changed object + script for migrating data
> - What if 2 developers add something at the same time to this
> script? This
> will cause conflicts, with potential lose of made changes?
>
> One very important note: The solution must be very straightforard,
> very
> easy, or things will explode here. I have some special kidn of
> developers,
> which can't be compared to the 'normal' developers.
> We use SQL 2000 and 2005.
I don't think you're going to find a simple straightforward solution
to this problem, because the problem is complex and requires each
developer's very careful attention to detail.
Consider files. Subversion cannot "figure out" when a file has been
renamed or moved. Instead, you must inform Subversion of this using
the "svn mv" command instead of your OS rename command.
Similarly, Subversion cannot figure out what you've done to a
database. Consider the case that you have split a table into two
tables and linked them via some key. For example, let's say you used
to have a phone_number field in the contacts table, but now want to
move phone numbers to a separate phone_numbers table to allow an
arbitrary number of phone numbers per contact. To do this, you have a
CREATE TABLE command to set up the structure of the new phone_numbers
table, you probably do an INSERT ... SELECT to get the data from the
contacts table into the phone_numbers table, and then you probably
ALTER the contacts table to remove the old phone_number field.
Subversion cannot detect that these things have taken place, much as
it cannot detect a file rename. However, whereas for files there are
commands like svn mv, there are no svn commands for dealing with the
database so you have to track it manually. I recommend having an SQL
file somewhere in the repository which lists all the SQL commands you
use to transform the database. Whenever you want to deploy a new
version of the software, you then manually run all the SQL commands
in the file that have not yet been run. You may need to include a
movable comment marker in the file to indicate the last command you
ran on the database system. In my case, we had several different
systems, so I used several comment markers in our SQL file, including
in each the name of the database server.
Some advocate creating a second parallel SQL file containing the
reverse operations, so that you can downgrade if you ever need to.
However, it would take much more effort to create and test such a
downgrade script. Also, if something goes wrong, it's likely it was
because of an error in your original upgrade SQL script, and if it
was, it's unlikely you have anticipated such an error in your
downgrade script, which would then likely fail, possibly even causing
data loss.
You may wish to set the SQL file to needing a lock, and/or setting it
so that Subversion does not auto-merge changes, so that a conflict is
guaranteed so that a developer will notice it, rather than a merge
possibly being inadvertently missed. If 2 developers try to change
the file at the same time, the 2nd developer should very carefully
examine the changes from the 1st developer to see if the changes are
compatible. Possibly the two developers should have a discussion
before proceeding.
You could also investigate whether you can develop an automated way
of executing the SQL statements to upgrade the database. This may be
difficult and dangerous, but at least some projects have successfully
implemented it, like the Mantis Bug Tracker and Wikipedia. I believe
part of the strategy is to have a table in the database, or a field
in each database table, where you can store the "version" each table,
and then there's an upgrade script which knows which SQL statements
to run to upgrade the table from any given version to the current one
(but I don't think they offer downgrade functionality). This becomes
more useful the more systems you have on which you need to deploy. In
the case of Mantis and Wikipedia, which are used by many many people
on many many systems, this is very helpful. If the system is only
used by you on one or two servers, it may not be worth the effort.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Mon Jul 16 00:02:48 2007