ShardingSphere-JDBC sharding rewrite engine

Preface

The above ShardingSphere-JDBC fragment routing engine introduces the routing engine in the fragmentation process, and finally obtains the routing result; the rewrite engine to be introduced in this article needs to use the routing result to rewrite the SQL, and the rewrite can be correct. SQL that can be executed by sub-database and sub-table; there are many situations involving rewriting various SQL, and this article will analyze one by one.

Rewrite decorator

The rewrite engine also uses the decorator pattern and provides the interface class SQLRewriteContextDecorator . The implementation classes include:

• ShardingSQLRewriteContextDecorator: Sharding SQL rewrite decorator;
• ShadowSQLRewriteContextDecorator: Shadow library SQL rewrite decorator;
• EncryptSQLRewriteContextDecorator: Data encryption SQL rewrite decorator;

Load ShardingSQLRewriteContextDecorator and EncryptSQLRewriteContextDecorator default, use java.util.ServiceLoader to load the rewrite decorator, you need to specify the specific implementation class META-INF/services/

org.apache.shardingsphere.sharding.rewrite.context.ShardingSQLRewriteContextDecorator
org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator

Decorators can be superimposed, so the priority function OrderAware , and each decorator has corresponding rules, roughly as follows:

Decorator-SQLRewriteContextDecorator	Rule-BaseRule	Priority-Order
ShardingSQLRewriteContextDecorator	ShardingRule	0
EncryptSQLRewriteContextDecorator	EncryptRule	20
ShadowSQLRewriteContextDecorator	ShadowRule	30

Only when the relevant BaseRule configured, the corresponding SQLRewriteContextDecorator can take effect. The most common one is ShardingSQLRewriteContextDecorator . The following is a focus on this decorator;

Rewrite engine

Different SQL statements require different rewrites. The overall structure of the rewrite engine is divided into the following figure (from the official website):

Some preparatory work needs to be done before executing the rewrite engine. The entire rewrite process is roughly divided into the following steps:

• SQLTokenGenerator lists according to different rewrite decorators;
• The SQLTokenGenerator generate corresponding SQLToken ;
• The rewrite engine executes the rewrite operation SQLToken

Construct SQLTokenGenerator

Different decorators need to construct different SQLTokenGenerator lists. Taking the most common ShardingSQLRewriteContextDecorator as an example, the following 13 types of SQLTokenGenerator will be prepared:

    private Collection<SQLTokenGenerator> buildSQLTokenGenerators() {
        Collection<SQLTokenGenerator> result = new LinkedList<>();
        addSQLTokenGenerator(result, new TableTokenGenerator());
        addSQLTokenGenerator(result, new DistinctProjectionPrefixTokenGenerator());
        addSQLTokenGenerator(result, new ProjectionsTokenGenerator());
        addSQLTokenGenerator(result, new OrderByTokenGenerator());
        addSQLTokenGenerator(result, new AggregationDistinctTokenGenerator());
        addSQLTokenGenerator(result, new IndexTokenGenerator());
        addSQLTokenGenerator(result, new OffsetTokenGenerator());
        addSQLTokenGenerator(result, new RowCountTokenGenerator());
        addSQLTokenGenerator(result, new GeneratedKeyInsertColumnTokenGenerator());
        addSQLTokenGenerator(result, new GeneratedKeyForUseDefaultInsertColumnsTokenGenerator());
        addSQLTokenGenerator(result, new GeneratedKeyAssignmentTokenGenerator());
        addSQLTokenGenerator(result, new ShardingInsertValuesTokenGenerator());
        addSQLTokenGenerator(result, new GeneratedKeyInsertValuesTokenGenerator());
        return result;
    }

The public interface class of the above implementation class is SQLTokenGenerator , which provides a public method whether it takes effect:

public interface SQLTokenGenerator {
    boolean isGenerateSQLToken(SQLStatementContext sqlStatementContext);
}

