IMDb SQLite database: reuse prepared statements for common operations

The add_movie, movie_set_rating, movie_add_genre, add_aka, add_plot and
movie_exists methods are called a lot during IMDb parsing. Optimize by
keeping the statement objects around.

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index 10065c0..9d3a3d5 100644 (file)
--- a/src/imdb/imdb-sqlite.vala
+++ b/src/imdb/imdb-sqlite.vala
@@ -63,32 +63,57 @@ class IMDbSqlite : Object {
                return 0;
        }
 
+       Statement add_statement = null;
        public int add_movie (string title, int year) {
-               string sql = "INSERT INTO Movies(Title, Year) VALUES (\"%s\", %d);".printf (title, year);
                int rc;
 
-               rc = db.exec (sql, callback, null);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
+               if (add_statement == null) {
+                       string sql = "INSERT INTO Movies(Title, Year) VALUES (?, ?);";
+                       assert (db.prepare_v2 (sql, -1, out add_statement) == Sqlite.OK);
+               }
+
+               assert (add_statement.bind_text (1, title) == Sqlite.OK);
+               assert (add_statement.bind_int (2, year) == Sqlite.OK);
+
+               rc = add_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       if (rc == Sqlite.CONSTRAINT)
+                               stderr.printf ("Skipped duplicate \"%s\" (%d)\n", title, year);
+                       else
+                               stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
+                       add_statement.reset ();
                        return 1;
                }
+               add_statement.reset ();
 
                return 0;
        }
 
+       Statement rating_statement = null;
        public int movie_set_rating (string title, int rating, int votes) {
-               var sql = "UPDATE Movies SET Rating=%d, Votes=%d WHERE Title=\"%s\";".printf (rating, votes, title);
                int rc;
 
-               rc = db.exec (sql, callback, null);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+               if (rating_statement == null) {
+                       var sql = "UPDATE Movies SET Rating=?, Votes=? WHERE Title=?;";
+                       assert (db.prepare_v2 (sql, -1, out rating_statement) == Sqlite.OK);
+               }
+
+               assert (rating_statement.bind_int (1, rating) == Sqlite.OK);
+               assert (rating_statement.bind_int (2, votes) == Sqlite.OK);
+               assert (rating_statement.bind_text (3, title) == Sqlite.OK);
+
+               rc = rating_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       stderr.printf ("Failed to set rating %d(%d) for \"%s\": %d, %s\n", rating, votes, title, rc, db.errmsg ());
+                       rating_statement.reset ();
                        return 1;
                }
+               rating_statement.reset ();
 
                return 0;
        }
 
+       Statement genre_statement = null;
        public int movie_add_genre (string title, string genre) {
                string sql;
                int bit;
@@ -108,12 +133,22 @@ class IMDbSqlite : Object {
                        }
                }
 
-               sql = "UPDATE Movies SET Genres=Genres|%d WHERE Title=\"%s\";".printf (bit, title);
-               rc = db.exec (sql, callback, null);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+               if (genre_statement == null) {
+                       sql = "UPDATE Movies SET Genres=Genres|? WHERE Title=?;";
+                       assert (db.prepare_v2 (sql, -1, out genre_statement) == Sqlite.OK);
+               }
+
+               assert (genre_statement.bind_int (1, bit) == Sqlite.OK);
+               assert (genre_statement.bind_text (2, title) == Sqlite.OK);
+
+
+               rc = genre_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       stderr.printf ("Failed to add genre %s to \"%s\": %d, %s\n", genre, title, rc, db.errmsg ());
+                       rating_statement.reset ();
                        return 1;
                }
+               genre_statement.reset ();
 
                return 0;
        }
@@ -126,66 +161,85 @@ class IMDbSqlite : Object {
                return 0;
        }
 
+       Statement aka_statement = null;
        public int add_aka (string title, string aka) {
+               int rc;
                int rowid;
 
                if (!movie_exists (title, out rowid))
                        return 1;
 
-               string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (\"%s\", %d);".printf (aka, rowid);
-               int rc;
-               rc = db.exec (sql, callback, null);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+               if (aka_statement == null) {
+                       string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (?, ?);";
+                       assert (db.prepare_v2 (sql, -1, out aka_statement) == Sqlite.OK);
+               }
+
+               assert (aka_statement.bind_text (1, aka) == Sqlite.OK);
+               assert (aka_statement.bind_int (2, rowid) == Sqlite.OK);
+               
+               rc = aka_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       stderr.printf ("Failed to add aka \"%s\" to \"%s\": %d, %s\n", aka, title, rc, db.errmsg ());
+                       aka_statement.reset ();
                        return 1;
                }
+               aka_statement.reset ();
 
                return 0;
        }
 
+       Statement plot_statement = null;
        public int add_plot (string title, string plot, string? author) {
+               int rc;
                int rowid;
 
                if (!movie_exists (title, out rowid))
                        return 1;
 
-               string sql;
-               if (author != null) {
-                       sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (%d, \"%s\", \"%s\");".printf (rowid, plot.replace ("\"", """), author.replace ("\"", """));
-               } else {
-                       sql = "INSERT INTO Plots(rowid, Plot) VALUES (%d, \"%s\");".printf (rowid, plot.replace ("\"", """));
+               if (plot_statement == null) {
+                       var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
+                       assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
                }
-               int rc;
-               rc = db.exec (sql, callback, null);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
-                       stderr.printf ("offending SQL: %s\n", sql);
+
+               assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
+               assert (plot_statement.bind_text (2, plot.replace ("\"", """)) == Sqlite.OK);
+               if (author != null)
+                       assert (plot_statement.bind_text (3, author) == Sqlite.OK);
+               else
+                       assert (plot_statement.bind_null (3) == Sqlite.OK);
+
+               rc = plot_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       stderr.printf ("Failed to add plot for \"%s\": %d, %s\n", title, rc, db.errmsg ());
+                       plot_statement.reset ();
                        return 1;
                }
+               plot_statement.reset ();
 
                return 0;
        }
 
+       Statement exists_statement = null;
        public bool movie_exists (string title, out int rowid = null) {
-               string sql = "SELECT rowid FROM Movies WHERE Title=\"%s\"".printf (title);
-               Statement stmt;
-               int rc;
-
-               rc = db.prepare_v2 (sql, -1, out stmt);
-               if (rc != Sqlite.OK) {
-                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
-                       return false;
+               if (exists_statement == null) {
+                       string sql = "SELECT rowid FROM Movies WHERE Title=?";
+                       assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
                }
 
+               assert (exists_statement.bind_text (1, title) == Sqlite.OK);
+
+               int rc = Sqlite.OK;
                do {
-                       rc = stmt.step ();
+                       rc = exists_statement.step ();
                        if (rc == Sqlite.ROW) {
                                if (&rowid != null) {
-                                       rowid = stmt.column_int (0);
+                                       rowid = exists_statement.column_int (0);
                                }
+                               exists_statement.reset ();
                                return true;
                        }
                } while (rc == Sqlite.ROW);
+               exists_statement.reset ();
 
                return false;
        }