void
pragha_database_delete_playlist (PraghaDatabase *database, const gchar *playlist)
{
gint playlist_id;
if (string_is_empty(playlist)) {
g_warning("Playlist name is NULL");
return;
}
playlist_id = pragha_database_find_playlist (database, playlist);
if (!playlist_id) {
g_warning("Playlist doesn't exist");
return;
}
pragha_database_flush_playlist (database, playlist_id);
const gchar *sql = "DELETE FROM PLAYLIST WHERE id = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, playlist_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
void
pragha_database_delete_radio (PraghaDatabase *database, const gchar *radio)
{
gint radio_id;
if (string_is_empty(radio)) {
g_warning("Radio name is NULL");
return;
}
radio_id = pragha_database_find_radio (database, radio);
if (!radio_id) {
g_warning("Radio doesn't exist");
return;
}
pragha_database_flush_radio (database, radio_id);
const gchar *sql = "DELETE FROM RADIO WHERE id = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, radio_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
GList *
prepend_song_with_artist_and_title_to_mobj_list(const gchar *artist,
const gchar *title,
GList *list,
struct con_win *cwin)
{
PraghaMusicobject *mobj = NULL;
gint location_id = 0;
if(pragha_mobj_list_already_has_title_of_artist(list, title, artist) ||
pragha_playlist_already_has_title_of_artist(cwin->cplaylist, title, artist))
return list;
const gchar *sql = "SELECT TRACK.title, ARTIST.name, LOCATION.id "
"FROM TRACK, ARTIST, LOCATION "
"WHERE ARTIST.id = TRACK.artist AND LOCATION.id = TRACK.location "
"AND TRACK.title = ? COLLATE NOCASE "
"AND ARTIST.name = ? COLLATE NOCASE "
"ORDER BY RANDOM() LIMIT 1;";
PraghaPreparedStatement *statement = pragha_database_create_statement (cwin->cdbase, sql);
pragha_prepared_statement_bind_string (statement, 1, title);
pragha_prepared_statement_bind_string (statement, 2, artist);
if (pragha_prepared_statement_step (statement)) {
location_id = pragha_prepared_statement_get_int (statement, 2);
mobj = new_musicobject_from_db (cwin->cdbase, location_id);
list = g_list_prepend (list, mobj);
}
pragha_prepared_statement_free (statement);
return list;
}
static void
pragha_application_provider_want_upgrade (PraghaDatabaseProvider *provider,
gint provider_id,
PraghaApplication *pragha)
{
PraghaDatabase *database;
PraghaScanner *scanner;
PraghaPreparedStatement *statement;
const gchar *sql, *provider_type = NULL;
sql = "SELECT name FROM provider_type WHERE id IN (SELECT type FROM provider WHERE id = ?)";
database = pragha_application_get_database (pragha);
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, provider_id);
if (pragha_prepared_statement_step (statement))
provider_type = pragha_prepared_statement_get_string (statement, 0);
if (g_ascii_strcasecmp (provider_type, "local") == 0)
{
scanner = pragha_application_get_scanner (pragha);
pragha_scanner_scan_library (scanner);
}
pragha_prepared_statement_free (statement);
}
void
pragha_database_flush_playlist (PraghaDatabase *database, gint playlist_id)
{
const gchar *sql = "DELETE FROM PLAYLIST_TRACKS WHERE playlist = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, playlist_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
void
pragha_database_flush_radio (PraghaDatabase *database, gint radio_id)
{
const gchar *sql = "DELETE FROM RADIO_TRACKS WHERE radio = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, radio_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
void
pragha_database_forget_location (PraghaDatabase *database, gint location_id)
{
const gchar *sql;
PraghaPreparedStatement *statement;
sql = "DELETE FROM TRACK WHERE location = ?";
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, location_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
sql = "DELETE FROM LOCATION WHERE id = ?";
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, location_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
void
pragha_database_add_playlist_track (PraghaDatabase *database, gint playlist_id, const gchar *file)
{
const gchar *sql = "INSERT INTO PLAYLIST_TRACKS (file, playlist) VALUES (?, ?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, file);
pragha_prepared_statement_bind_int (statement, 2, playlist_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
void
pragha_database_add_radio_track (PraghaDatabase *database, gint radio_id, const gchar *uri)
{
const gchar *sql = "INSERT INTO RADIO_TRACKS (uri, radio) VALUES (?, ?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, uri);
pragha_prepared_statement_bind_int (statement, 2, radio_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
gint
pragha_database_add_new_year (PraghaDatabase *database, guint year)
{
const gchar *sql = "INSERT INTO YEAR (year) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, year);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_year (database, year);
}
gint
pragha_database_add_new_genre (PraghaDatabase *database, const gchar *genre)
{
const gchar *sql = "INSERT INTO GENRE (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, genre);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_genre (database, genre);
}
gint
pragha_database_add_new_album (PraghaDatabase *database, const gchar *album)
{
const gchar *sql = "INSERT INTO ALBUM (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, album);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_album (database, album);
}
gint
pragha_database_add_new_mime_type (PraghaDatabase *database, const gchar *mime_type)
{
const gchar *sql = "INSERT INTO MIME_TYPE (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, mime_type);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_mime_type (database, mime_type);
}
gint
pragha_database_add_new_location (PraghaDatabase *database, const gchar *location)
{
const gchar *sql = "INSERT INTO LOCATION (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, location);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_location (database, location);
}
gint
pragha_database_add_new_playlist (PraghaDatabase *database, const gchar *playlist)
{
const gchar *sql = "INSERT INTO PLAYLIST (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, playlist);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_playlist (database, playlist);
}
gint
pragha_database_add_new_radio (PraghaDatabase *database, const gchar *radio)
{
const gchar *sql = "INSERT INTO RADIO (name) VALUES (?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, radio);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
return pragha_database_find_radio (database, radio);
}
gint
pragha_database_find_mime_type (PraghaDatabase *database, const gchar *mime_type)
{
gint mime_type_id = 0;
const gchar *sql = "SELECT id FROM MIME_TYPE WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, mime_type);
if (pragha_prepared_statement_step (statement))
mime_type_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return mime_type_id;
}
gint
pragha_database_find_album (PraghaDatabase *database, const gchar *album)
{
gint album_id = 0;
const gchar *sql = "SELECT id FROM ALBUM WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, album);
if (pragha_prepared_statement_step (statement))
album_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return album_id;
}
gint
pragha_database_find_genre (PraghaDatabase *database, const gchar *genre)
{
gint genre_id = 0;
const gchar *sql = "SELECT id FROM GENRE WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, genre);
if (pragha_prepared_statement_step (statement))
genre_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return genre_id;
}
gint
pragha_database_find_comment (PraghaDatabase *database, const gchar *comment)
{
gint comment_id = 0;
const gchar *sql = "SELECT id FROM COMMENT WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, comment);
if (pragha_prepared_statement_step (statement))
comment_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return comment_id;
}
gint
pragha_database_find_year (PraghaDatabase *database, gint year)
{
gint year_id = 0;
const gchar *sql = "SELECT id FROM YEAR WHERE year = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, year);
if (pragha_prepared_statement_step (statement))
year_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return year_id;
}
gint
pragha_database_find_playlist (PraghaDatabase *database, const gchar *playlist)
{
gint playlist_id = 0;
const gchar *sql = "SELECT id FROM PLAYLIST WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, playlist);
if (pragha_prepared_statement_step (statement))
playlist_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return playlist_id;
}
gint
pragha_database_find_radio (PraghaDatabase *database, const gchar *radio)
{
gint radio_id = 0;
const gchar *sql = "SELECT id FROM RADIO WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, radio);
if (pragha_prepared_statement_step (statement))
radio_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return radio_id;
}
gint
pragha_database_find_location (PraghaDatabase *database, const gchar *location)
{
gint location_id = 0;
const gchar *sql = "SELECT id FROM LOCATION WHERE name = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, location);
if (pragha_prepared_statement_step (statement))
location_id = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return location_id;
}
gint
pragha_database_get_version (PraghaDatabase *database)
{
gint version = 0;
const gchar *sql = "PRAGMA user_version";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
if (pragha_prepared_statement_step (statement))
version = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return version;
}
static void
pragha_database_add_new_track (PraghaDatabase *database,
gint location_id,
gint mime_type_id,
gint artist_id,
gint album_id,
gint genre_id,
gint year_id,
gint comment_id,
guint track_no,
gint length,
gint channels,
gint bitrate,
gint samplerate,
const gchar *title)
{
const gchar *sql = "INSERT INTO TRACK ("
"location, "
"file_type, "
"track_no, "
"artist, "
"album, "
"genre, "
"year, "
"comment, "
"bitrate, "
"samplerate, "
"length, "
"channels, "
"title) "
"VALUES "
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_int (statement, 1, location_id);
pragha_prepared_statement_bind_int (statement, 2, mime_type_id);
pragha_prepared_statement_bind_int (statement, 3, track_no);
pragha_prepared_statement_bind_int (statement, 4, artist_id);
pragha_prepared_statement_bind_int (statement, 5, album_id);
pragha_prepared_statement_bind_int (statement, 6, genre_id);
pragha_prepared_statement_bind_int (statement, 7, year_id);
pragha_prepared_statement_bind_int (statement, 8, comment_id);
pragha_prepared_statement_bind_int (statement, 9, bitrate);
pragha_prepared_statement_bind_int (statement, 10, samplerate);
pragha_prepared_statement_bind_int (statement, 11, length);
pragha_prepared_statement_bind_int (statement, 12, channels);
pragha_prepared_statement_bind_string (statement, 13, title);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
gint
pragha_database_get_playlist_count (PraghaDatabase *database)
{
gint n_playlists = 0;
const gchar *sql = "SELECT COUNT() FROM PLAYLIST WHERE name != ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, SAVE_PLAYLIST_STATE);
if (pragha_prepared_statement_step (statement))
n_playlists = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
return n_playlists;
}
void
pragha_database_delete_dir (PraghaDatabase *database, const gchar *dir_name)
{
const gchar *sql;
PraghaPreparedStatement *statement;
gchar *mask = g_strconcat (dir_name, "%", NULL);
/* Delete all tracks under the given dir */
sql = "DELETE FROM TRACK WHERE location IN (SELECT id FROM LOCATION WHERE NAME LIKE ?)";
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, mask);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
/* Delete the location entries */
sql = "DELETE FROM LOCATION WHERE name LIKE ?";
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, mask);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
/* Delete all entries from PLAYLIST_TRACKS which match given dir */
sql = "DELETE FROM PLAYLIST_TRACKS WHERE file LIKE ?";
statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, mask);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
/* Now flush unused artists, albums, genres, years */
pragha_database_flush_stale_entries (database);
g_free (mask);
}
static gint
pragha_database_get_table_count (PraghaDatabase *database, const gchar *table)
{
gchar *sql;
gint count = 0;
sql = g_strdup_printf ("SELECT COUNT() FROM %s;", table);
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
if (pragha_prepared_statement_step (statement))
count = pragha_prepared_statement_get_int (statement, 0);
pragha_prepared_statement_free (statement);
g_free (sql);
return count;
}
void
pragha_database_update_radio_name (PraghaDatabase *database, const gchar *old_name, const gchar *new_name)
{
gint radio_id = pragha_database_find_radio (database, old_name);
if (!radio_id)
return;
const gchar *sql = "UPDATE RADIO SET name = ? WHERE id = ?";
PraghaPreparedStatement *statement = pragha_database_create_statement (database, sql);
pragha_prepared_statement_bind_string (statement, 1, new_name);
pragha_prepared_statement_bind_int (statement, 2, radio_id);
pragha_prepared_statement_step (statement);
pragha_prepared_statement_free (statement);
}
