Examples with ShardingTable io.mycat.calcite.table.ShardingTable used on opensource projects

Example 26 with ShardingTable

use of io.mycat.calcite.table.ShardingTable in project Mycat2 by MyCATApache.

the class YYYYDDFunctionTest method testYYYYDD.

/**
 *     https://help.aliyun.com/document_detail/71337.html
 */
@Test
public void testYYYYDD() {
    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 YYYYDD(traveldate) tbpartition by YYYYDD(traveldate) tbpartitions 92 dbpartitions 8;");
    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_${index}\",\n" + "\t\t\t\t\t\"tableNum\":\"92\",\n" + "\t\t\t\t\t\"tableMethod\":\"YYYYDD(traveldate)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"YYYYDD(traveldate)\"\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("traveldate", (new RangeVariable("traveldate", RangeVariableType.EQUAL, LocalDate.of(2020, 6, 5)))));
    String s = calculate.toString();
    Assert.assertTrue(s.contains("[{targetName='c0', schemaName='db1_5', tableName='sharding_533', index=533, dbIndex=5, tableIndex=73}]"));
    Assert.assertEquals(736, shardingFuntion.calculate(Collections.emptyMap()).size());
    System.out.println();
}

Example 27 with ShardingTable

use of io.mycat.calcite.table.ShardingTable in project Mycat2 by MyCATApache.

the class YYYYDDFunctionTest method testYYYYDDHashId.

@Test
public void testYYYYDDHashId() {
    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 YYYYDD(traveldate) tbpartition by mod_hash(id) tbpartitions 8 dbpartitions 366;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"dbNum\":\"366\",\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"2\",\n" + "\t\t\t\t\t\"tableMethod\":\"hash(id)\",\n" + "\t\t\t\t\t\"storeNum\":1,\n" + "\t\t\t\t\t\"dbMethod\":\"YYYYDD(traveldate)\"\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("traveldate", (new RangeVariable("traveldate", RangeVariableType.EQUAL, LocalDate.of(2020, 6, 5)))));
    String s = calculate.toString();
    Assert.assertTrue(s.contains("[{targetName='c0', schemaName='db1_157', tableName='sharding_0', index=314, dbIndex=157, tableIndex=0}, {targetName='c0', schemaName='db1_157', tableName='sharding_1', index=315, dbIndex=157, tableIndex=1}]"));
    Assert.assertEquals(732, shardingFuntion.calculate(Collections.emptyMap()).size());
    System.out.println();
}

Example 28 with ShardingTable

use of io.mycat.calcite.table.ShardingTable in project Mycat2 by MyCATApache.

the class HashFunctionTest method testHashTableId.

@Test
public void testHashTableId() {
    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" + " tbpartition by mod_hash(id) tbpartitions 2;");
    mainSharding.setFunction(ShardingFunction.builder().properties(JsonUtil.from("{\n" + "\t\t\t\t\t\"mappingFormat\":\"c${targetIndex}/db1_${dbIndex}/sharding_${tableIndex}\",\n" + "\t\t\t\t\t\"tableNum\":\"2\",\n" + "\t\t\t\t\t\"tableMethod\":\"hash(id)\",\n" + "\t\t\t\t\t\"storeNum\":1,\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))));
    Assert.assertEquals(1, calculate.size());
    Assert.assertEquals(2, shardingFuntion.calculate(Collections.emptyMap()).size());
    System.out.println();
}

Example 29 with ShardingTable

use of io.mycat.calcite.table.ShardingTable in project Mycat2 by MyCATApache.

the class HashFunctionTest method testHashDbId.

@Test
public void testHashDbId() {
    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);");
    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\"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();
    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_1', tableName='sharding_0', index=1, dbIndex=1, tableIndex=0}]", s1);
    System.out.println();
}

Example 30 with ShardingTable

use of io.mycat.calcite.table.ShardingTable in project Mycat2 by MyCATApache.

the class StatisticCenterTest method testSharding.

@Test
@Disabled
@Ignore
public void testSharding() throws Exception {
    DrdsSqlCompiler drds = getDrds();
    String schemaName = "db1";
    String tableName = "sharding";
    MetadataManager metadataManager = getMetadataManager();
    TableHandler tableHandler = metadataManager.getTable(schemaName, tableName);
    if (tableHandler == null) {
        return;
    }
    tableHandler.createPhysicalTables();
    ShardingTable table = (ShardingTable) tableHandler;
    List<Partition> backends = table.getBackends();
    Partition c0 = backends.get(0);
    Partition c1 = backends.get(1);
    try (DefaultConnection connection = jdbcManager.getConnection("prototype")) {
        deleteData(connection.getRawConnection(), schemaName, tableName);
        JdbcUtils.execute(connection.getRawConnection(), "insert db1.sharding(id) values(?)", Arrays.asList(1));
    }
    Double count = statisticCenter.computeTableRowCount(tableHandler);
    Assert.assertTrue(count.equals(count));
    statisticCenter.fetchTableRowCount(tableHandler);
    try (DefaultConnection connection = jdbcManager.getConnection("prototype")) {
        List<Map<String, Object>> maps = JdbcUtils.executeQuery(connection.getRawConnection(), "select * from mycat.analyze_table ", Arrays.asList());
        Assert.assertTrue(maps.toString().contains(tableName));
    }
}