diff options
Diffstat (limited to 'src/test/scala/parallelai/spyglass/jdbc')
| -rw-r--r-- | src/test/scala/parallelai/spyglass/jdbc/GenerateTestingTables.java | 201 | ||||
| -rw-r--r-- | src/test/scala/parallelai/spyglass/jdbc/example/JdbcSourceExampleTest.scala | 54 |
2 files changed, 201 insertions, 54 deletions
diff --git a/src/test/scala/parallelai/spyglass/jdbc/GenerateTestingTables.java b/src/test/scala/parallelai/spyglass/jdbc/GenerateTestingTables.java new file mode 100644 index 0000000..54ec8fc --- /dev/null +++ b/src/test/scala/parallelai/spyglass/jdbc/GenerateTestingTables.java @@ -0,0 +1,201 @@ +package parallelai.spyglass.jdbc; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import junit.framework.Assert; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.junit.Test; + +/** + * Class generates TWO tables in database 'TABLE_01' and 'TABLE_02' + * + * Those tables are used by the 'integration-testing' of JDBCSource in file + * JdbcSourceShouldReadWrite.scala + * + * Run with: mvn -Dtestparallelai.spyglass.jdbc.GenerateTestingTables test + * + */ +public class GenerateTestingTables { + + // JDBC driver name and database URL + static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; + static final String DB_PORT = "3306"; + static final String DB_NAME = "database_name"; + + + static final String DB_URL = "jdbc:mysql://<hostname>:<port>/<db_name>?zeroDateTimeBehavior=convertToNull"; + + // Database credentials + static final String USER = "user"; + static final String PASS = "password"; + + public static enum TestingTable { + TABLE_01, TABLE_02 + } + + private static final Log LOG = LogFactory + .getLog(GenerateTestingTables.class); + + @Test + public void fakeTest() { + + // Connect to Quorum + LOG.info("Connecting to " + DB_URL + ":" + DB_PORT); + + Connection conn = null; + Statement stmt = null; + try { + // STEP 2: Register JDBC driver + Class.forName("com.mysql.jdbc.Driver"); + + // STEP 3: Open a connection + LOG.info("Connecting to a selected database..."); + conn = DriverManager.getConnection(DB_URL, USER, PASS); + LOG.info("Connected database successfully..."); + + + // Delete test tables + deleteTestTable(conn, TestingTable.TABLE_01.name()); + deleteTestTable(conn, TestingTable.TABLE_02.name()); + + // Generate test tables + createTestTable(conn, TestingTable.TABLE_01); + createTestTable(conn, TestingTable.TABLE_02); + + // Populate test tables + populateTestTable(conn, TestingTable.TABLE_01); + //populateTestTable(conn, TestingTable.TABLE_02); + + // Print content of test table + printHTable(conn, TestingTable.TABLE_01); + + // If we've reached here - the testing data are in + Assert.assertEquals("true", "true"); + + + } catch (SQLException se) { + // Handle errors for JDBC + se.printStackTrace(); + LOG.error(se.toString()); + } catch (Exception e) { + // Handle errors for Class.forName + e.printStackTrace(); + LOG.error(e.toString()); + } finally { + // finally block used to close resources + try { + if (stmt != null) + conn.close(); + } catch (SQLException se) { + }// do nothing + try { + if (conn != null) + conn.close(); + } catch (SQLException se) { + se.printStackTrace(); + LOG.error(se.toString()); + }// end finally try + }// end try + + } + + private static void populateTestTable(Connection connection, TestingTable testingTable) + throws SQLException { + + + // Load up table + LOG.info("Populating table in given database..."); + Statement stmt = connection.createStatement(); + + + String [] queries = { + "insert into " + testingTable.name() + " values (1, 'A', 'X', 123)", + "insert into " + testingTable.name() + " values (2, 'B', 'Y', 234)", + "insert into " + testingTable.name() + " values (3, 'C', 'Z', 345)", + }; + + Statement statement = connection.createStatement(); + + for (String query : queries) { + statement.addBatch(query); + } + statement.executeBatch(); + LOG.info("Populated table in given database..."); + + statement.close(); + + } + + private static void createTestTable(Connection connection, TestingTable testingTable) + throws SQLException { + + LOG.info("Creating table in given database..."); + Statement stmt = connection.createStatement(); + + String sql = "CREATE TABLE " + testingTable.name() + " " + + "(id INTEGER not NULL, " + " test_column1 VARCHAR(255), " + + " test_column2 VARCHAR(255), " + " test_column3 INTEGER, " + + " PRIMARY KEY ( id ))"; + + stmt.executeUpdate(sql); + LOG.info("Created table in given database..."); + + stmt.close(); + } + + /** + * Method to disable and delete HBase Tables i.e. "int-test-01" + */ + private static void deleteTestTable(Connection connection, String tableName) throws SQLException { + + + // Execute a query + LOG.info("Deleting table in given database..."); + Statement stmt = connection.createStatement(); + + String sql = "DROP TABLE IF EXISTS " + tableName; + + int result = stmt.executeUpdate(sql); + LOG.info("Deleted table in given database... " + result); + + + stmt.close(); + } + + /** + * Method to print-out an HTable + */ + private static void printHTable(Connection connection, TestingTable testingTable) + throws SQLException { + + // Execute a query + LOG.info("Printing table in given database..."); + Statement stmt = connection.createStatement(); + + String sql = "SELECT * FROM " + testingTable.name(); + + ResultSet resultSet = stmt.executeQuery(sql); + LOG.info("Get data from table in given database..."); + + while (resultSet.next()) { + Integer key = resultSet.getInt("id"); + String testColumn1 = resultSet.getString("test_column1"); + String testColumn2 = resultSet.getString("test_column2"); + Integer testColumn3 = resultSet.getInt("test_column3"); + + LOG.info(key + " : " + testColumn1 + " : " + testColumn2 + " : " + testColumn3); + } + + } + + public static void main(String[] args) { + GenerateTestingTables test = new GenerateTestingTables(); + test.fakeTest(); + } +}
\ No newline at end of file diff --git a/src/test/scala/parallelai/spyglass/jdbc/example/JdbcSourceExampleTest.scala b/src/test/scala/parallelai/spyglass/jdbc/example/JdbcSourceExampleTest.scala deleted file mode 100644 index b268fa1..0000000 --- a/src/test/scala/parallelai/spyglass/jdbc/example/JdbcSourceExampleTest.scala +++ /dev/null @@ -1,54 +0,0 @@ -package parallelai.spyglass.jdbc.example - -import org.scalatest.junit.JUnitRunner -import org.junit.runner.RunWith -import org.scalatest.FunSpec -import com.twitter.scalding.{Tsv, JobTest, TupleConversions} -import org.slf4j.LoggerFactory -import cascading.tuple.{Tuple, Fields} -import parallelai.spyglass.jdbc.JDBCSource - -/** - * Simple example of JDBCSource testing - */ -@RunWith(classOf[JUnitRunner]) -class JdbcSourceExampleTest extends FunSpec with TupleConversions { - - val output = "src/test/resources/outputs/testOutput" - - val log = LoggerFactory.getLogger(this.getClass.getName) - - val sampleData = List( - (1, "c11", "c12", 11), - (2, "c21", "c22", 22) - ) - - JobTest("parallelai.spyglass.jdbc.example.JdbcSourceExample") - .arg("test", "") - .arg("app.conf.path", "app.conf") - .arg("output", output) - .arg("debug", "true") - .source( - new JDBCSource( - "db_name", - "com.mysql.jdbc.Driver", - "jdbc:mysql://<hostname>:<port>/<db_name>?zeroDateTimeBehavior=convertToNull", - "user", - "password", - List("KEY_ID", "COL1", "COL2", "COL3"), - List("bigint(20)", "varchar(45)", "varchar(45)", "bigint(20)"), - List("key_id"), - new Fields("key_id", "col1", "col2", "col3")), sampleData) - .sink[Tuple](Tsv(output.format("get_list"))) { - outputBuffer => - log.debug("Output => " + outputBuffer) - - it("should return the mock data provided.") { - assert(outputBuffer.size === 2) - } - } - .run - .finish - - -} |