From 93258df36c8c9f82a07c5f05e052da63a619bc3b Mon Sep 17 00:00:00 2001
From: Jens Georg <mail@jensge.org>
Date: Wed, 30 Sep 2009 17:53:37 +0200
Subject: [PATCH] core: use index on Object.parent

---
 src/rygel/rygel-database.vala |  8 ++++----
 src/rygel/rygel-media-db.vala | 31 ++++++++++++++++++++++++++-----
 2 files changed, 30 insertions(+), 9 deletions(-)

diff --git a/src/rygel/rygel-database.vala b/src/rygel/rygel-database.vala
index 84e6407..01d675e 100644
--- a/src/rygel/rygel-database.vala
+++ b/src/rygel/rygel-database.vala
@@ -142,10 +142,10 @@ internal class Rygel.Database : Object {
         return this.db.changes ();
     }
 
-    /**
-     * Create a GLib.Value containing a null-pointer
-     * This is used to bind a parameter in a statement with bind_null
-     */
+    public void analyze () {
+        this.db.exec ("ANALYZE");
+    }
+
     public GLib.Value get_null () {
         GLib.Value v = GLib.Value (typeof (void *));
         v.set_pointer (null);
diff --git a/src/rygel/rygel-media-db.vala b/src/rygel/rygel-media-db.vala
index 63253a8..69f0300 100644
--- a/src/rygel/rygel-media-db.vala
+++ b/src/rygel/rygel-media-db.vala
@@ -43,13 +43,12 @@ public enum Rygel.MediaDBObjectType {
 public class Rygel.MediaDB : Object {
     private Database db;
     private MediaDBObjectFactory factory;
-    private const string schema_version = "4";
+    private const string schema_version = "5";
     private const string SCHEMA_STRING =
     "CREATE TABLE Schema_Info (version TEXT NOT NULL); " +
     "CREATE TABLE Object_Type (id INTEGER PRIMARY KEY, " +
                               "desc TEXT NOT NULL);" +
-    "CREATE TABLE Meta_Data (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
-                            "size INTEGER NOT NULL, " +
+    "CREATE TABLE Meta_Data (size INTEGER NOT NULL, " +
                             "mime_type TEXT NOT NULL, " +
                             "duration INTEGER, " +
                             "width INTEGER, " +
@@ -103,6 +102,10 @@ public class Rygel.MediaDB : Object {
         "DELETE FROM Uri WHERE Uri.object_fk = OLD.upnp_id;" +
     "END;";
 
+    private const string CREATE_INDICES_STRING =
+    "CREATE INDEX idx_parent on Object(parent);" +
+    "CREATE INDEX idx_uri on Uri(object_fk)";
+
 
     private const string INSERT_META_DATA_STRING =
     "INSERT INTO Meta_Data " +
@@ -207,15 +210,28 @@ public class Rygel.MediaDB : Object {
 
     private void update_v3_v4 () {
         try {
-            GLib.Value[] values = { schema_version };
             db.begin ();
             db.exec (UPDATE_V3_V4_STRING_1);
             db.exec (UPDATE_V3_V4_STRING_2);
             db.exec (UPDATE_V3_V4_STRING_3);
             db.exec (UPDATE_V3_V4_STRING_4);
             db.exec (CREATE_TRIGGER_STRING);
-            db.exec ("UPDATE Schema_Info SET version = ?", values);
+            db.exec ("UPDATE Schema_Info SET version = '4'");
+            db.commit ();
+        } catch (DatabaseError err) {
+            db.rollback ();
+            warning ("Database upgrade failed: %s", err.message);
+            db = null;
+        }
+    }
+
+    private void update_v4_v5 () {
+        try {
+            db.begin ();
+            db.exec (CREATE_INDICES_STRING);
+            db.exec ("UPDATE Schema_Info SET version = '5'");
             db.commit ();
+            db.analyze ();
         } catch (DatabaseError err) {
             db.rollback ();
             warning ("Database upgrade failed: %s", err.message);
@@ -248,6 +264,9 @@ public class Rygel.MediaDB : Object {
                             case 3:
                                 update_v3_v4 ();
                                 break;
+                            case 4:
+                                update_v4_v5 ();
+                                break;
                             default:
                                 warning ("Cannot upgrade");
                                 db = null;
@@ -496,7 +515,9 @@ public class Rygel.MediaDB : Object {
             db.begin ();
             db.exec (SCHEMA_STRING);
             db.exec (CREATE_TRIGGER_STRING);
+            db.exec (CREATE_INDICES_STRING);
             db.commit ();
+            db.analyze ();
             return true;
         } catch (Error err) {
             warning ("Failed to create schema: %s", err.message);
-- 
2.7.4