From cab98ef1e4d4501591a618aad30cf8f8202af2a3 Mon Sep 17 00:00:00 2001
From: Philipp Zabel <philipp.zabel@gmail.com>
Date: Fri, 13 Aug 2010 22:15:26 +0200
Subject: [PATCH 1/1] IMDb SQLite database: add people support -
 actors/actresses and roles

---
 src/imdb/imdb-sqlite.vala |  103 ++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 102 insertions(+), 1 deletion(-)

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index 9d3a3d5..11adef8 100644
--- 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 ());
-- 
1.7.9.5