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 2015/07/22 20:28:37 UTC
[1/3] phoenix git commit: PHOENIX-2110 Primary key changes should be
pushed to diverged views
Repository: phoenix
Updated Branches:
refs/heads/4.x-HBase-0.98 70d88b321 -> 64319e647
http://git-wip-us.apache.org/repos/asf/phoenix/blob/64319e64/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
index 3f43399..5a68e44 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java
@@ -17,6 +17,8 @@
*/
package org.apache.phoenix.coprocessor;
+import static com.google.common.base.Preconditions.checkArgument;
+import static com.google.common.base.Preconditions.checkState;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.ARRAY_SIZE_BYTES;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.CLASS_NAME_BYTES;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_COUNT_BYTES;
@@ -186,7 +188,6 @@ import org.apache.phoenix.util.UpgradeUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
-import com.google.common.base.Objects;
import com.google.common.cache.Cache;
import com.google.common.collect.Lists;
import com.google.protobuf.ByteString;
@@ -1678,309 +1679,526 @@ public class MetaDataEndpointImpl extends MetaDataProtocol implements Coprocesso
}
}
- private MetaDataMutationResult addRowsToChildViews(PTable table, List<Mutation> tableMetadata, List<Mutation> mutationsForAddingColumnsToViews, byte[] schemaName, byte[] tableName,
+ private final class PutWithOrdinalPosition implements Comparable<PutWithOrdinalPosition>{
+ private final Put put;
+ private final int ordinalPosition;
+
+ public PutWithOrdinalPosition(Put put, int ordinalPos) {
+ this.put = put;
+ this.ordinalPosition = ordinalPos;
+ }
+
+ @Override
+ public int compareTo(PutWithOrdinalPosition o) {
+ return (this.ordinalPosition < o.ordinalPosition ? -1 : this.ordinalPosition > o.ordinalPosition ? 1 : 0);
+ }
+ }
+
+ private static int getOrdinalPosition(PTable table, PColumn col) {
+ return table.getBucketNum() == null ? col.getPosition() + 1 : col.getPosition();
+ }
+
+ private static boolean isDivergedView(PTable view) {
+ return view.getBaseColumnCount() == QueryConstants.DIVERGED_VIEW_BASE_COLUMN_COUNT;
+ }
+
+ /**
+ *
+ * Class to keep track of columns and their ordinal positions as we
+ * process through the list of columns to be added.
+ *
+ */
+ private static class ColumnOrdinalPositionUpdateList {
+ final List<byte[]> columnKeys = new ArrayList<>(10);
+ int offset;
+
+ int size() {
+ return columnKeys.size();
+ }
+
+ private void setOffset(int lowestOrdinalPos) {
+ this.offset = lowestOrdinalPos;
+ }
+
+ private void addColumn(byte[] columnKey, int position) {
+ checkArgument(position >= this.offset);
+ int index = position - offset;
+ int size = columnKeys.size();
+ checkState(index <= size);
+ if (size == 0) {
+ columnKeys.add(columnKey);
+ return;
+ }
+ int stopIndex = size;
+ // Check if an entry for this column key is already there.
+ for (int i = 0; i < size; i++) {
+ if (Bytes.equals(columnKeys.get(i), columnKey)) {
+ stopIndex = i;
+ break;
+ }
+ }
+ if (stopIndex == size) {
+ /*
+ * The column key is not present in the list. So add it at the specified index
+ * and right shift the elements at this index and beyond.
+ */
+ columnKeys.add(index, columnKey);
+ } else {
+ /*
+ * The column key is already present in the list.
+ * Move the elements of the list to the left up to the stop index
+ */
+ for (int i = stopIndex; i > index; i--) {
+ columnKeys.set(i, columnKeys.get(i - 1));
+ }
+ columnKeys.set(index, columnKey);
+ }
+ }
+
+ private int getOrdinalPositionFromListIdx(int listIndex) {
+ checkArgument(listIndex < columnKeys.size());
+ return listIndex + offset;
+ }
+
+ /**
+ * @param columnKey
+ * @return if present - the ordinal position of the column in this list.
+ * If not present - -1.
+ */
+ private int getOrdinalPositionOfColumn(byte[] columnKey) {
+ int i = 0;
+ for (byte[] key : columnKeys) {
+ if (Bytes.equals(key, columnKey)) {
+ return i + offset;
+ }
+ i++;
+ }
+ return -1;
+ }
+ }
+
+ private static byte[] getColumnKey(byte[] viewKey, PColumn column) {
+ return getColumnKey(viewKey, column.getName().getString(), column.getFamilyName() != null ? column.getFamilyName().getString() : null);
+ }
+
+ private static byte[] getColumnKey(byte[] viewKey, String columnName, String columnFamily) {
+ byte[] columnKey = ByteUtil.concat(viewKey, QueryConstants.SEPARATOR_BYTE_ARRAY,
+ Bytes.toBytes(columnName));
+ if (columnFamily != null) {
+ columnKey = ByteUtil.concat(columnKey, QueryConstants.SEPARATOR_BYTE_ARRAY,
+ Bytes.toBytes(columnFamily));
+ }
+ return columnKey;
+ }
+
+ MetaDataMutationResult addRowsToChildViews(PTable basePhysicalTable, List<Mutation> tableMetadata, List<Mutation> mutationsForAddingColumnsToViews, byte[] schemaName, byte[] tableName,
List<ImmutableBytesPtr> invalidateList, long clientTimeStamp, TableViewFinderResult childViewsResult,
HRegion region, List<RowLock> locks) throws IOException, SQLException {
-
+ List<PutWithOrdinalPosition> columnPutsForBaseTable = new ArrayList<>(tableMetadata.size());
+ // Isolate the puts relevant to adding columns. Also figure out what kind of columns are being added.
+ for (Mutation m : tableMetadata) {
+ if (m instanceof Put) {
+ byte[][] rkmd = new byte[5][];
+ int pkCount = getVarChars(m.getRow(), rkmd);
+ if (m instanceof Put && pkCount > COLUMN_NAME_INDEX
+ && Bytes.compareTo(schemaName, rkmd[SCHEMA_NAME_INDEX]) == 0
+ && Bytes.compareTo(tableName, rkmd[TABLE_NAME_INDEX]) == 0) {
+ columnPutsForBaseTable.add(new PutWithOrdinalPosition((Put)m, getInteger((Put)m, TABLE_FAMILY_BYTES, ORDINAL_POSITION_BYTES)));
+ }
+ }
+ }
+ // Sort the puts by ordinal position
+ Collections.sort(columnPutsForBaseTable);
+ assert columnPutsForBaseTable.size() > 0;
for (Result viewResult : childViewsResult.getResults()) {
+ short deltaNumPkColsSoFar = 0;
+ short columnsAddedToView = 0;
+ short columnsAddedToBaseTable = 0;
byte[][] rowViewKeyMetaData = new byte[3][];
getVarChars(viewResult.getRow(), 3, rowViewKeyMetaData);
- byte[] viewTenantId = rowViewKeyMetaData[PhoenixDatabaseMetaData.TENANT_ID_INDEX];
- byte[] viewSchemaName = rowViewKeyMetaData[PhoenixDatabaseMetaData.SCHEMA_NAME_INDEX];
- byte[] viewName = rowViewKeyMetaData[PhoenixDatabaseMetaData.TABLE_NAME_INDEX];
- byte[] viewKey = SchemaUtil.getTableKey(viewTenantId, viewSchemaName, viewName);
+ byte[] viewKey = SchemaUtil.getTableKey(rowViewKeyMetaData[PhoenixDatabaseMetaData.TENANT_ID_INDEX],
+ rowViewKeyMetaData[PhoenixDatabaseMetaData.SCHEMA_NAME_INDEX],
+ rowViewKeyMetaData[PhoenixDatabaseMetaData.TABLE_NAME_INDEX]);
+
// lock the rows corresponding to views so that no other thread can modify the view meta-data
RowLock viewRowLock = acquireLock(region, viewKey, locks);
PTable view = doGetTable(viewKey, clientTimeStamp, viewRowLock);
- if (view.getBaseColumnCount() == QueryConstants.DIVERGED_VIEW_BASE_COLUMN_COUNT) {
- // if a view has diverged from the base table, we don't allow schema changes
- // to be propagated to it.
- // FIXME: We should allow PK changes to be propagated to a diverged view See PHOENIX-2110
- continue;
- }
- int numColsAddedToBaseTable = 0;
- int numColsAddedToView = 0;
- short deltaNumPkColsSoFar = 0;
- PColumn existingViewColumn = null;
- List<PColumn> viewPkColumns = Lists.newArrayList(view.getPKColumns());
- boolean addingPKColumn=false;
- for (Mutation m : tableMetadata) {
+
+ ColumnOrdinalPositionUpdateList ordinalPositionList = new ColumnOrdinalPositionUpdateList();
+ List<PColumn> viewPkCols = new ArrayList<>(view.getPKColumns());
+ boolean addingExistingPkCol = false;
+ int numCols = view.getColumns().size();
+ for (PutWithOrdinalPosition p : columnPutsForBaseTable) {
+ Put baseTableColumnPut = p.put;
+ PColumn existingViewColumn = null;
byte[][] rkmd = new byte[5][];
- int pkCount = getVarChars(m.getRow(), rkmd);
- if (m instanceof Put && pkCount > COLUMN_NAME_INDEX
- && Bytes.compareTo(schemaName, rkmd[SCHEMA_NAME_INDEX]) == 0
- && Bytes.compareTo(tableName, rkmd[TABLE_NAME_INDEX]) == 0) {
- try {
- // Maybe deserving of a new SchemaUtil.getColumnByName(byte[] familyName, String columnName) function
- String columnName = Bytes.toString(rkmd[COLUMN_NAME_INDEX]);
- existingViewColumn = rkmd[FAMILY_NAME_INDEX] == null
- ? view.getPKColumn(columnName)
- : view.getColumnFamily(rkmd[FAMILY_NAME_INDEX]).getColumn(columnName);
- }
- catch (ColumnFamilyNotFoundException e) {
+ getVarChars(baseTableColumnPut.getRow(), rkmd);
+ String columnName = Bytes.toString(rkmd[COLUMN_NAME_INDEX]);
+ String columnFamily = rkmd[FAMILY_NAME_INDEX] == null ? null : Bytes.toString(rkmd[FAMILY_NAME_INDEX]);
+ try {
+ existingViewColumn = columnFamily == null ? view.getColumn(columnName) : view.getColumnFamily(
+ columnFamily).getColumn(columnName);
+ } catch (ColumnFamilyNotFoundException e) {
+ // ignore since it means that the column family is not present for the column to be added.
+ } catch (ColumnNotFoundException e) {
+ // ignore since it means the column is not present in the view
+ }
+
+ boolean isPkCol = columnFamily == null;
+ byte[] columnKey = getColumnKey(viewKey, columnName, columnFamily);
+ if (existingViewColumn != null) {
+ MetaDataMutationResult result = validateColumnForAddToBaseTable(existingViewColumn, baseTableColumnPut, basePhysicalTable, isPkCol, view);
+ if (result != null) {
+ return result;
}
- catch (ColumnNotFoundException e) {
- } // Ignore - means column family or column name don't exist
-
- Put p = (Put)m;
- byte[] columnKey = ByteUtil.concat(viewKey, QueryConstants.SEPARATOR_BYTE_ARRAY, rkmd[COLUMN_NAME_INDEX]);
- if (rkmd[FAMILY_NAME_INDEX] != null) {
- columnKey = ByteUtil.concat(columnKey, QueryConstants.SEPARATOR_BYTE_ARRAY, rkmd[FAMILY_NAME_INDEX]);
+ if (isPkCol) {
+ viewPkCols.remove(existingViewColumn);
+ addingExistingPkCol = true;
}
- Put viewColumnDefinitionPut = new Put(columnKey, clientTimeStamp);
- for (Cell cell : p.getFamilyCellMap().values().iterator().next()) {
- viewColumnDefinitionPut.add(CellUtil.createCell(columnKey, CellUtil.cloneFamily(cell),
+ /*
+ * For views that are not diverged, we need to make sure that the existing columns
+ * have the same ordinal position as in the base table. This is important because
+ * we rely on the ordinal position of the column to figure out whether dropping a
+ * column from the view will end up diverging the view from the base table.
+ *
+ * For already diverged views, we don't care about the ordinal position of the existing column.
+ */
+ if (!isDivergedView(view)) {
+ int newOrdinalPosition = p.ordinalPosition;
+ // Check if the ordinal position of the column was getting updated from previous add column
+ // mutations.
+ int existingOrdinalPos = ordinalPositionList.getOrdinalPositionOfColumn(columnKey);
+ if (ordinalPositionList.size() == 0) {
+ /*
+ * No ordinal positions to be updated are in the list. In that case, check whether the
+ * existing ordinal position of the column is different from its new ordinal position.
+ * If yes, then initialize the ordinal position list with this column's ordinal position
+ * as the offset.
+ */
+ existingOrdinalPos = getOrdinalPosition(view, existingViewColumn);
+ if (existingOrdinalPos != newOrdinalPosition) {
+ ordinalPositionList.setOffset(newOrdinalPosition);
+ ordinalPositionList.addColumn(columnKey, newOrdinalPosition);
+ for (PColumn col : view.getColumns()) {
+ int ordinalPos = getOrdinalPosition(view, col);
+ if (ordinalPos >= newOrdinalPosition) {
+ if (ordinalPos == existingOrdinalPos) {
+ /*
+ * No need to update ordinal positions of columns beyond the existing column's
+ * old ordinal position.
+ */
+ break;
+ }
+ // increment ordinal position of columns occurring after this column by 1
+ int updatedPos = ordinalPos + 1;
+ ordinalPositionList.addColumn(getColumnKey(viewKey, col), updatedPos);
+ }
+ }
+ }
+ } else {
+ if (existingOrdinalPos != newOrdinalPosition) {
+ ordinalPositionList.addColumn(columnKey, newOrdinalPosition);
+ }
+ }
+ columnsAddedToBaseTable++;
+ }
+ } else {
+ // The column doesn't exist in the view.
+ Put viewColumnPut = new Put(columnKey, clientTimeStamp);
+ for (Cell cell : baseTableColumnPut.getFamilyCellMap().values().iterator().next()) {
+ viewColumnPut.add(CellUtil.createCell(columnKey, CellUtil.cloneFamily(cell),
CellUtil.cloneQualifier(cell), cell.getTimestamp(), cell.getTypeByte(),
CellUtil.cloneValue(cell)));
}
-
- // if there is already a view column with the same name as the base table column we are trying to add
- if (existingViewColumn != null) {
- List<Cell> dataTypes = viewColumnDefinitionPut
- .get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DATA_TYPE_BYTES);
- if (dataTypes != null && dataTypes.size() > 0) {
- Cell cell = dataTypes.get(0);
- int typeId = PInteger.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(), SortOrder.getDefault());
- PDataType dataType = PDataType.fromTypeId(typeId);
- if (!existingViewColumn.getDataType().equals(dataType)) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
- }
- }
- // if adding a pk column to the base table
- if (rkmd[FAMILY_NAME_INDEX] == null && rkmd[COLUMN_NAME_INDEX] != null) {
- List<Cell> keySeqCells = viewColumnDefinitionPut.get(
- PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.KEY_SEQ_BYTES);
- if (keySeqCells != null && keySeqCells.size() > 0) {
- Cell cell = keySeqCells.get(0);
- int keySeq = PSmallint.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(), SortOrder.getDefault());
- int pkPosition = SchemaUtil.getPKPosition(view, existingViewColumn)+1;
- if (pkPosition!=keySeq) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
- }
- }
- List<Cell> sortOrders = viewColumnDefinitionPut.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.SORT_ORDER_BYTES);
- SortOrder sortOrder = SortOrder.getDefault();
- if (sortOrders != null && sortOrders.size() > 0) {
- Cell cell = sortOrders.get(0);
- int sortOrderInt = PInteger.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(), SortOrder.getDefault());
- sortOrder = SortOrder.fromSystemValue(sortOrderInt);
- }
- if (!Objects.equal(sortOrder,existingViewColumn.getSortOrder())) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
- }
- viewPkColumns.remove(existingViewColumn);
- }
- // if there is an existing view column that matches the column being added to the base table and if the column being added has a null
- // scale or maxLength, we need to explicitly do a put to set the scale or maxLength to null (in case the view column has the scale or
- // max length set)
- Integer maxLength = null;
- List<Cell> columnSizes = viewColumnDefinitionPut.get(
- PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.COLUMN_SIZE_BYTES);
- if (columnSizes != null && columnSizes.size() > 0) {
- Cell cell = columnSizes.get(0);
- maxLength = cell.getValueArray()==null ? null :
- PInteger.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(), SortOrder.getDefault());
- }
- if (!Objects.equal(maxLength,existingViewColumn.getMaxLength())) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
- }
- Integer scale = null;
- List<Cell> decimalDigits = viewColumnDefinitionPut.get(
- PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DECIMAL_DIGITS_BYTES);
- if (decimalDigits != null && decimalDigits.size() > 0) {
- Cell cell = decimalDigits.get(0);
- scale = cell.getValueArray()==null ? null :
- PInteger.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(), SortOrder.getDefault());
- }
- if (!Objects.equal(scale,existingViewColumn.getScale())) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
+ if (isDivergedView(view)) {
+ if (isPkCol) {
+ /*
+ * Only pk cols of the base table are added to the diverged views. These pk
+ * cols are added at the end.
+ */
+ int lastOrdinalPos = getOrdinalPosition(view, view.getColumns().get(numCols - 1));
+ int newPosition = ++lastOrdinalPos;
+ byte[] ptr = new byte[PInteger.INSTANCE.getByteSize()];
+ PInteger.INSTANCE.getCodec().encodeInt(newPosition, ptr, 0);
+ viewColumnPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.ORDINAL_POSITION_BYTES, clientTimeStamp, ptr);
+ mutationsForAddingColumnsToViews.add(viewColumnPut);
+ } else {
+ continue; // move on to the next column
}
- }
- else {
- // if we are adding a column that already exists in the view, no need to update the base table or view table column count
- numColsAddedToView++;
- }
-
-
- numColsAddedToBaseTable++;
- mutationsForAddingColumnsToViews.add(viewColumnDefinitionPut);
- if (rkmd[FAMILY_NAME_INDEX] == null && rkmd[COLUMN_NAME_INDEX] != null) {
+ } else {
+ int newOrdinalPosition = p.ordinalPosition;
/*
- * If adding a pk column to the base table (and hence the view), see if there are any indexes on
- * the view. If yes, then generate puts for the index header row and column rows.
+ * For a non-diverged view, we need to make sure that the base table column
+ * is added at the right position.
*/
- addingPKColumn=true;
- deltaNumPkColsSoFar++;
- for (PTable index : view.getIndexes()) {
- int oldNumberOfColsInIndex = index.getColumns().size();
-
- byte[] indexColumnKey = ByteUtil.concat(getViewIndexHeaderRowKey(index),
- QueryConstants.SEPARATOR_BYTE_ARRAY,
- IndexUtil.getIndexColumnName(rkmd[FAMILY_NAME_INDEX], rkmd[COLUMN_NAME_INDEX]));
- Put indexColumnDefinitionPut = new Put(indexColumnKey, clientTimeStamp);
-
- // Set the index specific data type for the column
- List<Cell> dataTypes = viewColumnDefinitionPut
- .get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DATA_TYPE_BYTES);
- if (dataTypes != null && dataTypes.size() > 0) {
- Cell dataType = dataTypes.get(0);
- int dataColumnDataType = PInteger.INSTANCE.getCodec().decodeInt(
- dataType.getValueArray(), dataType.getValueOffset(), SortOrder.ASC);
- int indexColumnDataType = IndexUtil.getIndexColumnDataType(true,
- PDataType.fromTypeId(dataColumnDataType)).getSqlType();
- byte[] indexColumnDataTypeBytes = new byte[PInteger.INSTANCE.getByteSize()];
- PInteger.INSTANCE.getCodec().encodeInt(indexColumnDataType, indexColumnDataTypeBytes, 0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DATA_TYPE_BYTES, indexColumnDataTypeBytes);
- }
-
- // Set precision
- List<Cell> decimalDigits = viewColumnDefinitionPut.get(
- PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DECIMAL_DIGITS_BYTES);
- if (decimalDigits != null && decimalDigits.size() > 0) {
- Cell decimalDigit = decimalDigits.get(0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DECIMAL_DIGITS_BYTES, decimalDigit.getValueArray());
- }
-
- // Set size
- List<Cell> columnSizes = viewColumnDefinitionPut.get(
- PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.COLUMN_SIZE_BYTES);
- if (columnSizes != null && columnSizes.size() > 0) {
- Cell columnSize = columnSizes.get(0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.COLUMN_SIZE_BYTES, columnSize.getValueArray());
- }
-
- // Set sort order
- List<Cell> sortOrders = viewColumnDefinitionPut.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.SORT_ORDER_BYTES);
- if (sortOrders != null && sortOrders.size() > 0) {
- Cell sortOrder = sortOrders.get(0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.SORT_ORDER_BYTES, sortOrder.getValueArray());
+ if (ordinalPositionList.size() == 0) {
+ ordinalPositionList.setOffset(newOrdinalPosition);
+ ordinalPositionList.addColumn(columnKey, newOrdinalPosition);
+ for (PColumn col : view.getColumns()) {
+ int ordinalPos = getOrdinalPosition(view, col);
+ if (ordinalPos >= newOrdinalPosition) {
+ // increment ordinal position of columns by 1
+ int updatedPos = ordinalPos + 1;
+ ordinalPositionList.addColumn(getColumnKey(viewKey, col), updatedPos);
+ }
}
-
- // Set data table name
- List<Cell> dataTableNames = viewColumnDefinitionPut.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DATA_TABLE_NAME_BYTES);
- if (dataTableNames != null && dataTableNames.size() > 0) {
- Cell dataTableName = dataTableNames.get(0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.DATA_TABLE_NAME_BYTES, dataTableName.getValueArray());
- }
-
- // Set the ordinal position of the new column.
- byte[] ordinalPositionBytes = new byte[PInteger.INSTANCE.getByteSize()];
- int ordinalPositionOfNewCol = oldNumberOfColsInIndex + deltaNumPkColsSoFar;
- PInteger.INSTANCE.getCodec().encodeInt(ordinalPositionOfNewCol, ordinalPositionBytes, 0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.ORDINAL_POSITION_BYTES, ordinalPositionBytes);
-
- // New PK columns have to be nullable after the first DDL
- byte[] isNullableBytes = PBoolean.INSTANCE.toBytes(true);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.NULLABLE_BYTES, isNullableBytes);
-
- // Set the key sequence for the pk column to be added
- short currentKeySeq = SchemaUtil.getMaxKeySeq(index);
- short newKeySeq = (short)(currentKeySeq + deltaNumPkColsSoFar);
- byte[] keySeqBytes = new byte[PSmallint.INSTANCE.getByteSize()];
- PSmallint.INSTANCE.getCodec().encodeShort(newKeySeq, keySeqBytes, 0);
- indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.KEY_SEQ_BYTES, keySeqBytes);
-
- mutationsForAddingColumnsToViews.add(indexColumnDefinitionPut);
+ } else {
+ ordinalPositionList.addColumn(columnKey, newOrdinalPosition);
}
+ mutationsForAddingColumnsToViews.add(viewColumnPut);
}
- }
- }
- // allow adding a pk columns to base table :
- // 1. if all the view pk columns are exactly the same as the base table pk columns
- // 2. if we are adding all the existing view pk columns to the base table
- if (addingPKColumn && !viewPkColumns.equals(table.getPKColumns())) {
- return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), null);
- }
- if (deltaNumPkColsSoFar > 0) {
- for (PTable index : view.getIndexes()) {
- byte[] indexHeaderRowKey = getViewIndexHeaderRowKey(index);
- Put indexHeaderRowMutation = new Put(indexHeaderRowKey);
-
- // increment sequence number
- long newSequenceNumber = index.getSequenceNumber() + 1;
- byte[] newSequenceNumberPtr = new byte[PLong.INSTANCE.getByteSize()];
- PLong.INSTANCE.getCodec().encodeLong(newSequenceNumber, newSequenceNumberPtr, 0);
- indexHeaderRowMutation.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.TABLE_SEQ_NUM_BYTES, newSequenceNumberPtr);
-
- // increase the column count
- int newColumnCount = index.getColumns().size() + deltaNumPkColsSoFar;
- byte[] newColumnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
- PInteger.INSTANCE.getCodec().encodeInt(newColumnCount, newColumnCountPtr, 0);
- indexHeaderRowMutation.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.COLUMN_COUNT_BYTES, newColumnCountPtr);
-
- // add index row header key to the invalidate list to force clients to fetch the latest meta-data
- invalidateList.add(new ImmutableBytesPtr(indexHeaderRowKey));
- if (index.rowKeyOrderOptimizable()) {
- UpgradeUtil.addRowKeyOrderOptimizableCell(mutationsForAddingColumnsToViews, indexHeaderRowKey, clientTimeStamp);
+ if (isPkCol) {
+ deltaNumPkColsSoFar++;
+ // Set the key sequence for the pk column to be added
+ short currentKeySeq = SchemaUtil.getMaxKeySeq(view);
+ short newKeySeq = (short)(currentKeySeq + deltaNumPkColsSoFar);
+ byte[] keySeqBytes = new byte[PSmallint.INSTANCE.getByteSize()];
+ PSmallint.INSTANCE.getCodec().encodeShort(newKeySeq, keySeqBytes, 0);
+ viewColumnPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.KEY_SEQ_BYTES, keySeqBytes);
+ addMutationsForAddingPkColsToViewIndexes(mutationsForAddingColumnsToViews, clientTimeStamp, view,
+ deltaNumPkColsSoFar, columnName, viewColumnPut);
}
- mutationsForAddingColumnsToViews.add(indexHeaderRowMutation);
+ columnsAddedToView++;
+ columnsAddedToBaseTable++;
}
}
-
- int oldBaseColumnCount = view.getBaseColumnCount();
-
+ /*
+ * Allow adding a pk columns to base table : 1. if all the view pk columns are exactly the same as the base
+ * table pk columns 2. if we are adding all the existing view pk columns to the base table
+ */
+ if (addingExistingPkCol && !viewPkCols.equals(basePhysicalTable.getPKColumns())) {
+ return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable);
+ }
+ addViewIndexesHeaderRowMutations(mutationsForAddingColumnsToViews, invalidateList, clientTimeStamp, view,
+ deltaNumPkColsSoFar);
+
+ // Update the view header rows with new column counts.
Put viewHeaderRowPut = new Put(viewKey, clientTimeStamp);
- byte[] baseColumnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
- PInteger.INSTANCE.getCodec().encodeInt(oldBaseColumnCount + numColsAddedToBaseTable, baseColumnCountPtr, 0);
- byte[] columnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
- PInteger.INSTANCE.getCodec().encodeInt(view.getColumns().size() + numColsAddedToView, columnCountPtr, 0);
- byte[] viewSequencePtr = new byte[PLong.INSTANCE.getByteSize()];
- PLong.INSTANCE.getCodec().encodeLong(view.getSequenceNumber() + 1, viewSequencePtr, 0);
- viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.COLUMN_COUNT_BYTES, clientTimeStamp, columnCountPtr);
- viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.BASE_COLUMN_COUNT_BYTES, clientTimeStamp, baseColumnCountPtr);
- viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
- PhoenixDatabaseMetaData.TABLE_SEQ_NUM_BYTES, clientTimeStamp, viewSequencePtr);
- mutationsForAddingColumnsToViews.add(viewHeaderRowPut);
- if (view.rowKeyOrderOptimizable()) {
- UpgradeUtil.addRowKeyOrderOptimizableCell(mutationsForAddingColumnsToViews, viewKey, clientTimeStamp);
+ if (!isDivergedView(view) && columnsAddedToBaseTable > 0) {
+ // Base column count should only be updated for diverged views.
+ int oldBaseColumnCount = view.getBaseColumnCount();
+ byte[] baseColumnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
+ PInteger.INSTANCE.getCodec().encodeInt(oldBaseColumnCount + columnsAddedToBaseTable, baseColumnCountPtr, 0);
+ viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.BASE_COLUMN_COUNT_BYTES, clientTimeStamp, baseColumnCountPtr);
}
-
- // Update positions of view columns
- for (PColumn column : view.getColumns()) {
- if (column.getPosition() >= oldBaseColumnCount) {
- int newPosition = column.getPosition() + numColsAddedToView + 1;
-
- byte[] k = ByteUtil.concat(viewKey, QueryConstants.SEPARATOR_BYTE_ARRAY, column.getName()
- .getBytes());
- if (column.getFamilyName() != null) {
- k = ByteUtil.concat(k, QueryConstants.SEPARATOR_BYTE_ARRAY, column.getFamilyName().getBytes());
- }
-
- Put positionUpdatePut = new Put(k, clientTimeStamp);
+
+ if (columnsAddedToView > 0) {
+ byte[] columnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
+ PInteger.INSTANCE.getCodec().encodeInt(numCols + columnsAddedToView, columnCountPtr, 0);
+ viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.COLUMN_COUNT_BYTES, clientTimeStamp, columnCountPtr);
+ }
+
+ /*
+ * Increment the sequence number by 1 if:
+ * 1) For a diverged view, there were columns (pk columns) added to the view.
+ * 2) For a non-diverged view if the base column count changed.
+ *
+ */
+ boolean changeSequenceNumber = (isDivergedView(view) && columnsAddedToView > 0)
+ || (!isDivergedView(view) && columnsAddedToBaseTable > 0);
+ if (changeSequenceNumber) {
+ byte[] viewSequencePtr = new byte[PLong.INSTANCE.getByteSize()];
+ PLong.INSTANCE.getCodec().encodeLong(view.getSequenceNumber() + 1, viewSequencePtr, 0);
+ viewHeaderRowPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.TABLE_SEQ_NUM_BYTES, clientTimeStamp, viewSequencePtr);
+
+ mutationsForAddingColumnsToViews.add(viewHeaderRowPut);
+
+ // only invalidate if the sequence number is about to change
+ invalidateList.add(new ImmutableBytesPtr(viewKey));
+
+ // Update the ordinal positions. The list would be non-empty only if the sequence
+ // number will change.
+ int i = 0;
+ for (byte[] columnKey : ordinalPositionList.columnKeys) {
+ int ordinalPosition = ordinalPositionList.getOrdinalPositionFromListIdx(i);
+ Put positionUpdatePut = new Put(columnKey, clientTimeStamp);
byte[] ptr = new byte[PInteger.INSTANCE.getByteSize()];
- PInteger.INSTANCE.getCodec().encodeInt(newPosition, ptr, 0);
+ PInteger.INSTANCE.getCodec().encodeInt(ordinalPosition, ptr, 0);
positionUpdatePut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
PhoenixDatabaseMetaData.ORDINAL_POSITION_BYTES, clientTimeStamp, ptr);
mutationsForAddingColumnsToViews.add(positionUpdatePut);
+ i++;
+ }
+ }
+
+ if (view.rowKeyOrderOptimizable()) {
+ UpgradeUtil.addRowKeyOrderOptimizableCell(mutationsForAddingColumnsToViews, viewKey, clientTimeStamp);
+ }
+ }
+ return null;
+ }
+
+ private MetaDataMutationResult validateColumnForAddToBaseTable(PColumn existingViewColumn, Put columnToBeAdded, PTable basePhysicalTable, boolean isColumnToBeAddPkCol, PTable view) {
+ if (existingViewColumn != null) {
+
+ // Validate data type is same
+ int baseColumnDataType = getInteger(columnToBeAdded, TABLE_FAMILY_BYTES, DATA_TYPE_BYTES);
+ if (baseColumnDataType != existingViewColumn.getDataType().getSqlType()) {
+ return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable);
+ }
+
+ // Validate max length is same
+ int maxLength = getInteger(columnToBeAdded, TABLE_FAMILY_BYTES, COLUMN_SIZE_BYTES);
+ int existingMaxLength = existingViewColumn.getMaxLength() == null ? 0 : existingViewColumn.getMaxLength();
+ if (maxLength != existingMaxLength) {
+ return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable);
+ }
+
+ // Validate scale is same
+ int scale = getInteger(columnToBeAdded, TABLE_FAMILY_BYTES, DECIMAL_DIGITS_BYTES);
+ int existingScale = existingViewColumn.getScale() == null ? 0 : existingViewColumn.getScale();
+ if (scale != existingScale) {
+ return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable);
+ }
+
+ // Validate sort order is same
+ int sortOrder = getInteger(columnToBeAdded, TABLE_FAMILY_BYTES, SORT_ORDER_BYTES);
+ if (sortOrder != existingViewColumn.getSortOrder().getSystemValue()) {
+ return new MetaDataMutationResult(MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable);
+ }
+
+ // if the column to be added to the base table is a pk column, then we need to validate that the key slot position is the same
+ if (isColumnToBeAddPkCol) {
+ List<Cell> keySeqCells = columnToBeAdded.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.KEY_SEQ_BYTES);
+ if (keySeqCells != null && keySeqCells.size() > 0) {
+ Cell cell = keySeqCells.get(0);
+ int keySeq = PSmallint.INSTANCE.getCodec().decodeInt(cell.getValueArray(), cell.getValueOffset(),
+ SortOrder.getDefault());
+ int pkPosition = SchemaUtil.getPKPosition(view, existingViewColumn) + 1;
+ if (pkPosition != keySeq) { return new MetaDataMutationResult(
+ MutationCode.UNALLOWED_TABLE_MUTATION, EnvironmentEdgeManager.currentTimeMillis(), basePhysicalTable); }
}
}
- invalidateList.add(new ImmutableBytesPtr(viewKey));
}
return null;
}
+ private int getInteger(Put p, byte[] family, byte[] qualifier) {
+ List<Cell> cells = p.get(family, qualifier);
+ if (cells != null && cells.size() > 0) {
+ Cell cell = cells.get(0);
+ return (Integer)PInteger.INSTANCE.toObject(cell.getValueArray(), cell.getValueOffset(), cell.getValueLength());
+ }
+ return 0;
+ }
+
+ private void addViewIndexesHeaderRowMutations(List<Mutation> mutationsForAddingColumnsToViews,
+ List<ImmutableBytesPtr> invalidateList, long clientTimeStamp, PTable view, short deltaNumPkColsSoFar) {
+ if (deltaNumPkColsSoFar > 0) {
+ for (PTable index : view.getIndexes()) {
+ byte[] indexHeaderRowKey = getViewIndexHeaderRowKey(index);
+ Put indexHeaderRowMutation = new Put(indexHeaderRowKey);
+
+ // increment sequence number
+ long newSequenceNumber = index.getSequenceNumber() + 1;
+ byte[] newSequenceNumberPtr = new byte[PLong.INSTANCE.getByteSize()];
+ PLong.INSTANCE.getCodec().encodeLong(newSequenceNumber, newSequenceNumberPtr, 0);
+ indexHeaderRowMutation.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.TABLE_SEQ_NUM_BYTES, newSequenceNumberPtr);
+
+ // increase the column count
+ int newColumnCount = index.getColumns().size() + deltaNumPkColsSoFar;
+ byte[] newColumnCountPtr = new byte[PInteger.INSTANCE.getByteSize()];
+ PInteger.INSTANCE.getCodec().encodeInt(newColumnCount, newColumnCountPtr, 0);
+ indexHeaderRowMutation.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.COLUMN_COUNT_BYTES, newColumnCountPtr);
+
+ // add index row header key to the invalidate list to force clients to fetch the latest meta-data
+ invalidateList.add(new ImmutableBytesPtr(indexHeaderRowKey));
+ if (index.rowKeyOrderOptimizable()) {
+ UpgradeUtil.addRowKeyOrderOptimizableCell(mutationsForAddingColumnsToViews, indexHeaderRowKey, clientTimeStamp);
+ }
+ mutationsForAddingColumnsToViews.add(indexHeaderRowMutation);
+ }
+ }
+ }
+
+ private void addMutationsForAddingPkColsToViewIndexes(List<Mutation> mutationsForAddingColumnsToViews, long clientTimeStamp,
+ PTable view, short deltaNumPkColsSoFar, String viewPkColumnName, Put viewColumnDefinitionPut) {
+ for (PTable index : view.getIndexes()) {
+ int oldNumberOfColsInIndex = index.getColumns().size();
+
+ byte[] indexColumnKey = ByteUtil.concat(getViewIndexHeaderRowKey(index), QueryConstants.SEPARATOR_BYTE_ARRAY, Bytes.toBytes(IndexUtil.getIndexColumnName(null, viewPkColumnName)));
+ Put indexColumnDefinitionPut = new Put(indexColumnKey, clientTimeStamp);
+
+ // Set the index specific data type for the column
+ int viewPkColumnDataType = getInteger(viewColumnDefinitionPut, TABLE_FAMILY_BYTES, DATA_TYPE_BYTES);
+ byte[] indexColumnDataTypeBytes = new byte[PInteger.INSTANCE.getByteSize()];
+ int indexColumnDataType = IndexUtil.getIndexColumnDataType(true,
+ PDataType.fromTypeId(viewPkColumnDataType)).getSqlType();
+ PInteger.INSTANCE.getCodec().encodeInt(indexColumnDataType, indexColumnDataTypeBytes, 0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.DATA_TYPE_BYTES, indexColumnDataTypeBytes);
+
+
+ // Set precision
+ List<Cell> decimalDigits = viewColumnDefinitionPut.get(
+ PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.DECIMAL_DIGITS_BYTES);
+ if (decimalDigits != null && decimalDigits.size() > 0) {
+ Cell decimalDigit = decimalDigits.get(0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.DECIMAL_DIGITS_BYTES, decimalDigit.getValueArray());
+ }
+
+ // Set size
+ List<Cell> columnSizes = viewColumnDefinitionPut.get(
+ PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.COLUMN_SIZE_BYTES);
+ if (columnSizes != null && columnSizes.size() > 0) {
+ Cell columnSize = columnSizes.get(0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.COLUMN_SIZE_BYTES, columnSize.getValueArray());
+ }
+
+ // Set sort order
+ List<Cell> sortOrders = viewColumnDefinitionPut.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.SORT_ORDER_BYTES);
+ if (sortOrders != null && sortOrders.size() > 0) {
+ Cell sortOrder = sortOrders.get(0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.SORT_ORDER_BYTES, sortOrder.getValueArray());
+ }
+
+ // Set data table name
+ List<Cell> dataTableNames = viewColumnDefinitionPut.get(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.DATA_TABLE_NAME_BYTES);
+ if (dataTableNames != null && dataTableNames.size() > 0) {
+ Cell dataTableName = dataTableNames.get(0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.DATA_TABLE_NAME_BYTES, dataTableName.getValueArray());
+ }
+
+ // Set the ordinal position of the new column.
+ byte[] ordinalPositionBytes = new byte[PInteger.INSTANCE.getByteSize()];
+ int ordinalPositionOfNewCol = oldNumberOfColsInIndex + deltaNumPkColsSoFar;
+ PInteger.INSTANCE.getCodec().encodeInt(ordinalPositionOfNewCol, ordinalPositionBytes, 0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.ORDINAL_POSITION_BYTES, ordinalPositionBytes);
+
+ // New PK columns have to be nullable after the first DDL
+ byte[] isNullableBytes = PBoolean.INSTANCE.toBytes(true);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.NULLABLE_BYTES, isNullableBytes);
+
+ // Set the key sequence for the pk column to be added
+ short currentKeySeq = SchemaUtil.getMaxKeySeq(index);
+ short newKeySeq = (short)(currentKeySeq + deltaNumPkColsSoFar);
+ byte[] keySeqBytes = new byte[PSmallint.INSTANCE.getByteSize()];
+ PSmallint.INSTANCE.getCodec().encodeShort(newKeySeq, keySeqBytes, 0);
+ indexColumnDefinitionPut.add(PhoenixDatabaseMetaData.TABLE_FAMILY_BYTES,
+ PhoenixDatabaseMetaData.KEY_SEQ_BYTES, keySeqBytes);
+
+ mutationsForAddingColumnsToViews.add(indexColumnDefinitionPut);
+ }
+ }
+
private byte[] getViewIndexHeaderRowKey(PTable index) {
byte[] tenantIdBytes = index.getKey().getTenantId() != null ? index.getKey().getTenantId().getBytes() : EMPTY_BYTE_ARRAY;
byte[] schemaNameBytes = index.getSchemaName() != null ? index.getSchemaName().getBytes() : EMPTY_BYTE_ARRAY;
[2/3] phoenix git commit: PHOENIX-2110 Primary key changes should be
pushed to diverged views
Posted by sa...@apache.org.
http://git-wip-us.apache.org/repos/asf/phoenix/blob/64319e64/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableWithViewsIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableWithViewsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableWithViewsIT.java
new file mode 100644
index 0000000..0399af2
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableWithViewsIT.java
@@ -0,0 +1,1047 @@
+/*
+ * 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.exception.SQLExceptionCode.CANNOT_MUTATE_TABLE;
+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.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.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.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.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 AlterTableWithViewsIT extends BaseHBaseManagedTimeIT {
+
+ @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_COL4", "VIEW_COL2", "VIEW_COL1", "VIEW_COL3", "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));
+ int viewColOrdinalPos = viewColumnsRs.getInt(PhoenixDatabaseMetaData.ORDINAL_POSITION);
+ assertEquals(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(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(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");
+ }
+
+ }
+
+ @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 tenantConn = getTenantConnection("tenant2")) {
+ tenantConn.createStatement().executeUpdate(upsert);
+ tenantConn.commit();
+ Statement stmt = tenantConn.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 = tenantConn.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");
+ }
+ }
+
+ 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());
+ assertEquals(values.length, i - 1);
+ }
+}
[3/3] phoenix git commit: PHOENIX-2110 Primary key changes should be
pushed to diverged views
Posted by sa...@apache.org.
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/64319e64
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/64319e64
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/64319e64
Branch: refs/heads/4.x-HBase-0.98
Commit: 64319e647c3f1824583a3ab5ab83c6f8f8307436
Parents: 70d88b3
Author: Samarth <sa...@salesforce.com>
Authored: Wed Jul 22 11:28:19 2015 -0700
Committer: Samarth <sa...@salesforce.com>
Committed: Wed Jul 22 11:28:19 2015 -0700
----------------------------------------------------------------------
.../apache/phoenix/end2end/AlterTableIT.java | 929 +---------------
.../phoenix/end2end/AlterTableWithViewsIT.java | 1047 ++++++++++++++++++
.../coprocessor/MetaDataEndpointImpl.java | 756 ++++++++-----
3 files changed, 1535 insertions(+), 1197 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/64319e64/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