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 <[email protected]> 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(", "); + } } }
