Search in sources :

Example 6 with CustomRuleFunction

use of io.mycat.router.CustomRuleFunction in project Mycat2 by MyCATApache.

the class EnumTest method testHashMM.

@Test
public void testHashMM() {
    ShardingTableConfig mainSharding = new ShardingTableConfig();
    mainSharding.setCreateTableSQL("CREATE TABLE db1.`sharding` (\n" + "  `id` bigint NOT NULL AUTO_INCREMENT,\n" + "  `user_id` varchar(100) DEFAULT NULL,\n" + "  `traveldate` date DEFAULT NULL,\n" + "  `fee` decimal(10,0) DEFAULT NULL,\n" + "  `days` int DEFAULT NULL,\n" + "  `blob` longblob,\n" + "  PRIMARY KEY (`id`),\n" + "  KEY `id` (`id`)\n" + ") ENGINE=InnoDB  DEFAULT CHARSET=utf8" + " dbpartition by mod_hash(id) tbpartition by MM(traveldate) tbpartitions 2 dbpartitions 4;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"2\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"4\",\n" + "\t\t\t\t\t\"tableMethod\":\"MM(traveldate)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"hash(id)\"\n" + "\t\t\t\t}", Map.class)).build());
    MetadataManager metadataManager = getMetadataManager(mainSharding);
    ShardingTable tableHandler = (ShardingTable) metadataManager.getTable("db1", "sharding");
    CustomRuleFunction shardingFuntion = tableHandler.getShardingFuntion();
    LocalDate start = LocalDate.of(2021, 11, 8);
    List<Partition> calculate = shardingFuntion.calculate(Collections.singletonMap("traveldate", (new RangeVariable("traveldate", RangeVariableType.EQUAL, start))));
    Assert.assertEquals(2, calculate.size());
    for (Partition partition : calculate) {
        Assert.assertEquals(start.getMonthValue() % 4, (int) partition.getTableIndex());
    }
    {
        LocalDate end = LocalDate.of(2021, 11, 9);
        List<Partition> calculate2 = shardingFuntion.calculate(Collections.singletonMap("traveldate", (new RangeVariable("traveldate", RangeVariableType.RANGE, start, end))));
        Assert.assertEquals(calculate, calculate2);
    }
    {
        LocalDate end = LocalDate.of(2021, 12, 9);
        List<Partition> calculate2 = shardingFuntion.calculate(Collections.singletonMap("traveldate", (new RangeVariable("traveldate", RangeVariableType.RANGE, start, end))));
        HashSet<Integer> set = new HashSet<>();
        for (int i = 11; i <= 12; i++) {
            set.add(i % 4);
        }
        Assert.assertEquals(true, calculate2.stream().allMatch(i -> set.contains(i.getTableIndex())));
        System.out.println();
    }
    {
        LocalDate end = LocalDate.of(2022, 1, 9);
        List<Partition> calculate2 = shardingFuntion.calculate(Collections.singletonMap("traveldate", (new RangeVariable("traveldate", RangeVariableType.RANGE, start, end))));
        HashSet<Integer> set = new HashSet<>();
        for (Integer integer : Arrays.asList(11, 12, 1)) {
            set.add(integer % 4);
        }
        Assert.assertEquals(true, calculate2.stream().allMatch(i -> set.contains(i.getTableIndex())));
        System.out.println();
    }
}
Also used : Partition(io.mycat.Partition) RangeVariable(io.mycat.RangeVariable) CustomRuleFunction(io.mycat.router.CustomRuleFunction) LocalDate(java.time.LocalDate) MetadataManager(io.mycat.MetadataManager) ShardingTableConfig(io.mycat.config.ShardingTableConfig) ShardingTable(io.mycat.calcite.table.ShardingTable) Test(org.junit.Test)

Example 7 with CustomRuleFunction

use of io.mycat.router.CustomRuleFunction in project Mycat2 by MyCATApache.

the class EnumTest method testYYYYWEEK.

