[rygel] media-export: Speed-up get_children and search

[Zeeshan Ali Khattak <zeeshanak src gnome org>]

commit df935fb6bd1fae90bf2cef47c5ba1e8d2515e7c3
Author: Jens Georg <mail jensge org>
Date:   Mon Jan 3 20:53:02 2011 +0100

    media-export: Speed-up get_children and search

 .../rygel-media-export-media-cache-upgrader.vala   |   10 ++++++++++
 .../rygel-media-export-media-cache.vala            |    1 +
 .../rygel-media-export-sql-factory.vala            |   20 +++++++++++++-------
 3 files changed, 24 insertions(+), 7 deletions(-)
---
diff --git a/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala b/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
index bada741..28e6c3b 100644
--- a/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
+++ b/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
@@ -85,6 +85,16 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
         }
     }
 
+    public void ensure_indices () {
+        try {
+            this.database.exec (this.sql.make (SQLString.INDEX_COMMON));
+            this.database.analyze ();
+        } catch (Error error) {
+            warning ("Failed to create indices: " +
+                     error.message);
+        }
+    }
+
     public void upgrade (int old_version) {
         debug ("Older schema detected. Upgrading...");
         int current_version = SQLFactory.schema_version.to_int ();
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 554caa6..7c4b9ba 100644
--- a/src/plugins/media-export/rygel-media-export-media-cache.vala
+++ b/src/plugins/media-export/rygel-media-export-media-cache.vala
@@ -380,6 +380,7 @@ public class Rygel.MediaExport.MediaCache : Object {
                 throw new MediaCacheError.GENERAL_ERROR ("Database format" +
                                                          " not supported");
             }
+            upgrader.ensure_indices ();
         } catch (DatabaseError error) {
             debug ("Could not find schema version;" +
                    " checking for empty database...");
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 72803ed..2a551af 100644
--- a/src/plugins/media-export/rygel-media-export-sql-factory.vala
+++ b/src/plugins/media-export/rygel-media-export-sql-factory.vala
@@ -109,15 +109,18 @@ internal class Rygel.MediaExport.SQLFactory : Object {
      */
     private const string GET_CHILDREN_STRING =
     "SELECT " + ALL_DETAILS_STRING +
-    "FROM Object o LEFT OUTER JOIN meta_data m " +
-        "ON o.upnp_id = m.object_fk " +
-    "WHERE o.parent = ? " +
+    "FROM Object o " +
+        "JOIN Closure c ON (o.upnp_id = c.descendant) " +
+        "LEFT OUTER JOIN meta_data m " +
+        "ON c.descendant = m.object_fk " +
+    "WHERE c.ancestor = ? AND c.depth = 1 " +
         "ORDER BY 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 " +
@@ -222,10 +225,13 @@ internal class Rygel.MediaExport.SQLFactory : Object {
     "END;";
 
     private const string CREATE_INDICES_STRING =
-    "CREATE INDEX idx_parent on Object(parent);" +
-    "CREATE INDEX idx_meta_data_fk on meta_data(object_fk);" +
-    "CREATE INDEX idx_closure on Closure(descendant,depth);" +
-    "CREATE INDEX idx_uri on Object(uri);";
+    "CREATE INDEX IF NOT EXISTS idx_parent on Object(parent);" +
+    "CREATE INDEX IF NOT EXISTS idx_object_upnp_id on Object(upnp_id);" +
+    "CREATE INDEX IF NOT EXISTS idx_meta_data_fk on meta_data(object_fk);" +
+    "CREATE INDEX IF NOT EXISTS idx_closure on Closure(descendant,depth);" +
+    "CREATE INDEX IF NOT EXISTS idx_closure_descendant on Closure(descendant);" +
+    "CREATE INDEX IF NOT EXISTS idx_closure_ancestor on Closure(ancestor);" +
+    "CREATE INDEX IF NOT EXISTS idx_uri on Object(uri);";
 
 
     public unowned string make (SQLString query) {