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     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<>();
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("%");
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<String> 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<String> 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<String> 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<String> 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 }