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>
20 #include <lightmediascanner_db.h>
21 #include "lightmediascanner_db_private.h"
27 sqlite3_stmt *insert_audio;
28 sqlite3_stmt *insert_artist;
29 sqlite3_stmt *insert_album;
30 sqlite3_stmt *insert_genre;
31 sqlite3_stmt *get_artist;
32 sqlite3_stmt *get_album;
33 sqlite3_stmt *get_genre;
34 unsigned int _references;
35 unsigned int _is_started:1;
38 static struct lms_db_cache _cache = {0, NULL};
41 _db_create(sqlite3 *db, const char *name, const char *sql)
46 r = sqlite3_exec(db, sql, NULL, NULL, &err);
48 fprintf(stderr, "ERROR: could not create \"%s\": %s\n", name, err);
57 _db_table_updater_audios_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
60 ret = _db_create(db, "audios",
61 "CREATE TABLE IF NOT EXISTS audios ("
62 "id INTEGER PRIMARY KEY, "
73 ret = _db_create(db, "audios_title_idx",
74 "CREATE INDEX IF NOT EXISTS "
75 "audios_title_idx ON audios (title)");
79 ret = _db_create(db, "audios_album_idx",
80 "CREATE INDEX IF NOT EXISTS "
81 "audios_album_idx ON audios (album_id)");
85 ret = _db_create(db, "audios_genre_idx",
86 "CREATE INDEX IF NOT EXISTS "
87 "audios_genre_idx ON audios (genre_id)");
91 ret = _db_create(db, "audios_trackno_idx",
92 "CREATE INDEX IF NOT EXISTS "
93 "audios_trackno_idx ON audios (trackno)");
97 ret = _db_create(db, "audios_playcnt_idx",
98 "CREATE INDEX IF NOT EXISTS "
99 "audios_playcnt_idx ON audios (playcnt)");
103 ret = lms_db_create_trigger_if_not_exists(db,
104 "delete_audios_on_files_deleted "
105 "DELETE ON files FOR EACH ROW BEGIN"
106 " DELETE FROM audios WHERE id = OLD.id; END;");
110 ret = lms_db_create_trigger_if_not_exists(db,
111 "delete_files_on_audios_deleted "
112 "DELETE ON audios FOR EACH ROW BEGIN"
113 " DELETE FROM files WHERE id = OLD.id; END;");
119 static lms_db_table_updater_t _db_table_updater_audios[] = {
120 _db_table_updater_audios_0
124 _db_table_updater_audio_artists_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
127 ret = _db_create(db, "audio_artists",
128 "CREATE TABLE IF NOT EXISTS audio_artists ("
129 "id INTEGER PRIMARY KEY, "
135 ret = _db_create(db, "audio_artists_name_idx",
136 "CREATE INDEX IF NOT EXISTS "
137 "audio_artists_name_idx ON audio_artists (name)");
143 static lms_db_table_updater_t _db_table_updater_audio_artists[] = {
144 _db_table_updater_audio_artists_0
148 _db_table_updater_audio_albums_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
151 ret = _db_create(db, "audio_albums",
152 "CREATE TABLE IF NOT EXISTS audio_albums ("
153 "id INTEGER PRIMARY KEY, "
154 "artist_id INTEGER, "
160 ret = _db_create(db, "audio_albums_name_idx",
161 "CREATE INDEX IF NOT EXISTS "
162 "audio_albums_name_idx ON audio_albums (name)");
166 ret = _db_create(db, "audio_albums_artist_idx",
167 "CREATE INDEX IF NOT EXISTS "
168 "audio_albums_artist_idx ON audio_albums (artist_id)");
172 ret = lms_db_create_trigger_if_not_exists(db,
173 "delete_audios_on_albums_deleted "
174 "DELETE ON audio_albums FOR EACH ROW BEGIN"
175 " DELETE FROM audios WHERE album_id = OLD.id; END;");
179 ret = lms_db_create_trigger_if_not_exists(db,
180 "delete_audio_albums_on_artists_deleted "
181 "DELETE ON audio_artists FOR EACH ROW BEGIN"
182 " DELETE FROM audio_albums WHERE artist_id = OLD.id; END;");
188 static lms_db_table_updater_t _db_table_updater_audio_albums[] = {
189 _db_table_updater_audio_albums_0
193 _db_table_updater_audio_genres_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
196 ret = _db_create(db, "audio_genres",
197 "CREATE TABLE IF NOT EXISTS audio_genres ("
198 "id INTEGER PRIMARY KEY, "
204 ret = _db_create(db, "audio_genres_name_idx",
205 "CREATE INDEX IF NOT EXISTS "
206 "audio_albums_name_idx ON audio_albums (name)");
210 ret = lms_db_create_trigger_if_not_exists(db,
211 "delete_audios_on_genres_deleted "
212 "DELETE ON audio_genres FOR EACH ROW BEGIN"
213 " DELETE FROM audios WHERE genre_id = OLD.id; END;");
219 static lms_db_table_updater_t _db_table_updater_audio_genres[] = {
220 _db_table_updater_audio_genres_0
223 #define _DB_T_UPDATE(db, name, array) \
224 lms_db_table_update_if_required(db, name, LMS_ARRAY_SIZE(array), array)
227 _db_create_tables_if_required(sqlite3 *db)
231 ret = _DB_T_UPDATE(db, "audios", _db_table_updater_audios);
235 ret = _DB_T_UPDATE(db, "audio_artists", _db_table_updater_audio_artists);
239 ret = _DB_T_UPDATE(db, "audio_albums", _db_table_updater_audio_albums);
243 ret = _DB_T_UPDATE(db, "audio_genres", _db_table_updater_audio_genres);
252 * Create audio DB access tool.
254 * Creates or get a reference to tools to access 'audios' table in an
255 * optimized and easy way.
257 * This is usually called from plugin's @b setup() callback with the @p db
260 * @param db database connection.
262 * @return DB access tool handle.
263 * @ingroup LMS_Plugins
266 lms_db_audio_new(sqlite3 *db)
271 if (lms_db_cache_get(&_cache, db, &p) == 0) {
280 if (_db_create_tables_if_required(db) != 0) {
281 fprintf(stderr, "ERROR: could not create tables.\n");
285 lda = calloc(1, sizeof(lms_db_audio_t));
286 lda->_references = 1;
289 if (lms_db_cache_add(&_cache, db, lda) != 0) {
290 lms_db_audio_free(lda);
298 * Start audio DB access tool.
300 * Compile SQL statements and other initialization functions.
302 * This is usually called from plugin's @b start() callback.
304 * @param lda handle returned by lms_db_audio_new().
306 * @return On success 0 is returned.
307 * @ingroup LMS_Plugins
310 lms_db_audio_start(lms_db_audio_t *lda)
314 if (lda->_is_started)
317 lda->insert_audio = lms_db_compile_stmt(lda->db,
318 "INSERT OR REPLACE INTO audios "
319 "(id, title, album_id, genre_id, trackno, rating, playcnt) "
320 "VALUES (?, ?, ?, ?, ?, ?, ?)");
321 if (!lda->insert_audio)
324 lda->insert_artist = lms_db_compile_stmt(lda->db,
325 "INSERT INTO audio_artists (name) VALUES (?)");
326 if (!lda->insert_artist)
329 lda->insert_album = lms_db_compile_stmt(lda->db,
330 "INSERT INTO audio_albums (artist_id, name) VALUES (?, ?)");
331 if (!lda->insert_album)
334 lda->insert_genre = lms_db_compile_stmt(lda->db,
335 "INSERT INTO audio_genres (name) VALUES (?)");
336 if (!lda->insert_genre)
339 lda->get_artist = lms_db_compile_stmt(lda->db,
340 "SELECT id FROM audio_artists WHERE name = ? LIMIT 1");
341 if (!lda->get_artist)
344 lda->get_album = lms_db_compile_stmt(lda->db,
345 "SELECT id FROM audio_albums WHERE name = ? AND artist_id = ? LIMIT 1");
349 lda->get_genre = lms_db_compile_stmt(lda->db,
350 "SELECT id FROM audio_genres WHERE name = ? LIMIT 1");
354 lda->_is_started = 1;
359 * Free audio DB access tool.
361 * Unreference and possible free resources allocated to access tool.
363 * This is usually called from plugin's @b finish() callback.
365 * @param lda handle returned by lms_db_audio_new().
367 * @return On success 0 is returned.
368 * @ingroup LMS_Plugins
371 lms_db_audio_free(lms_db_audio_t *lda)
377 if (lda->_references == 0) {
378 fprintf(stderr, "ERROR: over-called lms_db_audio_free(%p)\n", lda);
383 if (lda->_references > 0)
386 if (lda->insert_audio)
387 lms_db_finalize_stmt(lda->insert_audio, "insert_audio");
389 if (lda->insert_artist)
390 lms_db_finalize_stmt(lda->insert_artist, "insert_artist");
392 if (lda->insert_album)
393 lms_db_finalize_stmt(lda->insert_album, "insert_album");
395 if (lda->insert_genre)
396 lms_db_finalize_stmt(lda->insert_genre, "insert_genre");
399 lms_db_finalize_stmt(lda->get_artist, "get_artist");
402 lms_db_finalize_stmt(lda->get_album, "get_album");
405 lms_db_finalize_stmt(lda->get_genre, "get_genre");
407 r = lms_db_cache_del(&_cache, lda->db, lda);
414 _db_get_id_by_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
418 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
422 r = sqlite3_step(stmt);
423 if (r == SQLITE_DONE) {
428 if (r != SQLITE_ROW) {
429 fprintf(stderr, "ERROR: could not get id by name: %s\n",
430 sqlite3_errmsg(sqlite3_db_handle(stmt)));
435 *id = sqlite3_column_int64(stmt, 0);
439 lms_db_reset_stmt(stmt);
445 _db_insert_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
449 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
453 r = sqlite3_step(stmt);
454 if (r != SQLITE_DONE) {
455 fprintf(stderr, "ERROR: could not insert name: %s\n",
456 sqlite3_errmsg(sqlite3_db_handle(stmt)));
461 *id = sqlite3_last_insert_rowid(sqlite3_db_handle(stmt));
465 lms_db_reset_stmt(stmt);
471 _db_get_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
473 return _db_get_id_by_name(lda->get_artist, &info->artist, artist_id);
477 _db_insert_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
481 if (!info->artist.str) /* fast path for unknown artist */
484 r =_db_get_artist(lda, info, artist_id);
490 return _db_insert_name(lda->insert_artist, &info->artist, artist_id);
494 _db_get_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id, int64_t *album_id)
499 stmt = lda->get_album;
501 ret = lms_db_bind_text(stmt, 1, info->album.str, info->album.len);
505 ret = lms_db_bind_int64_or_null(stmt, 2, artist_id);
509 r = sqlite3_step(stmt);
510 if (r == SQLITE_DONE) {
515 if (r != SQLITE_ROW) {
516 fprintf(stderr, "ERROR: could not get album from table: %s\n",
517 sqlite3_errmsg(lda->db));
522 *album_id = sqlite3_column_int64(stmt, 0);
526 lms_db_reset_stmt(stmt);
533 _db_insert_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id)
535 int r, ret, ret_artist;
539 if (!info->album.str) /* fast path for unknown album */
542 ret_artist = _db_insert_artist(lda, info, &artist_id);
546 r =_db_get_album(lda, info,
547 (ret_artist == 0) ? &artist_id : NULL,
554 stmt = lda->insert_album;
555 ret = lms_db_bind_int64_or_null(stmt, 1,
556 (ret_artist == 0) ? &artist_id : NULL);
560 ret = lms_db_bind_text(stmt, 2, info->album.str, info->album.len);
564 r = sqlite3_step(stmt);
565 if (r != SQLITE_DONE) {
566 fprintf(stderr, "ERROR: could not insert audio album: %s\n",
567 sqlite3_errmsg(lda->db));
572 *album_id = sqlite3_last_insert_rowid(lda->db);
576 lms_db_reset_stmt(stmt);
582 _db_get_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
584 return _db_get_id_by_name(lda->get_genre, &info->genre, genre_id);
588 _db_insert_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
592 if (!info->genre.str) /* fast path for unknown genre */
595 r =_db_get_genre(lda, info, genre_id);
601 return _db_insert_name(lda->insert_genre, &info->genre, genre_id);
605 _db_insert_audio(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id, int64_t *genre_id)
610 stmt = lda->insert_audio;
611 ret = lms_db_bind_int64(stmt, 1, info->id);
615 ret = lms_db_bind_text(stmt, 2, info->title.str, info->title.len);
619 ret = lms_db_bind_int64_or_null(stmt, 3, album_id);
623 ret = lms_db_bind_int64_or_null(stmt, 4, genre_id);
627 ret = lms_db_bind_int(stmt, 5, info->trackno);
631 ret = lms_db_bind_int(stmt, 6, info->rating);
635 ret = lms_db_bind_int(stmt, 7, info->playcnt);
639 r = sqlite3_step(stmt);
640 if (r != SQLITE_DONE) {
641 fprintf(stderr, "ERROR: could not insert audio info: %s\n",
642 sqlite3_errmsg(lda->db));
650 lms_db_reset_stmt(stmt);
656 * Add audio file to DB.
658 * This is usually called from plugin's @b parse() callback.
660 * @param lda handle returned by lms_db_audio_new().
661 * @param info audio information to store.
663 * @return On success 0 is returned.
664 * @ingroup LMS_Plugins
667 lms_db_audio_add(lms_db_audio_t *lda, struct lms_audio_info *info)
669 int64_t album_id, genre_id;
670 int ret_album, ret_genre;
679 ret_album = _db_insert_album(lda, info, &album_id);
683 ret_genre = _db_insert_genre(lda, info, &genre_id);
687 return _db_insert_audio(lda, info,
688 (ret_album == 0) ? &album_id : NULL,
689 (ret_genre == 0) ? &genre_id : NULL);