#831 Migrate from SQLiteOpenHelper to Room

15 files changed, 251 insertions, 128 deletions

diff --git a/app/src/main/java/it/niedermann/owncloud/notes/AppendToNoteActivity.java b/app/src/main/java/it/niedermann/owncloud/notes/AppendToNoteActivity.java
index f2532b81..7899a615 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/AppendToNoteActivity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/AppendToNoteActivity.java
@@ -9,6 +9,7 @@ import android.widget.Toast;
 import androidx.annotation.Nullable;
 import androidx.appcompat.app.ActionBar;
 
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.main.MainActivity;
 
@@ -36,7 +37,7 @@ public class AppendToNoteActivity extends MainActivity {
     @Override
     public void onNoteClick(int position, View v) {
         if (receivedText != null && receivedText.length() > 0) {
-            final DBNote note = sqliteOpenHelperDatabase.getNote(localAccount.getId(), ((DBNote) adapter.getItem(position)).getId());
+            final NoteEntity note = roomDatabase.getNoteDao().getNote(localAccount.getId(), ((DBNote) adapter.getItem(position)).getId());
             final String oldContent = note.getContent();
             String newContent;
             if (oldContent != null && oldContent.length() > 0) {
@@ -44,7 +45,7 @@ public class AppendToNoteActivity extends MainActivity {
             } else {
                 newContent = receivedText;
             }
-            sqliteOpenHelperDatabase.updateNoteAndSync(ssoAccount, localAccount, note, newContent, () -> Toast.makeText(this, getString(R.string.added_content, receivedText), Toast.LENGTH_SHORT).show());
+            sqliteOpenHelperDatabase.updateNoteAndSync(ssoAccount, localAccount, NoteEntity.entityToDBNote(note), newContent, () -> Toast.makeText(this, getString(R.string.added_content, receivedText), Toast.LENGTH_SHORT).show());
         } else {
             Toast.makeText(this, R.string.shared_text_empty, Toast.LENGTH_SHORT).show();
         }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/edit/BaseNoteFragment.java b/app/src/main/java/it/niedermann/owncloud/notes/edit/BaseNoteFragment.java
index 8c6323f2..8a632dde 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/edit/BaseNoteFragment.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/edit/BaseNoteFragment.java
@@ -39,6 +39,7 @@ import it.niedermann.owncloud.notes.edit.title.EditTitleDialogFragment.EditTitle
 import it.niedermann.owncloud.notes.persistence.NotesDatabase;
 import it.niedermann.owncloud.notes.persistence.NotesRoomDatabase;
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
+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.DBNote;
@@ -112,7 +113,7 @@ public abstract class BaseNoteFragment extends BrandedFragment implements Catego
                         SingleAccountHelper.setCurrentAccount(requireActivity().getApplicationContext(), localAccountEntity.getAccountName());
                     }
                     isNew = false;
-                    note = originalNote = sqliteOpenHelperDatabase.getNote(localAccountEntity.getId(), id);
+                    note = originalNote = NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(localAccountEntity.getId(), id));
                 } else {
                     CloudNote cloudNote = (CloudNote) requireArguments().getSerializable(PARAM_NEWNOTE);
                     String content = requireArguments().getString(PARAM_CONTENT);
@@ -123,7 +124,7 @@ public abstract class BaseNoteFragment extends BrandedFragment implements Catego
                             note = new DBNote(-1, -1, null, NoteUtil.generateNoteTitle(content), content, false, getString(R.string.category_readonly), null, DBStatus.VOID, -1, "", 0);
                         }
                     } else {
-                        note = sqliteOpenHelperDatabase.getNote(localAccountEntity.getId(), roomDatabase.addNoteAndSync(ssoAccount, localAccountEntity.getId(), cloudNote));
+                        note = NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(localAccountEntity.getId(), roomDatabase.addNoteAndSync(ssoAccount, localAccountEntity.getId(), cloudNote)));
                         originalNote = null;
                     }
                 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/edit/NotePreviewFragment.java b/app/src/main/java/it/niedermann/owncloud/notes/edit/NotePreviewFragment.java
