SQL Rewrite Test

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:

  1. dataSources:
  2.   db: !!com.zaxxer.hikari.HikariDataSource
  3.     driverClassName: org.h2.Driver
  4.     jdbcUrl: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
  5.     username: sa
  6.     password:
  8. ## sharding Rules
  9. rules:
  10. - !SHARDING
  11.   tables:
  12.     t_account:
  13.       actualDataNodes: db.t_account_${0..1}
  14.       tableStrategy: 
  15.         standard:
  16.           shardingColumn: account_id
  17.           shardingAlgorithmName: account_table_inline
  18.       keyGenerateStrategy:
  19.         column: account_id
  20.         keyGeneratorName: snowflake
  21.     t_account_detail:
  22.       actualDataNodes: db.t_account_detail_${0..1}
  23.       tableStrategy: 
  24.         standard:
  25.           shardingColumn: order_id
  26.           shardingAlgorithmName: account_detail_table_inline
  27.   bindingTables:
  28.     - t_account, t_account_detail
  29.   shardingAlgorithms:
  30.     account_table_inline:
  31.       type: INLINE
  32.       props:
  33.         algorithm-expression: t_account_${account_id % 2}
  34.     account_detail_table_inline:
  35.       type: INLINE
  36.       props:
  37.         algorithm-expression: t_account_detail_${account_id % 2}
  38.   keyGenerators:
  39.     snowflake:
  40.       type: SNOWFLAKE
  41.       props:
  42.         worker-id: 123

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:

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

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