Database db;
List<string> genres;
+ public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres);
+
public IMDbSqlite (string filename) {
int rc;
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);
-
- string title = strip_year (_title, year);
-
- string sql = "INSERT INTO Movies(ID, Title, Year) VALUES (%lld, \"%s\", %d);".printf ((int64) half_md5, title, year);
+ public int add_movie (string title, int year) {
+ string sql = "INSERT INTO Movies(Title, Year) VALUES (\"%s\", %d);".printf (title, year);
int rc;
rc = db.exec (sql, callback, null);
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 ();
- }
- }
-
- 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);
-
- var sql = "UPDATE Movies SET Rating=%d WHERE ID=%lld;".printf (rating, (int64) half_md5);
+ public int movie_set_rating (string title, int rating, int votes) {
+ var sql = "UPDATE Movies SET Rating=%d, Votes=%d WHERE Title=\"%s\";".printf (rating, votes, title);
int rc;
rc = db.exec (sql, callback, 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;
}
}
- sql = "UPDATE Movies SET Genres=Genres|%d WHERE ID=%lld;".printf (bit, (int64) half_md5);
+ sql = "UPDATE Movies SET Genres=Genres|%d WHERE Title=\"%s\";".printf (bit, title);
rc = db.exec (sql, callback, null);
if (rc != Sqlite.OK) {
stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
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 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);
+ if (rc != Sqlite.OK) {
+ stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+ return 1;
+ }
+
+ return 0;
+ }
+
+ public int create_title_index () {
+ int rc;
+
+ rc = db.exec ("CREATE INDEX MovieTitles ON Movies(Title);", 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, Cancellable? cancellable) {
+ var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
+ var sep = " WHERE ";
+ 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);
+ sep = " AND ";
+ }
+ if (filter.year_min > 0) {
+ sql += sep + "Year >= %d".printf (filter.year_min);
+ sep = " AND ";
+ }
+ if (filter.year_max > 0) {
+ sql += sep + "Year <= %d".printf (filter.year_max);
+ sep = " AND ";
+ }
+ if (filter.rating_min > 0) {
+ 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);
+
+ 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 ());
+ db.progress_handler (0, null);
+ return 1;
+ }
+
+ do {
+ 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);
+ }
+ } while (rc == Sqlite.ROW);
+
+ db.progress_handler (0, null);
+ return 0;
+ }
+
+ 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 ();
+ }
}
projects / cinaest / blobdiff