1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
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
92 /*** Database meta datas */
93 private DatabaseMetaData metaDatas;
94 /*** Acces authorizations */
95 private IAccessAuthorization authorizations;
96
97
98
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
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
119 ConfigurationHandler config = ConfigurationHandler.loadConfig(aConfStream);
120 return new JdbcInspector(config.getConnexionInfo(), config.getAccessAuthorizations());
121 }
122
123
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
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
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
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
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
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
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
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
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 }