View Javadoc

1   /* ==============================================================================
2    *   JDBCAdmin, data management software.
3    *   Copyright (C) 2005  Norsys S.A
4    *
5    *   This library is free software; you can redistribute it and/or
6    *   modify it under the terms of the GNU Lesser General Public
7    *   License as published by the Free Software Foundation; either
8    *   version 2.1 of the License, or (at your option) any later version.
9    *
10   *   This library is distributed in the hope that it will be useful,
11   *   but WITHOUT ANY WARRANTY; without even the implied warranty of
12   *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13   *   Lesser General Public License for more details.
14   *
15   *   You should have received a copy of the GNU Lesser General Public
16   *   License along with this library; if not, write to the Free Software
17   *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
18   * ==============================================================================
19   */
20  package jdbcadmin.core.tools;
21  
22  
23  import java.io.InputStream;
24  import java.sql.DatabaseMetaData;
25  import java.sql.ResultSet;
26  import java.sql.SQLException;
27  import java.sql.Types;
28  import java.util.ArrayList;
29  import java.util.Collection;
30  import java.util.Collections;
31  import java.util.Iterator;
32  import java.util.List;
33  import java.util.SortedMap;
34  import java.util.TreeMap;
35  
36  import jdbcadmin.core.access.IAccessAuthorization;
37  import jdbcadmin.core.data.Catalog;
38  import jdbcadmin.core.data.Column;
39  import jdbcadmin.core.data.ConnectionInfo;
40  import jdbcadmin.core.data.Schema;
41  import jdbcadmin.core.data.Table;
42  import jdbcadmin.core.exceptions.TechnicalException;
43  
44  /***
45   * JDBC Introspection class.
46   * @author Thomas Recloux (trecloux@norsys.fr)
47   */
48  public class JdbcInspector extends AbstractCnxUser {
49  
50      //////////////// Constantes /////////////////
51  
52      /*** MetaData column name which represents the table name */
53      private static final String TABLE_NAME     = "TABLE_NAME";
54      /*** MetaData column name which represents the column name */
55      private static final String COLUMN_NAME    = "COLUMN_NAME";
56      /*** MetaData column name which represents the data type*/
57      private static final String DATA_TYPE      = "DATA_TYPE";
58      /*** MetaData column name which represents the vendor lib for the data type*/
59      private static final String TYPE_NAME      = "TYPE_NAME";
60      /*** MetaData column name which represents the size of a column */
61      private static final String COLUMN_SIZE    = "COLUMN_SIZE";
62      /*** MetaData column name which represents the nullable property*/
63      private static final String NULLABLE       = "NULLABLE";
64      /*** MetaData column name which represents the number of decimal digits*/
65      private static final String DECIMAL_DIGITS = "DECIMAL_DIGITS";
66      /*** MetaData column name which represents the catalog of a table */
67      private static final String TABLE_CAT      = "TABLE_CAT";
68      /*** MetaData column name which represents the schema of a table */
69      private static final String TABLE_SCHEM    = "TABLE_SCHEM";
70  
71      /*** Table type : table */
72      private static final String TYP_TABLE      = "TABLE";
73      /*** Table type : view */
74      private static final String TYP_VIEW       = "VIEW";
75      /*** Table type : system table */
76      private static final String TYP_SYSTEM     = "SYSTEM TABLE";
77      /*** Table type : global temporary */
78      private static final String TYP_GLOB_TMP   = "GLOBAL TEMPORARY";
79      /*** Table type : local temporary */
80      private static final String TYP_LOCAL_TMP  = "LOCAL TEMPORARY";
81      /*** Table type : alias */
82      private static final String TYP_ALIAS      = "ALIAS";
83      /*** Table type : synomym */
84      private static final String TYP_SYNONYM    = "SYNONYM";
85  
86      /*** Date size */
87      private static final int DATE_SIZE = 10;
88      /*** Timestamp size*/
89      private static final int TSTP_SIZE = 23;
90  
91      /////////////// Attributes ////////////////////
92      /*** Database meta datas */
93      private DatabaseMetaData metaDatas;
94      /*** Acces authorizations */
95      private IAccessAuthorization authorizations;
96  
97  
98      /////////////// Constructors ////////////////
99      /***
100      * Constructor wich specify the connection informations and access authorizations.
101      * @param aConInfos connection informations
102      * @param aAuthorizations access authorizations
103      */
104     public JdbcInspector (ConnectionInfo aConInfos, IAccessAuthorization aAuthorizations) {
105         super(aConInfos);
106         this.authorizations = aAuthorizations;
107     }
108 
109 
110     ////////////// Static methods //////////
111     /***
112      * Retrieve an instance of the inspector.
113      * @param aConfStream Configuration stream
114      * @throws TechnicalException Technical error
115      * @return the instance
116      */
117     public static JdbcInspector getInstance (InputStream aConfStream) throws TechnicalException {
118         // Loads the configuration
119         ConfigurationHandler config = ConfigurationHandler.loadConfig(aConfStream);
120         return new JdbcInspector(config.getConnexionInfo(), config.getAccessAuthorizations());
121     }
122 
123     /////////////// Public methods ////////////////
124     /***
125      * Retrieves fully filled tables (with columns and primary key)<br>
126      * Key of the map : table names<br>
127      * Tables are sorted by their name
128      * @param aSchema schema to scan
129      * @throws TechnicalException technical error
130      * @return the tables
131      */
132     public SortedMap getFullTables(Schema aSchema) throws TechnicalException {
133         boolean doitDeconnecter = connect();
134         SortedMap tables = null;
135         try {
136             tables = getNakedTables(aSchema);
137             Iterator it = tables.values().iterator();
138             while (it.hasNext()) {
139                 Table tab = (Table) it.next();
140                 fillTable(tab);
141             }
142         } finally {
143             if (doitDeconnecter) {
144                 disconnect();
145             }
146         }
147         return tables;
148     }
149 
150     /***
151      * Retrieves the naked tables, without their columns
152      * Tables are sorted by their name
153      * @param aSchema schema to scan
154      * @throws TechnicalException technical error
155      * @return the tables
156      */
157     public SortedMap getNakedTables(Schema aSchema) throws TechnicalException {
158         boolean doitDeconnecter = connect();
159         SortedMap tables = new TreeMap();
160         ResultSet rs = null;
161         try {
162             //rs = metaDatas.getTables(null,null,null,new String [] {TYP_TABLE, TYP_ALIAS, TYP_SYNONYM});
163             rs = metaDatas.getTables(null, aSchema.getName(), "%", null);
164             while (rs.next()) {
165                 String nom = rs.getString(TABLE_NAME);
166                 if (authorizations == null || authorizations.isTableAuthorized(nom, aSchema.getName())) {
167                     tables.put(nom, new Table (nom, aSchema));
168                 }
169             }
170             return tables;
171         } catch (SQLException sqle) {
172             throw new TechnicalException("Error retrieving the tables", sqle);
173         } finally {
174             if (rs != null) {
175                 try {
176                     rs.close();
177                 } catch (SQLException sqle) {
178                     // Nothing to do
179                 }
180             }
181             if (doitDeconnecter) {
182                 disconnect();
183             }
184         }
185     }
186 
187     /***
188      * Retrieves the table columns.
189      * @param aTable the table
190      * @return a {@link #java.util.Collection} of {@link #jdbcadmin.core.data.Column}
191      * @throws TechnicalException Technical error
192      */
193     public Collection getColumns(Table aTable) throws TechnicalException {
194         boolean doitDeconnecter = connect();
195         Collection colones = new ArrayList();
196         ResultSet rs = null;
197         try {
198             rs = metaDatas.getColumns(null, aTable.getSchema().getName(), aTable.getName(), null);
199             while (rs.next()) {
200                 Column col = new Column();
201                 col.setName(rs.getString(COLUMN_NAME));
202                 col.setType(rs.getInt(DATA_TYPE));
203                 col.setTypeName(rs.getString(TYPE_NAME));
204                 col.setSize(rs.getInt(COLUMN_SIZE));
205                 col.setDecimalDigits(rs.getInt(DECIMAL_DIGITS));
206                 int nullable = rs.getInt(NULLABLE);
207                 if (nullable == DatabaseMetaData.columnNoNulls) {
208                     col.setNullable(false);
209                 } else if (nullable == DatabaseMetaData.columnNullable) {
210                     col.setNullable(true);
211                 } else {
212                     throw new TechnicalException ("Unknown nullable value |" + nullable + "| for "
213                             + aTable.getCompleteName() + "." + col.getName());
214                 }
215                 if (col.getType() == Types.DATE) {
216                     col.setSize(DATE_SIZE);
217                 } else if (col.getType() == Types.TIMESTAMP) {
218                     col.setSize(TSTP_SIZE);
219                 }
220                 colones.add(col);
221             }
222             return colones;
223         } catch (SQLException sqle) {
224             throw new TechnicalException("Error retrieving the table columns"
225                     + aTable.getCompleteName(), sqle);
226         } finally {
227             if (rs != null) {
228                 try {
229                     rs.close();
230                 } catch (SQLException sqle) {
231                     // Nothing to do
232                 }
233             }
234             if (doitDeconnecter) {
235                 disconnect();
236             }
237         }
238     }
239 
240     /***
241      * Retrieves primary keys for a table.
242      * @param aTableName The table name
243      * @return a {@link #java.util.Collection} of {@link #java.lang.String}
244      * @throws TechnicalException Technical error
245      */
246     public Collection getPrimaryKeys (String aTableName) throws TechnicalException {
247         boolean doitDeconnecter = connect();
248         Collection pks     = new ArrayList();
249         ResultSet rs = null;
250         try {
251             // recuperation des cles primaires
252             rs = metaDatas.getPrimaryKeys(null, null, aTableName);
253             while (rs.next()) {
254                 pks.add(rs.getString(COLUMN_NAME));
255             }
256             return pks;
257         } catch (SQLException sqle) {
258             throw new TechnicalException("Error retrieving the primary keys", sqle);
259         } finally {
260             if (rs != null) {
261                 try {
262                     rs.close();
263                 } catch (SQLException sqle) {
264                     // ne rien faire
265                 }
266             }
267             if (doitDeconnecter) {
268                 disconnect();
269             }
270         }
271     }
272 
273     /***
274      * @return The db catalogs
275      * @throws TechnicalException Technical error
276      */
277     public Collection getCalalogs() throws TechnicalException {
278         boolean doitDeconnecter = connect();
279         ResultSet rs = null;
280         Collection catalogues = new ArrayList();
281         try {
282             rs = metaDatas.getCatalogs();
283             while (rs.next()) {
284                 catalogues.add(new Catalog(rs.getString(TABLE_CAT)));
285             }
286             return catalogues;
287         } catch (SQLException sqle) {
288             throw new TechnicalException("Error retrieving the database catalogs", sqle);
289         } finally {
290             if (rs != null) {
291                 try {
292                     rs.close();
293                 } catch (SQLException sqle) {
294                     // Nothing to do
295                 }
296             }
297             if (doitDeconnecter) {
298                 disconnect();
299             }
300         }
301     }
302 
303     /***
304      * @return Sorted schema list
305      * @throws TechnicalException Technical error
306      */
307     public List getSchemas() throws TechnicalException {
308         boolean doitDeconnecter = connect();
309         ResultSet rs = null;
310         List schemas = new ArrayList();
311         try {
312             rs = metaDatas.getSchemas();
313             while (rs.next()) {
314                 String nomSchema = rs.getString(TABLE_SCHEM);
315                 if (authorizations == null || authorizations.isSchemaAuthorized(nomSchema)) {
316                     schemas.add(new Schema(nomSchema));
317                 }
318             }
319             Collections.sort(schemas);
320             return schemas;
321         } catch (SQLException sqle) {
322             throw new TechnicalException("Error retrieving the schemas", sqle);
323         } finally {
324             if (rs != null) {
325                 try {
326                     rs.close();
327                 } catch (SQLException sqle) {
328                     // Nothing to do
329                 }
330             }
331             if (doitDeconnecter) {
332                 disconnect();
333             }
334         }
335     }
336 
337     /***
338      * Complete a table (columns and primary keys)
339      * @param aTable the table to fill
340      * @throws TechnicalException technical error
341      */
342     public void fillTable (Table aTable) throws TechnicalException {
343         boolean doitDeconnecter = connect();
344         try {
345             Iterator it = getColumns(aTable).iterator();
346             while (it.hasNext()) {
347                 aTable.addColumn((Column) it.next());
348             }
349             it = getPrimaryKeys(aTable.getName()).iterator();
350             while (it.hasNext()) {
351                 aTable.addPKColumn((String) it.next());
352             }
353             aTable.setFilled(true);
354         } finally {
355             if (doitDeconnecter) {
356                 disconnect();
357             }
358         }
359     }
360 
361     /***
362      * Retrieve a filled table
363      * @param aSchema the schema to scan
364      * @param aTableName name of the table
365      * @return the filled table
366      * @throws TechnicalException technical error
367      */
368     public Table getFullTable(Schema aSchema, String aTableName) throws TechnicalException {
369         Table table = (Table) getNakedTables(aSchema).get(aTableName);
370         if (table != null) {
371             fillTable(table);
372         } else {
373             throw new TechnicalException("The table |" + aTableName + "|  has not been found in the schema |"
374                     + aSchema.getName() + "|");
375         }
376         return table;
377     }
378 
379 
380     /////////////// Private methods////////////////
381 
382     /***
383      * If not initialized, creates or retrieves the connection and updates the database meta datas
384      * @return <code>true</code> if the connection had to be created/retrieved
385      * (in this case, the calling method <b>must</b> call {@link #disconnect}), <code>false</code> otherwise
386      * @throws TechnicalException error connecting to db.
387      **/
388     protected boolean connect() throws TechnicalException  {
389         boolean aConnecte = super.connect();
390         if (aConnecte) {
391             try {
392                 metaDatas = cnx.getMetaData();
393             } catch (SQLException sqle) {
394                 throw new TechnicalException ("Erreur lors de la recuperation des metas données", sqle);
395             }
396         }
397         return aConnecte;
398     }
399 
400     /***
401      * Closes the connection.
402      **/
403     protected void disconnect() {
404         super.disconnect();
405         metaDatas = null;
406     }
407 }