[rygel] lms: Replace prepared statements with Cursor

[Jens Georg <jensgeorg src gnome org>]

commit 40223c069eb20cc98454c882f6ad6b06245c6986
Author: Jens Georg <mail jensge org>
Date:   Sun Feb 7 16:55:16 2016 +0100

    lms: Replace prepared statements with Cursor
    
    Signed-off-by: Jens Georg <mail jensge org>

 src/librygel-db/database-cursor.vala              |    3 +-
 src/librygel-db/database.vala                     |   12 ---
 src/plugins/lms/rygel-lms-all-videos.vala         |   16 ++--
 src/plugins/lms/rygel-lms-category-container.vala |   85 ++++++++++-----------
 src/plugins/lms/rygel-lms-database.vala           |   68 ----------------
 5 files changed, 51 insertions(+), 133 deletions(-)
---
diff --git a/src/librygel-db/database-cursor.vala b/src/librygel-db/database-cursor.vala
index 617b313..8b717c1 100644
--- a/src/librygel-db/database-cursor.vala
+++ b/src/librygel-db/database-cursor.vala
@@ -101,8 +101,9 @@ public class Rygel.Database.Cursor : Object {
             }
 
             if (this.db.errcode () != Sqlite.OK) {
-                throw new DatabaseError.BIND ("Failed to bind value %d: %s",
+                throw new DatabaseError.BIND ("Failed to bind value %d in %s: %s",
                                               i,
+                                              this.statement.sql (),
                                               this.db.errmsg ());
             }
         }
diff --git a/src/librygel-db/database.vala b/src/librygel-db/database.vala
index cc7bf18..fd6610b 100644
--- a/src/librygel-db/database.vala
+++ b/src/librygel-db/database.vala
@@ -293,16 +293,4 @@ public class Rygel.Database.Database : Object, Initable {
                       error.message);
         }
     }
-
-    public Statement prepare (string sql) throws DatabaseError {
-        Statement statement;
-
-        var err = this.db.prepare_v2 (sql, -1, out statement);
-        if (err != Sqlite.OK) {
-            var msg = "Unable to prepare statement '%s' : %s";
-            throw new DatabaseError.PREPARE (msg, sql, this.db.errmsg ());
-        }
-
-        return statement;
-    }
 }
