Java TableNode类代码示例

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Override
public void visit(DMLUpdateStatement node) {
    QueryTreeNode table = getTableNode(node);
    List<Pair<Identifier, Expression>> cvs = node.getValues();
    Object[] updateValues = new Object[cvs.size()];
    StringBuilder updateColumnsSb = new StringBuilder();
    for (int i = 0; i < cvs.size(); i++) {
        Pair<Identifier, Expression> p = cvs.get(i);
        if (i > 0) {
            updateColumnsSb.append(" ");
        }
        updateColumnsSb.append(p.getKey().getIdTextUpUnescape());
        MySqlExprVisitor mv = new MySqlExprVisitor();
        p.getValue().accept(mv);
        updateValues[i] = mv.getColumnOrValue();// 可能为function
    }

    Expression expr = node.getWhere();
    if (expr != null) {
        handleCondition(table, expr);
    }

    this.updateNode = ((TableNode) table).update(updateColumnsSb.toString(), updateValues);
    updateNode.setIgnore(node.isIgnore());
    updateNode.setLowPriority(node.isLowPriority());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
public void test_单表merge_函数下推() {
    TableNode table = new TableNode("TABLE1");
    table.select("MAX(ID) AS ID");
    table.orderBy("COUNT(ID)");
    table.groupBy("SUBSTRING(NAME,0,10)");
    table.having("COUNT(ID) > 1");
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency());// 并行

    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("SUBSTRING(NAME, 0, 10)", query.getColumns().get(1).toString());// 下推成功
    Assert.assertEquals("COUNT(ID)", query.getColumns().get(2).toString());// 下推成功
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_join条件下推_子表_case4_下推IDNAME() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    table1.alias("A");
    table2.alias("B");

    JoinNode join = table1.join(table2);
    join.setJoinStrategy(JoinStrategy.INDEX_NEST_LOOP);
    join.orderBy("A.ID");
    join.orderBy("A.NAME");
    join.build();

    OrderByPusher.optimize(join);

    Assert.assertEquals(2, table1.getOrderBys().size());
    Assert.assertEquals("TABLE1.ID", table1.getOrderBys().get(0).getColumn().toString());
    Assert.assertEquals("TABLE1.NAME", table1.getOrderBys().get(1).getColumn().toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
/**
 * C4,C5上只存在倒排索引，单C4的选择更高，选择C4倒排
 */
@Test
public void testChooseIndex选择倒排索引() throws QueryException {
    TableNode table = new TableNode("TABLE9");
    QueryTreeNode qn = table.query("C4=10&&C5>3");
    qn.build();

    IndexMeta index = IndexChooser.findBestIndex(table.getTableMeta(),
        table.getColumnsRefered(),
        toDNFFilter(table.getWhereFilter()),
        table.getTableName(),
        extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE9._C4");
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void testDistinct() throws SqlParserException, QueryException {
    String sql = "SELECT COUNT(DISTINCT ID) FROM TABLE1";
    QueryTreeNode qn = query(sql);

    TableNode table1 = new TableNode("TABLE1");
    IColumn c = ASTNodeFactory.getInstance().createColumn();
    c.setColumnName("ID");
    c.setDistinct(true);

    IFunction f = ASTNodeFactory.getInstance().createFunction();
    f.setFunctionName("COUNT");
    f.setColumnName("COUNT(DISTINCT ID)");

    List args = new ArrayList();
    args.add(c);
    f.setArgs(args);
    qn.build();

    QueryTreeNode qnExpected = table1.select(f);
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
private static QueryTreeNode preProcess(QueryTreeNode qtn, boolean typeConvert, Map<String, Object> extraCmd) {
    qtn.setOtherJoinOnFilter(processFilter(qtn.getOtherJoinOnFilter(), typeConvert, extraCmd));
    qtn.having(processFilter(qtn.getHavingFilter(), typeConvert, extraCmd));
    qtn.query(processFilter(qtn.getWhereFilter(), typeConvert, extraCmd));
    qtn.setKeyFilter(processFilter(qtn.getKeyFilter(), typeConvert, extraCmd));
    qtn.setResultFilter(processFilter(qtn.getResultFilter(), typeConvert, extraCmd));
    if (qtn instanceof TableNode) {
        ((TableNode) qtn).setIndexQueryValueFilter(processFilter(((TableNode) qtn).getIndexQueryValueFilter(),
            typeConvert,
            extraCmd));
    }

    if (qtn instanceof JoinNode) {
        for (int i = 0; i < ((JoinNode) qtn).getJoinFilter().size(); i++) {
            processFilter(((JoinNode) qtn).getJoinFilter().get(i), typeConvert, extraCmd);
        }
    }

    for (ASTNode child : qtn.getChildren()) {
        preProcess((QueryTreeNode) child, typeConvert, extraCmd);
    }

    return qtn;
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_合并_OR条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID > 1 OR ID < 3");
    table.build();
    FilterPreProcessor.optimize(table, true, null);
    Assert.assertEquals(table.getWhereFilter().toString(), "1");

    table.query("ID > 1 OR ID > 3");
    table.build();
    FilterPreProcessor.optimize(table, true, null);
    Assert.assertEquals(table.getWhereFilter().toString(), "TABLE1.ID > 1");

    table.query("ID > 1 OR ID = 5");
    table.build();
    FilterPreProcessor.optimize(table, true, null);
    Assert.assertEquals(table.getWhereFilter().toString(), "TABLE1.ID > 1");
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void testPreparedDeleteSql() throws SqlParserException, QueryException {
    String sql = "DELETE FROM TABLE1 WHERE ID>=? AND ID<=?";
    DeleteNode dn = delete(sql);

    Map<Integer, ParameterContext> parameterSettings = null;
    parameterSettings = new TreeMap<Integer, ParameterContext>();
    ParameterContext p1 = new ParameterContext(ParameterMethod.setObject1, new Object[] { 0, 3 });
    ParameterContext p2 = new ParameterContext(ParameterMethod.setObject1, new Object[] { 1, 5 });
    parameterSettings.put(1, p1);
    parameterSettings.put(2, p2);

    dn.assignment(parameterSettings);
    dn.build();

    TableNode table1 = new TableNode("TABLE1");
    DeleteNode dnExpected = table1.delete();
    table1.query("ID>=3 AND ID<=5");
    dnExpected.build();
    assertEquals(dn, dnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_合并_AND条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID > 1 AND ID < 10");
    table.build();
    FilterPreProcessor.optimize(table, true);
    Assert.assertEquals(table.getWhereFilter().toString(), "(TABLE1.ID > 1 AND TABLE1.ID < 10)");

    table.query("ID > 1 AND ID < 10 AND ID >= 2 AND ID < 11");
    table.build();
    FilterPreProcessor.optimize(table, true);
    Assert.assertEquals(table.getWhereFilter().toString(), "(TABLE1.ID >= 2 AND TABLE1.ID < 10)");

    table.query("ID > 1 AND ID < 0");
    table.build();
    try {
        FilterPreProcessor.optimize(table, true);
        Assert.fail();
    } catch (EmptyResultFilterException e) {
        // 会出现EmptyResultFilterException异常
    }
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void testQuery_左连接() throws SqlParserException {
    String sql = "SELECT * FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID=B.ID WHERE A.NAME=1";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    QueryTreeNode qnExpected = table1.alias("A")
        .join(table2.alias("B"))
        .setLeftOuterJoin()
        .addJoinKeys("A.ID", "B.ID")
        .query("A.NAME=1");
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
/**
 * C4,C5上只存在倒排索引，单C4的选择更高，选择C4倒排
 */
@Test
public void testChooseIndex选择倒排索引() {
    TableNode table = new TableNode("TABLE9");
    QueryTreeNode qn = table.query("C4=10&&C5>3");
    qn.build();

    IndexMeta index = IndexChooser.findBestIndex(table.getTableMeta(),
        table.getColumnsRefered(),
        toDNFFilter(table.getWhereFilter()),
        table.getTableName(),
        extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE9._C4");
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_order条件下推_子表_case3_不下推() {
    TableNode table1 = new TableNode("TABLE1");
    table1.alias("A");
    table1.orderBy("ID");
    table1.orderBy("NAME");
    table1.limit(0, 10);

    QueryNode query = new QueryNode(table1);
    query.orderBy("A.NAME");
    query.orderBy("A.SCHOOL");
    query.build();

    OrderByPusher.optimize(query);

    Assert.assertEquals(2, table1.getOrderBys().size());
    Assert.assertEquals("TABLE1.ID", table1.getOrderBys().get(0).getColumn().toString());
    Assert.assertEquals("TABLE1.NAME", table1.getOrderBys().get(1).getColumn().toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void testQuery_函数别名() throws SqlParserException, QueryException {
    String sql = "SELECT T.ID , LENGTH(NAME) AS LEN FROM TABLE1 T  WHERE ID=1";
    QueryTreeNode qn = query(sql);
    qn.build();

    IFunction function = ASTNodeFactory.getInstance().createFunction();
    function.setColumnName("LENGTH(NAME)");
    function.setAlias("LEN");
    function.setFunctionName("LENGTH");

    QueryTreeNode qnExpected = new TableNode("TABLE1").alias("T").select("T.ID").query("ID=1");
    qnExpected.addColumnsSelected(function);
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_where条件下推_多级join() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    TableNode table3 = new TableNode("TABLE3");

    JoinNode join = table1.join(table2);
    join.select("TABLE1.ID AS ID , TABLE1.NAME AS NAME");
    join.query("TABLE1.ID>5 AND TABLE2.ID<10 AND TABLE1.NAME = TABLE2.NAME");
    join.build();

    JoinNode nextJoin = join.join(table3);
    nextJoin.query("TABLE1.NAME = 6 AND TABLE1.ID = TABLE3.ID");
    nextJoin.build();
    FilterPusher.optimize(nextJoin);

    Assert.assertEquals("(TABLE1.NAME = 6 AND TABLE1.ID > 5)", ((JoinNode) nextJoin.getLeftNode()).getLeftNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("(TABLE2.ID < 10 AND TABLE2.NAME = 6)", ((JoinNode) nextJoin.getLeftNode()).getRightNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("TABLE1.ID = TABLE3.ID", nextJoin.getJoinFilter().get(0).toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入依赖的package包/类
@Test
public void test_where条件下推_join列存在函数不处理() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");

    JoinNode join = table1.join(table2);
    join.query("TABLE1.ID>5 AND TABLE2.ID<10 AND TABLE1.NAME = TABLE2.NAME + 1");
    join.build();
    FilterPreProcessor.optimize(join, true, null);
    FilterPusher.optimize(join);

    Assert.assertEquals("TABLE1.ID > 5", join.getLeftNode().getWhereFilter().toString());
    Assert.assertEquals("TABLE2.ID < 10", join.getRightNode().getWhereFilter().toString());
    Assert.assertEquals("TABLE1.NAME = TABLE2.NAME + 1", join.getWhereFilter().toString());// 还是留在where中
}