You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by bu...@apache.org on 2004/05/25 16:50:10 UTC
DO NOT REPLY [Bug 29204] New: -
[sql] [patch] sql server improvments, fix for prior patch
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: commons-dev@jakarta.apache.org
ReportedBy: bugzilla.e.wuuza@xoxy.net
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:jmarshall@connectria.com">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:jmarshall@connectria.com">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:jstrachan@apache.org">James Strachan</a>
+ * @author <a href="mailto:jvanzyl@zenplex.com">Jason van Zyl</a>
+ * @author <a href="mailto:dlr@finemaltcoding.com">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:drfish@cox.net">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: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org