Modify phoenix IT tests to extend BaseHBaseManagedTimeTableReuseIT (Prakul Agarwal, Samarth Jain)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/14dab2f4 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/14dab2f4 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/14dab2f4 Branch: refs/heads/calcite Commit: 14dab2f40df0d09f48f9cabbaea897009f635914 Parents: 64cb9b0 Author: Samarth <samarth.j...@salesforce.com> Authored: Fri Aug 26 16:15:57 2016 -0700 Committer: Samarth <samarth.j...@salesforce.com> Committed: Fri Aug 26 16:15:57 2016 -0700 ---------------------------------------------------------------------- .../end2end/AlterMultiTenantTableWithViews.java | 701 ------------------ .../AlterMultiTenantTableWithViewsIT.java | 704 +++++++++++++++++++ .../phoenix/end2end/AlterTableWithViewsIT.java | 193 ++--- .../phoenix/end2end/AppendOnlySchemaIT.java | 115 ++- .../phoenix/end2end/ArithmeticQueryIT.java | 20 +- .../phoenix/end2end/ArrayAppendFunctionIT.java | 186 ++--- .../phoenix/end2end/ArrayConcatFunctionIT.java | 160 +++-- .../phoenix/end2end/ArrayPrependFunctionIT.java | 220 +++--- .../phoenix/end2end/AutoPartitionViewsIT.java | 131 ++-- .../BaseHBaseManagedTimeTableReuseIT.java | 5 + .../org/apache/phoenix/end2end/BaseQueryIT.java | 6 +- .../end2end/BaseTenantSpecificViewIndexIT.java | 80 ++- .../apache/phoenix/end2end/BinaryRowKeyIT.java | 33 +- .../phoenix/end2end/CSVCommonsLoaderIT.java | 80 ++- .../phoenix/end2end/CoalesceFunctionIT.java | 104 +-- .../end2end/ColumnProjectionOptimizationIT.java | 17 +- .../end2end/CountDistinctCompressionIT.java | 3 +- .../phoenix/end2end/CustomEntityDataIT.java | 2 +- .../org/apache/phoenix/end2end/DateTimeIT.java | 146 ++-- .../org/apache/phoenix/end2end/DeleteIT.java | 138 ++-- .../apache/phoenix/end2end/DerivedTableIT.java | 3 +- .../phoenix/end2end/DisableLocalIndexIT.java | 18 +- .../apache/phoenix/end2end/DistinctCountIT.java | 4 +- .../apache/phoenix/end2end/DynamicColumnIT.java | 39 +- .../phoenix/end2end/EncodeFunctionIT.java | 42 +- .../phoenix/end2end/EvaluationOfORIT.java | 13 +- .../phoenix/end2end/ExecuteStatementsIT.java | 42 +- .../phoenix/end2end/ExpFunctionEnd2EndIT.java | 20 +- .../phoenix/end2end/ExtendedQueryExecIT.java | 12 +- .../apache/phoenix/end2end/FunkyNamesIT.java | 2 +- .../apache/phoenix/end2end/GroupByCaseIT.java | 217 +++--- .../apache/phoenix/end2end/HashJoinMoreIT.java | 51 +- .../org/apache/phoenix/end2end/InListIT.java | 41 +- .../phoenix/end2end/InMemoryOrderByIT.java | 2 +- .../apache/phoenix/end2end/InstrFunctionIT.java | 48 +- .../org/apache/phoenix/end2end/IsNullIT.java | 37 +- .../org/apache/phoenix/end2end/KeyOnlyIT.java | 6 +- .../phoenix/end2end/LastValueFunctionIT.java | 194 ++--- .../phoenix/end2end/LnLogFunctionEnd2EndIT.java | 15 +- .../org/apache/phoenix/end2end/MapReduceIT.java | 61 +- .../phoenix/end2end/MappingTableDataTypeIT.java | 2 +- .../phoenix/end2end/ModulusExpressionIT.java | 45 +- .../phoenix/end2end/MultiCfQueryExecIT.java | 2 +- .../end2end/NamespaceSchemaMappingIT.java | 4 +- .../phoenix/end2end/NativeHBaseTypesIT.java | 2 +- .../org/apache/phoenix/end2end/OrderByIT.java | 74 +- .../phoenix/end2end/ParallelIteratorsIT.java | 6 +- .../apache/phoenix/end2end/PercentileIT.java | 93 +-- .../phoenix/end2end/PhoenixRuntimeIT.java | 42 +- .../phoenix/end2end/ProductMetricsIT.java | 2 +- .../end2end/QueryDatabaseMetaDataIT.java | 49 +- .../phoenix/end2end/QueryExecWithoutSCNIT.java | 6 +- .../phoenix/end2end/QueryWithLimitIT.java | 4 +- .../phoenix/end2end/ReadIsolationLevelIT.java | 2 +- .../end2end/RegexpReplaceFunctionIT.java | 17 +- .../phoenix/end2end/RegexpSubstrFunctionIT.java | 12 +- .../apache/phoenix/end2end/ReverseScanIT.java | 58 +- .../phoenix/end2end/RowValueConstructorIT.java | 35 +- .../end2end/SkipScanAfterManualSplitIT.java | 44 +- .../apache/phoenix/end2end/SkipScanQueryIT.java | 156 ++-- .../phoenix/end2end/SortMergeJoinMoreIT.java | 66 +- .../phoenix/end2end/SpillableGroupByIT.java | 2 +- .../phoenix/end2end/SpooledTmpFileDeleteIT.java | 20 +- .../phoenix/end2end/SqrtFunctionEnd2EndIT.java | 25 +- .../org/apache/phoenix/end2end/StddevIT.java | 18 +- .../org/apache/phoenix/end2end/StringIT.java | 66 +- .../org/apache/phoenix/end2end/SubqueryIT.java | 5 +- .../apache/phoenix/end2end/TenantIdTypeIT.java | 4 +- .../end2end/TenantSpecificViewIndexIT.java | 48 +- .../phoenix/end2end/ToCharFunctionIT.java | 7 +- .../phoenix/end2end/ToDateFunctionIT.java | 9 +- .../java/org/apache/phoenix/end2end/TopNIT.java | 8 +- .../phoenix/end2end/TruncateFunctionIT.java | 2 +- .../org/apache/phoenix/end2end/UpgradeIT.java | 130 ++-- .../phoenix/end2end/UpsertBigValuesIT.java | 78 +- .../apache/phoenix/end2end/UpsertSelectIT.java | 18 +- .../apache/phoenix/end2end/UpsertValuesIT.java | 6 +- .../org/apache/phoenix/end2end/UseSchemaIT.java | 42 +- .../phoenix/end2end/VariableLengthPKIT.java | 38 +- .../phoenix/end2end/index/DropMetadataIT.java | 113 ++- .../index/GlobalIndexOptimizationIT.java | 175 ++--- .../phoenix/end2end/index/ImmutableIndexIT.java | 36 +- .../end2end/index/IndexExpressionIT.java | 327 +++++---- .../apache/phoenix/end2end/index/IndexIT.java | 85 ++- .../phoenix/end2end/index/IndexMetadataIT.java | 253 +++---- .../phoenix/end2end/index/LocalIndexIT.java | 125 +++- .../phoenix/end2end/index/MutableIndexIT.java | 94 ++- .../phoenix/end2end/index/SaltedIndexIT.java | 95 +-- .../phoenix/end2end/index/ViewIndexIT.java | 54 +- .../end2end/index/txn/MutableRollbackIT.java | 58 +- .../phoenix/end2end/index/txn/RollbackIT.java | 18 +- .../phoenix/end2end/salted/SaltedTableIT.java | 2 +- .../salted/SaltedTableUpsertSelectIT.java | 1 - .../salted/SaltedTableVarLengthRowKeyIT.java | 1 - .../phoenix/iterate/PhoenixQueryTimeoutIT.java | 3 - .../iterate/RoundRobinResultIteratorIT.java | 26 +- .../org/apache/phoenix/rpc/UpdateCacheIT.java | 26 +- .../phoenix/rpc/UpdateCacheWithScnIT.java | 2 +- .../apache/phoenix/trace/BaseTracingTestIT.java | 4 +- .../org/apache/phoenix/tx/TransactionIT.java | 246 ++++--- .../org/apache/phoenix/tx/TxCheckpointIT.java | 50 +- .../org/apache/phoenix/query/QueryServices.java | 2 +- .../phoenix/query/QueryServicesOptions.java | 4 +- .../phoenix/compile/WhereOptimizerTest.java | 10 +- .../java/org/apache/phoenix/query/BaseTest.java | 130 ++-- .../java/org/apache/phoenix/util/TestUtil.java | 23 +- .../org/apache/phoenix/flume/PhoenixSinkIT.java | 17 +- .../phoenix/flume/RegexEventSerializerIT.java | 16 +- .../apache/phoenix/pherf/ResultBaseTestIT.java | 4 +- .../apache/phoenix/pherf/SchemaReaderIT.java | 5 +- .../java/org/apache/phoenix/pig/BasePigIT.java | 6 +- .../phoenix/end2end/QueryServerBasicsIT.java | 2 +- pom.xml | 134 ++-- 113 files changed, 3974 insertions(+), 3331 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/14dab2f4/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViews.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViews.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViews.java deleted file mode 100644 index 2cdfe4a..0000000 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViews.java +++ /dev/null @@ -1,701 +0,0 @@ -/* - * 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.phoenix.end2end; - -import static org.apache.phoenix.query.QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT; -import static org.apache.phoenix.query.QueryConstants.DIVERGED_VIEW_BASE_COLUMN_COUNT; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertFalse; -import static org.junit.Assert.assertNotNull; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; - -import java.sql.Connection; -import java.sql.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.ResultSetMetaData; -import java.sql.SQLException; -import java.sql.Statement; -import java.util.List; -import java.util.Properties; - -import org.apache.hadoop.hbase.HTableDescriptor; -import org.apache.hadoop.hbase.util.Bytes; -import org.apache.phoenix.compile.QueryPlan; -import org.apache.phoenix.exception.SQLExceptionCode; -import org.apache.phoenix.jdbc.PhoenixConnection; -import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData; -import org.apache.phoenix.jdbc.PhoenixStatement; -import org.apache.phoenix.schema.ColumnNotFoundException; -import org.apache.phoenix.schema.PColumn; -import org.apache.phoenix.schema.PTable; -import org.apache.phoenix.schema.PTableKey; -import org.apache.phoenix.schema.PTableType; -import org.apache.phoenix.util.IndexUtil; -import org.apache.phoenix.util.PhoenixRuntime; -import org.apache.phoenix.util.SchemaUtil; -import org.junit.Test; - -import com.google.common.base.Objects; - -public class AlterMultiTenantTableWithViews extends BaseHBaseManagedTimeIT { - - private Connection getTenantConnection(String tenantId) throws Exception { - Properties tenantProps = new Properties(); - tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); - return DriverManager.getConnection(getUrl(), tenantProps); - } - - private static long getTableSequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { - PTable table = conn.getTable(new PTableKey(conn.getTenantId(), SchemaUtil.normalizeIdentifier(tableName))); - return table.getSequenceNumber(); - } - - private static short getMaxKeySequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { - PTable table = conn.getTable(new PTableKey(conn.getTenantId(), SchemaUtil.normalizeIdentifier(tableName))); - return SchemaUtil.getMaxKeySeq(table); - } - - private static void verifyNewColumns(ResultSet rs, String ... values) throws SQLException { - assertTrue(rs.next()); - int i = 1; - for (String value : values) { - assertEquals(value, rs.getString(i++)); - } - assertFalse(rs.next()); - assertEquals(values.length, i - 1); - } - - @Test - public void testAddDropColumnToBaseTablePropagatesToEntireViewHierarchy() throws Exception { - String baseTable = "testViewHierarchy"; - String view1 = "view1"; - String view2 = "view2"; - String view3 = "view3"; - String view4 = "view4"; - /* baseTable - / | \ - view1(tenant1) view3(tenant2) view4(global) - / - view2(tenant1) - */ - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - conn.createStatement().execute(baseTableDDL); - - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - String view1DDL = "CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - - String view2DDL = "CREATE VIEW " + view2 + " AS SELECT * FROM " + view1; - tenant1Conn.createStatement().execute(view2DDL); - } - - try (Connection tenant2Conn = getTenantConnection("tenant2")) { - String view3DDL = "CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable; - tenant2Conn.createStatement().execute(view3DDL); - } - - String view4DDL = "CREATE VIEW " + view4 + " AS SELECT * FROM " + baseTable; - conn.createStatement().execute(view4DDL); - - String alterBaseTable = "ALTER TABLE " + baseTable + " ADD V3 VARCHAR"; - conn.createStatement().execute(alterBaseTable); - - // verify that the column is visible to view4 - conn.createStatement().execute("SELECT V3 FROM " + view4); - - // verify that the column is visible to view1 and view2 - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - tenant1Conn.createStatement().execute("SELECT V3 from " + view1); - tenant1Conn.createStatement().execute("SELECT V3 from " + view2); - } - - // verify that the column is visible to view3 - try (Connection tenant2Conn = getTenantConnection("tenant2")) { - tenant2Conn.createStatement().execute("SELECT V3 from " + view3); - } - - alterBaseTable = "ALTER TABLE " + baseTable + " DROP COLUMN V1"; - conn.createStatement().execute(alterBaseTable); - - // verify that the column is not visible to view4 - try { - conn.createStatement().execute("SELECT V1 FROM " + view4); - fail(); - } catch (ColumnNotFoundException e) { - } - // verify that the column is not visible to view1 and view2 - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - try { - tenant1Conn.createStatement().execute("SELECT V1 from " + view1); - fail(); - } catch (ColumnNotFoundException e) { - } - try { - tenant1Conn.createStatement().execute("SELECT V1 from " + view2); - fail(); - } catch (ColumnNotFoundException e) { - } - } - - // verify that the column is not visible to view3 - try (Connection tenant2Conn = getTenantConnection("tenant2")) { - try { - tenant2Conn.createStatement().execute("SELECT V1 from " + view3); - fail(); - } catch (ColumnNotFoundException e) { - } - } - } - - } - - @Test - public void testChangingPKOfBaseTableChangesPKForAllViews() throws Exception { - String baseTable = "testChangePKOfBaseTable"; - String view1 = "view1"; - String view2 = "view2"; - String view3 = "view3"; - String view4 = "view4"; - /* baseTable - / | \ - view1(tenant1) view3(tenant2) view4(global) - / - view2(tenant1) - */ - Connection tenant1Conn = null, tenant2Conn = null; - try (Connection globalConn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " - + baseTable - + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - globalConn.createStatement().execute(baseTableDDL); - - tenant1Conn = getTenantConnection("tenant1"); - String view1DDL = "CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - - String view2DDL = "CREATE VIEW " + view2 + " AS SELECT * FROM " + view1; - tenant1Conn.createStatement().execute(view2DDL); - - tenant2Conn = getTenantConnection("tenant2"); - String view3DDL = "CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable; - tenant2Conn.createStatement().execute(view3DDL); - - String view4DDL = "CREATE VIEW " + view4 + " AS SELECT * FROM " + baseTable; - globalConn.createStatement().execute(view4DDL); - - String alterBaseTable = "ALTER TABLE " + baseTable + " ADD NEW_PK varchar primary key "; - globalConn.createStatement().execute(alterBaseTable); - - // verify that the new column new_pk is now part of the primary key for the entire hierarchy - - globalConn.createStatement().execute("SELECT * FROM " + baseTable); - assertTrue(checkColumnPartOfPk(globalConn.unwrap(PhoenixConnection.class), "NEW_PK", baseTable)); - - tenant1Conn.createStatement().execute("SELECT * FROM " + view1); - assertTrue(checkColumnPartOfPk(tenant1Conn.unwrap(PhoenixConnection.class), "NEW_PK", view1)); - - tenant1Conn.createStatement().execute("SELECT * FROM " + view2); - assertTrue(checkColumnPartOfPk(tenant1Conn.unwrap(PhoenixConnection.class), "NEW_PK", view2)); - - tenant2Conn.createStatement().execute("SELECT * FROM " + view3); - assertTrue(checkColumnPartOfPk(tenant2Conn.unwrap(PhoenixConnection.class), "NEW_PK", view3)); - - globalConn.createStatement().execute("SELECT * FROM " + view4); - assertTrue(checkColumnPartOfPk(globalConn.unwrap(PhoenixConnection.class), "NEW_PK", view4)); - - } finally { - if (tenant1Conn != null) { - try { - tenant1Conn.close(); - } catch (Throwable ignore) {} - } - if (tenant2Conn != null) { - try { - tenant2Conn.close(); - } catch (Throwable ignore) {} - } - } - - } - - private boolean checkColumnPartOfPk(PhoenixConnection conn, String columnName, String tableName) throws SQLException { - String normalizedTableName = SchemaUtil.normalizeIdentifier(tableName); - PTable table = conn.getTable(new PTableKey(conn.getTenantId(), normalizedTableName)); - List<PColumn> pkCols = table.getPKColumns(); - String normalizedColumnName = SchemaUtil.normalizeIdentifier(columnName); - for (PColumn pkCol : pkCols) { - if (pkCol.getName().getString().equals(normalizedColumnName)) { - return true; - } - } - return false; - } - - private int getIndexOfPkColumn(PhoenixConnection conn, String columnName, String tableName) throws SQLException { - String normalizedTableName = SchemaUtil.normalizeIdentifier(tableName); - PTable table = conn.getTable(new PTableKey(conn.getTenantId(), normalizedTableName)); - List<PColumn> pkCols = table.getPKColumns(); - String normalizedColumnName = SchemaUtil.normalizeIdentifier(columnName); - int i = 0; - for (PColumn pkCol : pkCols) { - if (pkCol.getName().getString().equals(normalizedColumnName)) { - return i; - } - i++; - } - return -1; - } - - @Test - public void testAddPKColumnToBaseTableWhoseViewsHaveIndices() throws Exception { - String baseTable = "testAddPKColumnToBaseTableWhoseViewsHaveIndices"; - String view1 = "view1"; - String view2 = "view2"; - String view3 = "view3"; - String tenant1 = "tenant1"; - String tenant2 = "tenant2"; - String view2Index = view2 + "_idx"; - String view3Index = view3 + "_idx"; - /* baseTable(mutli-tenant) - / \ - view1(tenant1) view3(tenant2, index) - / - view2(tenant1, index) - */ - try (Connection globalConn = DriverManager.getConnection(getUrl())) { - // make sure that the tables are empty, but reachable - globalConn - .createStatement() - .execute( - "CREATE TABLE " - + baseTable - + " (TENANT_ID VARCHAR NOT NULL, K1 varchar not null, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, K1)) MULTI_TENANT = true "); - - } - try (Connection viewConn = getTenantConnection(tenant1)) { - // create tenant specific view for tenant1 - view1 - viewConn.createStatement().execute("CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable); - PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); - assertEquals(0, getTableSequenceNumber(phxConn, view1)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view1)); - - // create a view - view2 on view - view1 - viewConn.createStatement().execute("CREATE VIEW " + view2 + " AS SELECT * FROM " + view1); - assertEquals(0, getTableSequenceNumber(phxConn, view2)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view2)); - - - // create an index on view2 - viewConn.createStatement().execute("CREATE INDEX " + view2Index + " ON " + view2 + " (v1) include (v2)"); - assertEquals(0, getTableSequenceNumber(phxConn, view2Index)); - assertEquals(4, getMaxKeySequenceNumber(phxConn, view2Index)); - } - try (Connection viewConn = getTenantConnection(tenant2)) { - // create tenant specific view for tenant2 - view3 - viewConn.createStatement().execute("CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable); - PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); - assertEquals(0, getTableSequenceNumber(phxConn, view3)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view3)); - - - // create an index on view3 - viewConn.createStatement().execute("CREATE INDEX " + view3Index + " ON " + view3 + " (v1) include (v2)"); - assertEquals(0, getTableSequenceNumber(phxConn, view3Index)); - assertEquals(4, getMaxKeySequenceNumber(phxConn, view3Index)); - - - } - - // alter the base table by adding 1 non-pk and 2 pk columns - try (Connection globalConn = DriverManager.getConnection(getUrl())) { - globalConn.createStatement().execute("ALTER TABLE " + baseTable + " ADD v3 VARCHAR, k2 VARCHAR PRIMARY KEY, k3 VARCHAR PRIMARY KEY"); - assertEquals(4, getMaxKeySequenceNumber(globalConn.unwrap(PhoenixConnection.class), baseTable)); - - // Upsert records in the base table - String upsert = "UPSERT INTO " + baseTable + " (TENANT_ID, K1, K2, K3, V1, V2, V3) VALUES (?, ?, ?, ?, ?, ?, ?)"; - PreparedStatement stmt = globalConn.prepareStatement(upsert); - stmt.setString(1, tenant1); - stmt.setString(2, "K1"); - stmt.setString(3, "K2"); - stmt.setString(4, "K3"); - stmt.setString(5, "V1"); - stmt.setString(6, "V2"); - stmt.setString(7, "V3"); - stmt.executeUpdate(); - stmt.setString(1, tenant2); - stmt.setString(2, "K11"); - stmt.setString(3, "K22"); - stmt.setString(4, "K33"); - stmt.setString(5, "V11"); - stmt.setString(6, "V22"); - stmt.setString(7, "V33"); - stmt.executeUpdate(); - globalConn.commit(); - } - - // Verify now that the sequence number of data table, indexes and views have changed. - // Also verify that the newly added pk columns show up as pk columns of data table, indexes and views. - try (Connection viewConn = getTenantConnection(tenant1)) { - - ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view1); - PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); - assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view1)); - assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view1)); - assertEquals(1, getTableSequenceNumber(phxConn, view1)); - assertEquals(4, getMaxKeySequenceNumber(phxConn, view1)); - verifyNewColumns(rs, "K2", "K3", "V3"); - - - rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view2); - assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view2)); - assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view2)); - assertEquals(1, getTableSequenceNumber(phxConn, view2)); - assertEquals(4, getMaxKeySequenceNumber(phxConn, view2)); - verifyNewColumns(rs, "K2", "K3", "V3"); - - assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view2Index)); - assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view2Index)); - assertEquals(1, getTableSequenceNumber(phxConn, view2Index)); - assertEquals(6, getMaxKeySequenceNumber(phxConn, view2Index)); - } - try (Connection viewConn = getTenantConnection(tenant2)) { - ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view3); - PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); - assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view3)); - assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view3)); - assertEquals(1, getTableSequenceNumber(phxConn, view3)); - verifyNewColumns(rs, "K22", "K33", "V33"); - - assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view3Index)); - assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view3Index)); - assertEquals(1, getTableSequenceNumber(phxConn, view3Index)); - assertEquals(6, getMaxKeySequenceNumber(phxConn, view3Index)); - } - // Verify that the index is actually being used when using newly added pk col - try (Connection viewConn = getTenantConnection(tenant1)) { - String upsert = "UPSERT INTO " + view2 + " (K1, K2, K3, V1, V2, V3) VALUES ('key1', 'key2', 'key3', 'value1', 'value2', 'value3')"; - viewConn.createStatement().executeUpdate(upsert); - viewConn.commit(); - Statement stmt = viewConn.createStatement(); - String sql = "SELECT V2 FROM " + view2 + " WHERE V1 = 'value1' AND K3 = 'key3'"; - QueryPlan plan = stmt.unwrap(PhoenixStatement.class).optimizeQuery(sql); - assertTrue(plan.getTableRef().getTable().getName().getString().equals(SchemaUtil.normalizeIdentifier(view2Index))); - ResultSet rs = viewConn.createStatement().executeQuery(sql); - verifyNewColumns(rs, "value2"); - } - - } - - @Test - public void testAddingPkAndKeyValueColumnsToBaseTableWithDivergedView() throws Exception { - String baseTable = "testAlteringPkOfBaseTableWithDivergedView".toUpperCase(); - String view1 = "view1".toUpperCase(); - String divergedView = "divergedView".toUpperCase(); - String divergedViewIndex = divergedView + "_IDX"; - /* baseTable - / | - view1(tenant1) divergedView(tenant2) - - */ - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - conn.createStatement().execute(baseTableDDL); - - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - } - - try (Connection tenant2Conn = getTenantConnection("tenant2")) { - String divergedViewDDL = "CREATE VIEW " + divergedView + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; - tenant2Conn.createStatement().execute(divergedViewDDL); - // Drop column V2 from the view to have it diverge from the base table - tenant2Conn.createStatement().execute("ALTER VIEW " + divergedView + " DROP COLUMN V2"); - - // create an index on the diverged view - String indexDDL = "CREATE INDEX " + divergedViewIndex + " ON " + divergedView + " (V1) include (V3)"; - tenant2Conn.createStatement().execute(indexDDL); - } - - String alterBaseTable = "ALTER TABLE " + baseTable + " ADD KV VARCHAR, PK2 VARCHAR PRIMARY KEY"; - conn.createStatement().execute(alterBaseTable); - - - // verify that the both columns were added to view1 - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - tenant1Conn.createStatement().execute("SELECT KV from " + view1); - tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); - } - - // verify that only the primary key column PK2 was added to diverged view - try (Connection tenant2Conn = getTenantConnection("tenant2")) { - tenant2Conn.createStatement().execute("SELECT PK2 from " + divergedView); - try { - tenant2Conn.createStatement().execute("SELECT KV FROM " + divergedView); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); - } - } - - // Upsert records in diverged view. Verify that the PK column was added to the index on it. - String upsert = "UPSERT INTO " + divergedView + " (PK1, PK2, V1, V3) VALUES ('PK1', 'PK2', 'V1', 'V3')"; - try (Connection viewConn = getTenantConnection("tenant2")) { - viewConn.createStatement().executeUpdate(upsert); - viewConn.commit(); - Statement stmt = viewConn.createStatement(); - String sql = "SELECT V3 FROM " + divergedView + " WHERE V1 = 'V1' AND PK2 = 'PK2'"; - QueryPlan plan = stmt.unwrap(PhoenixStatement.class).optimizeQuery(sql); - assertTrue(plan.getTableRef().getTable().getName().getString().equals(SchemaUtil.normalizeIdentifier(divergedViewIndex))); - ResultSet rs = viewConn.createStatement().executeQuery(sql); - verifyNewColumns(rs, "V3"); - } - - // For non-diverged view, base table columns will be added at the same position as base table - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 9, 7, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL1", "VIEW_COL2"); - // For a diverged view, only base table's pk column will be added and that too at the end. - assertTableDefinition(conn, divergedView, PTableType.VIEW, baseTable, 2, 7, DIVERGED_VIEW_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2", "PK2"); - - // Add existing column VIEW_COL2 to the base table - alterBaseTable = "ALTER TABLE " + baseTable + " ADD VIEW_COL2 CHAR(256)"; - conn.createStatement().execute(alterBaseTable); - - // For the non-diverged view, adding the column VIEW_COL2 will end up changing its ordinal position in the view. - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 2, 9, 8, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL2", "VIEW_COL1"); - // For the diverged view, adding the column VIEW_COL2 will not change its ordinal position in the view. It also won't change the base column count or the sequence number - assertTableDefinition(conn, divergedView, PTableType.VIEW, baseTable, 2, 7, DIVERGED_VIEW_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2", "PK2"); - } - } - - @Test - public void testAddColumnsToSaltedBaseTableWithViews() throws Exception { - String baseTable = "testAddColumnsToSaltedBaseTableWithViews".toUpperCase(); - String view1 = "view1".toUpperCase(); - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - conn.createStatement().execute(baseTableDDL); - - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - } - - assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 0, 5, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3"); - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 0, 7, 5, "TENANT_ID", "PK1", "V1", "V2", "V3", "VIEW_COL1", "VIEW_COL2"); - - String alterBaseTable = "ALTER TABLE " + baseTable + " ADD KV VARCHAR, PK2 VARCHAR PRIMARY KEY"; - conn.createStatement().execute(alterBaseTable); - - assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 1, 7, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2"); - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 9, 7, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL1", "VIEW_COL2"); - - // verify that the both columns were added to view1 - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - tenant1Conn.createStatement().execute("SELECT KV from " + view1); - tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); - } - } - } - - @Test - public void testDropColumnsFromSaltedBaseTableWithViews() throws Exception { - String baseTable = "testDropColumnsFromSaltedBaseTableWithViews".toUpperCase(); - String view1 = "view1".toUpperCase(); - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - conn.createStatement().execute(baseTableDDL); - - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - } - - assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 0, 5, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3"); - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 0, 7, 5, "TENANT_ID", "PK1", "V1", "V2", "V3", "VIEW_COL1", "VIEW_COL2"); - - String alterBaseTable = "ALTER TABLE " + baseTable + " DROP COLUMN V2"; - conn.createStatement().execute(alterBaseTable); - - assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 1, 4, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3"); - assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 6, 4, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2"); - - // verify that the dropped columns aren't visible - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - try { - tenant1Conn.createStatement().execute("SELECT KV from " + view1); - fail(); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); - } - try { - tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); - fail(); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); - } - } - } - } - - @Test - public void testAlteringViewConditionallyModifiesHTableMetadata() throws Exception { - String baseTable = "testAlteringViewConditionallyModifiesBaseTable".toUpperCase(); - String view1 = "view1".toUpperCase(); - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; - conn.createStatement().execute(baseTableDDL); - HTableDescriptor tableDesc1 = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); - try (Connection tenant1Conn = getTenantConnection("tenant1")) { - String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; - tenant1Conn.createStatement().execute(view1DDL); - // This should not modify the base table - String alterView = "ALTER VIEW " + view1 + " ADD NEWCOL1 VARCHAR"; - tenant1Conn.createStatement().execute(alterView); - HTableDescriptor tableDesc2 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); - assertEquals(tableDesc1, tableDesc2); - - // Add a new column family that doesn't already exist in the base table - alterView = "ALTER VIEW " + view1 + " ADD CF.NEWCOL2 VARCHAR"; - tenant1Conn.createStatement().execute(alterView); - - // Verify that the column family now shows up in the base table descriptor - tableDesc2 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); - assertFalse(tableDesc2.equals(tableDesc1)); - assertNotNull(tableDesc2.getFamily(Bytes.toBytes("CF"))); - - // Add a column with an existing column family. This shouldn't modify the base table. - alterView = "ALTER VIEW " + view1 + " ADD CF.NEWCOL3 VARCHAR"; - tenant1Conn.createStatement().execute(alterView); - HTableDescriptor tableDesc3 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); - assertTrue(tableDesc3.equals(tableDesc2)); - assertNotNull(tableDesc3.getFamily(Bytes.toBytes("CF"))); - } - } - } - - @Test - public void testCacheInvalidatedAfterAddingColumnToBaseTableWithViews() throws Exception { - String baseTable = "testCacheInvalidatedAfterAddingColumnToBaseTableWithViews"; - String viewName = baseTable + "_view"; - String tenantId = "tenantId"; - try (Connection globalConn = DriverManager.getConnection(getUrl())) { - String tableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true" ; - globalConn.createStatement().execute(tableDDL); - Properties tenantProps = new Properties(); - tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); - // create a tenant specific view - try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) { - String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + baseTable; - tenantConn.createStatement().execute(viewDDL); - - // Add a column to the base table using global connection - globalConn.createStatement().execute("ALTER TABLE " + baseTable + " ADD NEW_COL VARCHAR"); - - // Check now whether the tenant connection can see the column that was added - tenantConn.createStatement().execute("SELECT NEW_COL FROM " + viewName); - tenantConn.createStatement().execute("SELECT NEW_COL FROM " + baseTable); - } - } - } - - @Test - public void testCacheInvalidatedAfterDroppingColumnFromBaseTableWithViews() throws Exception { - String baseTable = "testCacheInvalidatedAfterDroppingColumnFromBaseTableWithViews"; - String viewName = baseTable + "_view"; - String tenantId = "tenantId"; - try (Connection globalConn = DriverManager.getConnection(getUrl())) { - String tableDDL = - "CREATE TABLE " - + baseTable - + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true" ; - globalConn.createStatement().execute(tableDDL); - Properties tenantProps = new Properties(); - tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); - // create a tenant specific view - try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) { - String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + baseTable; - tenantConn.createStatement().execute(viewDDL); - - // Add a column to the base table using global connection - globalConn.createStatement() - .execute("ALTER TABLE " + baseTable + " DROP COLUMN V1"); - - // Check now whether the tenant connection can see the column that was dropped - try { - tenantConn.createStatement().execute("SELECT V1 FROM " + viewName); - fail(); - } catch (ColumnNotFoundException e) { - } - try { - tenantConn.createStatement().execute("SELECT V1 FROM " + baseTable); - fail(); - } catch (ColumnNotFoundException e) { - } - } - } - } - - public static void assertTableDefinition(Connection conn, String tableName, PTableType tableType, String parentTableName, int sequenceNumber, int columnCount, int baseColumnCount, String... columnName) throws Exception { - PreparedStatement p = conn.prepareStatement("SELECT * FROM SYSTEM.CATALOG WHERE TABLE_NAME=? AND TABLE_TYPE=?"); - p.setString(1, tableName); - p.setString(2, tableType.getSerializedValue()); - ResultSet rs = p.executeQuery(); - assertTrue(rs.next()); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in BaseColumnCount"), baseColumnCount, rs.getInt("BASE_COLUMN_COUNT")); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnCount"), columnCount, rs.getInt("COLUMN_COUNT")); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in sequenceNumber"), sequenceNumber, rs.getInt("TABLE_SEQ_NUM")); - rs.close(); - - ResultSet parentTableColumnsRs = null; - if (parentTableName != null) { - parentTableColumnsRs = conn.getMetaData().getColumns(null, null, parentTableName, null); - parentTableColumnsRs.next(); - } - - ResultSet viewColumnsRs = conn.getMetaData().getColumns(null, null, tableName, null); - for (int i = 0; i < columnName.length; i++) { - if (columnName[i] != null) { - assertTrue(viewColumnsRs.next()); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnName: i=" + i), columnName[i], viewColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_NAME)); - int viewColOrdinalPos = viewColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in ordinalPosition: i=" + i), i+1, viewColOrdinalPos); - // validate that all the columns in the base table are present in the view - if (parentTableColumnsRs != null && !parentTableColumnsRs.isAfterLast()) { - ResultSetMetaData parentTableColumnsMetadata = parentTableColumnsRs.getMetaData(); - assertEquals(parentTableColumnsMetadata.getColumnCount(), viewColumnsRs.getMetaData().getColumnCount()); - int parentTableColOrdinalRs = parentTableColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION); - assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in ordinalPosition of view and base table for i=" + i), parentTableColOrdinalRs, viewColOrdinalPos); - for (int columnIndex = 1; columnIndex < parentTableColumnsMetadata.getColumnCount(); columnIndex++) { - String viewColumnValue = viewColumnsRs.getString(columnIndex); - String parentTableColumnValue = parentTableColumnsRs.getString(columnIndex); - if (!Objects.equal(viewColumnValue, parentTableColumnValue)) { - if (parentTableColumnsMetadata.getColumnName(columnIndex).equals(PhoenixDatabaseMetaData.TABLE_NAME)) { - assertEquals(parentTableName, parentTableColumnValue); - assertEquals(tableName, viewColumnValue); - } - } - } - parentTableColumnsRs.next(); - } - } - } - assertFalse(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, ""), viewColumnsRs.next()); - } -} http://git-wip-us.apache.org/repos/asf/phoenix/blob/14dab2f4/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViewsIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViewsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViewsIT.java new file mode 100644 index 0000000..8ec93bc --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViewsIT.java @@ -0,0 +1,704 @@ +/* + * 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.phoenix.end2end; + +import static org.apache.phoenix.query.QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT; +import static org.apache.phoenix.query.QueryConstants.DIVERGED_VIEW_BASE_COLUMN_COUNT; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.List; +import java.util.Properties; + +import org.apache.hadoop.hbase.HTableDescriptor; +import org.apache.hadoop.hbase.util.Bytes; +import org.apache.phoenix.compile.QueryPlan; +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData; +import org.apache.phoenix.jdbc.PhoenixStatement; +import org.apache.phoenix.schema.ColumnNotFoundException; +import org.apache.phoenix.schema.PColumn; +import org.apache.phoenix.schema.PTable; +import org.apache.phoenix.schema.PTableKey; +import org.apache.phoenix.schema.PTableType; +import org.apache.phoenix.util.IndexUtil; +import org.apache.phoenix.util.PhoenixRuntime; +import org.apache.phoenix.util.SchemaUtil; +import org.junit.Test; + +import com.google.common.base.Objects; + +public class AlterMultiTenantTableWithViewsIT extends BaseHBaseManagedTimeTableReuseIT { + + private Connection getTenantConnection(String tenantId) throws Exception { + Properties tenantProps = new Properties(); + tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); + return DriverManager.getConnection(getUrl(), tenantProps); + } + + private static long getTableSequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { + PTable table = conn.getTable(new PTableKey(conn.getTenantId(), SchemaUtil.normalizeIdentifier(tableName))); + return table.getSequenceNumber(); + } + + private static short getMaxKeySequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { + PTable table = conn.getTable(new PTableKey(conn.getTenantId(), SchemaUtil.normalizeIdentifier(tableName))); + return SchemaUtil.getMaxKeySeq(table); + } + + private static void verifyNewColumns(ResultSet rs, String ... values) throws SQLException { + assertTrue(rs.next()); + int i = 1; + for (String value : values) { + assertEquals(value, rs.getString(i++)); + } + assertFalse(rs.next()); + assertEquals(values.length, i - 1); + } + + @Test + public void testAddDropColumnToBaseTablePropagatesToEntireViewHierarchy() throws Exception { + String baseTable = "testViewHierarchy"; + String baseViewName = generateRandomString(); + String view1 = baseViewName + "_VIEW1"; + String view2 = baseViewName + "_VIEW2"; + String view3 = baseViewName + "_VIEW3"; + String view4 = baseViewName + "_VIEW4"; + /* baseTable + / | \ + view1(tenant1) view3(tenant2) view4(global) + / + view2(tenant1) + */ + try (Connection conn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + conn.createStatement().execute(baseTableDDL); + + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + String view1DDL = "CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + + String view2DDL = "CREATE VIEW " + view2 + " AS SELECT * FROM " + view1; + tenant1Conn.createStatement().execute(view2DDL); + } + + try (Connection tenant2Conn = getTenantConnection("tenant2")) { + String view3DDL = "CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable; + tenant2Conn.createStatement().execute(view3DDL); + } + + String view4DDL = "CREATE VIEW " + view4 + " AS SELECT * FROM " + baseTable; + conn.createStatement().execute(view4DDL); + + String alterBaseTable = "ALTER TABLE " + baseTable + " ADD V3 VARCHAR"; + conn.createStatement().execute(alterBaseTable); + + // verify that the column is visible to view4 + conn.createStatement().execute("SELECT V3 FROM " + view4); + + // verify that the column is visible to view1 and view2 + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + tenant1Conn.createStatement().execute("SELECT V3 from " + view1); + tenant1Conn.createStatement().execute("SELECT V3 from " + view2); + } + + // verify that the column is visible to view3 + try (Connection tenant2Conn = getTenantConnection("tenant2")) { + tenant2Conn.createStatement().execute("SELECT V3 from " + view3); + } + + alterBaseTable = "ALTER TABLE " + baseTable + " DROP COLUMN V1"; + conn.createStatement().execute(alterBaseTable); + + // verify that the column is not visible to view4 + try { + conn.createStatement().execute("SELECT V1 FROM " + view4); + fail(); + } catch (ColumnNotFoundException e) { + } + // verify that the column is not visible to view1 and view2 + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + try { + tenant1Conn.createStatement().execute("SELECT V1 from " + view1); + fail(); + } catch (ColumnNotFoundException e) { + } + try { + tenant1Conn.createStatement().execute("SELECT V1 from " + view2); + fail(); + } catch (ColumnNotFoundException e) { + } + } + + // verify that the column is not visible to view3 + try (Connection tenant2Conn = getTenantConnection("tenant2")) { + try { + tenant2Conn.createStatement().execute("SELECT V1 from " + view3); + fail(); + } catch (ColumnNotFoundException e) { + } + } + } + + } + + @Test + public void testChangingPKOfBaseTableChangesPKForAllViews() throws Exception { + String baseTable = "testChangePKOfBaseTable"; + String baseViewName = generateRandomString(); + String view1 = baseViewName + "_VIEW1"; + String view2 = baseViewName + "_VIEW2"; + String view3 = baseViewName + "_VIEW3"; + String view4 = baseViewName + "_VIEW4"; + /* baseTable + / | \ + view1(tenant1) view3(tenant2) view4(global) + / + view2(tenant1) + */ + Connection tenant1Conn = null, tenant2Conn = null; + try (Connection globalConn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + + baseTable + + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + globalConn.createStatement().execute(baseTableDDL); + + tenant1Conn = getTenantConnection("tenant1"); + String view1DDL = "CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + + String view2DDL = "CREATE VIEW " + view2 + " AS SELECT * FROM " + view1; + tenant1Conn.createStatement().execute(view2DDL); + + tenant2Conn = getTenantConnection("tenant2"); + String view3DDL = "CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable; + tenant2Conn.createStatement().execute(view3DDL); + + String view4DDL = "CREATE VIEW " + view4 + " AS SELECT * FROM " + baseTable; + globalConn.createStatement().execute(view4DDL); + + String alterBaseTable = "ALTER TABLE " + baseTable + " ADD NEW_PK varchar primary key "; + globalConn.createStatement().execute(alterBaseTable); + + // verify that the new column new_pk is now part of the primary key for the entire hierarchy + + globalConn.createStatement().execute("SELECT * FROM " + baseTable); + assertTrue(checkColumnPartOfPk(globalConn.unwrap(PhoenixConnection.class), "NEW_PK", baseTable)); + + tenant1Conn.createStatement().execute("SELECT * FROM " + view1); + assertTrue(checkColumnPartOfPk(tenant1Conn.unwrap(PhoenixConnection.class), "NEW_PK", view1)); + + tenant1Conn.createStatement().execute("SELECT * FROM " + view2); + assertTrue(checkColumnPartOfPk(tenant1Conn.unwrap(PhoenixConnection.class), "NEW_PK", view2)); + + tenant2Conn.createStatement().execute("SELECT * FROM " + view3); + assertTrue(checkColumnPartOfPk(tenant2Conn.unwrap(PhoenixConnection.class), "NEW_PK", view3)); + + globalConn.createStatement().execute("SELECT * FROM " + view4); + assertTrue(checkColumnPartOfPk(globalConn.unwrap(PhoenixConnection.class), "NEW_PK", view4)); + + } finally { + if (tenant1Conn != null) { + try { + tenant1Conn.close(); + } catch (Throwable ignore) {} + } + if (tenant2Conn != null) { + try { + tenant2Conn.close(); + } catch (Throwable ignore) {} + } + } + + } + + private boolean checkColumnPartOfPk(PhoenixConnection conn, String columnName, String tableName) throws SQLException { + String normalizedTableName = SchemaUtil.normalizeIdentifier(tableName); + PTable table = conn.getTable(new PTableKey(conn.getTenantId(), normalizedTableName)); + List<PColumn> pkCols = table.getPKColumns(); + String normalizedColumnName = SchemaUtil.normalizeIdentifier(columnName); + for (PColumn pkCol : pkCols) { + if (pkCol.getName().getString().equals(normalizedColumnName)) { + return true; + } + } + return false; + } + + private int getIndexOfPkColumn(PhoenixConnection conn, String columnName, String tableName) throws SQLException { + String normalizedTableName = SchemaUtil.normalizeIdentifier(tableName); + PTable table = conn.getTable(new PTableKey(conn.getTenantId(), normalizedTableName)); + List<PColumn> pkCols = table.getPKColumns(); + String normalizedColumnName = SchemaUtil.normalizeIdentifier(columnName); + int i = 0; + for (PColumn pkCol : pkCols) { + if (pkCol.getName().getString().equals(normalizedColumnName)) { + return i; + } + i++; + } + return -1; + } + + @Test + public void testAddPKColumnToBaseTableWhoseViewsHaveIndices() throws Exception { + String baseTable = "testAddPKColumnToBaseTableWhoseViewsHaveIndices"; + String baseViewName = generateRandomString(); + String view1 = baseViewName + "_VIEW1"; + String view2 = baseViewName + "_VIEW2"; + String view3 = baseViewName + "_VIEW3"; + String tenant1 = baseViewName + "_T1"; + String tenant2 = baseViewName + "_T2"; + String view2Index = view2 + "_IDX"; + String view3Index = view3 + "_IDX"; + /* baseTable(mutli-tenant) + / \ + view1(tenant1) view3(tenant2, index) + / + view2(tenant1, index) + */ + try (Connection globalConn = DriverManager.getConnection(getUrl())) { + // make sure that the tables are empty, but reachable + globalConn + .createStatement() + .execute( + "CREATE TABLE " + + baseTable + + " (TENANT_ID VARCHAR NOT NULL, K1 varchar not null, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, K1)) MULTI_TENANT = true "); + + } + try (Connection viewConn = getTenantConnection(tenant1)) { + // create tenant specific view for tenant1 - view1 + viewConn.createStatement().execute("CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable); + PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); + assertEquals(0, getTableSequenceNumber(phxConn, view1)); + assertEquals(2, getMaxKeySequenceNumber(phxConn, view1)); + + // create a view - view2 on view - view1 + viewConn.createStatement().execute("CREATE VIEW " + view2 + " AS SELECT * FROM " + view1); + assertEquals(0, getTableSequenceNumber(phxConn, view2)); + assertEquals(2, getMaxKeySequenceNumber(phxConn, view2)); + + + // create an index on view2 + viewConn.createStatement().execute("CREATE INDEX " + view2Index + " ON " + view2 + " (v1) include (v2)"); + assertEquals(0, getTableSequenceNumber(phxConn, view2Index)); + assertEquals(4, getMaxKeySequenceNumber(phxConn, view2Index)); + } + try (Connection viewConn = getTenantConnection(tenant2)) { + // create tenant specific view for tenant2 - view3 + viewConn.createStatement().execute("CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable); + PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); + assertEquals(0, getTableSequenceNumber(phxConn, view3)); + assertEquals(2, getMaxKeySequenceNumber(phxConn, view3)); + + + // create an index on view3 + viewConn.createStatement().execute("CREATE INDEX " + view3Index + " ON " + view3 + " (v1) include (v2)"); + assertEquals(0, getTableSequenceNumber(phxConn, view3Index)); + assertEquals(4, getMaxKeySequenceNumber(phxConn, view3Index)); + + + } + + // alter the base table by adding 1 non-pk and 2 pk columns + try (Connection globalConn = DriverManager.getConnection(getUrl())) { + globalConn.createStatement().execute("ALTER TABLE " + baseTable + " ADD v3 VARCHAR, k2 VARCHAR PRIMARY KEY, k3 VARCHAR PRIMARY KEY"); + assertEquals(4, getMaxKeySequenceNumber(globalConn.unwrap(PhoenixConnection.class), baseTable)); + + // Upsert records in the base table + String upsert = "UPSERT INTO " + baseTable + " (TENANT_ID, K1, K2, K3, V1, V2, V3) VALUES (?, ?, ?, ?, ?, ?, ?)"; + PreparedStatement stmt = globalConn.prepareStatement(upsert); + stmt.setString(1, tenant1); + stmt.setString(2, "K1"); + stmt.setString(3, "K2"); + stmt.setString(4, "K3"); + stmt.setString(5, "V1"); + stmt.setString(6, "V2"); + stmt.setString(7, "V3"); + stmt.executeUpdate(); + stmt.setString(1, tenant2); + stmt.setString(2, "K11"); + stmt.setString(3, "K22"); + stmt.setString(4, "K33"); + stmt.setString(5, "V11"); + stmt.setString(6, "V22"); + stmt.setString(7, "V33"); + stmt.executeUpdate(); + globalConn.commit(); + } + + // Verify now that the sequence number of data table, indexes and views have changed. + // Also verify that the newly added pk columns show up as pk columns of data table, indexes and views. + try (Connection viewConn = getTenantConnection(tenant1)) { + + ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view1); + PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); + assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view1)); + assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view1)); + assertEquals(1, getTableSequenceNumber(phxConn, view1)); + assertEquals(4, getMaxKeySequenceNumber(phxConn, view1)); + verifyNewColumns(rs, "K2", "K3", "V3"); + + + rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view2); + assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view2)); + assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view2)); + assertEquals(1, getTableSequenceNumber(phxConn, view2)); + assertEquals(4, getMaxKeySequenceNumber(phxConn, view2)); + verifyNewColumns(rs, "K2", "K3", "V3"); + + assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view2Index)); + assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view2Index)); + assertEquals(1, getTableSequenceNumber(phxConn, view2Index)); + assertEquals(6, getMaxKeySequenceNumber(phxConn, view2Index)); + } + try (Connection viewConn = getTenantConnection(tenant2)) { + ResultSet rs = viewConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view3); + PhoenixConnection phxConn = viewConn.unwrap(PhoenixConnection.class); + assertEquals(2, getIndexOfPkColumn(phxConn, "k2", view3)); + assertEquals(3, getIndexOfPkColumn(phxConn, "k3", view3)); + assertEquals(1, getTableSequenceNumber(phxConn, view3)); + verifyNewColumns(rs, "K22", "K33", "V33"); + + assertEquals(4, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k2"), view3Index)); + assertEquals(5, getIndexOfPkColumn(phxConn, IndexUtil.getIndexColumnName(null, "k3"), view3Index)); + assertEquals(1, getTableSequenceNumber(phxConn, view3Index)); + assertEquals(6, getMaxKeySequenceNumber(phxConn, view3Index)); + } + // Verify that the index is actually being used when using newly added pk col + try (Connection viewConn = getTenantConnection(tenant1)) { + String upsert = "UPSERT INTO " + view2 + " (K1, K2, K3, V1, V2, V3) VALUES ('key1', 'key2', 'key3', 'value1', 'value2', 'value3')"; + viewConn.createStatement().executeUpdate(upsert); + viewConn.commit(); + Statement stmt = viewConn.createStatement(); + String sql = "SELECT V2 FROM " + view2 + " WHERE V1 = 'value1' AND K3 = 'key3'"; + QueryPlan plan = stmt.unwrap(PhoenixStatement.class).optimizeQuery(sql); + assertTrue(plan.getTableRef().getTable().getName().getString().equals(SchemaUtil.normalizeIdentifier(view2Index))); + ResultSet rs = viewConn.createStatement().executeQuery(sql); + verifyNewColumns(rs, "value2"); + } + + } + + @Test + public void testAddingPkAndKeyValueColumnsToBaseTableWithDivergedView() throws Exception { + String baseTable = "testAlteringPkOfBaseTableWithDivergedView".toUpperCase(); + String view1 = generateRandomString(); + String divergedView = generateRandomString(); + String divergedViewIndex = divergedView + "_IDX"; + /* baseTable + / | + view1(tenant1) divergedView(tenant2) + + */ + try (Connection conn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + conn.createStatement().execute(baseTableDDL); + + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + } + + try (Connection tenant2Conn = getTenantConnection("tenant2")) { + String divergedViewDDL = "CREATE VIEW " + divergedView + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; + tenant2Conn.createStatement().execute(divergedViewDDL); + // Drop column V2 from the view to have it diverge from the base table + tenant2Conn.createStatement().execute("ALTER VIEW " + divergedView + " DROP COLUMN V2"); + + // create an index on the diverged view + String indexDDL = "CREATE INDEX " + divergedViewIndex + " ON " + divergedView + " (V1) include (V3)"; + tenant2Conn.createStatement().execute(indexDDL); + } + + String alterBaseTable = "ALTER TABLE " + baseTable + " ADD KV VARCHAR, PK2 VARCHAR PRIMARY KEY"; + conn.createStatement().execute(alterBaseTable); + + + // verify that the both columns were added to view1 + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + tenant1Conn.createStatement().execute("SELECT KV from " + view1); + tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); + } + + // verify that only the primary key column PK2 was added to diverged view + try (Connection tenant2Conn = getTenantConnection("tenant2")) { + tenant2Conn.createStatement().execute("SELECT PK2 from " + divergedView); + try { + tenant2Conn.createStatement().execute("SELECT KV FROM " + divergedView); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); + } + } + + // Upsert records in diverged view. Verify that the PK column was added to the index on it. + String upsert = "UPSERT INTO " + divergedView + " (PK1, PK2, V1, V3) VALUES ('PK1', 'PK2', 'V1', 'V3')"; + try (Connection viewConn = getTenantConnection("tenant2")) { + viewConn.createStatement().executeUpdate(upsert); + viewConn.commit(); + Statement stmt = viewConn.createStatement(); + String sql = "SELECT V3 FROM " + divergedView + " WHERE V1 = 'V1' AND PK2 = 'PK2'"; + QueryPlan plan = stmt.unwrap(PhoenixStatement.class).optimizeQuery(sql); + assertTrue(plan.getTableRef().getTable().getName().getString().equals(SchemaUtil.normalizeIdentifier(divergedViewIndex))); + ResultSet rs = viewConn.createStatement().executeQuery(sql); + verifyNewColumns(rs, "V3"); + } + + // For non-diverged view, base table columns will be added at the same position as base table + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 9, 7, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL1", "VIEW_COL2"); + // For a diverged view, only base table's pk column will be added and that too at the end. + assertTableDefinition(conn, divergedView, PTableType.VIEW, baseTable, 2, 7, DIVERGED_VIEW_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2", "PK2"); + + // Add existing column VIEW_COL2 to the base table + alterBaseTable = "ALTER TABLE " + baseTable + " ADD VIEW_COL2 CHAR(256)"; + conn.createStatement().execute(alterBaseTable); + + // For the non-diverged view, adding the column VIEW_COL2 will end up changing its ordinal position in the view. + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 2, 9, 8, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL2", "VIEW_COL1"); + // For the diverged view, adding the column VIEW_COL2 will not change its ordinal position in the view. It also won't change the base column count or the sequence number + assertTableDefinition(conn, divergedView, PTableType.VIEW, baseTable, 2, 7, DIVERGED_VIEW_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2", "PK2"); + } + } + + @Test + public void testAddColumnsToSaltedBaseTableWithViews() throws Exception { + String baseTable = "testAddColumnsToSaltedBaseTableWithViews".toUpperCase(); + String view1 = generateRandomString(); + try (Connection conn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + conn.createStatement().execute(baseTableDDL); + + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + } + + assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 0, 5, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3"); + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 0, 7, 5, "TENANT_ID", "PK1", "V1", "V2", "V3", "VIEW_COL1", "VIEW_COL2"); + + String alterBaseTable = "ALTER TABLE " + baseTable + " ADD KV VARCHAR, PK2 VARCHAR PRIMARY KEY"; + conn.createStatement().execute(alterBaseTable); + + assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 1, 7, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2"); + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 9, 7, "TENANT_ID", "PK1", "V1", "V2", "V3", "KV", "PK2", "VIEW_COL1", "VIEW_COL2"); + + // verify that the both columns were added to view1 + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + tenant1Conn.createStatement().execute("SELECT KV from " + view1); + tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); + } + } + } + + @Test + public void testDropColumnsFromSaltedBaseTableWithViews() throws Exception { + String baseTable = "testDropColumnsFromSaltedBaseTableWithViews".toUpperCase(); + String view1 = generateRandomString(); + try (Connection conn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + conn.createStatement().execute(baseTableDDL); + + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + } + + assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 0, 5, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V2", "V3"); + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 0, 7, 5, "TENANT_ID", "PK1", "V1", "V2", "V3", "VIEW_COL1", "VIEW_COL2"); + + String alterBaseTable = "ALTER TABLE " + baseTable + " DROP COLUMN V2"; + conn.createStatement().execute(alterBaseTable); + + assertTableDefinition(conn, baseTable, PTableType.TABLE, null, 1, 4, BASE_TABLE_BASE_COLUMN_COUNT, "TENANT_ID", "PK1", "V1", "V3"); + assertTableDefinition(conn, view1, PTableType.VIEW, baseTable, 1, 6, 4, "TENANT_ID", "PK1", "V1", "V3", "VIEW_COL1", "VIEW_COL2"); + + // verify that the dropped columns aren't visible + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + try { + tenant1Conn.createStatement().execute("SELECT KV from " + view1); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); + } + try { + tenant1Conn.createStatement().execute("SELECT PK2 from " + view1); + fail(); + } catch (SQLException e) { + assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); + } + } + } + } + + @Test + public void testAlteringViewConditionallyModifiesHTableMetadata() throws Exception { + String baseTable = "testAlteringViewConditionallyModifiesBaseTable".toUpperCase(); + String view1 = generateRandomString(); + try (Connection conn = DriverManager.getConnection(getUrl())) { + String baseTableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true "; + conn.createStatement().execute(baseTableDDL); + HTableDescriptor tableDesc1 = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); + try (Connection tenant1Conn = getTenantConnection("tenant1")) { + String view1DDL = "CREATE VIEW " + view1 + " ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 CHAR(256)) AS SELECT * FROM " + baseTable; + tenant1Conn.createStatement().execute(view1DDL); + // This should not modify the base table + String alterView = "ALTER VIEW " + view1 + " ADD NEWCOL1 VARCHAR"; + tenant1Conn.createStatement().execute(alterView); + HTableDescriptor tableDesc2 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); + assertEquals(tableDesc1, tableDesc2); + + // Add a new column family that doesn't already exist in the base table + alterView = "ALTER VIEW " + view1 + " ADD CF.NEWCOL2 VARCHAR"; + tenant1Conn.createStatement().execute(alterView); + + // Verify that the column family now shows up in the base table descriptor + tableDesc2 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); + assertFalse(tableDesc2.equals(tableDesc1)); + assertNotNull(tableDesc2.getFamily(Bytes.toBytes("CF"))); + + // Add a column with an existing column family. This shouldn't modify the base table. + alterView = "ALTER VIEW " + view1 + " ADD CF.NEWCOL3 VARCHAR"; + tenant1Conn.createStatement().execute(alterView); + HTableDescriptor tableDesc3 = tenant1Conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin().getTableDescriptor(Bytes.toBytes(baseTable)); + assertTrue(tableDesc3.equals(tableDesc2)); + assertNotNull(tableDesc3.getFamily(Bytes.toBytes("CF"))); + } + } + } + + @Test + public void testCacheInvalidatedAfterAddingColumnToBaseTableWithViews() throws Exception { + String baseTable = "testCacheInvalidatedAfterAddingColumnToBaseTableWithViews"; + String viewName = baseTable + "_view"; + String tenantId = "tenantId"; + try (Connection globalConn = DriverManager.getConnection(getUrl())) { + String tableDDL = "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true" ; + globalConn.createStatement().execute(tableDDL); + Properties tenantProps = new Properties(); + tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); + // create a tenant specific view + try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) { + String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + baseTable; + tenantConn.createStatement().execute(viewDDL); + + // Add a column to the base table using global connection + globalConn.createStatement().execute("ALTER TABLE " + baseTable + " ADD NEW_COL VARCHAR"); + + // Check now whether the tenant connection can see the column that was added + tenantConn.createStatement().execute("SELECT NEW_COL FROM " + viewName); + tenantConn.createStatement().execute("SELECT NEW_COL FROM " + baseTable); + } + } + } + + @Test + public void testCacheInvalidatedAfterDroppingColumnFromBaseTableWithViews() throws Exception { + String baseTable = "testCacheInvalidatedAfterDroppingColumnFromBaseTableWithViews"; + String viewName = baseTable + "_view"; + String tenantId = "tenantId"; + try (Connection globalConn = DriverManager.getConnection(getUrl())) { + String tableDDL = + "CREATE TABLE " + + baseTable + + " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true" ; + globalConn.createStatement().execute(tableDDL); + Properties tenantProps = new Properties(); + tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); + // create a tenant specific view + try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) { + String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + baseTable; + tenantConn.createStatement().execute(viewDDL); + + // Add a column to the base table using global connection + globalConn.createStatement() + .execute("ALTER TABLE " + baseTable + " DROP COLUMN V1"); + + // Check now whether the tenant connection can see the column that was dropped + try { + tenantConn.createStatement().execute("SELECT V1 FROM " + viewName); + fail(); + } catch (ColumnNotFoundException e) { + } + try { + tenantConn.createStatement().execute("SELECT V1 FROM " + baseTable); + fail(); + } catch (ColumnNotFoundException e) { + } + } + } + } + + public static void assertTableDefinition(Connection conn, String tableName, PTableType tableType, String parentTableName, int sequenceNumber, int columnCount, int baseColumnCount, String... columnName) throws Exception { + PreparedStatement p = conn.prepareStatement("SELECT * FROM SYSTEM.CATALOG WHERE TABLE_NAME=? AND TABLE_TYPE=?"); + p.setString(1, tableName); + p.setString(2, tableType.getSerializedValue()); + ResultSet rs = p.executeQuery(); + assertTrue(rs.next()); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in BaseColumnCount"), baseColumnCount, rs.getInt("BASE_COLUMN_COUNT")); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnCount"), columnCount, rs.getInt("COLUMN_COUNT")); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in sequenceNumber"), sequenceNumber, rs.getInt("TABLE_SEQ_NUM")); + rs.close(); + + ResultSet parentTableColumnsRs = null; + if (parentTableName != null) { + parentTableColumnsRs = conn.getMetaData().getColumns(null, null, parentTableName, null); + parentTableColumnsRs.next(); + } + + ResultSet viewColumnsRs = conn.getMetaData().getColumns(null, null, tableName, null); + for (int i = 0; i < columnName.length; i++) { + if (columnName[i] != null) { + assertTrue(viewColumnsRs.next()); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnName: i=" + i), columnName[i], viewColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_NAME)); + int viewColOrdinalPos = viewColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in ordinalPosition: i=" + i), i+1, viewColOrdinalPos); + // validate that all the columns in the base table are present in the view + if (parentTableColumnsRs != null && !parentTableColumnsRs.isAfterLast()) { + ResultSetMetaData parentTableColumnsMetadata = parentTableColumnsRs.getMetaData(); + assertEquals(parentTableColumnsMetadata.getColumnCount(), viewColumnsRs.getMetaData().getColumnCount()); + int parentTableColOrdinalRs = parentTableColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION); + assertEquals(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in ordinalPosition of view and base table for i=" + i), parentTableColOrdinalRs, viewColOrdinalPos); + for (int columnIndex = 1; columnIndex < parentTableColumnsMetadata.getColumnCount(); columnIndex++) { + String viewColumnValue = viewColumnsRs.getString(columnIndex); + String parentTableColumnValue = parentTableColumnsRs.getString(columnIndex); + if (!Objects.equal(viewColumnValue, parentTableColumnValue)) { + if (parentTableColumnsMetadata.getColumnName(columnIndex).equals(PhoenixDatabaseMetaData.TABLE_NAME)) { + assertEquals(parentTableName, parentTableColumnValue); + assertEquals(tableName, viewColumnValue); + } + } + } + parentTableColumnsRs.next(); + } + } + } + assertFalse(AlterTableWithViewsIT.getSystemCatalogEntriesForTable(conn, tableName, ""), viewColumnsRs.next()); + } +}