Various SQLTokenGenerator are not valid every time, and need to be judged according to different SQL statements. The SQL statement has been parsed as SQLStatementContext in the parsing engine, so that it can be judged SQLStatementContext

TableTokenGenerator

TableToken generator, mainly used to rewrite the table name;

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return true;
    }

It can be found that there is SQLToken , and it returns true directly; but when generating TableToken , it will check whether the table information exists and whether the related table TableRule is configured;

DistinctProjectionPrefixTokenGenerator

DistinctProjectionPrefixToken generator mainly deals with aggregate functions and de-duplication:

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext && !((SelectStatementContext) sqlStatementContext).getProjectionsContext().getAggregationDistinctProjections().isEmpty();
    }

The first must be the select statement, which also contains: aggregate function and Distinct de-duplication, such as the following SQL:

select sum(distinct user_id) from t_order where order_id = 101

The SQL after rewriting is as follows:

Actual SQL: ds0 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 from t_order1 where order_id = 101
Actual SQL: ds1 ::: select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 from t_order1 where order_id = 101

ProjectionsTokenGenerator

ProjectionsToken generator, aggregate functions need to be derived, such as AVG function

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext && !getDerivedProjectionTexts((SelectStatementContext) sqlStatementContext).isEmpty();
    }
    
    private Collection<String> getDerivedProjectionTexts(final SelectStatementContext selectStatementContext) {
        Collection<String> result = new LinkedList<>();
        for (Projection each : selectStatementContext.getProjectionsContext().getProjections()) {
            if (each instanceof AggregationProjection && !((AggregationProjection) each).getDerivedAggregationProjections().isEmpty()) {
                result.addAll(((AggregationProjection) each).getDerivedAggregationProjections().stream().map(this::getDerivedProjectionText).collect(Collectors.toList()));
            } else if (each instanceof DerivedProjection) {
                result.add(getDerivedProjectionText(each));
            }
        }
        return result;
    }

The first must be the select statement, and the second can be:

• Aggregate functions, and need to derive new functions, such as avg function;
• Derived keywords, such as order by, group by, etc.;

For example, the avg function uses:

select avg(user_id) from t_order where order_id = 101

The rewritten SQL is as follows:

Actual SQL: ds0 ::: select avg(user_id) , COUNT(user_id) AS AVG_DERIVED_COUNT_0 , SUM(user_id) AS AVG_DERIVED_SUM_0 from t_order1 where order_id = 101
Actual SQL: ds1 ::: select avg(user_id) , COUNT(user_id) AS AVG_DERIVED_COUNT_0 , SUM(user_id) AS AVG_DERIVED_SUM_0 from t_order1 where order_id = 101

For example, order by uses:

select user_id from t_order order by order_id

The rewritten SQL is as follows:

Actual SQL: ds0 ::: select user_id , order_id AS ORDER_BY_DERIVED_0 from t_order0 order by order_id
Actual SQL: ds0 ::: select user_id , order_id AS ORDER_BY_DERIVED_0 from t_order1 order by order_id
Actual SQL: ds1 ::: select user_id , order_id AS ORDER_BY_DERIVED_0 from t_order0 order by order_id
Actual SQL: ds1 ::: select user_id , order_id AS ORDER_BY_DERIVED_0 from t_order1 order by order_id

Need order by specified in the sort field in select not followed, this time will be a derivative;

OrderByTokenGenerator

OrderByToken generator, automatically generate order by, effective conditions:

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext && ((SelectStatementContext) sqlStatementContext).getOrderByContext().isGenerated();
    }

First, it must be the select sentence, and then there is the automatically generated order by content;

For example, the following SQL:

select distinct user_id from t_order

The rewritten SQL is shown below, and the rewritten automatically adds ORDER BY

Actual SQL: ds0 ::: select distinct user_id from t_order0 ORDER BY user_id ASC 
Actual SQL: ds0 ::: select distinct user_id from t_order1 ORDER BY user_id ASC 
Actual SQL: ds1 ::: select distinct user_id from t_order0 ORDER BY user_id ASC 
Actual SQL: ds1 ::: select distinct user_id from t_order1 ORDER BY user_id ASC 

