Search in sources :

Example 1 with ColumnRoutePair

use of io.mycat.sqlengine.mpp.ColumnRoutePair in project Mycat_plus by coderczp.

the class DruidSelectParser method changeSql.

/**
 * 改写sql:需要加limit的加上
 */
@Override
public void changeSql(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, LayerCachePool cachePool) throws SQLNonTransientException {
    tryRoute(schema, rrs, cachePool);
    rrs.copyLimitToNodes();
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        int limitStart = 0;
        int limitSize = schema.getDefaultMaxLimit();
        // clear group having
        SQLSelectGroupByClause groupByClause = mysqlSelectQuery.getGroupBy();
        // Modified by winbill, 20160614, do NOT include having clause when routing to multiple nodes
        if (groupByClause != null && groupByClause.getHaving() != null && isRoutMultiNode(schema, rrs)) {
            groupByClause.setHaving(null);
        }
        Map<String, Map<String, Set<ColumnRoutePair>>> allConditions = getAllConditions();
        boolean isNeedAddLimit = isNeedAddLimit(schema, rrs, mysqlSelectQuery, allConditions);
        if (isNeedAddLimit) {
            Limit limit = new Limit();
            limit.setRowCount(new SQLIntegerExpr(limitSize));
            mysqlSelectQuery.setLimit(limit);
            rrs.setLimitSize(limitSize);
            String sql = getSql(rrs, stmt, isNeedAddLimit);
            rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitSize, true);
        }
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit != null && !isNeedAddLimit) {
            SQLIntegerExpr offset = (SQLIntegerExpr) limit.getOffset();
            SQLIntegerExpr count = (SQLIntegerExpr) limit.getRowCount();
            if (offset != null) {
                limitStart = offset.getNumber().intValue();
                rrs.setLimitStart(limitStart);
            }
            if (count != null) {
                limitSize = count.getNumber().intValue();
                rrs.setLimitSize(limitSize);
            }
            if (isNeedChangeLimit(rrs)) {
                Limit changedLimit = new Limit();
                changedLimit.setRowCount(new SQLIntegerExpr(limitStart + limitSize));
                if (offset != null) {
                    if (limitStart < 0) {
                        String msg = "You have an error in your SQL syntax; check the manual that " + "corresponds to your MySQL server version for the right syntax to use near '" + limitStart + "'";
                        throw new SQLNonTransientException(ErrorCode.ER_PARSE_ERROR + " - " + msg);
                    } else {
                        changedLimit.setOffset(new SQLIntegerExpr(0));
                    }
                }
                mysqlSelectQuery.setLimit(changedLimit);
                String sql = getSql(rrs, stmt, isNeedAddLimit);
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitStart + limitSize, true);
                // 设置改写后的sql
                ctx.setSql(sql);
            } else {
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), getCtx().getSql(), rrs.getLimitStart(), rrs.getLimitSize(), true);
            // ctx.setSql(nativeSql);
            }
        }
        if (rrs.isDistTable()) {
            SQLTableSource from = mysqlSelectQuery.getFrom();
            for (RouteResultsetNode node : rrs.getNodes()) {
                SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
                sqlIdentifierExpr.setParent(from);
                sqlIdentifierExpr.setName(node.getSubTableName());
                SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
                from2.setAlias(from.getAlias());
                mysqlSelectQuery.setFrom(from2);
                node.setStatement(stmt.toString());
            }
        }
        rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
    }
}
Also used : SQLSelectGroupByClause(com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause) ColumnRoutePair(io.mycat.sqlengine.mpp.ColumnRoutePair) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLIdentifierExpr(com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLTableSource(com.alibaba.druid.sql.ast.statement.SQLTableSource) SQLNonTransientException(java.sql.SQLNonTransientException) RouteResultsetNode(io.mycat.route.RouteResultsetNode) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) SQLIntegerExpr(com.alibaba.druid.sql.ast.expr.SQLIntegerExpr) SQLExprTableSource(com.alibaba.druid.sql.ast.statement.SQLExprTableSource) Limit(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit) Map(java.util.Map) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap)

Example 2 with ColumnRoutePair

use of io.mycat.sqlengine.mpp.ColumnRoutePair in project Mycat_plus by coderczp.

the class RouteCalculateUnit method addShardingExpr.

