View Javadoc

1   package org.apache.torque.task;
2   
3   /*
4    * Copyright 2001-2005 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 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         // 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         Connection con = null;
190         try
191         {
192             // Attempt to connect to a database.
193             con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
194             log("DB connection established");
195 
196             // Get the database Metadata.
197             DatabaseMetaData dbMetaData = con.getMetaData();
198 
199             // The database map.
200             List tableList = getTableNames(dbMetaData);
201 
202             databaseNode = doc.createElement("database");
203             databaseNode.setAttribute("name", dbUser);
204 
205             // Build a database-wide column -> table map.
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                 // Add Table.
226                 String curTable = (String) tableList.get(i);
227                 // dbMap.addTable(curTable);
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                 // Add Columns.
238                 // TableMap tblMap = dbMap.getTable(curTable);
239 
240                 List columns = getColumns(dbMetaData, curTable);
241                 List primKeys = getPrimaryKeys(dbMetaData, curTable);
242                 Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
243 
244                 // Set the primary keys.
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                     // From DatabaseMetaData.java
261                     //
262                     // Indicates column might not allow NULL values.  Huh?
263                     // Might? Boy, that's a definitive answer.
264                     /* int columnNoNulls = 0; */
265 
266                     // Indicates column definitely allows NULL values.
267                     /* int columnNullable = 1; */
268 
269                     // Indicates NULLABILITY of column is unknown.
270                     /* int columnNullableUnknown = 2; */
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                         // trim out parens & quotes out of def value.
305                         // makes sense for MSSQL. not sure about others.
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                 // Foreign keys for this table.
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         // these are the entity types we want from the database
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                 // if FK has no name - make it up (use tablename instead)
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; //referenced table name
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); //local column
511                 ref[1] = foreignKeys.getString(4); //foreign column
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 }