More query issues

Forum to discuss MonkeyTunes DACP Server for MediaMonkey

More query issues

Postby fanguad » Fri Apr 17, 2015 10:47 am

I've noticed several cases where MonkeyTunes performs differently from iTunes. I will note them here - hopefully fixes for some of these may make it into the next version.

----------------------------------------------------------------------------

The biggest issue is that all queries return a fixed set of content codes regardless what was asked for. My application uses the following query:

http://localhost:port/databases/<dbid>/items?session-id=<sessionid>&meta=dmap.itemid,dmap.itemname,daap.songartist,daap.songalbum,daap.songgrouping,dmap.persistentid,daap.songuserrating,daap.songtime,daap.songtracknumber,daap.songtrackcount&type=music&sort=album&query=<query>

several content codes that I ask for are not returned, plus several that I didn't request
dmap.persistentid
daap.songgrouping
daap.songtrackcount

I can work around dmap.persistentid by using dmap.itemid (in MonkeyTunes, itemid is a persistent value), but the others just can't be retrieved. I also have some queries where I only want the persistenid/itemid, but I'm getting lots of other data back (not a huge deal, but unnecessary).

----------------------------------------------------------------------------

The next issue is that I am unable to get a list of all songs in the entire database.

this returns nothing
http://localhost:port/databases/<dbid>/items?session-id=<sessionid>&meta=dmap.itemid&type=music&sort=album

this returns something, but it's the contents of the "Now Playing" playlist
http://localhost:port/databases/<dbid>/items?session-id=<sessionid>&meta=dmap.itemid&type=music&sort=album&query=''

wildcard searches just keep returning the "Now Playing" playlist
http://localhost:port/databases/<dbid>/items?session-id=<sessionid>&meta=dmap.itemid&type=music&sort=album&query=(('dmap.itemname:%'))

none of these queries cause database queries to be recorded in the debug log
fanguad
 
Posts: 6
Joined: Tue Feb 17, 2015 10:44 am

Re: More query issues

Postby Melloware » Mon Apr 20, 2015 8:48 am

Hey,

That makes sense. All of the stuff Monkeytunes responds to are reverse engineered from Apple Remote so I did not implement every possible API call I only implemented what looked like the Apple Remote requested.

The reason I return the Now Playing instead of "ALL SONGS" is because my MM has 100,000 tracks and returning 100,000 tracks was causing both the Android and IOS remote to choke. So I chose not to have people with large catalogs writing me with bugs about crashing! So whenever I can't figure out a query I return the now playing list from looking at this code...

Code: Select all
string sql = BuildTracksSQL(tracksResponse);
if (String.Empty.Equals(sql)) {
   LOG.Warn("No query created so returning Now Playing list!");
   return GetNowPlaying(request);
}


However the TunesRemoteSE developer needed all songs so he sends in his HTTP Request a HTTP User Agent of TunesRemoteSE and I return the All Songs query.

Code: Select all
bool isFullLibrarySelectAllowed = tracksResponse.HttpRequest.UserAgent.StartsWith("TunesRemoteSE");


So if you set your HTTP User Agent to that you should see All Tracks come back. Let me know if that works?

Also so you have it if you want to review here is the code that builds the dynamic SQL depending on what the incoming HTTP query is.

