1 package org.apache.torque.task;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.io.FileOutputStream;
23 import java.io.PrintWriter;
24 import java.sql.Connection;
25 import java.sql.DatabaseMetaData;
26 import java.sql.DriverManager;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.sql.Types;
30 import java.util.ArrayList;
31 import java.util.Collection;
32 import java.util.Hashtable;
33 import java.util.Iterator;
34 import java.util.List;
35
36 import org.apache.commons.lang.StringUtils;
37 import org.apache.tools.ant.BuildException;
38 import org.apache.tools.ant.Project;
39 import org.apache.tools.ant.Task;
40 import org.apache.torque.engine.database.model.TypeMap;
41 import org.apache.torque.engine.database.transform.DTDResolver;
42 import org.apache.xerces.dom.DocumentImpl;
43 import org.apache.xerces.dom.DocumentTypeImpl;
44 import org.apache.xml.serialize.Method;
45 import org.apache.xml.serialize.OutputFormat;
46 import org.apache.xml.serialize.XMLSerializer;
47 import org.w3c.dom.Element;
48
49 /***
50 * This class generates an XML schema of an existing database from
51 * JDBC metadata.
52 *
53 * @author <a href="mailto:jvanzyl@periapt.com">Jason van Zyl</a>
54 * @author <a href="mailto:fedor.karpelevitch@barra.com">Fedor Karpelevitch</a>
55 * @version $Id: TorqueJDBCTransformTask.java 502761 2007-02-02 21:52:05Z tfischer $
56 */
57 public class TorqueJDBCTransformTask extends Task
58 {
59 /*** Name of XML database schema produced. */
60 protected String xmlSchema;
61
62 /*** JDBC URL. */
63 protected String dbUrl;
64
65 /*** JDBC driver. */
66 protected String dbDriver;
67
68 /*** JDBC user name. */
69 protected String dbUser;
70
71 /*** JDBC password. */
72 protected String dbPassword;
73
74 /*** DB schema to use. */
75 protected String dbSchema;
76
77 /*** DOM document produced. */
78 protected DocumentImpl doc;
79
80 /*** The document root element. */
81 protected Element databaseNode;
82
83 /*** Hashtable of columns that have primary keys. */
84 protected Hashtable primaryKeys;
85
86 /*** Hashtable to track what table a column belongs to. */
87 protected Hashtable columnTableMap;
88
89 protected boolean sameJavaName;
90
91 private XMLSerializer xmlSerializer;
92
93 public String getDbSchema()
94 {
95 return dbSchema;
96 }
97
98 public void setDbSchema(String dbSchema)
99 {
100 this.dbSchema = dbSchema;
101 }
102
103 public void setDbUrl(String v)
104 {
105 dbUrl = v;
106 }
107
108 public void setDbDriver(String v)
109 {
110 dbDriver = v;
111 }
112
113 public void setDbUser(String v)
114 {
115 dbUser = v;
116 }
117
118 public void setDbPassword(String v)
119 {
120 dbPassword = v;
121 }
122
123 public void setOutputFile (String v)
124 {
125 xmlSchema = v;
126 }
127
128 public void setSameJavaName(boolean v)
129 {
130 this.sameJavaName = v;
131 }
132
133 public boolean isSameJavaName()
134 {
135 return this.sameJavaName;
136 }
137
138 /***
139 * Default constructor.
140 *
141 * @throws BuildException
142 */
143 public void execute() throws BuildException
144 {
145 log("Torque - JDBCToXMLSchema starting");
146 log("Your DB settings are:");
147 log("driver : " + dbDriver);
148 log("URL : " + dbUrl);
149 log("user : " + dbUser);
150
151 log("schema : " + dbSchema);
152
153 DocumentTypeImpl docType = new DocumentTypeImpl(null, "database", null,
154 DTDResolver.WEB_SITE_DTD);
155 doc = new DocumentImpl(docType);
156 doc.appendChild(doc.createComment(
157 " Autogenerated by JDBCToXMLSchema! "));
158
159 try
160 {
161 generateXML();
162 log(xmlSchema);
163 xmlSerializer = new XMLSerializer(
164 new PrintWriter(
165 new FileOutputStream(xmlSchema)),
166 new OutputFormat(Method.XML, null, true));
167 xmlSerializer.serialize(doc);
168 }
169 catch (Exception e)
170 {
171 throw new BuildException(e);
172 }
173 log("Torque - JDBCToXMLSchema finished");
174 }
175
176 /***
177 * Generates an XML database schema from JDBC metadata.
178 *
179 * @throws Exception a generic exception.
180 */
181 public void generateXML() throws Exception
182 {
183
184 Class.forName(dbDriver);
185 log("DB driver sucessfuly instantiated");
186
187 Connection con = null;
188 try
189 {
190
191 con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
192 log("DB connection established");
193
194
195 DatabaseMetaData dbMetaData = con.getMetaData();
196
197
198 List tableList = getTableNames(dbMetaData);
199
200 databaseNode = doc.createElement("database");
201 databaseNode.setAttribute("name", dbUser);
202
203
204 columnTableMap = new Hashtable();
205
206 log("Building column/table map...");
207 for (int i = 0; i < tableList.size(); i++)
208 {
209 String curTable = (String) tableList.get(i);
210 List columns = getColumns(dbMetaData, curTable);
211
212 for (int j = 0; j < columns.size(); j++)
213 {
214 List col = (List) columns.get(j);
215 String name = (String) col.get(0);
216
217 columnTableMap.put(name, curTable);
218 }
219 }
220
221 for (int i = 0; i < tableList.size(); i++)
222 {
223
224 String curTable = (String) tableList.get(i);
225
226 log("Processing table: " + curTable);
227
228 Element table = doc.createElement("table");
229 table.setAttribute("name", curTable);
230 if (isSameJavaName())
231 {
232 table.setAttribute("javaName", curTable);
233 }
234
235
236
237
238 List columns = getColumns(dbMetaData, curTable);
239 List primKeys = getPrimaryKeys(dbMetaData, curTable);
240 Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
241
242
243 primaryKeys = new Hashtable();
244
245 for (int k = 0; k < primKeys.size(); k++)
246 {
247 String curPrimaryKey = (String) primKeys.get(k);
248 primaryKeys.put(curPrimaryKey, curPrimaryKey);
249 }
250
251 for (int j = 0; j < columns.size(); j++)
252 {
253 List col = (List) columns.get(j);
254 String name = (String) col.get(0);
255 Integer type = ((Integer) col.get(1));
256 int size = ((Integer) col.get(2)).intValue();
257 int scale = ((Integer) col.get(5)).intValue();
258
259
260
261
262
263
264
265
266
267
268
269
270
271 Integer nullType = (Integer) col.get(3);
272 String defValue = (String) col.get(4);
273
274 Element column = doc.createElement("column");
275 column.setAttribute("name", name);
276 if (isSameJavaName())
277 {
278 column.setAttribute("javaName", name);
279 }
280
281 column.setAttribute("type", TypeMap.getTorqueType(type).getName());
282
283 if (size > 0 && (type.intValue() == Types.CHAR
284 || type.intValue() == Types.VARCHAR
285 || type.intValue() == Types.LONGVARCHAR
286 || type.intValue() == Types.DECIMAL
287 || type.intValue() == Types.NUMERIC))
288 {
289 column.setAttribute("size", String.valueOf(size));
290 }
291
292 if (scale > 0 && (type.intValue() == Types.DECIMAL
293 || type.intValue() == Types.NUMERIC))
294 {
295 column.setAttribute("scale", String.valueOf(scale));
296 }
297
298 if (nullType.intValue() == 0)
299 {
300 column.setAttribute("required", "true");
301 }
302
303 if (primaryKeys.containsKey(name))
304 {
305 column.setAttribute("primaryKey", "true");
306 }
307
308 if (StringUtils.isNotEmpty(defValue))
309 {
310
311
312 if (defValue.startsWith("(") && defValue.endsWith(")"))
313 {
314 defValue = defValue.substring(1, defValue.length() - 1);
315 }
316
317 if (defValue.startsWith("'") && defValue.endsWith("'"))
318 {
319 defValue = defValue.substring(1, defValue.length() - 1);
320 }
321
322 column.setAttribute("default", defValue);
323 }
324 table.appendChild(column);
325 }
326
327
328 for (Iterator l = forgnKeys.iterator(); l.hasNext();)
329 {
330 Object[] forKey = (Object[]) l.next();
331 String foreignKeyTable = (String) forKey[0];
332 List refs = (List) forKey[1];
333 Element fk = doc.createElement("foreign-key");
334 fk.setAttribute("foreignTable", foreignKeyTable);
335 for (int m = 0; m < refs.size(); m++)
336 {
337 Element ref = doc.createElement("reference");
338 String[] refData = (String[]) refs.get(m);
339 ref.setAttribute("local", refData[0]);
340 ref.setAttribute("foreign", refData[1]);
341 fk.appendChild(ref);
342 }
343 table.appendChild(fk);
344 }
345 databaseNode.appendChild(table);
346 }
347 doc.appendChild(databaseNode);
348 }
349 finally
350 {
351 if (con != null)
352 {
353 con.close();
354 con = null;
355 }
356 }
357 }
358
359 /***
360 * Get all the table names in the current database that are not
361 * system tables.
362 *
363 * @param dbMeta JDBC database metadata.
364 * @return The list of all the tables in a database.
365 * @throws SQLException
366 */
367 public List getTableNames(DatabaseMetaData dbMeta)
368 throws SQLException
369 {
370 log("Getting table list...");
371 List tables = new ArrayList();
372 ResultSet tableNames = null;
373
374 String[] types = {"TABLE", "VIEW"};
375 try
376 {
377 tableNames = dbMeta.getTables(null, dbSchema, "%", types);
378 while (tableNames.next())
379 {
380 String name = tableNames.getString(3);
381 tables.add(name);
382 }
383 }
384 finally
385 {
386 if (tableNames != null)
387 {
388 tableNames.close();
389 }
390 }
391 return tables;
392 }
393
394 /***
395 * Retrieves all the column names and types for a given table from
396 * JDBC metadata. It returns a List of Lists. Each element
397 * of the returned List is a List with:
398 *
399 * element 0 => a String object for the column name.
400 * element 1 => an Integer object for the column type.
401 * element 2 => size of the column.
402 * element 3 => null type.
403 *
404 * @param dbMeta JDBC metadata.
405 * @param tableName Table from which to retrieve column information.
406 * @return The list of columns in <code>tableName</code>.
407 * @throws SQLException
408 */
409 public List getColumns(DatabaseMetaData dbMeta, String tableName)
410 throws SQLException
411 {
412 List columns = new ArrayList();
413 ResultSet columnSet = null;
414 try
415 {
416 columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
417 while (columnSet.next())
418 {
419 String name = columnSet.getString(4);
420 Integer sqlType = new Integer(columnSet.getString(5));
421 Integer size = new Integer(columnSet.getInt(7));
422 Integer decimalDigits = new Integer(columnSet.getInt(9));
423 Integer nullType = new Integer(columnSet.getInt(11));
424 String defValue = columnSet.getString(13);
425
426 List col = new ArrayList(6);
427 col.add(name);
428 col.add(sqlType);
429 col.add(size);
430 col.add(nullType);
431 col.add(defValue);
432 col.add(decimalDigits);
433 columns.add(col);
434 }
435 }
436 finally
437 {
438 if (columnSet != null)
439 {
440 columnSet.close();
441 }
442 }
443 return columns;
444 }
445
446 /***
447 * Retrieves a list of the columns composing the primary key for a given
448 * table.
449 *
450 * @param dbMeta JDBC metadata.
451 * @param tableName Table from which to retrieve PK information.
452 * @return A list of the primary key parts for <code>tableName</code>.
453 * @throws SQLException
454 */
455 public List getPrimaryKeys(DatabaseMetaData dbMeta, String tableName)
456 throws SQLException
457 {
458 List pk = new ArrayList();
459 ResultSet parts = null;
460 try
461 {
462 parts = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
463 while (parts.next())
464 {
465 pk.add(parts.getString(4));
466 }
467 }
468 finally
469 {
470 if (parts != null)
471 {
472 parts.close();
473 }
474 }
475 return pk;
476 }
477
478 /***
479 * Retrieves a list of foreign key columns for a given table.
480 *
481 * @param dbMeta JDBC metadata.
482 * @param tableName Table from which to retrieve FK information.
483 * @return A list of foreign keys in <code>tableName</code>.
484 * @throws SQLException
485 */
486 public Collection getForeignKeys(DatabaseMetaData dbMeta, String tableName)
487 throws SQLException
488 {
489 Hashtable fks = new Hashtable();
490 ResultSet foreignKeys = null;
491 try
492 {
493 foreignKeys = dbMeta.getImportedKeys(null, dbSchema, tableName);
494 while (foreignKeys.next())
495 {
496 String refTableName = foreignKeys.getString(3);
497 String fkName = foreignKeys.getString(12);
498
499 if (fkName == null)
500 {
501 fkName = refTableName;
502 }
503 Object[] fk = (Object[]) fks.get(fkName);
504 List refs;
505 if (fk == null)
506 {
507 fk = new Object[2];
508 fk[0] = refTableName;
509 refs = new ArrayList();
510 fk[1] = refs;
511 fks.put(fkName, fk);
512 }
513 else
514 {
515 refs = (ArrayList) fk[1];
516 }
517 String[] ref = new String[2];
518 ref[0] = foreignKeys.getString(8);
519 ref[1] = foreignKeys.getString(4);
520 refs.add(ref);
521 }
522 }
523 catch (SQLException e)
524 {
525
526
527 log("WARN: Could not read foreign keys for Table "
528 + tableName
529 + " : "
530 + e.getMessage(),
531 Project.MSG_WARN);
532 }
533 finally
534 {
535 if (foreignKeys != null)
536 {
537 foreignKeys.close();
538 }
539 }
540 return fks.values();
541 }
542 }