/*!
* \brief Clear orphaned entries from the genre, artist, album and albumart
* tables
*
* \returns Nothing.
*/
void FileScanner::cleanDB()
{
MythScreenStack *popupStack = GetMythMainWindow()->GetStack("popup stack");
QString message = QObject::tr("Cleaning music database");
MythUIProgressDialog *clean_progress = new MythUIProgressDialog(message,
popupStack,
"cleaningprogressdialog");
if (clean_progress->Create())
{
popupStack->AddScreen(clean_progress, false);
clean_progress->SetTotal(4);
}
else
{
delete clean_progress;
clean_progress = NULL;
}
uint counter = 0;
MSqlQuery query(MSqlQuery::InitCon());
MSqlQuery deletequery(MSqlQuery::InitCon());
if (!query.exec("SELECT g.genre_id FROM music_genres g "
"LEFT JOIN music_songs s ON g.genre_id=s.genre_id "
"WHERE s.genre_id IS NULL;"))
MythDB::DBError("FileScanner::cleanDB - select music_genres", query);
while (query.next())
{
int genreid = query.value(0).toInt();
deletequery.prepare("DELETE FROM music_genres WHERE genre_id=:GENREID");
deletequery.bindValue(":GENREID", genreid);
if (!deletequery.exec())
MythDB::DBError("FileScanner::cleanDB - delete music_genres",
deletequery);
}
if (clean_progress)
clean_progress->SetProgress(++counter);
if (!query.exec("SELECT a.album_id FROM music_albums a "
"LEFT JOIN music_songs s ON a.album_id=s.album_id "
"WHERE s.album_id IS NULL;"))
MythDB::DBError("FileScanner::cleanDB - select music_albums", query);
while (query.next())
{
int albumid = query.value(0).toInt();
deletequery.prepare("DELETE FROM music_albums WHERE album_id=:ALBUMID");
deletequery.bindValue(":ALBUMID", albumid);
if (!deletequery.exec())
MythDB::DBError("FileScanner::cleanDB - delete music_albums",
deletequery);
}
if (clean_progress)
clean_progress->SetProgress(++counter);
if (!query.exec("SELECT a.artist_id FROM music_artists a "
"LEFT JOIN music_songs s ON a.artist_id=s.artist_id "
"LEFT JOIN music_albums l ON a.artist_id=l.artist_id "
"WHERE s.artist_id IS NULL AND l.artist_id IS NULL"))
MythDB::DBError("FileScanner::cleanDB - select music_artists", query);
while (query.next())
{
int artistid = query.value(0).toInt();
deletequery.prepare("DELETE FROM music_artists WHERE artist_id=:ARTISTID");
deletequery.bindValue(":ARTISTID", artistid);
if (!deletequery.exec())
MythDB::DBError("FileScanner::cleanDB - delete music_artists",
deletequery);
}
if (clean_progress)
clean_progress->SetProgress(++counter);
if (!query.exec("SELECT a.albumart_id FROM music_albumart a LEFT JOIN "
"music_songs s ON a.song_id=s.song_id WHERE "
"embedded='1' AND s.song_id IS NULL;"))
MythDB::DBError("FileScanner::cleanDB - select music_albumart", query);
while (query.next())
{
int albumartid = query.value(0).toInt();
deletequery.prepare("DELETE FROM music_albumart WHERE albumart_id=:ALBUMARTID");
deletequery.bindValue(":ALBUMARTID", albumartid);
if (!deletequery.exec())
MythDB::DBError("FileScanner::cleanDB - delete music_albumart",
deletequery);
}
if (clean_progress)
{
clean_progress->SetProgress(++counter);
clean_progress->Close();
}
}
/*!
* \brief Clear orphaned entries from the genre, artist, album and albumart
* tables
*
* \returns Nothing.
*/
void MusicFileScanner::cleanDB()
{
LOG(VB_GENERAL, LOG_INFO, "Cleaning old entries from music database");
MSqlQuery query(MSqlQuery::InitCon());
MSqlQuery deletequery(MSqlQuery::InitCon());
// delete unused genre_ids from music_genres
if (!query.exec("SELECT g.genre_id FROM music_genres g "
"LEFT JOIN music_songs s ON g.genre_id=s.genre_id "
"WHERE s.genre_id IS NULL;"))
MythDB::DBError("MusicFileScanner::cleanDB - select music_genres", query);
deletequery.prepare("DELETE FROM music_genres WHERE genre_id=:GENREID");
while (query.next())
{
int genreid = query.value(0).toInt();
deletequery.bindValue(":GENREID", genreid);
if (!deletequery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - delete music_genres",
deletequery);
}
// delete unused album_ids from music_albums
if (!query.exec("SELECT a.album_id FROM music_albums a "
"LEFT JOIN music_songs s ON a.album_id=s.album_id "
"WHERE s.album_id IS NULL;"))
MythDB::DBError("MusicFileScanner::cleanDB - select music_albums", query);
deletequery.prepare("DELETE FROM music_albums WHERE album_id=:ALBUMID");
while (query.next())
{
int albumid = query.value(0).toInt();
deletequery.bindValue(":ALBUMID", albumid);
if (!deletequery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - delete music_albums",
deletequery);
}
// delete unused artist_ids from music_artists
if (!query.exec("SELECT a.artist_id FROM music_artists a "
"LEFT JOIN music_songs s ON a.artist_id=s.artist_id "
"LEFT JOIN music_albums l ON a.artist_id=l.artist_id "
"WHERE s.artist_id IS NULL AND l.artist_id IS NULL"))
MythDB::DBError("MusicFileScanner::cleanDB - select music_artists", query);
deletequery.prepare("DELETE FROM music_artists WHERE artist_id=:ARTISTID");
while (query.next())
{
int artistid = query.value(0).toInt();
deletequery.bindValue(":ARTISTID", artistid);
if (!deletequery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - delete music_artists",
deletequery);
}
// delete unused directory_ids from music_directories
// get a list of directory_ids not referenced in music_songs
if (!query.exec("SELECT d.directory_id, d.parent_id FROM music_directories d "
"LEFT JOIN music_songs s ON d.directory_id=s.directory_id "
"WHERE s.directory_id IS NULL ORDER BY directory_id DESC;"))
MythDB::DBError("MusicFileScanner::cleanDB - select music_directories", query);
deletequery.prepare("DELETE FROM music_directories WHERE directory_id=:DIRECTORYID");
MSqlQuery parentquery(MSqlQuery::InitCon());
parentquery.prepare("SELECT COUNT(*) FROM music_directories "
"WHERE parent_id=:DIRECTORYID ");
int deletedCount;
do
{
deletedCount = 0;
if (!query.first())
break;
// loop through the list of unused directory_ids deleting any which
// aren't referenced by any other directories parent_id
do
{
int directoryid = query.value(0).toInt();
// have we still got references to this directory_id from other directories
parentquery.bindValue(":DIRECTORYID", directoryid);
if (!parentquery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - get parent directory count",
parentquery);
if (parentquery.next())
{
int parentCount = parentquery.value(0).toInt();
if (parentCount == 0)
{
deletequery.bindValue(":DIRECTORYID", directoryid);
if (!deletequery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - delete music_directories",
deletequery);
deletedCount += deletequery.numRowsAffected();
}
}
} while (query.next());
} while (deletedCount);
// delete unused albumart_ids from music_albumart (embedded images)
if (!query.exec("SELECT a.albumart_id FROM music_albumart a LEFT JOIN "
"music_songs s ON a.song_id=s.song_id WHERE "
"embedded='1' AND s.song_id IS NULL;"))
MythDB::DBError("MusicFileScanner::cleanDB - select music_albumart", query);
deletequery.prepare("DELETE FROM music_albumart WHERE albumart_id=:ALBUMARTID");
while (query.next())
{
int albumartid = query.value(0).toInt();
deletequery.bindValue(":ALBUMARTID", albumartid);
if (!deletequery.exec())
MythDB::DBError("MusicFileScanner::cleanDB - delete music_albumart",
deletequery);
}
}
