Greetings Martin and all,
The task TorqueJDBCTransformTask does support the generation of XML
schema for tables with columns, primary key and foreign keys, but not
for index and unique constraints. Are there reasons behind ? The task
should have generated the index and unique constraints as parts of the
XML schema.
Here, I have written a method for retrieving the index information for
tables. Please review :
/**
* Retrieves a list of unique/non unique indices for a given table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve index information.
* @param unique Flag to indicate whther to retrieve unique/non
unique indices.
* @return A list of unique/non unique indices in
<code>tableName</code>.
* @throws SQLException
*/
public Collection getIndices(DatabaseMetaData dbMeta, String
tableName, boolean unique)
throws SQLException
{
Hashtable indices = new Hashtable();
ResultSet indexInfo = null;
try
{
indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName,
unique, false);
while (indexInfo.next())
{
short type = indexInfo.getShort(7);
if (type != DatabaseMetaData.tableIndexStatistic)
{
boolean nonUnique = indexInfo.getBoolean(4);
if (nonUnique != unique)
{
String indexName = indexInfo.getString(6);
short ordinalPosition = indexInfo.getShort(8);
String columnName = indexInfo.getString(9);
Object[] index = (Object[])
indices.get(indexName);
Vector indexColumns;
if (index == null)
{
index = new Object[2];
index[0] = indexName;
indexColumns = new Vector();
}
else
{
indexColumns = (Vector) index[1];
}
if (indexColumns.size() < ordinalPosition) {
indexColumns.setSize(ordinalPosition);
}
indexColumns.setElementAt(columnName,
ordinalPosition - 1);
index[1] = indexColumns;
indices.put(indexName, index);
}
}
}
}
finally
{
if (indexInfo != null)
{
indexInfo.close();
}
}
return indices.values();
}
Attached please find the updated source file for
TorqueJDBCTransformTask.java, with the new getIndices method and changes
in the execute method, which are not yet committed to the CVS.
If you all agree with the changes, I would like the updates to be
committed to the CVS. But I do not have the rights to do that, thus, can
someone, probably Martin, please help me to do this ?
Thanks.
Chiu Yee Weay
Systems Engineer
Paradigm Systems Sdn Bhd
package org.apache.torque.task;
/* ====================================================================
* The Apache Software License, Version 1.1
*
* Copyright (c) 2001-2003 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution,
* if any, must include the following acknowledgment:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowledgment may appear in the software itself,
* if and wherever such third-party acknowledgments normally appear.
*
* 4. The names "Apache" and "Apache Software Foundation" and
* "Apache Turbine" must not be used to endorse or promote products
* derived from this software without prior written permission. For
* written permission, please contact [EMAIL PROTECTED]
*
* 5. Products derived from this software may not be called "Apache",
* "Apache Turbine", nor may "Apache" appear in their name, without
* prior written permission of the Apache Software Foundation.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the Apache Software Foundation. For more
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*/
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.Task;
import org.apache.torque.engine.database.model.TypeMap;
import org.apache.torque.engine.database.transform.DTDResolver;
import org.apache.xerces.dom.DocumentImpl;
import org.apache.xerces.dom.DocumentTypeImpl;
import org.apache.xml.serialize.Method;
import org.apache.xml.serialize.OutputFormat;
import org.apache.xml.serialize.XMLSerializer;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
/**
* This class generates an XML schema of an existing database from
* JDBC metadata.
*
* @author <a href="mailto:[EMAIL PROTECTED]">Jason van Zyl</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Fedor Karpelevitch</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Chiu Yee Weay</a>
* @version $Id: TorqueJDBCTransformTask.java,v 1.6 2003/08/22 17:01:42 mpoeschl Exp $
*/
public class TorqueJDBCTransformTask extends Task
{
/** Name of XML database schema produced. */
protected String xmlSchema;
/** JDBC URL. */
protected String dbUrl;
/** JDBC driver. */
protected String dbDriver;
/** JDBC user name. */
protected String dbUser;
/** JDBC password. */
protected String dbPassword;
/** DB schema to use. */
protected String dbSchema;
/** DOM document produced. */
protected DocumentImpl doc;
/** The document root element. */
protected Node databaseNode;
/** Hashtable of columns that have primary keys. */
protected Hashtable primaryKeys;
/** Hashtable to track what table a column belongs to. */
protected Hashtable columnTableMap;
protected boolean sameJavaName;
private XMLSerializer xmlSerializer;
public String getDbSchema()
{
return dbSchema;
}
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)
{
xmlSchema = v;
}
public void setSameJavaName(boolean v)
{
this.sameJavaName = v;
}
public boolean isSameJavaName()
{
return this.sameJavaName;
}
/**
* Default constructor.
*
* @throws BuildException
*/
public void execute() throws BuildException
{
log("Torque - JDBCToXMLSchema starting");
log("Your DB settings are:");
log("driver : " + dbDriver);
log("URL : " + dbUrl);
log("user : " + dbUser);
// log("password : " + dbPassword);
log("schema : " + dbSchema);
DocumentTypeImpl docType = new DocumentTypeImpl(null, "database", null,
DTDResolver.WEB_SITE_DTD);
doc = new DocumentImpl(docType);
doc.appendChild(doc.createComment(
" Autogenerated by JDBCToXMLSchema! "));
try
{
generateXML();
log(xmlSchema);
xmlSerializer = new XMLSerializer(
new PrintWriter(
new FileOutputStream(xmlSchema)),
new OutputFormat(Method.XML, null, true));
xmlSerializer.serialize(doc);
}
catch (Exception e)
{
throw new BuildException(e);
}
log("Torque - JDBCToXMLSchema finished");
}
/**
* Generates an XML database schema from JDBC metadata.
*
* @throws Exception a generic exception.
*/
public void generateXML() throws Exception
{
// Load the Interbase Driver.
Class.forName(dbDriver);
log("DB driver sucessfuly instantiated");
// Attemtp to connect to a database.
Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
log("DB connection established");
// Get the database Metadata.
DatabaseMetaData dbMetaData = con.getMetaData();
// The database map.
List tableList = getTableNames(dbMetaData);
databaseNode = doc.createElement("database");
// Build a database-wide column -> table map.
columnTableMap = new Hashtable();
log("Building column/table map...");
for (int i = 0; i < tableList.size(); i++)
{
String curTable = (String) tableList.get(i);
List columns = getColumns(dbMetaData, curTable);
for (int j = 0; j < columns.size(); j++)
{
List col = (List) columns.get(j);
String name = (String) col.get(0);
columnTableMap.put(name, curTable);
}
}
for (int i = 0; i < tableList.size(); i++)
{
// Add Table.
String curTable = (String) tableList.get(i);
// dbMap.addTable(curTable);
log("Processing table: " + curTable);
Element table = doc.createElement("table");
table.setAttribute("name", curTable);
if (isSameJavaName())
{
table.setAttribute("javaName", curTable);
}
// Add Columns.
// TableMap tblMap = dbMap.getTable(curTable);
List columns = getColumns(dbMetaData, curTable);
List primKeys = getPrimaryKeys(dbMetaData, curTable);
Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
Collection indices = getIndices(dbMetaData, curTable, false);
Collection uniques = getIndices(dbMetaData, curTable, true);
// Set the primary keys.
primaryKeys = new Hashtable();
for (int k = 0; k < primKeys.size(); k++)
{
String curPrimaryKey = (String) primKeys.get(k);
primaryKeys.put(curPrimaryKey, curPrimaryKey);
}
for (int j = 0; j < columns.size(); j++)
{
List col = (List) columns.get(j);
String name = (String) col.get(0);
Integer type = ((Integer) col.get(1));
int size = ((Integer) col.get(2)).intValue();
// From DatabaseMetaData.java
//
// Indicates column might not allow NULL values. Huh?
// Might? Boy, that's a definitive answer.
/* int columnNoNulls = 0; */
// Indicates column definitely allows NULL values.
/* int columnNullable = 1; */
// Indicates NULLABILITY of column is unknown.
/* int columnNullableUnknown = 2; */
Integer nullType = (Integer) col.get(3);
String defValue = (String) col.get(4);
Element column = doc.createElement("column");
column.setAttribute("name", name);
if (isSameJavaName())
{
column.setAttribute("javaName", name);
}
column.setAttribute("type", TypeMap.getTorqueType(type));
if (size > 0 && (type.intValue() == Types.CHAR
|| type.intValue() == Types.VARCHAR
|| type.intValue() == Types.LONGVARCHAR
|| type.intValue() == Types.DECIMAL
|| type.intValue() == Types.NUMERIC))
{
column.setAttribute("size", String.valueOf(size));
}
if (nullType.intValue() == 0)
{
column.setAttribute("required", "true");
}
if (primaryKeys.containsKey(name))
{
column.setAttribute("primaryKey", "true");
}
if (defValue != null)
{
// trim out parens & quotes out of def value.
// makes sense for MSSQL. not sure about others.
if (defValue.startsWith("(") && defValue.endsWith(")"))
{
defValue = defValue.substring(1, defValue.length() - 1);
}
if (defValue.startsWith("'") && defValue.endsWith("'"))
{
defValue = defValue.substring(1, defValue.length() - 1);
}
column.setAttribute("default", defValue);
}
table.appendChild(column);
}
// Foreign keys for this table.
for (Iterator l = forgnKeys.iterator(); l.hasNext();)
{
Object[] forKey = (Object[]) l.next();
String foreignKeyTable = (String) forKey[0];
List refs = (List) forKey[1];
Element fk = doc.createElement("foreign-key");
fk.setAttribute("foreignTable", foreignKeyTable);
for (int m = 0; m < refs.size(); m++)
{
Element ref = doc.createElement("reference");
String[] refData = (String[]) refs.get(m);
ref.setAttribute("local", refData[0]);
ref.setAttribute("foreign", refData[1]);
fk.appendChild(ref);
}
table.appendChild(fk);
}
// Indices for this table.
for (Iterator n = indices.iterator(); n.hasNext();)
{
Object[] index = (Object[]) n.next();
String indexName = (String) index[0];
List indexColumns = (List) index[1];
Element indexElement = doc.createElement("index");
indexElement.setAttribute("name", indexName);
for (int o = 0; o < indexColumns.size(); o++)
{
String indexColumn = (indexColumns.get(o) == null ? null : (String) indexColumns.get(o));
if (indexColumn != null)
{
Element indexColumnElement = doc.createElement("index-column");
indexColumnElement.setAttribute("name", indexColumn);
indexElement.appendChild(indexColumnElement);
}
}
table.appendChild(indexElement);
}
// Uniques for this table.
for (Iterator p = uniques.iterator(); p.hasNext();)
{
Object[] unique = (Object[]) p.next();
String indexName = (String) unique[0];
List indexColumns = (List) unique[1];
Element uniqueElement = doc.createElement("unique");
uniqueElement.setAttribute("name", indexName);
boolean isPrimaryKey = (indexColumns.size() == primKeys.size());
for (int q = 0; q < indexColumns.size(); q++)
{
String indexColumn = (indexColumns.get(q) == null ? null : (String) indexColumns.get(q));
if (indexColumn != null)
{
Element uniqueColumnElement = doc.createElement("unique-column");
uniqueColumnElement.setAttribute("name", indexColumn);
uniqueElement.appendChild(uniqueColumnElement);
if (! primaryKeys.containsKey(indexColumn))
{
isPrimaryKey = false;
}
}
}
if (! isPrimaryKey)
{
table.appendChild(uniqueElement);
}
}
databaseNode.appendChild(table);
}
doc.appendChild(databaseNode);
}
/**
* Get all the table names in the current database that are not
* system tables.
*
* @param dbMeta JDBC database metadata.
* @return The list of all the tables in a database.
* @throws SQLException
*/
public List getTableNames(DatabaseMetaData dbMeta)
throws SQLException
{
log("Getting table list...");
List tables = new ArrayList();
ResultSet tableNames = null;
// these are the entity types we want from the database
String[] types = {"TABLE", "VIEW"};
try
{
tableNames = dbMeta.getTables(null, dbSchema, "%", types);
while (tableNames.next())
{
String name = tableNames.getString(3);
String type = tableNames.getString(4);
tables.add(name);
}
}
finally
{
if (tableNames != null)
{
tableNames.close();
}
}
return tables;
}
/**
* Retrieves all the column names and types for a given table from
* JDBC metadata. It returns a List of Lists. Each element
* of the returned List is a List with:
*
* element 0 => a String object for the column name.
* element 1 => an Integer object for the column type.
* element 2 => size of the column.
* element 3 => null type.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve column information.
* @return The list of columns in <code>tableName</code>.
* @throws SQLException
*/
public List getColumns(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
List columns = new ArrayList();
ResultSet columnSet = null;
try
{
columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
while (columnSet.next())
{
String name = columnSet.getString(4);
Integer sqlType = new Integer(columnSet.getString(5));
Integer size = new Integer(columnSet.getInt(7));
Integer nullType = new Integer(columnSet.getInt(11));
String defValue = columnSet.getString(13);
List col = new ArrayList(5);
col.add(name);
col.add(sqlType);
col.add(size);
col.add(nullType);
col.add(defValue);
columns.add(col);
}
}
finally
{
if (columnSet != null)
{
columnSet.close();
}
}
return columns;
}
/**
* Retrieves a list of the columns composing the primary key for a given
* table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve PK information.
* @return A list of the primary key parts for <code>tableName</code>.
* @throws SQLException
*/
public List getPrimaryKeys(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
List pk = new ArrayList();
ResultSet parts = null;
try
{
parts = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
while (parts.next())
{
pk.add(parts.getString(4));
}
}
finally
{
if (parts != null)
{
parts.close();
}
}
return pk;
}
/**
* Retrieves a list of foreign key columns for a given table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve FK information.
* @return A list of foreign keys in <code>tableName</code>.
* @throws SQLException
*/
public Collection getForeignKeys(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
Hashtable fks = new Hashtable();
ResultSet foreignKeys = null;
try
{
foreignKeys = dbMeta.getImportedKeys(null, dbSchema, tableName);
while (foreignKeys.next())
{
String refTableName = foreignKeys.getString(3);
String fkName = foreignKeys.getString(12);
// if FK has no name - make it up (use tablename instead)
if (fkName == null)
{
fkName = refTableName;
}
Object[] fk = (Object[]) fks.get(fkName);
List refs;
if (fk == null)
{
fk = new Object[2];
fk[0] = refTableName; //referenced table name
refs = new ArrayList();
fk[1] = refs;
fks.put(fkName, fk);
}
else
{
refs = (ArrayList) fk[1];
}
String[] ref = new String[2];
ref[0] = foreignKeys.getString(8); //local column
ref[1] = foreignKeys.getString(4); //foreign column
refs.add(ref);
}
}
finally
{
if (foreignKeys != null)
{
foreignKeys.close();
}
}
return fks.values();
}
/**
* Retrieves a list of unique/non unique indices for a given table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve index information.
* @param unique Flag to indicate whther to retrieve unique/non unique indices.
* @return A list of unique/non unique indices in <code>tableName</code>.
* @throws SQLException
*/
public Collection getIndices(DatabaseMetaData dbMeta, String tableName, boolean unique)
throws SQLException
{
Hashtable indices = new Hashtable();
ResultSet indexInfo = null;
try
{
indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName, unique, false);
while (indexInfo.next())
{
short type = indexInfo.getShort(7);
if (type != DatabaseMetaData.tableIndexStatistic)
{
boolean nonUnique = indexInfo.getBoolean(4);
if (nonUnique != unique)
{
String indexName = indexInfo.getString(6);
short ordinalPosition = indexInfo.getShort(8);
String columnName = indexInfo.getString(9);
Object[] index = (Object[]) indices.get(indexName);
Vector indexColumns;
if (index == null)
{
index = new Object[2];
index[0] = indexName;
indexColumns = new Vector();
}
else
{
indexColumns = (Vector) index[1];
}
if (indexColumns.size() < ordinalPosition) {
indexColumns.setSize(ordinalPosition);
}
indexColumns.setElementAt(columnName, ordinalPosition - 1);
index[1] = indexColumns;
indices.put(indexName, index);
}
}
}
}
finally
{
if (indexInfo != null)
{
indexInfo.close();
}
}
return indices.values();
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
