[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Version control of Databases

From: Ryan Schmidt <subversion-2005_at_ryandesign.com>
Date: 2005-06-14 11:29:33 CEST

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
Received on Tue Jun 14 11:31:44 2005

This is an archived mail posted to the Subversion Users mailing list.