mopidy/mopidy-local-sqlite

DEPRECATED (Mopidy SQLite local library extension)

Mopidy

Mopidy is an extensible music server written in Python.

Mopidy plays music from local disk, Spotify, SoundCloud, Google Play Music, and more. You edit the playlist from any phone, tablet, or computer using a variety of MPD and web clients.

Stream music from the cloud

Vanilla Mopidy only plays music from files and radio streams. Through extensions, Mopidy can play music from cloud services like Spotify, SoundCloud, and Google Play Music. With Mopidy's extension support, backends for new music sources can be easily added.

Mopidy is just a server

Mopidy is a Python application that runs in a terminal or in the background on Linux computers or Macs that have network connectivity and audio output. Out of the box, Mopidy is an HTTP server. If you install the Mopidy-MPD extension, it becomes an MPD server too. Many additional frontends for controlling Mopidy are available as extensions.

Pick your favorite client

You and the people around you can all connect their favorite MPD or web client to the Mopidy server to search for music and manage the playlist together. With a browser or MPD client, which is available for all popular operating systems, you can control the music from any phone, tablet, or computer.

Mopidy on Raspberry Pi

The Raspberry Pi is a popular device to run Mopidy on, either using Raspbian, Ubuntu, or Arch Linux. Pimoroni recommends Mopidy for use with their Pirate Audio audio gear for Raspberry Pi. Mopidy is also a significant building block in the Pi Musicbox integrated audio jukebox system for Raspberry Pi.

Mopidy is hackable

Mopidy's extension support and Python, JSON-RPC, and JavaScript APIs make Mopidy a perfect base for your projects. In one hack, a Raspberry Pi was embedded in an old cassette player. The buttons and volume control are wired up with GPIO on the Raspberry Pi, and are used to control playback through a custom Mopidy extension. The cassettes have NFC tags used to select playlists from Spotify.

Getting started

To get started with Mopidy, begin by reading the installation docs.

Contributing

Begin by reading the contributing section of our documentation. If you are a developer, please also read Development environment and/or Extension development. We welcome all kinds of help with bug fixing, testing, documentation, and supporting other users.

Project resources

• Documentation
• Discourse forum
• Zulip chat
• Source code
• Issue tracker

mopidy-local-sqlite's Issues

crash when mopidy local is disabled

When mopidy-local is not enabled, but mopidy-local-sqlite is installed, it will crash the webserver. Seems like it wants a configuration key media_dir that's not available. Error:

File "/usr/lib/python2.7/threading.py", line 808, in bootstrap_inner
self.run()
File "/usr/local/lib/python2.7/dist-packages/mopidy/http/actor.py", line 103, in run
self.app = tornado.web.Application(self._get_request_handlers())
File "/usr/local/lib/python2.7/dist-packages/mopidy/http/actor.py", line 118, in _get_request_handlers
request_handlers.extend(self._get_app_request_handlers())
File "/usr/local/lib/python2.7/dist-packages/mopidy/http/actor.py", line 136, in _get_app_request_handlers
request_handlers = app['factory'](self.config, self.core)
File "/usr/local/lib/python2.7/dist-packages/mopidy_local_sqlite/http.py", line 26, in factory
images = ImageDirectory(config)
File "/usr/local/lib/python2.7/dist-packages/mopidy_local_sqlite/images.py", line 26, in __init
self._media_dir = config['local']['media_dir']
File "/usr/local/lib/python2.7/dist-packages/mopidy/config/init.py", line 248, in getitem
item = self._data.getitem(key)
KeyError: u'media_dir'

Change max-age to use milliseconds for Mopidy v0.20

See mopidy/mopidy#678.

Probably it's just the browse URIs in the config that need to be changed.

This also means that Mopidy-Local-SQLite must require Mopidy >= 0.20.

Ignore invalid/unsupported search URIs

Mopidy-MusicBox-Webclient passes local: as the uri parameter to Library.search when limiting search to a specific backend. Although technically this is not a valid Mopidy-Local-SQLite URI -- the "root" of the local URI space is Library.ROOT_DIRECTORY_URI, which is local:directory -- raising an "Invalid search URI" exception may be an overly drastic reaction.