@Test
public void testYYYYWEEK() {
    ShardingTableConfig mainSharding = new ShardingTableConfig();
    mainSharding.setCreateTableSQL("CREATE TABLE db1.`sharding` (\n" + "  `id` bigint NOT NULL AUTO_INCREMENT,\n" + "  `user_id` varchar(100) DEFAULT NULL,\n" + "  `traveldate` date DEFAULT NULL,\n" + "  `fee` decimal(10,0) DEFAULT NULL,\n" + "  `days` int DEFAULT NULL,\n" + "  `blob` longblob,\n" + "  PRIMARY KEY (`id`),\n" + "  KEY `id` (`id`)\n" + ") ENGINE=InnoDB  DEFAULT CHARSET=utf8" + " dbpartition by YYYYWEEK(traveldate) tbpartition by YYYYWEEK(traveldate) tbpartitions 2 dbpartitions 4;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"8\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"14\",\n" + "\t\t\t\t\t\"tableMethod\":\"YYYYWEEK(traveldate)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"YYYYWEEK(traveldate)\"\n" + "\t\t\t\t}", Map.class)).build());
    MetadataManager metadataManager = getMetadataManager(mainSharding);
    ShardingTable tableHandler = (ShardingTable) metadataManager.getTable("db1", "sharding");
    CustomRuleFunction shardingFuntion = tableHandler.getShardingFuntion();
    // LocalDate start = LocalDate.of(2021, 11, 8);
    // 
    // List<Partition> calculate = shardingFuntion.calculate(Collections.singletonMap("traveldate",
    // (new RangeVariable("traveldate", RangeVariableType.EQUAL, start))));
    // Assert.assertEquals(1, calculate.size());
    // Assert.assertEquals( 7, (int) calculate.get(0).getTableIndex());
    // Assert.assertEquals( 3, (int) calculate.get(0).getDbIndex());
    // {
    // LocalDate end = LocalDate.of(2021, 11, 9);
    // 
    // List<Partition> calculate2 = shardingFuntion.calculate(Collections.singletonMap("traveldate",
    // (new RangeVariable("traveldate", RangeVariableType.RANGE, start, end))));
    // 
    // Assert.assertEquals(calculate, calculate2);
    // 
    // }
    // 
    // AutoFunction autoFunction = (AutoFunction) shardingFuntion;
    // 
    // ArrayList<Object> res = new ArrayList<>();
    // Optional<Set<LocalDate>> integers = autoFunction.enumMonthValue(12, new ToIntFunction<Object>() {
    // @Override
    // public int applyAsInt(Object value) {
    // res.add(value);
    // return ((LocalDate)value).getMonthValue();
    // }
    // }, LocalDate.of(2021, 11, 1), LocalDate.of(2022, 3, 1));
    // 
    // Assert.assertEquals("[2021-11-01, 2021-12-01, 2022-01-01, 2022-02-01]",res.toString());
    // Assert.assertEquals("[1, 2, 11, 12]",integers.get().toString());
    {
        List<Partition> calculate2 = shardingFuntion.calculate(Collections.singletonMap("traveldate", (new RangeVariable("traveldate", RangeVariableType.RANGE, LocalDate.of(2021, 11, 1), LocalDate.of(2022, 3, 1)))));
        Assert.assertEquals(18, calculate2.size());
        System.out.println("");
    }
    System.out.println();
}
Also used : RangeVariable(io.mycat.RangeVariable) MetadataManager(io.mycat.MetadataManager) ShardingTableConfig(io.mycat.config.ShardingTableConfig) CustomRuleFunction(io.mycat.router.CustomRuleFunction) ShardingTable(io.mycat.calcite.table.ShardingTable) Test(org.junit.Test)

Example 8 with CustomRuleFunction

use of io.mycat.router.CustomRuleFunction in project Mycat2 by MyCATApache.

the class HashFunctionTest method testHashIdHashUserId.