Code: Select all
/// <summary>
      /// Build the dynamic SQL statement for getting a list of tracks.
      /// </summary>
      /// <param name="tracksResponse">the TracksResponse containing the request data</param>
      /// <returns>a string containing the dynamic SQL</returns>
      private string BuildTracksSQL (TracksResponse tracksResponse) {
         StringBuilder builder = new StringBuilder();
         bool isFullLibrarySelectAllowed = tracksResponse.HttpRequest.UserAgent.StartsWith("TunesRemoteSE");
         //bool isFullLibrarySelectAllowed = true;

         bool useAnd = false;
         string[] utf16 = null;
         // build the where clause
         foreach (string column in tracksResponse.QueryParams.AllKeys) {
            utf16 = tracksResponse.QueryParams.GetValues(column);
            switch (column) {
               case DACPResponse.PROPERTY_ALBUMID:
                  String albumIds = tracksResponse.QueryParams[column];
                  builder.Remove(0, builder.Length);
                  if (albumIds.Contains(",")) {
                     builder.AppendFormat("Songs.IDAlbum IN ({0}) ", albumIds);
                  } else {
                     builder.AppendFormat("Songs.IDAlbum = {0} ", albumIds);
                  }
                  builder.Append(" AND ");
                  useAnd = false;
                  // hate using GOTO but in C# is only way to break out of nested loop
                  goto FINISHED;
               case DACPResponse.PROPERTY_GENRE:
                  if (builder.Length > 4) {
                     // remove the last OR clause make it an AND
                     builder.Remove(builder.Length-4, 4);
                     builder.Append(" AND ");
                  }
                  builder.AppendFormat("Songs.Genre LIKE '%{0}%' ", utf16);
                  useAnd = true;
                  break;
               case DACPResponse.PROPERTY_ARTISTNAME:
               case DACPResponse.PROPERTY_COMPOSER:
                  if (utf16 != null) {
                     CreateFilterSql(builder, "Songs.Artist LIKE '{0}'", utf16);
                     useAnd = false;
                  }

                  break;
               case DACPResponse.PROPERTY_ALBUMNAME:
                  CreateFilterSql(builder, "Songs.Album LIKE '{0}'", utf16);
                  useAnd = false;
                  break;
               case DACPResponse.PROPERTY_ITEMNAME:
                  CreateFilterSql(builder, "Songs.SongTitle LIKE '{0}'", utf16);
                  useAnd = false;
                  break;
               case DACPResponse.PROPERTY_ITEMID:
                  builder.AppendFormat("Songs.ID IN ({0}) ", utf16);
                  useAnd = false;
                  break;
               default:
                  // unknown so just continue
                  continue;
            }

            if (useAnd) {
               builder.Append(" AND ");
            } else {
               builder.Append(" OR ");
            }
         }

      FINISHED:
         LOG.Debug("Finished building Track SELECT");

         if (builder.Length > 4) {
            // remove the last OR clause
            builder.Remove(builder.Length-4, 4);
         } else {
            // no filter so just return no query
            if (isFullLibrarySelectAllowed == true) {
               builder.Append(" 1=1 ");
            } else {
               LOG.Warn("No Track SQL created because client is asking for the entire library!");
               return String.Empty;
            }
         }

         // build the sort order
         string sortOrder = tracksResponse.GetQuerySort();
         switch (sortOrder) {
            case DACPResponse.SORT_ARTIST:
               builder.Append("ORDER BY Songs.Album, Songs.DiscNumber COLLATE NUMERICSTRING, Songs.TrackNumber COLLATE NUMERICSTRING ");
               break;
            case DACPResponse.SORT_ALBUM:
               builder.Append("ORDER BY Songs.Album, Songs.DiscNumber COLLATE NUMERICSTRING, Songs.TrackNumber COLLATE NUMERICSTRING");
               break;
            case DACPResponse.SORT_TRACK:
               builder.Append("ORDER BY Songs.Artist ");
               break;
            default:
               if (isFullLibrarySelectAllowed == true) {
                  builder.Append("ORDER BY Songs.Artist, Songs.Album, Songs.DiscNumber COLLATE NUMERICSTRING, Songs.TrackNumber COLLATE NUMERICSTRING");
               } else {
                  builder.Append("ORDER BY Songs.DiscNumber COLLATE NUMERICSTRING, Songs.TrackNumber COLLATE NUMERICSTRING ");
               }
               break;
         }
         

         // add the limit if a limit was sent in
         if (tracksResponse.IsIndexed) {
            builder.AppendFormat(" LIMIT {0} OFFSET {1} ", tracksResponse.EndIndex, tracksResponse.StartIndex);
         }

         string sql = builder.ToString();
         LOG.InfoFormat("SQL: {0}", sql);
         return sql;
      }
=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Melloware Inc.
http://www.melloware.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-
User avatar
Melloware
Site Admin
 
Posts: 11016
Joined: Thu Dec 01, 2005 10:24 pm
Location: Philadelphia, Pennsylvania USA


Return to MonkeyTunes for MediaMonkey

Who is online

Users browsing this forum: No registered users and 1 guest

cron