View Javadoc

1   package net.sourceforge.pmd.util.database;
2   
3   import java.io.IOException;
4   import java.io.Reader;
5   import java.net.URISyntaxException;
6   import java.sql.Connection;
7   import java.sql.DriverManager;
8   import java.sql.ResultSet;
9   import java.sql.ResultSetMetaData;
10  import java.sql.SQLException;
11  import java.util.List;
12  import java.util.Properties;
13  import java.util.logging.Level;
14  import java.util.logging.Logger;
15  import junit.framework.Test;
16  import junit.framework.TestCase;
17  import junit.framework.TestSuite;
18  import org.junit.*; // import annotations
19  
20  /**
21   *
22   * @author sturton
23   */
24  @Ignore
25  public class DBMSMetadataTest extends TestCase {
26  
27    final static String C_ORACLE_THIN_1 = "jdbc:oracle:thin:scott/tiger@//192.168.100.21:5521/customer_db?characterset=utf8&schemas=scott,hr,sh,system&objectTypes=procedures,functions,triggers,package,types&languages=plsql,java&name=PKG_%25%7C%7CPRC_%25"; 
28  
29    final static String C_ORACLE_THIN_3 = "jdbc:oracle:thin:scott/oracle@//192.168.100.21:1521/orcl?characterset=utf8&schemas=scott,hr,sh,system&objectTypes=procedures,functions,triggers,package,types&languages=plsql,java&name=PKG_%25%7C%7CPRC_%25"; 
30  
31    final static String C_ORACLE_THIN_4 = "jdbc:oracle:thin:system/oracle@//192.168.100.21:1521/ORCL?characterset=utf8&schemas=scott,hr,sh,system&objectTypes=procedures,functions,triggers,package,types&languages=plsql,java&name=PKG_%25%7C%7CPRC_%25"; 
32  
33    final static String C_ORACLE_THIN_5 = "jdbc:oracle:thin:@//192.168.100.21:1521/ORCL?characterset=utf8&schemas=scott,hr,sh,system&objectTypes=procedures,functions,triggers,package,types&languages=plsql,java&name=PKG_%25%7C%7CPRC_%25&user=system&password=oracle"; 
34  
35     /**
36     * URI with minimum information, relying on defaults in testdefaults.properties
37     */
38    final static String C_TEST_DEFAULTS = "jdbc:oracle:testdefault://192.168.100.21:1521/ORCL";
39  
40  
41    private DBURI dbURI;
42    private DBURI dbURI4;
43    private DBURI dbURI5;
44    private DBURI dbURIDefault;
45  
46    public DBMSMetadataTest(String testName) throws URISyntaxException, Exception {
47      super(testName);
48      dbURI = new DBURI (C_ORACLE_THIN_3);
49      dbURI4 = new DBURI (C_ORACLE_THIN_4);
50      dbURI5 = new DBURI (C_ORACLE_THIN_5);
51      dbURIDefault = new DBURI (C_TEST_DEFAULTS);
52    }
53  
54    public static Test suite() {
55      TestSuite suite = new TestSuite(DBMSMetadataTest.class);
56      return suite;
57    }
58    
59    @Override
60    protected void setUp() throws Exception {
61      super.setUp();
62    }
63    
64    @Override
65    protected void tearDown() throws Exception {
66      super.tearDown();
67    }
68  
69     /*
70      * Convert Readers to Strings for eay output and comparison. 
71      */
72  
73    private static String getStringFromReader (Reader reader) throws IOException
74    {
75  
76      StringBuilder stringBuilder = new StringBuilder(1024);
77      char[] charArray = new char[1024];
78      int readChars ; 
79      while(( readChars = reader.read(charArray)) > 0 )
80      {
81        System.out.println("Reader.read(CharArray)=="+readChars);
82        stringBuilder.append(charArray, 0, readChars);
83      }
84      reader.close();
85      
86      return stringBuilder.toString() ;
87      
88    }
89  
90  
91     /*
92      * Dump ResultSet 
93      */
94  
95    private static void dumpResultSet (ResultSet resultSet, String description) 
96    {
97  
98      try {
99  
100       ResultSetMetaData metaData = resultSet.getMetaData();
101       
102       int columnCount = metaData.getColumnCount() ;
103 
104       System.out.format("ResultSet \"%s\" has %d columns and contains ...\n["
105                         , description 
106                         , columnCount 
107                         );
108 
109       /*
110        * Walk through the column names, writing out a header line
111        */
112       for (int columnNumber = 1 
113           ; columnNumber <= columnCount 
114           ; columnNumber++
115           )
116       {
117         System.out.format("%s%s" 
118                           , ((columnNumber > 1) ?  "," : "" )  
119                           , metaData.getColumnName(columnNumber) 
120                          );
121       }
122       System.out.format("\n"); 
123 
124 
125       // Output each row 
126       while (resultSet.next())
127       {
128         /*
129          * Walk through the columns of this row, writing out a row line
130          */
131         for (int columnNumber = 1 
132             ; columnNumber <= columnCount 
133             ; columnNumber++
134             )
135         {
136           System.out.format("%s%s" 
137                             , ((columnNumber > 0) ?  "," : "" )  
138                             , resultSet.getString(columnNumber) 
139                            );
140         }
141         System.out.format("\n"); 
142 
143       }
144 
145     } catch (SQLException ex) {
146       Logger.getLogger(DBMSMetadataTest.class.getName()).log(Level.SEVERE, null, ex);
147     }
148     System.out.format("...\n]\n", description );
149   }
150 
151 
152   /**
153    * Verify getConnection method, of class DBMSMetadata.
154    */
155   public void testGetConnection() throws Exception {
156     System.out.println("getConnection");
157     String driverClass = dbURI.getDriverClass();
158     System.out.println("driverClass=="+driverClass);
159     System.out.println("URL=="+dbURI.getURL());
160     Class.forName(driverClass);
161     Object object = DriverManager.getDriver(dbURI.getURL()) ;
162     //Object object = DriverManager.getDriver(C_ORACLE_OCI_3) ;
163     Properties properties = new Properties();
164     properties.put("user","system");
165     properties.put("password","oracle");
166     Connection expResult = DriverManager.getDriver(dbURI.getURL()).connect(dbURI.getURL(), properties);
167     DBMSMetadata instance = new DBMSMetadata(dbURI );
168     Connection result = instance.getConnection();
169     assertNotNull(result);
170     // TODO review the generated test code and remove the default call to fail.
171     //fail("The test case is a prototype.");
172   }
173 
174   /**
175    * Verify getConnection method, of class DBMSMetadata.
176    */
177   public void testGetConnectionWithConnectionParameters() throws Exception {
178     System.out.println("getConnection");
179     String driverClass = dbURI5.getDriverClass();
180     System.out.println("driverClass=="+driverClass);
181     System.out.println("URL=="+dbURI5.getURL());
182     Class.forName(driverClass);
183     Object object = DriverManager.getDriver(dbURI5.getURL()) ;
184     //Object object = DriverManager.getDriver(C_ORACLE_OCI_3) ;
185     Properties properties = new Properties();
186     properties.putAll(dbURI5.getParameters());
187     Connection expResult = DriverManager.getDriver(dbURI5.getURL()).connect(dbURI5.getURL(), properties);
188     DBMSMetadata instance = new DBMSMetadata(dbURI5 );
189     Connection result = instance.getConnection();
190     assertNotNull(result);
191     // TODO review the generated test code and remove the default call to fail.
192     //fail("The test case is a prototype.");
193   }
194 
195   /**
196    * Test of getSourceCode method, of class DBMSMetadata.
197    */
198   public void testGetSourceCode() throws Exception {
199     System.out.println("getSourceCode");
200     //String objectType = "PACKAGE";
201     //String name = "DBMS_REPCAT_AUTH";
202     //String schema = "SYSTEM";
203     String objectType = "TABLE";
204     String name = "EMP";
205     String schema = "SCOTT";
206     System.out.println("dbURI.driverClass=="+dbURI.getDriverClass());
207     System.out.println("dbURI.URL=="+dbURI.getURL());
208     System.out.println("dbURI.getDBType.getProperties()=="+dbURI.getDbType().getProperties() );
209     System.out.println("dbURI.getDBType.getSourceCodeReturnType()=="+dbURI.getDbType().getSourceCodeReturnType() );
210     System.out.println("dbURI.getDBType.getProperties()=="+dbURI.getDbType().getProperties().getProperty("getSourceCodeStatement") );
211     DBMSMetadata instance = new DBMSMetadata(dbURI );
212     Reader expResult = null;
213     Reader result = instance.getSourceCode(objectType, name, schema);
214 
215     /*
216     StringBuilder stringBuilder = new StringBuilder(1024);
217     char[] charArray = new char[1024];
218     int readChars = 0; 
219     while(( readChars = result.read(charArray)) > 0 )
220     {
221       System.out.println("Reader.read(CharArray)=="+readChars);
222       stringBuilder.append(charArray, 0, readChars);
223     }
224     result.close();
225     
226     System.out.println("getSourceCode()==\""+stringBuilder.toString()+"\"");
227     
228     assertTrue(stringBuilder.toString().startsWith("\n  CREATE "));
229     */
230 
231     String resultString = getStringFromReader ( result ); 
232 
233     System.out.println("getSourceCode()==\""+resultString );
234     // TODO review the generated test code and remove the default call to fail.
235     //fail("The test case is a prototype.");
236   }
237 
238   
239   /**
240    * Verify getSchemas method, of class DBMSMetadata.
241    */
242   public void testGetSchemas() throws Exception {
243     System.out.println("getSchemas");
244     DBURI testURI = dbURI4;
245     String driverClass = testURI.getDriverClass();
246     System.out.println("driverClass=="+driverClass);
247     System.out.println("URL=="+testURI.getURL());
248     Class.forName(driverClass);
249     Object object = DriverManager.getDriver(testURI.getURL()) ;
250     //Object object = DriverManager.getDriver(C_ORACLE_OCI_3) ;
251     Properties properties = new Properties();
252     properties.put("user","system");
253     properties.put("password","oracle");
254     Connection expResult = DriverManager.getDriver(testURI.getURL()).connect(testURI.getURL(), properties);
255     DBMSMetadata instance = new DBMSMetadata(testURI );
256     Connection result = instance.getConnection();
257     assertNotNull(result);
258 
259     ResultSet allSchemas = result.getMetaData().getSchemas() ;
260     dumpResultSet (allSchemas, "All Schemas") ;
261 
262     ResultSet allCatalogues = result.getMetaData().getCatalogs() ;
263     dumpResultSet (allCatalogues, "All Catalogues") ;
264 
265     String catalog = null; 
266     String schemasPattern = "PHPDEMO" ; 
267     String tablesPattern = null  ; 
268     String proceduresPattern = null ; 
269     // Not until Java6 ResultSet matchedSchemas = result.getMetaData().getSchemas(catalog, schemasPattern) ;
270     // Not until Java6 dumpResultSet (matchedSchemas, "Matched Schemas") ;
271     ResultSet matchedTables = result.getMetaData().getTables(catalog, schemasPattern, tablesPattern, null) ;
272     dumpResultSet (matchedTables, "Matched Tables") ;
273 
274     ResultSet matchedProcedures = result.getMetaData().getProcedures(catalog, schemasPattern, proceduresPattern ) ;
275     dumpResultSet (matchedProcedures, "Matched Procedures") ;
276 
277     System.out.format("testURI=%s,\ngetParameters()=%s\n",C_ORACLE_THIN_4
278                       , testURI.getParameters() 
279                      );
280 
281     System.out.format("testURI=%s,\ngetSchemasList()=%s\n,getSourceCodeTypesList()=%s\n,getSourceCodeNmesList()=%s\n",testURI
282                       , testURI.getSchemasList()
283                       , testURI.getSourceCodeTypesList()
284                       , testURI.getSourceCodeNamesList()
285                      );
286 
287   }
288 
289   
290   /**
291    * Verify getSchemas method, of class DBMSMetadata.
292    */
293   public void testGetSourceObjectList() throws Exception {
294     System.out.println("getConnection");
295     DBURI testURI = dbURI4;
296     String driverClass = testURI.getDriverClass();
297     System.out.println("driverClass=="+driverClass);
298     System.out.println("URL=="+testURI.getURL());
299     Class.forName(driverClass);
300     Object object = DriverManager.getDriver(testURI.getURL()) ;
301     //Object object = DriverManager.getDriver(C_ORACLE_OCI_3) ;
302     Properties properties = new Properties();
303     properties.put("user","system");
304     properties.put("password","oracle");
305     Connection expResult = DriverManager.getDriver(testURI.getURL()).connect(testURI.getURL(), properties);
306     DBMSMetadata instance = new DBMSMetadata(testURI );
307     Connection result = instance.getConnection();
308     assertNotNull(result);
309 
310     List<SourceObject> sourceObjectList = instance.getSourceObjectList();
311     assertNotNull(sourceObjectList);
312 
313     System.out.format("testURI=%s,\ngetParameters()=%s\n",C_ORACLE_THIN_4
314                       , testURI.getParameters() 
315                      );
316 
317     System.out.format("testURI=%s,\ngetSchemasList()=%s\n,getSourceCodeTypesList()=%s\n,getSourceCodeNmesList()=%s\n",testURI
318                       , testURI.getSchemasList()
319                       , testURI.getSourceCodeTypesList()
320                       , testURI.getSourceCodeNamesList()
321                      );
322 
323     System.out.printf("sourceObjectList ...\n" );
324     for (SourceObject sourceObject : sourceObjectList)
325     {
326       System.out.printf("sourceObject=%s\n", sourceObject);
327       System.out.printf("sourceCode=[%s]\n", getStringFromReader(instance.getSourceCode(sourceObject)) );
328     }
329   }
330 
331 }