You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2024/03/20 03:26:24 UTC
(shardingsphere) branch master updated: Support SQLServer dml encrypt with schema (#30534)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 d952966c347 Support SQLServer dml encrypt with schema (#30534)
d952966c347 is described below
commit d952966c3473642b344af019f5385202e7c2d638
Author: LotusMoon <ch...@aliyun.com>
AuthorDate: Wed Mar 20 11:26:17 2024 +0800
Support SQLServer dml encrypt with schema (#30534)
* Support SQLServer dml encrypt with schema
* fix Test fail in SQLServer
* apply spotless check
* fix SQLServer rewrite error
* adjust createSQLServerDatabase data type SQLServer
---
.../merge/dql/ShardingDQLResultMergerTest.java | 20 +++++++++++++++-----
.../ShardingCreateTableStatementValidatorTest.java | 2 +-
.../statement/SQLStatementContextFactoryTest.java | 2 ++
.../metadata/database/SQLServerDatabaseMetaData.java | 7 +++++++
.../query-with-cipher/dml/select/select-where.xml | 5 +++++
.../resources/scenario/sharding/case/dml/delete.xml | 4 ++--
.../resources/scenario/sharding/case/dml/select.xml | 6 +++---
7 files changed, 35 insertions(+), 11 deletions(-)
diff --git a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
index 5d7d81fa2e6..f8a5282b845 100644
--- a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
+++ b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
@@ -158,7 +158,7 @@ class ShardingDQLResultMergerTest {
selectStatement.setProjections(new ProjectionsSegment(0, 0));
selectStatement.setLimit(new LimitSegment(0, 0, new NumberLiteralLimitValueSegment(0, 0, 1), null));
SelectStatementContext selectStatementContext = new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(), selectStatement, DefaultDatabase.LOGIC_NAME);
- MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createDatabase(), mock(ConnectionContext.class));
+ MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createSQLServerDatabase(), mock(ConnectionContext.class));
assertThat(actual, instanceOf(TopAndRowNumberDecoratorMergedResult.class));
assertThat(((TopAndRowNumberDecoratorMergedResult) actual).getMergedResult(), instanceOf(IteratorStreamMergedResult.class));
}
@@ -235,7 +235,7 @@ class ShardingDQLResultMergerTest {
selectStatement.setLimit(new LimitSegment(0, 0, new NumberLiteralRowNumberValueSegment(0, 0, 1, true), null));
SelectStatementContext selectStatementContext = new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(),
selectStatement, DefaultDatabase.LOGIC_NAME);
- MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createDatabase(), mock(ConnectionContext.class));
+ MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createSQLServerDatabase(), mock(ConnectionContext.class));
assertThat(actual, instanceOf(TopAndRowNumberDecoratorMergedResult.class));
assertThat(((TopAndRowNumberDecoratorMergedResult) actual).getMergedResult(), instanceOf(OrderByStreamMergedResult.class));
}
@@ -316,7 +316,7 @@ class ShardingDQLResultMergerTest {
selectStatement.setLimit(new LimitSegment(0, 0, new NumberLiteralRowNumberValueSegment(0, 0, 1, true), null));
SelectStatementContext selectStatementContext = new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(),
selectStatement, DefaultDatabase.LOGIC_NAME);
- MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createDatabase(), mock(ConnectionContext.class));
+ MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createSQLServerDatabase(), mock(ConnectionContext.class));
assertThat(actual, instanceOf(TopAndRowNumberDecoratorMergedResult.class));
assertThat(((TopAndRowNumberDecoratorMergedResult) actual).getMergedResult(), instanceOf(GroupByStreamMergedResult.class));
}
@@ -395,7 +395,7 @@ class ShardingDQLResultMergerTest {
selectStatement.setLimit(new LimitSegment(0, 0, new NumberLiteralRowNumberValueSegment(0, 0, 1, true), null));
SelectStatementContext selectStatementContext = new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(),
selectStatement, DefaultDatabase.LOGIC_NAME);
- MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createDatabase(), mock(ConnectionContext.class));
+ MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createSQLServerDatabase(), mock(ConnectionContext.class));
assertThat(actual, instanceOf(TopAndRowNumberDecoratorMergedResult.class));
assertThat(((TopAndRowNumberDecoratorMergedResult) actual).getMergedResult(), instanceOf(GroupByMemoryMergedResult.class));
}
@@ -477,7 +477,7 @@ class ShardingDQLResultMergerTest {
selectStatement.setLimit(new LimitSegment(0, 0, new NumberLiteralRowNumberValueSegment(0, 0, 1, true), null));
SelectStatementContext selectStatementContext = new SelectStatementContext(createShardingSphereMetaData(database), Collections.emptyList(),
selectStatement, DefaultDatabase.LOGIC_NAME);
- MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createDatabase(), mock(ConnectionContext.class));
+ MergedResult actual = resultMerger.merge(createQueryResults(), selectStatementContext, createSQLServerDatabase(), mock(ConnectionContext.class));
assertThat(actual, instanceOf(TopAndRowNumberDecoratorMergedResult.class));
assertThat(((TopAndRowNumberDecoratorMergedResult) actual).getMergedResult(), instanceOf(GroupByMemoryMergedResult.class));
}
@@ -515,6 +515,16 @@ class ShardingDQLResultMergerTest {
mock(RuleMetaData.class), Collections.singletonMap(DefaultDatabase.LOGIC_NAME, schema));
}
+ private ShardingSphereDatabase createSQLServerDatabase() {
+ ShardingSphereColumn column1 = new ShardingSphereColumn("col1", 0, false, false, false, true, false, false);
+ ShardingSphereColumn column2 = new ShardingSphereColumn("col2", 0, false, false, false, true, false, false);
+ ShardingSphereColumn column3 = new ShardingSphereColumn("col3", 0, false, false, false, true, false, false);
+ ShardingSphereTable table = new ShardingSphereTable("tbl", Arrays.asList(column1, column2, column3), Collections.emptyList(), Collections.emptyList());
+ ShardingSphereSchema schema = new ShardingSphereSchema(Collections.singletonMap("tbl", table), Collections.emptyMap());
+ return new ShardingSphereDatabase(DefaultDatabase.LOGIC_NAME, TypedSPILoader.getService(DatabaseType.class, "SQLServer"), mock(ResourceMetaData.class),
+ mock(RuleMetaData.class), Collections.singletonMap("dbo", schema));
+ }
+
private SelectStatement buildSelectStatement(final SelectStatement result) {
result.setFrom(new SimpleTableSegment(new TableNameSegment(10, 13, new IdentifierValue("tbl"))));
result.setProjections(new ProjectionsSegment(0, 0));
diff --git a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/ddl/ShardingCreateTableStatementValidatorTest.java b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/ddl/ShardingCreateTableStatementValidatorTest.java
index b872abe6bb9..10d1fbd16e3 100644
--- a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/ddl/ShardingCreateTableStatementValidatorTest.java
+++ b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/route/engine/validator/ddl/ShardingCreateTableStatementValidatorTest.java
@@ -100,7 +100,7 @@ class ShardingCreateTableStatementValidatorTest {
void assertPreValidateCreateTableForSQLServer() {
SQLServerCreateTableStatement sqlStatement = new SQLServerCreateTableStatement();
sqlStatement.setTable(new SimpleTableSegment(new TableNameSegment(1, 2, new IdentifierValue("t_order"))));
- assertThrows(TableExistsException.class, () -> assertPreValidateCreateTable(sqlStatement, "sharding_db"));
+ assertThrows(TableExistsException.class, () -> assertPreValidateCreateTable(sqlStatement, "dbo"));
}
private void assertPreValidateCreateTable(final CreateTableStatement sqlStatement, final String schemaName) {
diff --git a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactoryTest.java b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactoryTest.java
index 98ed3ee0a0e..2d8cc5563b9 100644
--- a/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactoryTest.java
+++ b/infra/binder/src/test/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactoryTest.java
@@ -154,6 +154,8 @@ class SQLStatementContextFactoryTest {
when(database.containsSchema("public")).thenReturn(true);
when(database.getSchema(DefaultDatabase.LOGIC_NAME).containsTable("tbl")).thenReturn(true);
when(database.getSchema("public").containsTable("tbl")).thenReturn(true);
+ when(database.containsSchema("dbo")).thenReturn(true);
+ when(database.getSchema("dbo").containsTable("tbl")).thenReturn(true);
Map<String, ShardingSphereDatabase> databases = Collections.singletonMap(DefaultDatabase.LOGIC_NAME, database);
return new ShardingSphereMetaData(databases, mock(ResourceMetaData.class), mock(RuleMetaData.class), mock(ConfigurationProperties.class));
}
diff --git a/infra/database/type/sqlserver/src/main/java/org/apache/shardingsphere/infra/database/sqlserver/metadata/database/SQLServerDatabaseMetaData.java b/infra/database/type/sqlserver/src/main/java/org/apache/shardingsphere/infra/database/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
index 436257f8e09..69bed198459 100644
--- a/infra/database/type/sqlserver/src/main/java/org/apache/shardingsphere/infra/database/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
+++ b/infra/database/type/sqlserver/src/main/java/org/apache/shardingsphere/infra/database/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
@@ -21,6 +21,8 @@ import org.apache.shardingsphere.infra.database.core.metadata.database.DialectDa
import org.apache.shardingsphere.infra.database.core.metadata.database.enums.NullsOrderType;
import org.apache.shardingsphere.infra.database.core.metadata.database.enums.QuoteCharacter;
+import java.util.Optional;
+
/**
* Database meta data of SQLServer.
*/
@@ -40,4 +42,9 @@ public final class SQLServerDatabaseMetaData implements DialectDatabaseMetaData
public String getDatabaseType() {
return "SQLServer";
}
+
+ @Override
+ public Optional<String> getDefaultSchema() {
+ return Optional.of("dbo");
+ }
}
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
index b7f3a9add40..7e244df8ec7 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
@@ -98,4 +98,9 @@
<input sql="SELECT * FROM t_user where user_name like '%an%'"/>
<output sql="SELECT t_user.[user_id], t_user.[user_name_cipher] AS [user_name], t_user.[password_cipher] AS [password], t_user.[email_cipher] AS [email], t_user.[user_telephone_cipher] AS [telephone], t_user.[creation_date] FROM t_user where user_name_like like '%`m%'"/>
</rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_schema" db-types="SQLServer">
+ <input sql="SELECT * FROM dbo.t_user"/>
+ <output sql="SELECT t_user.[user_id], t_user.[user_name_cipher] AS [user_name], t_user.[password_cipher] AS [password], t_user.[email_cipher] AS [email], t_user.[user_telephone_cipher] AS [telephone], t_user.[creation_date] FROM dbo.t_user"/>
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/delete.xml b/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/delete.xml
index 922bfa12ceb..b1f89802f40 100644
--- a/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/delete.xml
+++ b/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/delete.xml
@@ -27,13 +27,13 @@
<output sql="DELETE FROM t_account_0 WHERE account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion id="delete_without_sharding_value_for_parameters" db-types="MySQL,Oracle,SQLServer,SQL92">
+ <rewrite-assertion id="delete_without_sharding_value_for_parameters" db-types="MySQL,Oracle,SQL92">
<input sql="DELETE FROM logic_db.t_account WHERE status = ?" parameters="OK" />
<output sql="DELETE FROM t_account_0 WHERE status = ?" parameters="OK" />
<output sql="DELETE FROM t_account_1 WHERE status = ?" parameters="OK" />
</rewrite-assertion>
- <rewrite-assertion id="delete_without_sharding_value_for_literals" db-types="MySQL,Oracle,SQLServer,SQL92">
+ <rewrite-assertion id="delete_without_sharding_value_for_literals" db-types="MySQL,Oracle,SQL92">
<input sql="DELETE FROM logic_db.t_account WHERE status = 'OK'" />
<output sql="DELETE FROM t_account_0 WHERE status = 'OK'" />
<output sql="DELETE FROM t_account_1 WHERE status = 'OK'" />
diff --git a/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml b/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml
index a2a47e796b9..d72d7f1503e 100644
--- a/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml
+++ b/test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml
@@ -58,7 +58,7 @@
<output sql="SELECT DISTINCT amount AS AGGREGATION_DISTINCT_DERIVED_0, account_id FROM t_account_1 ORDER BY account_id ASC " />
</rewrite-assertion>
- <rewrite-assertion id="select_with_schema" db-types="MySQL,Oracle,SQLServer,SQL92">
+ <rewrite-assertion id="select_with_schema" db-types="MySQL,Oracle,SQL92">
<input sql="SELECT * FROM logic_db.t_account" />
<output sql="SELECT * FROM t_account_0 UNION ALL SELECT * FROM t_account_1" />
</rewrite-assertion>
@@ -78,12 +78,12 @@
<output sql="SELECT * FROM t_account_0 WHERE account_id = ? AND amount BETWEEN (SELECT amount FROM t_account_0 WHERE account_id = ?) AND ?" parameters="100, 100, 1500" />
</rewrite-assertion>
- <rewrite-assertion id="select_without_sharding_value_for_parameters" db-types="MySQL,Oracle,SQLServer,SQL92">
+ <rewrite-assertion id="select_without_sharding_value_for_parameters" db-types="MySQL,Oracle,SQL92">
<input sql="SELECT * FROM logic_db.t_account WHERE amount = ?" parameters="1000" />
<output sql="SELECT * FROM t_account_0 WHERE amount = ? UNION ALL SELECT * FROM t_account_1 WHERE amount = ?" parameters="1000, 1000" />
</rewrite-assertion>
- <rewrite-assertion id="select_without_sharding_value_for_literals" db-types="MySQL,Oracle,SQLServer,SQL92">
+ <rewrite-assertion id="select_without_sharding_value_for_literals" db-types="MySQL,Oracle,SQL92">
<input sql="SELECT * FROM logic_db.t_account WHERE amount = 1000" />
<output sql="SELECT * FROM t_account_0 WHERE amount = 1000 UNION ALL SELECT * FROM t_account_1 WHERE amount = 1000" />
</rewrite-assertion>