From: Damyan Ivanov <dam+mobileledger@ktnx.net>
Date: Sun, 21 Mar 2021 20:20:31 +0000 (+0200)
Subject: another step towards surrogate ID db objects
X-Git-Tag: v0.18.0~106
X-Git-Url: https://git.ktnx.net/?p=mobile-ledger.git;a=commitdiff_plain;h=87a1e58f79757bb0257c911e9bd77eb5d77a3e40

another step towards surrogate ID db objects

rename profile's uuid column to catch (and remove) all its usages
use default theme id for the initial theme id
---

diff --git a/app/schemas/net.ktnx.mobileledger.db.DB/59.json b/app/schemas/net.ktnx.mobileledger.db.DB/59.json
index 3ebd83f9..414bfbe2 100644
--- a/app/schemas/net.ktnx.mobileledger.db.DB/59.json
+++ b/app/schemas/net.ktnx.mobileledger.db.DB/59.json
@@ -2,7 +2,7 @@
   "formatVersion": 1,
   "database": {
     "version": 59,
-    "identityHash": "a56d86c03528ece865d81fd8171c819f",
+    "identityHash": "0ab4d8a73295b6337c52ea561994b1c8",
     "entities": [
       {
         "tableName": "templates",
@@ -375,7 +375,7 @@
       },
       {
         "tableName": "profiles",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `url` TEXT NOT NULL, `use_authentication` INTEGER NOT NULL, `auth_user` TEXT, `auth_password` TEXT, `order_no` INTEGER NOT NULL, `permit_posting` INTEGER NOT NULL, `theme` INTEGER NOT NULL DEFAULT -1, `preferred_accounts_filter` TEXT, `future_dates` INTEGER NOT NULL, `api_version` INTEGER NOT NULL, `show_commodity_by_default` INTEGER NOT NULL, `default_commodity` TEXT, `show_comments_by_default` INTEGER NOT NULL DEFAULT 1, `detected_version_pre_1_19` INTEGER NOT NULL, `detected_version_major` INTEGER NOT NULL, `detected_version_minor` INTEGER NOT NULL)",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `deprecated_uuid` TEXT, `url` TEXT NOT NULL, `use_authentication` INTEGER NOT NULL, `auth_user` TEXT, `auth_password` TEXT, `order_no` INTEGER NOT NULL, `permit_posting` INTEGER NOT NULL, `theme` INTEGER NOT NULL DEFAULT -1, `preferred_accounts_filter` TEXT, `future_dates` INTEGER NOT NULL, `api_version` INTEGER NOT NULL, `show_commodity_by_default` INTEGER NOT NULL, `default_commodity` TEXT, `show_comments_by_default` INTEGER NOT NULL DEFAULT 1, `detected_version_pre_1_19` INTEGER NOT NULL, `detected_version_major` INTEGER NOT NULL, `detected_version_minor` INTEGER NOT NULL)",
         "fields": [
           {
             "fieldPath": "id",
@@ -389,6 +389,12 @@
             "affinity": "TEXT",
             "notNull": true
           },
+          {
+            "fieldPath": "deprecatedUUID",
+            "columnName": "deprecated_uuid",
+            "affinity": "TEXT",
+            "notNull": false
+          },
           {
             "fieldPath": "url",
             "columnName": "url",
@@ -499,11 +505,11 @@
       },
       {
         "tableName": "options",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`profile` INTEGER NOT NULL, `name` TEXT NOT NULL, `value` TEXT, PRIMARY KEY(`profile`, `name`))",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`profile_id` INTEGER NOT NULL, `name` TEXT NOT NULL, `value` TEXT, PRIMARY KEY(`profile_id`, `name`))",
         "fields": [
           {
-            "fieldPath": "profile",
-            "columnName": "profile",
+            "fieldPath": "profileId",
+            "columnName": "profile_id",
             "affinity": "INTEGER",
             "notNull": true
           },
@@ -522,7 +528,7 @@
         ],
         "primaryKey": {
           "columnNames": [
-            "profile",
+            "profile_id",
             "name"
           ],
           "autoGenerate": false
@@ -641,7 +647,7 @@
       },
       {
         "tableName": "transactions",
-        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `profile_id` INTEGER NOT NULL, `data_hash` TEXT NOT NULL, `year` INTEGER NOT NULL, `month` INTEGER NOT NULL, `day` INTEGER NOT NULL, `description` TEXT NOT NULL COLLATE NOCASE, `comment` TEXT, `generation` INTEGER NOT NULL, FOREIGN KEY(`profile_id`) REFERENCES `profiles`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
+        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `ledger_id` INTEGER NOT NULL, `profile_id` INTEGER NOT NULL, `data_hash` TEXT NOT NULL, `year` INTEGER NOT NULL, `month` INTEGER NOT NULL, `day` INTEGER NOT NULL, `description` TEXT NOT NULL COLLATE NOCASE, `comment` TEXT, `generation` INTEGER NOT NULL, FOREIGN KEY(`profile_id`) REFERENCES `profiles`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE )",
         "fields": [
           {
             "fieldPath": "id",
@@ -649,6 +655,12 @@
             "affinity": "INTEGER",
             "notNull": true
           },
+          {
+            "fieldPath": "ledgerId",
+            "columnName": "ledger_id",
+            "affinity": "INTEGER",
+            "notNull": true
+          },
           {
             "fieldPath": "profileId",
             "columnName": "profile_id",
@@ -706,13 +718,13 @@
         },
         "indices": [
           {
-            "name": "un_transactions_data_hash",
+            "name": "un_transactions_ledger_id",
             "unique": true,
             "columnNames": [
               "profile_id",
-              "data_hash"
+              "ledger_id"
             ],
-            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_transactions_data_hash` ON `${TABLE_NAME}` (`profile_id`, `data_hash`)"
+            "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `un_transactions_ledger_id` ON `${TABLE_NAME}` (`profile_id`, `ledger_id`)"
           },
           {
             "name": "idx_transaction_description",
@@ -808,12 +820,12 @@
         },
         "indices": [
           {
-            "name": "fk_tran_acc_trans",
+            "name": "fk_trans_acc_trans",
             "unique": false,
             "columnNames": [
               "transaction_id"
             ],
-            "createSql": "CREATE INDEX IF NOT EXISTS `fk_tran_acc_trans` ON `${TABLE_NAME}` (`transaction_id`)"
+            "createSql": "CREATE INDEX IF NOT EXISTS `fk_trans_acc_trans` ON `${TABLE_NAME}` (`transaction_id`)"
           },
           {
             "name": "un_transaction_accounts",
@@ -843,7 +855,7 @@
     "views": [],
     "setupQueries": [
       "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
-      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, 'a56d86c03528ece865d81fd8171c819f')"
+      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '0ab4d8a73295b6337c52ea561994b1c8')"
     ]
   }
 }
\ No newline at end of file
diff --git a/app/src/main/java/net/ktnx/mobileledger/dao/OptionDAO.java b/app/src/main/java/net/ktnx/mobileledger/dao/OptionDAO.java
new file mode 100644
index 00000000..f6ee035b
--- /dev/null
+++ b/app/src/main/java/net/ktnx/mobileledger/dao/OptionDAO.java
@@ -0,0 +1,46 @@
+/*
+ * 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
+ * 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 <https://www.gnu.org/licenses/>.
+ */
+
+package net.ktnx.mobileledger.dao;
+
+import androidx.lifecycle.LiveData;
+import androidx.room.Dao;
+import androidx.room.Delete;
+import androidx.room.Insert;
+import androidx.room.OnConflictStrategy;
+import androidx.room.Query;
+import androidx.room.Update;
+
+import net.ktnx.mobileledger.db.Option;
+
+@Dao
+public abstract class OptionDAO extends BaseDAO<Option> {
+    @Insert(onConflict = OnConflictStrategy.REPLACE)
+    public abstract long insertSync(Option item);
+
+    @Update
+    public abstract void updateSync(Option item);
+
+    @Delete
+    public abstract void deleteSync(Option item);
+
+    @Query("SELECT * FROM options WHERE profile_id = :profileId AND name = :name")
+    public abstract LiveData<Option> load(long profileId, String name);
+
+    @Query("SELECT * FROM options WHERE profile_id = :profileId AND name = :name")
+    public abstract Option loadSync(long profileId, String name);
+}
diff --git a/app/src/main/java/net/ktnx/mobileledger/db/DB.java b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
index 7dcb0897..958d1ac5 100644
--- a/app/src/main/java/net/ktnx/mobileledger/db/DB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/DB.java
@@ -30,6 +30,7 @@ import androidx.sqlite.db.SupportSQLiteDatabase;
 import net.ktnx.mobileledger.App;
 import net.ktnx.mobileledger.dao.AccountDAO;
 import net.ktnx.mobileledger.dao.CurrencyDAO;
+import net.ktnx.mobileledger.dao.OptionDAO;
 import net.ktnx.mobileledger.dao.TemplateAccountDAO;
 import net.ktnx.mobileledger.dao.TemplateHeaderDAO;
 import net.ktnx.mobileledger.dao.TransactionDAO;
@@ -72,6 +73,7 @@ abstract public class DB extends RoomDatabase {
                                                                  multiVersionMigration(34, 40),
                                                                  singleVersionMigration(41),
                                                                  multiVersionMigration(41, 58),
+                                                                 singleVersionMigration(59)
                                                                  })
                                   .addCallback(new Callback() {
                                       @Override
@@ -168,4 +170,6 @@ abstract public class DB extends RoomDatabase {
     public abstract AccountDAO getAccountDAO();
 
     public abstract TransactionDAO getTransactionDAO();
+
+    public abstract OptionDAO getOptionDAO();
 }
diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Option.java b/app/src/main/java/net/ktnx/mobileledger/db/Option.java
index 180295b3..dfecf56e 100644
--- a/app/src/main/java/net/ktnx/mobileledger/db/Option.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/Option.java
@@ -21,19 +21,26 @@ import androidx.annotation.NonNull;
 import androidx.room.ColumnInfo;
 import androidx.room.Entity;
 
+import org.jetbrains.annotations.NotNull;
+
 @Entity(tableName = "options", primaryKeys = {"profile_id", "name"})
 public class Option {
     @ColumnInfo(name = "profile_id")
     private long profileId;
     @NonNull
     @ColumnInfo
-    private String name = "";
+    private String name;
     @ColumnInfo
     private String value;
+    public Option(long profileId, @NotNull String name, String value) {
+        this.profileId = profileId;
+        this.name = name;
+        this.value = value;
+    }
     public long getProfileId() {
         return profileId;
     }
-    public void setProfile(long profileId) {
+    public void setProfileId(long profileId) {
         this.profileId = profileId;
     }
     @NonNull
diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Profile.java b/app/src/main/java/net/ktnx/mobileledger/db/Profile.java
index d2330380..06728c02 100644
--- a/app/src/main/java/net/ktnx/mobileledger/db/Profile.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/Profile.java
@@ -30,6 +30,8 @@ public class Profile {
     @NonNull
     @ColumnInfo
     private String name = "";
+    @ColumnInfo(name = "deprecated_uuid")
+    private String deprecatedUUID;
     @NonNull
     @ColumnInfo
     private String url = "";
@@ -63,6 +65,12 @@ public class Profile {
     private int detectedVersionMajor;
     @ColumnInfo(name = "detected_version_minor")
     private int detectedVersionMinor;
+    public String getDeprecatedUUID() {
+        return deprecatedUUID;
+    }
+    public void setDeprecatedUUID(String deprecatedUUID) {
+        this.deprecatedUUID = deprecatedUUID;
+    }
     public long getId() {
         return id;
     }
diff --git a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
index d67cedf5..05e63d8c 100644
--- a/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/Transaction.java
@@ -36,8 +36,8 @@ create index idx_transaction_description on transactions(description);
 @Entity(tableName = "transactions", foreignKeys = {
         @ForeignKey(entity = Profile.class, parentColumns = "id", childColumns = "profile_id",
                     onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.RESTRICT)
-}, indices = {@Index(name = "un_transactions_data_hash", unique = true,
-                     value = {"profile_id", "data_hash"}),
+}, indices = {@Index(name = "un_transactions_ledger_id", unique = true,
+                     value = {"profile_id", "ledger_id"}),
               @Index(name = "idx_transaction_description", value = "description"),
               @Index(name = "fk_transaction_profile", value = "profile_id")
 })
@@ -45,6 +45,8 @@ public class Transaction {
     @ColumnInfo
     @PrimaryKey(autoGenerate = true)
     long id;
+    @ColumnInfo(name = "ledger_id")
+    long ledgerId;
     @ColumnInfo(name = "profile_id")
     private long profileId;
     @ColumnInfo(name = "data_hash")
@@ -63,6 +65,12 @@ public class Transaction {
     private String comment;
     @ColumnInfo
     private int generation = 0;
+    public long getLedgerId() {
+        return ledgerId;
+    }
+    public void setLedgerId(long ledgerId) {
+        this.ledgerId = ledgerId;
+    }
     public long getProfileId() {
         return profileId;
     }
diff --git a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
index 730a866a..80b185be 100644
--- a/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
+++ b/app/src/main/java/net/ktnx/mobileledger/db/TransactionAccount.java
@@ -27,8 +27,8 @@ import androidx.room.PrimaryKey;
 @Entity(tableName = "transaction_accounts", foreignKeys = {
         @ForeignKey(entity = Transaction.class, parentColumns = {"id"},
                     childColumns = {"transaction_id"}, onDelete = ForeignKey.CASCADE,
-                    onUpdate = ForeignKey.RESTRICT),
-}, indices = {@Index(name = "fk_tran_acc_trans", value = {"transaction_id"}),
+                    onUpdate = ForeignKey.RESTRICT)
+}, indices = {@Index(name = "fk_trans_acc_trans", value = {"transaction_id"}),
               @Index(name = "un_transaction_accounts", unique = true,
                      value = {"transaction_id", "order_no"})
 })
diff --git a/app/src/main/java/net/ktnx/mobileledger/model/Data.java b/app/src/main/java/net/ktnx/mobileledger/model/Data.java
index 3520711b..93285a1c 100644
--- a/app/src/main/java/net/ktnx/mobileledger/model/Data.java
+++ b/app/src/main/java/net/ktnx/mobileledger/model/Data.java
@@ -136,7 +136,7 @@ public final class Data {
             return -1;
 
         SQLiteDatabase db = App.getDatabase();
-        try (Cursor c = db.rawQuery("SELECT theme from profiles where uuid=?",
+        try (Cursor c = db.rawQuery("SELECT theme from profiles where id=?",
                 new String[]{String.valueOf(profileId)}))
         {
             if (c.moveToNext())
diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/activity/MainActivity.java b/app/src/main/java/net/ktnx/mobileledger/ui/activity/MainActivity.java
index 25efe111..e9ce686a 100644
--- a/app/src/main/java/net/ktnx/mobileledger/ui/activity/MainActivity.java
+++ b/app/src/main/java/net/ktnx/mobileledger/ui/activity/MainActivity.java
@@ -452,6 +452,7 @@ public class MainActivity extends ProfileThemedActivity implements FabManager.Fa
         Data.lastUpdateAccountCount.removeObservers(this);
         Data.lastUpdateDate.removeObservers(this);
 
+        Logger.debug("MainActivity", "profileThemeChanged(): recreating activity");
         recreate();
     }
     public void fabNewTransactionClicked(View view) {
diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/activity/ProfileThemedActivity.java b/app/src/main/java/net/ktnx/mobileledger/ui/activity/ProfileThemedActivity.java
index 64d84ef0..07aae23c 100644
--- a/app/src/main/java/net/ktnx/mobileledger/ui/activity/ProfileThemedActivity.java
+++ b/app/src/main/java/net/ktnx/mobileledger/ui/activity/ProfileThemedActivity.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
@@ -25,6 +25,7 @@ import androidx.annotation.Nullable;
 import net.ktnx.mobileledger.model.Data;
 import net.ktnx.mobileledger.model.MobileLedgerProfile;
 import net.ktnx.mobileledger.utils.Colors;
+import net.ktnx.mobileledger.utils.Logger;
 
 @SuppressLint("Registered")
 public class ProfileThemedActivity extends CrashReportingActivity {
@@ -36,8 +37,11 @@ public class ProfileThemedActivity extends CrashReportingActivity {
 
         Colors.setupTheme(this, themeHue);
 
-        if (themeSetUp)
+        if (themeSetUp) {
+            Logger.debug("prf-thm-act",
+                    "setupProfileColors(): theme already set up, recreating activity");
             this.recreate();
+        }
         themeSetUp = true;
 
         Colors.profileThemeId = Data.retrieveCurrentThemeIdFromDb();
diff --git a/app/src/main/java/net/ktnx/mobileledger/ui/activity/SplashActivity.java b/app/src/main/java/net/ktnx/mobileledger/ui/activity/SplashActivity.java
index 2a238adc..41f3cddf 100644
--- a/app/src/main/java/net/ktnx/mobileledger/ui/activity/SplashActivity.java
+++ b/app/src/main/java/net/ktnx/mobileledger/ui/activity/SplashActivity.java
@@ -54,6 +54,7 @@ public class SplashActivity extends CrashReportingActivity {
         startupTime = System.currentTimeMillis();
 
         AsyncTask<Void, Void, Void> dbInitTask = new DatabaseInitTask();
+        Logger.debug("splash", "starting dbInit task");
         dbInitTask.execute();
     }
     @Override
diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/Colors.java b/app/src/main/java/net/ktnx/mobileledger/utils/Colors.java
index 9d0c6aaa..adefa6f3 100644
--- a/app/src/main/java/net/ktnx/mobileledger/utils/Colors.java
+++ b/app/src/main/java/net/ktnx/mobileledger/utils/Colors.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
@@ -70,7 +70,7 @@ public class Colors {
     int secondary;
     @ColorInt
     public static int tableRowDarkBG;
-    public static int profileThemeId = -1;
+    public static int profileThemeId = DEFAULT_HUE_DEG;
     public static void refreshColors(Resources.Theme theme) {
         TypedValue tv = new TypedValue();
         theme.resolveAttribute(R.attr.table_row_dark_bg, tv, true);
diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
index 2a9dca89..6521dedc 100644
--- a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
@@ -19,7 +19,6 @@ package net.ktnx.mobileledger.utils;
 
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
-import android.os.AsyncTask;
 
 import androidx.annotation.NonNull;
 
@@ -36,18 +35,7 @@ public final class MLDB {
     public static final String OPT_LAST_SCRAPE = "last_scrape";
     @NonNls
     public static final String OPT_PROFILE_ID = "profile_id";
-    private static final String NO_PROFILE = "-";
-    @SuppressWarnings("unused")
-    static public int getIntOption(String name, int default_value) {
-        String s = getOption(name, String.valueOf(default_value));
-        try {
-            return Integer.parseInt(s);
-        }
-        catch (Exception e) {
-            debug("db", "returning default int value of " + name, e);
-            return default_value;
-        }
-    }
+    public static final long NO_PROFILE = 0;
     @SuppressWarnings("unused")
     static public long getLongOption(String name, long default_value) {
         String s = getOption(name, String.valueOf(default_value));
@@ -59,45 +47,11 @@ public final class MLDB {
             return default_value;
         }
     }
-    static public void getOption(String name, String defaultValue, GetOptCallback cb) {
-        AsyncTask<Void, Void, String> t = new AsyncTask<Void, Void, String>() {
-            @Override
-            protected String doInBackground(Void... params) {
-                SQLiteDatabase db = App.getDatabase();
-                try (Cursor cursor = db.rawQuery(
-                        "select value from options where profile=? and name=?",
-                        new String[]{NO_PROFILE, name}))
-                {
-                    if (cursor.moveToFirst()) {
-                        String result = cursor.getString(0);
-
-                        if (result == null)
-                            result = defaultValue;
-
-                        debug("async-db", "option " + name + "=" + result);
-                        return result;
-                    }
-                    else
-                        return defaultValue;
-                }
-                catch (Exception e) {
-                    debug("db", "returning default value for " + name, e);
-                    return defaultValue;
-                }
-            }
-            @Override
-            protected void onPostExecute(String result) {
-                cb.onResult(result);
-            }
-        };
-
-        t.executeOnExecutor(AsyncTask.THREAD_POOL_EXECUTOR, (Void) null);
-    }
     static public String getOption(String name, String default_value) {
         debug("db", "about to fetch option " + name);
         SQLiteDatabase db = App.getDatabase();
-        try (Cursor cursor = db.rawQuery("select value from options where profile=? and name=?",
-                new String[]{NO_PROFILE, name}))
+        try (Cursor cursor = db.rawQuery("select value from options where profile_id=? and name=?",
+                new String[]{String.valueOf(NO_PROFILE), name}))
         {
             if (cursor.moveToFirst()) {
                 String result = cursor.getString(0);
@@ -118,8 +72,8 @@ public final class MLDB {
     }
     static public void setOption(String name, String value) {
         debug("option", String.format("%s := %s", name, value));
-        DbOpQueue.add("insert or replace into options(profile, name, value) values(?, ?, ?);",
-                new String[]{NO_PROFILE, name, value});
+        DbOpQueue.add("insert or replace into options(profile_id, name, value) values(?, ?, ?);",
+                new String[]{String.valueOf(NO_PROFILE), name, value});
     }
     @SuppressWarnings("unused")
     static public void setLongOption(String name, long value) {
diff --git a/app/src/main/res/raw/db_59.sql b/app/src/main/res/raw/db_59.sql
index 67c9beb7..8f5d8009 100644
--- a/app/src/main/res/raw/db_59.sql
+++ b/app/src/main/res/raw/db_59.sql
@@ -15,9 +15,18 @@
 
 -- migrate from revision 58 to revision 59
 
+-- pragmas need to be outside of transaction control
+-- foreign_keys is needed so that foreign key constraints are redirected
+
+commit transaction;
+pragma foreign_keys = on;
+
+begin transaction;
+
+-- profiles
 CREATE TABLE profiles_new (
 id INTEGER NOT NULL PRIMARY KEY,
-uuid text,
+deprecated_uuid text,
 name TEXT NOT NULL,
 url TEXT NOT NULL,
 use_authentication INTEGER NOT NULL,
@@ -37,7 +46,7 @@ detected_version_major INTEGER NOT NULL,
 detected_version_minor INTEGER NOT NULL);
 
 insert into profiles_new(
-       uuid, name, url, use_authentication, auth_user, auth_password,
+       deprecated_uuid, name, url, use_authentication, auth_user, auth_password,
        order_no, permit_posting, theme, preferred_accounts_filter, future_dates, api_version,
        show_commodity_by_default, default_commodity, show_comments_by_default, detected_version_pre_1_19,
        detected_version_major, detected_version_minor)
@@ -47,6 +56,7 @@ select uuid, name, url, use_authentication, auth_user, auth_password,
        detected_version_major, detected_version_minor
 from profiles;
 
+-- accounts
 create table accounts_new(
 id integer primary key not null,
 profile_id integer not null references profiles_new(id) on delete cascade on update restrict,
@@ -61,10 +71,100 @@ generation INTEGER NOT NULL DEFAULT 0);
 insert into accounts_new(profile_id, level, name, name_upper, parent_name, expanded, amounts_expanded, generation)
 select p.id, a.level, a.name, a.name_upper, a.parent_name, a.expanded, a.amounts_expanded, a.generation
 from profiles_new p
-join accounts a on a.profile=p.uuid;
+join accounts a on a.profile=p.deprecated_uuid;
 
 drop table accounts;
 alter table accounts_new rename to accounts;
 
 drop table profiles;
-alter table profiles_new rename to profiles;
\ No newline at end of file
+alter table profiles_new rename to profiles;
+
+create index fk_account_profile on accounts(profile_id);
+create unique index un_account_name on accounts(profile_id, name);
+
+-- options
+create table options_new(
+name text not null,
+profile_id integer not null,
+value text,
+primary key(profile_id,name));
+
+insert into options_new(name, profile_id, value)
+select o.name, p.id, o.value
+from options o
+join profiles p on p.deprecated_uuid = o.profile;
+
+drop table options;
+alter table options_new rename to options;
+
+update options
+set name='profile_id'
+  , value=(select p.id from profiles p where p.deprecated_uuid=options.value)
+where name='profile_uuid';
+
+-- account_values
+create table account_values_new(
+id integer not null primary key,
+account_id integer not null references accounts(id) on delete cascade on update restrict,
+currency text not null default '',
+value real not null,
+generation integer not null default 0);
+
+insert into account_values_new(account_id, currency, value, generation)
+select a.id, av.currency, av.value, av.generation
+from account_values av
+join profiles p on p.deprecated_uuid=av.profile
+join accounts a on a.profile_id = p.id and a.name = av.account;
+
+drop table account_values;
+alter table account_values_new rename to account_values;
+
+create index fk_account_value_acc on account_values(account_id);
+create unique index un_account_values on account_values(account_id, currency);
+
+-- transactions
+create table transactions_new(
+id integer not null primary key,
+profile_id integer not null references profiles(id) on delete cascade on update restrict,
+ledger_id integer not null,
+description text not null,
+year integer not null,
+month integer not null,
+day integer not null,
+comment text,
+data_hash text not null,
+generation integer not null);
+
+insert into transactions_new(profile_id, ledger_id, description, year, month, day, comment, data_hash, generation)
+select p.id, t.id, t.description, t.year, t.month, t.day, t.comment, t.data_hash, t.generation
+from transactions t
+join profiles p on p.deprecated_uuid = t.profile;
+
+-- transaction_accounts
+create table transaction_accounts_new(
+    id integer not null primary key,
+    transaction_id integer not null references transactions_new(id) on delete cascade on update restrict,
+    order_no integer not null,
+    account_name text not null,
+    currency text not null default '',
+    amount real not null,
+    comment text,
+    generation integer not null default 0);
+
+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;
+
+drop table transaction_accounts;
+alter table transaction_accounts_new rename to transaction_accounts;
+
+drop table transactions;
+alter table transactions_new rename to transactions;
+
+create index idx_transaction_description on transactions(description);
+create unique index un_transactions_ledger_id on transactions(profile_id, ledger_id);
+create index fk_transaction_profile on transactions(profile_id);
+
+create unique index un_transaction_accounts on transaction_accounts(transaction_id, order_no);
+create index fk_trans_acc_trans on transaction_accounts(transaction_id);
\ No newline at end of file