Examples with MySqlStatementParser com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser used on opensource projects

Example 6 with MySqlStatementParser

use of com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser in project Mycat-Server by MyCATApache.

the class GlobalTableUtil method convertInsertSQL.

/**
	 * Syntax:
		INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name,...)]
	    [(col_name,...)]
	    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
	    [ ON DUPLICATE KEY UPDATE
	      col_name=expr
	        [, col_name=expr] ... ]
	
		Or:
	
		INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name,...)]
	    SET col_name={expr | DEFAULT}, ...
	    [ ON DUPLICATE KEY UPDATE
	      col_name=expr
	        [, col_name=expr] ... ]
	
		Or:
	
		INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name,...)]
	    [(col_name,...)]
	    SELECT ...
	    [ ON DUPLICATE KEY UPDATE
	      col_name=expr
        [, col_name=expr] ... ]
        mysql> insert user value (33333333,'ddd');
		mysql> insert into user value (333333,'ddd');
		mysql> insert user values (3333,'ddd');
     * insert into user(id,name) valueS(1111,'dig'),
     * (1111,  'dig'), (1111,'dig') ,(1111,'dig');
	 * @param sql
	 * @return
	 */
private static String convertInsertSQL(String sql) {
    try {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement statement = parser.parseStatement();
        MySqlInsertStatement insert = (MySqlInsertStatement) statement;
        String tableName = StringUtil.removeBackquote(insert.getTableName().getSimpleName());
        if (!isGlobalTable(tableName))
            return sql;
        if (!isInnerColExist(tableName))
            return sql;
        if (// insert into tab select 
        insert.getQuery() != null)
            return sql;
        StringBuilder sb = // 指定初始容量可以提高性能
        new StringBuilder(200).append("insert into ").append(tableName);
        List<SQLExpr> columns = insert.getColumns();
        int idx = -1;
        int colSize = -1;
        if (columns == null || columns.size() <= 0) {
            // insert 没有带列名：insert into t values(xxx,xxx)
            String columnsList = tableColumsMap.get(tableName.toUpperCase());
            if (StringUtils.isNotBlank(columnsList)) {
                //"id,name,_mycat_op_time"
                //newSQL = "insert into t(id,name,_mycat_op_time)";
                // 构建一个虚拟newSQL来寻找 内部列的索引位置
                String newSQL = "insert into " + tableName + "(" + columnsList + ")";
                MySqlStatementParser newParser = new MySqlStatementParser(newSQL);
                SQLStatement newStatement = newParser.parseStatement();
                MySqlInsertStatement newInsert = (MySqlInsertStatement) newStatement;
                List<SQLExpr> newColumns = newInsert.getColumns();
                for (int i = 0; i < newColumns.size(); i++) {
                    String column = StringUtil.removeBackquote(newInsert.getColumns().get(i).toString());
                    if (column.equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN))
                        // 找到 内部列的索引位置
                        idx = i;
                }
                colSize = newColumns.size();
                sb.append("(").append(columnsList).append(")");
            } else {
                // tableName 是全局表，但是 tableColumsMap 没有其对应的列list，这种情况不应该存在
                LOGGER.warn("you'd better do not use 'insert into t values(a,b)' Syntax (without column list) on global table, " + "If you do. Then you must make sure inner column '_mycat_op_time' is last column of global table: " + tableName + " in all database. Good luck. ^_^");
            // 我们假定 内部列位于表中所有列的最后，后面我们在values 子句的最后 给他附加上时间戳
            }
        } else {
            // insert 语句带有 列名
            sb.append("(");
            for (int i = 0; i < columns.size(); i++) {
                if (i < columns.size() - 1)
                    sb.append(columns.get(i).toString()).append(",");
                else
                    sb.append(columns.get(i).toString());
                String column = StringUtil.removeBackquote(insert.getColumns().get(i).toString());
                if (column.equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN))
                    idx = i;
            }
            if (idx <= -1)
                sb.append(",").append(GLOBAL_TABLE_MYCAT_COLUMN);
            sb.append(")");
            colSize = columns.size();
        }
        sb.append(" values");
        List<ValuesClause> vcl = insert.getValuesList();
        if (vcl != null && vcl.size() > 1) {
            // 批量insert
            for (int j = 0; j < vcl.size(); j++) {
                if (j != vcl.size() - 1)
                    appendValues(vcl.get(j).getValues(), sb, idx, colSize).append(",");
                else
                    appendValues(vcl.get(j).getValues(), sb, idx, colSize);
            }
        } else {
            // 非批量 insert
            List<SQLExpr> valuse = insert.getValues().getValues();
            appendValues(valuse, sb, idx, colSize);
        }
        List<SQLExpr> dku = insert.getDuplicateKeyUpdate();
        if (dku != null && dku.size() > 0) {
            sb.append(" on duplicate key update ");
            for (int i = 0; i < dku.size(); i++) {
                SQLExpr exp = dku.get(i);
                if (exp != null) {
                    if (i < dku.size() - 1)
                        sb.append(exp.toString()).append(",");
                    else
                        sb.append(exp.toString());
                }
            }
        }
        return sb.toString();
    } catch (Exception e) {
        // 发生异常，则返回原始 sql
        LOGGER.warn(e.getMessage());
        return sql;
    }
}

