speed up transaction storage a bit

by updating existing records in-place. sqlite's insert or replace
statement tries insert, and when that fails because of unique/primary
key violation *removes* the existing record together with any child
records and then retries the insert. it is almost like deleting all the
transactions and inserting them again, but with a failed insert before
that

diff --git a/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java b/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java
index b004ec9a0f1a11e6077f3327c0425365a3b4e443..0e42f87dfcd75b3e0de018dc1c23224c8807675b 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java
+++ b/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java
@@ -22,21 +22,17 @@ import android.os.AsyncTask;
 import android.os.OperationCanceledException;
 
 import androidx.annotation.NonNull;
-import androidx.room.Transaction;
 
 import com.fasterxml.jackson.core.JsonParseException;
 import com.fasterxml.jackson.databind.RuntimeJsonMappingException;
 
 import net.ktnx.mobileledger.dao.AccountDAO;
-import net.ktnx.mobileledger.dao.AccountValueDAO;
-import net.ktnx.mobileledger.dao.TransactionAccountDAO;
 import net.ktnx.mobileledger.dao.TransactionDAO;
 import net.ktnx.mobileledger.db.Account;
 import net.ktnx.mobileledger.db.AccountWithAmounts;
 import net.ktnx.mobileledger.db.DB;
 import net.ktnx.mobileledger.db.Option;
 import net.ktnx.mobileledger.db.Profile;
-import net.ktnx.mobileledger.db.TransactionAccount;
 import net.ktnx.mobileledger.db.TransactionWithAccounts;
 import net.ktnx.mobileledger.err.HTTPException;
 import net.ktnx.mobileledger.json.API;
@@ -50,7 +46,6 @@ import net.ktnx.mobileledger.model.LedgerTransactionAccount;
 import net.ktnx.mobileledger.ui.MainModel;
 import net.ktnx.mobileledger.utils.Logger;
 import net.ktnx.mobileledger.utils.NetworkUtil;
-import net.ktnx.mobileledger.utils.Profiler;
 
 import java.io.BufferedReader;
 import java.io.IOException;
@@ -575,11 +570,11 @@ public class RetrieveTransactionsTask extends
                 retrieveTransactionListLegacy(accounts, transactions);
             }
 
-            storeAccountsAndTransactions(accounts, transactions);
-
             mainModel.updateDisplayedTransactionsFromWeb(transactions);
 
-            return new Result(accounts, transactions);
+            new AccountAndTransactionListSaver(accounts, transactions).start();
+
+            return new Result(null);
         }
         catch (MalformedURLException e) {
             e.printStackTrace();
@@ -614,71 +609,6 @@ public class RetrieveTransactionsTask extends
             Data.backgroundTaskFinished();
         }
     }
-    @Transaction
-    private void storeAccountsAndTransactions(List<LedgerAccount> accounts,
-                                              List<LedgerTransaction> transactions) {
-        AccountDAO accDao = DB.get()
-                              .getAccountDAO();
-        TransactionDAO trDao = DB.get()
-                                 .getTransactionDAO();
-        TransactionAccountDAO trAccDao = DB.get()
-                                           .getTransactionAccountDAO();
-        AccountValueDAO valDao = DB.get()
-                                   .getAccountValueDAO();
-
-        Logger.debug(TAG, "Preparing account list");
-        final List<AccountWithAmounts> list = new ArrayList<>();
-        for (LedgerAccount acc : accounts) {
-            final AccountWithAmounts a = acc.toDBOWithAmounts();
-            Account existing = accDao.getByNameSync(profile.getId(), acc.getName());
-            if (existing != null) {
-                a.account.setExpanded(existing.isExpanded());
-                a.account.setAmountsExpanded(existing.isAmountsExpanded());
-                a.account.setId(
-                        existing.getId()); // not strictly needed, but since we have it anyway...
-            }
-
-            list.add(a);
-        }
-        Logger.debug(TAG, "Account list prepared. Storing");
-        accDao.storeAccountsSync(list, profile.getId());
-        Logger.debug(TAG, "Account list stored");
-
-        Profiler tranProfiler = new Profiler("transactions");
-        Profiler tranAccProfiler = new Profiler("transaction accounts");
-
-        Logger.debug(TAG, "Storing transactions");
-        long trGen = trDao.getGenerationSync(profile.getId());
-        for (LedgerTransaction tr : transactions) {
-            TransactionWithAccounts tran = tr.toDBO();
-            tran.transaction.setGeneration(trGen);
-            tran.transaction.setProfileId(profile.getId());
-
-            tranProfiler.opStart();
-            tran.transaction.setId(trDao.insertSync(tran.transaction));
-            tranProfiler.opEnd();
-
-            for (TransactionAccount trAcc : tran.accounts) {
-                trAcc.setGeneration(trGen);
-                trAcc.setTransactionId(tran.transaction.getId());
-                tranAccProfiler.opStart();
-                trAcc.setId(trAccDao.insertSync(trAcc));
-                tranAccProfiler.opEnd();
-            }
-        }
-
-        tranProfiler.dumpStats();
-        tranAccProfiler.dumpStats();
-
-        Logger.debug(TAG, "Transactions stored. Purging old");
-        trDao.purgeOldTransactionsSync(profile.getId(), trGen);
-        Logger.debug(TAG, "Old transactions purged");
-
-        DB.get()
-          .getOptionDAO()
-          .insertSync(new Option(profile.getId(), Option.OPT_LAST_SCRAPE,
-                  String.valueOf((new Date()).getTime())));
-    }
     public void throwIfCancelled() {
         if (isCancelled())
             throw new OperationCanceledException(null);
@@ -776,4 +706,60 @@ public class RetrieveTransactionsTask extends
             this.transactions = transactions;
         }
     }
+
+    private class AccountAndTransactionListSaver extends Thread {
+        private final List<LedgerAccount> accounts;
+        private final List<LedgerTransaction> transactions;
+        public AccountAndTransactionListSaver(List<LedgerAccount> accounts,
+                                              List<LedgerTransaction> transactions) {
+            this.accounts = accounts;
+            this.transactions = transactions;
+        }
+        private void storeAccountsAndTransactions(List<LedgerAccount> accounts,
+                                                  List<LedgerTransaction> transactions) {
+            AccountDAO accDao = DB.get()
+                                  .getAccountDAO();
+            TransactionDAO trDao = DB.get()
+                                     .getTransactionDAO();
+
+            Logger.debug(TAG, "Preparing account list");
+            final List<AccountWithAmounts> list = new ArrayList<>();
+            for (LedgerAccount acc : accounts) {
+                final AccountWithAmounts a = acc.toDBOWithAmounts();
+                Account existing = accDao.getByNameSync(profile.getId(), acc.getName());
+                if (existing != null) {
+                    a.account.setExpanded(existing.isExpanded());
+                    a.account.setAmountsExpanded(existing.isAmountsExpanded());
+                    a.account.setId(
+                            existing.getId()); // not strictly needed, but since we have it
+                    // anyway...
+                }
+
+                list.add(a);
+            }
+            Logger.debug(TAG, "Account list prepared. Storing");
+            accDao.storeAccountsSync(list, profile.getId());
+            Logger.debug(TAG, "Account list stored");
+
+            Logger.debug(TAG, "Preparing transaction list");
+            final List<TransactionWithAccounts> tranList = new ArrayList<>();
+
+            for (LedgerTransaction tr : transactions)
+                tranList.add(tr.toDBO());
+
+            Logger.debug(TAG, "Storing transaction list");
+            trDao.storeTransactionsSync(tranList, profile.getId());
+
+            Logger.debug(TAG, "Transactions stored");
+
+            DB.get()
+              .getOptionDAO()
+              .insertSync(new Option(profile.getId(), Option.OPT_LAST_SCRAPE,
+                      String.valueOf((new Date()).getTime())));
+        }
+        @Override
+        public void run() {
+            storeAccountsAndTransactions(accounts, transactions);
+        }
+    }
 }

diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionAccountDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionAccountDAO.java
index eae262624139c64e235fa90214f0ef1c93dcdfac..c63ed223c1cf051c2800cd146661ce373e9866c2 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionAccountDAO.java
+++ b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionAccountDAO.java
@@ -45,4 +45,8 @@ public abstract class TransactionAccountDAO extends BaseDAO<TransactionAccount>
 
     @Query("SELECT * FROM transaction_accounts WHERE id = :id")
     public abstract LiveData<TransactionAccount> getById(long id);
+
+    @Query("SELECT * FROM transaction_accounts WHERE transaction_id = :transactionId AND order_no" +
+           " = :orderNo")
+    public abstract TransactionAccount getByOrderNoSync(long transactionId, int orderNo);
 }

diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
index 584ddc65f634bcb8f5ad941e3320f4f7008e3e6e..7d91728fe59ebbbd71e4e4651e475e0a870421d1 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
+++ b/app/src/main/java/net/ktnx/mobileledger/dao/TransactionDAO.java
@@ -27,11 +27,15 @@ import androidx.room.OnConflictStrategy;
 import androidx.room.Query;
 import androidx.room.Update;
 
+import net.ktnx.mobileledger.db.DB;
 import net.ktnx.mobileledger.db.Transaction;
