ShardingSphere-JDBC sharding rewriting engine

preface

Shardingsphere-jdbc sharding routing engine introduces the routing engine in the sharding process and finally obtains the routing result. The rewriting engine described in this article uses routing results to rewrite SQL into SQL that can be executed by the correct sub-database and sub-table. There are many cases involving rewriting various SQL, and this article will examine them one by one.

Rewriting decorator

Rewriting engine also use the decorator pattern, provides the interface class SQLRewriteContextDecorator, implementation classes include:

• ShardingSQLRewriteContextDecorator: shard to rewrite the SQL decoration;
• ShadowSQLRewriteContextDecorator: shadow library SQL rewrite the decorator;
• EncryptSQLRewriteContextDecorator: data encryption to rewrite the SQL decoration;

The default load ShardingSQLRewriteContextDecorator and EncryptSQLRewriteContextDecorator, using Java. Util. ServiceLoader to load the rewrite a decorator, The specific implementation class needs to be specified in meta-INF /services/ :

org.apache.shardingsphere.sharding.rewrite.context.ShardingSQLRewriteContextDecorator
org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator
Copy the code

Decorators can be stacked, so the priority function OrderAware is provided, and each decorator has a corresponding rule, roughly as follows:

A decorator – SQLRewriteContextDecorator	Rules – BaseRule	Priority Order
ShardingSQLRewriteContextDecorator	ShardingRule	0
EncryptSQLRewriteContextDecorator	EncryptRule	20
ShadowSQLRewriteContextDecorator	ShadowRule	30

Only in the configuration related BaseRule, corresponding SQLRewriteContextDecorator take effect, the most common is ShardingSQLRewriteContextDecorator, introduced below the decorator;

Rewrite the engine

Different SQL statements require different rewriting. The overall structure of the rewriting engine is as follows:

Before executing the rewrite engine, you need to do some preparatory work. The whole rewrite process can be roughly divided into the following steps:

• Construct different decorators according to different rewriteSQLTokenGeneratorList;
• According to theSQLTokenGeneratorGenerate the correspondingSQLToken;
• Rewrite engine basedSQLTokenPerform rewrite operations;

Tectonic SQLTokenGenerator

Different decorator needs to construct different SQLTokenGenerator list, in the case of the most common ShardingSQLRewriteContextDecorator, 13 kinds of SQLTokenGenerator will prepare the following:

    private Collection<SQLTokenGenerator> buildSQLTokenGenerators(a) {
        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;
    }
Copy the code

The public interface class for the above implementation class is SQLTokenGenerator, which provides a public method to validate or not:

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

A variety of SQLTokenGenerator does not take effect every time, so you need to determine based on different SQL statements. SQL statements have been parsed as SQLStatementContext in the parsing engine, which can be determined by the SQLStatementContext parameter.

TableTokenGenerator

TableToken generator, mainly used for table name rewriting;

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

As you can see, there is no condition on whether to generate SQLToken. However, when TableToken is generated, it checks whether table information exists and whether the related table TableRule is configured.

DistinctProjectionPrefixTokenGenerator

DistinctProjectionPrefixToken generator, mainly handling of aggregation function and to weight:

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceofSelectStatementContext && ! ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getAggregationDistinctProjections().isEmpty(); }Copy the code

A select statement must contain both aggregate functions and Distinct de-duplicates, such as the following SQL statement:

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

The rewritten SQL looks like this:

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
Copy the code

ProjectionsTokenGenerator

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

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

The first must be a SELECT statement, and the second can be:

• Aggregate functions, while deriving new functions, such as AVG functions;
• 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
Copy the code

The rewritten SQL looks like this:

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
Copy the code

For example order by using:

select user_id from t_order order by order_id
Copy the code

The rewritten SQL looks like this:

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
Copy the code

The sort field that needs to be specified in order by does not exist after SELECT, so one is derived;

OrderByTokenGenerator

OrderByToken generator, automatically generate order by, effective conditions:

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

The first must be a SELECT statement, followed by an automatically generated order by content;

For example, SQL:

select distinct user_id from t_order
Copy the code

The SQL overwritten is shown below, and the overwritten 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 
Copy the code

AggregationDistinctTokenGenerator

A similar DistinctProjectionPrefixTokenGenerator AggregationDistinctToken generator,

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

Whether to generate SQLToken Is not checked. However, when SQLToken is generated, aggregation functions and Distinct deduplication are checked.

IndexTokenGenerator

IndexToken is used to rename indexes where they are used. It is used in SQL Server, PostgreSQL, but not Mysql.

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceofIndexAvailable && ! ((IndexAvailable) sqlStatementContext).getIndexes().isEmpty(); }Copy the code

OffsetTokenGenerator

