From 2de3d8a8c96e78f4ab89880be9fab05735acc910 Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Wed, 24 Mar 2021 20:53:05 +0200 Subject: [PATCH] more fall-outs after transition to surrogate IDs --- .../async/RetrieveTransactionsTask.java | 4 +- .../mobileledger/model/LedgerTransaction.java | 13 ++-- .../model/MobileLedgerProfile.java | 60 ++++++++++++------- .../new_transaction/NewTransactionModel.java | 2 +- app/src/main/res/raw/db_59.sql | 10 ++++ 5 files changed, 58 insertions(+), 31 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 b3f38355..e0b4e2eb 100644 --- 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 fb773bf8..001c107e 100644 --- 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 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 809d7cc4..15be74b6 100644 --- 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 641a9b7b..fdcab487 100644 --- 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 27ad83b2..5ba11aff 100644 --- 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; -- 2.39.2