From 9b96a8e36b9db5061ab49578cdbac1b465558dfa Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Mon, 22 Mar 2021 07:41:42 +0200 Subject: [PATCH] another step towards surrogate ID db objects MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit profile → profile_id in more places transactions.id is globally unique; ledger-supplied ID (profile-specific) is in transactions.ledger_id --- .../ktnx/mobileledger/async/UpdateTransactionsTask.java | 5 +++-- .../net/ktnx/mobileledger/model/LedgerTransaction.java | 8 ++++---- .../net/ktnx/mobileledger/model/MobileLedgerProfile.java | 3 ++- .../main/java/net/ktnx/mobileledger/ui/MainModel.java | 9 +++++---- .../ui/account_summary/AccountSummaryAdapter.java | 2 +- app/src/main/res/raw/db_59.sql | 6 ++++-- 6 files changed, 19 insertions(+), 14 deletions(-) diff --git a/app/src/main/java/net/ktnx/mobileledger/async/UpdateTransactionsTask.java b/app/src/main/java/net/ktnx/mobileledger/async/UpdateTransactionsTask.java index b6e9c3ed..9e7c8101 100644 --- a/app/src/main/java/net/ktnx/mobileledger/async/UpdateTransactionsTask.java +++ b/app/src/main/java/net/ktnx/mobileledger/async/UpdateTransactionsTask.java @@ -43,7 +43,7 @@ public class UpdateTransactionsTask extends AsyncTask { final String accFilter = model[0].getAccountFilter() .getValue(); if (accFilter == null) { - sql = "SELECT id, year, month, day FROM transactions WHERE profile=? ORDER BY " + + sql = "SELECT id, year, month, day FROM transactions WHERE profile_id=? ORDER BY " + "year desc, month desc, day desc, id desc"; params = new String[]{String.valueOf(profile_id)}; @@ -51,7 +51,8 @@ public class UpdateTransactionsTask extends AsyncTask { else { sql = "SELECT distinct tr.id, tr.year, tr.month, tr.day from transactions tr " + "JOIN " + "transaction_accounts ta " + - "ON ta.transaction_id=tr.id AND ta.profile=tr.profile WHERE tr.profile=? " + + "ON ta.transaction_id=tr.id AND ta.profile=tr.profile WHERE tr.profile_id=?" + + " " + "and ta.account_name LIKE ?||'%' AND ta" + ".amount <> 0 ORDER BY tr.year desc, tr.month desc, tr.day desc, tr.id " + "desc"; diff --git a/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java b/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java index 2eba45e9..fb773bf8 100644 --- a/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java +++ b/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java @@ -169,8 +169,8 @@ public class LedgerTransaction { return; try (Cursor cTr = db.rawQuery( - "SELECT year, month, day, description, comment from transactions WHERE profile=? " + - "AND id=?", new String[]{String.valueOf(profile), String.valueOf(id)})) + "SELECT year, month, day, description, comment from transactions WHERE id=?", + new String[]{String.valueOf(id)})) { if (cTr.moveToFirst()) { date = new SimpleDate(cTr.getInt(0), cTr.getInt(1), cTr.getInt(2)); @@ -181,8 +181,8 @@ public class LedgerTransaction { try (Cursor cAcc = db.rawQuery( "SELECT account_name, amount, currency, comment FROM " + - "transaction_accounts WHERE profile=? AND transaction_id = ?", - new String[]{String.valueOf(profile), String.valueOf(id)})) + "transaction_accounts WHERE transaction_id = ?", + new String[]{String.valueOf(id)})) { while (cAcc.moveToNext()) { // debug("transactions", 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 fddacf67..809d7cc4 100644 --- a/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java +++ b/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java @@ -442,7 +442,8 @@ public final class MobileLedgerProfile { } public String getOption(String name, String default_value) { SQLiteDatabase db = App.getDatabase(); - try (Cursor cursor = db.rawQuery("select value from options where profile = ? and name=?", + try (Cursor cursor = db.rawQuery( + "select value from options where profile_id = ? and name=?", new String[]{String.valueOf(id), name})) { if (cursor.moveToFirst()) { diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/MainModel.java b/app/src/main/java/net/ktnx/mobileledger/ui/MainModel.java index 813e3a00..59cad326 100644 --- a/app/src/main/java/net/ktnx/mobileledger/ui/MainModel.java +++ b/app/src/main/java/net/ktnx/mobileledger/ui/MainModel.java @@ -290,8 +290,8 @@ public class MainModel extends ViewModel { ArrayList list = new ArrayList<>(); HashMap map = new HashMap<>(); - String sql = "SELECT a.name, a.expanded, a.amounts_expanded"; - sql += " from accounts a WHERE a.profile = ?"; + String sql = "SELECT a.name, a.expanded, a.amounts_expanded, a.id"; + sql += " from accounts a WHERE a.profile_id = ?"; sql += " ORDER BY a.name"; SQLiteDatabase db = App.getDatabase(); @@ -302,6 +302,7 @@ public class MainModel extends ViewModel { if (isInterrupted()) return; + final long accId = cursor.getLong(3); final String accName = cursor.getString(0); // debug("accounts", // String.format("Read account '%s' from DB [%s]", accName, @@ -325,8 +326,8 @@ public class MainModel extends ViewModel { acc.setHasSubAccounts(false); try (Cursor c2 = db.rawQuery( - "SELECT value, currency FROM account_values WHERE profile = ?" + " " + - "AND account = ?", new String[]{String.valueOf(profileId), accName})) + "SELECT value, currency FROM account_values WHERE account_id = ?", + new String[]{String.valueOf(accId)})) { while (c2.moveToNext()) { acc.addAmount(c2.getFloat(0), c2.getString(1)); diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/account_summary/AccountSummaryAdapter.java b/app/src/main/java/net/ktnx/mobileledger/ui/account_summary/AccountSummaryAdapter.java index 20b72915..fa089878 100644 --- a/app/src/main/java/net/ktnx/mobileledger/ui/account_summary/AccountSummaryAdapter.java +++ b/app/src/main/java/net/ktnx/mobileledger/ui/account_summary/AccountSummaryAdapter.java @@ -168,7 +168,7 @@ public class AccountSummaryAdapter .rotation(mAccount.isExpanded() ? 0 : 180); model.updateDisplayedAccounts(); - DbOpQueue.add("update accounts set expanded=? where name=? and profile=?", + DbOpQueue.add("update accounts set expanded=? where name=? and profile_id=?", new Object[]{mAccount.isExpanded(), mAccount.getName(), profile.getId() }); diff --git a/app/src/main/res/raw/db_59.sql b/app/src/main/res/raw/db_59.sql index 8f5d8009..115f3ce9 100644 --- a/app/src/main/res/raw/db_59.sql +++ b/app/src/main/res/raw/db_59.sql @@ -153,8 +153,10 @@ create table transaction_accounts_new( insert into transaction_accounts_new(transaction_id, order_no, account_name, currency, amount, comment, generation) -select ta.transaction_id, ta.order_no, ta.account_name, ta.currency, ta.amount, ta.comment, ta.generation -from transaction_accounts ta; +select t.id, ta.order_no, ta.account_name, ta.currency, ta.amount, ta.comment, ta.generation +from transaction_accounts ta +join profiles p in ta.profile=p.deprecated_uuid +join transactions t on ta.transaction_id = t.ledger_id and t.profile_id=p.id; drop table transaction_accounts; alter table transaction_accounts_new rename to transaction_accounts; -- 2.39.2