2 * Copyright (C) 2007 by INdT
4 * This program is free software; you can redistribute it and/or
5 * modify it under the terms of the GNU Lesser General Public License
6 * as published by the Free Software Foundation; either version 2
7 * of the License, or (at your option) any later version.
9 * This program is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License for more details.
14 * You should have received a copy of the GNU Lesser General Public License
15 * along with this program; if not, write to the Free Software
16 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
18 * @author Gustavo Sverzut Barbieri <gustavo.barbieri@openbossa.org>
21 #include "lightmediascanner_db_private.h"
26 #if SQLITE_VERSION_NUMBER < 3003009
28 sqlite3_prepare_v2(sqlite3 *db, const char *sql, int len, sqlite3_stmt **stmt, const char **tail)
30 return sqlite3_prepare(db, sql, len, stmt, tail);
32 #endif /* SQLITE_VERSION_NUMBER < 3003009 */
34 #if SQLITE_VERSION_NUMBER < 3003007
36 sqlite3_clear_bindings(sqlite3_stmt *stmt)
42 last = sqlite3_bind_parameter_count(stmt);
43 for(i = 1; rc == SQLITE_OK && i <= last; i++) {
44 rc = sqlite3_bind_null(stmt, i);
48 #endif /* SQLITE_VERSION_NUMBER < 3003007 */
50 #if SQLITE_VERSION_NUMBER < 3003008
51 /* Until 3.3.8 it doesn't support CREATE TRIGGER IF NOT EXISTS, so
52 * just ignore errors :-(
55 lms_db_create_trigger_if_not_exists(sqlite3 *db, const char *sql)
58 int r, sql_len, prefix_len;
60 prefix_len = sizeof("CREATE TRIGGER ") - 1;
61 sql_len = strlen(sql);
62 query = malloc((prefix_len + sql_len + 1) * sizeof(char));
66 memcpy(query, "CREATE TRIGGER ", prefix_len);
67 memcpy(query + prefix_len, sql, sql_len + 1);
68 r = sqlite3_exec(db, query, NULL, NULL, &errmsg);
74 #else /* SQLITE_VERSION_NUMBER < 3003008 */
76 lms_db_create_trigger_if_not_exists(sqlite3 *db, const char *sql)
79 int r, sql_len, prefix_len;
81 prefix_len = sizeof("CREATE TRIGGER IF NOT EXISTS ") - 1;
82 sql_len = strlen(sql);
83 query = malloc((prefix_len + sql_len + 1) * sizeof(char));
87 memcpy(query, "CREATE TRIGGER IF NOT EXISTS ", prefix_len);
88 memcpy(query + prefix_len, sql, sql_len + 1);
89 r = sqlite3_exec(db, query, NULL, NULL, &errmsg);
92 fprintf(stderr, "ERROR: could not create trigger: %s\n", errmsg);
98 #endif /* SQLITE_VERSION_NUMBER < 3003008 */
101 lms_db_compile_stmt(sqlite3 *db, const char *sql)
105 if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
106 fprintf(stderr, "ERROR: could not prepare \"%s\": %s\n", sql,
113 lms_db_finalize_stmt(sqlite3_stmt *stmt, const char *name)
117 r = sqlite3_finalize(stmt);
118 if (r != SQLITE_OK) {
119 fprintf(stderr, "ERROR: could not finalize %s statement: #%d\n",
128 lms_db_reset_stmt(sqlite3_stmt *stmt)
132 ret = r = sqlite3_reset(stmt);
134 fprintf(stderr, "ERROR: could not reset SQL statement: #%d\n", r);
136 r = sqlite3_clear_bindings(stmt);
139 fprintf(stderr, "ERROR: could not clear SQL: #%d\n", r);
145 lms_db_bind_text(sqlite3_stmt *stmt, int col, const char *text, int len)
150 r = sqlite3_bind_text(stmt, col, text, len, SQLITE_STATIC);
152 r = sqlite3_bind_null(stmt, col);
160 db = sqlite3_db_handle(stmt);
161 err = sqlite3_errmsg(db);
162 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
168 lms_db_bind_blob(sqlite3_stmt *stmt, int col, const void *blob, int len)
173 r = sqlite3_bind_blob(stmt, col, blob, len, SQLITE_STATIC);
175 r = sqlite3_bind_null(stmt, col);
183 db = sqlite3_db_handle(stmt);
184 err = sqlite3_errmsg(db);
185 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
191 lms_db_bind_int64(sqlite3_stmt *stmt, int col, int64_t value)
195 r = sqlite3_bind_int64(stmt, col, value);
202 db = sqlite3_db_handle(stmt);
203 err = sqlite3_errmsg(db);
204 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
210 lms_db_bind_int64_or_null(sqlite3_stmt *stmt, int col, int64_t *p_value)
215 r = sqlite3_bind_int64(stmt, col, *p_value);
217 r = sqlite3_bind_null(stmt, col);
224 db = sqlite3_db_handle(stmt);
225 err = sqlite3_errmsg(db);
226 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
232 lms_db_bind_int(sqlite3_stmt *stmt, int col, int value)
236 r = sqlite3_bind_int(stmt, col, value);
243 db = sqlite3_db_handle(stmt);
244 err = sqlite3_errmsg(db);
245 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
251 lms_db_bind_double(sqlite3_stmt *stmt, int col, double value)
255 r = sqlite3_bind_double(stmt, col, value);
262 db = sqlite3_db_handle(stmt);
263 err = sqlite3_errmsg(db);
264 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
270 lms_db_table_version_get(sqlite3 *db, const char *table)
275 stmt = lms_db_compile_stmt(db,
276 "SELECT version FROM lms_internal WHERE tab = ?");
280 if (lms_db_bind_text(stmt, 1, table, -1) != 0) {
285 r = sqlite3_step(stmt);
286 if (r == SQLITE_DONE)
288 else if (r == SQLITE_ROW)
289 version = sqlite3_column_int(stmt, 1);
292 fprintf(stderr, "ERROR: could not get table '%s' version: %s\n",
293 table, sqlite3_errmsg(db));
297 lms_db_reset_stmt(stmt);
298 lms_db_finalize_stmt(stmt, "table_version_get");
304 lms_db_table_version_set(sqlite3 *db, const char *table, unsigned int version)
309 stmt = lms_db_compile_stmt(db,
310 "INSERT OR REPLACE INTO lms_internal (tab, version) VALUES (?, ?)");
314 ret = lms_db_bind_text(stmt, 1, table, -1);
318 ret = lms_db_bind_int(stmt, 2, version);
322 r = sqlite3_step(stmt);
323 if (r != SQLITE_DONE) {
325 fprintf(stderr, "ERROR: could not set table '%s' version: %s\n",
326 table, sqlite3_errmsg(db));
330 lms_db_reset_stmt(stmt);
331 lms_db_finalize_stmt(stmt, "table_version_set");
337 lms_db_table_update(sqlite3 *db, const char *table, unsigned int current_version, unsigned int last_version, const lms_db_table_updater_t *updaters)
339 if (current_version == last_version)
341 else if (current_version > last_version) {
343 "WARNING: current version (%d) of table '%s' is greater than "
344 "last known version (%d), no updates will be made.\n",
345 current_version, table, last_version);
349 for (; current_version < last_version; current_version++) {
352 is_last_run = current_version == (last_version - 1);
353 r = updaters[current_version](db, table, current_version, is_last_run);
356 "ERROR: could not update table '%s' from version %d->%d\n",
357 table, current_version, current_version + 1);
360 lms_db_table_version_set(db, table, current_version + 1);
367 lms_db_table_update_if_required(sqlite3 *db, const char *table, unsigned int last_version, lms_db_table_updater_t *updaters)
371 current_version = lms_db_table_version_get(db, table);
372 if (current_version < 0)
375 return lms_db_table_update(db, table, current_version, last_version,
380 lms_db_cache_find_db(const struct lms_db_cache *cache, const sqlite3 *db)
384 for (i = 0; i < cache->size; i++)
385 if (cache->entries[i].db == db)
392 lms_db_cache_resize(struct lms_db_cache *cache, int new_size)
394 cache->size = new_size;
395 cache->entries = realloc(cache->entries,
396 cache->size * sizeof(*cache->entries));
397 if (cache->size && !cache->entries) {
407 lms_db_cache_add(struct lms_db_cache *cache, const sqlite3 *db, void *data)
409 struct lms_db_cache_entry *e;
412 idx = lms_db_cache_find_db(cache, db);
414 e = cache->entries + idx;
419 "ERROR: cache %p for db %p has another data registered"
420 ": %p (current is %p)\n", cache, db, e->data, data);
426 if (lms_db_cache_resize(cache, cache->size + 1) != 0) {
430 e = cache->entries + idx;
437 lms_db_cache_del(struct lms_db_cache *cache, const sqlite3 *db, void *data)
440 struct lms_db_cache_entry *e;
442 idx = lms_db_cache_find_db(cache, db);
444 fprintf(stderr, "ERROR: no db %p found in cache %p\n", db, cache);
448 e = cache->entries + idx;
449 if (e->data != data) {
450 fprintf(stderr, "ERROR: data mismatch in request to delete from cache: "
451 "want %p, has %p, cache %p, db %p\n", data, e->data, cache, db);
455 for (; idx < cache->size - 1; idx++)
456 cache->entries[idx] = cache->entries[idx + 1];
458 return lms_db_cache_resize(cache, cache->size - 1);
462 lms_db_cache_get(struct lms_db_cache *cache, const sqlite3 *db, void **pdata)
466 idx = lms_db_cache_find_db(cache, db);
470 *pdata = cache->entries[idx].data;
475 lms_db_create_core_tables_if_required(sqlite3 *db)
482 "CREATE TABLE IF NOT EXISTS lms_internal ("
483 "tab TEXT NOT NULL UNIQUE, "
484 "version INTEGER NOT NULL"
486 NULL, NULL, &errmsg);
487 if (r != SQLITE_OK) {
488 fprintf(stderr, "ERROR: could not create 'lms_internal' table: %s\n",
490 sqlite3_free(errmsg);
495 "CREATE TABLE IF NOT EXISTS files ("
496 "id INTEGER PRIMARY KEY AUTOINCREMENT, "
497 "path BLOB NOT NULL UNIQUE, "
498 "mtime INTEGER NOT NULL, "
499 "dtime INTEGER NOT NULL, "
500 "size INTEGER NOT NULL"
502 NULL, NULL, &errmsg);
503 if (r != SQLITE_OK) {
504 fprintf(stderr, "ERROR: could not create 'files' table: %s\n", errmsg);
505 sqlite3_free(errmsg);
510 "CREATE INDEX IF NOT EXISTS files_path_idx ON files ("
513 NULL, NULL, &errmsg);
514 if (r != SQLITE_OK) {
515 fprintf(stderr, "ERROR: could not create 'files_path_idx' index: %s\n",
517 sqlite3_free(errmsg);
526 lms_db_compile_stmt_begin_transaction(sqlite3 *db)
528 return lms_db_compile_stmt(db, "BEGIN TRANSACTION");
532 lms_db_begin_transaction(sqlite3_stmt *stmt)
537 r = sqlite3_step(stmt);
538 if (r != SQLITE_DONE) {
539 fprintf(stderr, "ERROR: could not begin transaction: %s\n",
540 sqlite3_errmsg(sqlite3_db_handle(stmt)));
544 r = sqlite3_reset(stmt);
546 fprintf(stderr, "ERROR: could not reset SQL statement: %s\n",
547 sqlite3_errmsg(sqlite3_db_handle(stmt)));
553 lms_db_compile_stmt_end_transaction(sqlite3 *db)
555 return lms_db_compile_stmt(db, "COMMIT");
559 lms_db_end_transaction(sqlite3_stmt *stmt)
564 r = sqlite3_step(stmt);
565 if (r != SQLITE_DONE) {
566 fprintf(stderr, "ERROR: could not end transaction: %s\n",
567 sqlite3_errmsg(sqlite3_db_handle(stmt)));
571 r = sqlite3_reset(stmt);
573 fprintf(stderr, "ERROR: could not reset SQL statement: %s\n",
574 sqlite3_errmsg(sqlite3_db_handle(stmt)));
580 lms_db_compile_stmt_get_file_info(sqlite3 *db)
582 return lms_db_compile_stmt(db,
583 "SELECT id, mtime, dtime, size FROM files WHERE path = ?");
587 lms_db_get_file_info(sqlite3_stmt *stmt, struct lms_file_info *finfo)
591 ret = lms_db_bind_blob(stmt, 1, finfo->path, finfo->path_len);
595 r = sqlite3_step(stmt);
596 if (r == SQLITE_DONE) {
602 if (r != SQLITE_ROW) {
603 fprintf(stderr, "ERROR: could not get file info from table: %s\n",
604 sqlite3_errmsg(sqlite3_db_handle(stmt)));
609 finfo->id = sqlite3_column_int64(stmt, 0);
610 finfo->mtime = sqlite3_column_int(stmt, 1);
611 finfo->dtime = sqlite3_column_int(stmt, 2);
612 finfo->size = sqlite3_column_int(stmt, 3);
616 lms_db_reset_stmt(stmt);
622 lms_db_compile_stmt_update_file_info(sqlite3 *db)
624 return lms_db_compile_stmt(db,
625 "UPDATE files SET mtime = ?, dtime = ?, size = ? WHERE id = ?");
629 lms_db_update_file_info(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
633 ret = lms_db_bind_int(stmt, 1, finfo->mtime);
637 ret = lms_db_bind_int(stmt, 2, finfo->dtime);
641 ret = lms_db_bind_int(stmt, 3, finfo->size);
645 ret = lms_db_bind_int(stmt, 4, finfo->id);
649 r = sqlite3_step(stmt);
650 if (r != SQLITE_DONE) {
651 fprintf(stderr, "ERROR: could not update file info: %s\n",
652 sqlite3_errmsg(sqlite3_db_handle(stmt)));
660 lms_db_reset_stmt(stmt);
666 lms_db_compile_stmt_insert_file_info(sqlite3 *db)
668 return lms_db_compile_stmt(db,
669 "INSERT INTO files (path, mtime, dtime, size) VALUES(?, ?, ?, ?)");
673 lms_db_insert_file_info(sqlite3_stmt *stmt, struct lms_file_info *finfo)
677 ret = lms_db_bind_blob(stmt, 1, finfo->path, finfo->path_len);
681 ret = lms_db_bind_int(stmt, 2, finfo->mtime);
685 ret = lms_db_bind_int(stmt, 3, finfo->dtime);
689 ret = lms_db_bind_int(stmt, 4, finfo->size);
693 r = sqlite3_step(stmt);
694 if (r != SQLITE_DONE) {
695 fprintf(stderr, "ERROR: could not insert file info: %s\n",
696 sqlite3_errmsg(sqlite3_db_handle(stmt)));
701 finfo->id = sqlite3_last_insert_rowid(sqlite3_db_handle(stmt));
705 lms_db_reset_stmt(stmt);
711 lms_db_compile_stmt_delete_file_info(sqlite3 *db)
713 return lms_db_compile_stmt(db, "DELETE FROM files WHERE id = ?");
717 lms_db_delete_file_info(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
721 ret = lms_db_bind_int64(stmt, 1, finfo->id);
725 r = sqlite3_step(stmt);
726 if (r != SQLITE_DONE) {
727 fprintf(stderr, "ERROR: could not delete file info: %s\n",
728 sqlite3_errmsg(sqlite3_db_handle(stmt)));
735 lms_db_reset_stmt(stmt);
741 lms_db_compile_stmt_set_file_dtime(sqlite3 *db)
743 return lms_db_compile_stmt(db, "UPDATE files SET dtime = ? WHERE id = ?");
747 lms_db_set_file_dtime(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
751 ret = lms_db_bind_int(stmt, 1, finfo->dtime);
755 ret = lms_db_bind_int64(stmt, 1, finfo->id);
759 r = sqlite3_step(stmt);
760 if (r != SQLITE_DONE) {
761 fprintf(stderr, "ERROR: could not set file dtime: %s\n",
762 sqlite3_errmsg(sqlite3_db_handle(stmt)));
770 lms_db_reset_stmt(stmt);
776 lms_db_compile_stmt_get_files(sqlite3 *db)
778 return lms_db_compile_stmt(db,
779 "SELECT id, path, mtime, dtime, size FROM files WHERE path LIKE ?");
783 lms_db_get_files(sqlite3_stmt *stmt, const char *path, int len)
787 ret = lms_db_bind_blob(stmt, 1, path, len);