You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2023/02/07 12:53:22 UTC
[shardingsphere] branch master updated: Fix Migration not support PostgreSQL json type (#24026)
This is an automated email from the ASF dual-hosted git repository.
zhonghongsheng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 5cba140daf6 Fix Migration not support PostgreSQL json type (#24026)
5cba140daf6 is described below
commit 5cba140daf61f3fa8d3c118a429c4bd04b8f0ef1
Author: Xinze Guo <10...@users.noreply.github.com>
AuthorDate: Tue Feb 7 20:53:09 2023 +0800
Fix Migration not support PostgreSQL json type (#24026)
* Fix Migration not support PostgreSQL json type
* Fix unit test
* Add jsonb type
* Add jsonb type and fix ci
* Fix unique key ci error
* Fix name
---
.../PostgreSQLJdbcQueryPropertiesExtension.java | 1 +
...PostgreSQLJdbcQueryPropertiesExtensionTest.java | 3 ++-
.../pipeline/cases/base/PipelineBaseE2EIT.java | 12 ++++++---
.../cases/task/PostgreSQLIncrementTask.java | 15 +++++------
.../framework/helper/PipelineCaseHelper.java | 29 ++++++++++++++++------
.../resources/env/scenario/general/postgresql.xml | 2 +-
6 files changed, 42 insertions(+), 20 deletions(-)
diff --git a/kernel/data-pipeline/dialect/postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtension.java b/kernel/data-pipeline/dialect/postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtension.java
index 96e639dde87..e555251a39b 100644
--- a/kernel/data-pipeline/dialect/postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtension.java
+++ b/kernel/data-pipeline/dialect/postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtension.java
@@ -30,6 +30,7 @@ public final class PostgreSQLJdbcQueryPropertiesExtension implements JdbcQueryPr
@Override
public Properties extendQueryProperties() {
+ queryProps.setProperty("stringtype", "unspecified");
return queryProps;
}
diff --git a/kernel/data-pipeline/dialect/postgresql/src/test/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtensionTest.java b/kernel/data-pipeline/dialect/postgresql/src/test/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtensionTest.java
index 697a10ccf4c..6777ab01d3b 100644
--- a/kernel/data-pipeline/dialect/postgresql/src/test/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtensionTest.java
+++ b/kernel/data-pipeline/dialect/postgresql/src/test/java/org/apache/shardingsphere/data/pipeline/postgresql/datasource/PostgreSQLJdbcQueryPropertiesExtensionTest.java
@@ -26,6 +26,7 @@ import java.util.Optional;
import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.CoreMatchers.instanceOf;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
public final class PostgreSQLJdbcQueryPropertiesExtensionTest {
@@ -40,6 +41,6 @@ public final class PostgreSQLJdbcQueryPropertiesExtensionTest {
private void assertExtension(final JdbcQueryPropertiesExtension actual) {
assertThat(actual, instanceOf(PostgreSQLJdbcQueryPropertiesExtension.class));
assertThat(actual.getType(), equalTo("PostgreSQL"));
- assertTrue(actual.extendQueryProperties().isEmpty());
+ assertFalse(actual.extendQueryProperties().isEmpty());
}
}
diff --git a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/base/PipelineBaseE2EIT.java b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/base/PipelineBaseE2EIT.java
index b42a3e7bb59..311d40e4337 100644
--- a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/base/PipelineBaseE2EIT.java
+++ b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/base/PipelineBaseE2EIT.java
@@ -201,9 +201,14 @@ public abstract class PipelineBaseE2EIT {
}
protected String appendExtraParam(final String jdbcUrl) {
- return DatabaseTypeUtil.isMySQL(getDatabaseType())
- ? new JdbcUrlAppender().appendQueryProperties(jdbcUrl, PropertiesBuilder.build(new Property("rewriteBatchedStatements", Boolean.TRUE.toString())))
- : jdbcUrl;
+ String result = jdbcUrl;
+ if (DatabaseTypeUtil.isMySQL(getDatabaseType())) {
+ result = new JdbcUrlAppender().appendQueryProperties(jdbcUrl, PropertiesBuilder.build(new Property("rewriteBatchedStatements", Boolean.TRUE.toString())));
+ }
+ if (DatabaseTypeUtil.isPostgreSQL(getDatabaseType()) || DatabaseTypeUtil.isOpenGauss(getDatabaseType())) {
+ result = new JdbcUrlAppender().appendQueryProperties(jdbcUrl, PropertiesBuilder.build(new Property("stringtype", "unspecified")));
+ }
+ return result;
}
protected String getActualJdbcUrlTemplate(final String databaseName, final boolean isInContainer, final int storageContainerIndex) {
@@ -355,6 +360,7 @@ public abstract class PipelineBaseE2EIT {
proxyExecuteWithLog("REFRESH TABLE METADATA", 2);
String countSQL = Strings.isNullOrEmpty(schema) ? "SELECT COUNT(*) as count FROM t_order" : String.format("SELECT COUNT(*) as count FROM %s.t_order", schema);
Map<String, Object> actual = queryForListWithLog(countSQL).get(0);
+ log.info("actual count {}", actual.get("count"));
assertTrue("actual count " + actual.get("count"), Integer.parseInt(actual.get("count").toString()) > tableInitRows);
}
}
diff --git a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/task/PostgreSQLIncrementTask.java b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/task/PostgreSQLIncrementTask.java
index 03fcf985a1c..beffecb7f28 100644
--- a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/task/PostgreSQLIncrementTask.java
+++ b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/cases/task/PostgreSQLIncrementTask.java
@@ -73,8 +73,9 @@ public final class PostgreSQLIncrementTask extends BaseIncrementTask {
private Object insertOrder() {
ThreadLocalRandom random = ThreadLocalRandom.current();
String status = 0 == random.nextInt() % 2 ? null : "中文测试";
- Object[] orderInsertDate = new Object[]{KEY_GENERATE_ALGORITHM.generateKey(), random.nextInt(0, 6), status};
- String insertSQL = String.format("INSERT INTO %s (order_id,user_id,status) VALUES (?, ?, ?)", getTableNameWithSchema(orderTableName));
+ Object[] orderInsertDate = new Object[]{KEY_GENERATE_ALGORITHM.generateKey(), random.nextInt(0, 6), status, PipelineCaseHelper.generateJsonString(4, true),
+ PipelineCaseHelper.generateJsonString(4, false)};
+ String insertSQL = String.format("INSERT INTO %s (order_id,user_id,status,t_json,t_jsonb) VALUES (?, ?, ?, ?, ?)", getTableNameWithSchema(orderTableName));
log.info("insert order sql:{}", insertSQL);
DataSourceExecuteUtil.execute(dataSource, insertSQL, orderInsertDate);
return orderInsertDate[0];
@@ -84,15 +85,15 @@ public final class PostgreSQLIncrementTask extends BaseIncrementTask {
ThreadLocalRandom random = ThreadLocalRandom.current();
String status = 0 == random.nextInt() % 2 ? null : "NOT-NULL";
Object[] orderInsertItemDate = new Object[]{KEY_GENERATE_ALGORITHM.generateKey(), PipelineCaseHelper.generateSnowflakeKey(), random.nextInt(0, 6), status};
- String insertSql = "INSERT INTO %s(item_id,order_id,user_id,status) VALUES(?,?,?,?)";
- DataSourceExecuteUtil.execute(dataSource, String.format(insertSql, getTableNameWithSchema("t_order_item")), orderInsertItemDate);
+ String insertSql = String.format("INSERT INTO %s(item_id,order_id,user_id,status) VALUES(?,?,?,?)", getTableNameWithSchema("t_order_item"));
+ DataSourceExecuteUtil.execute(dataSource, insertSql, orderInsertItemDate);
return orderInsertItemDate[0];
}
private void updateOrderByPrimaryKey(final Object primaryKey) {
- Object[] updateData = {"updated" + Instant.now().getEpochSecond(), primaryKey};
- String updateSql = String.format("UPDATE %s SET status = ? WHERE order_id = ?", getTableNameWithSchema(orderTableName));
- DataSourceExecuteUtil.execute(dataSource, String.format(updateSql, getTableNameWithSchema("t_order_item")), updateData);
+ Object[] updateData = {"updated" + Instant.now().getEpochSecond(), PipelineCaseHelper.generateJsonString(4, true), PipelineCaseHelper.generateJsonString(4, false), primaryKey};
+ String updateSql = String.format("UPDATE %s SET status = ?, t_json = ?, t_jsonb = ? WHERE order_id = ?", getTableNameWithSchema(orderTableName));
+ DataSourceExecuteUtil.execute(dataSource, updateSql, updateData);
}
private String getTableNameWithSchema(final String tableName) {
diff --git a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/framework/helper/PipelineCaseHelper.java b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/framework/helper/PipelineCaseHelper.java
index a6517644502..66e2f1dbbb6 100644
--- a/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/framework/helper/PipelineCaseHelper.java
+++ b/test/e2e/pipeline/src/test/java/org/apache/shardingsphere/test/e2e/data/pipeline/framework/helper/PipelineCaseHelper.java
@@ -28,12 +28,12 @@ import org.apache.shardingsphere.test.e2e.data.pipeline.util.AutoIncrementKeyGen
import java.math.BigDecimal;
import java.sql.Connection;
-import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
-import java.sql.Time;
import java.sql.Timestamp;
+import java.time.LocalDate;
import java.time.LocalDateTime;
+import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.Year;
import java.util.ArrayList;
@@ -80,14 +80,13 @@ public final class PipelineCaseHelper {
Object[] addObjs = {orderId, userId, generateString(6) + "", randomInt, randomInt, randomInt,
randomUnsignedInt, randomUnsignedInt, randomUnsignedInt, randomUnsignedInt, generateFloat(), generateDouble(-100000000, 100000000),
BigDecimal.valueOf(generateDouble(1, 100)), now, now, now.toLocalDate(), now.toLocalTime(), Year.now().getValue(), "1", "t", "e", "s", "t", generateString(2),
- emojiText, generateString(1), "1", "2", generateJsonString(32)};
+ emojiText, generateString(1), "1", "2", generateJsonString(32, false)};
orderData.add(addObjs);
} else {
- long currentTimeMillis = System.currentTimeMillis();
orderData.add(new Object[]{orderId, userId, generateString(6), randomInt,
BigDecimal.valueOf(generateDouble(1, 100)), true, "bytea".getBytes(), generateString(2), generateString(2), generateFloat(), generateDouble(0, 1000),
- generateJsonString(8), generateJsonString(12), emojiText, new Date(currentTimeMillis), new Time(currentTimeMillis), Timestamp.valueOf(LocalDateTime.now()),
- OffsetDateTime.now()});
+ generateJsonString(8, false), generateJsonString(12, true), emojiText, LocalDate.now(),
+ LocalTime.now(), Timestamp.valueOf(LocalDateTime.now()), OffsetDateTime.now()});
}
orderItemData.add(new Object[]{orderItemKeyGenerate.generateKey(), orderId, userId, "SUCCESS"});
}
@@ -102,8 +101,22 @@ public final class PipelineCaseHelper {
return RandomStringUtils.randomAlphabetic(strLength);
}
- private static String generateJsonString(final int strLength) {
- return String.format("{\"test\":\"%s\"}", generateString(strLength));
+ /**
+ * Generate json string.
+ *
+ * @param useUnicodeCharacter use unicode character
+ * @param length length
+ * @return json string
+ */
+ public static String generateJsonString(final int length, final boolean useUnicodeCharacter) {
+ String value;
+ if (useUnicodeCharacter && length > 1) {
+ // TODO need support unicode
+ value = generateString(length);
+ } else {
+ value = generateString(length);
+ }
+ return String.format("{\"test\":\"%s\"}", value);
}
private static float generateFloat() {
diff --git a/test/e2e/pipeline/src/test/resources/env/scenario/general/postgresql.xml b/test/e2e/pipeline/src/test/resources/env/scenario/general/postgresql.xml
index d73c4343d53..90cdfdf27a7 100644
--- a/test/e2e/pipeline/src/test/resources/env/scenario/general/postgresql.xml
+++ b/test/e2e/pipeline/src/test/resources/env/scenario/general/postgresql.xml
@@ -73,7 +73,7 @@
t_time,
t_timestmap,
t_timestamptz)
- VALUES (?,?,?,?,?,?,?,?,?,?,?,?::json,?::jsonb,?,?,?,?,?)
+ VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
</full-insert-order>
<full-insert-order-item>