DRILL-6955: storage-jdbc tests improvements

- Remove plugins usage for instantiating test databases and tables
- Replace derby with h2 database

closes #1603

6 files changed, 213 insertions, 82 deletions

diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
index 168b5f37a..a40eec2fa 100644
--- a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithDerbyIT.java
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
@@ -18,48 +18,96 @@
 package org.apache.drill.exec.store.jdbc;
 
 import org.apache.drill.categories.JdbcStorageTest;
-import org.apache.drill.PlanTestBase;
+import org.apache.drill.exec.ExecConstants;
 import org.apache.drill.exec.expr.fn.impl.DateUtility;
 
+import org.apache.drill.exec.server.DrillbitContext;
+import org.apache.drill.exec.store.StoragePluginRegistryImpl;
 import org.apache.drill.exec.util.StoragePluginTestUtils;
+import org.apache.drill.test.ClusterFixture;
+import org.apache.drill.test.ClusterTest;
+import org.h2.tools.RunScript;
+import org.junit.Assert;
 import org.junit.BeforeClass;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
+import java.io.FileReader;
 import java.math.BigDecimal;
+import java.net.URL;
 import java.nio.file.Paths;
+import java.sql.Connection;
+import java.sql.DriverManager;
 
+import static org.hamcrest.CoreMatchers.containsString;
+import static org.hamcrest.core.IsNot.not;
 import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertThat;
 
 /**
- * JDBC storage plugin tests against Derby.
+ * JDBC storage plugin tests against H2.
  */
 @Category(JdbcStorageTest.class)
