View Javadoc

1   package org.apache.torque.task;
2   
3   /*
4    * Copyright 2001-2004 The Apache Software Foundation.
5    * 
6    * Licensed under the Apache License, Version 2.0 (the "License");
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    * 
10   *      http://www.apache.org/licenses/LICENSE-2.0
11   * 
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
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         // log("password : " + dbPassword);
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         // Load the Interbase Driver.
186         Class.forName(dbDriver);
187         log("DB driver sucessfuly instantiated");
188 
189         // Attemtp to connect to a database.
190         Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
191         log("DB connection established");
192 
193         // Get the database Metadata.
194         DatabaseMetaData dbMetaData = con.getMetaData();
195 
196         // The database map.
197         List tableList = getTableNames(dbMetaData);
198 
199         databaseNode = doc.createElement("database");
200         databaseNode.setAttribute("name", dbUser);
201 
202         // Build a database-wide column -> table map.
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             // Add Table.
223             String curTable = (String) tableList.get(i);
224             // dbMap.addTable(curTable);
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             // Add Columns.
235             // TableMap tblMap = dbMap.getTable(curTable);
236 
237             List columns = getColumns(dbMetaData, curTable);
238             List primKeys = getPrimaryKeys(dbMetaData, curTable);
239             Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
240 
241             // Set the primary keys.
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                 // From DatabaseMetaData.java
258                 //
259                 // Indicates column might not allow NULL values.  Huh?
260                 // Might? Boy, that's a definitive answer.
261                 /* int columnNoNulls = 0; */
262 
263                 // Indicates column definitely allows NULL values.
264                 /* int columnNullable = 1; */
265 
266                 // Indicates NULLABILITY of column is unknown.
267                 /* int columnNullableUnknown = 2; */
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                     // trim out parens & quotes out of def value.
302                     // makes sense for MSSQL. not sure about others.
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             // Foreign keys for this table.
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         // these are the entity types we want from the database
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                 // if FK has no name - make it up (use tablename instead)
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; //referenced table name
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); //local column
500                 ref[1] = foreignKeys.getString(4); //foreign column
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 }