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 person_statement = null;
223 public int add_person (string name) {
226 if (person_statement == null) {
227 string sql = "INSERT INTO People(Name) VALUES (?);";
228 rc = db.prepare_v2 (sql, -1, out person_statement);
229 if (rc != Sqlite.OK) {
230 error ("Failed to prepare statement \"" + sql + "\": " + db.errmsg ());
234 assert (person_statement.bind_text (1, name) == Sqlite.OK);
236 rc = person_statement.step ();
237 if (rc != Sqlite.DONE) {
238 if (rc == Sqlite.CONSTRAINT)
239 stderr.printf ("Skipped duplicate person \"%s\"\n", name);
241 stderr.printf ("Failed to insert person \"%s\": %d, %s\n", name, rc, db.errmsg ());
242 person_statement.reset ();
245 person_statement.reset ();
250 Statement actor_statement;
251 public int add_actor (string name, string title, string? info, string? character, int number = 0) {
256 if (!person_exists (name, out actorid))
258 if (!movie_exists (title, out titleid))
261 if (actor_statement == null) {
262 string sql = "INSERT INTO Actors(ActorID, TitleID, Info, Character, Number) VALUES (?, ?, ?, ?, ?);";
263 assert (db.prepare_v2 (sql, -1, out actor_statement) == Sqlite.OK);
266 assert (actor_statement.bind_int (1, actorid) == Sqlite.OK);
267 assert (actor_statement.bind_int (2, titleid) == Sqlite.OK);
269 assert (actor_statement.bind_text (3, info) == Sqlite.OK);
271 assert (actor_statement.bind_null (3) == Sqlite.OK);
273 if (character != null)
274 assert (actor_statement.bind_text (4, character) == Sqlite.OK);
276 assert (actor_statement.bind_null (4) == Sqlite.OK);
277 assert (actor_statement.bind_int (5, number) == Sqlite.OK);
279 rc = actor_statement.step ();
280 if (rc != Sqlite.DONE) {
281 stderr.printf ("Failed to insert actor \"%s\": %d, %s\n", name, rc, db.errmsg ());
282 actor_statement.reset ();
285 actor_statement.reset ();
291 Statement exists_statement = null;
292 public bool movie_exists (string title, out int rowid = null) {
293 if (exists_statement == null) {
294 string sql = "SELECT rowid FROM Movies WHERE Title=?";
295 assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
298 assert (exists_statement.bind_text (1, title) == Sqlite.OK);
302 rc = exists_statement.step ();
303 if (rc == Sqlite.ROW) {
304 if (&rowid != null) {
305 rowid = exists_statement.column_int (0);
307 exists_statement.reset ();
310 } while (rc == Sqlite.ROW);
311 exists_statement.reset ();
316 Statement person_exists_statement = null;
317 public bool person_exists (string name, out int rowid = null) {
318 if (person_exists_statement == null) {
319 string sql = "SELECT rowid FROM People WHERE Name=?";
320 assert (db.prepare_v2 (sql, -1, out person_exists_statement) == Sqlite.OK);
323 assert (person_exists_statement.bind_text (1, name) == Sqlite.OK);
327 rc = person_exists_statement.step ();
328 if (rc == Sqlite.ROW) {
329 if (&rowid != null) {
330 rowid = person_exists_statement.column_int (0);
332 person_exists_statement.reset ();
336 } while (rc == Sqlite.ROW);
337 person_exists_statement.reset ();
342 public int count (string table) {
343 string sql = "SELECT count(*) FROM %s".printf (table);
348 rc = db.prepare_v2 (sql, -1, out stmt);
349 if (rc != Sqlite.OK) {
350 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
351 db.progress_handler (0, null);
357 if (rc == Sqlite.ROW) {
358 count = stmt.column_int (0);
360 } while (rc == Sqlite.ROW);
365 public bool has_plots () {
366 return (count ("Plots") > 0);
369 public string get_plot (string title) {
370 string sql = "SELECT Plot FROM Plots WHERE rowid in (SELECT rowid FROM Movies WHERE Title=\"%s\")".printf (title);
375 rc = db.prepare_v2 (sql, -1, out stmt);
376 if (rc != Sqlite.OK) {
377 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
378 db.progress_handler (0, null);
384 if (rc == Sqlite.ROW) {
385 return stmt.column_text (0).replace (""", "\"");
387 } while (rc == Sqlite.ROW);
392 public int clear () {
396 "DROP TABLE IF EXISTS Movies;" +
397 "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);" +
398 "DROP TABLE IF EXISTS Genres;" +
399 "CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);" +
400 "DROP TABLE IF EXISTS Akas;" +
401 "CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);" +
402 "DROP TABLE IF EXISTS Plots;" +
403 "CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT);" +
404 "DROP TABLE IF EXISTS People;" +
405 "CREATE TABLE People (Name TEXT PRIMARY KEY COLLATE NOCASE);" +
406 "DROP TABLE IF EXISTS Actors;" +
407 "CREATE TABLE Actors (ActorID INTEGER NOT NULL, TitleID INTEGER NOT NULL, Info TEXT, Character TEXT, Number INTEGER NOT NULL DEFAULT 0);" +
408 "CREATE INDEX MovieCast ON Actors(TitleID);" +
409 "CREATE INDEX ActorFilmography ON Actors(ActorID);",
411 if (rc != Sqlite.OK) {
412 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
419 public int create_aka_index () {
422 rc = db.exec ("CREATE INDEX AkasAka ON Akas(Aka);", callback, null);
423 if (rc != Sqlite.OK) {
424 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
431 public int create_votes_index () {
434 rc = db.exec ("CREATE INDEX MovieVotes ON Movies(Votes);", callback, null);
435 if (rc != Sqlite.OK) {
436 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
443 private Cancellable? _cancellable;
444 public async int query (MovieFilter filter, ReceiveMovieFunction receive_movie, int limit, Cancellable? cancellable) {
445 var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
451 // FIXME - how many opcodes until main loop iteration for best responsivity?
452 _cancellable = cancellable;
453 db.progress_handler (1000, progress_handler);
455 if (filter.title != null && filter.title != "") {
456 if ("*" in filter.title) {
457 match = "GLOB \"%s (*)\"".printf (filter.title);
459 match = "LIKE \"%s%%\"".printf (filter.title);
461 sql += sep + "(Title %s OR rowid IN (SELECT TitleID FROM Akas WHERE Aka %s))".printf (match, match);
464 if (filter.year_min > 0) {
465 sql += sep + "Year >= %d".printf (filter.year_min);
468 if (filter.year_max > 0) {
469 sql += sep + "Year <= %d".printf (filter.year_max);
472 if (filter.rating_min > 0) {
473 sql += sep + "Rating >= %d".printf (filter.rating_min);
476 if (filter.genres.field != 0) {
477 sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
479 sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit + 1);
481 stdout.printf("SQL: \"%s\"\n", sql);
483 rc = db.prepare_v2 (sql, -1, out stmt);
484 if (rc != Sqlite.OK) {
485 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
486 db.progress_handler (0, null);
494 Idle.add (query.callback);
497 if (rc == Sqlite.ROW) {
498 int year = stmt.column_int (1);
499 string title = stmt.column_text (0);
501 if (stmt.column_type (2) != Sqlite.NULL)
502 rating = stmt.column_int (2);
505 int genres = stmt.column_int (3);
507 if (match != null && !(filter.matches_title (strip_year (title, year)))) {
508 aka = movie_aka (title, match);
510 aka = strip_year (aka, year);
512 receive_movie (strip_year (title, year), aka, year, rating, genres);
514 } while (rc == Sqlite.ROW);
516 db.progress_handler (0, null);
520 private string? movie_aka (string title, string match) {
521 string sql = "SELECT Aka FROM Akas WHERE (TitleID = (SELECT rowid FROM Movies WHERE Title = \"%s\") AND Aka %s) LIMIT 1;".printf (title, match);
526 rc = db.prepare_v2 (sql, -1, out stmt);
527 if (rc != Sqlite.OK) {
528 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
534 if (rc == Sqlite.ROW) {
535 aka = stmt.column_text (0);
537 } while (rc == Sqlite.ROW);
542 private int progress_handler () {
543 ((MainContext) null).iteration (false);
544 return (int) _cancellable.is_cancelled ();
547 private string strip_year (string title, int year) {
548 string year_suffix = " (%d)".printf (year);
549 if (title.has_suffix (year_suffix))
550 return title.substring (0, title.length - year_suffix.length);
551 year_suffix = " (%d/I)".printf (year);
552 if (title.has_suffix (year_suffix))
553 return title.substring (0, title.length - year_suffix.length);
554 year_suffix = " (%d/II)".printf (year);
555 if (title.has_suffix (year_suffix))
556 return title.substring (0, title.length - year_suffix.length);