From 4f33c34223e62ce884916ac5cfa3969684807bb8 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Sun, 6 Sep 2020 11:47:41 +0300 Subject: [PATCH] speed up parsed transaction/accounts storage new approach avoids mass deletion followed by mass inserts --- .../model/MobileLedgerProfile.java | 171 ++++++++++-------- .../utils/MobileLedgerDatabase.java | 3 +- app/src/main/res/raw/create_db.sql | 13 +- app/src/main/res/raw/sql_36.sql | 18 ++ app/src/main/res/raw/sql_37.sql | 17 ++ 5 files changed, 142 insertions(+), 80 deletions(-) create mode 100644 app/src/main/res/raw/sql_36.sql create mode 100644 app/src/main/res/raw/sql_37.sql diff --git a/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java b/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java index 7214e2b0..373f1325 100644 --- a/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java +++ b/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java @@ -37,6 +37,7 @@ import net.ktnx.mobileledger.utils.Locker; import net.ktnx.mobileledger.utils.Logger; import net.ktnx.mobileledger.utils.MLDB; import net.ktnx.mobileledger.utils.Misc; +import net.ktnx.mobileledger.utils.SimpleDate; import org.jetbrains.annotations.Contract; @@ -421,11 +422,13 @@ public final class MobileLedgerProfile { db.endTransaction(); } } - public void storeAccount(SQLiteDatabase db, LedgerAccount acc, boolean storeUiFields) { + public void storeAccount(SQLiteDatabase db, int generation, LedgerAccount acc, + boolean storeUiFields) { // replace into is a bad idea because it would reset hidden to its default value // we like the default, but for new accounts only - String sql = "update accounts set keep = 1"; + String sql = "update accounts set generation = ?"; List params = new ArrayList<>(); + params.add(generation); if (storeUiFields) { sql += ", expanded=?"; params.add(acc.isExpanded() ? 1 : 0); @@ -436,37 +439,52 @@ public final class MobileLedgerProfile { db.execSQL(sql, params.toArray()); db.execSQL("insert into accounts(profile, name, name_upper, parent_name, level, " + - "expanded, keep) " + "select ?,?,?,?,?,0,1 where (select changes() = 0)", + "expanded, generation) select ?,?,?,?,?,0,? where (select changes() = 0)", new Object[]{uuid, acc.getName(), acc.getName().toUpperCase(), acc.getParentName(), - acc.getLevel() + acc.getLevel(), generation }); // debug("accounts", String.format("Stored account '%s' in DB [%s]", acc.getName(), uuid)); } - public void storeAccountValue(SQLiteDatabase db, String name, String currency, Float amount) { + public void storeAccountValue(SQLiteDatabase db, int generation, String name, String currency, + Float amount) { db.execSQL("replace into account_values(profile, account, " + - "currency, value, keep) values(?, ?, ?, ?, 1);", - new Object[]{uuid, name, Misc.emptyIsNull(currency), amount}); + "currency, value, generation) values(?, ?, ?, ?, ?);", + new Object[]{uuid, name, Misc.emptyIsNull(currency), amount, generation}); } - public void storeTransaction(SQLiteDatabase db, LedgerTransaction tr) { + public void storeTransaction(SQLiteDatabase db, int generation, LedgerTransaction tr) { tr.fillDataHash(); - db.execSQL("DELETE from transactions WHERE profile=? and id=?", - new Object[]{uuid, tr.getId()}); - db.execSQL("DELETE from transaction_accounts WHERE profile = ? and transaction_id=?", - new Object[]{uuid, tr.getId()}); - + SimpleDate d = tr.getDate(); + db.execSQL("UPDATE transactions SET year=?, month=?, day=?, description=?, comment=?, " + + "data_hash=?, generation=? WHERE profile=? AND id=?", + new Object[]{d.year, d.month, d.day, tr.getDescription(), tr.getComment(), + tr.getDataHash(), generation, uuid, tr.getId() + }); db.execSQL("INSERT INTO transactions(profile, id, year, month, day, description, " + - "comment, data_hash, keep) values(?,?,?,?,?,?,?,?,1)", + "comment, data_hash, generation) " + + "select ?,?,?,?,?,?,?,?,? WHERE (select changes() = 0)", new Object[]{uuid, tr.getId(), tr.getDate().year, tr.getDate().month, tr.getDate().day, tr.getDescription(), tr.getComment(), - tr.getDataHash() + tr.getDataHash(), generation }); + int accountOrderNo = 1; for (LedgerTransactionAccount item : tr.getAccounts()) { + db.execSQL("UPDATE transaction_accounts SET account_name=?, amount=?, currency=?, " + + "comment=?, generation=? " + + "WHERE profile=? AND transaction_id=? AND order_no=?", + new Object[]{item.getAccountName(), item.getAmount(), + Misc.nullIsEmpty(item.getCurrency()), item.getComment(), + generation, uuid, tr.getId(), accountOrderNo + }); db.execSQL("INSERT INTO transaction_accounts(profile, transaction_id, " + - "account_name, amount, currency, comment) values(?, ?, ?, ?, ?, ?)", - new Object[]{uuid, tr.getId(), item.getAccountName(), item.getAmount(), - Misc.nullIsEmpty(item.getCurrency()), item.getComment() + "order_no, account_name, amount, currency, comment, generation) " + + "select ?, ?, ?, ?, ?, ?, ?, ? WHERE (select changes() = 0)", + new Object[]{uuid, tr.getId(), accountOrderNo, item.getAccountName(), + item.getAmount(), Misc.nullIsEmpty(item.getCurrency()), + item.getComment(), generation }); + + accountOrderNo++; } // debug("profile", String.format("Transaction %d stored", tr.getId())); } @@ -558,33 +576,43 @@ public final class MobileLedgerProfile { // debug("profile", String.format("Profile.setThemeHue(%d) called", themeHue)); this.themeHue = themeHue; } - public void markTransactionsAsNotPresent(SQLiteDatabase db) { - db.execSQL("UPDATE transactions set keep=0 where profile=?", new String[]{uuid}); - - } - private void markAccountsAsNotPresent(SQLiteDatabase db) { - db.execSQL("update account_values set keep=0 where profile=?;", new String[]{uuid}); - db.execSQL("update accounts set keep=0 where profile=?;", new String[]{uuid}); - - } - private void deleteNotPresentAccounts(SQLiteDatabase db) { - db.execSQL("delete from account_values where keep=0 and profile=?", new String[]{uuid}); - db.execSQL("delete from accounts where keep=0 and profile=?", new String[]{uuid}); - } - private void markTransactionAsPresent(SQLiteDatabase db, LedgerTransaction transaction) { - db.execSQL("UPDATE transactions SET keep = 1 WHERE profile = ? and id=?", - new Object[]{uuid, transaction.getId() - }); - } - private void markTransactionsBeforeTransactionAsPresent(SQLiteDatabase db, - LedgerTransaction transaction) { - db.execSQL("UPDATE transactions SET keep=1 WHERE profile = ? and id < ?", - new Object[]{uuid, transaction.getId() - }); - + public int getNextTransactionsGeneration(SQLiteDatabase db) { + int generation = 1; + try (Cursor c = db.rawQuery("SELECT generation FROM transactions WHERE profile=? LIMIT 1", + new String[]{uuid})) + { + if (c.moveToFirst()) { + generation = c.getInt(0) + 1; + } + } + return generation; } - private void deleteNotPresentTransactions(SQLiteDatabase db) { - db.execSQL("DELETE FROM transactions WHERE profile=? AND keep = 0", new String[]{uuid}); + private int getNextAccountsGeneration(SQLiteDatabase db) { + int generation = 1; + try (Cursor c = db.rawQuery("SELECT generation FROM accounts WHERE profile=? LIMIT 1", + new String[]{uuid})) + { + if (c.moveToFirst()) { + generation = c.getInt(0) + 1; + } + } + return generation; + } + private void deleteNotPresentAccounts(SQLiteDatabase db, int generation) { + Logger.debug("db/benchmark", "Deleting obsolete accounts"); + db.execSQL("DELETE FROM account_values WHERE profile=? AND generation <> ?", + new Object[]{uuid, generation}); + db.execSQL("DELETE FROM accounts WHERE profile=? AND generation <> ?", + new Object[]{uuid, generation}); + Logger.debug("db/benchmark", "Done deleting obsolete accounts"); + } + private void deleteNotPresentTransactions(SQLiteDatabase db, int generation) { + Logger.debug("db/benchmark", "Deleting obsolete transactions"); + db.execSQL("DELETE FROM transaction_accounts WHERE profile=? AND generation <> ?", + new Object[]{uuid, generation}); + db.execSQL("DELETE FROM transactions WHERE profile=? AND generation <> ?", + new Object[]{uuid, generation}); + Logger.debug("db/benchmark", "Done deleting obsolete transactions"); } private void setLastUpdateStamp() { debug("db", "Updating transaction value stamp"); @@ -676,14 +704,19 @@ public final class MobileLedgerProfile { SQLiteDatabase db = App.getDatabase(); db.beginTransactionNonExclusive(); try { - markAccountsAsNotPresent(db); + Logger.debug("db/benchmark", + String.format(Locale.US, "Storing %d accounts", list.size())); + int gen = getNextAccountsGeneration(db); + Logger.debug("db/benckmark", + String.format(Locale.US, "Got next generation of %d", gen)); for (LedgerAccount acc : list) { - storeAccount(db, acc, false); + storeAccount(db, gen, acc, false); for (LedgerAmount amt : acc.getAmounts()) { - storeAccountValue(db, acc.getName(), amt.getCurrency(), amt.getAmount()); + storeAccountValue(db, gen, acc.getName(), amt.getCurrency(), amt.getAmount()); } } - deleteNotPresentAccounts(db); + Logger.debug("db/benchmark", "Done storing accounts"); + deleteNotPresentAccounts(db, gen); setLastUpdateStamp(); db.setTransactionSuccessful(); } @@ -700,19 +733,6 @@ public final class MobileLedgerProfile { } public void setAndStoreTransactionList(ArrayList list) { storeTransactionListAsync(this, list); - SQLiteDatabase db = App.getDatabase(); - db.beginTransactionNonExclusive(); - try { - markTransactionsAsNotPresent(db); - for (LedgerTransaction tr : list) - storeTransaction(db, tr); - deleteNotPresentTransactions(db); - setLastUpdateStamp(); - db.setTransactionSuccessful(); - } - finally { - db.endTransaction(); - } allTransactions.postValue(list); } @@ -924,15 +944,15 @@ public final class MobileLedgerProfile { SQLiteDatabase db = App.getDatabase(); db.beginTransactionNonExclusive(); try { - profile.markAccountsAsNotPresent(db); + int generation = profile.getNextAccountsGeneration(db); if (isInterrupted()) return; for (LedgerAccount acc : list) { - profile.storeAccount(db, acc, storeUiFields); + profile.storeAccount(db, generation, acc, storeUiFields); if (isInterrupted()) return; } - profile.deleteNotPresentAccounts(db); + profile.deleteNotPresentAccounts(db, generation); if (isInterrupted()) return; profile.setLastUpdateStamp(); @@ -956,15 +976,20 @@ public final class MobileLedgerProfile { SQLiteDatabase db = App.getDatabase(); db.beginTransactionNonExclusive(); try { - profile.markTransactionsAsNotPresent(db); + Logger.debug("db/benchmark", + String.format(Locale.US, "Storing %d transactions", list.size())); + int generation = profile.getNextTransactionsGeneration(db); + Logger.debug("db/benchmark", + String.format(Locale.US, "Got next generation of %d", generation)); if (isInterrupted()) return; for (LedgerTransaction tr : list) { - profile.storeTransaction(db, tr); + profile.storeTransaction(db, generation, tr); if (isInterrupted()) return; } - profile.deleteNotPresentTransactions(db); + Logger.debug("db/benchmark", "Done storing transactions"); + profile.deleteNotPresentTransactions(db, generation); if (isInterrupted()) return; profile.setLastUpdateStamp(); @@ -994,33 +1019,33 @@ public final class MobileLedgerProfile { SQLiteDatabase db = App.getDatabase(); db.beginTransactionNonExclusive(); try { - profile.markAccountsAsNotPresent(db); + int accountsGeneration = profile.getNextAccountsGeneration(db); if (isInterrupted()) return; - profile.markTransactionsAsNotPresent(db); + int transactionsGeneration = profile.getNextTransactionsGeneration(db); if (isInterrupted()) { return; } for (LedgerAccount acc : accounts) { - profile.storeAccount(db, acc, storeAccUiFields); + profile.storeAccount(db, accountsGeneration, acc, storeAccUiFields); if (isInterrupted()) return; } for (LedgerTransaction tr : transactions) { - profile.storeTransaction(db, tr); + profile.storeTransaction(db, transactionsGeneration, tr); if (isInterrupted()) { return; } } - profile.deleteNotPresentAccounts(db); + profile.deleteNotPresentAccounts(db, accountsGeneration); if (isInterrupted()) { return; } - profile.deleteNotPresentTransactions(db); + profile.deleteNotPresentTransactions(db, transactionsGeneration); if (isInterrupted()) return; diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java index 1b17ca23..fc03a9f8 100644 --- a/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java +++ b/app/src/main/java/net/ktnx/mobileledger/utils/MobileLedgerDatabase.java @@ -34,7 +34,7 @@ import static net.ktnx.mobileledger.utils.Logger.debug; public class MobileLedgerDatabase extends SQLiteOpenHelper { private static final String DB_NAME = "MoLe.db"; - private static final int LATEST_REVISION = 35; + private static final int LATEST_REVISION = 37; private static final String CREATE_DB_SQL = "create_db"; private final Application mContext; @@ -62,6 +62,7 @@ public class MobileLedgerDatabase extends SQLiteOpenHelper { public void onOpen(SQLiteDatabase db) { super.onOpen(db); db.execSQL("pragma case_sensitive_like=ON;"); +// db.execSQL("PRAGMA foreign_keys=ON"); } private void applyRevision(SQLiteDatabase db, int rev_no) { diff --git a/app/src/main/res/raw/create_db.sql b/app/src/main/res/raw/create_db.sql index 14a0311a..69be40d3 100644 --- a/app/src/main/res/raw/create_db.sql +++ b/app/src/main/res/raw/create_db.sql @@ -12,18 +12,19 @@ -- -- You should have received a copy of the GNU General Public License -- along with MoLe. If not, see . -create table accounts(profile varchar not null, name varchar not null, name_upper varchar not null, keep boolean not null default 0, level integer not null, parent_name varchar, expanded default 1, amounts_expanded boolean default 0); +create table accounts(profile varchar not null, name varchar not null, name_upper varchar not null, level integer not null, parent_name varchar, expanded default 1, amounts_expanded boolean default 0, generation integer default 0); create unique index un_accounts on accounts(profile, name); create table options(profile varchar not null, name varchar not null, value varchar); create unique index un_options on options(profile,name); -create table account_values(profile varchar not null, account varchar not null, currency varchar not null default '', keep boolean, value decimal not null ); +create table account_values(profile varchar not null, account varchar not null, currency varchar not null default '', value decimal not null, generation integer default 0 ); create unique index un_account_values on account_values(profile,account,currency); -create table description_history(description varchar not null primary key, keep boolean, description_upper varchar); +create table description_history(description varchar not null primary key, description_upper varchar); create table profiles(uuid varchar not null primary key, name not null, url not null, use_authentication boolean not null, auth_user varchar, auth_password varchar, order_no integer, permit_posting boolean default 0, theme integer default -1, preferred_accounts_filter varchar, future_dates integer, api_version integer, show_commodity_by_default boolean default 0, default_commodity varchar, show_comments_by_default boolean default 1); -create table transactions(profile varchar not null, id integer not null, data_hash varchar not null, year integer not null, month integer not null, day integer not null, description varchar not null, comment varchar, keep boolean not null default 0); +create table transactions(profile varchar not null, id integer not null, data_hash varchar not null, year integer not null, month integer not null, day integer not null, description varchar not null, comment varchar, generation integer default 0); create unique index un_transactions_id on transactions(profile,id); create unique index un_transactions_data_hash on transactions(profile,data_hash); create index idx_transaction_description on transactions(description); -create table transaction_accounts(profile varchar not null, transaction_id integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, comment varchar, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,account_name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id)); +create table transaction_accounts(profile varchar not null, transaction_id integer not null, order_no integer not null, account_name varchar not null, currency varchar not null default '', amount decimal not null, comment varchar, constraint fk_transaction_accounts_acc foreign key(profile,account_name) references accounts(profile,account_name), constraint fk_transaction_accounts_trn foreign key(profile, transaction_id) references transactions(profile,id), generation integer default 0); +create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); create table currencies(id integer not null primary key, name varchar not null, position varchar not null, has_gap boolean not null); --- updated to revision 35 \ No newline at end of file +-- updated to revision 37 \ No newline at end of file diff --git a/app/src/main/res/raw/sql_36.sql b/app/src/main/res/raw/sql_36.sql new file mode 100644 index 00000000..378eddc7 --- /dev/null +++ b/app/src/main/res/raw/sql_36.sql @@ -0,0 +1,18 @@ +-- Copyright © 2020 Damyan Ivanov. +-- This file is part of MoLe. +-- MoLe is free software: you can distribute it and/or modify it +-- under the term of the GNU General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your opinion), any later version. +-- +-- MoLe is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License terms for details. +-- +-- You should have received a copy of the GNU General Public License +-- along with MoLe. If not, see . +alter table accounts add generation integer default 0; +alter table account_values add generation integer default 0; +alter table transactions add generation integer default 0; +alter table transaction_accounts add generation integer default 0; diff --git a/app/src/main/res/raw/sql_37.sql b/app/src/main/res/raw/sql_37.sql new file mode 100644 index 00000000..60b6e930 --- /dev/null +++ b/app/src/main/res/raw/sql_37.sql @@ -0,0 +1,17 @@ +-- Copyright © 2020 Damyan Ivanov. +-- This file is part of MoLe. +-- MoLe is free software: you can distribute it and/or modify it +-- under the term of the GNU General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your opinion), any later version. +-- +-- MoLe is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License terms for details. +-- +-- You should have received a copy of the GNU General Public License +-- along with MoLe. If not, see . +alter table transaction_accounts add order_no integer not null default 0; +update transaction_accounts set order_no = rowid; +create unique index un_transaction_accounts_order on transaction_accounts(profile, transaction_id, order_no); -- 2.39.2