/*
* 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 com.facebook.presto.tests.jdbc;
import com.facebook.presto.jdbc.PrestoConnection;
import com.teradata.tempto.BeforeTestWithContext;
import com.teradata.tempto.ProductTest;
import com.teradata.tempto.Requirement;
import com.teradata.tempto.RequirementsProvider;
import com.teradata.tempto.Requires;
import com.teradata.tempto.configuration.Configuration;
import com.teradata.tempto.fulfillment.table.hive.tpch.ImmutableTpchTablesRequirements.ImmutableNationTable;
import com.teradata.tempto.query.QueryResult;
import io.airlift.log.Logger;
import org.testng.annotations.Test;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import static com.facebook.presto.tests.TestGroups.JDBC;
import static com.facebook.presto.tests.TestGroups.SIMBA_JDBC;
import static com.facebook.presto.tests.TpchTableResults.PRESTO_NATION_RESULT;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.getSessionProperty;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.resetSessionProperty;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.setSessionProperty;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.usingPrestoJdbcDriver;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.usingTeradataJdbc4Driver;
import static com.facebook.presto.tests.utils.JdbcDriverUtils.usingTeradataJdbcDriver;
import static com.teradata.tempto.Requirements.compose;
import static com.teradata.tempto.assertions.QueryAssert.Row.row;
import static com.teradata.tempto.assertions.QueryAssert.assertThat;
import static com.teradata.tempto.fulfillment.table.MutableTableRequirement.State.CREATED;
import static com.teradata.tempto.fulfillment.table.MutableTablesState.mutableTablesState;
import static com.teradata.tempto.fulfillment.table.TableRequirements.immutableTable;
import static com.teradata.tempto.fulfillment.table.TableRequirements.mutableTable;
import static com.teradata.tempto.fulfillment.table.hive.tpch.TpchTableDefinitions.NATION;
import static com.teradata.tempto.internal.convention.SqlResultDescriptor.sqlResultDescriptorForResource;
import static com.teradata.tempto.query.QueryExecutor.defaultQueryExecutor;
import static com.teradata.tempto.query.QueryExecutor.query;
import static java.lang.Boolean.FALSE;
import static java.lang.Boolean.TRUE;
import static java.util.Locale.CHINESE;
import static org.assertj.core.api.Assertions.assertThat;
public class JdbcTests
extends ProductTest
{
private static final Logger LOGGER = Logger.get(JdbcTests.class);
private static final String TABLE_NAME = "nation_table_name";
private static class ImmutableAndMutableNationTable
implements RequirementsProvider
{
public Requirement getRequirements(Configuration configuration)
{
return compose(immutableTable(NATION), mutableTable(NATION, TABLE_NAME, CREATED));
}
}
private Connection connection;
@BeforeTestWithContext
public void setup()
throws SQLException
{
connection = defaultQueryExecutor().getConnection();
}
@Test(groups = JDBC)
@Requires(ImmutableNationTable.class)
public void shouldExecuteQuery()
throws SQLException
{
try (Statement statement = connection.createStatement()) {
QueryResult result = queryResult(statement, "select * from hive.default.nation");
assertThat(result).matches(PRESTO_NATION_RESULT);
}
}
@Test(groups = JDBC)
@Requires(ImmutableAndMutableNationTable.class)
public void shouldInsertSelectQuery()
throws SQLException
{
String tableNameInDatabase = mutableTablesState().get(TABLE_NAME).getNameInDatabase();
assertThat(query("SELECT * FROM " + tableNameInDatabase)).hasNoRows();
try (Statement statement = connection.createStatement()) {
assertThat(statement.executeUpdate("insert into " + tableNameInDatabase + " select * from nation"))
.isEqualTo(25);
}
assertThat(query("SELECT * FROM " + tableNameInDatabase)).matches(PRESTO_NATION_RESULT);
}
@Test(groups = JDBC)
@Requires(ImmutableNationTable.class)
public void shouldExecuteQueryWithSelectedCatalogAndSchema()
throws SQLException
{
connection.setCatalog("hive");
connection.setSchema("default");
try (Statement statement = connection.createStatement()) {
QueryResult result = queryResult(statement, "select * from nation");
assertThat(result).matches(PRESTO_NATION_RESULT);
}
}
@Test(groups = JDBC)
public void shouldSetTimezone()
throws SQLException
{
if (usingPrestoJdbcDriver(connection)) {
String timeZoneId = "Indian/Kerguelen";
((PrestoConnection) connection).setTimeZoneId(timeZoneId);
try (Statement statement = connection.createStatement()) {
QueryResult result = queryResult(statement, "select current_timezone()");
assertThat(result).contains(row(timeZoneId));
}
}
else {
LOGGER.warn("shouldSetTimezone() only applies to PrestoJdbcDriver");
}
}
@Test(groups = JDBC)
public void shouldSetLocale()
throws SQLException
{
if (usingPrestoJdbcDriver(connection)) {
((PrestoConnection) connection).setLocale(CHINESE);
try (Statement statement = connection.createStatement()) {
QueryResult result = queryResult(statement, "SELECT date_format(TIMESTAMP '2001-01-09 09:04', '%M')");
assertThat(result).contains(row("一月"));
}
}
else {
LOGGER.warn("shouldSetLocale() only applies to PrestoJdbcDriver");
}
}
@Test(groups = JDBC)
public void shouldGetSchemas()
throws SQLException
{
QueryResult result = QueryResult.forResultSet(metaData().getSchemas("hive", null));
assertThat(result).contains(row("default", "hive"));
}
@Test(groups = JDBC)
@Requires(ImmutableNationTable.class)
public void shouldGetTables()
throws SQLException
{
QueryResult result = QueryResult.forResultSet(metaData().getTables("hive", null, null, null));
assertThat(result).contains(row("hive", "default", "nation", "TABLE", null, null, null, null, null, null));
}
@Test(groups = JDBC)
@Requires(ImmutableNationTable.class)
public void shouldGetColumns()
throws SQLException
{
// The JDBC spec is vague on what values getColumns() should return, so accept the values that Facebook or Simba return.
QueryResult result = QueryResult.forResultSet(metaData().getColumns("hive", "default", "nation", null));
if (usingPrestoJdbcDriver(connection)) {
assertThat(result).matches(sqlResultDescriptorForResource("com/facebook/presto/tests/jdbc/get_nation_columns.result"));
}
else if (usingTeradataJdbc4Driver(connection)) {
assertThat(result).matches(sqlResultDescriptorForResource("com/facebook/presto/tests/jdbc/get_nation_columns_simba4.result"));
}
else if (usingTeradataJdbcDriver(connection)) {
assertThat(result).matches(sqlResultDescriptorForResource("com/facebook/presto/tests/jdbc/get_nation_columns_simba.result"));
}
else {
throw new IllegalStateException();
}
}
@Test(groups = JDBC)
@Requires(ImmutableNationTable.class)
public void shouldGetTableTypes()
throws SQLException
{
QueryResult result = QueryResult.forResultSet(metaData().getTableTypes());
assertThat(result).contains(row("TABLE"), row("VIEW"));
}
@Test(groups = {JDBC, SIMBA_JDBC})
public void testSqlEscapeFunctions()
throws SQLException
{
if (usingTeradataJdbcDriver(connection)) {
// These functions, which are defined in the ODBC standard, are implemented within
// the Simba JDBC and ODBC drivers. The drivers translate them into equivalent Presto syntax.
// The translated SQL is executed by Presto. These tests do not make use of edge-case values or null
// values because those code paths are covered by other (non-Simba specifc) tests.
assertThat(query("select {fn char(40)}")).containsExactly(row("("));
assertThat(query("select {fn convert('2016-10-10', SQL_DATE)}")).containsExactly(row(Date.valueOf("2016-10-10")));
// This translates to: SELECT cast('1234.567' as DECIMAL).
// When casting to DECIMAL without parameters, Presto rounds to the nearest integer value.
assertThat(query("select {fn convert('1234.567', SQL_DECIMAL)}")).containsExactly(row(new BigDecimal(1235)));
assertThat(query("select {fn convert('123456', SQL_INTEGER)}")).containsExactly(row(123456));
assertThat(query("select {fn convert('123abcd', SQL_VARBINARY)}")).containsExactly(row("123abcd".getBytes()));
assertThat(query("select {fn dayofmonth(date '2016-10-20')}")).containsExactly(row(20));
assertThat(query("select {fn dayofweek(date '2016-10-20')}")).containsExactly(row(5));
assertThat(query("select {fn dayofyear(date '2016-10-20')}")).containsExactly(row(294));
assertThat(query("select {fn ifnull({fn ifnull(null, null)}, '2')}")).containsExactly(row("2"));
assertThat(query("select {fn ifnull('abc', '2')}")).containsExactly(row("abc"));
assertThat(query("select {fn ifnull(null, '2')}")).containsExactly(row("2"));
assertThat(query("select {fn lcase('ABC def 123')}")).containsExactly(row("abc def 123"));
assertThat(query("select {fn left('abc def', 2)}")).containsExactly(row("ab"));
assertThat(query("select {fn locate('d', 'abc def')}")).containsExactly(row(5));
assertThat(query("select {fn log(5)}")).containsExactly(row(1.60943791243));
assertThat(query("select {fn right('abc def', 2)}")).containsExactly(row("ef"));
assertThat(query("select {fn substring('abc def', 2)}")).containsExactly(row("bc def"));
assertThat(query("select {fn substring('abc def', 2, 2)}")).containsExactly(row("bc"));
assertThat(query("select {fn timestampadd(SQL_TSI_DAY, 21, date '2001-01-01')}")).containsExactly(row(Date.valueOf("2001-01-22")));
assertThat(query("select {fn timestampdiff(SQL_TSI_DAY,date '2001-01-01',date '2002-01-01')}")).containsExactly(row(365));
assertThat(query("select {fn ucase('ABC def 123')}")).containsExactly(row("ABC DEF 123"));
}
else {
LOGGER.warn("testSqlEscapeFunctions() only applies to TeradataJdbcDriver");
}
}
@Test(groups = JDBC)
public void testSessionProperties()
throws SQLException
{
final String distributedJoin = "distributed_join";
assertThat(getSessionProperty(connection, distributedJoin)).isEqualTo(TRUE.toString());
setSessionProperty(connection, distributedJoin, FALSE.toString());
assertThat(getSessionProperty(connection, distributedJoin)).isEqualTo(FALSE.toString());
resetSessionProperty(connection, distributedJoin);
assertThat(getSessionProperty(connection, distributedJoin)).isEqualTo(TRUE.toString());
}
private QueryResult queryResult(Statement statement, String query)
throws SQLException
{
return QueryResult.forResultSet(statement.executeQuery(query));
}
private DatabaseMetaData metaData()
throws SQLException
{
return connection.getMetaData();
}
}