phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tdsi...@apache.org
Subject [5/5] phoenix git commit: PHOENIX-2156 Support drop of column from table with views
Date Mon, 11 Apr 2016 20:16:33 GMT
PHOENIX-2156 Support drop of column from table with views


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/6cc17278
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/6cc17278
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/6cc17278

Branch: refs/heads/master
Commit: 6cc17278d7e8be64ac606b2d0030cdb3edfda961
Parents: 6d14789
Author: Thomas D'Silva <tdsilva@salesforce.com>
Authored: Tue Mar 22 20:18:41 2016 -0700
Committer: Thomas D'Silva <tdsilva@salesforce.com>
Committed: Mon Apr 11 13:09:55 2016 -0700

----------------------------------------------------------------------
 .../end2end/AlterMultiTenantTableWithViews.java |  701 +++++++
 .../apache/phoenix/end2end/AlterTableIT.java    |   29 +-
 .../phoenix/end2end/AlterTableWithViewsIT.java  |  866 ++-------
 .../end2end/TenantSpecificTablesDDLIT.java      |    5 +-
 .../phoenix/end2end/index/DropMetadataIT.java   |  315 +++
 .../phoenix/end2end/index/DropViewIT.java       |  106 -
 .../coprocessor/MetaDataEndpointImpl.java       |  447 +++--
 .../phoenix/coprocessor/MetaDataProtocol.java   |  106 +
 .../coprocessor/generated/MetaDataProtos.java   | 1837 +++++++++++++++++-
 .../apache/phoenix/jdbc/PhoenixConnection.java  |    4 +-
 .../apache/phoenix/schema/MetaDataClient.java   |   70 +-
 .../org/apache/phoenix/schema/PTableImpl.java   |   18 +
 phoenix-protocol/src/main/MetaDataService.proto |   10 +
 13 files changed, 3515 insertions(+), 999 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/6cc17278/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
new file mode 100644
index 0000000..2cdfe4a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterMultiTenantTableWithViews.java
@@ -0,0 +1,701 @@
+/*
+ * 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/6cc17278/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
index 122ec16..e09dcea 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
@@ -36,7 +36,6 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Collections;
-import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 
@@ -44,8 +43,10 @@ import org.apache.hadoop.hbase.HColumnDescriptor;
 import org.apache.hadoop.hbase.HTableDescriptor;
 import org.apache.hadoop.hbase.KeepDeletedCells;
 import org.apache.hadoop.hbase.client.HBaseAdmin;
-import org.apache.hadoop.hbase.client.HTableInterface;
-import org.apache.hadoop.hbase.client.Put;
+import org.apache.hadoop.hbase.client.HTable;
+import org.apache.hadoop.hbase.client.Result;
+import org.apache.hadoop.hbase.client.ResultScanner;
+import org.apache.hadoop.hbase.client.Scan;
 import org.apache.hadoop.hbase.util.Bytes;
 import org.apache.phoenix.coprocessor.MetaDataProtocol;
 import org.apache.phoenix.exception.SQLExceptionCode;
@@ -55,8 +56,6 @@ import org.apache.phoenix.query.QueryConstants;
 import org.apache.phoenix.schema.PTable;
 import org.apache.phoenix.schema.PTableKey;
 import org.apache.phoenix.schema.TableNotFoundException;
-import org.apache.phoenix.schema.types.PInteger;
-import org.apache.phoenix.util.ByteUtil;
 import org.apache.phoenix.util.IndexUtil;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.PropertiesUtil;
@@ -65,8 +64,6 @@ import org.apache.phoenix.util.SchemaUtil;
 import org.junit.BeforeClass;
 import org.junit.Test;
 
-import com.google.common.collect.Lists;
-
 /**
  *
  * A lot of tests in this class test HBase level properties. As a result,
@@ -358,6 +355,13 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT {
         assertIndexExists(conn,true);
 
         // verify data table rows
+        Scan scan = new Scan();
+        HTable table = (HTable) conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(Bytes.toBytes(DATA_TABLE_FULL_NAME));
+        ResultScanner results = table.getScanner(scan);
+        for (Result res : results) {
+        	assertNull("Column value was not deleted",res.getValue(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES,
Bytes.toBytes("V2")));
+        }
+        results.close();
         rs = conn.createStatement().executeQuery(dataTableQuery);
         assertTrue(rs.next());
         assertEquals("a",rs.getString(1));
@@ -366,6 +370,13 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT {
         assertFalse(rs.next());
         
         // verify index table rows
+        scan = new Scan();
+        table = (HTable) conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(Bytes.toBytes(INDEX_TABLE_FULL_NAME));
+        results = table.getScanner(scan);
+        for (Result res : results) {
+        	assertNull("Column value was not deleted",res.getValue(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES,
Bytes.toBytes("0:V2")));
+        }
+        results.close();
         rs = conn.createStatement().executeQuery(indexTableQuery);
         assertTrue(rs.next());
         assertEquals("x",rs.getString(1));
@@ -2204,6 +2215,6 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT {
 			}
 		}
 	}
-    
+	
 }
- 
\ No newline at end of file
+ 


Mime
View raw message