index 04af4a08..2a5fc325 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/edit/NotePreviewFragment.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/edit/NotePreviewFragment.java
@@ -35,6 +35,7 @@ import com.yydcdut.markdown.syntax.text.TextFactory;
 import it.niedermann.owncloud.notes.R;
 import it.niedermann.owncloud.notes.databinding.FragmentNotePreviewBinding;
 import it.niedermann.owncloud.notes.persistence.NotesDatabase;
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.util.MarkDownUtil;
 import it.niedermann.owncloud.notes.shared.util.NoteLinksUtils;
@@ -160,7 +161,7 @@ public class NotePreviewFragment extends SearchableBaseNoteFragment implements O
                         .setOnLinkClickCallback((view, link) -> {
                             if (NoteLinksUtils.isNoteLink(link)) {
                                 final Intent intent = new Intent(requireActivity().getApplicationContext(), EditNoteActivity.class)
-                                        .putExtra(EditNoteActivity.PARAM_NOTE_ID, sqliteOpenHelperDatabase.getLocalIdByRemoteId(this.note.getAccountId(), extractNoteRemoteId(link)));
+                                        .putExtra(EditNoteActivity.PARAM_NOTE_ID, roomDatabase.getNoteDao().getLocalIdByRemoteId(this.note.getAccountId(), extractNoteRemoteId(link)));
                                 startActivity(intent);
                             } else {
                                 Intent browserIntent = new Intent(Intent.ACTION_VIEW, Uri.parse(link));
@@ -213,7 +214,7 @@ public class NotePreviewFragment extends SearchableBaseNoteFragment implements O
                 TextProcessorChain chain = defaultTextProcessorChain(note);
                 SingleSignOnAccount ssoAccount = SingleAccountHelper.getCurrentSingleSignOnAccount(requireContext());
                 sqliteOpenHelperDatabase.getNoteServerSyncHelper().addCallbackPull(ssoAccount, () -> {
-                    note = sqliteOpenHelperDatabase.getNote(note.getAccountId(), note.getId());
+                    note = NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(note.getAccountId(), note.getId()));
                     changedText = note.getContent();
                     binding.singleNoteContent.setText(parseCompat(markdownProcessor, chain.apply(note.getContent())));
                     binding.swiperefreshlayout.setRefreshing(false);
@@ -236,7 +237,7 @@ public class NotePreviewFragment extends SearchableBaseNoteFragment implements O
 
     private TextProcessorChain defaultTextProcessorChain(DBNote note) {
         TextProcessorChain chain = new TextProcessorChain();
-        chain.add(new NoteLinksProcessor(sqliteOpenHelperDatabase.getRemoteIds(note.getAccountId())));
+        chain.add(new NoteLinksProcessor(roomDatabase.getNoteDao().getRemoteIds(note.getAccountId())));
         chain.add(new WwwLinksProcessor());
         return chain;
     }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/edit/NoteReadonlyFragment.java b/app/src/main/java/it/niedermann/owncloud/notes/edit/NoteReadonlyFragment.java
index d9c2a38e..2d35594e 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/edit/NoteReadonlyFragment.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/edit/NoteReadonlyFragment.java
@@ -105,7 +105,7 @@ public class NoteReadonlyFragment extends SearchableBaseNoteFragment {
                         .setOnLinkClickCallback((view, link) -> {
                             if (NoteLinksUtils.isNoteLink(link)) {
                                 long noteRemoteId = NoteLinksUtils.extractNoteRemoteId(link);
-                                long noteLocalId = sqliteOpenHelperDatabase.getLocalIdByRemoteId(this.note.getAccountId(), noteRemoteId);
+                                long noteLocalId = roomDatabase.getNoteDao().getLocalIdByRemoteId(this.note.getAccountId(), noteRemoteId);
                                 Intent intent = new Intent(requireActivity().getApplicationContext(), EditNoteActivity.class);
                                 intent.putExtra(EditNoteActivity.PARAM_NOTE_ID, noteLocalId);
                                 startActivity(intent);
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/main/MainActivity.java b/app/src/main/java/it/niedermann/owncloud/notes/main/MainActivity.java
index d6d5ba0a..28df5165 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/main/MainActivity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/main/MainActivity.java
@@ -79,6 +79,7 @@ import it.niedermann.owncloud.notes.persistence.NotesDatabase;
 import it.niedermann.owncloud.notes.persistence.NotesRoomDatabase;
 import it.niedermann.owncloud.notes.persistence.dao.LocalAccountDao;
 import it.niedermann.owncloud.notes.persistence.entity.LocalAccountEntity;
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.preferences.PreferencesActivity;
 import it.niedermann.owncloud.notes.shared.model.Capabilities;
 import it.niedermann.owncloud.notes.shared.model.Category;
@@ -531,7 +532,7 @@ public class MainActivity extends LockedActivity implements NoteClickListener, V
                 itemUncategorized = null;
             }
 
-            Map<String, Integer> favorites = sqliteOpenHelperDatabase.getFavoritesCount(localAccount.getId());
+            Map<String, Integer> favorites = roomDatabase.getNoteDao().getFavoritesCount(localAccount.getId());
             //noinspection ConstantConditions
             int numFavorites = favorites.containsKey("1") ? favorites.get("1") : 0;
             //noinspection ConstantConditions
@@ -982,7 +983,7 @@ public class MainActivity extends LockedActivity implements NoteClickListener, V
         adapter.deselect(0);
         for (Integer i : selection) {
             DBNote note = (DBNote) adapter.getItem(i);
-            roomDatabase.moveNoteToAnotherAccount(ssoAccount, note.getAccountId(), sqliteOpenHelperDatabase.getNote(note.getAccountId(), note.getId()), account.getId());
+            roomDatabase.moveNoteToAnotherAccount(ssoAccount, note.getAccountId(), NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(note.getAccountId(), note.getId())), account.getId());
             RecyclerView.ViewHolder viewHolder = listView.findViewHolderForAdapterPosition(i);
             if (viewHolder != null) {
                 viewHolder.itemView.setSelected(false);
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/main/MultiSelectedActionModeCallback.java b/app/src/main/java/it/niedermann/owncloud/notes/main/MultiSelectedActionModeCallback.java
index eb21b024..15dc2ec5 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/main/MultiSelectedActionModeCallback.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/main/MultiSelectedActionModeCallback.java
@@ -33,6 +33,7 @@ import it.niedermann.owncloud.notes.main.items.ItemAdapter;
 import it.niedermann.owncloud.notes.persistence.NoteServerSyncHelper.ViewProvider;
 import it.niedermann.owncloud.notes.persistence.NotesDatabase;
 import it.niedermann.owncloud.notes.persistence.NotesRoomDatabase;
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.util.ShareUtil;
 
@@ -108,7 +109,7 @@ public class MultiSelectedActionModeCallback implements Callback {
                     List<Integer> selection = adapter.getSelected();
                     for (Integer i : selection) {
                         DBNote note = (DBNote) adapter.getItem(i);
-                        deletedNotes.add(sqliteOpenHelperDatabase.getNote(note.getAccountId(), note.getId()));
+                        deletedNotes.add(NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(note.getAccountId(), note.getId())));
                         roomDatabase.deleteNoteAndSync(ssoAccount, note.getId());
                     }
                     mode.finish(); // Action picked, so close the CAB
@@ -148,7 +149,7 @@ public class MultiSelectedActionModeCallback implements Callback {
                 final StringBuilder noteContents = new StringBuilder();
                 for (Integer i : adapter.getSelected()) {
                     final DBNote noteWithoutContent = (DBNote) adapter.getItem(i);
-                    final String tempFullNote = sqliteOpenHelperDatabase.getNote(noteWithoutContent.getAccountId(), noteWithoutContent.getId()).getContent();
+                    final String tempFullNote = roomDatabase.getNoteDao().getNote(noteWithoutContent.getAccountId(), noteWithoutContent.getId()).getContent();
                     if (!TextUtils.isEmpty(tempFullNote)) {
                         if (noteContents.length() > 0) {
                             noteContents.append("\n\n");
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/main/items/list/NotesListViewItemTouchHelper.java b/app/src/main/java/it/niedermann/owncloud/notes/main/items/list/NotesListViewItemTouchHelper.java
index efa1e7f6..131e5c56 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/main/items/list/NotesListViewItemTouchHelper.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/main/items/list/NotesListViewItemTouchHelper.java
@@ -18,6 +18,7 @@ import com.nextcloud.android.sso.model.SingleSignOnAccount;
 import it.niedermann.owncloud.notes.R;
 import it.niedermann.owncloud.notes.branding.BrandedSnackbar;
 import it.niedermann.owncloud.notes.persistence.NotesRoomDatabase;
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.ISyncCallback;
 import it.niedermann.owncloud.notes.main.items.ItemAdapter;
@@ -74,7 +75,7 @@ public class NotesListViewItemTouchHelper extends ItemTouchHelper {
                 switch (direction) {
                     case ItemTouchHelper.LEFT:
                         final DBNote dbNoteWithoutContent = (DBNote) adapter.getItem(viewHolder.getAdapterPosition());
-                        final DBNote dbNote = sqliteOpenHelperDatabase.getNote(dbNoteWithoutContent.getAccountId(), dbNoteWithoutContent.getId());
+                        final DBNote dbNote = NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(dbNoteWithoutContent.getAccountId(), dbNoteWithoutContent.getId()));
                         roomDatabase.deleteNoteAndSync(ssoAccount, dbNote.getId());
                         adapter.remove(dbNote);
                         refreshLists.run();
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 08bd38e4..1c187d95 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
@@ -68,6 +68,7 @@ import static it.niedermann.owncloud.notes.widget.singlenote.SingleNoteWidget.up
 /**
  * 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();
@@ -95,56 +96,6 @@ public class NotesDatabase extends AbstractNotesDatabase {
     }
 
     /**
-     * Get a single Note by ID
-     *
-     * @param id int - ID of the requested Note
-     * @return requested {@link DBNote}
-     */
-    public DBNote getNote(long accountId, long id) {
-        List<DBNote> notes = getNotesCustom(accountId, key_id + " = ? AND " + key_status + " != ? AND " + key_account_id + " = ? ", new String[]{String.valueOf(id), DBStatus.LOCAL_DELETED.getTitle(), "" + accountId}, null, false);
-        return notes.isEmpty() ? null : notes.get(0);
-    }
-
-    /**
-     * Gets all the remoteIds of all not deleted notes of an account
-     *
-     * @param accountId get the remoteIds from all notes of this account
-     * @return {@link Set<String>} remoteIds from all notes
-     */
-    public Set<String> getRemoteIds(long accountId) {
-        Cursor cursor = getReadableDatabase()
-                .query(
-                        table_notes,
-                        new String[]{key_remote_id},
-                        key_status + " != ? AND " + key_account_id + " = ?",
-                        new String[]{DBStatus.LOCAL_DELETED.getTitle(), "" + accountId},
-                        null,
-                        null,
-                        null
-                );
-        Set<String> remoteIds = new HashSet<>();
-        while (cursor.moveToNext()) {
-            remoteIds.add(cursor.getString(0));
-        }
-        cursor.close();
-        return remoteIds;
-    }
-
-    /**
-     * Get a single Note by remote Id (aka. nextcloud file id)
-     *
-     * @param remoteId int - remote ID of the requested Note
-     * @return {@link DBNote#getId()}
-     */
-    public long getLocalIdByRemoteId(long accountId, long remoteId) {
-        List<DBNote> notes = getNotesCustom(accountId, key_remote_id + " = ? AND " + key_status + " != ? AND " + key_account_id + " = ? ", new String[]{String.valueOf(remoteId), DBStatus.LOCAL_DELETED.getTitle(), "" + accountId}, null, true);
-        if (notes.isEmpty() || notes.get(0) == null) {
-            throw new IllegalArgumentException("There is no note with remoteId \"" + remoteId + "\"");
-        }
-        return notes.get(0).getId();
-    }
-
-    /**
      * Query the database with a custom raw query.
      *
      * @param selection     A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself).
@@ -323,26 +274,6 @@ public class NotesDatabase extends AbstractNotesDatabase {
         return getNotesCustom(accountId, key_status + " != ? AND " + key_account_id + " = ?", new String[]{DBStatus.VOID.getTitle(), "" + accountId}, null, false);
     }
 
-    @NonNull
-    @WorkerThread
-    public Map<String, Integer> getFavoritesCount(long accountId) {
-        validateAccountId(accountId);
-        SQLiteDatabase db = getReadableDatabase();
-        Cursor cursor = db.query(
-                table_notes,
-                new String[]{key_favorite, "COUNT(*)"},
-                key_status + " != ? AND " + key_account_id + " = ?",
-                new String[]{DBStatus.LOCAL_DELETED.getTitle(), "" + accountId},
-                key_favorite,
-                null,
-                key_favorite);
-        Map<String, Integer> favorites = new HashMap<>(cursor.getCount());
-        while (cursor.moveToNext()) {
-            favorites.put(cursor.getString(0), cursor.getInt(1));
-        }
-        cursor.close();
-        return favorites;
-    }
 
     /**
      * This method return all of the categories with given accountId
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 d0624b1a..1195bbed 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,10 +1,12 @@
 package it.niedermann.owncloud.notes.persistence;
 
-import android.content.ContentValues;
 import android.content.Context;
+import android.content.Intent;
+import android.content.pm.ShortcutInfo;
 import android.content.pm.ShortcutManager;
-import android.database.sqlite.SQLiteDatabase;
+import android.graphics.drawable.Icon;
 import android.os.Build;
+import android.text.TextUtils;
 import android.util.Log;
 
 import androidx.annotation.NonNull;
@@ -17,9 +19,12 @@ import androidx.sqlite.db.SupportSQLiteDatabase;
 
 import com.nextcloud.android.sso.model.SingleSignOnAccount;
 
+import java.util.ArrayList;
 import java.util.Collections;
+import java.util.List;
 
 import it.niedermann.owncloud.notes.R;
+import it.niedermann.owncloud.notes.edit.EditNoteActivity;
 import it.niedermann.owncloud.notes.persistence.dao.CategoryDao;
 import it.niedermann.owncloud.notes.persistence.dao.LocalAccountDao;
 import it.niedermann.owncloud.notes.persistence.dao.NoteDao;
@@ -32,6 +37,7 @@ import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.DBStatus;
 import it.niedermann.owncloud.notes.shared.util.ColorUtil;
 
+import static it.niedermann.owncloud.notes.edit.EditNoteActivity.ACTION_SHORTCUT;
 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;
@@ -147,8 +153,8 @@ public abstract class NotesRoomDatabase extends RoomDatabase {
             return categoryId;
         } else {
             CategoryEntity entity = new CategoryEntity();
-            entity.accountId = accountId;
-            entity.title = categoryTitle;
+            entity.setAccountId(accountId);
+            entity.setTitle(categoryTitle);
             return getCategoryDao().addCategory(entity);
         }
     }
@@ -162,8 +168,6 @@ public abstract class NotesRoomDatabase extends RoomDatabase {
         syncHelper.scheduleSync(ssoAccount, true);
     }
 
-
-
     /**
      * Marks a Note in the Database as Deleted. In the next Synchronization it will be deleted
      * from the Server.
@@ -215,25 +219,26 @@ public abstract class NotesRoomDatabase extends RoomDatabase {
         if (note instanceof DBNote) {
             DBNote dbNote = (DBNote) note;
             if (dbNote.getId() > 0) {
-                entity.id = dbNote.getId();
+                entity.setId(dbNote.getId());
             }
-            entity.status = dbNote.getStatus().getTitle();
-            entity.accountId = dbNote.getAccountId();
-            entity.excerpt = dbNote.getExcerpt();
+            entity.setStatus(dbNote.getStatus());
+            entity.setAccountId(dbNote.getAccountId());
+            entity.setExcerpt(dbNote.getExcerpt());
         } else {
-            entity.status = DBStatus.VOID.getTitle();
-            entity.accountId = accountId;
-            entity.excerpt = generateNoteExcerpt(note.getContent(), note.getTitle());
+            entity.setStatus(DBStatus.VOID);
+            entity.setAccountId(accountId);
+            entity.setExcerpt(generateNoteExcerpt(note.getContent(), note.getTitle()));
         }
         if (note.getRemoteId() > 0) {
-            entity.remoteId = note.getRemoteId();
+            entity.setRemoteId(note.getRemoteId());
         }
-        entity.title = note.getTitle();
-        entity.modified = note.getModified().getTimeInMillis() / 1000;
-        entity.content = note.getContent();
-        entity.favorite = note.isFavorite();
-        entity.categoryId = getOrCreateCategoryIdByTitle(accountId, note.getCategory());
-        entity.eTag = note.getEtag();
+        entity.setTitle(note.getTitle());
+        entity.setModified(note.getModified().getTimeInMillis() / 1000);
+        entity.setContent(note.getContent());
+        entity.setFavorite(note.isFavorite());
+        // FIXME
+//        entity.setCategory(getOrCreateCategoryIdByTitle(accountId, note.getCategory()));
+        entity.seteTag(note.getEtag());
         return getNoteDao().addNote(entity);
     }
 
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 5284fa75..3e64c64a 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
@@ -9,7 +9,11 @@ import androidx.room.Dao;
 import androidx.room.Insert;
 import androidx.room.Query;
 
+import java.util.HashMap;
+import java.util.Map;
+
 import it.niedermann.owncloud.notes.persistence.entity.CategoryEntity;
+import it.niedermann.owncloud.notes.shared.model.DBStatus;
 
 @Dao
 public interface CategoryDao {
@@ -23,7 +27,7 @@ public interface CategoryDao {
      *
      * @param accountId The user accountId
      */
-    @Query("DELETE FROM categoryentity WHERE id NOT IN (SELECT noteentity.categoryId FROM noteentity)")
+    @Query("DELETE FROM categoryentity WHERE id NOT IN (SELECT noteentity.category FROM noteentity)")
     void removeEmptyCategory(long accountId);
 
     @Insert
@@ -31,5 +35,4 @@ public interface CategoryDao {
 
     @Query("SELECT id FROM categoryentity WHERE accountId = :accountId AND title = :title")
     Long getCategoryIdByTitle(long accountId, @NonNull String title);
-
 }
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 213fe9c7..580eadc1 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
@@ -1,10 +1,22 @@
 package it.niedermann.owncloud.notes.persistence.dao;
 
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+
+import androidx.annotation.NonNull;
+import androidx.annotation.WorkerThread;
 import androidx.room.Dao;
 import androidx.room.Insert;
 import androidx.room.Query;
 
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
 import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
+import it.niedermann.owncloud.notes.shared.model.DBNote;
 import it.niedermann.owncloud.notes.shared.model.DBStatus;
 
 @Dao
@@ -16,9 +28,34 @@ public interface NoteDao {
     @Query("UPDATE noteentity SET scrollY = :scrollY WHERE id = :id")
     void updateScrollY(long id, int scrollY);
 
+    @Query("SELECT * FROM noteentity WHERE id = :id AND accountId = :accountId AND status != :")
+    NoteEntity getNote(long accountId, long id);
+
     @Insert
     long addNote(NoteEntity noteEntity);
 
     @Query("UPDATE noteentity SET status = :status WHERE id = :id")
     void updateStatus(long id, DBStatus status);
+
+    /**
+     * Gets all the remoteIds of all not deleted notes of an account
+     *
+     * @param accountId get the remoteIds from all notes of this account
+     * @return {@link Set<String>} remoteIds from all notes
+     */
+    @Query("SELECT remoteId FROM noteentity WHERE accountId = :accountId AND status != \"LOCAL_DELETED\"")
+    Set<String> getRemoteIds(long accountId);
+
+
+    /**
+     * Get a single Note by remote Id (aka. nextcloud file id)
+     *
+     * @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\"")
+    Long getLocalIdByRemoteId(long accountId, long remoteId);
+
+    @Query("SELECT favorite, COUNT(*) FROM noteentity WHERE status != \"LOCAL_DELETED\" AND accountId = :accountId GROUP BY favorite ORDER BY favorite")
+    Map<String, Integer> getFavoritesCount(long accountId);
 }
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryEntity.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryEntity.java
index 2ae6bc00..907b12f3 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryEntity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/entity/CategoryEntity.java
@@ -6,9 +6,33 @@ import androidx.room.PrimaryKey;
 @Entity
 public class CategoryEntity {
     @PrimaryKey
-    public long id;
-    public long accountId;
-    public String title;
+    private long id;
+    private long accountId;
+    private String title;
+
+    public long getId() {
+        return id;
+    }
+
+    public void setId(long id) {
+        this.id = id;
+    }
+
+    public long getAccountId() {
+        return accountId;
+    }
+
+    public void setAccountId(long accountId) {
+        this.accountId = accountId;
+    }
+
+    public String getTitle() {
+        return title;
+    }
+
+    public void setTitle(String title) {
+        this.title = title;
+    }
 }
 //                "FOREIGN KEY(" + key_category + ") REFERENCES " + table_category + "(" + key_category_id + "), " +
 //                "FOREIGN KEY(" + key_account_id + ") REFERENCES " + table_accounts + "(" + key_id + "))");
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 3d386cd1..87213d3a 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
@@ -1,37 +1,150 @@
 package it.niedermann.owncloud.notes.persistence.entity;
 
-import androidx.room.ColumnInfo;
 import androidx.room.Entity;
-import androidx.room.Index;
 import androidx.room.PrimaryKey;
+import androidx.room.Relation;
+
+import java.util.Calendar;
+
+import it.niedermann.owncloud.notes.shared.model.DBNote;
+import it.niedermann.owncloud.notes.shared.model.DBStatus;
 
 @Entity
 public class NoteEntity {
     @PrimaryKey
-    public long id;
+    private long id;
+    private long remoteId;
+    private long accountId;
+    private DBStatus status;
+    private String title;
+    private long modified;
+    private String content;
+    private Boolean favorite;
+    private String eTag;
+    private String excerpt;
+    private int scrollY;
+    @Relation(parentColumn = "id", entityColumn = "id")
+    private CategoryEntity category;
+
+    public long getId() {
+        return id;
+    }
+
+    public void setId(long id) {
+        this.id = id;
+    }
+
+    public long getRemoteId() {
+        return remoteId;
+    }
+
+    public void setRemoteId(long remoteId) {
+        this.remoteId = remoteId;
+    }
+
+    public long getAccountId() {
+        return accountId;
+    }
+
+    public void setAccountId(long accountId) {
+        this.accountId = accountId;
+    }
+
+    public DBStatus getStatus() {
+        return status;
+    }
+
+    public void setStatus(DBStatus status) {
+        this.status = status;
+    }
+
+    public String getTitle() {
+        return title;
+    }
+
+    public void setTitle(String title) {
+        this.title = title;
+    }
+
+    public long getModified() {
+        return modified;
+    }
+
+    public void setModified(long modified) {
+        this.modified = modified;
+    }
+
+    public String getContent() {
+        return content;
+    }
 
-    public long remoteId;
+    public void setContent(String content) {
+        this.content = content;
+    }
 
-    public long accountId;
+    public Boolean getFavorite() {
+        return favorite;
+    }
 
-    public String status;
+    public void setFavorite(Boolean favorite) {
+        this.favorite = favorite;
+    }
 
-    public String title;
+    public String geteTag() {
+        return eTag;
+    }
 
-    public long modified;
+    public void seteTag(String eTag) {
+        this.eTag = eTag;
+    }
 
-    public String content;
+    public String getExcerpt() {
+        return excerpt;
+    }
 
-    public Boolean favorite;
+    public void setExcerpt(String excerpt) {
+        this.excerpt = excerpt;
+    }
 
-    public String eTag;
+    public int getScrollY() {
+        return scrollY;
+    }
 
-    public String excerpt;
+    public void setScrollY(int scrollY) {
+        this.scrollY = scrollY;
+    }
 
-    public int scrollY;
+    public CategoryEntity getCategory() {
+        return category;
+    }
 
-    public long categoryId;
+    public void setCategory(CategoryEntity category) {
+        this.category = category;
+    }
 
+    @Deprecated
+    public static DBNote entityToDBNote(NoteEntity entity) {
+        if(entity == null) {
+            return null;
+        }
+        Calendar modified = Calendar.getInstance();
+        modified.setTimeInMillis(entity.getModified() * 1000);
+        DBNote note = new DBNote(
+                entity.getId(),
+                entity.getRemoteId(),
+                modified,
+                entity.getTitle(),
+                entity.getContent(),
+                entity.getFavorite(),
+                entity.getCategory().getTitle(),
+                entity.geteTag(),
+                entity.getStatus(),
+                entity.getAccountId(),
+                entity.getExcerpt(),
+                entity.getScrollY()
+        );
+        return note;
+    }
 }
 //                "FOREIGN KEY(" + key_category + ") REFERENCES " + table_category + "(" + key_category_id + "), " +
 //                "FOREIGN KEY(" + key_account_id + ") REFERENCES " + table_accounts + "(" + key_id + "))");
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/widget/notelist/NoteListWidgetConfigurationActivity.java b/app/src/main/java/it/niedermann/owncloud/notes/widget/notelist/NoteListWidgetConfigurationActivity.java
index 6256f578..c694522b 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/widget/notelist/NoteListWidgetConfigurationActivity.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/widget/notelist/NoteListWidgetConfigurationActivity.java
@@ -156,7 +156,7 @@ public class NoteListWidgetConfigurationActivity extends LockedActivity {
                 itemUncategorized.icon = NavigationAdapter.ICON_NOFOLDER;
             }
 
-            Map<String, Integer> favorites = sqliteOpenHelperDatabase.getFavoritesCount(localAccount.getId());
+            Map<String, Integer> favorites = roomDatabase.getNoteDao().getFavoritesCount(localAccount.getId());
             //noinspection ConstantConditions
             int numFavorites = favorites.containsKey("1") ? favorites.get("1") : 0;
             //noinspection ConstantConditions
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/widget/singlenote/SingleNoteWidgetFactory.java b/app/src/main/java/it/niedermann/owncloud/notes/widget/singlenote/SingleNoteWidgetFactory.java
index 939c41ef..431bcd7b 100644
--- a/app/src/main/java/it/niedermann/owncloud/notes/widget/singlenote/SingleNoteWidgetFactory.java
+++ b/app/src/main/java/it/niedermann/owncloud/notes/widget/singlenote/SingleNoteWidgetFactory.java
@@ -14,6 +14,8 @@ import com.yydcdut.markdown.syntax.text.TextFactory;
 import java.util.NoSuchElementException;
 
 import it.niedermann.owncloud.notes.R;
+import it.niedermann.owncloud.notes.persistence.NotesRoomDatabase;
+import it.niedermann.owncloud.notes.persistence.entity.NoteEntity;
 import it.niedermann.owncloud.notes.preferences.DarkModeSetting;
 import it.niedermann.owncloud.notes.edit.EditNoteActivity;
 import it.niedermann.owncloud.notes.shared.model.DBNote;
@@ -29,7 +31,8 @@ public class SingleNoteWidgetFactory implements RemoteViewsService.RemoteViewsFa
     private final Context context;
     private final int appWidgetId;
 
-    private NotesDatabase db;
+    private NotesDatabase sqliteOpenHelperDatabase;
+    private NotesRoomDatabase roomDatabase;
     private DBNote note;
     private boolean darkModeActive = false;
 
@@ -39,11 +42,12 @@ public class SingleNoteWidgetFactory implements RemoteViewsService.RemoteViewsFa
         this.context = context;
         appWidgetId = intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID,
                 AppWidgetManager.INVALID_APPWIDGET_ID);
-        db = NotesDatabase.getInstance(context);
+        sqliteOpenHelperDatabase = NotesDatabase.getInstance(context);
+        roomDatabase = NotesRoomDatabase.getInstance(context);
         markdownProcessor = new MarkdownProcessor(this.context);
         markdownProcessor.factory(TextFactory.create());
         try {
-            SingleNoteWidgetData data = db.getSingleNoteWidgetData(appWidgetId);
+            SingleNoteWidgetData data = sqliteOpenHelperDatabase.getSingleNoteWidgetData(appWidgetId);
             darkModeActive = NotesApplication.isDarkThemeActive(context, DarkModeSetting.fromModeID(data.getThemeMode()));
         } catch (NoSuchElementException e) {
             Log.w(TAG, "Widget with ID " + appWidgetId + " seems to be not configured yet.");
@@ -60,10 +64,10 @@ public class SingleNoteWidgetFactory implements RemoteViewsService.RemoteViewsFa
     @Override
     public void onDataSetChanged() {
         try {
-            final SingleNoteWidgetData data = db.getSingleNoteWidgetData(appWidgetId);
+            final SingleNoteWidgetData data = sqliteOpenHelperDatabase.getSingleNoteWidgetData(appWidgetId);
             final long noteId = data.getNoteId();
             Log.v(TAG, "Fetch note with id " + noteId);
-            note = db.getNote(data.getAccountId(), noteId);
+            note = NoteEntity.entityToDBNote(roomDatabase.getNoteDao().getNote(data.getAccountId(), noteId));
 
             if (note == null) {
                 Log.e(TAG, "Error: note not found");