DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT <http://issues.apache.org/bugzilla/show_bug.cgi?id=29204>. ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND INSERTED IN THE BUG DATABASE.
http://issues.apache.org/bugzilla/show_bug.cgi?id=29204 [sql] [patch] sql server improvments, fix for prior patch Summary: [sql] [patch] sql server improvments, fix for prior patch Product: Commons Version: Nightly Builds Platform: All OS/Version: Other Status: NEW Severity: Enhancement Priority: Other Component: Sandbox AssignedTo: [EMAIL PROTECTED] ReportedBy: [EMAIL PROTECTED] Patch requested to be posted here by Matt Hawthorne ************************** Index: src/java/org/apache/commons/sql/builder/MSSqlBuilder.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java,v retrieving revision 1.7 diff -u -r1.7 MSSqlBuilder.java --- src/java/org/apache/commons/sql/builder/MSSqlBuilder.java 28 Feb 2004 03:35:47 -0000 1.7 +++ src/java/org/apache/commons/sql/builder/MSSqlBuilder.java 24 May 2004 18:38:50 -0000 @@ -21,6 +21,7 @@ import org.apache.commons.sql.model.Column; import org.apache.commons.sql.model.ForeignKey; +import org.apache.commons.sql.model.Index; import org.apache.commons.sql.model.Table; /** @@ -44,7 +45,7 @@ int counter = 1; for (Iterator iter = table.getForeignKeys().iterator(); iter.hasNext (); ) { ForeignKey key = (ForeignKey) iter.next(); - + String constraintName = tableName + "_FK_" + counter; println("IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='" + constraintName + "'" @@ -53,7 +54,7 @@ print("ALTER TABLE " + tableName + " DROP CONSTRAINT " + constraintName ); printEndOfStatement(); } - + // now drop the table println( "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = '" + tableName + "')" ); println( "BEGIN" ); @@ -81,13 +82,50 @@ print( "END" ); printEndOfStatement(); } - - protected void printComment(String text) throws IOException { - print("# "); - println(text); - } - + protected void printAutoIncrementColumn(Table table, Column column) throws IOException { print( "IDENTITY (1,1) " ); } + + protected boolean shouldGeneratePrimaryKeys(java.util.List primaryKeyColumns) { + /* + * requires primary key indication for autoincrement key columns + * I'm not sure why the default skips the pk statement if all are identity + */ + return primaryKeyColumns.size() > 0; + } + + protected String getSqlType(Column column) { + //SQL Server reports datetime columns as java.sql.Types.TIMESTAMP from DatabaseMetaData + //Since dates are much more useful than the actual MSSQL timestamp versioning data type + // this uses DATETIME for all these types + switch (column.getTypeCode()) { + case java.sql.Types.DATE : + case java.sql.Types.TIME : + case java.sql.Types.TIMESTAMP : + return "DATETIME"; + default : + return super.getSqlType(column); + } + } + + public void alterColumn( Table table, Column column, boolean add ) throws IOException { + + writeAlterHeader( table ); + + print( add ? "ADD " : "ALTER COLUMN " ); + createColumn( table, column ); + printEndOfStatement(); + } + + public void dropIndex( Table table, Index index ) throws IOException { + + print( "DROP INDEX " ); + print( table.getName() ); + print( "." ); + print( index.getName() ); + + printEndOfStatement(); + } + } Index: src/java/org/apache/commons/sql/builder/SqlBuilder.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java,v retrieving revision 1.16 diff -u -r1.16 SqlBuilder.java --- src/java/org/apache/commons/sql/builder/SqlBuilder.java 2 Mar 2004 13:18:31 -0000 1.16 +++ src/java/org/apache/commons/sql/builder/SqlBuilder.java 24 May 2004 18:38:51 -0000 @@ -185,7 +185,7 @@ */ public void createColumn(Table table, Column column) throws IOException { //see comments in columnsDiffer about null/"" defaults - + print(column.getName()); print(" "); print(getSqlType(column)); @@ -493,7 +493,7 @@ println(")"); printEndOfStatement(); } - + } /** @@ -510,7 +510,7 @@ /** * Writes one index for a table - */ + */ protected void writeIndex( Table table, Index index ) throws IOException { if (index.getName() == null) { log.warn( "Index Name is null for index: " + index); @@ -526,15 +526,15 @@ print(table.getName()); print(" ("); - + for (Iterator idxColumnIter = index.getIndexColumns().iterator(); idxColumnIter.hasNext(); - ) + ) { IndexColumn idxColumn = (IndexColumn)idxColumnIter.next(); if (idxColumnIter.hasNext()) { - print(idxColumn.getName() + ", "); + print(idxColumn.getName() + ", "); } else { @@ -545,7 +545,7 @@ print(")"); printEndOfStatement(); } - + } /** * Writes the indexes embedded within the create table statement. not @@ -671,10 +671,10 @@ * Generates the DDL to modify an existing database so the schema matches * the current specified database schema. Drops and modifications will * not be made. - * + * * @param desiredDb The desired database schema * @param cn A connection to the existing database that should be modified - * + * * @throws IOException if the ddl cannot be output * @throws SQLException if there is an error reading the current schema */ @@ -685,21 +685,21 @@ /** * Generates the DDL to modify an existing database so the schema matches * the current specified database schema. - * + * * @param desiredDb The desired database schema * @param cn A connection to the existing database that should be modified - * @param doDrops true if columns and indexes should be dropped, false if + * @param doDrops true if columns and indexes should be dropped, false if * just a message should be output - * @param modifyColumns true if columns should be altered for datatype, size, etc., + * @param modifyColumns true if columns should be altered for datatype, size, etc., * false if just a message should be output - * + * * @throws IOException if the ddl cannot be output * @throws SQLException if there is an error reading the current schema */ public void alterDatabase(Database desiredDb, Connection cn, boolean doDrops, boolean modifyColumns) throws IOException, SQLException { - + Database currentDb = new JdbcModelReader(cn).getDatabase(); - + for (Iterator iter = desiredDb.getTables().iterator(); iter.hasNext (); ) { Table desiredTable = (Table) iter.next(); Table currentTable = currentDb.findTable( desiredTable.getName() ); @@ -713,7 +713,7 @@ createTable( desiredTable ); } else { //add any columns, indices, or constraints - + Iterator desiredColumns = desiredTable.getColumns().iterator(); while ( desiredColumns.hasNext() ) { Column desiredColumn = (Column) desiredColumns.next(); @@ -776,25 +776,29 @@ Index desiredIndex = desiredTable.findIndex (currentIndex.getName()); if ( null == desiredIndex ) { //make sure this isn't the primary key index (mySQL reports this at least) - + Iterator indexColumns = currentIndex.getIndexColumns ().iterator(); boolean isPk = true; while ( indexColumns.hasNext() ) { IndexColumn ic = (IndexColumn) indexColumns.next(); Column c = currentTable.findColumn( ic.getName() ); - if ( !c.isPrimaryKey() ) { + if ( c != null && !c.isPrimaryKey() ) { isPk = false; break; } } - + if ( !isPk ) { - log.info( "dropping non-primary index " + currentTable.getName() + "." + currentIndex.getName() ); - dropIndex( currentTable, currentIndex ); + if ( doDrops ) { + log.info( "dropping non-primary index " + currentTable.getName() + "." + currentIndex.getName() ); + dropIndex( currentTable, currentIndex ); + } else { + log.info( "not dropping index " + currentTable.getName() + "." + currentIndex.getName() ); + } } } } - + } //table exists? } //for tables create @@ -802,7 +806,7 @@ for (Iterator iter = currentDb.getTables().iterator(); iter.hasNext (); ) { Table currentTable = (Table) iter.next(); Table desiredTable = desiredDb.findTable( currentTable.getName() ); - + if ( desiredTable == null ) { if ( doDrops ) { log.info( "dropping table " + currentTable.getName() ); @@ -813,18 +817,18 @@ printComment( text ); } } - + } //for tables drops } /** * Generates the alter statement to add or modify a single column on a table. - * + * * @param table The table the index is on * @param column The column to drop * @param add true if the column is new, false if it is to be changed - * + * * @throws IOException if the statement cannot be written */ public void alterColumn( Table table, Column column, boolean add ) throws IOException { @@ -838,10 +842,10 @@ /** * Generates the statement to drop an column from a table. - * + * * @param table The table the index is on * @param column The column to drop - * + * * @throws IOException if the statement cannot be written */ public void dropColumn( Table table, Column column ) throws IOException { @@ -856,9 +860,9 @@ /** * Generates the first part of the ALTER TABLE statement including the * table name. - * + * * @param table The table being altered - * + * * @throws IOException if the statement cannot be written */ protected void writeAlterHeader( Table table ) throws IOException { @@ -866,19 +870,19 @@ println(table.getName()); printIndent(); - + } /** * Generates the statement to drop an index from the database. The * <code>alterTableForDrop</code> property is checked to determine what * style of drop is generated. - * + * * @param table The table the index is on * @param index The index to drop - * + * * @throws IOException if the statement cannot be written - * + * * @see SqlBuilder#useAlterTableForDrop */ public void dropIndex( Table table, Index index ) throws IOException { @@ -899,13 +903,13 @@ } /** - * Helper method to determine if two column specifications represent + * Helper method to determine if two column specifications represent * different types. Type, nullability, size, scale, default value, * and precision radix are the attributes checked. Currently default - * values are compared where null and empty string are considered equal. + * values are compared where null and empty string are considered equal. * See comments in the method body for explanation. - * - * + * + * * @param first First column to compare * @param second Second column to compare * @return true if the columns differ @@ -926,7 +930,7 @@ //schema xml if you really want null and not just unspecified. String desiredDefault = desired.getDefaultValue(); String currentDefault = current.getDefaultValue(); - boolean defaultsEqual = desiredDefault == null || + boolean defaultsEqual = desiredDefault == null || desiredDefault.equals(currentDefault); boolean sizeMatters = desired.getSize() > 0; Index: src/java/org/apache/commons/sql/builder/SybaseBuilder.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/builder/SybaseBuilder.java,v retrieving revision 1.7 diff -u -r1.7 SybaseBuilder.java --- src/java/org/apache/commons/sql/builder/SybaseBuilder.java 28 Feb 2004 03:35:47 -0000 1.7 +++ src/java/org/apache/commons/sql/builder/SybaseBuilder.java 24 May 2004 18:38:51 -0000 @@ -42,7 +42,7 @@ int counter = 1; for (Iterator iter = table.getForeignKeys().iterator(); iter.hasNext (); ) { ForeignKey key = (ForeignKey) iter.next(); - + String constraintName = tableName + "_FK_" + counter; println("IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name=''" + constraintName + "')" Index: src/java/org/apache/commons/sql/io/JdbcModelReader.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/io/JdbcModelReader.java,v retrieving revision 1.6 diff -u -r1.6 JdbcModelReader.java --- src/java/org/apache/commons/sql/io/JdbcModelReader.java 28 Feb 2004 03:35:48 -0000 1.6 +++ src/java/org/apache/commons/sql/io/JdbcModelReader.java 24 May 2004 18:38:51 -0000 @@ -1,5 +1,5 @@ /* - * Copyright 1999-2004 The Apache Software Foundation. + * Copyright 1999-2002,2004 The Apache Software Foundation. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. @@ -159,9 +159,7 @@ t1.setName(tableName); tables.add(t1); } - if (tableData != null) { - tableData.close(); - } + Iterator i = tables.iterator(); while (i.hasNext()) { Table t = (Table) i.next(); @@ -495,34 +493,36 @@ DatabaseMetaData dbmd = connection.getMetaData(); Map indexesByName = new HashMap(); - + ResultSet columnData = null; try { columnData = dbmd.getIndexInfo(catalog, schema, tableName, false, false); } catch ( SQLException e ) { log.trace("database does not support getIndexInfo()", e); } - + if ( columnData != null ) { try { //can be multiple columns per index while ( columnData.next() ) { - - String indexName = columnData.getString("INDEX_NAME"); + boolean unique = !columnData.getBoolean("NON_UNIQUE"); + String indexName = columnData.getString("INDEX_NAME"); String column = columnData.getString("COLUMN_NAME"); - + Index index = (Index) indexesByName.get(indexName); - if ( index == null ) { + if ( index == null && indexName != null ) { index = new Index(); index.setName( indexName ); indexesByName.put( indexName, index ); index.setUnique( unique ); } - - IndexColumn ic = new IndexColumn(); - ic.setName( column ); - index.addIndexColumn( ic ); + + if ( index != null ) { + IndexColumn ic = new IndexColumn(); + ic.setName( column ); + index.addIndexColumn( ic ); + } } } finally { @@ -531,7 +531,7 @@ } } } - + return new Vector(indexesByName.values()); } Index: src/java/org/apache/commons/sql/model/Index.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/model/Index.java,v retrieving revision 1.5 diff -u -r1.5 Index.java --- src/java/org/apache/commons/sql/model/Index.java 28 Feb 2004 03:35:48 - 0000 1.5 +++ src/java/org/apache/commons/sql/model/Index.java 24 May 2004 18:38:51 - 0000 @@ -13,6 +13,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ + package org.apache.commons.sql.model; import java.util.ArrayList; @@ -25,7 +26,7 @@ private List indexColumns = new ArrayList(); private boolean unique = false; - + public Index() {} public String getName() @@ -35,6 +36,7 @@ public void setName(String name) { + if ( name == null ) throw new IllegalArgumentException("Null index name"); this.name = name; } Index: src/java/org/apache/commons/sql/model/Table.betwixt =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/model/Table.betwixt,v retrieving revision 1.3 diff -u -r1.3 Table.betwixt --- src/java/org/apache/commons/sql/model/Table.betwixt 16 Dec 2003 15:09:50 - 0000 1.3 +++ src/java/org/apache/commons/sql/model/Table.betwixt 24 May 2004 18:38:51 - 0000 @@ -1,8 +1,9 @@ -<?xml version="1.0" encoding="UTF-8" ?> -<info> - <element> - <hide property="primaryKeyColumns"/> - <hide property="autoIncrementColumn"/> - <addDefaults/> - </element> -</info> +<?xml version="1.0" encoding="UTF-8" ?> +<info> + <element name="table"> + <hide property="primaryKeyColumns"/> + <hide property="autoIncrementColumn"/> + <hide property="uniques"/> + <addDefaults/> + </element> +</info> \ No newline at end of file Index: src/java/org/apache/commons/sql/model/Table.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/model/Table.java,v retrieving revision 1.12 diff -u -r1.12 Table.java --- src/java/org/apache/commons/sql/model/Table.java 28 Feb 2004 03:35:48 - 0000 1.12 +++ src/java/org/apache/commons/sql/model/Table.java 24 May 2004 18:38:51 - 0000 @@ -57,6 +57,11 @@ { } + public String toString() + { + return super.toString() + "[name=" + name + "]"; + } + public String getCatalog() { return this.catalog; Index: src/java/org/apache/commons/sql/model/Unique.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/model/Unique.java,v retrieving revision 1.3 diff -u -r1.3 Unique.java --- src/java/org/apache/commons/sql/model/Unique.java 2 Mar 2004 13:18:31 - 0000 1.3 +++ src/java/org/apache/commons/sql/model/Unique.java 24 May 2004 18:38:51 - 0000 @@ -22,7 +22,7 @@ * <unique> tags, but adds no functionality. All indexes are treated the * same by the Table. * - * @author <a href="mailto:[EMAIL PROTECTED]">John Marshall</a> + * @author John Marshall/Connectria * @version $Revision: 1.3 $ */ public class Unique extends Index Index: src/java/org/apache/commons/sql/model/UniqueColumn.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/model/UniqueColumn.java,v retrieving revision 1.2 diff -u -r1.2 UniqueColumn.java --- src/java/org/apache/commons/sql/model/UniqueColumn.java 28 Feb 2004 03:35:48 -0000 1.2 +++ src/java/org/apache/commons/sql/model/UniqueColumn.java 24 May 2004 18:38:51 -0000 @@ -20,7 +20,8 @@ * <unique> tags, but adds no functionality. All indexes are treated the * same by the Table. * - * @author <a href="mailto:[EMAIL PROTECTED]">John Marshall</a> + * @author John Marshall/Connectria + * @version $Revision: 1.2 $ */ public class UniqueColumn extends IndexColumn Index: src/java/org/apache/commons/sql/task/DDLTask.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/task/DDLTask.java,v retrieving revision 1.4 diff -u -r1.4 DDLTask.java --- src/java/org/apache/commons/sql/task/DDLTask.java 28 Feb 2004 03:35:48 - 0000 1.4 +++ src/java/org/apache/commons/sql/task/DDLTask.java 24 May 2004 18:38:51 - 0000 @@ -1,5 +1,5 @@ /* - * Copyright 2001-2004 The Apache Software Foundation. + * Copyright 1999-2004 The Apache Software Foundation. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. @@ -13,28 +13,63 @@ * See the License for the specific language governing permissions and * limitations under the License. */ + package org.apache.commons.sql.task; import java.io.File; import java.io.FileWriter; +import java.io.StringWriter; import java.io.Writer; +import java.sql.Connection; + +import javax.sql.DataSource; import org.apache.commons.sql.builder.SqlBuilder; import org.apache.commons.sql.builder.SqlBuilderFactory; import org.apache.commons.sql.io.DatabaseReader; import org.apache.commons.sql.model.Database; +import org.apache.commons.sql.util.DDLExecutor; import org.apache.tools.ant.BuildException; -import org.apache.tools.ant.Task; /** * A base task which generates the SQL DDL to create a database * to a given output file from an XML schema representing * a data model contains tables for a <strong>single</strong> - * database. - * - * @version $Id$ + * database. This task can optionally generate DDL to upgrade an existing + * database to the current schema definition. The results of either + * generation can be executed against an existing database. + * <p> + * Here is a ant/maven excerpt for using this: + * <pre> + <taskdef + name="ddl" + classname="org.apache.commons.sql.task.DDLTask"> + <classpath refid="maven.dependency.classpath"/> + </taskdef> + + <target name="custom-ddl" description="Creates ddl"> + <ddl + xmlFile="schema/schema.xml" + targetDatabase="mysql" + output="target/schema.sql" + dbUrl="jdbc:mysql://localhost:3306/test" + dbUser="user" + dbPassword="pass" + dbDriver="com.mysql.jdbc.Driver" + alterDb="true" + executeSql="true" + modifyColumns="true" + doDrops="true" + /> + </target> + * </pre> + * + * @author <a href="mailto:[EMAIL PROTECTED]">James Strachan</a> + * @author <a href="mailto:[EMAIL PROTECTED]">Jason van Zyl</a> + * @author <a href="mailto:[EMAIL PROTECTED]">Daniel Rall</a> + * @author John Marshall/Connectria */ -public class DDLTask extends Task +public class DDLTask extends DatabaseTask { /** * XML that describes the database model, this is transformed @@ -54,11 +89,26 @@ private String targetDatabase; /** - * Flag indicates whether SQL drop statements should be generated. + * Flag for executing the sql or not. */ - private boolean dropTables = true; + private boolean executeSql; /** + * Flag for whether to alter the database or recreate from scratch + */ + private boolean alterDb; + + /** + * Flag for whether to modify column definitions in an existing database + */ + private boolean modifyColumns; + + /** + * Flag for whether drops should be made when updating an existing database + */ + private boolean doDrops; + + /** * Get the xml schema describing the application model. * * @return String xml schema file. @@ -115,26 +165,80 @@ { this.output = output; } - + /** - * @return Returns the dropTables. + * Check if the database should be altered to match the schema or + * recreated from scratch + * @return alter flag */ - public boolean isDropTables() { - return dropTables; + public boolean getAlterDb() { + return alterDb; } /** - * @param dropTables The dropTables to set. + * Set whether the database should be altered to match the schema or + * recreated from scratch + * @param alterDb alter flag */ - public void setDropTables(boolean dropTables) { - this.dropTables = dropTables; + public void setAlterDb(boolean alterDb) { + this.alterDb = alterDb; } - + /** - * Create the SQL DDL for the given database. + * Check if the generated ddl should be executed against the databsase + * @return true if sql is to be executed */ - public void execute() throws BuildException - { + public boolean getExecuteSql() { + return executeSql; + } + + /** + * Set whether the generated ddl should be executed against the databsase + * @param executeSql the execute flag + */ + public void setExecuteSql(boolean executeSql) { + this.executeSql = executeSql; + } + + /** + * Check if tables/columns/indexes should be dropped when updating a database + * @return true if drops should be made + */ + public boolean getDoDrops() { + return doDrops; + } + + /** + * Set whether tables/columns/indexes should be dropped when updating a database + * @param doDrops the new drop flag + */ + public void setDoDrops(boolean doDrops) { + this.doDrops = doDrops; + } + + /** + * Modify column definitions in an existing database + * @return true if columns should be modified + */ + public boolean getModifyColumns() { + return modifyColumns; + } + + /** + * Modify column definitions in an existing database + * @param modifyColumns the new flag + */ + public void setModifyColumns(boolean modifyColumns) { + this.modifyColumns = modifyColumns; + } + + + /** + * Checks that settings exist and in valid combinations + * + * @throws BuildException if parameters are incorrect + */ + private void assertValidSettings() throws BuildException { if (targetDatabase == null) { throw new BuildException( "Must specify a targetDatabase attribute" ); @@ -147,7 +251,21 @@ { throw new BuildException( "Must specify an output attribute" ); } - + if (getDbUrl() == null && ( alterDb || executeSql )) + { + throw new BuildException( "Connection url is required if altering database or executing sql" ); + } + } + + /** + * Create the SQL DDL for the given database. + * + * @throws BuildException + */ + public void execute() throws BuildException + { + assertValidSettings(); + Database database = null; try { @@ -155,26 +273,33 @@ } catch (Exception e) { + e.printStackTrace(); throw new BuildException( "Failed to parse file: " + getXmlFile(), e ); } - FileWriter writer = null; - try + DataSource dataSource = null; + if (getDbUrl() != null) { - writer = new FileWriter( getOutput() ); - } - catch (Exception e) - { - throw new BuildException( "Failed to create file: " + getOutput(), e ); + try + { + dataSource = getDataSource(); + } + catch ( Exception e ) + { + e.printStackTrace(); + throw new BuildException( "Could not get connection: " + dbUrl, e ); + } } + StringWriter writer = new StringWriter(); SqlBuilder builder = null; try - { + { builder = newSqlBuilder(writer); } catch (Exception e) { + e.printStackTrace(); throw new BuildException( "Failed to create SqlBuilder for database: " + getTargetDatabase(), e ); } if ( builder == null) @@ -183,15 +308,79 @@ } // OK we're ready now, lets try create the DDL + Connection con = null; + try + { + if ( alterDb ) + { + con = dataSource.getConnection(); + builder.alterDatabase(database, con, doDrops, modifyColumns); + } + else + { + builder.createDatabase(database); + } + } + catch (Exception e) + { + e.printStackTrace(); + throw new BuildException( "Error occurred while creating ddl", e ); + } + finally + { + try + { + if ( con != null ) + { + con.close(); + } + } + catch (Exception e) + { + //ignore + } + } + + String sql = writer.toString(); + if ( executeSql ) + { + try + { + DDLExecutor exec = new DDLExecutor( dataSource ); + exec.evaluateBatch(sql); + + } + catch (Exception e) + { + e.printStackTrace(); + throw new BuildException( "Failed to create evaluate sql", e ); + } + } + + //write it out + FileWriter out = null; try { - builder.createDatabase(database, dropTables); - writer.close(); + out = new FileWriter( getOutput() ); + out.write( sql ); } catch (Exception e) { - throw new BuildException( "Error occurred while writing to file: " + getOutput(), e ); + e.printStackTrace(); + throw new BuildException( "Failed to create file: " + getOutput(), e ); } + finally + { + try + { + out.close(); + } + catch (Exception e) + { + //ignore + } + } + } // Implementation methods @@ -199,19 +388,33 @@ /** * Loads the XML schema from the XML file and returns the database model bean + * + * @return Database schema + * @throws Exception */ protected Database loadDatabase() throws Exception { DatabaseReader reader = new DatabaseReader(); - return (Database) reader.parse( getXmlFile() ); + Database db = (Database) reader.parse( getXmlFile() ); + +//org.apache.commons.sql.io.DatabaseWriter writer = new org.apache.commons.sql.io.DatabaseWriter(System.err); +//writer.write(db); + + return db; } + /** + * Gets an SqlBuilder for the given writer + * + * @param writer Destination writer + * @return SqlBuilder + * + * @throws Exception + */ protected SqlBuilder newSqlBuilder(Writer writer) throws Exception { SqlBuilder builder = SqlBuilderFactory.newSqlBuilder(getTargetDatabase ()); builder.setWriter(writer); return builder; } - - } Index: src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java,v retrieving revision 1.4 diff -u -r1.4 JdbcToSchemaTask.java --- src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java 28 Feb 2004 03:35:48 -0000 1.4 +++ src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java 24 May 2004 18:38:51 -0000 @@ -1,5 +1,5 @@ /* - * Copyright 1999-2002,2004 The Apache Software Foundation. + * Copyright 1999-2004 The Apache Software Foundation. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. @@ -17,15 +17,11 @@ package org.apache.commons.sql.task; import java.io.FileWriter; -import java.sql.Connection; -import java.sql.DriverManager; import java.util.Hashtable; import org.apache.commons.sql.io.DatabaseWriter; -import org.apache.commons.sql.io.JdbcModelReader; import org.apache.commons.sql.model.Database; import org.apache.tools.ant.BuildException; -import org.apache.tools.ant.Task; /** * This class generates an XML schema of an existing database from @@ -34,29 +30,11 @@ * @author <a href="mailto:[EMAIL PROTECTED]">J. Russell Smyth</a> * @version $Id: $ */ -public class JdbcToSchemaTask extends Task +public class JdbcToSchemaTask extends DatabaseTask { /** Name of database schema file produced. */ protected String outputFile; - /** JDBC URL. */ - protected String dbUrl; - - /** JDBC driver. */ - protected String dbDriver; - - /** JDBC user name. */ - protected String dbUser; - - /** JDBC password. */ - protected String dbPassword; - - /** DB catalog to use. */ - protected String dbCatalog; - - /** DB schema to use. */ - protected String dbSchema; - /** Hashtable of columns that have primary keys. */ protected Hashtable primaryKeys; @@ -66,41 +44,6 @@ protected boolean useTypeNames = false; - public String getDbSchema() - { - return dbSchema; - } - - public void setDbCatalog(String dbCatalog) - { - this.dbCatalog = dbCatalog; - } - - public void setDbSchema(String dbSchema) - { - this.dbSchema = dbSchema; - } - - public void setDbUrl(String v) - { - dbUrl = v; - } - - public void setDbDriver(String v) - { - dbDriver = v; - } - - public void setDbUser(String v) - { - dbUser = v; - } - - public void setDbPassword(String v) - { - dbPassword = v; - } - public void setOutputFile (String v) { outputFile = v; @@ -117,16 +60,11 @@ public void execute() throws BuildException { System.err.println("Commons-Sql JdbcToSchema starting\n"); - System.err.println("Your DB settings are:"); - System.err.println("driver : " + dbDriver); - System.err.println("URL : " + dbUrl); - System.err.println("user : " + dbUser); - System.err.println("password : " + dbPassword); - System.err.println("schema : " + dbSchema); - + printDbSettings(); + Database db = null; try{ - db = getDbFromJdbc(); + db = getDbFromConnection( getDataSource().getConnection() ); DatabaseWriter w = new DatabaseWriter(new FileWriter(outputFile)); // w.setWriteIDs(true); w.write(db); @@ -136,29 +74,4 @@ } } - /** - */ - public Database getDbFromJdbc() throws Exception - { - // Load the database Driver. - Class.forName(dbDriver); - System.err.println("DB driver sucessfuly instantiated"); - - // Attemtp to connect to a database. - Connection con = DriverManager.getConnection(dbUrl, - dbUser, - dbPassword); - System.err.println("DB connection established"); - - JdbcModelReader reader = new JdbcModelReader(con); - if ( dbCatalog!=null ) { - reader.setCatalog(dbCatalog); - } - if ( dbSchema!=null ) { - reader.setSchema(dbSchema); - } - - Database db = reader.getDatabase(); - return db; - } } Index: src/java/org/apache/commons/sql/util/DDLExecutor.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/java/org/apache/commons/sql/util/DDLExecutor.java,v retrieving revision 1.5 diff -u -r1.5 DDLExecutor.java --- src/java/org/apache/commons/sql/util/DDLExecutor.java 28 Feb 2004 03:35:48 -0000 1.5 +++ src/java/org/apache/commons/sql/util/DDLExecutor.java 24 May 2004 18:38:51 -0000 @@ -146,7 +146,7 @@ * * @throws SQLException if an error occurs and isContinueOnError == false */ - public void evaluateBatch(String sql) throws SQLException { + public int evaluateBatch(String sql) throws SQLException { Connection connection = borrowConnection(); Statement statement = null; int errors = 0; @@ -203,6 +203,8 @@ closeStatement(statement); returnConnection(connection); } + + return errors; } Index: src/test/org/apache/commons/sql/TestDataModelRoundTrip.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/test/org/apache/commons/sql/TestDataModelRoundTrip.java,v retrieving revision 1.5 diff -u -r1.5 TestDataModelRoundTrip.java --- src/test/org/apache/commons/sql/TestDataModelRoundTrip.java 30 Apr 2003 11:27:31 -0000 1.5 +++ src/test/org/apache/commons/sql/TestDataModelRoundTrip.java 24 May 2004 18:38:51 -0000 @@ -65,7 +65,7 @@ throws Exception { super.setUp(); - String baseDir = System.getProperty("basedir"); + String baseDir = System.getProperty("basedir", "."); assertNotNull("The system property basedir was not defined.", baseDir); String fs = System.getProperty("file.separator"); assertNotNull("The system property file.separator was not defined.", fs); @@ -113,6 +113,10 @@ assertTrue("isbn should be required", c1.isRequired()); assertTrue("isbn should not be primary key but is", ! c1.isPrimaryKey()); + + Column title = t1.getColumn(3); + assertEquals("title", title.getName()); + assertEquals("N/A", title.getDefaultValue()); List keyList1 = t1.getForeignKeys(); assertEquals( "Foreign key count", 1, keyList1.size() ); Index: src/test/org/apache/commons/sql/task/TestDDLTask.java =================================================================== RCS file: /home/cvspublic/jakarta-commons- sandbox/sql/src/test/org/apache/commons/sql/task/TestDDLTask.java,v retrieving revision 1.2 diff -u -r1.2 TestDDLTask.java --- src/test/org/apache/commons/sql/task/TestDDLTask.java 28 Feb 2004 03:35:49 -0000 1.2 +++ src/test/org/apache/commons/sql/task/TestDDLTask.java 24 May 2004 18:38:52 -0000 @@ -61,7 +61,7 @@ // Without drop statements task.setOutput(createOutputFile()); - task.setDropTables(false); + task.setDoDrops(false); task.execute(); } --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