@Test
public void testHashIdHashUserId() {
    ShardingTableConfig mainSharding = new ShardingTableConfig();
    mainSharding.setCreateTableSQL("CREATE TABLE db1.`sharding` (\n" + "  `id` bigint NOT NULL AUTO_INCREMENT,\n" + "  `user_id` varchar(100) DEFAULT NULL,\n" + "  `traveldate` date DEFAULT NULL,\n" + "  `fee` decimal(10,0) DEFAULT NULL,\n" + "  `days` int DEFAULT NULL,\n" + "  `blob` longblob,\n" + "  PRIMARY KEY (`id`),\n" + "  KEY `id` (`id`)\n" + ") ENGINE=InnoDB  DEFAULT CHARSET=utf8" + " dbpartition by mod_hash(id) tbpartition by mod_hash(user_id) tbpartitions 2 dbpartitions 4;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"2\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"4\",\n" + "\t\t\t\t\t\"tableMethod\":\"hash(user_id)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"hash(id)\"\n" + "\t\t\t\t}", Map.class)).build());
    MetadataManager metadataManager = getMetadataManager(mainSharding);
    ShardingTable tableHandler = (ShardingTable) metadataManager.getTable("db1", "sharding");
    CustomRuleFunction shardingFuntion = tableHandler.getShardingFuntion();
    ImmutableMap<String, RangeVariable> map = ImmutableMap.of("id", new RangeVariable("id", RangeVariableType.EQUAL, 15), "user_id", (new RangeVariable("user_id", RangeVariableType.EQUAL, 2)));
    List<Partition> insertRoute = shardingFuntion.calculate(map);
    Assert.assertEquals(1, insertRoute.size());
    List<Partition> firstColumn = shardingFuntion.calculate(ImmutableMap.of("id", (new RangeVariable("id", RangeVariableType.EQUAL, 15))));
    Assert.assertTrue(firstColumn.containsAll(insertRoute));
    List<Partition> secondColumn = shardingFuntion.calculate(ImmutableMap.of("user_id", (new RangeVariable("user_id", RangeVariableType.EQUAL, 2))));
    Assert.assertTrue(secondColumn.containsAll(insertRoute));
    String s1 = shardingFuntion.calculate(Collections.emptyMap()).toString();
    Assert.assertEquals("[{targetName='c0', schemaName='db1_0', tableName='sharding_0', index=0, dbIndex=0, tableIndex=0}, {targetName='c0', schemaName='db1_0', tableName='sharding_1', index=1, dbIndex=0, tableIndex=1}, {targetName='c0', schemaName='db1_0', tableName='sharding_2', index=2, dbIndex=0, tableIndex=2}, {targetName='c0', schemaName='db1_0', tableName='sharding_3', index=3, dbIndex=0, tableIndex=3}, {targetName='c0', schemaName='db1_1', tableName='sharding_0', index=4, dbIndex=1, tableIndex=0}, {targetName='c0', schemaName='db1_1', tableName='sharding_1', index=5, dbIndex=1, tableIndex=1}, {targetName='c0', schemaName='db1_1', tableName='sharding_2', index=6, dbIndex=1, tableIndex=2}, {targetName='c0', schemaName='db1_1', tableName='sharding_3', index=7, dbIndex=1, tableIndex=3}]", s1);
    System.out.println();
}
Also used : RangeVariable(io.mycat.RangeVariable) Partition(io.mycat.Partition) MetadataManager(io.mycat.MetadataManager) ShardingTableConfig(io.mycat.config.ShardingTableConfig) CustomRuleFunction(io.mycat.router.CustomRuleFunction) ImmutableMap(com.google.common.collect.ImmutableMap) Map(java.util.Map) ShardingTable(io.mycat.calcite.table.ShardingTable) Test(org.junit.Test)

Example 9 with CustomRuleFunction

use of io.mycat.router.CustomRuleFunction in project Mycat2 by MyCATApache.

the class HashFunctionTest method testHashIdHashId.

/**
 * https://help.aliyun.com/document_detail/71276.html
 * <p>
 * 若分库和分表都使用同一个拆分键进行HASH时,则根据拆分键的键值按总的分表数取余。
 * 例如有2个分库,每个分库4张分表,那么0库上保存分表0~3,1库上保存分表4~7。某个键值为15,那么根据该路由方式,则该键值15将被分到1库的表7上((15 % (2 * 4) =7))
 */
