You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/07/27 06:15:51 UTC
[GitHub] [shardingsphere] johnny2002 opened a new issue #6458: How to skip sql parse and rewrite
johnny2002 opened a new issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458
## Question
We have an ETL batch job, that the SQLs are very complex. We want to manually specify the datasource by HintManager and the real table name already fixed in SQLs, while we cann't skip sql parser, and the sql parser caused some exceptions.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683561266
I think that org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext should be placed in the implementation of org.apache.shardingsphere.underlying.common.rule.BaseRule in order to satisfy everyone's way of parsing or expanding sql to facilitate implementation Cover funation
For performance issues, you can use cache sql as the key and RouteContext result as vlaue
You can also use the callback method to parse when needed and provide a RouteContext result operation object
中文:
我认为 在org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext 应该放到org.apache.shardingsphere.underlying.common.rule.BaseRule 的实现中,以满足大家对sql 解析或者拓展的方式,以方便实现覆盖funation
性能问题可以使用缓存sql 为key,RouteContext result 为vlaue
也可以使用回调方式,在需要时进行解析,提供一个RouteContext result操作对象
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683561266
I think that org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext should be placed in the implementation of org.apache.shardingsphere.underlying.common.rule.BaseRule in order to satisfy everyone's way of parsing or expanding sql to facilitate implementation Cover funation
中文:
我认为 在org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext 应该放到org.apache.shardingsphere.underlying.common.rule.BaseRule 的实现中,以满足大家对sql 解析或者拓展的方式,以方便实现覆盖funation
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-692039656
This post is silent for 2 weeks and discussion out of the original topic, so I should close it now.
And anything forward and can help us move on NEED TO open a new issue to track it.
Thanks guys.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.statement.dal.DALStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private static final SQLStatement DEFAULT_SQL_STATEMENT = new DALStatement() {
@Override
public int getParameterCount() {
return 0;
}
};
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public SQLParserEngine getParserEngine() {
return parserEngine;
}
public ShardingSphereMetaData getMetaData() {
return metaData;
}
public String getSql() {
return sql;
}
public RouteContext getResult() {
return result;
}
public List<Object> getParameters() {
return parameters;
}
public boolean isUseCache() {
return useCache;
}
public SQLStatement getSqlStatement() {
return sqlStatement;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public void createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement == null ? DEFAULT_SQL_STATEMENT : sqlStatement);
result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSetMetaData
```java
public final class ShardingResultSetMetaData extends WrapperAdapter implements ResultSetMetaData {
...
@Override
public int getColumnCount() throws SQLException {
//非SelectStatementContext 返回resultSetMetaData.getColumnCount(); 大小
//Non selectstatementcontext return resultSetMetaData.getColumnCount (); size
return sqlStatementContext instanceof SelectStatementContext ? ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getExpandProjections().size() : resultSetMetaData.getColumnCount();
}
...
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking closed issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-671215272
> @johnny2002 Got it. Thanks for your further clarification.
> Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
> Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
>
> I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
other advantages has been stated on your official document already:
“Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.**After that, SQL parse and rewrite phase will be skipped and the _overall enforcement efficiency can be enhanced_**”
redarding "why do not you use multiple independent MySQL instances instead", in one application, since I have already configured one Shardingsphere datasource, I don't want to repeat configure for several datasources. Otherwise, I need to implement another datasource route routine.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-671215272
> @johnny2002 Got it. Thanks for your further clarification.
> Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
> Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
>
> I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
other advantages has been stated on your official document already:
“Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.**After that, SQL parse and rewrite phase will be skipped and the _overall enforcement efficiency can be enhanced_**”, In fact, advantages is far from that, most of the time, complex SQLs usaully causes Sharding-jdbc parsing SQL exception.
redarding "why do not you use multiple independent MySQL instances instead", in one application, since I have already configured one Shardingsphere datasource, I don't want to repeat configure for several datasources. Otherwise, I need to implement another datasource route program.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Jiugang commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
Jiugang commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-970155752
@tristaZero OK, thank you.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Jiugang commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
Jiugang commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-957278781
@tristaZero The following statements still not removed，which mislead me for 2~3 days until I find this post.
> @johnny2002 It is my honor to look at your comment in my lifetimes. The PR is,
>
> **To remove the relevant statements below to avoid users' confusion like you.**
>
> > Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-671215272
> @johnny2002 Got it. Thanks for your further clarification.
> Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
> Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
>
> I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
other advantages has been stated on your official document already:
“Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.**After that, SQL parse and rewrite phase will be skipped and the _overall enforcement efficiency can be enhanced_**”
redarding "why do not you use multiple independent MySQL instances instead", in one application, since I have already configured one Shardingsphere datasource, I don't want to repeat configure for several datasources. Otherwise, I need to implement another datasource route program.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669840352
>> Hi @johnny2002
>> You mean to add `logical table` and `sharding key` to `hintManger` **just for** skipping the SQL parsing and rewriting?
>[A] Exactly!
>> If so, actually, sharding JDBC does need to parse SQL. Do you think there are any other scenarios needing this feature?
>[A] My case is, I have some very complex SQLs that involes many tables, while I already know which database to use, and the real tablename already hardcode in the SQLs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669840352
> Hi @johnny2002
> You mean to add `logical table` and `sharding key` to `hintManger` **just for** skipping the SQL parsing and rewriting?
[A] Exactly!
> If so, actually, sharding JDBC does need to parse SQL. Do you think there are any other scenarios needing this feature?
My case is, I have some very complex SQLs that involes many tables, while I already know whick database to use, and the real tablename already hardcode in the SQLs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664144149
my config file:
spring.shardingsphere.!SHARDING.tables.tab_user.actual-data-nodes=master${0..1}.tab_user
spring.shardingsphere.!SHARDING.tables.tab_user.database-strategy.hint.algorithmClassName=com.ibm.sharding.MyHintShardingAlgorithm
My java code:
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("0"); //
I want sharding-jdbc do not parse my sql, and get the right datasource from MyHintShardingAlgorithm directly. I think, you need to have somewhere to let me set the logic table name so that you know the configuration.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
shardingsphere 4.1.1
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.statement.dal.DALStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private static final SQLStatement DEFAULT_SQL_STATEMENT = new DALStatement() {
@Override
public int getParameterCount() {
return 0;
}
};
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public SQLParserEngine getParserEngine() {
return parserEngine;
}
public ShardingSphereMetaData getMetaData() {
return metaData;
}
public String getSql() {
return sql;
}
public RouteContext getResult() {
return result;
}
public List<Object> getParameters() {
return parameters;
}
public boolean isUseCache() {
return useCache;
}
public SQLStatement getSqlStatement() {
return sqlStatement;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public void createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement == null ? DEFAULT_SQL_STATEMENT : sqlStatement);
result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSetMetaData
```java
public final class ShardingResultSetMetaData extends WrapperAdapter implements ResultSetMetaData {
...
@Override
public int getColumnCount() throws SQLException {
//非SelectStatementContext 返回resultSetMetaData.getColumnCount(); 大小
//Non selectstatementcontext return resultSetMetaData.getColumnCount (); size
return sqlStatementContext instanceof SelectStatementContext ? ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getExpandProjections().size() : resultSetMetaData.getColumnCount();
}
...
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664163454
sql parsing is a necessary phase.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664194201
> sql parsing is a necessary phase.
If you know the logicTableName, why do you need to parse SQL?
So suggestted solution:
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("0");
hintManager.setLogicTableName("tab_user");
Then skip sql parsing
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669652411
Hi @johnny2002
You mean to add `logical table` and `sharding key` to `hintManger` **just for** skipping the SQL parsing and rewriting?
If so, actually, sharding JDBC does not need to parse SQL. Do you think there are any other scenarios needing this feature?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664194201
> sql parsing is a necessary phase.
If you know the logicTableName, why do you need to parse SQL.
So suggestted solution:
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("0");
hintManager.setLogicTableName("tab_user");
Then skip sql parsing
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Jiugang commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
Jiugang commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-957278781
@tristaZero The following statements still not removed，which mislead me for 2~3 days until I find this post.
> @johnny2002 It is my honor to look at your comment in my lifetimes. The PR is,
>
> **To remove the relevant statements below to avoid users' confusion like you.**
>
> > Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-968835812
> @tristaZero The following statements still not removed，which mislead me for 2~3 days until I find this post.
>
> > @johnny2002 It is my honor to look at your comment in my lifetimes. The PR is,
> > **To remove the relevant statements below to avoid users' confusion like you.**
> > > Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced
Removed by https://github.com/apache/shardingsphere/pull/12190.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-674339190
BTW, where is the following description? I am sorry for the confusion. If possible, would you mind raizing a PR to change the following words to avoid confusing others?
> “Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced”,
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-684132721
I have completed the test of the above code,RouteDecorator implements class is not open code
Developed, universal adaptation strategy, no need to configure table by table
中文:
上述代码我已完成测试, RouteDecorator implements class 未开放代码
已开发,通用适配策略,无需逐个表配置
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664236906
@johnny2002 The reason is that we can not know whether this `logicTableName` exists at your input SQL if there is no SQL parsing.
> > sql parsing is a necessary phase.
>
> If you know the logicTableName, why do you need to parse SQL?
>
> So suggestted solution:
> HintManager hintManager = HintManager.getInstance();
> hintManager.setDatabaseShardingValue("0");
> hintManager.setLogicTableName("tab_user");
>
> Then skip sql parsing
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669840352
>> Hi @johnny2002
>> You mean to add `logical table` and `sharding key` to `hintManger` **just for** skipping the SQL parsing and rewriting?
>[A] Exactly!
>> If so, actually, sharding JDBC does need to parse SQL. Do you think there are any other scenarios needing this feature?
>[A] My case is, I have some very complex SQLs that involes many tables, while I already know whick database to use, and the real tablename already hardcode in the SQLs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
// **延期处理createRouteContext ,交给RouteDecorator decorate 决定**
//**Postpone processing of createRouteContext and leave it to RouteDecorator decorate to decide**
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public RouteContext createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement);
return result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
return result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669128677
> @johnny2002 The reason is that we can not know whether this `logicTableName` exists at your input SQL if there is no SQL parsing.
>
> > > sql parsing is a necessary phase.
> >
> >
> > If you know the logicTableName, why do you need to parse SQL?
> > So suggestted solution:
> > HintManager hintManager = HintManager.getInstance();
> > hintManager.setDatabaseShardingValue("0");
> > hintManager.setLogicTableName("tab_user");
> > Then skip sql parsing
Dear,
Since I already told you the logicTableName, you should treat it as exists, even sometimes, the logicTableName not exists in SQL, but this is my intent. e.g. I want to use the sharding rule of table-a to shard table-b, this should be allowed.
Hope you can double check my suggestion. This feature should broadly promote the usage of Sharding-jdbc.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-673320487
@johnny2002 Catch your idea. I think it is reasonable, Please give the community some time to discuss this feature. I will give the feedback here ASAP.
Thanks for your feedback. :-)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669652411
Hi @johnny2002
You mean to add `logical table` and `sharding key` to `hintManger` **just for** skipping the SQL parsing and rewriting?
If so, actually, sharding JDBC does need to parse SQL. Do you think there are any other scenarios needing this feature?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664144149
my config file:
spring.shardingsphere.!SHARDING.tables.tab_user.actual-data-nodes=ds${0..1}.tab_user
spring.shardingsphere.!SHARDING.tables.tab_user.database-strategy.hint.algorithmClassName=com.ibm.sharding.MyHintShardingAlgorithm
My java code:
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("0"); //
boolean execute = statement.execute("insert into ... select ... from .... group by ... having...");
I want sharding-jdbc do not parse my sql, and get the right datasource from MyHintShardingAlgorithm directly.
I think, you need to have somewhere to let me set the logic table name so that you know the configuration without parse SQL.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-671215272
> @johnny2002 Got it. Thanks for your further clarification.
> Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
> Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
>
> I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
other advantages has been stated on your official document already:
“Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.**After that, SQL parse and rewrite phase will be skipped and the _overall enforcement efficiency can be enhanced_**”, In fact, advantages is far from that, most importantly, complex SQLs usaully causes Sharding-jdbc parsing SQL exception.
redarding "why do not you use multiple independent MySQL instances instead", in one application, since I have already configured one Shardingsphere datasource, I don't want to repeat the config for several datasources. Otherwise, I need to implement another datasource route program.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-678013082
Hi @johnny2002
It is a friendly ping.
Could you point the location of the statements below?
So we can fix them to avoid users' confusion like you. Plus, it will be a volunteer task for the community. If you like, then welcome your PR.
> "Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced",
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-673921034
@johnny2002 After a short discussion about this issue, others also give their thoughts about it. Here is a summary,
1. Actually, `Hint` is to inject `sharding key` which does not exist in your actual table, rather than to skip parsing and rewriting.
2. If there is no `parsing`, then `rewriting SQL` can not work either. Consequently, users have to write SQL with the actual table name instead of a logic table. That will confuse the user, right?
3. We would rather take more efforts on `SQL Parser` than provide users `non-standard API`.
Welcome to your opinion!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking closed issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717707017
thanks for your response.
in my situation,i just need database-sharding and no table-sharding. now batch insert 4000 rows will take 32s, and What can I do to improve the speed？
sql like this: insert into pressure(userid,orderid) values(?, ?),(?,?),...
sharding-jdbc 4.0.0-RC2, MYSQL 5.7.12-log
_the following is some of my log while batch insert 4000 rows:
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : HintShardingAlgorithm.doSharding
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : 数据库路由完毕.
2020-10-28 13:19:38.553 DEBUG 9960 --- [nio-9002-exec-1] c.h.s.test.mapper.MapperTest.insertData : <== Updates: 4000
2020-10-28 13:19:38.562 INFO 9960 --- [nio-9002-exec-1] c.h.sharding.test.controller.controller : sql: 30.554s_
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-684167802
Hi @unjie
Thanks for your comment. Although I want to catch your idea, the rough and unformatted code clips held me back.
Could you raize a PR to present your programming and thought?
Very appreciated that,
Trista
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717673839
Hi @gongguoyu, It seems we will begin an `archeological` work.
First, @unjie thanks for your comments. If your idea is a general and universal solution, welcome your PR to make it merged into the master branch. :-) What's more, I suppose a simple explanation about your approach will make others catch your idea easily
Second, @gongguoyu Is your case only about database-sharding rather than table-sharding? As if users want to set table sharding value with hintManager, parsing work is needed to rewrite the logic SQLs to actual SQLs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664217895
see https://github.com/apache/shardingsphere/issues/4806
https://github.com/apache/shardingsphere/issues/4281
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-664144149
my config file:
spring.shardingsphere.!SHARDING.tables.tab_user.actual-data-nodes=master${0..1}.tab_user
spring.shardingsphere.!SHARDING.tables.tab_user.database-strategy.hint.algorithmClassName=com.ibm.sharding.MyHintShardingAlgorithm
My java code:
HintManager hintManager = HintManager.getInstance();
hintManager.setDatabaseShardingValue("0"); //
boolean execute = statement.execute("insert into ... select ... from .... group by ... having...");
I want sharding-jdbc do not parse my sql, and get the right datasource from MyHintShardingAlgorithm directly.
I think, you need to have somewhere to let me set the logic table name so that you know the configuration without parse SQL.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717707017
@tristaZero thanks for your response.
in my situation,i just need database-sharding ,no table-sharding, and all sql will run in the selected database. now batch insert 4000 rows will take 32s
sql like this: insert into pressure(userid,orderid) values(?, ?),(?,?),...
sharding-jdbc 4.0.0-RC2, MYSQL 5.7.12-log
en.................
it's seems ok in 4.1.1
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Jiugang commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
Jiugang commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-957278781
@tristaZero The following statements still not removed，which mislead me for 2~3 days until I find this post.
> @johnny2002 It is my honor to look at your comment in my lifetimes. The PR is,
>
> **To remove the relevant statements below to avoid users' confusion like you.**
>
> > Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683561266
I think that org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext should be placed in the implementation of org.apache.shardingsphere.underlying.common.rule.BaseRule in order to satisfy everyone's way of parsing or expanding sql to facilitate implementation Cover funation
for performance issues, you can use cache sql as the key and RouteContext result as vlaue
中文:
我认为 在org.apache.shardingsphere.underlying.route.DataNodeRouter#createRouteContext 应该放到org.apache.shardingsphere.underlying.common.rule.BaseRule 的实现中,以满足大家对sql 解析或者拓展的方式,以方便实现覆盖funation
性能问题可以使用缓存sql 为key,RouteContext result 为vlaue
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
`
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
`
org.apache.shardingsphere.underlying.route.DataNodeRouter
`
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public RouteContext createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement);
return result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
return result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-682394290
> Hi @johnny2002
>
> It is a friendly ping.
> Could you point the location of the statements below?
> So we can fix them to avoid users' confusion like you. Plus, it will be a volunteer task for the community. If you like, then welcome your PR.
>
> > "Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced",
Sorry for reply late. what is PR?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683285021
@johnny2002 It is my honor to look at your comment in my lifetimes.
The PR is,
**To remove the relevant statements below to avoid users' confusion like you.**
> Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-673779863
> @johnny2002 Catch your idea. I think it is reasonable, Please give the community some time to discuss this feature. I will give the feedback here ASAP.
> Thanks for your feedback. :-)
Perfect!
Suggestted solution:
hintManager.setDatabaseShardingValue(Comparable<?> value, String logicTable,). if the logicTable is present, then use it instead of parsing logicTable from SQL.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-968780735
@Jiugang Sorry for my late response, Thanks. @RaigorJiang could you have a look here?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-670407492
@johnny2002 Got it. Thanks for your further clarification.
Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717665367
same question:
https://shardingsphere.apache.org/document/legacy/4.x/document/en/manual/sharding-jdbc/usage/hint/
Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables. After that, SQL parse and rewrite phase will be skipped and the overall enforcement efficiency can be enhanced.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717707017
@tristaZero thanks for your response.
in my situation,i just need database-sharding ,no table-sharding, and all sql will run in the selected database. now batch insert 4000 rows will take 32s, and What can I do to improve the speed？
sql like this: insert into pressure(userid,orderid) values(?, ?),(?,?),...
sharding-jdbc 4.0.0-RC2, MYSQL 5.7.12-log
_the following is some of my log while batch insert 4000 rows:
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : HintShardingAlgorithm.doSharding
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : 数据库路由完毕.
2020-10-28 13:19:38.553 DEBUG 9960 --- [nio-9002-exec-1] c.h.s.test.mapper.MapperTest.insertData : <== Updates: 4000
2020-10-28 13:19:38.562 INFO 9960 --- [nio-9002-exec-1] c.h.sharding.test.controller.controller : sql: 30.554s_
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-684132721
I have completed the test of the above code,RouteDecorator implements class is not open code
中文:
上述代码我已完成测试, RouteDecorator implements class 未开放代码
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717707017
@tristaZero thanks for your response.
in my situation,i just need database-sharding and no table-sharding. now batch insert 4000 rows will take 32s, and What can I do to improve the speed？
sql like this: insert into pressure(userid,orderid) values(?, ?),(?,?),...
sharding-jdbc 4.0.0-RC2, MYSQL 5.7.12-log
_the following is some of my log while batch insert 4000 rows:
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : HintShardingAlgorithm.doSharding
2020-10-28 13:19:37.759 INFO 9960 --- [nio-9002-exec-1] c.h.s.test.config.ShardingJDBCConfig : 数据库路由完毕.
2020-10-28 13:19:38.553 DEBUG 9960 --- [nio-9002-exec-1] c.h.s.test.mapper.MapperTest.insertData : <== Updates: 4000
2020-10-28 13:19:38.562 INFO 9960 --- [nio-9002-exec-1] c.h.sharding.test.controller.controller : sql: 30.554s_
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-718366951
@gongguoyu So a later version solved your issue, right?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gongguoyu edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
gongguoyu edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-717707017
@tristaZero thanks for your response.
in my situation,i just need database-sharding ,no table-sharding, and all sql will run in the selected database. now batch insert 4000 rows will take 32s, and What can I do to improve the speed？
sql like this: insert into pressure(userid,orderid) values(?, ?),(?,?),...
sharding-jdbc 4.0.0-RC2, MYSQL 5.7.12-log
en.................
it's seems ok in 4.1.1
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 commented on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 commented on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-673779863
> @johnny2002 Catch your idea. I think it is reasonable, Please give the community some time to discuss this feature. I will give the feedback here ASAP.
> Thanks for your feedback. :-)
Perfect!
Suggestted solution:
hintManager.setDatabaseShardingValue(Comparable<?> value, String logicTable,). if the logicTable is present, then use it instead of parsing SQL.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.statement.dal.DALStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private static final SQLStatement DEFAULT_SQL_STATEMENT = new DALStatement() {
@Override
public int getParameterCount() {
return 0;
}
};
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public SQLParserEngine getParserEngine() {
return parserEngine;
}
public ShardingSphereMetaData getMetaData() {
return metaData;
}
public String getSql() {
return sql;
}
public RouteContext getResult() {
return result;
}
public List<Object> getParameters() {
return parameters;
}
public boolean isUseCache() {
return useCache;
}
public SQLStatement getSqlStatement() {
return sqlStatement;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public void createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement == null ? DEFAULT_SQL_STATEMENT : sqlStatement);
result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-669128677
> @johnny2002 The reason is that we can not know whether this `logicTableName` exists at your input SQL if there is no SQL parsing.
>
> > > sql parsing is a necessary phase.
> >
> >
> > If you know the logicTableName, why do you need to parse SQL?
> > So suggestted solution:
> > HintManager hintManager = HintManager.getInstance();
> > hintManager.setDatabaseShardingValue("0");
> > hintManager.setLogicTableName("tab_user");
> > Then skip sql parsing
Dear,
Since I already told you the logicTableName, you should treat it as exists, even sometimes, the logicTableName not exists in SQL, but this is my intention. e.g. I want to use the sharding rules of table-a to shard table-b, this should be allowed.
Hope you can double think my suggestion. This feature should broadly promote the usage of Sharding-jdbc.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
shardingsphere 4.1.1
-----------------------------------------------------------------------------------
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.statement.dal.DALStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private static final SQLStatement DEFAULT_SQL_STATEMENT = new DALStatement() {
@Override
public int getParameterCount() {
return 0;
}
};
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public SQLParserEngine getParserEngine() {
return parserEngine;
}
public ShardingSphereMetaData getMetaData() {
return metaData;
}
public String getSql() {
return sql;
}
public RouteContext getResult() {
return result;
}
public List<Object> getParameters() {
return parameters;
}
public boolean isUseCache() {
return useCache;
}
public SQLStatement getSqlStatement() {
return sqlStatement;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public void createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement == null ? DEFAULT_SQL_STATEMENT : sqlStatement);
result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSetMetaData
```java
public final class ShardingResultSetMetaData extends WrapperAdapter implements ResultSetMetaData {
...
@Override
public int getColumnCount() throws SQLException {
//非SelectStatementContext 返回resultSetMetaData.getColumnCount(); 大小
//Non selectstatementcontext return resultSetMetaData.getColumnCount (); size
return sqlStatementContext instanceof SelectStatementContext ? ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getExpandProjections().size() : resultSetMetaData.getColumnCount();
}
...
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] unjie edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
unjie edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-683634478
org.apache.shardingsphere.underlying.route.decorator.RouteDecorator:
------------------------------------------------------------------------------------
```java
/**
* Route decorator.
*
* @param <T> type of rule
*/
public interface RouteDecorator<T extends BaseRule> extends OrderAware<Class<T>> {
/**
* Decorate route context.
*
* @param routeContext route context
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
RouteContext decorate(RouteContext routeContext, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties);
/**
* Decorate route context.
*
* @param operation route sql
* @param metaData meta data of ShardingSphere
* @param rule rule
* @param properties configuration properties
* @return decorated route context
*/
default RouteContext decorate(DataNodeRouter.RouteContextOperation operation, ShardingSphereMetaData metaData, T rule, ConfigurationProperties properties) {
operation.parseSQL();
return decorate(operation.get(), metaData, rule, properties);
}
}
```
org.apache.shardingsphere.underlying.route.DataNodeRouter:
-------------------------------------------------------------------------
```java
package org.apache.shardingsphere.underlying.route;
import org.apache.shardingsphere.sql.parser.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.sql.parser.binder.statement.CommonSQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.statement.dal.DALStatement;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.underlying.common.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.underlying.common.rule.BaseRule;
import org.apache.shardingsphere.underlying.route.context.RouteContext;
import org.apache.shardingsphere.underlying.route.context.RouteResult;
import org.apache.shardingsphere.underlying.route.decorator.RouteDecorator;
import org.apache.shardingsphere.underlying.route.hook.SPIRoutingHook;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
/**
* Data node router.
*
* @author unjie
*/
public final class DataNodeRouter {
private static final SQLStatement DEFAULT_SQL_STATEMENT = new DALStatement() {
@Override
public int getParameterCount() {
return 0;
}
};
private final ShardingSphereMetaData metaData;
private final ConfigurationProperties properties;
private final SQLParserEngine parserEngine;
private final Map<BaseRule, RouteDecorator> decorators = new LinkedHashMap();
private SPIRoutingHook routingHook = new SPIRoutingHook();
public DataNodeRouter(ShardingSphereMetaData metaData, ConfigurationProperties properties, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.properties = properties;
this.parserEngine = parserEngine;
}
public void registerDecorator(BaseRule rule, RouteDecorator decorator) {
this.decorators.put(rule, decorator);
}
public RouteContext route(String sql, List<Object> parameters, boolean useCache) {
this.routingHook.start(sql);
try {
RouteContext result = this.executeRoute(sql, parameters, useCache);
this.routingHook.finishSuccess(result, this.metaData.getSchema());
return result;
} catch (Exception var5) {
this.routingHook.finishFailure(var5);
throw var5;
}
}
private RouteContext executeRoute(String sql, List<Object> parameters, boolean useCache) {
RouteContextOperation operation = new RouteContextOperation(this.metaData, sql, parameters, useCache, this.parserEngine);
RouteContext result = null;
Entry entry;
for (Iterator var5 = this.decorators.entrySet().iterator(); var5.hasNext(); result = ((RouteDecorator) entry.getValue()).decorate(operation, this.metaData, (BaseRule) entry.getKey(), this.properties)) {
entry = (Entry) var5.next();
}
return result;
}
public class RouteContextOperation {
private SQLParserEngine parserEngine;
private ShardingSphereMetaData metaData;
private String sql;
private RouteContext result = null;
private List<Object> parameters;
private boolean useCache;
private SQLStatement sqlStatement;
public RouteContextOperation(ShardingSphereMetaData metaData, String sql, List<Object> parameters, boolean useCache, SQLParserEngine parserEngine) {
this.metaData = metaData;
this.sql = sql;
this.parameters = parameters;
this.useCache = useCache;
this.parserEngine = parserEngine;
}
public SQLParserEngine getParserEngine() {
return parserEngine;
}
public ShardingSphereMetaData getMetaData() {
return metaData;
}
public String getSql() {
return sql;
}
public RouteContext getResult() {
return result;
}
public List<Object> getParameters() {
return parameters;
}
public boolean isUseCache() {
return useCache;
}
public SQLStatement getSqlStatement() {
return sqlStatement;
}
public void parseSQL() {
if (sqlStatement == null) {
sqlStatement = parserEngine.parse(sql, useCache);
}
}
public RouteContext get() {
if (result == null) {
createRouteContext();
}
return result;
}
public void createRouteContext() {
try {
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(this.metaData.getSchema(), sql, parameters, sqlStatement == null ? DEFAULT_SQL_STATEMENT : sqlStatement);
result = new RouteContext(sqlStatementContext, parameters, new RouteResult());
} catch (IndexOutOfBoundsException var6) {
result = new RouteContext(new CommonSQLStatementContext(sqlStatement), parameters, new RouteResult());
}
}
}
}
```
org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSetMetaData
```java
public final class ShardingResultSetMetaData extends WrapperAdapter implements ResultSetMetaData {
...
@Override
public int getColumnCount() throws SQLException {
//非SelectStatementContext 返回resultSetMetaData.getColumnCount(); 大小
//Non selectstatementcontext return resultSetMetaData.getColumnCount (); size
return sqlStatementContext instanceof SelectStatementContext ? ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getExpandProjections().size() : resultSetMetaData.getColumnCount();
}
...
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] johnny2002 edited a comment on issue #6458: How to skip sql parse and rewrite phase
Posted by GitBox <gi...@apache.org>.
johnny2002 edited a comment on issue #6458:
URL: https://github.com/apache/shardingsphere/issues/6458#issuecomment-671215272
> @johnny2002 Got it. Thanks for your further clarification.
> Just to confirm, you have the `sharding tables` and these `hint tables` meanwhile, don't you?
> Your particular case made me feel you only use shardingJDBC for routing the SQL to specific DB of the DB cluster. If so, why do not you use multiple independent MySQL instances instead?
>
> I just wanna get other advantages of your suggestion for `hintManger` apart from skipping parsing and rewriting.
other advantages has been stated on your official document already:
“Users can use hintManager.setDatabaseShardingValue to add shardings in hint route to some certain sharding database without sharding tables.**After that, SQL parse and rewrite phase will be skipped and the _overall enforcement efficiency can be enhanced_**”, In fact, advantages is far from that, most importantly, complex SQLs usaully causes Sharding-jdbc parsing SQL exception.
redarding "why do not you use multiple independent MySQL instances instead", in one application, since I have already configured one Shardingsphere datasource, I don't want to repeat configure for several datasources. Otherwise, I need to implement another datasource route program.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org