Exemplo n.º 1
0
  private void getRoute(String sql) {
    try {
      if (joinParser != null) {
        rrs =
            RouteStrategyFactory.getRouteStrategy()
                .route(sysConfig, schema, sqltype, sql, charset, sc, cachePool);
      }
    } catch (Exception e) {

    }
  }
Exemplo n.º 2
0
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());
  }
}