AggregationDistinctTokenGenerator

AggregationDistinctToken generator, similar to DistinctProjectionPrefixTokenGenerator

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext;
    }

Whether to generate SQLToken not checked, but when generating SQLToken , it will check whether there is an aggregate function and Distinct for deduplication;

IndexTokenGenerator

IndexToken generator is mainly used where the index is used, and the index name is renamed. It is used in sql server and PostgreSQL, but Mysql is not used;

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof IndexAvailable && !((IndexAvailable) sqlStatementContext).getIndexes().isEmpty();
    }

OffsetTokenGenerator

OffsetToken generator major role in the tab, the corresponding limit the offset keywords

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext
                && ((SelectStatementContext) sqlStatementContext).getPaginationContext().getOffsetSegment().isPresent()
                && ((SelectStatementContext) sqlStatementContext).getPaginationContext().getOffsetSegment().get() instanceof NumberLiteralPaginationValueSegment;
    }

Paging query is realized limit as follows:

SELECT * FROM t_order LIMIT 1,2

The rewritten SQL is shown below, and the paging parameters are rewritten to 0,3

Actual SQL: ds0 ::: SELECT * FROM t_order0 LIMIT 0,3
Actual SQL: ds0 ::: SELECT * FROM t_order1 LIMIT 0,3
Actual SQL: ds1 ::: SELECT * FROM t_order0 LIMIT 0,3
Actual SQL: ds1 ::: SELECT * FROM t_order1 LIMIT 0,3

RowCountTokenGenerator

RowCountToken generator, acting on the same page, the corresponding limit the count keywords

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof SelectStatementContext
                && ((SelectStatementContext) sqlStatementContext).getPaginationContext().getRowCountSegment().isPresent()
                && ((SelectStatementContext) sqlStatementContext).getPaginationContext().getRowCountSegment().get() instanceof NumberLiteralPaginationValueSegment;
    }

The example is consistent with the OffsetTokenGenerator

GeneratedKeyForUseDefaultInsertColumnsTokenGenerator

UseDefaultInsertColumnsToken generator, the insert sql does not contain the column name of the table

    public final boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof InsertStatementContext && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().isPresent()
                && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().get().isGenerated() && isGenerateSQLToken(((InsertStatementContext) sqlStatementContext).getSqlStatement());
    }
    
    protected boolean isGenerateSQLToken(final InsertStatement insertStatement) {
        return insertStatement.useDefaultColumns();
    }

Multiple conditions are required to use the above TokenGenerator including:

• Must be an insert statement;

• Configured KeyGeneratorConfiguration as follows:

  orderTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));

• The primary key is automatically generated, that is, the user did not actively generate the primary key;
• Use the default fields, the insert sql does not contain the column names of the table;

Let's look at an instance of insert sql:

insert into t_order values (1,1)

The rewritten SQL is as follows:

Actual SQL: ds1 ::: insert into t_order1(user_id, order_id, id) values (1, 1, 600986707608731648)

GeneratedKeyInsertColumnTokenGenerator

GeneratedKeyInsertColumnToken generator, insert sql contains the column names of the table, the other basics are the same as above

    protected boolean isGenerateSQLToken(final InsertStatement insertStatement) {
        Optional<InsertColumnsSegment> sqlSegment = insertStatement.getInsertColumns();
        return sqlSegment.isPresent() && !sqlSegment.get().getColumns().isEmpty();
    }

Let's look at an instance of insert sql:

insert into t_order (user_id,order_id) values (1,1)

The rewritten SQL is as follows:

Actual SQL: ds1 ::: insert into t_order1 (user_id,order_id, id) values (1, 1, 600988204400640000)

GeneratedKeyAssignmentTokenGenerator

