Merge pull request #14227 from DaveTBlake/JSONSorting

[JSON]Sorting Music Items various fixes

8 files changed, 292 insertions, 61 deletions

diff --git a/xbmc/dbwrappers/mysqldataset.cpp b/xbmc/dbwrappers/mysqldataset.cpp
index 9f01993fc4..2666d45935 100644
--- a/xbmc/dbwrappers/mysqldataset.cpp
+++ b/xbmc/dbwrappers/mysqldataset.cpp
@@ -585,6 +585,12 @@ std::string MysqlDatabase::vprepare(const char *format, va_list args)
     pos += 6;
   }
 
+  // Remove COLLATE NOCASE the SQLite case insensitive collation.
+  // In MySQL all tables are defined with case insensitive collation utf8_general_ci
+  pos = 0;
+  while ((pos = strResult.find(" COLLATE NOCASE", pos)) != std::string::npos)
+    strResult.erase(pos++, 15);
+
   return strResult;
 }
 
diff --git a/xbmc/dbwrappers/sqlitedataset.cpp b/xbmc/dbwrappers/sqlitedataset.cpp
index fe7dcfe236..b40496a7f7 100644
--- a/xbmc/dbwrappers/sqlitedataset.cpp
+++ b/xbmc/dbwrappers/sqlitedataset.cpp
@@ -450,6 +450,40 @@ std::string SqliteDatabase::vprepare(const char *format, va_list args)
     sqlite3_free(p);
   }
 
+  // Strip SEPARATOR from all GROUP_CONCAT statements:
+  // before: GROUP_CONCAT(field SEPARATOR '; ')
+  // after:  GROUP_CONCAT(field, '; ')
+  pos = strResult.find("GROUP_CONCAT(");
+  while (pos != std::string::npos)
+  {
+    size_t pos2 = strResult.find(" SEPARATOR ", pos + 1);
+    if (pos2 != std::string::npos)
+      strResult.replace(pos2, 10, ",");
+    pos = strResult.find("GROUP_CONCAT(", pos + 1);
+  }
+  // Replace CONCAT with || to concatenate text fields:
+  // before: CONCAT(field1, field2)
+  // after:  field1 || field2
+  pos = strResult.find("CONCAT(");
+  while (pos != std::string::npos)
+  {
+    if (pos == 0 || strResult[pos - 1] == ' ') // Not GROUP_CONCAT
+    {
+      size_t pos2 = strResult.find(",", pos + 1);
+      if (pos2 != std::string::npos)
+      {
+        size_t pos3 = strResult.find(")", pos2 + 1);
+        if (pos3 != std::string::npos)
+        {
+          strResult.erase(pos3, 1);
+          strResult.replace(pos2, 1, " || ");
+          strResult.erase(pos, 7);
+        }
+      }
+    }
+    pos = strResult.find("CONCAT(", pos + 1);
+  }
+
   return strResult;
 }
 
diff --git a/xbmc/interfaces/json-rpc/ApplicationOperations.cpp b/xbmc/interfaces/json-rpc/ApplicationOperations.cpp
index 0c893d96cf..989d8b1e41 100644
--- a/xbmc/interfaces/json-rpc/ApplicationOperations.cpp
+++ b/xbmc/interfaces/json-rpc/ApplicationOperations.cpp
@@ -19,15 +19,16 @@
  */
 
 #include "ApplicationOperations.h"
-#include "InputOperations.h"
 #include "Application.h"
-#include "messaging/ApplicationMessenger.h"
+#include "CompileInfo.h"
 #include "FileItem.h"
+#include "GUIInfoManager.h"
+#include "InputOperations.h"
+#include "LangInfo.h"
 #include "Util.h"
 #include "input/Key.h"
+#include "messaging/ApplicationMessenger.h"
 #include "utils/log.h"
-#include "GUIInfoManager.h"
-#include "CompileInfo.h"
 #include "utils/StringUtils.h"
 #include "utils/Variant.h"
 #include <string.h>