In general, it may be preferable to just log a warning for unsupported search URIs and treat them as a root URI otherwise.

'list artists' / 'list albums' are broken

There's a limit of 100 on search results in schema.py.

Unfortunately, the way that mopidy core responds to a 'list artists' command (in mopidy/mpd/music_db.py ) is to retrieve all tracks. This means that if you have more than 100 tracks, calling list artists or albums only returns the artists or albums for the first 100 tracks.

The fix is probably for mopidy to update to some kind of library API that knows about tracks and albums, because the current one is a bit limited. You can also up the limit, although that makes doing these queries slow if you have a lot of artists or albums.

mopidy/mopidy#913

It isn't obvious in pi-musicbox, because that doesn't use list artists ever, and only ever uses the file browser interface, but it is obvious in pretty much any MPD client which supports listing artists or albums (e.g. mpdroid, ncmpcpp).

Move image extraction to separate submodule

It's an optional, experimental feature that uses undocumented Mopidy internals, so it probably shouldn't even be imported unless extract_images = true.

SQL Error Aborts Local Scan

Contrary to expectation, exceptions raised from local.Library.add() are not caught by mopidy local scan, but abort the import process. Therefore, no exceptions should be raised from SQLiteLibrary.add() at all.

Support Untagged Media Files

This extension currently skips tracks that do not have a name attribute; this should be substituted with the file name.

Support Browsing by Composer and Performer

Return list of Track references, no albums.

Improve unit tests

Depends on mopidy/mopidy#837, i.e. Mopidy v0.20.0.

Improve Browsing Performance

The new browsing scheme introduced in v0.7.0 has some negative effects on performance. Especially on embedded plattforms like the Raspberry Pi, there is a noticable lag when browsing artists or composers. This should be investigated.

Reduce Database Locking During Local Scan

When starting Mopidy while a local scan is running, it will most likely fail due to a connection timeout. Rethink transactions/locking/isolation levels, e.g. commit after every insert.

Search URIs use old URI scheme

See comment in library.py:246...

Support Search

'nuff said.

Implement Mopidy v0.20 get_distinct API

mopidy/mopidy#1022

Browse by Album Artist

Consider making"Album Artists" a new top-level directory. The current "Artists" directory should only show track artists, i.e. no "Various Artists", etc.
This would greatly simplify queries and most likely improve performance, too.

Implement Mopidy v0.20 get_images API

If we want to use this for album and artist URIs, too, this can no longer be handled by Mopidy-Local-Images, I suppose.

Set album date from track dates

Since gstreamer only provides a single date per track, all album dates in the database end up empty. Album dates should therefore be set to the date of the respective tracks, iff they all have the same value. This is necessary to account for e.g. career-spanning artist compilations. Therefore, this will probably have to be done in the cleanup step, after a local scan is done.

Artist not found and Album not found when browsing "Show artist" or "Show album"

For some reason when I try to see the Artist page from the context menu on the musicbox's default webclient (from wouter) I get a strange behaviour.

Not only does it say artist not found, but it seems that the angular or single page app is broken - instead of just

http://192.168.0.138/#browse

I now get
http://192.168.0.138/#artists?local:artist:md5:259c6b3c747416f3b5273566559b756c

A little looking and I see that if I go the route of browse->artist the code is:

<a href="#" onclick="return getBrowseDir(this.id);" id="local:artist:md5:259c6b3c747416f3b5273566559b756c" "=""><h1 class="trackname"><i class="fa fa-folder-o"></i> !!!</h1></a>

However in the queue page with the show artist popup, the code is

<a href="#" onclick="showArtist(&quot;local:artist:md5:259c6b3c747416f3b5273566559b756c&quot;);" class="popupArtistHref ui-link-inherit">Show Artist <span class="popupArtistName">!!!</span> </a>

Create an "albums" View

For testing and debugging, an albums view similar to the tracks view would be convenient.
Unless performance measurements recommend otherwise, this should be also be used in the tracks join, instead of the separate joins for album and albumartist.

Browse unscanned media files

Browsing via local:directory:path URIs currently shows all directories under local/media_dir, but returns only files that have been imported via mopidy local scan.

While this is useful for checking for unscanned files, it may be confusing for new users. It may be preferrable to return all files, except those matching local/excluded_file_extensions, with local refs for files already in the database, and plain file refs otherwise. Thus, it would be possible to browse (via "Folders") and play unscanned media files, though searching and browsing via "Artists", "Albums", etc. would still be limited to local files already scanned.

Browse "new" tracks

See mopidy/mopidy#562. Could be implemented based on Track.last_modified, with something like a max_age=<seconds> browse filter.

Support Browsing by Genre, Date, ...

Details TBD.

Support External Album Art

Like "cover.jpg" or "folder.png" in the same directory as the media file. File names configurable, only if no embedded album art is found.

Add config to override search limit

Since Mopidy v0.19 does not pass a limit to Library.search(), all searches will return max. 100 results, as defined in the mopidy.local.Library spec. As a temporary workaround, a new config parameter can be introduced that, if set, overrides the limit parameter. In the default config, this should be set to -1 for now (no limit) to mirror the behavior if the json library.

Logfile

Hi There - I find that sometimes a local scan will render the "browse->local folders" empty - and other times not.

I suspect that there are certain characters in a filename perhaps that are causing this.

Is there any kind of logging so I can see perhaps why some folders end of empty?

Thanks

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)

Improve handling of file system encoding errors

Currently, if a directory path cannot be decoded when browsing "Folders", an exception is raised, and the parent folder will appear to be empty. This is usually the case when mopidy is running with the "POSIX" locale, i.e. an incomplete/erroneous locale setup (such as on Pi MusicBox RC5.1).
It might be better to follow the json library, and explicitly decode directory paths (and probably track names) using sys.getfilesystemencoding() with errors=replace. Note that this will not automagically provide support for Unicode directory paths, but will result in less cryptic behavior (i.e. users who see non-ASCII characters replaced with something funny are more likely to look into locale and filesystem settings).

Empty albums via browsing if albumartist is missing

If an album does not contain an albumartist, it may be reported as empty when browsed via Artists/<artist>/<album>, due to a malformed artist URI.

INFO     2015-03-05 10:06:39,151 [9591:MpdSession-24] mopidy.mpd.session
  New MPD connection from [::ffff:127.0.0.1]:57313
DEBUG    2015-03-05 10:06:39,152 [9591:MpdSession-24] mopidy.mpd.session
  Request from [::ffff:127.0.0.1]:57313: lsinfo "Local media/Artists/Vampire Weekend/Modern Vampires of the City"
DEBUG    2015-03-05 10:06:39,154 [9591:LocalBackend-4] mopidy_local_sqlite.schema
  SQLite browse query []: 
    SELECT 'artist' AS type, uri AS uri, name AS name
      FROM artist
     WHERE EXISTS (
                SELECT * FROM track WHERE track.artists = artist.uri
            ) OR EXISTS (
                SELECT * FROM album WHERE album.artists = artist.uri
            )
     ORDER BY type, name

DEBUG    2015-03-05 10:06:39,155 [9591:LocalBackend-4] mopidy_local_sqlite.schema
  SQLite browse query [u'local:artist:md5:5cb761c4aaf2977354756b081b66374f']: 
    SELECT 'album' AS type, uri AS uri, name AS name
      FROM album
     WHERE artists = ?
     ORDER BY type, name

DEBUG    2015-03-05 10:06:39,155 [9591:LocalBackend-4] mopidy_local_sqlite.schema
  SQLite browse query [u'local:artist:md5:5cb761c4aaf2977354756b081b66374f']: 
    SELECT CASE WHEN album.uri IS NULL THEN 'track' ELSE 'album' END AS type,
           coalesce(album.uri, track.uri) AS uri,
           coalesce(album.name, track.name) AS name
      FROM track LEFT OUTER JOIN album ON track.album = album.uri
     WHERE track.artists = ?
     GROUP BY coalesce(album.uri, track.uri)
     ORDER BY type, name

DEBUG    2015-03-05 10:06:39,156 [9591:LocalBackend-4] mopidy_local_sqlite.schema
  SQLite browse query [u'local:album:md5:b31168f3158e1a7c87c357ec45166138', u'local:directory?album=local:album:md5:cb66bdd94f5a29ffcecfb3501e8a76b1&type=track&artist=local:artist:md5:5cb761c4aaf2977354756b081b66374f']: 
    SELECT 'track' AS type, uri AS uri, name AS name
      FROM track
     WHERE album = ? AND artists = ?
     ORDER BY disc_no, track_no, name

