[gnome-music] query: Rework albums query

[Carlos Garnacho <carlosg src gnome org>]

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