Thread: Question - help needed

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

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.

--
Joost

PS. I'm not familiar with a free tool that can help with this, but that does
not mean there is no free tool.

Hi,
> >
> > 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
Please have a look at
http://www.liquibase.org/
It provides scripts to make the databases consistent. You should be able
to use it for this. We have used it with Oracle.
Regards,
Jayadevan





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

Jayadevan M, 10.11.2010 04:31:
> Please have a look at
> http://www.liquibase.org/
> It provides scripts to make the databases consistent. You should be able
> to use it for this. We have used it with Oracle.

I second this (we use it with Postgres and Oracle).

Managing the database model should always be done through a defined process and Liquibase does help in making that
easier.Especially because it "know" which changes to apply to a given database (but this requires that *all* upgrades
aremade through Liquibase) 

We had some problems though with one or two customers where the DBA would not accept either that fact that the "script"
isan XML file, nor the Liquibase generated SQL scripts. As we had to target only a single DBMS we could write our own
XSLTthat would transform Liquibase's format into a SQL that the DBA would accept. 

Regards
Thomas

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