Re: Question - help needed

On 09/11/10 17:56, J. Roeleveld wrote:
> On Tuesday 09 November 2010 18:18:23 Dragan Keselj wrote:
>> Hello,
>>
>> I have 2 identical databases. One is with data and second one is empty (for
>> designing).
>> Is it possible to make changes on empty database (for example to add,
>> remove column or change name...) and, with these changes, refresh design
>> of second one database (without data changes)?
>>
>> thnx for help
>
> Hi,
>
> Simple answer: yes and no...
>
> More usefull answer:
> If you make changes to the empty database using "ALTER TABLE" commands, you
> should be able to use those same commands on the actual database.
>
> But, if you delete a table/column/... that actually contains data, you WILL
> loose that data.
>
> If you redesign using DROP TABLE / CREATE TABLE commands, you will need to find
> a tool that can compare 2 states of the database and create the ALTER TABLE
> commands for you.
>
> Same goes for other database objects, like VIEWs, INDEXes,....., etc.

I'd like to add in my 2 cents - I don't trust a tool to do this job.
What we prefer to do at work is write scripts that transform the
database from one state, to another. Here's how it works:

We start out with a CreateTables.sql file. Someone then needs to change
the schema, so they modify this file. They *also* write a script to go
with it, like update-20101121-description-here.sql. If they added a
column to the database, this script would contain an appropriate ALTER
TABLE statement.

This isn't automated, but it does the job for us because we don't tend
to modify the schema all that often anymore. You can slightly automate
this process, Rake for Ruby on Rails handles the scripts for you in a
database agnostic manner. I wrote Schema-Evolution to do a similar type
of thing, but with plain SQL statements [1].

Hope this helps you!

--
[1]: http://search.cpan.org/~cycles/SchemaEvolution-0.03/script/evolve.pl