DEBUG    2015-03-05 10:06:39,157 [9591:MpdSession-24] mopidy.mpd.session
  Response to [::ffff:127.0.0.1]:57313: OK
DEBUG    2015-03-05 10:06:39,162 [9591:MpdSession-24] mopidy.utils.network
  Client most likely disconnected.
DEBUG    2015-03-05 10:06:39,163 [9591:MpdSession-24] mopidy.utils.network
  Already stopping: Actor is shutting down.

Apparently, this does not always occur, but can reproduced if two or more albums without albumartist tags exist for a single artist.

Support uris Parameter in Search

If uris contains local album or artist URIs as returned from browsing, search results should be limited to the respective albums/artists.
Note that root directory URIs must also be handled (TBD).

Using Filename as Track Name Misses Encoding

Python 2.7 urllib.unquote() always returns byte strings, so if gstreamer does not report an appropriate title tag, a byte string representation of the file name will be inserted into track.name.

Deprecate foreign_keys config

No problems with SQLite foreign key support enabled, so far, so this should be set for every connection unconditionally.

Return Ref.ARTIST for artists

Depends on mopidy/mopidy#884

Lookup album and artist URIs

Since Library.lookup() can return a list in Mopidy v0.20, it is now possible to handle album and artist URIs, too. Basically a prerequisite for #31.

Perform ANALYZE After a Local Scan

SQLiteLibrary.close() is a real good place to give the query optimizer some hints...

Same album on two locations

I had a situation on MusicBox where an album was both on USB and the Network and hence indexed twice. It found it ok, but in the Albums and Artists sections, the tracks of both albums are merged into one album-entry in the browsing section of mopidy. So I am left with an album with all tracks doubled, one from USB, one from Network. It's ok when I use the Folder structure. Maybe you could split duplicate albums in two?

Support Browsing by Folder

Similar to the JSON local library and popular DLNA clients, support browsing by mirroring the original folder/directory structure.

Refactor ImageDirectory

