Support for executing DistSQL for ShardingSphere JDBC logical databases

[linghengqian]

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 Improve GraalVM Reachability Metadata and corresponding nativeTest related unit tests #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,

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.

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.execute("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.

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. There is absolutely no reason why we must create a YAML file when using the ShardingSphere JDBC driver.

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.execute("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.

• To make a long story short, the following issues need to be addressed.

  • The distsql executor for RAL and RUL statements, which are independent of the ShardingSphere proxy, will be migrated into a kernel submodule.
  • Modify DriverDatabaseConnectionManager and ShardingSphereStatement to support executing distsql on the JDBC Driver.
  • Add E2E for DistSQL and ShardingSphere JDBC.
  • Refactor the shardingsphere-test-native module to avoid most of the YAML file.
  • Add an infra URL impl like jdbc:shardingsphere:scratch:sharding_db to avoid creating the YAML file.
  • Refactor the shardingsphere-test-native module to avoid all YAML file.
  • Add document for DistSQL and ShardingSphere JDBC.

[terrymanu]

That's a good suggestion, but the workload for implementation seems too heavy. I recommend putting it on hold for now.

[linghengqian]

That's a good suggestion, but the workload for implementation seems too heavy. I recommend putting it on hold for now.

I'm not entirely sure if there's a second impl. I've already proposed my impl at #37778 .

However, I still need to wait for #37775 to fix CI.