OffsetToken generator, mainly for paging, corresponding to the offset keyword limit

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

To implement paging query by limit:

SELECT * FROM t_order LIMIT 1.2
Copy the code

The rewritten SQL looks like this, with the paging parameters 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
Copy the code

RowCountTokenGenerator

RowCountToken generator, also for paging, corresponding to the count keyword of limit

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

The instance is the same as OffsetTokenGenerator above;

GeneratedKeyForUseDefaultInsertColumnsTokenGenerator

UseDefaultInsertColumnsToken generator, the insert SQL does not contain table column name

    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();
    }
Copy the code

Several conditions are required to use the above TokenGenerator:

• Must be an INSERT statement;

• Configure the KeyGeneratorConfiguration, as shown below:

  orderTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE"."id"));
  Copy the code

• The primary key is automatically generated meaning the user didn’t actively generate the primary key;

• The default fields are used, and the INSERT SQL does not contain the column names of the tables;

Let’s look at an example of insert SQL:

insert into t_order values (1.1)
Copy the code

The rewritten SQL looks like this:

Actual SQL: ds1 ::: insert into t_order1(user_id, order_id, id) values (1.1.600986707608731648)
Copy the code

GeneratedKeyInsertColumnTokenGenerator

GeneratedKeyInsertColumnToken generator, the insert SQL contained in the table column name, other basic same as above

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

Let’s look at an example of insert SQL:

insert into t_order (user_id,order_id) values (1.1)
Copy the code

The rewritten SQL looks like this:

Actual SQL: ds1 ::: insert into t_order1 (user_id,order_id, id) values (1.1.600988204400640000)
Copy the code

GeneratedKeyAssignmentTokenGenerator

GeneratedKeyAssignmentToken generator, mainly for the insert… Set operation, same as above without specifying primary key

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

Here’s an example of an insert set:

insert into t_order set user_id = 111,order_id=111
Copy the code

The rewritten SQL looks like this:

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

ShardingInsertValuesTokenGenerator

InsertValuesToken generator, with an insert value to do fragment processing

    public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
        return sqlStatementContext instanceofInsertStatementContext && ! (((InsertStatementContext) sqlStatementContext).getSqlStatement()).getValues().isEmpty(); }Copy the code

Let’s look at an insert SQL instance:

insert into t_order values (95.1.1), (96.2.2)
Copy the code

The rewritten SQL looks like this:

Actual SQL: ds1 ::: insert into t_order1 values (95.1.1)
Actual SQL: ds0 ::: insert into t_order0 values (96.2.2)
Copy the code

GeneratedKeyInsertValuesTokenGenerator

InsertValuesToken generator, exists whenever there is an insert value, if primary key cannot be specified, use automatic generation

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

Let’s look at an insert SQL instance:

insert into t_order values (1.1), (2.2)
Copy the code

The rewritten SQL looks like this:

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)
Copy the code

Generate SQLToken

This section focuses on how to generate SQLToken, and provides two interface classes:

• CollectionSQLTokenGenerator: correspondingTokenGeneratorWill be generatedSQLTokenList; Implementation classes include:AggregationDistinctTokenGenerator,TableTokenGenerator,IndexTokenGeneratorEtc.
• OptionalSQLTokenGenerator: correspondingTokenGeneratorWill generate a uniqueSQLToken; Implementation classes include:DistinctProjectionPrefixTokenGenerator,ProjectionsTokenGenerator,OrderByTokenGenerator,OffsetTokenGenerator,RowCountTokenGenerator,GeneratedKeyForUseDefaultInsertColumnsTokenGenerator,GeneratedKeyInsertColumnTokenGenerator,GeneratedKeyAssignmentTokenGenerator,ShardingInsertValuesTokenGenerator,GeneratedKeyInsertValuesTokenGeneratorAnd so on;

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);
}
Copy the code

The following focuses on how each generator generates the corresponding SQLToken;

TableTokenGenerator

SQLStatementContext = SimpleTableSegment; TableToken = SimpleTableSegment; TableToken = SimpleTableSegment; TableToken = SimpleTableSegment;

public final class TableToken extends SQLToken implements Substitutable.RouteUnitAware {
    @Getter
    private final int stopIndex;// End position, start position in parent class SQLToken
    private final IdentifierValue identifier; // Table identifier information
    private final SQLStatementContext sqlStatementContext;/ / SQLStatement context
    private final ShardingRule shardingRule;// Define sharding rules
}
Copy the code

If there are multiple tables in the SQL, the TableToken list is generated. As table name replacement is required, relevant parameters defined above are required to facilitate subsequent rewriting.

DistinctProjectionPrefixTokenGenerator

To reprocess the rewritten SQL, add the DISTINCT keyword in the specified location:

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

You only need to provide a starting location, where the DISTINCT keyword is inserted;

