DatabaseSQLMetaData


1   /**
2    * C-JDBC: Clustered JDBC.
3    * Copyright (C) 2002-2004 French National Institute For Research In Computer
4    * Science And Control (INRIA).
5    * Contact: c-jdbc@objectweb.org
6    * 
7    * This library is free software; you can redistribute it and/or modify it
8    * under the terms of the GNU Lesser General Public License as published by the
9    * Free Software Foundation; either version 2.1 of the License, or any later
10   * version.
11   * 
12   * This library is distributed in the hope that it will be useful, but WITHOUT
13   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
14   * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
15   * for more details.
16   * 
17   * You should have received a copy of the GNU Lesser General Public License
18   * along with this library; if not, write to the Free Software Foundation,
19   * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
20   *
21   * Initial developer(s): Nicolas Modrzyk.
22   * Contributor(s): ______________________.
23   */
24  
25  package org.objectweb.cjdbc.common.sql.schema;
26  
27  import java.sql.Connection  ;
28  import java.sql.DatabaseMetaData  ;
29  import java.sql.ResultSet  ;
30  import java.sql.SQLException  ;
31  
32  import org.objectweb.cjdbc.common.i18n.Translate;
33  import org.objectweb.cjdbc.common.log.Trace;
34  import org.objectweb.cjdbc.controller.backend.DatabaseBackendSchemaConstants;
35  
36  /**
37   * This class defines a DatabaseSQLMetaData. It is used to collect metadata from
38   * a live connection to a database
39   * 
40   * @author <a HREF="mailto:Nicolas.Modrzyk@inrialpes.fr">Nicolas Modrzyk </a>
41   * @version 1.0
42   */
43  public class DatabaseSQLMetaData
44  {
45    Trace      logger;
46    Connection   connection;
47    int        dynamicPrecision;
48    boolean    gatherSystemTables;
49    String       schemaPattern;
50  
51    /**
52     * Creates a new <code>MetaData</code> object
53     * 
54     * @param logger the log4j logger to output to
55     * @param connection a jdbc connection to a database
56     * @param dynamicPrecision precision used to create the schema
57     * @param gatherSystemTables should we gather system tables
58     * @param schemaPattern schema pattern to look for (reduce the scope of
59     *          gathering if not null)
60     */
61    public DatabaseSQLMetaData(Trace logger, Connection   connection,
62        int dynamicPrecision, boolean gatherSystemTables, String   schemaPattern)
63    {
64      super();
65      this.logger = logger;
66      this.connection = connection;
67      this.dynamicPrecision = dynamicPrecision;
68      this.gatherSystemTables = gatherSystemTables;
69      this.schemaPattern = schemaPattern;
70    }
71  
72    /**
73     * Create a database schema from the given connection
74     * 
75     * @return <code>DataSchema</code> contructed from the information collected
76     *         through jdbc
77     * @throws SQLException if an error occurs with the given connection
78     */
79    public final DatabaseSchema createDatabaseSchema() throws SQLException  
80    {
81      ResultSet   rs = null;
82  
83      connection.setAutoCommit(false); // Needed for Derby Get DatabaseMetaData
84      DatabaseMetaData   metaData = connection.getMetaData();
85      if (metaData == null)
86      {
87        logger.warn(Translate.get("backend.meta.received.null"));
88        return null;
89      }
90  
91      DatabaseSchema databaseSchema = new DatabaseSchema();
92  
93      // Check if we should get system tables or not
94      String  [] types;
95      if (gatherSystemTables)
96      {
97        schemaPattern = null;
98        types = new String  []{"TABLE", "VIEW", "SYSTEM TABLE", "SYSTEM VIEW"};
99      }
100     else
101       types = new String  []{"TABLE", "VIEW"};
102 
103     // Get tables meta data
104     // getTables() gets a description of tables matching the catalog, schema,
105     // table name pattern and type. Sending in null for catalog and schema
106     // drops them from the selection criteria. The table name pattern "%"
107     // means match any substring of 0 or more characters.
108     // Last argument allows to obtain only database tables
109     try
110     {
111       rs = metaData.getTables(null, schemaPattern, "%", types);
112     }
113     catch (Exception   e)
114     {
115       // VIEWS cannot be retrieved with this backend
116       logger.error(Translate.get("backend.meta.view.not.supported"), e);
117       if (gatherSystemTables)
118         types = new String  []{"TABLE", "SYSTEM TABLE",};
119       else
120         types = new String  []{"TABLE"};
121       rs = metaData.getTables(null, schemaPattern, "%", types);
122     }
123 
124     if (rs == null)
125     {
126       logger.warn(Translate.get("backend.meta.received.null"));
127       connection.commit();
128       return null;
129     }
130 
131     String   tableName;
132     DatabaseTable table = null;
133     while (rs.next())
134     {
135       // 1 is table catalog, 2 is table schema, 3 is table name, 4 is type
136       tableName = rs.getString(3);
137       if (logger.isDebugEnabled())
138         logger.debug(Translate.get("backend.meta.found.table", tableName));
139 
140       // Create a new table and add it to the database schema
141       table = new DatabaseTable(tableName);
142       databaseSchema.addTable(table);
143 
144       if (dynamicPrecision >= DatabaseBackendSchemaConstants.DynamicPrecisionColumn)
145       {
146         // Get information about this table columns
147         getColumns(metaData, table);
148         // Get information about this table primary keys
149         getPrimaryKeys(metaData, table);
150       }
151     }
152 
153     // Get Procedures for this database
154     if (dynamicPrecision >= DatabaseBackendSchemaConstants.DynamicPrecisionProcedures)
155       getProcedures(metaData, databaseSchema);
156 
157     try
158     {
159       rs.close();
160     }
161     catch (Exception   ignore)
162     {
163     }
164 
165     try
166     {
167       connection.commit();
168     }
169     catch (Exception   ignore)
170     {
171       // This was a read-only transaction
172     }
173 
174     try
175     {
176       // restore connection
177       connection.setAutoCommit(true);
178     }
179     catch (SQLException   e1)
180     {
181       // ignore, transaction is no more valid
182     }
183 
184     return databaseSchema;
185   }
186 
187   /**
188    * @see java.sql.DatabaseMetaData#getProcedures
189    * @see java.sql.DatabaseMetaData#getProcedureColumns
190    */
191   private void getProcedures(DatabaseMetaData   metaData, DatabaseSchema schema)
192   {
193     if (logger.isDebugEnabled())
194       logger.debug(Translate.get("backend.meta.get.procedures"));
195     ResultSet   rs = null;
196     ResultSet   rs2 = null;
197     try
198     {
199       // Get Procedures meta data
200       rs = metaData.getProcedures(null, null, "%");
201 
202       if (rs == null)
203       {
204         logger.warn(Translate.get("backend.meta.get.procedures.failed",
205             metaData.getConnection().getCatalog()));
206         return;
207       }
208 
209       while (rs.next())
210       {
211         // Each row is a procedure description
212         // 3 = PROCEDURE_NAME
213         // 7 = REMARKS
214         // 8 = PROCEDURE_TYPE
215         DatabaseProcedure procedure = new DatabaseProcedure(rs.getString(3), rs
216             .getString(7), rs.getShort(8));
217 
218         if (schema.getProcedure(procedure.getName()) != null)
219         {
220           if (logger.isDebugEnabled())
221             logger.debug(Translate
222                 .get("backend.meta.procedure.already.in.schema", procedure
223                     .getName()));
224           continue;
225         }
226         else
227         {
228           if (logger.isDebugEnabled())
229             logger.debug(Translate.get("backend.meta.found.procedure",
230                 procedure.getName()));
231         }
232 
233         // TODO: Current limitation is that we don't distinguish 2 procedures
234         // with the same name but different signatures
235 
236         if (dynamicPrecision < DatabaseBackendSchemaConstants.DynamicPrecisionProcedures)
237           continue;
238         // This is a new stored procedure, get the column information
239         rs2 = metaData
240             .getProcedureColumns(null, null, procedure.getName(), "%");
241         if (rs2 == null)
242           logger.warn(Translate.get("backend.meta.get.procedure.params.failed",
243               procedure.getName()));
244         else
245         {
246           while (rs2.next())
247           {
248             // Each row is a parameter description for the current procedure
249             // 4 = COLUMN_NAME
250             // 5 = COLUMN_TYPE
251             // 6 = DATA_TYPE
252             // 7 = TYPE_NAME
253             // 8 = PRECISION
254             // 9 = LENGTH
255             // 10 = SCALE
256             // 11 = RADIX
257             // 12 = NULLABLE
258             // 13 = REMARKS
259             DatabaseProcedureParameter param = new DatabaseProcedureParameter(
260                 rs2.getString(4), rs2.getInt(5), rs2.getInt(6), rs2
261                     .getString(7), rs2.getFloat(8), rs2.getInt(9), rs2
262                     .getInt(10), rs2.getInt(11), rs2.getInt(12), rs2
263                     .getString(13));
264             // TO CHECK:
265             // This cannot happen since we don't allow 2 procedures with the
266             // same name.
267             // It seems useless to test if a parameter already exist since it
268             // should never happen.
269             // We will need to reuse some pieces of this code when we will
270             // implement support for
271             // stored procedures of the same name with different signatures.
272             // ArrayList parameters = procedure.getParameters();
273             // for (int i = 0; i < parameters.size(); i++)
274             // {
275             // String name =
276             // ((DatabaseProcedureParameter) parameters.get(i)).getName();
277             // if (name.equals(param.getName()))
278             // break;
279             // }
280             procedure.addParameter(param);
281           }
282           rs2.close();
283         }
284 
285         schema.addProcedure(procedure);
286       }
287     }
288     catch (Exception   e)
289     {
290       logger.error(Translate.get("backend.meta.get.procedures.failed", e
291           .getMessage()), e);
292     }
293     finally
294     {
295       try
296       {
297         rs.close();
298       }
299       catch (Exception   ignore)
300       {
301       }
302       try
303       {
304         rs2.close();
305       }
306       catch (Exception   ignoreAsWell)
307       {
308       }
309     }
310   }
311 
312   /**
313    * Gets the list of columns of a given database table. The caller must ensure
314    * that the parameters are not <code>null</code>.
315    * 
316    * @param metaData the database meta data
317    * @param table the database table
318    * @exception SQLException if an error occurs
319    */
320   private void getColumns(DatabaseMetaData   metaData, DatabaseTable table)
321       throws SQLException  
322   {
323     ResultSet   rs = null;
324     try
325     {
326       // Get columns meta data
327       // getColumns() gets a description of columns matching the catalog,
328       // schema, table name and column name pattern. Sending in null for
329       // catalog and schema drops them from the selection criteria. The
330       // column pattern "%" allows to obtain all columns.
331       rs = metaData.getColumns(null, null, table.getName(), "%");
332 
333       if (rs == null)
334       {
335         logger.warn(Translate.get("backend.meta.get.columns.failed", table
336             .getName()));
337         return;
338       }
339 
340       DatabaseColumn column = null;
341       int type;
342       while (rs.next())
343       {
344         // 1 is table catalog, 2 is table schema, 3 is table name,
345         // 4 is column name, 5 is data type
346         type = rs.getShort(5);
347         column = new DatabaseColumn(rs.getString(4), false, type);
348         table.addColumn(column);
349 
350         if (logger.isDebugEnabled())
351           logger.debug(Translate.get("backend.meta.found.column", rs
352               .getString(4)));
353       }
354     }
355     catch (SQLException   e)
356     {
357       throw new SQLException  (Translate.get("backend.meta.get.columns.failed",
358           table.getName()));
359     }
360     finally
361     {
362       try
363       {
364         rs.close();
365       }
366       catch (Exception   ignore)
367       {
368       }
369     }
370   }
371 
372   /**
373    * Gets the primary keys of a given database table. The caller must ensure
374    * that the parameters are not <code>null</code>.
375    * 
376    * @param metaData the database meta data
377    * @param table the database table
378    * @exception SQLException if an error occurs
379    */
380   private void getPrimaryKeys(DatabaseMetaData   metaData, DatabaseTable table)
381       throws SQLException  
382   {
383     ResultSet   rs = null;
384     try
385     {
386       // Get primary keys meta data
387       // getPrimaryKeys() gets a description of primary keys matching the
388       // catalog, schema, and table name. Sending in null for catalog and
389       // schema drop them from the selection criteria.
390 
391       rs = metaData.getPrimaryKeys(null, null, table.getName());
392 
393       if (rs == null)
394       {
395         logger.warn(Translate.get("backend.meta.get.primary.keys.failed", table
396             .getName()));
397         return;
398       }
399 
400       String   columnName = null;
401       while (rs.next())
402       {
403 
404         // 1 is table catalog, 2 is table schema, 3 is table name, 4 is column
405         // name
406         columnName = rs.getString(4);
407         if (columnName == null)
408           continue;
409         if (logger.isDebugEnabled())
410           logger.debug(Translate.get("backend.meta.found.primary.key",
411               columnName));
412 
413         // Set the column to unique
414         table.getColumn(columnName).setIsUnique(true);
415       }
416     }
417     catch (SQLException   e)
418     {
419       throw new SQLException  (Translate.get(
420           "backend.meta.get.primary.keys.failed", table.getName()));
421     }
422     finally
423     {
424       try
425       {
426         rs.close();
427       }
428       catch (Exception   ignore)
429       {
430       }
431     }
432   }
433 
434 }
435
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags