db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From robk99 <ROBEKL...@TX.RR.COM>
Subject Re: Adding a new column in an existing table using the API
Date Mon, 01 Oct 2007 04:41:04 GMT

Never mind figured it out, at least for now.

1 Read the modified tables from an XML file and created source Model
2 Created a clean Model that is devoid of tables
3 Read a live database and created a target Model
4 Walk through each table in the source model and try to find in target
5 if table from source model is found in target model then i add it to the
clean Model
6 if table from source model is not found in target model i do nothing
7 when each table in the source model is reviewed, i perform an
alterdatabase using the clean and source models
8 parse the resulting ddls from the alterdatabase and execute in a batch


robk99 wrote:
> Still having issues.  When executing alterTables after adding the column
> to the table it is trying to recreate other existing tables in the target
> database.
> The following is code I wrote that compares columns of two tables that
> both exist in two different databases, sourceDatabase (contains only 1
> table) vs targetDatabase (contains over 600 tables).  If a column does not
> exit in the table of the target database table that exists in the source
> database table then it is added to the table.
> void updateTables(Database targetDatabase, Database sourceDatabase, String
> table) throws Exception
> 	{
> 			Platform platform =
> PlatformFactory.createNewPlatformInstance("oracle");
> 			//			find table source table	in sourceDatabae				
> 			Table sourceTable = sourceDatabase.findTable(table,false);			
> 			int numCols = sourceTable.getColumnCount();						
> 			//			find table in targetDatabase
> 			Table targetTable = targetDatabase.findTable(table,false);
> 			//compare source to target (Looks for only additions does not delete
> columns)
> 			for (int i=0; i<numCols; i++){
> 				Column sourceColumn =sourceTable.getColumn(i);
> 				Column targetColumn = targetTable.findColumn(sourceColumn.getName());
> 				if (targetColumn == null){
> 					System.out.println("adding column");					
> 					targetTable.addColumn(sourceColumn);					
> 					System.out.println("added column");
> 				}
> 			}
> 			platform.alterTables(conn,null,"schematoupdate",null,targetDatabase,
> false);			
> 			conn.setAutoCommit(true);
> }
> Any ideas?
> Thanks
> Rob
> Thomas Dudziak wrote:
>> On 9/23/07, robk99 <ROBEKLEIN@tx.rr.com> wrote:
>>> Is there a method using the API to modify an existing table by adding a
>>> column to it without deleting the original table and then recreating the
>>> table with the new column?
>> That's pretty much what DdlUtils is made for. It involves basically three
>> steps:
>> * Read the database model from either the live database (via the
>> readModelFromDatabase method in the Platform instance that you'll get
>> from the PlatformFactory) or an XML file (via the DatabaseIO class).
>> * Change the mode which is basically a bunch of objects representing
>> tables, columns, foreign keys etc. E.g. in your case, find the table
>> in question and simply add a column to it.
>> * Write the database model to an XML file (again via the DatabaseIO
>> class) or to the live database (using the alterTables method in the
>> Platform).
>> You could also use a variations of the above to get SQL for the
>> alteration that you then could execute manually.
>> Tom

View this message in context: http://www.nabble.com/Adding-a-new-column-in-an-existing-table-using-the-API-tf4505049.html#a12973274
Sent from the Apache DdlUtils - User mailing list archive at Nabble.com.

View raw message