Search in sources :

Example 56 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class DruidMycatRouteStrategy method routeNormalSqlWithAST.

@Override
public RouteResultset routeNormalSqlWithAST(SchemaConfig schema, String stmt, RouteResultset rrs, String charset, LayerCachePool cachePool, int sqlType, ServerConnection sc) throws SQLNonTransientException {
    /**
     *  只有mysql时只支持mysql语法
     */
    SQLStatementParser parser = null;
    if (schema.isNeedSupportMultiDBType()) {
        parser = new MycatStatementParser(stmt);
    } else {
        parser = new MySqlStatementParser(stmt);
    }
    MycatSchemaStatVisitor visitor = null;
    SQLStatement statement;
    /**
     * 解析出现问题统一抛SQL语法错误
     */
    try {
        statement = parser.parseStatement();
        visitor = new MycatSchemaStatVisitor();
    } catch (Exception t) {
        LOGGER.error("DruidMycatRouteStrategyError", t);
        throw new SQLSyntaxErrorException(t);
    }
    /**
     * 检验unsupported statement
     */
    checkUnSupportedStatement(statement);
    DruidParser druidParser = DruidParserFactory.create(schema, statement, visitor);
    druidParser.parser(schema, rrs, statement, stmt, cachePool, visitor);
    DruidShardingParseInfo ctx = druidParser.getCtx();
    rrs.setTables(ctx.getTables());
    if (visitor.isSubqueryRelationOr()) {
        String err = "In subQuery,the or condition is not supported.";
        LOGGER.error(err);
        throw new SQLSyntaxErrorException(err);
    }
    /* 按照以下情况路由
			1.2.1 可以直接路由.
       		1.2.2 两个表夸库join的sql.调用calat
       		1.2.3 需要先执行subquery 的sql.把subquery拆分出来.获取结果后,与outerquery
		 */
    // add huangyiming 分片规则不一样的且表中带查询条件的则走Catlet
    List<String> tables = ctx.getTables();
    SchemaConfig schemaConf = MycatServer.getInstance().getConfig().getSchemas().get(schema.getName());
    int index = 0;
    RuleConfig firstRule = null;
    boolean directRoute = true;
    Set<String> firstDataNodes = new HashSet<String>();
    Map<String, TableConfig> tconfigs = schemaConf == null ? null : schemaConf.getTables();
    Map<String, RuleConfig> rulemap = new HashMap<>();
    if (tconfigs != null) {
        for (String tableName : tables) {
            TableConfig tc = tconfigs.get(tableName);
            if (tc == null) {
                // add 别名中取
                Map<String, String> tableAliasMap = ctx.getTableAliasMap();
                if (tableAliasMap != null && tableAliasMap.get(tableName) != null) {
                    tc = schemaConf.getTables().get(tableAliasMap.get(tableName));
                }
            }
            if (index == 0) {
                if (tc != null) {
                    firstRule = tc.getRule();
                    // 没有指定分片规则时,不做处理
                    if (firstRule == null) {
                        continue;
                    }
                    firstDataNodes.addAll(tc.getDataNodes());
                    rulemap.put(tc.getName(), firstRule);
                }
            } else {
                if (tc != null) {
                    // ER关系表的时候是可能存在字表中没有tablerule的情况,所以加上判断
                    RuleConfig ruleCfg = tc.getRule();
                    if (ruleCfg == null) {
                        // 没有指定分片规则时,不做处理
                        continue;
                    }
                    Set<String> dataNodes = new HashSet<String>();
                    dataNodes.addAll(tc.getDataNodes());
                    rulemap.put(tc.getName(), ruleCfg);
                    // 如果匹配规则不相同或者分片的datanode不相同则需要走子查询处理
                    if (firstRule != null && ((ruleCfg != null && !ruleCfg.getRuleAlgorithm().equals(firstRule.getRuleAlgorithm())) || (!dataNodes.equals(firstDataNodes)))) {
                        directRoute = false;
                        break;
                    }
                }
            }
            index++;
        }
    }
    RouteResultset rrsResult = rrs;
    if (directRoute) {
        // 直接路由
        if (!RouterUtil.isAllGlobalTable(ctx, schemaConf)) {
            if (rulemap.size() > 1 && !checkRuleField(rulemap, visitor)) {
                String err = "In case of slice table,there is no rule field in the relationship condition!";
                LOGGER.error(err);
                throw new SQLSyntaxErrorException(err);
            }
        }
        rrsResult = directRoute(rrs, ctx, schema, druidParser, statement, cachePool);
    } else {
        int subQuerySize = visitor.getSubQuerys().size();
        if (subQuerySize == 0 && ctx.getTables().size() == 2) {
            // 两表关联,考虑使用catlet
            if (!visitor.getRelationships().isEmpty()) {
                rrs.setCacheAble(false);
                rrs.setFinishedRoute(true);
                rrsResult = catletRoute(schema, ctx.getSql(), charset, sc);
            } else {
                rrsResult = directRoute(rrs, ctx, schema, druidParser, statement, cachePool);
            }
        } else if (subQuerySize == 1) {
            // 只涉及一张表的子查询,使用  MiddlerResultHandler 获取中间结果后,改写原有 sql 继续执行 TODO 后期可能会考虑多个子查询的情况.
            SQLSelect sqlselect = visitor.getSubQuerys().iterator().next();
            if (!visitor.getRelationships().isEmpty()) {
                // 当 inner query  和 outer  query  有关联条件时,暂不支持
                String err = "In case of slice table,sql have different rules,the relationship condition is not supported.";
                LOGGER.error(err);
                throw new SQLSyntaxErrorException(err);
            } else {
                SQLSelectQuery sqlSelectQuery = sqlselect.getQuery();
                if (((MySqlSelectQueryBlock) sqlSelectQuery).getFrom() instanceof SQLExprTableSource) {
                    rrs.setCacheAble(false);
                    rrs.setFinishedRoute(true);
                    rrsResult = middlerResultRoute(schema, charset, sqlselect, sqlType, statement, sc);
                }
            }
        } else if (subQuerySize >= 2) {
            String err = "In case of slice table,sql has different rules,currently only one subQuery is supported.";
            LOGGER.error(err);
            throw new SQLSyntaxErrorException(err);
        }
    }
    return rrsResult;
}
Also used : SchemaConfig(io.mycat.config.model.SchemaConfig) MycatStatementParser(io.mycat.route.parser.druid.MycatStatementParser) MycatSchemaStatVisitor(io.mycat.route.parser.druid.MycatSchemaStatVisitor) HashMap(java.util.HashMap) SQLSyntaxErrorException(java.sql.SQLSyntaxErrorException) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) TableConfig(io.mycat.config.model.TableConfig) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) HashSet(java.util.HashSet) RouteResultset(io.mycat.route.RouteResultset) DruidShardingParseInfo(io.mycat.route.parser.druid.DruidShardingParseInfo) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLSelect(com.alibaba.druid.sql.ast.statement.SQLSelect) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLNonTransientException(java.sql.SQLNonTransientException) SQLSyntaxErrorException(java.sql.SQLSyntaxErrorException) DruidParser(io.mycat.route.parser.druid.DruidParser) SQLExprTableSource(com.alibaba.druid.sql.ast.statement.SQLExprTableSource) RuleConfig(io.mycat.config.model.rule.RuleConfig)