@Test
public void testHashIdHashId() {
    ShardingTableConfig mainSharding = new ShardingTableConfig();
    mainSharding.setCreateTableSQL("CREATE TABLE db1.`sharding` (\n" + "  `id` bigint NOT NULL AUTO_INCREMENT,\n" + "  `user_id` varchar(100) DEFAULT NULL,\n" + "  `traveldate` date DEFAULT NULL,\n" + "  `fee` decimal(10,0) DEFAULT NULL,\n" + "  `days` int DEFAULT NULL,\n" + "  `blob` longblob,\n" + "  PRIMARY KEY (`id`),\n" + "  KEY `id` (`id`)\n" + ") ENGINE=InnoDB  DEFAULT CHARSET=utf8" + " dbpartition by mod_hash(id) tbpartition by mod_hash(id) tbpartitions 2 dbpartitions 4;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"2\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"4\",\n" + "\t\t\t\t\t\"tableMethod\":\"hash(id)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"hash(id)\"\n" + "\t\t\t\t}", Map.class)).build());
    MetadataManager metadataManager = getMetadataManager(mainSharding);
    ShardingTable tableHandler = (ShardingTable) metadataManager.getTable("db1", "sharding");
    CustomRuleFunction shardingFuntion = tableHandler.getShardingFuntion();
    List<Partition> calculate = shardingFuntion.calculate(Collections.singletonMap("id", (new RangeVariable("id", RangeVariableType.EQUAL, 15))));
    String s = calculate.toString();
    Assert.assertTrue(s.contains("[{targetName='c0', schemaName='db1_1', tableName='sharding_3', index=7, dbIndex=1, tableIndex=3}]"));
    String s1 = shardingFuntion.calculate(Collections.emptyMap()).toString();
    Assert.assertEquals("[{targetName='c0', schemaName='db1_0', tableName='sharding_0', index=0, dbIndex=0, tableIndex=0}, {targetName='c0', schemaName='db1_0', tableName='sharding_1', index=1, dbIndex=0, tableIndex=1}, {targetName='c0', schemaName='db1_0', tableName='sharding_2', index=2, dbIndex=0, tableIndex=2}, {targetName='c0', schemaName='db1_0', tableName='sharding_3', index=3, dbIndex=0, tableIndex=3}, {targetName='c0', schemaName='db1_1', tableName='sharding_0', index=4, dbIndex=1, tableIndex=0}, {targetName='c0', schemaName='db1_1', tableName='sharding_1', index=5, dbIndex=1, tableIndex=1}, {targetName='c0', schemaName='db1_1', tableName='sharding_2', index=6, dbIndex=1, tableIndex=2}, {targetName='c0', schemaName='db1_1', tableName='sharding_3', index=7, dbIndex=1, tableIndex=3}]", s1);
    System.out.println();
}
Also used : Partition(io.mycat.Partition) RangeVariable(io.mycat.RangeVariable) MetadataManager(io.mycat.MetadataManager) ShardingTableConfig(io.mycat.config.ShardingTableConfig) CustomRuleFunction(io.mycat.router.CustomRuleFunction) ImmutableMap(com.google.common.collect.ImmutableMap) Map(java.util.Map) ShardingTable(io.mycat.calcite.table.ShardingTable) Test(org.junit.Test)

Example 10 with CustomRuleFunction

use of io.mycat.router.CustomRuleFunction in project Mycat2 by MyCATApache.

the class HashFunctionTest method testHashIdHashId2.

/*
    test for mappingFormat
     */
