Examples with SQLOrderingSpecification com.alibaba.druid.sql.ast.SQLOrderingSpecification used on opensource projects

Example 1 with SQLOrderingSpecification

use of com.alibaba.druid.sql.ast.SQLOrderingSpecification in project Mycat_plus by coderczp.

the class GlobalTableUtil method convertUpdateSQL.

/**
 * UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 *    	SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
 *    	[WHERE where_condition]
 *    	[ORDER BY ...]
 *    	[LIMIT row_count]
 *
 *		Multiple-table syntax:
 *
 *		UPDATE [LOW_PRIORITY] [IGNORE] table_references
 *    	SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
 *    	[WHERE where_condition]
 *
 *    	update user, tuser set user.name='dddd',tuser.pwd='aaa'
 *    	where user.id=2 and tuser.id=0;
 * @param sql update tuser set pwd='aaa', name='digdee' where id=0;
 * @return
 */
public static String convertUpdateSQL(String sql) {
    try {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement stmt = parser.parseStatement();
        MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;
        SQLTableSource ts = update.getTableSource();
        if (ts != null && ts.toString().contains(",")) {
            System.out.println(ts.toString());
            LOGGER.warn("Do not support Multiple-table udpate syntax...");
            return sql;
        }
        String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName());
        if (!isGlobalTable(tableName))
            return sql;
        if (!isInnerColExist(tableName))
            // 没有内部列
            return sql;
        StringBuilder sb = new StringBuilder(150);
        SQLExpr se = update.getWhere();
        // where中有子查询： update company set name='com' where id in (select id from xxx where ...)
        if (se instanceof SQLInSubQueryExpr) {
            // return sql;
            int idx = sql.toUpperCase().indexOf(" SET ") + 5;
            sb.append(sql.substring(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(sql.substring(idx));
            return sb.toString();
        }
        String where = null;
        if (update.getWhere() != null)
            where = update.getWhere().toString();
        SQLOrderBy orderBy = update.getOrderBy();
        Limit limit = update.getLimit();
        sb.append("update ").append(tableName).append(" set ");
        List<SQLUpdateSetItem> items = update.getItems();
        boolean flag = false;
        for (int i = 0; i < items.size(); i++) {
            SQLUpdateSetItem item = items.get(i);
            String col = item.getColumn().toString();
            String val = item.getValue().toString();
            if (StringUtil.removeBackquote(col).equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN)) {
                flag = true;
                sb.append(col).append("=");
                if (i != items.size() - 1)
                    sb.append(operationTimestamp).append(",");
                else
                    sb.append(operationTimestamp);
            } else {
                sb.append(col).append("=");
                if (i != items.size() - 1)
                    sb.append(val).append(",");
                else
                    sb.append(val);
            }
        }
        if (!flag) {
            sb.append(",").append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp);
        }
        sb.append(" where ").append(where);
        if (orderBy != null && orderBy.getItems() != null && orderBy.getItems().size() > 0) {
            sb.append(" order by ");
            for (int i = 0; i < orderBy.getItems().size(); i++) {
                SQLSelectOrderByItem item = orderBy.getItems().get(i);
                SQLOrderingSpecification os = item.getType();
                sb.append(item.getExpr().toString());
                if (i < orderBy.getItems().size() - 1) {
                    if (os != null)
                        sb.append(" ").append(os.toString());
                    sb.append(",");
                } else {
                    if (os != null)
                        sb.append(" ").append(os.toString());
                }
            }
        }
        if (limit != null) {
            // 分为两种情况： limit 10;   limit 10,10;
            sb.append(" limit ");
            if (limit.getOffset() != null)
                sb.append(limit.getOffset().toString()).append(",");
            sb.append(limit.getRowCount().toString());
        }
        return sb.toString();
    } catch (Exception e) {
        LOGGER.warn(e.getMessage());
        return sql;
    }
}

Example 2 with SQLOrderingSpecification

use of com.alibaba.druid.sql.ast.SQLOrderingSpecification in project dble by actiontech.

the class OrderByPusher method getJoinColumnOrders.

/**
 * generatejoinOnFilters,if joinOn's orderBy can change to match implicitOrders ,return true
 *
 * @param joinOnFilters  in
 * @param leftOnOrders   out
 * @param rightOnOrders  out
 * @param implicitOrders in
 * @return
 */
