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 }