GeneratedKeyAssignmentToken generator, mainly used for insert...set operations, no need to specify the primary key as above

    protected boolean isGenerateSQLToken(final InsertStatement insertStatement) {
        return insertStatement.getSetAssignment().isPresent();
    }

Let's look at an example of an insert set:

insert into t_order set user_id = 111,order_id=111

The rewritten SQL is as follows:

Actual SQL: ds1 ::: insert into t_order1 set user_id = 111,order_id=111, id = 600999588391813120

ShardingInsertValuesTokenGenerator

InsertValuesToken generator, with interpolated value for fragmentation processing

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceof InsertStatementContext && !(((InsertStatementContext) sqlStatementContext).getSqlStatement()).getValues().isEmpty();
    }

Let's look at an instance of insert sql:

insert into t_order values (95,1,1),(96,2,2)

The rewritten SQL is as follows:

Actual SQL: ds1 ::: insert into t_order1 values (95, 1, 1)
Actual SQL: ds0 ::: insert into t_order0 values (96, 2, 2)

GeneratedKeyInsertValuesTokenGenerator

InsertValuesToken generator, as long as there is an insert value, there will be this generator, provided that the primary key cannot be specified, use automatic generation

    protected boolean isGenerateSQLToken(final InsertStatement insertStatement) {
        return !insertStatement.getValues().isEmpty();
    }

Let's look at an instance of insert sql:

insert into t_order values (1,1),(2,2)

The rewritten SQL is as follows:

Actual SQL: ds1 ::: insert into t_order1(user_id, order_id, id) values (1, 1, 601005570564030465)
Actual SQL: ds0 ::: insert into t_order0(user_id, order_id, id) values (2, 2, 601005570564030464)

Generate SQLToken

The above introduced several common TokenGenerator and the conditions under which SQLToken can be generated. This section focuses on how to generate SQLToken . Two interface classes are provided:

• CollectionSQLTokenGenerator: The corresponding TokenGenerator will generate a SQLToken list; the implementation classes include: AggregationDistinctTokenGenerator , TableTokenGenerator , IndexTokenGenerator etc.
• OptionalSQLTokenGenerator: corresponding TokenGenerator generates unique SQLToken ; implementation class include: DistinctProjectionPrefixTokenGenerator , ProjectionsTokenGenerator , OrderByTokenGenerator , OffsetTokenGenerator , RowCountTokenGenerator , GeneratedKeyForUseDefaultInsertColumnsTokenGenerator , GeneratedKeyInsertColumnTokenGenerator , GeneratedKeyAssignmentTokenGenerator , ShardingInsertValuesTokenGenerator , GeneratedKeyInsertValuesTokenGenerator the like;

public interface CollectionSQLTokenGenerator<T extends SQLStatementContext> extends SQLTokenGenerator {
    Collection<? extends SQLToken> generateSQLTokens(T sqlStatementContext);
}

public interface OptionalSQLTokenGenerator<T extends SQLStatementContext> extends SQLTokenGenerator {
    SQLToken generateSQLToken(T sqlStatementContext);
}

Let's focus on analyzing how each generator generates the corresponding SQLToken ;

TableTokenGenerator

First obtain all table information SimpleTableSegment SQLStatementContext obtained from the parsing engine, and then check whether the current table is configured with TableRule , if it can be satisfied, then a TableToken will be created:

public final class TableToken extends SQLToken implements Substitutable, RouteUnitAware {
    @Getter
    private final int stopIndex;//结束位置，开始位置在父类SQLToken中
    private final IdentifierValue identifier; //表标识符信息 
    private final SQLStatementContext sqlStatementContext;//SQLStatement上下文
    private final ShardingRule shardingRule;//定义的分片规则
}

If there are multiple table information in SQL, a TableToken list will be generated here; because table name replacement processing is required, the relevant parameters defined above are required to facilitate subsequent rewriting processing;

DistinctProjectionPrefixTokenGenerator