private static boolean getJoinColumnOrders(List<ItemFuncEqual> joinOnFilters, List<Order> leftOnOrders, List<Order> rightOnOrders, List<Order> implicitOrders) {
    List<Item> leftOnSels = new ArrayList<>();
    List<Item> rightOnSels = new ArrayList<>();
    for (ItemFuncEqual bf : joinOnFilters) {
        leftOnSels.add(bf.arguments().get(0));
        rightOnSels.add(bf.arguments().get(1));
    }
    // is on's orderBy can be changed to match implicitOrders
    boolean canMatch = false;
    if (implicitOrders.size() < leftOnSels.size())
        canMatch = false;
    else {
        Map<Integer, SQLOrderingSpecification> foundOnIndexs = new LinkedHashMap<>();
        for (Order orderby : implicitOrders) {
            Item orderSel = orderby.getItem();
            int index = -1;
            if ((index = leftOnSels.indexOf(orderSel)) >= 0) {
                foundOnIndexs.put(index, orderby.getSortOrder());
            } else if ((index = rightOnSels.indexOf(orderSel)) >= 0) {
                foundOnIndexs.put(index, orderby.getSortOrder());
            } else {
                // neither belong to leftOn nor belong to rightOn
                break;
            }
        }
        if (foundOnIndexs.size() == leftOnSels.size()) {
            canMatch = true;
            for (Map.Entry<Integer, SQLOrderingSpecification> entry : foundOnIndexs.entrySet()) {
                int foundOnIndex = entry.getKey();
                SQLOrderingSpecification sortOrder = entry.getValue();
                Item leftOn = leftOnSels.get(foundOnIndex);
                Item rightOn = rightOnSels.get(foundOnIndex);
                // add lefton order
                Order leftOnOrder = new Order(leftOn, sortOrder);
                leftOnOrders.add(leftOnOrder);
                // add righton order
                Order rightOnOrder = new Order(rightOn, sortOrder);
                rightOnOrders.add(rightOnOrder);
            }
            return canMatch;
        }
    }
    // can not match
    for (int index = 0; index < leftOnSels.size(); index++) {
        SQLOrderingSpecification sortOrder = SQLOrderingSpecification.ASC;
        Item leftOn = leftOnSels.get(index);
        Item rightOn = rightOnSels.get(index);
        // add lefton order
        Order leftOnOrder = new Order(leftOn, sortOrder);
        leftOnOrders.add(leftOnOrder);
        // add righton order
        Order rightOnOrder = new Order(rightOn, sortOrder);
        rightOnOrders.add(rightOnOrder);
    }
    return canMatch;
}

Example 3 with SQLOrderingSpecification

use of com.alibaba.druid.sql.ast.SQLOrderingSpecification in project Mycat-Server by MyCATApache.

the class DruidSelectParser method buildOrderByCols.

protected LinkedHashMap<String, Integer> buildOrderByCols(List<SQLSelectOrderByItem> orderByItems, Map<String, String> aliaColumns) {
    LinkedHashMap<String, Integer> map = new LinkedHashMap<String, Integer>();
    for (int i = 0; i < orderByItems.size(); i++) {
        SQLOrderingSpecification type = orderByItems.get(i).getType();
        // orderColumn只记录字段名称,因为返回的结果集是不带表名的。
        SQLExpr expr = orderByItems.get(i).getExpr();
        String col;
        if (expr instanceof SQLName) {
            col = ((SQLName) expr).getSimpleName();
        } else {
            col = expr.toString();
        }
        if (type == null) {
            type = SQLOrderingSpecification.ASC;
        }
        // 此步骤得到的col必须是不带.的，有别名的用别名，无别名的用字段名
        col = getAliaColumn(aliaColumns, col);
        map.put(col, type == SQLOrderingSpecification.ASC ? OrderCol.COL_ORDER_TYPE_ASC : OrderCol.COL_ORDER_TYPE_DESC);
    }
    return map;
}

Example 4 with SQLOrderingSpecification

use of com.alibaba.druid.sql.ast.SQLOrderingSpecification in project Mycat-Server by MyCATApache.

the class GlobalTableUtil method convertUpdateSQL.

/**
 * UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 *    	SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
 *    	[WHERE where_condition]
 *    	[ORDER BY ...]
 *    	[LIMIT row_count]
 *
 *		Multiple-table syntax:
 *
 *		UPDATE [LOW_PRIORITY] [IGNORE] table_references
 *    	SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
 *    	[WHERE where_condition]
 *
 *    	update user, tuser set user.name='dddd',tuser.pwd='aaa'
 *    	where user.id=2 and tuser.id=0;
 * @param sql update tuser set pwd='aaa', name='digdee' where id=0;
 * @return
 */
