ShardingSphere 4.x Test engine-SQL case/SQL parse test engine/SQL rewrite test engine

4 min readSep 3, 2020

ShardingSphere provided a full functionality test engine. it defines SQLs in xml files, every single SQL is drove by SQL parse unit test engine and integration test engine, each engine is suit for H2、MySQL、PostgreSQL、SQLServer and Oracle.

To make the test engine more easy to get start, all test engines in shardingsphere are designed to modify the configuration files to execute all assertions without any Java code modification.

The SQL parsing unit test covers both SQL placeholder and literal dimension. Integration test can be further divided into two dimensions of strategy and JDBC; the former one includes strategies as Sharding, table Sharding, database Sharding, and read-write split while the latter one includes Statement and PreparedStatement.

Therefore, one SQL can drive 5 kinds of database parsing * 2 kinds of parameter transmission modes + 5 kinds of databases * 5 kinds of Sharding strategies * 2 kinds of JDBC operation modes = 60 test cases, to enable ShardingSphere to achieve the pursuit of high quality.

whenever describe the sub-path under a specific path, there may be more than one, here represent SQL-TYPE and SHARDING-TYPE as following :
SQL-TYPE : is one of or collection of dal,dcl，ddl，dml，dql，tcl
SHARDING-TYPE : is one of or collection of db，dbtbl_with_masterslave，masterslave，tbl

SQL Case

Target

The code for SQL case is in module sharding-sql-test.There are two targets for this module:

Test the wildcard replacement by unit test.
Share the SQL resource in resources folder.

<sql-cases>
    <sql-case id="select_constant_without_table" value="SELECT 1 as a" />
    <sql-case id="select_with_same_table_name_and_alias" value="SELECT t_order.* FROM t_order t_order WHERE user_id = ? AND order_id = ?" />
    <sql-case id="select_with_same_table_name_and_alias_column_with_owner" value="SELECT t_order.order_id,t_order.user_id,status FROM t_order t_order WHERE t_order.user_id = ? AND order_id = ?" db-types="MySQL,H2"/>
</sql-cases>

Developer setup the SQL for assertion and database type during on the configuration file. And these SQLs could share in different test engine, that’s why we extract the sharding-sql-test as a stand alone module.

Process

Following is the data handling process in SQL case :

SQL Parse Test Engine

Prepare Data

Not like Integration test, SQL parse test doesn’t need a specific database environment, just define the sql to parse, and the assert data:

SQL Data

As mentioned sql-case-id in Integration test，test-case-id could be shared in different module to test, and the file is at /sharding-sql-test/src/main/resources/sql/sharding/SQL-TYPE/*.xml

Parser Assert Data

The assert data is at /sharding-core/sharding-core-parse/sharding-core-parse-test/src/test/resources/sharding/SQL-TYPE/*.xml in that xml file, it could assert against the table name, token or sql condition and so on. For example:

<parser-result-sets>
<parser-result sql-case-id="insert_with_multiple_values">
        <tables>
            <table name="t_order" />
        </tables>
        <tokens>
            <table-token start-index="12" table-name="t_order" length="7" />
        </tokens>
        <sharding-conditions>
            <and-condition>
                <condition column-name="order_id" table-name="t_order" operator="EQUAL">
                    <value literal="1" type="int" />
                </condition>
                <condition column-name="user_id" table-name="t_order" operator="EQUAL">
                    <value literal="1" type="int" />
                </condition>
            </and-condition>
            <and-condition>
                <condition column-name="order_id" table-name="t_order" operator="EQUAL">
                    <value literal="2" type="int" />
                </condition>
                <condition column-name="user_id" table-name="t_order" operator="EQUAL">
                    <value literal="2" type="int" />
                </condition>
            </and-condition>
        </sharding-conditions>
    </parser-result>
</parser-result-sets>

When these configs are ready, launch the test engine in sharding-core-parse-test to test SQL parse.

SQL Rewrite Test Engine Target

Facing logic databases and tables cannot be executed directly in actual databases. SQL rewrite is used to rewrite logic SQL into rightly executable ones in actual databases, including two parts, correctness rewrite and optimization rewrite. rewrite tests are for these targets.

Test

The rewrite tests are in the test folder under sharding-core/sharding-core-rewrite . Followings are the main part for rewrite tests:

test engine
environment configuration
assert data

Test engine is the entrance of rewrite tests, just like other test engines, through Junit Parameterized, read every and each data in the xml file under the target test type in test\resources, and then assert by the engine one by one

Environment configuration is the yaml file under test type under test\resources\yaml. The configuration file contains dataSources，shardingRule，encryptRule and other info. for example:

dataSources:
  db: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: org.h2.Driver
    jdbcUrl: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
    username: sa
    password:## sharding Rules
shardingRule:
  tables:
    t_account:
      actualDataNodes: db.t_account_${0..1}
      tableStrategy: 
        inline:
          shardingColumn: account_id
          algorithmExpression: t_account_${account_id % 2}
      keyGenerator:
        type: TEST
        column: account_id
    t_account_detail:
      actualDataNodes: db.t_account_detail_${0..1}
      tableStrategy: 
        inline:
          shardingColumn: order_id
          algorithmExpression: t_account_detail_${account_id % 2}
  bindingTables:
    - t_account, t_account_detail

Assert data are in the xml under test type in test\resources. In the xml file, yaml-rule means the environment configuration file path, input contains the target SQL and parameters, output contains the expected SQL and parameters. The db-type described the type for SQL parse, default is SQL92. For example:

<rewrite-assertions yaml-rule="yaml/sharding/sharding-rule.yaml">
    <!-- to change SQL parse type, change db-type --> 
    <rewrite-assertion id="create_index_for_mysql" db-type="MySQL">
        <input sql="CREATE INDEX index_name ON t_account ('status')" />
        <output sql="CREATE INDEX index_name ON t_account_0 ('status')" />
        <output sql="CREATE INDEX index_name ON t_account_1 ('status')" />
    </rewrite-assertion>
</rewrite-assertions>

After set up the assert data and environment configuration, rewrite test engine will assert the corresponding SQL without any Java code modification.