PHOENIX-2110 Primary key changes should be pushed to diverged views
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/5b46793b Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/5b46793b Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/5b46793b Branch: refs/heads/calcite Commit: 5b46793b6951cf1ca04850a5ad771f2cd9f5802a Parents: 498cc55 Author: Samarth <samarth.j...@salesforce.com> Authored: Wed Jul 22 11:46:05 2015 -0700 Committer: Samarth <samarth.j...@salesforce.com> Committed: Wed Jul 22 11:46:05 2015 -0700 ---------------------------------------------------------------------- .../apache/phoenix/end2end/AlterTableIT.java | 929 +--------------- .../phoenix/end2end/AlterTableWithViewsIT.java | 1047 ++++++++++++++++++ .../coprocessor/MetaDataEndpointImpl.java | 755 ++++++++----- 3 files changed, 1536 insertions(+), 1195 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/5b46793b/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 1758dd4..4053301 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 @@ -18,7 +18,6 @@ package org.apache.phoenix.end2end; import static org.apache.hadoop.hbase.HColumnDescriptor.DEFAULT_REPLICATION_SCOPE; -import static org.apache.phoenix.exception.SQLExceptionCode.CANNOT_MUTATE_TABLE; import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; import static org.apache.phoenix.util.TestUtil.closeConnection; import static org.apache.phoenix.util.TestUtil.closeStatement; @@ -33,11 +32,9 @@ 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.Collections; -import java.util.List; import java.util.Map; import java.util.Properties; @@ -46,17 +43,13 @@ import org.apache.hadoop.hbase.HTableDescriptor; import org.apache.hadoop.hbase.KeepDeletedCells; import org.apache.hadoop.hbase.client.HBaseAdmin; import org.apache.hadoop.hbase.util.Bytes; -import org.apache.phoenix.compile.QueryPlan; import org.apache.phoenix.coprocessor.MetaDataProtocol; 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.query.QueryConstants; -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.schema.TableNotFoundException; import org.apache.phoenix.util.IndexUtil; import org.apache.phoenix.util.PhoenixRuntime; @@ -66,8 +59,6 @@ import org.apache.phoenix.util.SchemaUtil; import org.junit.BeforeClass; import org.junit.Test; -import com.google.common.base.Objects; - /** * * A lot of tests in this class test HBase level properties. As a result, @@ -2007,923 +1998,5 @@ public class AlterTableIT extends BaseOwnClusterHBaseManagedTimeIT { conn.close(); } } - - @Test - public void testAddNewColumnsToBaseTableWithViews() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); - try { - conn.createStatement().execute("CREATE TABLE IF NOT EXISTS TABLEWITHVIEW (" - + " ID char(1) NOT NULL," - + " COL1 integer NOT NULL," - + " COL2 bigint NOT NULL," - + " CONSTRAINT NAME_PK PRIMARY KEY (ID, COL1, COL2)" - + " )"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR ) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - // adding a new pk column and a new regular column - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD COL3 varchar(10) PRIMARY KEY, COL4 integer"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 1, 5, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2", "COL3", "COL4"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 1, 7, 5, "ID", "COL1", "COL2", "COL3", "COL4", "VIEW_COL1", "VIEW_COL2"); - } finally { - conn.close(); - } - } - - @Test - public void testAddExistingViewColumnToBaseTableWithViews() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); - try { - conn.createStatement().execute("CREATE TABLE IF NOT EXISTS TABLEWITHVIEW (" - + " ID char(10) NOT NULL," - + " COL1 integer NOT NULL," - + " COL2 bigint NOT NULL," - + " CONSTRAINT NAME_PK PRIMARY KEY (ID, COL1, COL2)" - + " )"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256), VIEW_COL3 VARCHAR, VIEW_COL4 DECIMAL, VIEW_COL5 DECIMAL(10,2), VIEW_COL6 VARCHAR, CONSTRAINT pk PRIMARY KEY (VIEW_COL5, VIEW_COL6) ) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 0, 9, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2", "VIEW_COL3", "VIEW_COL4", "VIEW_COL5", "VIEW_COL6"); - - // upsert single row into view - String dml = "UPSERT INTO VIEWOFTABLE VALUES(?,?,?,?,?, ?, ?, ?, ?)"; - PreparedStatement stmt = conn.prepareStatement(dml); - stmt.setString(1, "view1"); - stmt.setInt(2, 12); - stmt.setInt(3, 13); - stmt.setInt(4, 14); - stmt.setString(5, "view5"); - stmt.setString(6, "view6"); - stmt.setInt(7, 17); - stmt.setInt(8, 18); - stmt.setString(9, "view9"); - stmt.execute(); - conn.commit(); - - try { - // should fail because there is already a view column with same name of different type - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 char(10)"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there is already a view column with same name with different scale - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,1)"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there is already a view column with same name with different length - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(9,2)"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there is already a view column with different length - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL2 VARCHAR"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - // validate that there were no columns added to the table or view - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 0, 9, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2", "VIEW_COL3", "VIEW_COL4", "VIEW_COL5", "VIEW_COL6"); - - // should succeed - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL4 DECIMAL, VIEW_COL2 VARCHAR(256)"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 1, 5, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2", "VIEW_COL4", "VIEW_COL2"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 1, 9, 5, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2", "VIEW_COL3", "VIEW_COL4", "VIEW_COL5", "VIEW_COL6"); - - // query table - ResultSet rs = stmt.executeQuery("SELECT * FROM TABLEWITHVIEW"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertEquals(17, rs.getInt("VIEW_COL4")); - assertFalse(rs.next()); - - // query view - rs = stmt.executeQuery("SELECT * FROM VIEWOFTABLE"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertEquals("view6", rs.getString("VIEW_COL3")); - assertEquals(17, rs.getInt("VIEW_COL4")); - assertEquals(18, rs.getInt("VIEW_COL5")); - assertEquals("view9", rs.getString("VIEW_COL6")); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test - public void testAddExistingViewPkColumnToBaseTableWithViews() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); - try { - conn.createStatement().execute("CREATE TABLE IF NOT EXISTS TABLEWITHVIEW (" - + " ID char(10) NOT NULL," - + " COL1 integer NOT NULL," - + " COL2 integer NOT NULL," - + " CONSTRAINT NAME_PK PRIMARY KEY (ID, COL1, COL2)" - + " )"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256) CONSTRAINT pk PRIMARY KEY (VIEW_COL1, VIEW_COL2)) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - // upsert single row into view - String dml = "UPSERT INTO VIEWOFTABLE VALUES(?,?,?,?,?)"; - PreparedStatement stmt = conn.prepareStatement(dml); - stmt.setString(1, "view1"); - stmt.setInt(2, 12); - stmt.setInt(3, 13); - stmt.setInt(4, 14); - stmt.setString(5, "view5"); - stmt.execute(); - conn.commit(); - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 to the pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 to the pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 to the pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 to the pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY, VIEW_COL2 VARCHAR(256)"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 to the pk in the right order - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL2 VARCHAR(256) PRIMARY KEY, VIEW_COL1 DECIMAL(10,2) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there we have to add both VIEW_COL1 and VIEW_COL2 with the right sort order - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY DESC, VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - // add the pk column of the view to the base table - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY, VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 1, 5, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - assertTableDefinition(conn, "VIEWOFTABLE", PTableType.VIEW, "TABLEWITHVIEW", 1, 5, 5, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - // query table - ResultSet rs = stmt.executeQuery("SELECT * FROM TABLEWITHVIEW"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertFalse(rs.next()); - - // query view - rs = stmt.executeQuery("SELECT * FROM VIEWOFTABLE"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test - public void testAddExistingViewPkColumnToBaseTableWithMultipleViews() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); - try { - conn.createStatement().execute("CREATE TABLE IF NOT EXISTS TABLEWITHVIEW (" - + " ID char(10) NOT NULL," - + " COL1 integer NOT NULL," - + " COL2 integer NOT NULL," - + " CONSTRAINT NAME_PK PRIMARY KEY (ID, COL1, COL2)" - + " )"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE1 ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256) CONSTRAINT pk PRIMARY KEY (VIEW_COL1, VIEW_COL2)) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE1", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE2 ( VIEW_COL3 VARCHAR(256), VIEW_COL4 DECIMAL(10,2) CONSTRAINT pk PRIMARY KEY (VIEW_COL3, VIEW_COL4)) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE2", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL3", "VIEW_COL4"); - - try { - // should fail because there are two view with different pk columns - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL PRIMARY KEY, VIEW_COL2 VARCHAR PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because there are two view with different pk columns - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL3 VARCHAR PRIMARY KEY, VIEW_COL4 DECIMAL PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because slot positions of pks are different - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL PRIMARY KEY, VIEW_COL2 VARCHAR PRIMARY KEY, VIEW_COL3 VARCHAR PRIMARY KEY, VIEW_COL4 DECIMAL PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because slot positions of pks are different - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL3 VARCHAR PRIMARY KEY, VIEW_COL4 DECIMAL PRIMARY KEY, VIEW_COL1 DECIMAL PRIMARY KEY, VIEW_COL2 VARCHAR PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - } finally { - conn.close(); - } - } - - @Test - public void testAddExistingViewPkColumnToBaseTableWithMultipleViewsHavingSamePks() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); - try { - conn.createStatement().execute("CREATE TABLE IF NOT EXISTS TABLEWITHVIEW (" - + " ID char(10) NOT NULL," - + " COL1 integer NOT NULL," - + " COL2 integer NOT NULL," - + " CONSTRAINT NAME_PK PRIMARY KEY (ID, COL1, COL2)" - + " )"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 0, 3, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE1 ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256) CONSTRAINT pk PRIMARY KEY (VIEW_COL1, VIEW_COL2)) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE1", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - conn.createStatement().execute("CREATE VIEW VIEWOFTABLE2 ( VIEW_COL1 DECIMAL(10,2), VIEW_COL2 VARCHAR(256) CONSTRAINT pk PRIMARY KEY (VIEW_COL1, VIEW_COL2)) AS SELECT * FROM TABLEWITHVIEW"); - assertTableDefinition(conn, "VIEWOFTABLE2", PTableType.VIEW, "TABLEWITHVIEW", 0, 5, 3, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - // upsert single row into both view - String dml = "UPSERT INTO VIEWOFTABLE1 VALUES(?,?,?,?,?)"; - PreparedStatement stmt = conn.prepareStatement(dml); - stmt.setString(1, "view1"); - stmt.setInt(2, 12); - stmt.setInt(3, 13); - stmt.setInt(4, 14); - stmt.setString(5, "view5"); - stmt.execute(); - conn.commit(); - dml = "UPSERT INTO VIEWOFTABLE2 VALUES(?,?,?,?,?)"; - stmt = conn.prepareStatement(dml); - stmt.setString(1, "view1"); - stmt.setInt(2, 12); - stmt.setInt(3, 13); - stmt.setInt(4, 14); - stmt.setString(5, "view5"); - stmt.execute(); - conn.commit(); - - try { - // should fail because the view have two extra columns in their pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because the view have two extra columns in their pk - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - try { - // should fail because slot positions of pks are different - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL2 DECIMAL(10,2) PRIMARY KEY, VIEW_COL1 VARCHAR(256) PRIMARY KEY"); - fail(); - } - catch (SQLException e) { - assertEquals("Unexpected exception", CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - - conn.createStatement().execute("ALTER TABLE TABLEWITHVIEW ADD VIEW_COL1 DECIMAL(10,2) PRIMARY KEY, VIEW_COL2 VARCHAR(256) PRIMARY KEY"); - assertTableDefinition(conn, "TABLEWITHVIEW", PTableType.TABLE, null, 1, 5, QueryConstants.BASE_TABLE_BASE_COLUMN_COUNT, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - assertTableDefinition(conn, "VIEWOFTABLE1", PTableType.VIEW, "TABLEWITHVIEW", 1, 5, 5, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - assertTableDefinition(conn, "VIEWOFTABLE2", PTableType.VIEW, "TABLEWITHVIEW", 1, 5, 5, "ID", "COL1", "COL2", "VIEW_COL1", "VIEW_COL2"); - - // query table - ResultSet rs = stmt.executeQuery("SELECT * FROM TABLEWITHVIEW"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertFalse(rs.next()); - - // query both views - rs = stmt.executeQuery("SELECT * FROM VIEWOFTABLE1"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertFalse(rs.next()); - rs = stmt.executeQuery("SELECT * FROM VIEWOFTABLE2"); - assertTrue(rs.next()); - assertEquals("view1", rs.getString("ID")); - assertEquals(12, rs.getInt("COL1")); - assertEquals(13, rs.getInt("COL2")); - assertEquals(14, rs.getInt("VIEW_COL1")); - assertEquals("view5", rs.getString("VIEW_COL2")); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - private 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(getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in BaseColumnCount"), baseColumnCount, rs.getInt("BASE_COLUMN_COUNT")); - assertEquals(getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnCount"), columnCount, rs.getInt("COLUMN_COUNT")); - assertEquals(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(getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in columnName: i=" + i), columnName[i], viewColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_NAME)); - assertEquals(getSystemCatalogEntriesForTable(conn, tableName, "Mismatch in ordinalPosition: i=" + i), i+1, viewColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION)); - // 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()); - - // if you add a non-pk column that already exists in the view - if (!viewColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_NAME).equals(parentTableColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_NAME))) { - continue; - } - - 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); - } - // its ok if the ordinal positions don't match for non-pk columns - else if (!(parentTableColumnsMetadata.getColumnName(columnIndex).equals(PhoenixDatabaseMetaData.ORDINAL_POSITION) && parentTableColumnsRs.getString(PhoenixDatabaseMetaData.COLUMN_FAMILY)!=null)) { - fail(parentTableColumnsMetadata.getColumnName(columnIndex) + " of base table " + parentTableColumnValue + " does not match view "+viewColumnValue) ; - } - } - } - parentTableColumnsRs.next(); - } - } - } - assertFalse(getSystemCatalogEntriesForTable(conn, tableName, ""), viewColumnsRs.next()); - } - - private String getSystemCatalogEntriesForTable(Connection conn, String tableName, String message) throws Exception { - StringBuilder sb = new StringBuilder(message); - sb.append("\n\n\n"); - ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM SYSTEM.CATALOG WHERE TABLE_NAME='"+ tableName +"'"); - ResultSetMetaData metaData = rs.getMetaData(); - int rowNum = 0; - while (rs.next()) { - sb.append(rowNum++).append("------\n"); - for (int i = 1; i <= metaData.getColumnCount(); i++) { - sb.append("\t").append(metaData.getColumnLabel(i)).append("=").append(rs.getString(i)).append("\n"); - } - sb.append("\n"); - } - rs.close(); - return sb.toString(); - } - - @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 testDropColumnOnTableWithViewsNotAllowed() throws Exception { - String baseTable = "testDropColumnOnTableWithViewsNotAllowed"; - String viewName = baseTable + "_view"; - try (Connection conn = DriverManager.getConnection(getUrl())) { - String tableDDL = "CREATE TABLE " + baseTable + " (PK1 VARCHAR NOT NULL PRIMARY KEY, V1 VARCHAR, V2 VARCHAR)"; - conn.createStatement().execute(tableDDL); - - String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + baseTable; - conn.createStatement().execute(viewDDL); - - String dropColumn = "ALTER TABLE " + baseTable + " DROP COLUMN V2"; - try { - conn.createStatement().execute(dropColumn); - fail("Dropping column on a base table that has views is not allowed"); - } catch (SQLException e) { - assertEquals(CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode()); - } - } - } - - @Test - public void testAlteringViewThatHasChildViews() throws Exception { - String baseTable = "testAlteringViewThatHasChildViews"; - String childView = "childView"; - String grandChildView = "grandChildView"; - try (Connection conn = DriverManager.getConnection(getUrl())) { - String baseTableDDL = - "CREATE TABLE " + baseTable + " (TENANT_ID VARCHAR NOT NULL, PK2 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK2))"; - conn.createStatement().execute(baseTableDDL); - - String childViewDDL = "CREATE VIEW " + childView + " AS SELECT * FROM " + baseTable; - conn.createStatement().execute(childViewDDL); - - String addColumnToChildViewDDL = - "ALTER VIEW " + childView + " ADD CHILD_VIEW_COL VARCHAR"; - conn.createStatement().execute(addColumnToChildViewDDL); - - String grandChildViewDDL = - "CREATE VIEW " + grandChildView + " AS SELECT * FROM " + childView; - conn.createStatement().execute(grandChildViewDDL); - - // dropping base table column from child view should succeed - String dropColumnFromChildView = "ALTER VIEW " + childView + " DROP COLUMN V2"; - conn.createStatement().execute(dropColumnFromChildView); - - // dropping view specific column from child view should succeed - dropColumnFromChildView = "ALTER VIEW " + childView + " DROP COLUMN CHILD_VIEW_COL"; - conn.createStatement().execute(dropColumnFromChildView); - - // Adding column to view that has child views is allowed - String addColumnToChildView = "ALTER VIEW " + childView + " ADD V5 VARCHAR"; - conn.createStatement().execute(addColumnToChildView); - // V5 column should be visible now for childView - conn.createStatement().execute("SELECT V5 FROM " + childView); - - // However, column V5 shouldn't have propagated to grandChildView. Not till PHOENIX-2054 is fixed. - try { - conn.createStatement().execute("SELECT V5 FROM " + grandChildView); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); - } - - // dropping column from the grand child view, however, should work. - String dropColumnFromGrandChildView = - "ALTER VIEW " + grandChildView + " DROP COLUMN CHILD_VIEW_COL"; - conn.createStatement().execute(dropColumnFromGrandChildView); - - // similarly, dropping column inherited from the base table should work. - dropColumnFromGrandChildView = "ALTER VIEW " + grandChildView + " DROP COLUMN V2"; - conn.createStatement().execute(dropColumnFromGrandChildView); - } - } - - @Test - public void testDivergedViewsStayDiverged() throws Exception { - String baseTable = "testDivergedViewsStayDiverged"; - String view1 = baseTable + "_view1"; - String view2 = baseTable + "_view2"; - try (Connection conn = DriverManager.getConnection(getUrl())) { - String tableDDL = "CREATE TABLE " + baseTable + " (PK1 VARCHAR NOT NULL PRIMARY KEY, V1 VARCHAR, V2 VARCHAR)"; - conn.createStatement().execute(tableDDL); - - String viewDDL = "CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable; - conn.createStatement().execute(viewDDL); - - viewDDL = "CREATE VIEW " + view2 + " AS SELECT * FROM " + baseTable; - conn.createStatement().execute(viewDDL); - - // Drop the column inherited from base table to make it diverged - String dropColumn = "ALTER VIEW " + view1 + " DROP COLUMN V2"; - conn.createStatement().execute(dropColumn); - - String alterBaseTable = "ALTER TABLE " + baseTable + " ADD V3 VARCHAR"; - conn.createStatement().execute(alterBaseTable); - - // Column V3 shouldn't have propagated to the diverged view. - String sql = "SELECT V3 FROM " + view1; - try { - conn.createStatement().execute(sql); - } catch (SQLException e) { - assertEquals(SQLExceptionCode.COLUMN_NOT_FOUND.getErrorCode(), e.getErrorCode()); - } - - // However, column V3 should have propagated to the non-diverged view. - sql = "SELECT V3 FROM " + view2; - conn.createStatement().execute(sql); - } - } - - @Test - public void testAddingColumnToBaseTablePropagatesToEntireViewHierarchy() 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); - } - - } - - } - - @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.getMetaDataCache().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.getMetaDataCache().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; - } - - private Connection getTenantConnection(String tenantId) throws Exception { - Properties tenantProps = new Properties(); - tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); - return DriverManager.getConnection(getUrl(), tenantProps); - } - - @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 tenantConn = getTenantConnection(tenant1)) { - // create tenant specific view for tenant1 - view1 - tenantConn.createStatement().execute("CREATE VIEW " + view1 + " AS SELECT * FROM " + baseTable); - PhoenixConnection phxConn = tenantConn.unwrap(PhoenixConnection.class); - assertEquals(0, getTableSequenceNumber(phxConn, view1)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view1)); - - // create a view - view2 on view - view1 - tenantConn.createStatement().execute("CREATE VIEW " + view2 + " AS SELECT * FROM " + view1); - assertEquals(0, getTableSequenceNumber(phxConn, view2)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view2)); - - - // create an index on view2 - tenantConn.createStatement().execute("CREATE INDEX " + view2Index + " ON " + view2 + " (v1) include (v2)"); - assertEquals(0, getTableSequenceNumber(phxConn, view2Index)); - assertEquals(4, getMaxKeySequenceNumber(phxConn, view2Index)); - } - try (Connection tenantConn = getTenantConnection(tenant2)) { - // create tenant specific view for tenant2 - view3 - tenantConn.createStatement().execute("CREATE VIEW " + view3 + " AS SELECT * FROM " + baseTable); - PhoenixConnection phxConn = tenantConn.unwrap(PhoenixConnection.class); - assertEquals(0, getTableSequenceNumber(phxConn, view3)); - assertEquals(2, getMaxKeySequenceNumber(phxConn, view3)); - - - // create an index on view3 - tenantConn.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 tenantConn = getTenantConnection(tenant1)) { - - ResultSet rs = tenantConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view1); - PhoenixConnection phxConn = tenantConn.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 = tenantConn.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 tenantConn = getTenantConnection(tenant2)) { - ResultSet rs = tenantConn.createStatement().executeQuery("SELECT K2, K3, V3 FROM " + view3); - PhoenixConnection phxConn = tenantConn.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 tenantConn = getTenantConnection(tenant1)) { - String upsert = "UPSERT INTO " + view2 + " (K1, K2, K3, V1, V2, V3) VALUES ('key1', 'key2', 'key3', 'value1', 'value2', 'value3')"; - tenantConn.createStatement().executeUpdate(upsert); - tenantConn.commit(); - Statement stmt = tenantConn.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 = tenantConn.createStatement().executeQuery(sql); - verifyNewColumns(rs, "value2"); - } - - } - - private static long getTableSequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { - PTable table = conn.getMetaDataCache().getTable(new PTableKey(conn.getTenantId(), SchemaUtil.normalizeIdentifier(tableName))); - return table.getSequenceNumber(); - } - - private static short getMaxKeySequenceNumber(PhoenixConnection conn, String tableName) throws SQLException { - PTable table = conn.getMetaDataCache().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()); - } - } + \ No newline at end of file