Java tutorial
/* * Copyright 2018 University of Michigan * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.verdictdb.core.scramblingquerying; import static org.junit.Assert.fail; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Map; import org.apache.commons.lang3.RandomStringUtils; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Ignore; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.Parameterized; import org.verdictdb.commons.DatabaseConnectionHelpers; import org.verdictdb.commons.VerdictOption; import org.verdictdb.exception.VerdictDBDbmsException; import org.verdictdb.exception.VerdictDBException; import org.verdictdb.jdbc41.VerdictConnection; import com.google.common.base.Charsets; import com.google.common.io.Files; /** Created by Dong Young Yoon on 7/18/18. */ @Ignore("This test uses private resources") @RunWith(Parameterized.class) public class TpchScrambleQueryForAllDatabasesTest { private static Map<String, Connection> connMap = new HashMap<>(); private static Map<String, Connection> vcMap = new HashMap<>(); private static Map<String, String> schemaMap = new HashMap<>(); private static VerdictOption options = new VerdictOption(); private static final String MYSQL_HOST; private static final int MYSQL_TPCH_QUERY_COUNT = 21; private static final int TPCH_QUERY_COUNT = 22; private static final String VERDICT_META_SCHEMA = "verdictdbmetaschema_" + RandomStringUtils.randomAlphanumeric(8).toLowerCase(); private static final String VERDICT_TEMP_SCHEMA = "verdictdbtempschema_" + RandomStringUtils.randomAlphanumeric(8).toLowerCase(); private String database = ""; private String query; // TODO: Add support for all four databases // private static final String[] targetDatabases = {"mysql", "impala", "redshift", "postgresql"}; // Disabled redshift test due to unavailable test instance private static final String[] targetDatabases = { "mysql", "impala" }; // private static final String[] targetDatabases = {"mysql", "impala", "redshift"}; // private static final String[] targetDatabases = {"mysql"}; public TpchScrambleQueryForAllDatabasesTest(String database, String query) { this.database = database; this.query = query; } static { String env = System.getenv("BUILD_ENV"); if (env != null && (env.equals("GitLab") || env.equals("DockerCompose"))) { MYSQL_HOST = "mysql"; } else { MYSQL_HOST = "localhost"; } } private static final String MYSQL_DATABASE = "tpch_test_" + RandomStringUtils.randomAlphanumeric(4).toLowerCase(); private static final String MYSQL_USER = "root"; private static final String MYSQL_PASSWORD = ""; private static final String IMPALA_HOST; static { IMPALA_HOST = System.getenv("VERDICTDB_TEST_IMPALA_HOST"); } private static final String IMPALA_DATABASE = "tpch_2_parquet"; private static final String IMPALA_USER = ""; private static final String IMPALA_PASSWORD = ""; private static final String REDSHIFT_HOST; private static final String REDSHIFT_DATABASE = "dev"; private static final String REDSHIFT_USER; private static final String REDSHIFT_PASSWORD; private static final String POSTGRES_HOST; private static final String POSTGRES_DATABASE = "test"; private static final String POSTGRES_USER = "postgres"; private static final String POSTGRES_PASSWORD = ""; static { String env = System.getenv("BUILD_ENV"); if (env != null && (env.equals("GitLab") || env.equals("DockerCompose"))) { POSTGRES_HOST = "postgres"; } else { POSTGRES_HOST = "localhost"; } } static { REDSHIFT_HOST = System.getenv("VERDICTDB_TEST_REDSHIFT_ENDPOINT"); REDSHIFT_USER = System.getenv("VERDICTDB_TEST_REDSHIFT_USER"); REDSHIFT_PASSWORD = System.getenv("VERDICTDB_TEST_REDSHIFT_PASSWORD"); } private static final String SCHEMA_NAME = "verdictdb_tpch_query_test_" + RandomStringUtils.randomAlphanumeric(8).toLowerCase(); @BeforeClass public static void setupDatabases() throws SQLException, VerdictDBDbmsException, IOException { options.setVerdictMetaSchemaName(VERDICT_META_SCHEMA); options.setVerdictTempSchemaName(VERDICT_TEMP_SCHEMA); setupMysql(); setupImpala(); // Disabled redshift test due to unavailable test instance // setupRedshift(); // TODO: Add below databases too // setupPostgresql(); } @AfterClass public static void tearDown() throws SQLException { for (String s : connMap.keySet()) { Connection conn = connMap.get(s); if (s.equals("mysql")) { conn.createStatement().execute(String.format("DROP SCHEMA IF EXISTS %s", SCHEMA_NAME)); } else { conn.createStatement().execute(String.format("DROP SCHEMA IF EXISTS %s CASCADE", SCHEMA_NAME)); } } } @Parameterized.Parameters(name = "{0}_tpch_{1}") public static Collection<Object[]> databases() { Collection<Object[]> params = new ArrayList<>(); for (String database : targetDatabases) { int queryCount = 0; switch (database) { case "mysql": queryCount = MYSQL_TPCH_QUERY_COUNT; break; case "impala": case "redshift": queryCount = TPCH_QUERY_COUNT; break; } // query 4, 13, 16, 21 contains count distinct for (int query = 1; query <= queryCount; ++query) { if (query != 13 && query != 21) { params.add(new Object[] { database, String.valueOf(query) }); } } if (database.equals("redshift")) { params.add(new Object[] { database, "e1" }); params.add(new Object[] { database, "e2" }); params.add(new Object[] { database, "e3" }); params.add(new Object[] { database, "e4" }); params.add(new Object[] { database, "e5" }); params.add(new Object[] { database, "e6" }); params.add(new Object[] { database, "e7" }); params.add(new Object[] { database, "e8" }); } // Uncomment below lines to test a specific query // params.clear(); // params.add(new Object[] {database, "14"}); } return params; } private static Connection setupMysql() throws SQLException, VerdictDBDbmsException { String mysqlConnectionString = String.format("jdbc:mysql://%s?autoReconnect=true&useSSL=false", MYSQL_HOST); String vcMysqlConnectionString = String.format( "jdbc:verdict:mysql://%s?autoReconnect=true&useSSL=false&" + "verdictdbmetaschema=%s&verdictdbtempschema=%s", MYSQL_HOST, VERDICT_META_SCHEMA, VERDICT_TEMP_SCHEMA); Connection conn = DatabaseConnectionHelpers.setupMySql(mysqlConnectionString, MYSQL_USER, MYSQL_PASSWORD, SCHEMA_NAME); Connection vc = DriverManager.getConnection(vcMysqlConnectionString, MYSQL_USER, MYSQL_PASSWORD); conn.setCatalog(SCHEMA_NAME); vc.setCatalog(SCHEMA_NAME); connMap.put("mysql", conn); vcMap.put("mysql", vc); schemaMap.put("mysql", MYSQL_DATABASE + "."); conn.createStatement() .execute(String.format("CREATE SCHEMA IF NOT EXISTS %s", options.getVerdictTempSchemaName())); vc.createStatement().execute( String.format("CREATE SCRAMBLE %s.lineitem_scramble FROM %s.lineitem", SCHEMA_NAME, SCHEMA_NAME)); return conn; } private static Connection setupImpala() throws SQLException, VerdictDBDbmsException, IOException { String connectionString = String.format("jdbc:impala://%s", IMPALA_HOST); String verdictConnectionString = String.format( "jdbc:verdict:impala://%s;verdictdbmetaschema=%s;verdictdbtempschema=%s", IMPALA_HOST, VERDICT_META_SCHEMA, VERDICT_TEMP_SCHEMA); Connection conn = DatabaseConnectionHelpers.setupImpala(connectionString, IMPALA_USER, IMPALA_PASSWORD, SCHEMA_NAME); Connection vc = DriverManager.getConnection(verdictConnectionString, IMPALA_USER, IMPALA_PASSWORD); connMap.put("impala", conn); vcMap.put("impala", vc); schemaMap.put("impala", IMPALA_DATABASE + "."); conn.createStatement() .execute(String.format("CREATE SCHEMA IF NOT EXISTS %s", options.getVerdictTempSchemaName())); vc.createStatement().execute( String.format("CREATE SCRAMBLE %s.lineitem_scramble FROM %s.lineitem", SCHEMA_NAME, SCHEMA_NAME)); return conn; } private static Connection setupRedshift() throws SQLException, VerdictDBDbmsException, IOException { String connectionString = String.format("jdbc:redshift://%s/%s", REDSHIFT_HOST, REDSHIFT_DATABASE); String verdictConnectionString = String.format( "jdbc:verdict:redshift://%s/%s;verdictdbtempschema=%s;verdictdbmetaschema=%s", REDSHIFT_HOST, REDSHIFT_DATABASE, SCHEMA_NAME, SCHEMA_NAME); Connection conn = DatabaseConnectionHelpers.setupRedshift(connectionString, REDSHIFT_USER, REDSHIFT_PASSWORD, SCHEMA_NAME); Connection vc = DriverManager.getConnection(verdictConnectionString, REDSHIFT_USER, REDSHIFT_PASSWORD); connMap.put("redshift", conn); vcMap.put("redshift", vc); schemaMap.put("redshift", ""); conn.createStatement() .execute(String.format("CREATE SCHEMA IF NOT EXISTS %s", options.getVerdictTempSchemaName())); vc.createStatement().execute( String.format("CREATE SCRAMBLE %s.lineitem_scramble FROM %s.lineitem", SCHEMA_NAME, SCHEMA_NAME)); return conn; } public static Connection setupPostgresql() throws SQLException, IOException, VerdictDBException { String connectionString = String.format("jdbc:postgresql://%s/%s", POSTGRES_HOST, POSTGRES_DATABASE); Connection conn = DatabaseConnectionHelpers.setupPostgresql(connectionString, POSTGRES_HOST, POSTGRES_PASSWORD, SCHEMA_NAME); VerdictConnection vc = new VerdictConnection(connectionString, POSTGRES_USER, POSTGRES_PASSWORD, options); connMap.put("postgresql", conn); vcMap.put("postgresql", vc); schemaMap.put("postgresql", ""); return conn; } @Test(expected = Test.None.class /* no exception expected */) public void testTpch() throws IOException, SQLException { ClassLoader classLoader = getClass().getClassLoader(); String filename = ""; switch (database) { case "mysql": filename = "companya/templated/mysql_queries/tpchMySQLQuery" + query + ".sql"; break; case "impala": filename = "companya/templated/impala_queries/tpchImpalaQuery" + query + ".sql"; break; case "redshift": filename = "companya/templated/redshift_queries/" + query + ".sql"; break; default: fail(String.format("Database '%s' not supported.", database)); } File queryFile = new File(classLoader.getResource(filename).getFile()); if (queryFile.exists()) { String originalSql = Files.toString(queryFile, Charsets.UTF_8); String sql = originalSql.replaceAll(DatabaseConnectionHelpers.TEMPLATE_SCHEMA_NAME, SCHEMA_NAME); Statement jdbcStmt = connMap.get(database).createStatement(); Statement vcStmt = vcMap.get(database).createStatement(); ResultSet jdbcRs = jdbcStmt.executeQuery(sql); ResultSet vcRs = vcStmt.executeQuery(sql); int columnCount = jdbcRs.getMetaData().getColumnCount(); int columnCount2 = vcRs.getMetaData().getColumnCount(); // assertEquals(columnCount, columnCount2); boolean jdbcNext = jdbcRs.next(); boolean vcNext = vcRs.next(); while (jdbcNext && vcNext) { // assertEquals(jdbcNext, vcNext); for (int i = 1; i <= columnCount; ++i) { System.out.println(jdbcRs.getObject(i) + " : " + vcRs.getObject(i)); // assertEquals(jdbcRs.getObject(i), vcRs.getObject(i)); } jdbcNext = jdbcRs.next(); vcNext = vcRs.next(); } // assertEquals(jdbcNext, vcNext); } else { System.out.println(String.format("tpch%d does not exist.", query)); } } }