IMDb SQLite database: add people support - actors/actresses and roles

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index 9d3a3d5..11adef8 100644 (file)
--- a/src/imdb/imdb-sqlite.vala
+++ b/src/imdb/imdb-sqlite.vala
@@ -219,6 +219,75 @@ class IMDbSqlite : Object {
                return 0;
        }
 
+       Statement person_statement = null;
+       public int add_person (string name) {
+               int rc;
+
+               if (person_statement == null) {
+                       string sql = "INSERT INTO People(Name) VALUES (?);";
+                       rc = db.prepare_v2 (sql, -1, out person_statement);
+                       if (rc != Sqlite.OK) {
+                               error ("Failed to prepare statement \"" + sql + "\": " + db.errmsg ());
+                       }
+               }
+
+               assert (person_statement.bind_text (1, name) == Sqlite.OK);
+
+               rc = person_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       if (rc == Sqlite.CONSTRAINT)
+                               stderr.printf ("Skipped duplicate person \"%s\"\n", name);
+                       else
+                               stderr.printf ("Failed to insert person \"%s\": %d, %s\n", name, rc, db.errmsg ());
+                       person_statement.reset ();
+                       return 1;
+               }
+               person_statement.reset ();
+
+               return 0;
+       }
+
+       Statement actor_statement;
+       public int add_actor (string name, string title, string? info, string? character, int number = 0) {
+               int actorid;
+               int titleid;
+               int rc;
+
+               if (!person_exists (name, out actorid))
+                       return 1;
+               if (!movie_exists (title, out titleid))
+                       return 1;
+
+               if (actor_statement == null) {
+                       string sql = "INSERT INTO Actors(ActorID, TitleID, Info, Character, Number) VALUES (?, ?, ?, ?, ?);";
+                       assert (db.prepare_v2 (sql, -1, out actor_statement) == Sqlite.OK);
+               }
+
+               assert (actor_statement.bind_int (1, actorid) == Sqlite.OK);
+               assert (actor_statement.bind_int (2, titleid) == Sqlite.OK);
+               if (info != null)
+                       assert (actor_statement.bind_text (3, info) == Sqlite.OK);
+               else
+                       assert (actor_statement.bind_null (3) == Sqlite.OK);
+
+               if (character != null)
+                       assert (actor_statement.bind_text (4, character) == Sqlite.OK);
+               else
+                       assert (actor_statement.bind_null (4) == Sqlite.OK);
+               assert (actor_statement.bind_int (5, number) == Sqlite.OK);
+
+               rc = actor_statement.step ();
+               if (rc != Sqlite.DONE) {
+                       stderr.printf ("Failed to insert actor \"%s\": %d, %s\n", name, rc, db.errmsg ());
+                       actor_statement.reset ();
+                       return 1;
+               }
+               actor_statement.reset ();
+
+               return 0;
+
+       }
+
        Statement exists_statement = null;
        public bool movie_exists (string title, out int rowid = null) {
                if (exists_statement == null) {
@@ -244,6 +313,32 @@ class IMDbSqlite : Object {
                return false;
        }
 
+       Statement person_exists_statement = null;
+       public bool person_exists (string name, out int rowid = null) {
+               if (person_exists_statement == null) {
+                       string sql = "SELECT rowid FROM People WHERE Name=?";
+                       assert (db.prepare_v2 (sql, -1, out person_exists_statement) == Sqlite.OK);
+               }
+
+               assert (person_exists_statement.bind_text (1, name) == Sqlite.OK);
+
+               int rc = Sqlite.OK;
+               do {
+                       rc = person_exists_statement.step ();
+                       if (rc == Sqlite.ROW) {
+                               if (&rowid != null) {
+                                       rowid = person_exists_statement.column_int (0);
+                               }
+                               person_exists_statement.reset ();
+
+                               return true;
+                       }
+               } while (rc == Sqlite.ROW);
+               person_exists_statement.reset ();
+
+               return false;
+       }
+
        public int count (string table) {
                string sql = "SELECT count(*) FROM %s".printf (table);
                Statement stmt;
@@ -305,7 +400,13 @@ class IMDbSqlite : Object {
                        "DROP TABLE IF EXISTS Akas;" +
                        "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)",
+                       "CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT);" +
+                       "DROP TABLE IF EXISTS People;" +
+                       "CREATE TABLE People (Name TEXT PRIMARY KEY COLLATE NOCASE);" +
+                       "DROP TABLE IF EXISTS Actors;" +
+                       "CREATE TABLE Actors (ActorID INTEGER NOT NULL, TitleID INTEGER NOT NULL, Info TEXT, Character TEXT, Number INTEGER NOT NULL DEFAULT 0);" +
+                       "CREATE INDEX MovieCast ON Actors(TitleID);" +
+                       "CREATE INDEX ActorFilmography ON Actors(ActorID);",
                        callback, null);
                if (rc != Sqlite.OK) {
                        stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());