X-Git-Url: http://vcs.maemo.org/git/?a=blobdiff_plain;f=src%2Fimdb%2Fimdb-sqlite.vala;h=ff9edcbf91de4398bcc5635bb2b147ab7f634fd1;hb=e0d6417e36aa8a6c6c705e186ffa81ab3ca2d141;hp=26e21fe352e3e50c04511158a7f38c0f2b260b7b;hpb=2837467371907308a365efca7f893d0735d32343;p=cinaest

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index 26e21fe..ff9edcb 100644
--- a/src/imdb/imdb-sqlite.vala
+++ b/src/imdb/imdb-sqlite.vala
@@ -22,7 +22,7 @@ class IMDbSqlite : Object {
 	Database db;
 	List<string> genres;
 
-	public delegate void ReceiveMovieFunction (string title, int year, int rating);
+	public delegate void ReceiveMovieFunction (string title, string? aka, int year, int rating, int genres);
 
 	public IMDbSqlite (string filename) {
 		int rc;
@@ -63,52 +63,58 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
-	public int add_movie (string _title, int year) {
-		string md5 = Checksum.compute_for_string (ChecksumType.MD5, _title);
-		uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
+	Statement add_statement = null;
+	public int add_movie (string title, int year) {
+		int rc;
 
-		string title = strip_year (_title, year);
+		if (add_statement == null) {
+			string sql = "INSERT INTO Movies(Title, Year) VALUES (?, ?);";
+			assert (db.prepare_v2 (sql, -1, out add_statement) == Sqlite.OK);
+		}
 
-		string sql = "INSERT INTO Movies(ID, Title, Year) VALUES (%lld, \"%s\", %d);".printf ((int64) half_md5, title, year);
-		int rc;
+		assert (add_statement.bind_text (1, title) == Sqlite.OK);
+		assert (add_statement.bind_int (2, year) == Sqlite.OK);
 
-		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 ());
+		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;
 	}
 
-	private string strip_year (string title, int year) {
-		string year_suffix = " (%d)".printf (year);
-		if (title.has_suffix (year_suffix)) {
-			return title.substring (0, title.length - year_suffix.length);
-		} else {
-			return title.dup ();
-		}
-	}
+	Statement rating_statement = null;
+	public int movie_set_rating (string title, int rating, int votes) {
+		int rc;
 
-	public int movie_set_rating (string title, int rating) {
-		string md5 = Checksum.compute_for_string (ChecksumType.MD5, title);
-		uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
+		if (rating_statement == null) {
+			var sql = "UPDATE Movies SET Rating=?, Votes=? WHERE Title=?;";
+			assert (db.prepare_v2 (sql, -1, out rating_statement) == Sqlite.OK);
+		}
 
-		var sql = "UPDATE Movies SET Rating=%d WHERE ID=%lld;".printf (rating, (int64) half_md5);
-		int rc;
+		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 = db.exec (sql, callback, null);
-		if (rc != Sqlite.OK) {
-			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+		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 md5 = Checksum.compute_for_string (ChecksumType.MD5, title);
-		uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
 		string sql;
 		int bit;
 		int rc;
@@ -127,12 +133,22 @@ class IMDbSqlite : Object {
 			}
 		}
 
-		sql = "UPDATE Movies SET Genres=Genres|%d WHERE ID=%lld;".printf (bit, (int64) half_md5);
-		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;
 	}
@@ -145,10 +161,300 @@ 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;
+
+		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;
+
+		if (plot_statement == null) {
+			var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
+			assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
+		}
+
+		assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
+		assert (plot_statement.bind_text (2, plot.replace ("\"", "&quot;")) == 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 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) {
+			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 = exists_statement.step ();
+			if (rc == Sqlite.ROW) {
+				if (&rowid != null) {
+					rowid = exists_statement.column_int (0);
+				}
+				exists_statement.reset ();
+				return true;
+			}
+		} while (rc == Sqlite.ROW);
+		exists_statement.reset ();
+
+		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;
+		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_table (string table) {
+		string sql = "SELECT * FROM sqlite_master WHERE type=\"table\" AND name=\"%s\"".printf (table);
+		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 ());
+			db.progress_handler (0, null);
+			return false;
+		}
+
+		do {
+			rc = stmt.step ();
+			if (rc == Sqlite.ROW)
+				return true;
+		} while (rc == Sqlite.ROW);
+
+		return false;
+	}
+
+	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 ("&quot;", "\"");
+			}
+		} while (rc == Sqlite.ROW);
+
+		return "";
+	}
+
+	public List<Role> get_cast (string title) {
+		int rowid;
+		string sql = "SELECT Name,Character FROM Actors,People WHERE TitleID IN (SELECT rowid FROM Movies WHERE Title=\"%s\") AND ActorID=People.rowid AND Number>0 ORDER BY NUMBER LIMIT 3".printf (title);
+		Statement stmt;
+		int rc;
+		int count = 0;
+		var cast = new List<Role> ();
+
+		if (!movie_exists (title, out rowid))
+			return null;
+
+		rc = db.prepare_v2 (sql, -1, out stmt);
+		if (rc != Sqlite.OK) {
+			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+			return null;
+		}
+
+		do {
+			rc = stmt.step ();
+			if (rc == Sqlite.ROW) {
+				var role = new Role ();
+				role.actor_name = stmt.column_text (0);
+				role.character = stmt.column_text (1);
+				cast.append (role);
+			}
+		} while (rc == Sqlite.ROW);
+
+		return cast;
+	}
+
 	public int clear () {
 		int rc;
 
-		rc = db.exec ("DROP TABLE IF EXISTS Movies; CREATE TABLE Movies (ID INTEGER PRIMARY KEY, Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Genres; CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);", callback, null);
+		rc = db.exec (
+			"DROP TABLE IF EXISTS Movies;" +
+			"CREATE TABLE Movies (Title TEXT PRIMARY KEY COLLATE NOCASE, Year INTEGER, Rating INTEGER, Votes INTEGER NOT NULL DEFAULT 0, Genres INTEGER NOT NULL DEFAULT 0);" +
+			"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);" +
+			"DROP TABLE IF EXISTS Plots;" +
+			"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 ());
 			return 1;
@@ -157,17 +463,49 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
-	public int query (MovieFilter filter, ReceiveMovieFunction receive_movie) {
-		var sql = "SELECT Title, Year, Rating FROM Movies";
+	public int create_aka_index () {
+		int rc;
+
+		rc = db.exec ("CREATE INDEX AkasAka ON Akas(Aka);", callback, null);
+		if (rc != Sqlite.OK) {
+			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+			return 1;
+		}
+
+		return 0;
+	}
+
+	public int create_votes_index () {
+		int rc;
+
+		rc = db.exec ("CREATE INDEX MovieVotes ON Movies(Votes);", callback, null);
+		if (rc != Sqlite.OK) {
+			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+			return 1;
+		}
+
+		return 0;
+	}
+
+	private Cancellable? _cancellable;
+	public async int query (MovieFilter filter, ReceiveMovieFunction receive_movie, int limit, Cancellable? cancellable) {
+		var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
 		var sep = " WHERE ";
+		string match = null;
 		Statement stmt;
 		int rc;
 
+		// FIXME - how many opcodes until main loop iteration for best responsivity?
+		_cancellable = cancellable;
+		db.progress_handler (1000, progress_handler);
+
 		if (filter.title != null && filter.title != "") {
-			if ("*" in filter.title)
-				sql += sep + "Title GLOB \"%s\"".printf (filter.title);
-			else
-				sql += sep + "Title LIKE \"%s%%\"".printf (filter.title);
+			if ("*" in filter.title) {
+				match = "GLOB \"%s (*)\"".printf (filter.title);
+			} else {
+				match = "LIKE \"%s%%\"".printf (filter.title);
+			}
+			sql += sep + "(Title %s OR rowid IN (SELECT TitleID FROM Akas WHERE Aka %s))".printf (match, match);
 			sep = " AND ";
 		}
 		if (filter.year_min > 0) {
@@ -179,32 +517,90 @@ class IMDbSqlite : Object {
 			sep = " AND ";
 		}
 		if (filter.rating_min > 0) {
-			sql += sep + "Rating >= = %d".printf (filter.rating_min);
+			sql += sep + "Rating >= %d".printf (filter.rating_min);
 			sep = " AND ";
 		}
-		if (filter.genres > 0) {
-			sql += sep + "Genres&%d = %d".printf (filter.genres, filter.genres);
+		if (filter.genres.field != 0) {
+			sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
 		}
-		sql += " LIMIT %d;".printf (100);
+		sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit + 1);
 
 		stdout.printf("SQL: \"%s\"\n", sql);
 
 		rc = db.prepare_v2 (sql, -1, out stmt);
 		if (rc != Sqlite.OK) {
 			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
-			return 1;
+			db.progress_handler (0, null);
+			return 0;
 		}
 
+		int n = 0;
 		do {
+			if (++n > limit)
+				break;
+			Idle.add (query.callback);
+			yield;
 			rc = stmt.step ();
 			if (rc == Sqlite.ROW) {
-				string title = stmt.column_text (0);
 				int year = stmt.column_int (1);
-				int rating = stmt.column_int (2);
-				receive_movie (title, year, rating);
+				string title = stmt.column_text (0);
+				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)))) {
+					aka = movie_aka (title, match);
+					if (aka != null)
+						aka = strip_year (aka, year);
+				}
+				receive_movie (strip_year (title, year), aka, year, rating, genres);
 			}
 		} while (rc == Sqlite.ROW);
 
-		return 0;
+		db.progress_handler (0, null);
+		return n;
+	}
+
+	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;
+		string aka = null;
+
+		rc = db.prepare_v2 (sql, -1, out stmt);
+		if (rc != Sqlite.OK) {
+			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+			return null;
+		}
+
+		do {
+			rc = stmt.step ();
+			if (rc == Sqlite.ROW) {
+				aka = stmt.column_text (0);
+			}
+		} while (rc == Sqlite.ROW);
+
+		return aka;
+	}
+
+	private int progress_handler () {
+		((MainContext) null).iteration (false);
+		return (int) _cancellable.is_cancelled ();
+	}
+
+	private string strip_year (string title, int year) {
+		string year_suffix = " (%d)".printf (year);
+		if (title.has_suffix (year_suffix))
+			return title.substring (0, title.length - year_suffix.length);
+		year_suffix = " (%d/I)".printf (year);
+		if (title.has_suffix (year_suffix))
+			return title.substring (0, title.length - year_suffix.length);
+		year_suffix = " (%d/II)".printf (year);
+		if (title.has_suffix (year_suffix))
+			return title.substring (0, title.length - year_suffix.length);
+		return title.dup ();
 	}
 }