1f67c469787a2804a0887e701f57e337cea40473
[sqoop.git] / src / test / org / apache / sqoop / manager / oracle / OraOopTypesTest.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.assertEquals;
22
23 import java.sql.Connection;
24 import java.sql.ResultSet;
25 import java.sql.Statement;
26
27 import org.junit.Test;
28
29 public class OraOopTypesTest extends OraOopTestCase {
30 @Test
31 public void ensureTypesAfterExportMappedAsExpected() throws Exception {
32 try {
33 setSqoopTargetDirectory(getSqoopTargetDirectory() + "types_test");
34 setSqoopGenClassName(OraOopTypesTest.class.getSimpleName());
35 String tempTableName = "ORACLE_DATATYPES_TEMPLATE";
36 String tableName = "ORACLE_DATATYPES";
37 createTableFromSQL("create table " + tempTableName + " ("
38 + "C1_NUM NUMBER(*,0),"
39 + "C2_NUM NUMBER(*,5),"
40 + "C3_NUM NUMBER(16,8),"
41 + "C4_NUM NUMBER(9,-3),"
42 + "C5_FLOAT BINARY_FLOAT,"
43 + "C6_DOUBLE BINARY_DOUBLE,"
44 + "C7_DATE DATE,"
45 + "C8_TIMESTAMP TIMESTAMP,"
46 + "C9_TIMESTAMP_WITH_TZ TIMESTAMP WITH TIME ZONE,"
47 + "C10_TIMESTAMP_WITH_LTZ TIMESTAMP WITH LOCAL TIME ZONE,"
48 + "C11_CHAR CHAR(255),"
49 + "C12_VARCHAR VARCHAR(255),"
50 + "C13_VARCHAR2 VARCHAR2(255),"
51 + "C14_NCHAR NCHAR(255),"
52 + "C15_NVARCHAR2 NVARCHAR2(255),"
53 + "C16_URITYPE UriType"
54 + ")", tempTableName);
55
56 Connection conn = getTestEnvConnection();
57 try (Statement stmt = conn.createStatement()) {
58 stmt.execute("insert into " + tempTableName + " values ("
59 + "123456789101112.123456789101112,"
60 + "123456789101112.123456789101112,"
61 + "12345678.12345678,"
62 + "123456789101.123456789101112,"
63 + "123456789101112.123456789101112,"
64 + "123456789101112.123456789101112,"
65 + "DATE '2015-02-23',"
66 + "TIMESTAMP '2015-02-23 13:42:24.123456 -07:00',"
67 + "TIMESTAMP '2015-02-23 13:42:24.123456 -08:00',"
68 + "TIMESTAMP '2015-02-23 13:42:24.123456 -09:00',"
69 + "'ÁRÍZTŰRŐTÜKÖRFÚRÓGÉP',"
70 + "'ÁRÍZTŰRŐTÜKÖRFÚRÓGÉP',"
71 + "'ÁRÍZTŰRŐTÜKÖRFÚRÓGÉP',"
72 + "'ÁRÍZTŰRŐTÜKÖRFÚRÓGÉP',"
73 + "'ÁRÍZTŰRŐTÜKÖRFÚRÓGÉP',"
74 + "httpuritype.createuri('http://www.oracle.com'))");
75 }
76 conn.commit();
77 runImport(tempTableName, getSqoopConf(), false);
78 runExportFromTemplateTable(tempTableName, tableName, true);
79 try (Statement stmt = conn.createStatement()) {
80 ResultSet rs = stmt.executeQuery(
81 "select count(*) from ("
82 + "select * from (select "
83 + "T1.C1_NUM, "
84 + "T1.C2_NUM, "
85 + "T1.C3_NUM, "
86 + "T1.C4_NUM, "
87 + "T1.C5_FLOAT, "
88 + "T1.C6_DOUBLE, "
89 + "T1.C7_DATE, "
90 + "T1.C8_TIMESTAMP, "
91 + "T1.C9_TIMESTAMP_WITH_TZ, "
92 + "T1.C10_TIMESTAMP_WITH_LTZ, "
93 + "T1.C11_CHAR, "
94 + "T1.C12_VARCHAR, "
95 + "T1.C13_VARCHAR2, "
96 + "T1.C14_NCHAR, "
97 + "T1.C15_NVARCHAR2, "
98 + "T1.C16_URITYPE.GETURL() from "
99 + tempTableName
100 + " T1 "
101 + "minus select "
102 + "T2.C1_NUM, "
103 + "T2.C2_NUM, "
104 + "T2.C3_NUM, "
105 + "T2.C4_NUM, "
106 + "T2.C5_FLOAT, "
107 + "T2.C6_DOUBLE, "
108 + "T2.C7_DATE, "
109 + "T2.C8_TIMESTAMP, "
110 + "T2.C9_TIMESTAMP_WITH_TZ, "
111 + "T2.C10_TIMESTAMP_WITH_LTZ, "
112 + "T2.C11_CHAR, "
113 + "T2.C12_VARCHAR, "
114 + "T2.C13_VARCHAR2, "
115 + "T2.C14_NCHAR, "
116 + "T2.C15_NVARCHAR2, "
117 + "T2.C16_URITYPE.GETURL() from "+tableName+" T2) "
118 + "union all select * from (select "
119 + "T1.C1_NUM, "
120 + "T1.C2_NUM, "
121 + "T1.C3_NUM, "
122 + "T1.C4_NUM, "
123 + "T1.C5_FLOAT, "
124 + "T1.C6_DOUBLE, "
125 + "T1.C7_DATE, "
126 + "T1.C8_TIMESTAMP, "
127 + "T1.C9_TIMESTAMP_WITH_TZ, "
128 + "T1.C10_TIMESTAMP_WITH_LTZ, "
129 + "T1.C11_CHAR, "
130 + "T1.C12_VARCHAR, "
131 + "T1.C13_VARCHAR2, "
132 + "T1.C14_NCHAR, "
133 + "T1.C15_NVARCHAR2, "
134 + "T1.C16_URITYPE.GETURL() from "+tableName+" T1 "
135 + "minus select "
136 + "T2.C1_NUM, "
137 + "T2.C2_NUM, "
138 + "T2.C3_NUM, "
139 + "T2.C4_NUM, "
140 + "T2.C5_FLOAT, "
141 + "T2.C6_DOUBLE, "
142 + "T2.C7_DATE, "
143 + "T2.C8_TIMESTAMP, "
144 + "T2.C9_TIMESTAMP_WITH_TZ, "
145 + "T2.C10_TIMESTAMP_WITH_LTZ, "
146 + "T2.C11_CHAR, "
147 + "T2.C12_VARCHAR, "
148 + "T2.C13_VARCHAR2, "
149 + "T2.C14_NCHAR, "
150 + "T2.C15_NVARCHAR2, "
151 + "T2.C16_URITYPE.GETURL() from "+tempTableName+" T2))");
152 rs.next();
153 assertEquals(0, rs.getInt(1));
154 }
155 } finally {
156 cleanupFolders();
157 closeTestEnvConnection();
158 }
159 }
160 }