diff --git a/src/plugins/lms/rygel-lms-all-videos.vala b/src/plugins/lms/rygel-lms-all-videos.vala
index 7f01f85..fd76598 100644
--- a/src/plugins/lms/rygel-lms-all-videos.vala
+++ b/src/plugins/lms/rygel-lms-all-videos.vala
@@ -96,13 +96,15 @@ public class Rygel.LMS.AllVideos : Rygel.LMS.CategoryContainer {
             "from videos, videos_audios, videos_videos where videos.id = ? " +
             "and videos.id = videos_audios.video_id and videos.id = videos_videos.video_id;";
         try {
-            var stmt = this.lms_db.prepare (video_data);
-            Rygel.LMS.Database.find_object ("%d".printf (id), stmt);
-            video.bitrate = stmt.column_int (0) / 8; //convert bits per second into bytes per second
-            video.width = stmt.column_int (1);
-            video.height = stmt.column_int (2);
-            video.channels = stmt.column_int (3);
-            video.sample_freq = stmt.column_int (4);
+            GLib.Value[] args = { id };
+            var cursor = this.lms_db.exec_cursor (video_data, args);
+            foreach (var stmt in cursor) {
+                video.bitrate = stmt.column_int (0) / 8; //convert bits per second into bytes per second
+                video.width = stmt.column_int (1);
+                video.height = stmt.column_int (2);
+                video.channels = stmt.column_int (3);
+                video.sample_freq = stmt.column_int (4);
+            }
         } catch (DatabaseError e) {
             warning ("Query failed: %s", e.message);
         }
diff --git a/src/plugins/lms/rygel-lms-category-container.vala 
b/src/plugins/lms/rygel-lms-category-container.vala
index f208ef2..ff9a24b 100644
--- a/src/plugins/lms/rygel-lms-category-container.vala
+++ b/src/plugins/lms/rygel-lms-category-container.vala
@@ -48,10 +48,10 @@ public abstract class Rygel.LMS.CategoryContainer : Rygel.MediaContainer,
     public string sql_added { get; construct; }
     public string sql_removed { get; construct; }
 
-    protected Statement stmt_all;
-    protected Statement stmt_find_object;
-    protected Statement stmt_added;
-    protected Statement stmt_removed;
+    protected Cursor cursor_all;
+    protected Cursor cursor_find_object;
+    protected Cursor cursor_added;
+    protected Cursor cursor_removed;
 
     protected string child_prefix;
     protected string ref_prefix;
@@ -282,12 +282,12 @@ public abstract class Rygel.LMS.CategoryContainer : Rygel.MediaContainer,
                                         throws Error {
         MediaObjects retval = new MediaObjects ();
 
-        Database.get_children_init (this.stmt_all,
-                                    offset,
-                                    max_count,
-                                    sort_criteria);
-        while (Database.get_children_step (this.stmt_all)) {
-            retval.add (this.object_from_statement (this.stmt_all));
+        // FIXME: sort_criteria is ignored
+        GLib.Value[] args = { max_count, offset };
+
+        this.cursor_all.bind (args);
+        foreach (var stmt in cursor_all) {
+            retval.add (this.object_from_statement (stmt));
         }
 
         return retval;
@@ -312,18 +312,21 @@ public abstract class Rygel.LMS.CategoryContainer : Rygel.MediaContainer,
         }
 
         try {
-            Database.find_object (real_id, this.stmt_find_object);
-            var child = this.object_from_statement (this.stmt_find_object);
-            if (index < 0) {
-                object = child;
-            } else {
-                /* try grandchildren */
-                var container = child as CategoryContainer;
-                object = yield container.find_object (id, cancellable);
-
-                /* tell object to keep a reference to the parent --
-                 * otherwise parent is freed before object is serialized */
-                object.parent_ref = object.parent;
+            GLib.Value[] args = { int.parse (real_id) };
+            this.cursor_find_object.bind (args);
+            foreach (var statement in this.cursor_find_object) {
+                var child = this.object_from_statement (statement);
+                if (index < 0) {
+                    object = child;
+                } else {
+                    /* try grandchildren */
+                    var container = child as CategoryContainer;
+                    object = yield container.find_object (id, cancellable);
+
+                    /* tell object to keep a reference to the parent --
+                     * otherwise parent is freed before object is serialized */
+                    object.parent_ref = object.parent;
+                }
             }
         } catch (DatabaseError e) {
             debug ("find_object %s in %s: %s", id, this.id, e.message);
@@ -349,25 +352,19 @@ public abstract class Rygel.LMS.CategoryContainer : Rygel.MediaContainer,
 
     private void on_db_updated(uint64 old_id, uint64 new_id) {
         try {
-            var stmt_count = this.lms_db.prepare (this.sql_count);
+            this.lms_db.query_value (this.sql_count);
 
-            if (stmt_count.step () == Sqlite.ROW) {
-                this.child_count = stmt_count.column_int (0);
-            }
-
-            Database.get_children_with_update_id_init (this.stmt_added,
-                                                       old_id,
-                                                       new_id);
-            while (Database.get_children_step (this.stmt_added)) {
-                var object = this.object_from_statement (this.stmt_added);
+            GLib.Value[] args = { new_id < old_id ? 0 : old_id,
+                                  new_id };
+            this.cursor_added.bind (args);
+            foreach (var stmt in this.cursor_added) {
+                var object = this.object_from_statement (stmt);
                 this.add_child_tracked.begin (object);
             }
 
-            Database.get_children_with_update_id_init (this.stmt_removed,
-                                                       old_id,
-                                                       new_id);
-            while (Database.get_children_step (this.stmt_removed)) {
-                var object = this.object_from_statement (this.stmt_removed);
+            this.cursor_removed.bind (args);
+            foreach (var stmt in this.cursor_removed) {
+                var object = this.object_from_statement (stmt);
                 this.remove_child_tracked.begin (object);
             }
 
@@ -409,18 +406,16 @@ public abstract class Rygel.LMS.CategoryContainer : Rygel.MediaContainer,
         this.ref_prefix = this.id.slice (0, index) + ":all:";
 
         try {
-            this.stmt_all = this.lms_db.prepare (this.sql_all);
-            this.stmt_find_object = this.lms_db.prepare (this.sql_find_object);
-            var stmt_count = this.lms_db.prepare (this.sql_count);
+            this.cursor_all = this.lms_db.exec_cursor (this.sql_all);
+            this.cursor_find_object = this.lms_db.exec_cursor
+                                        (this.sql_find_object);
 
-            if (stmt_count.step () == Sqlite.ROW) {
-                this.child_count = stmt_count.column_int (0);
-            }
+            this.child_count = this.lms_db.query_value (this.sql_count);
             // some container implementations don't have a reasonable way to provide
             // id-based statements to fetch added or removed items
             if (this.sql_added != null && this.sql_removed != null) {
-                this.stmt_added = this.lms_db.prepare (this.sql_added);
-                this.stmt_removed = this.lms_db.prepare (this.sql_removed);
+                this.cursor_added = this.lms_db.exec_cursor (this.sql_added);
+                this.cursor_removed = this.lms_db.exec_cursor (this.sql_removed);
                 lms_db.db_updated.connect (this.on_db_updated);
             }
         } catch (DatabaseError e) {
diff --git a/src/plugins/lms/rygel-lms-database.vala b/src/plugins/lms/rygel-lms-database.vala
index 70dd7f8..1847e34 100644
--- a/src/plugins/lms/rygel-lms-database.vala
+++ b/src/plugins/lms/rygel-lms-database.vala
@@ -84,72 +84,4 @@ public class Rygel.LMS.Database : Rygel.Database.Database, Initable {
             }
         }
     }
-
-    public static void find_object(string id, Statement stmt) throws DatabaseError {
-
-        (void) stmt.reset();
-
-        int integer_id = int.parse(id);
-        int sqlite_err = stmt.bind_int(1, integer_id);
-        if (sqlite_err != Sqlite.OK)
-            throw new DatabaseError.BIND("Unable to bind id %d", sqlite_err);
-
-        sqlite_err = stmt.step();
-        if (sqlite_err != Sqlite.ROW)
-            throw new DatabaseError.STEP("Unable to find id %s", id);
-    }
-
-    public static void get_children_init (Statement stmt,
-        uint offset, uint max_count, string sort_criteria) throws DatabaseError {
-
-        int sqlite_err;
-
-        (void) stmt.reset();
-
-        sqlite_err = stmt.bind_int(1, (int) max_count);
-        if (sqlite_err != Sqlite.OK)
-            throw new DatabaseError.BIND("Unable to bind max_count %d",
-                                         sqlite_err);
-
-        sqlite_err = stmt.bind_int(2, (int) offset);
-        if (sqlite_err != Sqlite.OK)
-            throw new DatabaseError.BIND("Unable to bind offset %d",
-                                         sqlite_err);
-    }
-
-    public static void get_children_with_update_id_init (Statement stmt,
-        uint64 old_id, uint64 new_id) throws DatabaseError {
-
-        int sqlite_err;
-
-        (void) stmt.reset();
-
-        if (new_id < old_id) // id value wrapped over
-            sqlite_err = stmt.bind_int64(1, 0);
-        else
-            sqlite_err = stmt.bind_int64(1, (int64)old_id);
-        if (sqlite_err != Sqlite.OK)
-            throw new DatabaseError.BIND("Unable to bind old_id %d",
-                                         sqlite_err);
-
-        sqlite_err = stmt.bind_int64(2, (int64)new_id);
-        if (sqlite_err != Sqlite.OK)
-            throw new DatabaseError.BIND("Unable to bind new_id %d",
-                                         sqlite_err);
-    }
-
-    public static bool get_children_step(Statement stmt) throws DatabaseError {
-
-        bool retval;
-        int sqlite_err;
-
-        sqlite_err = stmt.step();
-        retval = sqlite_err == Sqlite.ROW;
-
-        if (!retval && (sqlite_err != Sqlite.DONE))
-            throw new DatabaseError.STEP("Error iterating through rows %d",
-                                         sqlite_err);
-
-        return retval;
-    }
 }