Example 57 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class HintSQLHandler method parseProcedure.

private Procedure parseProcedure(String sql, Map hintMap) {
    boolean fields = hintMap.containsKey("list_fields");
    boolean isResultList = hintMap != null && ("list".equals(hintMap.get("result_type")) || fields);
    Procedure procedure = new Procedure();
    procedure.setOriginSql(sql);
    procedure.setResultList(isResultList);
    List<String> sqls = Splitter.on(";").trimResults().splitToList(sql);
    Set<String> outSet = new HashSet<>();
    for (int i = sqls.size() - 1; i >= 0; i--) {
        String s = sqls.get(i);
        if (Strings.isNullOrEmpty(s)) {
            continue;
        }
        SQLStatementParser parser = new MySqlStatementParser(s);
        SQLStatement statement = parser.parseStatement();
        if (statement instanceof SQLSelectStatement) {
            MySqlSelectQueryBlock selectQuery = (MySqlSelectQueryBlock) ((SQLSelectStatement) statement).getSelect().getQuery();
            if (selectQuery != null) {
                List<SQLSelectItem> selectItems = selectQuery.getSelectList();
                for (SQLSelectItem selectItem : selectItems) {
                    String select = selectItem.toString();
                    outSet.add(select);
                    procedure.getSelectColumns().add(select);
                }
            }
            procedure.setSelectSql(s);
        } else if (statement instanceof SQLCallStatement) {
            SQLCallStatement sqlCallStatement = (SQLCallStatement) statement;
            procedure.setName(sqlCallStatement.getProcedureName().getSimpleName());
            List<SQLExpr> paramterList = sqlCallStatement.getParameters();
            for (int i1 = 0; i1 < paramterList.size(); i1++) {
                SQLExpr sqlExpr = paramterList.get(i1);
                String pName = sqlExpr.toString();
                String pType = outSet.contains(pName) ? ProcedureParameter.OUT : ProcedureParameter.IN;
                ProcedureParameter parameter = new ProcedureParameter();
                parameter.setIndex(i1 + 1);
                parameter.setName(pName);
                parameter.setParameterType(pType);
                if (pName.startsWith("@")) {
                    procedure.getParamterMap().put(pName, parameter);
                } else {
                    procedure.getParamterMap().put(String.valueOf(i1 + 1), parameter);
                }
            }
            procedure.setCallSql(s);
        } else if (statement instanceof SQLSetStatement) {
            procedure.setSetSql(s);
            SQLSetStatement setStatement = (SQLSetStatement) statement;
            List<SQLAssignItem> sets = setStatement.getItems();
            for (SQLAssignItem set : sets) {
                String name = set.getTarget().toString();
                SQLExpr value = set.getValue();
                ProcedureParameter parameter = procedure.getParamterMap().get(name);
                if (parameter != null) {
                    if (value instanceof SQLIntegerExpr) {
                        parameter.setValue(((SQLIntegerExpr) value).getNumber());
                        parameter.setJdbcType(Types.INTEGER);
                    } else if (value instanceof SQLNumberExpr) {
                        parameter.setValue(((SQLNumberExpr) value).getNumber());
                        parameter.setJdbcType(Types.NUMERIC);
                    } else if (value instanceof SQLTextLiteralExpr) {
                        parameter.setValue(((SQLTextLiteralExpr) value).getText());
                        parameter.setJdbcType(Types.VARCHAR);
                    } else if (value instanceof SQLValuableExpr) {
                        parameter.setValue(((SQLValuableExpr) value).getValue());
                        parameter.setJdbcType(Types.VARCHAR);
                    }
                }
            }
        }
    }
    if (fields) {
        String list_fields = (String) hintMap.get("list_fields");
        List<String> listFields = Splitter.on(",").trimResults().splitToList(list_fields);
        for (String field : listFields) {
            if (!procedure.getParamterMap().containsKey(field)) {
                ProcedureParameter parameter = new ProcedureParameter();
                parameter.setParameterType(ProcedureParameter.OUT);
                parameter.setName(field);
                parameter.setJdbcType(-10);
                parameter.setIndex(procedure.getParamterMap().size() + 1);
                procedure.getParamterMap().put(field, parameter);
            }
        }
        procedure.getListFields().addAll(listFields);
    }
    return procedure;
}
Also used : SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SQLValuableExpr(com.alibaba.druid.sql.ast.expr.SQLValuableExpr) SQLIntegerExpr(com.alibaba.druid.sql.ast.expr.SQLIntegerExpr) List(java.util.List) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) HashSet(java.util.HashSet) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLNumberExpr(com.alibaba.druid.sql.ast.expr.SQLNumberExpr) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) SQLTextLiteralExpr(com.alibaba.druid.sql.ast.expr.SQLTextLiteralExpr)

