more fall-outs after transition to surrogate IDs

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 b3f38355a005089c5d98052175e73b887b324ade..e0b4e2eb1e6df6f5cf0dfcc868e55e325fe620b6 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java
+++ b/app/src/main/java/net/ktnx/mobileledger/async/RetrieveTransactionsTask.java
@@ -1,5 +1,5 @@
 /*
- * Copyright © 2020 Damyan Ivanov.
+ * Copyright © 2021 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
@@ -555,7 +555,7 @@ public class RetrieveTransactionsTask extends
                         .compareTo(o1.getDate());
             if (res != 0)
                 return res;
-            return Integer.compare(o2.getId(), o1.getId());
+            return Long.compare(o2.getId(), o1.getId());
         });
         return trList;
     }

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 fb773bf81055dcd8c26608f0764478d42cce9337..001c107e0193b2a5c5a272bb6c60ec232a1a5e74 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java
+++ b/app/src/main/java/net/ktnx/mobileledger/model/LedgerTransaction.java
@@ -53,18 +53,17 @@ public class LedgerTransaction {
         return Float.compare(o1.getAmount(), o2.getAmount());
     };
     private final long profile;
-    private final Integer id;
+    private final long id;
     private final List<LedgerTransactionAccount> accounts;
     private SimpleDate date;
     private String description;
     private String comment;
     private String dataHash;
     private boolean dataLoaded;
-    public LedgerTransaction(Integer id, String dateString, String description)
-            throws ParseException {
+    public LedgerTransaction(long id, String dateString, String description) throws ParseException {
         this(id, Globals.parseLedgerDate(dateString), description);
     }
-    public LedgerTransaction(Integer id, SimpleDate date, String description,
+    public LedgerTransaction(long id, SimpleDate date, String description,
                              MobileLedgerProfile profile) {
         this.profile = profile.getId();
         this.id = id;
@@ -74,11 +73,11 @@ public class LedgerTransaction {
         this.dataHash = null;
         dataLoaded = false;
     }
-    public LedgerTransaction(Integer id, SimpleDate date, String description) {
+    public LedgerTransaction(long id, SimpleDate date, String description) {
         this(id, date, description, Data.getProfile());
     }
     public LedgerTransaction(SimpleDate date, String description) {
-        this(null, date, description);
+        this(0, date, description);
     }
     public LedgerTransaction(int id) {
         this(id, (SimpleDate) null, null);
@@ -126,7 +125,7 @@ public class LedgerTransaction {
     public void setComment(String comment) {
         this.comment = comment;
     }
-    public int getId() {
+    public long getId() {
         return id;
     }
     protected void fillDataHash() {

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 809d7cc469268f3935370f97ef94c9f1563a2570..15be74b6be1c572ba9c4e65d4a94e84dab2bfcbd 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java
+++ b/app/src/main/java/net/ktnx/mobileledger/model/MobileLedgerProfile.java
@@ -366,12 +366,12 @@ public final class MobileLedgerProfile {
             sql += ", expanded=?";
             params.add(acc.isExpanded() ? 1 : 0);
         }
-        sql += " where profile=? and name=?";
+        sql += " where profile_id=? and name=?";
         params.add(id);
         params.add(acc.getName());
         db.execSQL(sql, params.toArray());
 
-        db.execSQL("insert into accounts(profile, name, name_upper, parent_name, level, " +
+        db.execSQL("insert into accounts(profile_id, name, name_upper, parent_name, level, " +
                    "expanded, generation) select ?,?,?,?,?,0,? where (select changes() = 0)",
                 new Object[]{id, acc.getName(), acc.getName().toUpperCase(), acc.getParentName(),
                              acc.getLevel(), generation
@@ -398,22 +398,42 @@ public final class MobileLedgerProfile {
             }
         }
 
-        db.execSQL("replace into account_values(profile, account, " +
-                   "currency, value, generation) values(?, ?, ?, ?, ?);",
-                new Object[]{id, name, Misc.emptyIsNull(currency), amount, generation});
+        long accId = findAddAccount(db, name);
+
+        db.execSQL("replace into account_values(account_id, " +
+                   "currency, value, generation) values(?, ?, ?, ?);",
+                new Object[]{accId, Misc.emptyIsNull(currency), amount, generation});
+    }
+    private long findAddAccount(SQLiteDatabase db, String accountName) {
+        try (Cursor c = db.rawQuery("select id from accounts where profile_id=? and name=?",
+                new String[]{String.valueOf(id), accountName}))
+        {
+            if (c.moveToFirst())
+                return c.getLong(0);
+
+        }
+
+        try (Cursor c = db.rawQuery(
+                "insert into accounts(profile_id, name, name_upper) values(?, ?, ?) returning id",
+                new String[]{String.valueOf(id), accountName, accountName.toUpperCase()}))
+        {
+            c.moveToFirst();
+            return c.getInt(0);
+        }
     }
     public void storeTransaction(SQLiteDatabase db, int generation, LedgerTransaction tr) {
         tr.fillDataHash();
 //        Logger.debug("storeTransaction", String.format(Locale.US, "ID %d", tr.getId()));
         SimpleDate d = tr.getDate();
         db.execSQL("UPDATE transactions SET year=?, month=?, day=?, description=?, comment=?, " +
-                   "data_hash=?, generation=? WHERE profile=? AND id=?",
+                   "data_hash=?, generation=? WHERE profile_id=? AND ledger_id=?",
                 new Object[]{d.year, d.month, d.day, tr.getDescription(), tr.getComment(),
                              tr.getDataHash(), generation, id, tr.getId()
                 });
-        db.execSQL("INSERT INTO transactions(profile, id, year, month, day, description, " +
-                   "comment, data_hash, generation) " +
-                   "select ?,?,?,?,?,?,?,?,? WHERE (select changes() = 0)",
+        db.execSQL(
+                "INSERT INTO transactions(profile_id, ledger_id, year, month, day, description, " +
+                "comment, data_hash, generation) " +
+                "select ?,?,?,?,?,?,?,?,? WHERE (select changes() = 0)",
                 new Object[]{id, tr.getId(), tr.getDate().year, tr.getDate().month,
                              tr.getDate().day, tr.getDescription(), tr.getComment(),
                              tr.getDataHash(), generation
@@ -422,16 +442,15 @@ public final class MobileLedgerProfile {
         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=?",
+                       "comment=?, generation=? " + "WHERE transaction_id=? AND order_no=?",
                     new Object[]{item.getAccountName(), item.getAmount(),
                                  Misc.nullIsEmpty(item.getCurrency()), item.getComment(),
-                                 generation, id, tr.getId(), accountOrderNo
+                                 generation, tr.getId(), accountOrderNo
                     });
-            db.execSQL("INSERT INTO transaction_accounts(profile, transaction_id, " +
+            db.execSQL("INSERT INTO transaction_accounts(transaction_id, " +
                        "order_no, account_name, amount, currency, comment, generation) " +
                        "select ?, ?, ?, ?, ?, ?, ?, ? WHERE (select changes() = 0)",
-                    new Object[]{id, tr.getId(), accountOrderNo, item.getAccountName(),
+                    new Object[]{tr.getId(), accountOrderNo, item.getAccountName(),
                                  item.getAmount(), Misc.nullIsEmpty(item.getCurrency()),
                                  item.getComment(), generation
                     });
@@ -488,7 +507,7 @@ public final class MobileLedgerProfile {
     }
     public void setOption(String name, String value) {
         debug("profile", String.format("setting option %s=%s", name, value));
-        DbOpQueue.add("insert or replace into options(profile, name, value) values(?, ?, ?);",
+        DbOpQueue.add("insert or replace into options(profile_id, name, value) values(?, ?, ?);",
                 new String[]{String.valueOf(id), name, value});
     }
     public void setLongOption(String name, long value) {
@@ -500,12 +519,10 @@ public final class MobileLedgerProfile {
         db.beginTransactionNonExclusive();
         try {
             Object[] id_param = new Object[]{id};
-            db.execSQL("delete from transaction_accounts where profile=?", id_param);
-            db.execSQL("delete from transactions where profile=?", id_param);
-            db.execSQL("delete from account_values where profile=?", id_param);
+            db.execSQL("delete from transactions where profile_id=?", id_param);
             db.execSQL("delete from accounts where profile=?", id_param);
             db.execSQL("delete from options where profile=?", id_param);
-            db.execSQL("delete from profiles where uuid=?", id_param);
+            db.execSQL("delete from profiles where id=?", id_param);
             db.setTransactionSuccessful();
         }
         finally {
@@ -531,7 +548,8 @@ public final class MobileLedgerProfile {
     }
     public int getNextTransactionsGeneration(SQLiteDatabase db) {
         int generation = 1;
-        try (Cursor c = db.rawQuery("SELECT generation FROM transactions WHERE profile=? LIMIT 1",
+        try (Cursor c = db.rawQuery(
+                "SELECT generation FROM transactions WHERE profile_id=? LIMIT 1",
                 new String[]{String.valueOf(id)}))
         {
             if (c.moveToFirst()) {
@@ -542,7 +560,7 @@ public final class MobileLedgerProfile {
     }
     private int getNextAccountsGeneration(SQLiteDatabase db) {
         int generation = 1;
-        try (Cursor c = db.rawQuery("SELECT generation FROM accounts WHERE profile=? LIMIT 1",
+        try (Cursor c = db.rawQuery("SELECT generation FROM accounts WHERE profile_id=? LIMIT 1",
                 new String[]{String.valueOf(id)}))
         {
             if (c.moveToFirst()) {

diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/new_transaction/NewTransactionModel.java b/app/src/main/java/net/ktnx/mobileledger/ui/new_transaction/NewTransactionModel.java
index 641a9b7b8686271f78e8b65f444755dcb77ed456..fdcab48788b94f6336f41254298f92c02748e4c6 100644 (file)
--- a/app/src/main/java/net/ktnx/mobileledger/ui/new_transaction/NewTransactionModel.java
+++ b/app/src/main/java/net/ktnx/mobileledger/ui/new_transaction/NewTransactionModel.java
@@ -1031,7 +1031,7 @@ public class NewTransactionModel extends ViewModel {
             return ItemType.generalData;
         }
         public LedgerTransaction asLedgerTransaction() {
-            return new LedgerTransaction(null, date, description, Data.getProfile());
+            return new LedgerTransaction(0, date, description, Data.getProfile());
         }
         public boolean equalContents(TransactionHead other) {
             if (other == null)

diff --git a/app/src/main/res/raw/db_59.sql b/app/src/main/res/raw/db_59.sql
index 27ad83b26f62c70ba52a8fee5d38400fa2e989be..5ba11afffaf9281705dc969abc16fed7e96fe8de 100644 (file)
--- a/app/src/main/res/raw/db_59.sql
+++ b/app/src/main/res/raw/db_59.sql
@@ -94,6 +94,16 @@ select o.name, p.id, o.value
 from options o
 join profiles p on p.deprecated_uuid = o.profile;
 
+insert into options_new(name, profile_id, value)
+select o.name, 0, o.value
+from options o
+where o.profile='-';
+
+update options_new
+set name='profile_id'
+  , value=(select p.id from profiles p where p.deprecated_uuid=value)
+where name='profile_uuid';
+
 drop table options;
 alter table options_new rename to options;