public void addShardingExpr(String tableName, String columnName, Object value) {
    Map<String, Set<ColumnRoutePair>> tableColumnsMap = tablesAndConditions.get(tableName);
    if (value == null) {
        // where a=null
        return;
    }
    if (tableColumnsMap == null) {
        tableColumnsMap = new LinkedHashMap<String, Set<ColumnRoutePair>>();
        tablesAndConditions.put(tableName, tableColumnsMap);
    }
    String uperColName = columnName.toUpperCase();
    Set<ColumnRoutePair> columValues = tableColumnsMap.get(uperColName);
    if (columValues == null) {
        columValues = new LinkedHashSet<ColumnRoutePair>();
        tablesAndConditions.get(tableName).put(uperColName, columValues);
    }
    if (value instanceof Object[]) {
        for (Object item : (Object[]) value) {
            if (item == null) {
                continue;
            }
            columValues.add(new ColumnRoutePair(item.toString()));
        }
    } else if (value instanceof RangeValue) {
        columValues.add(new ColumnRoutePair((RangeValue) value));
    } else {
        columValues.add(new ColumnRoutePair(value.toString()));
    }
}
Also used : Set(java.util.Set) LinkedHashSet(java.util.LinkedHashSet) ColumnRoutePair(io.mycat.sqlengine.mpp.ColumnRoutePair) RangeValue(io.mycat.sqlengine.mpp.RangeValue)

Example 3 with ColumnRoutePair

use of io.mycat.sqlengine.mpp.ColumnRoutePair in project Mycat_plus by coderczp.

the class RouterUtil method tryRouteForTables.

/**
 * 多表路由
 */
public static RouteResultset tryRouteForTables(SchemaConfig schema, DruidShardingParseInfo ctx, RouteCalculateUnit routeUnit, RouteResultset rrs, boolean isSelect, LayerCachePool cachePool) throws SQLNonTransientException {
    List<String> tables = ctx.getTables();
    if (schema.isNoSharding() || (tables.size() >= 1 && isNoSharding(schema, tables.get(0)))) {
        return routeToSingleNode(rrs, schema.getDataNode(), ctx.getSql());
    }
    // 只有一个表的
    if (tables.size() == 1) {
        return RouterUtil.tryRouteForOneTable(schema, ctx, routeUnit, tables.get(0), rrs, isSelect, cachePool);
    }
    Set<String> retNodesSet = new HashSet<String>();
    // 每个表对应的路由映射
    Map<String, Set<String>> tablesRouteMap = new HashMap<String, Set<String>>();
    // 分库解析信息不为空
    Map<String, Map<String, Set<ColumnRoutePair>>> tablesAndConditions = routeUnit.getTablesAndConditions();
    if (tablesAndConditions != null && tablesAndConditions.size() > 0) {
        // 为分库表找路由
        RouterUtil.findRouteWithcConditionsForTables(schema, rrs, tablesAndConditions, tablesRouteMap, ctx.getSql(), cachePool, isSelect);
        if (rrs.isFinishedRoute()) {
            return rrs;
        }
    }
    // 为全局表和单库表找路由
    for (String tableName : tables) {
        TableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());
        if (tableConfig == null) {
            // add 如果表读取不到则先将表名从别名中读取转化后再读取
            String alias = ctx.getTableAliasMap().get(tableName);
            if (!StringUtil.isEmpty(alias)) {
                tableConfig = schema.getTables().get(alias.toUpperCase());
            }
            if (tableConfig == null) {
                String msg = "can't find table define in schema " + tableName + " schema:" + schema.getName();
                LOGGER.warn(msg);
                throw new SQLNonTransientException(msg);
            }
        }
        if (tableConfig.isGlobalTable()) {
            // 全局表
            if (tablesRouteMap.get(tableName) == null) {
                tablesRouteMap.put(tableName, new HashSet<String>());
            }
            tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());
        } else if (tablesRouteMap.get(tableName) == null) {
            // 余下的表都是单库表
            tablesRouteMap.put(tableName, new HashSet<String>());
            tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());
        }
    }
    boolean isFirstAdd = true;
    for (Map.Entry<String, Set<String>> entry : tablesRouteMap.entrySet()) {
        if (entry.getValue() == null || entry.getValue().size() == 0) {
            throw new SQLNonTransientException("parent key can't find any valid datanode ");
        } else {
            if (isFirstAdd) {
                retNodesSet.addAll(entry.getValue());
                isFirstAdd = false;
            } else {
                retNodesSet.retainAll(entry.getValue());
                if (retNodesSet.size() == 0) {
                    // 两个表的路由无交集
                    String errMsg = "invalid route in sql, multi tables found but datanode has no intersection " + " sql:" + ctx.getSql();
                    LOGGER.warn(errMsg);
                    throw new SQLNonTransientException(errMsg);
                }
            }
        }
    }
    if (retNodesSet != null && retNodesSet.size() > 0) {
        String tableName = tables.get(0);
        TableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());
        if (tableConfig.isDistTable()) {
            routeToDistTableNode(tableName, schema, rrs, ctx.getSql(), tablesAndConditions, cachePool, isSelect);
            return rrs;
        }
        if (retNodesSet.size() > 1 && isAllGlobalTable(ctx, schema)) {
            // mulit routes ,not cache route result
            if (isSelect) {
                rrs.setCacheAble(false);
                routeToSingleNode(rrs, retNodesSet.iterator().next(), ctx.getSql());
            } else {
                // delete 删除全局表的记录
                routeToMultiNode(isSelect, rrs, retNodesSet, ctx.getSql(), true);
            }
        } else {
            routeToMultiNode(isSelect, rrs, retNodesSet, ctx.getSql());
        }
    }
    return rrs;
}
Also used : Set(java.util.Set) HashSet(java.util.HashSet) LinkedHashSet(java.util.LinkedHashSet) HashMap(java.util.HashMap) ColumnRoutePair(io.mycat.sqlengine.mpp.ColumnRoutePair) SQLNonTransientException(java.sql.SQLNonTransientException) TableConfig(io.mycat.config.model.TableConfig) Map(java.util.Map) HashMap(java.util.HashMap) HashSet(java.util.HashSet) LinkedHashSet(java.util.LinkedHashSet)