-public class TestJdbcPluginWithDerbyIT extends PlanTestBase {
+public class TestJdbcPluginWithH2IT extends ClusterTest {
 
   private static final String TABLE_PATH = "jdbcmulti/";
   private static final String TABLE_NAME = String.format("%s.`%s`", StoragePluginTestUtils.DFS_PLUGIN_NAME, TABLE_PATH);
 
   @BeforeClass
+  public static void initH2() throws Exception {
+    Class.forName("org.h2.Driver");
+    String connString = String.format(
+        "jdbc:h2:%s", dirTestWatcher.getTmpDir().getCanonicalPath());
+
+    try (Connection connection = DriverManager.getConnection(connString, "root", "root")) {
+      URL scriptFile = TestJdbcPluginWithH2IT.class.getClassLoader().getResource("h2-test-data.sql");
+      Assert.assertNotNull("Script for test tables generation 'h2-test-data.sql' " +
+          "cannot be found in test resources", scriptFile);
+      RunScript.execute(connection, new FileReader(scriptFile.getFile()));
+    }
+
+    startCluster(ClusterFixture.builder(dirTestWatcher));
+
+    JdbcStorageConfig jdbcStorageConfig = new JdbcStorageConfig(
+        "org.h2.Driver",
+        connString,
+        "root",
+        "root",
+        true);
+    jdbcStorageConfig.setEnabled(true);
+
+    String pluginName = "h2";
+    DrillbitContext context = cluster.drillbit().getContext();
+    JdbcStoragePlugin jdbcStoragePlugin = new JdbcStoragePlugin(jdbcStorageConfig,
+        context, pluginName);
+    StoragePluginRegistryImpl pluginRegistry = (StoragePluginRegistryImpl) context.getStorage();
+    pluginRegistry.addPluginToPersistentStoreIfAbsent(pluginName, jdbcStorageConfig, jdbcStoragePlugin);
+  }
+
+  @BeforeClass
   public static void copyData() {
     dirTestWatcher.copyResourceToRoot(Paths.get(TABLE_PATH));
   }
 
   @Test
   public void testCrossSourceMultiFragmentJoin() throws Exception {
-    testNoResult("SET `planner.slice_target` = 1");
-    test("select x.person_id, y.salary from derby.drill_derby_test.person x "
-        + "join %s y on x.person_id = y.person_id ", TABLE_NAME);
+    try {
+      client.alterSession(ExecConstants.SLICE_TARGET, 1);
+      run("select x.person_id, y.salary from h2.drill_h2_test.person x "
+              + "join %s y on x.person_id = y.person_id ", TABLE_NAME);
+    } finally {
+      client.resetSession(ExecConstants.SLICE_TARGET);
+    }
   }
 
   @Test
   public void validateResult() throws Exception {
-    // Skip date, time, and timestamp types since derby mangles these due to improper timezone support.
+    // Skip date, time, and timestamp types since h2 mangles these due to improper timezone support.
     testBuilder()
         .sqlQuery(
             "select person_id, first_name, last_name, address, city, state, zip, json, bigint_field, smallint_field, " +
                 "numeric_field, boolean_field, double_field, float_field, real_field, time_field, timestamp_field, " +
-                "date_field, clob_field from derby.`drill_derby_test`.person")
+                "date_field, clob_field from h2.`drill_h2_test`.person")
         .ordered()
         .baselineColumns("person_id", "first_name", "last_name", "address", "city", "state", "zip", "json",
             "bigint_field", "smallint_field", "numeric_field", "boolean_field", "double_field", "float_field",
@@ -85,84 +133,108 @@ public class TestJdbcPluginWithDerbyIT extends PlanTestBase {
 
   @Test
   public void pushdownJoin() throws Exception {
-    testNoResult("use derby");
-    String query = "select x.person_id from (select person_id from derby.drill_derby_test.person) x "
-            + "join (select person_id from derby.drill_derby_test.person) y on x.person_id = y.person_id ";
-    testPlanMatchingPatterns(query, new String[]{}, new String[]{"Join"});
+    run("use h2");
+    String query = "select x.person_id from (select person_id from h2.drill_h2_test.person) x "
+            + "join (select person_id from h2.drill_h2_test.person) y on x.person_id = y.person_id ";
+
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Join operator",
+        plan, not(containsString("Join")));
   }
 
   @Test
   public void pushdownJoinAndFilterPushDown() throws Exception {
-    final String query = "select * from \n" +
-        "derby.drill_derby_test.person e\n" +
+    String query = "select * from \n" +
+        "h2.drill_h2_test.person e\n" +
         "INNER JOIN \n" +
-        "derby.drill_derby_test.person s\n" +
+        "h2.drill_h2_test.person s\n" +
         "ON e.FIRST_NAME = s.FIRST_NAME\n" +
         "WHERE e.LAST_NAME > 'hello'";
 
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Join operator",
+        plan, not(containsString("Join")));
+    assertThat("Query plan shouldn't contain Filter operator",
+        plan, not(containsString("Filter")));
   }
 
   @Test
   public void pushdownAggregation() throws Exception {
-    final String query = "select count(*) from derby.drill_derby_test.person";
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Aggregate" });
+    String query = "select count(*) from h2.drill_h2_test.person";
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Aggregate operator",
+        plan, not(containsString("Aggregate")));
   }
 
   @Test
   public void pushdownDoubleJoinAndFilter() throws Exception {
-    final String query = "select * from \n" +
-        "derby.drill_derby_test.person e\n" +
+    String query = "select * from \n" +
+        "h2.drill_h2_test.person e\n" +
         "INNER JOIN \n" +
-        "derby.drill_derby_test.person s\n" +
+        "h2.drill_h2_test.person s\n" +
         "ON e.person_ID = s.person_ID\n" +
         "INNER JOIN \n" +
-        "derby.drill_derby_test.person ed\n" +
+        "h2.drill_h2_test.person ed\n" +
         "ON e.person_ID = ed.person_ID\n" +
         "WHERE s.first_name > 'abc' and ed.first_name > 'efg'";
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
+
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Join operator",
+        plan, not(containsString("Join")));
+    assertThat("Query plan shouldn't contain Filter operator",
+        plan, not(containsString("Filter")));
   }
 
   @Test
   public void showTablesDefaultSchema() throws Exception {
-    testNoResult("use derby.drill_derby_test");
-    assertEquals(1, testSql("show tables like 'PERSON'"));
+    run("use h2.drill_h2_test");
+    assertEquals(1, queryBuilder().sql("show tables like 'PERSON'").run().recordCount());
 
     // check table names case insensitivity
-    assertEquals(1, testSql("show tables like 'person'"));
+    assertEquals(1, queryBuilder().sql("show tables like 'person'").run().recordCount());
   }
 
   @Test
   public void describe() throws Exception {
-    testNoResult("use derby.drill_derby_test");
-    assertEquals(19, testSql("describe PERSON"));
+    run("use h2.drill_h2_test");
+    assertEquals(19, queryBuilder().sql("describe PERSON").run().recordCount());
 
     // check table names case insensitivity
-    assertEquals(19, testSql("describe person"));
+    assertEquals(19, queryBuilder().sql("describe person").run().recordCount());
   }
 
   @Test
   public void ensureDrillFunctionsAreNotPushedDown() throws Exception {
     // This should verify that we're not trying to push CONVERT_FROM into the JDBC storage plugin. If were pushing
     // this function down, the SQL query would fail.
-    testNoResult("select CONVERT_FROM(JSON, 'JSON') from derby.drill_derby_test.person where person_ID = 4");
+    run("select CONVERT_FROM(JSON, 'JSON') from h2.drill_h2_test.person where person_ID = 4");
   }
 
   @Test
   public void pushdownFilter() throws Exception {
-    String query = "select * from derby.drill_derby_test.person where person_ID = 1";
-    testPlanMatchingPatterns(query, new String[]{}, new String[]{"Filter"});
+    String query = "select * from h2.drill_h2_test.person where person_ID = 1";
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Filter operator",
+        plan, not(containsString("Filter")));
   }
 
   @Test
   public void testCaseInsensitiveTableNames() throws Exception {
-    assertEquals(5, testSql("select * from derby.drill_derby_test.PeRsOn"));
-    assertEquals(5, testSql("select * from derby.drill_derby_test.PERSON"));
-    assertEquals(5, testSql("select * from derby.drill_derby_test.person"));
+    assertEquals(5, queryBuilder().sql("select * from h2.drill_h2_test.PeRsOn").run().recordCount());
+    assertEquals(5, queryBuilder().sql("select * from h2.drill_h2_test.PERSON").run().recordCount());
+    assertEquals(5, queryBuilder().sql("select * from h2.drill_h2_test.person").run().recordCount());
   }
 
   @Test
   public void testJdbcStoragePluginSerDe() throws Exception {
-    testPhysicalPlanExecutionBasedOnQuery("select * from derby.drill_derby_test.PeRsOn");
+    String query = "select * from h2.drill_h2_test.PeRsOn";
+
+    String plan = queryBuilder().sql(query).explainJson();
+    assertEquals(5, queryBuilder().physical(plan).run().recordCount());
   }
 }
