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