You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hugegraph.apache.org by je...@apache.org on 2022/06/06 03:31:17 UTC
[incubator-hugegraph] 03/05: fix mysql/pgsql
This is an automated email from the ASF dual-hosted git repository.
jermy pushed a commit to branch schema-update-bug-rebase
in repository https://gitbox.apache.org/repos/asf/incubator-hugegraph.git
commit 73c7fb810d47c0b39cd5a94bbd07fc1790703593
Author: Zhangmei Li <li...@baidu.com>
AuthorDate: Tue Mar 1 18:15:24 2022 +0800
fix mysql/pgsql
Change-Id: I397fd5d300c563cd9298b76ab336bff78f2564ea
---
.../hugegraph/backend/store/mysql/MysqlTable.java | 201 +++++++++++++++------
.../backend/store/postgresql/PostgresqlTable.java | 47 +++--
2 files changed, 183 insertions(+), 65 deletions(-)
diff --git a/hugegraph-mysql/src/main/java/com/baidu/hugegraph/backend/store/mysql/MysqlTable.java b/hugegraph-mysql/src/main/java/com/baidu/hugegraph/backend/store/mysql/MysqlTable.java
index 727513334..69efd96bd 100644
--- a/hugegraph-mysql/src/main/java/com/baidu/hugegraph/backend/store/mysql/MysqlTable.java
+++ b/hugegraph-mysql/src/main/java/com/baidu/hugegraph/backend/store/mysql/MysqlTable.java
@@ -63,10 +63,12 @@ public abstract class MysqlTable
private static final String DECIMAL = "DECIMAL";
- // The template for insert and delete statements
+ // The template cache for insert and delete statements
private String insertTemplate;
private String insertTemplateTtl;
private String deleteTemplate;
+ private String updateIfPresentTemplate;
+ private String updateIfAbsentTemplate;
private final MysqlShardSplitter shardSplitter;
@@ -75,6 +77,9 @@ public abstract class MysqlTable
this.insertTemplate = null;
this.insertTemplateTtl = null;
this.deleteTemplate = null;
+ this.updateIfPresentTemplate = null;
+ this.updateIfAbsentTemplate = null;
+
this.shardSplitter = new MysqlShardSplitter(this.table());
}
@@ -178,31 +183,56 @@ public abstract class MysqlTable
return ImmutableList.of(id.asObject());
}
- protected String buildInsertTemplate(MysqlBackendEntry.Row entry) {
- if (entry.ttl() != 0L) {
- return this.buildInsertTemplateWithTtl(entry);
- }
- if (this.insertTemplate != null) {
- return this.insertTemplate;
+ protected void insertOrUpdate(Session session, String template,
+ List<?> params) {
+ PreparedStatement insertStmt;
+ try {
+ // Create or get insert prepare statement
+ insertStmt = session.prepareStatement(template);
+ int i = 1;
+ for (Object param : params) {
+ insertStmt.setObject(i++, param);
+ }
+ } catch (SQLException e) {
+ throw new BackendException("Failed to prepare statement '%s' " +
+ "with params: %s", template, params);
}
-
- this.insertTemplate = this.buildInsertTemplateForce(entry);
- return this.insertTemplate;
+ session.add(insertStmt);
}
- protected String buildInsertTemplateWithTtl(MysqlBackendEntry.Row entry) {
- assert entry.ttl() != 0L;
- if (this.insertTemplateTtl != null) {
+ protected final String buildUpdateTemplate(MysqlBackendEntry.Row entry) {
+ if (entry.ttl() != 0L) {
+ if (this.insertTemplateTtl != null) {
+ return this.insertTemplateTtl;
+ }
+
+ this.insertTemplateTtl = this.buildUpdateForcedTemplate(entry);
return this.insertTemplateTtl;
+ } else {
+ if (this.insertTemplate != null) {
+ return this.insertTemplate;
+ }
+
+ this.insertTemplate = this.buildUpdateForcedTemplate(entry);
+ return this.insertTemplate;
}
+ }
- this.insertTemplateTtl = this.buildInsertTemplateForce(entry);
- return this.insertTemplateTtl;
+ protected String buildUpdateForcedTemplate(MysqlBackendEntry.Row entry) {
+ StringBuilder insert = new StringBuilder();
+ insert.append("REPLACE INTO ").append(this.table());
+ return this.buildInsertKeys(insert, entry);
}
- protected String buildInsertTemplateForce(MysqlBackendEntry.Row entry) {
+ protected String buildUpdateIfAbsentTemplate(MysqlBackendEntry.Row entry) {
StringBuilder insert = new StringBuilder();
- insert.append("REPLACE INTO ").append(this.table()).append(" (");
+ insert.append("INSERT IGNORE INTO ").append(this.table());
+ return this.buildInsertKeys(insert, entry);
+ }
+
+ protected String buildInsertKeys(StringBuilder insert,
+ MysqlBackendEntry.Row entry) {
+ insert.append(" (");
int i = 0;
int n = entry.columns().size();
@@ -213,7 +243,7 @@ public abstract class MysqlTable
}
}
insert.append(") VALUES (");
- // Fill with '?'
+ // Fill with '?' as a placeholder
for (i = 0; i < n; i++) {
insert.append("?");
if (i != n - 1) {
@@ -225,11 +255,76 @@ public abstract class MysqlTable
return insert.toString();
}
- protected String buildDeleteTemplate(List<HugeKeys> idNames) {
- if (this.deleteTemplate != null) {
- return this.deleteTemplate;
+ protected List<?> buildUpdateForcedParams(MysqlBackendEntry.Row entry) {
+ return this.buildColumnsParams(entry);
+ }
+
+ protected List<?> buildUpdateIfAbsentParams(MysqlBackendEntry.Row entry) {
+ return this.buildColumnsParams(entry);
+ }
+
+ protected List<Object> buildColumnsParams(MysqlBackendEntry.Row entry) {
+ return this.buildColumnsParams(entry, null);
+ }
+
+ protected List<Object> buildColumnsParams(MysqlBackendEntry.Row entry,
+ List<HugeKeys> skipKeys) {
+ List<Object> objects = new ArrayList<>();
+ for (Map.Entry<HugeKeys, Object> e : entry.columns().entrySet()) {
+ HugeKeys key = e.getKey();
+ Object value = e.getValue();
+ if (skipKeys != null && skipKeys.contains(key)) {
+ continue;
+ }
+ String type = this.tableDefine().columns().get(key);
+ if (type.startsWith(DECIMAL)) {
+ value = new BigDecimal(value.toString());
+ }
+ objects.add(value);
}
+ return objects;
+ }
+
+ protected String buildUpdateIfPresentTemplate(MysqlBackendEntry.Row entry) {
+ StringBuilder update = new StringBuilder();
+ update.append("UPDATE ").append(this.table());
+ update.append(" SET ");
+
+ List<HugeKeys> idNames = this.idColumnName();
+
+ int i = 0;
+ int size = entry.columns().size();
+ for (HugeKeys key : entry.columns().keySet()) {
+ if (idNames.contains(key)) {
+ size--;
+ continue;
+ }
+ update.append(formatKey(key));
+ update.append("=?");
+ if (++i != size) {
+ update.append(", ");
+ }
+ }
+
+ WhereBuilder where = this.newWhereBuilder();
+ where.and(formatKeys(idNames), "=");
+ update.append(where.build());
+
+ return update.toString();
+ }
+
+ protected List<?> buildUpdateIfPresentParams(MysqlBackendEntry.Row entry) {
+ List<HugeKeys> idNames = this.idColumnName();
+ List<Object> params = this.buildColumnsParams(entry, idNames);
+
+ List<Long> idValues = this.idColumnValue(entry);
+ params.addAll(idValues);
+
+ return params;
+ }
+
+ protected String buildDeleteTemplate(List<HugeKeys> idNames) {
StringBuilder delete = new StringBuilder();
delete.append("DELETE FROM ").append(this.table());
this.appendPartition(delete);
@@ -238,8 +333,7 @@ public abstract class MysqlTable
where.and(formatKeys(idNames), "=");
delete.append(where.build());
- this.deleteTemplate = delete.toString();
- return this.deleteTemplate;
+ return delete.toString();
}
protected String buildDropTemplate() {
@@ -259,40 +353,21 @@ public abstract class MysqlTable
*/
@Override
public void insert(Session session, MysqlBackendEntry.Row entry) {
- String template = this.buildInsertTemplate(entry);
-
- PreparedStatement insertStmt;
- try {
- // Create or get insert prepare statement
- insertStmt = session.prepareStatement(template);
- int i = 1;
- for (Object object : this.buildInsertObjects(entry)) {
- insertStmt.setObject(i++, object);
- }
- } catch (SQLException e) {
- throw new BackendException("Failed to prepare statement '%s'" +
- "for entry: %s", template, entry);
- }
- session.add(insertStmt);
- }
-
- protected List<Object> buildInsertObjects(MysqlBackendEntry.Row entry) {
- List<Object> objects = new ArrayList<>();
- for (Map.Entry<HugeKeys, Object> e : entry.columns().entrySet()) {
- Object value = e.getValue();
- String type = this.tableDefine().columns().get(e.getKey());
- if (type.startsWith(DECIMAL)) {
- value = new BigDecimal(value.toString());
- }
- objects.add(value);
- }
- return objects;
+ String template = this.buildUpdateTemplate(entry);
+ List<?> params = this.buildUpdateForcedParams(entry);
+ this.insertOrUpdate(session, template, params);
}
@Override
public void delete(Session session, MysqlBackendEntry.Row entry) {
List<HugeKeys> idNames = this.idColumnName();
- String template = this.buildDeleteTemplate(idNames);
+
+ String template = this.deleteTemplate;
+ if (template == null) {
+ template = this.buildDeleteTemplate(idNames);
+ this.deleteTemplate = template;
+ }
+
PreparedStatement deleteStmt;
try {
deleteStmt = session.prepareStatement(template);
@@ -331,6 +406,28 @@ public abstract class MysqlTable
this.delete(session, entry);
}
+ @Override
+ public void updateIfPresent(Session session, MysqlBackendEntry.Row entry) {
+ String template = this.updateIfPresentTemplate;
+ if (template == null) {
+ template = this.buildUpdateIfPresentTemplate(entry);
+ this.updateIfPresentTemplate = template;
+ }
+ List<?> params = this.buildUpdateIfPresentParams(entry);
+ this.insertOrUpdate(session, template, params);
+ }
+
+ @Override
+ public void updateIfAbsent(Session session, MysqlBackendEntry.Row entry) {
+ String template = this.updateIfAbsentTemplate;
+ if (template == null) {
+ template = this.buildUpdateIfAbsentTemplate(entry);
+ this.updateIfAbsentTemplate = template;
+ }
+ List<?> params = this.buildUpdateIfAbsentParams(entry);
+ this.insertOrUpdate(session, template, params);
+ }
+
@Override
public boolean queryExist(Session session, MysqlBackendEntry.Row entry) {
Query query = new IdQuery.OneIdQuery(HugeType.UNKNOWN, entry.id());
diff --git a/hugegraph-postgresql/src/main/java/com/baidu/hugegraph/backend/store/postgresql/PostgresqlTable.java b/hugegraph-postgresql/src/main/java/com/baidu/hugegraph/backend/store/postgresql/PostgresqlTable.java
index f811274c4..e3b0bea0b 100644
--- a/hugegraph-postgresql/src/main/java/com/baidu/hugegraph/backend/store/postgresql/PostgresqlTable.java
+++ b/hugegraph-postgresql/src/main/java/com/baidu/hugegraph/backend/store/postgresql/PostgresqlTable.java
@@ -24,6 +24,7 @@ import java.util.List;
import org.apache.logging.log4j.util.Strings;
+import com.baidu.hugegraph.backend.serializer.TableBackendEntry.Row;
import com.baidu.hugegraph.backend.store.mysql.MysqlBackendEntry;
import com.baidu.hugegraph.backend.store.mysql.MysqlSessions.Session;
import com.baidu.hugegraph.backend.store.mysql.MysqlTable;
@@ -54,15 +55,31 @@ public abstract class PostgresqlTable extends MysqlTable {
}
@Override
- protected List<Object> buildInsertObjects(MysqlBackendEntry.Row entry) {
- List<Object> objects = new ArrayList<>();
- objects.addAll(super.buildInsertObjects(entry));
- objects.addAll(super.buildInsertObjects(entry));
- return objects;
+ protected String buildUpdateForcedTemplate(MysqlBackendEntry.Row entry) {
+ return this.buildInsertKeys(entry, false);
}
@Override
- protected String buildInsertTemplateForce(MysqlBackendEntry.Row entry) {
+ protected List<?> buildUpdateForcedParams(MysqlBackendEntry.Row entry) {
+ List<Object> params = new ArrayList<>();
+ List<Object> allColumns = this.buildColumnsParams(entry);
+ params.addAll(allColumns);
+ params.addAll(allColumns);
+ return params;
+ }
+
+ @Override
+ protected String buildUpdateIfAbsentTemplate(Row entry) {
+ return this.buildInsertKeys(entry, true);
+ }
+
+ @Override
+ protected List<?> buildUpdateIfAbsentParams(MysqlBackendEntry.Row entry) {
+ return this.buildColumnsParams(entry);
+ }
+
+ protected String buildInsertKeys(MysqlBackendEntry.Row entry,
+ boolean ignoreConflicts) {
StringBuilder insert = new StringBuilder();
insert.append("INSERT INTO ").append(this.table()).append(" (");
@@ -95,13 +112,17 @@ public abstract class PostgresqlTable extends MysqlTable {
}
insert.append(")");
- i = 0;
- size = entry.columns().keySet().size();
- insert.append(" DO UPDATE SET ");
- for (HugeKeys key : entry.columns().keySet()) {
- insert.append(formatKey(key)).append(" = ?");
- if (++i != size) {
- insert.append(", ");
+ if (ignoreConflicts) {
+ insert.append(" DO NOTHING");
+ } else {
+ i = 0;
+ size = entry.columns().keySet().size();
+ insert.append(" DO UPDATE SET ");
+ for (HugeKeys key : entry.columns().keySet()) {
+ insert.append(formatKey(key)).append(" = ?");
+ if (++i != size) {
+ insert.append(", ");
+ }
}
}