diff --git a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
index 963d75e7b..447e76a38 100644
--- a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
+++ b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
@@ -17,24 +17,103 @@
  */
 package org.apache.drill.exec.store.jdbc;
 
+import com.wix.mysql.EmbeddedMysql;
+import com.wix.mysql.ScriptResolver;
+import com.wix.mysql.config.MysqldConfig;
+import com.wix.mysql.config.SchemaConfig;
+import com.wix.mysql.distribution.Version;
 import org.apache.drill.categories.JdbcStorageTest;
 import org.apache.drill.exec.expr.fn.impl.DateUtility;
-import org.apache.drill.PlanTestBase;
-
+import org.apache.drill.exec.store.StoragePluginRegistryImpl;
+import org.apache.drill.test.ClusterFixture;
+import org.apache.drill.test.ClusterTest;
+import org.apache.drill.test.QueryTestUtil;
+import org.joda.time.DateTimeZone;
+import org.junit.AfterClass;
 import org.junit.Assume;
+import org.junit.BeforeClass;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
 import java.math.BigDecimal;
 
+import static org.hamcrest.CoreMatchers.containsString;
+import static org.hamcrest.core.IsNot.not;
 import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertThat;
 
 /**
  * JDBC storage plugin tests against MySQL.
  * Note: it requires libaio.so library in the system
  */
 @Category(JdbcStorageTest.class)
