1
2
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
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
40
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
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
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
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
96 while (resultSet.next()) {
97
98
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
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
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
133
134
135 }
136
137
138
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
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
156
157
158 }
159
160
161
162
163 @Test
164 public void testGetSourceCode() throws Exception {
165 System.out.println("getSourceCode");
166
167
168
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
184
185
186
187
188
189
190
191
192
193
194
195 String resultString = getStringFromReader(result);
196
197 System.out.println("getSourceCode()==\"" + resultString);
198
199
200
201 }
202
203
204
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
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
235
236
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
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
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 }