Example 58 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class InSubQueryResultHandler method dohandler.

@Override
public String dohandler(SQLStatement statement, SQLSelect sqlselect, SQLObject parent, List param) {
    SQLExprImpl inlistExpr = null;
    if (null == param || param.isEmpty()) {
        inlistExpr = new SQLNullExpr();
    } else {
        inlistExpr = new SQLInListExpr();
        ((SQLInListExpr) inlistExpr).setTargetList(param);
        ((SQLInListExpr) inlistExpr).setExpr(((SQLInSubQueryExpr) parent).getExpr());
        ((SQLInListExpr) inlistExpr).setNot(((SQLInSubQueryExpr) parent).isNot());
        ((SQLInListExpr) inlistExpr).setParent(sqlselect.getParent());
    }
    if (parent.getParent() instanceof MySqlSelectQueryBlock) {
        ((MySqlSelectQueryBlock) parent.getParent()).setWhere(inlistExpr);
    } else if (parent.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) parent.getParent());
        if (pp.getLeft().equals(parent)) {
            pp.setLeft(inlistExpr);
        } else if (pp.getRight().equals(parent)) {
            pp.setRight(inlistExpr);
        }
    }
    return statement.toString();
}
Also used : SQLInListExpr(com.alibaba.druid.sql.ast.expr.SQLInListExpr) SQLBinaryOpExpr(com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr) SQLExprImpl(com.alibaba.druid.sql.ast.SQLExprImpl) SQLNullExpr(com.alibaba.druid.sql.ast.expr.SQLNullExpr) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)

Example 59 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class SQLExistsResultHandler method dohandler.

@Override
public String dohandler(SQLStatement statement, SQLSelect sqlselect, SQLObject parent, List param) {
    SQLExpr se = null;
    if (param == null || param.isEmpty()) {
        se = new SQLNullExpr();
    } else {
        se = (SQLExpr) param.get(0);
    }
    if (parent.getParent() instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock msqb = (MySqlSelectQueryBlock) parent.getParent();
        msqb.setWhere(se);
    } else if (parent.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr sbqe = (SQLBinaryOpExpr) parent.getParent();
        if (sbqe.getLeft().equals(parent)) {
            sbqe.setLeft(se);
        } else {
            sbqe.setRight(se);
        }
    }
    return statement.toString();
}
Also used : SQLBinaryOpExpr(com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr) SQLNullExpr(com.alibaba.druid.sql.ast.expr.SQLNullExpr) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr)

