From df935fb6bd1fae90bf2cef47c5ba1e8d2515e7c3 Mon Sep 17 00:00:00 2001 From: Jens Georg Date: Mon, 3 Jan 2011 20:53:02 +0100 Subject: [PATCH] media-export: Speed-up get_children and search --- .../rygel-media-export-media-cache-upgrader.vala | 10 ++++++++++ .../media-export/rygel-media-export-media-cache.vala | 1 + .../media-export/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) { -- 2.7.4