ProjectionsTokenGenerator

Aggregate functions and derived keyword generators, 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_");
}
Copy the code

For example, in the example above, AVG will generate:

• COUNT (user_id) AS AVG_DERIVED_COUNT_0;
• The SUM (user_id) AS AVG_DERIVED_SUM_0;

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

public final class ProjectionsToken extends SQLToken implements Attachable {
    private final Collection<String> projections;// Derived list
    public ProjectionsToken(final int startIndex, final Collection<String> projections) {
        super(startIndex);
        this.projections = projections; }}Copy the code

OrderByTokenGenerator

The point is to generate the OrderByContext class, and specifically look at OrderByContextEngine, which will judge isDistinctRow; Finally, all the fields and sorts needed to generate order BY are saved in OrderByToken:

public final class OrderByToken extends SQLToken implements Attachable {
    private final List<String> columnLabels = new LinkedList<>();  / / order by field
    private final List<OrderDirection> orderDirections = new LinkedList<>();// Sort
    public OrderByToken(final int startIndex) {
        super(startIndex); }}Copy the code

AggregationDistinctTokenGenerator

Conditions and DistinctProjectionPrefixTokenGenerator is consistent, the former mainly through DistinctProjectionPrefixToken generates keywords DISTINCT; This generator, in turn, is to add derived aliases to the related fields, such as AGGREGATION_DISTINCT_DERIVED_0 in the example above

select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 from t_order1 where order_id = 101
Copy the code

public final class AggregationDistinctToken extends SQLToken implements Substitutable {
    private final String columnName;// Field name
    private final String derivedAlias;/ / alias
}
Copy the code

IndexTokenGenerator

If the SQL is IndexAvailable and contains index information, an IndexToken is generated with the same information as the TableToken. Common IndexAvailable items include: AlterIndexStatementContext, CreateIndexStatementContext, CreateTableStatementContext, DropIndexStatementContext;

OffsetTokenGenerator

The offset value in the limit keyword is reset, and the processing information is contained in OffsetToken:

public final class OffsetToken extends SQLToken implements Substitutable {
    @Getter
    private final int stopIndex;
    private final long revisedOffset; // Modified offset
}
Copy the code

RowCountTokenGenerator

This resets the count value in the limit keyword, which is contained in RowCountToken:

public final class RowCountToken extends SQLToken implements Substitutable {
    @Getter
    private final int stopIndex;
    private final long revisedRowCount; // Revised Rowcout
}
Copy the code

GeneratedKeyForUseDefaultInsertColumnsTokenGenerator

Because the configuration KeyGeneratorConfiguration, automatically generated components in the insert statement, could generate GeneratedKeyContext in InsertStatementContext parsing, contains the primary key field, as well as the primary key value; This generator is not specified in the insert fields, all of the fields will be saved to UseDefaultInsertColumnsToken, field list is ordered, need to generate the id of the move to the end of the list;

public final class UseDefaultInsertColumnsToken extends SQLToken implements Attachable {
    private final List<String> columns;// Field list: user_id,order_id,id
}
Copy the code

GeneratedKeyInsertColumnTokenGenerator

This generator is specified in the insert the field situation, need to rewrite is adding a primary key field name, save the GeneratedKeyInsertColumnToken:

public final class GeneratedKeyInsertColumnToken extends SQLToken implements Attachable {
    private final String column;// Primary key field: id
}
Copy the code

GeneratedKeyAssignmentTokenGenerator

This generator is used in an insert set and needs to add primary keys, values, but since parameters can be specified by the user, different tokens are generated depending on whether parameter is configured:

• LiteralGeneratedKeyAssignmentToken: no parameter, provide the name of the primary key and primary key values:

  public final class LiteralGeneratedKeyAssignmentToken extends GeneratedKeyAssignmentToken {
      private final Object value;/ / the primary key
      public LiteralGeneratedKeyAssignmentToken(final int startIndex, final String columnName, final Object value) {
          super(startIndex, columnName);// Start position and primary key name
          this.value = value;
      }
  Copy the code

• ParameterMarkerGeneratedKeyAssignmentToken: specifies the parameter, you just need to provide the name of the primary key, the values obtained from the parameter:

  public final class ParameterMarkerGeneratedKeyAssignmentToken extends GeneratedKeyAssignmentToken {
      public ParameterMarkerGeneratedKeyAssignmentToken(final int startIndex, final String columnName) {
          super(startIndex, columnName);// Start position and primary key name}}Copy the code

ShardingInsertValuesTokenGenerator

The inserted value can be one or more, and each piece of data will be bound to DataNode, that is, which library belongs to which table. Here, the binding between data and DataNode is wrapped in ShardingInsertValue:

public final class ShardingInsertValue {
    private final Collection<DataNode> dataNodes;// Data node information
    private final List<ExpressionSegment> values;// Data information
}
Copy the code

Finally all the data package to the ShardingInsertValuesToken;

GeneratedKeyInsertValuesTokenGenerator

The generator to the front ShardingInsertValuesTokenGenerator generated ShardingInsertValue reprocessing treatment, mainly in view of the situation is not specified primary key, Add an ExpressionSegment to values

Perform rewrite

SQLRouteRewriteEngine with two important parameters: SQLRouteRewriteEngine

• SQLRewriteContext: SQL rewrite context in which sqlTokens are generated;
• RouteResult: Result generated by the routing engine;

The overwrite operation can be performed with these two core parameters:

    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;
    }
Copy the code

Each RouteUnit is traversed, and each routing unit corresponds to an SQL statement. Generate and rewrite this SQL according to routing unit and SQLToken list; You can see that the toSQL method in RouteSQLBuilder is executed here:

    public final String toSQL(a) {
        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));
    }
