1 #include "lightmediascanner_db_private.h"
6 #if SQLITE_VERSION_NUMBER < 3003009
8 sqlite3_prepare_v2(sqlite3 *db, const char *sql, int len, sqlite3_stmt **stmt, const char **tail)
10 return sqlite3_prepare(db, sql, len, stmt, tail);
12 #endif /* SQLITE_VERSION_NUMBER < 3003009 */
14 #if SQLITE_VERSION_NUMBER < 3003007
16 sqlite3_clear_bindings(sqlite3_stmt *stmt)
22 last = sqlite3_bind_parameter_count(stmt);
23 for(i = 1; rc == SQLITE_OK && i <= last; i++) {
24 rc = sqlite3_bind_null(stmt, i);
28 #endif /* SQLITE_VERSION_NUMBER < 3003007 */
30 #if SQLITE_VERSION_NUMBER < 3003008
31 /* Until 3.3.8 it doesn't support CREATE TRIGGER IF NOT EXISTS, so
32 * just ignore errors :-(
35 lms_db_create_trigger_if_not_exists(sqlite3 *db, const char *sql)
38 int r, sql_len, prefix_len;
40 prefix_len = sizeof("CREATE TRIGGER ") - 1;
41 sql_len = strlen(sql);
42 query = malloc((prefix_len + sql_len + 1) * sizeof(char));
46 memcpy(query, "CREATE TRIGGER ", prefix_len);
47 memcpy(query + prefix_len, sql, sql_len + 1);
48 r = sqlite3_exec(db, query, NULL, NULL, &errmsg);
54 #else /* SQLITE_VERSION_NUMBER < 3003008 */
56 lms_db_create_trigger_if_not_exists(sqlite3 *db, const char *sql)
59 int r, sql_len, prefix_len;
61 prefix_len = sizeof("CREATE TRIGGER IF NOT EXISTS ") - 1;
62 sql_len = strlen(sql);
63 query = malloc((prefix_len + sql_len + 1) * sizeof(char));
67 memcpy(query, "CREATE TRIGGER IF NOT EXISTS ", prefix_len);
68 memcpy(query + prefix_len, sql, sql_len + 1);
69 r = sqlite3_exec(db, query, NULL, NULL, &errmsg);
72 fprintf(stderr, "ERROR: could not create trigger: %s\n", errmsg);
78 #endif /* SQLITE_VERSION_NUMBER < 3003008 */
81 lms_db_compile_stmt(sqlite3 *db, const char *sql)
85 if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
86 fprintf(stderr, "ERROR: could not prepare \"%s\": %s\n", sql,
93 lms_db_finalize_stmt(sqlite3_stmt *stmt, const char *name)
97 r = sqlite3_finalize(stmt);
99 fprintf(stderr, "ERROR: could not finalize %s statement: #%d\n",
108 lms_db_reset_stmt(sqlite3_stmt *stmt)
112 ret = r = sqlite3_reset(stmt);
114 fprintf(stderr, "ERROR: could not reset SQL statement: #%d\n", r);
116 r = sqlite3_clear_bindings(stmt);
119 fprintf(stderr, "ERROR: could not clear SQL: #%d\n", r);
125 lms_db_bind_text(sqlite3_stmt *stmt, int col, const char *text, int len)
130 r = sqlite3_bind_text(stmt, col, text, len, SQLITE_STATIC);
132 r = sqlite3_bind_null(stmt, col);
140 db = sqlite3_db_handle(stmt);
141 err = sqlite3_errmsg(db);
142 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
148 lms_db_bind_blob(sqlite3_stmt *stmt, int col, const void *blob, int len)
153 r = sqlite3_bind_blob(stmt, col, blob, len, SQLITE_STATIC);
155 r = sqlite3_bind_null(stmt, col);
163 db = sqlite3_db_handle(stmt);
164 err = sqlite3_errmsg(db);
165 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
171 lms_db_bind_int64(sqlite3_stmt *stmt, int col, int64_t value)
175 r = sqlite3_bind_int64(stmt, col, value);
182 db = sqlite3_db_handle(stmt);
183 err = sqlite3_errmsg(db);
184 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
190 lms_db_bind_int64_or_null(sqlite3_stmt *stmt, int col, int64_t *p_value)
195 r = sqlite3_bind_int64(stmt, col, *p_value);
197 r = sqlite3_bind_null(stmt, col);
204 db = sqlite3_db_handle(stmt);
205 err = sqlite3_errmsg(db);
206 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
212 lms_db_bind_int(sqlite3_stmt *stmt, int col, int value)
216 r = sqlite3_bind_int(stmt, col, value);
223 db = sqlite3_db_handle(stmt);
224 err = sqlite3_errmsg(db);
225 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
231 lms_db_bind_double(sqlite3_stmt *stmt, int col, double value)
235 r = sqlite3_bind_double(stmt, col, value);
242 db = sqlite3_db_handle(stmt);
243 err = sqlite3_errmsg(db);
244 fprintf(stderr, "ERROR: could not bind SQL value %d: %s\n", col, err);
250 lms_db_table_version_get(sqlite3 *db, const char *table)
255 stmt = lms_db_compile_stmt(db,
256 "SELECT version FROM lms_internal WHERE tab = ?");
260 if (lms_db_bind_text(stmt, 1, table, -1) != 0) {
265 r = sqlite3_step(stmt);
266 if (r == SQLITE_DONE)
268 else if (r == SQLITE_ROW)
269 version = sqlite3_column_int(stmt, 1);
272 fprintf(stderr, "ERROR: could not get table '%s' version: %s\n",
273 table, sqlite3_errmsg(db));
277 lms_db_reset_stmt(stmt);
278 lms_db_finalize_stmt(stmt, "table_version_get");
284 lms_db_table_version_set(sqlite3 *db, const char *table, unsigned int version)
289 stmt = lms_db_compile_stmt(db,
290 "INSERT OR REPLACE INTO lms_internal (tab, version) VALUES (?, ?)");
294 ret = lms_db_bind_text(stmt, 1, table, -1);
298 ret = lms_db_bind_int(stmt, 2, version);
302 r = sqlite3_step(stmt);
303 if (r != SQLITE_DONE) {
305 fprintf(stderr, "ERROR: could not set table '%s' version: %s\n",
306 table, sqlite3_errmsg(db));
310 lms_db_reset_stmt(stmt);
311 lms_db_finalize_stmt(stmt, "table_version_set");
317 lms_db_table_update(sqlite3 *db, const char *table, unsigned int current_version, unsigned int last_version, const lms_db_table_updater_t *updaters)
319 if (current_version == last_version)
321 else if (current_version > last_version) {
323 "WARNING: current version (%d) of table '%s' is greater than "
324 "last known version (%d), no updates will be made.\n",
325 current_version, table, last_version);
329 for (; current_version < last_version; current_version++) {
332 is_last_run = current_version == (last_version - 1);
333 r = updaters[current_version](db, table, current_version, is_last_run);
336 "ERROR: could not update table '%s' from version %d->%d\n",
337 table, current_version, current_version + 1);
340 lms_db_table_version_set(db, table, current_version + 1);
347 lms_db_table_update_if_required(sqlite3 *db, const char *table, unsigned int last_version, lms_db_table_updater_t *updaters)
351 current_version = lms_db_table_version_get(db, table);
352 if (current_version < 0)
355 return lms_db_table_update(db, table, current_version, last_version,
360 lms_db_cache_find_db(const struct lms_db_cache *cache, const sqlite3 *db)
364 for (i = 0; i < cache->size; i++)
365 if (cache->entries[i].db == db)
372 lms_db_cache_resize(struct lms_db_cache *cache, int new_size)
374 cache->size = new_size;
375 cache->entries = realloc(cache->entries,
376 cache->size * sizeof(*cache->entries));
377 if (cache->size && !cache->entries) {
387 lms_db_cache_add(struct lms_db_cache *cache, const sqlite3 *db, void *data)
389 struct lms_db_cache_entry *e;
392 idx = lms_db_cache_find_db(cache, db);
394 e = cache->entries + idx;
399 "ERROR: cache %p for db %p has another data registered"
400 ": %p (current is %p)\n", cache, db, e->data, data);
406 if (lms_db_cache_resize(cache, cache->size + 1) != 0) {
410 e = cache->entries + idx;
417 lms_db_cache_del(struct lms_db_cache *cache, const sqlite3 *db, void *data)
420 struct lms_db_cache_entry *e;
422 idx = lms_db_cache_find_db(cache, db);
424 fprintf(stderr, "ERROR: no db %p found in cache %p\n", db, cache);
428 e = cache->entries + idx;
429 if (e->data != data) {
430 fprintf(stderr, "ERROR: data mismatch in request to delete from cache: "
431 "want %p, has %p, cache %p, db %p\n", data, e->data, cache, db);
435 for (; idx < cache->size - 1; idx++)
436 cache->entries[idx] = cache->entries[idx + 1];
438 return lms_db_cache_resize(cache, cache->size - 1);
442 lms_db_cache_get(struct lms_db_cache *cache, const sqlite3 *db, void **pdata)
446 idx = lms_db_cache_find_db(cache, db);
450 *pdata = cache->entries[idx].data;
455 lms_db_create_core_tables_if_required(sqlite3 *db)
462 "CREATE TABLE IF NOT EXISTS lms_internal ("
463 "tab TEXT NOT NULL UNIQUE, "
464 "version INTEGER NOT NULL"
466 NULL, NULL, &errmsg);
467 if (r != SQLITE_OK) {
468 fprintf(stderr, "ERROR: could not create 'lms_internal' table: %s\n",
470 sqlite3_free(errmsg);
475 "CREATE TABLE IF NOT EXISTS files ("
476 "id INTEGER PRIMARY KEY AUTOINCREMENT, "
477 "path BLOB NOT NULL UNIQUE, "
478 "mtime INTEGER NOT NULL, "
479 "dtime INTEGER NOT NULL, "
480 "size INTEGER NOT NULL"
482 NULL, NULL, &errmsg);
483 if (r != SQLITE_OK) {
484 fprintf(stderr, "ERROR: could not create 'files' table: %s\n", errmsg);
485 sqlite3_free(errmsg);
490 "CREATE INDEX IF NOT EXISTS files_path_idx ON files ("
493 NULL, NULL, &errmsg);
494 if (r != SQLITE_OK) {
495 fprintf(stderr, "ERROR: could not create 'files_path_idx' index: %s\n",
497 sqlite3_free(errmsg);
506 lms_db_compile_stmt_begin_transaction(sqlite3 *db)
508 return lms_db_compile_stmt(db, "BEGIN TRANSACTION");
512 lms_db_begin_transaction(sqlite3_stmt *stmt)
517 r = sqlite3_step(stmt);
518 if (r != SQLITE_DONE) {
519 fprintf(stderr, "ERROR: could not begin transaction: %s\n",
520 sqlite3_errmsg(sqlite3_db_handle(stmt)));
524 r = sqlite3_reset(stmt);
526 fprintf(stderr, "ERROR: could not reset SQL statement: %s\n",
527 sqlite3_errmsg(sqlite3_db_handle(stmt)));
533 lms_db_compile_stmt_end_transaction(sqlite3 *db)
535 return lms_db_compile_stmt(db, "COMMIT");
539 lms_db_end_transaction(sqlite3_stmt *stmt)
544 r = sqlite3_step(stmt);
545 if (r != SQLITE_DONE) {
546 fprintf(stderr, "ERROR: could not end transaction: %s\n",
547 sqlite3_errmsg(sqlite3_db_handle(stmt)));
551 r = sqlite3_reset(stmt);
553 fprintf(stderr, "ERROR: could not reset SQL statement: %s\n",
554 sqlite3_errmsg(sqlite3_db_handle(stmt)));
560 lms_db_compile_stmt_get_file_info(sqlite3 *db)
562 return lms_db_compile_stmt(db,
563 "SELECT id, mtime, dtime, size FROM files WHERE path = ?");
567 lms_db_get_file_info(sqlite3_stmt *stmt, struct lms_file_info *finfo)
571 ret = lms_db_bind_blob(stmt, 1, finfo->path, finfo->path_len);
575 r = sqlite3_step(stmt);
576 if (r == SQLITE_DONE) {
582 if (r != SQLITE_ROW) {
583 fprintf(stderr, "ERROR: could not get file info from table: %s\n",
584 sqlite3_errmsg(sqlite3_db_handle(stmt)));
589 finfo->id = sqlite3_column_int64(stmt, 0);
590 finfo->mtime = sqlite3_column_int(stmt, 1);
591 finfo->dtime = sqlite3_column_int(stmt, 2);
592 finfo->size = sqlite3_column_int(stmt, 3);
596 lms_db_reset_stmt(stmt);
602 lms_db_compile_stmt_update_file_info(sqlite3 *db)
604 return lms_db_compile_stmt(db,
605 "UPDATE files SET mtime = ?, dtime = ?, size = ? WHERE id = ?");
609 lms_db_update_file_info(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
613 ret = lms_db_bind_int(stmt, 1, finfo->mtime);
617 ret = lms_db_bind_int(stmt, 2, finfo->dtime);
621 ret = lms_db_bind_int(stmt, 3, finfo->size);
625 ret = lms_db_bind_int(stmt, 4, finfo->id);
629 r = sqlite3_step(stmt);
630 if (r != SQLITE_DONE) {
631 fprintf(stderr, "ERROR: could not update file info: %s\n",
632 sqlite3_errmsg(sqlite3_db_handle(stmt)));
640 lms_db_reset_stmt(stmt);
646 lms_db_compile_stmt_insert_file_info(sqlite3 *db)
648 return lms_db_compile_stmt(db,
649 "INSERT INTO files (path, mtime, dtime, size) VALUES(?, ?, ?, ?)");
653 lms_db_insert_file_info(sqlite3_stmt *stmt, struct lms_file_info *finfo)
657 ret = lms_db_bind_blob(stmt, 1, finfo->path, finfo->path_len);
661 ret = lms_db_bind_int(stmt, 2, finfo->mtime);
665 ret = lms_db_bind_int(stmt, 3, finfo->dtime);
669 ret = lms_db_bind_int(stmt, 4, finfo->size);
673 r = sqlite3_step(stmt);
674 if (r != SQLITE_DONE) {
675 fprintf(stderr, "ERROR: could not insert file info: %s\n",
676 sqlite3_errmsg(sqlite3_db_handle(stmt)));
681 finfo->id = sqlite3_last_insert_rowid(sqlite3_db_handle(stmt));
685 lms_db_reset_stmt(stmt);
691 lms_db_compile_stmt_delete_file_info(sqlite3 *db)
693 return lms_db_compile_stmt(db, "DELETE FROM files WHERE id = ?");
697 lms_db_delete_file_info(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
701 ret = lms_db_bind_int64(stmt, 1, finfo->id);
705 r = sqlite3_step(stmt);
706 if (r != SQLITE_DONE) {
707 fprintf(stderr, "ERROR: could not delete file info: %s\n",
708 sqlite3_errmsg(sqlite3_db_handle(stmt)));
715 lms_db_reset_stmt(stmt);
721 lms_db_compile_stmt_set_file_dtime(sqlite3 *db)
723 return lms_db_compile_stmt(db, "UPDATE files SET dtime = ? WHERE id = ?");
727 lms_db_set_file_dtime(sqlite3_stmt *stmt, const struct lms_file_info *finfo)
731 ret = lms_db_bind_int(stmt, 1, finfo->dtime);
735 ret = lms_db_bind_int64(stmt, 1, finfo->id);
739 r = sqlite3_step(stmt);
740 if (r != SQLITE_DONE) {
741 fprintf(stderr, "ERROR: could not set file dtime: %s\n",
742 sqlite3_errmsg(sqlite3_db_handle(stmt)));
750 lms_db_reset_stmt(stmt);
756 lms_db_compile_stmt_get_files(sqlite3 *db)
758 return lms_db_compile_stmt(db,
759 "SELECT id, path, mtime, dtime, size FROM files WHERE path LIKE ?");
763 lms_db_get_files(sqlite3_stmt *stmt, const char *path, int len)
767 ret = lms_db_bind_blob(stmt, 1, path, len);