Java > Open Source Codes > org > continuent > sequoia > controller > backend > DatabaseSQLMetaData


1 /**
2  * Sequoia: Database clustering technology.
3  * Copyright (C) 2002-2004 French National Institute For Research In Computer
4  * Science And Control (INRIA).
5  * Copyright (C) 2005 AmicoSoft, Inc. dba Emic Networks
6  * Copyright (C) 2005 Continuent, Inc.
7  * Contact: sequoia@continuent.org
8  *
9  * Licensed under the Apache License, Version 2.0 (the "License");
10  * you may not use this file except in compliance with the License.
11  * You may obtain a copy of the License at
12  *
13  * http://www.apache.org/licenses/LICENSE-2.0
14  *
15  * Unless required by applicable law or agreed to in writing, software
16  * distributed under the License is distributed on an "AS IS" BASIS,
17  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
18  * See the License for the specific language governing permissions and
19  * limitations under the License.
20  *
21  * Initial developer(s): Nicolas Modrzyk.
22  * Contributor(s): Emmanuel Cecchet, Edward Archibald.
23  */
24
25 package org.continuent.sequoia.controller.backend;
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.continuent.sequoia.common.i18n.Translate;
33 import org.continuent.sequoia.common.log.Trace;
34 import org.continuent.sequoia.controller.sql.schema.DatabaseColumn;
35 import org.continuent.sequoia.controller.sql.schema.DatabaseProcedure;
36 import org.continuent.sequoia.controller.sql.schema.DatabaseProcedureParameter;
37 import org.continuent.sequoia.controller.sql.schema.DatabaseSchema;
38 import org.continuent.sequoia.controller.sql.schema.DatabaseTable;
39 import org.continuent.sequoia.controller.sql.schema.DynamicDatabaseSchema;
40
41 /**
42  * This class defines a DatabaseSQLMetaData. It is used to collect metadata from
43  * a live connection to a database
44  *
45  * @author <a HREF="mailto:ed.archibald@continuent.com">Edward Archibald</a>
46  * @author <a HREF="mailto:emmanuel.cecchet@continuent.com">Emmanuel Cecchet
47  * </a>
48  * @author <a HREF="mailto:Nicolas.Modrzyk@inrialpes.fr">Nicolas Modrzyk </a>
49  * @version 1.0
50  */
51 public class DatabaseSQLMetaData
52 {
53   private Trace logger;
54   private Connection connection;
55   private DynamicDatabaseSchema dynamicDatabaseSchema;
56   private String schemaPattern;
57
58   /**
59    * Creates a new <code>DatabaseSQLMetaData</code> object
60    *
61    * @param logger the log4j logger to output to
62    * @param connection a jdbc connection to a database
63    * @param dynamicDatabaseSchema precision used to create the schema
64    * @see DatabaseBackendSchemaConstants
65    */
66   public DatabaseSQLMetaData(Trace logger, Connection connection,
67       DynamicDatabaseSchema dynamicDatabaseSchema)
68   {
69     super();
70     this.logger = logger;
71     this.connection = connection;
72     this.dynamicDatabaseSchema = dynamicDatabaseSchema;
73     this.schemaPattern = dynamicDatabaseSchema.getSchemaName();
74   }
75
76   /**
77    * Create a database schema from the given connection
78    *
79    * @param vdbName the virtual database name this schema represents
80    * @return <code>DataSchema</code> contructed from the information collected
81    * through jdbc
82    * @throws SQLException if an error occurs with the given connection
83    */
84   public final DatabaseSchema createDatabaseSchema(String vdbName)
85       throws SQLException
86   {
87     ResultSet rs = null;
88
89     boolean connectionWasAutocommit = connection.getAutoCommit();
90
91     if (connectionWasAutocommit)
92       connection.setAutoCommit(false); // Needed for Derby Get DatabaseMetaData
93
94     DatabaseMetaData metaData = connection.getMetaData();
95     if (metaData == null)
96     {
97       logger.warn(Translate.get("backend.meta.received.null"));
98       return null;
99     }
100
101     if (logger.isDebugEnabled())
102       logger.debug("Fetching schema"
103           + (dynamicDatabaseSchema.useStoredProcedures()
104               ? ""
105               : " with stored procedure information ")
106           + (dynamicDatabaseSchema.gatherSystemTables() ? "" : " not")
107           + " including system tables.");
108
109     DatabaseSchema databaseSchema = new DatabaseSchema(vdbName);
110
111     // Check if we should get system tables or not
112 String [] types;
113     if (dynamicDatabaseSchema.gatherSystemTables())
114     {
115       schemaPattern = null;
116       if (dynamicDatabaseSchema.useViews())
117         types = new String []{"TABLE", "VIEW", "SYSTEM TABLE", "SYSTEM VIEW", "SEQUENCE"};
118       else
119         types = new String []{"TABLE", "SYSTEM TABLE", "SEQUENCE"};
120     }
121     else
122     {
123       if (dynamicDatabaseSchema.useViews())
124         types = new String []{"TABLE", "VIEW", "SEQUENCE"};
125       else
126         types = new String []{"TABLE", "SEQUENCE"};
127     }
128
129     // Get tables meta data
130 // getTables() gets a description of tables matching the catalog, schema,
131 // table name pattern and type. Sending in null for catalog and schema
132 // drops them from the selection criteria. The table name pattern "%"
133 // means match any substring of 0 or more characters.
134 // Last argument allows to obtain only database tables
135 try
136     {
137       rs = metaData.getTables(null, schemaPattern, "%", types);
138     }
139     catch (Exception e)
140     {
141       // VIEWS cannot be retrieved with this backend
142 logger.error(Translate.get("backend.meta.view.not.supported"), e);
143       if (dynamicDatabaseSchema.gatherSystemTables())
144         types = new String []{"TABLE", "SYSTEM TABLE"};
145       else
146         types = new String []{"TABLE"};
147       rs = metaData.getTables(null, schemaPattern, "%", types);
148     }
149
150     if (rs == null)
151     {
152       logger.warn(Translate.get("backend.meta.received.null"));
153       if (connectionWasAutocommit)
154         connection.commit();
155       return null;
156     }
157
158     String tableName;
159     String schemaName;
160     DatabaseTable table = null;
161     while (rs.next())
162     {
163       // 1 is table catalog, 2 is table schema, 3 is table name, 4 is type
164 schemaName = rs.getString(2);
165       tableName = rs.getString(3);
166       String type = rs.getString(4);
167       if (logger.isDebugEnabled())
168         logger.debug(Translate.get("backend.meta.found.table", schemaName + "."
169             + tableName));
170
171       // Create a new table and add it to the database schema
172 table = new DatabaseTable(tableName);
173       table.setSchema(schemaName);
174       if (!type.equals("VIEW"))
175       {
176         getExportedKeys(metaData, table);
177         getImportedKeys(metaData, table);
178       }
179       databaseSchema.addTable(table);
180
181       // Get information about this table columns (not used)
182 if (dynamicDatabaseSchema.useColumns())
183       {
184         getColumns(metaData, table);
185
186         // Get information about this table primary keys (only if columns are
187 // fetched)
188 if (!type.equals("VIEW"))
189         {
190           getPrimaryKeys(metaData, table);
191         }
192       }
193     }
194
195     // Get Procedures for this database
196 if (dynamicDatabaseSchema.useStoredProcedures())
197       getProcedures(metaData, databaseSchema);
198
199     try
200     {
201       rs.close();
202     }
203     catch (Exception ignore)
204     {
205     }
206
207     if (connectionWasAutocommit)
208     {
209       try
210       {
211         connection.commit();
212       }
213       catch (Exception ignore)
214       {
215         // This was a read-only transaction
216 }
217
218       try
219       {
220         // restore connection
221 connection.setAutoCommit(true);
222       }
223       catch (SQLException e1)
224       {
225         // ignore, transaction is no more valid
226 }
227     }
228
229     return databaseSchema;
230   }
231
232   /**
233    * @see java.sql.DatabaseMetaData#getProcedures
234    * @see java.sql.DatabaseMetaData#getProcedureColumns
235    */
236   private void getProcedures(DatabaseMetaData metaData, DatabaseSchema schema)
237   {
238     if (logger.isDebugEnabled())
239       logger.debug(Translate.get("backend.meta.get.procedures"));
240
241     ResultSet rs = null;
242     ResultSet rs2 = null;
243     try
244     {
245       // Get Procedures meta data
246 rs = metaData.getProcedures(null, schemaPattern, "%");
247
248       if (rs == null)
249       {
250         logger.warn(Translate.get("backend.meta.get.procedures.failed",
251             metaData.getConnection().getCatalog()));
252         return;
253       }
254
255       while (rs.next())
256       {
257         // Each row is a procedure description
258 // 3 = PROCEDURE_NAME
259 // 7 = REMARKS
260 // 8 = PROCEDURE_TYPE
261 DatabaseProcedure procedure = new DatabaseProcedure(rs.getString(3), rs
262             .getString(7), rs.getShort(8));
263
264         // Check if we need to fetch the procedure parameters
265 if (!dynamicDatabaseSchema.useStoredProcedures())
266         {
267           if (logger.isDebugEnabled()
268               && schema.getProcedure(procedure.getName()) != null)
269           {
270             logger.debug(Translate
271                 .get("backend.meta.procedure.already.in.schema", procedure
272                     .getName()));
273           }
274           continue;
275         }
276
277         // Get the column information
278 rs2 = metaData
279             .getProcedureColumns(null, null, procedure.getName(), "%");
280         if (rs2 == null)
281           logger.warn(Translate.get("backend.meta.get.procedure.params.failed",
282               procedure.getName()));
283         else
284         {
285           while (rs2.next())
286           {
287             // Each row is a parameter description for the current procedure
288 // 4 = COLUMN_NAME
289 // 5 = COLUMN_TYPE
290 // 6 = DATA_TYPE
291 // 7 = TYPE_NAME
292 // 8 = PRECISION
293 // 9 = LENGTH
294 // 10 = SCALE
295 // 11 = RADIX
296 // 12 = NULLABLE
297 // 13 = REMARKS
298 DatabaseProcedureParameter param = new DatabaseProcedureParameter(
299                 rs2.getString(4), rs2.getInt(5), rs2.getInt(6), rs2
300                     .getString(7), rs2.getFloat(8), rs2.getInt(9), rs2
301                     .getInt(10), rs2.getInt(11), rs2.getInt(12), rs2
302                     .getString(13));
303             procedure.addParameter(param);
304             if (logger.isDebugEnabled())
305               logger.debug(procedure.getName() + ": adding parameter "
306                   + param.getName());
307           }
308           rs2.close();
309         }
310
311         // Add procedure only if it is not already in schema
312 if (!schema.getProcedures().containsValue(procedure))
313         {
314           schema.addProcedure(procedure);
315           if (logger.isDebugEnabled())
316             logger.debug(Translate.get("backend.meta.procedure.added",
317                 procedure.getKey()));
318         }
319         else if (logger.isDebugEnabled())
320         {
321           logger.debug(Translate.get(
322               "backend.meta.procedure.already.in.schema", procedure.getName()));
323         }
324       }
325     }
326     catch (Exception e)
327     {
328       logger.error(Translate.get("backend.meta.get.procedures.failed", e
329           .getMessage()), e);
330     }
331     finally
332     {
333       try
334       {
335         rs.close();
336       }
337       catch (Exception ignore)
338       {
339       }
340       try
341       {
342         rs2.close();
343       }
344       catch (Exception ignoreAsWell)
345       {
346       }
347     }
348   }
349
350   /**
351    * Gets the list of columns of a given database table. The caller must ensure
352    * that the parameters are not <code>null</code>.
353    *
354    * @param metaData the database meta data
355    * @param table the database table
356    * @exception SQLException if an error occurs
357    */
358   private void getColumns(DatabaseMetaData metaData, DatabaseTable table)
359       throws SQLException
360   {
361     ResultSet rs = null;
362     try
363     {
364       // Get columns meta data
365 // getColumns() gets a description of columns matching the catalog,
366 // schema, table name and column name pattern. Sending in null for
367 // catalog and schema drops them from the selection criteria. The
368 // column pattern "%" allows to obtain all columns.
369 rs = metaData.getColumns(null, table.getSchema(), table.getName(), "%");
370
371       if (rs == null)
372       {
373         logger.warn(Translate.get("backend.meta.get.columns.failed", table
374             .getSchema()
375             + "." + table.getName()));
376         return;
377       }
378
379       DatabaseColumn column = null;
380       int type;
381       while (rs.next())
382       {
383         // 1 is table catalog, 2 is table schema, 3 is table name,
384 // 4 is column name, 5 is data type
385 type = rs.getShort(5);
386         column = new DatabaseColumn(rs.getString(4), false, type);
387         table.addColumn(column);
388
389         if (logger.isDebugEnabled())
390           logger.debug(Translate.get("backend.meta.found.column", rs
391               .getString(4)));
392       }
393     }
394     catch (SQLException e)
395     {
396       throw new SQLException (Translate.get("backend.meta.get.columns.failed",
397           table.getSchema() + "." + table.getName()));
398     }
399     finally
400     {
401       try
402       {
403         rs.close();
404       }
405       catch (Exception ignore)
406       {
407       }
408     }
409   }
410
411   /**
412    * Gets the exported keys of a given database table. The exported keys will be
413    * automatically added to the list of depending tables of the given table.<br>
414    * The caller must ensure that the parameters are not <code>null</code>.
415    *
416    * @param metaData the database meta data
417    * @param table the database table
418    */
419   private void getExportedKeys(DatabaseMetaData metaData, DatabaseTable table)
420   {
421     ResultSet rs = null;
422     try
423     {
424       // Get exported keys meta data
425 // getExportedKeys() gets a description of exported keys matching the
426 // catalog, schema, and table name. Sending in null for catalog and
427 // schema drop them from the selection criteria.
428
429       rs = metaData.getExportedKeys(null, table.getSchema(), table.getName());
430
431       if (rs == null)
432       {
433         logger.warn(Translate.get("backend.meta.get.exported.keys.failed",
434             table.getSchema() + "." + table.getName()));
435         return;
436       }
437
438       String referencingTableName = null;
439       while (rs.next())
440       {
441         // 7. FKTABLE_NAME (8. FK_COLUMN_NAME)
442 referencingTableName = rs.getString(7);
443         if (referencingTableName == null)
444           continue;
445         if (logger.isDebugEnabled())
446           logger.debug(Translate.get("backend.meta.found.exported.key",
447               referencingTableName));
448
449         // Set the column to unique
450 table.addDependingTable(referencingTableName);
451       }
452     }
453     catch (SQLException e)
454     {
455       logger.warn(Translate.get("backend.meta.get.exported.keys.failed", table
456           .getSchema()
457           + "." + table.getName()), e);
458     }
459     finally
460     {
461       try
462       {
463         rs.close();
464       }
465       catch (Exception ignore)
466       {
467       }
468     }
469   }
470
471   /**
472    * Gets the imported keys of a given database table. The imported keys will be
473    * automatically added to the list of depending tables of the given table.<br>
474    * The caller must ensure that the parameters are not <code>null</code>.
475    *
476    * @param metaData the database meta data
477    * @param table the database table
478    */
479   private void getImportedKeys(DatabaseMetaData metaData, DatabaseTable table)
480   {
481     ResultSet rs = null;
482     try
483     {
484       // Get imported keys meta data
485 // getImportedKeys() gets a description of imported keys matching the
486 // catalog, schema, and table name. Sending in null for catalog and
487 // schema drop them from the selection criteria.
488
489       rs = metaData.getImportedKeys(null, table.getSchema(), table.getName());
490
491       if (rs == null)
492       {
493         logger.warn(Translate.get("backend.meta.get.imported.keys.failed",
494             table.getSchema() + "." + table.getName()));
495         return;
496       }
497
498       String referencedTableName = null;
499       while (rs.next())
500       {
501         // 3. PKTABLE_NAME (4. PKCOLUMN_NAME)
502 referencedTableName = rs.getString(3);
503         if (referencedTableName == null)
504           continue;
505         if (logger.isDebugEnabled())
506           logger.debug(Translate.get("backend.meta.found.imported.key",
507               referencedTableName));
508
509         // Set the column to unique
510 table.addDependingTable(referencedTableName);
511       }
512     }
513     catch (SQLException e)
514     {
515       logger.warn(Translate.get("backend.meta.get.imported.keys.failed", table
516           .getSchema()
517           + "." + table.getName()), e);
518     }
519     finally
520     {
521       try
522       {
523         rs.close();
524       }
525       catch (Exception ignore)
526       {
527       }
528     }
529   }
530
531   /**
532    * Gets the primary keys of a given database table. The caller must ensure
533    * that the parameters are not <code>null</code>.
534    *
535    * @param metaData the database meta data
536    * @param table the database table
537    * @exception SQLException if an error occurs
538    */
539   private void getPrimaryKeys(DatabaseMetaData metaData, DatabaseTable table)
540       throws SQLException
541   {
542     ResultSet rs = null;
543     try
544     {
545       // Get primary keys meta data
546 // getPrimaryKeys() gets a description of primary keys matching the
547 // catalog, schema, and table name. Sending in null for catalog and
548 // schema drop them from the selection criteria.
549
550       rs = metaData.getPrimaryKeys(null, table.getSchema(), table.getName());
551
552       if (rs == null)
553       {
554         logger.warn(Translate.get("backend.meta.get.primary.keys.failed", table
555             .getSchema()
556             + "." + table.getName()));
557         return;
558       }
559
560       String columnName = null;
561       while (rs.next())
562       {
563
564         // 1 is table catalog, 2 is table schema, 3 is table name, 4 is column
565 // name
566 columnName = rs.getString(4);
567         if (columnName == null)
568           continue;
569         if (logger.isDebugEnabled())
570           logger.debug(Translate.get("backend.meta.found.primary.key",
571               columnName));
572
573         // Set the column to unique
574 table.getColumn(columnName).setIsUnique(true);
575       }
576     }
577     catch (SQLException e)
578     {
579       throw new SQLException (Translate.get(
580           "backend.meta.get.primary.keys.failed", table.getSchema() + "."
581               + table.getName()));
582     }
583     finally
584     {
585       try
586       {
587         rs.close();
588       }
589       catch (Exception ignore)
590       {
591       }
592     }
593   }
594
595 }
596

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags