Skip to main content
aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorStefan Winkler2011-12-21 16:22:41 -0500
committerStefan Winkler2011-12-21 16:22:41 -0500
commitfa59e356884fff3964b78274c1e7a394b18afcc2 (patch)
tree96acc705885d321fe6c21c36c50f63746979f1cf
parent4753819a93fc995788de5d22eb02130284824f09 (diff)
downloadcdo-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
-rw-r--r--plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/NonAuditListTableMapping.java273
-rw-r--r--plugins/org.eclipse.net4j.db/src/org/eclipse/net4j/db/DBUtil.java1
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);

Back to the top