You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by John M <in...@yahoo.com> on 2004/05/24 21:02:51 UTC
[sql] [patch] sql server improvments, fix for prior patch
I had previously submitted a patch for the sql
project, and it was finally accepted, sort of. It
seems only part of the patch was merged. I have
included the other parts that didn't make it before,
as well as some fixes for SQL Server DDL generation
that I discovered while working on a new project with
it. Alas, some of the changes in the patch are mere
whitespace changes, but I didn't feel like trying to
undo all of those.
John Marshall
Connectria
I couldn't get this to go as an attachment. If you
want me to send it somewhere else let me know.
=================================================
cvs diff -u
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();
}
__________________________________
Do you Yahoo!?
Yahoo! Domains � Claim yours for only $14.70/year
http://smallbusiness.promotions.yahoo.com/offer
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [sql] [patch] sql server improvments, fix for prior patch
Posted by "matthew.hawthorne" <ma...@apache.org>.
John M wrote:
> I had previously submitted a patch for the sql
> project, and it was finally accepted, sort of. It
> seems only part of the patch was merged. I have
> included the other parts that didn't make it before,
> as well as some fixes for SQL Server DDL generation
> that I discovered while working on a new project with
> it. Alas, some of the changes in the patch are mere
> whitespace changes, but I didn't feel like trying to
> undo all of those.
Please submit your patches to bugzilla.
If I remember the patches you submitted before correctly, there were
reasons that I left pieces out. There were some tests that connected
to a local mysql database, which just won't work on everyone's machine.
There also was some maven.xml magic that I didn't view as necessary.
I'll try to take another look at those parts, to make sure I didn't
misunderstand something.
Unfortunately, I think I'm the only person who is involved with [sql] these
days, and I've been struggling to find time to get it moved over to the
db-commons. This is something that I've been wanting to do since
the fall. So, bear with me -- I'm not sure when I'll get to commit these.
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org