@Test
public void testHashIdHashId2() {
    ShardingTableConfig mainSharding = new ShardingTableConfig();
    mainSharding.setCreateTableSQL("CREATE TABLE db1.`sharding` (\n" + "  `id` bigint NOT NULL AUTO_INCREMENT,\n" + "  `user_id` varchar(100) DEFAULT NULL,\n" + "  `traveldate` date DEFAULT NULL,\n" + "  `fee` decimal(10,0) DEFAULT NULL,\n" + "  `days` int DEFAULT NULL,\n" + "  `blob` longblob,\n" + "  PRIMARY KEY (`id`),\n" + "  KEY `id` (`id`)\n" + ") ENGINE=InnoDB  DEFAULT CHARSET=utf8" + " dbpartition by mod_hash(id) tbpartition by mod_hash(id) tbpartitions 2 dbpartitions 4;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"2\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${index}\",\n" + "\t\t\t\t\t\"tableNum\":\"4\",\n" + "\t\t\t\t\t\"tableMethod\":\"hash(id)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"hash(id)\"\n" + "\t\t\t\t}", Map.class)).build());
    MetadataManager metadataManager = getMetadataManager(mainSharding);
    ShardingTable tableHandler = (ShardingTable) metadataManager.getTable("db1", "sharding");
    CustomRuleFunction shardingFuntion = tableHandler.getShardingFuntion();
    List<Partition> calculate = shardingFuntion.calculate(Collections.singletonMap("id", (new RangeVariable("id", RangeVariableType.EQUAL, 15))));
    String s = calculate.toString();
    Assert.assertTrue(s.contains("[{targetName='c0', schemaName='db1_1', tableName='sharding_7', index=7, dbIndex=1, tableIndex=3}]"));
    String s1 = shardingFuntion.calculate(Collections.emptyMap()).toString();
    Assert.assertEquals("[{targetName='c0', schemaName='db1_0', tableName='sharding_0', index=0, dbIndex=0, tableIndex=0}, {targetName='c0', schemaName='db1_0', tableName='sharding_1', index=1, dbIndex=0, tableIndex=1}, {targetName='c0', schemaName='db1_0', tableName='sharding_2', index=2, dbIndex=0, tableIndex=2}, {targetName='c0', schemaName='db1_0', tableName='sharding_3', index=3, dbIndex=0, tableIndex=3}, {targetName='c0', schemaName='db1_1', tableName='sharding_4', index=4, dbIndex=1, tableIndex=0}, {targetName='c0', schemaName='db1_1', tableName='sharding_5', index=5, dbIndex=1, tableIndex=1}, {targetName='c0', schemaName='db1_1', tableName='sharding_6', index=6, dbIndex=1, tableIndex=2}, {targetName='c0', schemaName='db1_1', tableName='sharding_7', index=7, dbIndex=1, tableIndex=3}]", s1);
    System.out.println();
}
Also used : Partition(io.mycat.Partition) RangeVariable(io.mycat.RangeVariable) MetadataManager(io.mycat.MetadataManager) ShardingTableConfig(io.mycat.config.ShardingTableConfig) CustomRuleFunction(io.mycat.router.CustomRuleFunction) ImmutableMap(com.google.common.collect.ImmutableMap) Map(java.util.Map) ShardingTable(io.mycat.calcite.table.ShardingTable) Test(org.junit.Test)

Aggregations

CustomRuleFunction (io.mycat.router.CustomRuleFunction)19 ShardingTable (io.mycat.calcite.table.ShardingTable)14 MetadataManager (io.mycat.MetadataManager)12 ShardingTableConfig (io.mycat.config.ShardingTableConfig)12 Test (org.junit.Test)12 Partition (io.mycat.Partition)11 RangeVariable (io.mycat.RangeVariable)11 Map (java.util.Map)10 ImmutableMap (com.google.common.collect.ImmutableMap)5 ImmutableList (com.google.common.collect.ImmutableList)3 RelColumnOrigin (org.apache.calcite.rel.metadata.RelColumnOrigin)3 NormalTable (io.mycat.calcite.table.NormalTable)2 ShardingTableHandler (io.mycat.router.ShardingTableHandler)2 LocalDate (java.time.LocalDate)2 java.util (java.util)2 Collectors (java.util.stream.Collectors)2 SQLUtils (com.alibaba.druid.sql.SQLUtils)1 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)1 SQLMethodInvokeExpr (com.alibaba.druid.sql.ast.expr.SQLMethodInvokeExpr)1 SQLExprParser (com.alibaba.druid.sql.parser.SQLExprParser)1