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 String urlString = init(dbURI);
121
122 Properties mergedProperties = dbURI.getDbType().getProperties() ;
123 Map<String,String> dbURIParameters = dbURI.getParameters();
124 mergedProperties.putAll(dbURIParameters) ;
125 mergedProperties.put("user", user) ;
126 mergedProperties.put("password", password) ;
127
128 connection = DriverManager.getConnection(urlString, mergedProperties );
129 if (LOGGER.isLoggable(Level.FINE)) {
130 LOGGER.fine("we have a connection="+connection);
131 }
132 }
133
134
135 /**
136 * Define database connection and source code to retrieve with database properties.
137 *
138 * @param properties database settings such as database username, password
139 * @param dbURI {@link DBURI } containing JDBC connection plus parameters to specify source code.
140 * @throws SQLException on failing to create JDBC connection
141 * @throws MalformedURLException on attempting to connect with malformed JDBC URL
142 * @throws ClassNotFoundException on failing to locate the JDBC driver class.
143 */
144 public DBMSMetadata(Properties properties, DBURI dbURI) throws SQLException, MalformedURLException, ClassNotFoundException
145 {
146 String urlString = init(dbURI);
147
148 Properties mergedProperties = dbURI.getDbType().getProperties();
149 Map<String,String> dbURIParameters = dbURI.getParameters();
150 mergedProperties.putAll(dbURIParameters) ;
151 mergedProperties.putAll(properties) ;
152
153 if (LOGGER.isLoggable(Level.FINE)) {
154 LOGGER.fine("Retrieving connection for urlString"+urlString);
155 }
156 connection = DriverManager.getConnection(urlString ,mergedProperties);
157 if (LOGGER.isLoggable(Level.FINE)) {
158 LOGGER.fine("Secured Connection for DBURI"+dbURI);
159 }
160 }
161
162 /**
163 * Define database connection and source code to retrieve.
164 *
165 * <p>This constructor is reliant on database username and password embedded in the JDBC URL
166 * or defaulted from the {@link DBURI}'s {@link DriverType}.
167 *
168 * @param user Database username
169 * @param password Database password
170 * @param dbURI {@link DBURI } containing JDBC connection plus parameters to specify source code.
171 * @throws SQLException on failing to create JDBC connection
172 * @throws ClassNotFoundException on failing to locate the JDBC driver class.
173 */
174 public DBMSMetadata(DBURI dbURI) throws SQLException, ClassNotFoundException
175 {
176 String urlString = init(dbURI);
177
178 Properties dbURIProperties = dbURI.getDbType().getProperties();
179 Map<String,String> dbURIParameters = dbURI.getParameters();
180
181 /*Overwrite any DBType properties with DBURI parameters
182 * allowing JDBC connection properties to be inherited from DBType
183 * or passed as DBURI parameters
184 */
185 dbURIProperties.putAll(dbURIParameters) ;
186
187 connection = DriverManager.getConnection(urlString, dbURIProperties);
188 }
189
190 private String init(DBURI dbURI) throws ClassNotFoundException {
191 this.dburi = dbURI;
192 this.returnSourceCodeObjectsStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_OBJECTS_STATEMENT);
193 this.returnSourceCodeStatement = dbURI.getDbType().getProperties().getProperty(GET_SOURCE_CODE_STATEMENT);
194 this.returnType = dbURI.getSourceCodeType();
195 if (LOGGER.isLoggable(Level.FINE)) {
196 LOGGER.fine("returnSourceCodeStatement="+returnSourceCodeStatement +", returnType="+returnType);
197 }
198
199 String driverClass = dbURI.getDriverClass();
200 String urlString = dbURI.getURL().toString();
201 if (LOGGER.isLoggable(Level.FINE)) {
202 LOGGER.fine("driverClass="+driverClass+", urlString="+urlString);
203 }
204 Class.forName(driverClass);
205 if (LOGGER.isLoggable(Level.FINE)) {
206 LOGGER.fine("Located class for driverClass="+driverClass);
207 }
208 return urlString;
209 }
210
211 /**
212 * Return source code text from the database.
213 *
214 * @param source object
215 * @return source code
216 * @throws SQLException
217 */
218 public java.io.Reader getSourceCode (SourceObject sourceObject )
219 throws SQLException
220 {
221 return getSourceCode(
222 sourceObject.getType(), sourceObject.getName(), sourceObject.getSchema());
223
224 }
225
226 /**
227 * return source code text
228 *
229 * @param objectType
230 * @param name Source Code name
231 * @param schema Owner of the code
232 * @return Source code text.
233 * @throws SQLException on failing to retrieve the source Code text
234 */
235 public java.io.Reader getSourceCode (
236 String objectType,
237 String name,
238 String schema
239 )
240 throws SQLException
241 {
242 Object result;
243
244 /* Only define callableStatement once and reuse it for subsequent calls to getSourceCode()*/
245 if (null == callableStatement)
246 {
247 if (LOGGER.isLoggable(Level.FINEST)) {
248 LOGGER.finest("getSourceCode: returnSourceCodeStatement=\""+returnSourceCodeStatement+"\"");
249 LOGGER.finest("getSourceCode: returnType=\""+returnType+"\"");
250 }
251 callableStatement = getConnection().prepareCall(returnSourceCodeStatement);
252 callableStatement.registerOutParameter(1, returnType);
253 }
254
255 // set IN parameters
256 callableStatement.setString(2, objectType);
257 callableStatement.setString(3, name);
258 callableStatement.setString(4, schema);
259 //
260 // execute statement
261 callableStatement.executeUpdate();
262 // retrieve OUT parameters
263 result = callableStatement.getObject(1);
264
265 return (java.sql.Types.CLOB == returnType)
266 ? ((Clob) result).getCharacterStream()
267 : new java.io.StringReader( result.toString() )
268 ;
269 }
270
271 /**
272 * Return all source code objects associated with any associated DBURI.
273 * @return
274 */
275 public List<SourceObject> getSourceObjectList ()
276 {
277
278 if (null == dburi)
279 {
280 LOGGER.warning("No dbUri defined - no further action possible");
281 return null;
282 }
283 else
284 {
285 return getSourceObjectList(
286 dburi.getLanguagesList()
287 ,dburi.getSchemasList()
288 ,dburi.getSourceCodeTypesList()
289 ,dburi.getSourceCodeNamesList()
290 );
291 }
292
293 }
294
295 /**
296 * Return all source code objects associated with the specified languages, schemas, source code types and source code names.
297 *
298 * <p>Each parameter may be null and the appropriate field from any related DBURI is assigned, defaulting to the normal SQL wildcard expression ("%").
299 * </.>
300 * @param languages Optional list of languages to search for
301 * @param schemas Optional list of schemas to search for
302 * @param sourceCodeTypes Optional list of source code types to search for
303 * @param sourceCodeNames Optional list of source code names to search for
304 */
305 public List<SourceObject> getSourceObjectList (List<String> languages, List<String> schemas, List<String> sourceCodeTypes, List<String> sourceCodeNames )
306 {
307
308 ResultSet sourceCodeObjects = null ;
309 List<SourceObject> sourceObjectsList = new ArrayList<SourceObject>() ;
310
311 List<String> searchLanguages = languages ;
312 List<String> searchSchemas = schemas ;
313 List<String> searchSourceCodeTypes = sourceCodeTypes ;
314 List<String> searchSourceCodeNames = sourceCodeNames ;
315 List<String> wildcardList = Arrays.asList(new String[] {"%"} );
316
317 /*
318 * Assign each search list to the first
319 *
320 * explicit parameter
321 * dburi field
322 * wildcard list
323 *
324 */
325 if( null == searchLanguages )
326 {
327 List dbURIList = (null == dburi) ? null : dburi.getLanguagesList() ;
328 if (null == dbURIList || dbURIList.isEmpty())
329 {
330 searchLanguages = wildcardList;
331 }
332 else
333 {
334 searchLanguages = dbURIList;
335 }
336 }
337
338 if( null == searchSchemas )
339 {
340 List dbURIList = (null == dburi) ? null : dburi.getSchemasList() ;
341 if (null == dbURIList || dbURIList.isEmpty())
342 {
343 searchSchemas = wildcardList;
344 }
345 else
346 {
347 searchSchemas = dbURIList;
348 }
349 }
350
351 if( null == searchSourceCodeTypes )
352 {
353 List dbURIList = (null == dburi) ? null : dburi.getSourceCodeTypesList() ;
354 if (null == dbURIList || dbURIList.isEmpty())
355 {
356 searchSourceCodeTypes = wildcardList;
357 }
358 else
359 {
360 searchSourceCodeTypes = dbURIList;
361 }
362 }
363
364 if( null == searchSourceCodeNames )
365 {
366 List dbURIList = (null == dburi) ? null : dburi.getSourceCodeNamesList() ;
367 if (null == dbURIList || dbURIList.isEmpty())
368 {
369 searchSourceCodeNames = wildcardList;
370 }
371 else
372 {
373 searchSourceCodeNames = dbURIList;
374 }
375 }
376
377 try
378 {
379
380 if (null != returnSourceCodeObjectsStatement)
381 {
382 LOGGER.log(Level.FINE, "Have bespoke returnSourceCodeObjectsStatement from DBURI: \"{0}\""
383 , returnSourceCodeObjectsStatement
384 );
385 PreparedStatement sourceCodeObjectsStatement = getConnection().prepareStatement(returnSourceCodeObjectsStatement);
386
387 for (String language : searchLanguages )
388 {
389 for (String schema : searchSchemas )
390 {
391 for (String sourceCodeType : searchSourceCodeTypes )
392 {
393 for (String sourceCodeName : searchSourceCodeNames )
394 {
395 sourceCodeObjectsStatement.setString(1, language );
396 sourceCodeObjectsStatement.setString(2, schema );
397 sourceCodeObjectsStatement.setString(3, sourceCodeType );
398 sourceCodeObjectsStatement.setString(4, sourceCodeName );
399 LOGGER.finer(String.format("searching for language=\"%s\", schema=\"%s\", sourceCodeType=\"%s\", sourceCodeNames=\"%s\" "
400 ,language
401 ,schema
402 ,sourceCodeType
403 ,sourceCodeName
404 )
405 );
406
407 /*
408 * public ResultSet getProcedures(String catalog
409 * , String schemaPattern
410 * , String procedureNamePattern) throws SQLException
411 */
412
413 sourceCodeObjects = sourceCodeObjectsStatement.executeQuery();
414
415 /* From Javadoc ....
416 * Each procedure description has the the following columns:
417 PROCEDURE_CAT String => procedure catalog (may be null)
418 PROCEDURE_SCHEM String => procedure schema (may be null)
419 PROCEDURE_NAME String => procedure name
420 reserved for future use
421 reserved for future use
422 reserved for future use
423 REMARKS String => explanatory comment on the procedure
424 PROCEDURE_TYPE short => kind of procedure:
425 procedureResultUnknown - Cannot determine if a return value will be returned
426 procedureNoResult - Does not return a return value
427 procedureReturnsResult - Returns a return value
428 SPECIFIC_NAME String => The name which uniquely identifies this procedure within its schema.
429 */
430 while( sourceCodeObjects.next() )
431 {
432 LOGGER.finest(String.format("Found schema=%s,object_type=%s,object_name=%s"
433 ,sourceCodeObjects.getString("PROCEDURE_SCHEM")
434 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
435 ,sourceCodeObjects.getString("PROCEDURE_NAME")
436 )
437 );
438
439
440 sourceObjectsList.add(new SourceObject(
441 sourceCodeObjects.getString("PROCEDURE_SCHEM")
442 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
443 ,sourceCodeObjects.getString("PROCEDURE_NAME")
444 ,null
445 )
446 );
447 }
448 }
449 }
450 }
451 }
452 }
453 else // Use standard DatabaseMetaData interface
454 {
455 LOGGER.fine("Have dbUri - no returnSourceCodeObjectsStatement, reverting to DatabaseMetaData.getProcedures(...)");
456
457 DatabaseMetaData metadata = connection.getMetaData() ;
458 List<String> schemasList = dburi.getSchemasList() ;
459 for (String schema : schemasList )
460 {
461 for (String sourceCodeName : dburi.getSourceCodeNamesList() )
462 {
463 /*
464 * public ResultSet getProcedures(String catalog
465 * , String schemaPattern
466 * , String procedureNamePattern) throws SQLException
467 */
468 sourceCodeObjects = metadata.getProcedures(null
469 ,schema
470 ,sourceCodeName
471 );
472 /* From Javadoc ....
473 * Each procedure description has the the following columns:
474 PROCEDURE_CAT String => procedure catalog (may be null)
475 PROCEDURE_SCHEM String => procedure schema (may be null)
476 PROCEDURE_NAME String => procedure name
477 reserved for future use
478 reserved for future use
479 reserved for future use
480 REMARKS String => explanatory comment on the procedure
481 PROCEDURE_TYPE short => kind of procedure:
482 procedureResultUnknown - Cannot determine if a return value will be returned
483 procedureNoResult - Does not return a return value
484 procedureReturnsResult - Returns a return value
485 SPECIFIC_NAME String => The name which uniquely identifies this procedure within its schema.
486
487 Oracle getProcedures actually returns these 8 columns:-
488 ResultSet "Matched Procedures" has 8 columns and contains ...
489 [PROCEDURE_CAT,PROCEDURE_SCHEM,PROCEDURE_NAME,NULL,NULL,NULL,REMARKS,PROCEDURE_TYPE
490 ,null,PHPDEMO,ADD_JOB_HISTORY,null,null,null,Standalone procedure or function,1
491 ,FETCHPERFPKG,PHPDEMO,BULKSELECTPRC,null,null,null,Packaged function,2
492 ,FETCHPERFPKG,PHPDEMO,BULKSELECTPRC,null,null,null,Packaged procedure,1
493 ,null,PHPDEMO,CITY_LIST,null,null,null,Standalone procedure or function,1
494 ,null,PHPDEMO,EDDISCOUNT,null,null,null,Standalone procedure or function,2
495 ,SELPKG_BA,PHPDEMO,EMPSELBULK,null,null,null,Packaged function,2
496 ,SELPKG_BA,PHPDEMO,EMPSELBULK,null,null,null,Packaged procedure,1
497 ,INSPKG,PHPDEMO,INSFORALL,null,null,null,Packaged procedure,1
498 ,null,PHPDEMO,MYDOFETCH,null,null,null,Standalone procedure or function,2
499 ,null,PHPDEMO,MYPROC1,null,null,null,Standalone procedure or function,1
500 ,null,PHPDEMO,MYPROC2,null,null,null,Standalone procedure or function,1
501 ,null,PHPDEMO,MYXAQUERY,null,null,null,Standalone procedure or function,1
502 ,null,PHPDEMO,POLICY_VPDPARTS,null,null,null,Standalone procedure or function,2
503 ,FETCHPERFPKG,PHPDEMO,REFCURPRC,null,null,null,Packaged procedure,1
504 ,null,PHPDEMO,SECURE_DML,null,null,null,Standalone procedure or function,1
505 ...
506 ]
507 */
508 while( sourceCodeObjects.next() )
509 {
510 LOGGER.finest(String.format("Located schema=%s,object_type=%s,object_name=%s\n"
511 ,sourceCodeObjects.getString("PROCEDURE_SCHEM")
512 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
513 ,sourceCodeObjects.getString("PROCEDURE_NAME")
514 ));
515
516
517 sourceObjectsList.add(new SourceObject(
518 sourceCodeObjects.getString("PROCEDURE_SCHEM")
519 ,sourceCodeObjects.getString("PROCEDURE_TYPE")
520 ,sourceCodeObjects.getString("PROCEDURE_NAME")
521 ,null
522 )
523 );
524 }
525 }
526 }
527 }
528
529 LOGGER.finer(String.format("Identfied=%d sourceObjects", sourceObjectsList.size()));
530
531 return sourceObjectsList ;
532 }
533 catch (SQLException sqle)
534 {
535 throw new RuntimeException ("Problem collecting list of source code objects",sqle);
536 }
537 }
538 }