[rygel] media-export: Speed up virtual folder browsing

[Jens Georg <jensgeorg src gnome org>]

commit 62edc4da510703b91ce1668a925e8801484426e5
Author: Jens Georg <mail jensge org>
Date:   Wed Jun 13 20:42:05 2012 +0200

    media-export: Speed up virtual folder browsing
    
    Get rid of the unnecessary join on the closure table; the virtual
    folders don't need to check for the ancestor.

 .../rygel-media-export-leaf-query-container.vala   |    3 +-
 .../rygel-media-export-media-cache.vala            |   31 ++++++++++++++------
 .../rygel-media-export-query-container.vala        |    2 +-
 .../rygel-media-export-sql-factory.vala            |   24 ++++++++++++++-
 4 files changed, 46 insertions(+), 14 deletions(-)
---
diff --git a/src/plugins/media-export/rygel-media-export-leaf-query-container.vala b/src/plugins/media-export/rygel-media-export-leaf-query-container.vala
index e82021d..f363290 100644
--- a/src/plugins/media-export/rygel-media-export-leaf-query-container.vala
+++ b/src/plugins/media-export/rygel-media-export-leaf-query-container.vala
@@ -45,7 +45,6 @@ internal class Rygel.MediaExport.LeafQueryContainer : QueryContainer {
 
     protected override int count_children () throws Error {
         return (int) this.media_db.get_object_count_by_search_expression
-                                        (this.expression,
-                                         RootContainer.FILESYSTEM_FOLDER_ID);
+                                        (this.expression, null);
     }
 }
