1 /* This file is part of Cinaest.
3 * Copyright (C) 2009 Philipp Zabel
5 * Cinaest is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
10 * Cinaest is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with Cinaest. If not, see <http://www.gnu.org/licenses/>.
21 class IMDbSqlite : Object {
25 public delegate void ReceiveMovieFunction (string title, string? aka, int year, int rating, int genres);
27 public IMDbSqlite (string filename) {
30 genres = new List<string> ();
32 rc = Database.open (filename, out db);
33 if (rc != Sqlite.OK) {
34 stderr.printf ("Can't open database: %d, %s\n", rc, db.errmsg ());
38 rc = db.exec ("PRAGMA journal_mode = OFF;", callback, null);
39 if (rc != Sqlite.OK) {
40 stderr.printf ("Can't turn off journal mode: %d, %s\n", rc, db.errmsg ());
44 rc = db.exec ("PRAGMA locking_mode = EXCLUSIVE;", callback, null);
45 if (rc != Sqlite.OK) {
46 stderr.printf ("Can't get exclusive lock: %d, %s\n", rc, db.errmsg ());
50 rc = db.exec ("PRAGMA synchronous = OFF;", callback, null);
52 stderr.printf ("Can't turn off synchronous access: %d, %s\n", rc, db.errmsg ());
56 public static int callback (int n_columns, string[] values,
57 string[] column_names) {
58 for (int i = 0; i < n_columns; i++) {
59 stdout.printf ("%s = %s\n", column_names[i], values[i]);
66 public int add_movie (string title, int year) {
67 string sql = "INSERT INTO Movies(Title, Year) VALUES (\"%s\", %d);".printf (title, year);
70 rc = db.exec (sql, callback, null);
71 if (rc != Sqlite.OK) {
72 stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
79 public int movie_set_rating (string title, int rating, int votes) {
80 var sql = "UPDATE Movies SET Rating=%d, Votes=%d WHERE Title=\"%s\";".printf (rating, votes, title);
83 rc = db.exec (sql, callback, null);
84 if (rc != Sqlite.OK) {
85 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
92 public int movie_add_genre (string title, string genre) {
97 bit = genre_bit (genre);
99 genres.append (genre);
100 bit = genre_bit (genre);
102 sql = "INSERT INTO Genres(Bit, Genre) VALUES (%d, \"%s\");".printf (bit, genre);
104 rc = db.exec (sql, callback, null);
105 if (rc != Sqlite.OK) {
106 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
111 sql = "UPDATE Movies SET Genres=Genres|%d WHERE Title=\"%s\";".printf (bit, title);
112 rc = db.exec (sql, callback, null);
113 if (rc != Sqlite.OK) {
114 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
121 int genre_bit (string genre) {
122 for (int i = 0; i < genres.length (); i++) {
123 if (genres.nth_data (i) == genre)
129 public int add_aka (string title, string aka) {
132 if (!movie_exists (title, out rowid))
135 string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (\"%s\", %d);".printf (aka, rowid);
137 rc = db.exec (sql, callback, null);
138 if (rc != Sqlite.OK) {
139 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
146 public int add_plot (string title, string plot, string? author) {
149 if (!movie_exists (title, out rowid))
153 if (author != null) {
154 sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (%d, \"%s\", \"%s\");".printf (rowid, plot.replace ("\"", """), author.replace ("\"", """));
156 sql = "INSERT INTO Plots(rowid, Plot) VALUES (%d, \"%s\");".printf (rowid, plot.replace ("\"", """));
159 rc = db.exec (sql, callback, null);
160 if (rc != Sqlite.OK) {
161 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
162 stderr.printf ("offending SQL: %s\n", sql);
169 public bool movie_exists (string title, out int rowid = null) {
170 string sql = "SELECT rowid FROM Movies WHERE Title=\"%s\"".printf (title);
174 rc = db.prepare_v2 (sql, -1, out stmt);
175 if (rc != Sqlite.OK) {
176 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
182 if (rc == Sqlite.ROW) {
183 if (&rowid != null) {
184 rowid = stmt.column_int (0);
188 } while (rc == Sqlite.ROW);
193 public int count (string table) {
194 string sql = "SELECT count(*) FROM %s".printf (table);
199 rc = db.prepare_v2 (sql, -1, out stmt);
200 if (rc != Sqlite.OK) {
201 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
202 db.progress_handler (0, null);
208 if (rc == Sqlite.ROW) {
209 count = stmt.column_int (0);
211 } while (rc == Sqlite.ROW);
216 public bool has_plots () {
217 return (count ("Plots") > 0);
220 public string get_plot (string title) {
221 string sql = "SELECT Plot FROM Plots WHERE rowid in (SELECT rowid FROM Movies WHERE Title=\"%s\")".printf (title);
226 rc = db.prepare_v2 (sql, -1, out stmt);
227 if (rc != Sqlite.OK) {
228 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
229 db.progress_handler (0, null);
235 if (rc == Sqlite.ROW) {
236 return stmt.column_text (0);
238 } while (rc == Sqlite.ROW);
243 public int clear () {
247 "DROP TABLE IF EXISTS Movies;" +
248 "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);" +
249 "DROP TABLE IF EXISTS Genres;" +
250 "CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);" +
251 "DROP TABLE IF EXISTS Akas;" +
252 "CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);" +
253 "DROP TABLE IF EXISTS Plots;" +
254 "CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT)",
256 if (rc != Sqlite.OK) {
257 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
264 public int create_aka_index () {
267 rc = db.exec ("CREATE INDEX AkasAka ON Akas(Aka);", callback, null);
268 if (rc != Sqlite.OK) {
269 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
276 public int create_votes_index () {
279 rc = db.exec ("CREATE INDEX MovieVotes ON Movies(Votes);", callback, null);
280 if (rc != Sqlite.OK) {
281 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
288 private Cancellable? _cancellable;
289 public async int query (MovieFilter filter, ReceiveMovieFunction receive_movie, int limit, Cancellable? cancellable) {
290 var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
296 // FIXME - how many opcodes until main loop iteration for best responsivity?
297 _cancellable = cancellable;
298 db.progress_handler (1000, progress_handler);
300 if (filter.title != null && filter.title != "") {
301 if ("*" in filter.title) {
302 match = "GLOB \"%s (*)\"".printf (filter.title);
304 match = "LIKE \"%s%%\"".printf (filter.title);
306 sql += sep + "(Title %s OR rowid IN (SELECT TitleID FROM Akas WHERE Aka %s))".printf (match, match);
309 if (filter.year_min > 0) {
310 sql += sep + "Year >= %d".printf (filter.year_min);
313 if (filter.year_max > 0) {
314 sql += sep + "Year <= %d".printf (filter.year_max);
317 if (filter.rating_min > 0) {
318 sql += sep + "Rating >= %d".printf (filter.rating_min);
321 if (filter.genres.field != 0) {
322 sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
324 sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit + 1);
326 stdout.printf("SQL: \"%s\"\n", sql);
328 rc = db.prepare_v2 (sql, -1, out stmt);
329 if (rc != Sqlite.OK) {
330 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
331 db.progress_handler (0, null);
339 Idle.add (query.callback);
342 if (rc == Sqlite.ROW) {
343 int year = stmt.column_int (1);
344 string title = stmt.column_text (0);
345 int rating = stmt.column_int (2);
346 int genres = stmt.column_int (3);
348 if (match != null && !(filter.matches_title (strip_year (title, year)))) {
349 aka = movie_aka (title, match);
351 aka = strip_year (aka, year);
353 receive_movie (strip_year (title, year), aka, year, rating, genres);
355 } while (rc == Sqlite.ROW);
357 db.progress_handler (0, null);
361 private string? movie_aka (string title, string match) {
362 string sql = "SELECT Aka FROM Akas WHERE (TitleID = (SELECT rowid FROM Movies WHERE Title = \"%s\") AND Aka %s) LIMIT 1;".printf (title, match);
367 rc = db.prepare_v2 (sql, -1, out stmt);
368 if (rc != Sqlite.OK) {
369 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
375 if (rc == Sqlite.ROW) {
376 aka = stmt.column_text (0);
378 } while (rc == Sqlite.ROW);
383 private int progress_handler () {
384 ((MainContext) null).iteration (false);
385 return (int) _cancellable.is_cancelled ();
388 private string strip_year (string title, int year) {
389 string year_suffix = " (%d)".printf (year);
390 if (title.has_suffix (year_suffix))
391 return title.substring (0, title.length - year_suffix.length);
392 year_suffix = " (%d/I)".printf (year);
393 if (title.has_suffix (year_suffix))
394 return title.substring (0, title.length - year_suffix.length);
395 year_suffix = " (%d/II)".printf (year);
396 if (title.has_suffix (year_suffix))
397 return title.substring (0, title.length - year_suffix.length);