You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@subversion.apache.org by Gabor Szabo <sz...@gmail.com> on 2005/06/09 17:12:10 UTC
Version control of Databases
When developing an application that uses database an interesting
question arises on how to track the changes you made to the database.
Changes can be in schema (including indexes, user rights),
in meta data (e.g. you might keep the string translations of your
application in an database),
some might even require some changes to the application data.
(e.g. in an application earlier the user table had an e-mail field but
in the new version you
enable unlimited number of e-mails so you moved the e-mail column of
the users table
to a separate table)
Maybe a slightly better explanation of the problem can be see here:
http://www.dragonsoftru.com/prod_dsds_1.php
How do people maintain versions of a database ?
How do you make it easy to upgrade your application (including the database) ?
I guess there might be applications providing some help with this.
What integration exisits with Subversion ?
Gabor
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Frank Gruman <fg...@verizon.net>.
Gabor,
I solve this by keeping all of the database creation/manipulation
scripts in version control as well. So as branches/tags are made, the
scripts are copied from one to the next. So when a do a release build,
I can also include the proper DB changes.
Hope that helps.
Regards,
Frank
Gabor Szabo wrote:
>When developing an application that uses database an interesting
>question arises on how to track the changes you made to the database.
>Changes can be in schema (including indexes, user rights),
>in meta data (e.g. you might keep the string translations of your
>application in an database),
>some might even require some changes to the application data.
>(e.g. in an application earlier the user table had an e-mail field but
>in the new version you
>enable unlimited number of e-mails so you moved the e-mail column of
>the users table
>to a separate table)
>
>Maybe a slightly better explanation of the problem can be see here:
>http://www.dragonsoftru.com/prod_dsds_1.php
>
>How do people maintain versions of a database ?
>How do you make it easy to upgrade your application (including the database) ?
>
>I guess there might be applications providing some help with this.
>What integration exisits with Subversion ?
>
>Gabor
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
>For additional commands, e-mail: users-help@subversion.tigris.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by John Browne <jk...@gmail.com>.
I was doing this with dumpfiles for postgresql, and it seemed to work
fairly well. Here's what/how I did it:
- Live linux server - running postgresql 8.0
- Local windows development box - running postgresql 8.0
- Schema dumpfile for the live server's database was versioned in subversion
- Local windows development box had a working copy of this dumpfile checked out
- Local windows development box had a scheduled task to dump it's
schema, overwriting the working copy dumpfile
So, whenever I made changes to the database schema on the local
windows development box, subversion would be able to show me the
changes in the dumpfile. It seemed to work pretty well, but the
dumpfiles did have a-few minor differences due to the fact one was
running on windows and one was on linux.
On 6/9/05, Gabor Szabo <sz...@gmail.com> wrote:
> When developing an application that uses database an interesting
> question arises on how to track the changes you made to the database.
> Changes can be in schema (including indexes, user rights),
> in meta data (e.g. you might keep the string translations of your
> application in an database),
> some might even require some changes to the application data.
> (e.g. in an application earlier the user table had an e-mail field but
> in the new version you
> enable unlimited number of e-mails so you moved the e-mail column of
> the users table
> to a separate table)
>
> Maybe a slightly better explanation of the problem can be see here:
> http://www.dragonsoftru.com/prod_dsds_1.php
>
> How do people maintain versions of a database ?
> How do you make it easy to upgrade your application (including the database) ?
>
> I guess there might be applications providing some help with this.
> What integration exisits with Subversion ?
>
> Gabor
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Gabor Szabo <sz...@gmail.com>.
For further inspiration I climed over to CM Crossroad and found the following:
Daversy - The DAtabase VERsioning SYstem - Mutating Specs
http://www.cmcrossroads.com/cgi-bin/cmwiki/bin/view/CM/DaversySpecs
Agile Configuration Management Environments
http://www.cmcrossroads.com/newsletter/articles/agilejan04.pdf
There might be further documents there.
Gabor
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Russ Brown <pi...@gmail.com>.
Ryan Schmidt wrote:
> On 14.06.2005, at 09:10, Gabor Szabo wrote:
>
>> Lots of nice comments but we are more and more in the direction of
>> writing the changes in some script file outside the database.
>>
>> In my situation the boss wants to let people use SQL Navigator
>> and sqlplus and probably other tools as well and then somhow
>> (magically ?) create the installation script. The boss says if the
>> way to
>> versioning the database isn't seemless then people won't do it.
>>
>> It is possible that what I need is not a super-duper tool but
>> education so
>> they will understand what they request is not possible.
>> But befor I can do that I have to be convinced that there are no such
>> tools.
>> And they don't care how much does it cost or if it free software or not.
>
>
> Imagine you have a table:
>
> USE `test`;
> CREATE TABLE `foo` (
> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
> `foo` VARCHAR(255) NOT NULL,
> PRIMARY KEY (`id`)
> ) TYPE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
> INSERT INTO `foo` (`id`, `foo`) VALUES
> ('1', 'one'),
> ('2', 'two'),
> ('3', 'three'),
> ('4', 'four');
>
> And now, in order to upgrade this table to the new structure, you need
> to do this:
>
> ALTER TABLE `foo`
> ADD `id2` INT UNSIGNED NOT NULL,
> ADD `bar` VARCHAR(255) NOT NULL;
> UPDATE `foo` SET
> `id2`=`id`*2,
> `bar`=CONCAT(`foo`,'bar');
> ALTER TABLE `foo` DROP `foo`;
>
> I don't know how any tool, given databases in the "before" and "after"
> state, would be able to generate the desired SQL to transform the one
> into the other.
>
> The above tables are of course silly, but consider a more-realistic
> example: table customer has a field phone_number. Now you want to be
> able to enter multiple phone numbers per customer, so you need a new
> table phone_numbers which references table customer by id, and you need
> to migrate the data from field phone_number in table customer to new
> records in table phone_numbers, and then you can delete field
> phone_number in table customers.
>
>
> Versioning the code isn't seamless either. You can't just use your
> usual operating system commands for deleting or removing files and
> expect Subversion to be able to keep up; you have to inform Subversion
> what you're doing, else it's ambiguous. But the benefit of keeping
> track of file moves, adds and deletes is clear, and I think it's the
> same with a database.
>
Agreed, it is an extremely complex problem, and the chances are it's
simply impossible to write a tool or system that covers each and every
possible action that could need to be performed to take a database from
one state to another.
What I'm interested in is a tool that can cover as many of those actions
as possible. Most of the things we're talking about are structural
changes, and I don't think that's too much of a problem. Changes in data
however have meaning attached to them far beyond what any automated tool
could hope to understand or spot. Take your phone number example (I
happen to have actually done exactly that operation myself recently, so
it's a very good real-world example). Unless you were to indicate in
some way that what you are doing is a step that is required to move the
database version from one state to another, there's no way that the
system could cleverly distinguish that series of commands from the
application itself modifying the data.
To be able to do something like that, you'd need to be able to hook into
the database engine itself and add commands that let you declare that
you're making a change that needs to be versioned. Somebody on the
PostgreSQL list brought up the possibility of triggers on the system
catalogue tables, but that would only cover structural changes, isn't
portable and wouldn't work anyway because you can't add triggers on
those tables.
Such a change would be very difficult, and probably impossible on
closed-source database systems.
Still, it's a possibility that I hadn't thought of: building the
versioning client into the database server.
Then again, there's no real reason why that would have to be built into
the server. The client could do that job. You could have a
specially-modified client that knows about database versions and accepts
a couple of new commands that declare the start and end of a database
versioning 'transaction', and then saves the information to the file
that is put into the source control system.
Like you say, when dealing with files and Subversion you don't expect
Subversion to know that new files need adding or that you've copied a
file: you have to tell it. The same could be the case with database changes.
Something more for me to think about. :-)
--
Russ.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Ryan Schmidt <su...@ryandesign.com>.
On 14.06.2005, at 09:10, Gabor Szabo wrote:
> Lots of nice comments but we are more and more in the direction of
> writing the changes in some script file outside the database.
>
> In my situation the boss wants to let people use SQL Navigator
> and sqlplus and probably other tools as well and then somhow
> (magically ?) create the installation script. The boss says if the
> way to
> versioning the database isn't seemless then people won't do it.
>
> It is possible that what I need is not a super-duper tool but
> education so
> they will understand what they request is not possible.
> But befor I can do that I have to be convinced that there are no
> such tools.
> And they don't care how much does it cost or if it free software or
> not.
Imagine you have a table:
USE `test`;
CREATE TABLE `foo` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`foo` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) TYPE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `foo` (`id`, `foo`) VALUES
('1', 'one'),
('2', 'two'),
('3', 'three'),
('4', 'four');
And now, in order to upgrade this table to the new structure, you
need to do this:
ALTER TABLE `foo`
ADD `id2` INT UNSIGNED NOT NULL,
ADD `bar` VARCHAR(255) NOT NULL;
UPDATE `foo` SET
`id2`=`id`*2,
`bar`=CONCAT(`foo`,'bar');
ALTER TABLE `foo` DROP `foo`;
I don't know how any tool, given databases in the "before" and
"after" state, would be able to generate the desired SQL to transform
the one into the other.
The above tables are of course silly, but consider a more-realistic
example: table customer has a field phone_number. Now you want to be
able to enter multiple phone numbers per customer, so you need a new
table phone_numbers which references table customer by id, and you
need to migrate the data from field phone_number in table customer to
new records in table phone_numbers, and then you can delete field
phone_number in table customers.
Versioning the code isn't seamless either. You can't just use your
usual operating system commands for deleting or removing files and
expect Subversion to be able to keep up; you have to inform
Subversion what you're doing, else it's ambiguous. But the benefit of
keeping track of file moves, adds and deletes is clear, and I think
it's the same with a database.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Gabor Szabo <sz...@gmail.com>.
Lots of nice comments but we are more and more in the direction of
writing the changes in some script file outside the database.
In my situation the boss wants to let people use SQL Navigator
and sqlplus and probably other tools as well and then somhow
(magically ?) create the installation script. The boss says if the way to
versioning the database isn't seemless then people won't do it.
It is possible that what I need is not a super-duper tool but education so
they will understand what they request is not possible.
But befor I can do that I have to be convinced that there are no such tools.
And they don't care how much does it cost or if it free software or not.
Gabor
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Daniel Patterson <da...@danpat.net>.
Mark Parker wrote:
>
> We tried something like that. We discovered that mere mortal humans
> don't have the discipline to ensure that what the create-from-scratch
> script makes and what the upgrade-from-previous script mades aren't
> always the same. We ended up with a
> create-from-scratch-and-use-a-sync-tool approach that we're quite happy
> with.
Have you looked at the approach that Bugzilla takes? Basically, there
is one great big "create-database.sh" script which is *all* written
as changes. Last time I looked, a new installation created a minimal
schema, and then applied a whole stack of changes that have accumulated.
Whenever someone wants to make a change to the schema, they add it to
the bottom of the script, along with a test to see if that change
has already been applied. Seems to work well enough. I believe someone
generates a "current schema" for new installations with each release
to, but those schemas are generated at compile time and are not
under version control (they can be generated from the "create-database.sh"
script, or whatever it's called).
There is no ability to downgrade with this setup (yet), but I guess
it could be added.
daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Mark Parker <ma...@msdhub.com>.
Ryan Schmidt wrote:
> Another solution I've thought of is this: SQL statements that belong
> with a commit should be added as a revprop (say "foo:sql") to that
> revision. When you want to deploy a new release of the system, you
> gather, in order, all the foo:sql properties from all those revisions
> and execute the SQL. Ideally you would also store a second property
> (like "foo:sql-undo") which could undo whatever it was that the SQL
> did. This way, if you need to downgrade to an older release, you can
> pull all the foo:sql-undo properties, in reverse order, and get back to
> more or less the state you were in before. Depending on your confidence
> in these SQL statements this could even be automated to a greater or
> lesser degree in your deployment process.
>
> I'm not sure how best to handle SQL statements that do not relate to a
> commit.
We tried something like that. We discovered that mere mortal humans
don't have the discipline to ensure that what the create-from-scratch
script makes and what the upgrade-from-previous script mades aren't
always the same. We ended up with a
create-from-scratch-and-use-a-sync-tool approach that we're quite happy
with.
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Ryan Schmidt <su...@ryandesign.com>.
On 12.06.2005, at 14:30, erymuzuan wrote:
> Yeah you're right, it's not as simple as it seem
I've thought about this too and agree. :-) In particular, using a
tool that compares the "before" state of the database with the
"after" and generates the SQL statements to get from one to the other
is not viable, in the same way that a script to examine your working
copy "before" and "after" and generate the add and delete commands
isn't viable -- the working copy analyzer can't know whether that
unversioned file is new or is a versioned file that was renamed or
copied from somewhere, and the DB analyzer can't know whether that
new field or table is new or whether it was renamed from an existing
one, or if it's new, whether its data was copied from somewhere.
I believe that the developer needs to manually keep track of the SQL
statements necessary to get from A to B. We are currently doing this
in different ways in different projects: in one, we've put all SQL
statements into a single SQL file in the project's root in the
repository. Best idea here I think is to put a text marker into the
file to indicate where each version of the database is. For example,
the structure of the database used by the live version of the system
corresponds to the SQL statements done up to line 200 of the SQL
file, so after line 200, there's a line "-- live server." And on the
development server all the rest of the statements have also been done
already, so at the end of the file, there's another marker "-- dev
server." In another project, we keep the SQL statements in the bug
tracker records for the bugs (or feature requests) they fix (or
implement) -- completely separate from the repository. This has also
worked well.
Another solution I've thought of is this: SQL statements that belong
with a commit should be added as a revprop (say "foo:sql") to that
revision. When you want to deploy a new release of the system, you
gather, in order, all the foo:sql properties from all those revisions
and execute the SQL. Ideally you would also store a second property
(like "foo:sql-undo") which could undo whatever it was that the SQL
did. This way, if you need to downgrade to an older release, you can
pull all the foo:sql-undo properties, in reverse order, and get back
to more or less the state you were in before. Depending on your
confidence in these SQL statements this could even be automated to a
greater or lesser degree in your deployment process.
I'm not sure how best to handle SQL statements that do not relate to
a commit.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by erymuzuan <er...@gmail.com>.
Yeah you're right, it's not as simple as it seem, they are only a few
pointers out there , namely the agile database group. make popular by
ThoughWorks guys , such as Martin Fowler.
We're working on the same thing , but in our case , the primary
database is MSSQL Server. The major problems is recognizing which
objects needed to in version control, properly mapping the
dependencies between objects, schema and test data, Test data is
particularly poses it own set of challenges, cause it also involves
the migration script. We're using a set of tools, namely NAnt and
PsTools, as well as SQL DMO objects.
Then they're thing like the "release stage", bronze, silver or gold,
and how this release are propagated into higher level, then how to
rebuild the gold and copy them across developers machine.
I'm writing this on my blog at
http://www.ivirtualsb.com/cs/blogs/erymuzuan, it's a work in progress.
On 6/12/05, Russ Brown <pi...@gmail.com> wrote:
> Gabor Szabo wrote:
> > On Thu, 9 Jun 2005, Frank Gruman wrote:
> >
> >> Just as a side note - I see this discussion ran around for a while on
> >> this board before. Maybe following the thread will help you find some
> >> answers.
> >>
> >> http://svn.haxx.se/users/archive-2004-05/1453.shtml
> >>
> >
> > Thanks for all the responses.
> >
>
> I'm currently involved with a thread on the PostgreSQL mailing list on
> this very subject, where we've gone into quite a bit of detail about the
> nuances of the problem (it's not as simple as it might first appear to
> be). I'm actually considering starting up a project to solve it in a
> comprehensive manner. You can read more on my blog, which also contains
> a link to the pgsql thread:
>
> http://pickscrape.blogspot.com/
>
> Thanks.
>
> --
>
> Russ.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Russ Brown <pi...@gmail.com>.
Gabor Szabo wrote:
> On Thu, 9 Jun 2005, Frank Gruman wrote:
>
>> Just as a side note - I see this discussion ran around for a while on
>> this board before. Maybe following the thread will help you find some
>> answers.
>>
>> http://svn.haxx.se/users/archive-2004-05/1453.shtml
>>
>
> Thanks for all the responses.
>
I'm currently involved with a thread on the PostgreSQL mailing list on
this very subject, where we've gone into quite a bit of detail about the
nuances of the problem (it's not as simple as it might first appear to
be). I'm actually considering starting up a project to solve it in a
comprehensive manner. You can read more on my blog, which also contains
a link to the pgsql thread:
http://pickscrape.blogspot.com/
Thanks.
--
Russ.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Gabor Szabo <sz...@gmail.com>.
On Thu, 9 Jun 2005, Frank Gruman wrote:
> Just as a side note - I see this discussion ran around for a while on this
> board before. Maybe following the thread will help you find some answers.
>
> http://svn.haxx.se/users/archive-2004-05/1453.shtml
>
Thanks for all the responses.
Gabor
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Frank Gruman <fg...@verizon.net>.
Just as a side note - I see this discussion ran around for a while on
this board before. Maybe following the thread will help you find some
answers.
http://svn.haxx.se/users/archive-2004-05/1453.shtml
Regards,
Frank
Frank Gruman wrote:
> We are also using Oracle.
>
> No biggie. We use TOAD, and there is (with the pricier license, of
> course) a DB Compare tool that will automagically create your scripts
> for you. If necessary, run this tool just before you do a branch (or
> after) and save that in. The other option - when they make a change
> through their GUI tools is to then take a snapshot of the DDL and save
> that out, saving it as the actual object name (with the object type as
> the extension).
>
> Of course - your developers may choose option 1 because it's work they
> don't have to do. But then how do you track which developer is making
> which change to your DB?
>
> Regards,
> Frank
>
> Gabor Szabo wrote:
>
>> Thanks for the suggestions so far.
>>
>> I am sure they fit small teams of 1-5 ppl (I do similar things) but I
>> wonder if they fit
>> larger teams.
>>
>> What I forgot to mention that we are dealing here with Oracle and a
>> slow company.
>> When I mentioned them that the developers will have to write scripts
>> to update
>> the database they wanted to run away.
>>
>> They want to have some wizzard or GUI tool to maintain the database
>> (e.g. SQL Navigator)
>> and then they somehow want to have the upgrade script magically.
>>
>> Gabor
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
>> For additional commands, e-mail: users-help@subversion.tigris.org
>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Frank Gruman <fg...@verizon.net>.
We are also using Oracle.
No biggie. We use TOAD, and there is (with the pricier license, of
course) a DB Compare tool that will automagically create your scripts
for you. If necessary, run this tool just before you do a branch (or
after) and save that in. The other option - when they make a change
through their GUI tools is to then take a snapshot of the DDL and save
that out, saving it as the actual object name (with the object type as
the extension).
Of course - your developers may choose option 1 because it's work they
don't have to do. But then how do you track which developer is making
which change to your DB?
Regards,
Frank
Gabor Szabo wrote:
>Thanks for the suggestions so far.
>
>I am sure they fit small teams of 1-5 ppl (I do similar things) but I
>wonder if they fit
>larger teams.
>
>What I forgot to mention that we are dealing here with Oracle and a
>slow company.
>When I mentioned them that the developers will have to write scripts to update
>the database they wanted to run away.
>
>They want to have some wizzard or GUI tool to maintain the database
>(e.g. SQL Navigator)
>and then they somehow want to have the upgrade script magically.
>
>Gabor
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
>For additional commands, e-mail: users-help@subversion.tigris.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: Version control of Databases
Posted by Gabor Szabo <sz...@gmail.com>.
Thanks for the suggestions so far.
I am sure they fit small teams of 1-5 ppl (I do similar things) but I
wonder if they fit
larger teams.
What I forgot to mention that we are dealing here with Oracle and a
slow company.
When I mentioned them that the developers will have to write scripts to update
the database they wanted to run away.
They want to have some wizzard or GUI tool to maintain the database
(e.g. SQL Navigator)
and then they somehow want to have the upgrade script magically.
Gabor
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org