The rewritten SQL for deduplication processing needs to add the DISTINCT keyword at the specified position:

public final class DistinctProjectionPrefixToken extends SQLToken implements Attachable {
    public DistinctProjectionPrefixToken(final int startIndex) {
        super(startIndex);
    }
}

You only need to provide a starting position here, that is, the position where you start inserting the DISTINCT keyword;

ProjectionsTokenGenerator

Aggregate function and derived keyword generator, you can view the enumeration class DerivedColumn :

public enum DerivedColumn {
    AVG_COUNT_ALIAS("AVG_DERIVED_COUNT_"), 
    AVG_SUM_ALIAS("AVG_DERIVED_SUM_"), 
    ORDER_BY_ALIAS("ORDER_BY_DERIVED_"), 
    GROUP_BY_ALIAS("GROUP_BY_DERIVED_"),
    AGGREGATION_DISTINCT_DERIVED("AGGREGATION_DISTINCT_DERIVED_");
}

For example, in the example described above, avg will generate:

• COUNT(user_id) AS AVG_DERIVED_COUNT_0 ；
• SUM(user_id) AS AVG_DERIVED_SUM_0 ；

Finally, all derivations are saved in a text and encapsulated in ProjectionsToken :

public final class ProjectionsToken extends SQLToken implements Attachable {
    private final Collection<String> projections;//派生列表
    public ProjectionsToken(final int startIndex, final Collection<String> projections) {
        super(startIndex);
        this.projections = projections;
    }
}

OrderByTokenGenerator

The key point is to generate the OrderByContext category. For details, you can view OrderByContextEngine , which will determine isDistinctRow ; finally, all fields and sorting methods that need to be generated order by OrderByToken :

public final class OrderByToken extends SQLToken implements Attachable {
    private final List<String> columnLabels = new LinkedList<>();  //order by字段
    private final List<OrderDirection> orderDirections = new LinkedList<>();//排序方式
    public OrderByToken(final int startIndex) {
        super(startIndex);
    }
}

AggregationDistinctTokenGenerator

The conditions are the DistinctProjectionPrefixTokenGenerator . The former mainly generates DISTINCT DistinctProjectionPrefixToken ; and this generator adds derived aliases to related fields, such as AGGREGATION_DISTINCT_DERIVED_0

select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 from t_order1 where order_id = 101

public final class AggregationDistinctToken extends SQLToken implements Substitutable {
    private final String columnName;//字段名称
    private final String derivedAlias;//别名
}

IndexTokenGenerator

If the SQL is a IndexAvailable and contains index information, a IndexToken will be generated, where the information is TableToken ; the common IndexAvailable includes: AlterIndexStatementContext , CreateIndexStatementContext , CreateTableStatementContext ; DropIndexStatementContext ;

OffsetTokenGenerator

Mainly for limit keywords offset value reset process, the information contained in the processing OffsetToken in:

public final class OffsetToken extends SQLToken implements Substitutable {
    @Getter
    private final int stopIndex;
    private final long revisedOffset; //修订过的offset
}

RowCountTokenGenerator

It mainly count limit in the 060a8c81c412b9 keyword. The processed information is contained in RowCountToken :

public final class RowCountToken extends SQLToken implements Substitutable {
    @Getter
    private final int stopIndex;
    private final long revisedRowCount; //修订过的rowcout
}

GeneratedKeyForUseDefaultInsertColumnsTokenGenerator

Because KeyGeneratorConfiguration configured, components will be automatically generated in the insert statement. When parsing, InsertStatementContext will be generated in GeneratedKeyContext , which contains the primary key field and the primary key value; this generator is the case in which no fields are specified in the insert, all fields will be It is saved in UseDefaultInsertColumnsToken , the field list is ordered, and the generated id needs to be moved to the end of the list;

public final class UseDefaultInsertColumnsToken extends SQLToken implements Attachable {
    private final List<String> columns;//字段列表：user_id,order_id,id
}

