/**
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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.apache.tajo.jdbc;
import com.google.common.collect.Sets;
import org.apache.tajo.QueryTestCaseBase;
import org.apache.tajo.TajoConstants;
import org.apache.tajo.common.type.TajoTypeUtil;
import org.apache.tajo.schema.IdentifierUtil;
import org.apache.tajo.util.TUtil;
import org.junit.BeforeClass;
import org.junit.Test;
import java.net.InetSocketAddress;
import java.sql.*;
import java.util.*;
import static org.junit.Assert.*;
public class TestTajoDatabaseMetaData extends QueryTestCaseBase {
private static InetSocketAddress tajoMasterAddress;
@BeforeClass
public static void setUp() throws Exception {
tajoMasterAddress = testingCluster.getMaster().getTajoMasterClientService().getBindAddress();
Class.forName("org.apache.tajo.jdbc.TajoDriver").newInstance();
}
public static List<String> getListFromResultSet(ResultSet resultSet, String columnName) throws SQLException {
List<String> list = new ArrayList<>();
while(resultSet.next()) {
list.add(resultSet.getString(columnName));
}
return list;
}
@Test
public void testSetAndGetCatalogAndSchema() throws Exception {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
Connection conn = DriverManager.getConnection(connUri);
assertDatabaseNotExists("jdbc_test1");
PreparedStatement pstmt = conn.prepareStatement("CREATE DATABASE jdbc_test1");
pstmt.executeUpdate();
assertDatabaseExists("jdbc_test1");
pstmt.close();
if (!testingCluster.isHiveCatalogStoreRunning()) {
assertDatabaseNotExists("Jdbc_Test2");
pstmt = conn.prepareStatement("CREATE DATABASE \"Jdbc_Test2\"");
pstmt.executeUpdate();
assertDatabaseExists("Jdbc_Test2");
pstmt.close();
}
conn.setCatalog("jdbc_test1");
assertEquals("jdbc_test1", conn.getCatalog());
if (!testingCluster.isHiveCatalogStoreRunning()) {
conn.setCatalog("Jdbc_Test2");
assertEquals("Jdbc_Test2", conn.getCatalog());
}
conn.setCatalog("jdbc_test1");
assertEquals("jdbc_test1", conn.getCatalog());
ResultSet resultSet = conn.getMetaData().getSchemas();
assertResultSet(resultSet, "getSchemas1.result");
resultSet.close();
resultSet = conn.getMetaData().getSchemas("jdbc_test1", "%");
assertResultSet(resultSet, "getSchemas2.result");
resultSet.close();
resultSet = conn.getMetaData().getTableTypes();
assertResultSet(resultSet, "getTableTypes.result");
resultSet.close();
conn.setCatalog(TajoConstants.DEFAULT_DATABASE_NAME);
pstmt = conn.prepareStatement("DROP DATABASE jdbc_test1");
pstmt.executeUpdate();
pstmt.close();
if (!testingCluster.isHiveCatalogStoreRunning()) {
pstmt = conn.prepareStatement("DROP DATABASE \"Jdbc_Test2\"");
pstmt.executeUpdate();
pstmt.close();
}
conn.close();
}
@Test
public void testGetCatalogsAndTables() throws Exception {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
Connection defaultConnect = DriverManager.getConnection(connUri);
DatabaseMetaData dbmd = defaultConnect.getMetaData();
List<String> existingDatabases = getListFromResultSet(dbmd.getCatalogs(), "TABLE_CAT");
// create database "jdbc_test1" and its tables
assertDatabaseNotExists("jdbc_test3");
PreparedStatement pstmt = defaultConnect.prepareStatement("CREATE DATABASE jdbc_test3");
pstmt.executeUpdate();
assertDatabaseExists("jdbc_test3");
pstmt.close();
pstmt = defaultConnect.prepareStatement("CREATE TABLE jdbc_test3.table1 (age int)");
pstmt.executeUpdate();
pstmt.close();
pstmt = defaultConnect.prepareStatement("CREATE TABLE jdbc_test3.table2 (age int)");
pstmt.executeUpdate();
pstmt.close();
if (!testingCluster.isHiveCatalogStoreRunning()) {
// create database "jdbc_test2" and its tables
assertDatabaseNotExists("Jdbc_Test4");
pstmt = defaultConnect.prepareStatement("CREATE DATABASE \"Jdbc_Test4\"");
pstmt.executeUpdate();
assertDatabaseExists("Jdbc_Test4");
pstmt.close();
pstmt = defaultConnect.prepareStatement("CREATE TABLE \"Jdbc_Test4\".table3 (age int)");
pstmt.executeUpdate();
pstmt.close();
pstmt = defaultConnect.prepareStatement("CREATE TABLE \"Jdbc_Test4\".table4 (age int)");
pstmt.executeUpdate();
pstmt.close();
}
// verify getCatalogs()
dbmd = defaultConnect.getMetaData();
List<String> newDatabases = getListFromResultSet(dbmd.getCatalogs(), "TABLE_CAT");
newDatabases.removeAll(existingDatabases);
if (!testingCluster.isHiveCatalogStoreRunning()) {
assertEquals(2, newDatabases.size());
} else {
assertEquals(1, newDatabases.size());
}
assertTrue(newDatabases.contains("jdbc_test3"));
if (!testingCluster.isHiveCatalogStoreRunning()) {
assertTrue(newDatabases.contains("Jdbc_Test4"));
}
// verify getTables()
ResultSet res = defaultConnect.getMetaData().getTables("jdbc_test3", null, null, null);
assertResultSet(res, "getTables1.result");
res.close();
if (!testingCluster.isHiveCatalogStoreRunning()) {
res = defaultConnect.getMetaData().getTables("Jdbc_Test4", null, null, null);
assertResultSet(res, "getTables2.result");
res.close();
}
defaultConnect.close();
// jdbc1_test database connection test
String jdbcTest1ConnUri =
TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(), "jdbc_test3");
Connection jdbcTest1Conn = DriverManager.getConnection(jdbcTest1ConnUri);
assertEquals("jdbc_test3", jdbcTest1Conn.getCatalog());
jdbcTest1Conn.close();
client.selectDatabase("default");
executeString("DROP TABLE jdbc_test3.table1");
executeString("DROP TABLE jdbc_test3.table2");
executeString("DROP DATABASE jdbc_test3");
if (!testingCluster.isHiveCatalogStoreRunning()) {
String jdbcTest2ConnUri =
TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(), "Jdbc_Test4");
Connection jdbcTest2Conn = DriverManager.getConnection(jdbcTest2ConnUri);
assertEquals("Jdbc_Test4", jdbcTest2Conn.getCatalog());
jdbcTest2Conn.close();
client.selectDatabase("default");
executeString("DROP TABLE \"Jdbc_Test4\".table3");
executeString("DROP TABLE \"Jdbc_Test4\".table4");
executeString("DROP DATABASE \"Jdbc_Test4\"");
}
}
@Test
public void testGetTablesWithPattern() throws Exception {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
Connection conn = DriverManager.getConnection(connUri);
Map<String,List<String>> tables = new HashMap<>();
assertDatabaseNotExists("db_1");
executeString("CREATE DATABASE db_1");
assertDatabaseExists("db_1");
for (int i = 0; i < 3; i++) {
String tableName = "tb_" + i;
TUtil.putToNestedList(tables, "db_1", tableName);
executeString("CREATE TABLE db_1." + tableName + " (age int)");
}
for (int i = 0; i < 3; i++) {
String tableName = "table_" + i + "_ptn";
TUtil.putToNestedList(tables, "db_1", tableName);
executeString("CREATE TABLE db_1." + tableName + " (age int)");
}
assertDatabaseNotExists("db_2");
executeString("CREATE DATABASE db_2");
assertDatabaseExists("db_2");
for (int i = 0; i < 3; i++) {
String tableName = "tb_" + i;
TUtil.putToNestedList(tables, "db_2", tableName);
executeString("CREATE TABLE db_2." + tableName + " (age int)");
}
for (int i = 0; i < 3; i++) {
String tableName = "table_" + i + "_ptn";
TUtil.putToNestedList(tables, "db_2", tableName);
executeString("CREATE TABLE db_2." + tableName + " (age int)");
}
// all wildcard test
Set<String> tableList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getTables("db_2", null, "%", null), "TABLE_NAME"));
assertEquals(Sets.newHashSet(tables.get("db_2")), tableList);
// leading wildcard test
tableList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getTables("db_2", null, "%_ptn", null), "TABLE_NAME"));
assertEquals(Sets.newHashSet("table_0_ptn", "table_1_ptn", "table_2_ptn"), tableList);
// tailing wildcard test
tableList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getTables("db_2", null, "tb_%", null), "TABLE_NAME"));
assertEquals(Sets.newHashSet("tb_0", "tb_1", "tb_2"), tableList);
ResultSet resultSet = conn.getMetaData().getTables(null, null, "tb\\_%", null);
int i = 0;
while(resultSet.next()) {
tables.get(resultSet.getString("TABLE_CAT")).contains(resultSet.getString("TABLE_NAME"));
i++;
}
assertEquals(6, i);
executeString("DROP DATABASE db_1");
executeString("DROP DATABASE db_2");
}
private static String getTestColName(String dbName, String tableName, int i) {
if (i % 2 == 1) {
return IdentifierUtil.denormalizeIdentifier(dbName + "_" + tableName + "_col") + " int";
} else {
return IdentifierUtil.denormalizeIdentifier(dbName + "_" + tableName + "_COL") + " int";
}
}
@Test
public void testGetColumnsWithPattern() throws Exception {
if (!testingCluster.isHiveCatalogStoreRunning()) {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
Connection conn = DriverManager.getConnection(connUri);
// Below creates the following 12 tables
// db<i>.tb<j>, i = {1,2}, 0 <= j < 2
// db<i>.table_<j>, i = {1,2}, 0 <= j < 2
Map<String,List<String>> tables = new HashMap<>();
for (int j = 1; j <= 2; j++) {
String dbName = "db" + j;
assertDatabaseNotExists(dbName);
executeString("CREATE DATABASE " + dbName).close();
assertDatabaseExists(dbName);
for (int i = 3; i < 6; i++) {
String tableName = "tb" + i;
if (i % 2 == 0) {
tableName = tableName.toUpperCase();
}
TUtil.putToNestedList(tables, dbName, tableName);
executeString("CREATE TABLE " + dbName + "." + IdentifierUtil.denormalizeIdentifier(tableName) +
" (" + getTestColName(dbName, tableName, 1) +
") PARTITION BY COLUMN (" + getTestColName(dbName, tableName, 2) + ")").close();
assertTableExists(dbName + "." + tableName);
}
for (int i = 3; i < 6; i++) {
String tableName = "table" + i;
if (i % 2 == 0) {
tableName = tableName.toUpperCase();
}
TUtil.putToNestedList(tables, dbName, tableName);
executeString("CREATE TABLE " + dbName + "." + IdentifierUtil.denormalizeIdentifier(tableName) +
" (" + getTestColName(dbName, tableName, 1) +
") PARTITION BY COLUMN (" + getTestColName(dbName, tableName, 2) + ")").close();
assertTableExists(dbName + "." + tableName);
}
}
// all wildcard test on columns
Set<String> columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "tb3", "%"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db2_tb3_col", "db2_tb3_COL"), columnList);
// tailing wildcard + case sensitive test on columns
columnList = Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "tb3", "%col"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db2_tb3_col"), columnList);
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "tb3", "%COL"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db2_tb3_COL"), columnList);
// tailing wildcard test on columns
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "tb3", "db2\\_tb3\\_%"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db2_tb3_col", "db2_tb3_COL"), columnList);
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "%", "db2\\_tb3\\_%"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db2_tb3_col", "db2_tb3_COL"), columnList);
// leading wildcard test on tables
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db1", null, "%3", "%"),
"COLUMN_NAME"));
assertEquals(
Sets.newHashSet(
"db1_tb3_col", "db1_tb3_COL",
"db1_table3_col", "db1_table3_COL"),
columnList);
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "%3", "%"),
"COLUMN_NAME"));
assertEquals(
Sets.newHashSet(
"db2_tb3_col", "db2_tb3_COL",
"db2_table3_col", "db2_table3_COL"),
columnList);
// tailing wildcard + case sensitive test on tables
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "TABLE%", "%"),
"COLUMN_NAME"));
assertEquals(
Sets.newHashSet(
"db2_TABLE4_col", "db2_TABLE4_COL"), columnList);
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "TABLE4", "%"),
"COLUMN_NAME"));
assertEquals(
Sets.newHashSet(
"db2_TABLE4_col", "db2_TABLE4_COL"),
columnList);
// tailing wildcard test on tables
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns("db2", null, "table%", "%"),
"COLUMN_NAME"));
assertEquals(
Sets.newHashSet(
"db2_table3_col", "db2_table3_COL",
"db2_table5_col", "db2_table5_COL"),
columnList);
// wildcard test on database
columnList =
Sets.newHashSet(getListFromResultSet(conn.getMetaData().getColumns(null, null, "%3", "db1_tb3%"),
"COLUMN_NAME"));
assertEquals(Sets.newHashSet("db1_tb3_col", "db1_tb3_COL"), columnList);
executeString("DROP DATABASE db1");
executeString("DROP DATABASE db2");
}
}
@Test
public void testEmptyMetaInfo() throws Exception {
if (!testingCluster.isHiveCatalogStoreRunning()) {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
try (Connection conn = DriverManager.getConnection(connUri)) {
DatabaseMetaData meta = conn.getMetaData();
ResultSet res = meta.getProcedures(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getProcedureColumns(null, null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getUDTs(null, null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getColumnPrivileges(null, null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getTablePrivileges(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getBestRowIdentifier(null, null, null, 0, false);
assertNotNull(res);
assertFalse(res.next());
res = meta.getVersionColumns(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getPrimaryKeys(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getImportedKeys(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getExportedKeys(null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getCrossReference(null, null, null, null, null, null);
assertNotNull(res);
assertFalse(res.next());
res = meta.getIndexInfo(null, null, null, false, false);
assertNotNull(res);
assertFalse(res.next());
res = meta.getClientInfoProperties();
assertNotNull(res);
assertFalse(res.next());
}
}
}
@Test
public void testGetTypeInfo() throws Exception {
if (!testingCluster.isHiveCatalogStoreRunning()) {
String connUri = TestTajoJdbc.buildConnectionUri(tajoMasterAddress.getHostName(), tajoMasterAddress.getPort(),
TajoConstants.DEFAULT_DATABASE_NAME);
try (Connection conn = DriverManager.getConnection(connUri)) {
DatabaseMetaData meta = conn.getMetaData();
ResultSet res = meta.getTypeInfo();
assertNotNull(res);
int numTypes = 0;
String[] columnNames = {"TYPE_NAME", "DATA_TYPE", "PRECISION", "LITERAL_PREFIX", "LITERAL_SUFFIX",
"CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE",
"FIXED_PREC_SCALE", "AUTO_INCREMENT", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE",
"SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX"};
while (res.next()) {
for (int i = 0; i < columnNames.length; i++) {
Object value = res.getObject(columnNames[i]);
if (i == 15 || i == 16) {
assertNull(value);
} else {
assertNotNull(value);
}
}
numTypes++;
}
assertEquals(numTypes, TajoTypeUtil.getTypeInfos().size());
}
}
}
}