From 451569cb01799c684b210cd6839d68825092a117 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Thu, 22 Apr 2021 23:43:55 +0300 Subject: [PATCH] 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 --- .../async/RetrieveTransactionsTask.java | 132 ++++++++---------- .../dao/TransactionAccountDAO.java | 4 + .../ktnx/mobileledger/dao/TransactionDAO.java | 59 +++++++- .../net/ktnx/mobileledger/db/Transaction.java | 12 ++ .../mobileledger/db/TransactionAccount.java | 11 ++ 5 files changed, 144 insertions(+), 74 deletions(-) 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 b004ec9a..0e42f87d 100644 --- 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 accounts, - List 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 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 accounts; + private final List transactions; + public AccountAndTransactionListSaver(List accounts, + List transactions) { + this.accounts = accounts; + this.transactions = transactions; + } + private void storeAccountsAndTransactions(List accounts, + List transactions) { + AccountDAO accDao = DB.get() + .getAccountDAO(); + TransactionDAO trDao = DB.get() + .getTransactionDAO(); + + Logger.debug(TAG, "Preparing account list"); + final List 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 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 eae26262..c63ed223 100644 --- 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 @Query("SELECT * FROM transaction_accounts WHERE id = :id") public abstract LiveData 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 584ddc65..7d91728f 100644 --- 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 { @@ -116,7 +120,60 @@ public abstract class TransactionDAO extends BaseDAO { } @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 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 d1757a2b..fcbdd82b 100644 --- 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 d556731f..abcaa034 100644 --- 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; + } } -- 2.39.2