@@ -114,7 +115,7 @@ JSONRPC_STATUS CApplicationOperations::Quit(const std::string &method, ITranspor
 JSONRPC_STATUS CApplicationOperations::GetPropertyValue(const std::string &property, CVariant &result)
 {
   if (property == "volume")
-    result = (int)g_application.GetVolume();
+    result = static_cast<int>(g_application.GetVolume());
   else if (property == "muted")
     result = g_application.IsMuted();
   else if (property == "name")
@@ -146,6 +147,15 @@ JSONRPC_STATUS CApplicationOperations::GetPropertyValue(const std::string &prope
     else
       result["tag"] = "prealpha";
   }
+  else if (property == "sorttokens")
+  {
+    result = CVariant(CVariant::VariantTypeArray); // Ensure no tokens returns as []
+    std::set<std::string> sortTokens = g_langInfo.GetSortTokens();
+    for (const auto& token : sortTokens)
+      result.append(token);
+  }
+  else if (property == "language")
+    result = g_langInfo.GetLocale().ToShortString();
   else
     return InvalidParams;
 
diff --git a/xbmc/interfaces/json-rpc/JSONUtils.h b/xbmc/interfaces/json-rpc/JSONUtils.h
index 7ec9a5f861..7ee75e43b5 100644
--- a/xbmc/interfaces/json-rpc/JSONUtils.h
+++ b/xbmc/interfaces/json-rpc/JSONUtils.h
@@ -100,9 +100,9 @@ namespace JSONRPC
       // parse the sort attributes
       sortAttributes = SortAttributeNone;
       if (parameterObject["sort"]["ignorearticle"].asBoolean())
-        sortAttributes = SortAttributeIgnoreArticle;
-      else
-        sortAttributes = SortAttributeNone;
+        sortAttributes = static_cast<SortAttribute>(sortAttributes | SortAttributeIgnoreArticle);
+      if (parameterObject["sort"]["useartistsortname"].asBoolean())
+        sortAttributes = static_cast<SortAttribute>(sortAttributes | SortAttributeUseArtistSortName);
 
       // parse the sort order
       sortOrder = SortUtils::SortOrderFromString(order);
diff --git a/xbmc/interfaces/json-rpc/schema/types.json b/xbmc/interfaces/json-rpc/schema/types.json
index bd1076b553..78280af2a1 100644
--- a/xbmc/interfaces/json-rpc/schema/types.json
+++ b/xbmc/interfaces/json-rpc/schema/types.json
@@ -111,7 +111,8 @@
                   "bitrate", "random" ]
       },
       "order": { "type": "string", "default": "ascending", "enum": [ "ascending", "descending" ] },
-      "ignorearticle": { "type": "boolean", "default": false }
+      "ignorearticle": { "type": "boolean", "default": false },
+      "useartistsortname": { "type": "boolean", "default": false }	  
     }
   },
   "Library.Id": {
@@ -1623,7 +1624,7 @@
   },
   "Application.Property.Name": {
     "type": "string",
-    "enum": [ "volume", "muted", "name", "version" ]
+    "enum": [ "volume", "muted", "name", "version", "volume", "sorttokens", "language" ]
   },
   "Application.Property.Value": {
     "type": "object",
@@ -1639,7 +1640,9 @@
           "tag": { "type": "string", "enum": [ "prealpha", "alpha", "beta", "releasecandidate", "stable" ], "required": true },
           "tagversion": { "type": "string" }
         }