Example 4 with ColumnRoutePair

use of io.mycat.sqlengine.mpp.ColumnRoutePair in project Mycat_plus by coderczp.

the class RouterUtil method findRouteWithcConditionsForTables.

/**
 * 处理分库表路由
 */
public static void findRouteWithcConditionsForTables(SchemaConfig schema, RouteResultset rrs, Map<String, Map<String, Set<ColumnRoutePair>>> tablesAndConditions, Map<String, Set<String>> tablesRouteMap, String sql, LayerCachePool cachePool, boolean isSelect) throws SQLNonTransientException {
    // 为分库表找路由
    for (Map.Entry<String, Map<String, Set<ColumnRoutePair>>> entry : tablesAndConditions.entrySet()) {
        String tableName = entry.getKey().toUpperCase();
        TableConfig tableConfig = schema.getTables().get(tableName);
        if (tableConfig == null) {
            String msg = "can't find table define in schema " + tableName + " schema:" + schema.getName();
            LOGGER.warn(msg);
            throw new SQLNonTransientException(msg);
        }
        if (tableConfig.getDistTables() != null && tableConfig.getDistTables().size() > 0) {
            routeToDistTableNode(tableName, schema, rrs, sql, tablesAndConditions, cachePool, isSelect);
        }
        // 全局表或者不分库的表略过(全局表后面再计算)
        if (tableConfig.isGlobalTable() || schema.getTables().get(tableName).getDataNodes().size() == 1) {
            continue;
        } else {
            // 非全局表:分库表、childTable、其他
            Map<String, Set<ColumnRoutePair>> columnsMap = entry.getValue();
            String joinKey = tableConfig.getJoinKey();
            String partionCol = tableConfig.getPartitionColumn();
            String primaryKey = tableConfig.getPrimaryKey();
            boolean isFoundPartitionValue = partionCol != null && entry.getValue().get(partionCol) != null;
            boolean isLoadData = false;
            if (LOGGER.isDebugEnabled() && sql.startsWith(LoadData.loadDataHint) || rrs.isLoadData()) {
                // 由于load data一次会计算很多路由数据,如果输出此日志会极大降低load data的性能
                isLoadData = true;
            }
            if (entry.getValue().get(primaryKey) != null && entry.getValue().size() == 1 && !isLoadData) {
                // 主键查找
                // try by primary key if found in cache
                Set<ColumnRoutePair> primaryKeyPairs = entry.getValue().get(primaryKey);
                if (primaryKeyPairs != null) {
                    if (LOGGER.isDebugEnabled()) {
                        LOGGER.debug("try to find cache by primary key ");
                    }
                    String tableKey = schema.getName() + '_' + tableName;
                    boolean allFound = true;
                    for (ColumnRoutePair pair : primaryKeyPairs) {
                        // 可能id in(1,2,3)多主键
                        String cacheKey = pair.colValue;
                        String dataNode = (String) cachePool.get(tableKey, cacheKey);
                        if (dataNode == null) {
                            allFound = false;
                            continue;
                        } else {
                            if (tablesRouteMap.get(tableName) == null) {
                                tablesRouteMap.put(tableName, new HashSet<String>());
                            }
                            tablesRouteMap.get(tableName).add(dataNode);
                            continue;
                        }
                    }
                    if (!allFound) {
                        // need cache primary key ->datanode relation
                        if (isSelect && tableConfig.getPrimaryKey() != null) {
                            rrs.setPrimaryKey(tableKey + '.' + tableConfig.getPrimaryKey());
                        }
                    } else {
                        // 主键缓存中找到了就执行循环的下一轮
                        continue;
                    }
                }
            }
            if (isFoundPartitionValue) {
                // 分库表
                Set<ColumnRoutePair> partitionValue = columnsMap.get(partionCol);
                if (partitionValue == null || partitionValue.size() == 0) {
                    if (tablesRouteMap.get(tableName) == null) {
                        tablesRouteMap.put(tableName, new HashSet<String>());
                    }
                    tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());
                } else {
                    for (ColumnRoutePair pair : partitionValue) {
                        AbstractPartitionAlgorithm algorithm = tableConfig.getRule().getRuleAlgorithm();
                        if (pair.colValue != null) {
                            Integer nodeIndex = algorithm.calculate(pair.colValue);
                            if (nodeIndex == null) {
                                String msg = "can't find any valid datanode :" + tableConfig.getName() + " -> " + tableConfig.getPartitionColumn() + " -> " + pair.colValue;
                                LOGGER.warn(msg);
                                throw new SQLNonTransientException(msg);
                            }
                            ArrayList<String> dataNodes = tableConfig.getDataNodes();
                            String node;
                            if (nodeIndex >= 0 && nodeIndex < dataNodes.size()) {
                                node = dataNodes.get(nodeIndex);
                            } else {
                                node = null;
                                String msg = "Can't find a valid data node for specified node index :" + tableConfig.getName() + " -> " + tableConfig.getPartitionColumn() + " -> " + pair.colValue + " -> " + "Index : " + nodeIndex;
                                LOGGER.warn(msg);
                                throw new SQLNonTransientException(msg);
                            }
                            if (node != null) {
                                if (tablesRouteMap.get(tableName) == null) {
                                    tablesRouteMap.put(tableName, new HashSet<String>());
                                }
                                if (algorithm instanceof SlotFunction) {
                                    rrs.getDataNodeSlotMap().put(node, ((SlotFunction) algorithm).slotValue());
                                }
                                tablesRouteMap.get(tableName).add(node);
                            }
                        }
                        if (pair.rangeValue != null) {
                            Integer[] nodeIndexs = algorithm.calculateRange(pair.rangeValue.beginValue.toString(), pair.rangeValue.endValue.toString());
                            ArrayList<String> dataNodes = tableConfig.getDataNodes();
                            String node;
                            for (Integer idx : nodeIndexs) {
                                if (idx >= 0 && idx < dataNodes.size()) {
                                    node = dataNodes.get(idx);
                                } else {
                                    String msg = "Can't find valid data node(s) for some of specified node indexes :" + tableConfig.getName() + " -> " + tableConfig.getPartitionColumn();
                                    LOGGER.warn(msg);
                                    throw new SQLNonTransientException(msg);
                                }
                                if (node != null) {
                                    if (tablesRouteMap.get(tableName) == null) {
                                        tablesRouteMap.put(tableName, new HashSet<String>());
                                    }
                                    if (algorithm instanceof SlotFunction) {
                                        rrs.getDataNodeSlotMap().put(node, ((SlotFunction) algorithm).slotValue());
                                    }
                                    tablesRouteMap.get(tableName).add(node);
                                }
                            }
                        }
                    }
                }
            } else if (joinKey != null && columnsMap.get(joinKey) != null && columnsMap.get(joinKey).size() != 0) {
                // childTable  (如果是select 语句的父子表join)之前要找到root table,将childTable移除,只留下root table
                Set<ColumnRoutePair> joinKeyValue = columnsMap.get(joinKey);
                Set<String> dataNodeSet = ruleByJoinValueCalculate(rrs, tableConfig, joinKeyValue);
                if (dataNodeSet.isEmpty()) {
                    throw new SQLNonTransientException("parent key can't find any valid datanode ");
                }
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug("found partion nodes (using parent partion rule directly) for child table to update  " + Arrays.toString(dataNodeSet.toArray()) + " sql :" + sql);
                }
                if (dataNodeSet.size() > 1) {
                    routeToMultiNode(rrs.isCacheAble(), rrs, dataNodeSet, sql);
                    rrs.setFinishedRoute(true);
                    return;
                } else {
                    rrs.setCacheAble(true);
                    routeToSingleNode(rrs, dataNodeSet.iterator().next(), sql);
                    return;
                }
            } else {
                // 没找到拆分字段,该表的所有节点都路由
                if (tablesRouteMap.get(tableName) == null) {
                    tablesRouteMap.put(tableName, new HashSet<String>());
                }
                boolean isSlotFunction = tableConfig.getRule() != null && tableConfig.getRule().getRuleAlgorithm() instanceof SlotFunction;
                if (isSlotFunction) {
                    for (String dn : tableConfig.getDataNodes()) {
                        rrs.getDataNodeSlotMap().put(dn, -1);
                    }
                }
                tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());
            }
        }
    }
}
Also used : AbstractPartitionAlgorithm(io.mycat.route.function.AbstractPartitionAlgorithm) Set(java.util.Set) HashSet(java.util.HashSet) LinkedHashSet(java.util.LinkedHashSet) ColumnRoutePair(io.mycat.sqlengine.mpp.ColumnRoutePair) SlotFunction(io.mycat.route.function.SlotFunction) SQLNonTransientException(java.sql.SQLNonTransientException) TableConfig(io.mycat.config.model.TableConfig) Map(java.util.Map) HashMap(java.util.HashMap) HashSet(java.util.HashSet) LinkedHashSet(java.util.LinkedHashSet)

