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