diff options
author | Stefan Winkler | 2011-12-21 21:22:41 +0000 |
---|---|---|
committer | Stefan Winkler | 2011-12-21 21:22:41 +0000 |
commit | fa59e356884fff3964b78274c1e7a394b18afcc2 (patch) | |
tree | 96acc705885d321fe6c21c36c50f63746979f1cf | |
parent | 4753819a93fc995788de5d22eb02130284824f09 (diff) | |
download | cdo-fa59e356884fff3964b78274c1e7a394b18afcc2.tar.gz cdo-fa59e356884fff3964b78274c1e7a394b18afcc2.tar.xz cdo-fa59e356884fff3964b78274c1e7a394b18afcc2.zip |
[367356] [DB] Reduce amount of update statements for non-audit mode
https://bugs.eclipse.org/bugs/show_bug.cgi?id=367356
2 files changed, 216 insertions, 58 deletions
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/NonAuditListTableMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/NonAuditListTableMapping.java index 3888bedac1..706e7413ff 100644 --- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/NonAuditListTableMapping.java +++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/NonAuditListTableMapping.java @@ -48,6 +48,8 @@ import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.LinkedList; +import java.util.ListIterator; /** * This is a list-to-table mapping optimized for non-audit-mode. It doesn't care about version and has delta support. @@ -84,6 +86,8 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement private String sqlDeleteItem; + private String sqlMassUpdateIndex; + public NonAuditListTableMapping(IMappingStrategy mappingStrategy, EClass eClass, EStructuralFeature feature) { super(mappingStrategy, eClass, feature); @@ -149,6 +153,23 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement builder.append(CDODBSchema.LIST_IDX); builder.append("=? "); //$NON-NLS-1$ sqlUpdateIndex = builder.toString(); + + // ----------- mass update item indexes -------------- + builder = new StringBuilder(); + builder.append("UPDATE "); //$NON-NLS-1$ + builder.append(getTable()); + builder.append(" SET "); //$NON-NLS-1$ + builder.append(CDODBSchema.LIST_IDX); + builder.append("="); //$NON-NLS-1$ + builder.append(CDODBSchema.LIST_IDX); + builder.append("+? WHERE "); //$NON-NLS-1$ + builder.append(CDODBSchema.LIST_REVISION_ID); + builder.append("=? AND "); //$NON-NLS-1$ + builder.append(CDODBSchema.LIST_IDX); + builder.append(" >= ? AND "); //$NON-NLS-1$ + builder.append(CDODBSchema.LIST_IDX); + builder.append(" <= ?"); //$NON-NLS-1$ + sqlMassUpdateIndex = builder.toString(); } @Override @@ -344,62 +365,30 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement } } - /* - * Step 3: move all elements which have to be shifted up or down because of add, remove or move of other elements - * to their proper position. This has to be done in two phases to avoid collisions, as the index has to be unique - */ - int size = manipulations.size(); - - /* Step 3a: shift down */ - for (int i = 0; i < size; i++) + /* now perform deletes and moves ... */ + if (deleteCounter > 0) { - ManipulationElement element = manipulations.get(i); - - if ((element.type == ManipulationConstants.NONE || element.type == ManipulationConstants.SET_VALUE) - && element.sourceIndex > element.destinationIndex) + if (TRACER.isEnabled()) { - if (moveStmt == null) - { - moveStmt = accessor.getStatementCache().getPreparedStatement(sqlUpdateIndex, ReuseProbability.HIGH); - idHandler.setCDOID(moveStmt, 2, id); - } - - moveStmt.setInt(3, element.sourceIndex); // from index - moveStmt.setInt(1, element.destinationIndex); // to index - moveStmt.addBatch(); - moveCounter++; - if (TRACER.isEnabled()) - { - TRACER.format(" - move {0} -> {1} ", element.sourceIndex, element.destinationIndex); //$NON-NLS-1$ - } + TRACER.format("Performing {0} delete operations", deleteCounter); //$NON-NLS-1$ } + + executeBatch(deleteStmt, deleteCounter); } - /* Step 3b: shift up */ - for (int i = size - 1; i >= 0; i--) + if (moveCounter > 0) { - ManipulationElement element = manipulations.get(i); - - if ((element.type == ManipulationConstants.NONE || element.type == ManipulationConstants.SET_VALUE) - && element.sourceIndex < element.destinationIndex) + if (TRACER.isEnabled()) { - if (moveStmt == null) - { - moveStmt = accessor.getStatementCache().getPreparedStatement(sqlUpdateIndex, ReuseProbability.HIGH); - idHandler.setCDOID(moveStmt, 2, id); - } - - moveStmt.setInt(3, element.sourceIndex); // from index - moveStmt.setInt(1, element.destinationIndex); // to index - moveStmt.addBatch(); - moveCounter++; - if (TRACER.isEnabled()) - { - TRACER.format(" - move {0} -> {1} ", element.sourceIndex, element.destinationIndex); //$NON-NLS-1$ - } + TRACER.format("Performing {0} move operations", moveCounter); //$NON-NLS-1$ } + + executeBatch(moveStmt, moveCounter); + moveCounter = 0; } + performMoveOperations(accessor, id); + for (ManipulationElement element : manipulations) { if (element.is(ManipulationConstants.MOVE)) @@ -466,16 +455,6 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement } // finally perform all operations - if (deleteCounter > 0) - { - if (TRACER.isEnabled()) - { - TRACER.format("Performing {0} delete operations", deleteCounter); //$NON-NLS-1$ - } - - executeBatch(deleteStmt, deleteCounter); - } - if (moveCounter > 0) { if (TRACER.isEnabled()) @@ -516,7 +495,12 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement } } - private static void executeBatch(PreparedStatement stmt, int counter) + public static void executeBatch(PreparedStatement stmt, int counter) + { + executeBatch(stmt, counter, true); + } + + public static void executeBatch(PreparedStatement stmt, int counter, boolean checkExactlyOne) { try { @@ -529,10 +513,14 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement for (int i = 0; i < results.length; i++) { int result = results[i]; - if (result != 1 && result != Statement.SUCCESS_NO_INFO) + if (result < 0 && result != Statement.SUCCESS_NO_INFO) { throw new DBException("Result " + i + " is not successful: " + result); } + else if (checkExactlyOne && result != 1) + { + throw new DBException("Result " + i + " did not affect exactly one row: " + result); + } } } catch (SQLException ex) @@ -541,6 +529,175 @@ public class NonAuditListTableMapping extends AbstractListTableMapping implement } } + private static class ShiftOperation + { + final int startIndex; + + final int endIndex; + + final int offset; + + ShiftOperation(int startIndex, int endIndex, int offset) + { + this.startIndex = startIndex; + this.endIndex = endIndex; + this.offset = offset; + } + + @Override + public String toString() + { + return "range [" + startIndex + ".." + endIndex + "] offset " + offset; + } + } + + /** + * Perform the moves re + * + * @throws SQLException + */ + private void performMoveOperations(IDBStoreAccessor accessor, CDOID id) throws SQLException + { + PreparedStatement shiftIndicesStmt = null; + try + { + /* + * Step 3: shift all elements which have to be shifted up or down because of add, remove or move of other elements + * to their proper position. This has to be done in two phases to avoid collisions, as the index has to be unique + * and shift up operations have to be executed in top to bottom order. + */ + + IIDHandler idHandler = getMappingStrategy().getStore().getIDHandler(); + int size = manipulations.size(); + + LinkedList<ShiftOperation> shiftOperations = new LinkedList<ShiftOperation>(); + + // If a necessary shift is detected (source and destination indices differ), firstIndex is set to the current + // index + // and + // currentOffset is set to the offset of the shift operation. When a new offset is detected or the range is + // interrupted, + // we record the range and start a new one if needed. + int rangeStartIndex = ManipulationConstants.NO_INDEX; + int rangeOffset = 0; + + // iterate through the manipulationElements and collect the necessary operations + for (int i = 0; i < size; i++) + { + ManipulationElement element = manipulations.get(i); + + // shift applies only to elements which are not moved, inserted or deleted (i.e. only plain SET_VALUE and NONE + // are + // affected) + if (element.type == ManipulationConstants.NONE || element.type == ManipulationConstants.SET_VALUE) + { + int elementOffset = element.destinationIndex - element.sourceIndex; + + // first make sure if we have to close a previous range. This is the case, if the current element's offset + // differs from + // the rangeOffset and a range is open. + if (elementOffset != rangeOffset && rangeStartIndex != ManipulationConstants.NO_INDEX) + { + // there is an open range but the rangeOffset differs. We have to close the open range + shiftOperations.add(new ShiftOperation(rangeStartIndex, i - 1, rangeOffset)); + // and reset the state + rangeStartIndex = ManipulationConstants.NO_INDEX; + rangeOffset = 0; + } + + // at this point, either a range is open, which means that the current element also fits in the range (i.e. + // the + // offsets match) + // or no range is open. In the latter case, we have to open one if the current element's offset is not 0. + + if (elementOffset != 0 && rangeStartIndex == ManipulationConstants.NO_INDEX) + { + rangeStartIndex = i; + rangeOffset = elementOffset; + } + } + else + { // shift does not apply to this element because of its type + if (rangeStartIndex != ManipulationConstants.NO_INDEX) + { + // if there is an open range, we have to close and remember it + shiftOperations.add(new ShiftOperation(rangeStartIndex, i - 1, rangeOffset)); + // and reset the state + rangeStartIndex = ManipulationConstants.NO_INDEX; + rangeOffset = 0; + } + } + } + + // after the iteration, we have to make sure that we remember the last open range, if it is there + if (rangeStartIndex != ManipulationConstants.NO_INDEX) + { + shiftOperations.add(new ShiftOperation(rangeStartIndex, size - 1, rangeOffset)); + } + + // now process the operations. Move down operations can be performed directly, move up operations need to be + // performed later + // in the reverse direction + int operationCounter = shiftOperations.size(); + ListIterator<ShiftOperation> operationIt = shiftOperations.listIterator(); + + while (operationIt.hasNext()) + { + ShiftOperation operation = operationIt.next(); + if (operation.offset < 0) + { + if (shiftIndicesStmt == null) + { + shiftIndicesStmt = accessor.getStatementCache().getPreparedStatement(sqlMassUpdateIndex, + ReuseProbability.HIGH); + idHandler.setCDOID(shiftIndicesStmt, 2, id); + } + + if (TRACER.isEnabled()) + { + TRACER.format(" - shift {0} ", operation); //$NON-NLS-1$ + } + + shiftIndicesStmt.setInt(1, operation.offset); + shiftIndicesStmt.setInt(3, operation.startIndex); + shiftIndicesStmt.setInt(4, operation.endIndex); + shiftIndicesStmt.addBatch(); + + operationIt.remove(); + } + } + while (operationIt.hasPrevious()) + { + ShiftOperation operation = operationIt.previous(); + if (shiftIndicesStmt == null) + { + shiftIndicesStmt = accessor.getStatementCache().getPreparedStatement(sqlMassUpdateIndex, + ReuseProbability.HIGH); + idHandler.setCDOID(shiftIndicesStmt, 2, id); + } + + if (TRACER.isEnabled()) + { + TRACER.format(" - shift {0} ", operation); //$NON-NLS-1$ + } + + shiftIndicesStmt.setInt(1, operation.offset); + shiftIndicesStmt.setInt(3, operation.startIndex); + shiftIndicesStmt.setInt(4, operation.endIndex); + shiftIndicesStmt.addBatch(); + } + + if (operationCounter > 0) + { + executeBatch(shiftIndicesStmt, operationCounter, false); + } + } + finally + { + releaseStatement(accessor, shiftIndicesStmt); + } + } + private void releaseStatement(IDBStoreAccessor accessor, PreparedStatement... stmts) { Throwable t = null; diff --git a/plugins/org.eclipse.net4j.db/src/org/eclipse/net4j/db/DBUtil.java b/plugins/org.eclipse.net4j.db/src/org/eclipse/net4j/db/DBUtil.java index c7f4b7df79..a618c9a877 100644 --- a/plugins/org.eclipse.net4j.db/src/org/eclipse/net4j/db/DBUtil.java +++ b/plugins/org.eclipse.net4j.db/src/org/eclipse/net4j/db/DBUtil.java @@ -518,6 +518,7 @@ public final class DBUtil } } + public static int update(Connection connection, String sql) { trace(sql); |