package com.alibaba.druid.bvt.sql.mysql.param;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils;
import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
import com.alibaba.druid.util.JdbcConstants;
import junit.framework.TestCase;
import java.util.ArrayList;
import java.util.List;
/**
* Created by wenshao on 16/8/23.
*/
public class MySqlParameterizedOutputVisitorTest_22 extends TestCase {
public void test_for_parameterize() throws Exception {
final String dbType = JdbcConstants.MYSQL;
String sql = "/* 0bba613214845441110397435e/0.4.6.25// */select `f`.`id`,`f`.`biz_id`,`f`.`user_id`,`f`.`file_name`,`f`.`parent_id`,`f`.`length`,`f`.`type`,`f`.`stream_key`,`f`.`biz_status`,`f`.`mark`,`f`.`content_modified`,`f`.`status`,`f`.`gmt_create`,`f`.`gmt_modified`,`f`.`md5`,`f`.`extra_str1`,`f`.`extra_str2`,`f`.`extra_str3`,`f`.`extra_num1`,`f`.`extra_num2`,`f`.`extra_num3`,`f`.`safe`,`f`.`open_status`,`f`.`inner_mark`,`f`.`sys_extra`,`f`.`feature`,`f`.`domain_option`,`f`.`version`,`f`.`reference_type`,`f`.`dentry_type`,`f`.`space_id`,`f`.`extension`,`f`.`creator_id`,`f`.`modifier_id`,`f`.`store_type`,`f`.`link_mark`,`f`.`content_type` from ( select `vfs_dentry_2664`.`id` from `vfs_dentry_2664` FORCE INDEX (idx_gmt) where ((`vfs_dentry_2664`.`extra_str1` = '97d45a25df387b4460e5b4151daeb452') AND (`vfs_dentry_2664`.`biz_id` = 62) AND (`vfs_dentry_2664`.`status` = 0) AND (`vfs_dentry_2664`.`user_id` = '11168360') AND (`vfs_dentry_2664`.`dentry_type` = 1)) limit 0,50 ) `t` join `vfs_dentry_2664` `f` on `t`.`id` = `f`.`id` where ((`t`.`id` = `f`.`id`) AND (`f`.`user_id` = 11168360))";
String psql = ParameterizedOutputVisitorUtils.parameterize(sql, dbType);
assertEquals("SELECT `f`.`id`, `f`.`biz_id`, `f`.`user_id`, `f`.`file_name`, `f`.`parent_id`\n" +
"\t, `f`.`length`, `f`.`type`, `f`.`stream_key`, `f`.`biz_status`, `f`.`mark`\n" +
"\t, `f`.`content_modified`, `f`.`status`, `f`.`gmt_create`, `f`.`gmt_modified`, `f`.`md5`\n" +
"\t, `f`.`extra_str1`, `f`.`extra_str2`, `f`.`extra_str3`, `f`.`extra_num1`, `f`.`extra_num2`\n" +
"\t, `f`.`extra_num3`, `f`.`safe`, `f`.`open_status`, `f`.`inner_mark`, `f`.`sys_extra`\n" +
"\t, `f`.`feature`, `f`.`domain_option`, `f`.`version`, `f`.`reference_type`, `f`.`dentry_type`\n" +
"\t, `f`.`space_id`, `f`.`extension`, `f`.`creator_id`, `f`.`modifier_id`, `f`.`store_type`\n" +
"\t, `f`.`link_mark`, `f`.`content_type`\n" +
"FROM (SELECT vfs_dentry.`id`\n" +
"\tFROM vfs_dentry FORCE INDEX (idx_gmt)\n" +
"\tWHERE vfs_dentry.`extra_str1` = ?\n" +
"\t\tAND vfs_dentry.`biz_id` = ?\n" +
"\t\tAND vfs_dentry.`status` = ?\n" +
"\t\tAND vfs_dentry.`user_id` = ?\n" +
"\t\tAND vfs_dentry.`dentry_type` = ?\n" +
"\tLIMIT ?, ?\n" +
"\t) `t`\n" +
"\tJOIN vfs_dentry `f` ON `t`.`id` = `f`.`id`\n" +
"WHERE `t`.`id` = `f`.`id`\n" +
"\tAND `f`.`user_id` = ?", psql);
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(psql, dbType);
List<SQLStatement> stmtList = parser.parseStatementList();
StringBuilder out = new StringBuilder();
SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
List<Object> parameters = new ArrayList<Object>();
visitor.setParameterized(true);
visitor.setParameterizedMergeInList(true);
visitor.setParameters(parameters);
visitor.setExportTables(true);
/*visitor.setPrettyFormat(false);*/
SQLStatement stmt = stmtList.get(0);
stmt.accept(visitor);
// System.out.println(parameters);
assertEquals(0, parameters.size());
StringBuilder buf = new StringBuilder();
SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
visitor1.addTableMapping("vfs_dentry", "vfs_dentry_001");
visitor1.setParameters(visitor.getParameters());
stmt.accept(visitor1);
assertEquals("SELECT `f`.`id`, `f`.`biz_id`, `f`.`user_id`, `f`.`file_name`, `f`.`parent_id`\n" +
"\t, `f`.`length`, `f`.`type`, `f`.`stream_key`, `f`.`biz_status`, `f`.`mark`\n" +
"\t, `f`.`content_modified`, `f`.`status`, `f`.`gmt_create`, `f`.`gmt_modified`, `f`.`md5`\n" +
"\t, `f`.`extra_str1`, `f`.`extra_str2`, `f`.`extra_str3`, `f`.`extra_num1`, `f`.`extra_num2`\n" +
"\t, `f`.`extra_num3`, `f`.`safe`, `f`.`open_status`, `f`.`inner_mark`, `f`.`sys_extra`\n" +
"\t, `f`.`feature`, `f`.`domain_option`, `f`.`version`, `f`.`reference_type`, `f`.`dentry_type`\n" +
"\t, `f`.`space_id`, `f`.`extension`, `f`.`creator_id`, `f`.`modifier_id`, `f`.`store_type`\n" +
"\t, `f`.`link_mark`, `f`.`content_type`\n" +
"FROM (SELECT vfs_dentry_001.`id`\n" +
"\tFROM vfs_dentry_001 FORCE INDEX (idx_gmt)\n" +
"\tWHERE vfs_dentry_001.`extra_str1` = ?\n" +
"\t\tAND vfs_dentry_001.`biz_id` = ?\n" +
"\t\tAND vfs_dentry_001.`status` = ?\n" +
"\t\tAND vfs_dentry_001.`user_id` = ?\n" +
"\t\tAND vfs_dentry_001.`dentry_type` = ?\n" +
"\tLIMIT ?, ?\n" +
"\t) `t`\n" +
"\tJOIN vfs_dentry_001 `f` ON `t`.`id` = `f`.`id`\n" +
"WHERE `t`.`id` = `f`.`id`\n" +
"\tAND `f`.`user_id` = ?", buf.toString());
}
}