1 #include <lightmediascanner_db.h>
2 #include "lightmediascanner_db_private.h"
8 sqlite3_stmt *insert_audio;
9 sqlite3_stmt *insert_artist;
10 sqlite3_stmt *insert_album;
11 sqlite3_stmt *insert_genre;
12 sqlite3_stmt *get_artist;
13 sqlite3_stmt *get_album;
14 sqlite3_stmt *get_genre;
15 unsigned int _references;
16 unsigned int _is_started:1;
19 static struct lms_db_cache _cache = {0, NULL};
22 _db_create(sqlite3 *db, const char *name, const char *sql)
27 r = sqlite3_exec(db, sql, NULL, NULL, &err);
29 fprintf(stderr, "ERROR: could not create \"%s\": %s\n", name, err);
38 _db_table_updater_audios_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
41 ret = _db_create(db, "audios",
42 "CREATE TABLE IF NOT EXISTS audios ("
43 "id INTEGER PRIMARY KEY, "
54 ret = _db_create(db, "audios_title_idx",
55 "CREATE INDEX IF NOT EXISTS "
56 "audios_title_idx ON audios (title)");
60 ret = _db_create(db, "audios_album_idx",
61 "CREATE INDEX IF NOT EXISTS "
62 "audios_album_idx ON audios (album_id)");
66 ret = _db_create(db, "audios_genre_idx",
67 "CREATE INDEX IF NOT EXISTS "
68 "audios_genre_idx ON audios (genre_id)");
72 ret = _db_create(db, "audios_trackno_idx",
73 "CREATE INDEX IF NOT EXISTS "
74 "audios_trackno_idx ON audios (trackno)");
78 ret = _db_create(db, "audios_playcnt_idx",
79 "CREATE INDEX IF NOT EXISTS "
80 "audios_playcnt_idx ON audios (playcnt)");
84 ret = lms_db_create_trigger_if_not_exists(db,
85 "delete_audios_on_files_deleted "
86 "DELETE ON files FOR EACH ROW BEGIN"
87 " DELETE FROM audios WHERE id = OLD.id; END;");
91 ret = lms_db_create_trigger_if_not_exists(db,
92 "delete_files_on_audios_deleted "
93 "DELETE ON audios FOR EACH ROW BEGIN"
94 " DELETE FROM files WHERE id = OLD.id; END;");
100 static lms_db_table_updater_t _db_table_updater_audios[] = {
101 _db_table_updater_audios_0
105 _db_table_updater_audio_artists_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
108 ret = _db_create(db, "audio_artists",
109 "CREATE TABLE IF NOT EXISTS audio_artists ("
110 "id INTEGER PRIMARY KEY, "
116 ret = _db_create(db, "audio_artists_name_idx",
117 "CREATE INDEX IF NOT EXISTS "
118 "audio_artists_name_idx ON audio_artists (name)");
124 static lms_db_table_updater_t _db_table_updater_audio_artists[] = {
125 _db_table_updater_audio_artists_0
129 _db_table_updater_audio_albums_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
132 ret = _db_create(db, "audio_albums",
133 "CREATE TABLE IF NOT EXISTS audio_albums ("
134 "id INTEGER PRIMARY KEY, "
135 "artist_id INTEGER, "
141 ret = _db_create(db, "audio_albums_name_idx",
142 "CREATE INDEX IF NOT EXISTS "
143 "audio_albums_name_idx ON audio_albums (name)");
147 ret = _db_create(db, "audio_albums_artist_idx",
148 "CREATE INDEX IF NOT EXISTS "
149 "audio_albums_artist_idx ON audio_albums (artist_id)");
153 ret = lms_db_create_trigger_if_not_exists(db,
154 "delete_audios_on_albums_deleted "
155 "DELETE ON audio_albums FOR EACH ROW BEGIN"
156 " DELETE FROM audios WHERE album_id = OLD.id; END;");
160 ret = lms_db_create_trigger_if_not_exists(db,
161 "delete_audio_albums_on_artists_deleted "
162 "DELETE ON audio_artists FOR EACH ROW BEGIN"
163 " DELETE FROM audio_albums WHERE artist_id = OLD.id; END;");
169 static lms_db_table_updater_t _db_table_updater_audio_albums[] = {
170 _db_table_updater_audio_albums_0
174 _db_table_updater_audio_genres_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
177 ret = _db_create(db, "audio_genres",
178 "CREATE TABLE IF NOT EXISTS audio_genres ("
179 "id INTEGER PRIMARY KEY, "
185 ret = _db_create(db, "audio_genres_name_idx",
186 "CREATE INDEX IF NOT EXISTS "
187 "audio_albums_name_idx ON audio_albums (name)");
191 ret = lms_db_create_trigger_if_not_exists(db,
192 "delete_audios_on_genres_deleted "
193 "DELETE ON audio_genres FOR EACH ROW BEGIN"
194 " DELETE FROM audios WHERE genre_id = OLD.id; END;");
200 static lms_db_table_updater_t _db_table_updater_audio_genres[] = {
201 _db_table_updater_audio_genres_0
204 #define _DB_T_UPDATE(db, name, array) \
205 lms_db_table_update_if_required(db, name, LMS_ARRAY_SIZE(array), array)
208 _db_create_tables_if_required(sqlite3 *db)
212 ret = _DB_T_UPDATE(db, "audios", _db_table_updater_audios);
216 ret = _DB_T_UPDATE(db, "audio_artists", _db_table_updater_audio_artists);
220 ret = _DB_T_UPDATE(db, "audio_albums", _db_table_updater_audio_albums);
224 ret = _DB_T_UPDATE(db, "audio_genres", _db_table_updater_audio_genres);
233 * Create audio DB access tool.
235 * Creates or get a reference to tools to access 'audios' table in an
236 * optimized and easy way.
238 * This is usually called from plugin's @b setup() callback with the @p db
241 * @param db database connection.
243 * @return DB access tool handle.
244 * @ingroup LMS_Plugins
247 lms_db_audio_new(sqlite3 *db)
252 if (lms_db_cache_get(&_cache, db, &p) == 0) {
261 if (_db_create_tables_if_required(db) != 0) {
262 fprintf(stderr, "ERROR: could not create tables.\n");
266 lda = calloc(1, sizeof(lms_db_audio_t));
267 lda->_references = 1;
270 if (lms_db_cache_add(&_cache, db, lda) != 0) {
271 lms_db_audio_free(lda);
279 * Start audio DB access tool.
281 * Compile SQL statements and other initialization functions.
283 * This is usually called from plugin's @b start() callback.
285 * @param lda handle returned by lms_db_audio_new().
287 * @return On success 0 is returned.
288 * @ingroup LMS_Plugins
291 lms_db_audio_start(lms_db_audio_t *lda)
295 if (lda->_is_started)
298 lda->insert_audio = lms_db_compile_stmt(lda->db,
299 "INSERT OR REPLACE INTO audios "
300 "(id, title, album_id, genre_id, trackno, rating, playcnt) "
301 "VALUES (?, ?, ?, ?, ?, ?, ?)");
302 if (!lda->insert_audio)
305 lda->insert_artist = lms_db_compile_stmt(lda->db,
306 "INSERT INTO audio_artists (name) VALUES (?)");
307 if (!lda->insert_artist)
310 lda->insert_album = lms_db_compile_stmt(lda->db,
311 "INSERT INTO audio_albums (artist_id, name) VALUES (?, ?)");
312 if (!lda->insert_album)
315 lda->insert_genre = lms_db_compile_stmt(lda->db,
316 "INSERT INTO audio_genres (name) VALUES (?)");
317 if (!lda->insert_genre)
320 lda->get_artist = lms_db_compile_stmt(lda->db,
321 "SELECT id FROM audio_artists WHERE name = ? LIMIT 1");
322 if (!lda->get_artist)
325 lda->get_album = lms_db_compile_stmt(lda->db,
326 "SELECT id FROM audio_albums WHERE name = ? AND artist_id = ? LIMIT 1");
330 lda->get_genre = lms_db_compile_stmt(lda->db,
331 "SELECT id FROM audio_genres WHERE name = ? LIMIT 1");
335 lda->_is_started = 1;
340 * Free audio DB access tool.
342 * Unreference and possible free resources allocated to access tool.
344 * This is usually called from plugin's @b finish() callback.
346 * @param lda handle returned by lms_db_audio_new().
348 * @return On success 0 is returned.
349 * @ingroup LMS_Plugins
352 lms_db_audio_free(lms_db_audio_t *lda)
358 if (lda->_references == 0) {
359 fprintf(stderr, "ERROR: over-called lms_db_audio_free(%p)\n", lda);
364 if (lda->_references > 0)
367 if (lda->insert_audio)
368 lms_db_finalize_stmt(lda->insert_audio, "insert_audio");
370 if (lda->insert_artist)
371 lms_db_finalize_stmt(lda->insert_artist, "insert_artist");
373 if (lda->insert_album)
374 lms_db_finalize_stmt(lda->insert_album, "insert_album");
376 if (lda->insert_genre)
377 lms_db_finalize_stmt(lda->insert_genre, "insert_genre");
380 lms_db_finalize_stmt(lda->get_artist, "get_artist");
383 lms_db_finalize_stmt(lda->get_album, "get_album");
386 lms_db_finalize_stmt(lda->get_genre, "get_genre");
388 r = lms_db_cache_del(&_cache, lda->db, lda);
395 _db_get_id_by_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
399 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
403 r = sqlite3_step(stmt);
404 if (r == SQLITE_DONE) {
409 if (r != SQLITE_ROW) {
410 fprintf(stderr, "ERROR: could not get id by name: %s\n",
411 sqlite3_errmsg(sqlite3_db_handle(stmt)));
416 *id = sqlite3_column_int64(stmt, 0);
420 lms_db_reset_stmt(stmt);
426 _db_insert_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
430 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
434 r = sqlite3_step(stmt);
435 if (r != SQLITE_DONE) {
436 fprintf(stderr, "ERROR: could not insert name: %s\n",
437 sqlite3_errmsg(sqlite3_db_handle(stmt)));
442 *id = sqlite3_last_insert_rowid(sqlite3_db_handle(stmt));
446 lms_db_reset_stmt(stmt);
452 _db_get_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
454 return _db_get_id_by_name(lda->get_artist, &info->artist, artist_id);
458 _db_insert_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
462 if (!info->artist.str) /* fast path for unknown artist */
465 r =_db_get_artist(lda, info, artist_id);
471 return _db_insert_name(lda->insert_artist, &info->artist, artist_id);
475 _db_get_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id, int64_t *album_id)
480 stmt = lda->get_album;
482 ret = lms_db_bind_text(stmt, 1, info->album.str, info->album.len);
486 ret = lms_db_bind_int64_or_null(stmt, 2, artist_id);
490 r = sqlite3_step(stmt);
491 if (r == SQLITE_DONE) {
496 if (r != SQLITE_ROW) {
497 fprintf(stderr, "ERROR: could not get album from table: %s\n",
498 sqlite3_errmsg(lda->db));
503 *album_id = sqlite3_column_int64(stmt, 0);
507 lms_db_reset_stmt(stmt);
514 _db_insert_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id)
516 int r, ret, ret_artist;
520 if (!info->album.str) /* fast path for unknown album */
523 ret_artist = _db_insert_artist(lda, info, &artist_id);
527 r =_db_get_album(lda, info,
528 (ret_artist == 0) ? &artist_id : NULL,
535 stmt = lda->insert_album;
536 ret = lms_db_bind_int64_or_null(stmt, 1,
537 (ret_artist == 0) ? &artist_id : NULL);
541 ret = lms_db_bind_text(stmt, 2, info->album.str, info->album.len);
545 r = sqlite3_step(stmt);
546 if (r != SQLITE_DONE) {
547 fprintf(stderr, "ERROR: could not insert audio album: %s\n",
548 sqlite3_errmsg(lda->db));
553 *album_id = sqlite3_last_insert_rowid(lda->db);
557 lms_db_reset_stmt(stmt);
563 _db_get_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
565 return _db_get_id_by_name(lda->get_genre, &info->genre, genre_id);
569 _db_insert_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
573 if (!info->genre.str) /* fast path for unknown genre */
576 r =_db_get_genre(lda, info, genre_id);
582 return _db_insert_name(lda->insert_genre, &info->genre, genre_id);
586 _db_insert_audio(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id, int64_t *genre_id)
591 stmt = lda->insert_audio;
592 ret = lms_db_bind_int64(stmt, 1, info->id);
596 ret = lms_db_bind_text(stmt, 2, info->title.str, info->title.len);
600 ret = lms_db_bind_int64_or_null(stmt, 3, album_id);
604 ret = lms_db_bind_int64_or_null(stmt, 4, genre_id);
608 ret = lms_db_bind_int(stmt, 5, info->trackno);
612 ret = lms_db_bind_int(stmt, 6, info->rating);
616 ret = lms_db_bind_int(stmt, 7, info->playcnt);
620 r = sqlite3_step(stmt);
621 if (r != SQLITE_DONE) {
622 fprintf(stderr, "ERROR: could not insert audio info: %s\n",
623 sqlite3_errmsg(lda->db));
631 lms_db_reset_stmt(stmt);
637 * Add audio file to DB.
639 * This is usually called from plugin's @b parse() callback.
641 * @param lda handle returned by lms_db_audio_new().
642 * @param info audio information to store.
644 * @return On success 0 is returned.
645 * @ingroup LMS_Plugins
648 lms_db_audio_add(lms_db_audio_t *lda, struct lms_audio_info *info)
650 int64_t album_id, genre_id;
651 int ret_album, ret_genre;
660 ret_album = _db_insert_album(lda, info, &album_id);
664 ret_genre = _db_insert_genre(lda, info, &genre_id);
668 return _db_insert_audio(lda, info,
669 (ret_album == 0) ? &album_id : NULL,
670 (ret_genre == 0) ? &genre_id : NULL);