Example 7 with MySqlStatementParser

use of com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser in project Mycat-Server by MyCATApache.

the class GlobalTableUtil method handleDDLSQL.

/*
	 * Name: 'ALTER TABLE'
		Description:
		Syntax:
		ALTER [IGNORE] TABLE tbl_name
		    [alter_specification [, alter_specification] ...]
		    [partition_options]
	       如果 DDL 修改了表结构，需要重新获得表的列list
	 */
private static String handleDDLSQL(String sql) {
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement statement = parser.parseStatement();
    // druid高版本去掉了 MySqlAlterTableStatement，在其父类 SQLAlterTableStatement 直接支持 mysql alter table 语句
    //			MySqlAlterTableStatement alter = (MySqlAlterTableStatement)statement;
    SQLExprTableSource source = getDDLTableSource(statement);
    if (source == null)
        return sql;
    String tableName = StringUtil.removeBackquote(source.toString());
    if (StringUtils.isNotBlank(tableName))
        tableName = tableName.trim();
    else
        return sql;
    if (!isGlobalTable(tableName))
        return sql;
    //增加对全局表create语句的解析，如果是建表语句创建的是全局表，且表中不含"_mycat_op_time"列
    //则为其增加"_mycat_op_time"列，方便导入数据。
    sql = addColumnIfCreate(sql, statement);
    final String tn = tableName;
    MycatServer.getInstance().getListeningExecutorService().execute(new Runnable() {

        public void run() {
            try {
                // DDL发出之后，等待3秒让DDL分发完成
                TimeUnit.SECONDS.sleep(3);
            } catch (InterruptedException e) {
            }
            // DDL 语句可能会增删 列，所以需要重新获取 全局表的 列list
            reGetColumnsForTable(tn);
        }
    });
    MycatServer.getInstance().getListeningExecutorService().execute(new Runnable() {

        public void run() {
            try {
                // DDL发出之后，等待10分钟再次执行，全局表一般很小，DDL耗时不会超过10分钟
                TimeUnit.MINUTES.sleep(10);
            } catch (InterruptedException e) {
            }
            // DDL 语句可能会增删 列，所以需要重新获取 全局表的 列list
            reGetColumnsForTable(tn);
        }
    });
    return sql;
}

Example 8 with MySqlStatementParser

use of com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser 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 9 with MySqlStatementParser

use of com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser 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 10 with MySqlStatementParser

use of com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser in project Mycat-Server by MyCATApache.

the class DefaultDruidParserTest method getParseTables.

private Object[] getParseTables(String sql) throws Exception {
    SQLStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement statement = parser.parseStatement();
    MycatSchemaStatVisitor visitor = new MycatSchemaStatVisitor();
    LayerCachePool cachePool = mock(LayerCachePool.class);
    RouteResultset rrs = new RouteResultset(sql, ServerParse.SELECT);
    druidParser.parser(schema, rrs, statement, sql, cachePool, visitor);
    DruidShardingParseInfo ctx = druidParser.getCtx();
    return ctx.getTables().toArray();
}