class CatalogSqlite : Object {
Database db;
+ SList<Movie> result;
+ int results_waiting;
public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres);
}
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;
}
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 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;
}
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;
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<Movie> ();
+ results_waiting = 0;
+
do {
Idle.add (query.callback);
yield;
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<Movie> ();
+ results_waiting = 0;
+ }
}
} while (rc == Sqlite.ROW);
+ if (results_waiting > 0)
+ callback (result);
+ result = new SList<Movie> ();
+
db.progress_handler (0, null);
return 0;
}
projects / cinaest / blobdiff