Example 5 with ColumnRoutePair

use of io.mycat.sqlengine.mpp.ColumnRoutePair in project Mycat-Server by MyCATApache.

the class RouterUtil method routeByERParentKey.

/**
 * 根据 ER分片规则获取路由集合
 *
 * @param stmt            执行的语句
 * @param rrs      		     数据路由集合
 * @param tc	      	     表实体
 * @param joinKeyVal      连接属性
 * @return RouteResultset(数据路由集合)	 *
 * @throws SQLNonTransientException,IllegalShardingColumnValueException
 * @author mycat
 */
public static RouteResultset routeByERParentKey(ServerConnection sc, SchemaConfig schema, int sqlType, String stmt, RouteResultset rrs, TableConfig tc, String joinKeyVal) throws SQLNonTransientException {
    // table's partition key
    if (tc.isSecondLevel() && // 判断是否为二级子表(父表不再有父表)
    tc.getParentTC().getPartitionColumn().equals(tc.getParentKey())) {
        // using
        // parent
        // rule to
        // find
        // datanode
        Set<ColumnRoutePair> parentColVal = new HashSet<ColumnRoutePair>(1);
        ColumnRoutePair pair = new ColumnRoutePair(joinKeyVal);
        parentColVal.add(pair);
        Set<String> dataNodeSet = ruleCalculate(tc.getParentTC(), parentColVal, rrs.getDataNodeSlotMap());
        if (dataNodeSet.isEmpty() || dataNodeSet.size() > 1) {
            throw new SQLNonTransientException("parent key can't find  valid datanode ,expect 1 but found: " + dataNodeSet.size());
        }
        String dn = dataNodeSet.iterator().next();
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("found partion node (using parent partion rule directly) for child table to insert  " + dn + " sql :" + stmt);
        }
        return RouterUtil.routeToSingleNode(rrs, dn, stmt);
    }
    return null;
}
Also used : SQLNonTransientException(java.sql.SQLNonTransientException) ColumnRoutePair(io.mycat.sqlengine.mpp.ColumnRoutePair) HashSet(java.util.HashSet) LinkedHashSet(java.util.LinkedHashSet)

Aggregations

ColumnRoutePair (io.mycat.sqlengine.mpp.ColumnRoutePair)16 LinkedHashSet (java.util.LinkedHashSet)14 SQLNonTransientException (java.sql.SQLNonTransientException)12 HashMap (java.util.HashMap)8 HashSet (java.util.HashSet)8 Map (java.util.Map)8 Set (java.util.Set)8 TableConfig (io.mycat.config.model.TableConfig)6 AbstractPartitionAlgorithm (io.mycat.route.function.AbstractPartitionAlgorithm)6 SlotFunction (io.mycat.route.function.SlotFunction)6 RouteResultsetNode (io.mycat.route.RouteResultsetNode)4 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)2 SQLIntegerExpr (com.alibaba.druid.sql.ast.expr.SQLIntegerExpr)2 SQLExprTableSource (com.alibaba.druid.sql.ast.statement.SQLExprTableSource)2 SQLSelectGroupByClause (com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause)2 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)2 SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)2 SQLTableSource (com.alibaba.druid.sql.ast.statement.SQLTableSource)2 MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)2 Limit (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit)2