public static String convertUpdateSQL(String sql) {
    try {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement stmt = parser.parseStatement();
        MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;
        SQLTableSource ts = update.getTableSource();
        if (ts != null && ts.toString().contains(",")) {
            System.out.println(ts.toString());
            LOGGER.warn("Do not support Multiple-table udpate syntax...");
            return sql;
        }
        String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName());
        if (!isGlobalTable(tableName))
            return sql;
        if (!isInnerColExist(tableName))
            // 没有内部列
            return sql;
        StringBuilder sb = new StringBuilder(150);
        SQLExpr se = update.getWhere();
        // where中有子查询： update company set name='com' where id in (select id from xxx where ...)
        if (se instanceof SQLInSubQueryExpr) {
            // return sql;
            int idx = sql.toUpperCase().indexOf(" SET ") + 5;
            sb.append(sql.substring(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(sql.substring(idx));
            return sb.toString();
        }
        String where = null;
        if (update.getWhere() != null)
            where = update.getWhere().toString();
        SQLOrderBy orderBy = update.getOrderBy();
        Limit limit = update.getLimit();
        sb.append("update ").append(tableName).append(" set ");
        List<SQLUpdateSetItem> items = update.getItems();
        boolean flag = false;
        for (int i = 0; i < items.size(); i++) {
            SQLUpdateSetItem item = items.get(i);
            String col = item.getColumn().toString();
            String val = item.getValue().toString();
            if (StringUtil.removeBackquote(col).equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN)) {
                flag = true;
                sb.append(col).append("=");
                if (i != items.size() - 1)
                    sb.append(operationTimestamp).append(",");
                else
                    sb.append(operationTimestamp);
            } else {
                sb.append(col).append("=");
                if (i != items.size() - 1)
                    sb.append(val).append(",");
                else
                    sb.append(val);
            }
        }
        if (!flag) {
            sb.append(",").append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp);
        }
        sb.append(" where ").append(where);
        if (orderBy != null && orderBy.getItems() != null && orderBy.getItems().size() > 0) {
            sb.append(" order by ");
            for (int i = 0; i < orderBy.getItems().size(); i++) {
                SQLSelectOrderByItem item = orderBy.getItems().get(i);
                SQLOrderingSpecification os = item.getType();
                sb.append(item.getExpr().toString());
                if (i < orderBy.getItems().size() - 1) {
                    if (os != null)
                        sb.append(" ").append(os.toString());
                    sb.append(",");
                } else {
                    if (os != null)
                        sb.append(" ").append(os.toString());
                }
            }
        }
        if (limit != null) {
            // 分为两种情况： limit 10;   limit 10,10;
            sb.append(" limit ");
            if (limit.getOffset() != null)
                sb.append(limit.getOffset().toString()).append(",");
            sb.append(limit.getRowCount().toString());
        }
        return sb.toString();
    } catch (Exception e) {
        LOGGER.warn(e.getMessage());
        return sql;
    }
}

Example 5 with SQLOrderingSpecification

use of com.alibaba.druid.sql.ast.SQLOrderingSpecification in project Mycat_plus by coderczp.

the class DruidSelectParser method buildOrderByCols.

protected LinkedHashMap<String, Integer> buildOrderByCols(List<SQLSelectOrderByItem> orderByItems, Map<String, String> aliaColumns) {
    LinkedHashMap<String, Integer> map = new LinkedHashMap<String, Integer>();
    for (int i = 0; i < orderByItems.size(); i++) {
        SQLOrderingSpecification type = orderByItems.get(i).getType();
        // orderColumn只记录字段名称,因为返回的结果集是不带表名的。
        SQLExpr expr = orderByItems.get(i).getExpr();
        String col;
        if (expr instanceof SQLName) {
            col = ((SQLName) expr).getSimpleName();
        } else {
            col = expr.toString();
        }
        if (type == null) {
            type = SQLOrderingSpecification.ASC;
        }
        // 此步骤得到的col必须是不带.的，有别名的用别名，无别名的用字段名
        col = getAliaColumn(aliaColumns, col);
        map.put(col, type == SQLOrderingSpecification.ASC ? OrderCol.COL_ORDER_TYPE_ASC : OrderCol.COL_ORDER_TYPE_DESC);
    }
    return map;
}