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.*;
19
20
21
22
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
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
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
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
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
126 while (resultSet.next())
127 {
128
129
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
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
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
171
172 }
173
174
175
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
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
192
193 }
194
195
196
197
198 public void testGetSourceCode() throws Exception {
199 System.out.println("getSourceCode");
200
201
202
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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231 String resultString = getStringFromReader ( result );
232
233 System.out.println("getSourceCode()==\""+resultString );
234
235
236 }
237
238
239
240
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
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
270
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
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
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 }