/**
* Get list of ColumnInfos that contain Column Name and its associated PDataType for an import.
* The supplied list of columns can be null -- if it is non-null, it represents a user-supplied
* list of columns to be imported.
*
* @param conn Phoenix connection from which metadata will be read
* @param tableName Phoenix table name whose columns are to be checked. Can include a schema name
* @param columns user-supplied list of import columns, can be null
* @param strict if true, an exception will be thrown if unknown columns are supplied
*/
public static List<ColumnInfo> generateColumnInfo(
Connection conn, String tableName, List<String> columns, boolean strict) throws SQLException {
Map<String, Integer> columnNameToTypeMap = Maps.newLinkedHashMap();
Set<String> ambiguousColumnNames = new HashSet<String>();
Map<String, Integer> fullColumnNameToTypeMap = Maps.newLinkedHashMap();
DatabaseMetaData dbmd = conn.getMetaData();
int unfoundColumnCount = 0;
// TODO: escape wildcard characters here because we don't want that
// behavior here
String escapedTableName = StringUtil.escapeLike(tableName);
String[] schemaAndTable = escapedTableName.split("\\.");
ResultSet rs = null;
try {
rs =
dbmd.getColumns(
null,
(schemaAndTable.length == 1 ? "" : schemaAndTable[0]),
(schemaAndTable.length == 1 ? escapedTableName : schemaAndTable[1]),
null);
while (rs.next()) {
String colName = rs.getString(QueryUtil.COLUMN_NAME_POSITION);
String colFam = rs.getString(QueryUtil.COLUMN_FAMILY_POSITION);
// use family qualifier, if available, otherwise, use column name
String fullColumn = (colFam == null ? colName : String.format("%s.%s", colFam, colName));
String sqlTypeName = rs.getString(QueryUtil.DATA_TYPE_NAME_POSITION);
// allow for both bare and family qualified names.
if (columnNameToTypeMap.keySet().contains(colName)) {
ambiguousColumnNames.add(colName);
}
columnNameToTypeMap.put(colName, PDataType.fromSqlTypeName(sqlTypeName).getSqlType());
fullColumnNameToTypeMap.put(
fullColumn, PDataType.fromSqlTypeName(sqlTypeName).getSqlType());
}
if (columnNameToTypeMap.isEmpty()) {
throw new IllegalArgumentException("Table " + tableName + " not found");
}
} finally {
if (rs != null) {
rs.close();
}
}
List<ColumnInfo> columnInfoList = Lists.newArrayList();
Set<String> unresolvedColumnNames = new TreeSet<String>();
if (columns == null) {
// use family qualified names by default, if no columns are specified.
for (Map.Entry<String, Integer> entry : fullColumnNameToTypeMap.entrySet()) {
columnInfoList.add(new ColumnInfo(entry.getKey(), entry.getValue()));
}
} else {
// Leave "null" as indication to skip b/c it doesn't exist
for (int i = 0; i < columns.size(); i++) {
String columnName = columns.get(i).trim();
Integer sqlType = null;
if (fullColumnNameToTypeMap.containsKey(columnName)) {
sqlType = fullColumnNameToTypeMap.get(columnName);
} else if (columnNameToTypeMap.containsKey(columnName)) {
if (ambiguousColumnNames.contains(columnName)) {
unresolvedColumnNames.add(columnName);
}
// fall back to bare column name.
sqlType = columnNameToTypeMap.get(columnName);
}
if (unresolvedColumnNames.size() > 0) {
StringBuilder exceptionMessage = new StringBuilder();
boolean first = true;
exceptionMessage.append(
"Unable to resolve these column names to a single column family:\n");
for (String col : unresolvedColumnNames) {
if (first) first = false;
else exceptionMessage.append(",");
exceptionMessage.append(col);
}
exceptionMessage.append("\nAvailable columns with column families:\n");
first = true;
for (String col : fullColumnNameToTypeMap.keySet()) {
if (first) first = false;
else exceptionMessage.append(",");
exceptionMessage.append(col);
}
throw new SQLException(exceptionMessage.toString());
}
if (sqlType == null) {
if (strict) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(columnName)
.setTableName(tableName)
.build()
.buildException();
}
unfoundColumnCount++;
} else {
columnInfoList.add(new ColumnInfo(columnName, sqlType));
}
}
if (unfoundColumnCount == columns.size()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(Arrays.toString(columns.toArray(new String[0])))
.setTableName(tableName)
.build()
.buildException();
}
}
return columnInfoList;
}
@Test
public void testAllDropParentTableWithCascadeWithMultipleTenantTablesAndIndexes()
throws Exception {
// Create a second tenant table
createTestTable(PHOENIX_JDBC_TENANT_SPECIFIC_URL2, TENANT_TABLE_DDL, null, nextTimestamp());
// TODO Create some tenant specific table indexes
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = null;
Connection connTenant1 = null;
Connection connTenant2 = null;
try {
conn = DriverManager.getConnection(getUrl(), props);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs =
meta.getSuperTables(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME) + "%");
assertTrue(rs.next());
assertEquals(TENANT_ID2, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(TENANT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(TENANT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(
TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(
PARENT_TABLE_NAME_NO_TENANT_TYPE_ID,
rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertFalse(rs.next());
rs.close();
conn.close();
// Drop Parent Table
conn.createStatement().executeUpdate("DROP TABLE " + PARENT_TABLE_NAME + " CASCADE");
// Validate Tenant Views are dropped
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
connTenant1 = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
validateTenantViewIsDropped(connTenant1);
connTenant2 = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL2, props);
validateTenantViewIsDropped(connTenant2);
// Validate Tenant Metadata is gone for the Tenant Table TENANT_TABLE_NAME
conn = DriverManager.getConnection(getUrl(), props);
meta = conn.getMetaData();
rs = meta.getSuperTables(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME) + "%");
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(
TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(
PARENT_TABLE_NAME_NO_TENANT_TYPE_ID,
rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertFalse(rs.next());
rs.close();
} finally {
if (conn != null) {
conn.close();
}
if (connTenant1 != null) {
connTenant1.close();
}
if (connTenant2 != null) {
connTenant2.close();
}
}
}
@Test
public void testTableMetadataScan() throws Exception {
// create a tenant table with same name for a different tenant to make sure we are not picking
// it up in metadata scans for TENANT_ID
String tenantId2 = "tenant2";
String secondTenatConnectionURL =
PHOENIX_JDBC_TENANT_SPECIFIC_URL.replace(TENANT_ID, tenantId2);
String tenantTable2 = TENANT_TABLE_NAME + "2";
createTestTable(
secondTenatConnectionURL,
TENANT_TABLE_DDL.replace(TENANT_TABLE_NAME, tenantTable2),
null,
nextTimestamp(),
false);
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
// empty string means global tenant id
// make sure connections w/o tenant id only see non-tenant-specific tables, both SYSTEM and
// USER
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables("", null, null, null);
assertTrue(rs.next());
assertTableMetaData(rs, SYSTEM_CATALOG_SCHEMA, SYSTEM_CATALOG_TABLE, SYSTEM);
assertTrue(rs.next());
assertTableMetaData(rs, SYSTEM_CATALOG_SCHEMA, TYPE_SEQUENCE, SYSTEM);
assertTrue(rs.next());
assertTableMetaData(rs, null, PARENT_TABLE_NAME, TABLE);
assertTrue(rs.next());
assertTableMetaData(rs, null, PARENT_TABLE_NAME_NO_TENANT_TYPE_ID, TABLE);
assertFalse(rs.next());
// make sure connections w/o tenant id only see non-tenant-specific columns
rs = meta.getColumns("", null, null, null);
while (rs.next()) {
assertNotEquals(TENANT_TABLE_NAME, rs.getString("TABLE_NAME"));
assertNotEquals(tenantTable2, rs.getString("TABLE_NAME"));
}
// null catalog means across all tenant_ids
rs = meta.getSuperTables(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME) + "%");
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(TENANT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(
TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(
PARENT_TABLE_NAME_NO_TENANT_TYPE_ID,
rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertTrue(rs.next());
assertEquals(tenantId2, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(tenantTable2, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertFalse(rs.next());
conn.close();
// Global connection sees all tenant tables
conn = DriverManager.getConnection(getUrl());
rs = conn.getMetaData().getSuperTables(TENANT_ID, null, null);
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(TENANT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(PARENT_TABLE_NAME, rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertEquals(
TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_NAME));
assertEquals(
PARENT_TABLE_NAME_NO_TENANT_TYPE_ID,
rs.getString(PhoenixDatabaseMetaData.SUPERTABLE_NAME));
assertFalse(rs.next());
rs = conn.getMetaData().getCatalogs();
assertTrue(rs.next());
assertEquals(TENANT_ID, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertTrue(rs.next());
assertEquals(tenantId2, rs.getString(PhoenixDatabaseMetaData.TABLE_CAT));
assertFalse(rs.next());
} finally {
props.clear();
conn.close();
}
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
try {
// make sure tenant-specific connections only see their own tables and the global tables
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null, null, null, null);
assertTrue(rs.next());
assertTableMetaData(
rs,
PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA,
PhoenixDatabaseMetaData.SYSTEM_CATALOG_TABLE,
PTableType.SYSTEM);
assertTrue(rs.next());
assertTableMetaData(
rs,
PhoenixDatabaseMetaData.SYSTEM_CATALOG_SCHEMA,
PhoenixDatabaseMetaData.TYPE_SEQUENCE,
PTableType.SYSTEM);
assertTrue(rs.next());
assertTableMetaData(rs, null, PARENT_TABLE_NAME, PTableType.TABLE);
assertTrue(rs.next());
assertTableMetaData(rs, null, PARENT_TABLE_NAME_NO_TENANT_TYPE_ID, PTableType.TABLE);
assertTrue(rs.next());
assertTableMetaData(rs, null, TENANT_TABLE_NAME, PTableType.VIEW);
assertTrue(rs.next());
assertTableMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, PTableType.VIEW);
assertFalse(rs.next());
// make sure tenants see parent table's columns and their own
rs = meta.getColumns(null, null, StringUtil.escapeLike(TENANT_TABLE_NAME) + "%", null);
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "user");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_type_id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_col");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "user");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "tenant_id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME_NO_TENANT_TYPE_ID, "tenant_col");
assertFalse(rs.next());
} finally {
conn.close();
}
}
/**
* Get list of ColumnInfos that contain Column Name and its associated PDataType for an import.
* The supplied list of columns can be null -- if it is non-null, it represents a user-supplied
* list of columns to be imported.
*
* @param conn Phoenix connection from which metadata will be read
* @param tableName Phoenix table name whose columns are to be checked. Can include a schema name
* @param columns user-supplied list of import columns, can be null
* @param strict if true, an exception will be thrown if unknown columns are supplied
*/
public static List<ColumnInfo> generateColumnInfo(
Connection conn, String tableName, List<String> columns, boolean strict) throws SQLException {
Map<String, Integer> columnNameToTypeMap = Maps.newLinkedHashMap();
DatabaseMetaData dbmd = conn.getMetaData();
int unfoundColumnCount = 0;
// TODO: escape wildcard characters here because we don't want that
// behavior here
String escapedTableName = StringUtil.escapeLike(tableName);
String[] schemaAndTable = escapedTableName.split("\\.");
ResultSet rs = null;
try {
rs =
dbmd.getColumns(
null,
(schemaAndTable.length == 1 ? "" : schemaAndTable[0]),
(schemaAndTable.length == 1 ? escapedTableName : schemaAndTable[1]),
null);
while (rs.next()) {
String sqlTypeName = rs.getString(QueryUtil.DATA_TYPE_NAME_POSITION);
columnNameToTypeMap.put(
rs.getString(QueryUtil.COLUMN_NAME_POSITION),
PDataType.fromSqlTypeName(sqlTypeName).getSqlType());
}
if (columnNameToTypeMap.isEmpty()) {
throw new IllegalArgumentException("Table " + tableName + " not found");
}
} finally {
if (rs != null) {
rs.close();
}
}
List<ColumnInfo> columnInfoList = Lists.newArrayList();
if (columns == null) {
for (Map.Entry<String, Integer> entry : columnNameToTypeMap.entrySet()) {
columnInfoList.add(new ColumnInfo(entry.getKey(), entry.getValue()));
}
} else {
// Leave "null" as indication to skip b/c it doesn't exist
for (int i = 0; i < columns.size(); i++) {
String columnName = columns.get(i).trim();
Integer sqlType = columnNameToTypeMap.get(columnName);
if (sqlType == null) {
if (strict) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(columnName)
.setTableName(tableName)
.build()
.buildException();
}
unfoundColumnCount++;
} else {
columnInfoList.add(new ColumnInfo(columnName, sqlType));
}
}
if (unfoundColumnCount == columns.size()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(Arrays.toString(columns.toArray(new String[0])))
.setTableName(tableName)
.build()
.buildException();
}
}
return columnInfoList;
}