This is an automated email from the ASF dual-hosted git repository. richardantal pushed a commit to branch 4.16 in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.16 by this push: new 59466c7 PHOENIX-6519 Make SchemaTool work with lower case table and column names 59466c7 is described below commit 59466c7d15c96de35b0577f9b8482fe19f3a90f3 Author: Richard Antal <antal97rich...@gmail.com> AuthorDate: Mon Jul 26 16:34:44 2021 +0200 PHOENIX-6519 Make SchemaTool work with lower case table and column names Change-Id: I643ed8c3db1ec7ebd0a68b13412a22ff7249f037 --- .../apache/phoenix/end2end/ShowCreateTableIT.java | 1 - .../schema/tool/SchemaToolExtractionIT.java | 72 +++++++++++++++++++++ .../schema/tool/SchemaExtractionProcessor.java | 62 +++++++++++------- .../java/org/apache/phoenix/util/SchemaUtil.java | 73 ++++++++++++++++++++++ 4 files changed, 183 insertions(+), 25 deletions(-) diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ShowCreateTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ShowCreateTableIT.java index f4502d0..4895a84 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ShowCreateTableIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ShowCreateTableIT.java @@ -43,7 +43,6 @@ public class ShowCreateTableIT extends ParallelStatsDisabledIT { rs.getString(1).contains(ddl)); } - @Ignore @Test public void testShowCreateTableLowerCase() throws Exception { Properties props = new Properties(); diff --git a/phoenix-core/src/it/java/org/apache/phoenix/schema/tool/SchemaToolExtractionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/schema/tool/SchemaToolExtractionIT.java index 89bd53a..0fd1603 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/schema/tool/SchemaToolExtractionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/schema/tool/SchemaToolExtractionIT.java @@ -71,6 +71,19 @@ public class SchemaToolExtractionIT extends ParallelStatsEnabledIT { } @Test + public void testCreateTableStatementLowerCase() throws Exception { + String tableName = "lowecasetbl1"; + String schemaName = "lowecaseschemaname1"; + String pTableFullName = SchemaUtil.getEscapedTableName(schemaName, tableName); + String createTableStmt = "CREATE TABLE "+ pTableFullName + "(\"smallK\" VARCHAR NOT NULL PRIMARY KEY, " + + "\"asd\".V1 VARCHAR, \"foo\".\"bar\" VARCHAR) TTL=2592000, IMMUTABLE_ROWS=true, DISABLE_WAL=true"; + List<String> queries = new ArrayList<String>(){}; + queries.add(createTableStmt); + String result = runSchemaExtractionTool("\"" + schemaName + "\"", "\"" + tableName + "\"", null, queries); + Assert.assertEquals(createTableStmt, result); + } + + @Test public void testCreateIndexStatement() throws Exception { String tableName = generateUniqueName(); String schemaName = generateUniqueName(); @@ -124,6 +137,42 @@ public class SchemaToolExtractionIT extends ParallelStatsEnabledIT { } @Test + public void testCreateLocalIndexStatement() throws Exception { + String tableName = generateUniqueName(); + String schemaName = generateUniqueName(); + String indexName = generateUniqueName(); + String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_WAL=true"; + String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName); + String createTableStatement = "CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + + properties; + String createIndexStatement = "CREATE LOCAL INDEX "+indexName + " ON "+pTableFullName+"(v1 DESC, k) INCLUDE (v2)"; + List<String> queries = new ArrayList<String>(){}; + queries.add(createTableStatement); + queries.add(createIndexStatement); + + String result = runSchemaExtractionTool(schemaName, indexName, null, queries); + Assert.assertEquals(createIndexStatement.toUpperCase(), result.toUpperCase()); + } + + @Test + public void testCreateIndexStatementLowerCase() throws Exception { + String tableName = "lowercase" + generateUniqueName(); + String schemaName = "lowercase" + generateUniqueName(); + String indexName = "\"lowercaseIND" + generateUniqueName() + "\""; + String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_WAL=true"; + String pTableFullName = SchemaUtil.getEscapedTableName(schemaName, tableName); + String createTableStatement = "CREATE TABLE " + pTableFullName + "(\"k\" VARCHAR NOT NULL PRIMARY KEY, \"a\".V1 VARCHAR, \"v2\" VARCHAR)" + + properties; + String createIndexStatement = "CREATE INDEX " + indexName + " ON "+ pTableFullName + "(\"a\".V1 DESC, \"k\") INCLUDE (\"v2\")"; + List<String> queries = new ArrayList<String>(){}; + queries.add(createTableStatement); + queries.add(createIndexStatement); + + String result = runSchemaExtractionTool("\"" + schemaName + "\"", indexName, null, queries); + Assert.assertEquals(createIndexStatement, result); + } + + @Test public void testCreateViewStatement() throws Exception { String tableName = generateUniqueName(); String schemaName = generateUniqueName(); @@ -148,6 +197,29 @@ public class SchemaToolExtractionIT extends ParallelStatsEnabledIT { } @Test + public void testCreateViewStatementLowerCase() throws Exception { + String tableName = "lowercase" + generateUniqueName(); + String schemaName = "lowercase" + generateUniqueName(); + String viewName = "lowercase" + generateUniqueName(); + String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_WAL=true"; + + String pTableFullName = SchemaUtil.getEscapedTableName(schemaName, tableName); + String createTableStmt = "CREATE TABLE "+pTableFullName + "(\"k\" BIGINT NOT NULL PRIMARY KEY, " + + "\"a\".V1 VARCHAR, v2 VARCHAR)" + + properties; + String viewFullName = SchemaUtil.getEscapedTableName(schemaName, viewName); + String createView = "CREATE VIEW "+viewFullName + "(ID1 BIGINT, \"id2\" BIGINT NOT NULL, " + + "ID3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (\"id2\", ID3 DESC)) " + + "AS SELECT * FROM " + pTableFullName + " WHERE \"k\" > 3"; + + List<String> queries = new ArrayList<String>(){}; + queries.add(createTableStmt); + queries.add(createView); + String result = runSchemaExtractionTool("\"" + schemaName + "\"", "\"" + viewName + "\"", null, queries); + Assert.assertEquals(createView, result); + } + + @Test public void testCreateViewStatement_customName() throws Exception { String tableName = generateUniqueName(); String schemaName = generateUniqueName(); diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/tool/SchemaExtractionProcessor.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/tool/SchemaExtractionProcessor.java index bf6faf9..656bd31 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/schema/tool/SchemaExtractionProcessor.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/tool/SchemaExtractionProcessor.java @@ -97,13 +97,16 @@ public class SchemaExtractionProcessor implements SchemaProcessor { protected String extractCreateIndexDDL(PTable indexPTable) throws SQLException, IOException { - String pTableName = indexPTable.getTableName().getString(); + String quotedIndexTableName = SchemaUtil + .getFullTableNameWithQuotes(null, indexPTable.getTableName().getString()); String baseTableName = indexPTable.getParentTableName().getString(); - String baseTableFullName = SchemaUtil - .getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName); + String baseTableFullName = indexPTable.getSchemaName().getString() + "." + baseTableName; PTable dataPTable = getPTable(baseTableFullName); + String quotedBaseTableFullName = SchemaUtil + .getFullTableNameWithQuotes(indexPTable.getSchemaName().getString(), baseTableName); + String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable); String indexedColumnsString = getIndexedColumnsString(indexPTable, dataPTable, defaultCF); String coveredColumnsString = getCoveredColumnsString(indexPTable, defaultCF); @@ -115,8 +118,8 @@ public class SchemaExtractionProcessor implements SchemaProcessor { setHTableProperties(htd); } String propertiesString = convertPropertiesToString(); - return generateIndexDDLString(baseTableFullName, indexedColumnsString, coveredColumnsString, - indexPTable.getIndexType().equals(PTable.IndexType.LOCAL), pTableName, propertiesString); + return generateIndexDDLString(quotedBaseTableFullName, indexedColumnsString, coveredColumnsString, + indexPTable.getIndexType().equals(PTable.IndexType.LOCAL), quotedIndexTableName, propertiesString); } //TODO: Indexed on an expression @@ -181,11 +184,17 @@ public class SchemaExtractionProcessor implements SchemaProcessor { private String extractIndexColumn(String columnName, String defaultCF) { String [] columnNameSplit = columnName.split(":"); - if(columnNameSplit[0].equals("") || columnNameSplit[0].equalsIgnoreCase(defaultCF)) { - return columnNameSplit[1]; + if(columnNameSplit[0].equals("") || columnNameSplit[0].equalsIgnoreCase(defaultCF) || + (defaultCF.startsWith("L#") && columnNameSplit[0].equalsIgnoreCase(defaultCF.substring(2)))) { + return SchemaUtil.formatColumnName(columnNameSplit[1]); } else { - return columnNameSplit.length > 1 ? - String.format("\"%s\".\"%s\"", columnNameSplit[0], columnNameSplit[1]) : columnNameSplit[0]; + if (columnNameSplit.length > 1) { + String schema = SchemaUtil.formatSchemaName(columnNameSplit[0]); + String name = SchemaUtil.formatColumnName(columnNameSplit[1]); + return String.format("%s.%s", schema, name); + } else { + return SchemaUtil.formatColumnName(columnNameSplit[0]); + } } } @@ -204,10 +213,10 @@ public class SchemaExtractionProcessor implements SchemaProcessor { return coveredColumnsBuilder.toString(); } - protected String generateIndexDDLString(String baseTableFullName, String indexedColumnString, - String coveredColumnString, boolean local, String pTableName, String properties) { + protected String generateIndexDDLString(String quotedBaseTableFullName, String indexedColumnString, + String coveredColumnString, boolean local, String quotedIndexTableName, String properties) { StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_INDEX, - local ? "LOCAL " : "", pTableName, baseTableFullName)); + local ? "LOCAL " : "", quotedIndexTableName, quotedBaseTableFullName)); outputBuilder.append("("); outputBuilder.append(indexedColumnString); outputBuilder.append(")"); @@ -230,7 +239,10 @@ public class SchemaExtractionProcessor implements SchemaProcessor { String pSchemaName = table.getSchemaName().getString(); String pTableName = table.getTableName().getString(); String baseTableName = table.getParentTableName().getString(); - String baseTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, baseTableName); + String quotedBaseTableName = SchemaUtil + .getFullTableNameWithQuotes(pSchemaName, baseTableName); + + String baseTableFullName = pSchemaName + "." + baseTableName; PTable baseTable = getPTable(baseTableFullName); String columnInfoString = getColumnInfoStringForView(table, baseTable); @@ -238,15 +250,15 @@ public class SchemaExtractionProcessor implements SchemaProcessor { if(whereClause != null) { whereClause = whereClause.substring(whereClause.indexOf("WHERE")); } - return generateCreateViewDDL(columnInfoString, baseTableFullName, + return generateCreateViewDDL(columnInfoString, quotedBaseTableName, whereClause == null ? "" : " "+whereClause, pSchemaName, pTableName); } - private String generateCreateViewDDL(String columnInfoString, String baseTableFullName, + private String generateCreateViewDDL(String columnInfoString, String quotedBaseTableName, String whereClause, String pSchemaName, String pTableName) { - String viewFullName = SchemaUtil.getPTableFullNameWithQuotes(pSchemaName, pTableName); - StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_VIEW, viewFullName, - columnInfoString, baseTableFullName, whereClause)); + String quotedViewFullName = SchemaUtil.getFullTableNameWithQuotes(pSchemaName, pTableName); + StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_VIEW, + quotedViewFullName, columnInfoString, quotedBaseTableName, whereClause)); return outputBuilder.toString(); } @@ -270,8 +282,9 @@ public class SchemaExtractionProcessor implements SchemaProcessor { private String generateTableDDLString(String columnInfoString, String propertiesString, String pSchemaName, String pTableName) { - String pTableFullName = SchemaUtil.getPTableFullNameWithQuotes(pSchemaName, pTableName); - StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_TABLE, pTableFullName)); + String quotedTableFullName = SchemaUtil.getFullTableNameWithQuotes(pSchemaName, pTableName); + StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_TABLE, + quotedTableFullName)); outputBuilder.append(columnInfoString).append(" ").append(propertiesString); return outputBuilder.toString(); } @@ -455,11 +468,12 @@ public class SchemaExtractionProcessor implements SchemaProcessor { } private String extractColumn(PColumn column) { - String colName = column.getName().getString(); + String colName = SchemaUtil.formatColumnName(column.getName().getString()); if (column.getFamilyName() != null){ - String colFamilyName = column.getFamilyName().getString(); + String colFamilyName = SchemaUtil.formatSchemaName(column.getFamilyName().getString()); // check if it is default column family name - colName = colFamilyName.equals(QueryConstants.DEFAULT_COLUMN_FAMILY)? colName : String.format("\"%s\".\"%s\"", colFamilyName, colName); + colName = colFamilyName.equals(QueryConstants.DEFAULT_COLUMN_FAMILY) ? colName : + String.format("%s.%s", colFamilyName, colName); } boolean isArrayType = column.getDataType().isArrayType(); String type = column.getDataType().getSqlTypeName(); @@ -524,7 +538,7 @@ public class SchemaExtractionProcessor implements SchemaProcessor { private String extractPKConstraint(List<PColumn> pkColumns) { ArrayList<String> colDefs = new ArrayList<>(pkColumns.size()); for (PColumn pkCol : pkColumns) { - colDefs.add(pkCol.getName().getString() + extractPKColumnAttributes(pkCol)); + colDefs.add(SchemaUtil.formatColumnName(pkCol.getName().getString()) + extractPKColumnAttributes(pkCol)); } return StringUtils.join(colDefs, ", "); } diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/SchemaUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/SchemaUtil.java index 571da1e..7a1deb7 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/SchemaUtil.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/SchemaUtil.java @@ -1252,6 +1252,79 @@ public class SchemaUtil { return columnParseNode.getName(); } + public static String getFullTableNameWithQuotes(String schemaName, String tableName, + boolean schemaNameCaseSensitive, boolean tableNameCaseSensitive) { + String fullTableName; + + if (tableNameCaseSensitive) { + fullTableName = "\"" + tableName + "\""; + } else { + fullTableName = tableName; + } + + if(schemaName != null && schemaName.length() != 0) { + if (schemaNameCaseSensitive) { + fullTableName = "\"" + schemaName + "\"" + QueryConstants.NAME_SEPARATOR + fullTableName; + } else { + fullTableName = schemaName + QueryConstants.NAME_SEPARATOR + fullTableName; + } + } + return fullTableName; + } + + public static String getFullTableNameWithQuotes(String schemaName, String tableName) { + return getFullTableNameWithQuotes(schemaName, tableName, + quotesNeededForSchema(schemaName), quotesNeededForTable(tableName)); + } + + private static boolean quotesNeededForSchema(String name) { + if (Strings.isNullOrEmpty(name) || name.equals(QueryConstants.DEFAULT_COLUMN_FAMILY)) { + return false; + } + return quotesNeededForTable(name); + } + + private static boolean quotesNeededForColumn(String name) { + if (!name.equals("_INDEX_ID") && name.startsWith("_")) { + return true; + } + return isQuotesNeeded(name) || containsLowerCase(name); + } + + private static boolean quotesNeededForTable(String name) { + if (name.startsWith("_")) { + return true; + } + return isQuotesNeeded(name) || containsLowerCase(name); + } + + public static String formatSchemaName(String name) { + if (quotesNeededForSchema(name)) { + name = "\"" + name + "\""; + } + return name; + } + + public static String formatColumnName(String name) { + if (quotesNeededForColumn(name)) { + name = "\"" + name + "\""; + } + return name; + } + + public static boolean containsLowerCase(String name) { + if (Strings.isNullOrEmpty(name)) { + return false; + } + for (int i=0; i<name.toCharArray().length; i++) { + char charAtI = name.charAt(i); + if (Character.isLowerCase(charAtI)){ + return true; + } + } + return false; + } + /** * This function is needed so that SchemaExtractionTool returns a valid DDL with correct * table/schema name that can be parsed