/* This file is part of Cinaest.
*
* Copyright (C) 2009 Philipp Zabel
*
* Cinaest is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* Cinaest is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with Cinaest. If not, see .
*/
using Sqlite;
class IMDbSqlite : Object {
Database db;
List genres;
public delegate void ReceiveMovieFunction (string title, string? aka, int year, int rating, int genres);
public IMDbSqlite (string filename) {
int rc;
genres = new List ();
rc = Database.open (filename, out db);
if (rc != Sqlite.OK) {
stderr.printf ("Can't open database: %d, %s\n", rc, db.errmsg ());
return;
}
rc = db.exec ("PRAGMA journal_mode = OFF;", callback, null);
if (rc != Sqlite.OK) {
stderr.printf ("Can't turn off journal mode: %d, %s\n", rc, db.errmsg ());
return;
}
rc = db.exec ("PRAGMA locking_mode = EXCLUSIVE;", callback, null);
if (rc != Sqlite.OK) {
stderr.printf ("Can't get exclusive lock: %d, %s\n", rc, db.errmsg ());
return;
}
rc = db.exec ("PRAGMA synchronous = OFF;", callback, null);
if (rc != Sqlite.OK)
stderr.printf ("Can't turn off synchronous access: %d, %s\n", rc, db.errmsg ());
}
public static int callback (int n_columns, string[] values,
string[] column_names) {
for (int i = 0; i < n_columns; i++) {
stdout.printf ("%s = %s\n", column_names[i], values[i]);
}
stdout.printf ("\n");
return 0;
}
Statement add_statement = null;
public int add_movie (string title, int year) {
int rc;
if (add_statement == null) {
string sql = "INSERT INTO Movies(Title, Year) VALUES (?, ?);";
assert (db.prepare_v2 (sql, -1, out add_statement) == Sqlite.OK);
}
assert (add_statement.bind_text (1, title) == Sqlite.OK);
assert (add_statement.bind_int (2, year) == Sqlite.OK);
rc = add_statement.step ();
if (rc != Sqlite.DONE) {
if (rc == Sqlite.CONSTRAINT)
stderr.printf ("Skipped duplicate \"%s\" (%d)\n", title, year);
else
stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
add_statement.reset ();
return 1;
}
add_statement.reset ();
return 0;
}
Statement rating_statement = null;
public int movie_set_rating (string title, int rating, int votes) {
int rc;
if (rating_statement == null) {
var sql = "UPDATE Movies SET Rating=?, Votes=? WHERE Title=?;";
assert (db.prepare_v2 (sql, -1, out rating_statement) == Sqlite.OK);
}
assert (rating_statement.bind_int (1, rating) == Sqlite.OK);
assert (rating_statement.bind_int (2, votes) == Sqlite.OK);
assert (rating_statement.bind_text (3, title) == Sqlite.OK);
rc = rating_statement.step ();
if (rc != Sqlite.DONE) {
stderr.printf ("Failed to set rating %d(%d) for \"%s\": %d, %s\n", rating, votes, title, rc, db.errmsg ());
rating_statement.reset ();
return 1;
}
rating_statement.reset ();
return 0;
}
Statement genre_statement = null;
public int movie_add_genre (string title, string genre) {
string sql;
int bit;
int rc;
bit = genre_bit (genre);
if (bit == 0) {
genres.append (genre);
bit = genre_bit (genre);
sql = "INSERT INTO Genres(Bit, Genre) VALUES (%d, \"%s\");".printf (bit, genre);
rc = db.exec (sql, callback, null);
if (rc != Sqlite.OK) {
stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
return 1;
}
}
if (genre_statement == null) {
sql = "UPDATE Movies SET Genres=Genres|? WHERE Title=?;";
assert (db.prepare_v2 (sql, -1, out genre_statement) == Sqlite.OK);
}
assert (genre_statement.bind_int (1, bit) == Sqlite.OK);
assert (genre_statement.bind_text (2, title) == Sqlite.OK);
rc = genre_statement.step ();
if (rc != Sqlite.DONE) {
stderr.printf ("Failed to add genre %s to \"%s\": %d, %s\n", genre, title, rc, db.errmsg ());
rating_statement.reset ();
return 1;
}
genre_statement.reset ();
return 0;
}
int genre_bit (string genre) {
for (int i = 0; i < genres.length (); i++) {
if (genres.nth_data (i) == genre)
return 1 << i;
}
return 0;
}
Statement aka_statement = null;
public int add_aka (string title, string aka) {
int rc;
int rowid;
if (!movie_exists (title, out rowid))
return 1;
if (aka_statement == null) {
string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (?, ?);";
assert (db.prepare_v2 (sql, -1, out aka_statement) == Sqlite.OK);
}
assert (aka_statement.bind_text (1, aka) == Sqlite.OK);
assert (aka_statement.bind_int (2, rowid) == Sqlite.OK);
rc = aka_statement.step ();
if (rc != Sqlite.DONE) {
stderr.printf ("Failed to add aka \"%s\" to \"%s\": %d, %s\n", aka, title, rc, db.errmsg ());
aka_statement.reset ();
return 1;
}
aka_statement.reset ();
return 0;
}
Statement plot_statement = null;
public int add_plot (string title, string plot, string? author) {
int rc;
int rowid;
if (!movie_exists (title, out rowid))
return 1;
if (plot_statement == null) {
var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
}
assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
assert (plot_statement.bind_text (2, plot.replace ("\"", """)) == Sqlite.OK);
if (author != null)
assert (plot_statement.bind_text (3, author) == Sqlite.OK);
else
assert (plot_statement.bind_null (3) == Sqlite.OK);
rc = plot_statement.step ();
if (rc != Sqlite.DONE) {
stderr.printf ("Failed to add plot for \"%s\": %d, %s\n", title, rc, db.errmsg ());
plot_statement.reset ();
return 1;
}
plot_statement.reset ();
return 0;
}
Statement person_statement = null;
public int add_person (string name) {
int rc;
if (person_statement == null) {
string sql = "INSERT INTO People(Name) VALUES (?);";
rc = db.prepare_v2 (sql, -1, out person_statement);
if (rc != Sqlite.OK) {
error ("Failed to prepare statement \"" + sql + "\": " + db.errmsg ());
}
}
assert (person_statement.bind_text (1, name) == Sqlite.OK);
rc = person_statement.step ();
if (rc != Sqlite.DONE) {
if (rc == Sqlite.CONSTRAINT)
stderr.printf ("Skipped duplicate person \"%s\"\n", name);
else
stderr.printf ("Failed to insert person \"%s\": %d, %s\n", name, rc, db.errmsg ());
person_statement.reset ();
return 1;
}
person_statement.reset ();
return 0;
}
Statement actor_statement;
public int add_actor (string name, string title, string? info, string? character, int number = 0) {
int actorid;
int titleid;
int rc;
if (!person_exists (name, out actorid))
return 1;
if (!movie_exists (title, out titleid))
return 1;
if (actor_statement == null) {
string sql = "INSERT INTO Actors(ActorID, TitleID, Info, Character, Number) VALUES (?, ?, ?, ?, ?);";
assert (db.prepare_v2 (sql, -1, out actor_statement) == Sqlite.OK);
}
assert (actor_statement.bind_int (1, actorid) == Sqlite.OK);
assert (actor_statement.bind_int (2, titleid) == Sqlite.OK);
if (info != null)
assert (actor_statement.bind_text (3, info) == Sqlite.OK);
else
assert (actor_statement.bind_null (3) == Sqlite.OK);
if (character != null)
assert (actor_statement.bind_text (4, character) == Sqlite.OK);
else
assert (actor_statement.bind_null (4) == Sqlite.OK);
assert (actor_statement.bind_int (5, number) == Sqlite.OK);
rc = actor_statement.step ();
if (rc != Sqlite.DONE) {
stderr.printf ("Failed to insert actor \"%s\": %d, %s\n", name, rc, db.errmsg ());
actor_statement.reset ();
return 1;
}
actor_statement.reset ();
return 0;
}
Statement exists_statement = null;
public bool movie_exists (string title, out int rowid = null) {
if (exists_statement == null) {
string sql = "SELECT rowid FROM Movies WHERE Title=?";
assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
}
assert (exists_statement.bind_text (1, title) == Sqlite.OK);
int rc = Sqlite.OK;
do {
rc = exists_statement.step ();
if (rc == Sqlite.ROW) {
if (&rowid != null) {
rowid = exists_statement.column_int (0);
}
exists_statement.reset ();
return true;
}
} while (rc == Sqlite.ROW);
exists_statement.reset ();
return false;
}
Statement person_exists_statement = null;
public bool person_exists (string name, out int rowid = null) {
if (person_exists_statement == null) {
string sql = "SELECT rowid FROM People WHERE Name=?";
assert (db.prepare_v2 (sql, -1, out person_exists_statement) == Sqlite.OK);
}
assert (person_exists_statement.bind_text (1, name) == Sqlite.OK);
int rc = Sqlite.OK;
do {
rc = person_exists_statement.step ();
if (rc == Sqlite.ROW) {
if (&rowid != null) {
rowid = person_exists_statement.column_int (0);
}
person_exists_statement.reset ();
return true;
}
} while (rc == Sqlite.ROW);
person_exists_statement.reset ();
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_table (string table) {
string sql = "SELECT * FROM sqlite_master WHERE type=\"table\" AND name=\"%s\"".printf (table);
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 ());
db.progress_handler (0, null);
return false;
}
do {
rc = stmt.step ();
if (rc == Sqlite.ROW)
return true;
} while (rc == Sqlite.ROW);
return false;
}
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 (""", "\"");
}
} while (rc == Sqlite.ROW);
return "";
}
public List get_cast (string title) {
int rowid;
string sql = "SELECT Name,Character FROM Actors,People WHERE TitleID IN (SELECT rowid FROM Movies WHERE Title=\"%s\") AND ActorID=People.rowid AND Number>0 ORDER BY NUMBER LIMIT 3".printf (title);
Statement stmt;
int rc;
int count = 0;
var cast = new List ();
if (!movie_exists (title, out rowid))
return 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) {
var role = new Role ();
role.actor_name = stmt.column_text (0);
role.character = stmt.column_text (1);
cast.append (role);
}
} while (rc == Sqlite.ROW);
return cast;
}
public int clear () {
int rc;
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);" +
"DROP TABLE IF EXISTS People;" +
"CREATE TABLE People (Name TEXT PRIMARY KEY COLLATE NOCASE);" +
"DROP TABLE IF EXISTS Actors;" +
"CREATE TABLE Actors (ActorID INTEGER NOT NULL, TitleID INTEGER NOT NULL, Info TEXT, Character TEXT, Number INTEGER NOT NULL DEFAULT 0);" +
"CREATE INDEX MovieCast ON Actors(TitleID);" +
"CREATE INDEX ActorFilmography ON Actors(ActorID);",
callback, null);
if (rc != Sqlite.OK) {
stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
return 1;
}
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;
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, 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) {
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) {
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 (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 ());
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;
if (stmt.column_type (2) != Sqlite.NULL)
rating = stmt.column_int (2);
else
rating = -1;
int genres = stmt.column_int (3);
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 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) {
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 ();
}
}