-      }
+      },
+      "sorttokens": { "$ref": "Array.String", "description": "Articles ignored during sorting when ignorearticle is enabled." },
+      "language": { "type": "string", "minLength": 1, "description": "Current language code and region e.g. en_GB" }
     }
   },
   "Favourite.Fields.Favourite": {
diff --git a/xbmc/interfaces/json-rpc/schema/version.txt b/xbmc/interfaces/json-rpc/schema/version.txt
index c079195d14..ea53cf3718 100644
--- a/xbmc/interfaces/json-rpc/schema/version.txt
+++ b/xbmc/interfaces/json-rpc/schema/version.txt
@@ -1 +1 @@
-JSONRPC_VERSION 9.4.4
+JSONRPC_VERSION 9.5.0
diff --git a/xbmc/music/MusicDatabase.cpp b/xbmc/music/MusicDatabase.cpp
index d7b42193b2..59bcc76f81 100644
--- a/xbmc/music/MusicDatabase.cpp
+++ b/xbmc/music/MusicDatabase.cpp
@@ -43,6 +43,7 @@
 #include "guilib/GUIWindowManager.h"
 #include "guilib/LocalizeStrings.h"
 #include "interfaces/AnnouncementManager.h"
+#include "LangInfo.h"
 #include "messaging/helpers/DialogHelper.h"
 #include "messaging/helpers/DialogOKHelper.h"
 #include "music/tags/MusicInfoTag.h"
@@ -62,6 +63,7 @@
 #include "utils/FileUtils.h"
 #include "utils/LegacyPathTranslation.h"
 #include "utils/log.h"
+#include "utils/Random.h"
 #include "utils/StringUtils.h"
 #include "utils/URIUtils.h"
 #include "utils/XMLUtils.h"
@@ -4787,8 +4789,7 @@ static const translateJSONField JSONtoDBArtist[] = {
 
   // Scalar subquery fields
   { "dateadded",                 "string", true,  "dateAdded",              "(SELECT MAX(song.dateAdded) FROM song_artist JOIN song ON song.idSong = song_artist.idSong WHERE song_artist.idArtist = artist.idArtist) AS dateAdded" },
-  { "",                          "string", true,  "titlesort",              "(CASE WHEN strSortName is not null THEN strSortname ELSE strArtist END) AS titlesort" },
-
+  { "",                          "string", true,  "artistsortname",         "(CASE WHEN strSortName IS NOT NULL THEN strSortname ELSE strArtist END) AS artistsortname" },
   // JOIN fields (multivalue), same order as _JoinToArtistFields
   { "",                                "", false, "isSong",                 "" },
   { "sourceid",                  "string", false, "idSourceAlbum",          "album_source.idSource AS idSourceAlbum" },
@@ -4877,7 +4878,7 @@ bool CMusicDatabase::GetArtistsByWhereJSON(const std::set<std::string>& fields,
     if (sortDescription.sortOrder == SortOrderDescending)
       DESC = " DESC";
     if (sortDescription.sortBy == SortByRandom)
-      orderfields.emplace_back("RANDOM()");
+      orderfields.emplace_back(PrepareSQL("RANDOM()")); // Adjust syntax
     else if (sortDescription.sortBy == SortByArtist)
       orderfields.emplace_back("strArtist");
     else if (sortDescription.sortBy == SortByDateAdded)
@@ -4887,9 +4888,23 @@ bool CMusicDatabase::GetArtistsByWhereJSON(const std::set<std::string>& fields,
     if (sortDescription.sortBy != SortByRandom)
       orderfields.emplace_back("artist.idArtist");
 
-    // Fill inline view filter order fields
-    for (const auto& name : orderfields)
-      extFilter.AppendOrder(name + DESC);
+    // Fill inline view filter order fields, and build sort scalar subquery SQL
+    std::string artistsortSQL;
+    for (auto& name : orderfields)
+    {
+      //Add field for adjusted name sorting using sort name and ignoring articles
+      if (name.compare("strArtist") == 0)
+      {
+        artistsortSQL = SortnameBuildSQL("artistsortname", sortDescription.sortAttributes,
+          "strArtist", "strSortName");
+        if (!artistsortSQL.empty())
+          name = "artistsortname";
+        // Natural number case insensitve sort
+        extFilter.AppendOrder(AlphanumericSortSQL(name, sortDescription.sortOrder));
+      }
+      else
+        extFilter.AppendOrder(name + DESC);
+    }
 
     std::string strSQL;
 
@@ -4934,7 +4949,12 @@ bool CMusicDatabase::GetArtistsByWhereJSON(const std::set<std::string>& fields,
             dbfieldindex.emplace_back(i);
           // Field from scaler subquery
           if (!JSONtoDBArtist[i].SQL.empty())
-            extFilter.AppendField(JSONtoDBArtist[i].SQL);
+          {
+            if (JSONtoDBArtist[i].fieldDB == "artistsortname")
+              extFilter.AppendField(artistsortSQL);
+            else
+              extFilter.AppendField(PrepareSQL(JSONtoDBArtist[i].SQL));
+          }
           else
             // Field from artist table
             extFilter.AppendField(JSONtoDBArtist[i].fieldDB);
@@ -5422,8 +5442,12 @@ bool CMusicDatabase::GetArtistsByWhereJSON(const std::set<std::string>& fields,
 
       m_pDS->next();
     }
-
     m_pDS->close(); // cleanup recordset data
+
+    // Ensure random order of output when results set is sorted to process multi-value joins
+    if (sortDescription.sortBy == SortByRandom && joinLayout.HasFilterFields())
+      KODI::UTILS::RandomShuffle(result["artists"].begin_array(), result["artists"].end_array());
+
     return true;
   }
   catch (...)
@@ -5458,7 +5482,7 @@ static const translateJSONField JSONtoDBAlbum[] = {
   { "dateadded",                 "string", true,  "dateAdded",              "" },  // Scalar subquery in view
   { "lastplayed",                "string", true,  "lastPlayed",             "" },  // Scalar subquery in view
   // Scalar subquery fields
-  { "sourceid",                  "string", true,  "sourceid",               "(SELECT GROUP_CONCAT(album_source.idSource, '; ')  FROM album_source WHERE album_source.idAlbum = albumview.idAlbum) AS sources" },
+  { "sourceid",                  "string", true,  "sourceid",               "(SELECT GROUP_CONCAT(album_source.idSource SEPARATOR '; ')  FROM album_source WHERE album_source.idAlbum = albumview.idAlbum) AS sources" },
   // Single value JOIN fields
   { "thumbnail",                  "image", true,  "thumbnail",              "art.url AS thumbnail" }, // or (SELECT art.url FROM art WHERE art.media_id = album.idAlbum AND art.media_type = "album" AND art.type = "thumb") as url
   // JOIN fields (multivalue), same order as _JoinToAlbumFields
@@ -5467,6 +5491,7 @@ static const translateJSONField JSONtoDBAlbum[] = {
   { "musicbrainzalbumartistid",   "array", false, "strArtistMBID",          "artist.strMusicBrainzArtistID AS strArtistMBID" },
   { "songgenres",                 "array", false, "idSongGenre",            "song_genre.idGenre AS idSongGenre" },
   { "",                                "", false, "strSongGenre",           "genre.strGenre AS strSongGenre" },
+  { "",                                "", true, "artistsortname",          "CASE WHEN strArtistSort IS NOT NULL THEN strArtistSort ELSE strArtists END AS artistsortname"}
   /*
    Album "fanart" and "art" fields of JSON schema are fetched using thumbloader
    and separate queries to allow for fallback strategy.
@@ -5517,7 +5542,7 @@ bool CMusicDatabase::GetAlbumsByWhereJSON(const std::set<std::string>& fields, c
     if (sortDescription.sortOrder == SortOrderDescending)
       DESC = " DESC";
     if (sortDescription.sortBy == SortByRandom)
-      orderfields.emplace_back("RANDOM()");
+      orderfields.emplace_back(PrepareSQL("RANDOM()")); //Adjust styntax
     else if (sortDescription.sortBy == SortByAlbum ||
       sortDescription.sortBy == SortByLabel ||
       sortDescription.sortBy == SortByTitle)
@@ -5591,9 +5616,28 @@ bool CMusicDatabase::GetAlbumsByWhereJSON(const std::set<std::string>& fields, c
     if (sortDescription.sortBy != SortByRandom)
       orderfields.emplace_back("albumview.idAlbum");
 
-    // Fill inline view filter order fields
-    for (const auto& name : orderfields)
-      extFilter.AppendOrder(name + DESC);
+    // Fill inline view filter order fields, and build sort scalar subquery SQL
+    std::string artistsortSQL;
+    for (auto& name : orderfields)
+    {
+      //Add field for adjusted name sorting using sort name and ignoring articles
+      if (name.compare("strArtists") == 0)
+      {
+        artistsortSQL = SortnameBuildSQL("artistsortname", sortDescription.sortAttributes,
+          "strArtists", "strArtistSort");
+        if (!artistsortSQL.empty())
+          name = "artistsortname";
+        // Natural number case insensitve sort
+        extFilter.AppendOrder(AlphanumericSortSQL(name, sortDescription.sortOrder));
+      }
+      else if (name.compare("strAlbum") == 0 || 
+               name.compare("strType") == 0 ||
+               name.compare("strGenres") == 0)
+        // Natural number case insensitve sort
+        extFilter.AppendOrder(AlphanumericSortSQL(name, sortDescription.sortOrder));
+      else
+        extFilter.AppendOrder(name + DESC);
+    }
     
     std::string strSQL;
 
@@ -5639,17 +5683,11 @@ bool CMusicDatabase::GetAlbumsByWhereJSON(const std::set<std::string>& fields, c
             dbfieldindex.emplace_back(i);
           // Field from scaler subquery
           if (!JSONtoDBAlbum[i].SQL.empty())
-          { // Adjust "sources" SQL for MySQL syntax
-            if (JSONtoDBAlbum[i].fieldJSON == "sourceid" && 
-              StringUtils::EqualsNoCase(g_advancedSettings.m_databaseMusic.type, "mysql"))
-            { 
-              // MySQL has syntax GROUP_CONCAT(album_source.idSource SEPARATOR '; ')
-              std::string mysqlgc(JSONtoDBAlbum[i].SQL);
-              StringUtils::Replace(mysqlgc, ", '; '", " SEPARATOR '; '");
-              extFilter.AppendField(mysqlgc);
-            }
+          { 
+            if (JSONtoDBAlbum[i].fieldDB == "artistsortname")
+              extFilter.AppendField(artistsortSQL);
             else
-              extFilter.AppendField(JSONtoDBAlbum[i].SQL);
+              extFilter.AppendField(PrepareSQL(JSONtoDBAlbum[i].SQL));
           }
           else
             // Field from album table
@@ -5864,8 +5902,12 @@ bool CMusicDatabase::GetAlbumsByWhereJSON(const std::set<std::string>& fields, c
       }
       m_pDS->next();
     }
-
     m_pDS->close(); // cleanup recordset data
+
+    // Ensure random order of output when results set is sorted to process multi-value joins
+    if (sortDescription.sortBy == SortByRandom && joinLayout.HasFilterFields())
+      KODI::UTILS::RandomShuffle(result["albums"].begin_array(), result["albums"].end_array());
+
     return true;
   }
   catch (...)
@@ -5896,7 +5938,7 @@ static const translateJSONField JSONtoDBSong[] = {
   { "userrating",              "unsigned", true,  "song.userrating",        "" },
   { "mood",                       "array", true,  "mood",                   "" },
   { "dateadded",                 "string", true,  "dateAdded",              "" },
-  { "file",                      "string", true,  "strPathFile",            "path.strPath || strFilename AS strPathFile" }, 
+  { "file",                      "string", true,  "strPathFile",            "CONCAT(path.strPath, strFilename) AS strPathFile" }, 
   { "",                          "string", true,  "strPath",                "path.strPath AS strPath" },
   { "album",                     "string", true,  "strAlbum",               "album.strAlbum AS strAlbum" },
   { "albumreleasetype",          "string", true,  "strAlbumReleaseType",    "album.strReleaseType AS strAlbumReleaseType" },
@@ -5926,7 +5968,8 @@ static const translateJSONField JSONtoDBSong[] = {
   // Scalar subquery fields
   { "track",                    "integer", true,  "track",                  "(iTrack & 0xffff) AS track" },
   { "disc",                     "integer", true,  "disc",                   "(iTrack >> 16) AS disc" },
-  { "sourceid",                  "string", true,  "sourceid",               "(SELECT GROUP_CONCAT(album_source.idSource, '; ') FROM album_source WHERE album_source.idAlbum = song.idAlbum) AS sources" } 
+  { "sourceid",                  "string", true,  "sourceid",               "(SELECT GROUP_CONCAT(album_source.idSource SEPARATOR '; ') FROM album_source WHERE album_source.idAlbum = song.idAlbum) AS sources" },
+  { "",                                "", true,  "artistsortname",         "CASE WHEN song.strArtistSort IS NOT NULL THEN song.strArtistSort ELSE song.strArtistDisp END AS artistsortname"}
   /* 
   Song "thumbnail", "fanart" and "art" fields of JSON schema are fetched using
   thumbloader and separate queries to allow for fallback strategy
@@ -5999,7 +6042,7 @@ bool CMusicDatabase::GetSongsByWhereJSON(const std::set<std::string>& fields, co
     if (sortDescription.sortOrder == SortOrderDescending)
       DESC = " DESC";
     if (sortDescription.sortBy == SortByRandom)
-      orderfields.emplace_back("RANDOM()");
+      orderfields.emplace_back(PrepareSQL("RANDOM()")); //Adjust styntax
     else if (sortDescription.sortBy == SortByLabel)
     {
       orderfields.emplace_back("song.iTrack");
@@ -6082,9 +6125,29 @@ bool CMusicDatabase::GetSongsByWhereJSON(const std::set<std::string>& fields, co
     if (sortDescription.sortBy != SortByRandom)      
       orderfields.emplace_back("song.idSong");
 
-    // Fill inline view filter order fields
-    for (const auto& name : orderfields)
+    // Fill inline view filter order fields, and build sort scalar subquery SQL
+    std::string artistsortSQL;
+    for (auto& name : orderfields)
+    {
+      //Add field for adjusted name sorting using sort name and ignoring articles
+      if (name.compare("song.strArtistDisp") == 0)
+      {
+        artistsortSQL = SortnameBuildSQL("artistsortname", sortDescription.sortAttributes, 
+          "song.strArtistDisp", "song.strArtistSort");
+        if (!artistsortSQL.empty())
+          name = "artistsortname";
+        // Natural number case insensitve sort
+        extFilter.AppendOrder(AlphanumericSortSQL(name, sortDescription.sortOrder));
+      }
+      else if (name.compare("strTitle") == 0  || 
+               name.compare("strAlbum") == 0 || 
+               name.compare("song.strGenres") == 0)
+        // Natural number case insensitve sort
+        extFilter.AppendOrder(AlphanumericSortSQL(name, sortDescription.sortOrder));
+      else
+
       extFilter.AppendOrder(name + DESC);
+    }
 
     std::string strSQL;
 
@@ -6132,25 +6195,10 @@ bool CMusicDatabase::GetSongsByWhereJSON(const std::set<std::string>& fields, co
           // Field from scaler subquery
           if (!JSONtoDBSong[i].SQL.empty())
           { 
-            if (StringUtils::EqualsNoCase(g_advancedSettings.m_databaseMusic.type, "mysql"))
-            {
-              if (JSONtoDBSong[i].fieldJSON == "sourceid")
-              { // Adjust "sources" SQL for MySQL syntax
-                // GROUP_CONCAT(album_source.idSource SEPARATOR '; ')
-                std::string mysqlgc(JSONtoDBSong[i].SQL);
-                StringUtils::Replace(mysqlgc, ", '; '", " SEPARATOR '; '");
-                extFilter.AppendField(mysqlgc);
-              }
-              else if (JSONtoDBSong[i].fieldJSON == "file")
-              { // Adjust "file" SQL for MySQL syntax
-                // String concatenation is CONCAT not ||
-                extFilter.AppendField("CONCAT(path.strPath, strFilename) as strPathFile");
-              }
-              else
-                extFilter.AppendField(JSONtoDBSong[i].SQL);
-            }
+            if (JSONtoDBSong[i].fieldDB == "artistsortname")
+              extFilter.AppendField(artistsortSQL);
             else
-              extFilter.AppendField(JSONtoDBSong[i].SQL);
+              extFilter.AppendField(PrepareSQL(JSONtoDBSong[i].SQL));
           }
           else
             // Field from song table
@@ -6559,9 +6607,12 @@ bool CMusicDatabase::GetSongsByWhereJSON(const std::set<std::string>& fields, co
       }
       m_pDS->next();
     }
+    m_pDS->close(); // cleanup recordset data
 
+    // Ensure random order of output when results set is sorted to process multi-value joins
+    if (sortDescription.sortBy == SortByRandom && joinLayout.HasFilterFields())
+      KODI::UTILS::RandomShuffle(result["songs"].begin_array(), result["songs"].end_array());
 
-    m_pDS->close(); // cleanup recordset data
     return true;
   }
   catch (...)
@@ -6572,6 +6623,104 @@ bool CMusicDatabase::GetSongsByWhereJSON(const std::set<std::string>& fields, co
   return false;
 }
 
+std::string CMusicDatabase::GetIgnoreArticleSQL(const std::string& strField)
+{
+  /* 
+  Make SQL clause from ignore article list.
+  Group tokens the same length together, for example :
+    WHEN strArtist LIKE 'the ' OR strArtist LIKE 'the.' strArtist LIKE 'the_' ESCAPE '_'
+    THEN SUBSTR(strArtist, 5)
+    WHEN strArtist LIKE 'an ' OR strArtist LIKE 'an.' strArtist LIKE 'an_' ESCAPE '_'
+    THEN SUBSTR(strArtist, 4)
+  */
+  std::set<std::string> sortTokens = g_langInfo.GetSortTokens();
+  std::string sortclause;
+  size_t tokenlength = 0;
+  std::string strWhen;
+  for (const auto& token : sortTokens)
+  {
+    if (token.length() != tokenlength)
+    {
+      if (!strWhen.empty())
+      {
+        if (!sortclause.empty())
+           sortclause += " ";
+        std::string strThen = PrepareSQL(" THEN SUBSTR(%s, %i)", strField.c_str(), tokenlength + 1);
+        sortclause += "WHEN " + strWhen + strThen;
+        strWhen.clear();
+      }
+      tokenlength = token.length();
+    }
+    std::string tokenclause = token;
+    //Escape any ' or % in the token
+    StringUtils::Replace(tokenclause, "'", "''");
+    StringUtils::Replace(tokenclause, "%", "%%");
+    // Single %, _ and ' so avoid using PrepareSQL
+    tokenclause = strField + " LIKE '" + tokenclause + "%'";
+    if (token.find("_") != std::string::npos)
+       tokenclause += " ESCAPE '_'";
+    if (!strWhen.empty())
+       strWhen += " OR ";
+    strWhen += tokenclause;
+  }
+  if (!strWhen.empty())
+  {
+    if (!sortclause.empty())
+       sortclause += " ";
+    std::string strThen = PrepareSQL(" THEN SUBSTR(%s, %i)", strField.c_str(), tokenlength + 1);
+    sortclause += "WHEN " + strWhen + strThen;
+  }
+  return sortclause;
+}
+
+std::string CMusicDatabase::SortnameBuildSQL(const std::string& strAlias, const SortAttribute& sortAttributes, const std::string& strField, const std::string& strSortField)
+{
+  /*
+  Build SQL for sort name scalar subquery from sort attributes and ignore article list.
+  For example :
+  CASE WHEN strArtistSort IS NOT NULL THEN strArtistSort 
+  WHEN strField LIKE 'the ' OR strField LIKE 'the_' ESCAPE '_' THEN SUBSTR(strArtist, 5)
+  WHEN strField LIKE 'LIKE 'an.' strField LIKE 'an_' ESCAPE '_' THEN SUBSTR(strArtist, 4)
+  ELSE strField
+  END AS strAlias
+  */
+
+  std::string artistsortSQL;
+  if (sortAttributes & SortAttributeUseArtistSortName)
+    artistsortSQL = PrepareSQL("WHEN %s IS NOT NULL THEN %s ", strSortField.c_str(), strSortField.c_str());
+  if (sortAttributes & SortAttributeIgnoreArticle)
+  {
+    if (!artistsortSQL.empty())
+      artistsortSQL += " ";
+    // Make SQL from ignore article list, grouping tokens the same length together
+    artistsortSQL += GetIgnoreArticleSQL(strField);
+  }
+  if (!artistsortSQL.empty())
+  {
+    artistsortSQL = "CASE " + artistsortSQL;  // Not prepare as may contain ' and % etc.
+    artistsortSQL += PrepareSQL(" ELSE %s END AS %s", strField.c_str(), strAlias.c_str());
+  }
+
+  return artistsortSQL;
+}
+
+std::string CMusicDatabase::AlphanumericSortSQL(const std::string& strField, const SortOrder& sortOrder)
+{
+  /*
+  Make sort of initial numbers natural, and case insensitive in SQLite.
+  Collation NOCASE ould be more efficient done in table create.
+  MySQL uses case insensitive utf8_general_ci collation defined for tables.
+  Use PrepareSQL to adjust syntax removing NOCASE and add AS UNSIGNED INTEGER
+  */
+  std::string DESC;
+  if (sortOrder == SortOrderDescending)
+    DESC = " DESC";
+  return PrepareSQL("CASE WHEN CAST(%s AS INTEGER) = 0 "
+    "THEN 100000000 ELSE CAST(%s AS INTEGER) END%s, "
+    "%s COLLATE NOCASE%s",
+    strField.c_str(), strField.c_str(), DESC.c_str(), strField.c_str(), DESC.c_str());
+}
+
 void CMusicDatabase::UpdateTables(int version)
 {
   CLog::Log(LOGINFO, "%s - updating tables", __FUNCTION__);
@@ -8485,7 +8634,7 @@ bool CMusicDatabase::UpdateArtistSortNames(int idArtist /*=-1*/)
 {
   // Propagate artist sort names into concatenated artist sort name string for songs and albums
   std::string strSQL;
-  // MySQL syntax for GROUP_CONCAT is different from that in SQLite
+  // MySQL syntax for GROUP_CONCAT with order is different from that in SQLite (not handled by PrepareSQL)
   bool bisMySQL = StringUtils::EqualsNoCase(g_advancedSettings.m_databaseMusic.type, "mysql");
 
   BeginMultipleExecute();
diff --git a/xbmc/music/MusicDatabase.h b/xbmc/music/MusicDatabase.h
index 92d9eb0c8b..b87f14563a 100644
--- a/xbmc/music/MusicDatabase.h
+++ b/xbmc/music/MusicDatabase.h
@@ -672,6 +672,35 @@ private:
   bool SearchSongs(const std::string& strSearch, CFileItemList &songs);
   int GetSongIDFromPath(const std::string &filePath);
 
+  /*! \brief Build SQL  for sort subquery from ignore article token list
+  \param strField original name or title field that articles could be removed from
+  \return SQL string e.g.  WHEN strField LIKE 'the_' ESCAPE '_' THEN SUBSTR(strArtist, 5)
+  */
+  std::string GetIgnoreArticleSQL(const std::string& strField);
+
+  /*! \brief Build SQL for sort name scalar subquery from sort attributes and ignore article list.
+  \param strAlias alias name of scalar subquery field
+  \param sortAttributes the sort attributes e.g. SortAttributeIgnoreArticle
+  \param strField original name or title field that articles could be removed from
+  \param strSortField sort name or title field to be used instead of original (when data not null)
+  \return SQL string e.g. 
+  CASE WHEN strArtistSort IS NOT NULL THEN strArtistSort    
+  WHEN strField LIKE 'the ' OR strField LIKE 'the_' ESCAPE '_' THEN SUBSTR(strArtist, 5)
+  ELSE strField
+  END AS strAlias
+  */
+  std::string SortnameBuildSQL(const std::string& strAlias, const SortAttribute& sortAttributes, 
+    const std::string& strField, const std::string& strSortField);
+
+  /*! \brief Build SQL for sorting field naturally and case insensitvely (in SQLite).
+  \param strField field name
+  \param sortOrder the sort order
+  \return SQL string e.g.   
+  CASE WHEN CAST(strTitle AS INTEGER) = 0 THEN 100000000 
+  ELSE CAST(strTitle AS INTEGER) END DESC, strTitle COLLATE NOCASE DESC
+  */
+  std::string AlphanumericSortSQL(const std::string& strField, const SortOrder& sortOrder);
+
   /*! \brief Checks that source table matches sources.xml
   returns true when they do 
   */