another step towards surrogate ID db objects

profile → profile_id in more places
transactions.id is globally unique; ledger-supplied ID
(profile-specific) is in transactions.ledger_id

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 b6e9c3edad505f5d786a30388c0eaa26e4ede6fa..9e7c8101b669a91b106fbba811c101ebb11c27f3 100644 (file)
--- 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<MainModel, Void, String> {
             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<MainModel, Void, String> {
             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 2eba45e9cd4ea1e58d243b794c59d3547b2aaa81..fb773bf81055dcd8c26608f0764478d42cce9337 100644 (file)
--- 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 fddacf67d44e794bc84efcd97d2cefba22d3a255..809d7cc469268f3935370f97ef94c9f1563a2570 100644 (file)
--- 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 813e3a00b3ab54a49b480ccad5f9b5584d077cd5..59cad326f1719fa481c8a15748ff7bdd31c511a4 100644 (file)
--- 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<LedgerAccount> list = new ArrayList<>();
             HashMap<String, LedgerAccount> 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 20b72915b9224158cfb11d07d0caa3c5c71b4afa..fa089878d52ec35f6a4a07da9132530b930f112f 100644 (file)
--- 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 8f5d8009c7569a0008ea7ce6279c0cc840833cde..115f3ce9b4f5bb9a48feb2138b2e5964aa109b8e 100644 (file)
--- 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;