linghengqian opened a new issue, #37761: URL: https://github.com/apache/shardingsphere/issues/37761
## Feature Request **For English only**, other languages will not be accepted. Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot make decision by current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Is your feature request related to a problem? - This is related to the `org.apache.shardingsphere:shardingsphere-test-native` module introduced in https://github.com/apache/shardingsphere/issues/29052 . - Currently, the `org.apache.shardingsphere:shardingsphere-test-native` module contains a large number of YAML files, initially intended to avoid writing Java configuration. Over time, the YAML files and related documentation have become so numerous that making changes has become quite cumbersome. The key point is that the Proxy's native test has been using DistSQL, while the JDBC native test has been using YAML. From my perspective, using so much YAML here makes no sense. The operations mentioned in https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/known-implementation/ are not dynamic enough. ### Describe the feature you would like. - Support for executing DistSQL for JDBC logical databases should be added. TLDR, for a file like `test/native/src/test/resources/test-native/yaml/jdbc/features/sharding.yaml`, ```yaml dataSources: ds_0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: org.h2.Driver jdbcUrl: jdbc:h2:mem:local_sharding_ds_0;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE username: sa password: ds_1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: org.h2.Driver jdbcUrl: jdbc:h2:mem:local_sharding_ds_1;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE username: sa password: ds_2: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: org.h2.Driver jdbcUrl: jdbc:h2:mem:local_sharding_ds_2;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE username: sa password: rules: - !SHARDING tables: t_order: actualDataNodes: <LITERAL>ds_0.t_order, ds_1.t_order, ds_2.t_order keyGenerateStrategy: column: order_id keyGeneratorName: snowflake t_order_item: actualDataNodes: <LITERAL>ds_0.t_order_item, ds_1.t_order_item, ds_2.t_order_item keyGenerateStrategy: column: order_item_id keyGeneratorName: snowflake defaultDatabaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: inline shardingAlgorithms: inline: type: CLASS_BASED props: strategy: STANDARD algorithmClassName: org.apache.shardingsphere.test.natived.commons.algorithm.ClassBasedInlineShardingAlgorithmFixture keyGenerators: snowflake: type: SNOWFLAKE auditors: sharding_key_required_auditor: type: DML_SHARDING_CONDITIONS - !BROADCAST tables: - t_address props: sql-show: false ``` - Currently, using this YAML file also requires a jdbcUrl of `jdbc:shardingsphere:classpath:test-native/yaml/jdbc/features/sharding.yaml`. This is too much trouble. - We should be able to execute distsql from this datasource, like this. ```java import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class ExampleUtils { void test() throws SQLException { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:shardingsphere:classpath:test-native/yaml/jdbc/features/sharding.yaml"); config.setDriverClassName("org.apache.shardingsphere.driver.ShardingSphereDriver"); try (HikariDataSource dataSource = new HikariDataSource(config); Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { statement.execute("REGISTER STORAGE UNIT ds_0 (URL='jdbc:h2:mem:local_sharding_ds_0;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD=''), ds_1 (URL='jdbc:h2:mem:local_sharding_ds_1;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD=''), ds_2 (URL='jdbc:h2:mem:local_sharding_ds_2;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD='')"); statement.executeQuery("CREATE DEFAULT SHARDING DATABASE STRATEGY (TYPE='standard', SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=CLASS_BASED, PROPERTIES('strategy'='STANDARD','algorithmClassName'='org.apache.shardingsphere.test.natived.commons.algorithm.ClassBasedInlineShardingAlgorithmFixture'))))"); statement.execute("CREATE SHARDING TABLE RULE t_order (DATANODES('<LITERAL>ds_0.t_order, ds_1.t_order, ds_2.t_order'), KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME='SNOWFLAKE'))), t_order_item (DATANODES('<LITERAL>ds_0.t_order_item, ds_1.t_order_item, ds_2.t_order_item'), KEY_GENERATE_STRATEGY(COLUMN=order_item_id,TYPE(NAME='SNOWFLAKE')))"); statement.execute("CREATE BROADCAST TABLE RULE t_address"); } } } ``` - In this case, `test/native/src/test/resources/test-native/yaml/jdbc/features/sharding.yaml` only needs to define the following content. ```yaml props: sql-show: false ``` - This is the first step in completely eliminating the large number of YAML files in `org.apache.shardingsphere:shardingsphere-test-native`. Once this is complete, we can introduce a new Infra URL implementation for the JDBC Driver. ```java import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class ExampleUtils { void test() throws SQLException { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:shardingsphere:scratch:sharding_db"); config.setDriverClassName("org.apache.shardingsphere.driver.ShardingSphereDriver"); try (HikariDataSource dataSource = new HikariDataSource(config); Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { statement.execute("REGISTER STORAGE UNIT ds_0 (URL='jdbc:h2:mem:local_sharding_ds_0;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD=''), ds_1 (URL='jdbc:h2:mem:local_sharding_ds_1;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD=''), ds_2 (URL='jdbc:h2:mem:local_sharding_ds_2;MODE=MYSQL;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE',USER='sa',PASSWORD='')"); statement.executeQuery("CREATE DEFAULT SHARDING DATABASE STRATEGY (TYPE='standard', SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=CLASS_BASED, PROPERTIES('strategy'='STANDARD','algorithmClassName'='org.apache.shardingsphere.test.natived.commons.algorithm.ClassBasedInlineShardingAlgorithmFixture'))))"); statement.execute("CREATE SHARDING TABLE RULE t_order (DATANODES('<LITERAL>ds_0.t_order, ds_1.t_order, ds_2.t_order'), KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME='SNOWFLAKE'))), t_order_item (DATANODES('<LITERAL>ds_0.t_order_item, ds_1.t_order_item, ds_2.t_order_item'), KEY_GENERATE_STRATEGY(COLUMN=order_item_id,TYPE(NAME='SNOWFLAKE')))"); statement.execute("CREATE BROADCAST TABLE RULE t_address"); } } } ``` - A potential issue is that DistSQL does not yet support setting `props` attributes within YAML. Furthermore, because the current master branch's jdbcUrl defaults to the logical database name, statements like `CREATE DATABASE sharding_db` and `USE sharding_db` cannot be used. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
