X-Git-Url: http://vcs.maemo.org/git/?a=blobdiff_plain;f=src%2Fimdb%2Fimdb-sqlite.vala;h=2e13ef20c5ab8dde96add837777d7481502994c6;hb=2c46416f0ee3685871b552d7e6a4511a54aada27;hp=a48a3f54317a0fdf518c1f49badcc770d4ee8937;hpb=cc4e8710d7d92624f6216e5ff7394262d7694458;p=cinaest

diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala
index a48a3f5..2e13ef2 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,10 +126,110 @@ 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 int clear () {
 		int rc;
 
-		rc = db.exec ("DROP TABLE IF EXISTS Movies; CREATE TABLE Movies (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Votes 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)",
+			callback, null);
 		if (rc != Sqlite.OK) {
 			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
 			return 1;
@@ -138,10 +238,10 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
-	public int create_title_index () {
+	public int create_aka_index () {
 		int rc;
 
-		rc = db.exec ("CREATE INDEX MovieTitles ON Movies(Title);", callback, null);
+		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;
@@ -162,17 +262,25 @@ class IMDbSqlite : Object {
 		return 0;
 	}
 
-	public int query (MovieFilter filter, ReceiveMovieFunction receive_movie) {
+	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) {
@@ -184,34 +292,74 @@ 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.field != 0) {
 			sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
 		}
-		sql += " ORDER BY Votes DESC 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) {
 				int year = stmt.column_int (1);
 				string title = stmt.column_text (0);
 				int rating = stmt.column_int (2);
 				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);
 
-		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) {