Thanks for your reaction.
It's indeed not that easy, and I'm kind of worried about this.
I guess making upgrade scripts with the lock you wrote about seems the
easiest solution for now...
Thanks,
Pieter
"Ryan Schmidt" <subversion-2007b@ryandesign.com> wrote in message
news:C44897CD-BF83-471E-B619-61CE1332F1D9@ryandesign.com...
> 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 Fri Jul 20 16:55:05 2007