You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@subversion.apache.org by Pieter <pi...@hotmail.com> on 2007/07/13 10:11:21 UTC
OT: Database Versioning
Hi,
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.
Any help will be really appreciated, thansk a lot in advance,
Pieter
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: OT: Database Versioning
Posted by Pieter <pi...@hotmail.com>.
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" <su...@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
Re: access granularity
Posted by br...@3dsite.com.
Sorry to all.
I sent his from another mail account a little while back and is a
repeat of what I already posted.
-Brian
On Jul 17, 2007, at 1:31 PM, Mark wrote:
> This sounds more like a web server authz setup issue than subversion.
> In item 1, do you mean committing instead of updating? or by updating
> do you mean any kind of read?
>
> On 7/15/07, brian@3dsite.com <br...@3dsite.com> wrote:
>> Hi all,
>>
>> I'm trying to set one of my repositories to be;
>>
>> 1) no auth required when updating from a particular subnet via https.
>> 2) auth required when commiting regardless of subnet via https.
>>
>> Currently my repos require uname/pass for any kind of access.
>>
>> I'm reading the svn book but am not really finding what I need as
>> this seems more of an apache config issue I guess.
>>
>> Any clues would be appreciated.
>>
>> Thanks in advance,
>>
>> -Brian
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
>> For additional commands, e-mail: users-help@subversion.tigris.org
>>
>>
>
>
> --
> Mark
> "Blessed is he who finds happiness in his own foolishness, for he will
> always be happy."
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: access granularity
Posted by Mark <ma...@mitsein.net>.
This sounds more like a web server authz setup issue than subversion.
In item 1, do you mean committing instead of updating? or by updating
do you mean any kind of read?
On 7/15/07, brian@3dsite.com <br...@3dsite.com> wrote:
> Hi all,
>
> I'm trying to set one of my repositories to be;
>
> 1) no auth required when updating from a particular subnet via https.
> 2) auth required when commiting regardless of subnet via https.
>
> Currently my repos require uname/pass for any kind of access.
>
> I'm reading the svn book but am not really finding what I need as
> this seems more of an apache config issue I guess.
>
> Any clues would be appreciated.
>
> Thanks in advance,
>
> -Brian
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org
>
>
--
Mark
"Blessed is he who finds happiness in his own foolishness, for he will
always be happy."
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
access granularity
Posted by br...@3dsite.com.
Hi all,
I'm trying to set one of my repositories to be;
1) no auth required when updating from a particular subnet via https.
2) auth required when commiting regardless of subnet via https.
Currently my repos require uname/pass for any kind of access.
I'm reading the svn book but am not really finding what I need as
this seems more of an apache config issue I guess.
Any clues would be appreciated.
Thanks in advance,
-Brian
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: OT: Database Versioning
Posted by Ryan Schmidt <su...@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