34e182f4c304332c074bdadc527e4b5df617c2bb
[sqoop.git] / src / test / org / apache / sqoop / manager / oracle / OracleConnectionFactoryTest.java
1 /**
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing, software
13 * distributed under the License is distributed on an "AS IS" BASIS,
14 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 * See the License for the specific language governing permissions and
16 * limitations under the License.
17 */
18
19 package org.apache.sqoop.manager.oracle;
20
21 import static org.junit.Assert.*;
22
23 import java.sql.Connection;
24 import java.sql.PreparedStatement;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.text.SimpleDateFormat;
28 import java.util.Date;
29 import java.util.List;
30
31 import org.apache.hadoop.conf.Configuration;
32 import org.junit.Assert;
33 import org.junit.Test;
34
35 import org.apache.sqoop.manager.oracle.util.OracleUtils;
36
37 /**
38 * Test OracleConnectionFactory class including initialization statements.
39 */
40 public class OracleConnectionFactoryTest extends OraOopTestCase {
41
42 /**
43 * This is just to expose methods in OracleConnectionFactory.
44 */
45 public class Exposer extends OracleConnectionFactory {
46
47 }
48
49 @Test
50 public void testSetJdbcFetchSize() {
51 setAndCheckJdbcFetchSize(45);
52 setAndCheckJdbcFetchSize(2000);
53 }
54
55 private void setAndCheckJdbcFetchSize(int jdbcFetchSize) {
56
57 try {
58 Connection conn = getConnection();
59
60 String uniqueJunk =
61 (new SimpleDateFormat("yyyyMMddHHmmsszzz")).format(new Date())
62 + jdbcFetchSize;
63
64 org.apache.hadoop.conf.Configuration conf = new Configuration();
65 conf.setInt(OraOopConstants.ORACLE_ROW_FETCH_SIZE, jdbcFetchSize);
66
67 // Prevent setJdbcFetchSize() from logging information about the
68 // fetch-size
69 // changing. Otherwise, the junit output will be polluted with messages
70 // about
71 // things that aren't actually a problem...
72 boolean logIsBeingCached =
73 OracleConnectionFactory.LOG.getCacheLogEntries();
74 OracleConnectionFactory.LOG.setCacheLogEntries(true);
75
76 OraOopOracleQueries.setJdbcFetchSize(conn, conf);
77
78 OracleConnectionFactory.LOG.setCacheLogEntries(logIsBeingCached);
79
80 String uniqueSql =
81 String.format("select /*%s*/ * from dba_objects", uniqueJunk);
82 // Usually dba_objects will have a lot of rows
83 ResultSet resultSet1 = conn.createStatement().executeQuery(uniqueSql);
84 while (resultSet1.next()) {
85 // Nothing to do
86 continue;
87 }
88
89 ResultSet resultSet2 =
90 conn.createStatement().executeQuery(OraOopTestConstants.SQL_TABLE);
91 boolean sqlFound = false;
92 double rowsPerFetch = 0;
93 while (resultSet2.next()) {
94 String sqlText = resultSet2.getString("SQL_TEXT");
95 if (sqlText.contains(uniqueJunk)) {
96 sqlFound = true;
97 rowsPerFetch = resultSet2.getDouble("ROWS_PER_FETCH");
98 break;
99 }
100 }
101
102 if (!sqlFound) {
103 Assert
104 .fail("Unable to find the performance metrics for the SQL "
105 + "statement being used to check the JDBC fetch size.");
106 }
107
108 if (rowsPerFetch < jdbcFetchSize * 0.95
109 || rowsPerFetch > jdbcFetchSize * 1.05) {
110 Assert
111 .fail(String
112 .format(
113 "The measured JDBC fetch size is not within 5%% of what we "
114 + "expected. Expected=%s rows/fetch, actual=%s rows/fetch",
115 jdbcFetchSize, rowsPerFetch));
116 }
117
118 } catch (SQLException ex) {
119 Assert.fail(ex.getMessage());
120 }
121 }
122
123 @Test
124 public void testCreateOracleJdbcConnectionBadUserName() {
125
126 try {
127
128 // Prevent createOracleJdbcConnection() from logging a problem with the
129 // bad username we're about to use. Otherwise, the junit output will be
130 // polluted with messages about things that aren't actually a problem...
131 boolean logIsBeingCached =
132 OracleConnectionFactory.LOG.getCacheLogEntries();
133 OracleConnectionFactory.LOG.setCacheLogEntries(true);
134
135 OracleConnectionFactory.createOracleJdbcConnection(
136 OraOopConstants.ORACLE_JDBC_DRIVER_CLASS, OracleUtils.CONNECT_STRING,
137 OracleUtils.ORACLE_INVALID_USER_NAME, OracleUtils.ORACLE_USER_PASS);
138
139 OracleConnectionFactory.LOG.setCacheLogEntries(logIsBeingCached);
140
141 Assert
142 .fail("OracleConnectionFactory should have thrown an exception in "
143 + "response to a rubbish user name.");
144
145 } catch (SQLException ex) {
146 assertEquals(ex.getErrorCode(), 1017); // <- ORA-01017 invalid
147 // username/password; logon denied.
148 }
149 }
150
151 @Test
152 public void testCreateOracleJdbcConnectionBadPassword() {
153
154 try {
155 // Prevent createOracleJdbcConnection() from logging a problem with the
156 // bad username we're about to use. Otherwise, the junit output will be
157 // polluted with messages about things that aren't actually a problem...
158 boolean logIsBeingCached =
159 OracleConnectionFactory.LOG.getCacheLogEntries();
160 OracleConnectionFactory.LOG.setCacheLogEntries(true);
161
162 OracleConnectionFactory.createOracleJdbcConnection(
163 OraOopConstants.ORACLE_JDBC_DRIVER_CLASS, OracleUtils.CONNECT_STRING,
164 OracleUtils.ORACLE_USER_NAME, "a" + OracleUtils.ORACLE_USER_PASS);
165
166 OracleConnectionFactory.LOG.setCacheLogEntries(logIsBeingCached);
167
168 Assert
169 .fail("OracleConnectionFactory should have thrown an exception in "
170 + "response to a rubbish password.");
171
172 } catch (SQLException ex) {
173 assertEquals(ex.getErrorCode(), 1017); // <- ORA-01017 invalid
174 // username/password; logon denied.
175 }
176 }
177
178 @Test
179 public void testCreateOracleJdbcConnectionOk() {
180
181 try {
182 Connection conn = getConnection();
183
184 assertEquals(
185 "The connection to the Oracle database does not appear to be valid.",
186 true, conn.isValid(15));
187
188 ResultSet resultSet =
189 conn.createStatement().executeQuery(
190 "select instance_name from v$instance");
191 if (!resultSet.next() || resultSet.getString(1).isEmpty()) {
192 Assert.fail("Got blank instance name from v$instance");
193 }
194 } catch (SQLException ex) {
195 Assert.fail(ex.getMessage());
196 }
197 }
198
199 @Test
200 public void testExecuteOraOopSessionInitializationStatements() {
201
202 // Exposer.LOG = null;
203 // protected static final Log LOG =
204 // LogFactory.getLog(OracleConnectionFactory.class.getName());
205
206 OraOopLogFactory.OraOopLog2 oraoopLog = Exposer.LOG;
207
208 oraoopLog.setCacheLogEntries(true);
209
210 // Check that the default session-initialization statements are reflected in
211 // the log...
212 oraoopLog.clearCache();
213 checkExecuteOraOopSessionInitializationStatements(null);
214 checkLogContainsText(oraoopLog,
215 "Initializing Oracle session with SQL : alter session disable "
216 + "parallel query");
217 checkLogContainsText(
218 oraoopLog,
219 "Initializing Oracle session with SQL : alter session set "
220 + "\"_serial_direct_read\"=true");
221
222 // Check that the absence of session-initialization statements is reflected
223 // in the log...
224 oraoopLog.clearCache();
225 checkExecuteOraOopSessionInitializationStatements("");
226 checkLogContainsText(oraoopLog,
227 "No Oracle 'session initialization' statements were found to execute");
228
229 // This should do nothing (i.e. not throw an exception)...
230 checkExecuteOraOopSessionInitializationStatements(";");
231
232 // This should throw an exception, as Oracle won't know what to do with
233 // this...
234 oraoopLog.clearCache();
235 checkExecuteOraOopSessionInitializationStatements("loremipsum");
236 checkLogContainsText(oraoopLog, "loremipsum");
237 checkLogContainsText(oraoopLog, "ORA-00900: invalid SQL statement");
238
239 Connection conn = getConnection();
240 try {
241
242 // Try a session-initialization statement that creates a table...
243 dropTable(conn, OracleUtils.SYSTEMTEST_TABLE_NAME);
244 checkExecuteOraOopSessionInitializationStatements("create table "
245 + OracleUtils.SYSTEMTEST_TABLE_NAME + " (col1 varchar2(1))");
246 if (!doesTableExist(conn, OracleUtils.SYSTEMTEST_TABLE_NAME)) {
247 Assert.fail("The session-initialization statement to create the table "
248 + OracleUtils.SYSTEMTEST_TABLE_NAME + " did not work.");
249 }
250
251 // Try a sequence of a few statements...
252 dropTable(conn, OracleUtils.SYSTEMTEST_TABLE_NAME);
253 checkExecuteOraOopSessionInitializationStatements("create table "
254 + OracleUtils.SYSTEMTEST_TABLE_NAME + " (col1 number);insert into "
255 + OracleUtils.SYSTEMTEST_TABLE_NAME + " values (1) ; --update "
256 + OracleUtils.SYSTEMTEST_TABLE_NAME + " set col1 = col1 + 1; update "
257 + OracleUtils.SYSTEMTEST_TABLE_NAME
258 + " set col1 = col1 + 1; commit ;;");
259
260 ResultSet resultSet =
261 conn.createStatement().executeQuery(
262 "select col1 from " + OracleUtils.SYSTEMTEST_TABLE_NAME);
263 resultSet.next();
264 int actualValue = resultSet.getInt("col1");
265 if (actualValue != 2) {
266 Assert.fail("The table " + OracleUtils.SYSTEMTEST_TABLE_NAME
267 + " does not contain the data we expected.");
268 }
269
270 dropTable(conn, OracleUtils.SYSTEMTEST_TABLE_NAME);
271
272 } catch (Exception ex) {
273 Assert.fail(ex.getMessage());
274 }
275 }
276
277 @Test
278 public void testParseOraOopSessionInitializationStatements() {
279
280 List<String> statements = null;
281
282 try {
283 statements =
284 OracleConnectionFactory
285 .parseOraOopSessionInitializationStatements(null);
286 Assert.fail("An IllegalArgumentException should have been thrown.");
287 } catch (IllegalArgumentException ex) {
288 // This is what we wanted.
289 }
290
291 org.apache.hadoop.conf.Configuration conf = new Configuration();
292
293 statements =
294 OracleConnectionFactory
295 .parseOraOopSessionInitializationStatements(conf);
296 Assert.assertTrue(statements.size() > 0);
297
298 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS, "");
299 statements =
300 OracleConnectionFactory
301 .parseOraOopSessionInitializationStatements(conf);
302 Assert.assertEquals(0, statements.size());
303
304 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS, ";");
305 statements =
306 OracleConnectionFactory
307 .parseOraOopSessionInitializationStatements(conf);
308 Assert.assertEquals(0, statements.size());
309
310 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS,
311 ";--;\t--");
312 statements =
313 OracleConnectionFactory
314 .parseOraOopSessionInitializationStatements(conf);
315 Assert.assertEquals(0, statements.size());
316
317 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS
318 , "\ta");
319 statements =
320 OracleConnectionFactory
321 .parseOraOopSessionInitializationStatements(conf);
322 Assert.assertEquals(1, statements.size());
323 if (!statements.get(0).equalsIgnoreCase("a")) {
324 Assert.fail("Expected a session initialization statement of \"a\"");
325 }
326
327 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS,
328 "a;b;--c;d;");
329 statements =
330 OracleConnectionFactory
331 .parseOraOopSessionInitializationStatements(conf);
332 Assert.assertEquals(3, statements.size());
333 if (!statements.get(0).equalsIgnoreCase("a")) {
334 Assert.fail("Expected a session initialization statement of \"a\"");
335 }
336 if (!statements.get(1).equalsIgnoreCase("b")) {
337 Assert.fail("Expected a session initialization statement of \"b\"");
338 }
339 if (!statements.get(2).equalsIgnoreCase("d")) {
340 Assert.fail("Expected a session initialization statement of \"d\"");
341 }
342
343 // Expressions without default values...
344 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS,
345 "set a={expr1};b={expr2}/{expr3};");
346 conf.set("expr1", "1");
347 conf.set("expr2", "2");
348 conf.set("expr3", "3");
349 statements =
350 OracleConnectionFactory
351 .parseOraOopSessionInitializationStatements(conf);
352 Assert.assertEquals(2, statements.size());
353 String actual = statements.get(0);
354 String expected = "set a=1";
355 if (!actual.equalsIgnoreCase(expected)) {
356 Assert.fail(String.format(
357 "Expected a session initialization statement of \"%s\", but got \"%s\"."
358 , expected, actual));
359 }
360 actual = statements.get(1);
361 expected = "b=2/3";
362 if (!actual.equalsIgnoreCase(expected)) {
363 Assert.fail(String.format(
364 "Expected a session initialization statement of \"%s\", but got \"%s\"."
365 , expected, actual));
366 }
367
368 // Expressions with default values...
369 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS,
370 "set c={expr3|66};d={expr4|15}/{expr5|90};");
371 conf.set("expr3", "20");
372 // conf.set("expr4", "21");
373 // conf.set("expr5", "23");
374 statements =
375 OracleConnectionFactory
376 .parseOraOopSessionInitializationStatements(conf);
377 Assert.assertEquals(2, statements.size());
378 actual = statements.get(0);
379 expected = "set c=20";
380 if (!actual.equalsIgnoreCase(expected)) {
381 Assert.fail(String.format(
382 "Expected a session initialization statement of \"%s\", but got \"%s\"."
383 , expected, actual));
384 }
385 actual = statements.get(1);
386 expected = "d=15/90";
387 if (!actual.equalsIgnoreCase(expected)) {
388 Assert.fail(String.format(
389 "Expected a session initialization statement of \"%s\", but got \"%s\"."
390 , expected, actual));
391 }
392
393 }
394
395 private void dropTable(Connection conn, String tableName) {
396
397 try {
398 conn.createStatement().executeQuery("drop table " + tableName);
399
400 if (doesTableExist(conn, tableName)) {
401 Assert.fail("Unable to drop the table " + tableName);
402 }
403 } catch (SQLException ex) {
404 if (ex.getErrorCode() != 942) { // <- Table or view does not exist
405 Assert.fail(ex.getMessage());
406 }
407 }
408 }
409
410 private boolean doesTableExist(Connection conn, String tableName) {
411
412 boolean result = false;
413 try {
414 List<OracleTable> tables = OraOopOracleQueries.getTables(conn);
415
416 for (int idx = 0; idx < tables.size(); idx++) {
417 if (tables.get(idx).getName().equalsIgnoreCase(tableName)) {
418 result = true;
419 break;
420 }
421 }
422 } catch (SQLException ex) {
423 Assert.fail(ex.getMessage());
424 }
425 return result;
426 }
427
428 private void checkLogContainsText(OraOopLogFactory.OraOopLog2 oraoopLog,
429 String text) {
430
431 if (!oraoopLog.getLogEntries().toLowerCase().contains(text.toLowerCase())) {
432 Assert.fail(
433 "The LOG does not contain the following text (when it should):\n\t"
434 + text);
435 }
436 }
437
438 private void checkExecuteOraOopSessionInitializationStatements(
439 String statements) {
440
441 Connection conn = getConnection();
442
443 org.apache.hadoop.conf.Configuration conf = new Configuration();
444 if (statements != null) {
445 conf.set(OraOopConstants.ORAOOP_SESSION_INITIALIZATION_STATEMENTS,
446 statements);
447 }
448
449 Exposer.executeOraOopSessionInitializationStatements(conn, conf);
450 }
451
452 @Test
453 public void testSetSessionClientInfo() {
454
455 Connection conn = getConnection();
456
457 org.apache.hadoop.conf.Configuration conf = new Configuration();
458
459 String moduleName = OraOopConstants.ORACLE_SESSION_MODULE_NAME;
460 String actionName =
461 (new SimpleDateFormat("yyyyMMddHHmmsszzz")).format(new Date());
462
463 conf.set(OraOopConstants.ORACLE_SESSION_ACTION_NAME, actionName);
464
465 try {
466 PreparedStatement statement =
467 conn.prepareStatement("select process, module, action "
468 + "from v$session " + "where module = ? and action = ?");
469 statement.setString(1, moduleName);
470 statement.setString(2, actionName);
471
472 // Check no session have this action name - because we haven't applied to
473 // our session yet...
474 ResultSet resultSet = statement.executeQuery();
475 if (resultSet.next()) {
476 Assert
477 .fail("There should be no Oracle sessions with an action name of "
478 + actionName);
479 }
480
481 // Apply this action name to our session...
482 OracleConnectionFactory.setSessionClientInfo(conn, conf);
483
484 // Now check there is a session with our action name...
485 int sessionFoundCount = 0;
486 resultSet = statement.executeQuery();
487 while (resultSet.next()) {
488 sessionFoundCount++;
489 }
490
491 if (sessionFoundCount < 1) {
492 Assert
493 .fail("Unable to locate an Oracle session with the expected module "
494 + "and action.");
495 }
496
497 if (sessionFoundCount > 1) {
498 Assert
499 .fail("Multiple sessions were found with the expected module and "
500 + "action - we only expected to find one.");
501 }
502 } catch (SQLException ex) {
503 Assert.fail(ex.getMessage());
504 }
505
506 }
507
508 private Connection getConnection() {
509
510 try {
511 return OracleConnectionFactory.createOracleJdbcConnection(
512 OraOopConstants.ORACLE_JDBC_DRIVER_CLASS, OracleUtils.CONNECT_STRING,
513 OracleUtils.ORACLE_USER_NAME, OracleUtils.ORACLE_USER_PASS);
514 } catch (SQLException ex) {
515 Assert.fail(ex.getMessage());
516 }
517 return null;
518 }
519
520 }