You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by sa...@apache.org on 2016/08/26 00:17:27 UTC
[14/14] phoenix git commit: PHOENIX-3036 Modify phoenix IT tests to
extend BaseHBaseManagedTimeTableReuseIT (Prakul Agarwal, Samarth Jain)
PHOENIX-3036 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/03df8ab5
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/03df8ab5
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/03df8ab5
Branch: refs/heads/4.x-HBase-0.98
Commit: 03df8ab55ec91f0ca400416f537f5b1869039505
Parents: 62f60e7
Author: Samarth <sa...@salesforce.com>
Authored: Thu Aug 25 17:16:32 2016 -0700
Committer: Samarth <sa...@salesforce.com>
Committed: Thu Aug 25 17:16:32 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 | 15 +-
.../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 | 78 +-
.../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 | 6 +-
113 files changed, 3907 insertions(+), 3256 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/03df8ab5/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/03df8ab5/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());
+ }
+}