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