Search in sources :

Example 66 with SchemaConfig

use of io.mycat.config.model.SchemaConfig in project Mycat-Server by MyCATApache.

the class DruidMysqlRouteStrategyTest method testERroute.

public void testERroute() throws Exception {
    SchemaConfig schema = schemaMap.get("TESTDB");
    String sql = "insert into orders (id,name,customer_id) values(1,'testonly',1)";
    RouteResultset rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals("dn1", rrs.getNodes()[0].getName());
    sql = "insert into orders (id,name,customer_id) values(1,'testonly',2000001)";
    rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    // can't update join key
    sql = "update orders set id=1 ,name='aaa' , customer_id=2000001";
    String err = null;
    try {
        rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    } catch (SQLNonTransientException e) {
        err = e.getMessage();
    }
    Assert.assertEquals(true, err.startsWith("Parent relevant column can't be updated ORDERS->CUSTOMER_ID"));
    // route by parent rule ,update sql
    sql = "update orders set id=1 ,name='aaa' where customer_id=2000001";
    rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    Assert.assertEquals(true, rrs.isCacheAble());
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    // route by parent rule but can't find datanode
    sql = "update orders set id=1 ,name='aaa' where customer_id=-1";
    try {
        rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    } catch (Exception e) {
        err = e.getMessage();
    }
    Assert.assertEquals(true, err.startsWith("can't find datanode for sharding column:"));
    // route by parent rule ,select sql
    sql = "select * from orders  where customer_id=2000001";
    rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    Assert.assertEquals(true, rrs.isCacheAble());
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    // route by parent rule ,delete sql
    sql = "delete from orders  where customer_id=2000001";
    rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    //test alias in column
    sql = "select name as order_name from  orders order by order_name limit 10,5";
    rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
    MySqlStatementParser parser = new MySqlStatementParser("SELECT name AS order_name FROM orders ORDER BY order_name LIMIT 0,15");
    SQLStatement statement = parser.parseStatement();
//		Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
}
Also used : SystemConfig(io.mycat.config.model.SystemConfig) SQLNonTransientException(java.sql.SQLNonTransientException) SchemaConfig(io.mycat.config.model.SchemaConfig) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SQLNonTransientException(java.sql.SQLNonTransientException) NoSuchElementException(java.util.NoSuchElementException) RouteResultset(io.mycat.route.RouteResultset)

Example 67 with SchemaConfig

use of io.mycat.config.model.SchemaConfig in project Mycat-Server by MyCATApache.

the class DruidMysqlRouteStrategyTest method testGlobalTableroute.

