#831 Migrate from SQLiteOpenHelper to Room

Remove old SqliteOpenHelper database

20 files changed, 207 insertions, 415 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 46d36e1c..5d46eda7 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
@@ -24,6 +24,9 @@ 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.shared.util.DatabaseIndexUtil;
 
+import static it.niedermann.owncloud.notes.widget.notelist.NoteListWidget.updateNoteListWidgets;
+import static it.niedermann.owncloud.notes.widget.singlenote.SingleNoteWidget.updateSingleNoteWidgets;
+
 abstract class AbstractNotesDatabase extends SQLiteOpenHelper {
 
     private static final int database_version = 19;
@@ -210,5 +213,12 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper {
         onCreate(db);
     }
 
-    protected abstract void notifyWidgets();
+
+    /**
+     * Notify about changed notes.
+     */
+    protected void notifyWidgets() {
+        updateSingleNoteWidgets(getContext());
+        updateNoteListWidgets(getContext());
+    }
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/CapabilitiesWorker.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/CapabilitiesWorker.java
index e0925188..1ffa7629 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/CapabilitiesWorker.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/CapabilitiesWorker.java
@@ -22,7 +22,6 @@ import java.util.concurrent.TimeUnit;
 
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
 import it.niedermann.owncloud.notes.shared.model.Capabilities;
-import it.niedermann.owncloud.notes.shared.model.LocalAccount;
 
 public class CapabilitiesWorker extends Worker {
 
@@ -52,7 +51,7 @@ public class CapabilitiesWorker extends Worker {
                 final Capabilities capabilities = CapabilitiesClient.getCapabilities(getApplicationContext(), ssoAccount, account.getCapabilitiesETag());
                 roomDatabase.getLocalAccountDao().updateCapabilitiesETag(account.getId(), capabilities.getETag());
                 roomDatabase.updateBrand(account.getId(), capabilities);
-                sqliteOpenHelperDatabase.updateApiVersion(account.getId(), capabilities.getApiVersion());
+                roomDatabase.updateApiVersion(account.getId(), capabilities.getApiVersion());
                 Log.i(TAG, capabilities.toString());
             } catch (Exception e) {
                 if (e instanceof NextcloudHttpRequestFailedException) {
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/LoadNotesListTask.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/LoadNotesListTask.java
index 973b74aa..3527d102 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/LoadNotesListTask.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/LoadNotesListTask.java
@@ -13,12 +13,12 @@ import java.util.Calendar;
 import java.util.List;
 
 import it.niedermann.owncloud.notes.R;
+import it.niedermann.owncloud.notes.main.items.section.SectionItem;
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.Category;
+import it.niedermann.owncloud.notes.shared.model.CategorySortingMethod;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.Item;
-import it.niedermann.owncloud.notes.main.items.section.SectionItem;
-import it.niedermann.owncloud.notes.shared.model.CategorySortingMethod;
 import it.niedermann.owncloud.notes.shared.util.NoteUtil;
 
 public class LoadNotesListTask extends AsyncTask<Void, Void, List<Item>> {
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 0783bc13..a3f0d509 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
@@ -37,16 +37,12 @@ import it.niedermann.owncloud.notes.branding.BrandedSnackbar;
 import it.niedermann.owncloud.notes.exception.ExceptionDialogFragment;
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
-import it.niedermann.owncloud.notes.shared.model.CloudNote;
-import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.DBStatus;
 import it.niedermann.owncloud.notes.shared.model.ISyncCallback;
-import it.niedermann.owncloud.notes.shared.model.LocalAccount;
 import it.niedermann.owncloud.notes.shared.model.ServerResponse;
 import it.niedermann.owncloud.notes.shared.model.SyncResultStatus;
 import it.niedermann.owncloud.notes.shared.util.SSOUtil;
 
-import static it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity.entityToLocalAccount;
 import static java.net.HttpURLConnection.HTTP_NOT_FOUND;
 import static java.net.HttpURLConnection.HTTP_NOT_MODIFIED;
 
@@ -59,8 +55,7 @@ public class NoteServerSyncHelper {
 
     private static NoteServerSyncHelper instance;
 
-    private final NotesDatabase sqliteOpenHelperDatabase;
-    private final NotesRoomDatabase roomDatabase;
+    private final NotesRoomDatabase db;
     private final Context context;
 
     // Track network connection changes using a BroadcastReceiver
@@ -102,10 +97,9 @@ public class NoteServerSyncHelper {
     private final Map<String, List<ISyncCallback>> callbacksPush = new HashMap<>();
     private final Map<String, List<ISyncCallback>> callbacksPull = new HashMap<>();
 
-    private NoteServerSyncHelper(NotesDatabase sqliteOpenHelperDatabase, NotesRoomDatabase roomDatabase) {
-        this.sqliteOpenHelperDatabase = sqliteOpenHelperDatabase;
-        this.roomDatabase = roomDatabase;
-        this.context = sqliteOpenHelperDatabase.getContext();
+    private NoteServerSyncHelper(NotesRoomDatabase db) {
+        this.db = db;
+        this.context = db.getContext();
         this.syncOnlyOnWifiKey = context.getApplicationContext().getResources().getString(R.string.pref_key_wifi_only);
 
         // Registers BroadcastReceiver to track network connection changes.
@@ -123,12 +117,12 @@ public class NoteServerSyncHelper {
      * This has to be a singleton in order to realize correct registering and unregistering of
      * the BroadcastReceiver, which listens on changes of network connectivity.
      *
-     * @param sqliteOpenHelperDatabase NoteSQLiteOpenHelper
+     * @param roomDatabase {@link NotesRoomDatabase}
      * @return NoteServerSyncHelper
      */
-    public static synchronized NoteServerSyncHelper getInstance(NotesDatabase sqliteOpenHelperDatabase, NotesRoomDatabase roomDatabase) {
+    public static synchronized NoteServerSyncHelper getInstance(NotesRoomDatabase roomDatabase) {
         if (instance == null) {
-            instance = new NoteServerSyncHelper(sqliteOpenHelperDatabase, roomDatabase);
+            instance = new NoteServerSyncHelper(roomDatabase);
         }
         return instance;
     }
@@ -219,7 +213,7 @@ public class NoteServerSyncHelper {
             Log.d(TAG, "Sync requested (" + (onlyLocalChanges ? "onlyLocalChanges" : "full") + "; " + (Boolean.TRUE.equals(syncActive.get(ssoAccount.name)) ? "sync active" : "sync NOT active") + ") ...");
             if (isSyncPossible() && (!Boolean.TRUE.equals(syncActive.get(ssoAccount.name)) || onlyLocalChanges)) {
                 Log.d(TAG, "... starting now");
-                final LocalAccountEntity localAccount = roomDatabase.getLocalAccountDao().getLocalAccountByAccountName(ssoAccount.name);
+                final LocalAccountEntity localAccount = db.getLocalAccountDao().getLocalAccountByAccountName(ssoAccount.name);
                 if (localAccount == null) {
                     Log.e(TAG, LocalAccountEntity.class.getSimpleName() + " for ssoAccount \"" + ssoAccount.name + "\" is null. Cannot synchronize.", new IllegalStateException());
                     return;
@@ -363,7 +357,7 @@ public class NoteServerSyncHelper {
             Log.d(TAG, "pushLocalChanges()");
 
             boolean success = true;
-            List<NoteEntity> notes = roomDatabase.getNoteDao().getLocalModifiedNotes(localAccount.getId());
+            List<NoteEntity> notes = db.getNoteDao().getLocalModifiedNotes(localAccount.getId());
             for (NoteEntity note : notes) {
                 Log.d(TAG, "   Process Local Note: " + note);
                 try {
@@ -388,7 +382,7 @@ public class NoteServerSyncHelper {
                                 remoteNote = notesClient.createNote(ssoAccount, note).getNote();
                             }
                             // Please note, that db.updateNote() realizes an optimistic conflict resolution, which is required for parallel changes of this Note from the UI.
-                            roomDatabase.updateNote(localAccount.getId(), note.getId(), remoteNote, note);
+                            db.updateNote(localAccount.getId(), note.getId(), remoteNote, note);
                             break;
                         case LOCAL_DELETED:
                             if (note.getRemoteId() > 0) {
@@ -406,7 +400,7 @@ public class NoteServerSyncHelper {
                                 Log.v(TAG, "   ...delete (only local, since it has never been synchronized)");
                             }
                             // Please note, that db.deleteNote() realizes an optimistic conflict resolution, which is required for parallel changes of this Note from the UI.
-                            roomDatabase.deleteNote(note.getId(), DBStatus.LOCAL_DELETED);
+                            db.deleteNote(note.getId(), DBStatus.LOCAL_DELETED);
                             break;
                         default:
                             throw new IllegalStateException("Unknown State of Note: " + note);
@@ -435,7 +429,7 @@ public class NoteServerSyncHelper {
         private boolean pullRemoteChanges() {
             Log.d(TAG, "pullRemoteChanges() for account " + localAccount.getAccountName());
             try {
-                final Map<Long, Long> idMap = roomDatabase.getIdMap(localAccount.getId());
+                final Map<Long, Long> idMap = db.getIdMap(localAccount.getId());
                 final ServerResponse.NotesResponse response = notesClient.getNotes(ssoAccount, localAccount.getModified(), localAccount.getETag());
                 List<NoteEntity> remoteNotes = response.getNotes();
                 Set<Long> remoteIDs = new HashSet<>();
@@ -449,13 +443,13 @@ public class NoteServerSyncHelper {
                         Log.v(TAG, "   ... found → Update");
                         Long remoteId = idMap.get(remoteNote.getRemoteId());
                         if (remoteId != null) {
-                            roomDatabase.updateNote(localAccount.getId(), remoteId, remoteNote, null);
+                            db.updateNote(localAccount.getId(), remoteId, remoteNote, null);
                         } else {
                             Log.e(TAG, "Tried to update note from server, but remoteId of note is null. " + remoteNote);
                         }
                     } else {
                         Log.v(TAG, "   ... create");
-                        roomDatabase.addNote(localAccount.getId(), remoteNote);
+                        db.addNote(localAccount.getId(), remoteNote);
                     }
                 }
                 Log.d(TAG, "   Remove remotely deleted Notes (only those without local changes)");
@@ -463,17 +457,17 @@ public class NoteServerSyncHelper {
                 for (Map.Entry<Long, Long> entry : idMap.entrySet()) {
                     if (!remoteIDs.contains(entry.getKey())) {
                         Log.v(TAG, "   ... remove " + entry.getValue());
-                        roomDatabase.deleteNote(entry.getValue(), DBStatus.VOID);
+                        db.deleteNote(entry.getValue(), DBStatus.VOID);
                     }
                 }
 
                 // update ETag and Last-Modified in order to reduce size of next response
                 localAccount.setETag(response.getETag());
                 localAccount.setModified(response.getLastModified());
-                roomDatabase.getLocalAccountDao().updateETag(localAccount.getId(), localAccount.getETag());
-                roomDatabase.getLocalAccountDao().updateModified(localAccount.getId(), localAccount.getModified());
+                db.getLocalAccountDao().updateETag(localAccount.getId(), localAccount.getETag());
+                db.getLocalAccountDao().updateModified(localAccount.getId(), localAccount.getModified());
                 try {
-                    if (sqliteOpenHelperDatabase.updateApiVersion(localAccount.getId(), response.getSupportedApiVersions())) {
+                    if (db.updateApiVersion(localAccount.getId(), response.getSupportedApiVersions())) {
                         localAccount.setPreferredApiVersion(response.getSupportedApiVersions());
                     }
                 } catch (Exception e) {
@@ -518,8 +512,8 @@ public class NoteServerSyncHelper {
                     callback.onFinish();
                 }
             }
-            sqliteOpenHelperDatabase.notifyWidgets();
-            roomDatabase.updateDynamicShortcuts(localAccount.getId());
+            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))) {
                 scheduleSync(ssoAccount, false);
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClient.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClient.java
index 941dd7f2..7134025e 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClient.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClient.java
@@ -27,7 +27,6 @@ import java.util.Objects;
 
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.ApiVersion;
-import it.niedermann.owncloud.notes.shared.model.CloudNote;
 import it.niedermann.owncloud.notes.shared.model.ServerResponse.NoteResponse;
 import it.niedermann.owncloud.notes.shared.model.ServerResponse.NotesResponse;
 
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClientV02.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClientV02.java
index bcdf5fff..b2ec6164 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClientV02.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesClientV02.java
@@ -13,7 +13,6 @@ import java.util.HashMap;
 import java.util.Map;
 
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
-import it.niedermann.owncloud.notes.shared.model.CloudNote;
 import it.niedermann.owncloud.notes.shared.model.ServerResponse.NoteResponse;
 import it.niedermann.owncloud.notes.shared.model.ServerResponse.NotesResponse;
 
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
deleted file mode 100644
index 08cdd89e..00000000
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesDatabase.java
+++ /dev/null
@@ -1,329 +0,0 @@
-package it.niedermann.owncloud.notes.persistence;
-
-import android.content.ContentValues;
-import android.content.Context;
-import android.content.res.Resources;
-import android.database.Cursor;
-import android.database.sqlite.SQLiteDatabase;
-import android.text.TextUtils;
-import android.util.Log;
-
-import androidx.annotation.NonNull;
-import androidx.annotation.Nullable;
-import androidx.annotation.WorkerThread;
-
-import com.nextcloud.android.sso.AccountImporter;
-import com.nextcloud.android.sso.exceptions.NextcloudFilesAppAccountNotFoundException;
-import com.nextcloud.android.sso.model.SingleSignOnAccount;
-
-import org.json.JSONArray;
-import org.json.JSONException;
-
-import java.util.ArrayList;
-import java.util.Calendar;
-import java.util.List;
-
-import it.niedermann.owncloud.notes.R;
-import it.niedermann.owncloud.notes.main.NavigationAdapter;
-import it.niedermann.owncloud.notes.shared.model.ApiVersion;
-import it.niedermann.owncloud.notes.shared.model.CategorySortingMethod;
-import it.niedermann.owncloud.notes.shared.model.CloudNote;
-import it.niedermann.owncloud.notes.shared.model.DBNote;
-import it.niedermann.owncloud.notes.shared.model.DBStatus;
-import it.niedermann.owncloud.notes.shared.model.ISyncCallback;
-import it.niedermann.owncloud.notes.shared.model.LocalAccount;
-import it.niedermann.owncloud.notes.shared.util.NoteUtil;
-
-import static it.niedermann.owncloud.notes.shared.util.NoteUtil.generateNoteExcerpt;
-import static it.niedermann.owncloud.notes.widget.notelist.NoteListWidget.updateNoteListWidgets;
-import static it.niedermann.owncloud.notes.widget.singlenote.SingleNoteWidget.updateSingleNoteWidgets;
-
-/**
- * Helps to add, get, update and delete Notes with the option to trigger a Resync with the Server.
- */
-@Deprecated
-public class NotesDatabase extends AbstractNotesDatabase {
-
-    private static final String TAG = NotesDatabase.class.getSimpleName();
-
-    private static NotesDatabase instance;
-
-    private final NoteServerSyncHelper serverSyncHelper;
-
-    private NotesDatabase(@NonNull Context context) {
-        super(context, database_name, null);
-        serverSyncHelper = NoteServerSyncHelper.getInstance(this, NotesRoomDatabase.getInstance(context));
-    }
-
-    public static NotesDatabase getInstance(Context context) {
-        if (instance == null)
-            return instance = new NotesDatabase(context);
-        else
-            return instance;
-    }
-
-    public NoteServerSyncHelper getNoteServerSyncHelper() {
-        return serverSyncHelper;
-    }
-
-    /**
-     * This method return all of the categories with given accountId
-     *
-     * @param accountId The user account Id
-     * @return All of the categories with given accountId
-     */
-    @NonNull
-    @WorkerThread
-    public List<NavigationAdapter.CategoryNavigationItem> getCategories(long accountId) {
-        return searchCategories(accountId, null);
-    }
-
-    /**
-     * This method return the category list containing all of the categories containing the
-     * search pattern and matched with the given accountId
-     * The join operation is used because it is needed that the number of notes in each category
-     * If search pattern is null, this method will return all of the categories for corresponding accountId
-     *
-     * @param accountId The user account ID
-     * @param search    The search pattern
-     * @return The category list containing all of the categories matched
-     */
-    @NonNull
-    @WorkerThread
-    public List<NavigationAdapter.CategoryNavigationItem> searchCategories(long accountId, String search) {
-        validateAccountId(accountId);
-        String columns = key_category_id + ", " + key_category_title + ", COUNT(*)";
-        String selection = key_status + " != ?  AND " +
-                key_category_account_id + " = ? AND " +
-                key_category_title + " LIKE ? " +
-                (search == null ? "" : " AND " + key_category_title + " != \"\"");
-        String rawQuery = "SELECT " + columns +
-                " FROM " + table_category +
-                " INNER JOIN " + table_notes +
-                " ON " + key_category + " = " + key_category_id +
-                " WHERE " + selection +
-                " GROUP BY " + key_category_title;
-
-        Cursor cursor = getReadableDatabase().rawQuery(rawQuery,
-                new String[]{DBStatus.LOCAL_DELETED.getTitle(), String.valueOf(accountId),
-                        search == null ? "%" : "%" + search.trim() + "%"});
-
-        List<NavigationAdapter.CategoryNavigationItem> categories = new ArrayList<>(cursor.getCount());
-        while (cursor.moveToNext()) {
-            Resources res = getContext().getResources();
-            String category = cursor.getString(1).toLowerCase();
-            int icon = NavigationAdapter.ICON_FOLDER;
-            if (category.equals(res.getString(R.string.category_music).toLowerCase())) {
-                icon = R.drawable.ic_library_music_grey600_24dp;
-            } else if (category.equals(res.getString(R.string.category_movies).toLowerCase()) || category.equals(res.getString(R.string.category_movie).toLowerCase())) {
-                icon = R.drawable.ic_local_movies_grey600_24dp;
-            } else if (category.equals(res.getString(R.string.category_work).toLowerCase())) {
-                icon = R.drawable.ic_work_grey600_24dp;
-            }
-            categories.add(new NavigationAdapter.CategoryNavigationItem("category:" + cursor.getString(1), cursor.getString(1), cursor.getInt(2), icon, cursor.getLong(0)));
-        }
-
-        cursor.close();
-        return categories;
-    }
-
-    private long addCategory(long accountId, @NonNull String title) {
-        validateAccountId(accountId);
-        SQLiteDatabase db = getWritableDatabase();
-        ContentValues values = new ContentValues(2);
-        values.put(key_category_account_id, accountId);
-        values.put(key_category_title, title);
-        return db.insert(table_category, null, values);
-    }
-
-    public DBNote updateNoteAndSync(SingleSignOnAccount ssoAccount, @NonNull LocalAccount localAccount, @NonNull DBNote oldNote, @Nullable String newContent, @Nullable ISyncCallback callback) {
-        return updateNoteAndSync(ssoAccount, localAccount, oldNote, newContent, null, callback);
-    }
-
-    /**
-     * Updates a single Note with a new content.
-     * The title is derived from the new content automatically, and modified date as well as DBStatus are updated, too -- if the content differs to the state in the database.
-     *
-     * @param oldNote    Note to be changed
-     * @param newContent New content. If this is <code>null</code>, then <code>oldNote</code> is saved again (useful for undoing changes).
-     * @param newTitle   New title. If this is <code>null</code>, then either the old title is reused (in case the note has been synced before) or a title is generated (in case it is a new note)
-     * @param callback   When the synchronization is finished, this callback will be invoked (optional).
-     * @return changed {@link DBNote} if differs from database, otherwise the old {@link DBNote}.
-     */
-    public DBNote updateNoteAndSync(SingleSignOnAccount ssoAccount, @NonNull LocalAccount localAccount, @NonNull DBNote oldNote, @Nullable String newContent, @Nullable String newTitle, @Nullable ISyncCallback callback) {
-        DBNote newNote;
-        if (newContent == null) {
-            newNote = new DBNote(oldNote.getId(), oldNote.getRemoteId(), oldNote.getModified(), oldNote.getTitle(), oldNote.getContent(), oldNote.isFavorite(), oldNote.getCategory(), oldNote.getEtag(), DBStatus.LOCAL_EDITED, localAccount.getId(), oldNote.getExcerpt(), oldNote.getScrollY());
-        } else {
-            final String title;
-            if (newTitle != null) {
-                title = newTitle;
-            } else {
-                if (oldNote.getRemoteId() == 0 || localAccount.getPreferredApiVersion() == null || localAccount.getPreferredApiVersion().compareTo(new ApiVersion("1.0", 0, 0)) < 0) {
-                    title = NoteUtil.generateNonEmptyNoteTitle(newContent, getContext());
-                } else {
-                    title = oldNote.getTitle();
-                }
-            }
-            newNote = new DBNote(oldNote.getId(), oldNote.getRemoteId(), Calendar.getInstance(), title, newContent, oldNote.isFavorite(), oldNote.getCategory(), oldNote.getEtag(), DBStatus.LOCAL_EDITED, localAccount.getId(), generateNoteExcerpt(newContent, title), oldNote.getScrollY());
-        }
-        SQLiteDatabase db = this.getWritableDatabase();
-        ContentValues values = new ContentValues(7);
-        values.put(key_status, newNote.getStatus().getTitle());
-        values.put(key_title, newNote.getTitle());
-        values.put(key_category, getCategoryIdByTitle(newNote.getAccountId(), newNote.getCategory()));
-        values.put(key_modified, newNote.getModified().getTimeInMillis() / 1000);
-        values.put(key_content, newNote.getContent());
-        values.put(key_excerpt, newNote.getExcerpt());
-        values.put(key_scroll_y, newNote.getScrollY());
-        int rows = db.update(table_notes, values, key_id + " = ? AND (" + key_content + " != ? OR " + key_category + " != ?)", new String[]{String.valueOf(newNote.getId()), newNote.getContent(), newNote.getCategory()});
-        removeEmptyCategory(localAccount.getId());
-        // if data was changed, set new status and schedule sync (with callback); otherwise invoke callback directly.
-        if (rows > 0) {
-            notifyWidgets();
-            if (callback != null) {
-                serverSyncHelper.addCallbackPush(ssoAccount, callback);
-            }
-            serverSyncHelper.scheduleSync(ssoAccount, true);
-            return newNote;
-        } else {
-            if (callback != null) {
-                callback.onFinish();
-            }
-            return oldNote;
-        }
-    }
-
-    /**
-     * Notify about changed notes.
-     */
-    protected void notifyWidgets() {
-        updateSingleNoteWidgets(getContext());
-        updateNoteListWidgets(getContext());
-    }
-
-    /**
-     * @param apiVersion has to be a JSON array as a string <code>["0.2", "1.0", ...]</code>
-     * @return whether or not the given {@link ApiVersion} has been written to the database
-     * @throws IllegalArgumentException if the apiVersion does not match the expected format
-     */
-    public boolean updateApiVersion(long accountId, @Nullable String apiVersion) throws IllegalArgumentException {
-        validateAccountId(accountId);
-        if (apiVersion != null) {
-            try {
-                JSONArray apiVersions = new JSONArray(apiVersion);
-                for (int i = 0; i < apiVersions.length(); i++) {
-                    ApiVersion.of(apiVersions.getString(i));
-                }
-                if (apiVersions.length() > 0) {
-                    final SQLiteDatabase db = this.getWritableDatabase();
-                    final ContentValues values = new ContentValues(1);
-                    values.put(key_api_version, apiVersion);
-                    final int updatedRows = db.update(table_accounts, values, key_id + " = ?", new String[]{String.valueOf(accountId)});
-                    if (updatedRows == 1) {
-                        Log.i(TAG, "Updated " + key_api_version + " to \"" + apiVersion + "\" for accountId = " + accountId);
-                    } else {
-                        Log.e(TAG, "Updated " + updatedRows + " but expected only 1 for accountId = " + accountId + " and apiVersion = \"" + apiVersion + "\"");
-                    }
-                    return true;
-                } else {
-                    Log.i(TAG, "Given API version is a valid JSON array but does not contain any valid API versions. Do not update database.");
-                }
-            } catch (NumberFormatException e) {
-                throw new IllegalArgumentException("API version does contain a non-valid version: " + apiVersion);
-            } catch (JSONException e) {
-                throw new IllegalArgumentException("API version must contain be a JSON array: " + apiVersion);
-            }
-        } else {
-            Log.v(TAG, "Given API version is null. Do not update database");
-        }
-        return false;
-    }
-
-    /**
-     * @param localAccount the {@link LocalAccount} that should be deleted
-     * @throws IllegalArgumentException if no account has been deleted by the given accountId
-     */
-    public void deleteAccount(@NonNull LocalAccount localAccount) throws IllegalArgumentException {
-        validateAccountId(localAccount.getId());
-        SQLiteDatabase db = this.getWritableDatabase();
-        int deletedAccounts = db.delete(table_accounts, key_id + " = ?", new String[]{String.valueOf(localAccount.getId())});
-        if (deletedAccounts < 1) {
-            Log.e(TAG, "AccountId '" + localAccount.getId() + "' did not delete any account");
-            throw new IllegalArgumentException("The given accountId does not delete any row");
-        } else if (deletedAccounts > 1) {
-            Log.e(TAG, "AccountId '" + localAccount.getId() + "' deleted unexpectedly '" + deletedAccounts + "' accounts");
-        }
-
-        try {
-            SSOClient.invalidateAPICache(AccountImporter.getSingleSignOnAccount(getContext(), localAccount.getAccountName()));
-        } catch (NextcloudFilesAppAccountNotFoundException e) {
-            e.printStackTrace();
-            SSOClient.invalidateAPICache();
-        }
-
-        final int deletedNotes = db.delete(table_notes, key_account_id + " = ?", new String[]{String.valueOf(localAccount.getId())});
-        Log.v(TAG, "Deleted " + deletedNotes + " notes from account " + localAccount.getId());
-    }
-
-    private static void validateAccountId(long accountId) {
-        if (accountId < 1) {
-            throw new IllegalArgumentException("accountId must be greater than 0");
-        }
-    }
-
-    /**
-     * Get the category if with the given category title
-     * The method does not support fuzzy search.
-     * Because the category title in database is unique, there will not at most one result.
-     * If there is no such category, database will create it if create flag is set.
-     * Otherwise this method will return -1 as default value.
-     *
-     * @param accountId     The user {@link LocalAccount} Id
-     * @param categoryTitle The category title which will be search in the db
-     * @return -1 if there is no such category else the corresponding id
-     * @deprecated replaced by #{{@link NotesRoomDatabase#getOrCreateCategoryIdByTitle(long, String)}}
-     */
-    @NonNull
-    @WorkerThread
-    @Deprecated
-    private Integer getCategoryIdByTitle(long accountId, @NonNull String categoryTitle) {
-        validateAccountId(accountId);
-        SQLiteDatabase db = getReadableDatabase();
-        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);
-        int id;
-        if (cursor.moveToNext()) {
-            id = cursor.getInt(0);
-        } else {
-            id = (int) addCategory(accountId, categoryTitle);
-            if (id == -1) {
-                Log.e(TAG, String.format("Error occurs when creating category: %s", categoryTitle));
-            }
-        }
-        cursor.close();
-        return id;
-    }
-
-    /**
-     * This function will be called when the category or note is updated.
-     * Because sometime we will remove some notes in categories.
-     * Such that there must be such a category without any note.
-     * For these useless category, it is better to remove.
-     * Move a note from a category to another may also lead to the same result.
-     *
-     * @param accountId The user accountId
-     */
-    private void removeEmptyCategory(long accountId) {
-        validateAccountId(accountId);
-        getReadableDatabase().delete(table_category,
-                key_category_id + " NOT IN (SELECT " + key_category + " FROM " + table_notes + ")",
-                null);
-    }
-}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesRoomDatabase.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesRoomDatabase.java
index 3fb6ce34..b2f1363b 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesRoomDatabase.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/NotesRoomDatabase.java
@@ -1,12 +1,12 @@
 package it.niedermann.owncloud.notes.persistence;
 
-import android.content.ContentValues;
 import android.content.Context;
 import android.content.Intent;
 import android.content.SharedPreferences;
 import android.content.pm.ShortcutInfo;
 import android.content.pm.ShortcutManager;
-import android.database.sqlite.SQLiteDatabase;
+import android.content.res.Resources;
+import android.database.Cursor;
 import android.graphics.drawable.Icon;
 import android.os.Build;
 import android.text.TextUtils;
@@ -23,8 +23,13 @@ import androidx.room.TypeConverters;
 import androidx.room.migration.Migration;
 import androidx.sqlite.db.SupportSQLiteDatabase;
 
+import com.nextcloud.android.sso.AccountImporter;
+import com.nextcloud.android.sso.exceptions.NextcloudFilesAppAccountNotFoundException;
 import com.nextcloud.android.sso.model.SingleSignOnAccount;
 
+import org.json.JSONArray;
+import org.json.JSONException;
+
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collections;
@@ -34,12 +39,14 @@ import java.util.Map;
 
 import it.niedermann.owncloud.notes.R;
 import it.niedermann.owncloud.notes.edit.EditNoteActivity;
+import it.niedermann.owncloud.notes.main.NavigationAdapter;
 import it.niedermann.owncloud.notes.persistence.dao.CategoryDao;
 import it.niedermann.owncloud.notes.persistence.dao.LocalAccountDao;
 import it.niedermann.owncloud.notes.persistence.dao.NoteDao;
 import it.niedermann.owncloud.notes.persistence.dao.WidgetNotesListDao;
 import it.niedermann.owncloud.notes.persistence.dao.WidgetSingleNoteDao;
 import it.niedermann.owncloud.notes.persistence.entity.CategoryEntity;
+import it.niedermann.owncloud.notes.persistence.entity.CategoryWithNotesCount;
 import it.niedermann.owncloud.notes.persistence.entity.Converters;
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
@@ -53,7 +60,6 @@ import it.niedermann.owncloud.notes.shared.model.CloudNote;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.DBStatus;
 import it.niedermann.owncloud.notes.shared.model.ISyncCallback;
-import it.niedermann.owncloud.notes.shared.model.LocalAccount;
 import it.niedermann.owncloud.notes.shared.util.ColorUtil;
 import it.niedermann.owncloud.notes.shared.util.NoteUtil;
 
@@ -82,9 +88,9 @@ public abstract class NotesRoomDatabase extends RoomDatabase {
 
     public static NotesRoomDatabase getInstance(@NonNull Context context) {
         if (instance == null) {
-            instance = create(context);
-            NotesRoomDatabase.context = context;
-            syncHelper = NoteServerSyncHelper.getInstance(NotesDatabase.getInstance(context), instance);
+            instance = create(context.getApplicationContext());
+            NotesRoomDatabase.context = context.getApplicationContext();
+            NotesRoomDatabase.syncHelper = NoteServerSyncHelper.getInstance(instance);
         }
         return instance;
     }
@@ -511,4 +517,104 @@ public abstract class NotesRoomDatabase extends RoomDatabase {
             return oldNote;
         }
     }
+
+    /**
+     * @param apiVersion has to be a JSON array as a string <code>["0.2", "1.0", ...]</code>
+     * @return whether or not the given {@link ApiVersion} has been written to the database
+     * @throws IllegalArgumentException if the apiVersion does not match the expected format
+     */
+    public boolean updateApiVersion(long accountId, @Nullable String apiVersion) throws IllegalArgumentException {
+        validateAccountId(accountId);
+        if (apiVersion != null) {
+            try {
+                JSONArray apiVersions = new JSONArray(apiVersion);
+                for (int i = 0; i < apiVersions.length(); i++) {
+                    ApiVersion.of(apiVersions.getString(i));
+                }
+                if (apiVersions.length() > 0) {
+                    final int updatedRows = getLocalAccountDao().updateApiVersion(accountId, apiVersion);
+                    if (updatedRows == 1) {
+                        Log.i(TAG, "Updated apiVersion to \"" + apiVersion + "\" for accountId = " + accountId);
+                    } else {
+                        Log.e(TAG, "Updated " + updatedRows + " but expected only 1 for accountId = " + accountId + " and apiVersion = \"" + apiVersion + "\"");
+                    }
+                    return true;
+                } else {
+                    Log.i(TAG, "Given API version is a valid JSON array but does not contain any valid API versions. Do not update database.");
+                }
+            } catch (NumberFormatException e) {
+                throw new IllegalArgumentException("API version does contain a non-valid version: " + apiVersion);
+            } catch (JSONException e) {
+                throw new IllegalArgumentException("API version must contain be a JSON array: " + apiVersion);
+            }
+        } else {
+            Log.v(TAG, "Given API version is null. Do not update database");
+        }
+        return false;
+    }
+
+    /**
+     * @param localAccount the {@link LocalAccountEntity} that should be deleted
+     * @throws IllegalArgumentException if no account has been deleted by the given accountId
+     */
+    public void deleteAccount(@NonNull LocalAccountEntity localAccount) throws IllegalArgumentException {
+        validateAccountId(localAccount.getId());
+        int deletedAccounts = getLocalAccountDao().deleteAccount(localAccount);
+        if (deletedAccounts < 1) {
+            Log.e(TAG, "AccountId '" + localAccount.getId() + "' did not delete any account");
+            throw new IllegalArgumentException("The given accountId does not delete any row");
+        } else if (deletedAccounts > 1) {
+            Log.e(TAG, "AccountId '" + localAccount.getId() + "' deleted unexpectedly '" + deletedAccounts + "' accounts");
+        }
+
+        try {
+            SSOClient.invalidateAPICache(AccountImporter.getSingleSignOnAccount(context, localAccount.getAccountName()));
+        } catch (NextcloudFilesAppAccountNotFoundException e) {
+            e.printStackTrace();
+            SSOClient.invalidateAPICache();
+        }
+
+        final int deletedNotes = getNoteDao().deleteByAccountId(localAccount.getId());
+        Log.v(TAG, "Deleted " + deletedNotes + " notes from account " + localAccount.getId());
+    }
+
+    public List<NavigationAdapter.CategoryNavigationItem> searchCategories(long accountId, String search) {
+        validateAccountId(accountId);
+        List<CategoryWithNotesCount> counters = getCategoryDao().searchCategories(accountId, search.trim());
+        List<NavigationAdapter.CategoryNavigationItem> categories = new ArrayList<>(counters.size());
+        for(CategoryWithNotesCount counter: counters) {
+            Resources res = context.getResources();
+            String category = counter.getCategory().getTitle().toLowerCase();
+            int icon = NavigationAdapter.ICON_FOLDER;
+            if (category.equals(res.getString(R.string.category_music).toLowerCase())) {
+                icon = R.drawable.ic_library_music_grey600_24dp;
+            } else if (category.equals(res.getString(R.string.category_movies).toLowerCase()) || category.equals(res.getString(R.string.category_movie).toLowerCase())) {
+                icon = R.drawable.ic_local_movies_grey600_24dp;
+            } else if (category.equals(res.getString(R.string.category_work).toLowerCase())) {
+                icon = R.drawable.ic_work_grey600_24dp;
+            }
+            categories.add(new NavigationAdapter.CategoryNavigationItem("category:" + counter.getCategory().getTitle(), counter.getCategory().getTitle(), counter.getTotalNotes(), icon, counter.getCategory().getId()));
+        }
+        return categories;
+    }
+
+    /**
+     * This method return all of the categories with given accountId
+     *
+     * @param accountId The user account Id
+     * @return All of the categories with given accountId
+     */
+    @NonNull
+    @WorkerThread
+    public List<NavigationAdapter.CategoryNavigationItem> getCategories(long accountId) {
+        return searchCategories(accountId, null);
+    }
+
+    public NoteServerSyncHelper getNoteServerSyncHelper() {
+        return NotesRoomDatabase.syncHelper;
+    }
+
+    public Context getContext() {
+        return NotesRoomDatabase.context;
+    }
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/SyncWorker.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/SyncWorker.java
index 810c1813..916ff48f 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/SyncWorker.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/SyncWorker.java
@@ -21,7 +21,6 @@ import java.util.concurrent.TimeUnit;
 
 import it.niedermann.owncloud.notes.R;
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
-import it.niedermann.owncloud.notes.shared.model.LocalAccount;
 
 public class SyncWorker extends Worker {
 
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/CategoryDao.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/CategoryDao.java
index f935d136..32f8b07d 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/CategoryDao.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/CategoryDao.java
@@ -4,7 +4,10 @@ import androidx.room.Dao;
 import androidx.room.Insert;
 import androidx.room.Query;
 
+import java.util.List;
+
 import it.niedermann.owncloud.notes.persistence.entity.CategoryEntity;
+import it.niedermann.owncloud.notes.persistence.entity.CategoryWithNotesCount;
 import it.niedermann.owncloud.notes.shared.model.CategorySortingMethod;
 
 @Dao
@@ -58,4 +61,8 @@ public interface CategoryDao {
 
     @Query("SELECT title FROM CategoryEntity WHERE id = :categoryId")
     String getCategoryTitleById(long categoryId);
+
+    @Query("SELECT id, title, COUNT(*) FROM CategoryEntity INNER JOIN NoteEntity ON CategoryEntity.id = NoteEntity.category_id" +
+            " WHERE status != 'LOCAL_DELETED' AND accountId = :accountId AND title LIKE '%' + :categoryTitle + '%' GROUP BY title")
+    List<CategoryWithNotesCount> searchCategories(Long accountId, String categoryTitle);
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/LocalAccountDao.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/LocalAccountDao.java
index c53a11f3..76a0fd6e 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/LocalAccountDao.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/LocalAccountDao.java
@@ -1,6 +1,7 @@
 package it.niedermann.owncloud.notes.persistence.dao;
 
 import androidx.room.Dao;
+import androidx.room.Delete;
 import androidx.room.Insert;
 import androidx.room.Query;
 
@@ -14,6 +15,9 @@ public interface LocalAccountDao {
     @Insert
     long insert(LocalAccountEntity localAccountEntity);
 
+    @Delete
+    int deleteAccount(LocalAccountEntity localAccountEntity);
+
     @Query("SELECT * FROM localaccountentity WHERE id = :accountId")
     LocalAccountEntity getAccount(long accountId);
 
@@ -37,4 +41,7 @@ public interface LocalAccountDao {
 
     @Query("UPDATE localaccountentity SET modified = :modified WHERE id = :id")
     void updateModified(long id, long modified);
+
+    @Query("UPDATE localaccountentity SET apiVersion = :apiVersion WHERE id = :id")
+    int updateApiVersion(Long id, String apiVersion);
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/NoteDao.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/NoteDao.java
index acad14f8..3dc521ed 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/NoteDao.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/NoteDao.java
@@ -24,12 +24,15 @@ public interface NoteDao {
     @Update(onConflict = OnConflictStrategy.REPLACE)
     int updateNote(NoteEntity newNote);
 
+    @Query("DELETE FROM noteentity WHERE accountId = :accountId")
+    int deleteByAccountId(Long accountId);
+
     /**
      * Returns a list of all Notes in the Database
      *
      * @return List&lt;Note&gt;
      */
-    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\" ORDER BY favorite DESC, modified DESC")
+    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != 'LOCAL_DELETED' ORDER BY favorite DESC, modified DESC")
     List<NoteEntity> getNotes(long accountId);
 
     @Query("DELETE FROM NoteEntity WHERE id = :id and status = :forceDBStatus")
@@ -53,10 +56,10 @@ public interface NoteDao {
      * @param accountId get the remoteIds from all notes of this account
      * @return {@link Set<String>} remoteIds from all notes
      */
-    @Query("SELECT DISTINCT remoteId FROM NoteEntity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\"")
+    @Query("SELECT DISTINCT remoteId FROM NoteEntity WHERE accountId = :accountId AND status != 'LOCAL_DELETED'")
     List<Long> getRemoteIds(long accountId);
 
-    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\"")
+    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != 'LOCAL_DELETED'")
     List<NoteEntity> getRemoteIdAndId(long accountId);
 
     /**
@@ -65,7 +68,7 @@ public interface NoteDao {
      * @param remoteId int - remote ID of the requested Note
      * @return {@link DBNote#getId()}
      */
-    @Query("SELECT id FROM NoteEntity WHERE accountId = :accountId AND remoteId = :remoteId AND status != \"LOCAL_DELETED\"")
+    @Query("SELECT id FROM NoteEntity WHERE accountId = :accountId AND remoteId = :remoteId AND status != 'LOCAL_DELETED'")
     Long getLocalIdByRemoteId(long accountId, long remoteId);
 
     @Query("SELECT COUNT(*) FROM NoteEntity WHERE status != 'LOCAL_DELETED' AND accountId = :accountId AND favorite = 1")
@@ -79,22 +82,22 @@ public interface NoteDao {
      *
      * @return {@link List<NoteEntity>}
      */
-    @Query("SELECT * FROM NoteEntity WHERE status != \"VOID\" AND accountId = :accountId")
+    @Query("SELECT * FROM NoteEntity WHERE status != 'VOID' AND accountId = :accountId")
     List<NoteEntity> getLocalModifiedNotes(long accountId);
 
-    @Query("SELECT * FROM NoteEntity WHERE status != \"LOCAL_DELETED\" AND accountId = :accountId ORDER BY modified DESC LIMIT 4")
+    @Query("SELECT * FROM NoteEntity WHERE status != 'LOCAL_DELETED' AND accountId = :accountId ORDER BY modified DESC LIMIT 4")
     List<NoteEntity> getRecentNotes(long accountId);
 
-    @Query("UPDATE NoteEntity SET status = \"LOCAL_EDITED\", favorite = ((favorite | 1) - (favorite & 1)) WHERE id = :id")
+    @Query("UPDATE NoteEntity SET status = 'LOCAL_EDITED', favorite = ((favorite | 1) - (favorite & 1)) WHERE id = :id")
     void toggleFavorite(long id);
 
-    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\" AND (title LIKE \"%\" + :query + \"%\" OR content LIKE \"%\" + :query + \"%\" OR category_title LIKE \"%\" + :query + \"%\") AND (category_title = :category OR title LIKE :category + \"/%\") AND favorite = :favorite ORDER BY favorite DESC, :sortingMethod")
+    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE '%' + :query + '%' OR content LIKE '%' + :query + '%' OR category_title LIKE '%' + :query + '%') AND (category_title = :category OR title LIKE :category + '/%') AND favorite = :favorite ORDER BY favorite DESC, :sortingMethod")
     List<NoteEntity> searchNotes(long accountId, String query, String category, Boolean favorite, CategorySortingMethod sortingMethod);
 
     /**
      * Needed for subcategories, see https://github.com/stefan-niedermann/nextcloud-notes/issues/902
      */
-    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\" AND (title LIKE \"%\" + :query + \"%\" OR content LIKE \"%\" + :query + \"%\" OR category_title LIKE \"%\" + :query + \"%\") AND (category_title = :category OR title LIKE :category + \"/%\") AND favorite = :favorite ORDER BY category_title, favorite DESC, :sortingMethod")
+    @Query("SELECT * FROM NoteEntity WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE '%' + :query + '%' OR content LIKE '%' + :query + '%' OR category_title LIKE '%' + :query + '%') AND (category_title = :category OR title LIKE :category + '/%') AND favorite = :favorite ORDER BY category_title, favorite DESC, :sortingMethod")
     List<NoteEntity> searchNotesSubcategory(long accountId, String query, String category, Boolean favorite, CategorySortingMethod sortingMethod);
 
     @Query("UPDATE NoteEntity SET remoteId = :remoteId WHERE id = :id")
@@ -116,7 +119,7 @@ public interface NoteDao {
      */
     @Query(
             "UPDATE NoteEntity SET id = :id, title = :title, modified = :modified, title = :title, favorite = :favorite, etag = :eTag, content = :content " +
-                    "WHERE id = :id AND status = \"VOID\" AND (modified != :modified OR favorite != :favorite OR category_title != :categoryTitle OR (eTag == NULL OR eTag != :eTag) OR content != :content)"
+                    "WHERE id = :id AND status = 'VOID' AND (modified != :modified OR favorite != :favorite OR category_title != :categoryTitle OR (eTag == NULL OR eTag != :eTag) OR content != :content)"
     )
     void updateIfNotModifiedLocallyAndRemoteColumnHasChanged(long id, long modified, String title, Boolean favorite, String categoryTitle, String eTag, String content);
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/WidgetNotesListDao.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/WidgetNotesListDao.java
index 444ecb62..902d6037 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/WidgetNotesListDao.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/dao/WidgetNotesListDao.java
@@ -1,15 +1,9 @@
 package it.niedermann.owncloud.notes.persistence.dao;
 
-import android.database.Cursor;
-import android.database.sqlite.SQLiteDatabase;
-
-import androidx.annotation.NonNull;
 import androidx.room.Dao;
 import androidx.room.Insert;
 import androidx.room.Query;
 
-import java.util.NoSuchElementException;
-
 import it.niedermann.owncloud.notes.persistence.entity.WidgetNotesListEntity;
 import it.niedermann.owncloud.notes.widget.notelist.NoteListsWidgetData;
 
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryWithNotesCount.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryWithNotesCount.java
new file mode 100644
index 00000000..3fcda2c0
--- /dev/null
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryWithNotesCount.java
@@ -0,0 +1,26 @@
+package it.niedermann.owncloud.notes.persistence.entity;
+
+import androidx.room.Embedded;
+
+public class CategoryWithNotesCount {
+
+    @Embedded
+    private CategoryEntity category;
+    private Integer totalNotes;
+
+    public CategoryEntity getCategory() {
+        return category;
+    }
+
+    public void setCategory(CategoryEntity category) {
+        this.category = category;
+    }
+
+    public Integer getTotalNotes() {
+        return totalNotes;
+    }
+
+    public void setTotalNotes(Integer totalNotes) {
+        this.totalNotes = totalNotes;
+    }
+}
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/Converters.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/Converters.java
index 4b9fc350..e8cacec6 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/Converters.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/Converters.java
@@ -31,7 +31,7 @@ public class Converters {
 
     @TypeConverter
     public static Calendar longToCalendar(Long value) {
-        if(value == null) {
+        if (value == null) {
             return null;
         }
         Calendar calendar = Calendar.getInstance();
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/LocalAccountEntity.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/LocalAccountEntity.java
index f8a0d1a1..229decab 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/LocalAccountEntity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/LocalAccountEntity.java
@@ -25,12 +25,12 @@ import it.niedermann.owncloud.notes.shared.util.ColorUtil;
 @Entity()
 public class LocalAccountEntity {
     @PrimaryKey
-    public int id;
+    public Long id;
     private String url;
     private String username;
     private String accountName;
     private String eTag;
-    private long modified;
+    private Long modified;
     private String apiVersion;
     private String capabilitiesETag;
     private String color;
@@ -40,11 +40,11 @@ public class LocalAccountEntity {
     @Ignore
     private ApiVersion preferredApiVersion;
 
-    public int getId() {
+    public Long getId() {
         return id;
     }
 
-    public void setId(int id) {
+    public void setId(Long id) {
         this.id = id;
     }
 
@@ -168,24 +168,6 @@ public class LocalAccountEntity {
             this.preferredApiVersion = null;
         }
     }
-
-    @Nullable
-    @Deprecated
-    public static LocalAccount entityToLocalAccount(@Nullable LocalAccountEntity entity) {
-        if(entity == null) {
-            return null;
-        }
-        LocalAccount localAccount = new LocalAccount();
-        localAccount.setAccountName(entity.getAccountName());
-        localAccount.setCapabilitiesETag(entity.getCapabilitiesETag());
-        localAccount.setColor(Color.parseColor('#' + entity.getColor()));
-        localAccount.setTextColor(Color.parseColor('#' + entity.getTextColor()));
-        localAccount.setETag(entity.getETag());
-        localAccount.setId(entity.getId());
-        localAccount.setModified(entity.getModified());
-        localAccount.setUrl(entity.getUrl());
-        return localAccount;
-    }
 }
 
 //                DatabaseIndexUtil.createIndex(db, table_accounts, key_url, key_username, key_account_name, key_etag, key_modified);
\ No newline at end of file
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/NoteEntity.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/NoteEntity.java
index d7f384de..674a33c6 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/NoteEntity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/NoteEntity.java
@@ -4,8 +4,6 @@ import androidx.room.Embedded;
 import androidx.room.Entity;
 import androidx.room.Ignore;
 import androidx.room.PrimaryKey;
-import androidx.room.Relation;
-import androidx.room.TypeConverters;
 
 import java.io.Serializable;
 import java.util.Calendar;
@@ -153,7 +151,7 @@ public class NoteEntity implements Serializable, Item {
 
     @Deprecated
     public static DBNote entityToDBNote(NoteEntity entity) {
-        if(entity == null) {
+        if (entity == null) {
             return null;
         }
         return new DBNote(
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/WidgetNotesListEntity.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/WidgetNotesListEntity.java
index 9180f552..e6a4666a 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/WidgetNotesListEntity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/WidgetNotesListEntity.java
@@ -1,11 +1,9 @@
 package it.niedermann.owncloud.notes.persistence.entity;
 
 import androidx.annotation.IntRange;
-import androidx.annotation.NonNull;
 import androidx.annotation.Nullable;
 import androidx.room.Entity;
 import androidx.room.Ignore;
-import androidx.room.PrimaryKey;
 
 import it.niedermann.owncloud.notes.widget.AbstractWidgetData;
 
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
index 6fd531c9..b77eeb88 100644
--- 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
@@ -5,8 +5,8 @@ import android.database.sqlite.SQLiteDatabase;
 
 import androidx.annotation.NonNull;
 
-import it.niedermann.owncloud.notes.shared.model.ApiVersion;
 import it.niedermann.owncloud.notes.persistence.CapabilitiesWorker;
+import it.niedermann.owncloud.notes.shared.model.ApiVersion;
 
 public class Migration_11_12 {
     /**
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
index 03e171f3..571adceb 100644
--- 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
@@ -15,9 +15,9 @@ import androidx.preference.PreferenceManager;
 import java.net.MalformedURLException;
 import java.net.URL;
 
+import it.niedermann.owncloud.notes.shared.util.DatabaseIndexUtil;
 import it.niedermann.owncloud.notes.widget.notelist.NoteListWidget;
 import it.niedermann.owncloud.notes.widget.singlenote.SingleNoteWidget;
-import it.niedermann.owncloud.notes.shared.util.DatabaseIndexUtil;
 
 public class Migration_8_9 {