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,v 1.9 2005/06/14 20:51:13 tfischer Exp $
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
190 Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
191 log("DB connection established");
192
193
194 DatabaseMetaData dbMetaData = con.getMetaData();
195
196
197 List tableList = getTableNames(dbMetaData);
198
199 databaseNode = doc.createElement("database");
200 databaseNode.setAttribute("name", dbUser);
201
202
203 columnTableMap = new Hashtable();
204
205 log("Building column/table map...");
206 for (int i = 0; i < tableList.size(); i++)
207 {
208 String curTable = (String) tableList.get(i);
209 List columns = getColumns(dbMetaData, curTable);
210
211 for (int j = 0; j < columns.size(); j++)
212 {
213 List col = (List) columns.get(j);
214 String name = (String) col.get(0);
215
216 columnTableMap.put(name, curTable);
217 }
218 }
219
220 for (int i = 0; i < tableList.size(); i++)
221 {
222
223 String curTable = (String) tableList.get(i);
224
225 log("Processing table: " + curTable);
226
227 Element table = doc.createElement("table");
228 table.setAttribute("name", curTable);
229 if (isSameJavaName())
230 {
231 table.setAttribute("javaName", curTable);
232 }
233
234
235
236
237 List columns = getColumns(dbMetaData, curTable);
238 List primKeys = getPrimaryKeys(dbMetaData, curTable);
239 Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
240
241
242 primaryKeys = new Hashtable();
243
244 for (int k = 0; k < primKeys.size(); k++)
245 {
246 String curPrimaryKey = (String) primKeys.get(k);
247 primaryKeys.put(curPrimaryKey, curPrimaryKey);
248 }
249
250 for (int j = 0; j < columns.size(); j++)
251 {
252 List col = (List) columns.get(j);
253 String name = (String) col.get(0);
254 Integer type = ((Integer) col.get(1));
255 int size = ((Integer) col.get(2)).intValue();
256
257
258
259
260
261
262
263
264
265
266
267
268
269 Integer nullType = (Integer) col.get(3);
270 String defValue = (String) col.get(4);
271
272 Element column = doc.createElement("column");
273 column.setAttribute("name", name);
274 if (isSameJavaName())
275 {
276 column.setAttribute("javaName", name);
277 }
278 column.setAttribute("type", TypeMap.getTorqueType(type).getName());
279
280 if (size > 0 && (type.intValue() == Types.CHAR
281 || type.intValue() == Types.VARCHAR
282 || type.intValue() == Types.LONGVARCHAR
283 || type.intValue() == Types.DECIMAL
284 || type.intValue() == Types.NUMERIC))
285 {
286 column.setAttribute("size", String.valueOf(size));
287 }
288
289 if (nullType.intValue() == 0)
290 {
291 column.setAttribute("required", "true");
292 }
293
294 if (primaryKeys.containsKey(name))
295 {
296 column.setAttribute("primaryKey", "true");
297 }
298
299 if (defValue != null)
300 {
301
302
303 if (defValue.startsWith("(") && defValue.endsWith(")"))
304 {
305 defValue = defValue.substring(1, defValue.length() - 1);
306 }
307
308 if (defValue.startsWith("'") && defValue.endsWith("'"))
309 {
310 defValue = defValue.substring(1, defValue.length() - 1);
311 }
312
313 column.setAttribute("default", defValue);
314 }
315 table.appendChild(column);
316 }
317
318
319 for (Iterator l = forgnKeys.iterator(); l.hasNext();)
320 {
321 Object[] forKey = (Object[]) l.next();
322 String foreignKeyTable = (String) forKey[0];
323 List refs = (List) forKey[1];
324 Element fk = doc.createElement("foreign-key");
325 fk.setAttribute("foreignTable", foreignKeyTable);
326 for (int m = 0; m < refs.size(); m++)
327 {
328 Element ref = doc.createElement("reference");
329 String[] refData = (String[]) refs.get(m);
330 ref.setAttribute("local", refData[0]);
331 ref.setAttribute("foreign", refData[1]);
332 fk.appendChild(ref);
333 }
334 table.appendChild(fk);
335 }
336 databaseNode.appendChild(table);
337 }
338 doc.appendChild(databaseNode);
339 }
340
341 /***
342 * Get all the table names in the current database that are not
343 * system tables.
344 *
345 * @param dbMeta JDBC database metadata.
346 * @return The list of all the tables in a database.
347 * @throws SQLException
348 */
349 public List getTableNames(DatabaseMetaData dbMeta)
350 throws SQLException
351 {
352 log("Getting table list...");
353 List tables = new ArrayList();
354 ResultSet tableNames = null;
355
356 String[] types = {"TABLE", "VIEW"};
357 try
358 {
359 tableNames = dbMeta.getTables(null, dbSchema, "%", types);
360 while (tableNames.next())
361 {
362 String name = tableNames.getString(3);
363 String type = tableNames.getString(4);
364 tables.add(name);
365 }
366 }
367 finally
368 {
369 if (tableNames != null)
370 {
371 tableNames.close();
372 }
373 }
374 return tables;
375 }
376
377 /***
378 * Retrieves all the column names and types for a given table from
379 * JDBC metadata. It returns a List of Lists. Each element
380 * of the returned List is a List with:
381 *
382 * element 0 => a String object for the column name.
383 * element 1 => an Integer object for the column type.
384 * element 2 => size of the column.
385 * element 3 => null type.
386 *
387 * @param dbMeta JDBC metadata.
388 * @param tableName Table from which to retrieve column information.
389 * @return The list of columns in <code>tableName</code>.
390 * @throws SQLException
391 */
392 public List getColumns(DatabaseMetaData dbMeta, String tableName)
393 throws SQLException
394 {
395 List columns = new ArrayList();
396 ResultSet columnSet = null;
397 try
398 {
399 columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
400 while (columnSet.next())
401 {
402 String name = columnSet.getString(4);
403 Integer sqlType = new Integer(columnSet.getString(5));
404 Integer size = new Integer(columnSet.getInt(7));
405 Integer nullType = new Integer(columnSet.getInt(11));
406 String defValue = columnSet.getString(13);
407
408 List col = new ArrayList(5);
409 col.add(name);
410 col.add(sqlType);
411 col.add(size);
412 col.add(nullType);
413 col.add(defValue);
414 columns.add(col);
415 }
416 }
417 finally
418 {
419 if (columnSet != null)
420 {
421 columnSet.close();
422 }
423 }
424 return columns;
425 }
426
427 /***
428 * Retrieves a list of the columns composing the primary key for a given
429 * table.
430 *
431 * @param dbMeta JDBC metadata.
432 * @param tableName Table from which to retrieve PK information.
433 * @return A list of the primary key parts for <code>tableName</code>.
434 * @throws SQLException
435 */
436 public List getPrimaryKeys(DatabaseMetaData dbMeta, String tableName)
437 throws SQLException
438 {
439 List pk = new ArrayList();
440 ResultSet parts = null;
441 try
442 {
443 parts = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
444 while (parts.next())
445 {
446 pk.add(parts.getString(4));
447 }
448 }
449 finally
450 {
451 if (parts != null)
452 {
453 parts.close();
454 }
455 }
456 return pk;
457 }
458
459 /***
460 * Retrieves a list of foreign key columns for a given table.
461 *
462 * @param dbMeta JDBC metadata.
463 * @param tableName Table from which to retrieve FK information.
464 * @return A list of foreign keys in <code>tableName</code>.
465 * @throws SQLException
466 */
467 public Collection getForeignKeys(DatabaseMetaData dbMeta, String tableName)
468 throws SQLException
469 {
470 Hashtable fks = new Hashtable();
471 ResultSet foreignKeys = null;
472 try
473 {
474 foreignKeys = dbMeta.getImportedKeys(null, dbSchema, tableName);
475 while (foreignKeys.next())
476 {
477 String refTableName = foreignKeys.getString(3);
478 String fkName = foreignKeys.getString(12);
479
480 if (fkName == null)
481 {
482 fkName = refTableName;
483 }
484 Object[] fk = (Object[]) fks.get(fkName);
485 List refs;
486 if (fk == null)
487 {
488 fk = new Object[2];
489 fk[0] = refTableName;
490 refs = new ArrayList();
491 fk[1] = refs;
492 fks.put(fkName, fk);
493 }
494 else
495 {
496 refs = (ArrayList) fk[1];
497 }
498 String[] ref = new String[2];
499 ref[0] = foreignKeys.getString(8);
500 ref[1] = foreignKeys.getString(4);
501 refs.add(ref);
502 }
503 }
504 finally
505 {
506 if (foreignKeys != null)
507 {
508 foreignKeys.close();
509 }
510 }
511 return fks.values();
512 }
513 }