bool CrateDAO::deleteCrate(const int crateId) {
ScopedTransaction transaction(m_database);
QSqlQuery query(m_database);
query.prepare("DELETE FROM " CRATE_TRACKS_TABLE " WHERE crate_id = :id");
query.bindValue(":id", crateId);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return false;
}
query.prepare("DELETE FROM " CRATE_TABLE " WHERE id = :id");
query.bindValue(":id", crateId);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return false;
}
transaction.commit();
emit(deleted(crateId));
// Update in-memory map
for (QMultiHash<int, int>::iterator it = m_cratesTrackIsIn.begin();
it != m_cratesTrackIsIn.end();) {
if (it.value() == crateId) {
it = m_cratesTrackIsIn.erase(it);
} else {
it++;
}
}
return true;
}
void AutoDJCratesDAO::slotCrateTrackRemoved(int crateId, int trackId) {
// Skip this if it's not an auto-DJ crate.
if (!m_rCrateDAO.isCrateInAutoDj(crateId))
return;
// Remove a crate-reference from this track.
ScopedTransaction oTransaction(m_rDatabase);
QSqlQuery oQuery(m_rDatabase);
// UPDATE temp_autodj_crates SET craterefs = craterefs - 1 WHERE track_id = :track_id;
oQuery.prepare("UPDATE " AUTODJCRATES_TABLE " SET "
AUTODJCRATESTABLE_CRATEREFS " = " AUTODJCRATESTABLE_CRATEREFS
" - 1 WHERE " AUTODJCRATESTABLE_TRACKID " = :track_id");
oQuery.bindValue(":track_id", trackId);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return;
}
// Remove the track if it no longer has a crate reference.
//DELETE FROM temp_autodj_crates WHERE track_id = :track_id AND craterefs = 0;
oQuery.prepare("DELETE FROM "AUTODJCRATES_TABLE " WHERE "
AUTODJCRATESTABLE_TRACKID " = :track_id AND "
AUTODJCRATESTABLE_CRATEREFS " = 0");
oQuery.bindValue(":track_id", trackId);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return;
}
// The transaction was successful.
oTransaction.commit();
}
void PlaylistDAO::deletePlaylist(const int playlistId) {
//qDebug() << "PlaylistDAO::deletePlaylist" << QThread::currentThread() << m_database.connectionName();
ScopedTransaction transaction(m_database);
// Get the playlist id for this
QSqlQuery query(m_database);
// Delete the row in the Playlists table.
query.prepare("DELETE FROM Playlists "
"WHERE id= :id");
query.bindValue(":id", playlistId);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return;
}
// Delete the tracks in this playlist from the PlaylistTracks table.
query.prepare("DELETE FROM PlaylistTracks "
"WHERE playlist_id = :id");
query.bindValue(":id", playlistId);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return;
}
transaction.commit();
//TODO: Crap, we need to shuffle the positions of all the playlists?
emit(deleted(playlistId));
}
void AnalysisDao::deleteAnalyses(const QList<TrackId>& trackIds) {
QStringList idList;
for (const auto& trackId: trackIds) {
idList << trackId.toString();
}
QSqlQuery query(m_db);
query.prepare(QString("SELECT track_analysis.id FROM track_analysis WHERE "
"track_id in (%1)").arg(idList.join(",")));
if (!query.exec()) {
LOG_FAILED_QUERY(query) << "couldn't delete analysis";
}
const int idColumn = query.record().indexOf("id");
while (query.next()) {
int id = query.value(idColumn).toInt();
QString dataPath = getAnalysisStoragePath().absoluteFilePath(
QString::number(id));
qDebug() << dataPath;
deleteFile(dataPath);
}
query.prepare(QString("DELETE FROM track_analysis "
"WHERE track_id in (%1)").arg(idList.join(",")));
if (!query.exec()) {
LOG_FAILED_QUERY(query) << "couldn't delete analysis";
}
}
void CrateDAO::populateCrateMembershipCache() {
// get the count to allocate HashMap
int tracksInCratesCount = 0;
QSqlQuery query(m_database);
query.prepare("SELECT COUNT(*) from " CRATE_TRACKS_TABLE);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
}
tracksInCratesCount = query.value(0).toInt();
m_cratesTrackIsIn.reserve(tracksInCratesCount);
// now fetch all Tracks from all crates and insert them into the hashmap
query.prepare("SELECT track_id, crate_id from " CRATE_TRACKS_TABLE);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
}
const int trackIdColumn = query.record().indexOf("track_id");
const int crateIdColumn = query.record().indexOf("crate_id");
while (query.next()) {
m_cratesTrackIsIn.insert(query.value(trackIdColumn).toInt(),
query.value(crateIdColumn).toInt());
}
}
void BaseExternalPlaylistModel::setPlaylist(QString playlist_path) {
QSqlQuery finder_query(m_database);
finder_query.prepare(QString("SELECT id from %1 where name=:name").arg(m_playlistsTable));
finder_query.bindValue(":name", playlist_path);
if (!finder_query.exec()) {
LOG_FAILED_QUERY(finder_query) << "Error getting id for playlist:" << playlist_path;
return;
}
// TODO(XXX): Why not last-insert id?
int playlistId = -1;
QSqlRecord finder_query_record = finder_query.record();
while (finder_query.next()) {
playlistId = finder_query.value(
finder_query_record.indexOf("id")).toInt();
}
if (playlistId == -1) {
qDebug() << "ERROR: Could not get the playlist ID for playlist:" << playlist_path;
return;
}
QString playlistViewTable = QString("%1_%2").arg(m_playlistTracksTable,
QString::number(playlistId));
QStringList columns;
columns << "track_id";
columns << "position";
columns << "'' AS " + LIBRARYTABLE_PREVIEW;
QSqlQuery query(m_database);
FieldEscaper f(m_database);
QString queryString = QString(
"CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
"SELECT %2 FROM %3 WHERE playlist_id = %4")
.arg(f.escapeString(playlistViewTable),
columns.join(","),
m_playlistTracksTable,
QString::number(playlistId));
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query) << "Error creating temporary view for playlist.";
return;
}
columns[2] = LIBRARYTABLE_PREVIEW;
setTable(playlistViewTable, columns[0], columns, m_trackSource);
setDefaultSort(fieldIndex(ColumnCache::COLUMN_PLAYLISTTRACKSTABLE_POSITION),
Qt::AscendingOrder);
setSearch("");
}
// Update the number of auto-DJ-playlist references to each track in the
// auto-DJ-crates database.
bool AutoDJCratesDAO::updateAutoDjPlaylistReferences() {
QSqlQuery oQuery(m_rDatabase);
// Rebuild the auto-DJ-playlist reference count.
// INSERT OR REPLACE INTO temp_autodj_crates (track_id, craterefs, timesplayed, autodjrefs) SELECT * FROM (SELECT PlaylistTracks.track_id, craterefs, timesplayed, COUNT (*) AS newautodjrefs FROM PlaylistTracks, temp_autodj_crates WHERE PlaylistTracks.playlist_id IN (SELECT id FROM Playlists WHERE hidden = 1) AND PlaylistTracks.track_id = temp_autodj_crates.track_id GROUP BY PlaylistTracks.track_id) WHERE newautodjrefs > 0;
QString strHidden;
strHidden.setNum(PlaylistDAO::PLHT_AUTO_DJ);
QString strQuery(QString ("INSERT OR REPLACE INTO " AUTODJCRATES_TABLE
" (" AUTODJCRATESTABLE_TRACKID ", " AUTODJCRATESTABLE_CRATEREFS ", "
AUTODJCRATESTABLE_TIMESPLAYED ", " AUTODJCRATESTABLE_AUTODJREFS ")"
" SELECT * FROM (SELECT " PLAYLIST_TRACKS_TABLE ".%1, "
AUTODJCRATESTABLE_CRATEREFS ", " AUTODJCRATESTABLE_TIMESPLAYED
", COUNT (*) AS new" AUTODJCRATESTABLE_AUTODJREFS " FROM "
PLAYLIST_TRACKS_TABLE ", " AUTODJCRATES_TABLE " WHERE "
PLAYLIST_TRACKS_TABLE ".%2 IN (SELECT %3 FROM " PLAYLIST_TABLE
" WHERE %4 = %5) AND " PLAYLIST_TRACKS_TABLE ".%1 = "
AUTODJCRATES_TABLE "." AUTODJCRATESTABLE_TRACKID " GROUP BY "
PLAYLIST_TRACKS_TABLE ".%1) WHERE new" AUTODJCRATESTABLE_AUTODJREFS
" > 0")
.arg(PLAYLISTTRACKSTABLE_TRACKID, // %1
PLAYLISTTRACKSTABLE_PLAYLISTID, // %2
PLAYLISTTABLE_ID, // %3
PLAYLISTTABLE_HIDDEN, // %4
strHidden)); // %5
oQuery.prepare(strQuery);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return false;
}
// Incorporate all tracks loaded into decks.
// Each track has to be done as a separate database query, in case the same
// track is loaded into multiple decks.
int iDecks = (int) PlayerManager::numDecks();
for (int i = 0; i < iDecks; ++i) {
QString group = PlayerManager::groupForDeck(i);
TrackPointer pTrack = PlayerInfo::Instance().getTrackInfo(group);
if (pTrack) {
int iTrackId = pTrack->getId();
// UPDATE temp_autodj_crates SET autodjrefs = autodjrefs + 1 WHERE track_id IN (:track_id);
oQuery.prepare("UPDATE " AUTODJCRATES_TABLE " SET "
AUTODJCRATESTABLE_AUTODJREFS " = " AUTODJCRATESTABLE_AUTODJREFS
" + 1 WHERE " AUTODJCRATESTABLE_TRACKID " IN (:track_id)");
oQuery.bindValue(":track_id", iTrackId);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return false;
}
}
}
return true;
}
// Signaled by the track DAO when a track's information is updated.
void AutoDJCratesDAO::slotTrackDirty(int trackId) {
// Update our record of the number of times played, if that changed.
TrackPointer pTrack = m_rTrackDAO.getTrack(trackId);
if (pTrack == NULL) {
return;
}
int iPlayed = pTrack->getTimesPlayed();
if (iPlayed == 0) {
return;
}
// Update our record of how many times this track has been played.
// UPDATE temp_autodj_crates SET timesplayed = :newplayed WHERE track_id = :track_id AND timesplayed = :oldplayed;
QSqlQuery oQuery(m_rDatabase);
oQuery.prepare("UPDATE " AUTODJCRATES_TABLE " SET "
AUTODJCRATESTABLE_TIMESPLAYED " = :newplayed WHERE "
AUTODJCRATESTABLE_TRACKID " = :track_id AND "
AUTODJCRATESTABLE_TIMESPLAYED " = :oldplayed");
oQuery.bindValue(":track_id", trackId);
oQuery.bindValue(":oldplayed", iPlayed - 1);
oQuery.bindValue(":newplayed", iPlayed);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return;
}
}
int CrateDAO::getCrateIdByName(const QString& name) {
QString pattern("(.*)\x20(\\(([1-9]\\d*|0)\\))");
QRegExp rxnum(pattern);
QSqlQuery query(m_database);
query.prepare("SELECT id,name FROM " CRATE_TABLE);
if (query.exec()) {
while (query.next()) {
int queryID = query.value(0).toInt();
QString queryName = query.value(1).toString();
if (queryName == name) {
return queryID;
} else if (rxnum.exactMatch(queryName)) {
QString originalName = rxnum.cap(1);
if (originalName == name) {
return queryID;
}
}
}
return -1;
} else {
LOG_FAILED_QUERY(query);
}
return -1;
}
BaseExternalTrackModel::BaseExternalTrackModel(QObject* parent,
TrackCollection* pTrackCollection,
const char* settingsNamespace,
const QString& trackTable,
QSharedPointer<BaseTrackCache> trackSource)
: BaseSqlTableModel(parent, pTrackCollection, settingsNamespace) {
QString viewTable = trackTable + "_view";
QStringList columns;
columns << "id";
columns << "'' AS " + LIBRARYTABLE_PREVIEW;
QSqlQuery query(m_database);
FieldEscaper f(m_database);
QString queryString = QString(
"CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
"SELECT %2 FROM %3")
.arg(f.escapeString(viewTable),
columns.join(","),
f.escapeString(trackTable));
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query) <<
"Error creating temporary view for" << trackTable;
return;
}
columns[1] = LIBRARYTABLE_PREVIEW;
setTable(viewTable, columns[0], columns, trackSource);
setDefaultSort(fieldIndex("artist"), Qt::AscendingOrder);
}
int PlaylistDAO::getPlaylistIdFromName(QString name) {
//qDebug() << "PlaylistDAO::getPlaylistIdFromName" << QThread::currentThread() << m_database.connectionName();
// since now showing the number of the tracks in every playlist is through change the "name" column
// in "playlists" table, so here we need a regular expression to match.
QString pattern("(.*)\x20(\\(([1-9]\\d*|0)\\))");
QRegExp rxnum(pattern);
QSqlQuery query(m_database);
query.prepare("SELECT id,name FROM Playlists");
if (query.exec()) {
while (query.next()) {
int queryID = query.value(0).toInt();
QString queryName = query.value(1).toString();
//qDebug() << "queryID:" << queryID
// << "queryName:" << queryName;
if (queryName == name) {
return queryID;
} else if (rxnum.exactMatch(queryName)) {
QString originalName = rxnum.cap(1);
if (originalName == name) {
return queryID;
}
}
}
return -1;
} else {
LOG_FAILED_QUERY(query);
}
return -1;
}
int CrateDAO::addTracksToCrate(const int crateId, QList<int>* trackIdList) {
ScopedTransaction transaction(m_database);
QSqlQuery query(m_database);
query.prepare("INSERT INTO " CRATE_TRACKS_TABLE " (crate_id, track_id) VALUES (:crate_id, :track_id)");
for (int i = 0; i < trackIdList->size(); ++i) {
query.bindValue(":crate_id", crateId);
query.bindValue(":track_id", trackIdList->at(i));
if (!query.exec()) {
LOG_FAILED_QUERY(query);
// We must emit only those trackID that were added so we need to
// remove the failed ones.
trackIdList->removeAt(i);
--i; // account for reduced size of list
}
}
transaction.commit();
// Emitting the trackAdded signals for each trackID outside the transaction
foreach(int trackId, *trackIdList) {
emit(trackAdded(crateId, trackId));
}
emit(changed(crateId));
// Return the number of tracks successfully added
return trackIdList->size();
}
QList<Cue*> CueDAO::getCuesForTrack(const int trackId) const {
//qDebug() << "CueDAO::getCuesForTrack" << QThread::currentThread() << m_database.connectionName();
QList<Cue*> cues;
QSqlQuery query(m_database);
query.prepare("SELECT * FROM " CUE_TABLE " WHERE track_id = :id");
query.bindValue(":id", trackId);
if (query.exec()) {
while (query.next()) {
Cue* cue = NULL;
int cueId = query.value(query.record().indexOf("id")).toInt();
if (m_cues.contains(cueId)) {
cue = m_cues[cueId];
}
if (cue == NULL) {
cue = cueFromRow(query);
}
if (cue != NULL) {
cues.push_back(cue);
}
}
} else {
LOG_FAILED_QUERY(query);
}
return cues;
}
bool CrateDAO::setCrateInAutoDj(int crateId, bool bIn) {
// SQLite3 doesn't support boolean value. Using integer instead.
int iIn = bIn ? 1 : 0;
// Mark this crate as being in/out-of the auto-DJ list.
QSqlQuery query(m_database);
// UPDATE crates SET autodj = :in WHERE id = :id AND autodj = :existing;
query.prepare(QString("UPDATE " CRATE_TABLE
" SET %1 = :in WHERE %2 = :id AND %1 = :existing")
.arg(CRATETABLE_AUTODJ_SOURCE) // %1
.arg(CRATETABLE_ID)); // %2
query.bindValue(":in", iIn);
query.bindValue(":id", crateId);
query.bindValue(":existing", 1 - iIn);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return false;
}
// Notify listeners if the auto-DJ status of a crate has changed.
bool bChange = (query.numRowsAffected() > 0);
if (bChange) {
emit(autoDjChanged(crateId, bIn));
}
// Let our caller know if there was a change.
return bChange;
}
// Update the last-played date/time for each track in the auto-DJ-crates
// database.
bool AutoDJCratesDAO::updateLastPlayedDateTime() {
QSqlQuery oQuery(m_rDatabase);
// Rebuild the auto-DJ-playlist last-played date/time.
// INSERT OR REPLACE INTO temp_autodj_crates (track_id, craterefs, timesplayed, autodjrefs, lastplayed) SELECT * FROM (SELECT PlaylistTracks.track_id, craterefs, timesplayed, autodjrefs, MAX(pl_datetime_added) AS newlastplayed FROM PlaylistTracks, temp_autodj_crates WHERE PlaylistTracks.playlist_id IN (SELECT id FROM Playlists WHERE hidden = 2) AND PlaylistTracks.track_id = temp_autodj_crates.track_id GROUP BY PlaylistTracks.track_id) WHERE newlastplayed != "";
QString strSetLog;
strSetLog.setNum(PlaylistDAO::PLHT_SET_LOG);
QString strQuery(QString ("INSERT OR REPLACE INTO " AUTODJCRATES_TABLE
" (" AUTODJCRATESTABLE_TRACKID ", " AUTODJCRATESTABLE_CRATEREFS ", "
AUTODJCRATESTABLE_TIMESPLAYED ", " AUTODJCRATESTABLE_AUTODJREFS ", "
AUTODJCRATESTABLE_LASTPLAYED ")"
" SELECT * FROM (SELECT " PLAYLIST_TRACKS_TABLE ".%1, "
AUTODJCRATESTABLE_CRATEREFS ", " AUTODJCRATESTABLE_TIMESPLAYED ", "
AUTODJCRATESTABLE_AUTODJREFS ", MAX(%3) AS new"
AUTODJCRATESTABLE_LASTPLAYED " FROM " PLAYLIST_TRACKS_TABLE ", "
AUTODJCRATES_TABLE " WHERE " PLAYLIST_TRACKS_TABLE
".%2 IN (SELECT %4 FROM " PLAYLIST_TABLE " WHERE %5 = %6) AND "
PLAYLIST_TRACKS_TABLE ".%1 = " AUTODJCRATES_TABLE "."
AUTODJCRATESTABLE_TRACKID " GROUP BY " PLAYLIST_TRACKS_TABLE
".%1) WHERE new" AUTODJCRATESTABLE_LASTPLAYED " != \"\"")
.arg(PLAYLISTTRACKSTABLE_TRACKID, // %1
PLAYLISTTRACKSTABLE_PLAYLISTID, // %2
PLAYLISTTRACKSTABLE_DATETIMEADDED, // %3
PLAYLISTTABLE_ID, // %4
PLAYLISTTABLE_HIDDEN, // %5
strSetLog)); // %6
oQuery.prepare(strQuery);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return false;
}
return true;
}
// Signaled by the PlayerInfo singleton when a track is loaded to a deck.
void AutoDJCratesDAO::slotPlayerInfoTrackLoaded(QString a_strGroup,
TrackPointer a_pTrack) {
// This gets called with a null track during an unload. Filter that out.
if (a_pTrack == NULL) {
return;
}
// This counts as an auto-DJ reference. The idea is to prevent tracks that
// are loaded into a deck from being randomly chosen.
int iTrackId = a_pTrack->getId();
unsigned int numDecks = PlayerManager::numDecks();
for (unsigned int i = 0; i < numDecks; ++i) {
if (a_strGroup == PlayerManager::groupForDeck(i)) {
// Update the number of auto-DJ-playlist references to this track.
QSqlQuery oQuery(m_rDatabase);
// UPDATE temp_autodj_crates SET autodjrefs = autodjrefs + 1 WHERE track_id = :track_id;
oQuery.prepare("UPDATE " AUTODJCRATES_TABLE " SET "
AUTODJCRATESTABLE_AUTODJREFS " = " AUTODJCRATESTABLE_AUTODJREFS
" + 1 WHERE " AUTODJCRATESTABLE_TRACKID " = :track_id");
oQuery.bindValue(":track_id", iTrackId);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return;
}
return;
}
}
}
// Update the number of auto-DJ-playlist references to the given track in the
// auto-DJ-crates database.
bool AutoDJCratesDAO::updateAutoDjPlaylistReferencesForTrack(int trackId) {
QSqlQuery oQuery(m_rDatabase);
// INSERT OR REPLACE INTO temp_autodj_crates (track_id, craterefs, timesplayed, autodjrefs) SELECT * FROM (SELECT :track_id AS new_track_id, craterefs, timesplayed, COUNT (*) AS newautodjrefs FROM PlaylistTracks, temp_autodj_crates WHERE PlaylistTracks.playlist_id IN (SELECT id FROM Playlists WHERE hidden = 1) AND PlaylistTracks.track_id = :track_id AND temp_autodj_crates.track_id = :track_id GROUP BY new_track_id) WHERE newautodjrefs > 0;
QString strHidden;
strHidden.setNum(PlaylistDAO::PLHT_AUTO_DJ);
oQuery.prepare(QString("INSERT OR REPLACE INTO " AUTODJCRATES_TABLE " ("
AUTODJCRATESTABLE_TRACKID ", " AUTODJCRATESTABLE_CRATEREFS ", "
AUTODJCRATESTABLE_TIMESPLAYED ", " AUTODJCRATESTABLE_AUTODJREFS
") SELECT * FROM (SELECT :track_id_1 AS new_track_id, "
AUTODJCRATESTABLE_CRATEREFS ", " AUTODJCRATESTABLE_TIMESPLAYED
", COUNT (*) AS new" AUTODJCRATESTABLE_AUTODJREFS " FROM "
PLAYLIST_TRACKS_TABLE ", " AUTODJCRATES_TABLE " WHERE "
PLAYLIST_TRACKS_TABLE ".%1 IN (SELECT %4 FROM " PLAYLIST_TABLE
" WHERE %2 = %5) AND " PLAYLIST_TRACKS_TABLE ".%3 = :track_id_2 AND "
AUTODJCRATES_TABLE "." AUTODJCRATESTABLE_TRACKID
" = :track_id_3 GROUP BY new_track_id) WHERE new"
AUTODJCRATESTABLE_AUTODJREFS " > 0")
.arg(PLAYLISTTRACKSTABLE_PLAYLISTID, // %1
PLAYLISTTABLE_HIDDEN, // %2
PLAYLISTTRACKSTABLE_TRACKID, // %3
PLAYLISTTABLE_ID, // %4
strHidden)); // %5
oQuery.bindValue(":track_id_1", trackId);
oQuery.bindValue(":track_id_2", trackId);
oQuery.bindValue(":track_id_3", trackId);
if (!oQuery.exec()) {
LOG_FAILED_QUERY(oQuery);
return false;
}
// The update was successful.
return true;
}
void LibraryTableModel::setTableModel(int id){
Q_UNUSED(id);
QStringList columns;
columns << "library."+LIBRARYTABLE_ID << "'' as preview";
QString tableName = "library_view";
QSqlQuery query(m_pTrackCollection->getDatabase());
QString queryString = "CREATE TEMPORARY VIEW IF NOT EXISTS "+tableName+" AS "
"SELECT " + columns.join(", ") +
" FROM library INNER JOIN track_locations "
"ON library.location = track_locations.id "
"WHERE (" + LibraryTableModel::DEFAULT_LIBRARYFILTER + ")";
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
}
QStringList tableColumns;
tableColumns << LIBRARYTABLE_ID;
tableColumns << "preview";
setTable(tableName, LIBRARYTABLE_ID, tableColumns,
m_pTrackCollection->getTrackSource("default"));
// BaseSqlTabelModel will setup the header info
initHeaderData();
setSearch("");
setDefaultSort(fieldIndex("artist"), Qt::AscendingOrder);
}
bool PlaylistDAO::appendTracksToPlaylist(const QList<int>& trackIds, const int playlistId) {
// qDebug() << "PlaylistDAO::appendTracksToPlaylist"
// << QThread::currentThread() << m_database.connectionName();
// Start the transaction
ScopedTransaction transaction(m_database);
int position = getMaxPosition(playlistId);
// Append after the last song. If no songs or a failed query then 0 becomes 1.
++position;
//Insert the song into the PlaylistTracks table
QSqlQuery query(m_database);
query.prepare("INSERT INTO PlaylistTracks (playlist_id, track_id, position, pl_datetime_added)"
"VALUES (:playlist_id, :track_id, :position, CURRENT_TIMESTAMP)");
query.bindValue(":playlist_id", playlistId);
int insertPosition = position;
foreach (int trackId, trackIds) {
query.bindValue(":track_id", trackId);
query.bindValue(":position", insertPosition++);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return false;
}
}
void BaseSqlTableModel::select() {
if (!m_bInitialized) {
return;
}
// We should be able to detect when a select() would be a no-op. The DAO's
// do not currently broadcast signals for when common things happen. In the
// future, we can turn this check on and avoid a lot of needless
// select()'s. rryan 9/2011
// if (!m_bDirty) {
// if (sDebug) {
// qDebug() << this << "Skipping non-dirty select()";
// }
// return;
// }
if (sDebug) {
qDebug() << this << "select()";
}
QTime time;
time.start();
QString columns = m_tableColumnsJoined;
QString orderBy = orderByClause();
QString queryString = QString("SELECT %1 FROM %2 %3")
.arg(columns, m_tableName, orderBy);
if (sDebug) {
qDebug() << this << "select() executing:" << queryString;
}
QSqlQuery query(m_database);
// This causes a memory savings since QSqlCachedResult (what QtSQLite uses)
// won't allocate a giant in-memory table that we won't use at all.
query.setForwardOnly(true);
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return;
}
// Remove all the rows from the table. We wait to do this until after the
// table query has succeeded. See Bug #1090888.
// TODO(rryan) we could edit the table in place instead of clearing it?
if (m_rowInfo.size() > 0) {
beginRemoveRows(QModelIndex(), 0, m_rowInfo.size()-1);
m_rowInfo.clear();
m_trackIdToRows.clear();
endRemoveRows();
}
QSqlRecord record = query.record();
int idColumn = record.indexOf(m_idColumn);
QLinkedList<int> tableColumnIndices;
foreach (QString column, m_tableColumns) {
Q_ASSERT(record.indexOf(column) == m_tableColumnIndex[column]);
tableColumnIndices.push_back(record.indexOf(column));
}
int PlaylistDAO::createPlaylist(QString name, HiddenType hidden)
{
// qDebug() << "PlaylistDAO::createPlaylist"
// << QThread::currentThread()
// << m_database.connectionName();
//Start the transaction
ScopedTransaction transaction(m_database);
//Find out the highest position for the existing playlists so we know what
//position this playlist should have.
QSqlQuery query(m_database);
query.prepare("SELECT max(position) as posmax FROM Playlists");
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return -1;
}
//Get the id of the last playlist.
int position = 0;
if (query.next()) {
position = query.value(query.record().indexOf("posmax")).toInt();
position++; //Append after the last playlist.
}
//qDebug() << "Inserting playlist" << name << "at position" << position;
query.prepare("INSERT INTO Playlists (name, position, hidden, date_created, date_modified) "
"VALUES (:name, :position, :hidden, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)");
query.bindValue(":name", name);
query.bindValue(":position", position);
query.bindValue(":hidden", static_cast<int>(hidden));
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return -1;
}
int playlistId = query.lastInsertId().toInt();
//Commit the transaction
transaction.commit();
updatePlaylistsTitleNum();
emit(added(playlistId));
return playlistId;
}
unsigned int CrateDAO::crateCount() {
QSqlQuery query(m_database);
query.prepare("SELECT count(*) FROM " CRATE_TABLE);
if (!query.exec() || !query.next()) {
LOG_FAILED_QUERY(query);
return 0;
}
return query.value(0).toInt();
}
LibraryScanner::~LibraryScanner() {
// IMPORTANT NOTE: This code runs in the GUI thread, so it should _NOT_ use
// the m_trackDao that lives inside this class. It should use
// the DAOs that live in m_pTrackCollection.
if (isRunning()) {
// Cancel any running library scan...
cancel();
wait(); // Wait for thread to finish
}
// Do housekeeping on the LibraryHashes table.
ScopedTransaction transaction(m_pCollection->getDatabase());
// Mark the corresponding file locations in the track_locations table as deleted
// if we find one or more deleted directories.
QStringList deletedDirs;
QSqlQuery query(m_pCollection->getDatabase());
query.prepare("SELECT directory_path FROM LibraryHashes "
"WHERE directory_deleted=1");
if (query.exec()) {
const int directoryPathColumn = query.record().indexOf("directory_path");
while (query.next()) {
QString directory = query.value(directoryPathColumn).toString();
deletedDirs << directory;
}
} else {
LOG_FAILED_QUERY(query) << "Couldn't SELECT deleted directories.";
}
// Delete any directories that have been marked as deleted...
query.finish();
query.exec("DELETE FROM LibraryHashes "
"WHERE directory_deleted=1");
// Print out any SQL error, if there was one.
if (query.lastError().isValid()) {
LOG_FAILED_QUERY(query);
}
foreach (QString dir, deletedDirs) {
m_pCollection->getTrackDAO().markTrackLocationsAsDeleted(dir);
}
void LibraryHashDAO::removeDeletedDirectoryHashes()
{
QSqlQuery query(m_database);
query.prepare("DELETE FROM LibraryHashes WHERE "
"directory_deleted=:directory_deleted");
query.bindValue(":directory_deleted", 1);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
}
}
void BansheePlaylistModel::dropTempTable() {
if (m_playlistId >= 0) {
// Clear old playlist
m_playlistId = -1;
QSqlQuery query(m_pTrackCollection->database());
QString strQuery("DROP TABLE IF EXISTS %1");
if (!query.exec(strQuery.arg(m_tempTableName))) {
LOG_FAILED_QUERY(query);
}
}
}
void PlaylistDAO::renamePlaylist(const int playlistId, const QString& newName) {
QSqlQuery query(m_database);
query.prepare("UPDATE Playlists SET name = :name WHERE id = :id");
query.bindValue(":name", newName);
query.bindValue(":id", playlistId);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return;
}
emit(renamed(playlistId, newName));
}
int DirectoryDAO::removeDirectory(const QString& dir) {
QSqlQuery query(m_database);
query.prepare("DELETE FROM " % DIRECTORYDAO_TABLE % " WHERE "
% DIRECTORYDAO_DIR % "= :dir");
query.bindValue(":dir", dir);
if (!query.exec()) {
LOG_FAILED_QUERY(query) << "purging dir (" % dir % ") failed";
return SQL_ERROR;
}
return ALL_FINE;
}
void PlaylistTableModel::setTableModel(int playlistId) {
//qDebug() << "PlaylistTableModel::setTableModel" << playlistId;
if (m_iPlaylistId == playlistId) {
qDebug() << "Already focused on playlist " << playlistId;
return;
}
m_iPlaylistId = playlistId;
QString playlistTableName = "playlist_" + QString::number(m_iPlaylistId);
QSqlQuery query(m_database);
FieldEscaper escaper(m_database);
QStringList columns;
columns << PLAYLISTTRACKSTABLE_TRACKID + " AS " + LIBRARYTABLE_ID
<< PLAYLISTTRACKSTABLE_POSITION
<< PLAYLISTTRACKSTABLE_DATETIMEADDED
<< "'' AS " + LIBRARYTABLE_PREVIEW
// For sorting the cover art column we give LIBRARYTABLE_COVERART
// the same value as the cover hash.
<< LIBRARYTABLE_COVERART_HASH + " AS " + LIBRARYTABLE_COVERART;
// We drop files that have been explicitly deleted from mixxx
// (mixxx_deleted=0) from the view. There was a bug in <= 1.9.0 where
// removed files were not removed from playlists, so some users will have
// libraries where this is the case.
QString queryString = QString("CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
"SELECT %2 FROM PlaylistTracks "
"INNER JOIN library ON library.id = PlaylistTracks.track_id "
"WHERE PlaylistTracks.playlist_id = %3")
.arg(escaper.escapeString(playlistTableName),
columns.join(","),
QString::number(playlistId));
if (!m_showAll) {
queryString.append(" AND library.mixxx_deleted = 0");
}
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
}
columns[0] = LIBRARYTABLE_ID;
// columns[1] = PLAYLISTTRACKSTABLE_POSITION from above
// columns[2] = PLAYLISTTRACKSTABLE_DATETIMEADDED from above
columns[3] = LIBRARYTABLE_PREVIEW;
columns[4] = LIBRARYTABLE_COVERART;
setTable(playlistTableName, LIBRARYTABLE_ID, columns,
m_pTrackCollection->getTrackSource());
setSearch("");
setDefaultSort(fieldIndex(ColumnCache::COLUMN_PLAYLISTTRACKSTABLE_POSITION), Qt::AscendingOrder);
setSort(defaultSortColumn(), defaultSortOrder());
connect(&m_playlistDao, SIGNAL(changed(int)),
this, SLOT(playlistChanged(int)));
}
void LibraryHashDAO::invalidateAllDirectories()
{
//qDebug() << "LibraryHashDAO::invalidateAllDirectories"
//<< QThread::currentThread() << m_database.connectionName();
QSqlQuery query(m_database);
query.prepare("UPDATE LibraryHashes "
"SET needs_verification=1");
if (!query.exec()) {
LOG_FAILED_QUERY(query)
<< "Couldn't mark directories previously hashed as needing verification.";
}
}
void LibraryHashDAO::markAsVerified(const QString& dirPath) {
//qDebug() << "LibraryHashDAO::markExisting" << QThread::currentThread() << m_database.connectionName();
QSqlQuery query(m_database);
query.prepare("UPDATE LibraryHashes "
"SET needs_verification=0 "
"WHERE directory_path=:directory_path");
// query.bindValue(":directory_deleted", 0);
query.bindValue(":directory_path", dirPath);
if (!query.exec()) {
LOG_FAILED_QUERY(query) << "Updating dirhash to mark as verified failed.";
}
}
