Java > Open Source Codes > org > apache > torque > task > TorqueJDBCTransformTask


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 }
514

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags