Repository: mopidy/mopidy-local-sqlite

Issue:
Add "sortname" fields
See mopidy/mopidy#940.

Even if this doesn't get added to models, the fields can still be extracted from gstreamer tags passed to `Library.add()` in Mopidy v0.20. `Library.browse()` can then return results using
```
ORDER BY coalesce(sortname, name)
```


Discussion:

Patch:
diff --git a/CHANGES.rst b/CHANGES.rst
index ae7111f..16f16e6 100644
--- a/CHANGES.rst
+++ b/CHANGES.rst
@@ -8,6 +8,10 @@
   If we can find the old data dir, all files are automatically moved to the new
   data dir.
 
+- By default, browsing artists will use the "sortname" field for
+  ordering results, if available.  Set ``use_artist_sortname = false``
+  to sort according to sort according to the displayed name only.
+
 - Remove file system ("Folders") browsing, since this is already
   handled by the ``file`` backend in Mopidy v1.1.
 
diff --git a/README.rst b/README.rst
index a9d63ce..ba529d3 100644
--- a/README.rst
+++ b/README.rst
@@ -64,6 +64,10 @@ but be aware that these are still subject to change::
   # multi-artist tracks [https://github.com/sampsyo/beets/issues/907]
   use_artist_mbid_uri = false
 
+  # whether to use the sortname field for sorting artist browse results;
+  # set to false to sort according to displayed name only
+  use_artist_sortname = true
+
 
 Project Resources
 ------------------------------------------------------------------------
diff --git a/mopidy_local_sqlite/__init__.py b/mopidy_local_sqlite/__init__.py
index 279d7d0..e6c1939 100644
--- a/mopidy_local_sqlite/__init__.py
+++ b/mopidy_local_sqlite/__init__.py
@@ -26,6 +26,7 @@ class Extension(ext.Extension):
         schema['timeout'] = config.Integer(optional=True, minimum=1)
         schema['use_album_mbid_uri'] = config.Boolean()
         schema['use_artist_mbid_uri'] = config.Boolean()
+        schema['use_artist_sortname'] = config.Boolean()
         # no longer used
         schema['search_limit'] = config.Deprecated()
         schema['extract_images'] = config.Deprecated()
diff --git a/mopidy_local_sqlite/ext.conf b/mopidy_local_sqlite/ext.conf
index 5919d64..cfbfa46 100644
--- a/mopidy_local_sqlite/ext.conf
+++ b/mopidy_local_sqlite/ext.conf
@@ -23,3 +23,7 @@ use_album_mbid_uri = true
 # disabled by default, since some taggers do not handle this well for
 # multi-artist tracks [https://github.com/sampsyo/beets/issues/907]
 use_artist_mbid_uri = false
+
+# whether to use the sortname field for sorting artist browse results;
+# set to false to sort according to displayed name only
+use_artist_sortname = true
diff --git a/mopidy_local_sqlite/library.py b/mopidy_local_sqlite/library.py
index d3623c5..da67cda 100644
--- a/mopidy_local_sqlite/library.py
+++ b/mopidy_local_sqlite/library.py
@@ -170,7 +170,8 @@ class SQLiteLibrary(local.Library):
         # to composers and performers
         if type == Ref.TRACK and 'album' in query:
             order = ('disc_no', 'track_no', 'name')
-
+        if type == Ref.ARTIST and self._config['use_artist_sortname']:
+            order = ('coalesce(sortname, name)',)
         roles = role or ('artist', 'albumartist')  # FIXME: re-think 'roles'...
 
         refs = []
diff --git a/mopidy_local_sqlite/schema.py b/mopidy_local_sqlite/schema.py
index df02db2..a169077 100644
--- a/mopidy_local_sqlite/schema.py
+++ b/mopidy_local_sqlite/schema.py
@@ -146,7 +146,7 @@ _SEARCH_FIELDS = {
     'comment'
 }
 
-schema_version = 5
+schema_version = 6
 
 logger = logging.getLogger(__name__)
 
@@ -167,16 +167,18 @@ class Connection(sqlite3.Connection):
 def load(c):
     sql_dir = os.path.join(os.path.dirname(__file__), b'sql')
     user_version = c.execute('PRAGMA user_version').fetchone()[0]
-    if not user_version:
-        logger.info('Creating SQLite database schema v%s', schema_version)
-        script = os.path.join(sql_dir, 'create-v%s.sql' % schema_version)
-        c.executescript(open(script).read())
-        user_version = c.execute('PRAGMA user_version').fetchone()[0]
     while user_version != schema_version:
-        logger.info('Upgrading SQLite database schema v%s', user_version)
-        script = os.path.join(sql_dir, 'upgrade-v%s.sql' % user_version)
-        c.executescript(open(script).read())
-        user_version = c.execute('PRAGMA user_version').fetchone()[0]
+        if user_version:
+            logger.info('Upgrading SQLite database schema v%s', user_version)
+            filename = 'upgrade-v%s.sql' % user_version
+        else:
+            logger.info('Creating SQLite database schema v%s', schema_version)
+            filename = 'schema.sql'
+        with open(os.path.join(sql_dir, filename)) as fh:
+            c.executescript(fh.read())
+        new_version = c.execute('PRAGMA user_version').fetchone()[0]
+        assert new_version != user_version
+        user_version = new_version
     return user_version
 
 
@@ -269,6 +271,7 @@ def insert_artists(c, artists):
     _insert(c, 'artist', {
         'uri': artist.uri,
         'name': artist.name,
+        'sortname': artist.sortname,
         'musicbrainz_id': artist.musicbrainz_id
     })
     return artist.uri
@@ -422,6 +425,7 @@ def _track(row):
             albumartists = [Artist(
                 uri=row.albumartist_uri,
                 name=row.albumartist_name,
+                sortname=row.albumartist_sortname,
                 musicbrainz_id=row.albumartist_musicbrainz_id
             )]
         else:
@@ -440,18 +444,21 @@ def _track(row):
         kwargs['artists'] = [Artist(
             uri=row.artist_uri,
             name=row.artist_name,
+            sortname=row.artist_sortname,
             musicbrainz_id=row.artist_musicbrainz_id
         )]
     if row.composer_uri is not None:
         kwargs['composers'] = [Artist(
             uri=row.composer_uri,
             name=row.composer_name,
+            sortname=row.composer_sortname,
             musicbrainz_id=row.composer_musicbrainz_id
         )]
     if row.performer_uri is not None:
         kwargs['performers'] = [Artist(
             uri=row.performer_uri,
             name=row.performer_name,
+            sortname=row.performer_sortname,
             musicbrainz_id=row.performer_musicbrainz_id
         )]
     return Track(**kwargs)
diff --git a/mopidy_local_sqlite/sql/create-v5.sql b/mopidy_local_sqlite/sql/schema.sql
similarity index 94%
rename from mopidy_local_sqlite/sql/create-v5.sql
rename to mopidy_local_sqlite/sql/schema.sql
index d6c119c..c633016 100644
--- a/mopidy_local_sqlite/sql/create-v5.sql
+++ b/mopidy_local_sqlite/sql/schema.sql
@@ -2,11 +2,12 @@
 
 BEGIN EXCLUSIVE TRANSACTION;
 
-PRAGMA user_version = 5;                -- schema version
+PRAGMA user_version = 6;                -- schema version
 
 CREATE TABLE artist (
     uri             TEXT PRIMARY KEY,   -- artist URI
     name            TEXT NOT NULL,      -- artist name
+    sortname        TEXT,               -- artist name for sorting
     musicbrainz_id  TEXT                -- MusicBrainz ID
 );
 
@@ -66,6 +67,7 @@ SELECT album.uri                        AS uri,
        album.name                       AS name,
        artist.uri                       AS artist_uri,
        artist.name                      AS artist_name,
+       artist.sortname                  AS artist_sortname,
        artist.musicbrainz_id            AS artist_musicbrainz_id,
        album.num_tracks                 AS num_tracks,
        album.num_discs                  AS num_discs,
@@ -97,15 +99,19 @@ SELECT track.rowid                      AS docid,
        album.images                     AS album_images,
        artist.uri                       AS artist_uri,
        artist.name                      AS artist_name,
+       artist.sortname                  AS artist_sortname,
        artist.musicbrainz_id            AS artist_musicbrainz_id,
        composer.uri                     AS composer_uri,
        composer.name                    AS composer_name,
+       composer.sortname                AS composer_sortname,
        composer.musicbrainz_id          AS composer_musicbrainz_id,
        performer.uri                    AS performer_uri,
        performer.name                   AS performer_name,
+       performer.sortname               AS performer_sortname,
        performer.musicbrainz_id         AS performer_musicbrainz_id,
        albumartist.uri                  AS albumartist_uri,
        albumartist.name                 AS albumartist_name,
+       albumartist.sortname             AS albumartist_sortname,
        albumartist.musicbrainz_id       AS albumartist_musicbrainz_id
   FROM track
   LEFT OUTER JOIN album                 ON track.album = album.uri
diff --git a/mopidy_local_sqlite/sql/upgrade-v5.sql b/mopidy_local_sqlite/sql/upgrade-v5.sql
new file mode 100644
index 0000000..45da1c1
--- /dev/null
+++ b/mopidy_local_sqlite/sql/upgrade-v5.sql
@@ -0,0 +1,70 @@
+-- Mopidy-Local-SQLite schema upgrade v5 -> v6
+
+BEGIN EXCLUSIVE TRANSACTION;
+
+ALTER TABLE artist ADD COLUMN sortname TEXT;
+
+DROP VIEW albums;
+DROP VIEW tracks;
+
+CREATE VIEW albums AS
+SELECT album.uri                        AS uri,
+       album.name                       AS name,
+       artist.uri                       AS artist_uri,
+       artist.name                      AS artist_name,
+       artist.sortname                  AS artist_sortname,
+       artist.musicbrainz_id            AS artist_musicbrainz_id,
+       album.num_tracks                 AS num_tracks,
+       album.num_discs                  AS num_discs,
+       album.date                       AS date,
+       album.musicbrainz_id             AS musicbrainz_id,
+       album.images                     AS images
+  FROM album
+  LEFT OUTER JOIN artist                ON album.artists = artist.uri;
+
+CREATE VIEW tracks AS
+SELECT track.rowid                      AS docid,
+       track.uri                        AS uri,
+       track.name                       AS name,
+       track.genre                      AS genre,
+       track.track_no                   AS track_no,
+       track.disc_no                    AS disc_no,
+       track.date                       AS date,
+       track.length                     AS length,
+       track.bitrate                    AS bitrate,
+       track.comment                    AS comment,
+       track.musicbrainz_id             AS musicbrainz_id,
+       track.last_modified              AS last_modified,
+       album.uri                        AS album_uri,
+       album.name                       AS album_name,
+       album.num_tracks                 AS album_num_tracks,
+       album.num_discs                  AS album_num_discs,
+       album.date                       AS album_date,
+       album.musicbrainz_id             AS album_musicbrainz_id,
+       album.images                     AS album_images,
+       artist.uri                       AS artist_uri,
+       artist.name                      AS artist_name,
+       artist.sortname                  AS artist_sortname,
+       artist.musicbrainz_id            AS artist_musicbrainz_id,
+       composer.uri                     AS composer_uri,
+       composer.name                    AS composer_name,
+       composer.sortname                AS composer_sortname,
+       composer.musicbrainz_id          AS composer_musicbrainz_id,
+       performer.uri                    AS performer_uri,
+       performer.name                   AS performer_name,
+       performer.sortname               AS performer_sortname,
+       performer.musicbrainz_id         AS performer_musicbrainz_id,
+       albumartist.uri                  AS albumartist_uri,
+       albumartist.name                 AS albumartist_name,
+       albumartist.sortname             AS albumartist_sortname,
+       albumartist.musicbrainz_id       AS albumartist_musicbrainz_id
+  FROM track
+  LEFT OUTER JOIN album                 ON track.album = album.uri
+  LEFT OUTER JOIN artist                ON track.artists = artist.uri
+  LEFT OUTER JOIN artist AS composer    ON track.composers = composer.uri
+  LEFT OUTER JOIN artist AS performer   ON track.performers = performer.uri
+  LEFT OUTER JOIN artist AS albumartist ON album.artists = albumartist.uri;
+
+PRAGMA user_version = 6;  -- update schema version
+
+END TRANSACTION;


Test patch:
diff --git a/tests/test_extension.py b/tests/test_extension.py
index a865514..099be12 100644
--- a/tests/test_extension.py
+++ b/tests/test_extension.py
@@ -17,3 +17,4 @@ def test_get_config_schema():
     assert 'timeout' in schema
     assert 'use_album_mbid_uri' in schema
     assert 'use_artist_mbid_uri' in schema
+    assert 'use_artist_sortname' in schema