Examples with SchemaConfig com.actiontech.dble.config.model.SchemaConfig used on opensource projects

Example 26 with SchemaConfig

use of com.actiontech.dble.config.model.SchemaConfig in project dble by actiontech.

the class DruidMysqlRouteStrategyTest method testRouteMultiTables.

public void testRouteMultiTables() throws Exception {
    // company is global table ,route to 3 datanode and ignored in route
    String sql = "select * from company,customer ,orders where customer.company_id=company.id and orders.customer_id=customer.id and company.name like 'aaa' limit 10";
    SchemaConfig schema = schemaMap.get("TESTDB");
    RouteResultset rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(2, rrs.getNodes().length);
    Assert.assertEquals(true, rrs.isCacheAble());
    Assert.assertEquals(10, rrs.getLimitSize());
    Assert.assertEquals("dn1", rrs.getNodes()[0].getName());
    Assert.assertEquals("dn2", rrs.getNodes()[1].getName());
}

Example 27 with SchemaConfig

use of com.actiontech.dble.config.model.SchemaConfig in project dble by actiontech.

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(schema, -1, sql, 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(schema, -1, sql, 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(schema, -1, sql, 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(schema, -1, sql, 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(schema, 1, sql, 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);
}

Example 28 with SchemaConfig

use of com.actiontech.dble.config.model.SchemaConfig in project dble by actiontech.

the class DruidMysqlRouteStrategyTest method testRouteCache.

public void testRouteCache() throws Exception {
    // select cache ID
    this.cachePool.putIfAbsent("TESTDB_EMPLOYEE", "88", "dn2");
    SchemaConfig schema = schemaMap.get("TESTDB");
    String sql = "select * from employee where id=88";
    RouteResultset rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(null, rrs.getPrimaryKey());
    Assert.assertEquals(-1, rrs.getLimitSize());
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    // select cache ID not found ,return all node and rrst not cached
    sql = "select * from employee where id=89";
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(2, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals("TESTDB_EMPLOYEE.ID", rrs.getPrimaryKey());
    Assert.assertEquals(-1, rrs.getLimitSize());
    // update cache ID found
    sql = "update employee  set name='aaa' where id=88";
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(null, rrs.getPrimaryKey());
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
    // delete cache ID found
    sql = "delete from  employee  where id=88";
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals("dn2", rrs.getNodes()[0].getName());
}

Example 29 with SchemaConfig

use of com.actiontech.dble.config.model.SchemaConfig in project dble by actiontech.

the class DruidMysqlRouteStrategyTest method testRouteInsertShort.

// public void testAlias() throws Exception {
// String sql = "SELECT  UM.UserId , UM.MenuId ,SM.ParentId ,SM.FullName , SM.Description , SM.Img , SM.NavigateUrl ,SM.FormName ,SM.Target ,SM.IsUnfold FROM    Lever_SysMenu SM INNER JOIN ( SELECT UR.UserId AS UserId ,  RM.MenuId AS MenuId FROM   Lever_RoleMenu RM  INNER JOIN Lever_UserRole UR ON RM.RoleId = UR.RoleId  UNION SELECT UserId ,   MenuId FROM   Lever_UserMenu   UNION SELECT U.UserId ,      RM.MenuId   FROM   Lever_User U     LEFT JOIN Lever_RoleMenu RM ON U.RoleId = RM.RoleId    WHERE  U.UserId = '8d28533f-1762-4e79-b71f-64eb1a50cb8b' ) UM ON SM.MenuId = UM.MenuId   WHERE   UM.UserId = '8d28533f-1762-4e79-b71f-64eb1a50cb8b'  AND SM.Enabled = 1  ORDER BY SM.SortCode";
// SchemaConfig schema = schemaMap.get("wdw");
// RouteResultset rrs = routeStrategy.route(new SystemConfig(),schema, -1, sql, null,
// null, cachePool);
// }
public void testRouteInsertShort() throws Exception {
    String sql = "inSErt into offer_detail (`offer_id`, gmt) values (123,now())";
    SchemaConfig schema = schemaMap.get("cndb");
    RouteResultset rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(-1L, rrs.getLimitSize());
    Assert.assertEquals("detail_dn15", rrs.getNodes()[0].getName());
    Assert.assertEquals("inSErt into offer_detail (`offer_id`, gmt) values (123,now())", rrs.getNodes()[0].getStatement());
    sql = "inSErt into offer_detail ( gmt) values (now())";
    schema = schemaMap.get("cndb");
    try {
        rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    } catch (Exception e) {
        String msg = "bad insert sql (sharding column:";
        Assert.assertTrue(e.getMessage().contains(msg));
    }
    sql = "inSErt into offer_detail (offer_id, gmt) values (123,now())";
    schema = schemaMap.get("cndb");
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(-1L, rrs.getLimitSize());
    Assert.assertEquals("detail_dn15", rrs.getNodes()[0].getName());
    Assert.assertEquals("inSErt into offer_detail (offer_id, gmt) values (123,now())", rrs.getNodes()[0].getStatement());
    sql = "insert into offer(group_id,offer_id,member_id)values(234,123,'abc')";
    schema = schemaMap.get("cndb");
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
    Assert.assertEquals(false, rrs.isCacheAble());
    Assert.assertEquals(-1L, rrs.getLimitSize());
    Assert.assertEquals("offer_dn12", rrs.getNodes()[0].getName());
    Assert.assertEquals("insert into offer(group_id,offer_id,member_id)values(234,123,'abc')", rrs.getNodes()[0].getStatement());
    sql = "\n" + "  INSERT INTO \n" + "`offer` \n" + "(`asf`,member_id) \n" + "VALUES \n" + "(' the articles sfroms user selection ','abc')";
    schema = schemaMap.get("cndb");
    rrs = routeStrategy.route(schema, -1, sql, null, cachePool);
    Assert.assertEquals(1, rrs.getNodes().length);
}

Example 30 with SchemaConfig

use of com.actiontech.dble.config.model.SchemaConfig in project dble by actiontech.

the class DruidMysqlSqlParserTest method testLimitPage.

@Test
public void testLimitPage() throws SQLException {
    String sql = "select * from offer order by id desc limit 5,10";
    SchemaConfig schema = schemaMap.get("mysqldb");
    RouteResultset rrs = routeStrategy.route(schema, -1, sql, 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("dn1", rrs.getNodes()[0].getName());
    Assert.assertEquals("dn2", rrs.getNodes()[1].getName());
    sql = rrs.getNodes()[0].getStatement();
    rrs = routeStrategy.route(schema, -1, sql, 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(schema, -1, sql, 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("dn1", rrs.getNodes()[0].getName());
    sql = "select * from offer1 order by id desc limit 10";
    rrs = routeStrategy.route(schema, -1, sql, 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("dn1", rrs.getNodes()[0].getName());
}