Merge lp:~vthompson/mediascanner2/add-case-insensitive-sorting into lp:mediascanner2

[Victor Thompson]

Add case insensitive sorting.

This adds case insensitive sorting.

However, from the Music app side of things there is still another issue. Currently we use the SDK's SortFilterModel to sort the SongsModel on the Songs tab and the AlbumsModel on the Albums tab since both of these models are sorted by artist by default. Unfortunately, the SortFilterModel also has the same A-Za-z sorting issue, so we'd like to be able to drop it and instead have the model sorted by song title in the Songs tab (SongsModel) and by album name in the Albums tab (AlbumsModel). Do we need to file a bug for this, or do we just need assistance in getting the model properly sorted?

[unity-api-1-bot (unity-api-1-bot)]

PASSED: Continuous integration, rev:
https://jenkins.canonical.com/unity-api-1/job/lp-mediascanner2-ci/18/
Executed test runs:
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build/1716
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-0-fetch/1723
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=amd64,release=xenial+overlay/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=amd64,release=xenial+overlay/1498/artifact/output/*zip*/output.zip
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=amd64,release=zesty/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=amd64,release=zesty/1498/artifact/output/*zip*/output.zip
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=armhf,release=xenial+overlay/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=armhf,release=xenial+overlay/1498/artifact/output/*zip*/output.zip
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=armhf,release=zesty/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=armhf,release=zesty/1498/artifact/output/*zip*/output.zip
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=i386,release=xenial+overlay/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=i386,release=xenial+overlay/1498/artifact/output/*zip*/output.zip
    SUCCESS: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=i386,release=zesty/1498
        deb: https://jenkins.canonical.com/unity-api-1/job/build-2-binpkg/arch=i386,release=zesty/1498/artifact/output/*zip*/output.zip

Click here to trigger a rebuild:
https://jenkins.canonical.com/unity-api-1/job/lp-mediascanner2-ci/18/rebuild

review: Approve (continuous-integration)

[dobey (dobey)]

I'm not sure what the test coverage is like for this code, but would like to see some regression tests for this as well if possible.

[James Henstridge (jamesh)]

I remember discussing this with Victor at the time on IRC, and I think the main issues were:

(a) SQLite's "nocase" collation only helped with characters in the 7-bit ASCII range.

(b) The change means we're no longer taking advantage of the indexes to sort results.

And just looking at the diff now, I can see a third problem:

(c) the "COLLATE $collation" modifier binds to the individual terms of the ORDER BY clause rather than being an independent clause itself. This means that for the places where we sort by multiple columns, we're only doing a nocase collation on the last term.

For the sorting issue, it is possible to create indexes like "CREATE INDEX idx_name ON table (column COLLATE nocase)" which can be used for a nocase sorted result set. But I can't seem to avoid the sort when combined with an indexed WHERE filter.

review: Needs Fixing

Unmerged revisions

289. By Victor Thompson on 2014-11-10

* Add case insensitive sorting

=== modified file 'src/mediascanner/MediaStore.cc'
--- src/mediascanner/MediaStore.cc	2014-09-24 09:39:06 +0000
+++ src/mediascanner/MediaStore.cc	2014-11-10 02:41:25 +0000
@@ -555,7 +555,7 @@
         }
         break;
     case MediaOrder::Title:
-        qs += " ORDER BY title";
+        qs += " ORDER BY title COLLATE NOCASE";
         if (filter.getReverse()) {
             qs += " DESC";
         }
@@ -610,7 +610,7 @@
     switch (filter.getOrder()) {
     case MediaOrder::Default:
     case MediaOrder::Title:
-        qs += " ORDER BY album";
+        qs += " ORDER BY album COLLATE NOCASE";
         if (filter.getReverse()) {
             qs += " DESC";
         }
@@ -645,7 +645,7 @@
     switch (filter.getOrder()) {
     case MediaOrder::Default:
     case MediaOrder::Title:
-        qs += " ORDER BY artist";
+        qs += " ORDER BY artist COLLATE NOCASE";
         if (filter.getReverse()) {
             qs += " DESC";
         }
@@ -716,6 +716,7 @@
     }
     qs += R"(
 ORDER BY album_artist, album, disc_number, track_number, title
+COLLATE NOCASE
 LIMIT ? OFFSET ?
 )";
     Statement query(db, qs.c_str());
@@ -756,6 +757,7 @@
     qs += R"(
 GROUP BY album, album_artist
 ORDER BY album_artist, album
+COLLATE NOCASE
 LIMIT ? OFFSET ?
 )";
     Statement query(db, qs.c_str());
@@ -787,6 +789,7 @@
     qs += R"(
   GROUP BY artist
   ORDER BY artist
+  COLLATE NOCASE
   LIMIT ? OFFSET ?
 )";
     Statement query(db, qs.c_str());
@@ -816,6 +819,7 @@
     qs += R"(
   GROUP BY album_artist
   ORDER BY album_artist
+  COLLATE NOCASE
   LIMIT ? OFFSET ?
 )";
     Statement query(db, qs.c_str());
@@ -840,6 +844,7 @@
   WHERE type = ?
   GROUP BY genre
   ORDER BY genre
+  COLLATE NOCASE
   LIMIT ? OFFSET ?
 )");
     query.bind(1, (int)AudioMedia);