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 {
26 public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres);
28 public CatalogSqlite (string filename) {
31 rc = Database.open (filename, out db);
32 if (rc != Sqlite.OK) {
33 stderr.printf ("Can't open database: %d, %s\n", rc, db.errmsg ());
37 rc = db.exec ("PRAGMA locking_mode = EXCLUSIVE;", callback, null);
38 if (rc != Sqlite.OK) {
39 stderr.printf ("Can't get exclusive lock: %d, %s\n", rc, db.errmsg ());
43 rc = db.exec ("PRAGMA synchronous = OFF;", callback, null);
45 stderr.printf ("Can't turn off synchronous access: %d, %s\n", rc, db.errmsg ());
50 public static int callback (int n_columns, string[] values,
51 string[] column_names) {
52 for (int i = 0; i < n_columns; i++) {
53 stdout.printf ("%s = %s\n", column_names[i], values[i]);
60 public int add_movie (string table, Movie movie) {
61 var sql = new StringBuilder ();
63 sql.append_printf ("INSERT INTO %s(Title, Year, Rating, Genres", table);
64 if (table == "Watched")
65 sql.append_printf (", Date) VALUES (\"%s\", %d, %d, %d, %u);",
66 movie.title, movie.year, movie.rating, movie.genres.field, movie.julian_date);
68 sql.append_printf (") VALUES (\"%s\", %d, %d, %d);",
69 movie.title, movie.year, movie.rating, movie.genres.field);
71 rc = db.exec (sql.str, callback, null);
72 if (rc != Sqlite.OK) {
73 stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
80 public int delete_movie (string table, Movie movie) {
81 string sql = "DELETE FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
84 rc = db.exec (sql, callback, null);
85 if (rc != Sqlite.OK) {
86 stderr.printf ("Failed to delete movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
93 public int count (string table) {
94 string sql = "SELECT count(*) FROM %s".printf (table);
99 rc = db.prepare_v2 (sql, -1, out stmt);
100 if (rc != Sqlite.OK) {
101 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
102 db.progress_handler (0, null);
108 if (rc == Sqlite.ROW) {
109 count = stmt.column_int (0);
111 } while (rc == Sqlite.ROW);
116 public bool contains (string table, Movie movie) {
117 string sql = "SELECT count(*) FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
122 rc = db.prepare_v2 (sql, -1, out stmt);
123 if (rc != Sqlite.OK) {
124 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
125 db.progress_handler (0, null);
131 if (rc == Sqlite.ROW) {
132 count = stmt.column_int (0);
134 } while (rc == Sqlite.ROW);
139 private int prepare () {
143 rc = db.exec ("CREATE TABLE IF NOT EXISTS Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " +
144 "CREATE TABLE IF NOT EXISTS Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " +
145 "CREATE TABLE IF NOT EXISTS Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0, Date INTEGER); " +
146 "CREATE TABLE IF NOT EXISTS Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);",
148 if (rc != Sqlite.OK) {
149 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
153 // Add a date column to the Watched table
154 rc = db.prepare_v2 ("SELECT sql FROM sqlite_master WHERE (type = 'table' AND name = 'Watched');",
156 if (rc != Sqlite.OK) {
157 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
162 if (rc == Sqlite.ROW) {
163 var sql = stmt.column_text (0);
164 if (sql == "CREATE TABLE Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0)") {
165 rc = db.exec ("ALTER TABLE Watched ADD COLUMN Date INTEGER;",
167 if (rc != Sqlite.OK) {
168 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
174 } while (rc == Sqlite.ROW);
179 public int clear () {
182 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);
183 if (rc != Sqlite.OK) {
184 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
191 private Cancellable? _cancellable;
192 public async int query (string table, MovieFilter filter, MovieSource.ReceiveMovieFunction callback, int limit, Cancellable? cancellable) {
193 var sql = new StringBuilder ();
194 sql.append ("SELECT Title, Year, Rating, Genres");
195 if (table == "Watched")
196 sql.append (", Date");
197 sql.append (" FROM ");
203 // FIXME - how many opcodes until main loop iteration for best responsivity?
204 _cancellable = cancellable;
205 db.progress_handler (1000, progress_handler);
207 if (filter.title != null && filter.title != "") {
208 if ("*" in filter.title) {
210 sql.append_printf ("Title GLOB \"%s\"", filter.title);
213 sql.append_printf ("Title LIKE \"%s%%\"", filter.title);
217 if (filter.year_min > 0) {
219 sql.append_printf ("Year >= %d", filter.year_min);
222 if (filter.year_max > 0) {
224 sql.append_printf ("Year <= %d", filter.year_max);
227 if (filter.rating_min > 0) {
229 sql.append_printf ("Rating >= %d", filter.rating_min);
232 if (filter.genres.field != 0) {
234 sql.append_printf ("Genres&%d = %d", filter.genres.field, filter.genres.field);
236 sql.append_printf (" LIMIT %d;", limit);
238 stdout.printf("SQL: \"%s\"\n", sql.str);
240 rc = db.prepare_v2 (sql.str, -1, out stmt);
241 if (rc != Sqlite.OK) {
242 stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
243 db.progress_handler (0, null);
247 result = new SList<Movie> ();
251 Idle.add (query.callback);
254 if (rc == Sqlite.ROW) {
255 var movie = new Movie ();
256 movie.year = stmt.column_int (1);
257 movie.title = stmt.column_text (0);
258 movie.rating = stmt.column_int (2);
259 movie.genres.field = stmt.column_int (3);
260 if (table == "Watched")
261 movie.julian_date = stmt.column_int (4);
262 // TODO - depending on settings, this could be something else, like director info or runtime
263 movie.secondary = movie.genres.to_string ();
264 result.append (movie);
265 if (++results_waiting >= 10) {
267 result = new SList<Movie> ();
271 } while (rc == Sqlite.ROW);
273 if (results_waiting > 0)
275 result = new SList<Movie> ();
277 db.progress_handler (0, null);
281 private int progress_handler () {
282 ((MainContext) null).iteration (false);
283 return (int) _cancellable.is_cancelled ();