bd12c55661bbc6a36f704b2a24c74b0b01cd84b6
[sqoop.git] / src / test / org / apache / sqoop / manager / sqlserver / MSSQLTestUtils.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 package org.apache.sqoop.manager.sqlserver;
19
20 import java.sql.Connection;
21 import java.sql.DriverManager;
22 import java.sql.PreparedStatement;
23 import java.sql.ResultSet;
24 import java.sql.ResultSetMetaData;
25 import java.sql.SQLException;
26 import java.sql.Statement;
27
28 import org.apache.commons.logging.Log;
29 import org.apache.commons.logging.LogFactory;
30 import org.apache.hadoop.util.StringUtils;
31
32 /**
33 * Test utilities for SQL Server manual tests.
34 */
35 public class MSSQLTestUtils {
36
37 public static final Log LOG = LogFactory.getLog(
38 MSSQLTestUtils.class.getName());
39
40 public static final String DATABASE_USER = System.getProperty(
41 "ms.sqlserver.username", "SQOOPUSER");
42 public static final String DATABASE_PASSWORD = System.getProperty(
43 "ms.sqlserver.password", "PASSWORD");
44 public static final String DATABASE_NAME = System.getProperty(
45 "sqoop.test.sqlserver.database",
46 "sqooptest");
47 public static final String HOST_URL = System.getProperty(
48 "sqoop.test.sqlserver.connectstring.host_url",
49 "jdbc:sqlserver://sqlserverhost:1433");
50
51 public static final String CONNECT_STRING = HOST_URL + ";database=" + DATABASE_NAME;
52
53 public static final String CREATE_TALBE_LINEITEM
54 = "CREATE TABLE TPCH1M_LINEITEM"
55 + "( [L_ORDERKEY] [int] NULL, [L_PARTKEY] "
56 + "[int] NULL, [L_SUPPKEY] [int] NULL, [L_LINENUMBER] [int] NULL, "
57 + "[L_QUANTITY] [int] NULL, [L_EXTENDEDPRICE] [decimal](15, 2) NULL, "
58 + "[L_DISCOUNT] [decimal](15, 2) NULL, [L_TAX] [decimal](15, 2) NULL,"
59 + " [L_RETURNFLAG] [varchar](max) NULL, [L_LINESTATUS] [varchar](max)"
60 + " NULL, [L_SHIPDATE] [varchar](max) NULL, [L_COMMITDATE] [varchar](max)"
61 + " NULL, [L_RECEIPTDATE] [varchar](max) NULL, [L_SHIPINSTRUCT] [varchar]"
62 + "(max) NULL, [L_SHIPMODE] [varchar](max) NULL, [L_COMMENT] [varchar]"
63 + "(max) NULL) ";
64
65 private Connection conn = null;
66
67 private Connection getConnection() {
68
69 if (conn == null) {
70
71 try {
72 Connection con = DriverManager.getConnection(CONNECT_STRING,
73 DATABASE_USER, DATABASE_PASSWORD);
74 conn = con;
75 return con;
76 } catch (SQLException e) {
77 LOG.error("Get SQLException during setting up connection: " + StringUtils.stringifyException(e));
78 return null;
79 }
80 }
81
82 return conn;
83 }
84
85 public void createTableFromSQL(String sql) throws SQLException {
86 Connection dbcon = this.getConnection();
87
88 System.out.println("SQL : " + sql);
89 this.dropTableIfExists("TPCH1M_LINEITEM");
90
91 try {
92 Statement st = dbcon.createStatement();
93 int res = st.executeUpdate(sql);
94 System.out.println("Result : " + res);
95
96 } catch (SQLException e) {
97 LOG.error("Got SQLException during creating table: " + StringUtils.stringifyException(e));
98 }
99
100 }
101
102 public void populateLineItem() {
103 String sql = "insert into tpch1m_lineitem values (1,2,3,4,5,6,7,8,'AB',"
104 + "'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
105 String sql2 = "insert into tpch1m_lineitem values (2,3,4,5,6,7,8,9,'AB'"
106 + ",'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
107 String sql3 = "insert into tpch1m_lineitem values (3,4,5,6,7,8,9,10,'AB',"
108 + "'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
109 String sql4 = "insert into tpch1m_lineitem values (4,5,6,7,8,9,10,11,'AB'"
110 + ",'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
111 Connection dbcon = this.getConnection();
112 Statement st;
113 try {
114 st = dbcon.createStatement();
115 st.addBatch(sql);
116 st.addBatch(sql2);
117 st.addBatch(sql3);
118 st.addBatch(sql4);
119 int[] res = st.executeBatch();
120
121 System.out.println(res);
122 } catch (SQLException e) {
123 LOG.error(StringUtils.stringifyException(e));
124 }
125
126 }
127
128 public void metadataStuff(String table) {
129 Connection dbcon = this.getConnection();
130 String sql = "select top 1 * from " + table;
131
132 Statement st;
133 try {
134
135 st = dbcon.createStatement();
136 ResultSet rs = st.executeQuery(sql);
137 ResultSetMetaData rsmd = rs.getMetaData();
138
139 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
140 System.out.println(rsmd.getColumnName(i) + "\t"
141 + rsmd.getColumnClassName(i) + "\t"
142 + rsmd.getColumnType(i) + "\t"
143 + rsmd.getColumnTypeName(i) + "\n");
144 }
145
146 } catch (SQLException e) {
147 LOG.error(StringUtils.stringifyException(e));
148 }
149
150 }
151
152 public static String getDBUserName() {
153 return DATABASE_USER;
154 }
155
156 public static String getDBPassWord() {
157 return DATABASE_PASSWORD;
158 }
159
160 public static String getDBDatabaseName() {
161 return DATABASE_NAME;
162 }
163
164 public static String getDBConnectString() {
165 return CONNECT_STRING;
166 }
167
168 public void dropTableIfExists(String table) throws SQLException {
169 conn = getConnection();
170 System.out.println("Dropping table : " + table);
171 String sqlStmt = "IF OBJECT_ID('" + table
172 + "') IS NOT NULL DROP TABLE " + table;
173 PreparedStatement statement = conn.prepareStatement(sqlStmt,
174 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
175 try {
176 statement.executeUpdate();
177 conn.commit();
178 } finally {
179 statement.close();
180 }
181 }
182
183 public static String[] getColumns() {
184 return new String[] { "L_ORDERKEY", "L_PARTKEY", "L_SUPPKEY",
185 "L_LINENUMBER", "L_QUANTITY", "L_EXTENDEDPRICE", "L_DISCOUNT",
186 "L_TAX", "L_RETURNFLAG", "L_LINESTATUS", "L_SHIPDATE",
187 "L_COMMITDATE", "L_RECEIPTDATE", "L_SHIPINSTRUCT",
188 "L_SHIPMODE", "L_COMMENT", };
189 }
190 }