refinement: Improve performance of transaction deletes by including branch_id in sql

4 files changed, 30 insertions, 68 deletions

diff --git a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/ConsolidateRelationsTxOperation.java b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/ConsolidateRelationsTxOperation.java
index e39b5136077..fe9da980a10 100644
--- a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/ConsolidateRelationsTxOperation.java
+++ b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/ConsolidateRelationsTxOperation.java
@@ -41,7 +41,8 @@ public class ConsolidateRelationsTxOperation extends AbstractDbTxOperation {
    private static final String UPDATE_TXS_GAMMAS =
       "update osee_txs set gamma_id = ?, mod_type = ?, tx_current = ? where transaction_id = ? and gamma_id = ?";
 
-   private static final String DELETE_TXS = "delete from osee_txs where transaction_id = ? and gamma_id = ?";
+   private static final String DELETE_TXS =
+      "delete from osee_txs where branch_id = ? and transaction_id = ? and gamma_id = ?";
 
    private static final String DELETE_RELATIONS = "delete from osee_relation_link where gamma_id = ?";
 
@@ -174,7 +175,7 @@ public class ConsolidateRelationsTxOperation extends AbstractDbTxOperation {
                netModType = ModificationType.getMod(modType);
                netTxCurrent = txCurrent;
             } else {
-               addressingToDelete.add(new Object[] {transactionId, obsoleteGammaId});
+               addressingToDelete.add(new Object[] {chStmt.getInt("branch_id"), transactionId, obsoleteGammaId});
                computeNetAddressing(ModificationType.getMod(modType), txCurrent);
             }
 
diff --git a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeTransactionOperation.java b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeTransactionOperation.java
index 854fb57907a..8d2d53305e5 100644
--- a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeTransactionOperation.java
+++ b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeTransactionOperation.java
@@ -29,6 +29,7 @@ import org.eclipse.osee.framework.core.exception.OseeCoreException;
 import org.eclipse.osee.framework.core.exception.TransactionDoesNotExist;
 import org.eclipse.osee.framework.core.model.TransactionRecord;
 import org.eclipse.osee.framework.core.model.cache.TransactionCache;
+import org.eclipse.osee.framework.core.operation.NullOperationLogger;
 import org.eclipse.osee.framework.core.operation.OperationLogger;
 import org.eclipse.osee.framework.core.util.Conditions;
 import org.eclipse.osee.framework.database.IOseeDatabaseService;
@@ -50,32 +51,22 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
    private static final String UPDATE_TXS_DETAILS_COMMENT =
       "UPDATE osee_tx_details SET osee_comment = replace(osee_comment, ?, ?) WHERE osee_comment like ?";
 
-   private static final String SELECT_GAMMAS_FROM_TRANSACTION =
-      "SELECT DISTINCT txs1.gamma_id FROM osee_txs txs1, osee_join_transaction txj1 WHERE txs1.transaction_id = txj1.transaction_id AND txj1.query_id = ? AND NOT EXISTS (SELECT 'x' FROM osee_txs txs2 WHERE txs1.gamma_id = txs2.gamma_id AND txs1.transaction_id <> txs2.transaction_id)";
+   private static final String DELETE_TXS = "delete from osee_txs where branch_id = ? and transaction_id = ?";
+   private static final String DELETE_TX_DETAILS =
+      "delete from osee_tx_details where branch_id = ? and transaction_id = ?";
 
-   private final static String DELETE_TXS = "delete from osee_txs where transaction_id = ?";
-   private static final String DELETE_TX_DETAILS = "delete from osee_tx_details where transaction_id = ?";
-
-   private final static String DELETE_ARTIFACT_VERSIONS = "delete from osee_artifact items where items.gamma_id = ?";
-   private final static String DELETE_ATTRIBUTES = "delete from osee_attribute items where items.gamma_id = ?";
-   private final static String DELETE_RELATIONS = "delete from osee_relation_link items where items.gamma_id = ?";
-
-   private final static String TRANSACATION_GAMMA_IN_USE =
-      "SELECT txs2.transaction_id FROM osee_txs txs1, osee_txs txs2, osee_join_transaction jn where txs1.transaction_id = jn.transaction_id AND txs1.gamma_id = txs2.gamma_id and txs2.transaction_id != txs1.transaction_id AND jn.query_id = ?";
-
-   private final static String SELECT_AFFECTED_ITEMS =
+   private static final String SELECT_AFFECTED_ITEMS =
       "SELECT %s as item_id, txs.branch_id from osee_join_transaction ojt, osee_txs txs, %s item where ojt.query_id = ? AND ojt.transaction_id = txs.transaction_id AND txs.gamma_id = item.gamma_id";
 
-   private final static String FIND_NEW_TX_CURRENTS =
+   private static final String FIND_NEW_TX_CURRENTS =
       "SELECT oj.id as item_id, txs.mod_type, txs.gamma_id, txs.transaction_id from osee_join_id oj, %s item, osee_txs txs where oj.query_id = ? and oj.id = item.%s and item.gamma_id = txs.gamma_id and txs.branch_id = ? order by oj.id desc, txs.transaction_id desc";
 
    private static final String UPDATE_TX_CURRENT =
-      "update osee_txs set tx_current = ? where transaction_id = ? and gamma_id = ?";
+      "update osee_txs set tx_current = ? where branch_id = ? and transaction_id = ? and gamma_id = ?";
 
    private final int[] txIdsToDelete;
-   private final boolean force;
    private boolean success;
-
+   private final TransactionCache transactionCache;
    public static interface PurgeTransactionListener {
       void onPurgeTransactionSuccess(Collection<TransactionRecord> transactions);
    }
@@ -87,16 +78,12 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       super(databaseService, String.format("Delete transactions: %s", Arrays.toString(txIdsToDelete)),
          Activator.PLUGIN_ID, logger);
       this.txIdsToDelete = txIdsToDelete;
-      this.force = force;
       this.success = false;
+      transactionCache = Activator.getOseeCachingService().getTransactionCache();
    }
 
    public PurgeTransactionOperation(IOseeDatabaseService databaseService, boolean force, int... txIdsToDelete) {
-      super(databaseService, String.format("Delete transactions: %s", Arrays.toString(txIdsToDelete)),
-         Activator.PLUGIN_ID);
-      this.txIdsToDelete = txIdsToDelete;
-      this.force = force;
-      this.success = false;
+      this(databaseService, force, NullOperationLogger.getSingleton(), txIdsToDelete);
    }
 
    public void addListener(PurgeTransactionListener listener) {
@@ -132,32 +119,29 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       int txQueryId = txsToDeleteQuery.getQueryId();
 
       try {
-         checkForModifiedBaselines(connection, force, txQueryId);
-
          Map<Integer, IdJoinQuery> arts = findAffectedItems(connection, "art_id", "osee_artifact", txQueryId);
          Map<Integer, IdJoinQuery> attrs = findAffectedItems(connection, "attr_id", "osee_attribute", txQueryId);
          Map<Integer, IdJoinQuery> rels = findAffectedItems(connection, "rel_link_id", "osee_relation_link", txQueryId);
          monitor.worked(calculateWork(0.20));
 
          setChildBranchBaselineTxs(connection, monitor, deleteToPreviousTx, 0.20);
-         deleteItemEntriesForTransactions(connection, monitor, txQueryId, 0.20);
 
          monitor.subTask("Remove Tx Rows");
          List<Object[]> txsToDelete = new ArrayList<Object[]>();
          for (int txId : txIdsToDelete) {
             log("  Adding tx to list:" + txId);
-            txsToDelete.add(new Object[] {txId});
+            txsToDelete.add(new Object[] {transactionCache.getById(txId).getBranch().getId(), txId});
          }
          ConnectionHandler.runBatchUpdate(connection, DELETE_TX_DETAILS, txsToDelete);
          ConnectionHandler.runBatchUpdate(connection, DELETE_TXS, txsToDelete);
 
          monitor.subTask("Updating Previous Tx to Current");
-         List<Object[]> txsUpdate = new ArrayList<Object[]>();
-         computeNewTxCurrents(connection, txsUpdate, "art_id", "osee_artifact", arts);
-         computeNewTxCurrents(connection, txsUpdate, "attr_id", "osee_attribute", attrs);
-         computeNewTxCurrents(connection, txsUpdate, "rel_link_id", "osee_relation_link", rels);
+         List<Object[]> updateData = new ArrayList<Object[]>();
+         computeNewTxCurrents(connection, updateData, "art_id", "osee_artifact", arts);
+         computeNewTxCurrents(connection, updateData, "attr_id", "osee_attribute", attrs);
+         computeNewTxCurrents(connection, updateData, "rel_link_id", "osee_relation_link", rels);
 
-         ConnectionHandler.runBatchUpdate(connection, UPDATE_TX_CURRENT, txsUpdate);
+         ConnectionHandler.runBatchUpdate(connection, UPDATE_TX_CURRENT, updateData);
          monitor.worked(calculateWork(0.20));
          success = true;
       } finally {
@@ -176,7 +160,7 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       changedTransactions = null;
    }
 
-   private void computeNewTxCurrents(OseeConnection connection, Collection<Object[]> txsUpdate, String itemId, String tableName, Map<Integer, IdJoinQuery> affected) throws OseeCoreException {
+   private void computeNewTxCurrents(OseeConnection connection, Collection<Object[]> updateData, String itemId, String tableName, Map<Integer, IdJoinQuery> affected) throws OseeCoreException {
       String query = String.format(FIND_NEW_TX_CURRENTS, tableName, itemId);
 
       for (Entry<Integer, IdJoinQuery> entry : affected.entrySet()) {
@@ -193,8 +177,9 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
                if (previousItem != currentItem) {
                   ModificationType modType = ModificationType.getMod(statement.getInt("mod_type"));
                   TxChange txCurrent = TxChange.getCurrent(modType);
-                  txsUpdate.add(new Object[] {
+                  updateData.add(new Object[] {
                      txCurrent.getValue(),
+                     branchId,
                      statement.getInt("transaction_id"),
                      statement.getLong("gamma_id")});
                   previousItem = currentItem;
@@ -248,7 +233,6 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       Map<TransactionRecord, TransactionRecord> deleteToPreviousTx =
          new HashMap<TransactionRecord, TransactionRecord>();
       double workStep = workPercentage / txIdsToDelete.length;
-      TransactionCache transactionCache = Activator.getOseeCachingService().getTransactionCache();
       for (int index = 0; index < txIdsToDelete.length; index++) {
          monitor.subTask(String.format("Fetching Previous Tx Info: [%d of %d]", index + 1, txIdsToDelete.length));
          int fromTx = txIdsToDelete[index];
@@ -270,24 +254,6 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       return deleteToPreviousTx;
    }
 
-   private void deleteItemEntriesForTransactions(OseeConnection connection, IProgressMonitor monitor, int txsToDeleteQueryId, double workPercentage) throws OseeCoreException {
-      monitor.subTask("Deleting Tx Items");
-      List<Object[]> data = new ArrayList<Object[]>();
-      IOseeStatement chStmt = ConnectionHandler.getStatement(connection);
-      try {
-         chStmt.runPreparedQuery(SELECT_GAMMAS_FROM_TRANSACTION, txsToDeleteQueryId);
-         while (chStmt.next()) {
-            data.add(new Object[] {chStmt.getLong("gamma_id")});
-         }
-      } finally {
-         chStmt.close();
-      }
-      ConnectionHandler.runBatchUpdate(connection, DELETE_ARTIFACT_VERSIONS, data);
-      ConnectionHandler.runBatchUpdate(connection, DELETE_ATTRIBUTES, data);
-      ConnectionHandler.runBatchUpdate(connection, DELETE_RELATIONS, data);
-      monitor.worked(calculateWork(workPercentage));
-   }
-
    private void setChildBranchBaselineTxs(OseeConnection connection, IProgressMonitor monitor, Map<TransactionRecord, TransactionRecord> deleteToPreviousTx, double workPercentage) throws OseeCoreException {
       List<Object[]> data = new ArrayList<Object[]>();
       monitor.subTask("Update Baseline Txs for Child Branches");
@@ -307,14 +273,4 @@ public class PurgeTransactionOperation extends AbstractDbTxOperation {
       }
       monitor.worked(calculateWork(workPercentage));
    }
-
-   private void checkForModifiedBaselines(OseeConnection connection, boolean force, int queryId) throws OseeCoreException {
-      int transaction_id =
-         ConnectionHandler.runPreparedQueryFetchInt(connection, 0, TRANSACATION_GAMMA_IN_USE, queryId);
-      if (transaction_id > 0 && !force) {
-         throw new OseeCoreException(
-            "The Transaction %d holds a Gamma that is in use in other transactions.  In order to delete this Transaction you will need to select the force check box.\n\nNO TRANSACTIONS WERE DELETED.",
-            transaction_id);
-      }
-   }
 }
\ No newline at end of file
diff --git a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeUnusedBackingDataAndTransactions.java b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeUnusedBackingDataAndTransactions.java
index 1f7ffabe69c..cfb08428ae1 100644
--- a/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeUnusedBackingDataAndTransactions.java
+++ b/plugins/org.eclipse.osee.framework.database/src/org/eclipse/osee/framework/database/operation/PurgeUnusedBackingDataAndTransactions.java
@@ -29,12 +29,13 @@ public class PurgeUnusedBackingDataAndTransactions extends AbstractOperation {
    private static final String NOT_ADDRESSESED_GAMMAS =
       "select gamma_id from %s t1 where not exists (select 1 from osee_txs txs1 where t1.gamma_id = txs1.gamma_id) and not exists (select 1 from osee_txs_archived txs3 where t1.gamma_id = txs3.gamma_id)";
    private static final String EMPTY_TRANSACTIONS =
-      "select transaction_id from osee_tx_details txd where not exists (select 1 from osee_txs txs1 where txs1.branch_id = txd.branch_id and txs1.transaction_id = txd.transaction_id) and not exists (select 1 from osee_txs_archived txs2 where txs2.branch_id = txd.branch_id and txs2.transaction_id = txd.transaction_id)";
+      "select branch_id, transaction_id from osee_tx_details txd where not exists (select 1 from osee_txs txs1 where txs1.branch_id = txd.branch_id and txs1.transaction_id = txd.transaction_id) and not exists (select 1 from osee_txs_archived txs2 where txs2.branch_id = txd.branch_id and txs2.transaction_id = txd.transaction_id)";
 
    private static final String NONEXISTENT_GAMMAS =
       "SELECT gamma_id FROM %s txs WHERE NOT EXISTS " + "(SELECT 1 FROM osee_attribute att WHERE txs.gamma_id = att.gamma_id) AND NOT EXISTS " + "(SELECT 1 FROM osee_artifact art WHERE txs.gamma_id = art.gamma_id) AND NOT EXISTS " + "(SELECT 1 FROM osee_relation_link rel WHERE txs.gamma_id = rel.gamma_id)";
    private static final String DELETE_GAMMAS = "DELETE FROM %s WHERE gamma_id = ?";
-   private static final String DELETE_EMPTY_TRANSACTIONS = "DELETE FROM osee_tx_details WHERE transaction_id = ?";
+   private static final String DELETE_EMPTY_TRANSACTIONS =
+      "DELETE FROM osee_tx_details WHERE branch_id = ? and transaction_id = ?";
 
    public PurgeUnusedBackingDataAndTransactions(OperationLogger logger) {
       super("Data with no TXS Addressing and empty transactions", Activator.PLUGIN_ID, logger);
@@ -83,7 +84,7 @@ public class PurgeUnusedBackingDataAndTransactions extends AbstractOperation {
       try {
          chStmt.runPreparedQuery(EMPTY_TRANSACTIONS);
          while (chStmt.next()) {
-            emptyTransactions.add(new Object[] {chStmt.getInt("transaction_id")});
+            emptyTransactions.add(new Object[] {chStmt.getInt("branch_id"), chStmt.getInt("transaction_id")});
             log(String.valueOf(chStmt.getInt("transaction_id")));
          }
       } finally {
diff --git a/plugins/org.eclipse.osee.framework.skynet.core.test/src/org/eclipse/osee/framework/skynet/core/artifact/PurgeTransactionTest.java b/plugins/org.eclipse.osee.framework.skynet.core.test/src/org/eclipse/osee/framework/skynet/core/artifact/PurgeTransactionTest.java
index 15834d9eebf..c2ae5dae999 100644
--- a/plugins/org.eclipse.osee.framework.skynet.core.test/src/org/eclipse/osee/framework/skynet/core/artifact/PurgeTransactionTest.java
+++ b/plugins/org.eclipse.osee.framework.skynet.core.test/src/org/eclipse/osee/framework/skynet/core/artifact/PurgeTransactionTest.java
@@ -22,8 +22,11 @@ import org.eclipse.osee.framework.core.enums.CoreArtifactTypes;
 import org.eclipse.osee.framework.core.enums.CoreAttributeTypes;
 import org.eclipse.osee.framework.core.exception.OseeCoreException;
 import org.eclipse.osee.framework.core.operation.IOperation;
+import org.eclipse.osee.framework.core.operation.NullOperationLogger;
+import org.eclipse.osee.framework.core.operation.Operations;
 import org.eclipse.osee.framework.core.test.mocks.Asserts;
 import org.eclipse.osee.framework.database.core.ConnectionHandler;
+import org.eclipse.osee.framework.database.operation.PurgeUnusedBackingDataAndTransactions;
 import org.eclipse.osee.framework.skynet.core.mocks.DbTestUtil;
 import org.eclipse.osee.framework.skynet.core.transaction.SkynetTransaction;
 import org.eclipse.osee.framework.skynet.core.util.FrameworkTestUtil;
@@ -100,6 +103,7 @@ public class PurgeTransactionTest {
    private void purge(int transactionId, Map<String, Integer> dbCount) throws Exception {
       IOperation operation = PurgeTransactionOperationWithListener.getPurgeTransactionOperation(true, transactionId);
       Asserts.testOperation(operation, IStatus.OK);
+      Operations.executeWorkAndCheckStatus(new PurgeUnusedBackingDataAndTransactions(NullOperationLogger.getSingleton()));
       DbTestUtil.getTableRowCounts(dbCount, tables);
    }