1 /**
2 * BSD-style license; for more info see http://pmd.sourceforge.net/license.html
3 */
4 package net.sourceforge.pmd.util.database;
5
6 import java.net.MalformedURLException;
7 import java.sql.CallableStatement;
8 import java.sql.Clob;
9 import java.sql.Connection;
10 import java.sql.DatabaseMetaData;
11 import java.sql.DriverManager;
12 import java.sql.PreparedStatement;
13 import java.sql.ResultSet;
14 import java.sql.SQLException;
15 import java.util.ArrayList;
16 import java.util.Arrays;
17 import java.util.List;
18 import java.util.Map;
19 import java.util.Properties;
20 import java.util.logging.Level;
21 import java.util.logging.Logger;
22
23 /**
24 * Wrap JDBC connection for use by PMD: {@link DBURI} parameters specify the source code to
25 * be passed to PMD.
26 *
27 * @author sturton
28 */
29 public class DBMSMetadata
30 {
31
32 /**
33 * Classname utility string for use in logging.
34 */
35 private final static String CLASS_NAME = DBMSMetadata.class.getCanonicalName();
36
37 /**
38 * Local logger.
39 */
40 private final static Logger LOGGER = Logger.getLogger(CLASS_NAME);
41
42 /**
43 * Optional DBType property specifying a query to fetch the Source Objects from the database.
44 *
45 * <p>If the DBType lacks this property, then the standard DatabaseMetaData.getProcedures method is used.
46 * </p>
47 */
48 private final static String GET_SOURCE_OBJECTS_STATEMENT = "getSourceObjectsStatement" ;
49
50 /**
51 * Essential DBType property specifying a CallableStatement to retrieve the Source Object's code from the database.
52 *
53 * <p><b>If the DBType lacks this property, there is no DatabaseMetaData method to fallback to</b>.
54 * </p>
55 */
56 private final static String GET_SOURCE_CODE_STATEMENT = "getSourceCodeStatement" ;
57
58 /**
59 * DBURI
60 */
61 protected DBURI dburi = null;
62
63 /**
64 * Connection management
65 */
66 protected Connection connection = null;
67
68 /**
69 * Procedural statement to return list of source code objects.
70 */
71 protected String returnSourceCodeObjectsStatement = null ;
72
73 /**
74 * Procedural statement to return source code.
75 */
76 protected String returnSourceCodeStatement = null ;
77
78 /**
79 * CallableStatement to return source code.
80 */
81 protected CallableStatement callableStatement = null;
82
83 /**
84 * {@link java.sql.Types} value representing the type returned by {@link callableStatement}
85 *
86 * <b>Currently only java.sql.Types.String and java.sql.Types.Clob are supported</b>
87 */
88 protected int returnType = java.sql.Types.CLOB ;
89
90 /**
91 * Return JDBC Connection for direct JDBC access to the specified database.
92 *
93 * @return I=JDBC Connection
94 * @throws SQLException
95 */
96 public Connection getConnection() throws SQLException
97 { return connection; }
98
99 /* constructors */
100 /**
101 * Minimal constructor
102 * @param c JDBC Connection
103 * @throws SQLException
104 */
105 public DBMSMetadata(Connection c) throws SQLException
106 { connection = c; }
107
108 /**
109 * Define database connection and source code to retrieve with explicit database username and password.
110 *
111 * @param user Database username
112 * @param password Database password
113 * @param dbURI {@link DBURI } containing JDBC connection plus parameters to specify source code.
114 * @throws SQLException on failing to create JDBC connection
115 * @throws MalformedURLException on attempting to connect with malformed JDBC URL
116 * @throws ClassNotFoundException on failing to locate the JDBC driver class.
117 */
118 public DBMSMetadata(String user, String password, DBURI dbURI) throws SQLException, MalformedURLException, ClassNotFoundException
119 {
120 this.dburi = dbURI;
121
122 this.returnSourceCodeObjectsStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_OBJECTS_STATEMENT) ;
123
124 this.returnSourceCodeStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_CODE_STATEMENT) ;
125
126 this.returnType = dbURI.getSourceCodeType();
127
128 LOGGER.fine("returnSourceCodeStatement="+returnSourceCodeStatement +", returnType="+returnType);
129
130 String driverClass = dbURI.getDriverClass();
131 String urlString = dbURI.getURL().toString();
132
133 LOGGER.fine("driverClass="+driverClass+", urlString="+urlString);
134
135 Class.forName(driverClass);
136
137 Properties mergedProperties = dbURI.getDbType().getProperties() ;
138 Map<String,String> dbURIParameters = dbURI.getParameters();
139 mergedProperties.putAll(dbURIParameters) ;
140 mergedProperties.put("user", user) ;
141 mergedProperties.put("password", password) ;
142
143 connection = DriverManager.getConnection(urlString, mergedProperties );
144 LOGGER.fine("we have a connection="+connection);
145 }
146
147
148 /**
149 * Define database connection and source code to retrieve with database properties.
150 *
151 * @param properties database settings such as database username, password
152 * @param dbURI {@link DBURI } containing JDBC connection plus parameters to specify source code.
153 * @throws SQLException on failing to create JDBC connection
154 * @throws MalformedURLException on attempting to connect with malformed JDBC URL
155 * @throws ClassNotFoundException on failing to locate the JDBC driver class.
156 */
157 public DBMSMetadata(Properties properties, DBURI dbURI) throws SQLException, MalformedURLException, ClassNotFoundException
158 {
159 this.dburi = dbURI;
160 this.returnSourceCodeObjectsStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_OBJECTS_STATEMENT) ;
161 this.returnSourceCodeStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_CODE_STATEMENT) ;
162 this.returnType = dbURI.getSourceCodeType();
163 LOGGER.fine("returnSourceCodeStatement="+returnSourceCodeStatement +", returnType="+returnType);
164
165
166 String driverClass = dbURI.getDriverClass();
167 String urlString = dbURI.getURL().toString();
168
169 LOGGER.fine("driverClass="+driverClass+", urlString="+urlString);
170
171 Class.forName(driverClass);
172
173 LOGGER.fine("Located class for driverClass="+driverClass);
174
175 Properties mergedProperties = dbURI.getDbType().getProperties() ;
176 Map<String,String> dbURIParameters = dbURI.getParameters();
177 mergedProperties.putAll(dbURIParameters) ;
178 mergedProperties.putAll(properties) ;
179
180 LOGGER.fine("Retrieving connection for urlString"+urlString);
181 connection = DriverManager.getConnection(urlString ,mergedProperties);
182 LOGGER.fine("Secured Connection for DBURI"+dbURI);
183 }
184
185 /**
186 * Define database connection and source code to retrieve.
187 *
188 * <p>This constructor is reliant on database username and password embedded in the JDBC URL
189 * or defaulted from the {@link DBURI}'s {@link DriverType}.
190 *
191 * @param user Database username
192 * @param password Database password
193 * @param dbURI {@link DBURI } containing JDBC connection plus parameters to specify source code.
194 * @throws SQLException on failing to create JDBC connection
195 * @throws ClassNotFoundException on failing to locate the JDBC driver class.
196 */
197 public DBMSMetadata(DBURI dbURI) throws SQLException, ClassNotFoundException
198 {
199
200 this.dburi = dbURI;
201 this.returnType = dbURI.getSourceCodeType();
202
203 DBType dbType = dbURI.getDbType();
204 LOGGER.fine("dbType="+dbType );
205 Properties dbURIProperties = dbType.getProperties() ;
206 this.returnSourceCodeObjectsStatement = dbURIProperties.getProperty(GET_SOURCE_OBJECTS_STATEMENT) ;
207 this.returnSourceCodeStatement = dbURIProperties.getProperty(GET_SOURCE_CODE_STATEMENT) ;
208 LOGGER.fine("returnSourceCodeStatement="+returnSourceCodeStatement +", returnType="+returnType);
209
210 String driverClass = dbURI.getDriverClass();
211 String urlString = dbURI.getURL().toString();
212
213 LOGGER.fine("driverClass="+driverClass+", urlString="+urlString);
214
215 Class.forName(driverClass);
216
217 Map<String,String> dbURIParameters = dbURI.getParameters();
218
219 /*Overwrite any DBType properties with DBURI parameters
220 * allowing JDBC connection properties to be inherited from DBType
221 * or passed as DBURI parameters
222 */
223 dbURIProperties.putAll(dbURIParameters) ;
224
225 connection = DriverManager.getConnection(urlString, dbURIProperties);
226 }
227
228 /**
229 * Return source code text from the database.
230 *
231 * @param source object
232 * @return source code
233 * @throws SQLException
234 */
235 public java.io.Reader getSourceCode (SourceObject sourceObject )
236 throws SQLException
237 {
238 return getSourceCode(
239 sourceObject.getType(), sourceObject.getName(), sourceObject.getSchema());
240
241 }
242
243 /**
244 * return source code text
245 *
246 * @param objectType
247 * @param name Source Code name
248 * @param schema Owner of the code
249 * @return Source code text.
250 * @throws SQLException on failing to retrieve the source Code text
251 */
252 public java.io.Reader getSourceCode (
253 String objectType,
254 String name,
255 String schema
256 )
257 throws SQLException
258 {
259 Object result;
260
261 /* Only define callableStatement once and reuse it for subsequent calls to getSourceCode()*/
262 if (null == callableStatement)
263 {
264 LOGGER.finest("getSourceCode: returnSourceCodeStatement=\""+returnSourceCodeStatement+"\"");
265 LOGGER.finest("getSourceCode: returnType=\""+returnType+"\"");
266 callableStatement = getConnection().prepareCall(returnSourceCodeStatement);
267 callableStatement.registerOutParameter(1, returnType);
268 }
269
270 // set IN parameters
271 callableStatement.setString(2, objectType);
272 callableStatement.setString(3, name);
273 callableStatement.setString(4, schema);
274 //
275 // execute statement
276 callableStatement.executeUpdate();
277 // retrieve OUT parameters
278 result = callableStatement.getObject(1);
279
280 return (java.sql.Types.CLOB == returnType)
281 ? ((Clob) result).getCharacterStream()
282 : new java.io.StringReader( result.toString() )
283 ;
284 }
285
286 /**
287 * Return all source code objects associated with any associated DBURI.
288 * @return
289 */
290 public List<SourceObject> getSourceObjectList ()
291 {
292
293 if (null == dburi)
294 {
295 LOGGER.warning("No dbUri defined - no further action possible");
296 return null;
297 }
298 else
299 {
300 return getSourceObjectList(
301 dburi.getLanguagesList()
302 ,dburi.getSchemasList()
303 ,dburi.getSourceCodeTypesList()
304 ,dburi.getSourceCodeNamesList()
305 );
306 }
307
308 }
309
310 /**
311 * Return all source code objects associated with the specified languages, schemas, source code types and source code names.
312 *
313 * <p>Each parameter may be null and the appropriate field from any related DBURI is assigned, defaulting to the normal SQL wildcard expression ("%").
314 * </.>
315 * @param languages Optional list of languages to search for
316 * @param schemas Optional list of schemas to search for
317 * @param sourceCodeTypes Optional list of source code types to search for
318 * @param sourceCodeNames Optional list of source code names to search for
319 */
320 public List<SourceObject> getSourceObjectList (List<String> languages, List<String> schemas, List<String> sourceCodeTypes, List<String> sourceCodeNames )
321 {
322
323 ResultSet sourceCodeObjects = null ;
324 List<SourceObject> sourceObjectsList = new ArrayList<SourceObject>() ;
325
326 List<String> searchLanguages = languages ;
327 List<String> searchSchemas = schemas ;
328 List<String> searchSourceCodeTypes = sourceCodeTypes ;
329 List<String> searchSourceCodeNames = sourceCodeNames ;
330 List<String> wildcardList = Arrays.asList(new String[] {"%"} );
331
332 /*
333 * Assign each search list to the first
334 *
335 * explicit parameter
336 * dburi field
337 * wildcard list
338 *
339 */
340 if( null == searchLanguages )
341 {
342 List dbURIList = (null == dburi) ? null : dburi.getLanguagesList() ;
343 if (null == dbURIList || dbURIList.isEmpty())
344 {
345 searchLanguages = wildcardList;
346 }
347 else
348 {
349 searchLanguages = dbURIList;
350 }
351 }
352
353 if( null == searchSchemas )
354 {
355 List dbURIList = (null == dburi) ? null : dburi.getSchemasList() ;
356 if (null == dbURIList || dbURIList.isEmpty())
357 {
358 searchSchemas = wildcardList;
359 }
360 else
361 {
362 searchSchemas = dbURIList;
363 }
364 }
365
366 if( null == searchSourceCodeTypes )
367 {
368 List dbURIList = (null == dburi) ? null : dburi.getSourceCodeTypesList() ;
369 if (null == dbURIList || dbURIList.isEmpty())
370 {
371 searchSourceCodeTypes = wildcardList;
372 }
373 else
374 {
375 searchSourceCodeTypes = dbURIList;
376 }
377 }
378
379 if( null == searchSourceCodeNames )
380 {
381 List dbURIList = (null == dburi) ? null : dburi.getSourceCodeNamesList() ;
382 if (null == dbURIList || dbURIList.isEmpty())
383 {
384 searchSourceCodeNames = wildcardList;
385 }
386 else
387 {
388 searchSourceCodeNames = dbURIList;
389 }
390 }
391
392 try
393 {
394
395 if (null != returnSourceCodeObjectsStatement)
396 {
397 LOGGER.log(Level.FINE, "Have bespoke returnSourceCodeObjectsStatement from DBURI: \"{0}\""
398 , returnSourceCodeObjectsStatement
399 );
400 PreparedStatement sourceCodeObjectsStatement = getConnection().prepareStatement(returnSourceCodeObjectsStatement);
401
402 for (String language : searchLanguages )
403 {
404 for (String schema : searchSchemas )
405 {
406 for (String sourceCodeType : searchSourceCodeTypes )
407 {
408 for (String sourceCodeName : searchSourceCodeNames )
409 {
410 sourceCodeObjectsStatement.setString(1, language );
411 sourceCodeObjectsStatement.setString(2, schema );
412 sourceCodeObjectsStatement.setString(3, sourceCodeType );
413 sourceCodeObjectsStatement.setString(4, sourceCodeName );
414 LOGGER.finer(String.format("searching for language=\"%s\", schema=\"%s\", sourceCodeType=\"%s\", sourceCodeNames=\"%s\" "
415 ,language
416 ,schema
417 ,sourceCodeType
418 ,sourceCodeName
419 )
420 );
421
422 /*
423 * public ResultSet getProcedures(String catalog
424 * , String schemaPattern
425 * , String procedureNamePattern) throws SQLException
426 */
427
428 sourceCodeObjects = sourceCodeObjectsStatement.executeQuery();
429
430 /* From Javadoc ....
431 * Each procedure description has the the following columns:
432 PROCEDURE_CAT String => procedure catalog (may be null)
433 PROCEDURE_SCHEM String => procedure schema (may be null)
434 PROCEDURE_NAME String => procedure name
435 reserved for future use
436 reserved for future use
437 reserved for future use
438 REMARKS String => explanatory comment on the procedure
439 PROCEDURE_TYPE short => kind of procedure:
440 procedureResultUnknown - Cannot determine if a return value will be returned
441 procedureNoResult - Does not return a return value
442 procedureReturnsResult - Returns a return value
443 SPECIFIC_NAME String => The name which uniquely identifies this procedure within its schema.
444 */
445 while( sourceCodeObjects.next() )
446 {
447 LOGGER.finest(String.format("Found schema=%s,object_type=%s,object_name=%s"
448 ,sourceCodeObjects.getString("PROCEDURE_SCHEM")
449 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
450 ,sourceCodeObjects.getString("PROCEDURE_NAME")
451 )
452 );
453
454
455 sourceObjectsList.add(new SourceObject(
456 sourceCodeObjects.getString("PROCEDURE_SCHEM")
457 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
458 ,sourceCodeObjects.getString("PROCEDURE_NAME")
459 ,null
460 )
461 );
462 }
463 }
464 }
465 }
466 }
467 }
468 else // Use standard DatabaseMetaData interface
469 {
470 LOGGER.fine("Have dbUri - no returnSourceCodeObjectsStatement, reverting to DatabaseMetaData.getProcedures(...)");
471
472 DatabaseMetaData metadata = connection.getMetaData() ;
473 List<String> schemasList = dburi.getSchemasList() ;
474 for (String schema : schemasList )
475 {
476 for (String sourceCodeName : dburi.getSourceCodeNamesList() )
477 {
478 /*
479 * public ResultSet getProcedures(String catalog
480 * , String schemaPattern
481 * , String procedureNamePattern) throws SQLException
482 */
483 sourceCodeObjects = metadata.getProcedures(null
484 ,schema
485 ,sourceCodeName
486 );
487 /* From Javadoc ....
488 * Each procedure description has the the following columns:
489 PROCEDURE_CAT String => procedure catalog (may be null)
490 PROCEDURE_SCHEM String => procedure schema (may be null)
491 PROCEDURE_NAME String => procedure name
492 reserved for future use
493 reserved for future use
494 reserved for future use
495 REMARKS String => explanatory comment on the procedure
496 PROCEDURE_TYPE short => kind of procedure:
497 procedureResultUnknown - Cannot determine if a return value will be returned
498 procedureNoResult - Does not return a return value
499 procedureReturnsResult - Returns a return value
500 SPECIFIC_NAME String => The name which uniquely identifies this procedure within its schema.
501
502 Oracle getProcedures actually returns these 8 columns:-
503 ResultSet "Matched Procedures" has 8 columns and contains ...
504 [PROCEDURE_CAT,PROCEDURE_SCHEM,PROCEDURE_NAME,NULL,NULL,NULL,REMARKS,PROCEDURE_TYPE
505 ,null,PHPDEMO,ADD_JOB_HISTORY,null,null,null,Standalone procedure or function,1
506 ,FETCHPERFPKG,PHPDEMO,BULKSELECTPRC,null,null,null,Packaged function,2
507 ,FETCHPERFPKG,PHPDEMO,BULKSELECTPRC,null,null,null,Packaged procedure,1
508 ,null,PHPDEMO,CITY_LIST,null,null,null,Standalone procedure or function,1
509 ,null,PHPDEMO,EDDISCOUNT,null,null,null,Standalone procedure or function,2
510 ,SELPKG_BA,PHPDEMO,EMPSELBULK,null,null,null,Packaged function,2
511 ,SELPKG_BA,PHPDEMO,EMPSELBULK,null,null,null,Packaged procedure,1
512 ,INSPKG,PHPDEMO,INSFORALL,null,null,null,Packaged procedure,1
513 ,null,PHPDEMO,MYDOFETCH,null,null,null,Standalone procedure or function,2
514 ,null,PHPDEMO,MYPROC1,null,null,null,Standalone procedure or function,1
515 ,null,PHPDEMO,MYPROC2,null,null,null,Standalone procedure or function,1
516 ,null,PHPDEMO,MYXAQUERY,null,null,null,Standalone procedure or function,1
517 ,null,PHPDEMO,POLICY_VPDPARTS,null,null,null,Standalone procedure or function,2
518 ,FETCHPERFPKG,PHPDEMO,REFCURPRC,null,null,null,Packaged procedure,1
519 ,null,PHPDEMO,SECURE_DML,null,null,null,Standalone procedure or function,1
520 ...
521 ]
522 */
523 while( sourceCodeObjects.next() )
524 {
525 LOGGER.finest(String.format("Located schema=%s,object_type=%s,object_name=%s\n"
526 ,sourceCodeObjects.getString("PROCEDURE_SCHEM")
527 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
528 ,sourceCodeObjects.getString("PROCEDURE_NAME")
529 ));
530
531
532 sourceObjectsList.add(new SourceObject(
533 sourceCodeObjects.getString("PROCEDURE_SCHEM")
534 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
535 ,sourceCodeObjects.getString("PROCEDURE_NAME")
536 ,null
537 )
538 );
539 }
540 }
541 }
542 }
543
544 LOGGER.finer(String.format("Identfied=%d sourceObjects", sourceObjectsList.size()));
545
546 return sourceObjectsList ;
547 }
548 catch (SQLException sqle)
549 {
550 throw new RuntimeException ("Problem collecting list of source code objects",sqle);
551 }
552 }
553 }