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());
}
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);
}
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);
}
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);
}
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);
}
Aggregations