db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak" <tom...@gmail.com>
Subject Re: Alter tables/database question
Date Tue, 12 Jun 2007 04:17:20 GMT
On 6/11/07, Brian Devaney <bdevaney@docfinity.com> wrote:

> I have a few questions about some of the features in the ddlutils
> project and I would appreciate some advice.  I am writing an application
> to take our model of a database and allow out clients to upgrade their
> databases to make sure that we have all the tables, columns etc. that we
> need for our software to run.  This is a second attempt at this
> application.  The earlier attempt was prior to the existence of ddlutils
> and never really went anywhere.  I have the app creating databases with
> no problems, but altering an existing database has a couple of
> difficulties.
> Everything I see seems to be take the database model and alter the
> database ddlutils is connected to, to match it.  So if the model has:
> Table   column1 varchar(10), column2 integer
> And the database has:
> Table column1 varchar(10)
> It will generate:
> Alter table add column column2 integer
> Which is fine, except for the case where the database column has:
> Table column1 varchar(10) , usercolumn varchar(20)
> The alter script will generate:
> Alter table drop column usercolumn
> Alter table add column2 integer
> If the user has added columns, tables, indicies or anything that does
> not exist in the model database, ddlutils will generate a script to
> delete those items.  Is there any setting that will indicate to ddlutils
> that additional items found in the database are to be kept?


> What I am looking for is some way of making the model a minimum
> requirement rather than the exact plan.  If there are extra tables that
> do not appear in the model, they should be left alone.  Larger columns
> than the model are not truncated, but smaller columns are lengthened to
> match.  Missing indicies are added, but any others are not deleted.

DdlUtils does not do that out of the box. The major problem with
implementing this as a generic approach would be that DdlUtils would
have to know what columns in the current model are supposed to be kept
and which shall be deleted. It would certainly be a useful feature,
but it is currently outside of the scope of DdlUtils.

However, it should be quite easy to implement this using DdlUtils'
API. The basic idea would be to

(1) read in the model from the database as well as from the XML file
(2) add all tables/columns to be maintained in the database, to the
model read from the XML file
(3) alter the database

Steps 1 and 3 are what the Ant tasks do now, so all that is missing would be 2.


View raw message