Move database migrations into own classes

15 files changed, 590 insertions, 394 deletions

diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java
index fd536cb0..4d76a6a8 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java
@@ -1,31 +1,24 @@
 package it.niedermann.owncloud.notes.persistence;
 
-import android.appwidget.AppWidgetManager;
-import android.content.ComponentName;
-import android.content.ContentValues;
 import android.content.Context;
-import android.content.SharedPreferences;
-import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteOpenHelper;
-import android.util.Log;
 
 import androidx.annotation.NonNull;
 import androidx.annotation.Nullable;
-import androidx.preference.PreferenceManager;
-import androidx.work.WorkManager;
 
-import java.net.MalformedURLException;
-import java.net.URL;
-import java.util.Hashtable;
-import java.util.Map;
-
-import it.niedermann.owncloud.notes.android.DarkModeSetting;
-import it.niedermann.owncloud.notes.android.appwidget.NoteListWidget;
-import it.niedermann.owncloud.notes.android.appwidget.SingleNoteWidget;
-import it.niedermann.owncloud.notes.model.DBStatus;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_10_11;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_11_12;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_12_13;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_13_14;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_14_15;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_15_16;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_4_5;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_5_6;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_6_7;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_8_9;
+import it.niedermann.owncloud.notes.persistence.migration.Migration_9_10;
 import it.niedermann.owncloud.notes.util.DatabaseIndexUtil;
-import it.niedermann.owncloud.notes.util.NoteUtil;
 
 // Protected APIs
 @SuppressWarnings("WeakerAccess")