• change add() parameter to file URI, return (possibly empty) list of image URIs
• do not require local/media_dir (fix workaround for #30)
• make image_dir optional, defaulting to local/sqlite/images

Directory hierarchy for images

Two levels deep, directory name is first two characters of image name, i.e. max. 256 subdirectories.

Improve Documentation

Probably belongs on RTD.

Deprecate "encodings" Config

Since we use mopidy.local.translator anyway for #2, might as well use local_track_uri_to_path() for track names consistent with directory browsing.

Add date index

Oversight; may also clean up the DB schema a little (remove unused views).

Support Images

AFAICS, Mopidy v0.19 provides image URIs from coverartarchive.org, if a musicbrainz_id is present. These should be kept in the database.

Syntax error

2014-09-06 14:17:42,162 ERROR [19856:MpdSession-14] pykka: Unhandled exception in MpdSession (urn:uuid:68200601-f2eb-42b2-b498-7be5da7fb98b):
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 200, in _actor_loop
    response = self._handle_receive(message)
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 303, in _handle_receive
    return self.on_receive(message)
  File "/usr/share/mopidy/mopidy/utils/network.py", line 366, in on_receive
    self.on_line_received(line)
  File "/usr/share/mopidy/mopidy/mpd/session.py", line 33, in on_line_received
    response = self.dispatcher.handle_request(line)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 46, in handle_request
    return self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 75, in _catch_mpd_ack_errors_filter
    return self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 85, in _authenticate_filter
    return self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 104, in _command_list_filter
    response = self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 133, in _idle_filter
    response = self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 146, in _add_ok_filter
    response = self._call_next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 67, in _call_next_filter
    return next_filter(request, response, filter_chain)
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 158, in _call_handler_filter
    response = self._format_response(self._call_handler(request))
  File "/usr/share/mopidy/mopidy/mpd/dispatcher.py", line 167, in _call_handler
    return protocol.commands.call(tokens, context=self.context)
  File "/usr/share/mopidy/mopidy/mpd/protocol/__init__.py", line 178, in call
    return self.handlers[tokens[0]](context, *tokens[1:])
  File "/usr/share/mopidy/mopidy/mpd/protocol/__init__.py", line 140, in validate
    return func(*args, **kwargs)
  File "/usr/share/mopidy/mopidy/mpd/protocol/music_db.py", line 271, in list_
    return _list_artist(context, query)
  File "/usr/share/mopidy/mopidy/mpd/protocol/music_db.py", line 288, in _list_artist
    results = context.core.library.find_exact(**query).get()
  File "/usr/lib/python2.7/dist-packages/pykka/future.py", line 299, in get
    exec('raise exc_info[0], exc_info[1], exc_info[2]')
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 200, in _actor_loop
    response = self._handle_receive(message)
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 294, in _handle_receive
    return callee(*message['args'], **message['kwargs'])
  File "/usr/share/mopidy/mopidy/core/library.py", line 118, in find_exact
    return [result for result in pykka.get_all(futures) if result]
  File "/usr/lib/python2.7/dist-packages/pykka/future.py", line 330, in get_all
    return [future.get(timeout=timeout) for future in futures]
  File "/usr/lib/python2.7/dist-packages/pykka/future.py", line 299, in get
    exec('raise exc_info[0], exc_info[1], exc_info[2]')
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 200, in _actor_loop
    response = self._handle_receive(message)
  File "/usr/lib/python2.7/dist-packages/pykka/actor.py", line 294, in _handle_receive
    return callee(*message['args'], **message['kwargs'])
  File "/usr/share/mopidy/mopidy/local/library.py", line 45, in find_exact
    return self._library.search(query=query, uris=uris, exact=True)
  File "/usr/lib/python2.7/dist-packages/mopidy_local_sqlite/library.py", line 115, in search
    tracks = schema.search_tracks(c, q, limit, offset, exact, filters)
  File "/usr/lib/python2.7/dist-packages/mopidy_local_sqlite/schema.py", line 275, in search_tracks
    result = c.execute(sql + ' LIMIT ? OFFSET ?', params + [limit, offset])
OperationalError: near ")": syntax error

Browsing Artists is Slow

Real slow. Indexing album.artists and track.artist might help, and is planned for #1 anyway...

Provide Index Page for Integrated HTTP Handler

Version 0.6.0 integrates an HTTP handler for accessing local images. Since this shows up in Mopidy's Web clients list, it would be nice to provide some kind of index page when a user clicks on the link, instead of 404: Not Found.

At least, this should contain some explanation that this is just for providing images to Web clients, and there's nothing else to see here. It could also show some statistics, e.g. number of tracks, albums, images, disk space used/free, etc. Browsing extracted images would also be nice.

Exclude album num_tracks and images when generating md5 URIs

Currently, a multi-disc album will generate multiple album rows if the album doesn't have a musicbrainz_id and individual discs have different track counts, due to the way md5 URIs are calculated. According to Mopidy's metadata model, these should be handled as a single album. Note that this will not fix the album's num_tracks, which should probably be set to the total number of tracks, which is difficult to determine if a track's tags only provide the number of tracks for its disc.

Different album tracks may also include different images. Since Mopidy only handles album artwork for now, this will also create multiple album instances, unless the album URI is created from a musicbrainz_id.

Network folders are showing up empty

If a folder includes files or folders with umlauts, the folder is showing up empty in the webbrowser

Wrong track sort oder when browsing non-album URIs

Sort order is ('disc_no', 'track_no', 'name') when browsing album URIs, but defaults to ('type', 'name') when browsing albums via directory URIs, e.g. via Genre -> Album or via Track Artist -> Album.

Refactor URI Scheme/Browsing

With the addition of browsing by composer and performer, the current URI scheme starts looking ridiculous: local:composer:local:artist:.... Also, having a seperate scheme function for each variant starts becoming a maintenance drag, especially when more browsing variants get implement in #5.

Therefore, a general solution is needed, preferrably having a single browse function, or at most one per result type (e.g. browse_artists returning Artist refs, etc.). The URI scheme could use queries or fragments to filter results.

Use mopidy.local.ROOT_DIRECTORY_URI

See mopidy/mopidy#833.
Will have to update dependencies to Mopidy v0.19.4, though.