db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Devaney" <bdeva...@docfinity.com>
Subject RE: Alter tables/database question
Date Wed, 13 Jun 2007 13:35:11 GMT
I think I have come up with a method that will work and not affect the
existing code much.  

In a nutshell, I have created two flags: doDrop, which will control
whether the table gets dropped if it exists in the source database but
not in the target and doTruncate which will control if the column length
gets changed if the source is longer than the target.  Both these values
are defaulted to true so that if the setters are not called, DdlUtils
behaves exactly as it did before these flags were added.  

Setters were added to PlatformImplBase, SqlBuilder and ModelComparator
so that the setter, called for a platform will pass the flag down in the
alter table methods only to SqlBuilder which passes it to
ModelComparator.  In ModelComparator.compareTable and compareColumn is
where the decision will be made to either not drop or not truncate the
source database.  

Once I have my version tested, where would I send any changes for
review?  I have looked on the db.apache.org website for a location or
instructions for a contributor to send code, but I have not found
anything yet.  

-----Original Message-----
From: Guido Schreuder [mailto:g.schreuder@xs4all.nl] 
Sent: Wednesday, June 13, 2007 9:05 AM
To: ddlutils-user@db.apache.org
Subject: Re: Alter tables/database question

I myself wrestled with the same issue recently, allthough it was on the
table-level, not on column-level.

In general I would suggest it would be nice just to be able to tell
DDLUtils never to do anything destructive if not neccesary. That is,
never drop a table or column when not required to.

regards,
Guido


On Mon, 2007-06-11 at 21:17 -0700, Thomas Dudziak wrote:
> 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?
> 
> <snip>
> 
> > 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.
> 
> regards,
> Tom
> 


Mime
View raw message