-public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
+public class TestJdbcPluginWithMySQLIT extends ClusterTest {
+
+  private static EmbeddedMysql mysqld;
+
+  @BeforeClass
+  public static void initMysql() throws Exception {
+    String mysqlPluginName = "mysql";
+    String mysqlDBName = "drill_mysql_test";
+    int mysqlPort = QueryTestUtil.getFreePortNumber(2215, 300);
+
+    MysqldConfig config = MysqldConfig.aMysqldConfig(Version.v5_6_21)
+        .withPort(mysqlPort)
+        .withUser("mysqlUser", "mysqlPass")
+        .withTimeZone(DateTimeZone.UTC.toTimeZone())
+        .build();
+
+    SchemaConfig.Builder schemaConfig = SchemaConfig.aSchemaConfig(mysqlDBName)
+        .withScripts(ScriptResolver.classPathScript("mysql-test-data.sql"));
+
+    String osName = System.getProperty("os.name").toLowerCase();
+    if (osName.startsWith("linux")) {
+      schemaConfig.withScripts(ScriptResolver.classPathScript("mysql-test-data-linux.sql"));
+    }
+
+    mysqld = EmbeddedMysql.anEmbeddedMysql(config)
+        .addSchema(schemaConfig.build())
+        .start();
+
+    startCluster(ClusterFixture.builder(dirTestWatcher));
+
+    StoragePluginRegistryImpl pluginRegistry = (StoragePluginRegistryImpl) cluster.drillbit().getContext().getStorage();
+
+    JdbcStorageConfig jdbcStorageConfig = new JdbcStorageConfig(
+        "com.mysql.cj.jdbc.Driver",
+        String.format("jdbc:mysql://localhost:%s/%s?useJDBCCompliantTimezoneShift=true", mysqlPort, mysqlDBName),
+        "mysqlUser",
+        "mysqlPass",
+        false);
+    jdbcStorageConfig.setEnabled(true);
+
+    JdbcStoragePlugin jdbcStoragePlugin = new JdbcStoragePlugin(jdbcStorageConfig,
+        cluster.drillbit().getContext(), mysqlPluginName);
+    pluginRegistry.addPluginToPersistentStoreIfAbsent(mysqlPluginName, jdbcStorageConfig, jdbcStoragePlugin);
+
+    if (osName.startsWith("linux")) {
+      // adds storage plugin with case insensitive table names
+      String mysqlCaseSensitivePluginName = "mysqlCaseInsensitive";
+      JdbcStorageConfig jdbcCaseSensitiveStorageConfig = new JdbcStorageConfig(
+          "com.mysql.cj.jdbc.Driver",
+          String.format("jdbc:mysql://localhost:%s/%s?useJDBCCompliantTimezoneShift=true", mysqlPort, mysqlDBName),
+          "mysqlUser",
+          "mysqlPass",
+          true);
+      jdbcCaseSensitiveStorageConfig.setEnabled(true);
+
+      JdbcStoragePlugin jdbcCaseSensitiveStoragePlugin = new JdbcStoragePlugin(jdbcCaseSensitiveStorageConfig,
+          cluster.drillbit().getContext(), mysqlCaseSensitivePluginName);
+      pluginRegistry.addPluginToPersistentStoreIfAbsent(mysqlCaseSensitivePluginName, jdbcCaseSensitiveStorageConfig, jdbcCaseSensitiveStoragePlugin);
+    }
+  }
+
+  @AfterClass
+  public static void stopMysql() {
+    if (mysqld != null) {
+      mysqld.stop();
+    }
+  }
 
   @Test
   public void validateResult() throws Exception {
@@ -110,14 +189,17 @@ public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
             null,
             null,
             null, "XXX")
-            .go();
+         .go();
   }
 
   @Test
   public void pushdownJoin() throws Exception {
     String query = "select x.person_id from (select person_id from mysql.`drill_mysql_test`.person) x "
             + "join (select person_id from mysql.`drill_mysql_test`.person) y on x.person_id = y.person_id ";
-    testPlanMatchingPatterns(query, new String[]{}, new String[]{"Join"});
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Join operator",
+        plan, not(containsString("Join")));
   }
 
   @Test
@@ -129,19 +211,26 @@ public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
             "ON e.first_name = s.first_name " +
             "WHERE e.last_name > 'hello'";
 
-    testPlanMatchingPatterns(query, new String[] {}, new String[] { "Join", "Filter" });
+    String plan = queryBuilder().sql(query).explainText();
+
+    assertThat("Query plan shouldn't contain Join operator",
+        plan, not(containsString("Join")));
+    assertThat("Query plan shouldn't contain Filter operator",
+        plan, not(containsString("Filter")));
   }
 
   @Test
   public void testPhysicalPlanSubmission() throws Exception {
-    testPhysicalPlanExecutionBasedOnQuery("select * from mysql.`drill_mysql_test`.person");
+    String query = "select * from mysql.`drill_mysql_test`.person";
+    String plan = queryBuilder().sql(query).explainJson();
+    assertEquals(4, queryBuilder().physical(plan).run().recordCount());
   }
 
   @Test
   public void emptyOutput() throws Exception {
     String query = "select * from mysql.`drill_mysql_test`.person e limit 0";
 
-    test(query);
+    run(query);
   }
 
   @Test