@@ -160,377 +153,37 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper {
 
     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
-        if (oldVersion < 4) {
-            recreateDatabase(db);
-            return;
-        }
-        if (oldVersion < 5) {
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN REMOTEID INTEGER");
-            db.execSQL("UPDATE NOTES SET REMOTEID=ID WHERE (REMOTEID IS NULL OR REMOTEID=0) AND STATUS!=?", new String[]{"LOCAL_CREATED"});
-            db.execSQL("UPDATE NOTES SET REMOTEID=0, STATUS=? WHERE STATUS=?", new String[]{DBStatus.LOCAL_EDITED.getTitle(), "LOCAL_CREATED"});
-        }
-        if (oldVersion < 6) {
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN FAVORITE INTEGER DEFAULT 0");
-        }
-        if (oldVersion < 7) {
-            DatabaseIndexUtil.dropIndexes(db);
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN CATEGORY TEXT NOT NULL DEFAULT ''");
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN ETAG TEXT");
-            DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
-        }
-        if (oldVersion < 8) {
-            final String table_temp = "NOTES_TEMP";
-            db.execSQL("CREATE TABLE " + table_temp + " ( " +
-                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                    "REMOTEID INTEGER, " +
-                    "STATUS VARCHAR(50), " +
-                    "TITLE TEXT, " +
-                    "MODIFIED INTEGER DEFAULT 0, " +
-                    "CONTENT TEXT, " +
-                    "FAVORITE INTEGER DEFAULT 0, " +
-                    "CATEGORY TEXT NOT NULL DEFAULT '', " +
-                    "ETAG TEXT)");
-            DatabaseIndexUtil.createIndex(db, table_temp, "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
-            db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s) ", table_temp, "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG")
-                    + String.format("SELECT %s,%s,%s,%s,strftime('%%s',%s),%s,%s,%s,%s FROM %s", "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", "NOTES"));
-            db.execSQL("DROP TABLE NOTES");
-            db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", table_temp, "NOTES"));
-        }
-        if (oldVersion < 9) {
-            // Create accounts table
-            db.execSQL("CREATE TABLE ACCOUNTS ( " +
-                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                    "URL TEXT, " +
-                    "USERNAME TEXT, " +
-                    "ACCOUNT_NAME TEXT UNIQUE, " +
-                    "ETAG TEXT, " +
-                    "MODIFIED INTEGER)");
-            DatabaseIndexUtil.createIndex(db, "ACCOUNTS", "URL", "USERNAME", "ACCOUNT_NAME", "ETAG", "MODIFIED");
-
-            // Add accountId to notes table
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN ACCOUNT_ID INTEGER NOT NULL DEFAULT 0");
-            DatabaseIndexUtil.createIndex(db, "NOTES", "ACCOUNT_ID");
-
-            // Migrate existing account from SharedPreferences
-            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
-            String username = sharedPreferences.getString("settingsUsername", "");
-            String url = sharedPreferences.getString("settingsUrl", "");
-            if (!url.isEmpty() && url.endsWith("/")) {
-                url = url.substring(0, url.length() - 1);
-                try {
-                    String accountName = username + "@" + new URL(url).getHost();
-
-                    ContentValues migratedAccountValues = new ContentValues();
-                    migratedAccountValues.put("URL", url);
-                    migratedAccountValues.put("USERNAME", username);
-                    migratedAccountValues.put("ACCOUNT_NAME", accountName);
-                    db.insert("ACCOUNTS", null, migratedAccountValues);
-
-                    // After successful insertion of migrated account, set accountId to 1 in each note
-                    ContentValues values = new ContentValues();
-                    values.put("ACCOUNT_ID", 1);
-                    db.update("NOTES", values, "ACCOUNT_ID = ?", new String[]{"NULL"});
-
-                    // Add FOREIGN_KEY constraint
-                    final String table_temp = "NOTES_TEMP";
-                    db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", "NOTES", table_temp));
-
-                    db.execSQL("CREATE TABLE NOTES ( " +
-                            "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                            "REMOTEID INTEGER, " +
-                            "ACCOUNT_ID INTEGER, " +
-                            "STATUS VARCHAR(50), " +
-                            "TITLE TEXT, " +
-                            "MODIFIED INTEGER DEFAULT 0, " +
-                            "CONTENT TEXT, " +
-                            "FAVORITE INTEGER DEFAULT 0, " +
-                            "CATEGORY TEXT NOT NULL DEFAULT '', " +
-                            "ETAG TEXT," +
-                            "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
-                    DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
-
-                    db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ", "NOTES", "ID", "ACCOUNT_ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG")
-                            + String.format("SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s", "ID", values.get("ACCOUNT_ID"), "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", table_temp));
-                    db.execSQL(String.format("DROP TABLE %s;", table_temp));
-
-                    AppWidgetManager awm = AppWidgetManager.getInstance(context);
-                    SharedPreferences.Editor editor = sharedPreferences.edit();
-
-                    // Add accountId '1' to any existing (and configured) appwidgets
-                    int[] appWidgetIdsNLW = awm.getAppWidgetIds(new ComponentName(context, NoteListWidget.class));
-                    int[] appWidgetIdsSNW = awm.getAppWidgetIds(new ComponentName(context, SingleNoteWidget.class));
-
-                    final String WIDGET_MODE_KEY = "NLW_mode";
-                    final String ACCOUNT_ID_KEY = "NLW_account";
-
-                    for (int appWidgetId : appWidgetIdsNLW) {
-                        if (sharedPreferences.getInt(WIDGET_MODE_KEY + appWidgetId, -1) >= 0) {
-                            editor.putLong(ACCOUNT_ID_KEY + appWidgetId, 1);
-                        }
-                    }
-
-                    for (int appWidgetId : appWidgetIdsSNW) {
-                        if (sharedPreferences.getLong("single_note_widget" + appWidgetId, -1) >= 0) {
-                            editor.putLong("SNW_accountId" + appWidgetId, 1);
-                        }
-                    }
-
-                    notifyNotesChanged();
-
-                    // Clean up no longer needed SharedPreferences
-                    editor.remove("notes_last_etag");
-                    editor.remove("notes_last_modified");
-                    editor.remove("settingsUrl");
-                    editor.remove("settingsUsername");
-                    editor.remove("settingsPassword");
-                    editor.apply();
-                } catch (MalformedURLException e) {
-                    Log.e(TAG, "Previous URL could not be parsed. Recreating database...");
-                    e.printStackTrace();
-                    recreateDatabase(db);
-                    return;
-                }
-            } else {
-                Log.e(TAG, "Previous URL is empty or does not end with a '/' character. Recreating database...");
+        switch (oldVersion) {
+            case 0:
+            case 1:
+            case 2:
+            case 3:
                 recreateDatabase(db);
                 return;
-            }
-        }
-        if (oldVersion < 10) {
-            db.execSQL("ALTER TABLE NOTES ADD COLUMN EXCERPT INTEGER NOT NULL DEFAULT ''");
-            Cursor cursor = db.query("NOTES", new String[]{"ID", "CONTENT"}, null, null, null, null, null, null);
-            while (cursor.moveToNext()) {
-                ContentValues values = new ContentValues();
-                values.put("EXCERPT", NoteUtil.generateNoteExcerpt(cursor.getString(1)));
-                db.update("NOTES", values, "ID" + " = ? ", new String[]{cursor.getString(0)});
-            }
-            cursor.close();
-        }
-        if (oldVersion < 11) {
-            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
-            SharedPreferences.Editor editor = sharedPreferences.edit();
-            Map<String, ?> prefs = sharedPreferences.getAll();
-            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
-                String key = pref.getKey();
-                final String DARK_THEME_KEY = "NLW_darkTheme";
-                if ("darkTheme".equals(key) || key.startsWith(DARK_THEME_KEY) || key.startsWith("SNW_darkTheme")) {
-                    Boolean darkTheme = (Boolean) pref.getValue();
-                    editor.putString(pref.getKey(), darkTheme ? DarkModeSetting.DARK.name() : DarkModeSetting.LIGHT.name());
-                }
-            }
-            editor.apply();
-        }
-        if (oldVersion < 12) {
-            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN API_VERSION TEXT");
-            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN COLOR VARCHAR(6) NOT NULL DEFAULT '000000'");
-            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN TEXT_COLOR VARCHAR(6) NOT NULL DEFAULT '0082C9'");
-            CapabilitiesWorker.update(context);
-        }
-        if (oldVersion < 13) {
-            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN CAPABILITIES_ETAG TEXT");
-            WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("it.niedermann.owncloud.notes.persistence.SyncWorker");
-            WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("SyncWorker");
-        }
-        if (oldVersion < 14) {
-            // #754 Move single note widget preferences to database
-            db.execSQL("CREATE TABLE WIDGET_SINGLE_NOTES ( " +
-                    "ID INTEGER PRIMARY KEY, " +
-                    "ACCOUNT_ID INTEGER, " +
-                    "NOTE_ID INTEGER, " +
-                    "THEME_MODE INTEGER NOT NULL, " +
-                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " +
-                    "FOREIGN KEY(NOTE_ID) REFERENCES NOTES(ID))");
-
-            final String SP_WIDGET_KEY = "single_note_widget";
-            final String SP_ACCOUNT_ID_KEY = "SNW_accountId";
-            final String SP_DARK_THEME_KEY = "SNW_darkTheme";
-            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
-            SharedPreferences.Editor editor = sharedPreferences.edit();
-            Map<String, ?> prefs = sharedPreferences.getAll();
-            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
-                final String key = pref.getKey();
-                Integer widgetId = null;
-                Long noteId = null;
-                Long accountId = null;
-                Integer themeMode = null;
-                if (key != null && key.startsWith(SP_WIDGET_KEY)) {
-                    try {
-                        widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length()));
-                        noteId = (Long) pref.getValue();
-                        accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1);
-
-                        try {
-                            themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId();
-                        } catch (ClassCastException e) {
-                            //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting.
-                            themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId();
-                        }
-
-                        ContentValues migratedWidgetValues = new ContentValues();
-                        migratedWidgetValues.put("ID", widgetId);
-                        migratedWidgetValues.put("ACCOUNT_ID", accountId);
-                        migratedWidgetValues.put("NOTE_ID", noteId);
-                        migratedWidgetValues.put("THEME_MODE", themeMode);
-                        db.insert("WIDGET_SINGLE_NOTES", null, migratedWidgetValues);
-                    } catch (Throwable t) {
-                        Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", noteId: " + noteId + ", themeMode: " + themeMode + "}");
-                        t.printStackTrace();
-                    } finally {
-                        // Clean up old shared preferences
-                        editor.remove(SP_WIDGET_KEY + widgetId);
-                        editor.remove(SP_DARK_THEME_KEY + widgetId);
-                        editor.remove(SP_ACCOUNT_ID_KEY + widgetId);
-                    }
-                }
-            }
-            editor.apply();
-            notifyNotesChanged();
-        }
-        if (oldVersion < 15) {
-            // #814 normalize database (move category from string field to own table)
-            // Rename a tmp_NOTES table.
-            String tmpTableNotes = String.format("tmp_%s", "NOTES");
-            db.execSQL("ALTER TABLE NOTES RENAME TO " + tmpTableNotes);
-            db.execSQL("CREATE TABLE NOTES ( " +
-                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                    "REMOTEID INTEGER, " +
-                    "ACCOUNT_ID INTEGER, " +
-                    "STATUS VARCHAR(50), " +
-                    "TITLE TEXT, " +
-                    "MODIFIED INTEGER DEFAULT 0, " +
-                    "CONTENT TEXT, " +
-                    "FAVORITE INTEGER DEFAULT 0, " +
-                    "CATEGORY INTEGER, " +
-                    "ETAG TEXT," +
-                    "EXCERPT TEXT NOT NULL DEFAULT '', " +
-                    "FOREIGN KEY(CATEGORY) REFERENCES CATEGORIES(CATEGORY_ID), " +
-                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
-            DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
-            db.execSQL("CREATE TABLE CATEGORIES(" +
-                    "CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                    "CATEGORY_ACCOUNT_ID INTEGER NOT NULL, " +
-                    "CATEGORY_TITLE TEXT NOT NULL, " +
-                    "UNIQUE( CATEGORY_ACCOUNT_ID , CATEGORY_TITLE), " +
-                    "FOREIGN KEY(CATEGORY_ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
-            DatabaseIndexUtil.createIndex(db, "CATEGORIES", "CATEGORY_ID", "CATEGORY_ACCOUNT_ID", "CATEGORY_TITLE");
-            // A hashtable storing categoryTitle - categoryId Mapping
-            // This is used to prevent too many searches in database
-            Hashtable<String, Integer> categoryTitleIdMap = new Hashtable<>();
-            int id = 1;
-            Cursor tmpNotesCursor = db.rawQuery("SELECT * FROM " + tmpTableNotes, null);
-            while (tmpNotesCursor.moveToNext()) {
-                String categoryTitle = tmpNotesCursor.getString(8);
-                int accountId = tmpNotesCursor.getInt(2);
-                Log.e("###", accountId + "");
-                Integer categoryId;
-                if (categoryTitleIdMap.containsKey(categoryTitle) && categoryTitleIdMap.get(categoryTitle) != null) {
-                    categoryId = categoryTitleIdMap.get(categoryTitle);
-                } else {
-                    // The category does not exists in the database, create it.
-                    categoryId = id++;
-                    ContentValues values = new ContentValues();
-                    values.put("CATEGORY_ID", categoryId);
-                    values.put("CATEGORY_ACCOUNT_ID", accountId);
-                    values.put("CATEGORY_TITLE", categoryTitle);
-                    db.insert("CATEGORIES", null, values);
-                    categoryTitleIdMap.put(categoryTitle, categoryId);
-                }
-                // Move the data in tmp_NOTES to NOTES
-                ContentValues values = new ContentValues();
-                values.put("ID", tmpNotesCursor.getInt(0));
-                values.put("REMOTEID", tmpNotesCursor.getInt(1));
-                values.put("ACCOUNT_ID", tmpNotesCursor.getInt(2));
-                values.put("STATUS", tmpNotesCursor.getString(3));
-                values.put("TITLE", tmpNotesCursor.getString(4));
-                values.put("MODIFIED", tmpNotesCursor.getLong(5));
-                values.put("CONTENT", tmpNotesCursor.getString(6));
-                values.put("FAVORITE", tmpNotesCursor.getInt(7));
-                values.put("CATEGORY", categoryId);
-                values.put("ETAG", tmpNotesCursor.getString(9));
-                values.put("EXCERPT", tmpNotesCursor.getString(10));
-                db.insert("NOTES", null, values);
-            }
-            tmpNotesCursor.close();
-            db.execSQL("DROP TABLE IF EXISTS " + tmpTableNotes);
-        }
-        if (oldVersion < 16) {
-            // #832 Move note list widget preferences to database
-            db.execSQL("CREATE TABLE WIDGET_NOTE_LISTS ( " +
-                    "ID INTEGER PRIMARY KEY, " +
-                    "ACCOUNT_ID INTEGER, " +
-                    "CATEGORY_ID INTEGER, " +
-                    "MODE INTEGER NOT NULL, " +
-                    "THEME_MODE INTEGER NOT NULL, " +
-                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " +
-                    "FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID))");
-
-            final String SP_WIDGET_KEY = "NLW_mode";
-            final String SP_ACCOUNT_ID_KEY = "NLW_account";
-            final String SP_DARK_THEME_KEY = "NLW_darkTheme";
-            final String SP_CATEGORY_KEY = "NLW_cat";
-
-            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
-            SharedPreferences.Editor editor = sharedPreferences.edit();
-            Map<String, ?> prefs = sharedPreferences.getAll();
-            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
-                final String key = pref.getKey();
-                Integer widgetId = null;
-                Integer mode = null;
-                Long accountId = null;
-                Integer themeMode = null;
-                Integer categoryId = null;
-                if (key != null && key.startsWith(SP_WIDGET_KEY)) {
-                    try {
-                        widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length()));
-                        mode = (Integer) pref.getValue();
-                        accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1);
-
-                        try {
-                            themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId();
-                        } catch (ClassCastException e) {
-                            //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting.
-                            themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId();
-                        }
-
-                        if (mode == 2) {
-                            final String categoryTitle = sharedPreferences.getString(SP_CATEGORY_KEY + widgetId, null);
-                            Cursor cursor = db.query(
-                                    table_category,
-                                    new String[]{key_category_id},
-                                    key_category_title + " = ? AND " + key_category_account_id + " = ? ",
-                                    new String[]{categoryTitle, String.valueOf(accountId)},
-                                    null,
-                                    null,
-                                    null);
-                            if (cursor.moveToNext()) {
-                                categoryId = cursor.getInt(0);
-                            } else {
-                                throw new IllegalStateException("No category id found for title \"" + categoryTitle + "\"");
-                            }
-                            cursor.close();
-                        }
-
-                        ContentValues migratedWidgetValues = new ContentValues();
-                        migratedWidgetValues.put("ID", widgetId);
-                        migratedWidgetValues.put("ACCOUNT_ID", accountId);
-                        migratedWidgetValues.put("CATEGORY_ID", categoryId);
-                        migratedWidgetValues.put("MODE", mode);
-                        migratedWidgetValues.put("THEME_MODE", themeMode);
-                        db.insert("WIDGET_NOTE_LISTS", null, migratedWidgetValues);
-                    } catch (Throwable t) {
-                        Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", mode: " + mode + ", categoryId: " + categoryId + ", themeMode: " + themeMode + "}");
-                        t.printStackTrace();
-                    } finally {
-                        // Clean up old shared preferences
-                        editor.remove(SP_WIDGET_KEY + widgetId);
-                        editor.remove(SP_CATEGORY_KEY + widgetId);
-                        editor.remove(SP_DARK_THEME_KEY + widgetId);
-                        editor.remove(SP_ACCOUNT_ID_KEY + widgetId);
-                    }
-                }
-            }
-            editor.apply();
-            notifyNotesChanged();
+            case 4:
+                new Migration_4_5(db, oldVersion);
+            case 5:
+                new Migration_5_6(db, oldVersion);
+            case 6:
+                new Migration_6_7(db, oldVersion);
+            case 7:
+                new Migration_7_8(db, oldVersion);
+            case 8:
+                new Migration_8_9(db, oldVersion, context, this::recreateDatabase, this::notifyWidgets);
+            case 9:
+                new Migration_9_10(db, oldVersion);
+            case 10:
+                new Migration_10_11(oldVersion, context);
+            case 11:
+                new Migration_11_12(db, oldVersion, context);
+            case 12:
+                new Migration_12_13(db, oldVersion, context);
+            case 13:
+                new Migration_13_14(db, oldVersion, context, this::notifyWidgets);
+            case 14:
+                new Migration_14_15(db, oldVersion);
+            case 15:
+                new Migration_15_16(db, oldVersion, context, this::notifyWidgets);
         }
     }
 