Example 60 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class MongoSQLParser method query.

public MongoData query() throws MongoSQLException {
    if (!(statement instanceof SQLSelectStatement)) {
        // return null;
        throw new IllegalArgumentException("not a query sql statement");
    }
    MongoData mongo = new MongoData();
    DBCursor c = null;
    SQLSelectStatement selectStmt = (SQLSelectStatement) statement;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    int icount = 0;
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        BasicDBObject fields = new BasicDBObject();
        // 显示的字段
        for (SQLSelectItem item : mysqlSelectQuery.getSelectList()) {
            // System.out.println(item.toString());
            if (!(item.getExpr() instanceof SQLAllColumnExpr)) {
                if (item.getExpr() instanceof SQLAggregateExpr) {
                    SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
                    if (expr.getMethodName().equals("COUNT")) {
                        icount = 1;
                        mongo.setField(getExprFieldName(expr), Types.BIGINT);
                    }
                    fields.put(getExprFieldName(expr), Integer.valueOf(1));
                } else {
                    fields.put(getFieldName(item), Integer.valueOf(1));
                }
            }
        }
        // 表名
        SQLTableSource table = mysqlSelectQuery.getFrom();
        DBCollection coll = this._db.getCollection(table.toString());
        mongo.setTable(table.toString());
        SQLExpr expr = mysqlSelectQuery.getWhere();
        DBObject query = parserWhere(expr);
        // System.out.println(query);
        SQLSelectGroupByClause groupby = mysqlSelectQuery.getGroupBy();
        BasicDBObject gbkey = new BasicDBObject();
        if (groupby != null) {
            for (SQLExpr gbexpr : groupby.getItems()) {
                if (gbexpr instanceof SQLIdentifierExpr) {
                    String name = ((SQLIdentifierExpr) gbexpr).getName();
                    gbkey.put(name, Integer.valueOf(1));
                }
            }
            icount = 2;
        }
        int limitoff = 0;
        int limitnum = 0;
        if (mysqlSelectQuery.getLimit() != null) {
            limitoff = getSQLExprToInt(mysqlSelectQuery.getLimit().getOffset());
            limitnum = getSQLExprToInt(mysqlSelectQuery.getLimit().getRowCount());
        }
        if (icount == 1) {
            mongo.setCount(coll.count(query));
        } else if (icount == 2) {
            BasicDBObject initial = new BasicDBObject();
            initial.put("num", 0);
            String reduce = "function (obj, prev) { " + "  prev.num++}";
            mongo.setGrouyBy(coll.group(gbkey, query, initial, reduce));
        } else {
            if ((limitoff > 0) || (limitnum > 0)) {
                c = coll.find(query, fields).skip(limitoff).limit(limitnum);
            } else {
                c = coll.find(query, fields);
            }
            SQLOrderBy orderby = mysqlSelectQuery.getOrderBy();
            if (orderby != null) {
                BasicDBObject order = new BasicDBObject();
                for (int i = 0; i < orderby.getItems().size(); i++) {
                    SQLSelectOrderByItem orderitem = orderby.getItems().get(i);
                    order.put(orderitem.getExpr().toString(), Integer.valueOf(getSQLExprToAsc(orderitem.getType())));
                }
                c.sort(order);
            // System.out.println(order);
            }
        }
        mongo.setCursor(c);
    }
    return mongo;
}
Also used : MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) BasicDBObject(com.mongodb.BasicDBObject) DBObject(com.mongodb.DBObject) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) BasicDBObject(com.mongodb.BasicDBObject) DBCollection(com.mongodb.DBCollection) DBCursor(com.mongodb.DBCursor)

Aggregations

MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)82 SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)41 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)34 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)31 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)28 SQLSelect (com.alibaba.druid.sql.ast.statement.SQLSelect)25 MySqlSchemaStatVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor)23 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)19 SQLBinaryOpExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr)13 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)11 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)10 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)9 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)8 SQLQueryExpr (com.alibaba.druid.sql.ast.expr.SQLQueryExpr)8 HashMap (java.util.HashMap)8 Item (com.actiontech.dble.plan.common.item.Item)7 SQLNonTransientException (java.sql.SQLNonTransientException)7 Test (org.junit.Test)7 SQLInSubQueryExpr (com.alibaba.druid.sql.ast.expr.SQLInSubQueryExpr)6 SQLNullExpr (com.alibaba.druid.sql.ast.expr.SQLNullExpr)6