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 CatalogSqlite : Object {
24 public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres);
26 public CatalogSqlite (string filename) {
29 rc = Database.open (filename, out db);
30 if (rc != Sqlite.OK) {
31 stderr.printf ("Can't open database: %d, %s\n", rc, db.errmsg ());
35 rc = db.exec ("PRAGMA locking_mode = EXCLUSIVE;", callback, null);
36 if (rc != Sqlite.OK) {
37 stderr.printf ("Can't get exclusive lock: %d, %s\n", rc, db.errmsg ());
41 rc = db.exec ("PRAGMA synchronous = OFF;", callback, null);
43 stderr.printf ("Can't turn off synchronous access: %d, %s\n", rc, db.errmsg ());
48 public static int callback (int n_columns, string[] values,
49 string[] column_names) {
50 for (int i = 0; i < n_columns; i++) {
51 stdout.printf ("%s = %s\n", column_names[i], values[i]);
58 public int add_movie (string table, Movie movie) {
59 string sql = "INSERT INTO %s(Title, Year, Rating, Genres) VALUES (\"%s\", %d, %d, %d);".printf (table, movie.title, movie.year, movie.rating, movie.genres.field);
62 rc = db.exec (sql, callback, null);
63 if (rc != Sqlite.OK) {
64 stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
71 public int delete_movie (string table, Movie movie) {
72 string sql = "DELETE FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
75 rc = db.exec (sql, callback, null);
76 if (rc != Sqlite.OK) {
77 stderr.printf ("Failed to delete movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
84 public int count (string table) {
85 string sql = "SELECT count(*) FROM %s".printf (table);
90 rc = db.prepare_v2 (sql, -1, out stmt);
91 if (rc != Sqlite.OK) {
92 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
93 db.progress_handler (0, null);
99 if (rc == Sqlite.ROW) {
100 count = stmt.column_int (0);
102 } while (rc == Sqlite.ROW);
107 public bool contains (string table, Movie movie) {
108 string sql = "SELECT count(*) FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
113 rc = db.prepare_v2 (sql, -1, out stmt);
114 if (rc != Sqlite.OK) {
115 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
116 db.progress_handler (0, null);
122 if (rc == Sqlite.ROW) {
123 count = stmt.column_int (0);
125 } while (rc == Sqlite.ROW);
130 private int prepare () {
133 rc = db.exec ("CREATE TABLE IF NOT EXISTS Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);", callback, null);
134 if (rc != Sqlite.OK) {
135 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
142 public int clear () {
145 rc = db.exec ("DROP TABLE IF EXISTS Collection; CREATE TABLE Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Loaned; CREATE TABLE Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Watchlist; CREATE TABLE Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);", callback, null);
146 if (rc != Sqlite.OK) {
147 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
154 private Cancellable? _cancellable;
155 public async int query (string table, MovieFilter filter, MovieSource.ReceiveMovieFunction callback, int limit, Cancellable? cancellable) {
156 var sql = "SELECT Title, Year, Rating, Genres FROM %s".printf (table);
161 // FIXME - how many opcodes until main loop iteration for best responsivity?
162 _cancellable = cancellable;
163 db.progress_handler (1000, progress_handler);
165 if (filter.title != null && filter.title != "") {
166 if ("*" in filter.title)
167 sql += sep + "Title GLOB \"%s (*)\"".printf (filter.title);
169 sql += sep + "Title LIKE \"%s%%\"".printf (filter.title);
172 if (filter.year_min > 0) {
173 sql += sep + "Year >= %d".printf (filter.year_min);
176 if (filter.year_max > 0) {
177 sql += sep + "Year <= %d".printf (filter.year_max);
180 if (filter.rating_min > 0) {
181 sql += sep + "Rating >= = %d".printf (filter.rating_min);
184 if (filter.genres.field != 0) {
185 sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
187 sql += " LIMIT %d;".printf (limit);
189 stdout.printf("SQL: \"%s\"\n", sql);
191 rc = db.prepare_v2 (sql, -1, out stmt);
192 if (rc != Sqlite.OK) {
193 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
194 db.progress_handler (0, null);
199 Idle.add (query.callback);
202 if (rc == Sqlite.ROW) {
203 var movie = new Movie ();
204 movie.year = stmt.column_int (1);
205 movie.title = stmt.column_text (0);
206 movie.rating = stmt.column_int (2);
207 movie.genres.field = stmt.column_int (3);
208 // TODO - depending on settings, this could be something else, like director info or runtime
209 movie.secondary = movie.genres.to_string ();
212 } while (rc == Sqlite.ROW);
214 db.progress_handler (0, null);
218 private int progress_handler () {
219 ((MainContext) null).iteration (false);
220 return (int) _cancellable.is_cancelled ();