[rygel] media-export: Fix degrading database performance

[Zeeshan Ali Khattak <zeeshanak src gnome org>]

commit ea71e550887dfd40f7c501efdb88a0d256bf81c8
Author: Jens Georg <mail jensge org>
Date:   Sat Aug 7 12:40:25 2010 +0300

    media-export: Fix degrading database performance
    
    With items being changed often the performance of the database could
    decrease over time

 .../rygel-media-export-media-cache-upgrader.vala   |   26 +++++++++++++++++++-
 .../rygel-media-export-sql-factory.vala            |    6 ++++-
 2 files changed, 30 insertions(+), 2 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 d3e9183..723198e 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
@@ -106,6 +106,9 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
                     case 7:
                         update_v7_v8 ();
                         break;
+                    case 8:
+                        update_v8_v9 ();
+                        break;
                     default:
                         warning ("Cannot upgrade");
                         database = null;
@@ -249,5 +252,26 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
         }
     }
 
-
+    private void update_v8_v9 () {
+        try {
+            this.database.begin ();
+            this.database.exec ("DROP TRIGGER trgr_update_closure");
+            this.database.exec ("DROP TRIGGER trgr_delete_closure");
+            this.database.exec ("ALTER TABLE Closure RENAME TO _Closure");
+            this.database.exec (this.sql.make (SQLString.TABLE_CLOSURE));
+            this.database.exec ("INSERT INTO Closure (ancestor, " +
+                                "descendant, depth) SELECT DISTINCT " +
+                                "ancestor, descendant, depth FROM " +
+                                "_Closure");
+            this.database.exec (this.sql.make (SQLString.TRIGGER_CLOSURE));
+            this.database.exec ("DROP TABLE _Closure");
+            this.database.exec ("UPDATE schema_info SET version = '9'");
+            this.database.commit ();
+            this.database.exec ("VACUUM");
+        } catch (DatabaseError error) {
+            database.rollback ();
+            warning ("Database upgrade failed: %s", error.message);
+            database = null;
+        }
+    }
 }
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 f076ec1..25eefd4 100644
--- a/src/plugins/media-export/rygel-media-export-sql-factory.vala
+++ b/src/plugins/media-export/rygel-media-export-sql-factory.vala
@@ -152,7 +152,7 @@ internal class Rygel.MediaExport.SQLFactory : Object {
     "SELECT DISTINCT %s FROM meta_data AS m " +
         "WHERE %s IS NOT NULL %s ORDER BY %s LIMIT ?,?";
 
-    internal const string schema_version = "8";
+    internal const string schema_version = "9";
     internal const string CREATE_META_DATA_TABLE_STRING =
     "CREATE TABLE meta_data (size INTEGER NOT NULL, " +
                             "mime_type TEXT NOT NULL, " +
@@ -196,6 +196,10 @@ internal class Rygel.MediaExport.SQLFactory : Object {
     "CREATE TRIGGER trgr_update_closure " +
     "AFTER INSERT ON Object " +
     "FOR EACH ROW BEGIN " +
+        "SELECT RAISE(IGNORE) WHERE (SELECT COUNT(*) FROM Closure " +
+            "WHERE ancestor = NEW.upnp_id " +
+                  "AND descendant = NEW.upnp_id " +
+                  "AND depth = 0) != 0;" +
         "INSERT INTO Closure (ancestor, descendant, depth) " +
             "VALUES (NEW.upnp_id, NEW.upnp_id, 0); " +
         "INSERT INTO Closure (ancestor, descendant, depth) " +