Examples with MySqlUpdateStatement com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement used on opensource projects

Example 1 with MySqlUpdateStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement 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 2 with MySqlUpdateStatement

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

the class DruidUpdateParserTest method throwExceptionParse.

public void throwExceptionParse(String sql, boolean throwException) throws NoSuchMethodException {
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement sqlStatement = statementList.get(0);
    MySqlUpdateStatement update = (MySqlUpdateStatement) sqlStatement;
    SchemaConfig schemaConfig = mock(SchemaConfig.class);
    Map<String, TableConfig> tables = mock(Map.class);
    TableConfig tableConfig = mock(TableConfig.class);
    String tableName = "hotnews";
    when((schemaConfig).getTables()).thenReturn(tables);
    when(tables.get(tableName)).thenReturn(tableConfig);
    when(tableConfig.getParentTC()).thenReturn(null);
    RouteResultset routeResultset = new RouteResultset(sql, 11);
    Class c = DruidUpdateParser.class;
    Method method = c.getDeclaredMethod("confirmShardColumnNotUpdated", new Class[] { SQLUpdateStatement.class, SchemaConfig.class, String.class, String.class, String.class, RouteResultset.class });
    method.setAccessible(true);
    try {
        method.invoke(c.newInstance(), update, schemaConfig, tableName, "ID", "", routeResultset);
        if (throwException) {
            System.out.println("未抛异常，解析通过则不对！");
            Assert.assertTrue(false);
        } else {
            System.out.println("未抛异常，解析通过，此情况分片字段可能在update语句中但是实际不会被更新");
            Assert.assertTrue(true);
        }
    } catch (Exception e) {
        if (throwException) {
            System.out.println(e.getCause().getClass());
            Assert.assertTrue(e.getCause() instanceof SQLNonTransientException);
            System.out.println("抛异常原因为SQLNonTransientException则正确");
        } else {
            System.out.println("抛异常，需要检查");
            Assert.assertTrue(false);
        }
    }
}

Example 3 with MySqlUpdateStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement in project druid by alibaba.

the class WallVisitorUtils method checkUpdate.

public static void checkUpdate(WallVisitor visitor, SQLUpdateStatement x) {
    checkReadOnly(visitor, x.getTableSource());
    WallConfig config = visitor.getConfig();
    if (!config.isUpdateAllow()) {
        addViolation(visitor, ErrorCode.UPDATE_NOT_ALLOW, "update not allow", x);
        return;
    }
    SQLExpr where = x.getWhere();
    if (where == null) {
        WallContext context = WallContext.current();
        if (context != null) {
            context.incrementUpdateNoneConditionWarnings();
        }
        if (config.isUpdateWhereNoneCheck()) {
            if (x instanceof MySqlUpdateStatement) {
                MySqlUpdateStatement mysqlUpdate = (MySqlUpdateStatement) x;
                if (mysqlUpdate.getLimit() == null) {
                    addViolation(visitor, ErrorCode.NONE_CONDITION, "update none condition not allow", x);
                    return;
                }
            } else {
                addViolation(visitor, ErrorCode.NONE_CONDITION, "update none condition not allow", x);
                return;
            }
        }
    } else {
        where.setParent(x);
        checkCondition(visitor, where);
        if (Boolean.TRUE == getConditionValue(visitor, where, config.isUpdateWhereAlayTrueCheck())) {
            if (config.isUpdateWhereAlayTrueCheck() && visitor.isSqlEndOfComment() && !isSimpleConstExpr(where)) {
                addViolation(visitor, ErrorCode.ALWAYS_TRUE, "update alway true condition not allow", x);
            }
        }
    }
    checkUpdateForMultiTenant(visitor, x);
}

Example 4 with MySqlUpdateStatement

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

the class DruidUpdateParser method statementParse.

@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) throws SQLNonTransientException {
    //这里限制了update分片表的个数只能有一个
    if (ctx.getTables() != null && getUpdateTableCount() > 1 && !schema.isNoSharding()) {
        String msg = "multi table related update not supported,tables:" + ctx.getTables();
        LOGGER.warn(msg);
        throw new SQLNonTransientException(msg);
    }
    MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;
    String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName().toUpperCase());
    TableConfig tc = schema.getTables().get(tableName);
    if (RouterUtil.isNoSharding(schema, tableName)) {
        //整个schema都不分库或者该表不拆分
        RouterUtil.routeForTableMeta(rrs, schema, tableName, rrs.getStatement());
        rrs.setFinishedRoute(true);
        return;
    }
    String partitionColumn = tc.getPartitionColumn();
    String joinKey = tc.getJoinKey();
    if (tc.isGlobalTable() || (partitionColumn == null && joinKey == null)) {
        //修改全局表 update 受影响的行数
        RouterUtil.routeToMultiNode(false, rrs, tc.getDataNodes(), rrs.getStatement(), tc.isGlobalTable());
        rrs.setFinishedRoute(true);
        return;
    }
    confirmShardColumnNotUpdated(update, schema, tableName, partitionColumn, joinKey, rrs);
    if (schema.getTables().get(tableName).isGlobalTable() && ctx.getRouteCalculateUnit().getTablesAndConditions().size() > 1) {
        throw new SQLNonTransientException("global table is not supported in multi table related update " + tableName);
    }
}

Example 5 with MySqlUpdateStatement

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement in project druid by alibaba.

the class MySqlSelectParser method parseUpdateStatment.

protected MySqlUpdateStatement parseUpdateStatment() {
    MySqlUpdateStatement update = new MySqlUpdateStatement();
    lexer.nextToken();
    if (identifierEquals("LOW_PRIORITY")) {
        lexer.nextToken();
        update.setLowPriority(true);
    }
    if (identifierEquals("IGNORE")) {
        lexer.nextToken();
        update.setIgnore(true);
    }
    if (identifierEquals("COMMIT_ON_SUCCESS")) {
        lexer.nextToken();
        update.setCommitOnSuccess(true);
    }
    if (identifierEquals("ROLLBACK_ON_FAIL")) {
        lexer.nextToken();
        update.setRollBackOnFail(true);
    }
    if (identifierEquals("QUEUE_ON_PK")) {
        lexer.nextToken();
        update.setQueryOnPk(true);
    }
    if (identifierEquals("TARGET_AFFECT_ROW")) {
        lexer.nextToken();
        SQLExpr targetAffectRow = this.exprParser.expr();
        update.setTargetAffectRow(targetAffectRow);
    }
    SQLTableSource updateTableSource = this.exprParser.createSelectParser().parseTableSource();
    update.setTableSource(updateTableSource);
    accept(Token.SET);
    for (; ; ) {
        SQLUpdateSetItem item = this.exprParser.parseUpdateSetItem();
        update.addItem(item);
        if (lexer.token() != Token.COMMA) {
            break;
        }
        lexer.nextToken();
    }
    if (lexer.token() == (Token.WHERE)) {
        lexer.nextToken();
        update.setWhere(this.exprParser.expr());
    }
    update.setOrderBy(this.exprParser.parseOrderBy());
    update.setLimit(this.exprParser.parseLimit());
    return update;
}