+import net.ktnx.mobileledger.db.TransactionAccount;
 import net.ktnx.mobileledger.db.TransactionWithAccounts;
+import net.ktnx.mobileledger.utils.Logger;
 
 import java.util.ArrayList;
 import java.util.List;
+import java.util.Locale;
 
 @Dao
 public abstract class TransactionDAO extends BaseDAO<Transaction> {
@@ -116,7 +120,60 @@ public abstract class TransactionDAO extends BaseDAO<Transaction> {
     }
     @Query("DELETE FROM transactions WHERE profile_id = :profileId AND generation <> " +
            ":currentGeneration")
-    public abstract void purgeOldTransactionsSync(long profileId, long currentGeneration);
+    public abstract int purgeOldTransactionsSync(long profileId, long currentGeneration);
+
+    @Query("DELETE FROM transaction_accounts WHERE EXISTS (SELECT 1 FROM transactions tr WHERE tr" +
+           ".id=transaction_accounts.transaction_id AND tr.profile_id=:profileId) AND generation " +
+           "<> :currentGeneration")
+    public abstract int purgeOldTransactionAccountsSync(long profileId, long currentGeneration);
+    public void storeTransactionsSync(List<TransactionWithAccounts> list, long profileId) {
+        long generation = getGenerationSync(profileId) + 1;
+
+        for (TransactionWithAccounts tr : list) {
+            tr.transaction.setGeneration(generation);
+            tr.transaction.setProfileId(profileId);
+
+            storeSync(tr);
+        }
+
+        Logger.debug("Transaction", "Purging old transactions");
+        int removed = purgeOldTransactionsSync(profileId, generation);
+        Logger.debug("Transaction", String.format(Locale.ROOT, "Purged %d transactions", removed));
+
+        removed = purgeOldTransactionAccountsSync(profileId, generation);
+        Logger.debug("Transaction",
+                String.format(Locale.ROOT, "Purged %d transaction accounts", removed));
+    }
+    private void storeSync(TransactionWithAccounts rec) {
+        TransactionAccountDAO trAccDao = DB.get()
+                                           .getTransactionAccountDAO();
+
+        Transaction transaction = rec.transaction;
+        Transaction existing = getByLedgerId(transaction.getProfileId(), transaction.getLedgerId());
+        if (existing != null) {
+            existing.copyDataFrom(transaction);
+            updateSync(existing);
+            transaction = existing;
+        }
+        else
+            transaction.setId(insertSync(transaction));
+
+        for (TransactionAccount trAcc : rec.accounts) {
+            trAcc.setTransactionId(transaction.getId());
+            trAcc.setGeneration(transaction.getGeneration());
+            TransactionAccount existingAcc =
+                    trAccDao.getByOrderNoSync(trAcc.getTransactionId(), trAcc.getOrderNo());
+            if (existingAcc != null) {
+                existingAcc.copyDataFrom(trAcc);
+                trAccDao.updateSync(trAcc);
+                trAcc = existingAcc;
+            }
+            else
+                trAcc.setId(trAccDao.insertSync(trAcc));
+        }
+    }
+    @Query("SELECT * FROM transactions where profile_id = :profileId AND ledger_id = :ledgerId")
+    public abstract Transaction getByLedgerId(long profileId, long ledgerId);
     static class TransactionGenerationContainer {
         @ColumnInfo
         long generation;

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
index d1757a2bd80c2d3a1856fecc554ddcb9fcf65a1d..fcbdd82b7fc5d289fa0ff3520c5078ea3d11d246 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
@@ -126,4 +126,16 @@ public class Transaction {
         this.generation = generation;
     }
 
+    public void copyDataFrom(Transaction o) {
+        // id = o.id;
+        ledgerId = o.ledgerId;
+        profileId = o.profileId;
+        dataHash = o.dataHash;
+        year = o.year;
+        month = o.month;
+        day = o.day;
+        description = o.description;
+        comment = o.comment;
+        generation = o.generation;
+    }
 }

diff --git a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
index d556731f7ac709164cd296b5e5b4758ee0dafc9c..abcaa0343522a23bb839a0f1a87a2a5ef458f6a3 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
@@ -103,4 +103,15 @@ public class TransactionAccount {
     public void setGeneration(long generation) {
         this.generation = generation;
     }
+
+    public void copyDataFrom(TransactionAccount o) {
+        // id = o.id
+        transactionId = o.transactionId;
+        orderNo = o.orderNo;
+        accountName = o.accountName;
+        currency = o.currency;
+        amount = o.amount;
+        comment = o.comment;
+        generation = o.generation;
+    }
 }