diff --git a/src/plugins/media-export/rygel-media-export-media-cache.vala b/src/plugins/media-export/rygel-media-export-media-cache.vala
index 57e0979..43395c5 100644
--- a/src/plugins/media-export/rygel-media-export-media-cache.vala
+++ b/src/plugins/media-export/rygel-media-export-media-cache.vala
@@ -202,7 +202,7 @@ public class Rygel.MediaExport.MediaCache : Object {
 
     public MediaObjects get_objects_by_search_expression
                                         (SearchExpression? expression,
-                                         string            container_id,
+                                         string?           container_id,
                                          uint              offset,
                                          uint              max_count,
                                          out uint          total_matches)
@@ -233,7 +233,7 @@ public class Rygel.MediaExport.MediaCache : Object {
 
     public long get_object_count_by_search_expression
                                         (SearchExpression? expression,
-                                         string            container_id)
+                                         string?           container_id)
                                          throws Error {
         var args = new GLib.ValueArray (0);
         var filter = this.translate_search_expression (expression, args);
@@ -255,14 +255,22 @@ public class Rygel.MediaExport.MediaCache : Object {
     public long get_object_count_by_filter
                                         (string          filter,
                                          GLib.ValueArray args,
-                                         string          container_id)
+                                         string?         container_id)
                                          throws Error {
-        GLib.Value v = container_id;
-        args.prepend (v);
+        if (container_id != null) {
+            GLib.Value v = container_id;
+            args.prepend (v);
+        }
 
         debug ("Parameters to bind: %u", args.n_values);
-        unowned string pattern = this.sql.make
-                                        (SQLString.GET_OBJECT_COUNT_BY_FILTER);
+        unowned string pattern;
+        SQLString string_id;
+        if (container_id != null) {
+            string_id = SQLString.GET_OBJECT_COUNT_BY_FILTER_WITH_ANCESTOR;
+        } else {
+            string_id = SQLString.GET_OBJECT_COUNT_BY_FILTER;
+        }
+        pattern = this.sql.make (string_id);
 
         return this.db.query_value (pattern.printf (filter), args.values);
     }
@@ -270,7 +278,7 @@ public class Rygel.MediaExport.MediaCache : Object {
 
     public MediaObjects get_objects_by_filter (string          filter,
                                                GLib.ValueArray args,
-                                               string          container_id,
+                                               string?         container_id,
                                                long            offset,
                                                long            max_count)
                                                throws Error {
@@ -283,7 +291,12 @@ public class Rygel.MediaExport.MediaCache : Object {
 
         debug ("Parameters to bind: %u", args.n_values);
 
-        unowned string sql = this.sql.make (SQLString.GET_OBJECTS_BY_FILTER);
+        unowned string sql;
+        if (container_id != null) {
+            sql = this.sql.make (SQLString.GET_OBJECTS_BY_FILTER_WITH_ANCESTOR);
+        } else {
+            sql = this.sql.make (SQLString.GET_OBJECTS_BY_FILTER);
+        }
         var cursor = this.db.exec_cursor (sql.printf (filter), args.values);
         foreach (var statement in cursor) {
             unowned string parent_id = statement.column_text (DetailColumn.PARENT);
diff --git a/src/plugins/media-export/rygel-media-export-query-container.vala b/src/plugins/media-export/rygel-media-export-query-container.vala
index 2e75a8b..3800c62 100644
--- a/src/plugins/media-export/rygel-media-export-query-container.vala
+++ b/src/plugins/media-export/rygel-media-export-query-container.vala
@@ -68,7 +68,7 @@ internal abstract class Rygel.MediaExport.QueryContainer : DBContainer {
         try {
             children = this.media_db.get_objects_by_search_expression
                                         (combined_expression,
-                                         RootContainer.FILESYSTEM_FOLDER_ID,
+                                         null,
                                          offset,
                                          max_count,
                                          out total_matches);
diff --git a/src/plugins/media-export/rygel-media-export-sql-factory.vala b/src/plugins/media-export/rygel-media-export-sql-factory.vala
index 5991c91..899c031 100644
--- a/src/plugins/media-export/rygel-media-export-sql-factory.vala
+++ b/src/plugins/media-export/rygel-media-export-sql-factory.vala
@@ -53,7 +53,9 @@ internal enum Rygel.MediaExport.SQLString {
     GET_OBJECT,
     GET_CHILDREN,
     GET_OBJECTS_BY_FILTER,
+    GET_OBJECTS_BY_FILTER_WITH_ANCESTOR,
     GET_OBJECT_COUNT_BY_FILTER,
+    GET_OBJECT_COUNT_BY_FILTER_WITH_ANCESTOR,
     GET_META_DATA_COLUMN,
     CHILD_COUNT,
     EXISTS,
@@ -122,11 +124,22 @@ internal class Rygel.MediaExport.SQLFactory : Object {
                  "o.title ASC " +
     "LIMIT ?,?";
 
+    private const string GET_OBJECTS_BY_FILTER_STRING_WITH_ANCESTOR =
+    "SELECT DISTINCT " + ALL_DETAILS_STRING +
+    "FROM Object o " +
+        "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
+        "LEFT OUTER JOIN meta_data m " +
+            "ON o.upnp_id = m.object_fk %s" +
+        "ORDER BY o.parent ASC, " +
+                 "o.type_fk ASC, " +
+                 "m.class ASC, " +
+                 "m.track ASC, " +
+                 "o.title ASC " +
+    "LIMIT ?,?";
 
     private const string GET_OBJECTS_BY_FILTER_STRING =
     "SELECT DISTINCT " + ALL_DETAILS_STRING +
     "FROM Object o " +
-        "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
         "LEFT OUTER JOIN meta_data m " +
             "ON o.upnp_id = m.object_fk %s" +
         "ORDER BY o.parent ASC, " +
@@ -136,12 +149,15 @@ internal class Rygel.MediaExport.SQLFactory : Object {
                  "o.title ASC " +
     "LIMIT ?,?";
 
-    private const string GET_OBJECT_COUNT_BY_FILTER_STRING =
+    private const string GET_OBJECT_COUNT_BY_FILTER_STRING_WITH_ANCESTOR =
     "SELECT COUNT(o.type_fk) FROM Object o " +
         "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
         "LEFT OUTER JOIN meta_data m " +
             "ON o.upnp_id = m.object_fk %s";
 
+    private const string GET_OBJECT_COUNT_BY_FILTER_STRING =
+    "SELECT COUNT(1) FROM meta_data m %s";
+
     private const string CHILDREN_COUNT_STRING =
     "SELECT COUNT(upnp_id) FROM Object WHERE Object.parent = ?";
 
@@ -263,8 +279,12 @@ internal class Rygel.MediaExport.SQLFactory : Object {
                 return GET_CHILDREN_STRING;
             case SQLString.GET_OBJECTS_BY_FILTER:
                 return GET_OBJECTS_BY_FILTER_STRING;
+            case SQLString.GET_OBJECTS_BY_FILTER_WITH_ANCESTOR:
+                return GET_OBJECTS_BY_FILTER_STRING_WITH_ANCESTOR;
             case SQLString.GET_OBJECT_COUNT_BY_FILTER:
                 return GET_OBJECT_COUNT_BY_FILTER_STRING;
+            case SQLString.GET_OBJECT_COUNT_BY_FILTER_WITH_ANCESTOR:
+                return GET_OBJECT_COUNT_BY_FILTER_STRING_WITH_ANCESTOR;
             case SQLString.GET_META_DATA_COLUMN:
                 return GET_META_DATA_COLUMN_STRING;
             case SQLString.CHILD_COUNT: