00001 #include <lightmediascanner_db.h>
00002 #include "lightmediascanner_db_private.h"
00003 #include <stdlib.h>
00004 #include <stdio.h>
00005
00006 struct lms_db_audio {
00007 sqlite3 *db;
00008 sqlite3_stmt *insert_audio;
00009 sqlite3_stmt *insert_artist;
00010 sqlite3_stmt *insert_album;
00011 sqlite3_stmt *insert_genre;
00012 sqlite3_stmt *get_artist;
00013 sqlite3_stmt *get_album;
00014 sqlite3_stmt *get_genre;
00015 unsigned int _references;
00016 unsigned int _is_started:1;
00017 };
00018
00019 static struct lms_db_cache _cache = {0, NULL};
00020
00021 static int
00022 _db_create(sqlite3 *db, const char *name, const char *sql)
00023 {
00024 char *err;
00025 int r;
00026
00027 r = sqlite3_exec(db, sql, NULL, NULL, &err);
00028 if (r != SQLITE_OK) {
00029 fprintf(stderr, "ERROR: could not create \"%s\": %s\n", name, err);
00030 sqlite3_free(err);
00031 return -1;
00032 }
00033
00034 return 0;
00035 }
00036
00037 static int
00038 _db_table_updater_audios_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
00039 int ret;
00040
00041 ret = _db_create(db, "audios",
00042 "CREATE TABLE IF NOT EXISTS audios ("
00043 "id INTEGER PRIMARY KEY, "
00044 "title TEXT, "
00045 "album_id INTEGER, "
00046 "genre_id INTEGER, "
00047 "trackno INTEGER, "
00048 "rating INTEGER, "
00049 "playcnt INTEGER"
00050 ")");
00051 if (ret != 0)
00052 goto done;
00053
00054 ret = _db_create(db, "audios_title_idx",
00055 "CREATE INDEX IF NOT EXISTS "
00056 "audios_title_idx ON audios (title)");
00057 if (ret != 0)
00058 goto done;
00059
00060 ret = _db_create(db, "audios_album_idx",
00061 "CREATE INDEX IF NOT EXISTS "
00062 "audios_album_idx ON audios (album_id)");
00063 if (ret != 0)
00064 goto done;
00065
00066 ret = _db_create(db, "audios_genre_idx",
00067 "CREATE INDEX IF NOT EXISTS "
00068 "audios_genre_idx ON audios (genre_id)");
00069 if (ret != 0)
00070 goto done;
00071
00072 ret = _db_create(db, "audios_trackno_idx",
00073 "CREATE INDEX IF NOT EXISTS "
00074 "audios_trackno_idx ON audios (trackno)");
00075 if (ret != 0)
00076 goto done;
00077
00078 ret = _db_create(db, "audios_playcnt_idx",
00079 "CREATE INDEX IF NOT EXISTS "
00080 "audios_playcnt_idx ON audios (playcnt)");
00081 if (ret != 0)
00082 goto done;
00083
00084 ret = lms_db_create_trigger_if_not_exists(db,
00085 "delete_audios_on_files_deleted "
00086 "DELETE ON files FOR EACH ROW BEGIN"
00087 " DELETE FROM audios WHERE id = OLD.id; END;");
00088 if (ret != 0)
00089 goto done;
00090
00091 ret = lms_db_create_trigger_if_not_exists(db,
00092 "delete_files_on_audios_deleted "
00093 "DELETE ON audios FOR EACH ROW BEGIN"
00094 " DELETE FROM files WHERE id = OLD.id; END;");
00095
00096 done:
00097 return ret;
00098 }
00099
00100 static lms_db_table_updater_t _db_table_updater_audios[] = {
00101 _db_table_updater_audios_0
00102 };
00103
00104 static int
00105 _db_table_updater_audio_artists_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
00106 int ret;
00107
00108 ret = _db_create(db, "audio_artists",
00109 "CREATE TABLE IF NOT EXISTS audio_artists ("
00110 "id INTEGER PRIMARY KEY, "
00111 "name TEXT UNIQUE"
00112 ")");
00113 if (ret != 0)
00114 goto done;
00115
00116 ret = _db_create(db, "audio_artists_name_idx",
00117 "CREATE INDEX IF NOT EXISTS "
00118 "audio_artists_name_idx ON audio_artists (name)");
00119
00120 done:
00121 return ret;
00122 }
00123
00124 static lms_db_table_updater_t _db_table_updater_audio_artists[] = {
00125 _db_table_updater_audio_artists_0
00126 };
00127
00128 static int
00129 _db_table_updater_audio_albums_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
00130 int ret;
00131
00132 ret = _db_create(db, "audio_albums",
00133 "CREATE TABLE IF NOT EXISTS audio_albums ("
00134 "id INTEGER PRIMARY KEY, "
00135 "artist_id INTEGER, "
00136 "name TEXT"
00137 ")");
00138 if (ret != 0)
00139 goto done;
00140
00141 ret = _db_create(db, "audio_albums_name_idx",
00142 "CREATE INDEX IF NOT EXISTS "
00143 "audio_albums_name_idx ON audio_albums (name)");
00144 if (ret != 0)
00145 goto done;
00146
00147 ret = _db_create(db, "audio_albums_artist_idx",
00148 "CREATE INDEX IF NOT EXISTS "
00149 "audio_albums_artist_idx ON audio_albums (artist_id)");
00150 if (ret != 0)
00151 goto done;
00152
00153 ret = lms_db_create_trigger_if_not_exists(db,
00154 "delete_audios_on_albums_deleted "
00155 "DELETE ON audio_albums FOR EACH ROW BEGIN"
00156 " DELETE FROM audios WHERE album_id = OLD.id; END;");
00157 if (ret != 0)
00158 goto done;
00159
00160 ret = lms_db_create_trigger_if_not_exists(db,
00161 "delete_audio_albums_on_artists_deleted "
00162 "DELETE ON audio_artists FOR EACH ROW BEGIN"
00163 " DELETE FROM audio_albums WHERE artist_id = OLD.id; END;");
00164
00165 done:
00166 return ret;
00167 }
00168
00169 static lms_db_table_updater_t _db_table_updater_audio_albums[] = {
00170 _db_table_updater_audio_albums_0
00171 };
00172
00173 static int
00174 _db_table_updater_audio_genres_0(sqlite3 *db, const char *table, unsigned int current_version, int is_last_run) {
00175 int ret;
00176
00177 ret = _db_create(db, "audio_genres",
00178 "CREATE TABLE IF NOT EXISTS audio_genres ("
00179 "id INTEGER PRIMARY KEY, "
00180 "name TEXT UNIQUE"
00181 ")");
00182 if (ret != 0)
00183 goto done;
00184
00185 ret = _db_create(db, "audio_genres_name_idx",
00186 "CREATE INDEX IF NOT EXISTS "
00187 "audio_albums_name_idx ON audio_albums (name)");
00188 if (ret != 0)
00189 goto done;
00190
00191 ret = lms_db_create_trigger_if_not_exists(db,
00192 "delete_audios_on_genres_deleted "
00193 "DELETE ON audio_genres FOR EACH ROW BEGIN"
00194 " DELETE FROM audios WHERE genre_id = OLD.id; END;");
00195
00196 done:
00197 return ret;
00198 }
00199
00200 static lms_db_table_updater_t _db_table_updater_audio_genres[] = {
00201 _db_table_updater_audio_genres_0
00202 };
00203
00204 #define _DB_T_UPDATE(db, name, array) \
00205 lms_db_table_update_if_required(db, name, LMS_ARRAY_SIZE(array), array)
00206
00207 static int
00208 _db_create_tables_if_required(sqlite3 *db)
00209 {
00210 int ret;
00211
00212 ret = _DB_T_UPDATE(db, "audios", _db_table_updater_audios);
00213 if (ret != 0)
00214 goto done;
00215
00216 ret = _DB_T_UPDATE(db, "audio_artists", _db_table_updater_audio_artists);
00217 if (ret != 0)
00218 goto done;
00219
00220 ret = _DB_T_UPDATE(db, "audio_albums", _db_table_updater_audio_albums);
00221 if (ret != 0)
00222 goto done;
00223
00224 ret = _DB_T_UPDATE(db, "audio_genres", _db_table_updater_audio_genres);
00225
00226 done:
00227 return ret;
00228 }
00229
00230 #undef _DB_T_UPDATE
00231
00246 lms_db_audio_t *
00247 lms_db_audio_new(sqlite3 *db)
00248 {
00249 lms_db_audio_t *lda;
00250 void *p;
00251
00252 if (lms_db_cache_get(&_cache, db, &p) == 0) {
00253 lda = p;
00254 lda->_references++;
00255 return lda;
00256 }
00257
00258 if (!db)
00259 return NULL;
00260
00261 if (_db_create_tables_if_required(db) != 0) {
00262 fprintf(stderr, "ERROR: could not create tables.\n");
00263 return NULL;
00264 }
00265
00266 lda = calloc(1, sizeof(lms_db_audio_t));
00267 lda->_references = 1;
00268 lda->db = db;
00269
00270 if (lms_db_cache_add(&_cache, db, lda) != 0) {
00271 lms_db_audio_free(lda);
00272 return NULL;
00273 }
00274
00275 return lda;
00276 }
00277
00290 int
00291 lms_db_audio_start(lms_db_audio_t *lda)
00292 {
00293 if (!lda)
00294 return -1;
00295 if (lda->_is_started)
00296 return 0;
00297
00298 lda->insert_audio = lms_db_compile_stmt(lda->db,
00299 "INSERT OR REPLACE INTO audios "
00300 "(id, title, album_id, genre_id, trackno, rating, playcnt) "
00301 "VALUES (?, ?, ?, ?, ?, ?, ?)");
00302 if (!lda->insert_audio)
00303 return -2;
00304
00305 lda->insert_artist = lms_db_compile_stmt(lda->db,
00306 "INSERT INTO audio_artists (name) VALUES (?)");
00307 if (!lda->insert_artist)
00308 return -3;
00309
00310 lda->insert_album = lms_db_compile_stmt(lda->db,
00311 "INSERT INTO audio_albums (artist_id, name) VALUES (?, ?)");
00312 if (!lda->insert_album)
00313 return -4;
00314
00315 lda->insert_genre = lms_db_compile_stmt(lda->db,
00316 "INSERT INTO audio_genres (name) VALUES (?)");
00317 if (!lda->insert_genre)
00318 return -5;
00319
00320 lda->get_artist = lms_db_compile_stmt(lda->db,
00321 "SELECT id FROM audio_artists WHERE name = ? LIMIT 1");
00322 if (!lda->get_artist)
00323 return -6;
00324
00325 lda->get_album = lms_db_compile_stmt(lda->db,
00326 "SELECT id FROM audio_albums WHERE name = ? AND artist_id = ? LIMIT 1");
00327 if (!lda->get_album)
00328 return -7;
00329
00330 lda->get_genre = lms_db_compile_stmt(lda->db,
00331 "SELECT id FROM audio_genres WHERE name = ? LIMIT 1");
00332 if (!lda->get_genre)
00333 return -8;
00334
00335 lda->_is_started = 1;
00336 return 0;
00337 }
00338
00351 int
00352 lms_db_audio_free(lms_db_audio_t *lda)
00353 {
00354 int r;
00355
00356 if (!lda)
00357 return -1;
00358 if (lda->_references == 0) {
00359 fprintf(stderr, "ERROR: over-called lms_db_audio_free(%p)\n", lda);
00360 return -1;
00361 }
00362
00363 lda->_references--;
00364 if (lda->_references > 0)
00365 return 0;
00366
00367 if (lda->insert_audio)
00368 lms_db_finalize_stmt(lda->insert_audio, "insert_audio");
00369
00370 if (lda->insert_artist)
00371 lms_db_finalize_stmt(lda->insert_artist, "insert_artist");
00372
00373 if (lda->insert_album)
00374 lms_db_finalize_stmt(lda->insert_album, "insert_album");
00375
00376 if (lda->insert_genre)
00377 lms_db_finalize_stmt(lda->insert_genre, "insert_genre");
00378
00379 if (lda->get_artist)
00380 lms_db_finalize_stmt(lda->get_artist, "get_artist");
00381
00382 if (lda->get_album)
00383 lms_db_finalize_stmt(lda->get_album, "get_album");
00384
00385 if (lda->get_genre)
00386 lms_db_finalize_stmt(lda->get_genre, "get_genre");
00387
00388 r = lms_db_cache_del(&_cache, lda->db, lda);
00389 free(lda);
00390
00391 return r;
00392 }
00393
00394 static int
00395 _db_get_id_by_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
00396 {
00397 int r, ret;
00398
00399 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
00400 if (ret != 0)
00401 goto done;
00402
00403 r = sqlite3_step(stmt);
00404 if (r == SQLITE_DONE) {
00405 ret = 1;
00406 goto done;
00407 }
00408
00409 if (r != SQLITE_ROW) {
00410 fprintf(stderr, "ERROR: could not get id by name: %s\n",
00411 sqlite3_errmsg(sqlite3_db_handle(stmt)));
00412 ret = -2;
00413 goto done;
00414 }
00415
00416 *id = sqlite3_column_int64(stmt, 0);
00417 ret = 0;
00418
00419 done:
00420 lms_db_reset_stmt(stmt);
00421
00422 return ret;
00423
00424 }
00425 static int
00426 _db_insert_name(sqlite3_stmt *stmt, const struct lms_string_size *name, int64_t *id)
00427 {
00428 int r, ret;
00429
00430 ret = lms_db_bind_text(stmt, 1, name->str, name->len);
00431 if (ret != 0)
00432 goto done;
00433
00434 r = sqlite3_step(stmt);
00435 if (r != SQLITE_DONE) {
00436 fprintf(stderr, "ERROR: could not insert name: %s\n",
00437 sqlite3_errmsg(sqlite3_db_handle(stmt)));
00438 ret = -2;
00439 goto done;
00440 }
00441
00442 *id = sqlite3_last_insert_rowid(sqlite3_db_handle(stmt));
00443 ret = 0;
00444
00445 done:
00446 lms_db_reset_stmt(stmt);
00447
00448 return ret;
00449 }
00450
00451 static int
00452 _db_get_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
00453 {
00454 return _db_get_id_by_name(lda->get_artist, &info->artist, artist_id);
00455 }
00456
00457 static int
00458 _db_insert_artist(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id)
00459 {
00460 int r;
00461
00462 if (!info->artist.str)
00463 return 1;
00464
00465 r =_db_get_artist(lda, info, artist_id);
00466 if (r == 0)
00467 return 0;
00468 else if (r < 0)
00469 return -1;
00470
00471 return _db_insert_name(lda->insert_artist, &info->artist, artist_id);
00472 }
00473
00474 static int
00475 _db_get_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *artist_id, int64_t *album_id)
00476 {
00477 sqlite3_stmt *stmt;
00478 int r, ret;
00479
00480 stmt = lda->get_album;
00481
00482 ret = lms_db_bind_text(stmt, 1, info->album.str, info->album.len);
00483 if (ret != 0)
00484 goto done;
00485
00486 ret = lms_db_bind_int64_or_null(stmt, 2, artist_id);
00487 if (ret != 0)
00488 goto done;
00489
00490 r = sqlite3_step(stmt);
00491 if (r == SQLITE_DONE) {
00492 ret = 1;
00493 goto done;
00494 }
00495
00496 if (r != SQLITE_ROW) {
00497 fprintf(stderr, "ERROR: could not get album from table: %s\n",
00498 sqlite3_errmsg(lda->db));
00499 ret = -2;
00500 goto done;
00501 }
00502
00503 *album_id = sqlite3_column_int64(stmt, 0);
00504 ret = 0;
00505
00506 done:
00507 lms_db_reset_stmt(stmt);
00508
00509 return ret;
00510
00511 }
00512
00513 static int
00514 _db_insert_album(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id)
00515 {
00516 int r, ret, ret_artist;
00517 int64_t artist_id;
00518 sqlite3_stmt *stmt;
00519
00520 if (!info->album.str)
00521 return 1;
00522
00523 ret_artist = _db_insert_artist(lda, info, &artist_id);
00524 if (ret_artist < 0)
00525 return -1;
00526
00527 r =_db_get_album(lda, info,
00528 (ret_artist == 0) ? &artist_id : NULL,
00529 album_id);
00530 if (r == 0)
00531 return 0;
00532 else if (r < 0)
00533 return -1;
00534
00535 stmt = lda->insert_album;
00536 ret = lms_db_bind_int64_or_null(stmt, 1,
00537 (ret_artist == 0) ? &artist_id : NULL);
00538 if (ret != 0)
00539 goto done;
00540
00541 ret = lms_db_bind_text(stmt, 2, info->album.str, info->album.len);
00542 if (ret != 0)
00543 goto done;
00544
00545 r = sqlite3_step(stmt);
00546 if (r != SQLITE_DONE) {
00547 fprintf(stderr, "ERROR: could not insert audio album: %s\n",
00548 sqlite3_errmsg(lda->db));
00549 ret = -3;
00550 goto done;
00551 }
00552
00553 *album_id = sqlite3_last_insert_rowid(lda->db);
00554 ret = 0;
00555
00556 done:
00557 lms_db_reset_stmt(stmt);
00558
00559 return ret;
00560 }
00561
00562 static int
00563 _db_get_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
00564 {
00565 return _db_get_id_by_name(lda->get_genre, &info->genre, genre_id);
00566 }
00567
00568 static int
00569 _db_insert_genre(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *genre_id)
00570 {
00571 int r;
00572
00573 if (!info->genre.str)
00574 return 1;
00575
00576 r =_db_get_genre(lda, info, genre_id);
00577 if (r == 0)
00578 return 0;
00579 else if (r < 0)
00580 return -1;
00581
00582 return _db_insert_name(lda->insert_genre, &info->genre, genre_id);
00583 }
00584
00585 static int
00586 _db_insert_audio(lms_db_audio_t *lda, const struct lms_audio_info *info, int64_t *album_id, int64_t *genre_id)
00587 {
00588 sqlite3_stmt *stmt;
00589 int r, ret;
00590
00591 stmt = lda->insert_audio;
00592 ret = lms_db_bind_int64(stmt, 1, info->id);
00593 if (ret != 0)
00594 goto done;
00595
00596 ret = lms_db_bind_text(stmt, 2, info->title.str, info->title.len);
00597 if (ret != 0)
00598 goto done;
00599
00600 ret = lms_db_bind_int64_or_null(stmt, 3, album_id);
00601 if (ret != 0)
00602 goto done;
00603
00604 ret = lms_db_bind_int64_or_null(stmt, 4, genre_id);
00605 if (ret != 0)
00606 goto done;
00607
00608 ret = lms_db_bind_int(stmt, 5, info->trackno);
00609 if (ret != 0)
00610 goto done;
00611
00612 ret = lms_db_bind_int(stmt, 6, info->rating);
00613 if (ret != 0)
00614 goto done;
00615
00616 ret = lms_db_bind_int(stmt, 7, info->playcnt);
00617 if (ret != 0)
00618 goto done;
00619
00620 r = sqlite3_step(stmt);
00621 if (r != SQLITE_DONE) {
00622 fprintf(stderr, "ERROR: could not insert audio info: %s\n",
00623 sqlite3_errmsg(lda->db));
00624 ret = -8;
00625 goto done;
00626 }
00627
00628 ret = 0;
00629
00630 done:
00631 lms_db_reset_stmt(stmt);
00632
00633 return ret;
00634 }
00635
00647 int
00648 lms_db_audio_add(lms_db_audio_t *lda, struct lms_audio_info *info)
00649 {
00650 int64_t album_id, genre_id;
00651 int ret_album, ret_genre;
00652
00653 if (!lda)
00654 return -1;
00655 if (!info)
00656 return -2;
00657 if (info->id < 1)
00658 return -3;
00659
00660 ret_album = _db_insert_album(lda, info, &album_id);
00661 if (ret_album < 0)
00662 return -4;
00663
00664 ret_genre = _db_insert_genre(lda, info, &genre_id);
00665 if (ret_genre < 0)
00666 return -5;
00667
00668 return _db_insert_audio(lda, info,
00669 (ret_album == 0) ? &album_id : NULL,
00670 (ret_genre == 0) ? &genre_id : NULL);
00671 }