#1504 Sort notes using locale sorting rules

Signed-off-by: Stefan Niedermann <info@niedermann.it>

2 files changed, 26 insertions, 4 deletions

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 89a2a900..cd48b5d8 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
@@ -33,13 +33,13 @@ public interface NoteDao {
     String count = "SELECT COUNT(*) FROM NOTE WHERE status != 'LOCAL_DELETED' AND accountId = :accountId";
     String countFavorites = "SELECT COUNT(*) FROM NOTE WHERE status != 'LOCAL_DELETED' AND accountId = :accountId AND favorite = 1";
     String searchRecentByModified = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) ORDER BY favorite DESC, modified DESC";
-    String searchRecentLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) ORDER BY favorite DESC, title COLLATE NOCASE ASC";
+    String searchRecentLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) ORDER BY favorite DESC, title COLLATE LOCALIZED ASC";
     String searchFavoritesByModified = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND favorite = 1 ORDER BY modified DESC";
-    String searchFavoritesLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND favorite = 1 ORDER BY title COLLATE NOCASE ASC";
+    String searchFavoritesLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND favorite = 1 ORDER BY title COLLATE LOCALIZED ASC";
     String searchUncategorizedByModified = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND category = '' ORDER BY favorite DESC, modified DESC";
-    String searchUncategorizedLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND category = '' ORDER BY favorite DESC, title COLLATE NOCASE ASC";
+    String searchUncategorizedLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND category = '' ORDER BY favorite DESC, title COLLATE LOCALIZED ASC";
     String searchCategoryByModified = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND (category = :category OR category LIKE :category || '/%') ORDER BY category, favorite DESC, modified DESC";
-    String searchCategoryLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND (category = :category OR category LIKE :category || '/%') ORDER BY category, favorite DESC, title COLLATE NOCASE ASC";
+    String searchCategoryLexicographically = "SELECT id, remoteId, accountId, title, favorite, excerpt, modified, category, status, '' as eTag, '' as content, 0 as scrollY FROM NOTE WHERE accountId = :accountId AND status != 'LOCAL_DELETED' AND (title LIKE :query OR content LIKE :query) AND (category = :category OR category LIKE :category || '/%') ORDER BY category, favorite DESC, title COLLATE LOCALIZED ASC";
 
     @Query(getNoteById)
     LiveData<Note> getNoteById$(long id);
diff --git a/app/src/test/java/it/niedermann/owncloud/notes/persistence/NotesRepositoryTest.java b/app/src/test/java/it/niedermann/owncloud/notes/persistence/NotesRepositoryTest.java
index 7df8c86f..7788c248 100644
--- a/app/src/test/java/it/niedermann/owncloud/notes/persistence/NotesRepositoryTest.java
+++ b/app/src/test/java/it/niedermann/owncloud/notes/persistence/NotesRepositoryTest.java
@@ -36,16 +36,20 @@ import com.nextcloud.android.sso.model.SingleSignOnAccount;
 
 import org.junit.After;
 import org.junit.Before;
+import org.junit.Ignore;
 import org.junit.Rule;
 import org.junit.Test;
 import org.junit.runner.RunWith;
+import org.robolectric.Robolectric;
 import org.robolectric.RobolectricTestRunner;
+import org.robolectric.annotation.Config;
 
 import java.io.IOException;
 import java.lang.reflect.InvocationTargetException;
 import java.util.Arrays;
 import java.util.Calendar;
 import java.util.Collections;
+import java.util.Locale;
 import java.util.concurrent.ExecutorService;
 
 import io.reactivex.Observable;
@@ -284,4 +288,22 @@ public class NotesRepositoryTest {
         repo.updateDisplayName(account.getId(), null);
         assertEquals("Should read userName in favor of displayName if displayName is NULL", "彼得", db.getAccountDao().getAccountById(account.getId()).getDisplayName());
     }
+
+    @Config(qualifiers = "de")
+    @Test
+    @Ignore("Language is properly set to DE, but LOCALIZED SQL query does not work")
+    public void searchLexicographically() throws InterruptedException, IOException {
+        repo.searchRecentByModified(account.getId(), "").forEach(note -> repo.deleteByNoteId(note.getId(), note.getStatus()));
+        Arrays.stream(new Note[]{
+                new Note(10, 1001L, Calendar.getInstance(), "Baaa", "", "Špagety", false, null, VOID, account.getId(), "", 0),
+                new Note(11, null, Calendar.getInstance(), "Aaaa", "", "Svíčková", false, null, VOID, account.getId(), "", 0),
+                new Note(12, 1003L, Calendar.getInstance(), "Äaaa", "", "Zelí", false, null, VOID, account.getId(), "", 0),
+        }).forEach(note -> db.getNoteDao().addNote(note));
+
+        final var recent = NotesTestingUtil.getOrAwaitValue(repo.searchRecentLexicographically$(account.getId(), ""));
+        assertEquals(3, recent.size());
+        assertEquals(11, recent.get(0).getId());
+        assertEquals(12, recent.get(1).getId());
+        assertEquals(10, recent.get(2).getId());
+    }
 }
\ No newline at end of file