private void getRoute(String sql) {
try {
if (joinParser != null) {
rrs =
RouteStrategyFactory.getRouteStrategy()
.route(sysConfig, schema, sqltype, sql, charset, sc, cachePool);
}
} catch (Exception e) {
}
}
public class HintTest {
protected Map<String, SchemaConfig> schemaMap;
protected LayerCachePool cachePool = new SimpleCachePool();
protected RouteStrategy routeStrategy = RouteStrategyFactory.getRouteStrategy("fdbparser");
public HintTest() {
ConfigInitializer confInit = new ConfigInitializer(true);
schemaMap = confInit.getSchemas();
}
/**
* 测试注解
*
* @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);
}
}
public class DruidSqlServerSqlParserTest {
protected Map<String, SchemaConfig> schemaMap;
protected LayerCachePool cachePool = new SimpleCachePool();
protected RouteStrategy routeStrategy = RouteStrategyFactory.getRouteStrategy("druidparser");
public DruidSqlServerSqlParserTest() {
ConfigInitializer confInit = new ConfigInitializer(true);
schemaMap = confInit.getSchemas();
}
@Test
public void testLimitToSqlServerPage() throws SQLNonTransientException {
String sql = "select * from offer order by id desc limit 5,10";
SchemaConfig schema = schemaMap.get("sqlserverdb");
RouteResultset rrs =
routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(2, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(15, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
Assert.assertEquals("sqlserver_2", rrs.getNodes()[1].getName());
sql = rrs.getNodes()[0].getStatement();
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(15, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(0, rrs.getLimitStart());
Assert.assertEquals(15, rrs.getLimitSize());
sql = "select * from offer1 order by id desc limit 5,10";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(1, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(5, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
}
@Test
public void testSqlServerPageSQL() throws SQLNonTransientException {
String sql =
"SELECT *\n"
+ "FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid DESC) AS ROWNUM\n"
+ "\tFROM offer \n"
+ "\tWHERE sts <> 'N'\n"
+ "\t\t\t) XX\n"
+ "WHERE ROWNUM > 5\n"
+ "\tAND ROWNUM <= 15\n";
SchemaConfig schema = schemaMap.get("sqlserverdb");
RouteResultset rrs =
routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(2, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(15, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
Assert.assertEquals("sqlserver_2", rrs.getNodes()[1].getName());
sql =
"SELECT *\n"
+ "FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid DESC) AS ROWNUM\n"
+ "\tFROM offer1 \n"
+ "\tWHERE sts <> 'N'\n"
+ "\t\t\t) XX\n"
+ "WHERE ROWNUM > 5\n"
+ "\tAND ROWNUM <= 15\n";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(1, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(5, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
sql =
"select * from ( select row_number()over(order by tempColumn)tempRowNumber,* from ( select top \n"
+ "15 tempColumn=0, sid \n"
+ "from offer where sts<>'N' and asf like '%'+'akka'+'%' order by sid )t )tt where tempRowNumber>5";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(2, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(15, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
Assert.assertEquals("sqlserver_2", rrs.getNodes()[1].getName());
sql =
"select * from ( select row_number()over(order by tempColumn)tempRowNumber,* from ( select top \n"
+ "15 tempColumn=0, sid \n"
+ "from offer1 where sts<>'N' and asf like '%'+'akka'+'%' order by sid )t )tt where tempRowNumber>5";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(1, rrs.getNodes().length);
Assert.assertEquals(5, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(5, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
sql =
"SELECT TOP 10 sid \n"
+ " FROM offer where sts<>'N' and asf like '%'+'akka'+'%' \n"
+ " ORDER BY sid desc";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(2, rrs.getNodes().length);
Assert.assertEquals(0, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
Assert.assertEquals("sqlserver_2", rrs.getNodes()[1].getName());
}
@Test
public void testTopPageSQL() throws SQLNonTransientException {
SchemaConfig schema = schemaMap.get("sqlserverdb");
RouteResultset rrs = null;
String sql =
"SELECT TOP 10 * \n"
+ " FROM offer1 where sts<>'N' and asf like '%'+'akka'+'%' \n"
+ " ORDER BY sid desc";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(1, rrs.getNodes().length);
Assert.assertEquals(0, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
sql =
"SELECT TOP 10 offer1.name,offer1.id \n"
+ " FROM offer1 where sts<>'N' and asf like '%'+'akka'+'%' \n"
+ " ORDER BY sid desc";
rrs = routeStrategy.route(new SystemConfig(), schema, -1, sql, null, null, cachePool);
Assert.assertEquals(1, rrs.getNodes().length);
Assert.assertEquals(0, rrs.getLimitStart());
Assert.assertEquals(10, rrs.getLimitSize());
Assert.assertEquals(0, rrs.getNodes()[0].getLimitStart());
Assert.assertEquals(10, rrs.getNodes()[0].getLimitSize());
Assert.assertEquals(sql, rrs.getNodes()[0].getStatement());
Assert.assertEquals("sqlserver_1", rrs.getNodes()[0].getName());
}
}