001: package org.apache.torque.task;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.io.FileOutputStream;
023: import java.io.PrintWriter;
024: import java.sql.Connection;
025: import java.sql.DatabaseMetaData;
026: import java.sql.DriverManager;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029: import java.sql.Types;
030: import java.util.ArrayList;
031: import java.util.Collection;
032: import java.util.Hashtable;
033: import java.util.Iterator;
034: import java.util.List;
035:
036: import org.apache.commons.lang.StringUtils;
037: import org.apache.tools.ant.BuildException;
038: import org.apache.tools.ant.Project;
039: import org.apache.tools.ant.Task;
040: import org.apache.torque.engine.database.model.TypeMap;
041: import org.apache.torque.engine.database.transform.DTDResolver;
042: import org.apache.xerces.dom.DocumentImpl;
043: import org.apache.xerces.dom.DocumentTypeImpl;
044: import org.apache.xml.serialize.Method;
045: import org.apache.xml.serialize.OutputFormat;
046: import org.apache.xml.serialize.XMLSerializer;
047: import org.w3c.dom.Element;
048:
049: /**
050: * This class generates an XML schema of an existing database from
051: * JDBC metadata.
052: *
053: * @author <a href="mailto:jvanzyl@periapt.com">Jason van Zyl</a>
054: * @author <a href="mailto:fedor.karpelevitch@barra.com">Fedor Karpelevitch</a>
055: * @version $Id: TorqueJDBCTransformTask.java 502761 2007-02-02 21:52:05Z tfischer $
056: */
057: public class TorqueJDBCTransformTask extends Task {
058: /** Name of XML database schema produced. */
059: protected String xmlSchema;
060:
061: /** JDBC URL. */
062: protected String dbUrl;
063:
064: /** JDBC driver. */
065: protected String dbDriver;
066:
067: /** JDBC user name. */
068: protected String dbUser;
069:
070: /** JDBC password. */
071: protected String dbPassword;
072:
073: /** DB schema to use. */
074: protected String dbSchema;
075:
076: /** DOM document produced. */
077: protected DocumentImpl doc;
078:
079: /** The document root element. */
080: protected Element databaseNode;
081:
082: /** Hashtable of columns that have primary keys. */
083: protected Hashtable primaryKeys;
084:
085: /** Hashtable to track what table a column belongs to. */
086: protected Hashtable columnTableMap;
087:
088: protected boolean sameJavaName;
089:
090: private XMLSerializer xmlSerializer;
091:
092: public String getDbSchema() {
093: return dbSchema;
094: }
095:
096: public void setDbSchema(String dbSchema) {
097: this .dbSchema = dbSchema;
098: }
099:
100: public void setDbUrl(String v) {
101: dbUrl = v;
102: }
103:
104: public void setDbDriver(String v) {
105: dbDriver = v;
106: }
107:
108: public void setDbUser(String v) {
109: dbUser = v;
110: }
111:
112: public void setDbPassword(String v) {
113: dbPassword = v;
114: }
115:
116: public void setOutputFile(String v) {
117: xmlSchema = v;
118: }
119:
120: public void setSameJavaName(boolean v) {
121: this .sameJavaName = v;
122: }
123:
124: public boolean isSameJavaName() {
125: return this .sameJavaName;
126: }
127:
128: /**
129: * Default constructor.
130: *
131: * @throws BuildException
132: */
133: public void execute() throws BuildException {
134: log("Torque - JDBCToXMLSchema starting");
135: log("Your DB settings are:");
136: log("driver : " + dbDriver);
137: log("URL : " + dbUrl);
138: log("user : " + dbUser);
139: // log("password : " + dbPassword);
140: log("schema : " + dbSchema);
141:
142: DocumentTypeImpl docType = new DocumentTypeImpl(null,
143: "database", null, DTDResolver.WEB_SITE_DTD);
144: doc = new DocumentImpl(docType);
145: doc.appendChild(doc
146: .createComment(" Autogenerated by JDBCToXMLSchema! "));
147:
148: try {
149: generateXML();
150: log(xmlSchema);
151: xmlSerializer = new XMLSerializer(new PrintWriter(
152: new FileOutputStream(xmlSchema)), new OutputFormat(
153: Method.XML, null, true));
154: xmlSerializer.serialize(doc);
155: } catch (Exception e) {
156: throw new BuildException(e);
157: }
158: log("Torque - JDBCToXMLSchema finished");
159: }
160:
161: /**
162: * Generates an XML database schema from JDBC metadata.
163: *
164: * @throws Exception a generic exception.
165: */
166: public void generateXML() throws Exception {
167: // Load the Interbase Driver.
168: Class.forName(dbDriver);
169: log("DB driver sucessfuly instantiated");
170:
171: Connection con = null;
172: try {
173: // Attempt to connect to a database.
174: con = DriverManager
175: .getConnection(dbUrl, dbUser, dbPassword);
176: log("DB connection established");
177:
178: // Get the database Metadata.
179: DatabaseMetaData dbMetaData = con.getMetaData();
180:
181: // The database map.
182: List tableList = getTableNames(dbMetaData);
183:
184: databaseNode = doc.createElement("database");
185: databaseNode.setAttribute("name", dbUser);
186:
187: // Build a database-wide column -> table map.
188: columnTableMap = new Hashtable();
189:
190: log("Building column/table map...");
191: for (int i = 0; i < tableList.size(); i++) {
192: String curTable = (String) tableList.get(i);
193: List columns = getColumns(dbMetaData, curTable);
194:
195: for (int j = 0; j < columns.size(); j++) {
196: List col = (List) columns.get(j);
197: String name = (String) col.get(0);
198:
199: columnTableMap.put(name, curTable);
200: }
201: }
202:
203: for (int i = 0; i < tableList.size(); i++) {
204: // Add Table.
205: String curTable = (String) tableList.get(i);
206: // dbMap.addTable(curTable);
207: log("Processing table: " + curTable);
208:
209: Element table = doc.createElement("table");
210: table.setAttribute("name", curTable);
211: if (isSameJavaName()) {
212: table.setAttribute("javaName", curTable);
213: }
214:
215: // Add Columns.
216: // TableMap tblMap = dbMap.getTable(curTable);
217:
218: List columns = getColumns(dbMetaData, curTable);
219: List primKeys = getPrimaryKeys(dbMetaData, curTable);
220: Collection forgnKeys = getForeignKeys(dbMetaData,
221: curTable);
222:
223: // Set the primary keys.
224: primaryKeys = new Hashtable();
225:
226: for (int k = 0; k < primKeys.size(); k++) {
227: String curPrimaryKey = (String) primKeys.get(k);
228: primaryKeys.put(curPrimaryKey, curPrimaryKey);
229: }
230:
231: for (int j = 0; j < columns.size(); j++) {
232: List col = (List) columns.get(j);
233: String name = (String) col.get(0);
234: Integer type = ((Integer) col.get(1));
235: int size = ((Integer) col.get(2)).intValue();
236: int scale = ((Integer) col.get(5)).intValue();
237:
238: // From DatabaseMetaData.java
239: //
240: // Indicates column might not allow NULL values. Huh?
241: // Might? Boy, that's a definitive answer.
242: /* int columnNoNulls = 0; */
243:
244: // Indicates column definitely allows NULL values.
245: /* int columnNullable = 1; */
246:
247: // Indicates NULLABILITY of column is unknown.
248: /* int columnNullableUnknown = 2; */
249:
250: Integer nullType = (Integer) col.get(3);
251: String defValue = (String) col.get(4);
252:
253: Element column = doc.createElement("column");
254: column.setAttribute("name", name);
255: if (isSameJavaName()) {
256: column.setAttribute("javaName", name);
257: }
258:
259: column.setAttribute("type", TypeMap.getTorqueType(
260: type).getName());
261:
262: if (size > 0
263: && (type.intValue() == Types.CHAR
264: || type.intValue() == Types.VARCHAR
265: || type.intValue() == Types.LONGVARCHAR
266: || type.intValue() == Types.DECIMAL || type
267: .intValue() == Types.NUMERIC)) {
268: column.setAttribute("size", String
269: .valueOf(size));
270: }
271:
272: if (scale > 0
273: && (type.intValue() == Types.DECIMAL || type
274: .intValue() == Types.NUMERIC)) {
275: column.setAttribute("scale", String
276: .valueOf(scale));
277: }
278:
279: if (nullType.intValue() == 0) {
280: column.setAttribute("required", "true");
281: }
282:
283: if (primaryKeys.containsKey(name)) {
284: column.setAttribute("primaryKey", "true");
285: }
286:
287: if (StringUtils.isNotEmpty(defValue)) {
288: // trim out parens & quotes out of def value.
289: // makes sense for MSSQL. not sure about others.
290: if (defValue.startsWith("(")
291: && defValue.endsWith(")")) {
292: defValue = defValue.substring(1, defValue
293: .length() - 1);
294: }
295:
296: if (defValue.startsWith("'")
297: && defValue.endsWith("'")) {
298: defValue = defValue.substring(1, defValue
299: .length() - 1);
300: }
301:
302: column.setAttribute("default", defValue);
303: }
304: table.appendChild(column);
305: }
306:
307: // Foreign keys for this table.
308: for (Iterator l = forgnKeys.iterator(); l.hasNext();) {
309: Object[] forKey = (Object[]) l.next();
310: String foreignKeyTable = (String) forKey[0];
311: List refs = (List) forKey[1];
312: Element fk = doc.createElement("foreign-key");
313: fk.setAttribute("foreignTable", foreignKeyTable);
314: for (int m = 0; m < refs.size(); m++) {
315: Element ref = doc.createElement("reference");
316: String[] refData = (String[]) refs.get(m);
317: ref.setAttribute("local", refData[0]);
318: ref.setAttribute("foreign", refData[1]);
319: fk.appendChild(ref);
320: }
321: table.appendChild(fk);
322: }
323: databaseNode.appendChild(table);
324: }
325: doc.appendChild(databaseNode);
326: } finally {
327: if (con != null) {
328: con.close();
329: con = null;
330: }
331: }
332: }
333:
334: /**
335: * Get all the table names in the current database that are not
336: * system tables.
337: *
338: * @param dbMeta JDBC database metadata.
339: * @return The list of all the tables in a database.
340: * @throws SQLException
341: */
342: public List getTableNames(DatabaseMetaData dbMeta)
343: throws SQLException {
344: log("Getting table list...");
345: List tables = new ArrayList();
346: ResultSet tableNames = null;
347: // these are the entity types we want from the database
348: String[] types = { "TABLE", "VIEW" };
349: try {
350: tableNames = dbMeta.getTables(null, dbSchema, "%", types);
351: while (tableNames.next()) {
352: String name = tableNames.getString(3);
353: tables.add(name);
354: }
355: } finally {
356: if (tableNames != null) {
357: tableNames.close();
358: }
359: }
360: return tables;
361: }
362:
363: /**
364: * Retrieves all the column names and types for a given table from
365: * JDBC metadata. It returns a List of Lists. Each element
366: * of the returned List is a List with:
367: *
368: * element 0 => a String object for the column name.
369: * element 1 => an Integer object for the column type.
370: * element 2 => size of the column.
371: * element 3 => null type.
372: *
373: * @param dbMeta JDBC metadata.
374: * @param tableName Table from which to retrieve column information.
375: * @return The list of columns in <code>tableName</code>.
376: * @throws SQLException
377: */
378: public List getColumns(DatabaseMetaData dbMeta, String tableName)
379: throws SQLException {
380: List columns = new ArrayList();
381: ResultSet columnSet = null;
382: try {
383: columnSet = dbMeta.getColumns(null, dbSchema, tableName,
384: null);
385: while (columnSet.next()) {
386: String name = columnSet.getString(4);
387: Integer sqlType = new Integer(columnSet.getString(5));
388: Integer size = new Integer(columnSet.getInt(7));
389: Integer decimalDigits = new Integer(columnSet.getInt(9));
390: Integer nullType = new Integer(columnSet.getInt(11));
391: String defValue = columnSet.getString(13);
392:
393: List col = new ArrayList(6);
394: col.add(name);
395: col.add(sqlType);
396: col.add(size);
397: col.add(nullType);
398: col.add(defValue);
399: col.add(decimalDigits);
400: columns.add(col);
401: }
402: } finally {
403: if (columnSet != null) {
404: columnSet.close();
405: }
406: }
407: return columns;
408: }
409:
410: /**
411: * Retrieves a list of the columns composing the primary key for a given
412: * table.
413: *
414: * @param dbMeta JDBC metadata.
415: * @param tableName Table from which to retrieve PK information.
416: * @return A list of the primary key parts for <code>tableName</code>.
417: * @throws SQLException
418: */
419: public List getPrimaryKeys(DatabaseMetaData dbMeta, String tableName)
420: throws SQLException {
421: List pk = new ArrayList();
422: ResultSet parts = null;
423: try {
424: parts = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
425: while (parts.next()) {
426: pk.add(parts.getString(4));
427: }
428: } finally {
429: if (parts != null) {
430: parts.close();
431: }
432: }
433: return pk;
434: }
435:
436: /**
437: * Retrieves a list of foreign key columns for a given table.
438: *
439: * @param dbMeta JDBC metadata.
440: * @param tableName Table from which to retrieve FK information.
441: * @return A list of foreign keys in <code>tableName</code>.
442: * @throws SQLException
443: */
444: public Collection getForeignKeys(DatabaseMetaData dbMeta,
445: String tableName) throws SQLException {
446: Hashtable fks = new Hashtable();
447: ResultSet foreignKeys = null;
448: try {
449: foreignKeys = dbMeta.getImportedKeys(null, dbSchema,
450: tableName);
451: while (foreignKeys.next()) {
452: String refTableName = foreignKeys.getString(3);
453: String fkName = foreignKeys.getString(12);
454: // if FK has no name - make it up (use tablename instead)
455: if (fkName == null) {
456: fkName = refTableName;
457: }
458: Object[] fk = (Object[]) fks.get(fkName);
459: List refs;
460: if (fk == null) {
461: fk = new Object[2];
462: fk[0] = refTableName; //referenced table name
463: refs = new ArrayList();
464: fk[1] = refs;
465: fks.put(fkName, fk);
466: } else {
467: refs = (ArrayList) fk[1];
468: }
469: String[] ref = new String[2];
470: ref[0] = foreignKeys.getString(8); //local column
471: ref[1] = foreignKeys.getString(4); //foreign column
472: refs.add(ref);
473: }
474: } catch (SQLException e) {
475: // this seems to be happening in some db drivers (sybase)
476: // when retrieving foreign keys from views.
477: log("WARN: Could not read foreign keys for Table "
478: + tableName + " : " + e.getMessage(),
479: Project.MSG_WARN);
480: } finally {
481: if (foreignKeys != null) {
482: foreignKeys.close();
483: }
484: }
485: return fks.values();
486: }
487: }
|