public void testGlobalTableroute() throws Exception {
    String sql = null;
    SchemaConfig schema = schemaMap.get("TESTDB");
    RouteResultset rrs = null;
    // select of global table route to only one datanode defined
    sql = "select * from company where company.name like 'aaa'";
    schema = schemaMap.get("TESTDB");
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    // query of global table only route to one datanode
    sql = "insert into company (id,name,level) values(111,'company1',3)";
    schema = schemaMap.get("TESTDB");
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(3, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    // update of global table route to every datanode defined
    sql = "update company set name=name+aaa";
    schema = schemaMap.get("TESTDB");
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(3, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    // delete of global table route to every datanode defined
    sql = "delete from company where id = 1";
    schema = schemaMap.get("TESTDB");
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(3, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    // company is global table ,will route to differnt tables
    schema = schemaMap.get("TESTDB");
    sql = "select * from  company A where a.sharding_id=10001 union select * from  company B where B.sharding_id =10010";
    Set<String> nodeSet = new HashSet<String>();
    for (int i = 0; i < 10; i++) {
        rrs = routeStrategy.route(new SystemConfig(), schema, 1, sql, null, null, cachePool);
        Assert.assertEquals(false, rrs.isCacheAble());
        Assert.assertEquals(1, rrs.getNodes().length);
        nodeSet.add(rrs.getNodes()[0].getName());
    }
    Assert.assertEquals(true, nodeSet.size() > 1);
}
Also used : SystemConfig(io.mycat.config.model.SystemConfig) SchemaConfig(io.mycat.config.model.SchemaConfig) RouteResultset(io.mycat.route.RouteResultset) HashSet(java.util.HashSet)

Example 68 with SchemaConfig

use of io.mycat.config.model.SchemaConfig in project Mycat-Server by MyCATApache.

the class HintTest method testHint.

/**
     * 测试注解
     *
     * @throws Exception
     */
@Test
public void testHint() throws Exception {
    SchemaConfig schema = schemaMap.get("TESTDB");
    //使用注解(新注解,/*后面没有空格),路由到1个节点
    String sql = "/*!mycat: sql = select * from employee where sharding_id = 10010 */select * from employee";
    CacheService cacheService = new CacheService();
    RouteService routerService = new RouteService(cacheService);
    RouteResultset rrs = routerService.route(new SystemConfig(), schema, ServerParse.SELECT, sql, "UTF-8", null);
    Assert.assertTrue(rrs.getNodes().length == 1);
    //使用注解(新注解,/*后面有空格),路由到1个节点
    sql = "/*#mycat: sql = select * from employee where sharding_id = 10000 */select * from employee";
    rrs = routerService.route(new SystemConfig(), schema, ServerParse.SELECT, sql, "UTF-8", null);
    Assert.assertTrue(rrs.getNodes().length == 1);
    //不用注解,路由到2个节点
    sql = "select * from employee";
    rrs = routerService.route(new SystemConfig(), schema, ServerParse.SELECT, sql, "UTF-8", null);
    Assert.assertTrue(rrs.getNodes().length == 2);
}
Also used : SystemConfig(io.mycat.config.model.SystemConfig) SchemaConfig(io.mycat.config.model.SchemaConfig) RouteService(io.mycat.route.RouteService) CacheService(io.mycat.cache.CacheService) RouteResultset(io.mycat.route.RouteResultset) Test(org.junit.Test)

Example 69 with SchemaConfig

use of io.mycat.config.model.SchemaConfig in project Mycat-Server by MyCATApache.

the class TestSelectBetweenSqlParser method testBetweenSqlRoute.

@Test
public void testBetweenSqlRoute() throws SQLNonTransientException {
    String sql = "select * from offer_detail where offer_id between 1 and 33";
    SchemaConfig schema = schemaMap.get("cndb");
    RouteResultset rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(5, rrs.getNodes().length);
    sql = "select * from offer_detail where col_1 = 33 and offer_id between 1 and 33 and col_2 = 18";
    schema = schemaMap.get("cndb");
    rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(5, rrs.getNodes().length);
    //		sql = "select b.* from offer_date b join  offer_detail a on a.id=b.id " +
    //				"where b.col_date between '2014-02-02' and '2014-04-12' and col_1 = 3 and offer_id between 1 and 33";
    sql = "select b.* from offer_detail a  join  offer_date b on a.id=b.id " + "where b.col_date between '2014-02-02' and '2014-04-12' and col_1 = 3 and offer_id between 1 and 33";
    //		sql = "select a.* from offer_detail a join offer_date b on a.id=b.id " +
    //				"where b.col_date = '2014-04-02' and col_1 = 33 and offer_id =1";
    schema = schemaMap.get("cndb");
    rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    //这里2个表都有条件路由,取的是交集
    Assert.assertEquals(2, rrs.getNodes().length);
    //确认大于小于操作符
    sql = "select b.* from  offer_date b " + "where b.col_date > '2014-02-02'";
    //		sql = "select a.* from offer_detail a join offer_date b on a.id=b.id " +
    //				"where b.col_date = '2014-04-02' and col_1 = 33 and offer_id =1";
    schema = schemaMap.get("cndb");
    rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(128, rrs.getNodes().length);
    sql = "select * from offer_date where col_1 = 33 and col_date between '2014-01-02' and '2014-01-12'";
    schema = schemaMap.get("cndb");
    rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(2, rrs.getNodes().length);
    sql = "select * from offer_date a where col_1 = 33 and a.col_date between '2014-01-02' and '2014-01-12'";
    schema = schemaMap.get("cndb");
    rrs = RouteStrategyFactory.getRouteStrategy().route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(2, rrs.getNodes().length);
}
Also used : SystemConfig(io.mycat.config.model.SystemConfig) SchemaConfig(io.mycat.config.model.SchemaConfig) Test(org.junit.Test)

Example 70 with SchemaConfig

use of io.mycat.config.model.SchemaConfig in project Mycat-Server by MyCATApache.

the class PartitionByRangeModTest method testRange.

@Test
public void testRange() throws SQLNonTransientException {
    String sql = "select * from offer  where id between 2000000  and 4000001     order by id desc limit 100";
    SchemaConfig schema = schemaMap.get("TESTDB");
    RouteResultset rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(10, rrs.getNodes().length);
    sql = "select * from offer  where id between 9  and 2000     order by id desc limit 100";
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(5, rrs.getNodes().length);
    sql = "select * from offer  where id between 4000001  and 6005001     order by id desc limit 100";
    rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
    Assert.assertEquals(8, rrs.getNodes().length);
}
Also used : SystemConfig(io.mycat.config.model.SystemConfig) SchemaConfig(io.mycat.config.model.SchemaConfig) RouteResultset(io.mycat.route.RouteResultset) Test(org.junit.Test)

Aggregations

SchemaConfig (io.mycat.config.model.SchemaConfig)79 SystemConfig (io.mycat.config.model.SystemConfig)54 RouteResultset (io.mycat.route.RouteResultset)34 Test (org.junit.Test)31 TableConfig (io.mycat.config.model.TableConfig)12 MycatConfig (io.mycat.config.MycatConfig)9 RouteResultsetNode (io.mycat.route.RouteResultsetNode)7 SQLNonTransientException (java.sql.SQLNonTransientException)7 PhysicalDBNode (io.mycat.backend.datasource.PhysicalDBNode)6 RowDataPacket (io.mycat.net.mysql.RowDataPacket)6 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)5 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)5 CacheService (io.mycat.cache.CacheService)5 MycatCluster (io.mycat.config.MycatCluster)5 UserConfig (io.mycat.config.model.UserConfig)5 ArrayList (java.util.ArrayList)5 NoSuchElementException (java.util.NoSuchElementException)5 EOFPacket (io.mycat.net.mysql.EOFPacket)4 FieldPacket (io.mycat.net.mysql.FieldPacket)4 RouteService (io.mycat.route.RouteService)4