@@ -548,5 +201,5 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper {
     }
 
 
-    protected abstract void notifyNotesChanged();
+    protected abstract void notifyWidgets();
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java
new file mode 100644
index 00000000..44748b98
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java
@@ -0,0 +1,28 @@
+package it.niedermann.owncloud.notes.persistence;
+
+import android.database.sqlite.SQLiteDatabase;
+
+import it.niedermann.owncloud.notes.util.DatabaseIndexUtil;
+
+public class Migration_7_8 {
+    public Migration_7_8(SQLiteDatabase db, int oldVersion) {
+        if (oldVersion < 8) {
+            final String table_temp = "NOTES_TEMP";
+            db.execSQL("CREATE TABLE " + table_temp + " ( " +
+                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                    "REMOTEID INTEGER, " +
+                    "STATUS VARCHAR(50), " +
+                    "TITLE TEXT, " +
+                    "MODIFIED INTEGER DEFAULT 0, " +
+                    "CONTENT TEXT, " +
+                    "FAVORITE INTEGER DEFAULT 0, " +
+                    "CATEGORY TEXT NOT NULL DEFAULT '', " +
+                    "ETAG TEXT)");
+            DatabaseIndexUtil.createIndex(db, table_temp, "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
+            db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s) ", table_temp, "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG")
+                    + String.format("SELECT %s,%s,%s,%s,strftime('%%s',%s),%s,%s,%s,%s FROM %s", "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", "NOTES"));
+            db.execSQL("DROP TABLE NOTES");
+            db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", table_temp, "NOTES"));
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NoteServerSyncHelper.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NoteServerSyncHelper.java
index cb6c2717..7158c9d2 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NoteServerSyncHelper.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NoteServerSyncHelper.java
@@ -513,7 +513,7 @@ public class NoteServerSyncHelper {
                     callback.onFinish();
                 }
             }
-            db.notifyNotesChanged();
+            db.notifyWidgets();
             db.updateDynamicShortcuts(localAccount.getId());
             // start next sync if scheduled meanwhile
             if (syncScheduled.containsKey(ssoAccount.name) && syncScheduled.get(ssoAccount.name) != null && Boolean.TRUE.equals(syncScheduled.get(ssoAccount.name))) {
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesDatabase.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesDatabase.java
index 7cf2b366..a25f6181 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesDatabase.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesDatabase.java
@@ -96,7 +96,7 @@ public class NotesDatabase extends AbstractNotesDatabase {
     public long addNoteAndSync(SingleSignOnAccount ssoAccount, long accountId, CloudNote note) {
         DBNote dbNote = new DBNote(0, 0, note.getModified(), note.getTitle(), note.getContent(), note.isFavorite(), note.getCategory(), note.getEtag(), DBStatus.LOCAL_EDITED, accountId, NoteUtil.generateNoteExcerpt(note.getContent()));
         long id = addNote(accountId, dbNote);
-        notifyNotesChanged();
+        notifyWidgets();
         getNoteServerSyncHelper().scheduleSync(ssoAccount, true);
         return id;
     }
@@ -140,7 +140,7 @@ public class NotesDatabase extends AbstractNotesDatabase {
         addNoteAndSync(ssoAccount, newAccountId, new CloudNote(0, note.getModified(), note.getTitle(), note.getContent(), note.isFavorite(), note.getCategory(), null));
         deleteNoteAndSync(ssoAccount, note.getId());
 
-        notifyNotesChanged();
+        notifyWidgets();
         getNoteServerSyncHelper().scheduleSync(ssoAccount, true);
     }
 
@@ -523,7 +523,7 @@ public class NotesDatabase extends AbstractNotesDatabase {
         removeEmptyCategory(accountId);
         // if data was changed, set new status and schedule sync (with callback); otherwise invoke callback directly.
         if (rows > 0) {
-            notifyNotesChanged();
+            notifyWidgets();
             if (callback != null) {
                 serverSyncHelper.addCallbackPush(ssoAccount, callback);
             }
@@ -599,7 +599,7 @@ public class NotesDatabase extends AbstractNotesDatabase {
                 values,
                 key_id + " = ?",
                 new String[]{String.valueOf(id)});
-        notifyNotesChanged();
+        notifyWidgets();
         getNoteServerSyncHelper().scheduleSync(ssoAccount, true);
 
         if (android.os.Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
@@ -632,7 +632,7 @@ public class NotesDatabase extends AbstractNotesDatabase {
     /**
      * Notify about changed notes.
      */
-    protected void notifyNotesChanged() {
+    protected void notifyWidgets() {
         updateSingleNoteWidgets(getContext());
         updateNoteListWidgets(getContext());
     }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java
new file mode 100644
index 00000000..d09a44e8
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java
@@ -0,0 +1,30 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.Context;
+import android.content.SharedPreferences;
+
+import androidx.annotation.NonNull;
+import androidx.preference.PreferenceManager;
+
+import java.util.Map;
+
+import it.niedermann.owncloud.notes.android.DarkModeSetting;
+
+public class Migration_10_11 {
+    public Migration_10_11(int oldVersion, @NonNull Context context) {
+        if (oldVersion < 11) {
+            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
+            SharedPreferences.Editor editor = sharedPreferences.edit();
+            Map<String, ?> prefs = sharedPreferences.getAll();
+            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
+                String key = pref.getKey();
+                final String DARK_THEME_KEY = "NLW_darkTheme";
+                if ("darkTheme".equals(key) || key.startsWith(DARK_THEME_KEY) || key.startsWith("SNW_darkTheme")) {
+                    Boolean darkTheme = (Boolean) pref.getValue();
+                    editor.putString(pref.getKey(), darkTheme ? DarkModeSetting.DARK.name() : DarkModeSetting.LIGHT.name());
+                }
+            }
+            editor.apply();
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java
new file mode 100644
index 00000000..28d329d5
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java
@@ -0,0 +1,19 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.Context;
+import android.database.sqlite.SQLiteDatabase;
+
+import androidx.annotation.NonNull;
+
+import it.niedermann.owncloud.notes.persistence.CapabilitiesWorker;
+
+public class Migration_11_12 {
+    public Migration_11_12(SQLiteDatabase db, int oldVersion, @NonNull Context context) {
+        if (oldVersion < 12) {
+            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN API_VERSION TEXT");
+            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN COLOR VARCHAR(6) NOT NULL DEFAULT '000000'");
+            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN TEXT_COLOR VARCHAR(6) NOT NULL DEFAULT '0082C9'");
+            CapabilitiesWorker.update(context);
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java
new file mode 100644
index 00000000..e51280be
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java
@@ -0,0 +1,17 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.Context;
+import android.database.sqlite.SQLiteDatabase;
+
+import androidx.annotation.NonNull;
+import androidx.work.WorkManager;
+
+public class Migration_12_13 {
+    public Migration_12_13(SQLiteDatabase db, int oldVersion, @NonNull Context context) {
+        if (oldVersion < 13) {
+            db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN CAPABILITIES_ETAG TEXT");
+            WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("it.niedermann.owncloud.notes.persistence.SyncWorker");
+            WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("SyncWorker");
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java
new file mode 100644
index 00000000..9a5d5fdf
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java
@@ -0,0 +1,77 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.ContentValues;
+import android.content.Context;
+import android.content.SharedPreferences;
+import android.database.sqlite.SQLiteDatabase;
+import android.util.Log;
+
+import androidx.annotation.NonNull;
+import androidx.preference.PreferenceManager;
+
+import java.util.Map;
+
+import it.niedermann.owncloud.notes.android.DarkModeSetting;
+
+public class Migration_13_14 {
+
+    private static final String TAG = Migration_13_14.class.getSimpleName();
+
+    public Migration_13_14(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Runnable notifyWidgets) {
+        if (oldVersion < 14) {
+            // #754 Move single note widget preferences to database
+            db.execSQL("CREATE TABLE WIDGET_SINGLE_NOTES ( " +
+                    "ID INTEGER PRIMARY KEY, " +
+                    "ACCOUNT_ID INTEGER, " +
+                    "NOTE_ID INTEGER, " +
+                    "THEME_MODE INTEGER NOT NULL, " +
+                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " +
+                    "FOREIGN KEY(NOTE_ID) REFERENCES NOTES(ID))");
+
+            final String SP_WIDGET_KEY = "single_note_widget";
+            final String SP_ACCOUNT_ID_KEY = "SNW_accountId";
+            final String SP_DARK_THEME_KEY = "SNW_darkTheme";
+            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
+            SharedPreferences.Editor editor = sharedPreferences.edit();
+            Map<String, ?> prefs = sharedPreferences.getAll();
+            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
+                final String key = pref.getKey();
+                Integer widgetId = null;
+                Long noteId = null;
+                Long accountId = null;
+                Integer themeMode = null;
+                if (key != null && key.startsWith(SP_WIDGET_KEY)) {
+                    try {
+                        widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length()));
+                        noteId = (Long) pref.getValue();
+                        accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1);
+
+                        try {
+                            themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId();
+                        } catch (ClassCastException e) {
+                            //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting.
+                            themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId();
+                        }
+
+                        ContentValues migratedWidgetValues = new ContentValues();
+                        migratedWidgetValues.put("ID", widgetId);
+                        migratedWidgetValues.put("ACCOUNT_ID", accountId);
+                        migratedWidgetValues.put("NOTE_ID", noteId);
+                        migratedWidgetValues.put("THEME_MODE", themeMode);
+                        db.insert("WIDGET_SINGLE_NOTES", null, migratedWidgetValues);
+                    } catch (Throwable t) {
+                        Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", noteId: " + noteId + ", themeMode: " + themeMode + "}");
+                        t.printStackTrace();
+                    } finally {
+                        // Clean up old shared preferences
+                        editor.remove(SP_WIDGET_KEY + widgetId);
+                        editor.remove(SP_DARK_THEME_KEY + widgetId);
+                        editor.remove(SP_ACCOUNT_ID_KEY + widgetId);
+                    }
+                }
+            }
+            editor.apply();
+            notifyWidgets.run();
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java
new file mode 100644
index 00000000..7abaf133
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java
@@ -0,0 +1,82 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.ContentValues;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.util.Log;
+
+import java.util.Hashtable;
+
+import it.niedermann.owncloud.notes.util.DatabaseIndexUtil;
+
+public class Migration_14_15 {
+    public Migration_14_15(SQLiteDatabase db, int oldVersion) {
+        if (oldVersion < 15) {
+            // #814 normalize database (move category from string field to own table)
+            // Rename a tmp_NOTES table.
+            String tmpTableNotes = String.format("tmp_%s", "NOTES");
+            db.execSQL("ALTER TABLE NOTES RENAME TO " + tmpTableNotes);
+            db.execSQL("CREATE TABLE NOTES ( " +
+                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                    "REMOTEID INTEGER, " +
+                    "ACCOUNT_ID INTEGER, " +
+                    "STATUS VARCHAR(50), " +
+                    "TITLE TEXT, " +
+                    "MODIFIED INTEGER DEFAULT 0, " +
+                    "CONTENT TEXT, " +
+                    "FAVORITE INTEGER DEFAULT 0, " +
+                    "CATEGORY INTEGER, " +
+                    "ETAG TEXT," +
+                    "EXCERPT TEXT NOT NULL DEFAULT '', " +
+                    "FOREIGN KEY(CATEGORY) REFERENCES CATEGORIES(CATEGORY_ID), " +
+                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
+            DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
+            db.execSQL("CREATE TABLE CATEGORIES(" +
+                    "CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                    "CATEGORY_ACCOUNT_ID INTEGER NOT NULL, " +
+                    "CATEGORY_TITLE TEXT NOT NULL, " +
+                    "UNIQUE( CATEGORY_ACCOUNT_ID , CATEGORY_TITLE), " +
+                    "FOREIGN KEY(CATEGORY_ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
+            DatabaseIndexUtil.createIndex(db, "CATEGORIES", "CATEGORY_ID", "CATEGORY_ACCOUNT_ID", "CATEGORY_TITLE");
+            // A hashtable storing categoryTitle - categoryId Mapping
+            // This is used to prevent too many searches in database
+            Hashtable<String, Integer> categoryTitleIdMap = new Hashtable<>();
+            int id = 1;
+            Cursor tmpNotesCursor = db.rawQuery("SELECT * FROM " + tmpTableNotes, null);
+            while (tmpNotesCursor.moveToNext()) {
+                String categoryTitle = tmpNotesCursor.getString(8);
+                int accountId = tmpNotesCursor.getInt(2);
+                Log.e("###", accountId + "");
+                Integer categoryId;
+                if (categoryTitleIdMap.containsKey(categoryTitle) && categoryTitleIdMap.get(categoryTitle) != null) {
+                    categoryId = categoryTitleIdMap.get(categoryTitle);
+                } else {
+                    // The category does not exists in the database, create it.
+                    categoryId = id++;
+                    ContentValues values = new ContentValues();
+                    values.put("CATEGORY_ID", categoryId);
+                    values.put("CATEGORY_ACCOUNT_ID", accountId);
+                    values.put("CATEGORY_TITLE", categoryTitle);
+                    db.insert("CATEGORIES", null, values);
+                    categoryTitleIdMap.put(categoryTitle, categoryId);
+                }
+                // Move the data in tmp_NOTES to NOTES
+                ContentValues values = new ContentValues();
+                values.put("ID", tmpNotesCursor.getInt(0));
+                values.put("REMOTEID", tmpNotesCursor.getInt(1));
+                values.put("ACCOUNT_ID", tmpNotesCursor.getInt(2));
+                values.put("STATUS", tmpNotesCursor.getString(3));
+                values.put("TITLE", tmpNotesCursor.getString(4));
+                values.put("MODIFIED", tmpNotesCursor.getLong(5));
+                values.put("CONTENT", tmpNotesCursor.getString(6));
+                values.put("FAVORITE", tmpNotesCursor.getInt(7));
+                values.put("CATEGORY", categoryId);
+                values.put("ETAG", tmpNotesCursor.getString(9));
+                values.put("EXCERPT", tmpNotesCursor.getString(10));
+                db.insert("NOTES", null, values);
+            }
+            tmpNotesCursor.close();
+            db.execSQL("DROP TABLE IF EXISTS " + tmpTableNotes);
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java
new file mode 100644
index 00000000..27a04456
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java
@@ -0,0 +1,102 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.ContentValues;
+import android.content.Context;
+import android.content.SharedPreferences;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.util.Log;
+
+import androidx.annotation.NonNull;
+import androidx.preference.PreferenceManager;
+
+import java.util.Map;
+
+import it.niedermann.owncloud.notes.android.DarkModeSetting;
+
+public class Migration_15_16 {
+
+    private static final String TAG = Migration_15_16.class.getSimpleName();
+
+    public Migration_15_16(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Runnable notifyWidgets) {
+        if (oldVersion < 16) {
+            // #832 Move note list widget preferences to database
+            db.execSQL("CREATE TABLE WIDGET_NOTE_LISTS ( " +
+                    "ID INTEGER PRIMARY KEY, " +
+                    "ACCOUNT_ID INTEGER, " +
+                    "CATEGORY_ID INTEGER, " +
+                    "MODE INTEGER NOT NULL, " +
+                    "THEME_MODE INTEGER NOT NULL, " +
+                    "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " +
+                    "FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID))");
+
+            final String SP_WIDGET_KEY = "NLW_mode";
+            final String SP_ACCOUNT_ID_KEY = "NLW_account";
+            final String SP_DARK_THEME_KEY = "NLW_darkTheme";
+            final String SP_CATEGORY_KEY = "NLW_cat";
+
+            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
+            SharedPreferences.Editor editor = sharedPreferences.edit();
+            Map<String, ?> prefs = sharedPreferences.getAll();
+            for (Map.Entry<String, ?> pref : prefs.entrySet()) {
+                final String key = pref.getKey();
+                Integer widgetId = null;
+                Integer mode = null;
+                Long accountId = null;
+                Integer themeMode = null;
+                Integer categoryId = null;
+                if (key != null && key.startsWith(SP_WIDGET_KEY)) {
+                    try {
+                        widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length()));
+                        mode = (Integer) pref.getValue();
+                        accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1);
+
+                        try {
+                            themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId();
+                        } catch (ClassCastException e) {
+                            //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting.
+                            themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId();
+                        }
+
+                        if (mode == 2) {
+                            final String categoryTitle = sharedPreferences.getString(SP_CATEGORY_KEY + widgetId, null);
+                            Cursor cursor = db.query(
+                                    "CATEGORIES",
+                                    new String[]{"CATEGORY_ID"},
+                                    "CATEGORY_TITLE = ? AND CATEGORY_ACCOUNT_ID = ? ",
+                                    new String[]{categoryTitle, String.valueOf(accountId)},
+                                    null,
+                                    null,
+                                    null);
+                            if (cursor.moveToNext()) {
+                                categoryId = cursor.getInt(0);
+                            } else {
+                                throw new IllegalStateException("No category id found for title \"" + categoryTitle + "\"");
+                            }
+                            cursor.close();
+                        }
+
+                        ContentValues migratedWidgetValues = new ContentValues();
+                        migratedWidgetValues.put("ID", widgetId);
+                        migratedWidgetValues.put("ACCOUNT_ID", accountId);
+                        migratedWidgetValues.put("CATEGORY_ID", categoryId);
+                        migratedWidgetValues.put("MODE", mode);
+                        migratedWidgetValues.put("THEME_MODE", themeMode);
+                        db.insert("WIDGET_NOTE_LISTS", null, migratedWidgetValues);
+                    } catch (Throwable t) {
+                        Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", mode: " + mode + ", categoryId: " + categoryId + ", themeMode: " + themeMode + "}");
+                        t.printStackTrace();
+                    } finally {
+                        // Clean up old shared preferences
+                        editor.remove(SP_WIDGET_KEY + widgetId);
+                        editor.remove(SP_CATEGORY_KEY + widgetId);
+                        editor.remove(SP_DARK_THEME_KEY + widgetId);
+                        editor.remove(SP_ACCOUNT_ID_KEY + widgetId);
+                    }
+                }
+            }
+            editor.apply();
+            notifyWidgets.run();
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java
new file mode 100644
index 00000000..4ad3368a
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java
@@ -0,0 +1,13 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.database.sqlite.SQLiteDatabase;
+
+import it.niedermann.owncloud.notes.model.DBStatus;
+
+public class Migration_4_5 {
+    public Migration_4_5(SQLiteDatabase db, int oldVersion) {
+        db.execSQL("ALTER TABLE NOTES ADD COLUMN REMOTEID INTEGER");
+        db.execSQL("UPDATE NOTES SET REMOTEID=ID WHERE (REMOTEID IS NULL OR REMOTEID=0) AND STATUS!=?", new String[]{"LOCAL_CREATED"});
+        db.execSQL("UPDATE NOTES SET REMOTEID=0, STATUS=? WHERE STATUS=?", new String[]{DBStatus.LOCAL_EDITED.getTitle(), "LOCAL_CREATED"});
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java
new file mode 100644
index 00000000..412834cd
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java
@@ -0,0 +1,11 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.database.sqlite.SQLiteDatabase;
+
+public class Migration_5_6 {
+    public Migration_5_6(SQLiteDatabase db, int oldVersion) {
+        if (oldVersion < 6) {
+            db.execSQL("ALTER TABLE NOTES ADD COLUMN FAVORITE INTEGER DEFAULT 0");
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java
new file mode 100644
index 00000000..3003e7f1
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java
@@ -0,0 +1,16 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.database.sqlite.SQLiteDatabase;
+
+import it.niedermann.owncloud.notes.util.DatabaseIndexUtil;
+
+public class Migration_6_7 {
+    public Migration_6_7(SQLiteDatabase db, int oldVersion) {
+        if (oldVersion < 7) {
+            DatabaseIndexUtil.dropIndexes(db);
+            db.execSQL("ALTER TABLE NOTES ADD COLUMN CATEGORY TEXT NOT NULL DEFAULT ''");
+            db.execSQL("ALTER TABLE NOTES ADD COLUMN ETAG TEXT");
+            DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java
new file mode 100644
index 00000000..201d02c8
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java
@@ -0,0 +1,126 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.appwidget.AppWidgetManager;
+import android.content.ComponentName;
+import android.content.ContentValues;
+import android.content.Context;
+import android.content.SharedPreferences;
+import android.database.sqlite.SQLiteDatabase;
+import android.util.Log;
+
+import androidx.annotation.NonNull;
+import androidx.core.util.Consumer;
+import androidx.preference.PreferenceManager;
+
+import java.net.MalformedURLException;
+import java.net.URL;
+
+import it.niedermann.owncloud.notes.android.appwidget.NoteListWidget;
+import it.niedermann.owncloud.notes.android.appwidget.SingleNoteWidget;
+import it.niedermann.owncloud.notes.util.DatabaseIndexUtil;
+
+public class Migration_8_9 {
+
+    private static final String TAG = Migration_8_9.class.getSimpleName();
+
+    public Migration_8_9(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Consumer<SQLiteDatabase> recreateDatabase, @NonNull Runnable notifyWidgets) {
+        if (oldVersion < 9) {
+            // Create accounts table
+            db.execSQL("CREATE TABLE ACCOUNTS ( " +
+                    "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                    "URL TEXT, " +
+                    "USERNAME TEXT, " +
+                    "ACCOUNT_NAME TEXT UNIQUE, " +
+                    "ETAG TEXT, " +
+                    "MODIFIED INTEGER)");
+            DatabaseIndexUtil.createIndex(db, "ACCOUNTS", "URL", "USERNAME", "ACCOUNT_NAME", "ETAG", "MODIFIED");
+
+            // Add accountId to notes table
+            db.execSQL("ALTER TABLE NOTES ADD COLUMN ACCOUNT_ID INTEGER NOT NULL DEFAULT 0");
+            DatabaseIndexUtil.createIndex(db, "NOTES", "ACCOUNT_ID");
+
+            // Migrate existing account from SharedPreferences
+            SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
+            String username = sharedPreferences.getString("settingsUsername", "");
+            String url = sharedPreferences.getString("settingsUrl", "");
+            if (!url.isEmpty() && url.endsWith("/")) {
+                url = url.substring(0, url.length() - 1);
+                try {
+                    String accountName = username + "@" + new URL(url).getHost();
+
+                    ContentValues migratedAccountValues = new ContentValues();
+                    migratedAccountValues.put("URL", url);
+                    migratedAccountValues.put("USERNAME", username);
+                    migratedAccountValues.put("ACCOUNT_NAME", accountName);
+                    db.insert("ACCOUNTS", null, migratedAccountValues);
+
+                    // After successful insertion of migrated account, set accountId to 1 in each note
+                    ContentValues values = new ContentValues();
+                    values.put("ACCOUNT_ID", 1);
+                    db.update("NOTES", values, "ACCOUNT_ID = ?", new String[]{"NULL"});
+
+                    // Add FOREIGN_KEY constraint
+                    final String table_temp = "NOTES_TEMP";
+                    db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", "NOTES", table_temp));
+
+                    db.execSQL("CREATE TABLE NOTES ( " +
+                            "ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                            "REMOTEID INTEGER, " +
+                            "ACCOUNT_ID INTEGER, " +
+                            "STATUS VARCHAR(50), " +
+                            "TITLE TEXT, " +
+                            "MODIFIED INTEGER DEFAULT 0, " +
+                            "CONTENT TEXT, " +
+                            "FAVORITE INTEGER DEFAULT 0, " +
+                            "CATEGORY TEXT NOT NULL DEFAULT '', " +
+                            "ETAG TEXT," +
+                            "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))");
+                    DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED");
+
+                    db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ", "NOTES", "ID", "ACCOUNT_ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG")
+                            + String.format("SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s", "ID", values.get("ACCOUNT_ID"), "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", table_temp));
+                    db.execSQL(String.format("DROP TABLE %s;", table_temp));
+
+                    AppWidgetManager awm = AppWidgetManager.getInstance(context);
+                    SharedPreferences.Editor editor = sharedPreferences.edit();
+
+                    // Add accountId '1' to any existing (and configured) appwidgets
+                    int[] appWidgetIdsNLW = awm.getAppWidgetIds(new ComponentName(context, NoteListWidget.class));
+                    int[] appWidgetIdsSNW = awm.getAppWidgetIds(new ComponentName(context, SingleNoteWidget.class));
+
+                    final String WIDGET_MODE_KEY = "NLW_mode";
+                    final String ACCOUNT_ID_KEY = "NLW_account";
+
+                    for (int appWidgetId : appWidgetIdsNLW) {
+                        if (sharedPreferences.getInt(WIDGET_MODE_KEY + appWidgetId, -1) >= 0) {
+                            editor.putLong(ACCOUNT_ID_KEY + appWidgetId, 1);
+                        }
+                    }
+
+                    for (int appWidgetId : appWidgetIdsSNW) {
+                        if (sharedPreferences.getLong("single_note_widget" + appWidgetId, -1) >= 0) {
+                            editor.putLong("SNW_accountId" + appWidgetId, 1);
+                        }
+                    }
+
+                    notifyWidgets.run();
+
+                    // Clean up no longer needed SharedPreferences
+                    editor.remove("notes_last_etag");
+                    editor.remove("notes_last_modified");
+                    editor.remove("settingsUrl");
+                    editor.remove("settingsUsername");
+                    editor.remove("settingsPassword");
+                    editor.apply();
+                } catch (MalformedURLException e) {
+                    Log.e(TAG, "Previous URL could not be parsed. Recreating database...");
+                    e.printStackTrace();
+                    recreateDatabase.accept(db);
+                }
+            } else {
+                Log.e(TAG, "Previous URL is empty or does not end with a '/' character. Recreating database...");
+                recreateDatabase.accept(db);
+            }
+        }
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java
new file mode 100644
index 00000000..9aec3c9b
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java
@@ -0,0 +1,22 @@
+package it.niedermann.owncloud.notes.persistence.migration;
+
+import android.content.ContentValues;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+
+import it.niedermann.owncloud.notes.util.NoteUtil;
+
+public class Migration_9_10 {
+    public Migration_9_10(SQLiteDatabase db, int oldVersion) {
+        if (oldVersion < 10) {
+            db.execSQL("ALTER TABLE NOTES ADD COLUMN EXCERPT INTEGER NOT NULL DEFAULT ''");
+            Cursor cursor = db.query("NOTES", new String[]{"ID", "CONTENT"}, null, null, null, null, null, null);
+            while (cursor.moveToNext()) {
+                ContentValues values = new ContentValues();
+                values.put("EXCERPT", NoteUtil.generateNoteExcerpt(cursor.getString(1)));
+                db.update("NOTES", values, "ID" + " = ? ", new String[]{cursor.getString(0)});
+            }
+            cursor.close();
+        }
+    }
+}