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