src/lib/lightmediascanner_db_audio.c

Go to the documentation of this file.
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) /* fast path for unknown artist */
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) /* fast path for unknown album */
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) /* fast path for unknown genre */
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 }

Generated on Thu Dec 13 02:04:03 2007 for Light Media Scanner by  doxygen 1.5.2