Hi,
Try the JDBCtoXML utility.
You have to code some places in the Turbine code and compile Torque again.
You have then a Torque.jar wich contains the programm JDBCtoXML.
It sets up an JDBC connection with Informix and extracts all Info over META
Data from
every table in it. The result is a XML Description of all tables wich then
can be parsed
through Torque to generate Peer Object's.
Here is the modified code:
{copy this in the file JDBCtoXml.java and change the DB URL in the file}
----------------------------------------------------------------------------
------------------------------------------
package org.apache.turbine.torque.ant;
/*
* Copyright (c) 1997-2000 The Java Apache Project. 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. All advertising materials mentioning features or use of this
* software must display the following acknowledgment:
* "This product includes software developed by the Java Apache
* Project for use in the Apache JServ servlet engine project
* <http://java.apache.org/>."
*
* 4. The names "Apache JServ", "Apache JServ Servlet Engine", "Turbine",
* "Apache Turbine", "Turbine Project", "Apache Turbine Project" and
* "Java Apache Project" must not be used to endorse or promote products
* derived from this software without prior written permission.
*
* 5. Products derived from this software may not be called "Apache JServ"
* nor may "Apache" nor "Apache JServ" appear in their names without
* prior written permission of the Java Apache Project.
*
* 6. Redistributions of any form whatsoever must retain the following
* acknowledgment:
* "This product includes software developed by the Java Apache
* Project for use in the Apache JServ servlet engine project
* <http://java.apache.org/>."
*
* THIS SOFTWARE IS PROVIDED BY THE JAVA APACHE PROJECT "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 JAVA APACHE PROJECT 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 Java Apache Group. For more information
* on the Java Apache Project and the Apache JServ Servlet Engine project,
* please see <http://java.apache.org/>.
*
*/
// Java stuff.
import java.sql.*;
import java.io.*;
import java.util.*;
// W3C stuff.
import org.w3c.dom.*;
// XML stuff.
import org.apache.xerces.dom.*;
import org.apache.xml.serialize.*;
/**
* This class generates an XML schema of an existing database from
* JDBC metadata.
*
* @author <a href="mailto:[EMAIL PROTECTED]">Jason van Zyl</a>
* @version $Id: JDBCToXMLSchema.java,v 1.1 2000/11/07 23:38:11 jvanzyl Exp
$
*/
public class JDBCToXMLSchema
{
/** Torque properties. */
protected Properties props;
/** 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;
/** DOM document produced. */
protected DocumentImpl doc;
/** Database Node to start things off. */
protected Node database;
/** Hashtable of columns that have primary keys. */
protected Hashtable primaryKeys;
/** Hashtable to track what table a column belongs to. */
protected Hashtable columnTableMap;
/** Map of java.sql.Types: integer -> string representation. */
//protected Properties sqlTypes;
protected Hashtable sqlTypes;
XMLSerializer xmlSerializer;
/**
* Default constructor.
*/
public JDBCToXMLSchema()
{
//props = Utils.loadProps("config/torque.props");
//sqlTypes = Utils.loadProps("config/torque.types");
// This is just to get this thing to compile.
// This has to be turned into an Ant task proper.
props = new Properties();
sqlTypes = new Properties();
// xmlSchema = props.getProperty("jdbcXMLSchema");
// dbUrl = props.getProperty("dbUrl");
// dbDriver = props.getProperty("dbDriver");
// dbUser = props.getProperty("dbUser");
// dbPassword = props.getProperty("dbPassword");
xmlSchema = new String ("schema/test");
dbUrl = new
String("jdbc:informix-sqli://100.50.10.30:800:informixserver=server;database
=turbinehost");
dbDriver = new String("com.informix.jdbc.IfxDriver");
dbUser = new String("icbadm");
dbPassword = new String("tasskaff");
System.err.println("Torque - JDBCToXMLSchema 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);
sqlTypes = new Hashtable();
sqlTypes.put(new String("BIT"),new Integer(-7));
sqlTypes.put(new String("TINYINT"),new Integer(-6));
sqlTypes.put(new String("SMALLINT"),new Integer(5));
sqlTypes.put(new String("INTEGER"),new Integer(4));
sqlTypes.put(new String("BIGINT"),new Integer(-5));
sqlTypes.put(new String("FLOAT"),new Integer(6));
sqlTypes.put(new String("REAL"),new Integer(7));
sqlTypes.put(new String("DOUBLE"),new Integer(8));
sqlTypes.put(new String("NUMERIC"),new Integer(2));
sqlTypes.put(new String("DECIMAL"),new Integer(3));
sqlTypes.put(new String("CHAR"),new Integer(1));
sqlTypes.put(new String("VARCHAR"),new Integer(12));
sqlTypes.put(new String("LONGVARCHAR"),new Integer(-1));
sqlTypes.put(new String("DATE"),new Integer(91));
sqlTypes.put(new String("TIME"),new Integer(92));
sqlTypes.put(new String("TIMESTAMP"),new Integer(93));
sqlTypes.put(new String("BINARY"),new Integer(-2));
sqlTypes.put(new String("VARBINARY"),new Integer( -3));
sqlTypes.put(new String("LONGVARBINARY"),new Integer(-4));
sqlTypes.put(new String("NULL"),new Integer(0));
sqlTypes.put(new String("OTHER"),new Integer(1111));
sqlTypes.put(new String("JAVA_OBJECT"),new Integer(2000));
sqlTypes.put(new String("DISTINCT"),new Integer(2001));
sqlTypes.put(new String("STRUCT"),new Integer(2002));
sqlTypes.put(new String("ARRAY"),new Integer(2003));
sqlTypes.put(new String("BLOB"),new Integer(2004));
sqlTypes.put(new String("CLOB"),new Integer(2005));
sqlTypes.put(new String("REF"),new Integer(2006));
sqlTypes.put((new Integer(-7)).toString(),new String("BIT"));
sqlTypes.put((new Integer(-6)).toString(),new String("TINYINT"));
sqlTypes.put((new Integer(5)).toString(),new String("SMALLINT"));
sqlTypes.put((new Integer(4)).toString(),new String("INTEGER"));
sqlTypes.put((new Integer(-5)).toString(),new String("BIGINT"));
sqlTypes.put((new Integer(6)).toString(),new String("FLOAT"));
sqlTypes.put((new Integer(7)).toString(),new String("REAL"));
sqlTypes.put((new Integer(8)).toString(),new String("DOUBLE"));
sqlTypes.put((new Integer(2)).toString(),new String("NUMERIC"));
sqlTypes.put((new Integer(3)).toString(),new String("DECIMAL"));
sqlTypes.put((new Integer(1)).toString(),new String("CHAR"));
sqlTypes.put((new Integer(12)).toString(),new String("VARCHAR"));
sqlTypes.put((new Integer(-1)).toString(),new String("LONGVARCHAR"));
sqlTypes.put((new Integer(91)).toString(),new String("DATE"));
sqlTypes.put((new Integer(92)).toString(),new String("TIME"));
sqlTypes.put((new Integer(93)).toString(),new String("TIMESTAMP"));
sqlTypes.put((new Integer(-2)).toString(),new String("BINARY"));
sqlTypes.put((new Integer(-3)).toString(),new String("VARBINARY"));
sqlTypes.put((new Integer(-4)).toString(),new String("LONGVARBINARY"));
sqlTypes.put((new Integer(0)).toString(),new String("NULL"));
sqlTypes.put((new Integer(1111)).toString(),new String("OTHER"));
sqlTypes.put((new Integer(2000)).toString(),new String("JAVA_OBJECT"));
sqlTypes.put((new Integer(2001)).toString(),new String("DISTINCT"));
sqlTypes.put((new Integer(2002)).toString(),new String("STRUCT"));
sqlTypes.put((new Integer(2003)).toString(),new String("ARRAY"));
sqlTypes.put((new Integer(2004)).toString(),new String("BLOB"));
sqlTypes.put((new Integer(2005)).toString(),new String("CLOB"));
sqlTypes.put((new Integer(2006)).toString(),new String("REF"));
doc = new DocumentImpl();
doc.appendChild(doc.createComment(" Autogenerated by
JDBCToXMLSchema! "));
try
{
generateXML();
xmlSerializer = new XMLSerializer(
new PrintWriter(
new FileOutputStream(xmlSchema)),
new OutputFormat(Method.XML,null,true));
xmlSerializer.serialize(doc);
}
catch (Exception e)
{
System.err.println(e);
e.printStackTrace();
}
System.err.println("\nTorque - JDBCToXMLSchema finished");
}
/**
* Generates an XML database schema from JDBC metadata.
*
* @exception Exception, a generic exception.
*/
public void generateXML()
throws Exception
{
// Load the Interbase 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");
System.err.println("DB fetching Metadata");
// Get the database Metadata.
DatabaseMetaData dbMetaData = con.getMetaData();
System.err.println("Storing Tablenames in Vector");
// The database map.
Vector tableList = getTableNames(dbMetaData);
System.err.println("Building a database-wide column -table map");
database = doc.createElement("database");
// Build a database-wide column -> table map.
columnTableMap = new Hashtable();
for (int i = 0; i < tableList.size(); i++)
{
String curTable = (String) tableList.elementAt(i);
Vector columns = getColumns(dbMetaData, curTable);
for (int j = 0; j < columns.size(); j++)
{
Vector v = (Vector) columns.elementAt(j);
String name = (String) v.elementAt(0);
System.err.println(curTable + "." + name);
columnTableMap.put(name, curTable);
}
System.err.println("--------------------------------");
}
for (int i = 0; i < tableList.size(); i++)
{
// Add Table.
String curTable = (String) tableList.elementAt(i);
// dbMap.addTable(curTable);
Element table = doc.createElement("table");
table.setAttribute("name", curTable);
// Add Columns.
// TableMap tblMap = dbMap.getTable(curTable);
Vector columns = getColumns(dbMetaData, curTable);
Vector primKeys = getPrimaryKeys(dbMetaData, curTable);
Vector forgnKeys = getForeignKeys(dbMetaData, curTable);
// Set the primary keys.
primaryKeys = new Hashtable();
for (int k = 0; k < primKeys.size(); k++)
{
String curPrimaryKey = (String) primKeys.elementAt(k);
primaryKeys.put(curPrimaryKey, curPrimaryKey);
}
// Foreign keys for this table.
for (int l = 0; l < forgnKeys.size(); l++)
{
String curForeignKey = (String) forgnKeys.elementAt(l);
String foreignKeyTable =
(String) columnTableMap.get(curForeignKey);
System.out.println(curForeignKey + " => " +
foreignKeyTable);
}
for (int j = 0; j < columns.size(); j++)
{
Vector v = (Vector) columns.elementAt(j);
String name = (String) v.elementAt(0);
int type = ((Integer) v.elementAt(1)).intValue();
int size = ((Integer) v.elementAt(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) v.elementAt(3);
Element column = doc.createElement("column");
System.err.println(name + "->" + (String)sqlTypes.get(new
Integer(type).toString()));
column.setAttribute("name", name);
column.setAttribute("type",
(String) sqlTypes.get(
new Integer(type).toString()));
if (size > 0 &&
(type == Types.CHAR ||
type == Types.VARCHAR ||
type == Types.LONGVARCHAR))
{
column.setAttribute("size",
new Integer(size).toString());
}
if (nullType.intValue() == 0)
{
column.setAttribute("null", "false");
}
if (primaryKeys.containsKey(name))
{
column.setAttribute("primaryKey", "true");
}
table.appendChild(column);
}
database.appendChild(table);
}
doc.appendChild(database);
}
/**
* Get all the table names in the current database that are not
* system tables.
*
* @param dbMeta JDBC database metadata.
* @return A Vector with all the tables in a database.
* @exception SQLException.
*/
public Vector getTableNames(DatabaseMetaData dbMeta)
throws SQLException
{
ResultSet tableNames = dbMeta.getTables("",null, "%",null);
Vector tables = new Vector();
while (tableNames.next())
{
String name = tableNames.getString(3);
String type = tableNames.getString(4);
if (type.equals("TABLE"))
{
tables.addElement(name);
}
}
return tables;
}
/**
* Retrieves all the column names and types for a given table from
* JDBC metadata. It returns a vector of vectors. Each element
* of the returned vector is a vector 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 A Vector with the list of columns in tableName.
*/
public Vector getColumns(DatabaseMetaData dbMeta,
String tableName)
throws SQLException
{
ResultSet columnSet = dbMeta.getColumns("",null, tableName, null);
Vector columns = new Vector();
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));
Vector v = new Vector();
v.addElement (name);
v.addElement (sqlType);
v.addElement (size);
v.addElement (nullType);
columns.addElement (v);
}
return columns;
}
/**
* Retrieves a list of primary key columns for a given table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve PK information.
* @return A Vector with a list of PKs for tableName.
*/
public Vector getPrimaryKeys(DatabaseMetaData dbMeta,
String tableName)
throws SQLException
{
ResultSet primaryKeys = dbMeta.getPrimaryKeys("",null, tableName);
Vector keys = new Vector();
while (primaryKeys.next())
{
keys.addElement(primaryKeys.getString(4));
}
return keys;
}
/**
* 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 Vector with a list of foreign keys in tableName.
*/
public Vector getForeignKeys(DatabaseMetaData dbMeta,
String tableName)
throws SQLException
{
ResultSet foreignKeys = dbMeta.getImportedKeys("",null, tableName);
Vector keys = new Vector();
while (foreignKeys.next())
{
keys.addElement(foreignKeys.getString(8));
}
return keys;
}
/**
* Main function.
*
* @param args A String[] with the command line arguments.
* @exception Exception, a generic exception.
*/
static public void main (String[] args)
throws Exception
{
new JDBCToXMLSchema();
}
}
Good luck .......
Roland Ettema
Debis Systemhaus GEI GmbH
Pascalstra�e 14���������� Postfach 50 01 44
D-52076 Aachen��������� D-52085 Aachen
Telefon: (49) 24 08 / 943 867
Fax:���� (49) 24 08 / 943 354
E-Mail:� [EMAIL PROTECTED]
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
