X-Git-Url: http://vcs.maemo.org/git/?a=blobdiff_plain;f=src%2Fimdb%2Fimdb-sqlite.vala;h=10065c06048a652ce1ce844f93b1857739d0349f;hb=93f3b57e52dadc83c5751a66c5fd616aa06eefdd;hp=d26564b56b47075f283eec8a7f8dfe39eda4ef0a;hpb=e5dbe982430db4ef426666e6212cabff29af2306;p=cinaest

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index d26564b..10065c0 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, int genres);
+	public delegate void ReceiveMovieFunction (string title, string? aka, int year, int rating, int genres);
 
 	public IMDbSqlite (string filename) {
 		int rc;
@@ -126,6 +126,120 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
+	public int add_aka (string title, string aka) {
+		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 ());
+			return 1;
+		}
+
+		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 ("\"", "&quot;"), author.replace ("\"", "&quot;"));
+		} else {
+			sql = "INSERT INTO Plots(rowid, Plot) VALUES (%d, \"%s\");".printf (rowid, plot.replace ("\"", "&quot;"));
+		}
+		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;
+		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;
+		}
+
+		do {
+			rc = stmt.step ();
+			if (rc == Sqlite.ROW) {
+				if (&rowid != null) {
+					rowid = stmt.column_int (0);
+				}
+				return true;
+			}
+		} while (rc == Sqlite.ROW);
+
+		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 ("&quot;", "\"");
+			}
+		} while (rc == Sqlite.ROW);
+
+		return "";
+	}
+
 	public int clear () {
 		int rc;
 
@@ -133,7 +247,11 @@ class IMDbSqlite : Object {
 			"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);",
+			"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)",
 			callback, null);
 		if (rc != Sqlite.OK) {
 			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
@@ -143,6 +261,18 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
+	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;
 
@@ -159,6 +289,7 @@ class IMDbSqlite : Object {
 	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;
 
@@ -167,10 +298,12 @@ class IMDbSqlite : Object {
 		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) {
@@ -188,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);
 
@@ -196,24 +329,59 @@ 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);
-				receive_movie (strip_year (title, year), year, rating, genres);
+				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);
 
 		db.progress_handler (0, null);
-		return 0;
+		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 () {