You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by robk99 <RO...@TX.RR.COM> on 2007/09/23 18:54:07 UTC
Adding a new column in an existing table using the API
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?
Thanks
Rob
--
View this message in context: http://www.nabble.com/Adding-a-new-column-in-an-existing-table-using-the-API-tf4505049.html#a12848158
Sent from the Apache DdlUtils - User mailing list archive at Nabble.com.
Re: Adding a new column in an existing table using the API
Posted by robk99 <RO...@TX.RR.COM>.
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
Model
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
thanks,
Robk
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 <RO...@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.
Re: Adding a new column in an existing table using the API
Posted by robk99 <RO...@TX.RR.COM>.
Still having issues. The 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 to 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
Table sourceTable = sourceDatabase.findTable(table,false);
int numCols = sourceTable.getColumnCount();
// find columns for source
Table targetTable = targetDatabase.findTable(table,false);
Table targetTable1 = 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 <RO...@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#a12960193
Sent from the Apache DdlUtils - User mailing list archive at Nabble.com.
Re: Adding a new column in an existing table using the API
Posted by robk99 <RO...@TX.RR.COM>.
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 <RO...@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#a12960193
Sent from the Apache DdlUtils - User mailing list archive at Nabble.com.
Re: Adding a new column in an existing table using the API
Posted by Thomas Dudziak <to...@gmail.com>.
On 9/23/07, robk99 <RO...@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