View Javadoc

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 }