IMDb SQLite class: set rating to -1 to indicate a NULL value in the database

[cinaest] / src / imdb / imdb-sqlite.vala

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index 44b8a98..10065c0 100644 (file)
--- a/src/imdb/imdb-sqlite.vala
+++ b/src/imdb/imdb-sqlite.vala
@@ -143,6 +143,29 @@ class IMDbSqlite : Object {
                return 0;
        }
 
+       public int add_plot (string title, string plot, string? author) {
+               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 ("\"", """));
+               }
+               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);
+                       return 1;
+               }
+
+               return 0;
+       }
+
        public bool movie_exists (string title, out int rowid = null) {
                string sql = "SELECT rowid FROM Movies WHERE Title=\"%s\"".printf (title);
                Statement stmt;
@@ -167,6 +190,56 @@ class IMDbSqlite : Object {
                return false;
        }
 
+       public int count (string table) {
+               string sql = "SELECT count(*) FROM %s".printf (table);
+               Statement stmt;
+               int rc;
+               int count = 0;
+
+               rc = db.prepare_v2 (sql, -1, out stmt);
+               if (rc != Sqlite.OK) {
+                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+                       db.progress_handler (0, null);
+                       return 0;
+               }
+
+               do {
+                       rc = stmt.step ();
+                       if (rc == Sqlite.ROW) {
+                               count = stmt.column_int (0);
+                       }
+               } while (rc == Sqlite.ROW);
+
+               return count;
+       }
+
+       public bool has_plots () {
+               return (count ("Plots") > 0);
+       }
+
+       public string get_plot (string title) {
+               string sql = "SELECT Plot FROM Plots WHERE rowid in (SELECT rowid FROM Movies WHERE Title=\"%s\")".printf (title);
+               Statement stmt;
+               int rc;
+               int count = 0;
+
+               rc = db.prepare_v2 (sql, -1, out stmt);
+               if (rc != Sqlite.OK) {
+                       stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+                       db.progress_handler (0, null);
+                       return "";
+               }
+
+               do {
+                       rc = stmt.step ();
+                       if (rc == Sqlite.ROW) {
+                               return stmt.column_text (0).replace (""", "\"");
+                       }
+               } while (rc == Sqlite.ROW);
+
+               return "";
+       }
+
        public int clear () {
                int rc;
 
@@ -176,7 +249,9 @@ class IMDbSqlite : Object {
                        "DROP TABLE IF EXISTS Genres;" +
                        "CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);" +
                        "DROP TABLE IF EXISTS Akas;" +
-                       "CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);",
+                       "CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);" +
+                       "DROP TABLE IF EXISTS Plots;" +
+                       "CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT)",
                        callback, null);
                if (rc != Sqlite.OK) {
                        stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
@@ -246,7 +321,7 @@ class IMDbSqlite : Object {
                if (filter.genres.field != 0) {
                        sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
                }
-               sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit);
+               sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit + 1);
 
                stdout.printf("SQL: \"%s\"\n", sql);
 
@@ -254,17 +329,24 @@ class IMDbSqlite : Object {
                if (rc != Sqlite.OK) {
                        stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
                        db.progress_handler (0, null);
-                       return 1;
+                       return 0;
                }
 
+               int n = 0;
                do {
+                       if (++n > limit)
+                               break;
                        Idle.add (query.callback);
                        yield;
                        rc = stmt.step ();
                        if (rc == Sqlite.ROW) {
                                int year = stmt.column_int (1);
                                string title = stmt.column_text (0);
-                               int rating = stmt.column_int (2);
+                               int rating;
+                               if (stmt.column_type (2) != Sqlite.NULL)
+                                       rating = stmt.column_int (2);
+                               else
+                                       rating = -1;
                                int genres = stmt.column_int (3);
                                string aka = null;
                                if (match != null && !(filter.matches_title (strip_year (title, year)))) {
@@ -277,10 +359,10 @@ class IMDbSqlite : Object {
                } while (rc == Sqlite.ROW);
 
                db.progress_handler (0, null);
-               return 0;
+               return n;
        }
 
-       private string movie_aka (string title, string match) {
+       private string? movie_aka (string title, string match) {
                string sql = "SELECT Aka FROM Akas WHERE (TitleID = (SELECT rowid FROM Movies WHERE Title = \"%s\") AND Aka %s) LIMIT 1;".printf (title, match);
                Statement stmt;
                int rc;