linghengqian opened a new issue #13069: URL: https://github.com/apache/shardingsphere/issues/13069
## Feature Request **For English only**, other languages will not accept. Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot make decision by current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Is your feature request related to a problem? No. ### Describe the feature you would like. In org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.0.0-beta, I noticed that the document is for AUTO_INTERVAL, and the timestamp format is fixed as yyyy-MM-dd HH:mm:ss.  I described the process of matching the fields of the database suffix based on the source code. The following is the unit test code of java. In my opinion, the program got "public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue);" of "org.apache.shardingsphere.sharding.algorithm.sharding.datetime.AutoIntervalShardingAlgorithm" TableNameSuffix. And get "public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Comparable<?>> shardingValue);" The internal firstPartition and lastPartition go through the same process. ```java @Test public void test14() { //shardingSeconds is defined by application.properties long shardingSeconds = 604800L; //DATE_TIME_FORMAT由org.apache.shardingsphere.sharding.algorithm.sharding.datetime.AutoIntervalShardingAlgorithm定义 DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); //dateTimeLower is defined by public void init(); LocalDateTime dateTimeLower = LocalDateTime.parse("2020-01-01 00:00:00", DATE_TIME_FORMAT); System.out.println("dateTimeLower is----------------->" + dateTimeLower); //autoTablesAmount is defined by public void init(); and its internal reference private long parseDate(final Comparable<?> shardingValue); int autoTablesAmount = (int) (Math.ceil(Duration.between(dateTimeLower, LocalDateTime.parse("2022-12-28 00:00:00", DATE_TIME_FORMAT)).toMillis() / 1000 / shardingSeconds) + 2); System.out.println("autoTablesAmount is---------------------------->" + autoTablesAmount); //"2021-10-11 00:00:00" is the incoming fragmentation judgment value LocalDateTime dateValue = LocalDateTime.parse("2021-10-11 00:00:00", DATE_TIME_FORMAT); System.out.println("dateValue is-------------------->" + dateValue); //shardingValue由private long parseDate(final Comparable<?> shardingValue);定义 long shardingValue = Duration.between(dateTimeLower, dateValue).toMillis() / 1000; System.out.println("shardingValue is------------------->" + shardingValue); //tableNameSuffix is defined by private int doSharding(final long shardingValue); String position = new DecimalFormat("0.00").format((float) shardingValue / shardingSeconds); System.out.println("position is-------------------------->" + position); int tableNameSuffix = Math.min(Math.max(0, (int) Math.ceil(Float.parseFloat(position))), autoTablesAmount - 1); System.out.println("tableNameSuffix is--------------->" + tableNameSuffix); //private int getFirstPartition(final Range<Comparable<?>> valueRange); and private int getLastPartition(final Range<Comparable<?>> valueRange); also go through the above process } ``` The tableNameSuffix obtained from this set is normally 1, 2, 3,..., N. In this case, I thought I could make some changes, so that its shardingValue can be passed in the time stamp format yyyy-MM-dd HH:mm:ss.SSS Java Timestamp or JAVA LocalDateTime. For org.apache.shardingsphere.sharding.algorithm.sharding.datetime.AutoIntervalShardingAlgorithm, ```java private long parseDate(final Comparable<?> shardingValue) { LocalDateTime dateValue = LocalDateTime.parse(shardingValue.toString(), DATE_TIME_FORMAT); return Duration.between(dateTimeLower, dateValue).toMillis() / 1000; } ``` can be changed to ```java private long parseDate(final Comparable<?> shardingValue) { LocalDateTime dateValue = LocalDateTime.from(DATE_TIME_FORMAT.parse(shardingValue.toString(), new ParsePosition(0))); return Duration.between(dateTimeLower, dateValue).toMillis() / 1000; } ``` And for ```java private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); ``` can be changed to ``` private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss[.S]"); ``` In this way, although the datetime-lower and datetime-upper defined in application.properties are still in the yyyy-MM-dd HH:mm:ss format, the time passed in the fragmentation judgment value can contain any number of milliseconds. The following is my preliminary practice. An embarrassing situation is that 5.0.0-bata has a bug that prevents me from using the init() function ( https://github.com/apache/shardingsphere/pull/11931 ), so I made some strange changes to my test code.I am directly facing to change the source code. ```java import com.google.common.base.Preconditions; import com.google.common.collect.Range; import lombok.Getter; import lombok.Setter; import org.apache.shardingsphere.infra.config.exception.ShardingSphereConfigurationException; import org.apache.shardingsphere.sharding.api.sharding.ShardingAutoTableAlgorithm; import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm; import java.text.DecimalFormat; import java.text.ParsePosition; import java.time.Duration; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.time.format.DateTimeParseException; import java.util.Arrays; import java.util.Collection; import java.util.LinkedHashSet; import java.util.Properties; @Getter public final class ShardingTestAlgorithm implements StandardShardingAlgorithm<Comparable<?>>, ShardingAutoTableAlgorithm { private static final String DATE_TIME_LOWER_KEY = "datetime-lower"; private static final String DATE_TIME_UPPER_KEY = "datetime-upper"; private static final String SHARDING_SECONDS_KEY = "sharding-seconds"; // private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); private static final DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss[.S]"); @Setter private Properties props = new Properties(); private LocalDateTime dateTimeLower; private long shardingSeconds; private int autoTablesAmount; @Override public void init() { dateTimeLower = getDateTime(DATE_TIME_LOWER_KEY); shardingSeconds = getShardingSeconds(); autoTablesAmount = (int) (Math.ceil(parseDate(props.getProperty(DATE_TIME_UPPER_KEY)) / shardingSeconds) + 2); } private LocalDateTime getDateTime(final String dateTimeKey) { String value = props.getProperty(dateTimeKey); Preconditions.checkNotNull(value, "%s cannot be null.", dateTimeKey); try { return LocalDateTime.parse(value, DATE_TIME_FORMAT); } catch (final DateTimeParseException ex) { // throw new ShardingSphereConfigurationException("Invalid %s, datetime pattern should be `yyyy-MM-dd HH:mm:ss`, value is `%s`", dateTimeKey, value); throw new ShardingSphereConfigurationException("Invalid %s, datetime pattern should be `yyyy-MM-dd HH:mm:ss[.S]`, value is `%s`", dateTimeKey, value); } } private long getShardingSeconds() { Preconditions.checkArgument(props.containsKey(SHARDING_SECONDS_KEY), "%s cannot be null.", SHARDING_SECONDS_KEY); return Long.parseLong(props.getProperty(SHARDING_SECONDS_KEY)); } @Override public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) { String tableNameSuffix = String.valueOf(doSharding(parseDate(shardingValue.getValue()))); for (String each : availableTargetNames) { if (each.endsWith(tableNameSuffix)) { return each; } } return null; } @Override public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Comparable<?>> shardingValue) { Collection<String> result = new LinkedHashSet<>(availableTargetNames.size()); int firstPartition = getFirstPartition(shardingValue.getValueRange()); int lastPartition = getLastPartition(shardingValue.getValueRange()); for (int i = firstPartition; i <= lastPartition; i++) { for (String each : availableTargetNames) { if (each.endsWith(String.valueOf(i))) { result.add(each); break; } if (result.size() == availableTargetNames.size()) { return result; } } } return result; } private int doSharding(final long shardingValue) { String position = new DecimalFormat("0.00").format((float) shardingValue / shardingSeconds); return Math.min(Math.max(0, (int) Math.ceil(Float.parseFloat(position))), autoTablesAmount - 1); } private int getFirstPartition(final Range<Comparable<?>> valueRange) { return valueRange.hasLowerBound() ? doSharding(parseDate(valueRange.lowerEndpoint())) : 0; } private int getLastPartition(final Range<Comparable<?>> valueRange) { return valueRange.hasUpperBound() ? doSharding(parseDate(valueRange.upperEndpoint())) : autoTablesAmount - 1; } private long parseDate(final Comparable<?> shardingValue) { // LocalDateTime dateValue = LocalDateTime.parse(shardingValue.toString(), DATE_TIME_FORMAT); LocalDateTime dateValue = LocalDateTime.from(DATE_TIME_FORMAT.parse(shardingValue.toString(), new ParsePosition(0))); return Duration.between(dateTimeLower, dateValue).toMillis() / 1000; } @Override public String getType() { return "AUTO_INTERVAL"; } @Override public Collection<String> getAllPropertyKeys() { return Arrays.asList(DATE_TIME_LOWER_KEY, DATE_TIME_UPPER_KEY, SHARDING_SECONDS_KEY); } } ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