@@ -151,16 +240,16 @@ public class TestJdbcPluginWithMySQLIT extends PlanTestBase {
         "Skip tests for non-linux systems due to " +
             "table names case-insensitivity problems on Windows and MacOS",
         osName.startsWith("linux"));
-    test("use mysqlCaseInsensitive.`drill_mysql_test`");
+    run("use mysqlCaseInsensitive.`drill_mysql_test`");
     // two table names match the filter ignoring the case
-    assertEquals(2, testSql("show tables like 'caseSensitiveTable'"));
+    assertEquals(2, queryBuilder().sql("show tables like 'caseSensitiveTable'").run().recordCount());
 
-    test("use mysql.`drill_mysql_test`");
+    run("use mysql.`drill_mysql_test`");
     // single table matches the filter considering table name the case
-    assertEquals(1, testSql("show tables like 'caseSensitiveTable'"));
+    assertEquals(1, queryBuilder().sql("show tables like 'caseSensitiveTable'").run().recordCount());
 
     // checks that tables with names in different case are recognized correctly
-    assertEquals(1, testSql("describe caseSensitiveTable"));
-    assertEquals(2, testSql("describe CASESENSITIVETABLE"));
+    assertEquals(1, queryBuilder().sql("describe caseSensitiveTable").run().recordCount());
+    assertEquals(2, queryBuilder().sql("describe CASESENSITIVETABLE").run().recordCount());
   }
 }
diff --git a/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json b/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json
deleted file mode 100755
index 1c2aa3fc6..000000000
--- a/contrib/storage-jdbc/src/test/resources/bootstrap-storage-plugins.json
+++ /dev/null
@@ -1,25 +0,0 @@
-{
-    "storage" : {
-        derby : {
-          type    : "jdbc",
-          driver  : "org.apache.derby.jdbc.ClientDriver",
-          url     : "jdbc:derby://localhost:${derby.reserved.port}/memory:${derby.database.name};user=root;password=root",
-          caseInsensitiveTableNames: true,
-          enabled : true
-        },
-        mysql : {
-          type    : "jdbc",
-          driver  : "com.mysql.cj.jdbc.Driver",
-          url     : "jdbc:mysql://localhost:${mysql.reserved.port}/${mysql.database.name}?user=root&password=root&useJDBCCompliantTimezoneShift=true",
-          enabled : true
-        },
-        mysqlCaseInsensitive : {
-          type    : "jdbc",
-          driver  : "com.mysql.cj.jdbc.Driver",
-          url     : "jdbc:mysql://localhost:${mysql.reserved.port}/${mysql.database.name}?user=root&password=root&useJDBCCompliantTimezoneShift=true",
-          caseInsensitiveTableNames: true,
-          enabled : true
-        }
-    }
-}
-
diff --git a/contrib/storage-jdbc/src/test/resources/derby-test-data.sql b/contrib/storage-jdbc/src/test/resources/h2-test-data.sql
index 0d69724b4..a2643fbb7 100644
--- a/contrib/storage-jdbc/src/test/resources/derby-test-data.sql
+++ b/contrib/storage-jdbc/src/test/resources/h2-test-data.sql
@@ -1,6 +1,6 @@
 
-create SCHEMA drill_derby_test;
-set schema drill_derby_test;
+create SCHEMA drill_h2_test;
+set schema drill_h2_test;
 
 create table person (
   person_id       INT NOT NULL PRIMARY KEY,
diff --git a/contrib/storage-jdbc/src/test/resources/mysql-test-data-linux.sql b/contrib/storage-jdbc/src/test/resources/mysql-test-data-linux.sql
index e20916697..2d7f3ca4b 100644
--- a/contrib/storage-jdbc/src/test/resources/mysql-test-data-linux.sql
+++ b/contrib/storage-jdbc/src/test/resources/mysql-test-data-linux.sql
@@ -1,5 +1,3 @@
-set global time_zone = "+00:00";
-
 use drill_mysql_test;
 
 create table CASESENSITIVETABLE (
diff --git a/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql b/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql
index 0d2ab682c..9a180e042 100644
--- a/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql
+++ b/contrib/storage-jdbc/src/test/resources/mysql-test-data.sql
@@ -1,6 +1,3 @@
-
-set global time_zone = "+00:00";
-
 use drill_mysql_test;
 
 create table caseSensitiveTable (