GeneratedKeyInsertColumnTokenGenerator

This generator is the case where the field is specified in the insert, and the place that needs to be rewritten is to add the name of the primary key field and save it in GeneratedKeyInsertColumnToken :

public final class GeneratedKeyInsertColumnToken extends SQLToken implements Attachable {
    private final String column;//主键字段：id
}

GeneratedKeyAssignmentTokenGenerator

This generator is used in insert set , you need to add a primary key and value, but because the parameter can be specified by the user, here will generate different tokens according to whether the parameter is configured:

• LiteralGeneratedKeyAssignmentToken: If there is no parameter, provide the primary key name and primary key value:

  public final class LiteralGeneratedKeyAssignmentToken extends GeneratedKeyAssignmentToken {
      private final Object value;//主键值
      public LiteralGeneratedKeyAssignmentToken(final int startIndex, final String columnName, final Object value) {
          super(startIndex, columnName);//开始位置和主键名称
          this.value = value;
      }

• ParameterMarkerGeneratedKeyAssignmentToken: The parameter is specified, only the primary key name needs to be provided, and the value is obtained from the parameter:

  public final class ParameterMarkerGeneratedKeyAssignmentToken extends GeneratedKeyAssignmentToken {
      public ParameterMarkerGeneratedKeyAssignmentToken(final int startIndex, final String columnName) {
          super(startIndex, columnName);//开始位置和主键名称
      }
  }

ShardingInsertValuesTokenGenerator

The inserted value can be one or multiple. Each piece of data will be DataNode , that is, which library belongs to which table. Here, the binding of the DataNode ShardingInsertValue :

public final class ShardingInsertValue {
    private final Collection<DataNode> dataNodes;//数据节点信息
    private final List<ExpressionSegment> values;//数据信息
}

Finally, all data is packed into ShardingInsertValuesToken ;

GeneratedKeyInsertValuesTokenGenerator

This generator will front ShardingInsertValuesTokenGenerator generated ShardingInsertValue for reprocessing, mainly for the case where no primary key, on which the values increase a ExpressionSegment stored primary key information;

Rewrite

Through the above two steps, all SQLToken has been prepared, and the rewriting operation can be performed below. The rewriting engine SQLRouteRewriteEngine . The two important parameters are:

• SQLRewriteContext: SQL rewrite context, the generated SQLToken is stored in the context;
• RouteResult: the result generated by the routing engine;

With the above two core parameters, the rewrite operation can be performed:

    public Map<RouteUnit, SQLRewriteResult> rewrite(final SQLRewriteContext sqlRewriteContext, final RouteResult routeResult) {
        Map<RouteUnit, SQLRewriteResult> result = new LinkedHashMap<>(routeResult.getRouteUnits().size(), 1);
        for (RouteUnit each : routeResult.getRouteUnits()) {
            result.put(each, new SQLRewriteResult(new RouteSQLBuilder(sqlRewriteContext, each).toSQL(), getParameters(sqlRewriteContext.getParameterBuilder(), routeResult, each)));
        }
        return result;
    }

Traverse each routing unit RouteUnit , and each routing unit corresponds to a SQL statement; generate and rewrite this SQL SQLToken list; you can find that the toSQL method RouteSQLBuilder

    public final String toSQL() {
        if (context.getSqlTokens().isEmpty()) {
            return context.getSql();
        }
        Collections.sort(context.getSqlTokens());
        StringBuilder result = new StringBuilder();
        result.append(context.getSql().substring(0, context.getSqlTokens().get(0).getStartIndex()));
        for (SQLToken each : context.getSqlTokens()) {
            result.append(getSQLTokenText(each));
            result.append(getConjunctionText(each));
        }
        return result.toString();
    }
    
    protected String getSQLTokenText(final SQLToken sqlToken) {
        if (sqlToken instanceof RouteUnitAware) {
            return ((RouteUnitAware) sqlToken).toString(routeUnit);
        }
        return sqlToken.toString();
    }
    
    private String getConjunctionText(final SQLToken sqlToken) {
        return context.getSql().substring(getStartIndex(sqlToken), getStopIndex(sqlToken));
    }

First, sort the list SQLToken startIndex from small to large; then from intercepting from 0 to SQLToken , this part of the SQL does not need any changes; the next step is to traverse SQLToken , and it will determine whether SQLToken RouteUnitAware , if it is a routing replacement, such as physical table replacement logical table; finally intercept the middle part of the SQLToken SQLToken , and return the re-spliced SQL;

Take a query SQL as an example:

select user_id,order_id from t_order where order_id = 101

The first interception from position 0 to the start position of the SQLToken

select user_id,order_id from 

Then traverse SQLToken , there is currently only one TableToken , and it is a RouteUnitAware , the table will be replaced:

t_order->t_order1

Intercept the last remaining part:

 where order_id = 101

Then stitch each part together to form SQL that can be executed by the database:

select user_id,order_id from t_order1 where order_id = 101

The following briefly introduces how each type of SQLToken performs the rewrite operation;

TableToken

TableToken is a RouteUnitAware RouteUnit passed in when rewriting, and it is necessary to determine which physical table the logical table should be rewritten to according to the routing unit;

DistinctProjectionPrefixToken

Mainly deal with aggregate functions and de- DISTINCT Token into a 060a8c81c4185e, here is actually only part of the processing, the aggregate function is not reflected, so there is a merge process in the entire process, many aggregate functions are required Processing in the merge process;

ProjectionsToken

Aggregate functions need to be derivated. You only need to splice the derived characters. For example: AVG_DERIVED_COUNT and AVG_DERIVED_SUM derived from AVG function, the same AVG aggregate function also needs to be processed in the merge process;

OrderByToken

Perform traversal processing on the order by field saved in it and the corresponding sorting method, and the result is similar to the following:

order by column1 desc,column2 desc...

AggregationDistinctToken

Mainly deal with aggregate functions and de- DistinctProjectionPrefixToken spliced with DISTINCT keywords, and this Token is spliced with aliases; the combination is as follows:

select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 

IndexToken

IndexToken is a RouteUnitAware , rewrite the index name according to the routing unit;

OffsetToken

Rewrite the offset in limit through the revised offset;

RowCountToken

Rewrite the count in limit through the revised RowCount;

GeneratedKeyInsertColumnToken

This rewriting is only for the field name, not the value. The value needs to be routed, that is, the primary key name is added to the existing field list;

(user_id)->(user_id,id)

UseDefaultInsertColumnsToken

This mode does not specify any fields when inserting data, so all field names will be generated here, and the primary key name will be added at the same time

()->(user_id,id)

GeneratedKeyAssignmentToken

In the previous section, it was introduced that this Token contains two subclasses, which correspond to the case of insert set with and without parameters; the rewriting here includes the name and value

set user_id = 11,id = 1234567

InsertValuesToken

Here is the difference between GeneratedKeyInsertColumnToken and UseDefaultInsertColumnsToken . This Token is the processing of the value, and the value must be routed. All this Token is also RouteUnitAware ;

to sum up

This article focuses on 13 situations where SQL needs to be rewritten. In fact, it is to find out all the SQL that needs to be rewritten, and then record it in a different Token. At the same time, it will also record the startIndex and stopIndex of the current Token in the original SQL, so that the rewriting can be done. Process, replace the SQL in the principle position; traverse the entire Token list to finally rewrite all parts of the SQL; Of course, the whole rewrite changes the meaning of the original SQL such as aggregate functions, so ShardingSphere-JDBC also provides a special merge engine, Used to ensure the integrity of SQL.

reference

https://shardingsphere.apache.org/document/current/cn/overview/

Thanks for attention

You can follow the WeChat public "160a8c81c41a78 roll back code ", read it as soon as possible, and the article is continuously updated; focus on Java source code, architecture, algorithm and interview.