View Javadoc

1   package com.explosion.expfmodules.rdbmsconn.dbom.utils;
2   
3   /*
4    * =============================================================================
5    * 
6    * Copyright 2004 Stephen Cowx
7    * 
8    * Licensed under the Apache License, Version 2.0 (the "License"); you may not
9    * use this file except in compliance with the License. You may obtain a copy of
10   * the License at
11   * 
12   * http://www.apache.org/licenses/LICENSE-2.0
13   * 
14   * Unless required by applicable law or agreed to in writing, software
15   * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
16   * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
17   * License for the specific language governing permissions and limitations under
18   * the License.
19   * 
20   * =============================================================================
21   */
22  
23  import java.sql.Connection;
24  import java.sql.DatabaseMetaData;
25  import java.sql.PreparedStatement;
26  import java.sql.ResultSet;
27  import java.sql.ResultSetMetaData;
28  import java.sql.SQLException;
29  import java.util.Vector;
30  import javax.swing.JOptionPane;
31  import org.apache.log4j.LogManager;
32  import org.apache.log4j.Logger;
33  import com.explosion.datastream.exql.gui.ExqlTreeNode;
34  import com.explosion.expf.Application;
35  import com.explosion.expfmodules.rdbmsconn.connect.ConnectionManager;
36  import com.explosion.expfmodules.rdbmsconn.dbom.DBEntity;
37  import com.explosion.expfmodules.rdbmsconn.dbom.DBEntityColumn;
38  import com.explosion.utilities.GeneralUtils;
39  import com.explosion.utilities.exception.ExceptionManagerFactory;
40  import com.explosion.utilities.process.threads.SimpleProcess;
41  
42  /***
43   * @author Stephen Cowx Date created:@14-Feb-2003
44   */
45  public class MetadataUtils
46  {
47      boolean reportExceptions = false;
48      private static Logger log = LogManager.getLogger(MetadataUtils.class);
49     
50      /***
51       * * This method will return a list of the tables in this database for the
52       * given catalog or schema. Each row of this list contains table data in the
53       * folowing order: Catalog, Schema, Name, Type, Remarks <br>
54       * If there aren't any tables or the driver does not support this method
55       * then an empty Vector will be returned.
56       * 
57       * @param dbmd required
58       * @param catalogName must be null if schema is not null
59       * @param schemaName must be null if catalog is not null
60       * @param tableName can be null
61       * @param types can be null
62       * @param process can be null if this is not being called from inside a
63       *        process
64       * @return
65       */
66      public Vector getTables(DatabaseMetaData dbmd, String catalogName, String schemaName, String tableName, String[] types, SimpleProcess process)
67      {
68          Vector tables = new Vector();
69          ResultSet set = null;
70          try
71          {
72              set = dbmd.getTables(catalogName, schemaName, tableName, types);
73              while (set.next())
74              {
75                  if (process != null && process.isStopped())
76                      break;
77  
78                  Vector tableData = new Vector();
79                  tableData.addElement(set.getString(1));
80                  tableData.addElement(set.getString(2));
81                  tableData.addElement(set.getString(3));
82                  tableData.addElement(set.getString(4));
83                  tableData.addElement(set.getString(5));
84                  tables.addElement(tableData);
85              }
86          }
87          catch (SQLException e)
88          {
89              ExceptionManagerFactory.getExceptionManager().manageException(e, "Error while retrieving tables.  Resultset MetaData getTables() has possibly not been implemented.");
90          }
91          finally
92          {
93              try
94              {
95                  set.close();
96              }
97              catch (Exception e)
98              {
99                  log.error("Error:",e);
100             }
101         }
102         return tables;
103     }
104 
105     /***
106      * This method extracts the column information for this entity from the
107      * metadata and uses it to populate the DBEntity. It also poulates the node
108      * with column informatrion if it is not null
109      */
110     public void getColumns(DBEntity dbed, DatabaseMetaData dbmd, ExqlTreeNode node) throws Exception
111     {
112         /* Get the desired information from the DBEntity Descriptor */
113         String catalog = dbed.getCatalogName();
114         String schema = dbed.getSchemaName();
115         String table = dbed.getEntityName();
116         log.debug("getColumns for catalog:" + catalog + ", schema:" + schema + ", table:" + table + ".");
117 
118         ResultSet set = null;
119         try
120         {
121             set = dbmd.getColumns(catalog, schema, table, null);
122 
123             while (set.next())
124             {
125                 ExqlTreeNode columnNode = null;
126                 try
127                 {
128                     DBEntityColumn column = new DBEntityColumn();
129 
130                     String name = set.getString(4);
131                     Integer index = new Integer(set.getInt(17));
132                     int type = set.getInt(5);
133                     String typeName = set.getString(6);
134                     String isNullable = set.getString(18);
135                     Integer colSize = new Integer(set.getInt(7));
136                     String remarks = set.getString(12);
137 
138                     column.setColumnName(name);
139                     column.setIndex(index.intValue());
140                     column.setType(type);
141                     column.setColumnName(name);
142                     column.setTypeName(typeName);
143                     column.setNullable(GeneralUtils.getLenientBoolean(isNullable));
144                     column.setSize(colSize.intValue());
145                     column.setRemarks(remarks);
146                     dbed.addColumn(column);
147 
148                     /* Build tree */
149                     if (node != null)
150                     {
151                         columnNode = new ExqlTreeNode(column);
152 
153                         ExqlTreeNode descriptorNode1 = new ExqlTreeNode("Index      : " + index);
154                         ExqlTreeNode descriptorNode2 = new ExqlTreeNode("Name       : " + name);
155                         ExqlTreeNode descriptorNode3 = new ExqlTreeNode("Type       : " + typeName);
156                         ExqlTreeNode descriptorNode4 = new ExqlTreeNode("Is Nullable: " + isNullable);
157                         ExqlTreeNode descriptorNode5 = new ExqlTreeNode("Size       : " + colSize);
158                         ExqlTreeNode descriptorNode6 = new ExqlTreeNode("Remarks    :" + remarks);
159 
160                         columnNode.add(descriptorNode1);
161                         columnNode.add(descriptorNode2);
162                         columnNode.add(descriptorNode3);
163                         columnNode.add(descriptorNode4);
164                         columnNode.add(descriptorNode5);
165                         columnNode.add(descriptorNode6);
166                     }
167 
168                 }
169                 catch (SQLException e)
170                 {
171                     if (reportExceptions)
172                     {
173                         int decision = JOptionPane.showConfirmDialog(Application.getApplicationFrame(), "Caught error, ignore ?", "Error", JOptionPane.YES_NO_OPTION, JOptionPane.INFORMATION_MESSAGE);
174                         if (decision == JOptionPane.NO_OPTION)
175                         {
176                             throw e;
177                         }
178                         else
179                         {
180                             columnNode = null;
181                         }
182                     }
183                     columnNode = null;
184                 }
185 
186                 if (columnNode != null)
187                     node.add(columnNode);
188             }
189 
190             /*
191              * Get other column info about this table using an empty select
192              */
193             PreparedStatement stmt = null;
194             try
195             { /* Close the set before we use it again */
196                 set.close();
197                 /* Get the table information */
198                 stmt = dbmd.getConnection().prepareStatement("SELECT * from " + getFullEntityName(dbed, dbmd) + " WHERE 1 = 0");
199                 set = stmt.executeQuery();
200                 ResultSetMetaData rsm = set.getMetaData();
201 
202                 int numColumns = rsm.getColumnCount();
203                 for (int i = 0; i < numColumns; i++)
204                 {
205                     String columnName = rsm.getColumnName(i + 1);
206                     DBEntityColumn column = dbed.getColumn(columnName);
207 
208                     if (column != null)
209                     {
210                         column.setAutoIncrement(rsm.isAutoIncrement(i + 1));
211                         column.setCaseSensitive(rsm.isCaseSensitive(i + 1));
212                         column.setCurrency(rsm.isCurrency(i + 1));
213                         column.setDefinitelyWritable(rsm.isDefinitelyWritable(i + 1));
214                         column.setReadOnly(rsm.isReadOnly(i + 1));
215                         column.setSearchable(rsm.isSearchable(i + 1));
216                         column.setSigned(rsm.isSigned(i + 1));
217                         column.setWritable(rsm.isWritable(i + 1));
218                     }
219                 }
220             }
221             catch (SQLException e)
222             {
223                 if (reportExceptions)
224                 {
225                     int decision = JOptionPane.showConfirmDialog(Application.getApplicationFrame(), "Caught error, ignore ?", "Error", JOptionPane.YES_NO_OPTION, JOptionPane.INFORMATION_MESSAGE);
226                     if (decision == JOptionPane.NO_OPTION)
227                     {
228                         throw e;
229                     }
230                 }
231             }
232             finally
233             {
234                 if (stmt != null)
235                 {
236                     log.debug("Closing table info statement. ");
237                     stmt.close();
238                 }
239             }
240         }
241         catch (SQLException e)
242         {
243             if (!reportExceptions)
244             {
245                 ExceptionManagerFactory.getExceptionManager().manageException(e, "Exception caught while collecting column information.");
246             }
247         }
248         finally
249         {
250             if (set != null)
251             {
252                 log.debug("Closing table info result set. ");
253                 try {
254                     set.close();
255                 } catch (SQLException e1) {
256                     log.error("Error:",e1);
257                 }
258             }
259         }
260     }
261 
262     /***
263      * Populates the DBEntity with the PrimaryKeys arrayList
264      * 
265      * @param dbmd
266      * @param tableName
267      * @param tableTypeInt
268      * @throws SQLException
269      */
270     public void getTablePrimaryKeys(DatabaseMetaData dbmd, DBEntity dbed) throws SQLException
271     {
272         if (dbed.getEntityType() == DBEntity.TYPE_TABLE || dbed.getEntityType() == DBEntity.TYPE_SYSTEM_TABLE)
273         {
274             ResultSet pkcSet = null;
275             try {
276                 pkcSet = dbmd.getPrimaryKeys(dbed.getCatalogName(), dbed.getSchemaName(), dbed.getEntityName());
277                 while (pkcSet.next())
278                 {
279                     String name = pkcSet.getString(4);
280                     log.debug("Found primary key column " + name + " for table " + dbed.getEntityName());
281                     DBEntityColumn column = dbed.getColumn(name);
282                     if (column != null)
283                         column.setPrimaryKey(true);
284                 }
285             } catch (SQLException e) {
286                 //SQLExceptions here get thrown if the driver does not support this function.  We need to catch it and 
287                 //then let it go.
288             }
289             finally
290             {
291                 if (pkcSet != null)
292                 {
293                     try {
294                         pkcSet.close();
295                     } catch (SQLException e1) {
296                         log.error("Error:",e1);
297                     }
298                 }
299             }
300         }
301     }
302     
303     /***
304      * Populates the DBEntity with the Foreign Keys arrayList
305      * These are the Imported keys in JDBC speak
306      * 
307      * @param dbmd
308      * @param tableName
309      * @param tableTypeInt
310      * @throws SQLException
311      */
312     public void getTableForeignKeys(DatabaseMetaData dbmd, DBEntity dbed) throws SQLException
313     {
314         if (dbed.getEntityType() == DBEntity.TYPE_TABLE || dbed.getEntityType() == DBEntity.TYPE_SYSTEM_TABLE)
315         {
316             ResultSet pkcSet = null;
317             try {
318                 pkcSet = dbmd.getImportedKeys(dbed.getCatalogName(), dbed.getSchemaName(), dbed.getEntityName());
319                 while (pkcSet.next())
320                 {
321                     String name = pkcSet.getString(4);
322                     log.debug("Found imported key column " + name + " for table " + dbed.getEntityName());
323                     DBEntityColumn column = dbed.getColumn(name);
324                     if (column != null)
325                         column.setForeignKey(true);
326                 }
327                 
328             } catch (SQLException e) {
329                 //SQLExceptions here get thrown if the driver does not support this function.  We need to catch it and 
330                 //then let it go.
331             }
332             finally
333             {
334                 if (pkcSet != null)
335                 {
336                     try {
337                         pkcSet.close();
338                     } catch (SQLException e1) {
339                         log.error("Error:",e1);
340                     }
341                 }
342             }
343         }
344     }
345     
346     /***
347      * This method populates the dbentity descriptor with information 
348      * regarding the best row identifier for this table
349      * @param dbed
350      * @return
351      * @throws Exception
352      */
353     public void getBestRowIdentifiers(DBEntity dbed, DatabaseMetaData dbmd) throws Exception
354     {
355       /* Initialise */
356       ResultSet bestIdentifiers = null;
357       
358       try
359       {
360         /* Get required information from the DBEntity Descriptor */
361         String catalog = dbed.getCatalogName();
362         String schema = dbed.getSchemaName();
363         String table = dbed.getEntityName();
364 
365         /* Get the best row identifiers for this table */
366         bestIdentifiers = dbmd.getBestRowIdentifier(catalog, schema, table, DatabaseMetaData.bestRowTransaction, true);
367 
368         while (bestIdentifiers.next())
369         {
370           short scope = bestIdentifiers.getShort("SCOPE"); //=> actual scope of result
371           //bestRowTemporary - very temporary, while using row
372           //bestRowTransaction - valid for remainder of current transaction
373           //bestRowSession - valid for remainder of current session
374           String columnName = bestIdentifiers.getString("COLUMN_NAME"); // => column name
375           int dataType = -1; 
376           try
377           { 
378               dataType = bestIdentifiers.getInt("DATA_TYPE"); //=> SQL data type from java.sql.Types
379           }
380           catch (Exception e)
381           {
382               //This is to avoid a wierd exception that happens with the MySQL driver that I am using
383               // java.sql.SQLExcelption: Bad format for number '' in column 3(null)  
384               // hopefully it will get fixed in a later version
385               log.error("Exception caught (and ignored) while finding best row identifiers.",e);
386           }
387           String typeName = bestIdentifiers.getString("TYPE_NAME"); //=> Data source dependent type name, for a UDT the type name is fully qualified
388           int columnSize = bestIdentifiers.getInt("COLUMN_SIZE"); //=> precision
389           int bufferLength = bestIdentifiers.getInt("BUFFER_LENGTH"); //=> not used
390           short decimalDigits = bestIdentifiers.getShort("DECIMAL_DIGITS"); //=> scale
391           boolean pseudoColumn = bestIdentifiers.getBoolean("PSEUDO_COLUMN"); //=> is this a pseudo column like an Oracle ROWID bestRowUnknown - may or
392 
393           DBEntityColumn column = dbed.getColumn(columnName);
394           if (column == null)
395           {
396               column = new DBEntityColumn();
397               dbed.addColumn(column);
398           }
399           
400           log.debug("Found best row identifier for entity " + dbed.getEntityName());
401           log.debug("Name: " + columnName + ", isPseudoColumn " + pseudoColumn + ", typeName " + typeName + ", scope " + scope );
402           column.setColumnName(columnName);
403           if (dataType > 0)
404               column.setType(dataType);
405           column.setSize(columnSize);
406           column.setBufferLength(bufferLength);
407           column.setDecimalDigits(decimalDigits);
408           column.setPseudoColumn(pseudoColumn);
409           column.setTypeName(typeName);
410         }
411       }
412       catch (Exception e)
413       {
414           log.error(e);
415           throw e;
416       }
417       finally
418       {
419         if (bestIdentifiers != null)
420         {
421              try {
422                 bestIdentifiers.close();
423             } catch (SQLException e1) {
424                 log.error("Error:",e1);
425             }
426         }
427       }
428     }
429 
430 
431     /***
432      * This method returns a DatabaseMetaDataObject
433      */
434     public synchronized static DatabaseMetaData getDBMD(Connection conn) throws Exception
435     {
436         if (conn == null)
437             throw new Exception("Unable to return meta data as connection provided is null.");
438 
439         return conn.getMetaData();
440     }
441     
442     private static String[] escapeMeCharacters = { " ", "\'"};
443     
444     /***
445      * This method returns the correct identifier for an sql statement against the connected database.
446      * This may be fully qualified. Entity names and schema/ctalog names are prepared using the prepareName 
447      * method.  Entity names are delimited from the schema's ,catalogs etc with the relevant schema or 
448      * catalog separator for the connected database.
449      * @throws MetadataException
450      * @throws SQLException
451      */
452     public String getFullEntityName(DBEntity dbed, DatabaseMetaData meta) throws SQLException, MetadataException
453     {
454         return ConnectionManager.getInstance().getDialect(meta.getConnection()).getFullEntityName(dbed, meta);
455     }
456 
457     /***
458      * This method returns a fully qualified column name given the name and a
459      * identifier (tablename or whatever). 
460      * 
461      * Names are prepared using the prepareName method and are delimited from
462      * the identifier with a with a point (.) if the identifier is not null and not an empty string
463      * @throws SQLException
464      */
465     public String getFullColumnName(String columnName, String identifier, DatabaseMetaData meta) throws SQLException
466     {
467         return ConnectionManager.getInstance().getDialect(meta.getConnection()).getFullColumnName(columnName, identifier, meta);
468     }
469     
470 }