This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 85abe604471 Support query assist column rewrite when insert null value
with encrypt column (#21864)
85abe604471 is described below
commit 85abe604471c44867a50a3fc3c825f61fbe99826
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Mon Oct 31 18:10:10 2022 +0800
Support query assist column rewrite when insert null value with encrypt
column (#21864)
* Support query assist column rewrite when insert null value with encrypt
column
* fix unit test
---
.../EncryptInsertValuesTokenGeneratorTest.java | 10 ++++-----
.../insert/values/InsertValueContextTest.java | 2 +-
.../infra/hint/SQLHintExtractor.java | 3 ++-
.../sql/token/pojo/generic/InsertValue.java | 9 +++++++-
.../RewriteNormalEncryptAlgorithmFixture.java | 6 ++++++
...ewriteQueryAssistedEncryptAlgorithmFixture.java | 6 ++++++
...SchemaMetaDataAwareEncryptAlgorithmFixture.java | 6 ++++++
.../AbstractSQLRewriterParameterizedTest.java | 2 +-
.../parameter/SQLRewriteEngineTestParameters.java | 2 +-
.../SQLRewriteEngineTestParametersBuilder.java | 25 +++++++++++++---------
.../query-with-cipher/dml/insert/insert-column.xml | 5 +++++
.../query-with-plain/dml/insert/insert-column.xml | 5 +++++
12 files changed, 61 insertions(+), 20 deletions(-)
diff --git
a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGeneratorTest.java
b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGeneratorTest.java
index 60f08d50adb..6feb3ea71e7 100644
---
a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGeneratorTest.java
+++
b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGeneratorTest.java
@@ -40,27 +40,27 @@ public final class EncryptInsertValuesTokenGeneratorTest
extends EncryptGenerato
EncryptInsertValuesTokenGenerator encryptInsertValuesTokenGenerator =
new EncryptInsertValuesTokenGenerator();
encryptInsertValuesTokenGenerator.setEncryptRule(createEncryptRule());
encryptInsertValuesTokenGenerator.setPreviousSQLTokens(Collections.emptyList());
- encryptInsertValuesTokenGenerator.setDatabaseName("db_schema");
+ encryptInsertValuesTokenGenerator.setDatabaseName("encrypt_db");
assertThat(encryptInsertValuesTokenGenerator.generateSQLToken(createInsertStatementContext(Arrays.asList(1,
"Tom", 0, "123456"))).toString(), is("(?, ?, ?, ?, ?, ?)"));
}
@Test
public void assertGenerateSQLTokenFromPreviousSQLTokensWithNullField() {
EncryptInsertValuesTokenGenerator encryptInsertValuesTokenGenerator =
new EncryptInsertValuesTokenGenerator();
- encryptInsertValuesTokenGenerator.setDatabaseName("db-001");
+ encryptInsertValuesTokenGenerator.setDatabaseName("encrypt_db");
encryptInsertValuesTokenGenerator.setEncryptRule(createEncryptRule());
encryptInsertValuesTokenGenerator.setPreviousSQLTokens(getPreviousSQLTokens());
- encryptInsertValuesTokenGenerator.setDatabaseName("db_schema");
+ encryptInsertValuesTokenGenerator.setDatabaseName("encrypt_db");
assertThat(encryptInsertValuesTokenGenerator.generateSQLToken(createInsertStatementContext(Arrays.asList(1,
"Tom", 0, null))).toString(), is("(?, ?, ?, ?, ?, ?)"));
}
@Test
public void assertGenerateSQLTokenFromPreviousSQLTokens() {
EncryptInsertValuesTokenGenerator encryptInsertValuesTokenGenerator =
new EncryptInsertValuesTokenGenerator();
- encryptInsertValuesTokenGenerator.setDatabaseName("db-001");
+ encryptInsertValuesTokenGenerator.setDatabaseName("encrypt_db");
encryptInsertValuesTokenGenerator.setEncryptRule(createEncryptRule());
encryptInsertValuesTokenGenerator.setPreviousSQLTokens(getPreviousSQLTokens());
- encryptInsertValuesTokenGenerator.setDatabaseName("db_schema");
+ encryptInsertValuesTokenGenerator.setDatabaseName("encrypt_db");
assertThat(encryptInsertValuesTokenGenerator.generateSQLToken(createInsertStatementContext(Arrays.asList(1,
"Tom", 0, "123456"))).toString(), is("(?, ?, ?, ?, ?, ?)"));
}
}
diff --git
a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/insert/values/InsertValueContextTest.java
b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/insert/values/InsertValueContextTest.java
index 977d807afd3..38b74c6b7b3 100644
---
a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/insert/values/InsertValueContextTest.java
+++
b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/insert/values/InsertValueContextTest.java
@@ -66,7 +66,7 @@ public final class InsertValueContextTest {
Object valueFromInsertValueContext =
insertValueContext.getLiteralValue(0).get();
assertThat(valueFromInsertValueContext, is(parameterValue));
}
-
+
@Test
public void assertGetLiteralValueWhenParameterisNull() {
Collection<ExpressionSegment> assignments =
makeParameterMarkerExpressionSegment();
diff --git
a/infra/common/src/main/java/org/apache/shardingsphere/infra/hint/SQLHintExtractor.java
b/infra/common/src/main/java/org/apache/shardingsphere/infra/hint/SQLHintExtractor.java
index edab5129f1d..0398c42ee95 100644
---
a/infra/common/src/main/java/org/apache/shardingsphere/infra/hint/SQLHintExtractor.java
+++
b/infra/common/src/main/java/org/apache/shardingsphere/infra/hint/SQLHintExtractor.java
@@ -46,7 +46,8 @@ public final class SQLHintExtractor {
public SQLHintExtractor(final String sqlComment) {
sqlHintProperties = Strings.isNullOrEmpty(sqlComment)
- ? DEFAULT_SQL_HINT_PROPERTIES : new
SQLHintProperties(SQLHintUtils.getSQLHintProps(sqlComment));
+ ? DEFAULT_SQL_HINT_PROPERTIES
+ : new
SQLHintProperties(SQLHintUtils.getSQLHintProps(sqlComment));
}
private SQLHintProperties extract(final AbstractSQLStatement statement) {
diff --git
a/infra/rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/InsertValue.java
b/infra/rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/InsertValue.java
index 976036c3b8e..2ba80ca37c0 100644
---
a/infra/rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/InsertValue.java
+++
b/infra/rewrite/src/main/java/org/apache/shardingsphere/infra/rewrite/sql/token/pojo/generic/InsertValue.java
@@ -55,11 +55,18 @@ public class InsertValue {
}
if (expressionSegment instanceof LiteralExpressionSegment) {
Object literals = ((LiteralExpressionSegment)
expressionSegment).getLiterals();
- return literals instanceof String ? "'" +
((LiteralExpressionSegment) expressionSegment).getLiterals() + "'" :
literals.toString();
+ return getLiteralValue((LiteralExpressionSegment)
expressionSegment, literals);
}
if (expressionSegment instanceof BinaryOperationExpression) {
return ((BinaryOperationExpression) expressionSegment).getText();
}
return ((ComplexExpressionSegment) expressionSegment).getText();
}
+
+ private String getLiteralValue(final LiteralExpressionSegment
expressionSegment, final Object literals) {
+ if (null == literals) {
+ return "NULL";
+ }
+ return literals instanceof String ? "'" +
expressionSegment.getLiterals() + "'" : String.valueOf(literals);
+ }
}
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteNormalEncryptAlgorithmFixture.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteNormalEncryptAlgorithmFixture.java
index cda812f3861..68f18cd601b 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteNormalEncryptAlgorithmFixture.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteNormalEncryptAlgorithmFixture.java
@@ -35,11 +35,17 @@ public final class RewriteNormalEncryptAlgorithmFixture
implements EncryptAlgori
@Override
public String encrypt(final Object plainValue, final EncryptContext
encryptContext) {
+ if (null == plainValue) {
+ return null;
+ }
return "encrypt_" + plainValue;
}
@Override
public Object decrypt(final String cipherValue, final EncryptContext
encryptContext) {
+ if (null == cipherValue) {
+ return null;
+ }
return cipherValue.replaceAll("encrypt_", "");
}
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteQueryAssistedEncryptAlgorithmFixture.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteQueryAssistedEncryptAlgorithmFixture.java
index 543addd6711..80de92867f5 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteQueryAssistedEncryptAlgorithmFixture.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteQueryAssistedEncryptAlgorithmFixture.java
@@ -35,11 +35,17 @@ public final class
RewriteQueryAssistedEncryptAlgorithmFixture implements Encryp
@Override
public String encrypt(final Object plainValue, final EncryptContext
encryptContext) {
+ if (null == plainValue) {
+ return null;
+ }
return "assisted_query_" + plainValue;
}
@Override
public Object decrypt(final String cipherValue, final EncryptContext
encryptContext) {
+ if (null == cipherValue) {
+ return null;
+ }
return cipherValue.replaceAll("encrypt_", "");
}
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteSchemaMetaDataAwareEncryptAlgorithmFixture.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteSchemaMetaDataAwareEncryptAlgorithmFixture.java
index b5d770d7545..c712be5df51 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteSchemaMetaDataAwareEncryptAlgorithmFixture.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/fixture/encrypt/RewriteSchemaMetaDataAwareEncryptAlgorithmFixture.java
@@ -46,11 +46,17 @@ public final class
RewriteSchemaMetaDataAwareEncryptAlgorithmFixture implements
@Override
public String encrypt(final Object plainValue, final EncryptContext
encryptContext) {
+ if (null == plainValue) {
+ return null;
+ }
return "encrypt_" + plainValue + "_" +
schemas.get(databaseName).getTable(encryptContext.getTableName()).getName();
}
@Override
public Object decrypt(final String cipherValue, final EncryptContext
encryptContext) {
+ if (null == cipherValue) {
+ return null;
+ }
ShardingSphereTable table =
schemas.get(databaseName).getTable(encryptContext.getTableName());
return cipherValue.replaceAll("encrypt_", "").replaceAll("_" +
table.getName(), "");
}
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/AbstractSQLRewriterParameterizedTest.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/AbstractSQLRewriterParameterizedTest.java
index 658155cefca..7e55d67840e 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/AbstractSQLRewriterParameterizedTest.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/AbstractSQLRewriterParameterizedTest.java
@@ -97,7 +97,7 @@ public abstract class AbstractSQLRewriterParameterizedTest {
assertThat(each.getSql(),
is(testParameters.getOutputSQLs().get(count)));
assertThat(each.getParameters().size(),
is(testParameters.getOutputGroupedParameters().get(count).size()));
for (int i = 0; i < each.getParameters().size(); i++) {
- assertThat(each.getParameters().get(i).toString(),
is(testParameters.getOutputGroupedParameters().get(count).get(i)));
+ assertThat(String.valueOf(each.getParameters().get(i)),
is(String.valueOf(testParameters.getOutputGroupedParameters().get(count).get(i))));
}
count++;
}
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParameters.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParameters.java
index 245a5760a2d..695530e8bef 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParameters.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParameters.java
@@ -43,7 +43,7 @@ public final class SQLRewriteEngineTestParameters {
private final List<String> outputSQLs;
- private final List<List<String>> outputGroupedParameters;
+ private final List<List<Object>> outputGroupedParameters;
private final String databaseType;
diff --git
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParametersBuilder.java
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParametersBuilder.java
index ae557e76e8d..cb4b40640ec 100644
---
a/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParametersBuilder.java
+++
b/test/rewrite/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/engine/parameter/SQLRewriteEngineTestParametersBuilder.java
@@ -19,9 +19,10 @@ package
org.apache.shardingsphere.sharding.rewrite.parameterized.engine.paramete
import com.google.common.base.Splitter;
import com.google.common.base.Strings;
-import com.google.common.primitives.Ints;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.commons.lang3.math.NumberUtils;
import
org.apache.shardingsphere.sharding.rewrite.parameterized.entity.RewriteAssertionEntity;
import
org.apache.shardingsphere.sharding.rewrite.parameterized.entity.RewriteAssertionsRootEntity;
import
org.apache.shardingsphere.sharding.rewrite.parameterized.entity.RewriteOutputEntity;
@@ -97,7 +98,7 @@ public final class SQLRewriteEngineTestParametersBuilder {
for (RewriteAssertionEntity each : rootAssertions.getAssertions()) {
for (String databaseType :
getDatabaseTypes(each.getDatabaseTypes())) {
result.add(new SQLRewriteEngineTestParameters(type,
each.getId(), fileName, rootAssertions.getYamlRule(), each.getInput().getSql(),
-
createInputParameters(each.getInput().getParameters()),
createOutputSQLs(each.getOutputs()),
createOutputGroupedParameters(each.getOutputs()), databaseType).toArray());
+ createParameters(each.getInput().getParameters()),
createOutputSQLs(each.getOutputs()),
createOutputGroupedParameters(each.getOutputs()), databaseType).toArray());
}
}
return result;
@@ -111,14 +112,18 @@ public final class SQLRewriteEngineTestParametersBuilder {
return Arrays.asList("MySQL", "PostgreSQL", "Oracle", "SQLServer",
"SQL92", "openGauss");
}
- private static List<Object> createInputParameters(final String
inputParameters) {
+ private static List<Object> createParameters(final String inputParameters)
{
if (null == inputParameters) {
return Collections.emptyList();
}
- return
Splitter.on(",").trimResults().splitToList(inputParameters).stream().map(each
-> {
- Object result = Ints.tryParse(each);
- return null == result ? each : result;
- }).collect(Collectors.toList());
+ return
Splitter.on(",").trimResults().splitToList(inputParameters).stream().map(SQLRewriteEngineTestParametersBuilder::createInputParameter).collect(Collectors.toList());
+ }
+
+ private static Object createInputParameter(final String inputParameter) {
+ if (StringUtils.isNumeric(inputParameter)) {
+ return NumberUtils.createNumber(inputParameter);
+ }
+ return "NULL".equals(inputParameter) ? null : inputParameter;
}
private static List<String> createOutputSQLs(final
List<RewriteOutputEntity> outputs) {
@@ -129,10 +134,10 @@ public final class SQLRewriteEngineTestParametersBuilder {
return result;
}
- private static List<List<String>> createOutputGroupedParameters(final
List<RewriteOutputEntity> outputs) {
- List<List<String>> result = new ArrayList<>(outputs.size());
+ private static List<List<Object>> createOutputGroupedParameters(final
List<RewriteOutputEntity> outputs) {
+ List<List<Object>> result = new ArrayList<>(outputs.size());
for (RewriteOutputEntity each : outputs) {
- result.add(null == each.getParameters() ? Collections.emptyList()
: Splitter.on(",").trimResults().splitToList(each.getParameters()));
+ result.add(createParameters(each.getParameters()));
}
return result;
}
diff --git
a/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
b/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
index 1669a6b2e25..3bf1ccbaa0d 100644
---
a/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
+++
b/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
@@ -131,4 +131,9 @@
<input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (?,
?, ?);" parameters="1, 1, TEST" />
<output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT,
PLAIN_CONTENT) VALUES (?, ?, ?, ?);" parameters="1, 1, encrypt_TEST_t_order,
TEST" />
</rewrite-assertion>
+
+ <rewrite-assertion
id="insert_values_with_null_encrypt_column_for_parameters"
db-types="MySQL,PostgreSQL,openGauss">
+ <input sql="INSERT INTO t_account(account_id, certificate_number,
password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', NULL, 2000,
'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', NULL, 'OK')" parameters="1, NULL,
aaa, 1000, OK, 3, 333X, NULL, 3000, OK" />
+ <output sql="INSERT INTO t_account(account_id,
cipher_certificate_number, assisted_query_certificate_number, cipher_password,
assisted_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?),
(2, 'encrypt_222X', 'assisted_query_222X', NULL, NULL, 'encrypt_2000', 'OK'),
(?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X',
'encrypt_ddd', 'assisted_query_ddd', NULL, 'OK')" parameters="1, NULL, NULL,
encrypt_aaa, assisted_query_aaa, encrypt_1000, OK, 3, encry [...]
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
b/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
index 0ae07f4d841..0787e0ed036 100644
---
a/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
+++
b/test/rewrite/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
@@ -126,4 +126,9 @@
<input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (?,
?, ?);" parameters="1, 1, TEST" />
<output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT,
PLAIN_CONTENT) VALUES (?, ?, ?, ?);" parameters="1, 1, encrypt_TEST_t_order,
TEST" />
</rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_with_null_encrypt_column_for_plain"
db-types="MySQL,PostgreSQL,openGauss">
+ <input sql="INSERT INTO t_account(account_id, certificate_number,
password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', NULL, 2000,
'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', NULL, 'OK')" parameters="1, NULL,
aaa, 1000, OK, 3, 333X, NULL, 3000, OK" />
+ <output sql="INSERT INTO t_account(account_id,
cipher_certificate_number, assisted_query_certificate_number, cipher_password,
assisted_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?),
(2, 'encrypt_222X', 'assisted_query_222X', NULL, NULL, 'encrypt_2000', 'OK'),
(?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X',
'encrypt_ddd', 'assisted_query_ddd', NULL, 'OK')" parameters="1, NULL, NULL,
encrypt_aaa, assisted_query_aaa, encrypt_1000, OK, 3, encry [...]
+ </rewrite-assertion>
</rewrite-assertions>
