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, 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 md5 = Checksum.compute_for_string (ChecksumType.MD5, _title);
68 uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
70 string title = strip_year (_title, year);
72 string sql = "INSERT INTO Movies(ID, Title, Year) VALUES (%lld, \"%s\", %d);".printf ((int64) half_md5, title, year);
75 rc = db.exec (sql, callback, null);
76 if (rc != Sqlite.OK) {
77 stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
84 private string strip_year (string title, int year) {
85 string year_suffix = " (%d)".printf (year);
86 if (title.has_suffix (year_suffix)) {
87 return title.substring (0, title.length - year_suffix.length);
93 public int movie_set_rating (string title, int rating) {
94 string md5 = Checksum.compute_for_string (ChecksumType.MD5, title);
95 uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
97 var sql = "UPDATE Movies SET Rating=%d WHERE ID=%lld;".printf (rating, (int64) half_md5);
100 rc = db.exec (sql, callback, null);
101 if (rc != Sqlite.OK) {
102 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
109 public int movie_add_genre (string title, string genre) {
110 string md5 = Checksum.compute_for_string (ChecksumType.MD5, title);
111 uint64 half_md5 = (md5.ndup (16)).to_uint64 (null, 16);
116 bit = genre_bit (genre);
118 genres.append (genre);
119 bit = genre_bit (genre);
121 sql = "INSERT INTO Genres(Bit, Genre) VALUES (%d, \"%s\");".printf (bit, genre);
123 rc = db.exec (sql, callback, null);
124 if (rc != Sqlite.OK) {
125 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
130 sql = "UPDATE Movies SET Genres=Genres|%d WHERE ID=%lld;".printf (bit, (int64) half_md5);
131 rc = db.exec (sql, callback, null);
132 if (rc != Sqlite.OK) {
133 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
140 int genre_bit (string genre) {
141 for (int i = 0; i < genres.length (); i++) {
142 if (genres.nth_data (i) == genre)
148 public int clear () {
151 rc = db.exec ("DROP TABLE IF EXISTS Movies; CREATE TABLE Movies (ID INTEGER PRIMARY KEY, Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Genres; CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);", callback, null);
152 if (rc != Sqlite.OK) {
153 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
160 public int query (MovieFilter filter, ReceiveMovieFunction receive_movie) {
161 var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
166 if (filter.title != null && filter.title != "") {
167 if ("*" in filter.title)
168 sql += sep + "Title GLOB \"%s\"".printf (filter.title);
170 sql += sep + "Title LIKE \"%s%%\"".printf (filter.title);
173 if (filter.year_min > 0) {
174 sql += sep + "Year >= %d".printf (filter.year_min);
177 if (filter.year_max > 0) {
178 sql += sep + "Year <= %d".printf (filter.year_max);
181 if (filter.rating_min > 0) {
182 sql += sep + "Rating >= = %d".printf (filter.rating_min);
185 if (filter.genres.field != 0) {
186 sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
188 sql += " LIMIT %d;".printf (100);
190 stdout.printf("SQL: \"%s\"\n", sql);
192 rc = db.prepare_v2 (sql, -1, out stmt);
193 if (rc != Sqlite.OK) {
194 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
200 if (rc == Sqlite.ROW) {
201 string title = stmt.column_text (0);
202 int year = stmt.column_int (1);
203 int rating = stmt.column_int (2);
204 int genres = stmt.column_int (3);
205 receive_movie (title, year, rating, genres);
207 } while (rc == Sqlite.ROW);