From: Philipp Zabel Date: Fri, 13 Aug 2010 15:46:21 +0000 (+0200) Subject: IMDb SQLite database: reuse prepared statements for common operations X-Git-Url: https://vcs.maemo.org/git/?p=cinaest;a=commitdiff_plain;h=bcf3f2e60c2eb20ddf2de08f508192cc7de363e5 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 --- 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; }