X-Git-Url: http://vcs.maemo.org/git/?a=blobdiff_plain;f=src%2Fplugins%2Fcatalog-sqlite.vala;h=509b47aa667a5ecb548dc90c2b9ae4ff5f825dd2;hb=f0bd34d20cfcc467bc6d7d90adcee542f6998089;hp=151fc1cf3f64f8a77264176c799497ce4540d2b6;hpb=82dd8e68130df5a77db9085a848dda9b8b2eaf74;p=cinaest diff --git a/src/plugins/catalog-sqlite.vala b/src/plugins/catalog-sqlite.vala index 151fc1c..509b47a 100644 --- a/src/plugins/catalog-sqlite.vala +++ b/src/plugins/catalog-sqlite.vala @@ -20,6 +20,8 @@ using Sqlite; class CatalogSqlite : Object { Database db; + SList result; + int results_waiting; public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres); @@ -56,10 +58,17 @@ class CatalogSqlite : Object { } public int add_movie (string table, Movie movie) { - string sql = "INSERT INTO %s(Title, Year, Rating, Genres) VALUES (\"%s\", %d, %d, %d);".printf (table, movie.title, movie.year, movie.rating, movie.genres.field); + var sql = new StringBuilder (); int rc; - - rc = db.exec (sql, callback, null); + sql.append_printf ("INSERT INTO %s(Title, Year, Rating, Genres", table); + if (table == "Watched") + sql.append_printf (", Date) VALUES (\"%s\", %d, %d, %d, %u);", + movie.title, movie.year, movie.rating, movie.genres.field, movie.julian_date); + else + sql.append_printf (") VALUES (\"%s\", %d, %d, %d);", + movie.title, movie.year, movie.rating, movie.genres.field); + + rc = db.exec (sql.str, callback, null); if (rc != Sqlite.OK) { stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ()); return 1; @@ -128,14 +137,42 @@ class CatalogSqlite : Object { } private int prepare () { + Statement stmt; int rc; - rc = db.exec ("CREATE TABLE IF NOT EXISTS Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);", callback, null); + rc = db.exec ("CREATE TABLE IF NOT EXISTS Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " + + "CREATE TABLE IF NOT EXISTS Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " + + "CREATE TABLE IF NOT EXISTS Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0, Date INTEGER); " + + "CREATE TABLE IF NOT EXISTS Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);", + callback, null); if (rc != Sqlite.OK) { stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); return 1; } + // Add a date column to the Watched table + rc = db.prepare_v2 ("SELECT sql FROM sqlite_master WHERE (type = 'table' AND name = 'Watched');", + -1, out stmt); + if (rc != Sqlite.OK) { + stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); + return 1; + } + do { + rc = stmt.step (); + if (rc == Sqlite.ROW) { + var sql = stmt.column_text (0); + if (sql == "CREATE TABLE Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0)") { + rc = db.exec ("ALTER TABLE Watched ADD COLUMN Date INTEGER;", + callback, null); + if (rc != Sqlite.OK) { + stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); + return 1; + } + } + break; + } + } while (rc == Sqlite.ROW); + return 0; } @@ -153,7 +190,12 @@ class CatalogSqlite : Object { private Cancellable? _cancellable; public async int query (string table, MovieFilter filter, MovieSource.ReceiveMovieFunction callback, int limit, Cancellable? cancellable) { - var sql = "SELECT Title, Year, Rating, Genres FROM %s".printf (table); + var sql = new StringBuilder (); + sql.append ("SELECT Title, Year, Rating, Genres"); + if (table == "Watched") + sql.append (", Date"); + sql.append (" FROM "); + sql.append (table); var sep = " WHERE "; Statement stmt; int rc; @@ -163,38 +205,48 @@ class CatalogSqlite : 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) { + sql.append (sep); + sql.append_printf ("Title GLOB \"%s\"", filter.title); + } else { + sql.append (sep); + sql.append_printf ("Title LIKE \"%s%%\"", filter.title); + } sep = " AND "; } if (filter.year_min > 0) { - sql += sep + "Year >= %d".printf (filter.year_min); + sql.append (sep); + sql.append_printf ("Year >= %d", filter.year_min); sep = " AND "; } if (filter.year_max > 0) { - sql += sep + "Year <= %d".printf (filter.year_max); + sql.append (sep); + sql.append_printf ("Year <= %d", filter.year_max); sep = " AND "; } if (filter.rating_min > 0) { - sql += sep + "Rating >= %d".printf (filter.rating_min); + sql.append (sep); + sql.append_printf ("Rating >= %d", filter.rating_min); sep = " AND "; } if (filter.genres.field != 0) { - sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field); + sql.append (sep); + sql.append_printf ("Genres&%d = %d", filter.genres.field, filter.genres.field); } - sql += " LIMIT %d;".printf (limit); + sql.append_printf (" LIMIT %d;", limit); - stdout.printf("SQL: \"%s\"\n", sql); + stdout.printf("SQL: \"%s\"\n", sql.str); - rc = db.prepare_v2 (sql, -1, out stmt); + rc = db.prepare_v2 (sql.str, -1, out stmt); if (rc != Sqlite.OK) { stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); db.progress_handler (0, null); return 1; } + result = new SList (); + results_waiting = 0; + do { Idle.add (query.callback); yield; @@ -205,12 +257,23 @@ class CatalogSqlite : Object { movie.title = stmt.column_text (0); movie.rating = stmt.column_int (2); movie.genres.field = stmt.column_int (3); + if (table == "Watched") + movie.julian_date = stmt.column_int (4); // TODO - depending on settings, this could be something else, like director info or runtime movie.secondary = movie.genres.to_string (); - callback (movie); + result.append (movie); + if (++results_waiting >= 10) { + callback (result); + result = new SList (); + results_waiting = 0; + } } } while (rc == Sqlite.ROW); + if (results_waiting > 0) + callback (result); + result = new SList (); + db.progress_handler (0, null); return 0; }