Copy the code

First, sort the SQLToken list, sort according to startIndex from smallest to largest; The SQL is then truncated from position 0 to the start of the first SQLToken. No changes are required for this part of the SQL; The next step is to iterate over the SQLToken and determine whether the SQLToken is RouteUnitAware. If so, route replacement is performed, such as physical table replacement for logical table. Finally, the middle part of the two SQLTokens is intercepted, and all SQLTokens are iterated to return the rejoined SQL.

Take a query SQL as an example:

select user_id,order_id from t_order where order_id = 101
Copy the code

The first interception from position 0 to the start of the first SQLToken results in:

select user_id,order_id from 
Copy the code

After iterating through the SQLToken, there is currently only one TableToken, and it is a RouteUnitAware, and the table will be replaced:

t_order->t_order1
Copy the code

Take the last remaining part:

 where order_id = 101
Copy the code

Then splice 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
Copy the code

Here is a brief overview of how each SQLToken performs the rewrite operation;

TableToken

TableToken is a RouteUnitAware, so the routing unit RouteUnit is passed in the rewrite. According to the routing unit, it is necessary to decide which physical table the logical table should be rewritten into.

DistinctProjectionPrefixToken

It mainly deals with aggregation functions and de-duplication. Here, it only changes the current Token into a DISTINCT one. In fact, it is only partially processed here, and aggregation functions are not reflected.

ProjectionsToken

Aggregation functions need to be derived. You only need to concatenate the derived characters, such as AVG_DERIVED_COUNT and AVG_DERIVED_SUM derived from AVG functions. The same AVG aggregation functions also need to be processed in the merge process.

OrderByToken

The order by field saved inside and the corresponding sorting method are traversed, and the result is similar to the following:

order by column1 desc,column2 desc...
Copy the code

AggregationDistinctToken

Mainly for aggregation function and to the processing of heavy, DistinctProjectionPrefixToken splicing DISTINCT keywords, the Token splicing alias; Combined as follows:

select DISTINCT user_id AS AGGREGATION_DISTINCT_DERIVED_0 
Copy the code

IndexToken

IndexToken is a RouteUnitAware that overwrites index names based on routing units;

OffsetToken

Offset overwrites offset in limit with the revised offset;

RowCountToken

Overwrite count in limit with a revised RowCount;

GeneratedKeyInsertColumnToken

This rewrite is only for field names, not values, which need to do routing, i.e. add primary key names to the existing field list;

(user_id)->(user_id,id)
Copy the code

UseDefaultInsertColumnsToken

This mode does not specify any fields when inserting data, so all field names are generated and primary key names are added

()->(user_id,id)

GeneratedKeyAssignmentToken

The previous section described that this Token contains two subclasses, corresponding to insert set with and without parameters. The overrides here contain names and values

set user_id = 11,id = 1234567
Copy the code

InsertValuesToken

Here the difference between the above GeneratedKeyInsertColumnToken UseDefaultInsertColumnsToken and handling of the field, the Token is handling of values, the value must be route processing, all this Token is RouteUnitAware;

conclusion

This article focuses on 13 cases where SQL needs to be rewritten. In fact, it is necessary to find all THE SQL that needs to be rewritten and record it in different tokens. In addition, it will record the startIndex and stopIndex corresponding to the current Token in the original SQL. All parts of SQL can be rewritten by traversing the entire Token list. Of course, there are some changes in the meaning of the original SQL, such as aggregation functions, so ShardingSphere-JDBC also provides a special merge engine to ensure the integrity of SQL.

reference

Shardingsphere.apache.org/document/cu…

Thank you for attention

You can pay attention to the wechat public account “Roll back the code”, read the first time, the article continues to update; Focus on Java source code, architecture, algorithms, and interviews.