On 6/11/07, Brian Devaney <[EMAIL PROTECTED]> 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
