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 Statement add_statement = null;
67 public int add_movie (string title, int year) {
70 if (add_statement == null) {
71 string sql = "INSERT INTO Movies(Title, Year) VALUES (?, ?);";
72 assert (db.prepare_v2 (sql, -1, out add_statement) == Sqlite.OK);
75 assert (add_statement.bind_text (1, title) == Sqlite.OK);
76 assert (add_statement.bind_int (2, year) == Sqlite.OK);
78 rc = add_statement.step ();
79 if (rc != Sqlite.DONE) {
80 if (rc == Sqlite.CONSTRAINT)
81 stderr.printf ("Skipped duplicate \"%s\" (%d)\n", title, year);
83 stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
84 add_statement.reset ();
87 add_statement.reset ();
92 Statement rating_statement = null;
93 public int movie_set_rating (string title, int rating, int votes) {
96 if (rating_statement == null) {
97 var sql = "UPDATE Movies SET Rating=?, Votes=? WHERE Title=?;";
98 assert (db.prepare_v2 (sql, -1, out rating_statement) == Sqlite.OK);
101 assert (rating_statement.bind_int (1, rating) == Sqlite.OK);
102 assert (rating_statement.bind_int (2, votes) == Sqlite.OK);
103 assert (rating_statement.bind_text (3, title) == Sqlite.OK);
105 rc = rating_statement.step ();
106 if (rc != Sqlite.DONE) {
107 stderr.printf ("Failed to set rating %d(%d) for \"%s\": %d, %s\n", rating, votes, title, rc, db.errmsg ());
108 rating_statement.reset ();
111 rating_statement.reset ();
116 Statement genre_statement = null;
117 public int movie_add_genre (string title, string genre) {
122 bit = genre_bit (genre);
124 genres.append (genre);
125 bit = genre_bit (genre);
127 sql = "INSERT INTO Genres(Bit, Genre) VALUES (%d, \"%s\");".printf (bit, genre);
129 rc = db.exec (sql, callback, null);
130 if (rc != Sqlite.OK) {
131 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
136 if (genre_statement == null) {
137 sql = "UPDATE Movies SET Genres=Genres|? WHERE Title=?;";
138 assert (db.prepare_v2 (sql, -1, out genre_statement) == Sqlite.OK);
141 assert (genre_statement.bind_int (1, bit) == Sqlite.OK);
142 assert (genre_statement.bind_text (2, title) == Sqlite.OK);
145 rc = genre_statement.step ();
146 if (rc != Sqlite.DONE) {
147 stderr.printf ("Failed to add genre %s to \"%s\": %d, %s\n", genre, title, rc, db.errmsg ());
148 rating_statement.reset ();
151 genre_statement.reset ();
156 int genre_bit (string genre) {
157 for (int i = 0; i < genres.length (); i++) {
158 if (genres.nth_data (i) == genre)
164 Statement aka_statement = null;
165 public int add_aka (string title, string aka) {
169 if (!movie_exists (title, out rowid))
172 if (aka_statement == null) {
173 string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (?, ?);";
174 assert (db.prepare_v2 (sql, -1, out aka_statement) == Sqlite.OK);
177 assert (aka_statement.bind_text (1, aka) == Sqlite.OK);
178 assert (aka_statement.bind_int (2, rowid) == Sqlite.OK);
180 rc = aka_statement.step ();
181 if (rc != Sqlite.DONE) {
182 stderr.printf ("Failed to add aka \"%s\" to \"%s\": %d, %s\n", aka, title, rc, db.errmsg ());
183 aka_statement.reset ();
186 aka_statement.reset ();
191 Statement plot_statement = null;
192 public int add_plot (string title, string plot, string? author) {
196 if (!movie_exists (title, out rowid))
199 if (plot_statement == null) {
200 var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
201 assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
204 assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
205 assert (plot_statement.bind_text (2, plot.replace ("\"", """)) == Sqlite.OK);
207 assert (plot_statement.bind_text (3, author) == Sqlite.OK);
209 assert (plot_statement.bind_null (3) == Sqlite.OK);
211 rc = plot_statement.step ();
212 if (rc != Sqlite.DONE) {
213 stderr.printf ("Failed to add plot for \"%s\": %d, %s\n", title, rc, db.errmsg ());
214 plot_statement.reset ();
217 plot_statement.reset ();
222 Statement exists_statement = null;
223 public bool movie_exists (string title, out int rowid = null) {
224 if (exists_statement == null) {
225 string sql = "SELECT rowid FROM Movies WHERE Title=?";
226 assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
229 assert (exists_statement.bind_text (1, title) == Sqlite.OK);
233 rc = exists_statement.step ();
234 if (rc == Sqlite.ROW) {
235 if (&rowid != null) {
236 rowid = exists_statement.column_int (0);
238 exists_statement.reset ();
241 } while (rc == Sqlite.ROW);
242 exists_statement.reset ();
247 public int count (string table) {
248 string sql = "SELECT count(*) FROM %s".printf (table);
253 rc = db.prepare_v2 (sql, -1, out stmt);
254 if (rc != Sqlite.OK) {
255 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
256 db.progress_handler (0, null);
262 if (rc == Sqlite.ROW) {
263 count = stmt.column_int (0);
265 } while (rc == Sqlite.ROW);
270 public bool has_plots () {
271 return (count ("Plots") > 0);
274 public string get_plot (string title) {
275 string sql = "SELECT Plot FROM Plots WHERE rowid in (SELECT rowid FROM Movies WHERE Title=\"%s\")".printf (title);
280 rc = db.prepare_v2 (sql, -1, out stmt);
281 if (rc != Sqlite.OK) {
282 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
283 db.progress_handler (0, null);
289 if (rc == Sqlite.ROW) {
290 return stmt.column_text (0).replace (""", "\"");
292 } while (rc == Sqlite.ROW);
297 public int clear () {
301 "DROP TABLE IF EXISTS Movies;" +
302 "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);" +
303 "DROP TABLE IF EXISTS Genres;" +
304 "CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);" +
305 "DROP TABLE IF EXISTS Akas;" +
306 "CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);" +
307 "DROP TABLE IF EXISTS Plots;" +
308 "CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT)",
310 if (rc != Sqlite.OK) {
311 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
318 public int create_aka_index () {
321 rc = db.exec ("CREATE INDEX AkasAka ON Akas(Aka);", callback, null);
322 if (rc != Sqlite.OK) {
323 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
330 public int create_votes_index () {
333 rc = db.exec ("CREATE INDEX MovieVotes ON Movies(Votes);", callback, null);
334 if (rc != Sqlite.OK) {
335 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
342 private Cancellable? _cancellable;
343 public async int query (MovieFilter filter, ReceiveMovieFunction receive_movie, int limit, Cancellable? cancellable) {
344 var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
350 // FIXME - how many opcodes until main loop iteration for best responsivity?
351 _cancellable = cancellable;
352 db.progress_handler (1000, progress_handler);
354 if (filter.title != null && filter.title != "") {
355 if ("*" in filter.title) {
356 match = "GLOB \"%s (*)\"".printf (filter.title);
358 match = "LIKE \"%s%%\"".printf (filter.title);
360 sql += sep + "(Title %s OR rowid IN (SELECT TitleID FROM Akas WHERE Aka %s))".printf (match, match);
363 if (filter.year_min > 0) {
364 sql += sep + "Year >= %d".printf (filter.year_min);
367 if (filter.year_max > 0) {
368 sql += sep + "Year <= %d".printf (filter.year_max);
371 if (filter.rating_min > 0) {
372 sql += sep + "Rating >= %d".printf (filter.rating_min);
375 if (filter.genres.field != 0) {
376 sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
378 sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit + 1);
380 stdout.printf("SQL: \"%s\"\n", sql);
382 rc = db.prepare_v2 (sql, -1, out stmt);
383 if (rc != Sqlite.OK) {
384 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
385 db.progress_handler (0, null);
393 Idle.add (query.callback);
396 if (rc == Sqlite.ROW) {
397 int year = stmt.column_int (1);
398 string title = stmt.column_text (0);
400 if (stmt.column_type (2) != Sqlite.NULL)
401 rating = stmt.column_int (2);
404 int genres = stmt.column_int (3);
406 if (match != null && !(filter.matches_title (strip_year (title, year)))) {
407 aka = movie_aka (title, match);
409 aka = strip_year (aka, year);
411 receive_movie (strip_year (title, year), aka, year, rating, genres);
413 } while (rc == Sqlite.ROW);
415 db.progress_handler (0, null);
419 private string? movie_aka (string title, string match) {
420 string sql = "SELECT Aka FROM Akas WHERE (TitleID = (SELECT rowid FROM Movies WHERE Title = \"%s\") AND Aka %s) LIMIT 1;".printf (title, match);
425 rc = db.prepare_v2 (sql, -1, out stmt);
426 if (rc != Sqlite.OK) {
427 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
433 if (rc == Sqlite.ROW) {
434 aka = stmt.column_text (0);
436 } while (rc == Sqlite.ROW);
441 private int progress_handler () {
442 ((MainContext) null).iteration (false);
443 return (int) _cancellable.is_cancelled ();
446 private string strip_year (string title, int year) {
447 string year_suffix = " (%d)".printf (year);
448 if (title.has_suffix (year_suffix))
449 return title.substring (0, title.length - year_suffix.length);
450 year_suffix = " (%d/I)".printf (year);
451 if (title.has_suffix (year_suffix))
452 return title.substring (0, title.length - year_suffix.length);
453 year_suffix = " (%d/II)".printf (year);
454 if (title.has_suffix (year_suffix))
455 return title.substring (0, title.length - year_suffix.length);