[gnome-music] query: Rework albums query
- From: Carlos Garnacho <carlosg src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [gnome-music] query: Rework albums query
- Date: Sun, 3 Jan 2016 18:26:03 +0000 (UTC)
commit 0d2800445cda3c5afec6728a4493c8d11ba5d7a1
Author: Carlos Garnacho <carlosg gnome org>
Date: Sat Dec 26 23:40:06 2015 +0100
query: Rework albums query
The query can be greatly simplified if done as a song query grouped
by album. The several tracker:uri-is-descendant() can be then done
at once in the main query, and the tracker:uri-is-descendant calls
themselves have been replaced with pure string checks, which will
yield better performance over large sets.
https://bugzilla.gnome.org/show_bug.cgi?id=759587
gnomemusic/query.py | 151 +++++++++------------------------------------------
1 files changed, 26 insertions(+), 125 deletions(-)
---
diff --git a/gnomemusic/query.py b/gnomemusic/query.py
index 09f1dfd..c7b3d7d 100644
--- a/gnomemusic/query.py
+++ b/gnomemusic/query.py
@@ -144,136 +144,37 @@ class Query():
@staticmethod
def albums(where_clause):
query = '''
- SELECT DISTINCT
+ SELECT
rdf:type(?album)
- tracker:id(?album) AS id
- (
- SELECT
- nmm:artistName(?artist)
- WHERE {
- ?album nmm:albumArtist ?artist
- }
- LIMIT 1
- ) AS artist
- nie:title(?album) AS title
- nie:title(?album) AS album
- tracker:coalesce(
- (
- SELECT
- GROUP_CONCAT(
- nmm:artistName(?artist),
- ','
- )
- WHERE {
- ?album nmm:albumArtist ?artist
- }
- ),
- (
- SELECT
- GROUP_CONCAT(
- (
- SELECT
- nmm:artistName(nmm:performer(?_12)) AS perf
- WHERE {
- ?_12 nmm:musicAlbum ?album
- }
- GROUP BY ?perf
- ),
- ','
- ) AS album_performer
- WHERE {
- }
- )
- ) AS author
- xsd:integer(
- tracker:coalesce(
- nmm:albumTrackCount(?album),
- (
- SELECT
- COUNT(?_1)
- WHERE {
- ?_1 nmm:musicAlbum ?album ;
- tracker:available 'true'
- FILTER (
- tracker:uri-is-descendant(
- '%(music_dir)s', nie:url(?_1)
- ) ||
- tracker:uri-is-descendant(
- '%(download_dir)s', nie:url(?_1)
- )
- )
- FILTER (
- NOT EXISTS {
- ?_1 a nmm:Video
- } &&
- NOT EXISTS {
- ?_1 a nmm:Playlist
- }
- )
- }
- )
- )
- ) AS childcount
- (
- SELECT
- fn:year-from-dateTime(?c)
- WHERE {
- ?_2 nmm:musicAlbum ?album ;
- nie:contentCreated ?c ;
- tracker:available 'true'
- FILTER (
- tracker:uri-is-descendant(
- '%(music_dir)s', nie:url(?_2)
- ) ||
- tracker:uri-is-descendant(
- '%(download_dir)s', nie:url(?_2)
- )
- )
- FILTER (
- NOT EXISTS {
- ?_2 a nmm:Video
- } &&
- NOT EXISTS {
- ?_2 a nmm:Playlist
- }
- )
- }
- LIMIT 1
- ) AS creation-date
- {
- %(where_clause)s
- FILTER (
- EXISTS {
- ?_3 nmm:musicAlbum ?album ;
- tracker:available 'true'
- FILTER (
- tracker:uri-is-descendant(
- '%(music_dir)s', nie:url(?_3)
- ) ||
- tracker:uri-is-descendant(
- '%(download_dir)s', nie:url(?_3)
- )
- )
- FILTER (
- NOT EXISTS {
- ?_3 a nmm:Video
- } &&
- NOT EXISTS {
- ?_3 a nmm:Playlist
- }
- )
- }
- )
- }
- ORDER BY %(album_order)s
- %(artist_order)s
- ?albumyear
+ tracker:id(?album) AS ?id
+ nmm:artistName(?albumArtist) AS ?artist
+ ?title
+ tracker:coalesce((SELECT GROUP_CONCAT(nmm:artistName(?albumArtist), ',') { ?album nmm:albumArtist
?albumArtist }),
+ (SELECT GROUP_CONCAT(nmm:artistName(?performer), ',') { ?song nmm:performer
?performer })) AS ?performer
+ COUNT(?song) AS ?childcount
+ YEAR(MAX(nie:contentCreated(?song))) AS ?creation_date
+ {
+ %(where_clause)s
+ ?song a nmm:MusicPiece ;
+ nmm:musicAlbum ?album ;
+ nmm:performer ?performer .
+ ?album nmm:albumArtist ?albumArtist ;
+ nie:title ?title .
+ BIND(LCASE(?title) AS ?title_lower) .
+ BIND(LCASE(nmm:artistName(?albumArtist)) AS ?artist_lower) .
+ BIND((%(album_order)s) AS ?album_collation) .
+ BIND((%(artist_order)s) AS ?artist_collation) .
+ FILTER(STRSTARTS(nie:url(?song), '%(download_dir)s/') ||
+ STRSTARTS(nie:url(?song), '%(music_dir)s/'))
+ }
+ GROUP BY ?album
+ ORDER BY ?album_collation ?artist_collation ?creation_date
'''.replace('\n', ' ').strip() % {
'where_clause': where_clause.replace('\n', ' ').strip(),
'music_dir': Query.MUSIC_URI,
'download_dir': Query.DOWNLOAD_URI,
- 'album_order': Query.order_by_statement("?title"),
- 'artist_order': Query.order_by_statement("?author")
+ 'album_order': Query.order_by_statement("?title_lower"),
+ 'artist_order': Query.order_by_statement("?artist_lower"),
}
return query
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]