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