You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by to...@apache.org on 2004/10/14 21:42:48 UTC
cvs commit: jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task TestDDLTask.java
tomdz 2004/10/14 12:42:48
Modified: sql/src/java/org/apache/commons/sql/builder SqlBuilder.java
MSSqlBuilder.java FirebirdBuilder.java
sql/src/java/org/apache/commons/sql/model Table.betwixt
Reference.java Table.java ForeignKey.java
UniqueColumn.java Unique.java Index.java
sql/src/java/org/apache/commons/sql/task
JdbcToSchemaTask.java DDLTask.java
sql/src/java/org/apache/commons/sql/util DDLExecutor.java
sql/src/java/org/apache/commons/sql/io JdbcModelReader.java
sql/src/test/org/apache/commons/sql/task TestDDLTask.java
Log:
Applied patch by John Marshall
Revision Changes Path
1.24 +60 -18 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java
Index: SqlBuilder.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java,v
retrieving revision 1.23
retrieving revision 1.24
diff -u -r1.23 -r1.24
--- SqlBuilder.java 14 Aug 2004 19:51:35 -0000 1.23
+++ SqlBuilder.java 14 Oct 2004 19:42:48 -0000 1.24
@@ -588,11 +588,21 @@
}
}
- // TODO: add constraints here...
+ // add fk constraints
+ for (Iterator fkIt = desiredTable.getForeignKeys().iterator(); fkIt.hasNext();) {
+ ForeignKey desiredFk = (ForeignKey) fkIt.next();
+ ForeignKey currentFk = currentTable.findForeignKey(desiredFk);
+ if ( currentFk == null ) {
+ if (_log.isInfoEnabled())
+ {
+ _log.info("Creating foreign key " + desiredTable.getName() + "." + desiredFk);
+ }
+ writeExternalForeignKeyCreateStmt(desiredDb, desiredTable, desiredFk);
+ }
+ }
- // Hmm, m-w.com says indices and indexes are both okay
// TODO: should we check the index fields for differences?
-
+ //create new indexes
for (Iterator indexIt = desiredTable.getIndexes().iterator(); indexIt.hasNext();)
{
Index desiredIndex = (Index)indexIt.next();
@@ -608,8 +618,24 @@
}
}
- // TODO: drop constraints - probably need names on them for this
+ // drop fk constraints
+ for (Iterator fkIt = currentTable.getForeignKeys().iterator(); fkIt.hasNext();) {
+ ForeignKey currentFk = (ForeignKey) fkIt.next();
+ ForeignKey desiredFk = desiredTable.findForeignKey(currentFk);
+ if ( desiredFk == null ) {
+ if (_log.isInfoEnabled())
+ {
+ _log.info((doDrops ? "" : "Not ") + "Dropping foreign key " + currentTable.getName() + "." + currentFk);
+ }
+ if ( doDrops ) {
+ writeExternalForeignKeyDropStmt(currentTable, currentFk);
+ }
+ }
+ }
+
+
+ //Drop columns
for (Iterator columnIt = currentTable.getColumns().iterator(); columnIt.hasNext();)
{
Column currentColumn = (Column)columnIt.next();
@@ -638,6 +664,7 @@
}
}
+ //Drop indexes
for (Iterator indexIt = currentTable.getIndexes().iterator(); indexIt.hasNext();)
{
Index currentIndex = (Index)indexIt.next();
@@ -653,7 +680,7 @@
IndexColumn indexColumn = (IndexColumn)columnIt.next();
Column column = currentTable.findColumn(indexColumn.getName());
- if (!column.isPrimaryKey())
+ if (column != null && !column.isPrimaryKey())
{
isPk = false;
break;
@@ -663,9 +690,12 @@
{
if (_log.isInfoEnabled())
{
- _log.info("Dropping non-primary index " + currentTable.getName() + "." + currentIndex.getName());
+ _log.info((doDrops ? "" : "Not ") + "Dropping non-primary index " + currentTable.getName() + "." + currentIndex.getName());
+ }
+ if ( doDrops )
+ {
+ writeExternalIndexDropStmt(currentTable, currentIndex);
}
- writeExternalIndexDropStmt(currentTable, currentIndex);
}
}
}
@@ -674,6 +704,7 @@
}
// generating deferred foreignkeys
+ //TODO should we try to generate new FKs on existing tables?
for (Iterator fkIt = newTables.iterator(); fkIt.hasNext();)
{
createExternalForeignKeys(desiredDb, (Table)fkIt.next());
@@ -793,7 +824,7 @@
for (Iterator it = table.getForeignKeys().iterator(); it.hasNext(); numKey++)
{
- writeExternalForeignKeyCreateStmt(database, table, (ForeignKey)it.next(), numKey);
+ writeExternalForeignKeyCreateStmt(database, table, (ForeignKey)it.next());
}
}
}
@@ -848,7 +879,7 @@
for (Iterator it = table.getForeignKeys().iterator(); it.hasNext(); numKey++)
{
- writeExternalForeignKeyDropStmt(table, (ForeignKey)it.next(), numKey);
+ writeExternalForeignKeyDropStmt(table, (ForeignKey)it.next());
}
}
}
@@ -1146,7 +1177,7 @@
if (column.getDefaultValue() != null)
{
- print(" DEFAULT '");
+ print(" DEFAULT ");
print(_valueQuoteChar);
print(column.getDefaultValue());
print(_valueQuoteChar);
@@ -1366,10 +1397,10 @@
if ((columnA.getTypeCode() != columnB.getTypeCode()) ||
(columnA.isRequired() != columnB.isRequired()) ||
- (sizeMatters && (columnA.getSize() != columnB.getSize())) ||
+ (sizeMatters && (!columnA.getSize().equals(columnB.getSize()))) ||
+ !defaultsEqual /*|| //determined these two to be hardly useful
(columnA.getScale() != columnB.getScale()) ||
- !defaultsEqual ||
- (columnA.getPrecisionRadix() != columnB.getPrecisionRadix()))
+ (columnA.getPrecisionRadix() != columnB.getPrecisionRadix())*/ )
{
return true;
}
@@ -1379,6 +1410,12 @@
}
}
+ protected String getForeignKeyName(ForeignKey fk) {
+ //table and local column should be sufficient - is it possible for one
+ //column to reference multiple tables
+ return fk.firstReference().getLocal() + "_" + fk.getForeignTable();
+ }
+
/**
* Returns the constraint name. This method takes care of length limitations imposed by some databases.
*
@@ -1551,7 +1588,12 @@
{
IndexColumn idxColumn = (IndexColumn)it.next();
- print(getColumnName(table.findColumn(idxColumn.getName())));
+ Column col = table.findColumn(idxColumn.getName());
+ if ( col == null ) {
+ //would get null pointer on next line anyway, so throw exception
+ throw new RuntimeException("Invalid column '" + idxColumn.getName() + "' on index " + index.getName() + " for table " + table.getName());
+ }
+ print(getColumnName(col));
if (it.hasNext())
{
print(", ");
@@ -1634,7 +1676,7 @@
* @param key The foreign key
* @param numKey The number of the key, starting with 1
*/
- protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key, int numKey) throws IOException
+ protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key) throws IOException
{
if (key.getForeignTable() == null)
{
@@ -1645,7 +1687,7 @@
writeTableAlterStmt(table);
print("ADD CONSTRAINT ");
- print(getConstraintName(null, table, "FK", Integer.toString(numKey)));
+ print(getConstraintName(null, table, "FK", getForeignKeyName(key)));
print(" FOREIGN KEY (");
writeLocalReferences(key);
print(") REFERENCES ");
@@ -1699,11 +1741,11 @@
* @param key The foreign key
* @param numKey The number of the key, starting with 1
*/
- protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey, int numKey) throws IOException
+ protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey) throws IOException
{
writeTableAlterStmt(table);
print("DROP CONSTRAINT ");
- print(getConstraintName(null, table, "FK", Integer.toString(numKey)));
+ print(getConstraintName(null, table, "FK", getForeignKeyName(foreignKey)));
printEndOfStatement();
}
1.11 +28 -2 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java
Index: MSSqlBuilder.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -r1.10 -r1.11
--- MSSqlBuilder.java 14 Aug 2004 19:51:35 -0000 1.10
+++ MSSqlBuilder.java 14 Oct 2004 19:42:48 -0000 1.11
@@ -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;
/**
@@ -36,7 +37,7 @@
{
setEmbeddedForeignKeysNamed(true);
setForeignKeysEmbedded(false);
- setCommentPrefix("#");
+ //setCommentPrefix("#");
addNativeTypeMapping(Types.BLOB, "IMAGE");
addNativeTypeMapping(Types.BOOLEAN, "BIT");
addNativeTypeMapping(Types.CHAR, "NCHAR");
@@ -48,7 +49,7 @@
addNativeTypeMapping(Types.LONGVARCHAR, "TEXT");
addNativeTypeMapping(Types.TIME, "DATETIME");
addNativeTypeMapping(Types.TIMESTAMP, "DATETIME");
- addNativeTypeMapping(Types.VARCHAR, "NVARCHAR");
+ addNativeTypeMapping(Types.VARCHAR, "VARCHAR");
}
/* (non-Javadoc)
@@ -119,5 +120,30 @@
protected void writeColumnAutoIncrementStmt(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;
+ }
+
+ public void writeExternalIndexDropStmt(Table table, Index index) throws IOException
+ {
+ print("DROP INDEX ");
+ print( getTableName(table) );
+ print( "." );
+ print( getIndexName(index) );
+ printEndOfStatement();
+ }
+
+ public void writeColumnAlterStmt(Table table, Column column, boolean isNewColumn) throws IOException
+ {
+ writeTableAlterStmt(table);
+ print(isNewColumn ? "ADD " : "ALTER COLUMN ");
+ writeColumn(table, column);
+ printEndOfStatement();
}
}
1.3 +3 -3 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/FirebirdBuilder.java
Index: FirebirdBuilder.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/FirebirdBuilder.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- FirebirdBuilder.java 14 Aug 2004 19:51:35 -0000 1.2
+++ FirebirdBuilder.java 14 Oct 2004 19:42:48 -0000 1.3
@@ -79,11 +79,11 @@
}
/* (non-Javadoc)
- * @see org.apache.commons.sql.builder.SqlBuilder#writeExternalForeignKeyCreateStmt(org.apache.commons.sql.model.Table, org.apache.commons.sql.model.ForeignKey, int)
+ * @see org.apache.commons.sql.builder.SqlBuilder#writeExternalForeignKeyCreateStmt(org.apache.commons.sql.model.Table, org.apache.commons.sql.model.ForeignKey)
*/
- protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key, int numKey) throws IOException
+ protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key) throws IOException
{
- super.writeExternalForeignKeyCreateStmt(database, table, key, numKey);
+ super.writeExternalForeignKeyCreateStmt(database, table, key);
if (key.getForeignTable() != null)
{
print("COMMIT");
1.5 +10 -8 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.betwixt
Index: Table.betwixt
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.betwixt,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- Table.betwixt 26 Jul 2004 22:15:39 -0000 1.4
+++ Table.betwixt 14 Oct 2004 19:42:48 -0000 1.5
@@ -1,8 +1,10 @@
-<?xml version="1.0" encoding="UTF-8" ?>
-<info>
- <element name="table">
- <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="unique"/>
+ <hide property="uniques"/>
+ <addDefaults/>
+ </element>
+</info>
1.6 +26 -1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Reference.java
Index: Reference.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Reference.java,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -r1.5 -r1.6
--- Reference.java 19 Jul 2004 22:14:00 -0000 1.5
+++ Reference.java 14 Oct 2004 19:42:48 -0000 1.6
@@ -16,7 +16,7 @@
package org.apache.commons.sql.model;
-public class Reference implements Cloneable
+public class Reference implements Cloneable, Comparable
{
private String local;
private String foreign;
@@ -53,5 +53,30 @@
public void setForeign(String foreign)
{
this.foreign = foreign;
+ }
+
+ public boolean equals(Object o) {
+ boolean result = o != null && getClass().equals(o.getClass());
+ if ( result ) {
+ Reference ref = (Reference) o;
+ result = this.local.equalsIgnoreCase(ref.local) && this.foreign.equalsIgnoreCase(ref.foreign);
+ }
+ return result;
+ }
+
+ /* (non-Javadoc)
+ * @see java.lang.Comparable#compareTo(java.lang.Object)
+ */
+ public int compareTo(Object o) {
+ Reference ref = (Reference) o;
+ int result = this.local.compareTo(ref.local);
+ if ( result == 0 ) {
+ result = this.foreign.compareTo(ref.foreign);
+ }
+ return result;
+ }
+
+ public String toString() {
+ return "Reference[" + this.local + " to " + this.foreign + "]";
}
}
1.15 +23 -0 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java
Index: Table.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java,v
retrieving revision 1.14
retrieving revision 1.15
diff -u -r1.14 -r1.15
--- Table.java 19 Jul 2004 22:14:00 -0000 1.14
+++ Table.java 14 Oct 2004 19:42:48 -0000 1.15
@@ -59,6 +59,11 @@
{
}
+ public String toString()
+ {
+ return super.toString() + "[name=" + name + "]";
+ }
+
/* (non-Javadoc)
* @see java.lang.Object#clone()
*/
@@ -280,6 +285,24 @@
}
}
return null;
+ }
+
+ /**
+ * Finds a ForeignKey within the table that matches the supplied ForeignKey
+ * exactly, meaning the foreign table and all column references are the same.
+ */
+ public ForeignKey findForeignKey(ForeignKey key)
+ {
+ ForeignKey result = null;
+ for (Iterator iter = getForeignKeys().iterator(); iter.hasNext(); )
+ {
+ ForeignKey fk = (ForeignKey) iter.next();
+ if ( fk.equals(key) ) {
+ result = key;
+ break;
+ }
+ }
+ return result;
}
/**
1.8 +27 -0 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/ForeignKey.java
Index: ForeignKey.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/ForeignKey.java,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -r1.7 -r1.8
--- ForeignKey.java 25 Jul 2004 15:08:29 -0000 1.7
+++ ForeignKey.java 14 Oct 2004 19:42:48 -0000 1.8
@@ -16,6 +16,7 @@
package org.apache.commons.sql.model;
import java.util.ArrayList;
+import java.util.Collections;
import java.util.List;
// TODO: Add a name property to the foreignkey that is respected by
@@ -58,5 +59,31 @@
public List getReferences()
{
return references;
+ }
+
+ public Reference firstReference() {
+ return (Reference) (references.size() == 0 ? null : references.get(0));
+ }
+
+ public boolean equals(Object o) {
+ boolean result = o != null && getClass().equals(o.getClass());
+ if ( result ) {
+ ForeignKey fk = (ForeignKey) o;
+ result = this.foreignTable.equalsIgnoreCase(fk.foreignTable) && this.references.size() == fk.references.size();
+ if ( result ) {
+ //check all references - need to ensure order is same for valid comparison
+ List copyThis = (List) this.references.clone();
+ List copyThat = (List) fk.references.clone();
+ Collections.sort(copyThis);
+ Collections.sort(copyThat);
+ result = copyThis.equals(copyThat);
+ }
+ }
+ return result;
+ }
+
+ public String toString() {
+ return "ForeignKey[" + this.foreignTable + "]";
+ //TODO show references
}
}
1.4 +2 -2 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/UniqueColumn.java
Index: UniqueColumn.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/UniqueColumn.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- UniqueColumn.java 19 Jul 2004 22:14:00 -0000 1.3
+++ UniqueColumn.java 14 Oct 2004 19:42:48 -0000 1.4
@@ -20,7 +20,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$
*/
public class UniqueColumn extends IndexColumn
1.5 +2 -2 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Unique.java
Index: Unique.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Unique.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- Unique.java 19 Jul 2004 22:14:00 -0000 1.4
+++ Unique.java 14 Oct 2004 19:42:48 -0000 1.5
@@ -23,7 +23,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$
*/
public class Unique extends Index
1.7 +2 -0 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Index.java
Index: Index.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Index.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- Index.java 19 Jul 2004 22:14:00 -0000 1.6
+++ Index.java 14 Oct 2004 19:42:48 -0000 1.7
@@ -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;
@@ -46,6 +47,7 @@
public void setName(String name)
{
+ if ( name == null ) throw new IllegalArgumentException("Null index name");
this.name = name;
}
1.5 +5 -92 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java
Index: JdbcToSchemaTask.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- JdbcToSchemaTask.java 28 Feb 2004 03:35:48 -0000 1.4
+++ JdbcToSchemaTask.java 14 Oct 2004 19:42:48 -0000 1.5
@@ -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;
- }
}
1.5 +237 -34 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/DDLTask.java
Index: DDLTask.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/DDLTask.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- DDLTask.java 28 Feb 2004 03:35:48 -0000 1.4
+++ DDLTask.java 14 Oct 2004 19:42:48 -0000 1.5
@@ -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;
}
-
-
}
1.7 +3 -1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/DDLExecutor.java
Index: DDLExecutor.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/DDLExecutor.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- DDLExecutor.java 18 Jul 2004 21:55:58 -0000 1.6
+++ DDLExecutor.java 14 Oct 2004 19:42:48 -0000 1.7
@@ -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;
@@ -204,6 +204,8 @@
closeStatement(statement);
returnConnection(connection);
}
+
+ return errors;
}
1.8 +36 -19 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io/JdbcModelReader.java
Index: JdbcModelReader.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io/JdbcModelReader.java,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -r1.7 -r1.8
--- JdbcModelReader.java 18 Jul 2004 21:55:58 -0000 1.7
+++ JdbcModelReader.java 14 Oct 2004 19:42:48 -0000 1.8
@@ -26,6 +26,8 @@
import java.util.List;
import java.util.Map;
import java.util.Vector;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
@@ -52,6 +54,7 @@
String catalog = null;
String schema = null;
String[] tableTypes = { "TABLE", "VIEW" };
+ Pattern defaultPattern = Pattern.compile("\\(\\'?(.*?)\\'?\\)"); //value with parenthesis and/or quotes around it
public JdbcModelReader() {
}
@@ -159,9 +162,7 @@
t1.setName(tableName);
tables.add(t1);
}
- if (tableData != null) {
- tableData.close();
- }
+
Iterator i = tables.iterator();
while (i.hasNext()) {
Table t = (Table) i.next();
@@ -288,11 +289,12 @@
* An empty string means nobody knows.
* We make the assumption that "NO" means no, anything else means
* yes.
+ * jmm - sometimes (not always) jTDS/mssql would return "NO ", which doesn't match "NO", hence the trim
*/
boolean columnIsNullable = false;
- if (columnInfoColumns.contains("IS_NULLABLE")
- && "NO".equalsIgnoreCase(
- columnData.getString("IS_NULLABLE"))) {
+ String isNullableValue = columnInfoColumns.contains("IS_NULLABLE") ? columnData.getString("IS_NULLABLE") : null;
+ if ( isNullableValue != null ) isNullableValue = isNullableValue.trim();
+ if ( "NO".equalsIgnoreCase(isNullableValue) ) {
columnIsNullable = false;
}
else {
@@ -337,13 +339,21 @@
col.setTypeCode(columnType);
col.setSize(columnSize);
col.setRequired(!columnIsNullable);
- col.setDefaultValue(columnDefaultValue);
if (primaryKeys.contains(col.getName())) {
col.setPrimaryKey(true);
}
else {
col.setPrimaryKey(false);
}
+
+ //sometimes the default comes back with parenthesis around it (jTDS/mssql)
+ if ( columnDefaultValue != null ) {
+ Matcher m = defaultPattern.matcher(columnDefaultValue);
+ if ( m.matches() ) {
+ columnDefaultValue = m.group(1);
+ }
+ col.setDefaultValue(columnDefaultValue);
+ }
col.setPrecisionRadix(columnPrecision);
col.setScale(columnScale);
columns.add(col);
@@ -463,7 +473,12 @@
//importedKeyInitiallyDeferred - see SQL92 for definition
//importedKeyInitiallyImmediate - see SQL92 for definition
//importedKeyNotDeferrable - see SQL92 for definition
- if (!pkTable.equals(prevPkTable)) {
+
+ //if tables are different OR fk field is first within fk
+ //need new FK object
+ //without this two fks to the same table on different fields in the source table
+ //get put in the same pk
+ if (!pkTable.equals(prevPkTable) || keySequence == 1) {
if (currFk != null) {
fks.add(currFk);
}
@@ -495,34 +510,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 +548,7 @@
}
}
}
-
+
return new Vector(indexesByName.values());
}
1.3 +2 -2 jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task/TestDDLTask.java
Index: TestDDLTask.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task/TestDDLTask.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- TestDDLTask.java 28 Feb 2004 03:35:49 -0000 1.2
+++ TestDDLTask.java 14 Oct 2004 19:42:48 -0000 1.3
@@ -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